errors when running RMAN backup of the archivelogs

Experienced the following errors when running RMAN backup of the archivelogs:

RMAN-06059: expected archived log not found

ORA-19625: error identifying file /zklpdata/archive/zkpd/arch_1_44543_466886099.arc
ORA-27037: unable to obtain file status
IBM AIX RISC System/6000 Error: 2: No such file or directory
Additional information: 3

CAUSE

RMAN is attempting to backup this archivelog file but is not able to access the physical file on disk, because either the archivelog file is manually moved or deleted at the operating system level, and RMAN metadata is not updated.

SOLUTION

The steps required depends on if the archivelog file exists based on the name and location returned by the error.  Thus the first step is to check for the existence of the archivelog file. 

1.  If the archivelog files have been moved to a different directory, then they can be moved back to their original directory.

Otherwise, starting in Oracle9i, you can make RMAN aware of their new location using the following RMAN "catalog" command for each archivelog:

RMAN> catalog archivelog '/temp/archive/zkpd/arch_1_44543_466886099.arc';

Starting in Oracle10g, if several archivelog files are effected, you may issue the RMAN command "catalog start with" as follows:

RMAN> catalog start with '<new_directory_of_archivelogs>';

2.  If the archivelogs are no longer physically available, then you can issue the RMAN command "crosscheck copy of archivelog all".  This command checks for the physical existence of the archivelog file and will change the V$ARCHIVED_LOG.STATUS of the affected archivelog from "A" for AVAILABLE to "X" for EXPIRED.   Once the archivelog file has a status of X, RMAN will no longer attempt to backup this archivelog file. 

RMAN> crosscheck copy of archivelog all;

IMPORTANT NOTE: If there is no RMAN backup of the missing archivelog file, it is essential to perform a full-database backup. Otherwise, you may not be able to recover the database beyond missing log sequence number.




 Commands:
catalog start with '/home/oracle/DataBaseBackups/ArchiveDest/';

catalog start with '/home/oracle/DataBaseBackups/ArchiveDest/' NOPROMPT;

CATALOG DATAFILECOPY '/stage/users01.dbf';
CATALOG DATAFILECOPY '/u02/backup/users01.bak' LEVEL 0;
LIST COPY;


CATALOG BACKUPPIECE '/u01/c-874220581-20080315-01';

CATALOG CONTROLFILECOPY '/stage/control01.ctl'

CATALOG ARCHIVELOG '?/oradata/archive1_30.dbf','?/oradata/archive1_31.dbf','?/oradata/archive1_32.dbf';

ORACLE 10G standby database creation using rman

Step1: Take rman backup.

#!/bin/ksh
key=`date +%Y.%m.%d.%H.%M`
ldir=/oracle/exports
lname=db_name_stby_rman_bkp.log
logname=$ldir/$lname.$key
echo $logname

set ORACLE_SID=DB_NAME
echo $ORACLE_HOME
echo $ORACLE_SID

$ORACLE_HOME/bin/rman target sys/"password" nocatalog << EOF

spool log to $logname
run {
    allocate channel t1 type disk;
    allocate channel t2 type disk;
    allocate channel t3 type disk;
    allocate channel t4 type disk;
    backup as compressed backupset
    format '/oracle/exports/db_name_DB_%U_%t'
    (database);
    release channel t1;
    release channel t2;
    release channel t3;
    release channel t4;
    }

run {
    allocate channel t1 type disk;
    allocate channel t2 type disk;
    allocate channel t3 type disk;
    allocate channel t4 type disk;
    sql 'alter system archive log current';
    backup as compressed backupset
    format '/oracle/exports/db_name_arch_%t_%s.dbf'
    (archivelog all);
    release channel t1;
    release channel t2;
    release channel t3;
    release channel t4;
    }

run {
    allocate channel t1 device type disk format '/oracle/exports/db_name_stb_control_%U';
    backup current controlfile for standby ;
    }

quit

EOF

Step2: Transfer backup Dr server.

 FTP or SCP the backup pieces.
+ If the backups are on NFS mount then mount the NFS on standby server with the same name as you mounted on primary database.
+ Until 9i, you need to move the backup-pieces in exactly the same location on standby as they were created on primary.
+ From 10g onwards, use CATALOG BACKUPPIECE command if you are moving backup-pieces to a different location.
+ If the backups are on tape then make sure that you make proper changes on standby server so that you can restore the backups on standby server.

Step 3: Make proper changes in the parameter files of both primary and standby database
Add the below parameter in primary database parameter file :
log_archive_dest_2='SERVICE=STANDBY'

