Oracle RAC Physical Standby DataGuard Creation
The following article describes how to create an Oracle RAC 11gR2 physical standby database with Active DataGuard real-time apply enabled on Linux. This procedure can easily be modified for any other supported platform. Creating a standby database as RAC directly from primary RAC, rather than standalone, saves time and prevents potential issues, while converting from standalone to RAC. In addition, the following approach ensures the proper placement of database files at the standby ASM disk groups.
Both systems are using ASM for storage. The storage layout is identical between the primary and the standby databases. System, data, redo log, and fast recovery area are located on separate ASM disks for performance and recoverability reasons.
The source and target databases are 2-node Oracle RAC 11gR2. Maintaining standby database as RAC with Active DataGuard and a real-time apply feature allows using it for reading, backup, and testing by converting it over to a snapshot standby database and back after the completion.
The following are the names and their meaning used in this article.
Name
|
Description
|
MY_DB_DOMAIN
|
Database domain name
|
MYDB
|
Database name
|
STNDBY
|
Unique database name
|
stndbyhost1, stndbyhost2
|
Standby hosts
|
STNDBY1, STNDBY2
|
Standby instances
|
PRIMARY1, PRIMARY2
|
Primary instances
|
primaryhost1, primaryhost2
|
Primary hosts
|
Host’s virtual IP
| |
primary-grid-scan-name
|
Primary RAC Grid SCAN
|
standby-grid-scan-name
|
Standby RAC Grid SCAN
|
ASM_diskN
|
ASM disk name N
|
Oracle Grid should already be installed and running on all of the standby hosts. Oracle database software should also be installed on all of the standby hosts. All the prerequisites should have been completed for both environments for the Oracle physical standby DataGuard configuration.
The following are the actual steps needed for this process.
1. Prepare the primary database
a. Ensure that the database is in archivelog mode
SQL > SELECT LOG_MODE FROM V$DATABASE; LOG_MODE ------------ ARCHIVELOG
b. Enable force logging, so that all database changes are logged
SQL > ALTER DATABASE FORCE LOGGING;
c. Create standby redo logs
SQL > ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 '' SIZE GROUP 6 ' ' SIZE GROUP 7 ' ' SIZE ; SQL > ALTER DATABASE ADD STANDBY LOGFILE THREAD 2 GROUP 8 ' ' SIZE GROUP 9 ' ' SIZE GROUP 10 ' ' SIZE ;
Where and specify the location and size of log files
Note the following when creating standby redo logs:
- The quantity is determined by (maximum # of logfile groups +1) * maximum # of threads.
- Must be the same size as online redo logs.
- Should not be multiplexed.
2. Create $ORACLE_HOME/dbs/initSTNDBY1.ora on stndbyhost1 to start an auxiliary database instance.
*.db_domain='MY_DB_DOMAIN' *.db_name='MYDB' *.db_unique_name='STNDBY'
Note: delete spfileSTNDBY1.ora if one exists.
3. Create adump directory on stndbyhost1 and stndbyhost2.
CMD > mkdir -p $ORACLE_BASE/admin/STNDBY/adump
4. Create and start a temporary listener with static service information on stndbyhost1 in database home.
Edit $ORACLE_HOME/network/admin/listener.ora as follows:
SID_LIST_LISTENER_DBCLONE =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = STNDBY.MY_DB_DOMAIN)
(ORACLE_HOME = $ORACLE_HOME)
(SID_NAME = STNDBY1)
)
)
LISTENER_DBCLONE =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL=TCP)(HOST = stndbyhost1)(PORT = 1521))
)
)
)
Start the listener.
CMD > lsnrctl start LISTENER_DBCLONE
5. Add standby database to $ORACLE_HOME/network/admin/tnsnames.ora on primaryhost1.
This entry points to the listener in the previous step.
STNDBY1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = stndbyhost1-vip)(PORT = 1521)) (CONNECT_DATA = (UR=A) (SERVER = DEDICATED) (SERVICE_NAME = STNDBY.MY_DB_DOMAIN) ) )
Note: (UR=A) is needed to connect to a BLOCKED unmounted database instance.
6. Modify and reload the standby grid listeners with static service information for the standby database.
Edit $GRID_HOME/network/admin/listener.ora on both standby hosts and add the following configuration.
stndbyhost1
LISTENER = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL=IPC)(KEY=LISTENER)) (ADDRESS = (PROTOCOL = TCP)(HOST = stndbyhost1-vip)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = STNDBY.MY_DB_DOMAIN) (ORACLE_HOME = $ORACLE_HOME) (SID_NAME = STNDBY1) ) )
stndbyhost2
LISTENER = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = stndbyhost2-vip)(PORT = 1521)) ) ) SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = STNDBY.MY_DB_DOMAIN) (ORACLE_HOME = $ORACLE_HOME) (SID_NAME = STNDBY2) ) )
7. Reload the grid listeners on stndbyhost1 and stndbyhost2.
CMD > lsnrctl reload
8. Copy the password file from primaryhost1 to stndbyhost1 and stndbyhost2.
CMD > scp $ORACLE_HOME/dbs/orapwMYDB1 stndbyhost1:$ORACLE_HOME/dbs/orapwSTNDBY1
CMD > scp $ORACLE_HOME/dbs/orapwMYDB1 stndbyhost2:$ORACLE_HOME/dbs/orapwSTNDBY2
9. Add instance name to /etc/oratab.
Add STNDBY1 instance name to /etc/oratab on stndbyhost1 and STNDBY2 on stndbyhost2 and set the environment. Note that command line starts with dot “.” to make the environment values permanent.
CMD >. oraenv
10. Start the standby database in nomount state on stndbyhost1.
CMD > sqlplus / as sysdba SQL > startup nomount SQL > exit
11. Duplicate the database from primaryhost1.
Note: standby database name STNDBY1 matches to the one in step 4.
Parameter notes:
- db_create_file_dest and db_recovery_file_dest must be reset in order for all the files to be placed at the same location on destination as on the source.
- cluster_database must be initially disabled, because duplication works with one node only.
- remote_listener is required to register the database with SCAN listener.
Allocate multiple primary channels, as the primary work is performed by target channels.
CMD > rman target=sys@PRIMARY1 auxiliary=sys@STNDBY1
run
{
allocate channel prmy1 type disk;
allocate channel prmy2 type disk;
allocate channel prmy3 type disk;
allocate channel prmy4 type disk;
allocate auxiliary channel stby type disk;
DUPLICATE DATABASE FOR STANDBY FROM ACTIVE DATABASE
NOFILENAMECHECK
SPFILE
PARAMETER_VALUE_CONVERT 'MYDB','STNDBY'
SET db_unique_name='STNDBY'
SET db_file_name_convert='/MYDB/','/STNDBY/'
SET log_file_name_convert='/MYDB/','/STNDBY/'
SET fal_server='(DESCRIPTION = (LOAD_BALANCE = ON)(ADDRESS = (PROTOCOL = TCP)(HOST = primary-grid-scan-name)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = MYDB.MY_DB_DOMAIN)))' SET standby_file_management='AUTO' SET log_archive_config='dg_config=(STNDBY,MYDB)'
SET log_archive_dest_1='LOCATION=use_db_recovery_file_dest', 'valid_for=(ALL_ROLES,ALL_LOGFILES)'
SET log_archive_dest_2='service="(DESCRIPTION = (LOAD_BALANCE = ON)(ADDRESS = (PROTOCOL = TCP)(HOST = primary-grid-scan-name)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = MYDB.MY_DB_DOMAIN)))"', 'LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="MYDB" net_timeout=30', 'valid_for=(all_logfiles,primary_role)' SET log_archive_dest_state_2='enable' SET remote_listener='standby-grid-scan-name:1521' SET cluster_database='false' RESET db_create_file_dest RESET db_recovery_file_dest ; } exit
12. Set standby database parameters.
The following parameters were reset before the duplication and should be set now.
Run the following on the stndbyhost1, while replacing “+ARCH” and “+APP” with your own values:
SQL > ALTER SYSTEM SET db_recovery_file_dest='+ARCH'; SQL > ALTER SYSTEM SET db_create_file_dest='+APP'; SQL > ALTER SYSTEM SET cluster_database=true SCOPE=spfile; SQL > ALTER SYSTEM RESET db_file_name_convert; SQL > ALTER SYSTEM RESET log_file_name_convert;
13. Create pfile from spfile on stndbyhost1.
CMD > cd $ORACLE_HOME/dbs SQL > CREATE pfile='p.ora' FROM spfile;
Edit pfile as follows:
- Remove parameter local_listener, if one exists.
- Replace all parameter prefixes MYDB[N] with STNDBY[N], while keeping parameter values.
14. Create spfile on ASM from pfile created in the previous step.
SQL > CREATE spfile='+SYS/STNDBY/spfileSTNDBY.ora' FROM pfile='p.ora';
15. Prepare the standby database on stndbyhost1.
a. Shutdown the standby database and listener LISTENER_DBCLONE on stndbyhost1.
b. Rename or delete $ORACLE_HOME/network/admin/listener.ora, as it was used for duplication only.
16. Create initialization file on the stndbyhost1.
a. Remove spfile from $ORACLE_HOME/dbs.
b. Replace the contents of $ORACLE_HOME/dbs/initSTNDBY1.ora as follows:
SPFILE='+SYS/STNDBY/spfileSTNDBY.ora'
17. Prepare database on primaryhost1 for DataGuard.
SQL > ALTER SYSTEM SET log_archive_config='dg_config=(MYDB,STNDBY)'; SQL > ALTER SYSTEM SET log_archive_dest_1='LOCATION=+ARCH valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=MYDB'; SQL > ALTER SYSTEM SET log_archive_dest_2='service="(DESCRIPTION = (LOAD_BALANCE = ON) (ADDRESS = (PROTOCOL = TCP)(HOST = primary-grid-scan-name)(PORT = 1521)) (CONNECT_DATA = (SERVICE_NAME = STNDBY.MY_DB_DOMAIN)))"', 'LGWR ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=300 db_unique_name="STNDBY" net_timeout=30', 'valid_for=(all_logfiles,primary_role)'; SQL > ALTER SYSTEM SET log_archive_dest_state_2='enable'; SQL > ALTER SYSTEM SET dg_broker_start=true;
18. Register RAC on the standby.
Run the following commands on stndbyhost1 or stndbyhost2 to register standby RAC with Oracle Grid, while replacing ASM disk groups “ASM_disk” with the actual ones
CMD > srvctl add database -d STNDBY -o $ORACLE_HOME -c RAC -a "ASM_disk1,ASM_disk2,..., ASM_diskN" -p "+SYS/STNDBY/spfileSTNDBY.ora" -m "MY_DB_DOMAIN" -r physical_standby -n MYDB CMD > srvctl add instance -d STNDBY -n stndbyhost1 -i STNDBY1 CMD > srvctl add instance -d STNDBY -n stndbyhost2 -i STNDBY2
19. Startup the standby RAC from any standby node.
CMD > srvctl start database -d STNDBY
20. Enable flashback and start the Managed Recovery Process (MRP) on stndbyhost1.
SQL > ALTER DATABASE FLASHBACK ON; SQL > ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
If licensed for Active Dataguard (ADG), then open the Standby Database in READ ONLY mode:
SQL > ALTER DATABASE OPEN READ ONLY;
21. Verify the standby database.
On primaryhost1:
SQL > ALTER SYSTEM ARCHIVE LOG CURRENT;
On stndbyhost1 and primaryhost1:
The sequence number should be the same on both databases.
SQL > SELECT sequence#, first_time, next_time, applied
FROM v$archived_log ORDER BY sequence#;
22. Create DataGuard configuration on the primaryhost1.
CMD > dgmgrl / CREATE CONFIGURATION 'MYDB.MY_DB_DOMAIN' AS PRIMARY DATABASE IS 'MYDB' CONNECT IDENTIFIER IS '(DESCRIPTION = (LOAD_BALANCE = ON) (ADDRESS = (PROTOCOL = TCP)(HOST = primary-grid-scan-name)(PORT = 1521))(CONNECT_DATA = (SERVICE_NAME = MYDB.MY_DB_DOMAIN)))'; ADD DATABASE 'STNDBY' AS CONNECT IDENTIFIER IS '(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST= standby-grid-scan-name) (PORT=1521)) (CONNECT_DATA=(SERVICE_NAME=STNDBY.MY_DB_DOMAIN)(SERVER=DEDICATED)))'; ENABLE CONFIGURATION; EDIT DATABASE 'STNDBY' SET PROPERTY 'Enterprise Manager Name' = 'STNDBY.MY_DB_DOMAIN'; EDIT DATABASE 'MYDB' SET PROPERTY 'Enterprise Manager Name' = 'MYDB.MY_DB_DOMAIN';
23. Add the new standby database to Oracle Enterprise Manager (OEM).
