OGG-00869, ORA-04098: trigger 'SIOD.EMP' is invalid and failed re-validation (status = 4098).

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

2014-11-21 02:13:33  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, RTab.prm:  OCI Error
ORA-04098: trigger 'SIOD.EMP' is invalid and failed re-validation (status = 4098). INSERT /*+ RESTRICT_ALL_REF_CONS */ INTO "SIOD"."TAB_1" ("ID",

### Full Error

2014-11-21 02:13:27  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, RTab.prm:  REPLICAT RTab started.
2014-11-21 02:13:32  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, RTab.prm:  No unique key is defined for table 'TAB_1'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
2014-11-21 02:13:33  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, RTab.prm:  OCI Error ORA-04098: trigger 'SIOD.EMP' is invalid and failed re-validation (status = 4098).
2014-11-21 02:13:33  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, RTab.prm:  Error mapping from SIOD.TAB_1 to SIOD.TAB_1.
2014-11-21 02:13:33  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, RTab.prm:  PROCESS ABENDING.

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

Oracle Golden Gate Replicat process ABENDED with the above error.

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

start replicat RTab

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check ggserr.log file
#########################################

2014-11-21 02:13:27  INFO    OGG-00996  Oracle GoldenGate Delivery for Oracle, RTab.prm:  REPLICAT RTab started.
2014-11-21 02:13:32  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, RTab.prm:  No unique key is defined for table 'TAB_1'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
2014-11-21 02:13:33  WARNING OGG-00869  Oracle GoldenGate Delivery for Oracle, RTab.prm:  OCI Error ORA-04098: trigger 'SIOD.EMP' is invalid and failed re-validation (status = 4098).
2014-11-21 02:13:33  WARNING OGG-01003  Oracle GoldenGate Delivery for Oracle, RTab.prm:  Repositioning to rba 154752882 in seqno 7060.
2014-11-21 02:13:33  ERROR   OGG-01296  Oracle GoldenGate Delivery for Oracle, RTab.prm:  Error mapping from SIOD.TAB_1 to SIOD.TAB_1.
2014-11-21 02:13:33  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, RTab.prm:  PROCESS ABENDING.

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

Above error indicates that a trigger is in INVALID status due to which REPLICAT is failing to start.

=====================================================================================================================

#########################################
# 3) Check Trigger Status
#########################################

SET LINES 200
COL OBJECT_NAME FOR A30
COL OBJECT_TYPE FOR A25
COL Owner FOR A25

Select owner,object_name,object_type,status,created,LAST_DDL_TIME from dba_objects where object_name like 'EMP';

OWNER                     OBJECT_NAME                    OBJECT_TYPE               STATUS  CREATED   LAST_DDL_
------------------------- ------------------------------ ------------------------- ------- --------- ---------
SIOD         EMP           TRIGGER                   INVALID 29-OCT-14 21-NOV-14

set lines 200
col triggering_event for a35
col table_owner for a25

Select owner,trigger_name,triggering_Event,table_owner,table_name,status from dba_triggers where trigger_name='EMP';

OWNER                          TRIGGER_NAME                   TRIGGERING_EVENT                    TABLE_OWNER               TABLE_NAME                     STATUS
------------------------------ ------------------------------ ----------------------------------- ------------------------- ------------------------------ --------
SIOD             EMP           INSERT OR UPDATE                    SIOD       TAB_1     ENABLED

=====================================================================================================================
From above we can see that TRIGGER is in ENABLED at the database level but it is in INVALID status.
=====================================================================================================================

#########################################
# Solution Available
#########################################

a) Fix the errors in trigger compilation.
b) We can use DBOPTIONS SUPPRESSTRIGGER to prevent Trigger from its execution.
c) Disable the trigger if not needed.

=====================================================================================================================

##########################
## Solution ( a )
##########################

a) Fix the errors in trigger compilation.

Alter trigger SIOD.EMP compile;

SQL> Alter trigger SIOD.EMP compile;
Warning: Trigger altered with compilation errors.

SQL> sho err
Errors for TRIGGER SIOD.EMP:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/2      PLS-00049: bad bind variable 'NEW.START_DATE'

=====================================================================================================================
Compilation of Trigger is not working as there is some definition error in the trigger.
=====================================================================================================================

##########################
## Solution ( b )
##########################

b) We can use DBOPTIONS SUPPRESSTRIGGER to prevent Trigger from its execution.

GGSCI (host01.example.com) 8> view param RTab

replicat RTab
USERID gold@oralin, PASSWORD ****
ASSUMETARGETDEFS
GROUPTRANSOPS 2000
EOFDELAYCSECS 20

-- To suppress the trigger over the target table.
DBOPTIONS SUPPRESSTRIGGERS
DBOPTIONS DEFERREFCONST

#### Modified the Replicat Parameter file and included "DBOPTIONS SUPPRESSTRIGGERS" keyword and restarted the Replicat which again failed with the below error.

#### oggerr.log

2014-11-21 06:45:12  INFO    OGG-00995  Oracle GoldenGate Delivery for Oracle, RTab.prm:  REPLICAT RTAB starting.
2014-11-21 06:45:12  INFO    OGG-03035  Oracle GoldenGate Delivery for Oracle, RTab.prm:  Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
2014-11-21 06:45:12  INFO    OGG-03501  Oracle GoldenGate Delivery for Oracle, RTab.prm:  WARNING: NLS_LANG environment variable is invalid or not set. Using operating system character set value of AL32UTF8.
2014-11-21 06:45:12  ERROR   OGG-01746  Oracle GoldenGate Delivery for Oracle, RTab.prm:  Support for parameter SUPPRESSTRIGGERS is not available in the RDBMS version you are using.
2014-11-21 06:45:12  ERROR   OGG-01668  Oracle GoldenGate Delivery for Oracle, RTab.prm:  PROCESS ABENDING.

