Creating Physical Standby Using RMAN Duplicate For ASM Standby and ASM Primary

Creating Physical Standby Using RMAN Duplicate in ASM Standby For ASM Primary

Scenario

The primary and standby were up and running, some archives on standby were not applied for some reason, the disk were 91% full. There was a need to clear the disk and recreate standby from Active Primary database.

References

The steps are a combination of different notes. Mainly, We were following this metalink note

"Step By Step Guide On Creating Physical Standby Using RMAN Duplicate In ASM Filesystem For ASM Primary [ID 837102.1]" But since this note was valid both for 10g and 11R2, it created standby from primary backup not from active database because that option was not available for 10g. We need to modify it to create standby from active primary. So we were also referencing Oracle Data Guard Concepts and Administration for 11R2
http://download.oracle.com/docs/cd/E11882_01/server.112/e17022.pdf
where it says we could use "FROM ACTIVE DATABASE", and we need to also specify
NOFILENAMECHECK option for the DUPLICATE FOR STANDBY command.

Assumptions

The steps also assumes following.

The primary init parameters were configured per Data Guard documentation and is up and running.

A pfile was created from primary spfile and was modified for standby parameters and was copied to standby. Then an spfile was create from this copy of pfile for standby and resides in ASM.

A standby password file was created from primary password file using orapw

ASM instance and diskgroups were configured in Standby server.

Network connectivity between PRIMARY and STANDBY server was configured.

Force logging was enabled in primary, standby redologs was created in primary.

(See [[Setup Standby Database Method 1 (RMAN Backup Copy)]] for details or refer to Oracle Data Guard Concepts and Administration for 11R2)

Brief Info About Log Shipping and Applying

Redo received from redo source database via redo transport is written to the
current standby redo log group by an RFS foreground process. When a log switch
occurs on the source database, incoming redo is then written to the next standby
redo log group, and the previously used standby redo log group is archived by an
ARCn foreground process.
The process of sequentially filling and then archiving redo log file groups at source database is mirrored at each redo transport destination by the sequential filling and archiving of standby redo log groups. Each standby redo log file must be at least as large as the largest redo log file in the
redo log of the redo source database. For administrative ease, Oracle recommends that
all redo log files in the redo log at the redo source database and the standby redo log at
a redo transport destination be of the same size. The standby redo log must have at least one more redo log group than the redo log at the redo source database, for each redo thread at the redo source database. At the redo source database, query the V$LOG view to determine how many redo log groups are in the redo log at the redo source database

Steps

1. First of all, delete all the archive logs shipped from primary so the disk space is cleared. Since those archive logs were not applied, we had to use FORCE option to force delete.

RMAN> DELETE NOPROMPT FORCE ARCHIVELOG ALL;

2. Shutdown standby

--set ORCLE_HOME and ORACLE_SID

. oraenv

TESTDB

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL>SHUTDOWN IMMEDIATE

3. Remove all the database files

[oracle@test-testdb-02]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid
[oracle@test-testdb-02 archivelog]$ export ORACLE_SID=+ASM

or
. oraenv
+ASM

[oracle@test-testdb-02]$ asmcmd

cd DATA

rm all the control file, datafiles and onlinelog files, just leave spfile there.

4. Startup nomount using the spfile in ASM

SQL> startup nomount

5. Duplicate For Standby From Active Database

ON PRIMARY test -testdb-01
[oracle@test-testdb-02]$ rman target /

connected to target database: TESTDB (DBID=299787364)

RMAN> connect auxiliary sys/XXXXXX@TESTDB_STANDBY

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-04006: error from auxiliary database: ORA-12528: TNS:listener: all appropriate instances are blocking new connections

Debugging for the above error.

[oracle@test-testdb-02]$ lsnrctl status

STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.2.0 - Production
Start Date 14-AUG-2011 00:46:29
Uptime 3 days 14 hr. 1 min. 21 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/grid/network/admin/listener.ora
Listener Log File /u01/app/oracle/product/11.2.0/grid/network/log/listener.log
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=test-testdb-02.testsystem.edu)(PORT=1521)))
Services Summary...
Service "+ASM" has 1 instance(s).
Instance "+ASM", status READY, has 1 handler(s) for this service...
Service "TESTDB_STANDBY.testsystem.edu" has 1 instance(s).
Instance "TESTDB", status BLOCKED, has 1 handler(s) for this service...
Service "TESTDB_STANDBY_DGMGRL.testsystem.edu" has 1 instance(s).
Instance "TESTDB", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

The output showed that TESTDB_STANDBY.testsystem.edu was blocked, probably because it was in nomount mode and auto register listner is not working the same way as having a physical entry in the listener.

