19 | 08 | 2017
Latest Articles
Popular Articles

RMAN

How to backup with the keep option and restore without a recovery catalog

User Rating:  / 1
PoorBest 

You don' t have a RMAN recovery catalog, but would like to make a backup with the keep option, how to restore a - tagged - cold backup

 

If you are using 11G See also RMAN Archival backups with restore points

If you are using 10G See also See also Keep RMAN backupsets


1. You are not an Oracle Guru
2. You do a change on a system and need or would like to keep a restore point of your critical database
3. You thus would like to keep that backup for a while and this should to be very easy
4. The restore of that backup needs to be very easy and is (of course) critical.
5. You do not have a RMAN catalog database nor can or would like to set it up



When the above is all true then consider a cold RMAN backup with the keep option. Here we go


SQL> startup mount;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size            2229080 bytes
Variable Size          478153896 bytes
Database Buffers      138412032 bytes
Redo Buffers            7532544 bytes
Database mounted.
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options


If you do not have a RMAN catalog database and your database is thus not registered in a recovery catalog database

1. You can use the keep option but you need to specify how long you would like to keep that backup.

2. You cannot keep the keep until forever option.


[oracle@pcguy ~]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Mon May 2 17:12:14 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SILVER (DBID=2121303953, not open)

RMAN> run {
2> allocate channel ch1 device type disk;
3> allocate channel ch2 device type disk;
4> backup as compressed backupset database format '/opt/flash_recovery_area/backup/SILVER/DB_%d_%U' TAG=BEFORE_UPGRADE keep forever;
5> backup current controlfile format '/opt/flash_recovery_area/backup/SILVER/CT_%d_%U' TAG=BEFORE_UPGRADE keep forever;
6> };

allocated channel: ch1
channel ch1: SID=133 device type=DISK

allocated channel: ch2
channel ch2: SID=10 device type=DISK

Starting backup at 02-MAY-11

released channel: ch1
released channel: ch2
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup command at 05/02/2011 17:25:21
RMAN-06522: KEEP FOREVER option is not supported without the recovery catalog



RMAN> run {
2> allocate channel ch1 device type disk;
3> allocate channel ch2 device type disk;
4> backup as compressed backupset database format '/opt/flash_recovery_area/backup/SILVER/DB_%d_%U' TAG=BEFORE_UPGRADE_02052011 keep until time 'sysdate+365';
5> backup current controlfile format '/opt/flash_recovery_area/backup/SILVER/CT_%d_%U' TAG=BEFORE_UPGRADE_02052011 keep until time 'sysdate+365';
6> }

allocated channel: ch1
channel ch1: SID=133 device type=DISK

allocated channel: ch2
channel ch2: SID=10 device type=DISK

Starting backup at 02-MAY-11

backup will be obsolete on date 01-MAY-12
archived logs will not be kept or backed up
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
input datafile file number=00001 name=/opt/oradata/SILVER/system01.dbf
input datafile file number=00002 name=/opt/oradata/SILVER/sysaux01.dbf
channel ch1: starting piece 1 at 02-MAY-11
channel ch2: starting compressed full datafile backup set
channel ch2: specifying datafile(s) in backup set
input datafile file number=00003 name=/opt/oradata/SILVER/undotbs01.dbf
input datafile file number=00004 name=/opt/oradata/SILVER/users01.dbf
channel ch2: starting piece 1 at 02-MAY-11
channel ch2: finished piece 1 at 02-MAY-11
piece handle=/opt/flash_recovery_area/backup/SILVER/DB_SILVER_1kmbb82l_1_1 tag=BEFORE_UPGRADE_02052011 comment=NONE
channel ch2: backup set complete, elapsed time: 00:01:06
channel ch2: starting compressed full datafile backup set
channel ch2: specifying datafile(s) in backup set
including current control file in backup set
channel ch2: starting piece 1 at 02-MAY-11
channel ch2: finished piece 1 at 02-MAY-11
piece handle=/opt/flash_recovery_area/backup/SILVER/DB_SILVER_1lmbb84n_1_1 tag=BEFORE_UPGRADE_02052011 comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:01
channel ch2: starting compressed full datafile backup set
channel ch2: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ch2: starting piece 1 at 02-MAY-11
channel ch2: finished piece 1 at 02-MAY-11
piece handle=/opt/flash_recovery_area/backup/SILVER/DB_SILVER_1mmbb84r_1_1 tag=BEFORE_UPGRADE_02052011 comment=NONE
channel ch2: backup set complete, elapsed time: 00:00:01
channel ch1: finished piece 1 at 02-MAY-11
piece handle=/opt/flash_recovery_area/backup/SILVER/DB_SILVER_1jmbb82k_1_1 tag=BEFORE_UPGRADE_02052011 comment=NONE
channel ch1: backup set complete, elapsed time: 00:01:35

backup will be obsolete on date 01-MAY-12
archived logs will not be kept or backed up
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 02-MAY-11
channel ch1: finished piece 1 at 02-MAY-11
piece handle=/opt/flash_recovery_area/backup/SILVER/DB_SILVER_1nmbb85k_1_1 tag=BEFORE_UPGRADE_02052011 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01

backup will be obsolete on date 01-MAY-12
archived logs will not be kept or backed up
channel ch1: starting compressed full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
channel ch1: starting piece 1 at 02-MAY-11
channel ch1: finished piece 1 at 02-MAY-11
piece handle=/opt/flash_recovery_area/backup/SILVER/DB_SILVER_1ombb85m_1_1 tag=BEFORE_UPGRADE_02052011 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-MAY-11

Starting backup at 02-MAY-11

