How to Backup and Restore via RMAN without a Recovery Catalog

Posted at Sunday, February 24, 2008
Although an RMAN repository is critical for most enterprises nowadays, there are circumstances in which you may want to backup and restore via RMAN utilizing a database's controlfile (i.e. in lieu of a recovery catalog).

It is possible to backup and restore your database via RMAN without a recovery catalog and to do so in such a way that you can recover from a loss of all database files; the following is an example in which I use an 11g (11.1.0.6) database called nf (short for new features):

Backup the ENTIRE database to RMAN backuppieces using your desired device type:

backup_nf.bsh

#!/bin/bash

export ORACLE_SID=nf;
. oraenv;
export TNS_ADMIN=$ORACLE_HOME/network/admin;
export BACKUP_LOC=/u03/app/oracle/orabackup;

$ORACLE_HOME/bin/rman target=/ nocatalog <<EOF
configure backup optimization on;
configure default device type to disk;

crosscheck backup;
crosscheck archivelog all;

delete noprompt force obsolete;
delete noprompt force expired backup;
delete noprompt force expired archivelog all;

configure controlfile autobackup on;
configure controlfile autobackup format for device type disk to '$BACKUP_LOC/%d_%F_ctl.bckp';

backup full database format '$BACKUP_LOC/%d_%U.bckp' tag rman_nocat_${ORACLE_SID} plus archivelog format '$BACKUP_LOC/%d_%U.bckp' delete input tag rman_nocat_${ORACLE_SID} ;

list recoverable backup;
EOF

Of course, you will need to make small changes to the aforementioned to suit your environment. Once you have done so, run the script.

./backup_nf.bsh > backup_nf.log 2>&1 &

Afterwards, the backup_nf.log should have all the details necessary to troubleshoot, etc.

backup_nf.log

The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11.1.0.6 is /u03/app/oracle

Recovery Manager: Release 11.1.0.6.0 - Production on Sat Feb 23 22:43:24 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

connected to target database: NF (DBID=2018903642)
using target database control file instead of recovery catalog

RMAN>
old RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters:
CONFIGURE BACKUP OPTIMIZATION ON;
new RMAN configuration parameters are successfully stored

RMAN>
old RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters:
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
new RMAN configuration parameters are successfully stored

RMAN>
RMAN>
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=122 device type=DISK
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u03/app/oracle/orabackup/NF_31j9fdf6_1_1.bckp RECID=56 STAMP=647476722
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u03/app/oracle/orabackup/NF_34j9fdlq_1_1.bckp RECID=57 STAMP=647476933
crosschecked backup piece: found to be 'EXPIRED'
backup piece handle=/u03/app/oracle/orabackup/NF_36j9fdn7_1_1.bckp RECID=58 STAMP=647476979
Crosschecked 3 objects


RMAN>
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=122 device type=DISK
specification does not match any archived log in the recovery catalog

RMAN>
RMAN>
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
no obsolete backups found

RMAN>
using channel ORA_DISK_1

List of Backup Pieces
BP Key BS Key Pc# Cp# Status Device Type Piece Name
------- ------- --- --- ----------- ----------- ----------
56 47 1 1 EXPIRED DISK /u03/app/oracle/orabackup/NF_31j9fdf6_1_1.bckp
57 48 1 1 EXPIRED DISK /u03/app/oracle/orabackup/NF_34j9fdlq_1_1.bckp
58 49 1 1 EXPIRED DISK /u03/app/oracle/orabackup/NF_36j9fdn7_1_1.bckp
deleted backup piece
backup piece handle=/u03/app/oracle/orabackup/NF_31j9fdf6_1_1.bckp RECID=56 STAMP=647476722
deleted backup piece
backup piece handle=/u03/app/oracle/orabackup/NF_34j9fdlq_1_1.bckp RECID=57 STAMP=647476933
deleted backup piece
backup piece handle=/u03/app/oracle/orabackup/NF_36j9fdn7_1_1.bckp RECID=58 STAMP=647476979
Deleted 3 EXPIRED objects


RMAN>
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=122 device type=DISK
specification does not match any archived log in the recovery catalog

RMAN>
RMAN>
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored

RMAN>
old RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/app/oracle/orabackup/%d_%F_ctl.bckp';
new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/u03/app/oracle/orabackup/%d_%F_ctl.bckp';
new RMAN configuration parameters are successfully stored

RMAN>
RMAN>

Starting backup at 23-FEB-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=7 RECID=746 STAMP=647477049
channel ORA_DISK_1: starting piece 1 at 23-FEB-08
channel ORA_DISK_1: finished piece 1 at 23-FEB-08
piece handle=/u03/app/oracle/orabackup/NF_38j9fdpp_1_1.bckp tag=RMAN_NOCAT_NF comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_7_647475391.arc RECID=746 STAMP=647477049
Finished backup at 23-FEB-08

