Monday, May 14, 2012

Why the Redo does not get transported to the standby destination?

I am in an 11gR2 environment, with an active data guard configuration.
After some maintenance activities which required the servers down, I saw the redo does not get transported to one of our standby dbs.

On the primary:

SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select name,db_unique_name,database_role from v$database;
NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
PROD      PROD                           PRIMARY

The correct db view to start your investigation is V$ARCHIVE_DEST_STATUS.
V$ARCHIVE_DEST_STATUS  displays runtime and configuration information for the
archived redo log destinations. The information in this view does not persist across an
instance shutdown.
You can perform the following query on the redo source database to determine the most recently archived redo log file at each
redo transport destination.
The output is straight forward, and indicates no listener at one of our destination. 

SQL>  select destination, status,error from v$archive_dest_status where destination like 'STDBY';                                                                          ';
DESTINATION
--------------------------------------------------------------------------------
STATUS    ERROR
--------- -----------------------------------------------------------------
STDBY
ERROR     ORA-12541: TNS:no listener

On the physical standby:

SQL> select banner from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select name,db_unique_name, database_role from v$database;
NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
--------- ------------------------------ ----------------
PROD      STDBY                           PHYSICAL STANDBY
               
The action was as simple as starting up the listener and the error was corrected.
The redo started to get transported and applied on the standby server.

SQL> select destination, status,error from v$archive_dest_status where destination like 'STDBY';
DESTINATION
--------------------------------------------------------------------------------
STATUS    ERROR
--------- -----------------------------------------------------------------
STDBY
VALID

No comments:

Post a Comment