Target Re-Configuration in OEM gives ==> Connection Failure May Be Due To A Slow Network, or to the presence of intervening firewall

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

Connection Failure May Be Due To A Slow Network, or to the presence of intervening firewall

### Full Error

Connection Failure May Be Due To A Slow Network, or to the presence of intervening firewall

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

Error occured while trying to resolve metric collection error for a cluster database in 11.2.0.2.0 GI version

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

Reconfiguration of Target in OEM

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

### 11.2.0.2.0

#########################################
# Metric Collection Errors
#########################################

One of our database is in metric collection Error, So when trying to re-configure the database in OEM got the connection failure error.

This may be because of the network issue between Agent and OMS server. In our case all the targets in this server are working fine. So this is not the issue

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

Database has archive issues, FRA has become 100% full. Due to which agent cant communicate with the database.



set lines 200
select * from v$flash_recovery_Area_usage;

FILE_TYPE            PERCENT_SPACE_USED PERCENT_SPACE_RECLAIMABLE NUMBER_OF_FILES
-------------------- ------------------ ------------------------- ---------------
CONTROL FILE                        .05                         0               1
REDO LOG                              0                         0               0
ARCHIVED LOG                      99.76                         0             136
BACKUP PIECE                          0                         0               0
IMAGE COPY                            0                         0               0
FLASHBACK LOG                         0                         0               0
FOREIGN ARCHIVED LOG                  0                         0               0

7 rows selected.

After running an archivelog backup issue got resolved.

So next time when a database has metric collection errors, dont forget to check the archive usage too.

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

ORA-15053: diskgroup "OCR_VOTE" contains existing files

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

ORA-15053: diskgroup "OCR_VOTE" contains existing files

### Full Error

SQL> Drop diskgroup OCR_VOTE;
Drop diskgroup OCR_VOTE
*
ERROR at line 1:
ORA-15039: diskgroup not dropped
ORA-15053: diskgroup "OCR_VOTE" contains existing files

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

Error occured while trying to drop one of the diskgroup in 11.2.0.3.0 ASM version

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

In ASM Instance Sqlplus,

Drop diskgroup OCR_VOTE;

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

### 11.2.0.3.0

#########################################
# 1) Check the Files Stored in ASM Diskgroup
#########################################

Disk group is not getting dropped because it has some files stored. Use the below query to find the files stored in Diskgroup.

set lines 200
col full_path for a110
SELECT full_path, dir, sys FROM (SELECT CONCAT('+'||gname,SYS_CONNECT_BY_PATH(aname,'/')) full_path,dir, sys FROM (SELECT g.name gname,a.parent_index pindex, a.name

aname,a.reference_index rindex, a.ALIAS_DIRECTORY dir,a.SYSTEM_CREATED sys FROM v$asm_alias a, v$asm_diskgroup g WHERE a.group_number = g.group_number) START WITH

(MOD(pindex, POWER(2, 24))) = 0 CONNECT BY PRIOR rindex = pindex ORDER BY dir desc, full_path asc)
WHERE UPPER(full_path) LIKE '%OCR_VOTE%';

FULL_PATH                                                                                                      D S
-------------------------------------------------------------------------------------------------------------- - -
+OCR_VOTE/dev-cluster                                                                                         Y Y
+OCR_VOTE/dev-cluster/OCRFILE                                                                                 Y Y
+OCR_VOTE/dev-cluster/OCRFILE/REGISTRY.255.828701551                                                          N Y

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

Move/Drop the Files stored in the diskgroup before dropping, else use the below option to drop the diskgroup.

Drop diskgroup OCR_VOTE including contents;

SQL> Drop diskgroup OCR_VOTE including contents;

Diskgroup dropped.

ORA-15032: not all alterations performed ORA-15028: ASM file '+OCR_VOTE/spfileasm.ora' not dropped; currently being accessed

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

ORA-15028: ASM file '+OCR_VOTE/spfileasm.ora' not dropped; currently being accessed

### Full Error

SQL> alter diskgroup OCR_VOTE drop file '+OCR_VOTE/spfileasm.ora';
alter diskgroup OCR_VOTE drop file '+OCR_VOTE/spfileasm.ora'
*
ERROR at line 1:
ORA-15032: not all alterations performed
ORA-15028: ASM file '+OCR_VOTE/spfileasm.ora' not dropped; currently being accessed

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

Error occured while trying to drop SPFILE from one diskgroup, which is supposed to be dropped in 11.2.0.3.0 version

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

In Sqlplus

alter diskgroup OCR_VOTE drop file '+OCR_VOTE/spfileasm.ora';

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

### 11.2.0.3.0

#########################################
# 1) Why i'm dropping SPFILE
#########################################

We want to move all the files from +OCR_VOTE diskgroup to new diskgroup. All the files are moved except ASM Spfile.

As the ASM instance is currently using the Parameter file, it cant be dropped. So followed below steps to drop it.

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

1) Create a pfile to a temporary location from Spfile.

SQL> create pfile='$ORACLE_HOME/dbs/init+ASM.ora' from spfile;

