Oracle Dataguard in 5 Minutes

Posted on 2018-09-06 by Benjamin

ok, although I wrote that there are plenty of websites and blogs describing how to create a dataguard standby, I’m doing one on my own… specially because my colleague asked me to write them a manual, so I decided to do it here.

In my example I’m using two Servers with Oracle 12.2 clusterware (Oracle restart) including an ASM instance and a 12.2 db home. On the first server – lets call it the primary node – I’ve also installed a non-CDB Database with the database create assistant (dbca). The name of the database is ORCL, the db_unique_name is ORCLRA (the db_unique_name is quite important for dataguard to distinguish the different sites)

  • Prepare the source

there are some mandatory things that must be checked or adjusted before we can start to create the standby… the database must be in archivelog mode (dataguard is a log shipping technology, so this is obvious) and we must enable force logging, so we can transport also the changes executed with “nologging”

SQL> shutdown immediate;
SQL> startup mount;
SQL> alter database archivelog;
SQL> alter database force logging;
SQL> alter database open;
  • add standby redo logs

for those who are asking what the hell standby redo logs are – here a short explanation: the primary database is transferring the redo information to the standby via sql.net… this means there must be an oracle instance receiving this information and store it somewhere. And because oracle wants to separate standby and redo information, there is a new log type called standby log. The easiest way is to create it on the primary site – when the primary becomes a standby, the standby logs are already there… and if the logs are present before duplicating the database, they will be created automatically during the copy 😉 So we are creating one more standby logs than we have redo logs – this is because we have one extra source that could copy data from the primary: there can be a stream from each redo log group and one directly from the log buffer. For further information you may want to watch this site…

SQL> select count(*) from v$log;
  COUNT(*)
----------
         3

SQL> select distinct bytes from v$log;
     BYTES
----------
 209715200

SQL> alter database add standby logfile group 11 size 209715200;
SQL> alter database add standby logfile group 12 size 209715200;
SQL> alter database add standby logfile group 13 size 209715200;
SQL> alter database add standby logfile group 14 size 209715200;
  • enable standby_file_management

often forgotten, but in my opinion an important parameter. The default of this parameter is “manual”, which means when adding a datafile on the primary site, you have to do the same on the standby site. If you forget to do so, the replication will stop… and we are humans and humans do forget things sometimes. So just set the parameter to “auto” and oracle will take care of it.

SQL> alter system set STANDBY_FILE_MANAGEMENT=AUTO;
  • modify listener.ora

OK, now we need to modify the listener.ora file – oracle dataguard need access to the database via sql.net under any circumstances, even when the database is down. To solve this problem, we have to add a static entry to the listener.ora…

vi $GRID_HOME/network/admin/listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = primary)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
    )
  )

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET        # line added by Agent

# add static listener entry starts here:
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = ORCL)
      (GLOBAL_DBNAME=ORCLRA_DGMGRL)
      (ORACLE_HOME = /u00/app/oracle/product/12.2.0/db_1)
    )
  )

the same must be done on the standby site, too – only change the “GLOBAL_DBNAME” to a proper value – the “_DGMGRL” extension is mandatory for oracle dataguard… regarding the db_unique_name: I usually use the db_name with the suffix RA (for “recovery site A”), RB, RC, etc… and finaly restart or reload the listener to enable the new configuration.

  • init and password file

now we are doing the last few steps of preparation… to start the standby database, we need an pfile… so we a just creating one from the primary:

SQL> create pfile='/tmp/initORCL.ora' from spfile;

to be able to logon as sys via sql.net, we need the password file on the standby site… so we just copy the file together with the pfile to the other server:

BASH> scp /tmp/init${ORACLE_SID}.ora standby:/${ORACLE_HOME}/dbs/
BASH> scp ${ORACLE_HOME}/dbs/orapw${ORACLE_SID} standby:/${ORACLE_HOME}/dbs/

And now just edit the pfile and replace all values containing the db_unique_name with the now value.

# do on standby site!
BASH> sed -i 's/ORCLRA/ORCLRB/g' ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora

