Creating a RAC physical standby for a single instance primary database in Oracle 19c ensures high availability and disaster recovery. This comprehensive guide will walk you through the preparation, configuration, and validation steps to set up a reliable RAC physical standby.
Introduction to RAC Physical Standby
A RAC physical standby database provides redundancy and improves database availability by replicating the primary database in a RAC environment. The primary goal is to ensure that the standby database can take over seamlessly in case of primary database failure, providing minimal downtime and data loss. Setting up a RAC physical standby involves multiple steps that ensure the standby database is synchronized with the primary database.
Preparation for RAC physical Standby Setup
Copying Essential Files
Begin by copying essential files such as the password file, wallet, and adump directory from the source database to both RAC nodes. This ensures that both nodes have the necessary credentials and configurations. Use secure copy (scp) to transfer these files to the appropriate locations on each node.
Creating oratab Entries
Create the oratab entries on both nodes to ensure the databases can be managed easily. This involves editing the /etc/oratab file to include entries for both the primary and standby databases.
vi /etc/oratab--Add the below entries:
STDB:/u01/app/oracle/product/19c/dbhome_1:N --On Both nodes
STDB1:/u01/app/oracle/product/19c/dbhome_1:N --On Node1
STDB2:/u01/app/oracle/product/19c/dbhome_1:N --On Node2
Add the necessary entries for your databases.
Configuring TNS Entries and Listeners
Add TNS entries on both nodes and configure static listener entries on the standby node. This step ensures that the primary and standby databases can communicate effectively. Proper configuration of TNS entries is critical for the seamless operation of the RAC physical standby.
DB_PRIMARY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = primaryhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER= DEDICATED)
(SERVICE_NAME = primarydb)
)
)
DB_STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standbyhost)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME= STDB)
)
)
Create a physical stdby database for later conversion to RAC
Preparing the Primary Database
Startup the primary database and set it to ARCHIVELOG mode. This is crucial for enabling the logging of changes, which will be used by the standby database.
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> alter database force logging;
Configuring Standby Redo Logs
Create standby redo logs on the primary database to ensure that redo data is transmitted to the standby database efficiently. This step is essential for maintaining synchronization between the primary and RAC physical standby databases. You’ll have to add 1+ than the total number of normal redo groups, so if the database has 3 redolog groups, you must add 4 standby redolog groups.
SQL> alter database add standby logfile '+DATA/DB/standby_redo01.log' size 50M;
SQL> alter database add standby logfile '+DATA/DB/standby_redo02.log' size 50M;
SQL> alter database add standby logfile '+DATA/DB/standby_redo03.log' size 50M;
SQL> alter database add standby logfile '+DATA/DB/standby_redo04.log' size 50M;
Creating PFILE for Standby
Generate a PFILE from the SPFILE on the primary database and transfer it to the standby. This file contains the necessary parameters to start the standby database.
SQL> create pfile='/path/to/pfile.ora' from spfile;
Transfer the PFILE to the standby server.
scp /path/to/pfile.ora oracle@standby:/path/to/
Editing Standby PFILE
Edit the standby PFILE with the necessary parameters to configure it as a RAC physical standby.
*.db_unique_name='STDB'
*.log_archive_config='DG_CONFIG=(PRIMARYDB,STDB)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STDB'
*.log_archive_dest_2='SERVICE=DBPRIMARY_DG LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) NET_TIMEOUT=30 DB_UNIQUE_NAME=PRIMARYDB'
*.fal_server='DBPRIMARY_DG'
*.service_names='PRIMARYDB, STDB'
*.standby_file_management=AUTO
Starting the Standby Instance
Start the standby instance and create the SPFILE from the PFILE. This step is crucial for initializing the RAC standby setup.
$ . oraenv
ORACLE_SID = [standby] ? STDB
SQL> create spfile from pfile='/path/to/pfile.ora';
Creating SPFILE and Password File in ASM
Create the Password File on Primary and Transfer to Standby
On the primary database, create the password file if it doesn’t exist. This file is necessary for authenticating users in the RAC standby configuration.
orapwd file=$ORACLE_HOME/dbs/orapwDB password=yourpassword entries=10
Transfer the password file to the standby server.
scp $ORACLE_HOME/dbs/orapwDB oracle@standby:/path/to/
Move the Password File to ASM on Standby
On the standby server, use asmcmd to copy the password file to ASM. This ensures that the password file is accessible to all nodes in the RAC physical standby setup.
asmcmd pwcopy /path/to/orapwDB +DATA/DB/orapwSTDB
Adding RAC Physical Standby Database Configuration to SRVCTL
Add the standby database configuration to SRVCTL to manage the RAC environment. SRVCTL is a critical tool for managing Oracle RAC configurations.
srvctl add database -d STDB -c RAC -o /u01/app/oracle/product/19c/dbhome_1 -p +DATA/DB/spfileSTDB.ora -s OPEN -t IMMEDIATE -n STDB
Configuring SRVCTL for Password File
Configure SRVCTL to use the password file in ASM. This configuration is necessary for the RAC physical standby to function correctly.
srvctl modify database -d STDB -pwfile +DATA/DB/orapwSTDB
Running RMAN Duplication
Connect to RMAN and run the duplication script to create the standby database. This step clones the primary database to the standby location, ensuring that the RAC physical standby is an exact replica of the primary database.
rman target "sys/password@DBPRIMARY" auxiliary "sys/password@DBSTANDBY" <<EOF
run {
allocate channel p1 type disk;
allocate channel p2 type disk;
allocate channel p3 type disk;
allocate auxiliary channel s1 type disk;
allocate auxiliary channel s2 type disk;
duplicate target database for standby from active database using compressed backupset
spfile
set db_name='PRIMARYDB'
set db_unique_name='STDB'
set db_file_name_convert='/db01/primary/data','+DATA/STANDBY/DATAFILE' -- Add all existing locations for adjustments
set log_file_name_convert='/redo1a/primary/log','+REDO1A' -- Add all existing locations for adjustments
set fal_server='PRIMARYDB'
set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=STDB'
set standby_file_management='AUTO'
set db_recovery_file_dest='+FRA'
set db_recovery_file_dest_size='3000G'
set control_files='+DATA/STANDBY/CONTROLFILE/control01.ctl','+FRA/STANDBY/CONTROLFILE/control02.ctl'
nofilenamecheck;
}
exit
EOF
Validating Standby Configuration
Check if the standby database is in the correct role to ensure it can take over in case of a primary database failure. This step confirms that the RAC physical standby is configured correctly.
SQL> select name, db_unique_name, database_role from v$database;
Activating Redo Apply
Activate the redo apply process on the standby database to keep it synchronized with the primary database. This process applies the redo logs received from the primary database to the RAC physical standby.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;
Verifying Archive Destinations
Verify that the archive destinations are configured correctly to ensure that redo logs are being shipped and applied. This verification step is crucial for the proper functioning of the RAC standby setup.
SQL> select dest_id, status, valid_type, valid_role, valid_now, error from v$archive_dest;
Converting the Single Instance to RAC
Shutting Down the Standby Instance
Shut down the standby instance to prepare it for conversion to RAC. This step is necessary to change the configuration of the standby database.
SQL> shutdown immediate;
Editing the PFILE for RAC
Edit the original PFILE to configure it as a cluster database, enabling the RAC configuration. This change is critical for converting the single instance standby to a RAC physical standby.
*.cluster_database=trueorSQL> alter system set cluster_database=true scope=spfile;
Starting Up the Database in NOMOUNT Mode
Start up the database in NOMOUNT mode using the original PFILE. This step prepares the database for the creation of the SPFILE in ASM.
SQL> startup nomount pfile='$ORACLE_HOME/dbs/initdb.ora'
Creating the SPFILE in ASM
Create an SPFILE from the PFILE in ASM to store the database configuration. The SPFILE in ASM ensures that all nodes in the RAC physical standby can access the configuration.
SQL> create spfile='+DATA/db/spfiledb.ora' from pfile='$ORACLE_HOME/dbs/initdb.ora';
Configuring SRVCTL
Set up the SRVCTL configuration for the RAC database to manage it as a cluster database. This configuration is essential for the RAC physical standby.
--Just to make sure, remove the current srvctl entry:srvctl remove database -d STDB
srvctl add database -d STDB -c RAC -o /u01/app/oracle/product/19c/dbhome_1 -p +DATA/RACDB/spfileSTDB.ora -s OPEN -t IMMEDIATE -n STDB
Add instances to the configuration to ensure high availability and load balancing.
srvctl add instance -d STDB -i STDB1 -n node1
srvctl add instance -d STDB -i STDB2 -n node2
srvctl enable instance -d STDB -i "STDB1,STDB2"
Starting Up Instances in Mount Mode
Start up instance 1 in mount mode to prepare it for the RAC standby configuration. This step is necessary to begin the managed apply process on the RAC physical standby.
$ srvctl start instance -d STDB -i STDB1 -o mount
Starting Managed Apply on Node 1
Start managed apply on node 1 to begin applying redo logs. This process keeps the RAC physical standby synchronized with the primary database.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Starting Up Node 2 in Mount Mode
Start up node 2 in mount mode to complete the RAC configuration. This step ensures that both nodes in the RAC physical standby are operational.
$ srvctl start instance -d STDB -i STDB2 -o mount
Conclusion
Setting up a RAC physical stdby for a single instance primary database in Oracle 19c involves multiple steps, including configuring network services, preparing the primary and stdby databases, and ensuring proper data synchronization. By following these detailed instructions, you can achieve a reliable and high-performing RAC physical standby setup that enhances your database’s availability and resilience. This setup is crucial for disaster recovery and maintaining high availability in a production environment.
See more on Oracle’s website!
Be Oracle Dataguard Certified Professional, this world is full of opportunities for qualified DBAs!
