Creating standby database inc DG Broker and 12c changes

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)
)

 

1. 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)

 

3. 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'

 

 

4. 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;

 

 

5. 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.

 

6. 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)

 

7. 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

 

 

8. 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;

 

 

9. 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.

 

 

 

 

 

 

 

 

 

Advertisements