CATALOG missing ARCHIVE log file / RMAN

rman target /
RMAN> CATALOG ARCHIVELOG '/u01/scott/2_4553_621298904.dbf';
RMAN> list archivelog all;
exit;

Recovering Standby Database - Archive log missing from Standby

on PRIMARY:
mkdir /u01/scott

rman target /
RMAN> list archivelog all;
List of Archived Log Copies
Key Thrd Seq S Low Time Name
9833 2 4553 A 07-NOV-11 +FRA1/demo/archive_logs/2_4553_621298904.dbf
exit


sqlplus / as sysdba
CREATE OR REPLACE DIRECTORY log_files AS '+FRA1/demo/archive_logs' ;
CREATE DIRECTORY DSK_FILES AS '/u01/scott';
exec DBMS_FILE_TRANSFER.COPY_FILE ( 'log_files' , '2_4553_621298904.dbf' , 'dsk_files' , '2_4553_621298904.dbf' );

on STANDBY:
mkdir /u01/scott
cd /u01/scott
scp PRIMARY_HOST_IP_ADDRESS:/u01/scott/2_4553_621298904.dbf .
rman target /
RMAN> CATALOG ARCHIVELOG '/u01/scott/2_4553_621298904.dbf';
RMAN> list archivelog all;
exit;

ora-1111 , ora-1110 RMAN Recovery

-- RMAN  recovery terminated on test server as new file was added on prod  and RMAN fails to create the new datafile  on test server

ora-1111
ora-1110

Metalink doc id 739618.1

alter database create datafile 

       '/ora01/oracle/product/11.2.0.4/db/dbs/UNNAMED00068'
as  

      '/oradata01/oracle/oradata/DEMO/SCOTT_DATA10.dbf';

Restore database on another server - 2

Steps (1 to 10) to restore database on another server

1. create directory structure

2. add database to /etc/oratab or /var/opt/oracl/oratab

3. copy init.ora file from source

4. edit init.ora – replace controlfile name with diskgroup name such as ORADATA

5. startup nomount

6. start restore using restore shell script

  (NOTE : This will restore archive log files too)

7. monitor alert.log and restore log

8. after restore and recover completes

9. edit inot.ora file - replace controlfile parameter with real name

10. startup database; open for business

Sample init.ora file

  processes                = 500

  backup_tape_io_slaves    = TRUE

  sga_target               = 700m

  control_files            = "+ORADATA001", "+ORAFLASH001"

  control_file_record_keep_time= 14

  db_block_size            = 8192

  compatible               = 10.2.0.4.0

  log_archive_format       = %t_%s_%r.arc

  log_checkpoint_timeout   = 0

  db_file_multiblock_read_count= 16

  db_create_file_dest      = +ORADATA001

  db_create_online_log_dest_1= +ORADATA001

  db_create_online_log_dest_2= +ORAFLASH001

  db_recovery_file_dest    = +ORAFLASH001

  db_recovery_file_dest_size= 107374182400

  fast_start_mttr_target   = 900

  undo_management          = AUTO

  undo_tablespace          = UNDOTBS1

  undo_retention           = 18000

  remote_login_passwordfile= EXCLUSIVE

  job_queue_processes      = 10

  background_dump_dest     = /ora01/oracle/admin/demo/bdump

  user_dump_dest           = /ora01/oracle/admin/demo/udump

  core_dump_dest           = /ora01/oracle/admin/demo/cdump

  audit_file_dest          = /ora01/oracle/admin/demo/adump

  db_name                  = MZXP

  open_cursors             = 5000

  pga_aggregate_target     = 524288000

Sample restore command script : rman_restore_db.cmd

# script for restoring from an inconsistent backup, ie archivelog mode database

# change DBID  ###

# change NB_ORA_CLASS as appropriate ###

# change set option as appropriate  ###

set echo on;

host 'echo Starting restore at $(date).';

#set DBID = DBID;

startup nomount pfile='/ora01/oracle/admin/demo/pfile/initDEMO.ora';    # because there is no controlfile