Step 4: Copy the primary database parameter file and make necessary changes :
db_unique_name='standby'
instance_name='standby'
db_file_name_convert='/u01/oracle/product/oradata/PROD/data/','/u01/oracle/product/PROD/oradata/standby/data/','/log_file_name_convert='/u01/oracle/product/oradata/PROD/log/','/u01/oracle/product/PROD/oradata/standby/log'
standby_archive_dest='/u01/oraclCopy the primary database parameter file and make necessary changes :
db_unique_name='standby'
instance_name='standby'
db_file_name_convert='/u01/oracle/product/oradata/PROD/data/','/u01/oracle/product/PROD/oradata/standby/data/','/log_file_name_convert='/u01/oracle/product/oradata/PROD/log/','/u01/oracle/product/PROD/oradata/standby/log'
standby_archive_dest='/u01/oracle/product/PROD/oradata/standby/arch1'e/product/PROD/oradata/standby/arch1'

Step 5: Restore database in dr :  
key=`date +%Y.%m.%d.%H.%M`
ldir=/oracle/exports/
lname=db_name_stby_reco_rman.log
logname=$ldir/$lname.$key

echo $logname

rman target sys/'password'@db_name  auxiliary / << EOF

spool log to $logname
run {
allocate auxiliary channel t1 device type disk;
allocate auxiliary channel t2 device type disk;
allocate auxiliary channel t3 device type disk;
allocate auxiliary channel t4 device type disk;
duplicate target database for standby
nofilenamecheck;
}
quit

EOF

Step6: Once restore is completed shutdown database.    
shutdown immediate;

step7: start the database in mrp mode 
  startup nomount  
alter database mount standby database;  
alter database recover managed standby database disconnect from session;

ORA-19815: WARNING: db_recovery_file_dest_size of 66571993088 bytes is 85.04% used, and has 9960423424 remaining bytes available.
************************************************************************
You have following choices to free up space from recovery area:
1. Consider changing RMAN RETENTION POLICY. If you are using Data Guard,
   then consider changing RMAN ARCHIVELOG DELETION POLICY.
2. Back up files to tertiary device such as tape using RMAN
   BACKUP RECOVERY AREA command.
3. Add disk space and increase db_recovery_file_dest_size parameter to
   reflect the new space.
4. Delete unnecessary files using RMAN DELETE command. If an operating
   system command was used to delete files, then use RMAN CROSSCHECK and
   DELETE EXPIRED commands.
************************************************************************

Pre-check:
--To check the location and size
 set lines 100
col name format a60
select  name , floor(space_limit / 1024 / 1024) "Size MB", ceil(space_used  / 1024 / 1024) "Used MB" from v$recovery_file_dest order by name;

show parameter db_recovery_file_dest_size

--To verify which is occupying space.
Select file_type, percent_space_used as used,percent_space_reclaimable as reclaimable,number_of_files as "number" from v$flash_recovery_area_usage;

Solution:

Login to database using rman
if it in prod try to connect to catalog /in DR just connect to database.
try to purge old backup and old archive logs
rman >  crosscheck archivelog all;

rman >  delete expired archivelog all;

if backup :
rman > CROSSCHECK BACKUP;

rman >Delete expired backup;

On your standby database, connect it through RMAN:
rman target sys/<pwd>@<standby-db>
RMAN>delete archivelog all completed before 'SYSDATE-7';


This might be because of your ARCHIVELOG DELETION POLICY not being set correctly in your standby database. Please run the command below. Is it set to "NONE"?
RMAN> show archivelog deletion policy;
When set to NONE, archivelogs are not considered reclaimable until they are backed up. If you only backup archivelogs on the primary database, you can set the archivelog deletion policy to "APPLIED ON STANDBY" on the standby database. This will make archivelogs on the standby database reclaimable as soon as they have been applied. Once they are reclaimable, they will be deleted from the Flash Recovery Area when there is space pressure.

You can find all the details in the documentation, http://docs.oracle.com/cd/E11882_01/server.112/e25608/rman.htm#BAJDJEBE

Traditionally, you set archivelog deletion policy to NONE where you take your backups (primary or standby). On the other side, where you don't want to take any backups, you can set it to APPLIED ON STANDBY.
Where backups are taken:
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

Where backups are NOT taken:
RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO APPLIED ON STANDBY;
If you change the archivelog deletion policy, the view v$flash_recovery_area_usage will reflect the change immediately. If I'm correct, and the policy is your problem, you should see that most of your archived redo logs are listed as reclaimable (if they have been applied to the standby database). Also, the alert log should show that logs are being deleted in order to make room for new ones as they are being transferred from the primary database