=====================================================================================================================
Tried placing the suppresstriggers options below userid also which didn't work out.
=====================================================================================================================

##########################
## Solution ( c )
##########################

c) Disable the trigger if not needed.

Alter trigger SIOD.EMP disable;

SQL> Alter trigger SIOD.EMP disable;

Trigger altered.

Select owner,trigger_name,triggering_Event,table_owner,table_name,status from dba_triggers where trigger_name='EMP';

OWNER                          TRIGGER_NAME                   TRIGGERING_EVENT                    TABLE_OWNER               TABLE_NAME                     STATUS
------------------------------ ------------------------------ ----------------------------------- ------------------------- ------------------------------ --------
SIOD             EMP         INSERT OR UPDATE                    SIOD       TAB_1     DISABLED

=====================================================================================================================
As the other options are not working we disabled the trigger as it is not needed and restarted the replicat which came up without any issues...
=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

ORA-10458: standby database requires recovery

We want to place the Standby database to READ ONLY mode which is in Managed recovery mode of state MOUNTED.

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

ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '+DATA/oralin/datafile/system.264.854321773'

### Full Error

SQL> alter database open read only;
alter database open read only
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '+DATA/oralin/datafile/system.264.854321773'

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

Error occured while placing a standby database which is in mount stage to READ ONLY mode in 11.2.0.3.0 version

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

alter database open read only;

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check MRP is running
#########################################

SQL> !ps -ef|grep mrp
oracle   26516     1  0 15:09 ?        00:00:00 ora_mrp0_oralin2
oracle   26753 21120  0 15:10 pts/2    00:00:00 /bin/bash -c ps -ef|grep mrp
oracle   26755 26753  0 15:10 pts/2    00:00:00 grep mrp

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

To make standby database READ ONLY, all the datafiles should be consistent. Error is occuring because MRP is still running in the database which keeps the media recover on.

=====================================================================================================================

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

#### Stop the Media recovery and then place the database in read only mode..

SQL> recover managed standby database cancel;
Media recovery complete.

SQL>  alter database open read only;
Database altered.

=====================================================================================================================
Then if you want to start the MRP, we can do like below,
=====================================================================================================================

SQL> Recover managed standby database disconnect from session using current logfile;
Media recovery complete.
SQL>

SQL> !ps -ef|grep mrp
oracle   26516     1  0 15:09 ?        00:00:00 ora_mrp0_oralin2
oracle   26753 21120  0 15:10 pts/2    00:00:00 /bin/bash -c ps -ef|grep mrp
oracle   26755 26753  0 15:10 pts/2    00:00:00 grep mrp

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

ORA-19505: failed to identify file "/opt/backup/arch/orahost3_oralin_oralin_20140707_1096_1_1_arch"

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

ORA-19505: failed to identify file "/opt/backup/arch/orahost3_oralin_oralin_20140707_1096_1_1_arch"

### Full Error

RMAN> restore archivelog logseq 5481 thread 2;
Starting restore at 07-JUL-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=5481
channel ORA_DISK_1: reading from backup piece /opt/backup/arch/orahost3_oralin_oralin_20140707_1096_1_1_arch
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /opt/backup/arch/orahost3_oralin_oralin_20140707_1096_1_1_arch
ORA-19505: failed to identify file "/opt/backup/arch/orahost3_oralin_oralin_20140707_1096_1_1_arch"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

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

Error occured while trying to restore a archivelog from a backup in 11.2.0.3.0 version of RAC Database.

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

restore archivelog logseq 5481 thread 2;

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check Archive log Information
#########################################

set lines 200
col name for a75
col thread# for 9999
col creator for a8
col completion_time for a20

Select name,creator,thread#,sequence#,applied,status,to_char(first_time,'DD-MON-YYYY HH24:MI:SS') "first_time",to_char(completion_time,'DD-MON-YYYY HH24:MI:SS') "completion_time",resetlogs_id,archived,backup_count
from v$archived_log where sequence#>=5481 and thread#=2 order by sequence# desc;

NAME                                                                             CREATOR  THREAD#  SEQUENCE# APPLIED   S completion_time      RESETLOGS_ID ARC BACKUP_COUNT
-------------------------------------------------------------------------------- -------- ------- ---------- --------- - -------------------- ------------ --- ------------
+RECO/oralin/archivelog/2014_07_07/thread_2_seq_5484.411.852301159              ARCH           2       5484 NO        A 07-JUL-2014 14:19:32    825774564 YES            0
+RECO/oralin/archivelog/2014_07_07/thread_2_seq_5483.683.852301111              ARCH           2       5483 NO        A 07-JUL-2014 14:18:46    825774564 YES            0
                                                                                 ARCH           2       5482 NO        D 07-JUL-2014 12:01:56    825774564 YES            0
                                                                                 ARCH           2       5481 NO        D 07-JUL-2014 12:01:54    825774564 YES            1

Backup_count is 1, so archive log sequence 5481 is backed up once.

[oracle@orahost3 arch]$ ls -ltr
total 10648680
-rw-r----- 1 oracle oinstall 2199391232 Jul  7 12:04 orahost3_oralin_oralin_20140707_1096_1_1_arch
[oracle@orahost3 arch]$ 

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

RMAN tries to fetch the backup piece to restore the archive log but it can't find the backup piece.

This is a RAC cluster database. Backups are scheduled to run from orahost3 server. Restore command was run from orahost1 server.

=====================================================================================================================

#########################################
# 3) Reproduce the Error
#########################################

