Creating standby database by script while managed by Grid Control and Data Guard Broker

1.   Description

The Data Guard test environment will be created by the following plan:

Plans	Primary Database	Standby database
Cluster Instance	No	No
Database Version	Oracle Enterprise Edition 10.2.0.3	Oracle Enterprise Edition 10.2.0.3
OS Version	Red Hat Enterprise Linux AS release 4	Red Hat Enterprise Linux AS release 4
Protection Mode	Maximum Performance	N/A
Standby DB Type	N/A	Physical Standby
Flashback Logging	ON	ON

2.   Important Notes:

If you are using Data Guard Broker or OEM to manage your Data Guard environment, do NOT attempt to solve the Data Guard problem OUT OF Data Guard Broker or OEM interfaces, unless it is proved by comprehensive tests for all kinds of possible scenarios.

An Oracle official document explicitly specifies that these kinds of operations are not allowed:

“Note: When using the broker (with Enterprise Manager or the CLI), do not attempt to manually set the parameters that the broker controls. If you set them manually, either you render your configuration inoperable, or the broker simply resets the parameter to the setting it has recorded at the next opportunity. If you want to change a parameter value, you must change it by using one of the broker interfaces. ”

3.   Solution

You can create standby database by a cold backup of primary database, RMAN utility or just by Gird Control, but I think Grid Control is the most convenient way to do this. However, the following demonstration how to create a physical standby database manually and then get involved in Oracle Grid Control or Data Guard Broker.

3.1   Creating standby database by script or RMAN

The following two documents are well-described by Oracle in creating a physical database:

1)      Creating physical standby database by SQL scripts manually without using neither Data Guard Broker nor OEM

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239/create_ps.htm#i63561

2)      Creating physical standby database(duplicate database) with RMAN

http://download.oracle.com/docs/cd/B19306_01/backup.102/b14191/rcmdupdb.htm

3.2   Creating standby database by Grid Control

For creating standby database in Oracle Grid Control, you can just simply click on the button “Add Standby Database” in the Data Guard page of your primary database, followed by some more “mouse clicks”, and the creation will be completed automatically. It’s quite handy.

 

3.3   Creating standby database by script while managed by Grid Control and Data Guard Broker

In the following steps, we create a new standby from primary database by a cold backup followed by some scripts and set it to be managed by Data Guard Broker and Grid Control as well.

	Primary Database	Standby Database
1	Restore primary database from production database backup sets	N/A
2	Recreate primary database control file by CREATE CONTROLFILE SET DATABASE “new_database_name” RESETLOGS …	N/A
3	Recovery primary database if necessary	N/A
4	Open primary database with UPGRADE/Patch option if necessary  – STARTUP UPGRADE/@catupgrd.sql	N/A
5	Recreate primary database password file	N/A
6	Recreate primary database SPFILE	N/A
7	Enable primary database ARCHIVE LOG mode & FORCE LOGGING	N/A
8	Create standby control file by ALTER DATABASE CREATE STANDBY CONTROLFILE …	N/A
9	Add standby logfile(member size should be the same as primary online redo log file) if using Real Time Apply mechanism by ALTER DATABASE ADD STANDBY LOGFILE…	N/A
10		Create standby PFILE/SPFILE and password file
11	DB_NAME must be the SAME in BOTH primary and Standby database, but different for DB_UNIQUE_NAME
12	N/A	Restore from  primary database backup and mount standby database
13	N/A	Recovery primary db if necessary
14	Configure Data Guard on both primary and standby database server by ALTER SYSTEM SET DG_BROKER_START=TRUE SCOPE=BOTH;
15	Configure Network setting in listener.ora and tnsnames.ora	Configure Network setting in listener.ora and tnsnames.ora
	N/A	Add standby database Oracle Grid Control
16	Data Guard configuration on primary database by DGMMGRL:DGMGRL> CONNECT SYS DGMGRL> CREATE CONFIGURATION ‘DGTest’ AS PRIMARY DATABASE IS ‘DGPRI’ CONNECT IDENTIFIER IS DGPRI; DGMGRL> SHOW CONFIGURATION; DGMGRL> ADD DATABASE ‘DGSTB’ AS CONNECT IDENTIFIER IS DGSTB MAINTAINED AS PHYSICAL; DGMGRL> EDIT DATABASE ‘DGPRI’ SET PROPERTY ‘StandbyArchiveLocation’=’/u01/app/oracle/flash_recovery_area/’; DGMGRL> EDIT DATABASE ‘DGSTB’ SET PROPERTY ‘StandbyArchiveLocation’=’/u01/app/oracle/flash_recovery_area/’; DGMGRL> EDIT DATABASE ‘DGSTB’ SET PROPERTY ‘LogXptMode’=’ASYNC’; DGMGRL> ENABLE DATABASE ‘DGSTB’; DGMGRL> ENABLE CONFIGURATION; DGMGRL> SHOW DATABASE VERBOSE ‘DGPRI’; DGMGRL> SHOW DATABASE VERBOSE ‘DGSTB’;	N/A
17	Switchover with Oracle Grid Control in Enterprise Manager or DGMGRL
18		Add standby logfile
19	Switchover with Oracle Grid Control in Enterprise Manager or DGMGRL
	Switch logfile by ALTER SYSTEM SWITCH LOGFILE;	N/A
20	Check processes status by SELECT PROCESS, STATUS FROM V$MANAGED_STANDBY;

Tags: Data Guard, Data Guard Broker, DGMGRL, Enterprise Manager, Gird Control, RMAN