Drop Down MenusCSS Drop Down MenuPure CSS Dropdown Menu

Oracle 12c Active Data Guard

In the Multitenant world, the PDB Database is considered an independent database but the operational tasks performed at the CDB level effect all the PDB databases plugged into it. The task of setting up of a Data Guard, switching or failing over are all performed at the CDB level. When the Data Guard is initially setup, with the CDB database, all PDB’s are also replicated to the target. Similarly when a failover or switchover is performed all the PDBs also change role along with the CDB container. This greatly eases the management of the PDB databases.
Moving certain PDBs from a non Data Guard environment to a Data Guard environment is extremely easy. You just unplug it from one CDB container and plug it to the another CDB which is in a Data Guard configuration and the new PDB will automatically get replicated to the destination. The reason that this is possible is that the redo logs which are shipped belong at the CDB level and include redo data for all PDBs plugged into it.

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.
(ADDRESS = (PROTOCOL = TCP)(HOST = vst12c_dg)(PORT = 1522))
(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.

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

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 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 - 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.
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.


Popular posts from this blog

How to find the server is whether standby (slave) or primary(master) in Postgresql replication ?

How to Get Table Size, Database Size, Indexes Size, schema Size, Tablespace Size, column Size in PostgreSQL Database

7 Steps to configure BDR replication in postgresql

Postgres Streaming Replication Configuration

vacuumlo - removing large objects orphans from a database PostgreSQL