RMAN - solid backup

This isn't the Oracle Suggested Backup script, but this is my RMAN script for a good, solid RMAN backup. It will need slight tweaking if you decide to use it.

backup check logical database; 

sql 'alter system archive log current'; 

backup archivelog all delete all input; 

 allocate channel for maintenance device type disk; 

RUN {  

crosscheck backup of database; 

crosscheck backup of controlfile; 

crosscheck archivelog all;} 

delete force noprompt expired backup of database; 

delete force noprompt expired backup of controlfile; 

delete force noprompt expired archivelog all; 

delete force noprompt obsolete redundancy 5; 

sql "ALTER DATABASE BACKUP CONTROLFILE TO TRACE"; 

sql "ALTER DATABASE BACKUP CONTROLFILE TO ''D:\FRA\SBY01\SBY01_CONTROLFILE_BACKUP.CTL'' REUSE"; 

sql "CREATE PFILE=''C:\oracle\product\10.2.0\db_1\admin\SBY01\pfile\INITSBY01.INI'' FROM  SPFILE"; 

exit; 

Bug 9789190 - RMAN RESTORE raises ORA-19660 from a compressed backup taken from an 6k DB_BLOCK_SIZE [ID 9789190.8]

I finally found the issue with my migrated database. It's a bug - it prevents RMAN compressed backups made with a 6k blocksize from being usable. Yup, my backups were completely unusable. Thank goodness I discovered that BEFORE needing a backup.

I'm not going to reproduce the text of the bug. Its Bug ID is 9789190 - you can look it up.

The bug describes it as compressed backups created using an 'unusual' block size. Well, OK, is 6k an unusual block size? I thought in 9i it was pretty common. It's been fixed in 11.2.0.1, it definitely affects 10.2.0.5 & 10.2.0.4.

I discovered it by accident. After converting the training db, I backed it up, and then messed around, then restored it. It didn't restore. I wasn't concerned about the restore per se, since I still had the offline backup from the 9i db. But, I was concerned that a freshly baked backup didn't work. That's how I found this bug. I was not a happy bunny when I found it, since that customer's live db has the same problem.

But, there are workarounds if you discover this issue the easy way. Don't use compressed backups or don't use an 'unusual' blocksize. Sigh.

I tested the use of uncompressed RMAN backups, and yes, that works. I would also say that the standard Export I would also highly recommend in such circumstances.

In a separate blog I'll detail how I'm going to deal with this permanently.

RMAN Restore by Command-Line

Overview: I will explain RMAN fundamentals and work through an example to demonstrate an RMAN restore.

Fundamentals

An RMAN restore is really just two commands:

1. RESTORE DATABASE. This command instructs RMAN to search the controlfile for a list of valid database backups, select the most recent and issue a restore. A restore is simply the placing of database files on disk.

2. RECOVER DATABASE. This command tells RMAN: make the database consistent. Before a database can be opened, it must be consistent. Unless directed by Oracle Support, this rule can not be overturned, and is always enforced by the database.

In Oracle terms, consistency means all database files must be at the same System Change Number(SCN). SCN is the way Oracle keeps internal time – every change to the database is assigned a unique SCN. Every change – INSERT, UPDATE, DELETE, ALTER, COMMIT or System Checkpoints – is assigned a unique SCN.

Now, a consistent restore is not necessarily complete. In Oracle terms, if a backup was taken while the database was open (hence using archivelog mode), any recovery from such a backup will be incomplete.

A backup taken from an open database in archivelog mode can not backup the online REDO logs. So any changes recorded in a Redo log will not be backed up, hence the reason why the restore is referred to as incomplete. This is not a problem, since for most intents & purposes, a restore to the last available archivelog is expected.

Example

We will use RMAN to restore a database called LIVE as the example process. We assume:

·         The database was in archivelog mode

·         Autobackup turned on

·         The backup & autobackup are available on disk

In this example, commands to be typed are shown in bold - in the context of the command output. Steps should be followed in the order given since later steps depend on successful completion of previous steps.

1. This step may be optional. If the restore is to be done on a new server, then this step is necessary, otherwise the instance may already exist. Create a new Oracle instance called LIVE using these commands in a new Command Prompt window:

CD /D C:\oracle\product\10.2.0\db_1\BIN

oradim -NEW -SID LIVE -SYSPWD password_live

2. In this step, we will use RMAN to restore the latest spfile and controlfile using what is known as an Autobackup. An autobackup contains the controlfile and spfile of the LIVE database.

HUMAN INTERVENTION NECESSARY: You need to identify the appropriate autobackup file. It is usually located in the latest-dated sub-folder with an Oracle-generated name. When typing the commands below, substitute the appropriate file names.

