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!!
Very clear explanation.
Great post!