2) Shutdown ASM instance.

SQL> shutdown immediate

3) Startup with Pfile

SQL> startup pfile=$ORACLE_HOME/dbs/init+ASM.ora

4) Now Drop the Spfile from +OCR_VOTE diskgroup.

alter diskgroup OCR_VOTE drop file '+OCR_VOTE/spfileasm.ora';

PROT-30: The Oracle Cluster Registry location to be added is not usable

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

PROT-30: The Oracle Cluster Registry location to be added is not usable

### Full Error

+ASM1 @ hostd01:/u01/app/oracle/exp
> sudo ocrconfig -add /u01/app/oracle/exp
PROT-30: The Oracle Cluster Registry location to be added is not usable
PROC-8: Cannot perform cluster registry operation because one of the parameters is invalid. Operating System error [Resource temporarily unavailable] [11]

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

Error occured while trying to add another(mirror) location to OCR Files. Our OCR is presently in ASM diskgroup.

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

In Terminal, by setting ASM instance,

sudo ocrconfig -add /u01/app/oracle/exp

(or)

sudo ocrconfig -add /u01/app/oracle/product/11.2/ocr.dat

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

### 11.2.0.3.0

#########################################
# 1) Check the CRS logs for errors recorded while running this command.
#########################################

+ASM1 @ hostd01:/u01/app/oracle/exp
> sudo ocrconfig -add /u01/app/oracle/exp
PROT-30: The Oracle Cluster Registry location to be added is not usable
PROC-8: Cannot perform cluster registry operation because one of the parameters is invalid. Operating System error [Resource temporarily unavailable] [11]

Errors Recorded in crsd.log :

+ASM1 @ hostd01:/u01/app/11.2/grid1/log/hostd01/crsd
> tail -f crsd.log
2013-10-14 01:21:23.162: [   CRSPE][1187469632] {2:40710:2864} Expression Filter : ((NAME == ora.scan1.vip) AND (LAST_SERVER == hostd02))
2013-10-14 01:21:41.455: [  OCROSD][947927360]utdvch:-1: New location /u01/app/oracle/exp configured is not valid storage type. Return code [37].
2013-10-14 01:21:41.455: [  OCRRAW][947927360]propriodvch: Error  [8] returned device check for [/u01/app/oracle/exp]
2013-10-14 01:21:41.455: [  OCRRAW][947927360]dev_replace: master could not verify the new disk (8)
[  OCRSRV][947927360]proas_replace_disk: Failed in changing configurations in the Master 8

If we can see from the above crsd.log, its mentioned as STORAGE TYPE is not a valid one.

When tried the below command by creating a dummy file(ocr.dat) with touch command. Received the below errors,

sudo ocrconfig -add /u01/app/oracle/exp/ocr.dat

Errors Recorded in crsd.log :

+ASM1 @ hostd01:/u01/app/11.2/grid1/log/hostd01/crsd
> tail -f crsd.log
2013-10-14 01:15:23.108: [   CRSPE][1187469632] {2:40710:2858} Expression Filter : ((NAME == ora.scan1.vip) AND (LAST_SERVER == hostd02))
2013-10-14 01:16:04.995: [  OCROSD][945826112]utstoragetypecommon: Oracle Cluster Registry does not support the storage type configured.
OCR can be configured on: ASM, OCFS, OCFS2, NFS, Block Device, Character Device, VxFS
2013-10-14 01:16:04.995: [  OCROSD][945826112]utdvch:-1: New location /u01/app/oracle/exp/ocr.dat configured is not valid storage type. Return code [37].
2013-10-14 01:16:04.995: [  OCRRAW][945826112]propriodvch: Error  [8] returned device check for [/u01/app/oracle/exp/ocr.dat]
2013-10-14 01:16:04.995: [  OCRRAW][945826112]dev_replace: master could not verify the new disk (8)
[  OCRSRV][945826112]proas_replace_disk: Failed in changing configurations in the Master 8

If we look in the error log details, it shows as,

OCR can be configured on: ASM, OCFS, OCFS2, NFS, Block Device, Character Device, VxFS

Checking the File system type,

df -T /u01/app/oracle/exp


+ASM1 @ hostd01:/u01
> df -T /u01/app/oracle/exp
Filesystem    Type   1K-blocks      Used Available Use% Mounted on
/dev/mapper/vg00-oracle--exp
              ext3     5160576   4205536    692896  86% /u01/app/oracle/exp

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

As the file system type is ext3, i have add the alternate location for OCR to ASM diskgroup.


+ASM1 @ hostd01:/u01/app/oracle/backup
> sudo ocrconfig -add +test_DATA
+ASM1 @ hostd01:/u01/app/oracle/backup

+ASM1 @ hostd01:/u01/app/oracle/backup
> ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       3444
         Available space (kbytes) :     258676
         ID                       : 1882253608
         Device/File Name         :  +OCR_VOTE
                                    Device/File integrity check succeeded
         Device/File Name         : +test_DATA
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check bypassed due to non-privileged user