PGA , SGA , MEMORY Advisors

PGA Advisor

Sql>select pga_target_for_estimate,pga_target_factor,estd_extra_bytes_rw from v$pga_target_advice;

In the above figure, look at 5^th row
Value=1358954496 is the current orcldb.__pga_aggregate_target  value  (set in initorcldb.ora)

So, Subtract pga_target_for_estimate - estd_extra_bytes_rw for 5^th row , 1358954496-954314752

equals to 404639744 i.e eqals to 386 MB.

So, conclusion is that adding extra 386 MB will improve performance(less I/O would be needed).

SGA Advisor

Sql>select sga_size,sga_size_factor,estd_db_time from v$sga_target_advice;

DB_TIME is an overall figure for the amount of time spent taken within the database to execute SQL;

Minimizing DB_TIME is the overall objective of all tuning.

Look at the 3^rd row i.e value 1952 MB , this is the value of parameter orcldb.__sga_target being set

in initorcldb.ora.

So,It can be seen that if the SGA is raised from its current value of 1952 MB to 3904 MB then

DB_TIME would reduce.

MEMORY TARGET Advisor

Sql>select memory_size,memory_size_factor,estd_db_time from v$memory_target_advice;

This query is against the memory target advisor, which gives advice on the total (SGA plus PGA) memory allocation.Here, the value of MEMORY_SIZE in 3rd row is equal to the value of parameter memory_target mentioned in initorcldb.ora.The above figure shows that the optimal value is 3248 MB which is the current value already set.So, no changes are needed.

TUNING BUFFER CACHE

Tuning the Buffer Cache

 In Oracle database all the operations ( select, DML, DDL) are not done directly in the physical files, but in an intermediate memory area named Buffer Cache. If a row is modified, first the modification is done in this area. If you want to access a row, first Oracle will take a look in this area to see if the information is here. If the information is here the data is sent to the user or modified in function of the user request. In this case, Oracle name this event a HIT. If the information is not present in this area (in the Buffer Cache) the information is read from the disk and put in this memory zone (in the Buffer Cache). In this case, Oracle name this event a MISS.

One time modified and written to the disk or sent to the user, data is kept in this area for further use. If new data has to be brought in the Buffer Cache and in no room there, the oldest data is replaced by the newest one.

 A well optimized database mustn't have too many reloads of the data in the Buffer Cache. To evaluate the reload of data The Buffer Cache Hit Ratio is calculated.

This ratio could be calculated for the entire instance (since the database was started):

SQL> SELECT (P1.VALUE+P2.VALUE-P3.VALUE)/ (P1.VALUE+P2.VALUE)*100 "BUFFER  HIT RATIO" FROM v$sysstat P1, v$sysstat P2, v$sysstat P3

  2  WHERE P1.NAME=LOWER ('DB BLOCK GETS')

  3  AND P2.NAME=LOWER ('CONSISTENT GETS')

  4  AND P3.NAME=LOWER ('PHYSICAL READS');

BUFFER HIT RATIO

----------------

      96.5158211

Ratio should be above 90%, if not increase the shared_pool_size

If the cache-hit ratio goes below 90% then:

* For Oracle 8 and earlier: increase the initialisation parameter DB_BLOCK_BUFFERS.

* For Oracle 9 onwards: increate the initialisation parameter DB_CACHE_SIZE.”

If the Buffer Cache  is well tuned and no full table scans run in the database, this Ratio must be grater than 90%.

SQL> alter system set db_cache_advice=on ;

System altered.

SQL> select NAME,SIZE_FOR_ESTIMATE "BC size(M)",SIZE_FACTOR,ESTD_PHYSICAL_READ_FACTOR es_phy_r_f,ESTD_PHYSICAL_READS es_phy_r from v$db_cache_advice;

NAME                 BC size(M) SIZE_FACTOR ES_PHY_R_F   ES_PHY_R

-------------------- ---------- ----------- ---------- ----------

