Standby database deployment with DBCA – 12.2

To create physical standby database or logical standby database then so far we have used various methods and few of them what i’ve used in my career so far.

  1. SCP method
  2. RMAN Traditional restore
  3. RMAN Duplicate
  4. RMAN Active Duplicate
  5. EMCC – Enterprise manager cloud control.

Each method have its own advantages, Most of the cases so far personally i’ve used RMAN duplicate, because RMAN active duplicate is always challenge for mission critical environments and consideration of bandwidth as well with the database size.

In 12.2 Oracle introduced one more new feature to deploy the standby database using DBCA, I’ve performed this test during my beta test and will share what are the steps and prerequisites to deploy the standby database and also configuring Broker.

Prerequisites – Am not including all the steps as creating Pfile, Password file copy, TNS entries so on, because those are common tasks we used to perform every time but i want to show the key content.

  • Installation of 12.2 Enterprise Edition software
  • Prepare SPfile.
  • Network configuration.
  • Primary database PDB can be in open or close
  • Standby/auxiliary should be in shutdown status
  • Target database should started with SPFILE.
  • DB_FILE_NAME_CONVERT, LOG_FILE_NAME_CONVERT if applicable
  • Sufficient shared memory at host level

Deployment

Connect to the standby instance and then ensure you have gathered all the information required for the attributes of dbca such as sys password, database names and so on. DBCA will use in silent mode and primary database can be connected with easy access. We can extract the DBCA options by help command like below.

[oracle@ORA-C2 dbs]$ dbca -silent -createDuplicateDB -gdbName R2B -primaryDBConnectionString 192.168.0.80:1522/ottawa 
-sid hyd -sysPassword oracle -createAsStandby -dbUniqueName hyd
[WARNING] [DBT-11210] Automatic Memory Management is not a feasible option on the system.
 CAUSE: The system has more than 4GB physical memory, or the required space of shared memory is not met.
Listener config step
33% complete
Auxiliary instance creation
66% complete
RMAN duplicate
100% complete
Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/hyd/R2B3.log" for further details.
[oracle@ORA-C2 dbs]$

Crack the DBCA Log

If we see the log file carefully we can see what commands are used intenrlaly by DBCA to deploy the DBCA.

GIVEN COMMAND=run {duplicate target database
for standby
from active database
dorecover
spfile
set db_unique_name='hyd'
set log_archive_dest_1='location=/u01/app/oracle/fast_recovery_area/hyd'
 set control_files='/u01/app/oracle/oradata/hyd/control01.ctl'
 set db_create_file_dest='/u01/app/oracle/oradata/hyd'
set db_recovery_file_dest='/u01/app/oracle/fast_recovery_area/hyd'
set audit_file_dest='/u01/app/oracle/audit'
set diagnostic_dest='/u01/app/oracle'
set local_listener=''
nofilenamecheck;
}
[oracle@ORA-C2 dbs]$ cat /u01/app/oracle/cfgtoollogs/dbca/hyd/R2B3.log
[ 2016-01-11 02:06:58.664 NZDT ] Listener config step
DBCA_PROGRESS : 33%
[ 2016-01-11 02:06:58.754 NZDT ] Auxiliary instance creation
DBCA_PROGRESS : 66%
[ 2016-01-11 02:07:11.667 NZDT ] RMAN duplicate
DBCA_PROGRESS : 100%
[oracle@ORA-C2 dbs]$

Post-Deployment

After the successful deployment we will review the new standby database details, to ensure everything looks to be good.

SQL> show parameter db_unique_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_unique_name string hyd
SQL> show parameter db_name

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_name string R2B
SQL> show parameter instance

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
active_instance_count integer
cluster_database_instances integer 1
instance_abort_delay_time integer 0
instance_groups string
instance_mode string READ-WRITE
instance_name string ottawa
instance_number integer 0
instance_type string RDBMS
open_links_per_instance integer 4
parallel_instance_group string
parallel_server_instances integer 1
SQL> show parameter pfile

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/12.2.0
 .1/dbhome_1/dbs/spfilehyd.ora
SQL> select name from v$datafile;

NAME
------------------------------------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/oradata/hyd/HYD/datafile/o1_mf_system_c94oxnvk_.dbf
/u01/app/oracle/oradata/hyd/HYD/datafile/o1_mf_sysaux_c94oygm7_.dbf
/u01/app/oracle/oradata/hyd/HYD/datafile/o1_mf_undotbs1_c94oyy79_.dbf
/u01/app/oracle/oradata/hyd/HYD/28ABAF54BBB516B1E0535000A8C0986C/datafile/o1_mf_system_c94oz17y_.dbf
/u01/app/oracle/oradata/hyd/HYD/datafile/o1_mf_users_c94oz48y_.dbf
/u01/app/oracle/oradata/hyd/HYD/28ABAF54BBB516B1E0535000A8C0986C/datafile/o1_mf_sysaux_c94oz5dj_.dbf
/u01/app/oracle/oradata/hyd/HYD/28ABD8368EF61EEEE0535000A8C02333/datafile/o1_mf_system_c94ozdy5_.dbf
/u01/app/oracle/oradata/hyd/HYD/28ABD8368EF61EEEE0535000A8C02333/datafile/o1_mf_sysaux_c94ozn6n_.dbf

8 rows selected.

SQL> !ls -ltr /u01/app/oracle/oradata/hyd/HYD/datafile/
total 1977384
-rw-r-----. 1 oracle oinstall 922755072 Jan 11 02:09 o1_mf_system_c94oxnvk_.dbf
-rw-r-----. 1 oracle oinstall 183508992 Jan 11 02:09 o1_mf_undotbs1_c94oyy79_.dbf
-rw-r-----. 1 oracle oinstall 912269312 Jan 11 02:09 o1_mf_sysaux_c94oygm7_.dbf
-rw-r-----. 1 oracle oinstall 5251072 Jan 11 02:09 o1_mf_users_c94oz48y_.dbf
-rw-r-----. 1 oracle oinstall 139468800 Jan 11 02:09 o1_mf_temp_c94p0q2h_.tmp

SQL> !ls -ltr /u01/app/oracle/oradata/hyd/HYD/28ABAF54BBB516B1E0535000A8C0986C/datafile/
total 820248
-rw-r-----. 1 oracle oinstall 283123712 Jan 11 02:09 o1_mf_system_c94oz17y_.dbf
-rw-r-----. 1 oracle oinstall 555753472 Jan 11 02:09 o1_mf_sysaux_c94oz5dj_.dbf
-rw-r-----. 1 oracle oinstall 59777024 Jan 11 02:09 o1_mf_temp_c94p0t5j_.tmp

SQL> !ls -ltr /u01/app/oracle/oradata/hyd/HYD/
total 16
drwxr-x---. 3 oracle oinstall 4096 Jan 11 02:08 28ABAF54BBB516B1E0535000A8C0986C
drwxr-x---. 3 oracle oinstall 4096 Jan 11 02:08 28ABD8368EF61EEEE0535000A8C02333
drwxr-x---. 2 oracle oinstall 4096 Jan 11 02:09 onlinelog
drwxr-x---. 2 oracle oinstall 4096 Jan 11 02:09 datafile

SQL> select name,open_mode from v$pdbs;

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

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
 2 PDB$SEED READ ONLY NO
 3 R2BPDB MOUNTED
SQL> alter pluggable database all open;

Pluggable database altered.

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
 2 PDB$SEED READ ONLY NO
 3 R2BPDB READ ONLY NO
SQL>

SQL> alter system set dg_broker_start=true;

System altered.

SQL>

We can see even the PDB was also available and opened successfully all in read-only mode as it is Standby database.

Broker Configuration

In my test cases i always prefer to use Data Guard broker and there are no changes in 12.2 to configure Broker but complete setup when we configure Deployment + Broker. 🙂

DGMGRL> create configuration R2B as primary database is ottawa connect identifier is ottawa;
Configuration "r2b" created with primary database "ottawa"
DGMGRL
DGMGRL> add database hyd as connect identifier is hyd;
Database "hyd" added
DGMGRL>
DGMGRL> enable configuration;
Enabled.
DGMGRL>
DGMGRL> show configuration
Configuration - r2b
Protection Mode: MaxPerformance
 Members:
 ottawa - Primary database
 hyd - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
SUCCESS (status updated 30 seconds ago)
DGMGRL> show database hyd
Database - hyd
Role: PHYSICAL STANDBY
 Intended State: APPLY-ON
 Transport Lag: 0 seconds (computed 0 seconds ago)
 Apply Lag: 0 seconds (computed 0 seconds ago)
 Average Apply Rate: 12.00 KByte/s
 Real Time Query: ON
 Instance(s):
 hyd
Database Status:
SUCCESS
DGMGRL>

Question: What restore method will be used by DBCA internally?

Answer: Indeed Active Duplicate. 🙂

Question: Will PDB also included when deployment used by DBCA?

Answer: Yes, everything and also we can pass more attributes to skip PDB’s.

We have seen how to deploy the standby in 12.2 using DBCA starting with prerequisites and also we have seen how to check whether standby in sync with primary or not by using Data Guard broker and finally with simple questions related to 12.2 DBCA for standby. Happy Reading. 🙂