to startup the instance, you have to create the audit file destination manually

BASH> grep audit_file_dest ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
BASH> mkdir -p /u00/app/oracle/admin/ORCLRB/adump
  • create spfile

Oracle dataguard requires an spfile, so we have to create it…

BASH> echo "ORCL:/u00/app/oracle/product/12.2.0/db_1:N" >> /etc/oratab
BASH> . oraenv <<< ORCL
SQL> startup nomount;
SQL> create spfile='+DATA/ORCLRB/spfileORCL.ora' from pfile;
SQL> shutdown;
BASH> mv ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora.bak
BASH> echo "spfile='+DATA/ORCLRB/spfileORCL.ora'&amp;amp;quot; ${ORACLE_HOME}/dbs/init${ORACLE_SID}.ora
SQL> startup nomount;
SQL> show parameter spfile
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------------------------------------------
spfile                               string      +DATA/ORCLRB/spfileorcl.ora
  • final checks before copy

ok, now we have got a primary database in archivelog mode with force logging enabled and a standby instance in nomount state with the same password file, using an spfile. Furthermore, both instances are accessable via sql.net… so lets check if it’s working!
the easiest way is just to logon to the database via sqlplus:

sqlplus sys/oracle@primary:1521/orclra_dgmgrl as sysdba
sqlplus sys/oracle@standby:1521/orclrb_dgmgrl as sysdba

if both are working, we can continue, otherwise check the password file and listener entries again. Also checking the sqlnet.ora if ezconnect is allowed could be a good idea.

  • duplicate

the next step is the easiest one, although it might take a while, depending on the size of your database… we do an rman duplicate:

rman target sys/oracle@primary:1521/orclra_dgmgrl auxiliary sys/oracle@standby:1521/orclrb_dgmgrl
RMAN> duplicate target database for standby from active database;

when done you should also add the database to the clusterware, so the database will be started automatically after a reboot etc

BASH> srvctl add database -db ORCLRB -oraclehome /u00/app/oracle/product/12.2.0/db_1 -role physical_standby -instance ORCL -startoption MOUNT
  • enable log shipping

ok, and now the last step: configure the dataguard broker. First we have to enable the broker on both sites…

SQL> alter system set dg_broker_start=true;

and now (maybe wait a few seconds until the broker process is really up) we have to configure it… if you once understand the syntax, it’s really a no brainer.

BASH> dgmgrl /
DGMGRL> create configuration 'DG' as primary database is 'ORCLRA' connect identifier is '192.168.56.21:1521/ORCLRA_DGMGRL';
DGMGRL> add database 'ORCLRB' as connect identifier is '192.168.56.22:1521/ORCLRB_DGMGRL';
DGMGRL> enable configuration;

thats it… you can check your success via the broker (check the lines “Transport LAG” and “Apply Lag”) or by checking the alert.log of the standby database (just check if the media recovery process is waiting for an archivelog in transit)… suspicious people may do both 😉

DGMGRL> show database 'ORCLRB'
Database - ORCLRB

  Role:               PHYSICAL STANDBY
  Intended State:     APPLY-ON
  Transport Lag:      0 seconds (computed 0 seconds ago)
  Apply Lag:          0 seconds (computed 0 seconds ago)
  Average Apply Rate: 4.00 KByte/s
  Real Time Query:    OFF
  Instance(s):
    ORCL

BASH> tail /u00/app/oracle/diag/rdbms/orclrb/ORCL/trace/alert_ORCL.log
Media Recovery Waiting for thread 1 sequence 15 (in transit)
2018-09-06T12:59:27.551097+02:00
Recovery of Online Redo Log: Thread 1 Group 12 Seq 15 Reading mem 0
  Mem# 0: +DATA/ORCLRB/ONLINELOG/group_12.274.986057281
  Mem# 1: +DATA/ORCLRB/ONLINELOG/group_12.275.986057283

just tell me if I have missed something…

so far
Benjamin

This entry was posted in Uncategorized and tagged , . Bookmark the permalink.

Leave a Reply