Wednesday, October 23, 2013

ORA-01031: insufficient privileges ==> GAPS in Dataguard Primary and Standby Databases


##########################
## Error
##########################

ORA-01031: insufficient privileges

### Full Error

ORA-01031: insufficient privileges

##########################
#  Error Occurred
##########################

Dataguard Primary and standby are not in SYNC and log_archive_dest of standby site is showing ORA-01031 error in 11.2.0.3.0 version

##########################
## Command Executed
##########################

In Sqlplus

select dest_id,dest_name,target,name_space,destination,status,error,db_unique_name from v$archive_dest where destination is not null;

##########################
## Issue Description.
##########################

### 11.2.0.3.0

#########################################
# Gaps in Primary n Standby
#########################################

In Primary :
--------------

set lines 200
col dest_name for a20
col destination for a50
col error for a15
col db_unique_name for a15
col name for a100

select dest_id,dest_name,target,name_space,destination,status,error,db_unique_name from v$archive_dest where destination is not null;


   DEST_ID DEST_NAME            TARGET  NAME_SP DESTINATION                                        STATUS    ERROR           DB_UNIQUE_NAME
---------- -------------------- ------- ------- -------------------------------------------------- --------- --------------- ---------------
         1 LOG_ARCHIVE_DEST_1   PRIMARY SYSTEM  USE_DB_RECOVERY_FILE_DEST                          VALID                     est01p_stl
         2 LOG_ARCHIVE_DEST_2   STANDBY SYSTEM  est01p_gvl                                         ERROR     ORA-01031:      est01p_gvl
                                                                                                             insufficient
                                                                                                             privileges

Select thread#,sequence# from v$thread;

   THREAD#  SEQUENCE#
---------- ----------
         1      15933
         2        231

Standby :
------------

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                  15933                 15933          0
         2                    212                   212          0

##########################
## Solution
##########################

From the above output we can see that, In Standby Destination received archive log sequence is 212 of thread 2, but in primary its 231.

#########################################
# Check Password File in Standby
#########################################


est01p_1 @ hostorap01:/u01/app/oracle/product/11.2/db_6/dbs
> ls -ltr orapwe*
est01p_1 @ hostorap01:/u01/app/oracle/product/11.2/db_6/dbs

Password File is not available for est01p database.

Create a new password file ( or ) FTP the password file from Primary to STandby location.

After ftping the password file, GAPS are resolved automatically.

In Primary :
--------------

set lines 200
col dest_name for a20
col destination for a50
col error for a15
col db_unique_name for a15
col name for a100

select dest_id,dest_name,target,name_space,destination,status,error,db_unique_name from v$archive_dest where destination is not null;

   DEST_ID DEST_NAME            TARGET  NAME_SP DESTINATION                                        STATUS    ERROR           DB_UNIQUE_NAME
---------- -------------------- ------- ------- -------------------------------------------------- --------- --------------- ---------------
         1 LOG_ARCHIVE_DEST_1   PRIMARY SYSTEM  USE_DB_RECOVERY_FILE_DEST                          VALID                     est01p_stl
         2 LOG_ARCHIVE_DEST_2   STANDBY SYSTEM  est01p_gvl                                         VALID                     est01p_gvl
         3 LOG_ARCHIVE_DEST_3   PRIMARY SYSTEM  /u01/app/oracle/exp/est01p/arch02                  ALTERNATE                 est01p_Stl


Standby :
------------

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received", APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference" FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN (SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE ARCH.THREAD# = APPL.THREAD# ORDER BY 1;


    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                  15933                 15933          0
         2                    230                   230          0

No comments: