Forgot your password?    
+ Reply to Thread
Results 1 to 1 of 1

Thread: Troubleshooting Data Guard Implementation I

  1. #1
    Expert Oracle Administrator
    Join Date
    Oct 2011
    Posts
    120

    Troubleshooting Data Guard Implementation I

    Database Alert Logs
    The database alert logs provide a great source of information in investigating issues with both the
    primary and standby databases. The Data Guard Broker also writes information to the alert logs as well as its own Disaster Recovery Center (DRC) log files.

    Observer Log Files
    If you are using the Fast-Start Failover (FSFO) feature of Data Guard, additional diagnostic
    information is available in the observer log file. The log files are called dgmgrl<nnnnn>.log. The <nnnnn> is a system-generated number that will change each time the observer is started.

    Data Guard Trace Files
    Data Guard trace files can also provide crucial information to diagnosing Data Guard configuration
    issues and/or identify areas in the Data Guard topology that need attention. Oracle will write an
    audit trail of the archived redo logs received from the primary database into a trace file when the
    LOG_ARCHIVE_TRACE initialization parameter is enabled.

    Data Guard Broker Log Files and Tools
    The Data Guard Monitor (DMON) process also writes status information to a log file. DMON log
    files can be useful in diagnosing Data Guard failures and can be found in the background_dump_
    dest directory.

    Dynamic Performance Views
    Name Description
    DBA_LOGSTDBY_EVENTS Contains the last 100 (default) events that occurred on the
    logical standby
    DBA_LOGSTDBY_PROGRESS Checks whether SQL Apply is progressing
    DBA_LOGSTDBY_LOG Checks whether archive logs are being delivered to a logical
    standby
    DBA_LOGSTDBY_UNSUPPORTED Identifies SQL Apply unsupported data types
    V$ARCHIVE_DEST Describes all the destinations in the Data Guard configuration,
    including each destination’s current settings
    V$ARCHIVE_DEST_STATUS Displays runtime and configuration information for the redo
    transport destinations
    V$ARCHIVE_GAP Displays information to help you identify a gap in the
    archived redo log files on a physical standby
    V$DATAGUARD_CONFIG Lists the DB_UNIQUE_NAME parameters defined in the Data
    Guard configuration in LOG_ARCHIVE_CONFIG
    V$DATAGUARD_STATUS Displays and records events that would typically be triggered
    by any message to the alert log or server process trace files
    limited to the last 256 messages
    V$LOG Displays information from the online redo log files
    V$LOGFILE Contains information about the online redo log files and
    standby redo log files
    V$LOG_HISTORY
    V$ARCHIVED_LOG
    Contains archive log history information from the control file
    Contains more detailed archived log information from the
    control file.
    V$LOGSTDBY_PROCESS Shows whether logical standby process is running; if query
    returns no rows, it is not running
    V$MANAGED_STANDBY Displays current status information for Oracle Database
    processes related to Data Guard
    V$STANDBY_LOG Contains standby log file information
    Data Guard Configuration and Management Errors

    Common Management Issues

    The Password File
    You may encounter a couple of errors associated with the password file. For instance, you may
    have problems connecting to the standby database and may see the “ORA-01034: ORACLE not
    available” error message. Check your password for the SYS account on the primary and standby
    databases to make sure that they are the same.
    If you encounter an ORA-16191 error when the primary attempts to connect to the standby,
    verify that the SYS password is the same on the primary and standby databases. Ensure that the
    primary and standby databases are using a password file and that the REMOTE_LOGIN_PASSWORD
    parameter is set to SHARED or EXCLUSIVE.
    SQL Apply Fails with ORA-01031
    This error tells you that SQL Apply has failed with insufficient privileges. The problem is that
    background processes are not running with sysdba privileges. They will need to be granted
    manually on both the primary and standby databases. You will see the following in the alert log
    on the logical standby:
    LOGSTDBY stmt: grant sysdba to maxtrix_user
    LOGSTDBY status: ORA-01031: insufficient privileges

    Resolving Gaps Manually
    A redo gap occurs whenever redo transmission is interrupted. Redo Transport Services will
    automatically detect the redo gap and resolve it by sending the missing redo to the destination
    once redo transmission resumes. In some cases, though, gap resolution can’t be performed
    automatically and must be done manually.
    To determine whether a redo gap exists on your physical standby, you can run the following
    query.
    SQL> SELECT * FROM V$ARCHIVE_GAP;
    THREAD# LOW_SEQUENCE# HIGH_SEQUENCE#
    ----------- ------------- --------------
    1 6233 6233
    1 4531 4531
    1 4938 4939
    You can use the low and high sequence numbers to identify the actual log files that are missing. Run
    the following query on the primary to capture this information:
    SQL> SELECT name
    FROM v$archived_log
    WHERE thread# = 2

    AND dest_id = 2
    AND sequence# = 6233;
    NAME
    -----------------------------------------------
    /u06/oradata/MATRIX/recovery_area/arch_2_6233_656445787.arc

    Use an Incremental RMAN Backup to Roll a Standby Forward
    In cases where the standby has fallen behind for a long period of time, you may want to use an
    incremental backup as a more efficient method for handling large gap scenarios. You should use
    this method only if the standby has fallen significantly behind the primary or no NOLOGGING
    operations have been performed on the primary. This solution can be used only on a physical
    standby database.

    Standby Database Is Not Receiving Redo Logs
    If the redo data is not being transported to the standby database, query the V$ARCHIVE_DEST
    view and check for error messages. Here is an example of the query:
    SQL> SELECT DEST_ID, STATUS, ERROR
    FROM V$ARCHIVE_DEST;
    DEST_ID STATUS ERROR
    ------- --------- --------------------------------------
    1 ERROR ORA-16012: Archivelog standby database identifier mismatch
    2 INACTIVE
    3 INACTIVE
    If the output of the query does not help you, check the following list of possible issues. If any
    of the following conditions exists, log transport services will fail to transmit redo data to the
    standby database.

    Listener Issues
    ■ The listener.ora file has not been configured correctly.
    ■ The listener has not been started on the standby.
    ■ The service name for the standby instance is not configured correctly in the tnsnames.ora
    file on the primary database.
    ■ If you cannot log in remotely to the standby database using the TNSNAME identifier,
    then Data Guard cannot log in either. Try the remote login yourself:
    SQL> CONNECT SYS/PASSWORD@MYTNSNAME AS SYSDBA;

    Archive Destination Issues
    ■ The service name specified by the LOG_ARCHIVE_DEST_n parameter for the primary
    database is incorrect.
    ■ The LOG_ARCHIVE_DEST_STATE_n parameter for the standby database is not set to the
    value ENABLE.
    ■ A disconnect occurred and the number of seconds specified by REOPEN has not yet passed.
    ■ The standby instance is not started.
    ■ The standby control file was created incorrectly.
    ■ The correct backup was not used to build the standby.
    Standby Waiting on Log Files that Exist at the Standby
    If the standby is waiting on logs that are in the standby destination area, the logs are most likely
    not properly registered on the standby. Check the appropriate views (physical is V$ARCHIVED_
    LOG and logical is DBA_LOGSTDBY_LOG) to see if they are registered. If they are not registered,
    the file on disk is unusable and you should use the manual procedure if Data Guard cannot
    resolve the gaps automatically. Do not try to use the file on disk! Also verify that the local
    archival destination on the standby is correct.
    Receive an ORA-16032 on Alter System Archive Log All
    One of the LOG_ARCHIVE_DEST_n parameters is not configured correctly. Verify the local
    destinations on the primary.

    Media Recovery Failures
    If media recovery fails on the standby, leaving your standby in an unrecoverable state, you will
    see the following messages:
    ORA-01578: ORACLE data block corrupted
    ORA-26040: Data block was loaded using the NOLOGGING option.
    On the physical standby database, you will need to do the following:
    1. Offline the corresponding datafiles.
    2. Copy the backup datafiles from the primary database.
    3. Replace the corrupted datafiles.
    4. Stop the Redo Apply.
    5. Online the corresponding datafiles.
    6. Restart the Redo Apply.
    Resync Logical Standby Database When Nologging Issue Occurs Keep in mind that SQL
    Apply will skip over nologging operations. Therefore, the datafiles will not be affected by the data
    block corruption. You will eventually see an “ORA-01403: No Data Found on the standby” error.
    To resync the table with the primary table, you will need to re-create it with the instantiate table
    procedure.
    If you see an “ORA-00308: cannot open archived log,” cancel SQL Apply and manually
    retrieve the missing files.
    Renaming Datafiles with the ALTER DATABASE Statement
    You cannot rename a datafile on the standby site when the STANDBY_FILE_MANAGEMENT
    initialization parameter is set to AUTO. In addition, you are not permitted to use any of the
    following SQL statements:
    SQL> ALTER DATABASE RENAME
    SQL> ALTER DATABASE ADD/DROP LOGFILE
    SQL> ALTER DATABASE ADD/DROP STANDBY LOGFILE MEMBER
    SQL> ALTER DATABASE CREATE DATAFILE AS
    If you attempt to use any of these statements on the standby database, the following error is
    returned if STANDBY_FILE_MANAGEMENT is set to AUTO:
    ORA-01511: error in renaming log/data files
    ORA-01270: RENAME operation is not allowed
    You can still add and delete standby redo log files:
    SQL> ALTER DATABASE ADD/DROP STANDBY LOGFILE;
    Last edited by Hemant; 01-10-2012 at 07:50 PM.

