One of the real nice new features in Oracle 12c Release 2 (12.2.0.1) is the ability to create an Oracle Data Guard Standby Database using DBCA (Database Configuration Assistant). This really does simplify the process of creating a standby database as well and automates a number of steps in the creation process which were earlier manually performed.
In this example we will see how a 12.2.0.1 Data Guard environment is created via DBCA and then Data Guard Broker (DGMGRL).
The source database is called salesdb and the standby database DB_UNIQUE_NAME will be salesdb_sb.
Primary database host name is host01 and the Standby database host name is host02.
The syntax is:
dbca -createDuplicateDB -gdbName global_database_name -primaryDBConnectionString easy_connect_string_to_primary -sid database_system_identifier [-createAsStandby [-dbUniqueName db_unique_name_for_standby]]
We will run the command from the standby host host02 as shown below.
[oracle@host02 ~]$ dbca -silent -createDuplicateDB -gdbName salesdb -primaryDBConnectionString host01:1521/salesdb -sid salesdb -createAsStandby -dbUniqueName salesdb_sb Enter SYS user password: Listener config step 33% complete Auxiliary instance creation 66% complete RMAN duplicate 100% complete Look at the log file "/u02/app/oracle/cfgtoollogs/dbca/salesdb_sb/salesdb.log" for further details.
Connect to the Standby Database and verify the role of the database
Note that the SPFILE and Password File for the Standby Database has been automatically created
[oracle@host02 dbs]$ ls -l sp* -rw-r-----. 1 oracle dba 5632 Mar 22 09:40 spfilesalesdb.ora [oracle@host02 dbs]$ ls -l ora* -rw-r-----. 1 oracle dba 3584 Mar 17 14:38 orapwsalesdb
Add the required entries to the tnsnames.ora file
Continue with the Data Guard Standby Database creation using the Data Guard Broker
SQL> alter system set dg_broker_start=true scope=both; System altered. SQL> quit Disconnected from Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production [oracle@host01 archivelog]$ dgmgrl DGMGRL for Linux: Release 12.2.0.1.0 - Production on Fri Mar 17 14:47:27 2017connect / Connected to "salesdb" Connected as SYSDG. DGMGRL> create configuration 'salesdb_dg' > as primary database is 'salesdb' > connect identifier is 'salesdb'; Configuration "salesdb_dg" created with primary database "salesdb"
DGMGRL> add database 'salesdb_sb' as connect identifier is 'salesdb_sb'; Database "salesdb_sb" added DGMGRL> enable configuration; Enabled.
Create the Standby Redo Log Files on the primary database
SQL> select member from v$logfile; MEMBER -------------------------------------------------------------------------------- /u03/app/oradata/salesdb/redo03.log /u03/app/oradata/salesdb/redo02.log /u03/app/oradata/salesdb/redo01.log SQL> select bytes/1048576 from v$log; BYTES/1048576 ------------- 200 200 200 SQL> alter database add standby logfile '/u03/app/oradata/salesdb/standy_redo1.log' size 200m; Database altered. SQL> alter database add standby logfile '/u03/app/oradata/salesdb/standy_redo2.log' size 200m; Database altered. SQL> alter database add standby logfile '/u03/app/oradata/salesdb/standy_redo3.log' size 200m; Database altered. SQL> alter database add standby logfile '/u03/app/oradata/salesdb/standy_redo4.log' size 200m; Database altered.
Create the Standby Redo Log Files on the standby database
DGMGRL> connect / Connected to "salesdb" Connected as SYSDG. DGMGRL> edit database 'salesdb_sb' set state='APPLY-OFF'; Succeeded. SQL> shutdown immediate; Database closed. Database dismounted. ORACLE instance shut down. SQL> startup mount; ORACLE instance started. Total System Global Area 1174405120 bytes Fixed Size 8619984 bytes Variable Size 436209712 bytes Database Buffers 721420288 bytes Redo Buffers 8155136 bytes Database mounted. SQL> alter database add standby logfile '/u03/app/oradata/salesdb/standy_redo1.log' size 200m; Database altered. SQL> alter database add standby logfile '/u03/app/oradata/salesdb/standy_redo2.log' size 200m; Database altered. SQL> alter database add standby logfile '/u03/app/oradata/salesdb/standy_redo3.log' size 200m; Database altered. SQL> alter database add standby logfile '/u03/app/oradata/salesdb/standy_redo4.log' size 200m; Database altered. SQL> alter database open; Database altered. SQL>
Verify the Data Guard Configuration
DGMGRL> edit database 'salesdb_sb' set state='APPLY-ON'; Succeeded. DGMGRL> show configuration; Configuration - salesdb_dg Protection Mode: MaxPerformance salesdb - Primary database salesdb_sb - Physical standby database Fast-Start Failover: DISABLED Configuration Status: SUCCESS (status updated 8 seconds ago)
Set the property StaticConnectIdentifier to prevent errors during switchover operations
Edit database ‘salesdb’ set property StaticConnectIdentifier= '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host01.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=salesdb_DGMGRL)(INSTANCE_NAME=salesdb)(SERVER=DEDICATED)))'; Edit database ‘salesdb_sb’ set property StaticConnectIdentifier=StaticConnectIdentifier= '(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=host02.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=salesdb_sb_DGMGRL)(INSTANCE_NAME=salesdb)(SERVER=DEDICATED)))';
Edit listener.ora on primary database host and add the lines shown below. Reload the listener.
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = salesdb_DGMGRL) (SID_NAME = salesdb) ) )
Edit listener.ora on standby database host and add the lines shown below. Reload the listener.
SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = salesdb_sb_DGMGRL) (SID_NAME = salesdb) ) )
Thanks Gavin for this wonderful post explaining this nice new feature of 12.2.
One doubt I have:
Say I have two ASM DGs on primary named +DATA_PRIM and +FRA_PRIM and the corresponding DGs on DR site are named +DATA_SB and +FRA_SB. So basically, we need to map the files with *_file_name_convert. If we take the above approach, where are we specifying this? Do we need to have the init.ora configured beforehand itself before we initiate the DBCA and in that init file we need to specify these type of convert parameters?
Thanks
Samrat
Thanks buddy, it’s very helpful.
Excellent!! What about Primary is rac and standby also you want rac what would change?
hi gaving ,
first of all i would like to thank you for providing awesome knowledge, i have learnt lots from your blog.
today i tried to implement new feature of 12cR2 creating standby using DBCA.
all looks good but everytime am getting stuck in between, beloaw are the detailed problem description
[oracle@db1 admin]$ dbca -silent -createDuplicateDB -gdbName prd -primaryDBConnectionString 192.168.198.21:1521/prd -sid prdstd -sysPassword Sharma12$ -createAsStandby -dbUniqueName prdstd
Listener config step
33% complete
Auxiliary instance creation
66% complete
RMAN duplicate
DBCA Operation failed.
Look at the log file “/opt/app/cfgtoollogs/dbca/prdstd/prd4.log” for further details.
below are the log file details.
[oracle@db1 admin]$ cat /opt/app/cfgtoollogs/dbca/prdstd/prd3.log
[ 2017-05-03 21:36:17.991 IST ] Listener config step
DBCA_PROGRESS : 33%
[ 2017-05-03 21:36:18.769 IST ] Auxiliary instance creation
DBCA_PROGRESS : 66%
[ 2017-05-03 21:36:29.178 IST ] RMAN duplicate
[ 2017-05-03 21:39:57.587 IST ] echo set off
RMAN> 2> 3> 4> 5> 6> 7> 8> 9> 10> 11> 12> 13> 14> 15> 16> 17> 18> 19> 20> 21> 22> 23> 24> 25> 26> 27> 28> 29> 30> 31> 32> 33> 34> 35> 36> 37> 38> 39> 40>
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting Duplicate Db at 03-MAY-17
using target database control file instead of recovery catalog
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=35 device type=DISK
current log archived
contents of Memory Script:
{
backup as copy reuse
targetfile ‘/opt/app/oracle/product/12cR2/dbs/orapwprd’ auxiliary format
‘/opt/app/oracle/product/12cR2/dbs/orapwprdstd’ ;
restore clone from service ‘192.168.198.21:1521/prd’ spfile to
‘/opt/app/oracle/product/12cR2/dbs/spfileprdstd.ora’;
sql clone “alter system set spfile= ”/opt/app/oracle/product/12cR2/dbs/spfileprdstd.ora””;
}
executing Memory Script
Starting backup at 03-MAY-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=50 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 05/03/2017 21:39:57
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 05/03/2017 21:39:57
ORA-17627: ORA-12154: TNS:could not resolve the connect identifier specified
ORA-17629: Cannot connect to the remote database server
RMAN>
echo set on
my listener.ora getting created at standby side with strange numbers everytime,
SID_LIST_LISTENER20170503213556 =
(SID_LIST =
(SID_DESC =
(SID_NAME = prdstd)
)
)
LISTENER =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1521))
)
LISTENER20170503213556 =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = db1)(PORT = 1522))
)
kindly help Gavin to resolve the issue
Oh wow…I didn’t know Oracle had this feature, I’m about to load up Virtual Box now and test this out. Extremely descriptive post. Will let you know how this works out this weekend.
Awe some Gavin. Even the switchover worked as expected. You Rock always 🙂