Oracle Dataguard in 5 Minutes

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;
    
  • 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
    
    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 hostname (if not already there) and 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, 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
    
  • 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'" > ${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
    
  • enable log shipping
  • ok, and now the last step: configure the dataguard broker. First we have to enable the broker on both sites…

    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. Bookmark the permalink.

    Leave a Reply