backup will be obsolete on date 01-MAY-12
archived logs will not be kept or backed up
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
channel ch1: starting piece 1 at 02-MAY-11
channel ch1: finished piece 1 at 02-MAY-11
piece handle=/opt/flash_recovery_area/backup/SILVER/CT_SILVER_1pmbb85o_1_1 tag=BEFORE_UPGRADE_02052011 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01

backup will be obsolete on date 01-MAY-12
archived logs will not be kept or backed up
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ch1: starting piece 1 at 02-MAY-11
channel ch1: finished piece 1 at 02-MAY-11
piece handle=/opt/flash_recovery_area/backup/SILVER/CT_SILVER_1qmbb85q_1_1 tag=BEFORE_UPGRADE_02052011 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01

backup will be obsolete on date 01-MAY-12
archived logs will not be kept or backed up
channel ch1: starting full datafile backup set
channel ch1: specifying datafile(s) in backup set
including current control file in backup set
channel ch1: starting piece 1 at 02-MAY-11
channel ch1: finished piece 1 at 02-MAY-11
piece handle=/opt/flash_recovery_area/backup/SILVER/CT_SILVER_1rmbb85s_1_1 tag=BEFORE_UPGRADE_02052011 comment=NONE
channel ch1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-MAY-11
released channel: ch1
released channel: ch2

RMAN>



And then the evil happened. The upgrade of the application schema failed. Somewhere in between the beginning and the end, some data was changed, some DDL and DML. It is too hard to restart where the failure occurred and to relaunch. Why is that ? Many application upgrades launches a master script which itself launches some others. There is a hierarchy of scripts. You are in a validated environment you simply can' t and wouldn't like to dig into the mess. So we restore our tagged backup BEFORE_UPGRADE_02052011. We shutdown the database, remove (or rename) the database files, the control files


SQL> startup nomount;
ORACLE instance started.

Total System Global Area  626327552 bytes
Fixed Size            2229080 bytes
Variable Size          478153896 bytes
Database Buffers      138412032 bytes
Redo Buffers            7532544 bytes
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@pcguy SILVER]$ rman target /

Recovery Manager: Release 11.2.0.2.0 - Production on Wed May 4 15:28:13 2011

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: SILVER (not mounted)

RMAN> set dbid 2121303953

executing command: SET DBID

RMAN> restore controlfile from '/opt/flash_recovery_area/backup/SILVER/CT_SILVER_1pmbb85o_1_1';

Starting restore at 04-MAY-11
using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/opt/oradata/SILVER/control01.ctl
output file name=/opt/flash_recovery_area/SILVER/control02.ctl
Finished restore at 04-MAY-11

RMAN> sql "alter database mount";

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN> restore database from TAG=BEFORE_UPGRADE_02052011;

Starting restore at 04-MAY-11
Starting implicit crosscheck backup at 04-MAY-11
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=134 device type=DISK
Crosschecked 11 objects
Finished implicit crosscheck backup at 04-MAY-11

Starting implicit crosscheck copy at 04-MAY-11
using channel ORA_DISK_1
Finished implicit crosscheck copy at 04-MAY-11

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /opt/flash_recovery_area/SILVER/archivelog/2011_05_04/o1_mf_1_107_6w28vrvy_.arc
File Name: /opt/flash_recovery_area/SILVER/archivelog/2011_05_03/o1_mf_1_105_6vzx48jy_.arc
File Name: /opt/flash_recovery_area/SILVER/archivelog/2011_05_03/o1_mf_1_106_6vzx5hwd_.arc

using channel ORA_DISK_1

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 00003 to /opt/oradata/SILVER/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /opt/oradata/SILVER/users01.dbf
channel ORA_DISK_1: reading from backup piece /opt/flash_recovery_area/backup/SILVER/DB_SILVER_1kmbb82l_1_1
channel ORA_DISK_1: piece handle=/opt/flash_recovery_area/backup/SILVER/DB_SILVER_1kmbb82l_1_1 tag=BEFORE_UPGRADE_02052011
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:55
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 /opt/oradata/SILVER/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /opt/oradata/SILVER/sysaux01.dbf
channel ORA_DISK_1: reading from backup piece /opt/flash_recovery_area/backup/SILVER/DB_SILVER_1jmbb82k_1_1
channel ORA_DISK_1: piece handle=/opt/flash_recovery_area/backup/SILVER/DB_SILVER_1jmbb82k_1_1 tag=BEFORE_UPGRADE_02052011
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 04-MAY-11

RMAN> sql "alter database open resetlogs";

sql statement: alter database open resetlogs



That' s all we need to do, we obviously can allocate multiple channels to enhance parallelism.
Note that since we opened the database with resetlogs there is a new database incarnation


RMAN> list incarnation;

using target database control file instead of recovery catalog

List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       SILVER   2121303953       PARENT  1          10-MAR-11
2       2       SILVER   2121303953       CURRENT 1288787    04-MAY-11


RMAN> exit


Recovery Manager complete.


SQL> select name,CREATED,RESETLOGS_CHANGE#,RESETLOGS_TIME,PRIOR_RESETLOGS_CHANGE#,PRIOR_RESETLOGS_TIME from v$database;

NAME      CREATED         RESETLOGS_CHANGE# RESETLOGS_TIME
--------- ------------------ ----------------- ------------------
PRIOR_RESETLOGS_CHANGE# PRIOR_RESETLOGS_TI
----------------------- ------------------
SILVER      10-MAR-11               1288787 04-MAY-11
1 10-MAR-11


This method is easy and you can use it from 9i onwards. Note that in 9i once you opened the database with resetlogs you needed to take a full backup as fast as possible.
A new wonderfull 10G feature is that you can recover through resetlogs from 10G release 1 onwards, you can use database backups prior to the resetlogs for recovery.