Standby Database Duplication is a crucial process for ensuring disaster recovery and high availability in Oracle 19c. This guide will walk you through creating a physical standby database using the RMAN DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE command. By understanding the nuances of RMAN Duplication, you can set up a reliable standby DB efficiently. This will serve as an essential intro to Database Duplication, making complex concepts accessible for all levels of expertise.
Standby Database Creation – Preparation
Creating a standby database involves several steps to ensure it is an exact copy of the primary database. This setup is vital for maintaining data redundancy and ensuring continuous availability.
Preparation for Standby Database Duplication:
Firstly, prepare the primary database by configuring the necessary parameters and ensuring it is in archive log mode.
Example:
ALTER DATABASE ARCHIVELOG;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary_db' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby_db LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) NET_TIMEOUT=30 DB_UNIQUE_NAME=standby_db' SCOPE=SPFILE;
ALTER SYSTEM SET FAL_SERVER=standby_db SCOPE=SPFILE;
ALTER SYSTEM SET DB_FILE_NAME_CONVERT='/primary/','/standby/' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_FILE_NAME_CONVERT='/primary/','/standby/' SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=SPFILE;
Creating the Password File and SPFILE for Standby DB Duplication:
Create the password file and SPFILE for the standby database.
Example:
orapwd file='+DATA/orapwstandby' password=your_password entries=10
SQL> CREATE SPFILE='+DATA/spfilestandby.ora' FROM PFILE='/path/to/pfile.ora';
Adding Listener and TNS Entries for Standby Database Duplication:
Ensure the proper listener and TNS entries are configured on both primary and standby servers to facilitate communication.
Example:
LISTENER.ORA:
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = standby_db)
(ORACLE_HOME = /u01/app/oracle/product/19c/dbhome_1)
(SID_NAME = standby)
)
)
TNSNAMES.ORA:
standby_db =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = standby-host)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = standby_db)
)
)
Standby Database Duplication – RMAN Duplication Process
Using the RMAN DUPLICATE command is essential for duplicating the primary database to create the standby database. This process involves connecting to both the primary and standby databases and running the duplicate command.
Step-by-Step RMAN Duplication:
- Connect to RMAN for Standby DB Duplication:
Example:
rman TARGET sys/password@primary_db AUXILIARY sys/password@standby_db
- Run the DUPLICATE Command for Standby Database Duplication:
Example:
run {
allocate channel prmy1 type disk;
allocate auxiliary channel stby1 type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert ('primary','standby','PRIMARY','STANDBY')
set db_name='primary'
set db_unique_name='standby'
set db_file_name_convert='PRIMARY','STANDBY'
set log_file_name_convert='PRIMARY','STANDBY'
set fal_server='primary'
set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES,ALL_ROLES) db_unique_name=standby'
nofilenamecheck;
allocate auxiliary channel stby type disk;
}
Post-Duplication Configuration – Data Guard Setup
After duplicating the standby database, several configurations are necessary to ensure it functions correctly as a standby database.
Configuring Data Guard for Standby Database Duplication:
Set up Data Guard parameters to manage redo transport and apply services.
Example:
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary_db,standby_db)' SCOPE=BOTH;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_STATE_2=ENABLE SCOPE=BOTH;
Starting Redo Apply for Standby DB Duplication:
Start the redo apply process on the standby database to begin applying the redo logs from the primary database.
Example:
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;
Troubleshooting and Monitoring – Standby Database Duplication
Verifying Configuration for Standby DB Duplication:
Check the configuration to ensure the standby database is correctly set up and synchronized with the primary database.
Example:
SELECT DEST_ID, STATUS, DESTINATION, ERROR FROM V$ARCHIVE_DEST;
SELECT NAME, OPEN_MODE FROM V$DATABASE;
Monitoring Redo Apply for Standby Database Duplication:
Monitor the redo apply process to ensure it is functioning correctly and that logs are being applied in a timely manner.
Example:
SELECT PROCESS, STATUS, THREAD#, SEQUENCE# FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';
Resolving Issues During Standby Database Duplication:
Address any issues that arise during the duplication or redo apply processes by reviewing the alert logs and using diagnostic queries.
Example:
SELECT MESSAGE FROM V$DIAG_ALERT_EXT WHERE ORIGINATING_TIMESTAMP > SYSDATE - 1;
Conclusion
Mastering Standby Database Duplication and RMAN Duplicate is essential for ensuring robust and efficient disaster recovery solutions in Oracle 19c. By following the steps outlined in this guide, you can create a reliable standby database setup. Understanding the intricacies of Database Duplication and Duplicate Command will enhance your ability to maintain data integrity and availability. This comprehensive intro to Database Duplication ensures that you can apply these concepts effectively, providing a solid foundation for managing Oracle databases.
See more on Oracle’s website!
Be Oracle DataGuard Certified Professional, this world is full of opportunities for qualified DBAs!