You will need to determine the appropriate controlfile path & name for this step. Note that the entire file name is enclosed by single quotes.

3. Perform the restore:
1. Open a Command prompt and type:

set oracle_sid=LIVE

   rman target /

Recovery Manager: Release 10.2.0.1.0 - Production on Thu Oct 27 16:18:41 2011

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

connected to target database (not started)

RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file 'C:\ORACLEXE\APP\ORACLE\PRODUCT\10.2.0\

SERVER\DATABASE\INITXE.ORA'

starting Oracle instance without parameter file for retrival of spfile

Oracle instance started

Total System Global Area     159383552 bytes

Fixed Size                     1286292 bytes

Variable Size                 58724204 bytes

Database Buffers              96468992 bytes

Redo Buffers                   2904064 bytes

RMAN> restore spfile from 'C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA\XE\AUTOBAC

KUP\2011_10_27\O1_MF_S_765644418_7BLS3FWT_.BKP';

Starting restore at 27-OCT-11

using channel ORA_DISK_1

channel ORA_DISK_1: autobackup found: C:\ORACLEXE\APP\ORACLE\FLASH_RECOVERY_AREA

\XE\AUTOBACKUP\2011_10_27\O1_MF_S_765644418_7BLS3FWT_.BKP

channel ORA_DISK_1: SPFILE restore from autobackup complete

Finished restore at 27-OCT-11

RMAN>

RMAN> shutdown abort;

ORACLE instance shut down.

RMAN> startup nomount;

ORACLE instance started.

Total System Global Area 1677721600 bytes

Fixed Size                  2066240 bytes

Variable Size             318769344 bytes

Database Buffers         1342177280 bytes

Redo Buffers               14708736 bytes

RMAN> restore controlfile from 'E:\DSLD01\FRA\LIVE\AUTOBACKUP\2010_07_28\O1_MF_S

_725565668_650RLONZ_.BKP';

Starting restore at 29-JUL-10

using channel ORA_DISK_1

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:16

output filename=+DG1/sby02/controlfile/control01.ctl

Finished restore at 29-JUL-10

RMAN> shutdown immediate

ORA-01507: database not mounted

ORACLE instance shut down.

RMAN> startup mount

ORACLE instance started.

Total System Global Area 1677721600 bytes

Fixed Size                  2066240 bytes

Variable Size             318769344 bytes

Database Buffers         1342177280 bytes

Redo Buffers               14708736 bytes

Database mounted.

HUMAN INTERVENTION NECESSARY: Note the following command requires the correct folder/date to be substituted (similar to commands above).

RMAN> catalog start with ‘E:\FRA\LIVE\BACKUPSET\2010_07_28\' noprompt;

Starting implicit crosscheck backup at 29-JUL-10

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=479 devtype=DISK

Crosschecked 8 objects

Finished implicit crosscheck backup at 29-JUL-10

Starting implicit crosscheck copy at 29-JUL-10

using channel ORA_DISK_1

Finished implicit crosscheck copy at 29-JUL-10

searching for all files in the recovery area

cataloging files...

no files cataloged

[Not all output shown, ignore any errors]

4. Here we perform the restore and recover. The Recover command may produce some errors – ignore.

RMAN> RUN

{ allocate channel disk1 type disk;

  RESTORE DATABASE;

  RECOVER DATABASE;

}

allocated channel: disk1

channel disk1: sid=479 devtype=DISK

Starting restore at 29-JUL-10

channel disk1: starting datafile backupset restore

channel disk1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to +DG1/live/datafile/system.256.619109535

restoring datafile 00002 to +DG1/live/datafile/undotbs1.258.619109535

restoring datafile 00003 to +DG1/live/datafile/sysaux.257.619109535

restoring datafile 00004 to +DG1/live/datafile/users.259.619109535

...

channel disk1: reading from backup piece E:\FRA\LIVE\BACKUPSET\2010_07_23

\O1_MF_NNNDF_TAG20100723T180022_64MLO798_.BKP

Make a cup of tea or two. This will take some time.

5. At this point, we should have an incompletely recovered database. RMAN should say:

...

archive log filename=E:\FRA\LIVE\ARCHIVELOG\2010_07_28\O1_MF_1_4607_651BY

17T_.ARC thread=1 sequence=4607

media recovery complete, elapsed time: 00:01:23

Finished recover at 29-JUL-10

released channel: disk1

Continue with the process:

SQL> alter database set standby database to maximize performance;

Database altered.

SQL> alter database open resetlogs;

Database altered.

SQL> QUIT;

That’s the end of the RMAN restore process.