Configuring an Oracle Standby Database
My Environment for this practice:
Step 01 - Make sure the primary database is running in archive log mode.
[oracle@testserver-k ~]$ sqlplus / as sysdba
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 7
Next log sequence to archive 9
Current log sequence 9
# In this case the database is already running in archive log mode. But if it is not in archive log mode follow the below steps to alter it to archive log mode.
SQL> shu immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
Step 2 - Enable force logging
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
NO
SQL> alter database force logging;
Database altered.
SQL> select force_logging from v$database;
FORCE_LOGGING
---------------------------------------
YES
Step 03 - Take a full RMAN backup of the primary database (pfile, controlfile as standby, database and archivelogs)
[oracle@testserver-k u01]$ sqlplus / as sysdba
SQL> create pfile='/u01/initstandby.ora' from spfile;
File created.
[oracle@testserver-k ~]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Thu Feb 6 03:38:03 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: PRODTEST (DBID=2973952766)
RMAN> backup as compressed backupset database format '/u01/RMAN_BACKUPS/PROD_bkp/standby_db_%u';
RMAN> backup as compressed backupset archivelog all format '/u01/RMAN_BACKUPS/PROD_bkp/standby_archive_%u';
# Backup controlfile as standby format
RMAN> backup current controlfile for standby format '/u01/RMAN_BACKUPS/PROD_bkp/control_standby.ctl';
RMAN> exit
Recovery Manager complete.
#Get a copy of the password file as well
[oracle@testserver-k dbs]$ cd $ORACLE_HOME/dbs
[oracle@testserver-k dbs]$ cp orapwPRODTEST /u01/RMAN_BACKUPS/PROD_bkp/
Step 04 - Transfer the files to the standby database.
[oracle@testserver-k ~]$ cd /u01/RMAN_BACKUPS/PROD_bkp/
[oracle@testserver-k PROD_bkp]$ scp * oracle@10.14.2.251:/u01/STB_BKP
Step 05 - Change the parameters in the pfile to match the standby database and add the required parameters
[oracle@testserver-k-dr STB_BKP]$ vi initstandby.ora
TESTDB.__data_transfer_cache_size=0
TESTDB.__db_cache_size=3808428032
TESTDB.__inmemory_ext_roarea=0
TESTDB.__inmemory_ext_rwarea=0
TESTDB.__java_pool_size=67108864
TESTDB.__large_pool_size=16777216
TESTDB.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
TESTDB.__pga_aggregate_target=1660944384
TESTDB.__sga_target=4949278720
TESTDB.__shared_io_pool_size=134217728
TESTDB.__shared_pool_size=872415232
TESTDB.__streams_pool_size=33554432
TESTDB.__unified_pga_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/STBTEST/adump'
*.audit_trail='db'
*.compatible='19.0.0'
*.control_files='/u01/app/oracle/oradata/STBTEST/controlfile/control_standby.ctl','/u01/app/oracle/fast_recovery_area/STBTEST/controlfile/control_standby.ctl'
*.db_block_size=8192
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_name='PRODTEST'
*.db_unique_name='STBTEST'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=8256m
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=PRODTESTXDB)'
*.local_listener='LISTENER_PRODTEST'
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=1570m
*.processes=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=4710m
*.undo_tablespace='UNDOTBS1'
*.db_file_name_convert='PRODTEST','STBTEST'
*.log_file_name_convert='PRODTEST','STBTEST'
*.fal_server='PRODTEST'
*.log_archive_config='dg_config=(STBTEST,PRODTEST)'
*.log_archive_dest_state_2='ENABLE'
#If restoring datafiles from file system to ASM the following parameters should be changed
*.db_create_file_dest='/u01/app/oracle/oradata'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
TO
*.db_create_file_dest='+DATA'
*.db_recovery_file_dest='+RECO'
Step 06 - Add password file to the relevant location
[oracle@testserver-k-dr STB_BKP]$ cd /u01/STB_BKP/
[oracle@testserver-k-dr STB_BKP]$ cp orapwPRODTEST /u01/app/oracle/product/19.0.0/dbhome_1/dbs/
[oracle@testserver-k-dr STB_BKP]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/dbs/
Rename the password file:
[oracle@testserver-k-dr dbs]$ cp orapwPRODTEST orapwSTBTEST
Create the following directories accordingly:
#db_create_file_dest
[oracle@testserver-k-dr dbs]$ mkdir -p /u01/app/oracle/oradata
#audit_file_dest
[oracle@testserver-k-dr dbs]$ mkdir -p /u01/app/oracle/admin/STBTEST/adump
#control file locations
[oracle@testserver-k-dr dbs]$ mkdir -p /u01/app/oracle/oradata/STBTEST/controlfile
[oracle@testserver-k-dr dbs]$ mkdir -p /u01/app/oracle/fast_recovery_area/STBTEST/controlfile
Step 07 - Create the standby database by restoring the backup you took from the primary database.
For a more in depth guide on RMAN backup and restoration, refer the below guide:
# Add the SID to the oratab directory
[oracle@testserver-k-dr dbs]$ vi /etc/oratab
STBTEST:/u01/app/oracle/product/19.0.0/dbhome_1:N
# Set the environment to the SID entry added to the oratab
[oracle@testserver-k-dr dbs]$ . oraenv
ORACLE_SID = [oracle] ? STBTEST
The Oracle base remains unchanged with value /u01/app/oracle
[oracle@testserver-k-dr dbs]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Thu Feb 6 06:33:12 2025
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile='/u01/STB_BKP/initstandby.ora';
ORACLE instance started.
Total System Global Area 4949276568 bytes
Fixed Size 8906648 bytes
Variable Size 889192448 bytes
Database Buffers 4043309056 bytes
Redo Buffers 7868416 bytes
SQL> create spfile from pfile='/u01/STB_BKP/initstandby.ora';
File created.
SQL> shu immediate
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 4949276568 bytes
Fixed Size 8906648 bytes
Variable Size 889192448 bytes
Database Buffers 4043309056 bytes
Redo Buffers 7868416 bytes
[oracle@testserver-k-dr dbs]$ rman target /
RMAN> restore controlfile from '/u01/STB_BKP/control_standby.ctl';
RMAN> alter database mount;
RMAN> catalog start with '/u01/STB_BKP';
RMAN> restore database;
RMAN> recover database;
Step 08 - Add TNS entries
SQL> select value from v$parameter where name='service_names';
VALUE
---------------------------------------------------------------------
STBTEST
[oracle@testserver-k-dr dbs]$ lsnrctl status
# First we will add TNS entries to the standby side. Add the TNS entries of the production side
[oracle@testserver-k-dr dbs]$ cd /u01/app/oracle/product/19.0.0/dbhome_1/network/admin/
[oracle@testserver-k-dr admin]$ vi tnsnames.ora
PRODTEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.14.1.162)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = PRODTEST)
)
)
STBTEST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 10.14.2.251)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = STBTEST)
)
)
# Test if tnsping works
[oracle@testserver-k-dr admin]$ tnsping PRODTEST
TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 06-FEB-2025 06:52:20
Copyright (c) 1997, 2019, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.14.1.162)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = PRODTEST)))
OK (0 msec)
# Do the same for the production server as well. Add the correct tnsnames entries to the tnsnames.ora file on the production side.
Step 09 - Create the standby redo logs both primary and standby side
# First, we need to check the count of online redo log groups and logs
SQL> select GROUP#,STATUS,MEMBER,TYPE from v$logfile where type='ONLINE';
# run below query to see the redo log size
SQL> select bytes/1024/1024 from v$log;
# Now we can create redo logs both primary and standby side In primary side
Steps to do before this in primary side:
[oracle@testserver-k-dr admin]$ mkdir -p /u01/app/oracle/oradata/PRODTEST/onlinelog
[oracle@testserver-k-dr admin]$ mkdir -p /u01/app/oracle/fast_recovery_area/PRODTEST/onlinelog
In Primary Side:
alter database add standby logfile thread 1 group 4 ('/u01/app/oracle/oradata/PRODTEST/onlinelog/standby_redo04.log','/u01/app/oracle/fast_recovery_area/PRODTEST/onlinelog/standby_redo04.log')size 200m;
alter database add standby logfile thread 1 group 5
('/u01/app/oracle/oradata/PRODTEST/onlinelog/standby_redo05.log','/u01/app/oracle/fast_recovery_area/PRODTEST/onlinelog/standby_redo05.log')size 200m;
alter database add standby logfile thread 1 group 6
('/u01/app/oracle/oradata/PRODTEST/onlinelog/standby_redo06.log','/u01/app/oracle/fast_recovery_area/PRODTEST/onlinelog/standby_redo06.log')size 200m;
alter database add standby logfile thread 1 group 7
('/u01/app/oracle/oradata/PRODTEST/onlinelog/standby_redo07.log','/u01/app/oracle/fast_recovery_area/PRODTEST/onlinelog/standby_redo07.log')size 200m;
Steps to do before this in standby side:
[oracle@testserver-k-dr admin]$ mkdir -p /u01/app/oracle/oradata/STBTEST/onlinelog
[oracle@testserver-k-dr admin]$ mkdir -p /u01/app/oracle/fast_recovery_area/STBTEST/onlinelog
In Standby Side:
alter database add standby logfile thread 1 group 4
('/u01/app/oracle/oradata/STBTEST/onlinelog/standby_redo04.log','/u01/app/oracle/fast_recovery_area/STBTEST/onlinelog/standby_redo04.log')size 200m;
alter database add standby logfile thread 1 group 5
('/u01/app/oracle/oradata/STBTEST/onlinelog/standby_redo05.log','/u01/app/oracle/fast_recovery_area/STBTEST/onlinelog/standby_redo05.log')size 200m;
alter database add standby logfile thread 1 group 6
('/u01/app/oracle/oradata/STBTEST/onlinelog/standby_redo06.log','/u01/app/oracle/fast_recovery_area/STBTEST/onlinelog/standby_redo06.log')size 200m;
alter database add standby logfile thread 1 group 7
('/u01/app/oracle/oradata/STBTEST/onlinelog/standby_redo07.log','/u01/app/oracle/fast_recovery_area/STBTEST/onlinelog/standby_redo07.log')size 200m;
# Run below query to see the created standby logs and groups
SQL> select member from v$logfile;
SQL> select GROUP#,STATUS,MEMBER,TYPE from v$logfile where type='STANDBY';
Primary Database Configuration:
First we need to disable archive dest until we finish the configuration
SQL> alter system set log_archive_dest_state_2=defer;
SQL> select FORCE_LOGGING from v$database;
(If output is no alter it to yes)
SQL> alter database force logging;
# Run the following commands
ALTER SYSTEM SET log_archive_config='dg_config=(PRODTEST,STBTEST)';
ALTER SYSTEM SET log_archive_dest_2='service=STBTEST noaffirm async valid_for=(online_logfiles,primary_role) db_unique_name=STBTEST';
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
ALTER SYSTEM SET FAL_SERVER=STBTEST;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Step 10 - Get the standby database to mount mode.
SQL> shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.
Total System Global Area 4949276568 bytes
Fixed Size 8906648 bytes
Variable Size 889192448 bytes
Database Buffers 4043309056 bytes
Redo Buffers 7868416 bytes
Database mounted.
Step 11 - Get the alert log of both databases side by side and execute the following query in the primary database (Keep checking the alert logs for the below steps)
SQL> alter system set log_archive_dest_state_2=enable;
Step 12 - Run the following commands in the standby database
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(PRODTEST,STBTEST)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=PRODTEST NOAFFIRM ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRODTEST';
ALTER SYSTEM SET LOG_ARCHIVE_FORMAT='%t_%s_%r.arc' SCOPE=SPFILE;
ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=30;
ALTER SYSTEM SET REMOTE_LOGIN_PASSWORDFILE=EXCLUSIVE SCOPE=SPFILE;
ALTER SYSTEM SET FAL_SERVER=PRODTEST;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
Step 13 - .Run below command in standby side to start the media recovery
SQL> alter database recover managed standby database disconnect from session;
Step 14 - Verification
# We can check whether data guard is successfully configured by using below
commands we can use below query in the production database to switch the redo
log file to get an archive log to be transferred to the standby database
SQL> alter system switch logfile;
(Now if we check the standby database alert log, we can check whether an
archive log is being transferred or not.)
Run below command to see the gap between databases
In primary side:
SQL> select max(sequence#),THREAD# from gV$ARCHIVED_LOG group by thread#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
8 1
In standby side:
SQL> select max(sequence#),THREAD# from gV$ARCHIVED_LOG where applied='YES' group by thread#;
MAX(SEQUENCE#) THREAD#
-------------- ----------
8 1
After that type below queries to check the parameters:
SQL> show parameter fal;
NAME TYPE VALUE
-------------------------- ----------- ---------------
fal_client string
fal_server string PRODTEST
SQL> show parameter log_archive;
SQL> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- --------------------
cdb_cluster_name string
cell_offloadgroup_name string
db_file_name_convert string PRODTEST, STBTEST
db_name string PRODTEST
db_unique_name string STBTEST
global_names boolean FALSE
instance_name string STBTEST
lock_name_space string
log_file_name_convert string PRODTEST, STBTEST
pdb_file_name_convert string
processor_group_name string
NAME TYPE VALUE
------------------------------------ ----------- --------------------
service_names string STBTEST
SQL> show parameter standby;
NAME TYPE VALUE
------------------------------------ ----------- --------------------
enabled_PDBs_on_standby string *
standby_db_preserve_states string NONE
standby_file_management string AUTO
standby_pdb_source_file_dblink string
standby_pdb_source_file_directory string
(Output should be “auto”)
Checking Log Gaps
From DR Side:
select max(sequence#),THREAD# from gV$ARCHIVED_LOG where applied='YES' group by thread#;
From Production Side:
select max(sequence#),THREAD# from gV$ARCHIVED_LOG group by thread#;
Comments
Post a Comment