Standby Database creation with RMAN Duplicate

Posted By Pankaj Yadav

Setup: Physical Standby Data Guard Creation with RMAN, step by step 

	Primary Site	Standby Site
Hostname	pk.prod.com	pk.standby.com
Database Name	prod	prod
Database Unique Name	prod	standby
Net Service Name	prod	standby

In this article, I explained the step by step process to create a physical standby database using RMAN duplicate command without shutting down the primary Database.

1.      Enable Database Force Logging

2.      Create SRL (Standby Redo Logs)

3.      Modify Init.ora parameters

4.      Enable Archiving

5.      Create a init.ora file for Standby Database ( Optional )

6.      Configure listener.ora and tnsnames.ora

7.      Copy init.ora and password  files to Standby Server

Standby Site Preparation :

1.      Create a remote login password file ( if it is not copied from primary site )

2.      Create a directory structure

3.      Modify the init.ora parameter file (Optional) [oracle@pk ~]$ sqlplus / as sysdba

[oracle@pk ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Wed Aug 28 08:21:24 2013

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

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select name from v$database;

NAME

---------

PROD

SQL>  select name,db_unique_name,database_role,switchover_status from v$database

;

NAME      DB_UNIQUE_NAME                 DATABASE_ROLE    SWITCHOVER_STATUS

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

PROD      prod                           PRIMARY          SESSIONS ACTIVE

 SQL> select FORCE_LOGGING from v$database;

FOR

---

NO

SQL>  ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> select name,user from v$database;

NAME      USER

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

PROD      SYS

SQL> col group# for 9999

SQL> col member for a50

SQL> set linesize 120

SQL> select name,user from v$database;

NAME      USER

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

PROD      SYS

SQL> select GROUP#,THREAD#,MEMBERS,BYTES FROM V$LOG;

GROUP#    THREAD#    MEMBERS      BYTES

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

     1          1          1   52428800

     2          1          1   52428800

     3          1          1   52428800

SQL> select group#,member from v$logfile;

GROUP# MEMBER

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

     3 /home/oracle/oracle/product/10.2.0/oradata/prod/redo03.log

     2 /home/oracle/oracle/product/10.2.0/oradata/prod/redo02.log

     1 /home/oracle/oracle/product/10.2.0/oradata/prod/redo01.log

SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/home/oracle/oracle/product/10.2.0/oradata/prod/s_redo1.log') size 52428800;

Database altered.

SQL>  ALTER DATABASE ADD STANDBY LOGFILE ('/home/oracle/oracle/product/10.2.0/oradata/prod/s_redo2.log')  size 52428800;                                               

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE ('/home/oracle/oracle/product/10.2.0/oradata/prod/s_redo3.log') size 52428800;

Database altered.

SQL>  ALTER DATABASE ADD STANDBY LOGFILE ('/home/oracle/oracle/product/10.2.0/oradata/prod/s_redo4.log')  size 52428800;                                                

Database altered.

SQL> select GROUP#,THREAD#,MEMBERS,BYTES FROM V$LOG;

    GROUP#    THREAD#    MEMBERS      BYTES

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

         1          1          1   52428800

         2          1          1   52428800

         3          1          1   52428800

SQL> SELECT GROUP#, BYTES FROM V$STANDBY_LOG;

    GROUP#      BYTES

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

         4   52428800

         5   52428800

         6   52428800

         7   52428800

************ Modify the init.ora parameters: *******************

Modify the initprod.ora parameters on the primary database side to reflect the following values. If you are using spfile, you can add these parameter online without

down the primary database.

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,standby)';

alter system set LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod';

alter system set LOG_ARCHIVE_DEST_2=’SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby’;

alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;

alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;

alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=spfile;

ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc' SCOPE=spfile;

alter system set LOG_ARCHIVE_MAX_PROCESSES=30;

alter system set FAL_SERVER=standby;

alter system set DB_FILE_NAME_CONVERT='standby','prod' SCOPE=spfile;

alter system set LOG_FILE_NAME_CONVERT=

'/home/oracle/oracle/product/10.2.0/oradata/prod/','/home/oracle/oracle/product/10.2.0/oradata/standby/'SCOPE=spfile;    

alter system set STANDBY_FILE_MANAGEMENT=AUTO;

                                  

SQL> alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prod,standby)';

