Benjamin Kurschies – just another oracle nerd

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

well, oracle dataguard in general is rock solid (if configured well – but you will find a lot of guides in the internet how to do so)… but as always, sometimes something is not working as it should and the standby is out of sync afterwards (i.e. a network outage between the servers, and to avoid archiver stuck on the primary site you are forcing a delete of the archive logs).

OK, the network is working again, so how to solve the gap? If only a few archivelogs are missing, my first choice would be a restore of the archivelogs from backup. But what if thousends of archivelogs are missing? restore them all from tape? Based on my experiences, this could be a very time consuming procedure, in worst case the tape drive is not fast enough to catch up with the primary. In this case you may consider rebuilding the standby (rman> duplicate database for standby from active database;) … have you ever tried to do this with a 50 TB database? The solution might be an incremental recovery! If you consider some peculiarities, it’s not so complicated…

1. stop redo apply (it’s not working anyway)

you cannot apply an incremental backup while the apply is running, so let’s stop it:

dgmgrl /
edit database 'ORCLRB' set state='APPLY-OFF';

2. evaluate what’s missing

Then we have to figure out where our incremental backup shoud start… the easy way is to query the v$database view to get the current scn – but this only returns the information stored in the controlfile, sometimes the headers of the datafiles do have a lower scn and rman refuses to start the recovery. so the sql must be like this:

# execute on standby!
sqlplus / as sysdba
set linesize 200
SELECT 'BACKUP INCREMENTAL FROM SCN '||min(SCN)||' DATABASE FORMAT ''/tmp/recover_standby_%U'';' FROM (
   select current_scn SCN from V$DATABASE
   union all
   select min(checkpoint_change#) from v$datafile_header 
   where file# not in (select file# from v$datafile where enabled = 'READ ONLY')
);

3. start backup

well, just execute the output of the above sql on the primary database… and when it is finished, you also need a fresh standby controlfile:

# execute on primary!
sqlplus / as sysdba
ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/tmp/stby.ctl';

4. start recover

First you have to make the backup availeble on the standby site… either mount as NFS device or copy the backup files via scp to the standby site. Then catalog the files and start recovery…

# execute on standby!
rman target /
catalog start with '/tmp/recover_standby';
recover database noredo;

5. restore controlfile

now we have to “update” the controlfile… since this is not possible, we are doing a full restore.

# execute on standby!
rman target /
shutdown immediate;
startup nomount;
restore controlfile from '/tmp/stby.ctl';
alter database mount;
exit;

if the structure of the database is not exactly the same as on the primary site, you have to populate the the new file and folder names:

rman target /
catalog start with '+U01/ORCLRB/DATAFILE';
switch database to copy;

5. enable redo apply

almost finished, now we only have to enable the dataguard process again…

dgmgrl /
edit database 'ORCLRB' set state='APPLY-ON';

done… just check the alert.log if the standby is receiving and applying the last few archivelogs.