Create an Oracle DataGuard Physical Standby

June 17, 2020 12-minute read

Oracle • DataGuard • Standby • Multitenant

Introduction

This time we are going to build a DataGuard environment by generating a Physical Standby for our Oracle 19c multitenant installation.

Prerequisites

We have a Primary Oracle 19c multitenant instance prmydb with one pluggable database taller running in the Virtual Machine Instance patodgprmy on Google Cloud Platform.

For database creation steps check Create Oracle Multitenant Database in Silent Mode.

Also we need another Virtual Machine Instance patodgstby with the same operating system, and same oracle software version installed that will be our Physical Standby server.

Prepare the Primary Database

We need to do some preparation of the database like archive mode, force logging and standby redo, before initiating DataGuard configuration.

Validate Archive Mode

[oracle@patodgprmy prmydb]$ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Wed Jun 17 18:20:33 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.


Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     2
Next log sequence to archive   4
Current log sequence           4

Enable Logging

SQL> ALTER DATABASE FORCE LOGGING;

Database altered.

SQL> SELECT force_logging FROM v$database;

FORCE_LOGGING
---------------------------------------
YES

Configure Redo for Future Switchover

SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 ('/oradata/cdbs/PRMYDB/stbyredo1.rdo') SIZE 64M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 ('/oradata/cdbs/PRMYDB/stbyredo2.rdo') SIZE 64M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 ('/oradata/cdbs/PRMYDB/stbyredo3.rdo') SIZE 64M;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE thread 1 ('/oradata/cdbs/PRMYDB/stbyredo4.rdo') SIZE 64M;

Database altered.

SQL> select group#, type, member from v$logfile order by 1;

    GROUP# TYPE    MEMBER
---------- ------- --------------------------------------------------
         1 ONLINE  /oradata/cdbs/PRMYDB/redo01.log
         2 ONLINE  /oradata/cdbs/PRMYDB/redo02.log
         3 ONLINE  /oradata/cdbs/PRMYDB/redo03.log
         4 STANDBY /oradata/cdbs/PRMYDB/stbyredo1.rdo
         5 STANDBY /oradata/cdbs/PRMYDB/stbyredo2.rdo
         6 STANDBY /oradata/cdbs/PRMYDB/stbyredo3.rdo
         7 STANDBY /oradata/cdbs/PRMYDB/stbyredo4.rdo

7 rows selected.

Configure Primary DataGuard

Set Primary Database Initialization Parameters

Define the DataGuard archiving configuration with the unique database name of the members of the replication:

alter system set LOG_ARCHIVE_CONFIG='DG_CONFIG=(prmydb,stbydb)' scope=both;

System altered.

Setup the destination for the archive logs, to the Primary and to the Standby:

alter system set LOG_ARCHIVE_DEST_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prmydb' scope=both;

System altered.

alter system set LOG_ARCHIVE_DEST_2='SERVICE=stbydb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stbydb' scope=both;

System altered.

Configure our Standby instance as the failover server:

alter system set FAL_SERVER=stbydb scope=both;

System altered.

Configure automatic datafile management for creation/modification and also define naming conversion for archivelogs and datafiles according the instance directory name:

alter system set STANDBY_FILE_MANAGEMENT=AUTO scope=both;

System altered.

SQL> alter system set db_file_name_convert='/STBYDB/','/PRMYDB/' scope=spfile;

System altered.

SQL> alter system set log_file_name_convert='/STBYDB/','/PRMYDB/' scope=spfile;

System altered.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Prepare Data and Files for the Standby

We need to generate a backup and some configuration files from the Primary instance.

Generate a Backup

This time we are going to do an offline backup and restore, following the procedure described in the document Duplicate Oracle Database from RMAN Offline Backup but you can also try other methods like RMAN duplicate.

So we mount and backup the database:

[oracle@patoracle ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jun 17 18:32:14 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

RMAN> backup database ;

Starting backup at 17-JUN-20
using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/oradata/cdbs/PRMYDB/system01.dbf
input datafile file number=00003 name=/oradata/cdbs/PRMYDB/sysaux01.dbf
input datafile file number=00014 name=/oradata/cdbs/PRMYDB/undotbsp.dbf
input datafile file number=00007 name=/oradata/cdbs/PRMYDB/users01.dbf
channel ORA_DISK_1: starting piece 1 at 17-JUN-20
channel ORA_DISK_1: finished piece 1 at 17-JUN-20
piece handle=/rman/respaldo/PRMYDB_20200617_1av30dhp.bkp tag=TAG20200617T183641 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00006 name=/oradata/cdbs/PRMYDB/pdbseed/sysaux01.dbf
input datafile file number=00005 name=/oradata/cdbs/PRMYDB/pdbseed/system01.dbf
input datafile file number=00008 name=/oradata/cdbs/PRMYDB/pdbseed/undotbs01.dbf
channel ORA_DISK_1: starting piece 1 at 17-JUN-20
channel ORA_DISK_1: finished piece 1 at 17-JUN-20
piece handle=/rman/respaldo/PRMYDB_20200617_1bv30dis.bkp tag=TAG20200617T183641 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting compressed full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00010 name=/oradata/pdbs/taller/sysaux01.dbf
input datafile file number=00009 name=/oradata/pdbs/taller/system01.dbf
input datafile file number=00012 name=/oradata/pdbs/taller/datos.dbf
channel ORA_DISK_1: starting piece 1 at 17-JUN-20
channel ORA_DISK_1: finished piece 1 at 17-JUN-20
piece handle=/rman/respaldo/PRMYDB_20200617_1cv30djl.bkp tag=TAG20200617T183641 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:15
Finished backup at 17-JUN-20

Starting Control File and SPFILE Autobackup at 17-JUN-20
piece handle=/rman/controlfile/c-2978577906-20200617-01.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 17-JUN-20

RMAN>

write down the backup tag TAG20200617T183641 for later reference in the restore.

Generate Standby Control File

SQL> ALTER DATABASE CREATE STANDBY CONTROLFILE AS '/home/oracle/stbydb.ctl';

Database altered.

Validate or Create the Password File

[oracle@patodgprmy ~]$ ls -l $ORACLE_HOME/dbs/orapw*
-rw-r-----. 1 oracle oinstall 6144 Jun 17 18:16 /opt/oracle/product/19c/dbhome_1/dbs/orapwprmydb

if there is none available you can create one using the utility orapwd:

[oracle@patodgprmy ~]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID sysdg=y

Enter password for SYS:

Enter password for SYSDG:

[oracle@patodgprmy ~]$

Create Standby Init File

Generate a pfile from the current spfile:

SQL> CREATE PFILE='/home/oracle/initstbydb.ora' FROM SPFILE;

File created.

then edit it to modify only the following parameters with the following values:

[oracle@patodgprmy ~]$ vi /home/oracle/initstbydb.ora


*.db_file_name_convert='/PRMYDB/','/STBYDB/'
*.db_name=prmydb
...
*.db_unique_name=stbydb
...
*.fal_server='STBYDB'
...
*.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stbydb'
*.log_archive_dest_2='SERVICE=prmydb ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prmydb'
...
*.log_file_name_convert='/PRMYDB/','/STBYDB/'

Prepare Networking

We created our machines using the reserved static addresses from our subnet patosubnet as described in the document Using Customized Internal IP, and we assigned the following IP addresses:

patodgprmy: prmynet (10.20.0.10) and patodgstby: stbynet (10.20.0.20)

Configure Listener

Configure the Listener in both machines using our internal IP addresses:

[oracle@patodgprmy ~]$ vi $ORACLE_HOME/network/admin/listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.0.10)(PORT = 1521))


[oracle@patodgstby ~]$ vi $ORACLE_HOME/network/admin/listener.ora
LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.0.20)(PORT = 1521))

Start Listeners

And start the listener in both servers:

[oracle@patodgstby ~}$ lsnrctl start

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/patodgstby/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.0.10)(PORT=1521)))

[oracle@patodgstby ~}$ lsnrctl start

TNSLSNR for Linux: Version 19.0.0.0.0 - Production
System parameter file is /opt/oracle/product/19c/dbhome_1/network/admin/listener.ora
Log messages written to /opt/oracle/diag/tnslsnr/patodgstby/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.20.0.20)(PORT=1521)))

Add the Other Instance in TNSNAMES

We need to access the Primary service from the Standby and the Standby service from the Primary so make sure we have both databases registered in both tnsnames.ora files:

$ vi $ORACLE_HOME/network/admin/tnsnames.ora

prmydb =
  (DESCRIPTION =
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.0.10)(PORT = 1521)))
    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = prmydb))
  )

stbydb =
  (DESCRIPTION =
    (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.0.20)(PORT = 1521)))
    (CONNECT_DATA = (SERVER = DEDICATED)(SERVICE_NAME = stbydb))
  )

and then test we can communicate in both ways:

[oracle@patodgprmy ~]$ tnsping stbydb

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-JUN-2020 19:01:09

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.0.20)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = stbydb)))
OK (0 msec)

[oracle@patodgstby ~]$ tnsping prmydb

TNS Ping Utility for Linux: Version 19.0.0.0.0 - Production on 17-JUN-2020 19:01:59

Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.20.0.10)(PORT = 1521))) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = prmydb)))
OK (0 msec)