+ Reply to Thread

Similar Threads

  1. Concurrent Request troubleshooting HELP NEEDED
    By saioracleappsdba in forum Concurrent Processing Server / Workflow
    Replies: 1
    Last Post: 06-15-2012, 04:51 AM
  2. Forms server troubleshooting
    By saioracleappsdba in forum Concepts,Service Management,Apache,OC4J,OPMN,JServ,Forms, Issues,Troubleshooting,
    Replies: 1
    Last Post: 06-15-2012, 04:47 AM
  3. Patching troubleshooting help needed
    By saioracleappsdba in forum Oracle Apps Patching and Cloning, Installation , Migration,Upgrades
    Replies: 1
    Last Post: 06-15-2012, 04:41 AM
  4. Log Files for Troubleshooting Oracle RAC issues
    By Hemant in forum RAC Performance,LoadBalancing,TAF,FAN,Troubleshooting
    Replies: 0
    Last Post: 12-15-2011, 06:16 PM
  5. Troubleshooting Data Guard Implementation II
    By mdmasood in forum DataGuard Setup and Management
    Replies: 0
    Last Post: 11-05-2011, 05:46 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

DBA Lounge (P) Ltd. deals in Oracle Technologies on Consulting, Resourcing, Corporate Training


Online and corporate training available on Oracle Database 11g, Oracle 11g Real Application Cluster (RAC), Oracle Applications 11i/R12, Oracle Fusion Middleware 11g, Oracle Identity Management-OIM, Oracle Internet Directory 11g-OID, Oracle Business Intelligence Enterprise Edition-OBIEE, Oracle Golden Gate, Oracle Access Management-OAM, Oracle Internet Directory-ODS, Oracle Identity Analytics Architecture-OIA Statistics