Oracle9i Data Guard Broker Release 1 (9.0.1) Part Number A88807-01 |
|
This chapter describes how to use the Data Guard command-line interface (CLI) to create, manage, and monitor a broker configuration, including updating or changing the states and properties. It also shows how to coordinate failover and switchover operations outside of the broker.
This chapter provides the following sections:
One of the prerequisites for using the CLI is that a primary database and a standby database must already exist. If you are using Data Guard Manager, you can skip the steps described in this section because the graphical user interface (GUI) can automatically create a standby database for you, including configuring the initialization parameter file and setting up the network.
Typically, you will create the standby database on a separate host from the primary database. However, Oracle9i Data Guard does not restrict you from configuring the primary and standby databases on the same host.
To create your Data Guard configuration, you must construct the standby database from backups of the primary database control files and datafiles, and then prepare it for recovery. The following list summarizes the steps:
See Also:
Oracle9i Data Guard Concepts and Administration for detailed information about creating standby databases. |
This section provides examples that create a broker configuration named Sales that includes a primary and standby site located in two different cities.
Each site in this configuration has a single database instance:
Creating a configuration requires that you connect to the primary database and then specify an Oracle Net service name to uniquely identify the primary site.
The following steps show how to create a configuration and add one physical standby site:
To start the CLI, enter DGMGRL
at the command line prompt on a system where Oracle9i Data Guard is installed:
% DGMGRL [options] DGMGRL for Solaris: Version 9.0.1.0.0 - Production. (c) Copyright 2001 Oracle Corporation. All rights reserved. Welcome to DGMGRL, type "help" for information. DGMGRL>
Before you specify any command (other than the HELP,
EXIT,
or QUIT
command), you must first connect to the primary database using the DGMGRL
CONNECT
command.
The account from which you connect to the database (SYS
in this example) must have SYSDBA
privileges on the primary and standby sites. You do not have to include AS SYSDBA
on the CONNECT
command because SYSDBA
is the default setting for this command.
The following examples show two variations of the CONNECT
command. Example 5-1 shows how to connect to the default database on the local system and Example 5-2 includes the Oracle Net service name (prmy) to make a connection to a database located on a remote system.
DGMGRL> CONNECT sys/change_on_install; Connected.
DGMGRL> CONNECT sys/change_on_install@prmy; Connected.
To create the broker configuration, you first define the configuration including the primary site, which in this case is called Boston. In a later command, you will add the standby site, San Francisco.
CREATE CONFIGURATION
command to create the Sales configuration and define the primary site, Boston. The Boston site hosts a database resource called Sales_db.
DGMGRL> CREATE CONFIGURATION 'Sales' AS PRIMARY SITE IS 'Boston' RESOURCE IS 'Sales_db' HOSTNAME IS 'System1' INSTANCE NAME IS 'bstn' SERVICE NAME IS 'primary' SITE IS MAINTAINED AS PHYSICAL;
The CLI returns the following information:
Configuration "Sales" added with primary site "Boston" Database resource "Sales_db" added.
Use the SHOW CONFIGURATION
command to display a brief summary of the configuration:
DGMGRL> SHOW CONFIGURATION;
The CLI returns the following information:
Configuration 'Sales' is Primary Site is 'Boston' Current status for "Sales": DISABLED
Use the SHOW CONFIGURATION VERBOSE
command to display a detailed summary of the configuration:
DGMGRL> SHOW CONFIGURATION VERBOSE;
The CLI returns the following information that shows the broker configuration currently contains only the primary site:
Configuration Name: 'Sales' Enabled: 'no' Default state: 'ONLINE' Intended state: 'OFFLINE' Number of sites: 1 Sites: Name: Boston
To add a standby database site to the Sales
configuration, use the CREATE SITE
command.
The following command defines the San Francisco location as a standby site hosting a database resource called reportingdb
, which is the standby database associated with the primary database called Salesdb.
DGMGRL> CREATE SITE 'San Francisco' RESOURCE IS 'reportingdb' HOSTNAME IS 'system2' INSTANCE NAME IS 'sfdb' SERVICE NAME IS 'dest2' SITE IS MAINTAINED AS PHYSICAL;
The CLI returns the following information:
Site "San Francisco" added to configuration. Database resource "reportingdb" added.
Then, use the SHOW SITE
VERBOSE
command to verify that the San Francisco site was added to the Sales
configuration:
DGMGRL> SHOW SITE VERBOSE 'San Francisco';
The CLI returns the following information:
Site Name: 'San Francisco' Hostname: 'system2' Instance name: 'sfdb' Service Name: 'dest2' Standby Type: 'physical' Number Built-in Processes: '2' Number Generic Processes: '0' Enabled: 'no' Required: 'yes' Default state: 'STANDBY' Intended state: 'OFFLINE' Number of resources: 1 Resources: Name: reportingdb (default) (verbose name='reportingdb')
After you create the configuration with the CLI, you must set the LogArchiveDestOptions
, ArchiveDestDependency
, and StandbyArchiveDest
critical properties before the configuration can be successfully enabled. If you do not set values for these properties, the Data Guard configuration will not function properly.
For example, the following SQL statements set the critical properties for the reportingdb
standby database resource:
DGMGRL> ALTER RESOURCE reportingdb SET PROPERTY LogArchiveDestOptions = ''; DGMGRL> ALTER RESOURCE reportingdb SET PROPERTY ArchiveDestDependency = ''; DGMGRL> ALTER RESOURCE reportingdb SET PROPERTY StandbyArchiveDest = '/archfs/arch/';
These properties map directly to the following database initialization parameters: LOG_ARCHIVE_DEST_
n
, the DEPENDENCY
attribute of the LOG_ARCHIVE_DEST_
n
parameter, and STANDBY_ARCHIVE_DEST
. You do not need to set critical properties for the primary database resource.
So far, the Sales configuration has been disabled, which means it is not under the control of the Data Guard monitor. When you finish configuring the sites and resources into a broker configuration and setting the critical database properties (described in Section 5.3), you must enable the configuration to allow the Data Guard monitor to manage the configuration, and so that you bring the primary and standby database systems online.
You can enable:
You can enable the entire configuration, including all of the sites and resources, with the following command:
DGMGRL> ENABLE CONFIGURATION; Enabled.
When you enable the configuration, its default state is online.
Use the SHOW
command to verify that the configuration and its resources were successfully enabled and brought online.
DGMGRL> SHOW CONFIGURATION VERBOSE;
The CLI returns the following information:
Configuration Name: 'Sales' Enabled: 'yes' Default state: 'ONLINE' Intended state: 'ONLINE' Number of sites: 2 Sites: Name: Boston Name: San Francisco
There may be situations in which you want to change the state or properties of the objects in a broker configuration to perform routine maintenance on one or more objects. You might also need to disable objects in a configuration when you want to transition the resources from a managed mode to a state of no longer being managed by the Data Guard monitor. For example, you might want to transition the standby site to the offline state to stop applying archived redo logs temporarily while you replace a faulty disk drive.
As you monitor the configuration, you might need to dynamically modify Data Guard properties. For example, you might choose to change one of the properties that controls log transport services and log apply services, or you might put the standby database in read-only mode temporarily to run some reporting applications.
The following sections show how to change the state or properties of the objects in the configuration:
Example 5-3 shows how to take all objects offline across the entire broker configuration.
DGMGRL> ALTER CONFIGURATION SET STATE = 'OFFLINE'; Succeeded.
Section 5.3 described how you must set critical database properties before the configuration can be enabled. You can also modify the values of critical and noncritical database properties at any time--you can edit database resource properties if the database is enabled, disabled, online, or offline.
Example 5-4 shows how to use the ALTER RESOURCE
command to change the LogArchiveTrace
property to the value 127 for the Sales_db
database resource
DGMGRL> ALTER RESOURCE 'Sales_db' ON SITE 'Boston'SET PROPERTY LogArchiveTrace='127';
The CLI returns the following message to indicate that the LogArchiveTrace
property was updated successfully in the Data Guard configuration file:
Property "LogArchiveTrace"
updated
If the configuration is currently disabled, the database resource does not use the new property value until you enable the broker configuration with theENABLE CONFIGURATION
command. You might want to use the standby database temporarily for reporting applications. To change the state of the standby database to read-only, enter the
ALTER RESOURCE
command as shown in Example 5-5.
DGMGRL> ALTER RESOURCE 'reportingdb' ON SITE 'San Francisco'SET STATE='READ-ONLY';
Remember that when you put the standby database in the read-only state, it stops log apply services from applying the archived redo logs to the standby database.
The CLI returns the following message to indicate that the command was successfully updated in the Data Guard configuration file:
Succeeded.
By default, a site is in the same state as the configuration. However, you can use the ALTER SITE
command (shown in Example 5-6) to restrict a site and its dependent database resources from going online when its parent configuration goes online.
DGMGRL> ALTER SITE 'Boston' SET STATE='Offline';
The CLI returns the following message to indicate that the command was successfully updated in the Data Guard configuration file:
Succeeded.
When you disable the broker configuration or any of its sites or resources, you are effectively removing your ability to use the CLI to manage and monitor the disabled object. However, disabling a broker configuration does not affect the actual operation of the configuration, its sites, or the database resources. For example, the log transport services and log apply services in the Data Guard configuration continue to function unchanged, but you cannot manage them with the CLI.
In addition, disabling an object does not remove or delete it from the Data Guard configuration file. You can re-enable your ability to use the CLI (or Data Guard Manager) to manage the object by entering the appropriate ENABLE
CONFIGURATION
, ENABLE SITE
, or ENABLE RESOURCE
command.
After you enter a DISABLE CONFIGURATION
, DISABLE SITE
, or DISABLE RESOURCE
command, the CLI returns the following message to indicate that the command successfully updated the Data Guard configuration file:
Disabled.
You must use the DISABLE CONFIGURATION
command to disable the entire broker configuration or to disable the primary site as shown in Example 5-7.
DGMGRL> DISABLE CONFIGURATION;
The only way to disable the primary site is to use the DISABLE CONFIGURATION
command; the DISABLE SITE
command only disables the standby site.
You use the DISABLE RESOURCE
command on the primary database or standby database when you no longer want to use the CLI to manage and monitor it. The DISABLE RESOURCE
command disables the database, but it does not stop or change actual database operations (for example, log apply services) occuring in the Data Guard configuration. The command shown in Example 5-8 disables management of the reportingdb
standby database.
DGMGRL> DISABLE RESOURCE reportingdb ON SITE 'San Francisco';
You use the DISABLE SITE
command when you no longer want to use the CLI to manage and monitor the standby site and the standby database resource.
You can explicitly disable a standby site to prevent it from being brought online when the rest of the configuration is brought online. Example 5-9 shows how to disable the San Francisco standby site.
DGMGRL> DISABLE SITE 'San Francisco';
When you use either the REMOVE CONFIGURATION
or REMOVE SITE
command, you effectively delete the configuration or standby site information from the Data Guard configuration file, removing the ability of the Data Guard monitor (DMON) process to manage the configuration or the standby site, respectively.
A remove operation does not remove or delete the actual Data Guard configuration, nor does it affect the operation of the actual Data Guard configuration, its sites, or the database resources.
Caution:
After you use the |
When you use the REMOVE SITE
command, you remove the standby site and standby database from management and monitoring by Data Guard monitor.
DGMGRL> REMOVE SITE 'San Francisco';
The CLI returns the following message to indicate that the command successfully removed the San Francisco site information from the Data Guard configuration file:
Removed site "San Francisco" from configuration.
Use the following command to remove the entire configuration from management and monitoring by Data Guard monitor:
DGMGRL> REMOVE CONFIGURATION;
The CLI returns the following message to indicate that the command successfully removed all of the configuration information from the Data Guard configuration file:
Removed configuration.
There may be occasions when you want to perform some operations outside of the CLI framework. For example, you might want to use the SQL statements that will perform a switchover operation.
When you remove a configuration, you are removing all of the objects in the configuration from management and monitoring by Data Guard monitor. It does not affect the underlying operations of the primary and standby databases, log transport services, or log apply services. Operations such as log shipping and log applying continue to run. However, these services are no longer manageable through the CLI.
DGMGRL> REMOVE CONFIGURATION; Removed configuration.
To exit from the CLI, enter the QUIT
or EXIT
command. For example:
DGMGRL> QUIT;
On both the primary and standby sites, stop the Data Guard monitor by setting the DRS_START=FALSE
parameter using the SQL ALTER SYSTEM
statement.
SQL> ALTER SYSTEM SET DRS_START=FALSE; System altered. SQL> SHOW PARAMETER DRS_START NAME TYPE VALUE ------------------------------------ drs_start boolean FALSE
Use the SQL statement ALTER DATABASE COMMIT TO SWITCHOVER
on both the primary and standby sites to perform a switchover operation.
See Also:
Oracle9i Data Guard Concepts and Administration for complete information about performing a switchover operation |
Follow the steps in Section 5.2 and Section 5.4 to create and enable the broker configuration based on the new primary and standby roles.
The scenario in this section demonstrates how to use SHOW
commands to view database monitorable properties, and identify and resolve a failure situation.
Assume that a failure occurred when the primary database attempted to transport an archived redo log to the standby site. To identify the failure, examine the LogXptStatus
(log transport status) property to see the error status of log transport services for the standby site. Use the following command at the DGMGRL
command-line prompt:
DGMGRL> SHOW RESOURCE VERBOSE 'Sales_db' LogXptStatus; LogXptStatus = 'San Francisco=ORA-16049: simulated error on archivelog write'
This LogXptStatus
property indicates that the error ORA-16049 has been returned during a write operation to the standby site, San Francisco.
To obtain additional information, use the SHOW LOG ALERT LATEST
command to view the database alert log on the primary site, Boston. For example:
DGMGRL> SHOW LOG ALERT LATEST ON SITE 'Boston'; DRSLOG LINE LOG
The command returns the following output:
-------------------------------------------------------------------------------- 7590 Transmitting activation ID 1332649663 (4f6e9ebf) 7591 ARCH: Completed archiving log# 1 thrd# 1 seq# 737 7592 Fri Jan 19 16:23:26 2001 7593 Completed checkpoint up to RBA [0x2e2.2.10], SCN: 0x0000.0000df8d 7594 Fri Jan 19 16:25:07 2001 7595 Beginning log switch checkpoint up to RBA [0x2e3.2.10], SCN:0x0000.0000df91 7596 Fri Jan 19 16:25:07 2001 7597 ARCH: Beginning to archive log# 2 thrd# 1 seq# 738 7598 Fri Jan 19 16:25:07 2001 7599 Thread 1 advanced to log sequence 739 7600 Current log# 1 seq# 739 mem# 0: /vobs/oracle/dbs/t_log1.f 7601 Fri Jan 19 16:25:07 2001 7602 ARC0: Beginning to archive log# 2 thrd# 1 seq# 738 7603 ARC0: Unable to archive log# 2 thrd# 1 seq# 738 7604 Log actively being archived by another process 7605 Fri Jan 19 16:25:07 2001 7606 Transmitting activation ID 1332649663 (4f6e9ebf) 7607 Transmitting activation ID 1332649663 (4f6e9ebf) 7608 ARCH: I/O error 16049 archiving log 2 to 'standby1' 7609 ARCH: Completed archiving log# 2 thrd# 1 seq# 738
In the example, lines 7603 through 7609 (in boldface type) show that the archiver process failed to transmit log file 738 to the standby archive destination identified as standby1. This is probably because an I/O error occured when archiving the redo log to the standby site.
To determine the severity of this failure and its effect on the integrity of the Data Guard configuration, use the following commands to examine the state of the archived redo logs from the perspective of both the primary and standby sites.
SendQEntries
(send queue entries) property on the primary database, Sales_db.
The SendQEntries
property shows the archive status of all of the log files on the primary site:
DGMGRL> SHOW RESOURCE VERBOSE 'Sales_db' SendQEntries; DGMGRL> PRIMARY_SEND_QUEUE SITE_NAME STATUS LOG_SEQ TIME_GENERATED TIME_COMPLETED San Francisco ARCHIVED 738 01/19/2001 16:23:23 01/19/2001 16:25:07 CURRENT 739 01/19/2001 16:25:07
The output shows that log 738 has been archived locally on the primary site and shipped to the San Francisco standby site.
SbyLogQueue
(standby log queue) property to view the archived redo logs that have been received by the standby site, but have not been applied to the standby database, reportingdb
:
DGMGRL> SHOW RESOURCE VERBOSE 'reportingdb' SbyLogQueue; DGMGRL> STANDBY_RECEIVE_QUEUE LOG_SEQ TIME_GENERATED TIME_COMPLETED 738 01/19/2001 16:23:23 01/19/2001 16:25:07
DGMGRL> SHOW RESOURCE VERBOSE 'Sales_db' SendQEntries; DGMGRL> PRIMARY_SEND_QUEUE SITE_NAME STATUS LOG_SEQ TIME_GENERATED TIME_COMPLETED San Francisco ARCHIVED 738 01/19/2001 16:23:23 01/19/2001 16:25:07 San Francisco ARCHIVED 740 01/19/2001 16:31:26 01/19/2001 16:32:33 CURRENT 745 01/19/2001 16:51:55 DGMGRL> SHOW RESOURCE VERBOSE 'reportingdb' SbyLogQueue; DGMGRL> STANDBY_RECEIVE_QUEUE LOG_SEQ TIME_GENERATED TIME_COMPLETED 738 01/19/2001 16:23:23 01/19/2001 16:25:07 739 01/19/2001 16:25:07 01/19/2001 16:31:26 740 01/19/2001 16:31:26 01/19/2001 16:32:33 741 01/19/2001 16:32:33 01/19/2001 16:36:28 742 01/19/2001 16:36:28 01/19/2001 16:41:36 743 01/19/2001 16:41:36 01/19/2001 16:46:41 744 01/19/2001 16:46:41 01/19/2001 16:51:55
As you can see, the problem is not resolving itself. The primary send queue contents shown by the SendQEntries
property show that logs 738 and 740 have not been successfully archived to the standby destination. The initial failure with transporting log 738 to the standby has caused log apply services on the standby database to fall behind the primary database. The output for the SbyLogQueue
property shows that the standby database receive queue grows with every new archived redo log sent by the primary database.
The failure resulted in only a portion of log 738 being written to the standby database destination.
The final step in this process is to examine the database alert log on the standby site to determine a possible solution to the problem. The following command allows you to view the latest entries in the database alert log for the standby site.
DGMGRL> SHOW LOG ALERT LATEST ON SITE 'San Francisco'; DRSLOG LINE LOG --------------------------------------------------------------------------------
7571 Fri Jan 19 16:21:15 2001 7572 Media Recovery Log /vobs/oracle/dbs/stdby_1_736.arc 7573 Media Recovery Waiting for thread 1 seq# 737 7574 Fri Jan 19 16:23:30 2001 7575 Media Recovery Log /vobs/oracle/dbs/stdby_1_737.arc 7576 Media Recovery Waiting for thread 1 seq# 738 7577 Fri Jan 19 16:25:15 2001 7578 Media Recovery Log /vobs/oracle/dbs/stdby_1_738.arc 7579 Fri Jan 19 16:25:15 2001 7580 Errors in file /vobs/oracle/rdbms/log/stdby1_mrp0_28842.trc: 7581 ORA-00311: cannot read header from archived log 7582 ORA-00334: archived log: '/vobs/oracle/dbs/stdby_1_738.arc' 7583 ORA-27091: skgfqio: unable to queue I/O 7584 ORA-27072: skgfdisp: I/O error 7585 SVR4 Error: 25: Inappropriate ioctl for device 7586 Additional information: 1 7587 MRP0: Background Media Recovery failed with error 311 7588 Recovery interrupted. 7589 Recovered data files restored to a consistent state at change 270314464672. 7590 MRP0: Background Media Recovery process is now terminated
This output from the database alert log shows that a fatal error reading log 738 (the corrupted log file) has resulted in the shutdown of the background Media Recovery Process. For this reason, no other archived redo logs have been applied to the standby database.
The solution is to manually copy logs 738 and 740 from the primary site to the standby site. Then, the next log file that is sent automatically to the standby site should trigger the application of all of the log files waiting in the standby queue to the standby database.
If this does not fix the problem, you might need to take the standby database resource offline and then put it back online again.
The ALTER RESOURCE
command in the following example changes the state of the reportingdb
database resource to offline
and then back into an online
state.
DGMGRL> ALTER RESOURCE 'reportingdb' ON SITE 'San Francisco' SET STATE='offline'; Succeeded. DGMGRL> ALTER RESOURCE 'reportingdb' ON SITE 'San Francisco' SET STATE='PHYSICAL-APPLY-ON'; Succeeded.
|
Copyright © 1996-2001, Oracle Corporation. All Rights Reserved. |
|