Step-by-step guide to configure a physical standby database.
There are various steps in which you can configure physical standby database. We need to make several changes to the primary database before we can even setup the standby database. In this article, we will set up physical standby on Oracle Linux 7.6 server.
Physical Standby Configuration Overview
Primary details
SID: test
ORACLE_HOME: /u01/app/oracle/product/12.2.0.1
Host Name: prm.entopdba.com
Standby details:
SID: test_s
ORACLE_HOME: /u01/app/oracle/product/12.2.0.1
Host Name: stb.entopdba.com
Assumption: we assume that primary server has a database (SID=test) up and running. The standby database has Oracle 12cR2 installation done in the same oracle home location as primary.
Primary database changes
Primary must run in archive log mode. Check the archive log mode
SELECT log_mode FROM v$database;
LOG_MODE
------------
NOARCHIVELOG
If it is not running in archive log mode, then enable it
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> alter database open;
SQL> archive log list;
Enable force logging on primary: In oracle, users can restrict redo generation for SQL by using NOLOGGING clause. This NOLOGGING transaction will be a problem for physical standby. Hence, we force logging so even user uses NOLOGGING clause, every SQL will be logged on to redo
SQL> alter database force logging;
SQL> select name, force_logging from v$database;
Standby file management: We need to make sure whenever we add/drop datafile in primary database, those files are also added / dropped on standby
on Primary Server
==============
SQL> alter system set standby_file_management = 'AUTO';
Create standby log files: You must create standby log files on primary. These files are used by a standby database to store redo it receives from primary database. Our primary may become standby later and we would need them, so better to create it. First check the current log groups.
on Primary Server
==============
SQL> select GROUP#, THREAD#, bytes/1024/1024, MEMBERS, STATUS from v$log;
GROUP# THREAD# BYTES/1024/1024 MEMBERS STATUS
---------- ---------- --------------- ---------- ----------------
1 1 200 1 INACTIVE
2 1 200 1 CURRENT
3 1 200 1 INACTIVE
SQL> select member from v$logfile;
MEMBER
---------------------------------------------------
/u01/data/db_files/test/redo03.log
/u01/data/db_files/test/redo02.log
/u01/data/db_files/test/redo01.log
Add the standby logfiles, make sure group number should be from a different series like in this case we choose to start with 11 and above. This helps in easy differentiation. also in future if you want to add more log file on primary so you still have gap to add more from 3-11.
We start standby log group numbers higher (e.g., 11+) to avoid conflict with primary redo logs, make them easy to identify, and leave room to add more primary redo logs in the future
Make sure to keep the thread# and logfile size exactly same. Oracle also recommends to always create n+1 standby log files. Where n is the total number of log files. it is recommended by Oracle and most DBA's to configure one more log file compare to redo log and it is a formula that is followed by most of the DBA's.
EX: if redo log=3. -----> then SRL=4.
on Primary Server
==============
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 11 '/u01/data/db_files/test/redo11.log' SIZE 200M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 12 '/u01/data/db_files/test/redo12.log' SIZE 200M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 13 '/u01/data/db_files/test/redo13.log' SIZE 200M;
SQL>ALTER DATABASE ADD STANDBY LOGFILE GROUP 14 '/u01/data/db_files/test/redo14.log' SIZE 200M;
why You must create standby log files on primary?????
Standby redo logs on the primary allow seamless role transition and prevent data loss during failover or switchover.
The real reasons 👇
1️⃣ Role transitions happen fast (Primary ↔ Standby)
In Data Guard, roles can switch at any time:
Switchover (planned)
Failover (unplanned)
👉 When the primary becomes a standby, it must immediately receive redo.
If SRLs do not already exist on the primary:
Redo transport fails
Apply is delayed
Data loss risk increases
Bellow Diagram shows as soon as primary role change to standby then we must have configured SRL already on Primary side so it must immediately receive redo.
Check the standby log files via below query
SQL> SELECT GROUP#,THREAD#,SEQUENCE#,ARCHIVED,STATUS FROM V$STANDBY_LOG;
Create Password File for Standby=====>
this is needed for cloning purpose .Even if there is one password file in $ORACLE_HOME/dbs location ,Create a new one on standby SID
SQL>alter system set remote_login_passwordfile=exclusive scope=spfile;
SQL>exit
OS>cd $ORACLE_HOME/dbs
OS> orapwd file=orapwtest_s
OS>scp orapwdtest_s oracle@<standby_ip>:$ORACLE_HOME/dbs
Check DB Unique name parameter on primary:
Make sure your primary database has DB_UNIQUE_NAME parameter set for consistency. If it’s not set properly, use ALTER SYSTEM SET command
SQL> show parameter db_name
NAME TYPE VALUE
------------------------------------ ----------- -------------
db_name string test
SQL> show parameter db_unique_name
NAME TYPE VALUE
------------------------------------ ----------- -------------
db_unique_name string test
Enable flashback on primary:
Flashback database is highly recommended because in case of failover, you need not re-create primary database from scratch.
on Primary Server
==============
SQL> alter system set db_recovery_file_dest_size=45g;
SQL> alter database flashback on;
SQL> select flashback_on from v$database;
If flashback parameters are not set properly, use below commands
SQL> show parameter recovery;
SQL> alter system set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area';
SQL> alter system set db_recovery_file_dest_size=45g;
SQL> alter database flashback on;
Configure network. ==================>
Use below tns entries and put them under ORACLE user HOME/network/admin/tnsnames.ora. Change host as per your environment and execute on both primary and standby.
Notice the use of the SID, rather than the SERVICE_NAME in the entries. This is important as the broker will need to connect to the databases when they are down, so the services will not be present.
on Primary & Standby Server
==============
#vi $ORACLE_HOME/network/admin/tnsnames.ora
test =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = prm.entopdba.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = test)
)
)
test_s =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = stb.entopdba.com)(PORT = 1521))
)
(CONNECT_DATA =
(SID = test_s)
)
)
Configure listener on primary database. Since the broker will need to connect to the database when it’s down, we can’t rely on auto-registration with the listener, hence the explicit entry for the database.
on Primary Server
===============
#vi $ORACLE_HOME/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.111)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test)
(SID_NAME=test)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)
)
(SID_DESC =
(GLOBAL_DBNAME = test_DGMGRL)
(SID_NAME = test)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)
)
)
on Standby Server
================
#vi $ORACLE_HOME/network/admin/listener.ora
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.112)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = test_s)
(SID_NAME=test_s)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)
)
(SID_DESC =
(GLOBAL_DBNAME = test_s_DGMGRL)
(SID_NAME = test_s)
(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1)
)
)
Once the listener.ora changes are in place, restart the listener on both servers
lsnrctl stop
lsnrctl start
Configure redo transport
Configure redo transport from primary to standby: The below statement says that if the current database is in primary role, then transport logs to standby. We need to change service and db_unique_name for same parameter on standby server
On Primary Server
=================
SQL> alter system set log_archive_dest_2 = 'service=test_s async valid_for=(online_logfiles,primary_role) db_unique_name=test_s';
Set FAL_SERVER: Fetch Archive Log parameter tells primary as to where it will get archives from
On Primary Server
=================
SQL> alter system set fal_server = 'test_s';
Set dg_config parameter: This parameter defines which databases are in data guard configuration
On Primary Server
=================
SQL> alter system set log_archive_config = 'dg_config=(test,test_s)';
Build standby
=================================>>>
Create pfile on primary, open it and create the necessary directories on the standby server
On Primary Server
=================
SQL> create pfile ='/tmp/inittest_s.ora' from spfile;
exit
oracle# scp /tmp/inittest_s.ora oralce@stb:/tmp
on Standby Server
===================
$ cd /tmp
$ vi inittest.ora
- Change FAL_SERVER to the primary SID(test) :because if role change the standby database should know from where to fetch the archive log.
- In LOG_ARCHIVE_DEST_2 change the service and db_unique_name to the Primary SID( in this case test).
explanation: on primary side this parameter was pointing to Standby but now on standby it should
point to primary SID thats why we change to primary SID so the log should flow in revers
direction when role changes.
- Add parameter db_unique_name=‘test_s’
*.db_unique_name='test_s'
- Save files and close
- Create audit destination at os level using mkdir
On Standby Server
=================
Create directories as you find in the inittest.ora file
mkdir -p /u01/app/oracle/admin/test/adump
mkdir -p /u01/data/db_files/test
mkdir -p /u01/app/FRA/test
mkdir -p /u01/app/FRA
On standby server, create parameter file with just db_name parameter and start the instance in nomount mode
Create spfile on Standby :
On standby server
=================
#env | grep ORA make sure oracle home and sid is set to test_s
#vi $ORACLE_HOME/dbs/inittest.ora
add bellow
*.db_name='test'
$ export ORACLE_SID=test_s
$ sqlplus / as sysdba
SQL> create spfile from pfile='/tmp/inittest_s.ora';
SQL>exit
oracle# cd $ORACLE_HOME/dbs
oracle$ ls
$sqlplus / as sysdba
SQL> STARTUP NOMOUNT;
SQL> show parameter spfile;
SQL> exit; ----you must exit otherwise DUPLICATE will fail
in case of ORA-00485 edit /etc/fstab as root user if no skip this step
$vi /etc/fstab
edit bellow
===========================
tmpfs /dev/shm tmpfs. defaults,size=5G. 0 0
OS>mount -o remount /dev/shm
Duplicate primary database via RMAN: On primary, connect to RMAN, specifying a full connect string for both the TARGET and AUXILIARY instances. Do not attempt to use OS authentication else, the cloning will fail
To DORECOVERY option starts recovery by applying all available logs immediately after restore
On primary server
=================
rman target sys@test
RMAN>connect catalog rman_ct@rman_ct >> to conenct catalog
RMAN>connect auxiliary sys@test_s >>to connect standby db
SYS password: EnterCDB#123 >> same as Primary SYS
for cloning you have to give username ,database connection identifier
otherwise it will fail if you do RMAN TARGET /
RMAN> DUPLICATE TARGET DATABASE FOR STANDBY
FROM ACTIVE DATABASE
NOFILENAMECHECK;
Once cloning is done, you should see below at RMAN prompt
Finished Duplicate Db at 07-DEC-2015
Enable flashback on standby: As we know the importance of flashback in data guard, we must enable it on standby as well
On Standby Server
=================
SQL> alter database flashback on;
Verify standby configuration
Bounce database & start MRP (Apply Service): It's good to bounce standby, put it in mount mode and start MRP process
On Standby Server
=================
SQL> alter database recover managed standby database disconnect;
Once MRP is started, we must verify which archive log number MRP is applying on standby
On standby:
===========
select process, status, sequence# from v$managed_standby;
select sequence#, applied, first_time, next_time, name filename from v$archived_log order by sequence#;
Below queries will help you identify issues when your data guard setup is out of sync
On both primary & standby:
==========================
set lines 999;
select * from v$dataguard_status order by timestamp;
select dest_id, status, destination, error from v$archive_dest where dest_id<=2;
IF you see ORA-16058, do this on primary:
=========================================
SQL> alter system set log_archive_dest_state_2='DEFER'; -- disbale
SQL> alter system set log_archive_dest_state_2='ENABLE';
SQL> select dest_id, status, destination, error from v$archive_dest where dest_id<=2;
On primary:
===========
select sequence#, first_time, next_time, applied, archived from v$archived_log where name = 'test_s' order by first_time;
select STATUS, GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_ID = 2;
On Standby
==========
select sequence#,process,status from v$managed_standby;
this will check the MRP status whether it is running or not.
on primary
==========
do some log switch
SQL>alter system switch logfile;
/
/
On Standby
==========
select sequence#,process,status from v$managed_standby;
so here RFS continuously working to receive logfile and MRP is applying
make sure both archive log # is same on primary as well as standby side
select sequence#, first_time, next_time, applied, archived from v$archived_log order by sequence#;
SQL>archive log list;
Enable flashback on standby: As we know the importance of flashback in data guard, we must enable it on standby as well
On Standby Server
=================
SQL>alter database recover managed standby database cancel;
SQL> alter database flashback on;
SQL>alter database recover managed standby database disconnect;
Configure Archive deletion policy: We must set this policy in order to prevent accidental deletion of archive logs on primary database
On Primary:
===========
rman target /
configure archivelog deletion policy to applied on all standby;
it is because until unless the archive log are not applied on Standby database do not delete them from Primary database
this is helpful in case you face GAP that time they have to restore archive log using RMAN on Primary and copied to standby and then apply
you can configure more than one standby in real time as bellow here log_dest_2 send archive log to P.Stb
so you can setup up to 30 DR in real time thats why "applied to all standby" is important on bellow query.