[oracle@orahost1 srini]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jul 7 15:20:31 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: oralin (DBID=422297444)
RMAN> restore archivelog logseq 5481 thread 2;
Starting restore at 07-JUL-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=5481
channel ORA_DISK_1: reading from backup piece /opt/backup/arch/orahost3_oralin_oralin_20140707_1096_1_1_arch
channel ORA_DISK_1: ORA-19870: error while restoring backup piece /opt/backup/arch/orahost3_oralin_oralin_20140707_1096_1_1_arch
ORA-19505: failed to identify file "/opt/backup/arch/orahost3_oralin_oralin_20140707_1096_1_1_arch"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
failover to previous backup
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/07/2014 15:20:54
RMAN-06026: some targets not found - aborting restore
RMAN-06025: no backup of archived log for thread 2 with sequence 5481 and starting SCN of 9619365298 found to restore
RMAN>

=====================================================================================================================

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

Backups are scheduled to run in orahost3, and the backup mount point /opt seems to be not a shared one.

Backup mount point mostly will be a shared one in RAC databases, but looks like in this server has some exemption for it.

Ran a restore by logging into orahost3 and the restore went successful.

=====================================================================================================================
In case if you encounter the same issues, please check the backup pieces in all the nodes of a cluster before coming to the conclusion that there is no backups available.
=====================================================================================================================

[oracle@orahost3 ~]$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Mon Jul 7 15:23:06 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
connected to target database: oralin (DBID=422297444)
RMAN> restore archivelog logseq 5481 thread 2;
Starting restore at 07-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=566 instance=oralin3 device type=DISK
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=5481
channel ORA_DISK_1: reading from backup piece /opt/backup/arch/orahost3_oralin_oralin_20140707_1096_1_1_arch
channel ORA_DISK_1: piece handle=/opt/backup/arch/orahost3_oralin_oralin_20140707_1096_1_1_arch tag=%TAG
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 07-JUL-14
RMAN>

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

Restore of Archive log gives RMAN-20242: specification does not match any archived log in the repository

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

RMAN-20242: specification does not match any archived log in the repository

### Full Error

allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11657 instance=oralin1 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 07/07/2014 15:20:43
RMAN-20242: specification does not match any archived log in the repository

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

Error occured while trying to restore an archive log in 11.2.0.3.0 version of RAC database.

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

restore archivelog logseq 5481;

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check Archive log Status
#########################################

set lines 200
col name for a75
col thread# for 9999
col creator for a8
col completion_time for a20

Select name,creator,thread#,sequence#,applied,status,to_char(first_time,'DD-MON-YYYY HH24:MI:SS') "first_time",to_char(completion_time,'DD-MON-YYYY HH24:MI:SS') "completion_time",resetlogs_id,archived,backup_count
from v$archived_log where sequence#>=5481 and thread#=2 order by sequence# desc;

NAME                                                                             CREATOR  THREAD#  SEQUENCE# APPLIED   S completion_time      RESETLOGS_ID ARC BACKUP_COUNT
-------------------------------------------------------------------------------- -------- ------- ---------- --------- - -------------------- ------------ --- ------------
+RECO/oralin/archivelog/2014_07_07/thread_2_seq_5484.411.852301159              ARCH           2       5484 NO        A 07-JUL-2014 14:19:32    825774564 YES            0
+RECO/oralin/archivelog/2014_07_07/thread_2_seq_5483.683.852301111              ARCH           2       5483 NO        A 07-JUL-2014 14:18:46    825774564 YES            0
                                                                                 ARCH           2       5482 NO        D 07-JUL-2014 12:01:56    825774564 YES            0
                                                                                 ARCH           2       5481 NO        D 07-JUL-2014 12:01:54    825774564 YES            1

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

Error is occuring when a restore of archive logs is done. v$archived_log shows backup_count as 1 which means the archive log 5481 sequence is backed up once.

=====================================================================================================================

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

Step 1) shows backup has been taken, but RMAN says that backup is not available.

Well the database in which i'm playing is a RAC database. When it comes to rac cluster database we have to use thread# with the sequence number.

restore archivelog logseq 5481 thread 2;

RMAN> restore archivelog logseq 5481 thread 2;
Starting restore at 07-JUL-14
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=566 instance=oralin3 device type=DISK
channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=2 sequence=5481
channel ORA_DISK_1: reading from backup piece /opt/backup/arch/erwin_ch2_29_oralin_oralin_20140707_1096_1_1_arch
channel ORA_DISK_1: piece handle=/opt/backup/arch/erwin_ch2_29_oralin_oralin_20140707_1096_1_1_arch tag=%TAG
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 07-JUL-14
RMAN>

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

DBMS_STATS.GATHER_TABLE_STATS gives ORA-20005: object statistics are locked (stattype = ALL)

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

ORA-20005: object statistics are locked (stattype = ALL)

### Full Error

SQL> exec dbms_stats.gather_table_Stats('STHIRUNAVUKKARASU','T1');
BEGIN dbms_stats.gather_table_Stats('STHIRUNAVUKKARASU','T1'); END;
*
ERROR at line 1:
ORA-20005: object statistics are locked (stattype = ALL)
ORA-06512: at "SYS.DBMS_STATS", line 23154
ORA-06512: at "SYS.DBMS_STATS", line 23205
ORA-06512: at line 1

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

Error occured while trying to gather statistics for a table is run in 11.2.0.3.5 version

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

exec dbms_stats.gather_table_Stats('STHIRUNAVUKKARASU','T1');

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check the table Statistics
#########################################

set lines 200
Select owner,table_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') from dba_tables where table_name='T1';