run {

  allocate channel t1 type sbt;

  allocate channel t2 type sbt;

  set command id to 'rman restore';

# step 1 - restore controlfile from autobackup, faster if give explicit file name from list backup

# restore controlfile from autobackup;

  restore controlfile from 'c-49-20110602-02';    # change file name

  alter database mount;    # start using the restored controlfile

#  step 2 - set the recover to time using scn, time, or sequence #

#  omitting recovers to current time

#  the scn and sequence number can be determined from the list backup output

#  set until scn 562449965260;

  set until time "to_date('06-02-2011 18:00:00','mm-dd-yyyy hh24:mi:ss')";

#  set until sequence nnnn thread 1;

# use set newname commands here if restoring to a different directory structure, for ex

# set newname for datafile '/db/ora01/oracle/admin/ORA_SID/link/system01.dbf'

#  to '/ora01/oracle/admin/ORA_SID/link/system01.dbf';

# step 3 - restore the datafiles from the full backup based on set until command

  restore database;

# switch datafile all;   # if used set newname command, changes the names in the contro

# step 4 - recover applies logs up to set until time. It will restore logs from tape if

  recover database;

# step 5 - open the database and recreate the online redo logs

  alter database open resetlogs;

}

host 'echo Ending restore at $(date).';

# end of script

Sample restore shell script : restore.sh

. ~/.profile

ORACLE_SID=DEMO;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES

cd /ora01/oracle/admin/BACKUP/DEMO

rman nocatalog target / cmdfile rman_restore_db.cmd

status=$?

exit $status

Hope this helps! Rupam

Restore Archivelog with Particular sequence

Restore Archivelog with Particular sequence

RMAN> run {
allocate channel dsk1 type disk;
restore archivelog logseq 11645;
release channel dsk1;
 }

RAC - Restore Archivelog with Particular sequence and thread

RMAN> run {
allocate channel ch01 type disk format '/ora_backup/u0006/FIPRD3/archivelog/';
restore archivelog logseq 24208 thread 3;
}

Restore Archivelog with Particular sequence at diff. location

RMAN> run{
set archivelog destination to '/ora_backup/u0001/cloning/';
 restore archivelog sequence 6232 thread 3;
 }

RMAN Restore from Consistent and Inconsistent BACKUP

RMAN Restore from Inconsistent BACKUP

# start of script

# change DBID  ###

# change NB_ORA_CLASS as appropriate ###

# change set option as appropriate  ###

set echo on;

host 'echo Starting restore at $(date).';

set DBID = DBID;

startup nomount;

run {

  allocate channel t1 type sbt

    parms='ENV=(NB_ORA_CLASS=RMAN_Tier3)';

  allocate channel t2 type sbt

    parms='ENV=(NB_ORA_CLASS=RMAN_Tier3)';

# restore controlfile from autobackup maxseq 0;

# restore controlfile from 'c-1232717171-040130-00';

# restore controlfile from ‘/bkup/oracle/backup_controlfile.1223’

  restore controlfile from autobackup;

  alter database mount;

# set until scn 1239965260;

# set until time "to_date('01-28-2004 11:40:54','mm-dd-yyyy hh24:mi:ss')";

  restore database;

  recover database;

  alter database open resetlogs;

}

host 'echo Ending restore at $(date).';

# end of script

RMAN Restore from Consistent BACKUP

# start of script

# change DBID ##

# change NB_ORA_CLASS as appropriate  ###

set echo on;

host 'echo Starting restore at $(date).';

set DBID = DBID;

startup nomount;

run {

  allocate channel t1 type sbt

    parms='ENV=(NB_ORA_CLASS=RMAN_MTBackup_Tier3)';

  allocate channel t2 type sbt

    parms='ENV=(NB_ORA_CLASS=RMAN_MTBackup_Tier3)';

  set command id to 'rman restore';

#  in noarchivelog mode, no set means most current backup.

# set until scn 562449965260;

# set until time "to_date('01-30-2004 13:00:00','mm-dd-yyyy hh24:mi:ss')";

# restore controlfile from autobackup maxseq 0;

# restore controlfile from 'c-1232717171-040130-00';

# restore controlfile from ‘/bkup/oracle/backup_controlfile.1223’

  restore controlfile from autobackup;

  alter database mount;

  restore database;

  recover database noredo;

  alter database open resetlogs;

}

host 'echo Ending restore at $(date).';

# end of script

Hope this helps!  Rupam

RMAN-06207 RMAN-06208 RMAN-06214 - backup standby controlfile is missing from standby server/database

Symptoms

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 2
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Control File Copy     676    15-NOV-11          /ora01/oracle/admin/BACKUP/cisdbc3/cisdbc3_controlfile_bak_11-15-11_20:02:56

RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due
RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status
RMAN-06210: List of Mismatched objects
RMAN-06211: ==========================
RMAN-06212:   Object Type   Filename/Handle
RMAN-06213: --------------- ---------------------------------------------------
RMAN-06214: Datafile Copy   /ora01/oracle/admin/BACKUP/cisdbc3/cisdbc3_controlfile_bak_11-15-11_20:02:56

 

Cause

rman delete fails due to mismatched status of backup pieces

In this case,  backup standby controlfile is missing from standby server/database

