11gR2 DataGuard Build on Oracle Restart [With data guard Broker setup]

Posted: Monday in Oracle 11gR2

This is for the purpose of demonstration.

Note:
Standby is Physical.
ASM redundancy factor has not been taken into consideration.
Primary and Physical Standby databases reside on the same server on Oracle Restart Configuration.
Standby protection mode is set for Maximum Performance.

Primary database: mdiuat
ASM disk group: DATA, DATAEXT
Standby database: mdidr
ASM disk group: DATANORMAL

Step 1: Configure Init parameters

Parameters, only those relevant for the standby build is given here.

Primary:
*.db_unique_name=mdiuat
*.instance_name=mdiuat
*.db_name=’mdiuat’
*.fal_client=’MDIUAT’
*.fal_server=’MDIDR’
*.db_file_name_convert=’+DATANORMAL/mdidr’,’+DATA/mdiuat’
*.log_file_name_convert=’+DATANORMAL/mdidr’,’+DATA/mdiuat’
*.control_files=’+DATA/mdiuat/controlfile/current.271.729204761′,’+DATAEXT/mdiuat/controlfile/current.260.729204761′
*.log_archive_config=’DG_CONFIG=(mdiuat,mdidr)’
*.log_archive_dest_1=’LOCATION=+DATAEXT VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mdiuat’
*.log_archive_dest_2=’SERVICE=mdidr LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mdidr’
*.log_archive_dest_state_2=’ENABLE’
*.remote_login_passwordfile=’EXCLUSIVE’
*.sec_case_sensitive_logon=FALSE
*.service_names=’mdiuat’
*.standby_file_management=’AUTO’

Below 2 parameters are needed for configuring DG Broker after initial build has completed.

[dg_broker_config_file1 and dg_broker_config_file2 has default values that I do not plan to change ]
*.local_listener=’LISTENER’
*.dg_broker_start=TRUE

Standby parameters:
*.db_name=’mdiuat’
*.db_unique_name=’mdidr’
*.instance_name=’mdidr’
*.fal_client=’mdidr’
*.fal_server=’mdiuat’
*.control_files=’+DATANORMAL/mdidr/controlfile/current.261.734988671′
*.db_file_name_convert=’+DATA/mdiuat’,’+DATANORMAL/mdidr’,’+DATAHIGH/mdiuat’,’+DATANORMAL/mdidr’
*.log_file_name_convert=’+DATA/mdiuat’,’+DATANORMAL/mdidr’,’+DATAEXT/mdiuat’,’+DATANORMAL/mdidr’,’+DATANORMAL/mdiuat’,’+DATANORMAL/mdidr’
*.log_archive_config=’DG_CONFIG=(mdiuat,mdidr)’
*.log_archive_dest_1=’LOCATION=+DATANORMAL VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=mdidr’
*.log_archive_dest_2=’SERVICE=mdiuat LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=mdiuat’
*.remote_login_passwordfile=’EXCLUSIVE’
*.sec_case_sensitive_logon=FALSE
*.service_names=’mdidr’
*.standby_file_management=’AUTO’

Below 2 parameters needed for configuring DG Broker after initial build has completed.
*.dg_broker_start=TRUE
*.local_listener=’LISTENER_STDBY’

Step 2: Check FORCE LOGGING is enabled in primary database.

SQL> SELECT FORCE_LOGGING FROM V$DATABASE;

FOR

YES

If not, enable it:-
SQL> ALTER DATABASE FORCE LOGGING;

Step 3: Create standby redo log files on primary database

Creating standby log files on primary so that I’ll be ready for switchover as soon as standby database has been built.
The standby redo log must have at least one more redo log group than the redo log at the redo source database, for each redo thread at the redo source database. Each standby redo log file must be at least as large as the largest redo log file in the redo log of the redo source database.

In this configuration, primary & standby databases will has 2 logfile groups with each logfile sized at 50M. So there should be at least 3 standby log file groups.

ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 (‘+DATAEXT’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 (‘+DATAEXT’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 (‘+DATAEXT’) SIZE 50M;

Step 4. Take RMAN backup of primary:

RUN {
ALLOCATE CHANNEL ch1 DEVICE TYPE DISK FORMAT = ‘/backup/db_%d_t%t_s%s_p%p';
ALLOCATE CHANNEL ch2 DEVICE TYPE DISK FORMAT = ‘/backup/db_%d_t%t_s%s_p%p';
ALLOCATE CHANNEL ch3 DEVICE TYPE DISK FORMAT = ‘/backup/db_%d_t%t_s%s_p%p';
ALLOCATE CHANNEL ch4 DEVICE TYPE DISK FORMAT = ‘/backup/db_%d_t%t_s%s_p%p';
backup database tag = ‘Backup_ForDR’ ;
}

Step 5: Create Control file backup for standby

On primary database
$ rman target /
RMAN> BACKUP CURRENT CONTROLFILE FOR STANDBY FORMAT ‘/backup/%d_%s_%T_CONTROL_STDBY’ tag “CNTRL_STDBY_4DR”;
RMAN> LIST BACKUP OF CONTROLFILE;
RMAN> EXIT

Step 6: Backup Archived log files.

run
{
allocate channel ch1 type disk format ‘/backup/arch/arch_db_%d_%s_%p_%t';
allocate channel ch2 type disk format ‘/backup/arch/arch_db_%d_%s_%p_%t';
backup archivelog all;
}

All archive logs from the start to end of primary backup should be backed up using RMAN. Additionally, for keeping things as easy as possible, copy across all the backed up files to the exact same directory structure in the standby server. Since, I’m building the primary and standby on the same server, I do not need to move it anywhere.

Step 7: Check connectivity

Though basic standby set up and log transfer can be done using a single listener for primary as well as standby as they reside in the same server, I’m configuring 2 listeners to facilitate broker configuration.

Listener configuration:

/u01/11.2.0/grid/network/admin/listener.ora

LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = bnair.localdomain)(PORT = 1521))
)
)

SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = mdiuat)
(ORACLE_HOME = /u01/11.2.0/grid)
(SID_NAME = mdiuat)
)
(SID_DESC =
(GLOBAL_DBNAME = mdiuat_DGMGRL.world)
(ORACLE_HOME = /u01/11.2.0/grid)
(SID_NAME = mdiuat)
)
)

ADR_BASE_LISTENER = /u01/app/oracle
ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent

#
LISTENER_STDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bnair.localdomain)(PORT = 1522))
)
)

SID_LIST_LISTENER_STDBY =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = mdidr)
(ORACLE_HOME = /u01/11.2.0/grid)
(SID_NAME = mdidr)
)
(SID_DESC =
(GLOBAL_DBNAME = mdidr_DGMGRL.world)
(ORACLE_HOME = /u01/11.2.0/grid)
(SID_NAME = mdidr)
)
)

ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_STDBY=ON # line added by Agent

TNS Configuration

/u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

MDIUAT =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bnair.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
# (SERVICE_NAME = mdiuat.world)
(SID = mdiuat)
)
)

MDIDR =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bnair.localdomain)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
# (SERVICE_NAME = mdidr)
(SID = mdidr)
)
)

LISTENER =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bnair.localdomain)(PORT = 1521))
)

LISTENER_STDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = bnair.localdomain)(PORT = 1522))
)

Start the listeners and tnsping mdiuat & mdidr and confirm it’s working as expected.

Step 8: Configure password file for standby database

On standby host
$ cd ${ORACLE_HOME}/dbs
$ orapwd file=orapw${ORACLE_SID} password=

Step 9: Create standby database using RMAN Duplicate command

Export ORACLE_SID=mdidr

rman target sys/passwd@mdiuat auxiliary /

run {
allocate auxiliary channel c1 type disk;
allocate auxiliary channel c2 type disk;
allocate auxiliary channel c3 type disk;
allocate auxiliary channel c4 type disk;
allocate auxiliary channel c5 type disk;
allocate auxiliary channel c6 type disk;
DUPLICATE TARGET DATABASE FOR STANDBY;
}

Note: NOFILENAMECHECK should be used if you are creating an exact copy of primary on to a different server with the same paths (If LOG_FILE_NAME_CONVERT and DB_FILE_NAME_CONVERT are not used), else duplicate command will fail with:
RMAN-05001 auxiliary file name conflicts with a file used by the target database

Step 10. Create Standby redo log files in standby database

Standby log group numbers can be same or different from primary.