Starting backup at 23-FEB-08
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00003 name=/u03/app/oracle/oradata/nf/undotbs01.dbf
input datafile file number=00002 name=/u03/app/oracle/oradata/nf/sysaux01.dbf
input datafile file number=00004 name=/u03/app/oracle/oradata/nf/users01.dbf
input datafile file number=00001 name=/u03/app/oracle/oradata/nf/system01.dbf
input datafile file number=00005 name=/u03/app/oracle/oradata/nf/fba_tbs01.dbf
input datafile file number=00007 name=/u03/app/oracle/oradata/nf/enc_tbs02.dbf
input datafile file number=00006 name=/u03/app/oracle/oradata/nf/enc_tbs101.dbf
input datafile file number=00010 name=/u03/app/oracle/oradata/nf/users02.dbf
channel ORA_DISK_1: starting piece 1 at 23-FEB-08
channel ORA_DISK_1: finished piece 1 at 23-FEB-08
piece handle=/u03/app/oracle/orabackup/NF_39j9fdqs_1_1.bckp tag=RMAN_NOCAT_NF comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:11:35
Finished backup at 23-FEB-08

Starting backup at 23-FEB-08
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=747 STAMP=647477793
channel ORA_DISK_1: starting piece 1 at 23-FEB-08
channel ORA_DISK_1: finished piece 1 at 23-FEB-08
piece handle=/u03/app/oracle/orabackup/NF_3aj9feh2_1_1.bckp tag=RMAN_NOCAT_NF comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: deleting archived log(s)
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_8_647475391.arc RECID=747 STAMP=647477793
Finished backup at 23-FEB-08

Starting Control File and SPFILE Autobackup at 23-FEB-08
piece handle=/u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp comment=NONE
Finished Control File and SPFILE Autobackup at 23-FEB-08

RMAN>
RMAN>

List of Backup Sets
===================


BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
50 35.00K DISK 00:00:11 23-FEB-08
BP Key: 59 Status: AVAILABLE Compressed: NO Tag: RMAN_NOCAT_NF
Piece Name: /u03/app/oracle/orabackup/NF_38j9fdpp_1_1.bckp

List of Archived Logs in backup set 50
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 7 8095380 23-FEB-08 8095522 23-FEB-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
51 Full 2.56G DISK 00:11:48 23-FEB-08
BP Key: 60 Status: AVAILABLE Compressed: NO Tag: RMAN_NOCAT_NF
Piece Name: /u03/app/oracle/orabackup/NF_39j9fdqs_1_1.bckp
List of Datafiles in backup set 51
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/system01.dbf
2 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/sysaux01.dbf
3 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/undotbs01.dbf
4 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/users01.dbf
5 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/fba_tbs01.dbf
6 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/enc_tbs101.dbf
7 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/enc_tbs02.dbf
10 Full 8095586 23-FEB-08 /u03/app/oracle/oradata/nf/users02.dbf

BS Key Size Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ ---------------
52 834.00K DISK 00:00:12 23-FEB-08
BP Key: 61 Status: AVAILABLE Compressed: NO Tag: RMAN_NOCAT_NF
Piece Name: /u03/app/oracle/orabackup/NF_3aj9feh2_1_1.bckp

List of Archived Logs in backup set 52
Thrd Seq Low SCN Low Time Next SCN Next Time
---- ------- ---------- --------- ---------- ---------
1 8 8095522 23-FEB-08 8096603 23-FEB-08

BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
53 Full 9.73M DISK 00:00:13 23-FEB-08
BP Key: 62 Status: AVAILABLE Compressed: NO Tag: TAG20080223T225647
Piece Name: /u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp
SPFILE Included: Modification time: 23-FEB-08
SPFILE db_unique_name: NF
Control File Included: Ckp SCN: 8096623 Ckp time: 23-FEB-08

RMAN>

Recovery Manager complete.

Now that I have a complete backup of the database - datafiles, archivelogs, spfile, and controlfile - I am ready to drop my database and prove that I can restore from a complete loss of all files.

Drop the Database:

delete_nf.bsh

#!/bin/bash

export ORACLE_SID=nf;
. oraenv;
sqlplus "/ as sysdba" <<EOF
shutdown abort;
EOF