DEFAULT                      28       .0909     2.1867      27531

DEFAULT                      56       .1818     1.3393      16862

DEFAULT                      84       .2727     1.1498      14476

DEFAULT                     112       .3636     1.0095      12709

DEFAULT                     140       .4545          1      12590

DEFAULT                     168       .5455          1      12590

DEFAULT                     196       .6364          1      12590

DEFAULT                     224       .7273          1      12590

DEFAULT                     252       .8182          1      12590

DEFAULT                     280       .9091          1      12590

DEFAULT                     308           1              1      12590

DEFAULT                     336      1.0909          1      12590

DEFAULT                     364      1.1818          1      12590

DEFAULT                     392      1.2727          1      12590

DEFAULT                     420      1.3636          1      12590

DEFAULT                     448      1.4545          1      12590

DEFAULT                     476      1.5455          1      12590

DEFAULT                     504      1.6364          1      12590

DEFAULT                     532      1.7273          1      12590

DEFAULT                     560      1.8182          1      12590

20 rows selected.

Acc to above :-

CASE 1: IF BUFFER CACHE is set to 84 mb , then physical reads are 14476.

CASE 2:  IF BUFFER CACHE is set to 112 mb , then physical reads are 12709.

CASE 3:  IF BUFFER CACHE is set to 140 mb , then physical reads are 12590.

If the BC will be bigger than 140 Mb there is no gain on the database performance, so having a 140 Mb Buffer Cache could be a good solution for this system.  The size of the Buffer Cache is managed by DB_CACHE_SIZE initialization parameter.

Resizing Buffer Cache:

Now check the maximum size that SGA that can grow on.Compare the SGA Parameter size with the calculated size from the Shared Pool, Buffer Cache and Redo log sizes along with the java pool.

Sql>show parameter sga_max_size

Sql>select sum(value) as "SGA Size" from V$parameter

Where name in ('Shared_pool_size','db_cache_size','log_buffer','java_pool-size');

If the size is very close then decrease one size in order to increase the other.

Check the shared_pool and buffer_cache sizes individually.

Sql>alter system set DB_cache_size=100M;

Sql>alter system set shared_pool_size=200m;

Example—

In my database

acc to above 3 cases BC can be set 140MB, so as to give satisfactory performance .

ALTER SYSTEM SET db_cache_advice=OFF;

-- Then, decrease the Buffer Cache size to 140 Megabytes

ALTER SYSTEM SET db_cache_size=140M

-- Increase the Shared Pool size equal to the amount u decrease buffer cache size i.e  equal to buffer cache size – 140M let’s say 300M

ALTER SYSTEM SET shared_pool_size= 300M

PHYSICAL STANDBY DATABASE 11g R2

Target database : orcl (10.0.0.160) (primary database)

Auxiliary database: orclstby (10.0.0.161) (standby database)

Check that the primary database is in archivelog mode.

Sql > select log_mode from v$database;

If it is noarchivelog mode, switch it to archivelog mode.

Sql > SHUTDOWN IMMEDIATE

Sql > STARTUP MOUNT;

Sql > ALTER DATABASE ARCHIVELOG;

Sql > ALTER DATABASE OPEN;

Sql> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1=’LOCATION=/u01/orcl_archives’ SCOPE=SPFILE;

Enabled forced logging by issuing the following command

Sql > ALTER DATABASE FORCE LOGGING;

Add standby logfiles on primary database (it is recommended to create standby redo logs on primary database to make things easier during switchover and failover)

     
      Sql > ALTER DATABASE ADD STANDBY LOGFILE GROUP 4      

                 ‘/u01/app/oracle/oradata/orcl/redo04.log’  SIZE 15M; 

      Sql > ALTER DATABASE ADD STANDBY LOGFILE GROUP 5   

                 ‘/u01/app/oracle/oradata/orcl/redo05.log’  SIZE 15M; 

     Sql > ALTER DATABASE ADD STANDBY LOGFILE GROUP 6    

                 ‘/u01/app/oracle/oradata/orcl/redo06.log’  SIZE 15M; 