OWNER                          TABLE_NAME                     TO_CHAR(LAST_ANALYZED,'DD-MON
------------------------------ ------------------------------ -----------------------------
STHIRUNAVUKKARASU              T1                             10-JUL-2014 07:24:38

#### Check for table with Locked Statistics

SQL> select owner,table_name,stattype_locked from dba_tab_statistics where stattype_locked is not null and owner='STHIRUNAVUKKARASU';

OWNER                          TABLE_NAME                     STATT
------------------------------ ------------------------------ -----
STHIRUNAVUKKARASU              T1                             ALL

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

Error is occuring when stats for a single table is being gathered.

From Step 1), we can see Statistics has been locked for the table. Usually application developers or DBA's lock statistics for some table such that
the path oracle uses to fetch records from the table will not get changed.

=====================================================================================================================

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

#### In case if statistics for the table needs to be gathered, it can be done in 2 ways..

=====================================================================================================================
a) Gather Statistics with Force option
=====================================================================================================================

exec dbms_Stats.gather_table_stats(ownname=>'STHIRUNAVUKKARASU',tabname=>'T1',force=>TRUE);

SQL> exec dbms_Stats.gather_table_stats(ownname=>'STHIRUNAVUKKARASU',tabname=>'T1',force=>TRUE);
PL/SQL procedure successfully completed.
SQL>

Select owner,table_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') from dba_tables where table_name='T1';

OWNER                          TABLE_NAME                     TO_CHAR(LAST_ANALYZED,'DD-MON
------------------------------ ------------------------------ -----------------------------
STHIRUNAVUKKARASU              T1                             10-JUL-2014 07:26:16

select owner,table_name,stattype_locked from dba_tab_statistics where stattype_locked is not null and owner='STHIRUNAVUKKARASU';

OWNER                          TABLE_NAME                     STATT
------------------------------ ------------------------------ -----
STHIRUNAVUKKARASU              T1                             ALL

=====================================================================================================================
b) Unlock the table stats and run normal stats gather on that table.
=====================================================================================================================

exec dbms_stats.unlock_table_Stats('STHIRUNAVUKKARASU','T1');

SQL> exec dbms_stats.unlock_table_Stats('STHIRUNAVUKKARASU','T1');
PL/SQL procedure successfully completed.

SQL> select owner,table_name,stattype_locked from dba_tab_statistics where stattype_locked is not null and owner='STHIRUNAVUKKARASU';

no rows selected

SQL>

exec dbms_stats.gather_table_Stats('STHIRUNAVUKKARASU','T1');

SQL> exec dbms_stats.gather_table_Stats('STHIRUNAVUKKARASU','T1');
PL/SQL procedure successfully completed.

SQL>

SQL> Select owner,table_name,to_char(last_analyzed,'DD-MON-YYYY HH24:MI:SS') from dba_tables where table_name='T1';

OWNER                          TABLE_NAME                     TO_CHAR(LAST_ANALYZED,'DD-MON
------------------------------ ------------------------------ -----------------------------
STHIRUNAVUKKARASU              T1                             10-JUL-2014 07:28:18

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

Archive log files are not getting stored in DB_RECOVERY_FILE_DEST

Archive log files are configured to use db_Recovery_file_dest which has been set to ASM. But the archive log files are getting stored in $ORACLE_HOME/dbs instead of ASM disk group

**************************************** Step By Step Procedure ******************************************************

#########################################
# 1) Check Archive logs location
#########################################

set lines 200
col name for a90
col thread# for 9999
col creator for a8
col completion_time for a20

Select name,creator,thread#,sequence#,applied,status,to_char(completion_time,'DD-MON-YYYY HH24:MI:SS') "completion_time",archived,backup_count
from v$archived_log order by sequence#;

NAME                                                                                       CREATOR  THREAD#  SEQUENCE# APPLIED   S completion_time      ARC BACKUP_COUNT
------------------------------------------------------------------------------------------ -------- ------- ---------- --------- - -------------------- --- ------------
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_3_852382837.dbf  ARCH           1          3 NO        A 08-JUL-2014 18:22:57 YES            0
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_4_852382837.dbf  ARCH           1          4 NO        A 08-JUL-2014 18:23:36 YES            0
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_13_852382837.dbf ARCH           1         13 NO        A 09-JUL-2014 16:50:34 YES            0

=====================================================================================================================

#########################################
# 2) Check Log_Archive Destination
#########################################

archive Log List

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST,
Oldest online log sequence     14
Next log sequence to archive   17
Current log sequence           17
SQL>

SQL> sho parameter log_archive_dest_1
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=USE_DB_RECOVERY_FILE_
                                                 DEST, valid_for=(ALL_LOGFILES,
                                                  ALL_ROLES)

=====================================================================================================================

#########################################
# 3) So What's the Issue
#########################################

From Step 1), we can see that archive logs are getting stored in $ORACLE_HOME/dbs location

From Step 2), it clearly shows the db_recovery_file_dest is being used.

Seems like the parameters are configured correctly then why archive logs are not stored in ASM disk group.

=====================================================================================================================

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

Eventhough the configuration seems to be correct, there is a small mistake in defining log_archive_dest_1 parameter,

If we can see the keyword used in log_archive_dest_1 ( location=USE_DB_RECOVERY_FILE_DEST, )

A Comma (,) is being used at the end of the location keyword which seems to be the root cause of this issue.

#### Modifying the Parameter after removing comma (,)

Alter system set log_archive_dest_1 = 'location=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES, ALL_ROLES)' sid='*';

SQL> Alter system set log_archive_dest_1 = 'location=USE_DB_RECOVERY_FILE_DEST valid_for=(ALL_LOGFILES, ALL_ROLES)' sid='*';
System altered.

SQL> sho parameter log_archive_dest_1
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_1                   string      location=USE_DB_RECOVERY_FILE_
                                                 DEST valid_for=(ALL_LOGFILES,
                                                 ALL_ROLES)
