Tuesday, 29 November 2011

Oracle Problems : Troubleshooting Of Oracle Data Guard

First I will describe about some common problem regarding Dataguard which are as follows:

Common Problems:

If you encounter a problem when using a standby database, it is probably because of one of the following reasons:

  • Standby Archive Destination Is Not Defined Properly
  • Renaming Datafiles with the ALTER DATABASE Statement
  • Standby Database Does Not Receive Redo Data from the Primary Database
  • You Cannot Mount the Physical Standby Database

Standby Archive Destination Is Not Defined Properly


If the STANDBY_ARCHIVE_DEST initialization factor does not stipulate a applicable list of directions label on the standby database, the Oracle database will not be competent to ascertain the list of directions in which to warehouse the archived redo log files. Check the DESTINATION and ERROR posts in the V$ARCHIVE_DEST scenery by moving into the subsequent query and assure the location stayed at is valid:


SQL> SELECT DESTINATION, ERROR FROM V$ARCHIVE_DEST;
 

Renaming Datafiles with the ALTER DATABASE Statement


You cannot rename the datafile on the standby site when the STANDBY_FILE_MANAGEMENT initialization parameter is set to AUTO. When you set the STANDBY_FILE_MANAGEMENT initialization parameter to AUTO, use of the following SQL statements is not allowed:
  • ALTER DATABASE RENAME
  • ALTER DATABASE ADD/DROP LOGFILE
  • ALTER DATABASE ADD/DROP STANDBY LOGFILE MEMBER
  • ALTER DATABASE CREATE DATAFILE AS

If you attempt to use any of these statements on the standby database, an error is returned. For example:
SQL> ALTER DATABASE RENAME FILE '/disk1/oracle/oradata/payroll/t_db2.log' to 'dummy';  

alter database rename file '/disk1/oracle/oradata/payroll/t_db2.log' to 'dummy' * ERROR at line 1: ORA-01511: error in renaming log/data files
ORA-01270: RENAME operation is not allowed if STANDBY_FILE_MANAGEMENT is auto 

Standby Database Does Not Receive Redo Data from the Primary Database

If the standby place is not accepting redo written knowledge, query the V$ARCHIVE_DEST scenery and review for wrongdoing messages. For instance, move into the subsequent query:
SQL> SELECT DEST_ID "ID",
  2> STATUS "DB_status",
  3> DESTINATION "Archive_dest",
  4> ERROR "Error"
  5> FROM V$ARCHIVE_DEST WHERE DEST_ID <=5;

ID DB_status Archive_dest                   Error   
-- --------- ------------------------------ ------------------------------------
 1  VALID    /vobs/oracle/work/arc_dest/arc                          
 2  ERROR    standby1                       ORA-16012: Archivelog standby database identifier mismatch  
 3  INACTIVE                            
 4  INACTIVE                    
 5  INACTIVE                                           
5  rows selected.

If the end wares of the query does not aid you, review the subsequent table of probable issues. If any of the subsequent circumstances survive, log transport services will bungle to spread redo written knowledge to the standby database:
  • The service label for the standby case is not configured in the right way in the tnsnames.ora record for the main database.
  • The Oracle Net service label individual by the LOG_ARCHIVE_DEST_n factor for the main database is incorrect.
  • The LOG_ARCHIVE_DEST_STATE_n factor for the standby database is not set to the worth ENABLE.
  • The listener.ora record has not been configured in the right way for the standby database.
  • The listener is not commenced at the standby site.
  • The standby case is not started.
  • You have adjoined a standby archiving location stayed at to the main SPFILE or text initialization factor record, but have not yet empowered the change.
  • You employed an invalid backup as the foundation for the standby database (for instance, you employed a backup from the erroneous database, or did not bring ahead the standby manipulate record employing the correct method).
 You Cannot Mount the Physical Standby Database

You not able to get on the standby database if the standby manipulate record was not brought ahead with the ALTER DATABASE CREATE [LOGICAL] STANDBY CONTROLFILE ... assertion or RMAN command. You not able to use the subsequent sorts of manipulate record backups:
  • An running system-created backup
  • A backup brought ahead employing an ALTER DATABASE assertion without the [PHYSICAL] STANDBY or LOGICAL STANDBY option
 My Next Post will be about Log File Destination Failure