The DB_NAME of the standby database will be the same as that of the primary, but it must have a different DB_UNIQUE_NAME value. The DB_UNIQUE_NAME values of the primary and standby database should be used in the DG_CONFIG setting of the LOG_ARCHIVE_CONFIG parameter. For this example, the standby database will have the value "DB11G_STBY".

   
      Sql > ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(orcl,orclstby)';

Set suitable remote archive log destinations.

      
      Sql > ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=orclstby NOAFFIRM  ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orclstby';    

Sql > ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

The LOG_ARCHIVE_FORMAT and LOG_ARCHIVE_MAX_PROCESSES parameters must be set to appropriate values and the REMOTE_LOGIN_PASSWORDFILE must be set to exclusive.

Sql > ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;

Sql > ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;

Sql > ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;

In addition to the previous setting, it is recommended to make sure the primary is ready to switch roles to become a standby. For that to work properly we need to set the following parameters. Adjust the *_CONVERT parameters to account for your filename and patch differences between the servers.

 
       Sql > ALTER SYSTEM SET FAL_SERVER=orclstby;

 Sql > ALTER SYSTEM SET DB_FILE_NAME_CONVERT='orclstby','orcl' SCOPE=SPFILE;

Sql > ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='orclstby','orcl'  SCOPE=SPFILE;

Sql > ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;

Remember, some of the parameters are not modifiable, so the database will need to be restarted before they take effect.

PREPARE STANDBY DATABASE

·         Install oracle 11g R2 binaries on standby machine i.e on 10.0.0.161

·         Create directories on standby at the desired locations (refer to fig 1.2)

·         Copy password file to standby database(password file of my primary database is orapworcl ,so I copied it to standby database and changed its name to orapworclstby)

·         Configure tnsnames.ora and listener.ora on both primary (orcl) and standby (orclstby) database

Listener.ora of orcl (primary database)

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

      (GLOBAL_DBNAME = orcl)

      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

      (SID_NAME = orcl)

    ) )

          LISTENER =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

    )

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.160)(PORT = 1521))

    ))

      ADR_BASE_LISTENER = /u01/app/oracle

tnsnames.ora of orcl (primary database)

ORCL =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.160)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

      (INSTANCE_NAME = orcl)

    ))

           ORCLSTBY =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.161)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orclstby)

      (INSTANCE_NAME = orclstby)

    ) )

      Listener.ora of orclstby (standby database)

   

SID_LIST_LISTENER =

  (SID_LIST =

    (SID_DESC =

       (GLOBAL_DBNAME = orclstby)

       (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)

       (SID_NAME = orclstby)

     ))

LISTENER =

   (DESCRIPTION_LIST =

      (DESCRIPTION =

        (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))

      )

      (DESCRIPTION =

         (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.161)(PORT = 1521))

      ))

ADR_BASE_LISTENER = /u01/app/oracle

tnsnames.ora of orclstby (standby database)

ORCL =

  (DESCRIPTION =

   (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.160)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orcl)

      (INSTANCE_NAME = orcl)

    ))

ORCLSTBY =

  (DESCRIPTION =

   (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.0.0.161)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = orclstby)

      (INSTANCE_NAME = orclstby)

    ))

·         Create pfile for standby database

On primary database, execute

Sql > CREATE PFILE='/u01/initorclstby.ora' FROM SPFILE;

 Alter the above pfile and transfer it to standby database

So , pfile (initorclstby.ora) of my standby database (orclstby) is :

orclstby.__db_cache_size=109051904

orclstby.__java_pool_size=4194304

orclstby.__large_pool_size=4194304

orclstby.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment

orclstby.__pga_aggregate_target=184549376

orclstby.__sga_target=251658240

orclstby.__shared_io_pool_size=0

orclstby.__shared_pool_size=125829120

orclstby.__streams_pool_size=0

