Managing Data Guard using DGMGRL

Make dg_broker_start= TRUE on both primary (ocmdb) and secondary (phyocm) databases.

SQL> SHOW PARAMETER dg
SQL> ALTER SYSTEM SET dg_broker_start=TRUE SCOPE=BOTH;

Configure listener.ora for DGMGRL

These ‘_DGMGRL’ entries in the listener.ora files on each system are required for Broker operations invoked using DGMGRL that require connecting remotely to a database to function normally. Without them, the Broker cannot connect to the target database when it is down, as is the case in a switchover or failover or any other operation that requires a restart of a database.

As non-default port, local listener is used for ocmdb and phyocm. Add SID_DESC for DGMGRL for both Primary and Secondary on non-default port listener SID_LIST as mentioned in BOLD

SID_LIST_LISTENER_OCM =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = ocmdb)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = ocmdb)
)
(SID_DESC =
(GLOBAL_DBNAME = ocmdb_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = ocmdb)
)
)

SID_LIST_LISTENER_PHYOCM =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = phyocm)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = phyocm)
)
(SID_DESC =
(GLOBAL_DBNAME = phyocm_DGMGRL)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(SID_NAME = phyocm)
)
)

Run “netmgr” and verify that the Oracle Net pre-requisites for using the DGMGRL command line interface have been satisfied.
Commands to get dgmgrl help

DGMGRL>help edit
DGMGRL>help remove
DGMGRL> help enable
DGMGRL> help start

DGMGRL Create Configuration

[oracle@ocm ~]$ dgmgrl

DGMGRL> connect sys@ocmdb

Create configuration with “ocmdb” as the Primary database. Add in the information about the physical standby database

DGMGRL> CREATE CONFIGURATION OCMDB AS PRIMARY DATABASE IS OCMDB
CONNECT IDENTIFIER IS OCMDB;
Configuration “ocmdb” created with primary database “ocmdb”

DGMGRL> ADD DATABASE phyocm AS CONNECT IDENTIFIER IS phyocm
MAINTAINED AS PHYSICAL;
Database “phyocm” added

DGMGRL> enable configuration;
Enabled.

DGMGRL> show configuration;
Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ocmdb – Primary database
phyocm – Physical standby database

Current status for “ocmdb”:
SUCCESS

DGMGRL> show database verbose ocmdb ;
Database
Name: ocmdb
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
ocmdb

Properties:
InitialConnectIdentifier = ‘ocmdb’
LogXptMode = ‘ASYNC’
Dependency = ”
DelayMins = ’0′
Binding = ‘OPTIONAL’
MaxFailure = ’0′
MaxConnections = ’1′
ReopenSecs = ’300′
NetTimeout = ’180′
LogShipping = ‘ON’
PreferredApplyInstance = ”
ApplyInstanceTimeout = ’0′
ApplyParallel = ‘AUTO’
StandbyFileManagement = ‘AUTO’
ArchiveLagTarget = ’0′
LogArchiveMaxProcesses = ’2′
LogArchiveMinSucceedDest = ’1′
DbFileNameConvert = ”
LogFileNameConvert = ‘phyocm, ocmdb’
FastStartFailoverTarget = ”
StatusReport = ‘(monitor)’
InconsistentProperties = ‘(monitor)’
InconsistentLogXptProps = ‘(monitor)’
SendQEntries = ‘(monitor)’
LogXptStatus = ‘(monitor)’
RecvQEntries = ‘(monitor)’
HostName = ‘ocm.prusolutions.com’
SidName = ‘ocmdb’
LocalListenerAddress = ‘(ADDRESS=(PROTOCOL=TCP)(HOST=ocm.prusolutions.com)(PORT=2010))’
StandbyArchiveLocation = ‘location=use_db_recovery_file_dest’
AlternateLocation = ”
LogArchiveTrace = ’0′
LogArchiveFormat = ‘%t_%s_%r.dbf’
LatestLog = ‘(monitor)’
TopWaitEvents = ‘(monitor)’

Current status for “ocmdb”:
SUCCESS

Managing Databases using DGMGRL

You can view and change database properties using dgmgrl

DGMGRL> show database ocmdb logxptmode;
LogXptMode = ‘ASYNC’

DGMGRL> show database ocmdb nettimeout;
NetTimeout = ’180′

DGMGRL> show database phyocm logxptmode;
LogXptMode = ‘ASYNC’

DGMGRL> show database phyocm nettimeout;
NetTimeout = ’180′

Using sqlplus connect to primary database and change value of log_archive_max_processes parameter

SQL> SHOW PARAMETER log_archive_max_processes;

The current value is two, increase it to five and again check database configuration from dgmgrl

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=5 SCOPE=MEMORY;