ALTER DATABASE ADD STANDBY LOGFILE GROUP 3 (‘+DATANORMAL’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 (‘+DATANORMAL’) SIZE 50M;
ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 (‘+DATANORMAL’) SIZE 50M;

Step 11.Create tempfile in standby database

Once the standby database is created; you will need to manually add a temporary file on standby if the primary already has a temporary tablespace. In case you add a new temporary tablespace in primary after standby creation, it’ll create that file in standby.

Opening the physical standby database for read-only access enables you to add a temporary file. Because adding a temporary file does not generate redo data, it is allowed for a database that is open for read-only access.

SQL> alter tablespace temp add tempfile ‘+DATANORMAL’ size 500M reuse;

Step 12: Put the Standby database into managed standby mode:

SQL> shutdown immediate

SQL> startup nomount

SQL> alter database mount standby database;

SQL> alter database recover managed standby database disconnect;

Step 13: Set up managed recovery

Start Redo Apply
In the foreground, issue the following SQL statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Step 14: Add standby database to Oracle Restart

As oracle home owner

$ srvctl add database -d mdidr -n mdiuat -r PHYSICAL_STANDBY -s mount -y auto -a DATA,DATANORMAL -o /u01/app/oracle/product/11.2.0/db_1

This will enable the cluster to mount the standby database and restart the managed recovery if the standby database happens to go down or the server is restarted for whatever reason.

Step 15: Configure Data Guard Broker

Before starting the broker configuration, make sure that you’ve added the init parameters specific to broker configuration listed on Step 1 and bounce the databases.

$ dgmgrl
DGMGRL for Linux: Version 11.2.0.1.0 – 64bit Production

Copyright (c) 2000, 2009, Oracle. All rights reserved.

Welcome to DGMGRL, type “help” for information.
DGMGRL> connect sys/passwd@mdiuat ;
Connected.
DGMGRL> show configuration
ORA-16532: Data Guard broker configuration does not exist

Configuration details cannot be determined by DGMGRL
DGMGRL> create configuration ‘PRI_DR_MDI’ as primary database is ‘mdiuat’ connect identifier is ‘mdiuat';
Configuration “PRI_DR_MDI” created with primary database “mdiuat”
DGMGRL> show configuration

Configuration – PRI_DR_MDI

Protection Mode: MaxPerformance
Databases:
mdiuat – Primary database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> add database ‘mdidr’ as connect identifier is mdidr maintained as physical;
Database “mdidr” added
DGMGRL> show configuration;

Configuration – PRI_DR_MDI

Protection Mode: MaxPerformance
Databases:
mdiuat – Primary database
mdidr – Physical standby database

Fast-Start Failover: DISABLED

Configuration Status:
DISABLED

DGMGRL> show database verbose mdiuat;

Database – mdiuat

Role: PRIMARY
Intended State: OFFLINE
Instance(s):
mdiuat

Properties:
DGConnectIdentifier = ‘mdiuat’
ObserverConnectIdentifier = ”
LogXptMode = ‘ASYNC’
DelayMins = ‘0’
Binding = ‘optional’
MaxFailure = ‘0’
MaxConnections = ‘1’
ReopenSecs = ‘300’
NetTimeout = ’30’
RedoCompression = ‘DISABLE’
LogShipping = ‘ON’
PreferredApplyInstance = ”
ApplyInstanceTimeout = ‘0’
ApplyParallel = ‘AUTO’
StandbyFileManagement = ‘AUTO’
ArchiveLagTarget = ‘0’
LogArchiveMaxProcesses = ‘4’
LogArchiveMinSucceedDest = ‘1’
DbFileNameConvert = ”
LogFileNameConvert = ”
FastStartFailoverTarget = ”
StatusReport = ‘(monitor)’
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
HostName = ‘bnair.localdomain’
SidName = ‘mdiuat’
StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bnair.localdomain)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=mdiuat_DGMGRL.world)(INSTANCE_NAME=mdiuat)(SERVER=DEDICATED)))’
StandbyArchiveLocation = ‘+DATAEXT’
AlternateLocation = ”
LogArchiveTrace = ‘0’
LogArchiveFormat = ‘%t_%s_%r.dbf’
TopWaitEvents = ‘(monitor)’

Database Status:
DISABLED

DGMGRL> show database verbose mdidr;

Database – mdidr

Role: PHYSICAL STANDBY
Intended State: OFFLINE
Transport Lag: (unknown)
Apply Lag: (unknown)
Real Time Query: OFF
Instance(s):
mdidr

Properties:
DGConnectIdentifier = ‘mdidr’
ObserverConnectIdentifier = ”
LogXptMode = ‘ASYNC’
DelayMins = ‘0’
Binding = ‘OPTIONAL’
MaxFailure = ‘0’
MaxConnections = ‘1’
ReopenSecs = ‘300’
NetTimeout = ’30’
RedoCompression = ‘DISABLE’
LogShipping = ‘ON’
PreferredApplyInstance = ”
ApplyInstanceTimeout = ‘0’
ApplyParallel = ‘AUTO’
StandbyFileManagement = ‘AUTO’
ArchiveLagTarget = ‘0’
LogArchiveMaxProcesses = ‘4’
LogArchiveMinSucceedDest = ‘1’
DbFileNameConvert = ‘+DATA/mdiuat, +DATANORMAL/mdidr, +DATAHIGH/mdiuat, +DATANORMAL/mdidr’
LogFileNameConvert = ‘+DATA/mdiuat, +DATANORMAL/mdidr, +DATAEXT/mdiuat, +DATANORMAL/mdidr, +DATANORMAL/mdiuat, +DATANORMAL/mdidr’
FastStartFailoverTarget = ”
StatusReport = ‘(monitor)’
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
HostName = ‘bnair.localdomain’
SidName = ‘mdidr’
StaticConnectIdentifier = ‘(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=bnair.localdomain)(PORT=1522))(CONNECT_DATA=(SERVICE_NAME=mdidr_DGMGRL.world)(INSTANCE_NAME=mdidr)(SERVER=DEDICATED)))’
StandbyArchiveLocation = ‘+DATANORMAL’
AlternateLocation = ”
LogArchiveTrace = ‘0’
LogArchiveFormat = ‘%t_%s_%r.dbf’
TopWaitEvents = ‘(monitor)’

Database Status:
DISABLED

DGMGRL> ENABLE CONFIGURATION;
Enabled.

Should you wish to enable broker Fast-Start Failover, this link would help:-

http://download.oracle.com/docs/cd/E11882_01/server.112/e17023/sofo.htm#i1027843

About these ads

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