How to perform switchover operation in Data guard
DG db_unique_name = 'STBY'
Configuration(any name) = 'DG_PRMY'
sys password = 'sys'
Pri conn stg = 'PRMY'
1. Set up init parameters on primary to enable broker
SQL> alter system set dg_broker_start=false;
System altered.
SQL> alter system set dg_broker_config_file1='/opt/dg_file/dr1PRMY.dat';
System altered.
SQL> alter system set dg_broker_config_file2='/opt/dg_file/dr2PRMY.dat' ;
System altered.
SQL> alter system set dg_broker_start=true;
System altered.
2. Verify if DMON process has started on all the instances of primary. Example:
$ ps -ef|grep dmon|grep -v grep
oracle 16190 1 0 08:53 ? 00:00:00 ora_dmon_PRMY
3. Set up init parameters on standby
SQL> alter system set dg_broker_start=false;
System altered.
SQL> alter system set dg_broker_config_file1='/opt/dg_file/dr1STBY.dat' ;
System altered.
SQL> alter system set dg_broker_config_file2='/opt/dg_file/dr2STBY.dat';
System altered.
SQL> alter system set dg_broker_start=true ;
System altered.
$ ps -ef|grep dmon|grep -v grep
oracle 16190 1 0 08:53 ? 00:00:00 ora_dmon_STBY
Primary and standby Databases must be started from spfile
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /ora_home/ora_home10g/dbs/spfi
lePRMY.ora
SQL> show parameter spfile;
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /ora_home/ora_home10g/dbs/spfi
leSTBY.ora
SQL>
dgmgrl command can be executed from primary or standby
5. DGMGRL Configuration
[oracle@linux1 ~]$ dgmgrl
DGMGRL for Linux: Version 10.2.0.4.0 - Production
Copyright (c) 2000, 2005, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
DGMGRL> connect sys/sys
Connected.
DGMGRL> CREATE CONFIGURATION 'DG_PRMY' AS PRIMARY DATABASE IS 'PRMY' CONNECT IDENTIFIER IS PRMY;
Configuration "DG_PRMY" created with primary database "PRMY"
DGMGRL> SHOW CONFIGURATION;
Configuration
Name: DG_PRMY
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
PRMY - Primary database
Current status for "DG_PRMY":
DISABLED
DGMGRL> ADD DATABASE 'STBY' AS CONNECT IDENTIFIER IS STBY MAINTAINED AS PHYSICAL;
Database "STBY" added
DGMGRL> SHOW CONFIGURATION;
Configuration
Name: DG_PRMY
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
PRMY - Primary database
STBY - Physical standby database
Current status for "DG_PRMY":
DISABLED
DGMGRL> ENABLE CONFIGURATION;
Enabled.
DGMGRL> SHOW CONFIGURATION;
Configuration
Name: DG_PRMY
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
PRMY - Primary database
STBY - Physical standby database
Current status for "DG_PRMY":
Warning: ORA-16610: command 'ENABLE DATABASE STBY' in progress
DGMGRL> SHOW CONFIGURATION;
Configuration
Name: DG_PRMY
Enabled: YES
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases:
PRMY - Primary database
STBY - Physical standby database
Current status for "DG_PRMY":
SUCCESS
DGMGRL>
6. Troubleshooting
Let us see some sample issues and their fix
Issue
DGMGRL> CONNECT sys/sys
ORA-16525: the Data Guard broker is not yet available
Fix
Set dg_broker_start=true
Issue
After enabling the configuration, on issuing SHOW CONFIGURATION, this error comes
Warning: ORA-16608: one or more sites have warnings
Fix
To know details of the error, you may check log which will be generated at bdump with naming as drc{DB_NAME}.log or there are various monitorable properties that can be used to query the database status and assist in further troubleshooting.
Few Monitorable properties to troubleshoot
DGMGRL> SHOW DATABASE 'PRMY' 'StatusReport';
DGMGRL> SHOW DATABASE 'PRMY' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'PRMY' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'PRMY' 'InconsistentLogXptProps';
DGMGRL> SHOW DATABASE 'STBY' 'StatusReport';
DGMGRL> SHOW DATABASE 'STBY' 'LogXptStatus';
DGMGRL> SHOW DATABASE 'STBY' 'InconsistentProperties';
DGMGRL> SHOW DATABASE 'STBY' 'InconsistentLogXptProps';
Issue
DGMGRL> SHOW DATABASE 'PRMY' 'StatusReport';
STATUS REPORT
INSTANCE_NAME SEVERITY ERROR_TEXT
PRMYR2 WARNING ORA-16714: The value of property ArchiveLagTarget is inconsistent with the database setting.
PRMYR2 WARNING ORA-16714: The value of property LogArchiveMaxProcesses is inconsistent with the database setting.
Issue
DGMGRL> SHOW DATABASE 'PRMY' 'InconsistentProperties';
INCONSISTENT PROPERTIES
INSTANCE_NAME PROPERTY_NAME MEMORY_VALUE SPFILE_VALUE BROKER_VALUE
PRMYR2 ArchiveLagTarget 0 0
PRMYR2 LogArchiveMaxProcesses 4 2 4
Example
DGMGRL> SHOW DATABASE 'PRMY' 'LogArchiveMaxProcesses';
LogArchiveMaxProcesses = '4'
Fix
DGMGRL> EDIT DATABASE 'PRMY' SET PROPERTY 'LogArchiveMaxProcesses'=2;
or
SQL> alter system set log_archive_max_processes=4 scope=spfile sid='*';
System altered.
DGMGRL> SHOW DATABASE 'PRMY' 'LogArchiveMaxProcesses';
LogArchiveMaxProcesses = '4'
More commands
DGMGRL> SHOW DATABASE VERBOSE 'PRMY'
This will show all property values in detail
DGMGRL> HELP;
List of all broker commands with usage help
Equivalent Broker Commands to 'ALTER SYSTEM'
SQL> alter database recover managed standby database cancel;
DGMGRL> edit database 'STBY' set state='LOG-APPLY-OFF';
SQL> alter database recover managed standby database disconnect from session;
DGMGRL> edit database 'STBY' set state='ONLINE';
SQL> alter system set log_archive_max_processes=4;
DGMGRL> edit database 'dbname' set property 'LogArchiveMaxProcesses'=4;
SQL> alter system set log_archive_dest_state_2='enable' scope=both;
DGMGRL> edit database 'STBY' set property 'LogShipping'='ON';
SQL> alter system set log_archive_dest_state_2='defer' scope=both;
DGMGRL> edit database 'STBY' set property 'LogShipping'='ON';
DGMGRL> edit database 'PRMY' set state='LOG-TRANSPORT-OFF';
This will defer all standby databases
No comments:
Post a Comment