SQL> Alter system switch logfile;
System altered.
SQL>

NAME                                                                                       CREATOR  THREAD#  SEQUENCE# APPLIED   S completion_time      ARC BACKUP_COUNT
------------------------------------------------------------------------------------------ -------- ------- ---------- --------- - -------------------- --- ------------
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_13_852382837.dbf ARCH           1         13 NO        A 09-JUL-2014 16:50:34 YES            0
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_14_852382837.dbf ARCH           1         14 NO        A 09-JUL-2014 16:50:34 YES            0
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_15_852382837.dbf ARCH           1         15 NO        A 09-JUL-2014 16:50:34 YES            0
/u01/app/oracle/product/11.2.0.3/dbhome_1/dbs/USE_DB_RECOVERY_FILE_DEST,1_16_852382837.dbf ARCH           1         16 NO        A 10-JUL-2014 12:16:19 YES            0
+ARCH/oralin/archivelog/2014_07_10/thread_1_seq_17.264.852568337                           ARCH           1         17 NO        A 10-JUL-2014 16:32:17 YES            0

The new archive logs are getting created in +ARCH disk group.

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

How to Reset the Password for SYSMAN user at OMS level and at Repository Database

It has been a long time we have used SYSMAN password and the password repository is not updated with the current Password.

So the only option left out is to reset the password for SYSMAN user at OMS level and also at Repository Database.

**************************************** Step By Step Procedure ******************************************************

Current SYSMAN Password is UNKNOWN

#########################################
# 1) Shutdown OMS
#########################################

set OMS environment and shut it down

emctl stop oms

[oracle@host01 ~]$ emctl stop oms
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Successfully Stopped
Oracle Management Server is Down
[oracle@host01 ~]$

=====================================================================================================================

#########################################
# 2) Reset SYSMAN Password
#########################################

We should have Repository Database SYS password to reset the SYSMAN user password

emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd sys123 -new_pwd sysman123

[oracle@host01 ~]$ emctl config oms -change_repos_pwd -use_sys_pwd -sys_pwd sys123 -new_pwd sysman123
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Changing passwords in backend ...
Passwords changed in backend successfully.
Updating repository password in Credential Store...
Successfully updated Repository password in Credential Store.
Restart all the OMSs using 'emctl stop oms -all' and 'emctl start oms'.
Successfully changed repository password.
[oracle@host01 ~]$

Note :
-------

a) -sys_pwd = Repository Database SYS user password
b) -new_pwd = New Password for SYSMAN User

=====================================================================================================================

#########################################
# 3) Stop OMS
#########################################

emctl stop oms

[oracle@host01 ~]$ emctl stop oms
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Stopping WebTier...
WebTier Successfully Stopped
Stopping Oracle Management Server...
Oracle Management Server Already Stopped
Oracle Management Server is Down
[oracle@host01 ~]$

=====================================================================================================================

#########################################
# 4) Start OMS
#########################################

emctl start oms

[oracle@host01 ~]$ emctl start oms
Oracle Enterprise Manager Cloud Control 12c Release 4
Copyright (c) 1996, 2014 Oracle Corporation.  All rights reserved.
Starting Oracle Management Server...
Starting WebTier...
WebTier Successfully Started
Oracle Management Server Successfully Started
Oracle Management Server is Up
[oracle@host01 ~]$

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

ORA-01184: logfile group 5 already exists

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

ORA-01184: logfile group 5 already exists

### Full Error

SQL> Alter database add logfile
  2  group 5 ('+RECO1/','+RECO2/','+RECO3/','+RECO4/') size 4096m;
Alter database add logfile
*
ERROR at line 1:
ORA-01184: logfile group 5 already exists

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

Error occured while trying to add new redo log group in 11.2.0.3.0 version

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

Alter database add logfile group 5 ('+RECO1/','+RECO2/','+RECO3/','+RECO4/') size 4096m;

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check Online Redo Log Available
#########################################

SQL> select group# from v$log;
    GROUP#
----------
        1
        2
        3
        4

Here we can see only 4 online redo log groups..

=====================================================================================================================

#########################################
# 2) Check Standby Redo Logs
#########################################

SQL> select group# from v$standby_log;
    GROUP#
----------
         5
         6
         7
         8
         9

=====================================================================================================================

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

#### From step 2) we can see that standby redo logs are created with group 5

#### Below command completed successfully

Alter database add logfile group 10 ('+RECO1/','+RECO2/','+RECO3/','+RECO4/') size 4096m;

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

How to reset the Password for ASMSNMP user in an ASM instance?

**************************************** Step By Step Procedure ******************************************************

Well Resetting the password for ASMSNMP user is same as of any other oracle user.

Here we will see 2 methods to achieve it.

#########################################
# 1) Using Alter User Command
#########################################

Set ASM Environment and Login to the instance as "sysasm" privilege and reset password like below.

SQL> Alter user asmsnmp identified by welcome123;
User altered.

=====================================================================================================================

#########################################
# 2) Using ASMCMD Prompt
#########################################

We can modify the password for ASMSNMP user using asmcmd command "orapwusr"

orapwusr --modify --password asmsnmp

[oracle@host01 ~]$ asmcmd -p
ASMCMD [+] > lspwusr
Username sysdba sysoper sysasm
     SYS   TRUE    TRUE   TRUE
 ASMSNMP   TRUE   FALSE  FALSE
ASMCMD [+] >

ASMCMD [+] > orapwusr --modify --password asmsnmp
Enter password: **********
ASMCMD [+] > 

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

ORA-38709: Recovery Area is not enabled.

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

ORA-38709: Recovery Area is not enabled.

### Full Error

SQL> Alter database flashback on;
Alter database flashback on
*
ERROR at line 1:
ORA-38706: Cannot turn on FLASHBACK DATABASE logging.
ORA-38709: Recovery Area is not enabled.

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

Error occured while enabling flashback in 11.2.0.3.0 version

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

Alter database flashback on;

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check FRA
#########################################

SQL> sho parameter recovery
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                string
db_recovery_file_dest_size           big integer 80000M

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

Flashback logs are designed to get stored in Flash recovery area, so before enabling flashback in the database, we need to make sure that FRA is set.

=====================================================================================================================

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

#### Set FRA

SQL> Alter system set db_recovery_file_dest='+ARCH';

System altered.

SQL> sho parameter reco

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time        integer     7
db_recovery_file_dest                string      +ARCH
db_recovery_file_dest_size           big integer 80000M

=====================================================================================================================
 Enabling flashback worked now
=====================================================================================================================

SQL> Alter database flashback on;

Database altered.

select db_unique_name,log_mode,open_mode,flashback_on,force_logging from v$database;
SQL>

DB_UNIQUE_NAME                 LOG_MODE     OPEN_MODE            FLASHBACK_ON       FOR
------------------------------ ------------ -------------------- ------------------ ---
oralin                         ARCHIVELOG   READ WRITE           YES                NO

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

RMAN Duplicate Command gives, RMAN-05541: no archived logs found in target database

RMAN-05541: no archived logs found in target database

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

RMAN-05541: no archived logs found in target database

### Full Error

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 07/08/2014 18:12:40
RMAN-05501: aborting duplication of target database
RMAN-05541: no archived logs found in target database

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

Error occured while a doing a duplication for a standby database using active database duplication in 11.2.0.3.0 version

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

Duplicate command.

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check Archive Log Info
#########################################

#### In Primary (Target) database

SQL> archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   1
Current log sequence           1
SQL>

=====================================================================================================================

#########################################
# 2) Reason for Failure
#########################################

Error is occuring in Primary (Target) database which is in archivelog mode but there is no archived log records in controlfile.

The primary database has been built newly and from Step 1) we can see that Current log sequence is 1.

So when RMAN tries to run duplication command it couldn't find any archive logs.

=====================================================================================================================

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

Archive the current log before running duplication command, if the target database current sequence is 1.

Alter system archive log current;

( or )

Alter system switch logfile;

SQL> Alter system switch logfile;

System altered.

SQL>  archive log list
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Next log sequence to archive   2
Current log sequence           2
SQL>

=====================================================================================================================
Now the duplication command has been run and its successful.
=====================================================================================================================

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

ORA-15012: ASM file '+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3614.32449.850999411' does not exist

Full Backup of oralin database is Failing with the below errors,

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

ORA-15012: ASM file '+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3614.32449.850999411' does not exist

### Full Error

released channel: oralin_incr
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 07/01/2014 03:57:41
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file +RECO/oralin/archivelog/2014_06_23/thread_1_seq_3614.32449.850999411
ORA-17503: ksfdopn:2 Failed to open file +RECO/oralin/archivelog/2014_06_23/thread_1_seq_3614.32449.850999411
ORA-15012: ASM file '+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3614.32449.850999411' does not exist

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

Error occured while a scheduled FULL backup is run in 11.2.0.3.0 version

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

Full Database backup command

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Check Archive Log Availability
#########################################

set lines 200
col name for a80
col thread# for 9999
col creator for a8
col completion_time for a20

Select name,creator,thread#,sequence#,applied,status,to_char(completion_time,'DD-MON-YYYY HH24:MI:SS') "completion_time",resetlogs_id,archived,backup_count
from v$archived_log where sequence#>=3614 order by sequence# desc;

NAME                                                                             CREATOR  THREAD#  SEQUENCE# APPLIED   S completion_time      RESETLOGS_ID ARC BACKUP_COUNT
-------------------------------------------------------------------------------- -------- ------- ---------- --------- - -------------------- ------------ --- ------------
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3620.41615.851000431             ARCH           1       3620 NO        A 23-JUN-2014 13:00:40    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3619.11237.851000269             ARCH           1       3619 NO        A 23-JUN-2014 12:57:58    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3618.31003.851000109             ARCH           1       3618 NO        A 23-JUN-2014 12:55:17    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3617.25650.850999947             ARCH           1       3617 NO        A 23-JUN-2014 12:52:37    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3616.26704.850999771             ARCH           1       3616 NO        A 23-JUN-2014 12:49:40    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3615.42478.850999603             ARCH           1       3615 NO        A 23-JUN-2014 12:46:52    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3614.32449.850999411             ARCH           1       3614 NO        A 23-JUN-2014 12:43:40    806656703 YES            0

=====================================================================================================================
Sequence 3614 Shows as Available
=====================================================================================================================

#########################################
# 2) Check the Archive log Physically
#########################################

ASMCMD [+] > cd +RECO/oralin/archivelog/2014_06_23/
ASMCMD-08002: entry '2014_06_23' does not exist in directory '+RECO/oralin/archivelog/'
ASMCMD [+] > cd +RECO/oralin/archivelog
ASMCMD [+RECO/oralin/archivelog] > ls
2014_06_27/
2014_06_28/
2014_06_29/
2014_06_30/
2014_07_01/
ASMCMD [+RECO/oralin/archivelog] >

=====================================================================================================================
From Above output we can see that there is no archivelogs file present physically, but it still shows as available in v$archived_log
=====================================================================================================================

#########################################
# 3) Reason for Failure
#########################################

Error is occurring because there is no archivelog files physically available, but as per control file, the archive logs are still available.

So RMAN tries to backup the archivelogs and when it finds out its not available, backup fails.

=====================================================================================================================

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

