Creating the data guard configuration in a 12c Container Database.
NOTE – 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.
SID_NAME = condb1
Primary: orasql-001-dev.corporate.domain
db_unique_name=condb1
Standby: orasql-001-test.corporate.domain
db_unique_name=condb1_dr
On Primary
SQL> alter database force logging;
Database altered.
SQL> alter system set REMOTE_LOGIN_PASSWORD=exclusive;
System 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)
)
)
Create Active Data Guard
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.
On Primary
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
--------------------------------------------------------------------------------
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!!
NOTE – 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.
SID_NAME = condb1
Primary: orasql-001-dev.corporate.domain
db_unique_name=condb1
Standby: orasql-001-test.corporate.domain
db_unique_name=condb1_dr
On Primary
SQL> alter database force logging;
Database altered.
SQL> alter system set REMOTE_LOGIN_PASSWORD=exclusive;
System 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)
)
)
Create Active Data Guard
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.
On Primary
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
--------------------------------------------------------------------------------
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!!
No comments:
Post a Comment