In this post I have demonstrated Step By Step method to create ASM physical standby DB from ASM primary DB Using RMAN Duplicate (backup based approach)
| Environment Details |
| OS Version : AIX 6.1 |
| Oracle Version : 11.2.0.3 PSU 8 |
| Storage System : ASM |
| Grid Home : /prasprd/gridinfra/grid1 |
| RDBMS Home :/prasprd/db/db1 |
| Primary DB : PRASPRD Host: LABPRIM |
| Standby DB : PRASSBY Host : LABSBY |
| Pre-Requisite |
- Database (PRIMARY) is up and running and the data files, control files, redo log files, archive files residing in ASM.
- ASM instance and disk groups configured in STANDBY server.
- Network connectivity between PRIMARY and STANDBY server.
| PROCEDURE |
- Enable force logging in PRIMARY.
- Create SRL (standby redo logs) in PRIMARY.
- Backup the PRIMARY database.
- Make proper changes in the parameter file of PRIMARY.
- Create the parameter file for STANDBY .
- Copy the files (RMAN Backup, init.ora) to STANDBY.
- Establish the connectivity between PRIMARY and STANDBY.
- Start the STANDBY instance and use RMAN duplicate to create standby database.
- Create SRL on standby.
- Start the MRP process,
- Verify whether the log are shipped and applied properly @the standby .
| Step by Step to create new physical standby |
1. Enable Forced Logging on PRIMARY
SQL> ALTER DATABASE FORCE LOGGING;
2. Configure Standby Redo Log on PRIMARY
a. Check the log files and sizes,
SQL>SELECT GROUP#, BYTES FROM V$LOG;
b. Create SRL
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 (‘/path/<File Name>’, ‘/path/<File Name>’ ) SIZE 100M …. ;
NOTE: SIZE OF STANDBY LOGFILE SHOULD BE SAME AS ONLINE LOGFILE
c. Verify the standby redo log file groups were created (do this after the creation of standby 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/PRASPRD_%U’;
RMAN>backup archivelog all format ‘/tmp/dbbkp/PRASPRD_ARC_%U’;
RMAN> backup current controlfile for standby format ‘/tmp/dbbkp/PRASPRD_CONTROL.bkp’;
4. Make the necessary changes to PRIMARY.
DB_NAME=PRIMA
DB_UNIQUE_NAME=PRIMA
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRASPRD,PRASSBY)’
LOG_ARCHIVE_DEST_2= ‘SERVICE=stdby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRASSBY’
LOG_ARCHIVE_DEST_STATE_2=DEFER
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
LOG_ARCHIVE_FORMAT=log%t_%s_%r.arc
LOG_ARCHIVE_MAX_PROCESSES=30
FAL_SERVER=PRASSBY
FAL_CLIENT=PRASPRD
DB_FILE_NAME_CONVERT=’ PRASSBY ‘,’PRASPRD’ (ASMDISKGROUP NAME)
LOG_FILE_NAME_CONVERT=’PRASSBY’,’PRASPRD’ (ASMDISKGROUP NAME)
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.
DB_NAME=PRIMA
DB_UNIQUE_NAME=PRASSBY
LOG_ARCHIVE_CONFIG=’DG_CONFIG=(PRASPRD,PRASSBY)’
CONTROL_FILES=’+DATA’,’+FRA’
DB_FILE_NAME_CONVERT=’PRASPRD’,’PRASSBY’ (ASMDISKGROUP NAME)
LOG_FILE_NAME_CONVERT=’PRASPRD’,’PRASSBY’ (ASMDISKGROUP NAME)
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=PRASPRD’
LOG_ARCHIVE_DEST_STATE_1=ENABLE
LOG_ARCHIVE_DEST_STATE_2=ENABLE
REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE
STANDBY_FILE_MANAGEMENT=AUTO
FAL_SERVER=PRASPRD
FAL_CLIENT=PRASSBY
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
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)
PRASSBY=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.1)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRASSBY.pras.com)
)
)
In STANDBY (TNSNAMES.ORA)
PRASPRD=
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.10.10.2)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = PRASPRD.pras.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=PRASSBY
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,
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 (‘/path/<File Name>’, ‘/path/<File Name>’ ) SIZE 100M …. ;
NOTE: SIZE OF STANDBY LOGFILE SHOULD BE SAME AS ONLINE LOGFILE
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#;
Pradeep's Blog 