Creating standby database inc DG Broker and 12c changes
Posted by John Hallas on July 29, 2015
I thought I would refresh my knowledge of creating a standby database and at the same time include some DataGuard Broker configuration which also throws in some changes that came along with 12c
Overview
Database Name QUICKIE host server 1 ASM disk
Database Name STAN host server 2 ASM disk
Create a standby database STAN using ACTIVE DUPLICATE from the source database QUICKIE
QUICKIE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = QUICKIE) ) ) STAN = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STAN) )
server2 – listener.ora – note I have selected 1524 as that port is not currently in use and I do not want to interfere with any existing databases
LISTENERCLONE = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1524)) ) ) (SID_LIST = (SID_DESC = (GLOBAL_DBNAME = STAN) (ORACLE_HOME = /app/oracle/product/12.1.0.2/dbhome_1) (SID_NAME = STAN) ) ) SUBSCRIBE_FOR_NODE_DOWN_EVENT_LISTENER=OFF
server2 – tnsnames.ora
STAN = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1524)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STAN) ) ) LISTENERCLONE = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server2)(PORT = 1524)) ) (CONNECT_DATA = (SERVICE_NAME = STAN) )
- Start clone listener on server2
lsnrctl start LISTENERCLONE TNS Ping Utility for Linux: Version 12.1.0.2.0 - Production on 18-MAY-2015 09:19:27 Copyright (c) 1997, 2014, Oracle. All rights reserved. Used parameter files: /app/oracle/product/12.1.0.2/grid/network/admin/sqlnet.ora Used TNSNAMES adapter to resolve the alias Attempting to contact (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = server1)(PORT = 1524)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = STAN))) OK (10 msec)
- Create a pfile on server2 – $ORACLE_HOME/dbs/initSTAN.ora
db_unique_name=STAN compatible='12.1.0.2' db_name='QUICKIE’ local_listener='server2:1524'
- Create password file for STAN (use SOURCE DB SYS password)
orapwd file=orapwQUICKIE password=pI7KU4ai
or copy the source passwd file
Create standby logs on the primary database if they do not exist already:
alter database add standby logfile thread 1 group 4 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 50M; alter database add standby logfile thread 1 group 5 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 50M; alter database add standby logfile thread 1 group 6 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 50M; alter database add standby logfile thread 1 group 7 ('+DATA(ONLINELOG)','+FRA(ONLINELOG)') SIZE 50M;
- startup database in nomount on standby server
[oracle@server2][STAN] <strong>$sysdba</strong> SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 22 15:09:28 2015 Copyright (c) 1982, 2014, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount ORACLE instance started.
- login to RMAN console on source server via
rman target sys/pI7KU4ai auxiliary sys/pI7KU4ai@STAN< connected to target database: QUICKIE (DBID=4212874924) connected to auxiliary database: QUICKIE (not mounted)
- run restore script
run { allocate channel ch1 type disk; allocate channel ch2 type disk; allocate auxiliary channel aux1 type disk; allocate auxiliary channel aux2 type disk; duplicate target database for standby from active database SPFILE set db_unique_name='STAN' set audit_file_dest='/app/oracle/admin/STAN/adump' set cluster_database='FALSE' set control_files='+DATA/STAN/control01.ctl','+FRA/STAN/control02.ctl' set db_file_name_convert='QUICKIE','STAN' set local_listener='server2:1522' set log_file_name_convert='QUICKIE','STAN' set undo_tablespace='UNDOTBS1' set audit_trail='DB' nofilenamecheck; }
If you get the error RMAN-05537: DUPLICATE without TARGET connection when auxiliary instance is started with spfile cannot use SPFILE clause then either remove the SPFILE parameter from the RMAN duplicate line above or start the STAN database with a parameter file not a spfile.
In 12c it seems to create a spfile after starting with an init.ora file unless you use the syntax
startup nomount pfile=’/app/oracle/product/12.1.0.2/dbhome_1/dbs/spfileSTAN.ora’
I also got an error around DB_UNIQUE_NAME which is new in 12c. This is because the standby existed previously (as I re-tested my instructions for this document) and it creates a HAS /CRS resource for the database name
</pre> RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of Duplicate Db command at 07/22/2015 15:45:57 RMAN-05501: aborting duplication of target database RMAN-03015: error occurred in stored script Memory Script RMAN-03009: failure of sql command on clone_default channel at 07/22/2015 15:45:57 RMAN-11003: failure during parse/execution of SQL statement: alter system set db_unique_name = 'STAN' comment= '' scope=spfile ORA-32017: failure in updating SPFILE <pre>ORA-65500: could not modify DB_UNIQUE_NAME, resource exists
The fix is to remove that resource
srvctl remove database -d STAN
- Set parameters back on primary and standby and restart the databases to ensure that they are picked up
alter system reset log_archive_dest_1; alter system reset log_archive_dest_2; Set parameter on standby alter system set local_listener= ‘server2:1522’ scope=both;
- Dataguard broker configuration created at this point. Run from primary, although can be done on either
Sometimes it is best to stop/start the DG Broker if it has already been created – after deleting the dr files as well
alter system set dg_broker_start=FALSE; alter system set dg_broker_start=TRUE; dgmgrl / create configuration 'DGConfig1' as primary database is 'QUICKIE' connect identifier is QUICKIE; Configuration "DGConfig1" created with primary database "QUICKIE" add database 'STAN' as connect identifier is 'STAN' maintained as physical; Database "STAN" added edit database 'QUICKIE' set property 'DGConnectIdentifier'='QUICKIE'; edit database 'STAN' set property 'DGConnectIdentifier'='STAN'; The next 2 commands are required if you are not using Port 1521. Assuming you are not using Oracle Restart (which is now deprecated anyway) you also require the static entries to be defined in your listener.ora file STAN_DGMGRL and QUICKIE_DGMGRL in this case edit database 'STAN' set property 'StaticConnectIdentifier'='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server2)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=STAN_DGMGRL)(INSTANCE_NAME=STAN)(SERVER=DEDICATED)))'; edit database 'QUICKIE' set property 'StaticConnectIdentifier'='(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=server1)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=QUICKIE_DGMGRL)(INSTANCE_NAME=QUICKIE)(SERVER=DEDICATED)))'; ; The below entries seemed to be picked up by default but it is worth checking and correcting with the commands below if necessary. ; edit database 'QUICKIE' set property 'StandbyFileManagement'='AUTO'; edit database 'QUICKIE' set property 'DbFileNameConvert'='STAN,QUICKIE'; edit database 'QUICKIE' set property 'LogFileNameConvert'='STAN,QUICKIE'; edit database 'STAN' set property 'StandbyFileManagement'='AUTO'; edit database 'STAN' set property 'DbFileNameConvert'='QUICKIE,STAN'; edit database 'STAN' set property 'LogFileNameConvert'='QUICKIE,STAN';
Now to start the broker
enable configuration show configuration show database verbose 'QUICKIE' show database verbose 'STAN' validate database verbose 'QUICKIE' validate database verbose 'STAN'
Let’s try a switchover. You need to have SUCCESS as the final line of show configuration before any switchover will work. You need to be connected as SYS/password in DGMGRL, not using DGMGRL /. The latter uses OS authentication and the former is database authentication.
DGMGRL> switchover to 'STAN'; Performing switchover NOW, please wait... Operation requires a connection to instance "STAN" on database "STAN" Connecting to instance "STAN"... Connected as SYSDBA. New primary database "STAN" is opening... Oracle Clusterware is restarting database "QUICKIE" ... Switchover succeeded, new primary is "STAN" However when switching back the new primary QUICKIE opened but STAN hung New primary database "QUICKIE" is opening... Oracle Clusterware is restarting database "STAN" ... shut down instance "STAN" of database "STAN" start up instance "STAN" of database "STAN"
The database startup has hung and eventually times out. This is an issue around Oracle Restart which is now deprecated anyway
On the primary we can see a configuration for QUICKIE but there is not one on the standby for STAN
$srvctl config database -d STAN
PRCD-1120 : The resource for database STAN could not be found.
PRCR-1001 : Resource ora.stan.db does not exist
srvctl add database –d STAN –oraclehome ‘/app/oracle/product/12.1.0.2/dbhome_1’ –role ‘PHYSICAl_STANDBY’
Re-run the switchover and all should be well.
Creating standby database inc DG Broker and 12c changes | Dinesh Ram Kali. said
[…] https://jhdba.wordpress.com/2015/07/29/creating-standby-database-inc-dg-broker-and-12c-changes/ […]
Rman duplicating an oracle 11g database in active state on the same host | Bitbach's Blog said
[…] when auxiliary instance is started with spfile cannot use SPFILE clause underway the duplicate run. Creating standby database inc dg broker and 12c changes helped me out here in locating the cause of the problem. Thinking the problem through, yet another […]