rm -f /u03/app/oracle/oradata/nf/*.dbf*
rm -f /u03/app/oracle/oradata/nf/*.log*
rm -f /u03/app/oracle/oradata/nf/*.ctl*
rm -f /u03/app/oracle/oradata/nf/arch/*
rm -f $ORACLE_HOME/dbs/*${ORACLE_SID}*


After editing for your environment, run the script.

./delete_nf.bsh

Now I am ready to restore and recover my database using only the backuppieces resulting from the aforementioned backup.

Restore and recover the Database:

Identify the backuppiece that has a copy of the spfile and controlfile via

ls -lart /u03/app/oracle/orabackup/*ctl*

The complete path to this backuppiece will need to be passed to the restore script.

Edit the restore script for your environment:

restore_nf.bsh

#!/bin/bash

if [ "$1" == "" ]; then
echo "usage ${0} path to backuppiece with controlfile backup";
exit 1;
fi
CTL_PIECE=${1} ;
echo ${CTL_PIECE} ;
BACKUP_LOC=/u03/app/oracle/orabackup;
export ORACLE_SID=nf;
. oraenv;
export TNS_ADMIN=$ORACLE_HOME/network/admin ;

$ORACLE_HOME/bin/rman nocatalog <<EOF
connect target /;
startup force nomount;
restore spfile from '${CTL_PIECE}';
shutdown immediate;
startup nomount;
restore controlfile from '${CTL_PIECE}';
shutdown immediate;
startup mount;
catalog start with '${BACKUP_LOC}' noprompt;
restore database;
recover database;
sql 'alter database open resetlogs';
EOF

orapwd file=$ORACLE_HOME/dbs/orapw${ORACLE_SID} password=password ignorecase=Y ;

sqlplus "/ as sysdba" <<EOF
alter tablespace temp add tempfile '/u03/app/oracle/oradata/${ORACLE_SID}/temp01.dbf' size 500M reuse;
EOF

After editing as appropriate, run the script.

./restore_nf.bsh /u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp >restore_nf.log 2>&1 &

Afterwards, the restore_nf.log should have all the details necessary to troubleshoot, etc.

restore_nf.log

/u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp
The Oracle base for ORACLE_HOME=/u03/app/oracle/product/db/11.1.0.6 is /u03/app/oracle

Recovery Manager: Release 11.1.0.6.0 - Production on Sat Feb 23 23:03:07 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

RMAN>
connected to target database (not started)

RMAN>
startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u03/app/oracle/product/db/11.1.0.6/dbs/initnf.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area 159019008 bytes

Fixed Size 1298584 bytes
Variable Size 67112808 bytes
Database Buffers 83886080 bytes
Redo Buffers 6721536 bytes

RMAN>
Starting restore at 23-FEB-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=98 device type=DISK

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 23-FEB-08

RMAN>
Oracle instance shut down

RMAN>
connected to target database (not started)
Oracle instance started

Total System Global Area 422670336 bytes

Fixed Size 1300352 bytes
Variable Size 381683840 bytes
Database Buffers 33554432 bytes
Redo Buffers 6131712 bytes

RMAN>
Starting restore at 23-FEB-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u03/app/oracle/oradata/nf/control01.ctl
output file name=/u03/app/oracle/oradata/nf/control02.ctl
output file name=/u03/app/oracle/oradata/nf/control03.ctl
Finished restore at 23-FEB-08

RMAN>
Oracle instance shut down

RMAN>
connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area 422670336 bytes

Fixed Size 1300352 bytes
Variable Size 381683840 bytes
Database Buffers 33554432 bytes
Redo Buffers 6131712 bytes

RMAN>
searching for all files that match the pattern /u03/app/oracle/orabackup

List of Files Unknown to the Database
=====================================
File Name: /u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u03/app/oracle/orabackup/NF_c-2018903642-20080223-01_ctl.bckp

RMAN>
Starting restore at 23-FEB-08
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=153 device type=DISK

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u03/app/oracle/oradata/nf/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u03/app/oracle/oradata/nf/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u03/app/oracle/oradata/nf/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u03/app/oracle/oradata/nf/users01.dbf
channel ORA_DISK_1: restoring datafile 00005 to /u03/app/oracle/oradata/nf/fba_tbs01.dbf
channel ORA_DISK_1: restoring datafile 00006 to /u03/app/oracle/oradata/nf/enc_tbs101.dbf
channel ORA_DISK_1: restoring datafile 00007 to /u03/app/oracle/oradata/nf/enc_tbs02.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u03/app/oracle/oradata/nf/users02.dbf
channel ORA_DISK_1: reading from backup piece /u03/app/oracle/orabackup/NF_39j9fdqs_1_1.bckp
channel ORA_DISK_1: piece handle=/u03/app/oracle/orabackup/NF_39j9fdqs_1_1.bckp tag=RMAN_NOCAT_NF
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:05:37
Finished restore at 23-FEB-08

RMAN>
Starting recover at 23-FEB-08
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_DISK_1: reading from backup piece /u03/app/oracle/orabackup/NF_3aj9feh2_1_1.bckp
channel ORA_DISK_1: piece handle=/u03/app/oracle/orabackup/NF_3aj9feh2_1_1.bckp tag=RMAN_NOCAT_NF
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u03/app/oracle/oradata/nf/arch/nf_1_8_647475391.arc thread=1 sequence=8
unable to find archived log
archived log thread=1 sequence=9
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/23/2008 23:09:23
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 9 and starting SCN of 8096603

RMAN>
sql statement: alter database open resetlogs

RMAN>

Recovery Manager complete.

SQL*Plus: Release 11.1.0.6.0 - Production on Sat Feb 23 23:11:46 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter tablespace temp add tempfile '/u03/app/oracle/oradata/nf/temp01.dbf' size 500M reuse
*
ERROR at line 1:
ORA-01537: cannot add file '/u03/app/oracle/oradata/nf/temp01.dbf' - file
already part of database


SQL> Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

The files for this post - including complete log files - can be found here: rman_nf.tar

Labels: ,