Sunday, November 22, 2015

Fix ORA-16053

Today I will show you how to handle ORA-16053 error

--Check maximum sequence number of archive log
SQL> select max(sequence#) from v$archived_log;  
 MAX(SEQUENCE#)  
 --------------  
       36  


--Now check parameter
SQL> col destination for a70  
 SQL> select dest_id, status, destination, error from v$archive_dest;  
   DEST_ID STATUS  DESTINATION                              ERROR  
 ---------- --------- ---------------------------------------------------------------------- -----------------------------------------------------------------  
      1 BAD PARAM                                    ORA-16053: DB_UNIQUE_NAME is not in the Data Guard Configuration  
      2 VALID   PROD  
      3 INACTIVE  
      4 INACTIVE  
      5 INACTIVE  
      6 INACTIVE  
      7 INACTIVE  
      8 INACTIVE  
      9 INACTIVE  
     10 INACTIVE  
     11 VALID   /u01/app/oracle/product/11.0.1/db_1/dbs/arch  
 11 rows selected.  

Here is error which means db_unique_name not setted on parameter.


--Also below query provide us bad param if not properly configured.
SQL> select status, dest_id, error from v$archive_dest_status;  
 STATUS    DEST_ID ERROR  
 --------- ---------- -----------------------------------------------------------------  
 BAD PARAM     1 ORA-16053: DB_UNIQUE_NAME is not in the Data Guard Configuration  
 VALID       2  
 INACTIVE      3  
 INACTIVE      4  
 INACTIVE      5  
 INACTIVE      6  
 INACTIVE      7  
 INACTIVE      8  
 INACTIVE      9  
 INACTIVE     10  
 VALID       11  
 11 rows selected.  

SQL> show parameter log_archive_config  
 NAME                 TYPE    VALUE  
 ------------------------------------ ----------- ------------------------------  
 log_archive_config          string   DG_CONFIG=(PROD,STAND)  
 --set log_archive_config  
 SQL> alter system set log_archive_config='DG_CONFIG=(STAND,PROD)';  
 System altered. 


SQL> show parameter log_archive_dest_1  
 NAME                 TYPE    VALUE  
 ------------------------------------ ----------- ------------------------------  
 log_archive_dest_1          string   LOCATION=USE_DB_RECOVERY_FILE_  
                          DEST VALID_FOR=(ALL_LOGFILES,A  
                          LL_ROLES) DB_UNIQUE_NAME=PROD  
 log_archive_dest_10         string  
 SQL> show parameter log_archive_dest_2  
 NAME                 TYPE    VALUE  
 ------------------------------------ ----------- ------------------------------  
 log_archive_dest_2          string   SERVICE=PROD VALID_FOR=(ONLINE  
                          _LOGFILE,PRIMARY_ROLE) async d  
                          b_unique_name=PROD  
 SQL> alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES_ DB_UNIQUE_NAME=STAND' scope=both;  
 System altered.  



SQL> select status, dest_id, error from v$archive_dest_status where dest_id=2;  
 STATUS         DEST_ID ERROR  
 -------------------- ---------- -----------------------------------------------------------------  
 VALID    

 SQL> select dest_id, status, destination, error from v$archive_dest;  
   DEST_ID STATUS        DESTINATION                              ERROR  
 ---------- -------------------- ---------------------------------------------------------------------- -----------------------------------------------------------------  
      1 VALID        USE_DB_RECOVERY_FILE_DEST  
      2 VALID        PROD  
      3 INACTIVE  
      4 INACTIVE  
      5 INACTIVE  
      6 INACTIVE  
      7 INACTIVE  
      8 INACTIVE  
      9 INACTIVE  
     10 INACTIVE  
     11 VALID        USE_DB_RECOVERY_FILE_DEST  
 11 rows selected.  


--Now check again and you will see logs start to receive and applying.

 SQL> select max(sequence#) from v$archived_log;  
 MAX(SEQUENCE#)  
 --------------  
       45  

No comments:

Post a Comment

Cannot access dba_directories inside procedure

 Recently I faced one of familiar Oracle error ORA -00942 : table or view does not exist   I got it in while compiling procedure, becaus...