Roll Forward in ASM Standby with incremental backup of the NON ASM Primary Database

In the post I'll show you to deal with the issue when the archive log physical removed from the Primary Database server before to apply it over the Standby Database.

Here:

Database_Type     Database_Unique_Name    File_Management

Primary                  DB1              NON-ASM
Standby                  SDB1             ASM


first I'll create the scenario where :


1. I'll disable the second archive destination state over the Primary Database which is pointing for Standby Database service.
2. Once it is disable , I'll create drop and create a table under user 'SCOTT'.
3. The generated archive logs will move to the another directory.
4. Then will start the secondary archive log destination so it will start pointing to the standby database server.
5. The Standby recovery will identify the missing archive logs and will throw the message into the alert log file for missing archive logs.


After that I'll start the recovery of the missing archive logs on the standby database.

Before starting lets have a look on the Primary and Standby Database status.


At Primary


SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB1 READ WRITE PRIMARY

SQL> archive log list
 
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/fast_recovery_area/db1/archivelog/
Oldest online log sequence 108
Next log sequence to archive 110
Current log sequence 110

SQL> show parameter log_archive_dest_state_2

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE

At Standby Database.

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB1 READ ONLY WITH APPLY PHYSICAL STANDBY

SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA/
Oldest online log sequence 108
Next log sequence to archive 0
Current log sequence 110

At Primary Site.

Lets Disable the secondary archive log destination and create some table under 'Scott' schema.
And after that move the generated archives into another directory.
 
SQL> alter system set log_archive_dest_state_2='DEFER' scope=both;
System altered.

SQL> show parameter log_archive_dest_state_2

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------

log_archive_dest_state_2 string DEFER

SQL> select * from tab;

TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
BONUS TABLE
DEPT TABLE
EMP TABLE
SALGRADE TABLE
TEST TABLE

SQL> drop table test purge;

Table dropped.

SQL> create table test
2 as
3 select * from emp;

Table created.

SQL> select count(*) from test;
COUNT(*)
----------
14

SQL> commit;

Commit complete.


[oracle@node1 archivelog]$ ls -l
total 376384
-rw-r----- 1 oracle oinstall 868864 Jun 26 18:14 1_100_850229880.dbf
-rw-r----- 1 oracle oinstall 421376 Jun 26 18:27 1_101_850229880.dbf
-rw-r----- 1 oracle oinstall 42317824 Jun 27 01:30 1_102_850229880.dbf
-rw-r----- 1 oracle oinstall 33522688 Jun 27 11:26 1_103_850229880.dbf
-rw-r----- 1 oracle oinstall 6123520 Jun 27 13:25 1_104_850229880.dbf
-rw-r----- 1 oracle oinstall 86016 Jun 27 13:28 1_105_850229880.dbf
-rw-r----- 1 oracle oinstall 1191424 Jun 27 13:30 1_106_850229880.dbf
-rw-r----- 1 oracle oinstall 102912 Jun 27 13:33 1_107_850229880.dbf
-rw-r----- 1 oracle oinstall 932352 Jun 27 14:00 1_108_850229880.dbf
-rw-r----- 1 oracle oinstall 2560 Jun 27 14:01 1_109_850229880.dbf
-rw-r----- 1 oracle oinstall 515584 Jun 27 14:14 1_110_850229880.dbf
-rw-r----- 1 oracle oinstall 1024 Jun 27 14:14 1_111_850229880.dbf
-rw-r----- 1 oracle oinstall 3584 Jun 27 14:15 1_112_850229880.dbf
....

[oracle@node1 archivelog]$ mkdir -p bkp/
[oracle@node1 archivelog]$ mv 1_1* bkp/

Enable the secondary archive destination state.

SQL> alter system set log_archive_dest_state_2='ENABLE' scope=both;
System altered.

SQL> show parameter log_archive_dest_state_2
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2 string ENABLE




At Secondary Site.

SQL> select * from v$archive_gap;

THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
---------- ------------- --------------
1 110 112

