Setting up Oracle 12c Active Data Guard Database

Prerequisites for Oracle 12c Active Data Guard Database

In this article we will set up an Active Data Guard for a CDB named “CDB12C” using RMAN duplicate. This article assumes that database binaries are installed on Standby host.The very first step is to turn force logging on the Primary database.

Also set the parameter REMOTE_LOGIN_PASSWORD=exclusive.

SQL> alter database force logging;

Database altered.

SQL> alter system set REMOTE_LOGIN_PASSWORD=exclusive;
System altered.

Next on the standby server, create the directory structure below.

$ mkdir -p /u01/app/oracle/admin/cdb12c/adump
$ mkdir -p /u01/app/oracle/oradata/cdb12c/pdb1/
$ mkdir -p /u01/app/oracle/oradata/cdb12c/pdbseed
$ mkdir -p /u01/app/oracle/fast_recovery_area/cdb12c/
$ mkdir -p /u01/app/oracle/oradata/cdb12c/pdbseed/

Next copy the password file from Primary to Standby.

$ scp -rp orapwcdb12c* oracle@vst12c_dg:/u01/app/oracle/product/12.1.0/dbhome_1/dbs

On the Standby server configure the listener.ora file.

LISTENER12C =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = vst12c_dg)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1522))
)
)

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

Restart or reload the listener, for the changes to take affect.

$ lsnrctl reload listener12c

Add the db_name entry in init.ora file.

*.db_name=cdb12c

Add the following line to the /etc/oratab file.

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

Then add the tnsnames entries on both the Primary and Standby.

Create Active Data Guard

After the pre-requisite steps are complete, start your Standby instance in the no-mount state.

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

$ sqlplus sys as sysdba

SQL*Plus: Release 12.1.0.1.0 Production on Wed Mar 5 10:57:42 2014

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

After the instance is in the nomount state, start the database duplicate process. On the Primary database, connect to the primary database and also the Standby using an auxiliary command.

$ rman target sys/syspassword auxiliary sys/syspassword@cdb12c_dg
Recovery Manager: Release 12.1.0.1.0 - Production on Wed Mar 5 10:58:43 2014

Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: CDB12c (DBID=3738773602)
connected to auxiliary database: CDB12C (not mounted)

Once connected, use the following command to start the duplicate process.

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='cdb12c_dg'
set standby_file_management='AUTO'
set log_archive_config='dg_config=(cdb12c,cdb12c_dg)'
set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=cdb12c_dg'
set log_Archive_dest_2='service=cdb12c async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=cdb12c';
}

After the duplicate command is finished, we then need to add certain parameters on the Primary database.

RMAN> alter system set standby_file_management='AUTO';
Statement processed

RMAN> alter system set log_archive_config='dg_config=(cdb12c,cdb12c_dg)';
Statement processed

RMAN> alter system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST valid_for=(all_logfiles,all_roles) db_unique_name=cdb12c';
Statement processed

RMAN> alter system set log_Archive_dest_2='service=cdb12c_dg async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=cdb12c_dg';
Statement processed

We now need to create Standby online redo log file groups which are normally equal to the number of your online redo log groups plus one. Because we have three online redo log groups, we will have to create four standby redo log groups.

The redo log groups have to be created on both the Primary and Standby databases.

On Standby

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/cdb12c/standby_redo01.log' size 50m;

Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/cdb12c/standby_redo02.log' size 50m;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/cdb12c/standby_redo03.log' size 50m;
Database altered.

SQL> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/cdb12c/standby_redo04.log' size 50m;
Database altered.

On Primary

RMAN> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/cdb12c/standby_redo01.log' size 50m;
Statement processed

RMAN> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/cdb12c/standby_redo02.log' size 50m;
Statement processed

RMAN> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/cdb12c/standby_redo03.log' size 50m;
Statement processed

RMAN> ALTER DATABASE ADD STANDBY LOGFILE '/u01/app/oracle/oradata/cdb12c/standby_redo04.log' size 50m;
Statement processed

Then on the primary database change the protection mode to Maximum Availability.

RMAN> alter database set standby database to maximize availability;
Statement processed

Now let’s verify the status.

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

Finally shutdown the Standby database, reopen it.

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.