To resolve this error, need to add an entry in the listener.ora file

set the oraenv first (listener for 11R2 is in grid home)

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/grid
or
[oracle@test-testdb-02] . oraenv
+ASM
or
.oraenv
grid

--stop the listener

[oracle@test-testdb-02] lsnrctl stop

edit /u01/app/oracle/product/11.2.0/grid/network/admin/listener.ora

change this

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTDB_STANDBY_DGMGRL.testsystem.edu)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = TESTDB)
)
)

to

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = TESTDB_STANDBY.testsystem.edu)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = TESTDB)
)
(SID_DESC =
(GLOBAL_DBNAME = TESTDB_STANDBY_DGMGRL.testsystem.edu)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/dbhome_1)
(SID_NAME = TESTDB)
)
)

[oracle@test-testdb-02] lsnrctl start

Continue with Step 5. Duplicate For Standby From Active Database

ON PRIMARY test-testdb-01

[oracle@test-testdb-02]$ rman target /

connected to target database: TESTDB (DBID=299787364)

--setup 2 parallel channels for the rman job that follows
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;

-- connect to auxiliary standby from primary rman
RMAN> connect auxiliary sys/XXXXXX@TESTDB_STANDBY
--run the rman duplicate for standby from active database command as follows

RMAN>
run {
allocate auxiliary channel C1 device type disk;
duplicate target database for standby from active database nofilenamecheck;
}

This creates a standby control file and ship to standby asm automatically when not specifying any path. This took about 2 hours.

On STANDBY test-testdb-02:

On metalink note, it says standby redo logs (SRL) need to be created after duplicate, but since we already prepared primary and standby redo logs were created on primary, those were duplicated to standby as well. Otherwise do this

SQL> ALTER DATABASE ADD STANDBY LOGFILE Group 17 SIZE 256000K;

SQL> ALTER DATABASE ADD STANDBY LOGFILE Group 18 SIZE 256000K;

...

verify redologs
select * from v$log;

verify standby logs
select * from v$standby_log

--turn on the real time recovery mode for standby

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

6. Validate that log shipping and applying is working

a. execute on PRIMARY database
SQL> archive log list;

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/TESTDB/archivelog/
Oldest online log sequence 5112
Next log sequence to archive 5127
Current log sequence 5127

SQL> ALTER SYSTEM SWITCH LOGFILE;

SQL> archive log list;

Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/TESTDB/archivelog/
Oldest online log sequence 5113
Next log sequence to archive 5128
Current log sequence 5128

b. execute on STANDBY database
d. Verify the new redo data was archived on the standby database. On the standby database, query the V$ARCHIVED_LOG view to verify the redo data was received and archived on the standby database: SQL> SELECT SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#; NOTE: Verify new archived redo log files were applied. At the standby database, query the V$ARCHIVED_LOG view to verify the archived redo log files were applied. SQL> SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

--set some formatting things first.
SQL> alter session set NLS_DATE_FORMAT='yyyy/mm/dd-HH24:mi:ss';

SQL> set linesize 132;
col message for a100;
SQL> SELECT TIMESTAMP,DEST_ID,MESSAGE FROM V$DATAGUARD_STATUS;

TIMESTAMP DEST_ID MESSAGE
------------------- ---------- ----------------------------------------------------------------------------------------------------
2011/08/17-16:37:33 0 MRP0: Background Managed Standby Recovery process started
2011/08/17-16:37:38 0 Managed Standby Recovery starting Real Time Apply
2011/08/17-16:37:39 0 Media Recovery Log /u01/app/oracle/oradata/TESTDB/archivelog/1_4907_757654478.arc
2011/08/17-16:37:39 0 Media Recovery Log /u01/app/oracle/oradata/TESTDB/archivelog/1_4908_757654478.arc
2011/08/17-16:37:39 0 Media Recovery Log /u01/app/oracle/oradata/TESTDB/archivelog/1_4909_757654478.arc
2011/08/17-16:37:41 0 Media Recovery Log /u01/app/oracle/oradata/TESTDB/archivelog/TESTDB_STANDBY/archivelog/2011_08_17/o1_mf_
1_4910_74rdzx01_.arc

2011/08/17-16:37:44 0 Media Recovery Waiting for thread 1 sequence 4911 (in transit)

SQL> SQL> SELECT pid,process,status,thread#,sequence#,block#,blocks FROM v$managed_standby order by sequence#;

PID PROCESS STATUS THREAD# SEQUENCE# BLOCK# BLOCKS
---------- --------- ------------ ---------- ---------- ---------- ----------
27492 ARCH CONNECTED 0 0 0 0
27494 ARCH CONNECTED 0 0 0 0
3567 RFS IDLE 0 0 0 0
3874 RFS IDLE 0 0 0 0
3571 RFS IDLE 0 0 0 0
27498 ARCH CONNECTED 0 0 0 0
3899 RFS IDLE 0 0 0 0
3569 RFS IDLE 0 0 0 0
27496 ARCH CLOSING 1 4910 262144 1715
3835 RFS IDLE 1 4911 34257 2
3934 MRP0 APPLYING_LOG 1 4911 34256 512000

Notice that the status of MRP0 is APPLYING_LOG, RFS is responsible for receiving logs check to see if the sequence number matches the primary number when doing a log switch.

SQL> SELECT SEQUENCE#, APPLIED, DEST_ID, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

SEQUENCE# APPLIED DEST_ID FIRST_TIME NEXT_TIME
---------- --------- ---------- ------------------- -------------------
4907 YES 2 2011/08/17-14:53:17 2011/08/17-15:30:18
4908 YES 2 2011/08/17-15:30:18 2011/08/17-15:30:30
4909 YES 2 2011/08/17-15:30:30 2011/08/17-16:29:08
4910 NO 1 2011/08/17-16:29:08 2011/08/17-16:34:50
4910 YES 2 2011/08/17-16:29:08 2011/08/17-16:34:50
4911 IN-MEMORY 1 2011/08/17-16:34:50 2011/08/17-16:40:10
4911 IN-MEMORY 2 2011/08/17-16:34:50 2011/08/17-16:40:10

SQL> /

SEQUENCE# APPLIED DEST_ID FIRST_TIME NEXT_TIME
---------- --------- ---------- ------------------- -------------------
4907 YES 2 2011/08/17-14:53:17 2011/08/17-15:30:18
4908 YES 2 2011/08/17-15:30:18 2011/08/17-15:30:30
4909 YES 2 2011/08/17-15:30:30 2011/08/17-16:29:08
4910 NO 1 2011/08/17-16:29:08 2011/08/17-16:34:50
4910 YES 2 2011/08/17-16:29:08 2011/08/17-16:34:50
4911 IN-MEMORY 1 2011/08/17-16:34:50 2011/08/17-16:40:10
4911 YES 2 2011/08/17-16:34:50 2011/08/17-16:40:10
4912 IN-MEMORY 1 2011/08/17-16:40:10 2011/08/17-16:42:05
4912 IN-MEMORY 2 2011/08/17-16:40:10 2011/08/17-16:42:05

7. Validate ASM files

[oracle@test-testdb-02]$ . oraenv
ORACLE_SID = [TESTDB] ? +ASM

[oracle@test-testdb-02]$ asmcmd
ASMCMD> ls
DATA/
ASMCMD> cd data/TESTDB_STANDBY/
ASMCMD> ls

ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
spfileTESTDB_STANDBY.ora
ASMCMD> cd CONTROLFILE
ASMCMD> ls
Current.335.759425381
Current.353.759425381
ASMCMD> cd ..
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
spfileTESTDB_STANDBY.ora
ASMCMD> cd datafile
ASMCMD> ls
TEST1_DATA.338.759427589
TEST1_INDEX.261.759428529
TEST2_DATA.260.759428553
TEST3_DATA.342.759427135
TEST3_INDEX.270.759427951
SYSAUX.330.759428759
SYSTEM.264.759428353
UNDOTBS1.349.759425891
USERS.322.759428871
ASMCMD> cd ..
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
spfileTESTDB_STANDBY.ora
ASMCMD> cd onlinelog
ASMCMD> ls
group_1.304.759428953
group_10.295.759428959
group_11.293.759428961
group_12.292.759428961
group_13.291.759428961
group_14.290.759428963
group_15.289.759428963
group_16.288.759428963
group_17.287.759428965
group_18.286.759428965
group_19.285.759428967
group_2.303.759428953
group_20.284.759428967
group_21.283.759428967
group_22.282.759428969
group_23.281.759428969
group_24.280.759428971
group_25.279.759428971
group_26.278.759428973
group_27.277.759428973
group_28.275.759428975
group_29.274.759428975
group_3.302.759428953
group_30.273.759428975
group_31.294.759428977
group_32.276.759428977
group_33.257.759428979
group_4.301.759428955
group_5.300.759428955
group_6.299.759428957
group_7.298.759428957
group_8.297.759428957
group_9.296.759428959
ASMCMD> cd ..
ASMCMD> ls
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
spfileTESTDB_STANDBY.ora
ASMCMD> cd parameterfile
ASMCMD> ls
spfile.352.757690657