Looks like archive log files might have got deleted after it got shipped to standby due to wrong configuration.

From the V$archived_log column, backup_count we can see that its not backed up ever. So we cant restore the archive logs. In this case, we have a data loss.

We have to find out how the archive log got deleted. The database which i have faced is not that much important, so i am proceeding with the workaround.

=====================================================================================================================
Crosscheck all the archive logs using RMAN
=====================================================================================================================

Run below command in RMAN,

crosscheck archivelog all;

#### Now check the archive log status.

NAME                                                                             CREATOR  THREAD#  SEQUENCE# APPLIED   S completion_time      RESETLOGS_ID ARC BACKUP_COUNT
-------------------------------------------------------------------------------- -------- ------- ---------- --------- - -------------------- ------------ --- ------------
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3620.41615.851000431             ARCH           1       3620 NO        X 23-JUN-2014 13:00:40    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3619.11237.851000269             ARCH           1       3619 NO        X 23-JUN-2014 12:57:58    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3618.31003.851000109             ARCH           1       3618 NO        X 23-JUN-2014 12:55:17    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3617.25650.850999947             ARCH           1       3617 NO        X 23-JUN-2014 12:52:37    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3616.26704.850999771             ARCH           1       3616 NO        X 23-JUN-2014 12:49:40    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3615.42478.850999603             ARCH           1       3615 NO        X 23-JUN-2014 12:46:52    806656703 YES            0
+RECO/oralin/archivelog/2014_06_23/thread_1_seq_3614.32449.850999411             ARCH           1       3614 NO        X 23-JUN-2014 12:43:40    806656703 YES            0

Now we can see that the unavailable archivelogs are marked as EXPIRED.

Tried running the backup now and its successful.  ( Archive logs which are marked EXPIRED are skipped )

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

Run scp in Background By Providing Password @ ForeGround

Usually we use scp command to transfer a file from one server to another server. Say for example if we want to transfer 100GB file then best idea is to run at background.

But running scp at background has a problem which is "password". scp always prompt for a password which cannot be given in shell script. ( Check end of the post for other method to run as a shell script )

Here we will see a simple method to scp at background by providing password at the foreground.

**************************************** Step By Step Procedure ******************************************************

Source Server : host01
Source Path : /u01/backup/test.dmp Destination server : host02
Destination Path : /u02/test.dmp

#########################################
# 1) scp Command
#########################################

nohup scp /u01/backup/test.dmp host02:/u02/test.dmp > nohup.out 2>&1

=====================================================================================================================

#########################################
# 2) Run scp Command
#########################################

Once we run the above command it will prompt for Password, Input Password and then Press Ctrl + Z ( Now scp is stopped )

[oracle@host01 srini]$ nohup scp /u01/backup/test.dmp host02:/u02/test.dmp > nohup.out 2>&1
oracle@host02's password:
^Z
[1]+  Stopped                 nohup scp /u01/backup/test.dmp host02:/u02/test.dmp > nohup.out 2>&1
You have new mail in /var/spool/mail/oracle
[oracle@host01 srini]$

=====================================================================================================================

#########################################
# 3) Start the Process in Background
#########################################

Type "bg" command and scp process will start in background

[oracle@host01 srini]$ bg
[1]+ nohup scp /u01/backup/test.dmp host02:/u02/test.dmp > nohup.out 2>&1 &
[oracle@host01 srini]$

=====================================================================================================================

#########################################
# 4) Check if scp is running
#########################################

Type "jobs", it will show the scp job is running

[oracle@host01 srini]$ jobs
[1]+  Running                 nohup scp /u01/backup/test.dmp host02:/u02/test.dmp > nohup.out 2>&1 &
[oracle@host01 srini]$ 

=====================================================================================================================

#########################################
# 5) Run scp at Fore Ground
#########################################

Type "fg", it will show run the scp in foreground instead of background

[oracle@host01 srini]$ fg
nohup scp /u01/backup/test.dmp host02:/u02/test.dmp > nohup.out 2>&1

=====================================================================================================================

#########################################
# 6) Other Methods to run scp @ Background
#########################################