SQL> select VALUE from v$dataguard_stats
2 where name='transport lag';

VALUE
----------------------------------------------------------------
+00 00:31:22
 Excerpts from alert log

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

ARC2 started with pid=30, OS id=12037

Media Recovery Waiting for thread 1 sequence 110

Fetching gap sequence in thread 1, gap sequence 110-112

ARC1: Archival started

ARC2: Archival started

ARC1: Becoming the heartbeat ARCH

ARC1: Becoming the active heartbeat ARCH

Fri Jun 27 14:26:24 2014

ARC3 started with pid=31, OS id=12039

Fri Jun 27 14:26:25 2014

Using STANDBY_ARCHIVE_DEST parameter default value as +DATA

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

Check the current scn number of the standby database.

SQL> recover managed standby database cancel;
Media recovery complete.

SQL> select current_scn from v$database;
CURRENT_SCN
-----------
1327971
 SQL> conn scott/tiger

Connected.

SQL> select created from user_objects where object_name ='TEST';

CREATED
---------
26-JUN-14

The Test Table creation date is showing the earlier time which we had deleted on the Production database but not showing the current one.
Take the incremental backup from the database current scn over the Primary Database.

At Primary Site.

RMAN> backup incremental from scn 1327971 database format '/u01/bkp/inc_stby_%U.bkp';
Starting backup at 27-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=52 device type=DISK
backup will be obsolete on date 04-JUL-14
archived logs will not be kept or backed up
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/db1/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/db1/sysaux01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/db1/test01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/db1/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/db1/users01.dbf
channel ORA_DISK_1: starting piece 1 at 27-JUN-14
channel ORA_DISK_1: finished piece 1 at 27-JUN-14
piece handle=/u01/bkp/inc_stby_33pbt6b9_1_1.bkp tag=TAG20140627T143832 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
using channel ORA_DISK_1
backup will be obsolete on date 04-JUL-14
archived logs will not be kept or backed up
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 27-JUN-14
channel ORA_DISK_1: finished piece 1 at 27-JUN-14
piece handle=/u01/bkp/inc_stby_34pbt6cc_1_1.bkp tag=TAG20140627T143832 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 27-JUN-14
RMAN> exit

[oracle@node1 bkp]$ ls -l
total 13444
-rw-r----- 1 oracle oinstall 3588096 Jun 27 14:39 inc_stby_33pbt6b9_1_1.bkp
-rw-r----- 1 oracle oinstall 10158080 Jun 27 14:39 inc_stby_34pbt6cc_1_1.bkp

Copy the backup to the Standby Host.

[oracle@node1 bkp]$ scp orapwdb1 node2:/u01/app/oracle/product/11.2.0/db_1/dbs/
[oracle@node1 bkp]$ scp -r * node2:/u01/bkp/
oracle@node2's password:
inc_stby_33pbt6b9_1_1.bkp 100% 3504KB 3.4MB/s 00:01
inc_stby_34pbt6cc_1_1.bkp 100% 9920KB 9.7MB/s 00:00

At Standby Site.
 
Shut down the Standby Database, catalog the incremental backup and perform the recovery.
 
 SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount
ORACLE instance started.
Total System Global Area 1033670656 bytes
Fixed Size 1349476 bytes
Variable Size 595593372 bytes
Database Buffers 432013312 bytes
Redo Buffers 4714496 bytes
Database mounted.

[oracle@node2 ~]$ rman target /
connected to target database: DB1 (DBID=1444093493, not open)
RMAN> catalog start with '/u01/bkp';
using target database control file instead of recovery catalog
searching for all files that match the pattern /u01/bkp
List of Files Unknown to the Database
=====================================
File Name: /u01/bkp/inc_stby_2rpbqsba_1_1.bkp
File Name: /u01/bkp/inc_stby_2qpbqsa7_1_1.bkp
Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done
List of Cataloged Files
=======================
File Name: /u01/bkp/inc_stby_2rpbqsba_1_1.bkp
File Name: /u01/bkp/inc_stby_2qpbqsa7_1_1.bkp
 