Solution

Step 1 thru 5

Step 1

RMAN> report obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 2

Report of obsolete backups and copies

Type                 Key    Completion Time    Filename/Handle

-------------------- ------ ------------------ --------------------

Control File Copy     676    15-NOV-11          /ora01/oracle/admin/BACKUP/orcl/orcl_controlfile_bak_11-15-11_20:02:56

Step 2

RMAN> crosscheck copy of controlfile;

validation failed for control file copy

control file copy filename=/ora01/oracle/admin/BACKUP/orcl/orcl_controlfile_bak_11-15-11_20:02:56 recid=676 stamp=767304177

Crosschecked 1 objects

Step 3

RMAN> delete noprompt obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 2

Deleting the following obsolete backups and copies:

Type                 Key    Completion Time    Filename/Handle

-------------------- ------ ------------------ --------------------

Control File Copy     676    15-NOV-11          /ora01/oracle/admin/BACKUP/orcl/orcl_controlfile_bak_11-15-11_20:02:56

deleted control file copy

control file copy filename=/ora01/oracle/admin/BACKUP/orcl/orcl_controlfile_bak_11-15-11_20:02:56 recid=676 stamp=767304177

Deleted 1 objects

Step 4

RMAN>  crosscheck copy of controlfile;

Step 5

RMAN>  report obsolete;

RMAN retention policy will be applied to the command

RMAN retention policy is set to redundancy 2

no obsolete backups found

RMAN>

Hope this helps!Rupam

Rman: Ora-01008 When Connecting To Target in 11.2.0.2

Metalink note : [ID 1280447.1]

symptons

DBGSQL:     TARGET> select  nvl(max(al.recid), '0'),nvl(max(al.recid), 0)   into  :txtparmvalue, :parmvalue   from  v$archived_log al  where  al.status in ('X', 'A')    and  al.is_recovery_dest_file = 'YES'    and  al.creator = 'RMAN'

DBGSQL:        sqlcode = 1008

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of backup plus archivelog command at 04/26/2011 19:00:15

RMAN-03014: implicit resync of recovery catalog failed

ORA-01008: not all variables bound

Cause

It's a bug with a patch

Solution

It's a bug with a patch. Workaround is to :

SQL> alter system flush shared_pool;

Hope this helps! Rupam

Restore oracle database on another server

Steps (1 to 10) to restore database on another server

1. create directory structure

2. add database to /etc/oratab or /var/opt/oracl/oratab

3. copy init.ora file from source

4. edit init.ora – replace controlfile name with diskgroup name such as ORADATA

5. startup nomount

6. start restore using restore shell script

  (NOTE : This will restore archive log files too)

7. monitor alert.log and restore log

8. after restore and recover completes

9. edit inot.ora file - replace controlfile parameter with real name

10. startup database; open for business

 Hope this helps! Rupam

Rman: Ora-01008 When Connecting To Target in 11.2.0.2 [ID 1280447.1]

It's a bug with a patch. Workaround is to :

SQL> alter system flush shared_pool;

Hope This Helps! Rupam

RMAN Restore Datafile

Steps:

1. identify datafile#  to be recoved

2. plug in the value the restore script

3. execute restore.sh

# rman_file_recover.cmd

run {

  allocate channel t1 type sbt;

  restore datafile 4;

  recover datafile 4;

}

# restore.sh

. ~/.profile

ORACLE_SID=demo;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES

cd /ora01/oracle/admin/BACKUP/WKBP

rman nocatalog target / cmdfile rman_file_recover.cmd

status=$?

exit $status

Hope this helps!Rupam

RMAN Restore block

Steps:

1. identify file# and block# to be recoved

2. plug in the value the restore script

3. execute restore.sh

# rman_block_recover.cmd

run {

  allocate channel t1 type sbt;

  BLOCKRECOVER DATAFILE 19 BLOCK 1833660;

}

# restore.sh

. ~/.profile

ORACLE_SID=demo;ORAENV_ASK=NO;. oraenv;ORAENV_ASK=YES

cd /ora01/oracle/admin/BACKUP/WKBP

rman nocatalog target / cmdfile rman_block_recover.cmd

status=$?

exit $status

Hope this helps!Rupam

RMAN commands : quick reference guide

RMAN> list backupset;

RMAN> list backupset of database;

RMAN> list backupset of database completed before '22-NOV-00';

RMAN> list backupset of archivelog all;

RMAN> list backupset of tablespace users;

RMAN> list backupset of datafile 1;

RMAN> list backup summary;

RMAN> list backup;

RMAN> list backup of controlfile;