Use show configuration command to get Dataguard status

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ocmdb – Primary database
phyocm – Physical standby database

Current status for “ocmdb”:
Warning: ORA-16608: one or more databases have warnings

Use show database verbose ocmdb to get ocmdb database details

DGMGRL> show database verbose ocmdb ;

Database
Name: ocmdb
Role: PRIMARY
Enabled: YES
Intended State: ONLINE
Instance(s):
ocmdb

Current status for “ocmdb”:
Warning: ORA-16792: configuration property value is inconsistent with database setting

Get database inconsistent properties and fix the problem

DGMGRL> show database ocmdb inconsistentproperties;

INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
ocmdb LogArchiveMaxProcesses 5 2 2

SQL> ALTER SYSTEM SET LOG_ARCHIVE_MAX_PROCESSES=2 SCOPE=MEMORY;

DGMGRL> show database ocmdb inconsistentproperties;

INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ocmdb – Primary database
phyocm – Physical standby database

Current status for “ocmdb”:
SUCCESS

Open new terminals connect to ocmdb and phyocm using sqlplus and execute following sql

SQL>SELECT db_unique_name,
database_role,
protection_mode,
protection_level
FROM v$database;

Edit Database property using DG Broker

DGMGRL> edit database ocmdb set property LogArchiveMaxProcesses=10;
Property “logarchivemaxprocesses” updated

Change configuration set Protection Mode to MaxAvailability from MaxPerformance

When you try to change set Protection Mode to MaxAvailability, it will give error, as Standby database phyocm property logxptmode is ‘ASYNC’

DGMGRL> edit configuration set protection mode as maxavailability;
Error: ORA-16627: operation disallowed since no standby databases would remain to support protection mode
Failed.

Check database property logxptmode

DGMGRL> show database ocmdb logxptmode;
LogXptMode = ‘ASYNC’

DGMGRL> show database phyocm logxptmode;
LogXptMode = ‘ASYNC’

Change LogXptMode to SYNC of Physical Standby database PHYOCM ONLY and then change the protection mode

DGMGRL> edit database phyocm set property logxptmode=’SYNC’;
Property “logxptmode” updated

DGMGRL> show database phyocm logxptmode;
LogXptMode = ‘SYNC’

DGMGRL> edit configuration set protection mode as maxavailability;
Operation requires shutdown of instance “ocmdb” on database “ocmdb”
Shutting down instance “ocmdb”…
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “ocmdb” on database “ocmdb”
Starting instance “ocmdb”…
ORACLE instance started.
Database mounted.

Now change the property LogXptMode of ocmdb database to ‘SYNC’

DGMGRL> edit database ocmdb set property logxptmode=’SYNC’;
Property “logxptmode” updated

DGMGRL> show database ocmdb logxptmode;
LogXptMode = ‘SYNC’

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
ocmdb – Primary database
phyocm – Physical standby database

Current status for “ocmdb”:
SUCCESS

If due to some reason, the Dataguard Protection modes is not change to MaxAvailability using DGMGRL then do it from SQLPLUS

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

Open new terminals connect to ocmdb and phyocm using sqlplus and execute following sql

SQL>SELECT db_unique_name,
database_role,
protection_mode,
protection_level
FROM v$database;

Switch Over to Standby database phyocm

DGMGRL> switchover to phyocm;
Performing switchover NOW, please wait…
Operation requires shutdown of instance “ocmdb” on database “ocmdb”
Shutting down instance “ocmdb”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance “phyocm” on database “phyocm”
Shutting down instance “phyocm”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “ocmdb” on database “ocmdb”
Starting instance “ocmdb”…
ORACLE instance started.
Database mounted.
Operation requires startup of instance “phyocm” on database “phyocm”
Starting instance “phyocm”…
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is “phyocm”

If instances are down start them in mount mode, the primary database will automatically open

SQL> STARTUP MOUNT;

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
ocmdb – Physical standby database
phyocm – Primary database

Current status for “ocmdb”:
SUCCESS

Open new terminals connect to ocmdb and phyocm using sqlplus and execute following sql

SQL>SELECT db_unique_name,
database_role,
protection_mode,
protection_level
FROM v$database;

Switchover back to ocmdb

DGMGRL> switchover to ocmdb;

DGMGRL> show configuration;

SQL>SELECT db_unique_name,
database_role,
protection_mode,
protection_level
FROM v$database;

Failover to phyocm

From sqlplus shutdown, abort primary database

[oracle@ocm ~]$ echo $ORACLE_SID
ocmdb

SQL> SHUTDOWN abort;

Using dgmgrl connect to new phyocm

[oracle@ocm ~]$ dgmgrl sys/oracle@phyocm