audit_file_dest='/u01/app/oracle/admin/orclstby/adump'

audit_trail='db'

compatible='11.2.0.0.0'

control_files='/u01/app/oracle/oradata/orclstby/control01.ctl','/u01/app/oracle/flash_recovery_area/orclstby/control02.ctl'

db_block_size=8192

db_domain='localdomain'

db_unique_name='orclstby'

db_file_name_convert='orcl','orclstby'

db_name='orcl'

db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

db_recovery_file_dest_size=4039114752

diagnostic_dest='/u01/app/oracle'

dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

fal_server='orcl'

log_archive_config='DG_CONFIG=(orcl,orclstby)'

log_archive_dest_1='location=/u01/orcl_archives'

log_archive_dest_2='SERVICE=orcl ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'

log_archive_dest_state_2='ENABLE'

log_archive_format='%t_%s_%r.arc'

log_archive_max_processes=30

log_archive_start=TRUE

log_file_name_convert='orcl','orclstby'

memory_target=433061888

open_cursors=300

processes=150

remote_login_passwordfile='EXCLUSIVE'

standby_file_management='AUTO'

undo_tablespace='UNDOTBS1'

TAKING BACKUP OF PRIMARY DATABASE USING RMAN

-bash-3.2$ rman

          Recovery Manager: Release 11.2.0.1.0 - Production on Thu Nov 24 14:41:20 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

RMAN >  connect target sys@orcl

target database Password:

connected to target database: ORCL (DBID=1295591331)

RMAN >  backup database include current controlfile for standby plus archivelog;

Starting backup at 24-NOV-11

current log archived

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=101 device type=DISK

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=3 RECID=1 STAMP=767885366

input archived log thread=1 sequence=4 RECID=2 STAMP=767885391

input archived log thread=1 sequence=5 RECID=3 STAMP=767886484

input archived log thread=1 sequence=6 RECID=4 STAMP=767886616

input archived log thread=1 sequence=7 RECID=6 STAMP=767899462

input archived log thread=1 sequence=8 RECID=5 STAMP=767899353

input archived log thread=1 sequence=9 RECID=7 STAMP=767906298

input archived log thread=1 sequence=10 RECID=8 STAMP=767907740

input archived log thread=1 sequence=11 RECID=9 STAMP=767908826

input archived log thread=1 sequence=12 RECID=10 STAMP=767908911

input archived log thread=1 sequence=13 RECID=11 STAMP=767916311

input archived log thread=1 sequence=14 RECID=12 STAMP=767917336

input archived log thread=1 sequence=15 RECID=13 STAMP=767950266

input archived log thread=1 sequence=16 RECID=14 STAMP=767986293

input archived log thread=1 sequence=17 RECID=15 STAMP=768003827

input archived log thread=1 sequence=18 RECID=16 STAMP=768036665

input archived log thread=1 sequence=19 RECID=17 STAMP=768059623

input archived log thread=1 sequence=20 RECID=18 STAMP=768060626

input archived log thread=1 sequence=21 RECID=19 STAMP=768062538

channel ORA_DISK_1: starting piece 1 at 24-NOV-11

channel ORA_DISK_1: finished piece 1 at 24-NOV-11

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/o1_mf_annnn_TAG20111124T144222_7dw2my5x_.bkp tag=TAG20111124T144222 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:01:38

Finished backup at 24-NOV-11

Starting backup at 24-NOV-11

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/example01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf

channel ORA_DISK_1: starting piece 1 at 24-NOV-11

channel ORA_DISK_1: finished piece 1 at 24-NOV-11

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/o1_mf_nnndf_TAG20111124T144408_7dw2q1qv_.bkp tag=TAG20111124T144408 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:03:04

channel ORA_DISK_1: starting full datafile backup set

channel ORA_DISK_1: specifying datafile(s) in backup set

including standby control file in backup set

channel ORA_DISK_1: starting piece 1 at 24-NOV-11