RMAN> list backup of tablespace SYSTEM;

RMAN> list backup by file;

RMAN> list backup of archivelog all ;

RMAN> list copy of database;

RMAN> list copy of database archivelog all;

RMAN> allocate channel for maintenance type disk;

RMAN> configure channel device type disk clear ;

RMAN> report need backup days=2 database;

RMAN> report need backup days=10 tablespace TEMP;

RMAN> report need backup days=4 datafile 'D:\ORACLE\ORADATA\OR816\TEMP01.DBF';

RMAN> report schema;

RMAN> report obsolete;

RMAN> crosscheck backup;

RMAN> crosscheck backupset;

RMAN> crosscheck copy;

RMAN> crosscheck archivelog all;

RMAN> crosscheck controlfilecopy '/ora01/oracle/admin/BACKUP/demo/demo_controlfile_bak_03-15-11_19:59:11' ;

RMAN> delete noprompt expired backup ;

RMAN> delete noprompt obsolete;

RMAN> delete archivelog all;

RMAN> delete expired archivelog all;

RMAN> delete archivelog all completed before 'sysdate -1';

RMAN> delete noprompt archivelog until time 'sysdate - 1';

RMAN> delete archivelog all backed up 1 times to device type disk completed before 'sysdate-1';

Hope this helps! Rupam

crosscheck controlfilecopy

Problem :

while deleting obsolete backup files, following warning is thrown

"RMAN-06207: WARNING: 1 objects could not be deleted for DISK channel(s) due

RMAN-06208:          to mismatched status.  Use CROSSCHECK command to fix status"

Archiver Hung in Oracle database - ORA-16038, ORA-19504, ORA-00257

Archiver Hung in Oracle database

Steps to delete archive log file not needed for recovery (older than the last backup of the database)

Summary

1. check how much space is used by archiver

2. check the last good backup of the database

3. delete archive log files older than last good backup

4. crosscheck archive log

Oracle Database Backup Report - v$rman_backup_job_details

/*

-- rman - Query using V$RMAN_BACKUP_JOB_DETAILS is taking too long to execute both in SQL*Plus and also in DATABASE CONSOLE.

-- doc : 420200.1

*/

Steps :-

1. sqlplus as sysdba

2. execute following procedure

Sql> exec dbms_stats.DELETE_TABLE_STATS('SYS','X$KCCRSR'); # deletes the statistics on the fixed object.

Sql> exec dbms_stats.LOCK_TABLE_STATS('SYS','X$KCCRSR');   # lock that object so that statistics will not be collected in future.

  

Hope this helps. Regards Rupam

Recover database until cancel

Quick steps

1. Setup oracle database environment using . oraenv

2. restore controlfile

    $ rman target / nocatalog

    RMAN> set dbid=1185150074;

    RMAN> startup nomount;

    RMAN> run

                {

                restore controlfile from ‘/u01/BACKUP/demo/demo_control_backup.ctl’;

                }

RMAN Configuration

The default RMAN configuration may be changed as per need. Here is sample configuration change and also changing it back to default setting.

Connect to database using RMAN
$ rman target / nocatalog

# to check the current configuration

show all;

RMAN Backup quick reference guide

configure RMAN configuration

$ rman target / nocatalog

RMAN>configure controlfile autobackup on;

RMAN>configure retention policy to recovery window of 14 days;

RMAN backup Report - Oracle Database Backup Report

 script  : RMAN backup Report - Oracle Database Backup Report

Script : chkbkpstatus.sql

 set pages 999 lines 120
   col STATUS format a9
   col hrs format 999.99
   col start_time format a15
   col end_time format a15
   col dev format a5
   col inbytes format a10
   col outbytes format a10
   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,
       output_device_type dev,
       input_bytes_display inbytes,
       output_bytes_display outbytes
    from V$RMAN_BACKUP_JOB_DETAILS
   order by session_key;

Output

INPUT_TYPE    STATUS    START_TIME      END_TIME    HRS DEV   INBYTES    OUTBYTES

------------- --------- --------------- --------------- ------- ----- ---------- ----------

ARCHIVELOG    COMPLETED 08/11/10 15:00  08/11/10 15:01      .02 DISK    257.08M    107.06M

DB FULL       COMPLETED 08/11/10 21:00  08/11/10 23:57     2.96 DISK    227.16G     43.24G

ARCHIVELOG    COMPLETED 08/12/10 06:00  08/12/10 06:01      .02 DISK    260.92M     87.39M

ARCHIVELOG    COMPLETED 08/12/10 15:00  08/12/10 15:01      .02 DISK    250.10M    104.85M

Hope this help. Regards Rupam