Creating an Oracle 12c Data Guard Active Standby Database

This note examines how to create an Oracle 12.1.0 physical standby Active Data Guard database using the RMAN DUPLICATE FROM ACTIVE command.
We will be creating the data guard coniguration in a 12c Container Database.

Remember – in 12C Data Guard is setup at the container level and not the individual plug gable
database level as the redo logiles only belong to the Container database and the individual pluggable databases do not have their own online redo logiles.

In my next post we will examine how to unplug a pluggable database from a Container database not
having Data Guard set up and how easy it is to provide high availability for a pluggable database by just plugging it into a container database which has Data Guard conigured.

The platform is Linux 64 bit OEL 5.9 and the primary database db_unique_name is CONDB1 and the db_unique_name of the Active Standby database is CONDB1_DR.

Let us look at the steps involved.

on primary

SQL> alter database force logging; 
Database altered

on standby

Create the required directory structure

$ mkdir -p  /u01/app/oracle/admin/condb1/adump

$ mkdir -p /u01/app/oracle/oradata/condb1/pdb1/

$ mkdir -p /u01/app/oracle/oradata/condb1/pdbseed

$ mkdir -p /u01/app/oracle/fast_recovery_area/condb1/ 

$ mkdir -p /u01/app/oracle/oradata/condb1/pdbseed/

Copy the password ile from primary to standby

$ scp -rp orapwcondb1* oracle@orasql-001-test:/u01/app/oracle/product/12.1.0/dbhom oracle@orasql-001-test's password:

orapwcondb1
100% 7680     7.5KB/s   00:00

on standby

Add a static entry in the listener.ora for condb1_dr

LISTENER12C =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orasql-001-test.corporate.domain)(PORT = (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))
) )

SID_LIST_LISTENER12C = (SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = condb1_dr)
(ORACLE_HOME = /u01/app/oracle/product/12.1.0/dbhome_1) (SID_NAME = condb1)
) )

Reload the listener

$ lsnrctl reload listener12c

LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 06-NOV-2013 10:49:46

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orasql-001-test.corporate. The command completed successfully

Add an entry in the initcondb1.ora – just one line with the entry for db_name

$ cat initcondb1.ora *.db_name=condb1


Add an entry in the oratabile


condb1:/u01/app/oracle/product/12.1.0/dbhome_1:N

Add the tns aliases on both the primary as well as standby site

on primary

condb1_dr =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orasql-001-test.corporate.domain)(PORT = 15 (CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = condb1_dr) )
)

on standby

Since we are using a non-standard port for the listener we need to add an entry in the tnsnames.ora ile for the LOCAL_LISTENER database parameter.

LISTENER_CONDB1 =
(ADDRESS = (PROTOCOL = TCP)(HOST = orasql-001-test.corporate.domain)(PORT = 1523

CONDB1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orasql-001-dev.corporate.domain)(PORT = 152 (CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = condb1)
) )

CONDB1_DR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = orasql-001-test.corporate.domain)(PORT = 15 (CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = condb1_dr) )
)

on standby

Start the Standby instance in NOMOUNT mode

$ . oraenv
ORACLE_SID = [condb1] ? condb1
The Oracle base has been set to /u01/app/oracle

[oracle@orasql-001-test admin]$ `sqlplus sys as sysdba`

SQL*Plus: Release 12.1.0.1.0 Production on Wed Nov 6 10:57:42 2013

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

Enter password:
Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global	Area  229683200	bytes
Fixed Size	2286800	bytes
Variable Size	171969328	bytes
Database Buffers	50331648	bytes
Redo Buffers	5095424	bytes

on primary

Connect to Primary and auxiliary connection to Standby

$ rman target sys/syspassword auxiliary sys/syspassword@condb1_dr

Recovery Manager: Release 12.1.0.1.0 - Production on Wed Nov 6 10:58:43 2013

connected to target database: CONDB1 (DBID=3738773602) connected to auxiliary database: CONDB1 (not mounted)

This is the command we will run to create the Standby Database.

Note – since the data ile names are not being changed on the standby database we need to include the NOFILENAMECHECK

