News
Perth, Australia
+ (61) 417713124
prosolutions@gavinsoorma.com

Creating an Oracle 12c Data Guard Active Standby Database

  • Posted by Gavin Soorma
  • On November 19, 2013
  • 1 Comments
  • 12c, active, ACTIVE STANDBY, data guard, dupliate, multitenant, rman

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 configuration in a 12c Container Database.

Remember – in 12c Data Guard is set up at the Container level and not the individual Pluggable database level as the redo log files only belong to the Container database and the individual pluggable databases do not have their own online redo log files.

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

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 file from primary to standby

$ scp -rp orapwcondb1* oracle@orasql-001-test:/u01/app/oracle/product/12.1.0/dbhome_1/dbs
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 = 1523))
      (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

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orasql-001-test.corporate.domain)(PORT=1523)))
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 oratab file

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 = 1523))
    (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 file 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 = 1525))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = condb1)
    )
  )

CONDB1_DR =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orasql-001-test.corporate.domain)(PORT = 1523))
    (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

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

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 file 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,all_roles) db_unique_name=condb1_dr'
set log_Archive_dest_2='service=condb1 async noaffirm reopen=15 valid_for=(all_logfiles,primary_role) db_unique_name=condb1';
}

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 valid_for=(all_logfiles,all_roles) db_unique_name=condb1';

Statement processed

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

Statement processed

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

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

On Standby

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

Database altered.

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

Database altered.

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

Database altered.

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

Database altered.

On Primary

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

Statement processed

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

Statement processed

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

Statement processed

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

Statement processed

On Primary change the protection mode

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

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

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 options

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 process like '%MRP%';

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

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           MOUNTED

SQL>  alter pluggable database all open read only;

Pluggable database altered.

SQL> select name,open_mode from v$pdbs;

NAME                           OPEN_MODE
------------------------------ ----------
PDB$SEED                       READ ONLY
PDB1                           READ 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 options

SQL> select count(*) from sales_dr;

  COUNT(*)
----------
    918843

The test is successful and we have created our first Oracle 12c Active Stanbdy database!!

 1

1 Comments

Antonio
  • Dec 19 2017
Very clear explanation. Great post!

Leave Reply

Your email address will not be published. Required fields are marked *