a) Using Expect Script
b) SSHPass
c) Establish ssh connectivity by generating keygen ( http://stepintooracledba.blogspot.com/2013/12/configure-ssh-manually-for-rac-4-node.html )

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

DGMRL shows ORA-16664: unable to receive the result from a database

We have configured a new dataguard database. So we want to verify whether the dataguard configuration is set up good.

So we have used 12c Cloud control to verify the Data Guard Configuration and it has ended with the above error.

How to Verify Dataguard Configuration using 12c Cloud Control?

http://stepintooracledba.blogspot.com/2014/06/verify-data-guard-configuration-using.html

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

ORA-16664: unable to receive the result from a database

### Full Error

DGMGRL> show configuration;
Configuration - oralin_dg
  Protection Mode: MaxPerformance
  Databases:
    oralin   - Primary database
    oralin_stdby - Physical standby database
      Error: ORA-16664: unable to receive the result from a database
Fast-Start Failover: DISABLED
Configuration Status:
ERROR
DGMGRL>

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

Error occured while running a "show configuration" command in DGMGRL in 11.2.0.3.0 version

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

In DGMGRL,

show configuration;

**************************************** Step By Step Analysis ******************************************************

#########################################
# 1) Verify Data Guard Configuration in 12c Cloud Control
#########################################

Initializing
Connected to instance host01:oralin
Starting alert log monitor...
Updating Data Guard link on database homepage...
WARNING: Broker name (oralin) and target name (oralin_host01) do not match.
WARNING: The broker name will be renamed to match the target name.
WARNING: Broker name (oralin_stdby) and target name (oralin_stdby_host02) do not match.
WARNING: The broker name will be renamed to match the target name.
Skipping verification of fast-start failover static services check.
Data Protection Settings:
  Protection mode : Maximum Performance
  Redo Transport Mode settings:
    oralin: ASYNC
    oralin_stdby: ASYNC
  Checking standby redo log files.....not checked due to broker name mismatch. Run verify again.
Checking Data Guard status
  oralin : Normal
  oralin_stdby : ORA-16664: unable to receive the result from a database
Checking inconsistent properties
Checking agent status
WARNING: No credentials available for target. host01
Attempting agent ping ... Down
WARNING: No credentials available for target. host02
Attempting agent ping ... Down
Processing completed.

=====================================================================================================================
Verification of Data Guard in 12c oem gives Below Error for standby Database
ORA-16664: unable to receive the result from a database
=====================================================================================================================

#########################################
# 2) Reproduce the Error
#########################################

Show configuration in DGMGRL in standby site gives the error.

[oracle@host02 trace]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show configuration;
Configuration - oralin_dg
  Protection Mode: MaxPerformance
  Databases:
    oralin   - Primary database
    oralin_stdby - Physical standby database
Fast-Start Failover: DISABLED
Configuration Status:
ORA-16501: the Data Guard broker operation failed
ORA-16625: cannot reach database "oralin"
DGM-17017: unable to determine configuration status
DGMGRL>

=====================================================================================================================

#########################################
# 3) Check DRC logs
#########################################

#### Primary Database Log

/u01/app/oracle/diag/rdbms/oralin/oralin/trace/drcoralin.log

[oracle@host01 trace]$ vi drcoralin.log
05/31/2014 18:18:37
Site oralin_stdby returned ORA-16664.
Data Guard Broker Status Summary:
  Type                        Name                             Severity  Status
  Configuration               oralin_dg                        Warning  ORA-16607
  Primary Database            oralin                             Success  ORA-00000
  Physical Standby Database   oralin_stdby                             Error  ORA-16664

#### Standby Database Log

/u01/app/oracle/diag/rdbms/oralin_stdby/oralin_stdby/trace/drcoralin_stdby.log

[oracle@host02 trace]$ vi drcoralin_stdby.log
05/31/2014 18:34:38
Property 'DbFileNameConvert' has inconsistent values:METADATA='+DATA, +DATA', SPFILE='+DATA/oralin,+DATA/oralin_stdby', DATABASE='+DATA, +DATA'
Property 'LogFileNameConvert' has inconsistent values:METADATA='+RECO, +RECO, +DATA, +DATA', SPFILE='+DATA/oralin,+DATA/oralin_stdby,+RECO/oralin,+RECO/oralin_stdby', DATABASE='+RECO, +RECO, +DATA, +DATA'
Failed to send message to site oralin. Error code is ORA-16501.
[oracle@host02 trace]$

=====================================================================================================================
From Standby Database drcoralin_stdby.log we can see that there are inconsistent Values set
=====================================================================================================================

#########################################
# 4) Check Inconsistent Properties
#########################################

show database 'oralin_stdby' InconsistentProperties;

[oracle@host02 trace]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show database 'oralin_stdby' InconsistentProperties;
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
         oralin_stdby    DbFileNameConvert         +DATA, +DATA +DATA/oralin,+DATA/oralin_stdby         +DATA, +DATA
         oralin_stdby   LogFileNameConvert +RECO, +RECO, +DATA, +DATA +DATA/oralin,+DATA/oralin_stdby,+RECO/oralin,+RECO/oralin_stdby +RECO, +RECO, +DATA, +DATA
DGMGRL>

=====================================================================================================================

#########################################
# 5) Reason for Failure
#########################################

From the above output, we can find out the reason for failure is because of the Inconsistent Properties of db_file_name_convert and log_file_name_convert set at the database level in standby.

=====================================================================================================================

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

Alter both the parameter in standby Database.

ALTER SYSTEM SET log_file_name_convert='+DATA/oralin_stdby, +DATA/oralin, +RECO/oralin_stdby, +RECO/oralin' SCOPE=SPFILE;

ALTER SYSTEM SET db_file_name_convert='+DATA/oralin_stdby','+DATA/oralin' SCOPE=SPFILE;

=====================================================================================================================
Check Inconsistent Properties
=====================================================================================================================

[oracle@host02 ~]$ dgmgrl /
DGMGRL for Linux: Version 11.2.0.3.0 - 64bit Production
Copyright (c) 2000, 2009, Oracle. All rights reserved.
Welcome to DGMGRL, type "help" for information.
Connected.
DGMGRL> show database 'oralin_stdby' inconsistentproperties;
INCONSISTENT PROPERTIES
   INSTANCE_NAME        PROPERTY_NAME         MEMORY_VALUE         SPFILE_VALUE         BROKER_VALUE
DGMGRL>

=====================================================================================================================
Verification using 12c Is also Looks good.
=====================================================================================================================

=====================================================================================================================
 Comments Are Always welcome
=====================================================================================================================

Verify Data Guard Configuration using 12c Cloud Control

An Easiest way to check if your Data Guard is configured Correctly. This can be done using 12c Cloud Control.

12c Cloud Control uses DGMGRL to perform these validations at the background.

You have only 4 steps left to check your configuration.

**************************************** Step By Step Procedure ******************************************************

Step 1 :

Go to the Primary database Page and Click Availability à Verify Data Guard Configuration

Step 2 :

Provide the Named Credential to login and click continue

Step 3 :

Once we click Continue, Verification Process starts to Progress…

Step 4 :

Once Verification completes, we will get results like below. Please review the logs and everything should be ok. Click OK to Exit.

=====================================================================================================================

 Comments Are Always welcome

=====================================================================================================================