Step By Step Guide On Creating Physical Standby Using RMAN Duplicate In ASM Filesystem For ASM Primary

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.1 to 11.2.0.0 - Release: 10.2 to 11.2

when to use it:

This document offers step by step procedure to create ASM physical standby from ASM primary.

Solution

Pre-Requisite
a) Database (PRIMARY) is up and running and the datafiles, controlfiles residing in ASM.
b) ASM instance and diskgroups configured in STANDBY server.
c) Network connectivity between PRIMARY and STANDBY server
PROCEDURE
1.Enable force logging in PRIMARY.
2.Create SRL(standby redo logs) in PRIMARY.
3.Backup the PRIMARY database.
4.Make proper changes in the parameter file of PRIMARY.
5.Create the parameter file for STANDBY
6.Copy the files (RMAN Backup, init.ora) to STANDBY
7.Establish the connectivity between PRIMARY and STANDBY.
8.Start the STANDBY instance and use RMAN duplicate to create standby database
9. Create SRL on standby.
10.Start the MRP process,
11. Verify whether the log are shipped and applied properly @the standby


Consider two databases of names PRIMARY= PRIMA  and STANDBY= MYSTD

1. Enable Forced Logging on PRIMARY

SQL> ALTER DATABASE FORCE LOGGING;
This step must be applied before taking the backup

2. Configure Standby Redo Log on PRIMARY

NOTE:SIZE OF STANDBY LOGFILE SHOULD BE SAME AS ONLINE LOGFILE

a. Check the log files and sizes,

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

b. Create SRL
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 100M;

NOTE: The SRL will be created in the ASM Disk group mentioned in init.ora parameter DB_CREATE_ONLINE_LOG_DEST_1

c. Verify the standby redo log file groups were created(do this after the creation of stanby database)

SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;

3. Use RMAN to backup PRIMARY database with archivelog & controlfile.

RMAN>backup database format '/tmp/dbbkp/PRIMA_%U';
RMAN>backup archivelog all format '/tmp/dbbkp/PRIMA_ARC_%U';
RMAN> backup current controlfile for standby format '/tmp/dbbkp/PRIMA_CONTROL.bkp';

4. Make the necessary changes to PRIMARY  .

DB_NAME=pri
DB_UNIQUE_NAME=pri
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMA,MYSTD)'
LOG_ARCHIVE_DEST_2= 'SERVICE=stdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=MYSTD'
LOG_ARCHIVE_DEST_STATE_2=DEFER
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=MYSTD
FAL_CLIENT=PRIMA
DB_FILE_NAME_CONVERT='MYSTD','PRIMA'
LOG_FILE_NAME_CONVERT='MYSTD','PRIMA'
STANDBY_FILE_MANAGEMENT=AUTO


5. Create the parameter file for standby,

a. CREATE PFILE='<specify any location>' from spfile; (@primary,)
b. Make the necessary changes, for example,

DB_NAME=PRIMA
DB_UNIQUE_NAME=MYSTD
LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRIMA,MYSTD)'
CONTROL_FILES='+DATA','+FRA'
DB_FILE_NAME_CONVERT='PRIMA','MYSTD'
LOG_FILE_NAME_CONVERT='PRIMA','MYSTD'
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_DEST_2= 'SERVICE=PRIMA LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMA'
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=PRIMA
FAL_CLIENT=MYSTD

Note: If the ASM Disk groups names are different between PRIMARY and STANDBY do the necessary changes in STANDBY with the parameters DB_CREATE_FILE_DEST and DB_CREATE_ONLINE_LOG_DEST_n

6. Copy the files (RMAN Backup, init.ora) to STANDBY
a) Copy the RMAN backup files created in /tmp/dbbkp (step3) to STANDBY in identical location
Example:scp /tmp/dbbkp/* oracle@core1:/tmp/dbbkp/*
b) Copy the init.ora (step 5) to STANDBY
c) Recreate the password file in standby using orapwd utility


7. Establish the connectivity between primary and standby.

In PRIMARY (TNSNAMES.ORA)

MYSTD=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.108)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = MYSTD.server.com)
)
)

In STANDBY (TNSNAMES.ORA)
PRIMA=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.9.200.107)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRIMA.server.com)
)
)

NOTE: Use TNSPING <servicename> to check the connectivity between PRIMARY and STANDBY

8. Start the STANDBY instance and use RMAN duplicate to create standby database

NOTE: Make sure the ASM instance also running.
$export ORACLE_SID=MYSTD
SQL>create spfile from pfile=’<specify the newly created parameter location>’
SQL>startup nomount

NOTE: Connect to catalog if your primary database has catalog database.

$RMAN target sys/<passwd>@primary catalog RMAN/RMAN@RMAN auxiliary sys/<passwd>
RMAN> RUN {
allocate auxiliary channel C1 device type disk;
duplicate target database for standby;
}
9. Create SRL(Standby Redo logs) on standby,

NOTE:SIZE OF STANDBY LOGFILE SHOULD BE SAME AS ONLINE LOGFILE

For Example,

ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 SIZE 100M;

10. Start the MRP process in PRIMARY

In PRIMARY

SQL>ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE;

In STANDBY
SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

11. Verify whether the log are shipped and applied properly in the standby

a. execute on PRIMARY database

SQL> ALTER SYSTEM SWITCH LOGFILE;

b. execute on STANDBY database

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

c. Issue more log switches at PRIMARY

SQL> ALTER SYSTEM SWITCH LOGFILE;

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#;