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
=====================================================================================================================