DGMGRL> failover to phyocm;

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ocmdb – Physical standby database (disabled)
phyocm – Primary database

Current status for “ocmdb”:
SUCCESS

The Protection Mode automatically goes back to MaxPerformance, but the logxptmode properties remains ‘SYNC’

DGMGRL> show database ocmdb logxptmode;
LogXptMode = ‘SYNC’

DGMGRL> show database phyocm logxptmode;
LogXptMode = ‘SYNC’

Open a new terminal and set ORACLE_SID for phyocm

[oracle@ocm scripts]$ export ORACLE_SID=phyocm

SQL>SELECT db_unique_name,
database_role,
protection_mode,
protection_level
FROM v$database;

Connect to ocmdb and startup database in mount state

SQL> STARTUP mount;

Now reinstate database ocmdb as physical standby

DGMGRL> reinstate database ocmdb;
Reinstating database “ocmdb”, please wait…
Operation requires shutdown of instance “ocmdb” on database “ocmdb”
Shutting down instance “ocmdb”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “ocmdb” on database “ocmdb”
Starting instance “ocmdb”…
ORACLE instance started.
Database mounted.
Continuing to reinstate database “ocmdb” …
Reinstatement of database “ocmdb” succeeded

If reinstate fails for first time again start ocmdb database in mount state and reinstate, it will succeed in second or third attempt.

Switchover back to ocmdb to make ocmdb as Primary Database

DGMGRL> switchover to ocmdb;

Performing switchover NOW, please wait…
Operation requires shutdown of instance “phyocm” on database “phyocm”
Shutting down instance “phyocm”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance “ocmdb” on database “ocmdb”
Shutting down instance “ocmdb”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “phyocm” on database “phyocm”
Starting instance “phyocm”…
ORACLE instance started.
Database mounted.
Operation requires startup of instance “ocmdb” on database “ocmdb”
Starting instance “ocmdb”…
ORACLE instance started.
Database mounted.
Switchover succeeded, new primary is “ocmdb”

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
ocmdb – Primary database
phyocm – Physical standby database

Current status for “ocmdb”:
SUCCESS

Fast Start Failover

Connect to physical standby using dgmgrl

[oracle@ocm ~]$ dgmgrl sys/oracle@phyocm

Currently the Protection Mode is MaxPerformance, For fast start failover the Protection Mode should be MaxAvailability.

Anytime to change configuration to “MaxAvailability” make sure the primary database property is logxptmode is “ASYNC” and standby database “logxptmode” is “SYNC then only it can be changed to “MaxAvailability” otherwise it will fail. If maxavailability does not work with DGMGRL then you do it from SQLPLUS

ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE AVAILABILITY;

DGMGRL> show database phyocm logxptmode;
LogXptMode = ‘SYNC’

DGMGRL> show database ocmdb logxptmode;
LogXptMode = ‘SYNC’

DGMGRL> edit database ocmdb set property logxptmode=’ASYNC’;
Property “logxptmode” updated

DGMGRL> show database ocmdb logxptmode;
LogXptMode = ‘ASYNC’

DGMGRL> edit configuration set protection mode as maxavailability;

Operation requires shutdown of instance “ocmdb” on database “ocmdb”
Shutting down instance “ocmdb”…
Database closed.
Database dismounted.
ORACLE instance shut down.
Operation requires startup of instance “ocmdb” on database “ocmdb”
Starting instance “ocmdb”…
ORACLE instance started.
Database mounted.
Now make primary database “logxptmode” is “SYNC
DGMGRL> edit database ocmdb set property logxptmode=’SYNC’;
Property “logxptmode” updated

Make sure both primary and secondary databases have FLASHBACK ON

SQL> SELECT db_unique_name,
database_role,
open_mode,
flashback_on
FROM v$database;

Edit database property faststartfailovertarget and enable fast_start failover

DGMGRL> edit database phyocm set property faststartfailovertarget=ocmdb;
Property “faststartfailovertarget” updated

DGMGRL> edit database ocmdb set property faststartfailovertarget=phyocm;
Property “faststartfailovertarget” updated

DGMGRL> enable fast_start failover;
Enabled.

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
ocmdb – Primary database
phyocm – Physical standby database
– Fast-Start Failover target

Current status for “ocmdb”:
Warning: ORA-16608: one or more databases have warnings

DGMGRL> show database verbose ocmdb;

You will see warning in the end mentioned below

Current status for “ocmdb”:
Warning: ORA-16819: Fast-Start Failover observer not started

Connect to ocmdb using sqlplus and execute following commands

SQL> SELECT fs_failover_status, fs_failover_current_target, fs_failover_threshold
FROM v$database;

SQL> SELECT fs_failover_observer_present, fs_failover_observer_host
FROM v$database;

