Oracle 19c: Creating a Physical Standby Database by Using SQL and RMAN Commands

Physical Standby is essential for ensuring disaster recovery and high availability in an Oracle 19c environment. This blog will guide you through creating a Physical Standby database using SQL and RMAN commands, incorporating best practices and detailed steps. Let’s dive into the essentials of Standby Setup and understand their practical applications in real-world scenarios. This will be a helpful intro to Database Standby.

Physical Standby

Creating a Physical Standby database involves setting up a replica of your primary database for disaster recovery. This setup ensures that the standby database is an exact copy of the primary database and is kept in sync with it.

Creating the Database in Silent Mode

First, create the database in Silent Mode. Ensure the password file and SPFILE are correctly configured in ASM.

SPFILE Configuration:

srvctl stop database -d PRIMARYDB
SQL> create spfile='+DATA/spfilePRIMARYDB.ora' from pfile='/location/pfile.ora';
srvctl modify database -d PRIMARYDB -spfile +DATA/spfilePRIMARYDB.ora
srvctl start database -d PRIMARYDB

Password File Configuration:

srvctl modify database -d PRIMARYDB -pwfile +DATA/orapwPRIMARYDB
asmcmd pwcopy /nfs/scripts/server/orapwPRIMARYDB +DATA/orapwPRIMARYDB

SQL and RMAN

SQL and RMAN commands are critical for managing Physical Standby databases. These tools allow you to perform backups, restore operations, and manage the synchronization between the primary and standby databases.

Adding Listener Entries:

Add the following entries in both primary and standby listeners:

DATAGUARD =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary-hostname)(PORT = 1523)(SEND_SDU = 10485760)(RECV_SDU = 10485760))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )
SID_LIST_DATAGUARD =
  (SID_LIST =
    (SID_DESC =
      (GLOBAL_DBNAME = dataguard_service)
      (SID_NAME = dataguard_sid)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = dataguard_dgmgrl)
      (SID_NAME = dataguard_sid)
      (ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1)
    )
  )

Adding TNS Entries:

Add the following entries in both primary and standby tnsnames.ora:

PRIMARY_TNS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary-host)(PORT = 1523)(SEND_BUF_SIZE=10485760)(RECV_BUF_SIZE=10485760))
    )
    (SDU=65535)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary_service)
    )
  )

STANDBY_TNS =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = standby-host)(PORT = 1523)(SEND_BUF_SIZE=10485760)(RECV_BUF_SIZE=10485760))
    )
    (SDU=65535)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby_service)
    )
  )

📢 You might also like: Oracle 19c: Configure the Primary Database and Oracle Net Services to Support the Creation of the Physical Standby Database and Role Transition (Category: DataGuard)

Preparing the Database

Prepare the primary database:

ALTER SYSTEM SET DB_UNIQUE_NAME='primary_unique' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary_unique, standby_unique)' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=use_db_recovery_file_dest VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=primary_unique' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby_service LGWR ASYNC NOAFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) NET_TIMEOUT=30 DB_UNIQUE_NAME=standby_unique' SCOPE=SPFILE;
ALTER SYSTEM SET FAL_SERVER=standby_tns SCOPE=SPFILE;
ALTER SYSTEM SET SERVICE_NAMES='primary_service, dgp_service' SCOPE=SPFILE;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SCOPE=SPFILE;

Start the database in mount mode, enable archive logging, and open the database:

STARTUP MOUNT;
ALTER DATABASE ARCHIVELOG;
ALTER DATABASE OPEN;
ALTER DATABASE FORCE LOGGING;

Create standby redo logs (one more group than normal redo log groups, so if the database has 3 redolog groups, so you should add 4 standby redolog groups):

ALTER DATABASE ADD STANDBY LOGFILE '/u02/logfile/standby_redo01.log' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE '/u02/logfile/standby_redo02.log' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE '/u02/logfile/standby_redo03.log' SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE '/u02/logfile/standby_redo04.log' SIZE 50M;

Verify the archive destinations:

SELECT DEST_ID, STATUS, VALID_TYPE, VALID_ROLE, VALID_NOW, ERROR FROM V$ARCHIVE_DEST;

Advanced Configuration and Management

To make the most out of your Physical Standby setup, it’s crucial to understand advanced configuration and management practices. Consequently, these practices ensure high availability, reliability, and security of your database connections.

Creating PFILE from SPFILE and Copying to Standby:

CREATE PFILE='/home/oracle/pfile_primary.init' FROM SPFILE;
scp /home/oracle/pfile_primary.init oracle@standby-host:/u03

Edit the PFILE on the standby database:

*.db_unique_name='standby_unique'
*.log_archive_config='DG_CONFIG=(primary_unique, standby_unique)'
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=standby_unique'
*.log_archive_dest_2='SERVICE=dgp_service LGWR NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) NET_TIMEOUT=30 DB_UNIQUE_NAME=primary_unique'
*.fal_server='PRIMARY_TNS'
*.service_names='standby_service, dgs_service'
*.STANDBY_FILE_MANAGEMENT=AUTO

Starting the Standby Instance and Creating SPFILE:

. oraenv
ORACLE_SID = [standby] ? standby_instance
ORACLE_HOME = [/home/oracle] ? /u01/app/oracle/product/12.1.0/dbhome_1
SQL> CREATE SPFILE FROM PFILE='/u10/backup/rman/init_standby.ora';

Running RMAN Commands:

Connect to RMAN from the primary and standby (auxiliary) to run the script and create the standby database:

rman target sys/password@PRIMARY_TNS auxiliary sys/password@STANDBY_TNS

RUN {
  ALLOCATE CHANNEL primary1 TYPE DISK;
  ALLOCATE AUXILIARY CHANNEL stdy TYPE DISK;
  DUPLICATE TARGET DATABASE FOR STANDBY FROM ACTIVE DATABASE NOFILENAMECHECK;
}

Troubleshooting and Monitoring Physical Standby

Verify Standby Mode:

SELECT NAME, DB_UNIQUE_NAME, DATABASE_ROLE FROM V$DATABASE;

Activate Redo Apply on Standby Database:

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;  -- Real-time
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT; -- Normal mode
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;  -- To cancel
SELECT PROCESS, PID, STATUS FROM V$MANAGED_STANDBY WHERE PROCESS LIKE 'MRP%';

Verify Archive Destinations:

SELECT DB.NAME, LG.THREAD#, LG.DEST_ID, LG.SEQUENCE#, LG.FIRST_TIME, LG.NEXT_TIME
FROM V$ARCHIVED_LOG LG, V$DATABASE DB
WHERE LG.RESETLOGS_CHANGE# = (SELECT RESETLOGS_CHANGE# FROM V$DATABASE)
ORDER BY LG.THREAD#, LG.SEQUENCE#, LG.DEST_ID;

Troubleshoot Archive Issues:

SET LINE 200
COLUMN Archive_dest FORMAT A30
COLUMN Error FORMAT A20
SELECT DEST_ID "ID", STATUS "DB_status", DESTINATION "Archive_dest", ERROR "Error" FROM V$ARCHIVE_DEST;
SELECT INST_ID, PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM GV$MANAGED_STANDBY WHERE PROCESS IN ('RFS', 'LNS', 'MRP0');

Check Data Guard Stats:

SELECT NAME, VALUE, DATUM_TIME, TIME_COMPUTED FROM V$DATAGUARD_STATS WHERE NAME LIKE 'apply lag';

Physical Standby – Data Guard Broker (Optional)

Enable Data Guard Broker:

Set the DG_BROKER_START parameter to TRUE on both primary and standby databases:

ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH SID='*';

Configure Data Guard Broker:

dgmgrl /
CREATE CONFIGURATION 'DGConfig' AS PRIMARY DATABASE IS 'primary_unique' CONNECT IDENTIFIER IS PRIMARY_TNS;
ADD DATABASE 'standby_unique' AS CONNECT IDENTIFIER IS STANDBY_TNS MAINTAINED AS PHYSICAL;
ADD FAR_SYNC far_sync AS CONNECT IDENTIFIER IS FAR_SYNC;  -- If using FarSync
ENABLE CONFIGURATION;

Show Configuration:

SHOW CONFIGURATION;

Conclusion

Mastering Physical Standby and Standby DB setup is essential for ensuring robust and efficient database connectivity. By understanding the fundamental components and their configurations, as well as advanced management and troubleshooting techniques, you can optimize your Oracle database environment effectively. Whether you are new to Physical Standby or looking to deepen your knowledge, this guide provides a comprehensive starting point. Remember, consistent practice and staying updated with Oracle’s latest features will further enhance your expertise in Standby Setup. This comprehensive intro to Database Standby ensures that you can apply these concepts effectively.

See more on Oracle’s website!

Be Oracle DataGuard Certified Professional, this world is full of opportunities for qualified DBAs!