run {
allocate channel c1 type disk; allocate channel c2 type disk;
allocate auxiliary channel aux type disk;
duplicate target database for standby from active database nofilenamecheck spfile
set log_archive_max_processes='8' set db_unique_name='condb1_dr'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(condb1,condb1_dr)'
set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles set log_Archive_dest_2='service=condb1 async noaffirm reopen=15 valid_for=(all_log 
}

After the RMAN DUPLICATE command completes we now need to add the relevant parameters for the redo log transport on the Primary database.

RMAN> alter system  set standby_file_management='AUTO'; Statement processed
RMAN> alter system set log_archive_config='dg_config=(condb1,condb1_dr)'; Statement processed
RMAN> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST vali Statement processed
RMAN> alter system set log_Archive_dest_2='service=condb1_dr async noaffirm reopen

Statement processed

We will be running the standby database in Maximum Availability mode, so we need to create the standby redo logiles on both the primary as well as standby site.

Since we have 3 online redo logile groups, we need to create (3+1) 4 Standby redo logile groups

on standby

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/condb1/standby_re Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/condb1/standby_re Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/condb1/standby_re Database altered.
SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/condb1/standby_re Database altered.

on primary

RMAN> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/condb1/standby_r Statement processed
RMAN> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/condb1/standby_r Statement processed
RMAN> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/condb1/standby_r Statement processed
RMAN>  ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/condb1/standby_ Statement processed

RMAN> alter database set standby database to maximize availability;


Statement processed Check the status

RMAN> select destination,status from v$archive_dest_status where rownum <3;

DESTINATION

STATUS
---------

VALID

condb1_dr VALID

Test Redo Apply is working

connect to the plug gable database pDB1 as SH and create a table called SALES_DR. Populate it with rows from SALES table in the SH schema.

$ sqlplus sh/sh@localhost:1525/pdb1

SQL*Plus: Release 12.1.0.1.0 Production on Wed Nov 6 11:40:26 2013

Last Successful login time: Sat May 25 2013 04:25:15 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing optio

SQL> create table sales_dr as select * from sales; Table created.

on the standby database, the RMAN script which we ran from the primary database has not opened the database and started managed recovery.

Let us now manually do it.

on standby

SQL> shutdown immediate;
ORA-01109: database not open Database dismounted.
SQL> startup;
ORACLE instance started.

Total System Global Area 4275781632 bytes
Fixed Size 2296576 bytes
Variable Size 2214593792 bytes
Database Buffers 2046820352 bytes
Redo Buffers 12070912 bytes
Database mounted. Database opened.

SQL> recover managed standby database using current logfile disconnect;

Media recovery complete.

Check the MRP process is running

SQL> !ps -ef |grep mrp
oracle   28800     1  0 11:41 ?        00:00:00 ora_mrp0_condb1

SQL> select process,status,thread#,sequence#,blocks from v$managed_standby where p

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCKS
	
MRP0      WAIT_FOR_LOG          1         25          0

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN  MODE
------------------------------ ----------
READ ONLY MOUNTED
SQL>  alter pluggable database all open read only; Pluggable database altered.
SQL> select name,open_mode from v$pdbs;

Creating an Oracle 12c Data Guard Active Standby Database
OPEN MODE
_
PDB$SEED PDB1
READ READ
ONLY ONLY

The Pluggable database PDB1 has been opened in READ ONLY mode, but the Container Database is running as an Active Standby
database and applying changes real-time as soon as they are received from the primary even when the Standby Container database and all the associated pluggable databases have been opened in read only mode.
Let us see if the SALES_DR table we had created on the Primary database can be accessed from the active standby database.

On the standby site, connect to the container database PDB1 as SH

[oracle@orasql-001-test condb1]$ sqlplus sh/sh@localhost:1523/pdb1 
SQL*Plus: Release 12.1.0.1.0 Production on Wed Nov 6 11:43:40 2013
Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Last Successful login time: Wed Nov 06 2013 11:40:26 +08:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing optio 
SQL> select count(*) from sales_dr;
COUNT(*)
----------
918843