Now go to FSFO Observer window and start it >it is best recommended to use the ‘-logfile’ qualifier on the DGMGRL command to direct any of its output message to a file that can be examined in case of a problem. Additionally it is also suggested to specify unique data file name that Observer uses. This can be done by adding the ‘FILE=’ command to the ‘start observer’ command.

This will also allow you to start multiple Observers for multiple Primary/Standby pairs on the same Observer system using the same Oracle Home. In this example, since everything is on the same system, the log in was made using the current ORACLE_SID, which was setup to be the standby database phyocm. In a real setup neither of your databases should be on the Observer system so you would have to use the ‘@tnsname’ qualifier on the ‘sys/password’ login

[oracle@ocm ~]$ export ORACLE_SID=phyocm

[oracle@ocm ~]$ echo $ORACLE_SID
phyocm

[oracle@ocm ~]$ dgmgrl -logfile $ORACLE_HOME/rdbms/log/dgmgrlOCMDB.LOG sys/oracle
DGMGRL for Linux: Version 10.2.0.1.0 – Production

Welcome to DGMGRL, type “help” for information.
Connected.

DGMGRL> start observer file=’/u01/app/oracle/afoocmdb.dat’;

Make sure to correct observer file correctly having “afo.dat” in start

Connect to ocmdb using sqlplus and execute following commands
SQL> SELECT fs_failover_status, fs_failover_current_target, fs_failover_threshold
FROM v$database;

FS_FAILOVER_STATUS FS_FAILOVER_CURRENT_TARGET FS_FAILOVER_THRESHOLD
——————— —————————— ———————
SYNCHRONIZED phyocm 30

SQL> SELECT fs_failover_observer_present, fs_failover_observer_host
FROM v$database;

FS_FAIL FS_FAILOVER_OBSERVER_HOST
——- —————————————-
YES ocm.prusolutions.com

Open a new session using dgmgrl and connect to standby database phyocm

[oracle@ocm ~]$ dgmgrl sys/oracle@phyocm
DGMGRL> show configuration;

Current status for “ocmdb”:
SUCCESS

Connect to ocmdb database and shutdown abort;

SQL> SHUTDOWN abort;
ORACLE instance shut down.

$ps –ef | grep –i pmon

Kill pmon process to primary database ocmdb to failover

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
ocmdb – Physical standby database (disabled)
– Fast-Start Failover target
phyocm – Primary database

Current status for “ocmdb”:
Warning: ORA-16608: one or more databases have warnings

DGMGRL> show database ocmdb;

Database
Name: ocmdb
Role: PHYSICAL STANDBY
Enabled: NO
Intended State: ONLINE
Instance(s):
ocmdb

Current status for “ocmdb”:
Error: ORA-16661: the standby database needs to be reinstated

Connect to ocmdb instance and startup

[oracle@ocm ~]$ export ORACLE_SID=ocmdb

[oracle@ocm ~]$ sqlplus / as sysdba

SQL> STARTUP;
ORACLE instance started.

Total System Global Area 536870912 bytes
Fixed Size 1220460 bytes
Variable Size 209715348 bytes
Database Buffers 314572800 bytes
Redo Buffers 11362304 bytes
Database mounted.
ORA-16649: database will open after Data Guard broker has evaluated Fast-Start
Failover status

It will take few minutes to get database synchronized. Execute following sql on both primary and secondary.

SQL> SELECT db_unique_name, open_mode, database_role FROM v$database;

SQL> SELECT fs_failover_status, fs_failover_current_target, fs_failover_threshold
FROM v$database;

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: ENABLED
Databases:
ocmdb – Physical standby database
– Fast-Start Failover target
phyocm – Primary database

Current status for “ocmdb”:
SUCCESS

DGMGRL> STOP OBSERVER;
Done.

DGMGRL> disable fast_start failover;
Disabled.

DGMGRL> show configuration;

Configuration
Name: ocmdb
Enabled: YES
Protection Mode: MaxAvailability
Fast-Start Failover: DISABLED
Databases:
ocmdb – Physical standby database
phyocm – Primary database

Current status for “ocmdb”:
SUCCESS

Switchover back to ocmdb

DGMGRL> switchover to ocmdb;

Performing switchover NOW, please wait…
Operation requires shutdown of instance “phyocm” on database “phyocm”
Shutting down instance “phyocm”…
ORA-01109: database not open

Database dismounted.
ORACLE instance shut down.
Operation requires shutdown of instance “ocmdb” on database “ocmdb”
Shutting down instance “ocmdb”…
ORA-01109: database not open

ROHIT Blog

Sunday, November 6, 2011

Managing Data Guard using DGMGRL

Managing Data Guard using DGMGRL

No comments:

Post a Comment

Followers

Blog Archive

About Me