RMAN> report schema;

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema for database with db_unique_name SDB1
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 710 SYSTEM *** +DATA/sdb1/system01.dbf
2 570 SYSAUX *** +DATA/sdb1/sysaux01.dbf
3 30 UNDOTBS1 *** +DATA/sdb1/undotbs01.dbf
4 5 USERS *** +DATA/sdb1/users01.dbf
5 500 TEST *** +DATA/sdb1/test01.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATA/sdb1/temp01.dbf

RMAN> recover database noredo;
Starting recover at 27-JUN-14
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=33 device type=DISK
channel ORA_DISK_1: starting incremental datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: +DATA/sdb1/system01.dbf
destination for restore of datafile 00002: +DATA/sdb1/sysaux01.dbf
destination for restore of datafile 00003: +DATA/sdb1/undotbs01.dbf
destination for restore of datafile 00004: +DATA/sdb1/users01.dbf
destination for restore of datafile 00005: +DATA/sdb1/test01.dbf
channel ORA_DISK_1: reading from backup piece /u01/bkp/inc_stby_33pbt6b9_1_1.bkp
channel ORA_DISK_1: piece handle=/u01/bkp/inc_stby_33pbt6b9_1_1.bkp tag=TAG20140627T143832
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
Finished recover at 27-JUN-14
RMAN>
　
Create the Standby Controlfile from the Production server and ship that on the standby site.

At Primary Site.

RMAN> backup current controlfile for standby format '/u01/bkp/stby_control.ctl';
Starting backup at 26-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=37 device type=DISK
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 26-JUN-14
channel ORA_DISK_1: finished piece 1 at 26-JUN-14
piece handle=/u01/bkp/stby_control.ctl tag=TAG20140626T175537 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 26-JUN-14

[oracle@node1 bkp]$ scp stby_control.ctl node2:/u01/bkp/
oracle@node2's password:
stby_control.ctl 100% 9856KB 9.6MB/s 00:00
[oracle@node1 bkp]$
　
At Standby Site.

Bounce the Standby Database in nomount mode , restore the controlfile from the controlfile backup.

SQL> shut immediate
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
 
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1033670656 bytes
Fixed Size 1349476 bytes
Variable Size 595593372 bytes
Database Buffers 432013312 bytes
Redo Buffers 4714496 bytes
 
 [oracle@node2 bkp]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Jun 27 14:51:29 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DB1 (not mounted)

RMAN> restore standby controlfile from '/u01/bkp/stby_control.ctl';
Starting restore at 27-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=24 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=+DATA/sdb1/controlfile/current.275.851347679
Finished restore at 27-JUN-14

RMAN>

　

SQL> alter database mount;
Database altered.
connected to target database: DB1 (DBID=1444093493, not open)

RMAN> report schema;
Starting implicit crosscheck backup at 27-JUN-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=31 device type=DISK
Crosschecked 19 objects
Finished implicit crosscheck backup at 27-JUN-14
Starting implicit crosscheck copy at 27-JUN-14
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 27-JUN-14
searching for all files in the recovery area
cataloging files...
cataloging done
List of Cataloged Files
=======================

File Name: +data/SDB1/ARCHIVELOG/2014_06_27/thread_1_seq_107.284.851350363

File Name: +data/SDB1/ARCHIVELOG/2014_06_27/thread_1_seq_108.263.851350365

File Name: +data/SDB1/ARCHIVELOG/2014_06_27/thread_1_seq_109.259.851350365

File Name: +data/SDB1/ARCHIVELOG/2014_06_27/thread_1_seq_113.262.851350651

File Name: +data/SDB1/ARCHIVELOG/2014_06_27/thread_1_seq_114.277.851351187

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA

Report of database schema for database with db_unique_name SDB1

List of Permanent Datafiles

===========================

File Size(MB) Tablespace RB segs Datafile Name

---- -------- -------------------- ------- ------------------------
1 710 SYSTEM *** +DATA/sdb1/system01.dbf
2 570 SYSAUX *** +DATA/sdb1/sysaux01.dbf
3 30 UNDOTBS1 *** +DATA/sdb1/undotbs01.dbf
4 5 USERS *** +DATA/sdb1/users01.dbf
5 500 TEST *** +DATA/sdb1/test01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 20 TEMP 32767 +DATA/sdb1/temp01.dbf


Start the Recovery and check the status of the table created under SCOTT schema and the Database Status.

At Standby Site.

SQL> recover managed standby database using current logfile disconnect;
Media recovery complete.
　
SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DB1 READ ONLY WITH APPLY PHYSICAL STANDBY

　
SQL> conn scott/tiger

Connected.

SQL> select created from user_objects where object_name ='TEST';
CREATED
--------
27-JUN-14

SQL> conn /as sysdba
Connected.

SQL> select current_scn from v$database;
CURRENT_SCN
----------
1331998

SQL> select VALUE from v$dataguard_stats
2 where name='transport lag';

VALUE
----------------------------------------------------------------
+00 00:00:00
　References.

http://saruamit4.wordpress.com/2014/05/03/recovering-standby-database-using-scn-based-backup/#more-441

Create ASM Standby Database for a NON-ASM Active Primary Database in 11g

In this post I'll create a ASM Physical Database on a different host from a NON-ASM Active Primary database.

Here:-

Database_Type     Database_Name      Host   
Primary           db1                node1.oracle.com
Standby           sdb1               node2.oracle.com

Step 1.

Copy the parameter file from the Primary DB host to Standby DB host under $ORACLE_HOME/dbs/ and made the possible changes.

db1.__db_cache_size=432013312
db1.__java_pool_size=4194304
db1.__large_pool_size=4194304
db1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
db1.__pga_aggregate_target=415236096
db1.__sga_target=620756992
db1.__shared_io_pool_size=0
db1.__shared_pool_size=171966464
db1.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/sdb1/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.db_create_file_dest='+DATA/'
*.db_recovery_file_dest='+DATA/'
#*.control_files='/u01/app/oracle/oradata/sdb1/control01.ctl','/u01/app/oracle/fast_recovery_area/sdb1/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='db1'
*.db_unique_name='sdb1'

*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sdb1XDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
*.fal_client='sdb1'*.log_archive_config='DG_CONFIG=(db1,sdb1)'
*.log_archive_dest_1='LOCATION=+DATA/sdb1/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sdb1'
*.log_archive_dest_2='SERVICE=db1 LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) COMPRESSION=ENABLE DB_UNIQUE_NAME=db1'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_file_name_convert='db1','sdb1'
*.db_recovery_file_dest_size=7516192768
*.db_recovery_file_dest='+DATA/'



Step 2.

Copy the Oracle Password file from Primary machine to standby machine under $ORACLE_HOME/dbs/.

Step 3.

Make an entry into listener and tnsnames files for Primary and standby Databases.

For instance SDB1.

Entries Into Listener.ora

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = node2.oracle.com)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = db1)
      (ORACLE_HOME = /u01/app/oracle/product/11.2.0/db_1)
    )

  )

Entries Into tnsnames.ora

DB1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 17x.x.xx.xx)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = db1)
    )
  )

SDB1 =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 17x.2xx.xx.x)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = db1)
    )
  )

Step 4.

Start the standby database in nomount mode

export ORACLE_SID=db1

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

ORACLE instance started.

Total System Global Area  171581440 bytes
Fixed Size                  1343668 bytes
Variable Size             117444428 bytes
Database Buffers           50331648 bytes
Redo Buffers                2461696 bytes

Step 5.

From Primary Database use the following command.

run