`System altered.

  

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod';

System altered.

  

SQL> alter system set LOG_ARCHIVE_DEST_2='SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby' SCOPE=spfile;

System altered.

SQL> alter system set LOG_ARCHIVE_DEST_STATE_1=ENABLE;

System altered.

 SQL> alter system set LOG_ARCHIVE_DEST_STATE_2=ENABLE;

 System altered.

 SQL>  alter system set REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=spfile;

System altered.

SQL> ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc' SCOPE=spfile;

 System altered.

 SQL> alter system set LOG_ARCHIVE_MAX_PROCESSES=30;

 System altered.

 SQL> alter system set FAL_SERVER=standby;

 System altered.

 SQL> alter system set DB_FILE_NAME_CONVERT='standby','prod' SCOPE=spfile;

 System altered.

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;

 System altered.

SQL> alter system set LOG_FILE_NAME_CONVERT='/home/oracle/oracle/product/10.2.0/oradata/standby/','/home/oracle/oracle/product/10.2.0/oradata/prod/'SCOPE=spfile;

 System altered.

*****CHECK & UPDATE initprod.ora( PFILE ) PRIMARY DB SIDE *************

 vi initprod.ora 

prod.__db_cache_size=197132288

prod.__java_pool_size=4194304

prod.__large_pool_size=4194304

prod.__large_pool_size=4194304

prod.__shared_pool_size=75497472

prod.__db_cache_size=197132288

prod.__java_pool_size=4194304

prod.__large_pool_size=4194304

prod.__shared_pool_size=75497472

prod.__streams_pool_size=0

*.audit_file_dest='/home/oracle/oracle/product/10.2.0/db_1/admin/prod/adump'

*.background_dump_dest='/home/oracle/oracle/product/10.2.0/db_1/admin/prod/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/home/oracle/oracle/product/10.2.0/oradata/prod/control01.ctl',

                        '/home/oracle/oracle/product/10.2.0/oradata/prod/control02.ctl',  

                        '/home/oracle/oracle/product/10.2.0/oradata/prod/control03.ctl'

*.core_dump_dest='/home/oracle/oracle/product/10.2.0/db_1/admin/prod/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_file_name_convert='standby','prod'

*.db_name='prod'

*.db_recovery_file_dest='/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'

*.fal_server='STANDBY'

*.job_queue_processes=10

*.local_listener='LISTENER_PROD'

*.log_archive_config='DG_CONFIG=(prod,standby)'

*.log_archive_dest_1='LOCATION=/home/oracle/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod'

*.log_archive_dest_2='SERVICE=standby ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

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

*.log_file_name_convert='/home/oracle/oracle/product/10.2.0/oradata/standby/','/home/oracle/oracle/product/10.2.0/oradata/prod/'

*.db_file_name_convert='/home/oracle/oracle/product/10.2.0/oradata/standby/','/home/oracle/oracle/product/10.2.0/oradata/prod/'

*.open_cursors=300

*.pga_aggregate_target=94371840

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=285212672

*.standby_file_management='AUTO'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/home/oracle/oracle/product/10.2.0/db_1/admin/prod/udump'

sql> create spfile from pfile;

sql> shutdown immediate;

sql> startup;

Create pfile for standby database:

 SQL> create pfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/initstandby.ora' from spfile;

 File created.

Configure listener.ora and tnsnames.ora:

 # tnsnames.ora Network Configuration File: /home/oracle/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools. 

PROD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = pk.prod.com)(PORT = 1522))

# tnsnames.ora Network Configuration File: /home/oracle/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PROD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.129)(PORT = 1522))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = prod)

    )

  )

STANDBY =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.131)(PORT = 1522))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = standby)

    )

  )

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

LISTENER_PROD =

  (ADDRESS = (PROTOCOL = TCP)(HOST = pk.prod.com)(PORT = 1522))

[oracle@pk dbs]$ orapwd file=$ORACLE_SID  password=sys force=y;

 [oracle@pk dbs]$ ls

hc_prod.dat  initdw.ora  init.ora  initprod.ora  initstandby.ora  lkPROD  orapwprod  prod  spfileprod.ora

Transfer init.ora and password files to Standby Server:

Create Password file  and copy initstandby.ora and password file from primary server to standby server.

[oracle@pk dbs]$ ls

hc_prod.dat  initdw.ora  init.ora  initprod.ora  initstandby.ora  lkPROD  orapwprod  prod  spfileprod.ora

[oracle@pk dbs]$ scp initstandby.ora orapwprod oracle@192.168.226.131:/home/oracle/oracle/product/10.2.0/db_1/dbs/

oracle@192.168.226.131's password:

initstandby.ora                                                                          100% 1806     1.8KB/s   00:00

orapwprod                                                                                100% 1536     1.5KB/s   00:00

*****************************************************************************            Standby Database Site Preparation :

*****************************************************************************

* Create a remote login password file ( if it is not copied from primary site )

* Create a directory structure

If you are using a different directory structure on the standby server, then you need to create all the directories on the standby site.

Copy initstandby.ora to $ORACLE_HOME/dbs location.

SQL>  conn sys/sys@prod as sysdba

SQL> select name from v$database;

NAME

---------

PROD

Configure listener.ora and tnsnames.ora:

# tnsnames.ora Network Configuration File: /home/oracle/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PROD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = pk.prod.com)(PORT = 1522))

# tnsnames.ora Network Configuration File: /home/oracle/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora

# Generated by Oracle configuration tools.

PROD =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.129)(PORT = 1522))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = prod)

    )

  )

STANDBY =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.226.131)(PORT = 1522))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = standby)

    )

  )

EXTPROC_CONNECTION_DATA =

  (DESCRIPTION =

    (ADDRESS_LIST =

      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC2))

    )

    (CONNECT_DATA =

      (SID = PLSExtProc)

      (PRESENTATION = RO)

    )

  )

LISTENER_PROD =

  (ADDRESS = (PROTOCOL = TCP)(HOST = pk.prod.com)(PORT = 1522))

*****************************************************************************

            Standby Database Site Preparation :

*****************************************************************************

* Create a remote login password file ( if it is not copied from primary site )

* Create a directory structure

If you are using a different directory structure on the standby server, then you need to create all the directories on the standby site.

Copy initstandby.ora to $ORACLE_HOME/dbs location.

**************** Edit pfile and update parameter for Standby *******************

 DB_NAME=prod

DB_UNIQUE_NAME=standby

LOG_ARCHIVE_CONFIG=’DG_CONFIG=(prod,standby)’

CONTROL_FILES=’/home/oracle/app/oracle/oradata/sbyorcl/s_control01.ctl’, ‘/home/oracle/app/oracle/flash_recovery_area/sbyorcl/s_control02.ctl’

DB_FILE_NAME_CONVERT=’prod’,'standby’

LOG_FILE_NAME_CONVERT=’/home/oracle/app/oracle/flash_recovery_area/prod/’,'/home/oracle/app/oracle/flash_recovery_area/standby/’

LOG_ARCHIVE_FORMAT=%t_%s_%r.arc

LOG_ARCHIVE_DEST_1=’LOCATION=/home/oracle/app/oracle/flash_recovery_area/standby/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby’

LOG_ARCHIVE_DEST_2=’SERVICE=prod ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod’

LOG_ARCHIVE_DEST_STATE_1=ENABLE

LOG_ARCHIVE_DEST_STATE_2=ENABLE

REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE

STANDBY_FILE_MANAGEMENT=AUTO

FAL_SERVER=prod

Edit parameter file and update parameteres required for standby

 vi initstandby.ora

*.audit_file_dest='/home/oracle/oracle/product/10.2.0/db_1/admin/standby/adump'

*.background_dump_dest='/home/oracle/oracle/product/10.2.0/db_1/admin/standby/bdump'

*.compatible='10.2.0.1.0'

*.control_files='/home/oracle/oracle/product/10.2.0/oradata/standby/control01.ctl','/home/oracle/oracle/product/10.2.0/oradata/standby/control02.ctl','/home/oracle/ora

cle/product/10.2.0/oradata/standby/control03.ctl'

*.core_dump_dest='/home/oracle/oracle/product/10.2.0/db_1/admin/standby/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_file_name_convert='prod','standby'

*.db_name='prod'

*.db_unique_name=standby

*.db_recovery_file_dest='/home/oracle/oracle/product/10.2.0/db_1/flash_recovery_area'

#*.db_recovery_file_dest='/home/oracle/backup'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP) (SERVICE=prodXDB)'

*.fal_server='PROD'

*.job_queue_processes=10

*.local_listener='LISTENER_STANDBY'

*.log_archive_config='DG_CONFIG=(prod,standby)'

*.log_archive_dest_1='LOCATION=/home/oracle/archive/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby'

*.log_archive_dest_2='SERVICE=prod ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod'

*.log_archive_dest_state_1='ENABLE'

*.log_archive_dest_state_2='ENABLE'

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

*.log_file_name_convert='/home/oracle/oracle/product/10.2.0/oradata/prod/','/home/oracle/oracle/product/10.2.0/oradata/standby/'

*.db_file_name_convert='/home/oracle/oracle/product/10.2.0/oradata/prod/','/home/oracle/oracle/product/10.2.0/oradata/standby/'

*.open_cursors=300

*.pga_aggregate_target=94371840

*.processes=150

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=285212672

*.standby_file_management='AUTO'

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.user_dump_dest='/home/oracle/oracle/product/10.2.0/db_1/admin/standby/udump'

~

SYS@STANDBY > startup nomount pfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/initstandby.ora';

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              83887696 bytes

Database Buffers          197132288 bytes

Redo Buffers                2973696 bytes

SYS@STANDBY > create spfile from pfile='/home/oracle/oracle/product/10.2.0/db_1/dbs/initstandby.ora';

 File created.

 SYS@STANDBY > shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

SYS@STANDBY > startup nomount

ORACLE instance started.

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              83887696 bytes

Database Buffers          197132288 bytes

Redo Buffers                2973696 bytes

_________________________________________________

*********** Take the backup of Primary Database in Primary DB side ***********

_________________________________________________

Create one backup directory for the backup of database on both side ( primary database & Standby database)

same path and same directory name like here i create with the name of backup on '/home/oracle/' path

 RMAN> backup full database format'/home/oracle/backup/%d_%U.bckp' plus archivelog format '/home/oracle/backup/5D_%U.bkp';

RMAN> configure channel device type disk format '/home/oracle/backup/%U';

RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY;

RMAN> BACKUP ARCHIVELOG ALL;

****** Transfer ALL Backup to standby Database *********************

____________________________________________________

********** ON STANDBY DATABASE SIDE *****************************

Create the standby database using rman: 

_______________________________________________________________

[oracle@rac1 ~]$ . oraenv

ORACLE_SID = [standby] ? standby

STARTUP DATABASE IN MOUNT MODE

SYS@STANDBY > startup nomount

ORACLE instance started. 

Total System Global Area  285212672 bytes

Fixed Size                  1218992 bytes

Variable Size              83887696 bytes

Database Buffers          197132288 bytes

Redo Buffers                2973696 bytes

_________________________________________

  *******CONNECT RMAN**********

_________________________________________

 [oracle@pk ~]$ rman target=sys/sys@prod auxiliary=/

 Recovery Manager: Release 10.2.0.1.0 - Production on Fri Aug 30 08:29:55 2013

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

connected to target database: PROD (DBID=236467520)

connected to auxiliary database: PROD (DBID=236467520, not open)

RMAN> DUPLICATE TARGET DATABASE FOR STANDBY NOFILENAMECHECK; 

Starting Duplicate Db at 30-AUG-13

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=155 devtype=DISK

contents of Memory Script:

{

   restore clone standby controlfile;

   sql clone 'alter database mount standby database';

}

executing Memory Script

Starting restore at 30-AUG-13

using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore

channel ORA_AUX_DISK_1: restoring control file

channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/0toig3u6_1_1

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/home/oracle/backup/0toig3u6_1_1 tag=TAG20130829T055102

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

output filename=/home/oracle/oracle/product/10.2.0/oradata/standby/control01.ctl

output filename=/home/oracle/oracle/product/10.2.0/oradata/standby/control02.ctl

output filename=/home/oracle/oracle/product/10.2.0/oradata/standby/control03.ctl

Finished restore at 30-AUG-13

sql statement: alter database mount standby database

released channel: ORA_AUX_DISK_1

contents of Memory Script:

{

   set newname for tempfile  1 to

 "/home/oracle/oracle/product/10.2.0/oradata/standby/temp01.dbf";

   switch clone tempfile all;

   set newname for datafile  1 to

 "/home/oracle/oracle/product/10.2.0/oradata/standby/system01.dbf";

   set newname for datafile  2 to

 "/home/oracle/oracle/product/10.2.0/oradata/standby/undotbs01.dbf";

   set newname for datafile  3 to

 "/home/oracle/oracle/product/10.2.0/oradata/standby/sysaux01.dbf";

   set newname for datafile  4 to

 "/home/oracle/oracle/product/10.2.0/oradata/standby/users01.dbf";

   set newname for datafile  5 to

 "/home/oracle/oracle/product/10.2.0/oradata/standby/example01.dbf";

   restore

   check readonly

   clone database

   ;

}

executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /home/oracle/oracle/product/10.2.0/oradata/standby/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 30-AUG-13

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: sid=155 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore

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

restoring datafile 00001 to /home/oracle/oracle/product/10.2.0/oradata/standby/system01.dbf

restoring datafile 00002 to /home/oracle/oracle/product/10.2.0/oradata/standby/undotbs01.dbf

restoring datafile 00003 to /home/oracle/oracle/product/10.2.0/oradata/standby/sysaux01.dbf

restoring datafile 00004 to /home/oracle/oracle/product/10.2.0/oradata/standby/users01.dbf

restoring datafile 00005 to /home/oracle/oracle/product/10.2.0/oradata/standby/example01.dbf

channel ORA_AUX_DISK_1: reading from backup piece /home/oracle/backup/PROD_0qoig3k8_1_1.bckp

channel ORA_AUX_DISK_1: restored backup piece 1

piece handle=/home/oracle/backup/PROD_0qoig3k8_1_1.bckp tag=TAG20130829T054544

channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:11

Finished restore at 30-AUG-13

contents of Memory Script:

{

   switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy recid=7 stamp=824821751 filename=/home/oracle/oracle/product/10.2.0/oradata/standby/system01.dbf

datafile 2 switched to datafile copy

input datafile copy recid=8 stamp=824821751 filename=/home/oracle/oracle/product/10.2.0/oradata/standby/undotbs01.dbf

datafile 3 switched to datafile copy

input datafile copy recid=9 stamp=824821751 filename=/home/oracle/oracle/product/10.2.0/oradata/standby/sysaux01.dbf

datafile 4 switched to datafile copy

input datafile copy recid=10 stamp=824821751 filename=/home/oracle/oracle/product/10.2.0/oradata/standby/users01.dbf

datafile 5 switched to datafile copy

input datafile copy recid=11 stamp=824821751 filename=/home/oracle/oracle/product/10.2.0/oradata/standby/example01.dbf

Finished Duplicate Db at 30-AUG-13

RMAN> exit

Recovery Manager complete.

[oracle@pk dbs]$ sqlplus / as sysdba

 SYS@STANDBY > alter database recover managed standby database disconnect from session;

 Database altered.

 _________________________________________

      On the Primary:

_________________________________________

 SYS@PROD > alter system switch logfile;

 System altered.

SYS@PROD > alter system archive log current;

 System altered.

  

SYS@PROD > archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/oracle/archive/

Oldest online log sequence     16

Next log sequence to archive   18

Current log sequence           18

_______________________________________________

            ON STAND BY DATABASE

_______________________________________________

SYS@STANDBY > archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/oracle/archive/

Oldest online log sequence     16

Next log sequence to archive   0

Current log sequence           18

 SYS@STANDBY > SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

  SEQUENCE# APP

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

         8 NO

         9 NO

        10 NO

        11 NO

        12 NO

        13 YES

        14 YES

        15 YES

        16 YES

        17 YES

10 rows selected.

SYS@STANDBY > select recovery_mode  from v$archive_dest_status;

 RECOVERY_MODE

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

MANAGED

MANAGED

MANAGED

MANAGED

MANAGED

MANAGED

MANAGED

MANAGED

MANAGED

MANAGED

MANAGED

11 rows selected.

SYS@STANDBY > select max(sequence#) from v$log_history;

 MAX(SEQUENCE#)

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

            17

SYS@STANDBY > archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            /home/oracle/archive/

Oldest online log sequence     18

Next log sequence to archive   0

Current log sequence           20

  

***********CHECK ON BOTH PRIMARY AND STAND BY SIDE*************

SYS@STANDBY >  select max(sequence#) from v$archived_log; 

MAX(SEQUENCE#)

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

            19

Change database name using nid

SQL> startup mount

ORACLE instance started.

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              92276304 bytes
Database Buffers          188743680 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> !
[oracle@pk ~]$ nid TARGET=system DBNAME=prod1 SETNAME=Y

DBNEWID: Release 10.2.0.1.0 - Production on Sat Aug 17 18:43:12 2013

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

Password:
Connected to database DEV1 (DBID=3432497468)

Connected to server version 10.2.0

Control Files in database:
    /home/oracle/oracle/product/10.2.0/oradata/dev1/control01.ctl
    /home/oracle/oracle/product/10.2.0/oradata/dev1/control02.ctl
    /home/oracle/oracle/product/10.2.0/oradata/dev1/control03.ctl

Change database name of database DEV1 to PROD1? (Y/[N]) => Y

Proceeding with operation
Changing database name from DEV1 to PROD1
    Control File /home/oracle/oracle/product/10.2.0/oradata/dev1/control01.ctl - modified
    Control File /home/oracle/oracle/product/10.2.0/oradata/dev1/control02.ctl - modified
    Control File /home/oracle/oracle/product/10.2.0/oradata/dev1/control03.ctl - modified
    Datafile /home/oracle/oracle/product/10.2.0/oradata/dev1/system01.dbf - wrote new name
    Datafile /home/oracle/oracle/product/10.2.0/oradata/dev1/undotbs01.dbf - wrote new name
    Datafile /home/oracle/oracle/product/10.2.0/oradata/dev1/sysaux01.dbf - wrote new name
    Datafile /home/oracle/oracle/product/10.2.0/oradata/dev1/users01.dbf - wrote new name
    Datafile /home/oracle/oracle/product/10.2.0/oradata/dev1/example01.dbf - wrote new name
    Datafile /home/oracle/oracle/product/10.2.0/oradata/dev1/temp01.dbf - wrote new name
    Control File /home/oracle/oracle/product/10.2.0/oradata/dev1/control01.ctl - wrote new name
    Control File /home/oracle/oracle/product/10.2.0/oradata/dev1/control02.ctl - wrote new name
    Control File /home/oracle/oracle/product/10.2.0/oradata/dev1/control03.ctl - wrote new name
    Instance shut down

Database name changed to PROD1.
Modify parameter file and generate a new password file before restarting.
Succesfully changed database name.
DBNEWID - Completed succesfully.



This utility won’t change the database name in pfile, so change the database name (DB_NAME) in pfile manually and create password file (if necessary).


Mount the database
SQL> STARTUP MOUNT
Open the database in RESETLOGS mode and resume normal use
SQL> ALTER DATABASE OPEN RESETLOGS;
SQL> select dbid, name from v$database;


Make a new database backup. Because you had reset the online redo logs, the old backups and archived logs are no longer usable in the current incarnation of the database.

Oracle R12 operations

Oracle R12 operations

Start and stop applications - R12
Database Tier Scripts in R12

For Database tier you need to start database and database listener. Scripts are located in
$ORACLE_HOME/appsutil/scripts/$CONTEXT_NAME

Startup
1. Database Listener
$addlnctl.sh start

2. Database
$ addbctl.sh start

or alternatively you can use

lsnrctl start listener_name
$sqlplus “/as sysdba”
SQL> startup

Shutdown
1. Database Listener
$addlnctl.sh stop

2. Database
$ addbctl.sh stop immeidate|normal|abort

or alternatively you can use

lsnrctl stop listener_name

$sqlplus “/as sysdba”
SQL> shutdown immedidate|normal|abort

Application Tier Scripts in R12

Scripts for Application Tier services in R12 are located in
$ADMIN_SCRIPT_HOME

(Apps_base/inst/apps/$CONTEXT_NAME/admin/scripts)
where CONTEXT_NAME is of format SID_HOSTNAME

1) adstrtal.sh  [appsusername/appspassword]
Script to start all components/services of middle tier or application tier. This script will use Service Control API to start all services which are enabled after checking them in context file (SID_HOSTNAME.xml or CONTEXT_NAME.xml)

2) adstpall.sh [appsusername/appspassword]
Stop all components/services of middle tier or application tier.

3) adalnctl.sh {start | stop | status}
Script to start / stop apps listener (FNDFS and FNDFS). This listener will file will be in 10.1.2 ORACLE_HOME (i.e. Forms & Reports Home)
listener.ora file will be in $INST_TOP/apps/$CONTEXT_NAME/ora/10.1.2/network/admin directory
(compared to 11i, only change in ORACLE_HOME i.e. from 8.0.6 to 10.1.2)

In simple terms, it is

Start Database listener process  => addlnctl.sh start VIS
Start Database process  => addbctl.sh start
Start Applications processes  => adstrtal.sh apps/password

Stop Database listener process => addlnctl.sh stop VIS
Stop Database process => addbctl.sh stop
Stop Applications processes => adstpall.sh apps/password

4) adapcctl.sh {start|stop|status}
Script to start/stop Web Server or Oracle HTTP Server.
This script uses opmn (OPMN and Notification Server) with syntax similar to opmnctl start/stopproc ohs
like opmnctl stopproc ohs

(In 11i this script directly used to call apachectl executable but now calls opmnctl which in turn calls apachectl.
In 11i web server oracle home was 1.0.2.2.2 but in R12 its 10.1.3)

5) adcmctl.sh {start | stop | abort | status } [apps username/ apps password]
Script to start / stop concurrent manager, Similar to one in 11i. (This script in turn calls startmgr.sh )

6) adformsctl.sh {start | stop | status}
Script to start / stop Forms OC4J from 10.1.3 Oracle_Home. This script will also use opmnctl to start/stop Forms OC4J like opmnctl stopproc type=oc4j instancename=forms

7) adformsrvctl.sh
This script is used only if you wish to start forms in socket mode. Default forms connect method in R12 is servlet.

If started this will start frmsrv executable from 10.1.2 Oracle_Home in Apps R12

8) adoacorectl.sh {start | stop | status}
This script will start/stop oacore OC4J in 10.1.3 Oracle_Home. This scripts will also use opmnctl (similar to adapcctl & adformsctl) to start oacore instance of OC4J like

opmnctl startproc type=oc4j instancename=oacore

9) adoafmctl.sh {start | stop | status }
This script will start/stop oafm OC4J in 10.1.3 Oracle_Home. This scripts will also use opmnctl (similar to above) to start oacore instance of OC4J like

opmnctl startproc type=oc4j instancename=oafm

10) adopmnctl.sh {start | stop | status}
This script will start/stop opmn service in 10.1.3 Oracle_Home. opmn will control all services in 10.1.3 Oracle_Home like web server or various oc4j instances. If any services are stopped abnormally opmn will/should start them automatically.

11) jtffmctl.sh {start | stop}
This script will be used to start/stop one to one fulfilment server.

12) mwactl.sh {start | stop }
To start / stop mwa telnet server where mwa is mobile application.

Log files for startup/shutdown scripts for application/mid tier in R12 are in $INST_TOP/apps/$CONTEXT_NAME/logs/appl/admin/log

Recover archive gaps in standby database

Recover archive gaps in standby database from primary using RMAN incremental backups

A Physical Standby database synchs with Primary by continuous apply of archive logs from a Primary Database. In case of an archive log gone missing or corrupt, We have to bring back the standby to sync with the primary.

When the logs missing or corrupt is less in number (say below 15), we can ship the logs which were missing in the standby site from the primary site (scp/sftp/ftp) and then we can register the log file in the standby so that the gap can be resolved.

Find the archives which are missing by issueing the following command.

SQL> select * from v$archive_gap;

This would give the gap sequences. Or you can use the v$managed_standby view to find where the log apply stuck.

SQL> select sequence#,process,status from v$managed_standby;

Here u can see status as wait for log for say sequence# 100 but your primary would've proceeded to sequence# 110

At primary

SQL> select max(sequence#) from v$archived_log;      ---> This would show you 110

Copy the logs to the standby site from the primary site

$ scp log_file_name_n.arc oracle@standby:/log/file/location/log_file_name_n.arc

At standby site

SQL> alter database register logfile '/log/file/location/log_file_name_n.arc';

logfile registered

Do the log file registration at the standby site until all the missing log files are registered. Now apply would take place and your standby will become sync with the primary.

This is easy process if you have missing or corrupt logs in lesser number. But when the difference is huge (say around 500 logs) this method is very time consuming and not a proper approach. Else you have to rebuild the standby database from scratch.

As an enhancement from 10g, an incremental backup created with BACKUP INCREMENTAL... FROM SCN can be used to refresh the standby database with changes at the primary database since the last SCN at Standby and then managed recovery can resume i.e. Compensate for the missing archive logs.

Let us see the steps involved.

Step 1: On the primary:

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

144710998

On the standby:

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

130158742

Clearly there is a difference. But this by itself does not indicate a problem; since the standby is expected to lag behind the primary (this is an asynchronous non-real time apply setup). The real question is how much it is lagging in the terms of wall clock.

To know that use the scn_to_timestamp function to translate the SCN to a timestamp:

SQL> select scn_to_timestamp(144710998) from dual;

SCN_TO_TIMESTAMP(1447102)

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

18-AUG-11 08.54.28.000000000 AM

Run the same query to know the timestamp associated with the SCN of the standby database as well

SQL> select scn_to_timestamp(130158742) from dual;

SCN_TO_TIMESTAMP(1301571)

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

13-AUG-11 07.19.27.000000000 PM

Note: Run it on the primary database, since it will fail in the standby in a mounted mode

This shows that the standby is four and half days lagging!

Step 2: [Standby] Stop the managed standby apply process:

SQL> alter database recover managed standby database cancel;

Database altered.

Step 3: [Standby] Shutdown the standby database

SQL> shut immediate

Step 4: [Primary] On the primary, take an incremental backup from the SCN number where the standby has been stuck:

RMAN> run {

2> allocate channel c1 type disk format '/u01/backup/%U.bkp';

3> backup incremental from scn 130158740 database;

4> }

Step 5: [Primary] On the primary, create a new standby controlfile:

SQL> alter database create standby controlfile as '/u01/backup/for_standby.ctl';

Database altered.

or

RMAN> backup current controlfile for standby format '/backups/tempfol/_%U';

Step 6: [Primary] Copy these files to standby host:

oracle@dba1 /u01/backup]$ scp * oracle@dba2:/u01/backup

Step 7: [Standby] Bring up the instance in nomount mode:

SQL> startup nomount

Step 8: [Standby] Check the location of the controlfile:

SQL> show parameter control_files

NAME TYPE VALUE

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

control_files string /u01/oradata/standby_cntfile.ctl

Step 9: [Standby] Replace the controlfile with the one you just created in primary.

 $ cp /u01/backup/for_standby.ctl /u01/oradata/standby_cntfile.ctl

or

RMAN> RESTORE STANDBY CONTROLFILE FROM '/u01/backup/_v8o069ee_1_1';

Step 10: [Standby] Mount the standby database:

SQL> alter database mount standby database;

Step 11: [Standby] Connect to RMAN. RMAN does not know about these files yet; so you must let it know – by a process called cataloging. Catalog these files:

$ rman target=/

RMAN> catalog start with '/u01/backup';

Step 12: Recover these files:

RMAN> recover database;

Step 13: After some time, the recovery fails with the message:

archive log filename=/u01/oradata/1_18108_697108460.dbf thread=1 sequence=18109

ORA-00310: archived log contains sequence 18108; sequence 18109 required

This happens because we have come to the last of the archived logs. The expected archived log with sequence# 18108 has not been generated yet.

Step 14: At this point exit RMAN and start managed recovery process:

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

Database altered.

Step 15: Check the SCN’s in primary and standby:

[Standby] SQL> select current_scn from v$database;

CURRENT_SCN

-----------

144747125

[Primary] SQL> select current_scn from v$database;

CURRENT_SCN

-----------

144747111

Now they are very close to each other. The standby has now caught up.

Rman Recovery through RESETLOGS and reset of the incarnation of the database

Recovery through RESETLOGS and reset of the incarnation of the database:

Prior to Oracle 10g, the redo log files generated after opening the database with RESETLOGS could not be used with the backups taken before the RESETLOGS was performed.

Therefore, whenever a resetlogs was done, it was important to take an immediate full database backup, since all previous backups became invalid.

Just to recap, a RESETLOGS needs to be performed when we

1)            Do a point in time recovery

2)            Recover a database using a backup of the control file

What does a RESETLOGS do?

•              Archives the current online redo logs (if they are accessible) and then erases the contents of the online redo logs and resets the log sequence number to 1.

•              Creates the online redo log files if they do not currently exist.

•              Updates all current datafiles and online redo logs and all subsequent archived redo logs with a new RESETLOGS SCN and time stamp

The Recovery through Resetlogs feature provides the following benefits:

* There is no need to perform a full backup after an incomplete recovery.

* There is no need to recreate a new standby database after a failover operation.

* You can take incremental backups based on full backups of a previous incarnation when you use RMAN.

* Block media recovery can restore backups from parent incarnation backups and recover the corrupted blocks through a RESETLOGS operation.

* You can use the newly generated logs with an earlier incarnation of the database.

Important:

To perform recovery through RESETLOGS you must have all archived logs generated after the most recent backup.

The new log_archive_format introduced in 10g

Oracle 10g introduces a new format specification for archived log files. This new format avoids overwriting archived redo log files with the same sequence number across incarnations.

SQL> show parameter log_archive_format

NAME TYPE VALUE

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

log_archive_format string %t_%s_%r.dbf

The format specification of the log_archive_format string "%"r represents the resetlogs id. It will ensure that a unique name is constructed for the archived redo log file during RMAN restore and as well as restoring via SQL*plus auto recovery mode.

Note: The database would not start if you remove the %r from the log archive format specification.

What is an incarnation?

A database incarnation is created whenever you open the database with the RESETLOGS option.

The Current incarnation is the one in which the database is running at present

The incarnation from which the current incarnation branched after a ALTER DATABASE OPEN RESETLOGS  was performed is called the Parent incarnation.

If you want to go back to some SCN which is not part of the Current database incarnation, we need to use the RESET DATABASE TO INCARNATION 

command as shown in the example below

The purpose of incarnations

An incarnation helps to identify redo streams which have the same SCN, but occurred at different points in time. This prevents the possibility of applying the wrong archive log file from a previous incarnation which could corrupt the database.

Suppose we are at incarnation 1 and are at SCN 100 in the database. I do a resetlogs and now the incarnation of the database becomes 2. Suppose we do another resetlogs and it so happens that the SCN at the time we did the resetlogs was also 100. Somewhere down the line later  we want to do a point in time recovery and want to recover the database until SCN 100. How does Oracle know which is the correct SCN and how far back in time should the database be rolled back to and which archive logs need to be applied?

This is where incarnations come in and we will see how we can set the database to a particular incarnation to enable RMAN to do a point in time recovery.

Example

Let us now run the LIST INCARNATION OF DATABASE command from RMAN and this is the output.

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

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

1       1       TEST11G  916751459        PARENT  1          15-AUG-09

2       2       TEST11G  916751459        CURRENT 945184     18-OCT-11

What is the current SCN of the database?

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

     991395

Now, let us make a change in the database which we will then try to undo by restoring  and recovering the database to a point in time before the media failure or as in this case, a wrong transaction has happened.

SQL> conn sh/sh

Connected.

SQL> select count(*) from sales;

  COUNT(*)

----------

    918843

SQL> delete from sales where rownum < 1001;

1000 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from sales;

  COUNT(*)

----------

    917843

We now will roll the database back to an SCN before the delete operation was performed.

SQL> conn / as sysdba

Connected.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

[oracle@orasql-001 u01]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Oct 18 09:41:08 2011

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

connected to target database: TEST11G (DBID=916751459, not open)

RMAN> run {

2> set until scn 991395;

3> restore database;

4> recover database;

5> }

executing command: SET until clause

Starting restore at 18-OCT-11

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

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

channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/TEST11G/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/oradata/TEST11G/sysaux01.dbf

….

….

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

Finished restore at 18-OCT-11

Starting recover at 18-OCT-11

using channel ORA_DISK_1

starting media recovery

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

Finished recover at 18-OCT-11

RMAN> alter database open resetlogs;

database opened

We can now see that the deleted rows have been recovered and the number of rows in the table is now the same as before the delete operation was performed.

SQL> select count(*) from sh.sales;

  COUNT(*)

----------

    918843

Let us check what the incarnation of the database is.

RMAN> list incarnation of database;

using target database control file instead of recovery catalog

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

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

1       1       TEST11G  916751459        PARENT  1          15-AUG-09

2       2       TEST11G  916751459        PARENT  945184     18-OCT-11

3       3       TEST11G  916751459        CURRENT 991396     18-OCT-11

The current incarnation of the database is 3 and since we have recovered the database until SCN 991395, the RESET SCN has been set to the SCN 991396.

The alert log of the database will also log this resetlogs operation and we will see lines the ones shown below:

Incomplete Recovery applied until change 991395 time 10/18/2011 09:39:19

Media Recovery Complete (TEST11G)

Completed: alter database recover if needed

 start until change 991395

Tue Oct 18 09:42:14 2011

alter database open resetlogs

Archived Log entry 3 added for thread 1 sequence 5 ID 0x36a3e663 dest 1:

RESETLOGS after incomplete recovery UNTIL CHANGE 991395

Setting recovery target incarnation to 3

Tue Oct 18 09:42:15 2011

Assigning activation ID 916768223 (0x36a4c5df)

Now let us make another delete from the sales table.

SQL> delete from sh.sales where rownum < 10001;

10000 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from sh.sales;

  COUNT(*)

----------

    908843

Now, let us shutdown the database, startup and mount it and try the same incomplete recovery which we tried earlier until the SCN 991395

oracle@orasql-001 u01]$ rman target /

Recovery Manager: Release 11.2.0.1.0 - Production on Tue Oct 18 09:46:29 2011

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

connected to target database: TEST11G (DBID=916751459, not open)

RMAN> run {

2>  set until scn 991395;

3> restore database;

4> recover database;

5> }

executing command: SET until clause

Starting restore at 18-OCT-11

using target database control file instead of recovery catalog

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of restore command at 10/18/2011 09:46:55

RMAN-20208: UNTIL CHANGE is before RESETLOGS change

We get the error RMAN-20208 because since the current incarnation of the database is 3 and we trying to go to an SCN before this incarnation.

So how do we go back to SCN 993195?

For this to happen, we need to change the current incarnation (3) of the database to an older incarnation (2). The SCN 993195 was present during the incarnation 2.

After resetting the incarnation to 2, we now see that the restore is proceeding fine.

RMAN> reset database to incarnation 2;

database reset to incarnation 2

RMAN>  run {

2>  set until scn 991395;

3>  restore database;

4> recover database;

5> }

executing command: SET until clause

Starting restore at 18-OCT-11

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=63 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore

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

channel ORA_DISK_1: restoring datafile 00001 to /u01/oradata/TEST11G/system01.dbf

…

…

channel ORA_DISK_1: restore complete, elapsed time: 00:00:15

Finished restore at 18-OCT-11

Starting recover at 18-OCT-11

using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 4 is already on disk as file /u01/oradata/TEST11G/arch/1_4_764845989.arch

archived log for thread 1 with sequence 5 is already on disk as file /u01/oradata/TEST11G/arch/1_5_764845989.arch

archived log file name=/u01/oradata/TEST11G/arch/1_4_764845989.arch thread=1 sequence=4

archived log file name=/u01/oradata/TEST11G/arch/1_5_764845989.arch thread=1 sequence=5

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

Finished recover at 18-OCT-11

Now we open the database with the ALTER DATABASE OPEN RESETOGS command and see that a new incarnation key (4) has been allocated to the database as the CURRENT incarnation and the previous incarnation (3) has become an ORPHAN incarnation.

RMAN> list incarnation of database;

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

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

1       1       TEST11G  916751459        PARENT  1          15-AUG-09

2       2       TEST11G  916751459        PARENT  945184     18-OCT-11

3       3       TEST11G  916751459        ORPHAN  991396     18-OCT-11

4       4       TEST11G  916751459        CURRENT 991396     18-OCT-11

Now let us do the same procedure once again:

Delete some rows

Set the database to incarnation 2

Recover the database until the same SCN as previous case – 991395

Open the database with resetlogs

Now list the incarnation of the database

RMAN> list incarnation of database;

List of Database Incarnations

DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time

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

1       1       TEST11G  916751459        PARENT  1          15-AUG-09

2       2       TEST11G  916751459        PARENT  945184     18-OCT-11

4       4       TEST11G  916751459        ORPHAN  991396     18-OCT-11

5       5       TEST11G  916751459        CURRENT 991396     18-OCT-11

3       3       TEST11G  916751459        ORPHAN  991396     18-OCT-11

The incarnation key 5 now is the CURRENT incarnation of the database, and incarnations 3 and 4 both have become ORPHAN.

The current SCN now is 991734

SQL> select current_scn from v$database;

CURRENT_SCN

-----------

     991734

What happens if we change the structure of the database?  And we do not take any new backup after this change was made, but try and recover using a backup before we had changed the structure of the database. We add another datafile to the USERS tablespace. So the current control file has knowledge of the fact that the  USERS tablespace now has not one, but two datafiles.

We now try and do the same delete of rows and try and go back to our old SCN 991395. At this point in time, the control file had only knowledge of the fact that the USERS tablespace has one data file and not two and the restore and recovery process will not try to do anything with regards to the newly added datafile..

This is what we see in the alert log of the database. Note that datafile 6 which is the new datafile added is being taken offline and dropped because at SCN 991735, this datafile did not exist in the database.

Wed Oct 19 10:03:42 2011

alter database datafile 6 offline drop

Completed: alter database datafile 6 offline drop

alter database recover datafile list clear

Completed: alter database recover datafile list clear

alter database recover datafile list

 1 , 2 , 3 , 4 , 5

Completed: alter database recover datafile list

 1 , 2 , 3 , 4 , 5

alter database recover if needed

 start until change 991735

Dictionary check beginning

File #6 in the controlfile not found in data dictionary.

Removing file from controlfile.

data file 6: '/u01/oradata/TEST11G/users02.dbf'

Dictionary check complete