channel ORA_DISK_1: finished piece 1 at 24-NOV-11

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/o1_mf_ncnnf_TAG20111124T144408_7dw2ww2x_.bkp tag=TAG20111124T144408 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 24-NOV-11

Starting backup at 24-NOV-11

current log archived

using channel ORA_DISK_1

channel ORA_DISK_1: starting archived log backup set

channel ORA_DISK_1: specifying archived log(s) in backup set

input archived log thread=1 sequence=22 RECID=20 STAMP=768062838

channel ORA_DISK_1: starting piece 1 at 24-NOV-11

channel ORA_DISK_1: finished piece 1 at 24-NOV-11

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/o1_mf_annnn_TAG20111124T144718_7dw2x0bk_.bkp tag=TAG20111124T144718 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01

Finished backup at 24-NOV-11

Starting Control File Autobackup at 24-NOV-11

piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_24/o1_mf_n_768062841_7dw2x23z_.bkp comment=NONE

Finished Control File Autobackup at 24-NOV-11

RMAN>

 COPY ALL BACKUP AND ARCHIVES FROM PRIMARY DATABASE TO STANDBY DATABASE 

Location of archives of orcl (primary database) on 10.0.0.160 is /u01/ and these archives are copied on standby 10.0.0.161 at exactly the same location (/u01/).

·        Location of backupset of orcl (primary) on 10.0.0.160 is /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_22/ , /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/

              and

             /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_22/ ,

             /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_24/

              And so, they are copied on standby 10.0.0.161 at exactly the same location i.e

Location of backupset of orclstby (standby) on 10.0.0.161 is /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_22/ , /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/

              and

              /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_22/ ,

              /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/

So, directories  location on primary i.e orcl (10.0.0.160) are:

Fig 1.1

And directories location on standby i.e orclstby (10.0.0.161) are:

Fig.1.2

Notice: in above figures the path i.e /u01/app/oracle/flash_recovery_area/ORCL/autobackup/ on primary (orcl 10.0.0.160) and /u01/app/oracle/flash_recovery_area/ORCL/backupset/ on standby (orclstby 10.0.0.161) are exactly same.

CREATE STANDBY DATABASE (orclstby) USING DUPLICATE COMMAND

On standby database , orclstby (i.e at 10.0.0.161)

-bash-3.2$ export ORACLE_SID=orclstby

-bash-3.2$ sqlplus

SQL*Plus: Release 11.2.0.1.0 Production on Thu Nov 24 13:47:15 2011

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Enter user-name: sys@orclstby as sysdba

Enter password:

Connected to an idle instance.

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorclstby.ora';

ORACLE instance started.

Total System Global Area  435224576 bytes

Fixed Size                  1337044 bytes

Variable Size             318769452 bytes

Database Buffers          109051904 bytes

Redo Buffers                6066176 bytes

-bash-3.2$ rman

RMAN> connect target

RMAN>  duplicate target database for standby nofilenamecheck dorecover;

Starting Duplicate Db at 24-NOV-11

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=20 device type=DISK

contents of Memory Script:

{

   set until scn  1041555;

   restore clone standby controlfile;

}

executing Memory Script

executing command: SET until clause

Starting restore at 24-NOV-11

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_24/o1_mf_n_768062841_7dw2x23z_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/autobackup/2011_11_24/o1_mf_n_768062841_7dw2x23z_.bkp tag=TAG20111124T144721

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03

output file name=/u01/app/oracle/oradata/orclstby/control01.ctl

output file name=/u01/app/oracle/flash_recovery_area/orclstby/control02.ctl

Finished restore at 24-NOV-11

contents of Memory Script:

{

   sql clone 'alter database mount standby database';

}

executing Memory Script

sql statement: alter database mount standby database

contents of Memory Script:

{

   set until scn  1041555;

   set newname for tempfile  1 to

 "/u01/app/oracle/oradata/orclstby/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

"/u01/app/oracle/oradata/orclstby/system01.dbf";

   set newname for datafile  2 to

 "/u01/app/oracle/oradata/orclstby/sysaux01.dbf";

   set newname for datafile  3 to

 "/u01/app/oracle/oradata/orclstby/undotbs01.dbf";

   set newname for datafile  4 to

 "/u01/app/oracle/oradata/orclstby/users01.dbf";

   set newname for datafile  5 to

 "/u01/app/oracle/oradata/orclstby/example01.dbf";

   restore

   clone database

   ;

}

executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

renamed tempfile 1 to /u01/app/oracle/oradata/orclstby/temp01.dbf in control file

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 24-NOV-11

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backup set restore

channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set

channel ORA_AUX_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/orclstby/system01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/orclstby/sysaux01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/orclstby/undotbs01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/orclstby/users01.dbf

channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orclstby/example01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/o1_mf_nnndf_TAG20111124T144408_7dw2q1qv_.bkp

channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/flash_recovery_area/ORCL/backupset/2011_11_24/o1_mf_nnndf_TAG20111124T144408_7dw2q1qv_.bkp tag=TAG20111124T144408

channel ORA_AUX_DISK_1: restored backup piece 1

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:07

Finished restore at 24-NOV-11

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=3 STAMP=768070406 file name=/u01/app/oracle/oradata/orclstby/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=4 STAMP=768070406 file name=/u01/app/oracle/oradata/orclstby/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=5 STAMP=768070406 file name=/u01/app/oracle/oradata/orclstby/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=6 STAMP=768070406 file name=/u01/app/oracle/oradata/orclstby/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=7 STAMP=768070406 file name=/u01/app/oracle/oradata/orclstby/example01.dbf

contents of Memory Script:

{

   set until scn  1041555;

   recover

   standby

   clone database

    delete archivelog

   ;

}

executing Memory Script

executing command: SET until clause

Starting recover at 24-NOV-11

using channel ORA_AUX_DISK_1

starting media recovery

archived log for thread 1 with sequence 22 is already on disk as file /u01/orcl_archives1_22_767810598.arc

archived log for thread 1 with sequence 23 is already on disk as file /u01/orcl_archives1_23_767810598.arc

archived log for thread 1 with sequence 24 is already on disk as file /u01/orcl_archives1_24_767810598.arc

archived log file name=/u01/orcl_archives1_22_767810598.arc thread=1 sequence=22

archived log file name=/u01/orcl_archives1_23_767810598.arc thread=1 sequence=23

archived log file name=/u01/orcl_archives1_24_767810598.arc thread=1 sequence=24

media recovery complete, elapsed time: 00:00:08

Finished recover at 24-NOV-11

Finished Duplicate Db at 24-NOV-11

RMAN>

Test Log Transport by executing command ALTER SYSTEM SWITCH LOGFILE on primary database and then checking SEQUENCE# at standby database

So , on standby database ::

SQL> select sequence#,first_time,next_time from v$archived_log order by sequence#;

SEQUENCE# FIRST_TIM NEXT_TIME

---------- --------- ---------

        25 24-NOV-11 24-NOV-11

        26 24-NOV-11 24-NOV-11

        27 24-NOV-11 24-NOV-11

SQL> select protection_mode from v$database;

PROTECTION_MODE

--------------------

MAXIMUM PERFORMANCE

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

-------------------- ----------------

MOUNTED              PHYSICAL STANDBY

SQL> shu immediate

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  435224576 bytes

Fixed Size                  1337044 bytes

Variable Size             318769452 bytes

Database Buffers          109051904 bytes

Redo Buffers                6066176 bytes

Database mounted.

Database opened.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

-------------------- ----------------

READ ONLY            PHYSICAL STANDBY

Start the apply process on standby server

SQL>  alter database recover managed standby database disconnect from session;

Database altered.

SQL> select open_mode,database_role from v$database;

OPEN_MODE            DATABASE_ROLE

-------------------- ----------------

READ ONLY WITH APPLY PHYSICAL STANDBY