{

duplicate target database for standby from active database

spfile

parameter_value_convert 'db1','sdb1'

set db_file_name_convert='/u01/app/oracle/oradata/db1/','+DATA/sdb1/'

set log_file_name_convert='/u01/app/oracle/oradata/db1/','+DATA/sdb1/'

set db_recovery_file_dest_size='5G'

set db_recovery_file_dest='+DATA/'

set control_files='+DATA/'

set db_unique_name='sdb1'

set fal_client='sdb1'

set fal_server='db1'

set standby_file_management='AUTO'

set log_archive_config='dg_config=(db1,sdb1)'

set log_archive_dest_2='service=sdb1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=sdb1'

;

}

[oracle@node1 dbs]$ rman target / auxiliary sys/oracle@sdb1

Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jun 26 12:59:34 2014

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

connected to target database: DB1 (DBID=1444093493)

connected to auxiliary database: DB1 (not mounted)

RMAN> run

{

duplicate target database for standby from active database

spfile

parameter_value_convert 'db1','sdb1'

set db_file_name_convert='/u01/app/oracle/oradata/db1/','+DATA/sdb1/'

set log_file_name_convert='/u01/app/oracle/oradata/db1/','+DATA/sdb1/'

set db_recovery_file_dest_size='5G'

set db_recovery_file_dest='+DATA/'

set control_files='+DATA/'

set db_unique_name='sdb1'

set fal_client='sdb1'

set fal_server='db1'

set standby_file_management='AUTO'

set log_archive_config='dg_config=(db1,sdb1)'

set log_archive_dest_2='service=sdb1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=sdb1'

;

}

2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19>

Starting Duplicate Db at 26-JUN-14

using target database control file instead of recovery catalog

allocated channel: ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: SID=25 device type=DISK

contents of Memory Script:

{

backup as copy reuse

targetfile '/u01/app/oracle/product/11.2.0/db_1/dbs/orapwdb1' auxiliary format

'/u01/app/oracle/product/11.2.0/db_1/dbs/orapwdb1' targetfile

'/u01/app/oracle/product/11.2.0/db_1/dbs/spfiledb1.ora' auxiliary format

'/u01/app/oracle/product/11.2.0/db_1/dbs/spfiledb1.ora' ;

sql clone "alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfiledb1.ora''";

}

executing Memory Script

Starting backup at 26-JUN-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=42 device type=DISK

Finished backup at 26-JUN-14

sql statement: alter system set spfile= ''/u01/app/oracle/product/11.2.0/db_1/dbs/spfiledb1.ora''

contents of Memory Script:

{

sql clone "alter system set audit_file_dest =

''/u01/app/oracle/admin/sdb1/adump'' comment=

'''' scope=spfile";

sql clone "alter system set dispatchers =

''(PROTOCOL=TCP) (SERVICE=sdb1XDB)'' comment=

'''' scope=spfile";

sql clone "alter system set log_archive_dest_1 =

''LOCATION=/u01/app/oracle/fast_recovery_area/sdb1/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db1'' comment=

'''' scope=spfile";

sql clone "alter system set db_file_name_convert =

''/u01/app/oracle/oradata/db1/'', ''+DATA/sdb1/'' comment=

'''' scope=spfile";

sql clone "alter system set log_file_name_convert =

''/u01/app/oracle/oradata/db1/'', ''+DATA/sdb1/'' comment=

'''' scope=spfile";

sql clone "alter system set db_recovery_file_dest_size =

5G comment=

'''' scope=spfile";

sql clone "alter system set db_recovery_file_dest =

''+DATA/'' comment=

'''' scope=spfile";

sql clone "alter system set control_files =

''+DATA/'' comment=

'''' scope=spfile";

sql clone "alter system set db_unique_name =

''sdb1'' comment=

'''' scope=spfile";

sql clone "alter system set fal_client =

''sdb1'' comment=

'''' scope=spfile";

sql clone "alter system set fal_server =

''db1'' comment=

'''' scope=spfile";

sql clone "alter system set standby_file_management =

''AUTO'' comment=

'''' scope=spfile";

sql clone "alter system set log_archive_config =

''dg_config=(db1,sdb1)'' comment=

'''' scope=spfile";

sql clone "alter system set log_archive_dest_2 =

''service=sdb1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=sdb1'' comment=

'''' scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

sql statement: alter system set audit_file_dest = ''/u01/app/oracle/admin/sdb1/adump'' comment= '''' scope=spfile

sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=sdb1XDB)'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_1 = ''LOCATION=/u01/app/oracle/fast_recovery_area/sdb1/archivelog/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=db1'' comment= '''' scope=spfile

sql statement: alter system set db_file_name_convert = ''/u01/app/oracle/oradata/db1/'', ''+DATA/sdb1/'' comment= '''' scope=spfile

sql statement: alter system set log_file_name_convert = ''/u01/app/oracle/oradata/db1/'', ''+DATA/sdb1/'' comment= '''' scope=spfile

sql statement: alter system set db_recovery_file_dest_size = 5G comment= '''' scope=spfile

sql statement: alter system set db_recovery_file_dest = ''+DATA/'' comment= '''' scope=spfile

sql statement: alter system set control_files = ''+DATA/'' comment= '''' scope=spfile

sql statement: alter system set db_unique_name = ''sdb1'' comment= '''' scope=spfile

sql statement: alter system set fal_client = ''sdb1'' comment= '''' scope=spfile

sql statement: alter system set fal_server = ''db1'' comment= '''' scope=spfile

sql statement: alter system set standby_file_management = ''AUTO'' comment= '''' scope=spfile

sql statement: alter system set log_archive_config = ''dg_config=(db1,sdb1)'' comment= '''' scope=spfile

sql statement: alter system set log_archive_dest_2 = ''service=sdb1 ASYNC valid_for=(ONLINE_LOGFILE,PRIMARY_ROLE) db_unique_name=sdb1'' comment= '''' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1033670656 bytes

Fixed Size 1349476 bytes

Variable Size 595593372 bytes

Database Buffers 432013312 bytes

Redo Buffers 4714496 bytes

contents of Memory Script:

{

sql clone "alter system set control_files =

''+DATA/sdb1/controlfile/current.286.851259597'' comment=

''Set by RMAN'' scope=spfile";

backup as copy current controlfile for standby auxiliary format '+DATA/sdb1/controlfile/current.277.851259601';

sql clone "alter system set control_files =

''+DATA/sdb1/controlfile/current.277.851259601'' comment=

''Set by RMAN'' scope=spfile";

shutdown clone immediate;

startup clone nomount;

}

executing Memory Script

sql statement: alter system set control_files = ''+DATA/sdb1/controlfile/current.286.851259597'' comment= ''Set by RMAN'' scope=spfile

Starting backup at 26-JUN-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

copying standby control file

output file name=/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_db1.f tag=TAG20140626T130002 RECID=19 STAMP=851259603

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

Finished backup at 26-JUN-14

sql statement: alter system set control_files = ''+DATA/sdb1/controlfile/current.277.851259601'' comment= ''Set by RMAN'' scope=spfile

Oracle instance shut down

connected to auxiliary database (not started)

Oracle instance started

Total System Global Area 1033670656 bytes

Fixed Size 1349476 bytes

Variable Size 595593372 bytes

Database Buffers 432013312 bytes

Redo Buffers 4714496 bytes

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 newname for tempfile 1 to

"+DATA/sdb1/temp01.dbf";

switch clone tempfile all;

set newname for datafile 1 to

"+DATA/sdb1/system01.dbf";

set newname for datafile 2 to

"+DATA/sdb1/sysaux01.dbf";

set newname for datafile 3 to

"+DATA/sdb1/undotbs01.dbf";

set newname for datafile 4 to

"+DATA/sdb1/users01.dbf";

set newname for datafile 5 to

"+DATA/sdb1/test01.dbf";

backup as copy reuse

datafile 1 auxiliary format

"+DATA/sdb1/system01.dbf" datafile

2 auxiliary format

"+DATA/sdb1/sysaux01.dbf" datafile

3 auxiliary format

"+DATA/sdb1/undotbs01.dbf" datafile

4 auxiliary format

"+DATA/sdb1/users01.dbf" datafile

5 auxiliary format

"+DATA/sdb1/test01.dbf" ;

sql 'alter system archive log current';

}

executing Memory Script

executing command: SET NEWNAME

renamed tempfile 1 to +DATA/sdb1/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 backup at 26-JUN-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile copy

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

output file name=+DATA/sdb1/system01.dbf tag=TAG20140626T130028

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:45

channel ORA_DISK_1: starting datafile copy

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

output file name=+DATA/sdb1/sysaux01.dbf tag=TAG20140626T130028

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile copy

input datafile file number=00005 name=/u01/app/oracle/oradata/db1/test01.dbf

output file name=+DATA/sdb1/test01.dbf tag=TAG20140626T130028

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25

channel ORA_DISK_1: starting datafile copy

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

output file name=+DATA/sdb1/undotbs01.dbf tag=TAG20140626T130028

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

channel ORA_DISK_1: starting datafile copy

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

output file name=+DATA/sdb1/users01.dbf tag=TAG20140626T130028

channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07

Finished backup at 26-JUN-14

sql statement: alter system archive log current

contents of Memory Script:

{

switch clone datafile all;

}

executing Memory Script

datafile 1 switched to datafile copy

input datafile copy RECID=19 STAMP=851259739 file name=+DATA/sdb1/system01.dbf

datafile 2 switched to datafile copy

input datafile copy RECID=20 STAMP=851259739 file name=+DATA/sdb1/sysaux01.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=21 STAMP=851259739 file name=+DATA/sdb1/undotbs01.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=22 STAMP=851259739 file name=+DATA/sdb1/users01.dbf

datafile 5 switched to datafile copy

input datafile copy RECID=23 STAMP=851259739 file name=+DATA/sdb1/test01.dbf

Finished Duplicate Db at 26-JUN-14

RMAN>

Step 6.

Open the database in OPEN mode and start the recovery.

SQL> select name,open_mode from v$database;

NAME OPEN_MODE

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

DB1 MOUNTED

SQL> recover managed standby database using current logfile disconnect;

Media recovery complete.
　

　
SQL> recover managed standby database using current logfile disconnect;

Media recovery complete.

SQL> recover managed standby database cancel;

Media recovery complete.

SQL> alter database open;

Database altered.

SQL> recover managed standby database using current logfile disconnect;

Media recovery complete.

SQL> select process,status from v$managed_standby;

PROCESS STATUS

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

ARCH CONNECTED

ARCH CONNECTED

ARCH CONNECTED

ARCH CONNECTED

RFS IDLE

RFS IDLE

RFS IDLE

RFS IDLE

MRP0 APPLYING_LOG

9 rows selected.

Step 7. 

Now Check the Standby DB  Status.

SQL> select name,open_mode,database_role from v$database;

NAME OPEN_MODE DATABASE_ROLE

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

DB1 READ ONLY WITH APPLY PHYSICAL STANDBY



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

2 /

GROUP# MEMBER STATUS

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

3 +DATA/sdb1/redo03.log

2 +DATA/sdb1/redo02.log

1 +DATA/sdb1/redo01.log

4 +DATA/sdb1/onlinelog/group_4.279.851259755

5 +DATA/sdb1/onlinelog/group_5.281.851259759

6 +DATA/sdb1/onlinelog/group_6.282.851259763

6 rows selected.

SQL> select name from v$controlfile;

NAME

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

+DATA/sdb1/controlfile/current.277.851259601

　

SQL> select FILE# ,NAME,STATUS from v$datafile;

FILE# NAME STATUS

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

1 +DATA/sdb1/system01.dbf SYSTEM

2 +DATA/sdb1/sysaux01.dbf ONLINE

3 +DATA/sdb1/undotbs01.dbf ONLINE

4 +DATA/sdb1/users01.dbf ONLINE

5 +DATA/sdb1/test01.dbf ONLINE