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: , , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: