Error ora 16737 the redo transport service for standby database has an error

Hi All,

Here are the configuration details :-

Primary DB Name — Standby ( One node Oracle Restart)

Standby DB name — rac, two nodes RAC namely rac1,rac2

tnsnames.ora :-

Listener.ora in Standby :-

Listener.ora in RAC nodes(rac1,rac2)

I am getting error for logshipping to standby as ORA16737 : the redo transport service has error

Can anyone help ?

Best Regards,

Sandy                  

DBA Admin

My intention is to help other DBAs to learn and understand the concepts in a easy way.

Saturday, 6 August 2016

ORA-16737: the redo transport service for standby database ‘BHU_B» has an error

ORA-16737: the redo transport service for standby database «BHU_B» has an error

Error message
DGMGRL> show database verbose ‘BHU_A’;
Database – BHU_A
Role: PRIMARY
Intended State: TRANSPORT-ON
Instance(s):
BHU_1
BHU_2
BHU_3
Error: ORA-16737: the redo transport service for standby database «BHU_B» has an error
Database Warning(s):
ORA-16629: database reports a different protection level from the protection mode

#2
CHECK: whether you are getting any in the archive location
SQL>select dest_id,status,error from v$archive_dest;

#4
CHECK: Check whether standby redo log are configured in the primary &standby database properly, it includes size & accessibility

#5
CHECK: Check parameters are configured properly; some times instance parameters have a different value.
Ex: some common parameter will have different value for each instance in the cluster database. You need to check on the primary &standby cluster database environment.

#6
Check whether maximum Availability is enabled, when you have LogXptMode is synchronization
SYMP:
ORA-16629: database reports a different protection level from the protection mode

In DG Broker
DGMGRL> EDIT CONFIGURATION SET PROTECTION MODE AS MAXAVAILABILITY;
Succeeded.

#7 check your password with the setting
A)
1) check for the «sec_case_sensitive_logon» parameter.
2) if the problem exist, create the password file with ignorecase option in the orapwd password creation.
3) after recreating the password, restart both the primary & standby database.

B)
DGMGRL> show database verbose ‘BHU_A’ LogXptStatus;
LOG TRANSPORT STATUS
PRIMARY_INSTANCE_NAME STANDBY_DATABASE_NAME STATUS
BHU1_1 BHU1_B
BHU1_2 BHU1_B ORA-16191: Primary log shipping client not logged on standby

Источник

Log Shipping Issue in my dataguard environment- ORA16737 : the redo transport service has error

Here are the configuration details :-

Primary DB Name — Standby ( One node Oracle Restart)

Standby DB name — rac, two nodes RAC namely rac1,rac2

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

Welcome to DGMGRL, type «help» for information.

DGMGRL> show configuration;

Protection Mode: MaxPerformance

standby — Primary database

Error: ORA-16778: redo transport error for one or more databases

rac — Physical standby database

Fast-Start Failover: DISABLED

(ADDRESS = (PROTOCOL = TCP)(HOST = racattack-cluster-scan)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = racattack2-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = racattack1-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.78.58)(PORT = 1521))

Listener.ora in Standby :-

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.78.58)(PORT = 1521))

Listener.ora in RAC nodes(rac1,rac2)

(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER))

(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN3))

(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN2))

(ADDRESS = (PROTOCOL = IPC)(KEY = LISTENER_SCAN1))

I am getting error for logshipping to standby as ORA16737 : the redo transport service has error

Can anyone help ?

Answers

DGMGRL> show configuration;

Protection Mode: MaxPerformance

standby — Primary database

Error: ORA-16778: redo transport error for one or more databases

rac — Physical standby database

Fast-Start Failover: DISABLED

Please share the output of

DGMGRL> show database rac statusreport

Post last 100-200 lines of broker log from primary and standby.

Check the database alert log for additional information related to error.

Do you see LNS process running in primary

select process,status from v$managed_standby;

Run this on your PRIMARY DB.

Run the command below on both primary and standby

What happens if you try to connect with.

Is the name of your standby db really STANDBY? If no, why do you have it in the listener?

Also, can you run the command below and post back.

DGMGRL> show database rac statusreport

INSTANCE_NAME SEVERITY ERROR_TEXT

DGMGRL> show database standby statusreport

INSTANCE_NAME SEVERITY ERROR_TEXT

standby ERROR ORA-16737: the redo transport service for standby database ‘rac’ has an error

Redo transport problem detected: redo transport for database rac has the following error:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Data Guard Broker Status Summary:

Type Name Severity Status

Configuration haconfig Warning ORA-16607

Primary Database standby Error ORA-16778

Physical Standby Database rac Success ORA-00000

Redo transport problem detected: redo transport for database rac has the following error:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Data Guard Broker Status Summary:

Type Name Severity Status

Configuration haconfig Warning ORA-16607

Primary Database standby Error ORA-16778

Physical Standby Database rac Success ORA-00000

Redo transport problem detected: redo transport for database rac has the following error:

ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Data Guard Broker Status Summary:

Type Name Severity Status

Configuration haconfig Warning ORA-16607

Primary Database standby Error ORA-16778

Physical Standby Database rac Success ORA-00000

>> Starting Data Guard Broker bootstrap show database standby

Intended State: TRANSPORT-ON

Error: ORA-16737: the redo transport service for standby database «rac» has an error

DGMGRL> show database rac

Role: PHYSICAL STANDBY

Intended State: APPLY-ON

Transport Lag: (unknown)

Apply Lag: (unknown)

Real Time Query: ON

rac1 (apply instance)

[[email protected] admin]$ sqlplus sys/[email protected] ( While trying to connect from primary to standby)

SQL*Plus: Release 11.2.0.3.0 Production on Mon Feb 20 08:15:58 2017

Copyright (c) 1982, 2011, Oracle. All rights reserved.

ORA-12514: TNS:listener does not currently know of service requested in connect

LSNRCTL for Linux: Version 11.2.0.3.0 — Production on 20-FEB-2017 08:18:23

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 — Production

Start Date 20-FEB-2017 07:22:47

Uptime 0 days 0 hr. 55 min. 37 sec

Trace Level off

Security ON: Local OS Authentication

Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/racattackstandby/listener/alert/log.xml

Listening Endpoints Summary.

Service «+ASM» has 1 instance(s).

Instance «+ASM», status READY, has 1 handler(s) for this service.

Service «standby» has 1 instance(s).

Instance «standby», status UNKNOWN, has 1 handler(s) for this service.

Service «standby.racattack» has 1 instance(s).

Instance «standby», status READY, has 1 handler(s) for this service.

Service «standby_DGB.racattack» has 1 instance(s).

Instance «standby», status READY, has 1 handler(s) for this service.

The command completed successfully

LSNRCTL for Linux: Version 11.2.0.3.0 — Production on 20-FEB-2017 08:19:20

Copyright (c) 1991, 2011, Oracle. All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 — Production

Start Date 20-FEB-2017 07:26:24

Uptime 0 days 0 hr. 53 min. 0 sec

Trace Level off

Security ON: Local OS Authentication

Listener Parameter File /u01/app/11.2.0/grid/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/racattack1/listener/alert/log.xml

Listening Endpoints Summary.

Service «+ASM» has 1 instance(s).

Instance «+ASM1», status READY, has 1 handler(s) for this service.

Service «rac.racattack» has 1 instance(s).

Instance «rac1», status READY, has 1 handler(s) for this service.

Service «rac1_DGMGRL» has 1 instance(s).

Instance «rac», status UNKNOWN, has 1 handler(s) for this service.

Service «racXDB.racattack» has 1 instance(s).

Instance «rac1», status READY, has 1 handler(s) for this service.

Service «rac_DGB.racattack» has 1 instance(s).

Instance «rac1», status READY, has 1 handler(s) for this service.

The command completed successfully

I am very confused,can anyone kindly assists..

Источник

Log Shipping Issue in my dataguard environment- ORA16737 : the redo transport service has error

Answers

Your service_name at instance is standby and you have domain name racattack.
hence try adjust your TNS service as

Sorry, i have replied to @Gbenga Ajakaye

Thanks a lot , it solved my issue, just one more doubts clarifications is needed as why domain name should be part of service_name ?Also what will be ideal value of log_archive_dest 2 if primary is standby

NAME TYPE VALUE

log_archive_dest_2 string service=»rac», LGWR ASYNC NOAF

FIRM delay=0 optional compress

ion=disable max_failure=0 max_

connections=1 reopen=300 db_un

NAME TYPE VALUE

log_archive_dest_2 string service=»rac1″, LGWR ASYNC NOAF

FIRM delay=0 optional compress

ion=disable max_failure=0 max_

connections=1 reopen=300 db_un

(ADDRESS = (PROTOCOL = TCP)(HOST = racattack-cluster-scan)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = racattack2-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = racattack1-vip)(PORT = 1521))

(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.78.58)(PORT = 1521))

Thanks a lot in advance.

The «Service» in the log_archive_dest_2 is the TNS alias name and not the REAL service_name configured for the database.

In the below case, if service_name=rac.racattack, then «rac» is the NET ALIAS name and this is what you specify in the «log_archive_dest_2».

(ADDRESS = (PROTOCOL = TCP)(HOST = racattack-cluster-scan)(PORT = 1521))

I understand your point , but why can’t we give service name as ‘rac1’ in log_archive_dest_2, below tnsnames also have the entry as follows —

(ADDRESS = (PROTOCOL = TCP)(HOST = racattack1-vip)(PORT = 1521))

Why do we need to include db_domain(Domain name) in TNS service name portion , please elaborate a bit on it.

The service name format should be always like this (GLOBAL_DBNAME=db_unique_name_DGMGRL.db_domain)

If you do not have broker then you can remove the DGMGRL, that’s all. So if you have domain name configured then you must include.

Источник

6 Scenarios Using the DGMGRL Command-Line Interface

This chapter describes the prerequisites for getting started using the Data Guard command-line interface (DGMGRL). It also describes scenarios that demonstrate how to use DGMGRL to create, manage, and monitor a broker configuration.

This chapter contains the following sections:

6.1 Prerequisites for Getting Started

One of the prerequisites for using DGMGRL is that a primary database and any standby databases must already exist. The DG_BROKER_START initialization parameter must be set to TRUE for all databases in the configuration. You must use a server parameter file with the broker (see Section 2.1 and Section 7.1.3).

Convert the initialization parameter files (PFILE) on both primary and standby databases into server parameter files (SPFILE), if necessary. Use the following SQL*Plus command:

If an instance was not started with a server parameter file, then you must shut down the instance and restart it using the server parameter file.

After starting the Oracle instance, set the DG_BROKER_START=TRUE initialization parameter using the SQL ALTER SYSTEM statement. The parameter value will be saved in the server parameter file. The next time you start the Oracle instance, the broker is started automatically, and you do not need to issue the SQL ALTER SYSTEM statement again.

Oracle Database Administrator’s Guide for detailed information about creating server parameter files

The following assumptions are made for the scenarios in this chapter:

TCP/IP is used to connect to primary and standby databases.

The standby database has been created from backups of the primary database control files and datafiles as described in the Oracle Data Guard Concepts and Administration .

The scenarios in this chapter assume the following broker configuration:

The configuration name is DRSolution .

The database unique name ( DB_UNIQUE_NAME ) of the primary database is North_Sales .

The database unique name ( DB_UNIQUE_NAME ) of the remote standby database is South_Sales .

The protection mode is maximum performance mode.

There are standby redo log files configured for both the primary and standby database. The transport mode for both databases is ASYNC .

The standby database is a physical standby database.

6.2 Scenario 1: Creating a Configuration

This section provides examples that create a broker configuration named
DRSolution that includes a primary and standby database named North_Sales and South_Sales .

The following steps show how to create a configuration and add one physical standby database.

Step 1 Invoke DGMGRL.

To start DGMGRL, enter dgmgrl at the command-line prompt on a system where Oracle Data Guard is installed:

The DGMGRL prompt is displayed:

Step 2 Connect to the primary database.

Before you specify any command (other than the HELP, EXIT, or QUIT ), 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 databases.

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 6-1 shows how to connect to the default database on the local system, and Example 6-2 includes the Oracle Net Services connect identifier
( North_Sales.example.com ) to make a connection to a database located on a remote system. In both examples, you are prompted for a password.

Example 6-1 Connecting to the Primary Database on the Local System

Example 6-2 Connecting to the Primary Database on a Remote System

To create the broker configuration, you first define the configuration including a profile for the primary database, which in this case is called North_Sales . In a later command, you will add a profile for the standby database, South_Sales .

The names for the primary and standby databases must match their database unique names. Fetch these from their DB_UNIQUE_NAME initialization parameter as follows:

Use the CREATE CONFIGURATION command to create the DRSolution configuration and define the primary database, North_Sales :

DGMGRL returns the following information:

The name North_Sales is the value of this database’s DB_UNIQUE_NAME initialization parameter.

Step 4 Show the configuration information.

Use the SHOW CONFIGURATION command to display a brief summary of the configuration:

DGMGRL returns the following information:

Step 5 Add a standby database to the configuration.

To add a standby database to the DRSolution configuration, use the ADD DATABASE command to create a broker configuration profile for the standby database.

The following command defines South_Sales as a standby database, which is the standby database associated with the primary database called North_Sales :

DGMGRL returns the following information:

The name South_Sales is the value of the database’s DB_UNIQUE_NAME initialization parameter.

Use the SHOW CONFIGURATION command to verify that the South_Sales database was added to the DRSolution configuration:

DGMGRL returns the following information:

6.3 Scenario 2: Setting Database Properties

After you create the configuration with DGMGRL, you can set database properties at any time. For example, the following statements set the LogArchiveFormat and StandbyArchiveLocation database properties for the South_Sales standby database:

Use the SHOW DATABASE VERBOSE command to view all properties and their values for a database. The following example shows the properties for the South_Sales database.

If broker management of the database is enabled, setting a database property value causes the underlying parameter value to be changed in the corresponding database, and the value for the changed parameter is reflected in the server parameter file. Thus, if the database is shut down and restarted outside of Oracle Enterprise Manager and DGMGRL (such as from the SQL*Plus interface), the database uses the new parameter values from the updated server parameter file when it starts. However, you should not make changes to the redo transport services initialization parameters through SQL statements. Doing so will cause an inconsistency between the database and the broker.

The database properties are typically displayed in mixed-case (for example, LogArchiveFormat ) typeface to help you visually differentiate database properties (from the corresponding initialization parameter, SQL statement, or PL/SQL procedure), which are typically documented in UPPERCASE typeface. However, the commands to manage properties are not case sensitive; you can issue commands in uppercase, lowercase, or mixed-case.

You can change a property if the database is enabled or disabled. However, if the database is disabled when you change a property, the change does not take effect until the database is enabled.

6.4 Scenario 3: Enabling the Configuration and Databases

So far, the DRSolution configuration is disabled, which means it is not under the control of the Data Guard broker. When you finish configuring the databases into a broker configuration and setting any necessary database properties, you must enable the configuration to allow the Data Guard broker to manage it.

The entire configuration, including all of its databases

A standby database

Enable the entire configuration.

You can enable the entire configuration, including all of the databases, with the following command:

Show the configuration.

Use the SHOW command to verify that the configuration and its databases were successfully enabled:

DGMGRL returns the following information:

Enable the database.

This step is unnecessary except if the standby database was previously disabled with the DISABLE DATABASE command. Normally, enabling the configuration also enables the standby database.

Show the database.

6.5 Scenario 4: Setting the Configuration Protection Mode

You can change the protection mode of the configuration at any time. However, it is best if you do this when there is no activity occurring in the configuration if you are moving to the maximum protection or maximum availability modes.

If you change the protection mode from maximum performance mode to maximum protection mode, the broker automatically restarts the primary database. If you wish to avoid restarting the database, first change the protection mode to maximum availability mode and then change the protection mode to maximum protection mode.

A restart of the primary database is not required when changing the protection mode from:

maximum performance to maximum availability

maximum availability to maximum protection

This scenario sets the protection mode of the configuration to the MAXAVAILABILITY mode. Note that this protection mode requires that there be at least one standby database configured to use standby redo log files, with its LogXptMode database property set to SYNC .

Step 1 Configure standby redo log files, if necessary.

Because you will be setting the protection mode to the MAXAVAILABILITY mode, it is important to ensure that sufficient standby redo log files are configured on the standby database.

Step 2 Set the LogXptMode database property appropriately.

Use the EDIT DATABASE (property) command on the standby database to set the redo transport service that corresponds to the protection mode you plan to set. If the protection mode to be set is MAXAVAILABILITY , it is required that the redo transport service of at least one standby database is set to SYNC . For example:

The broker will not allow this command to succeed unless the standby database is configured with standby redo log files in the configuration.

Step 3 Change the overall protection mode for the configuration.

Use the EDIT CONFIGURATION command to upgrade the broker configuration to the MAXAVAILABILITY protection mode:

If the configuration is disabled when you enter this command, the actual protection mode change is not applied until you enable the configuration with the ENABLE CONFIGURATION command. The broker will not allow you to enable the configuration if it does not find a standby database in the configuration that can support the requirements of the protection mode.

Step 4 Verify the protection mode has changed.

Use the SHOW CONFIGURATION command to display the current protection mode for the configuration:

6.6 Scenario 5: Enabling Fast-Start Failover and Starting the Observer

You can enable fast-start failover from any site, including the observer site, while connected to any database in the broker configuration. Enabling fast-start failover does not trigger a failover. Instead, it allows the observer that is monitoring the configuration to initiate a fast-start failover if conditions warrant a failover.

Fast-start failover can be enabled in configurations operating in either maximum performance or maximum availability protection mode. This section describes the steps to enable fast-start failover and start the observer where the configuration protection mode is set to maximum availability mode.

Step 1 Ensure standby redo logs are configured on the primary and target standby databases.

Standby redo logs must be configured on the primary and standby databases. You must stop log apply services prior to configuring standby redo logs.

Oracle Data Guard Concepts and Administration for instructions on configuring standby redo log files

The LogXptMode database property must be set to SYNC on the primary and target standby databases.

To set the redo transport service that corresponds to the protection mode you plan to set, use the EDIT DATABASE (property) command on the primary and target standby databases. For example, if the protection mode to be set is MAXAVAILABILITY , you must set the LogXptMode property to SYNC on the primary database and on the target standby database, as shown in the following examples:

The broker does not allow these commands to succeed unless the databases are configured with standby redo log files.

Step 3 Set the F astStartFailoverTarget configuration property.

If you have two or more standby databases, set up the FastStartFailoverTarget configuration property on the primary database to indicate the desired target standby database. The broker reciprocally sets this property for the target standby database to indicate the primary database as its future target standby database when fast-start failover is actually enabled. There is no need for you set this property on the target standby as this is done for you automatically.For example:

Step 4 Upgrade the protection mode to MAXAVAILABILITY , if necessary.

If it is necessary to upgrade the protection mode, use the following DGMGRL EDIT CONFIGURATION command. For example:

Step 5 Enable Flashback Database on the primary and target standby databases, if necessary.

If it is not already enabled on the primary and standby databases, enable Flashback Database by issuing the following statements on each database:

Ensure the UNDO_RETENTION and DB_FLASHBACK_RETENTION_TARGET initialization parameters are set to sufficiently large values so that reinstatement is still possible after a prolonged outage.

Step 6 Start the observer.

Start the observer by logging into the observer computer and running DGMGRL. Connect to the configuration as a user who has the SYSDBA privilege and then issue the START OBSERVER command. Note that the command does not return; that is you will not get the DGMGRL prompt after issuing the command.

When starting the observer interactively, Oracle recommends that connection credentials not be supplied as command line parameters to the DGMGRL connect command. This practice prevents other users on the system from using a utility (for example, the UNIX ps utility) to display connection credentials. It also prevents clear-text passwords from being visible on the user’s terminal.

When starting the observer from a script, Oracle recommends that you use a method that supports ‘connect /’, so that database connection credentials do not have to be embedded within the script. If you choose to use a client-side Oracle Wallet as a secure external password store (see Oracle Database Advanced Security Administrator’s Guide ), be sure to add credentials for both the primary and fast-start failover target standby databases. The database connect string that you specify when adding the credentials for each database must match the ObserverConnectIdentifer or DGConnectIdentifier database property.

Step 7 Enable fast-start failover.

You can enable fast-start failover while connected to any database system in the broker configuration. For example:

Step 8 Verify the fast-start failover configuration.

Use the SHOW FAST_START FAILOVER command to display the fast-start failover settings:

The following commands show that the FastStartFailoverTarget property is set up reciprocally once fast-start failover is enabled. The first command, issued for the current primary database North_Sales , shows the value of the FastStartFailoverTarget property to be the current target standby, South_Sales . The second command, issued for the target standby South_Sales , shows the current primary, North_Sales , as the target standby’s future target standby should it ever take over as a primary.

6.7 Scenario 6: Performing Routine Management Tasks

There may be situations in which you want to change the state or properties of the databases in a broker configuration to perform routine maintenance on one or more databases. You might also need to temporarily disable broker management of databases in a configuration.

6.7.1 Changing Properties and States

As you monitor the configuration, you might need to dynamically modify the states of the databases or their properties. The following sections show how to change the state or properties of the databases in the configuration.

6.7.1.1 Alter a Database Property

You can modify the values of database properties at any time—if the database is enabled or disabled.

Example 6-3 shows how to use the EDIT DATABASE command to change the LogXptMode database property to the value ASYNC for the North_Sales database.

Example 6-3 Altering a Database Property

DGMGRL returns the following message to indicate that the LogXptMode property was updated successfully in the Data Guard configuration file:

If the configuration is currently disabled, the database does not use the new property value until you enable the broker configuration with the ENABLE CONFIGURATION command.

6.7.1.2 Alter the State of a Standby Database

You might want to temporarily stop Redo Apply on a physical standby. To change the state of the standby database to APPLY-OFF , enter the EDIT DATABASE command as shown in Example 6-4.

Example 6-4 Altering a Standby Database State

Redo data is still being received when you put the physical standby database in the APPLY-OFF state.

6.7.1.3 Alter the State of a Primary Database

You might want to stop the transmittal of redo data to the standby database. To change the state of the primary database to accommodate this, use the following command:

To change the state of the primary database back to TRANSPORT-ON, do the following:

6.7.2 Disabling the Configuration and Databases

When you disable the broker configuration or any of its databases, you are disabling the broker’s management of those objects and are effectively removing your ability to use DGMGRL to manage and monitor the disabled object. However, disabling the broker’s management of a broker configuration does not affect the actual operation of the underlying Data Guard configuration or the databases. For example, the redo transport services and log apply services in the Data Guard configuration continue to function unchanged, but you can no longer manage them.

6.7.2.1 Disable a Configuration

You must use the DISABLE CONFIGURATION command to disable management of the entire broker configuration including the primary database as shown in Example 6-5.

Example 6-5 Disabling the Configuration and Primary Database

The only way to disable broker management of the primary database is to use the DISABLE CONFIGURATION command; the DISABLE DATABASE command only disables management of a standby database.

If you disable management of a configuration while connected to the standby database, you must connect to the primary database to reenable the configuration.

Disabling the broker’s management of an object does not remove its profile from the broker configuration file. You can reenable your ability to use DGMGRL (or Enterprise Manager) to manage the object by entering the appropriate ENABLE CONFIGURATION or ENABLE DATABASE command.

6.7.2.2 Disable a Standby Database

You use the DISABLE DATABASE command when you temporarily do not want the broker to manage and monitor a standby database.

You can explicitly disable broker management of a standby database to prevent it from being enabled when the rest of the configuration is enabled. Example 6-6 shows how to disable the South_Sales standby database.

Example 6-6 Disabling a Standby Database

You cannot disable a standby database from the configuration if fast-start failover is enabled and the database to be disabled is the target standby database.

If you disable management of a standby database while connected to that standby database, you must connect to the primary database or another enabled standby database to reenable broker-management of the standby database.

If you disable broker management of a standby database in the broker configuration, that standby database cannot be used by the broker as a failover target in the event of loss of the primary database.

When operating under either maximum protection mode or maximum availability mode, the broker prevents you from disabling the last standby database that supports the protection mode.

6.7.3 Removing the Configuration or a Standby Database

When you use either the REMOVE CONFIGURATION or REMOVE DATABASE command, you effectively delete the configuration or standby database profile from the broker configuration file, removing the ability of the Data Guard broker to manage the configuration or the standby database, respectively.

A remove operation with the PRESERVE DESTINATIONS clause does not remove or delete the actual Data Guard configuration underneath, nor does it affect the operation of the actual Data Guard configuration and its databases.

After you use the REMOVE CONFIGURATION or REMOVE DATABASE command, you cannot recover the configuration or database profile that was deleted from the broker configuration file. You must go through the steps in Section 6.2 as necessary, to create a broker configuration that can be managed with DGMGRL (or the Enterprise Manager).

You cannot remove a standby database from the configuration if fast-start failover is enabled and the database to be removed is the target standby database.

When you use the REMOVE DATABASE command, broker management and monitoring of the database ceases and the database’s profile is deleted from the broker configuration file.

Show the configuration before deletion of the South_Sales standby database:

Issue the DGMGRL REMOVE DATABASE command to remove the South_Sales database information from the Data Guard configuration file:

Show the configuration after deletion of the South_Sales standby database:

When operating under either maximum protection mode or maximum availability mode, the broker prevents you from deleting the last standby database that supports the protection mode.

Step 2 Remove the broker configuration.

Use the following command to remove the entire configuration from management and monitoring by the broker:

You cannot remove the configuration if fast-start failover is enabled.

DGMGRL returns the following message to indicate the command successfully removed all of the configuration information from the Data Guard configuration file:

6.8 Scenario 7: Performing a Switchover Operation

You can switch the role of the primary database and a standby database using the SWITCHOVER command. Before you issue the SWITCHOVER command, you must ensure:

The state of the primary and standby databases are TRANSPORT-ON and APPLY-ON , respectively.

All participating databases are in good health, without any errors or warnings present.

The standby database properties were set on the primary database, so that the primary database can function correctly when transitioning to a standby database (shown in the following examples in boldface type).

Standby redo log files on the primary database are set up, and the LogXptMode database property is set to SYNC if the configuration is operating in either maximum availability mode or maximum protection mode.

If fast-start failover is enabled, you can perform a switchover only to the standby database that was specified as the target standby database.

Step 1 Check the primary database.

Use the SHOW DATABASE VERBOSE command to check the state, health, and properties of the primary database, as follows:

In particular, you should examine the boldface properties and the current status of the primary database. See Chapter 4 for information about managing databases.

Step 2 Check the standby database that is the target of the switchover.

Use the SHOW DATABASE VERBOSE command to check the state, health, and properties of the standby database that is the target of the switchover. For example:

In particular, you should examine the current status of the database.

Step 3 Issue the switchover command.

Issue the SWITCHOVER command to swap the roles of the primary and standby databases. The following example shows how the broker automatically shuts down and restarts the old primary database as a part of the switchover. (See the usage notes in Section 7.1.3 for information about how to set up the broker environment so that DGMGRL can automatically restart the primary and standby databases for you.)

After the switchover completes, use the SHOW CONFIGURATION and SHOW DATABASE commands to verify that the switchover operation was successful.

Step 4 Show the configuration.

Issue the SHOW CONFIGURATION command to verify that the switchover was successful.

6.9 Scenario 8: Performing a Manual Failover Operation

You invoke a failover operation in response to an emergency situation, usually when the primary database cannot be accessed or is unavailable. See Section 5.2 before you fail over to decide which standby database should be the target of the failover. The following scenario describes a failover to the remote database called South_Sales .

If fast-start failover is enabled, you can perform a manual failover only to the standby database that was specified as the target of a fast-start failover and only when the observer is running and currently has connectivity with the standby database.

If you want to perform a manual failover to a standby database that is not the fast-start failover target standby database, you must first disable fast-start failover using the FORCE option on the standby database you want to fail over. See Section 5.5.5, «Disabling Fast-Start Failover» for more information about the FORCE option.

To perform the failover operation, you must connect to the standby database to which you want to fail over to as a user that has the SYSDBA privilege. For example:

Step 2 Issue the failover command.

Now you can issue the failover command to make the target standby database the new primary database for the configuration.

Note that after the failover completes, the original primary database cannot be used as a standby database of the new primary database unless it is reinstated or re-created (as described in Section 5.4.3).

Step 3 Show the configuration.

Issue the SHOW CONFIGURATION command to verify the failover.

Note that in this example, the configuration was operating in maximum availability mode. The protection mode was preserved after the failover. The configuration also has a warning status. The SHOW DATABASE command for the new primary shows that the warning is the result of not having an enabled physical standby database. As a result, the warning status indicates that the protection level of the configuration is not the same as the configured mode.

Step 4 Show the new primary database. Step 5 Show the old primary database.

Issue the SHOW DATABASE command to see that the former (failed) primary database was disabled by the broker as a consequence of the failover. It must be reinstated (as described in Section 5.4.3).

6.10 Scenario 9: Reinstating a Failed Primary Database

If your primary database had been configured with Flashback Database, you can easily reinstate the failed primary database as a standby database of the new primary database. The failed primary database will be reinstated as a standby type that matches the old standby database. For example, if you failed over to a physical standby database, the old primary will be reinstated as a physical standby database.

To reinstate the failed primary database, start it to the mounted state. Then run DGMGRL, connect to the new primary database and reinstate the old primary database.

Step 1 Restart the Old Primary Database. Step 2 Reinstate the old primary database.

After the primary has been reinstated, issue the SHOW CONFIGURATION and SHOW DATABASE commands to confirm that the old primary has been successfully reinstated.

Step 3 Show the Configuration and Databases.

6.11 Scenario 10: Converting a Physical Standby to a Snapshot Standby

If you have a physical standby database that you would like to convert to a snapshot standby database, use the DGMGRL CONVERT DATABASE command. Redo data will continue to be received by the database while it is operating as a snapshot standby database, but it will not be applied until the snapshot standby is converted back into a physical standby database.

A physical standby database must be configured with a fast recovery area to convert it to a snapshot standby database. This is because a guaranteed restore point is created during the conversion process, and guaranteed restore points require a fast recovery area.

When you are ready to revert the database back to a physical standby database, use the DGMGRL CONVERT DATABASE command again as follows. Any updates made to the database while it was operating as a snapshot standby database will be discarded. All accumulated redo data will be applied by Redo Apply services after the database is converted back to a physical standby database.

6.12 Scenario 11: Monitoring a Data Guard Configuration

The scenario in this section demonstrates how to use the SHOW command and monitorable database properties to identify and resolve a failure situation.

Step 1 Check the configuration status.

The status of the broker configuration is an aggregated status of all databases and instances in the broker configuration. You can check the configuration status first to determine whether or not any further action needs to be taken. If the configuration status is SUCCESS, everything in the broker configuration is working properly. However, if you see a status of WARNING or ERROR, then something is wrong in the configuration.

For example, in the following display, you can see that the primary database has multiple warnings:

Step 2 Check the database status.

To identify the warnings on the primary database, show its status using the SHOW DATABASE command:

Step 3 Check the LogXptStatus monitorable database property.

The SHOW DATABASE output in step 2 shows a Warning for error ORA-16737. To identify the exact transport error, use the LogXptStatus monitorable database property:

The output shows that the listener for the physical standby database is not running. To fix this error, start the listener for the physical standby database South_Sales .

Step 4 Check the InconsistentProperties monitorable database property.

The SHOW DATABASE output in step 2 also shows a Warning for error ORA-16714. To identify the exact error, use the InconsistentProperties monitorable database property:

The current database memory value (511) is different from both the server parameter file (SPFILE) value (255) and the Data Guard broker’s property value (255). If you decide the database memory value is correct, you can update the Data Guard broker’s property value using the following command:

This command will result in the broker updating the SPFILE value so that the value of LogArchiveTrace is kept consistent.

Step 5 Check the InconsistentLogXptProps monitorable database property.

Another warning shown in the SHOW DATABASE display in Step 2 is ORA-16715. To identify the inconsistent values for the redo transport database property, ReopenSecs , you can use the InconsistentLogXptProps monitorable database property:

The current database memory value (600) is different from the Data Guard broker’s property value (300). If you think the broker’s property value is correct, you can fix the inconsistency by re-editing the property of the standby database with the same value, as shown in the following example:

You can also reenable the standby database or reset the state of the primary database to TRANSPORT-ON to fix this inconsistency.

Источник

Reason:
Primary and standby database SYS password is not same and it creates conflicts to access the standby database and vice versa.

Solution:

Step-1:  Check error and remote_login_passwordfile parameter

SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;

ERROR
—————————————————————–
ORA-16191: Primary log shipping client not logged on standby

NAME TYPE VALUE
———————————— ——————————– ——————————
remote_login_passwordfile string EXCLUSIVE

Step-2: Disable log_archive_dest_state_2 on Primary side.

SQL> show parameter log_archive_dest_state_2;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
log_archive_dest_state_2             string      ENABLE
log_archive_dest_state_20            string      enable
log_archive_dest_state_21            string      enable
log_archive_dest_state_22            string      enable
log_archive_dest_state_23            string      enable
log_archive_dest_state_24            string      enable
log_archive_dest_state_25            string      enable
log_archive_dest_state_26            string      enable
log_archive_dest_state_27            string      enable
log_archive_dest_state_28            string      enable
log_archive_dest_state_29            string      enable

SQL> alter system set log_archive_dest_state_2=DEFER scope=both sid=’*’;

System altered.

SQL> show parameter log_archive_dest_state_2;

NAME                                 TYPE        VALUE
———————————— ———– ——————————
log_archive_dest_state_2             string      DEFER

Step3: Shutdown the standby database

srvctl stop database -d STDGDB_SITE10  //RAC environment

Step4: Recreate password file on all nodes / create it on one node and distributed to  relevant nodes.

${HOME_DIR}/bin/orapwd file=orapw<Primaryinstancename> password=Myxlnc entries=15 force=y ignorecase=Y

Step5: start the standby database //should be in mount state.

srvctl start database -d STDGDB_SITE10  //RAC environment

Step6: Start the recover process on .

SQL> alter database reocver managed standby database using current logfile disconnect from session;

Step7: Enable log_archive_dest_state_2 on Primary side.

SQL> alter system set log_archive_dest_state_2=ENABLE scope=both sid=’*’;

System altered.

Post that run the below query once again. Hope there is no error.

SELECT DESTINATION, STATUS, ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID=2;