Session

Show all connected users

set lines 100 pages 999

col ID format a15

select username

,      sid || ',' || serial# "ID"

,      status

,      last_call_et "Last Activity"

from   v$session

where  username is not null

order by status desc

,        last_call_et desc

/

 Time since last user activity

set lines 100 pages 999

select username

,      floor(last_call_et / 60) "Minutes"

,      status

from   v$session

where  username is not null

order by last_call_et

/

 

Sessions sorted by logon time

set lines 100 pages 999

col ID  format a15

col osuser format a15

col login_time format a14

select  username

, osuser

, sid || ',' || serial# "ID"

, status

, to_char(logon_time, 'hh24:mi dd/mm/yy') login_time

, last_call_et

from v$session

where username is not null

order by login_time

/

 

Show user info including os pid

col "SID/SERIAL" format a10

col username format a15

col osuser format a15

col program format a40

select s.sid || ',' || s.serial# "SID/SERIAL"

, s.username

, s.osuser

, p.spid "OS PID"

, s.program

from v$session s

, v$process p

Where s.paddr = p.addr

order  by to_number(p.spid)

/

 

Show a users current sql

Select sql_text

from   v$sqlarea

where  (address, hash_value) in

(select sql_address, sql_hash_value 

        from v$session

        where username like '&username')

/

 

Session status associated with the specified os process id

select s.username

, s.sid

, s.serial#

, p.spid

, last_call_et

, status

from  V$SESSION s

, V$PROCESS p

where s.PADDR = p.ADDR

and p.spid='&pid'

/

 

Display any long operations

set lines 100 pages 999

col username format a15

col message format a40

col remaining format 9999

select username

, to_char(start_time, 'hh24:mi:ss dd/mm/yy') started

, time_remaining remaining

, message

from v$session_longops

where time_remaining = 0

order by time_remaining desc

/

Rman

Script – Check RMAN Backup Status

Scripts to check backup status and timings of database backups -
This script will be run in the database, not the catalog.
Login as sysdba -
This script will report on all backups – full, incremental and archivelog backups -
col STATUS format a9 col hrs format 999.99 select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'mm/dd/yy hh24:mi') start_time, to_char(END_TIME,'mm/dd/yy hh24:mi') end_time, elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS order by session_key;
This script will report all on full and incremental backups, not archivelog backups -
col STATUS format a9 col hrs format 999.99 select SESSION_KEY, INPUT_TYPE, STATUS, to_char(START_TIME,'mm/dd/yy hh24:mi') start_time, to_char(END_TIME,'mm/dd/yy hh24:mi') end_time, elapsed_seconds/3600 hrs from V$RMAN_BACKUP_JOB_DETAILS where input_type='DB INCR' order by session_key;

SCRIPTS TO CHECK RMAN RESTORE STATUS:
set lines 230
col opname for a26
col progress for a15
col progress for a8
col TARGET for a20
col USERNAME for a12
col TIME_REMAINING for 99999
set pages 800
select a.sid,a.serial#,b.username,b.opname,LAST_UPDATE_TIME,round(b.SOFAR*100 / b.TOTALWORK,2) || '%' as progress,
b.TIME_REMAINING,b.target from V$SESSION_LONGOPS b,V$SESSION a where a.sid=b.sid and TIME_REMAINING <> 0 order by 6;

SCRIPT TO CHECK RMAN SID:
COLUMN CLIENT_INFO FORMAT a30
COLUMN SID FORMAT 999
COLUMN SPID FORMAT 9999

SELECT s.SID, p.SPID, s.CLIENT_INFO
FROM V$PROCESS p, V$SESSION s
WHERE p.ADDR = s.PADDR
AND CLIENT_INFO LIKE 'rman%'
;
 

SCRIPT TO CHECK RMAN TAPE PROGRESS:
COLUMN EVENT FORMAT a10
COLUMN SECONDS_IN_WAIT FORMAT 999
COLUMN STATE FORMAT a20
COLUMN CLIENT_INFO FORMAT a30

SELECT p.SPID, EVENT, SECONDS_IN_WAIT AS SEC_WAIT,
       STATE, CLIENT_INFO
FROM V$SESSION_WAIT sw, V$SESSION s, V$PROCESS p
WHERE sw.EVENT LIKE 'sbt%'
       AND s.SID=sw.SID
       AND s.PADDR=p.ADDR
/