Prepare the Standby Server

Create Directory Structure

Create the directories where the Primary data files are stored and also the directories referred by the init.ora file but this time pointing to the new instance name:

[oracle@patodgstby ~]$ sudo mkdir /oradata
[oracle@patodgstby ~]$ sudo chown oracle:oinstall /oradata

[oracle@patodgstby ~]$ mkdir /oradata/cdbs
[oracle@patodgstby ~]$ mkdir /oradata/pdbs
[oracle@patodgstby ~]$ mkdir /oradata/recovery
[oracle@patodgstby ~]$ mkdir /oradata/cdbs/STBYDB
[oracle@patodgstby ~]$ mkdir /oradata/recovery/STBYDB

[oracle@patodgstby ~]$ mkdir -p /opt/oracle/admin/stbydb/adump

Copy Configuration Files

Copy the modified Standby init, the generated Standby controlfile and the Oracle password file to the new server, at the following locations and with the following names:

[oracle@patodgprmy ~]$ gcloud config set compute/zone us-central1-f
Updated property [compute/zone].
[oracle@patodgprmy ~]$ gcloud compute scp initstbydb.ora patodgstby:$ORACLE_HOME/dbs 
initstbydb.ora                                              100% 1708     2.3MB/s   00:00
[oracle@patodgprmy ~]$ gcloud compute scp stbydb.ctl patodgstby:/oradata/cdbs/STBYDB/control01.ctl
stbydb.ctl                                                  100%   18MB  36.3MB/s   00:00
[oracle@patodgprmy ~]$ gcloud compute scp stbydb.ctl patodgstby:/oradata/recovery/STBYDB/control02.ctl
stbydb.ctl                                                  100%   18MB  37.2MB/s   00:00
[oracle@patodgprmy ~]$ gcloud compute scp $ORACLE_HOME/dbs/orapwprmydb patodgstby:$ORACLE_HOME/dbs/orapwstbydb
orapwprmydb                                                 100% 6144     2.8MB/s   00:00

We need to have the generated backup available on the Standby server. You can copy the backup files to the other machine or to a shared location, we are going to use our RMAN Google disk.
Add the RMAN disk to the new Virtual Machine and then mount it to /rman directory so we can read the Primary backup:

[oracle@patodgstby ~]$ sudo mkdir /rman
[oracle@patodgstby ~]$ sudo chown oracle:oinstall /rman

[oracle@patodgstby ~]$ sudo mount -o discard,defaults /dev/sdb /rman
mount: /dev/sdb is write-protected, mounting read-only

[oracle@patodgstby ~]$ cd /rman
[oracle@patodgstby rman]$ ls
controlfile  lost+found  respaldo

For more information on the RMAN disk we are using here please check Google Disk for RMAN backups

Setup Oracle Variables

Export the following Oracle variables, for persistence include them in your .bash_profile:

[oracle@patodgstby ~]$ vi .bash_profile

# Database Variables
export ORACLE_BASE=/opt/oracle
export ORACLE_HOME=/opt/oracle/product/19c/dbhome_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export ORACLE_SID=stbydb

Restore Primary Backup into the Standby

Now that our environment is prepared we need to connect to RMAN to perform the restore.

Restore Database

We startup the database with mount option and we restore our database using the TAG from our offline backup:

[oracle@patodgstby ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Wed Jun 17 19:57:11 2020
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted


RMAN> restore database from tag 'TAG20200617T183641';

Starting restore at 17-JUN-20
Starting implicit crosscheck backup at 17-JUN-20
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=261 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 17-JUN-20

Starting implicit crosscheck copy at 17-JUN-20
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 17-JUN-20

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /oradata/cdbs/STBYDB/system01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /oradata/cdbs/STBYDB/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00007 to /oradata/cdbs/STBYDB/users01.dbf
channel ORA_DISK_1: restoring datafile 00014 to /oradata/cdbs/STBYDB/undotbsp.dbf
channel ORA_DISK_1: reading from backup piece /rman/respaldo/PRMYDB_PRMYDB_20200617_1av30dhp.bkp
channel ORA_DISK_1: piece handle=/rman/respaldo/PRMYDB_20200617_1av30dhp.bkp tag=TAG20200617T183641
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00005 to /oradata/cdbs/STBYDB/pdbseed/system01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /oradata/cdbs/STBYDB/pdbseed/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00008 to /oradata/cdbs/STBYDB/pdbseed/undotbs01.dbf
channel ORA_DISK_1: reading from backup piece /rman/respaldo/PRMYDB_20200617_1bv30dis.bkp
channel ORA_DISK_1: piece handle=/rman/respaldo/PRMYDB_20200617_1bv30dis.bkp tag=TAG20200617T183641
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00009 to /oradata/pdbs/taller/system01.dbf
channel ORA_DISK_1: restoring datafile 00010 to /oradata/pdbs/taller/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00012 to /oradata/pdbs/taller/datos.dbf
channel ORA_DISK_1: reading from backup piece /rman/respaldo/PRMYDB_20200617_1cv30djl.bkp
channel ORA_DISK_1: piece handle=/rman/respaldo/PRMYDB_20200617_1cv30djl.bkp tag=TAG20200617T183641
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:25
Finished restore at 17-JUN-20

RMAN> exit

Recovery Manager complete.

DataGuard Standby

Initiate Standby Recovery

We initiate our replication issuing the command alter database recover:

[oracle@patodgstby ~]$ sqlplus / as sysdba

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Database altered.

SQL> select name, open_mode, database_role, switchover_status from v$database ;

NAME      OPEN_MODE            DATABASE_ROLE    SWITCHOVER_STATUS
--------- -------------------- ---------------- --------------------
PRMYDB    MOUNTED              PHYSICAL STANDBY TO PRIMARY

SQL>

Validate the DataGuard Process

Review that the DataGuard processes are running and the last sequence applied:


SQL> SELECT ROLE, THREAD#, SEQUENCE#, ACTION FROM V$DATAGUARD_PROCESS;

ROLE                        THREAD#  SEQUENCE# ACTION
------------------------ ---------- ---------- ------------
RFS ping                          1          3 IDLE
recovery apply slave              0          0 IDLE
recovery apply slave              0          0 IDLE
managed recovery                  0          0 IDLE
RFS archive                       0          0 IDLE
archive local                     0          0 IDLE
archive redo                      0          0 IDLE
archive redo                      0          0 IDLE
redo transport timer              0          0 IDLE
archive redo                      0          0 IDLE
gap manager                       0          0 IDLE
redo transport monitor            0          0 IDLE
RFS async                         1          3 IDLE
log writer                        0          0 IDLE
recovery logmerger                1          3 APPLYING_LOG

15 rows selected.

and we can see in the alert log that the archive logs are being transferred and applied:

[oracle@patodgstby ~]$ cd $ORACLE_BASE/diag/rdbms/stbydb/stbydb/trace/
[oracle@patodgstby trace]$ tail -f alert_stbydb.log

2020-06-17T20:16:05.871028-05:00
 rfs (PID:4757): Primary database is in MAXIMUM PERFORMANCE mode
 rfs (PID:4757): Re-archiving LNO:6 T-1.S-2
2020-06-17T20:16:06.155055-05:00
ARC2 (PID:2756): Archived Log entry 2 added for T-1.S-2 ID 0xb1b5cab6 LAD:1
2020-06-17T20:16:06.219735-05:00
 rfs (PID:4757): Selected LNO:4 for T-1.S-3 dbid 2978577906 branch 1043350981
2020-06-17T20:16:07.082072-05:00
PR00 (PID:3761): Media Recovery Waiting for T-1.S-3 (in transit)
2020-06-17T20:16:07.082724-05:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 3 Reading mem 0
  Mem# 0: /oradata/cdbs/STBYDB/stbyredo1.rdo

Validate Replication

Create Data and Objects in Primary

In this example our user will connect to the pluggable database and create one table and insert a row inside:

[oracle@patodgprmy ~]$ sqlplus /nolog

SQL> conn pato@taller;
Enter password:
Connected.
SQL> create table tdg1 (i1 int, c1 varchar(20), d1 timestamp);

Table created.

SQL> alter table tdg1 add (constraint i1_pk primary key (i1) );

Table altered.

SQL> insert into tdg1 values (1,'DataGuard Test',current_timestamp);

1 row created.

SQL> commit;

Commit complete.

Validate Data Replicated in Standby

Disable replication and open the Standby in read only mode to validate that the new table and data are already here:

[oracle@patodgstby ~]$ sqlplus / as sysdba

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.

SQL> alter database open read only;

Database altered.

SQL> alter pluggable database taller open;

Pluggable database altered.

SQL> alter session set container = taller;

Session altered.

SQL> select * from pato.tdg1 ;

        I2 C2                   D2
---------- -------------------- ------------------------------
         1 DataGuard Test       17-JUN-20 08.20.14.216007 PM

Our DataGuard environment is working and data and objects are being recovered from the Primary to the Standby!

Conclusion

We have successfully configured a DataGuard environment with one Physical Standby using Oracle 19c multitenant instances running on Virtual Machine Instances from Google Cloud Platform.