Muhammad Nadeem Chaudhry's Blog


Recovery Catalog

Posted in Uncategorized by Muhammad Nadeem Chaudhry on February 21, 2011

Purpose of the Recovery Catalog

A recovery catalog is a database schema used by RMAN to store metadata about one or more Oracle databases. Typically, you store the catalog in a dedicated database. A recovery catalog provides the following benefits:

  • A recovery catalog creates redundancy for the RMAN repository stored in the control file of each target database. The recovery catalog serves as a secondary metadata repository. If the target control file and all backups are lost, then the RMAN metadata still exists in the recovery catalog.
  • A recovery catalog centralizes metadata for all your target databases. Storing the metadata in a single place makes reporting and administration tasks easier to perform.
  • A recovery catalog can store metadata history much longer than the control file. This capability is useful if you have to do a recovery that goes further back in time than the history in the control file. The added complexity of managing a recovery catalog database can be offset by the convenience of having the extended backup history available.

Some RMAN features function only when you use a recovery catalog. For example, you can store RMAN scripts in a recovery catalog. The chief advantage of a stored script is that it is available to any RMAN client that can connect to the target database and recovery catalog. Command files are only available if the RMAN client has access to the file system on which they are stored.

A recovery catalog is required when you use RMAN in a Data Guard environment. By storing backup metadata for all primary and standby databases, the catalog enables you to offload backup tasks to one standby database while enabling you to restore backups on other databases in the environment.

Basic Concepts for the Recovery Catalog

The recovery catalog contains metadata about RMAN operations for each registered target database. When RMAN is connected to a recovery catalog, RMAN obtains its metadata exclusively from the catalog. The catalog includes the following types of metadata:

  • Datafile and archived redo log backup sets and backup pieces
  • Datafile copies
  • Archived redo logs and their copies
  • Database structure (tablespaces and datafiles)
  • Stored scripts, which are named user-created sequences of RMAN commands
  • Persistent RMAN configuration settings

Stored Scripts

You can use a stored script as an alternative to a command file for managing frequently used sequences of RMAN commands. The script is stored in the recovery catalog rather than on the file system.

A local stored script is associated with the target database to which RMAN is connected when the script is created, and can only be executed when you are connected to this target database. A global stored script can be run against any database registered in the recovery catalog. A virtual private catalog user has read-only access to global scripts. Creating or updating global scripts must be done while connected to the base recovery catalog.

Create Recovery Catalog

First create a user to hold the recovery catalog:

CONNECT sys/password@w2k1 AS SYSDBA

— Create tablepsace to hold repository

CREATE TABLESPACE “RMAN”

DATAFILE ‘C:\ORACLE\ORADATA\W2K1\RMAN01.DBF’ SIZE 6208K REUSE

AUTOEXTEND ON NEXT 64K MAXSIZE 32767M

EXTENT MANAGEMENT LOCAL

SEGMENT SPACE MANAGEMENT AUTO; 

— Create rman schema owner

CREATE USER rman IDENTIFIED BY rman

TEMPORARY TABLESPACE temp

DEFAULT TABLESPACE rman

QUOTA UNLIMITED ON rman;

GRANT connect, resource, recovery_catalog_owner TO rman;

Then create the recovery catalog:

C:>rman catalog=rman/rman@w2k1

Recovery Manager: Release 9.2.0.1.0 – Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to recovery catalog database

recovery catalog is not installed

RMAN> create catalog tablespace “RMAN”;

recovery catalog created

RMAN> exit

Recovery Manager complete.

Register Database

Each database to be backed up by RMAN must be registered:

C:>rman catalog=rman/rman@w2k1 target=sys/password@w2k2

Recovery Manager: Release 9.2.0.1.0 – Production

Copyright (c) 1995, 2002, Oracle Corporation.  All rights reserved.

connected to target database: W2K2 (DBID=1371963417)

connected to recovery catalog database

RMAN> register database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

Creating Stored Scripts

You can use the CREATE SCRIPT command to create a stored script. If GLOBAL is specified, then a global script with this name must not already exist in the recovery catalog. If GLOBAL is not specified, then a local script must not already exist with the same name for the same target database. Note that you can also use the REPLACE SCRIPT to create a new script or update an existing script.

To create a stored script:

Start RMAN and connect to a target database and recovery catalog (if used).

Run the CREATE SCRIPT command.

The following example illustrates creation of a local script:

CREATE SCRIPT full_backup

{    

  BACKUP DATABASE PLUS ARCHIVELOG;

  DELETE OBSOLETE;

}

For a global script, the syntax is similar:

CREATE GLOBAL SCRIPT global_full_backup

{    

  BACKUP DATABASE PLUS ARCHIVELOG;

  DELETE OBSOLETE;

}

Optionally, you can provide a COMMENT with descriptive information:

CREATE GLOBAL SCRIPT global_full_backup

COMMENT ‘use only with ARCHIVELOG mode databases’

{    

  BACKUP DATABASE PLUS ARCHIVELOG;

  DELETE OBSOLETE;

}

You can also create a script by reading its contents from a text file. The file must begin with a left brace ({) character, contain a series of commands valid within a RUN block, and end with a right brace (}) character. Otherwise, a syntax error is signalled, just as if the commands were entered at the keyboard.

CREATE SCRIPT full_backup

 FROM FILE ‘/tmp/my_script_file.txt’;

Examine the output.

If no errors are displayed, then RMAN successfully created the script and stored in the recovery catalog.

Replacing Stored Scripts

To update stored scripts, use the REPLACE SCRIPT command. If you are replacing a local script, then you must be connected to the target database that you connected to when you created the script. If the script does not already exist, then RMAN creates it.

To replace a stored script:

Start RMAN and connect to a target database and recovery catalog (if used).

Execute REPLACE SCRIPT.

This following example updates the script full_backup with new contents:

REPLACE SCRIPT full_backup

{

  BACKUP DATABASE PLUS ARCHIVELOG;

}

You can update global scripts by specifying the GLOBAL keyword as follows:

REPLACE GLOBAL SCRIPT global_full_backup

COMMENT ‘A script for full backup to be used with any database’

{

  BACKUP AS BACKUPSET DATABASE PLUS ARCHIVELOG;

}

As with CREATE SCRIPT, you can update a local or global stored script from a text file with the following form of the command:

REPLACE GLOBAL SCRIPT global_full_backup

  FROM FILE ‘/tmp/my_script_file.txt’;

Executing Stored Scripts

Use the EXECUTE SCRIPT command to run a stored script. If GLOBAL is specified, then a global script with this name must already exist in the recovery catalog; otherwise, RMAN returns error RMAN-06004. If GLOBAL is not specified, then RMAN searches for a local stored script defined for the current target database. If no local script with this name is found, then RMAN searches for a global script by the same name and executes it if one is found.

To execute a stored script:

Start RMAN and connect to a target database and recovery catalog (if used).

If needed, use SHOW to examine your configured channels.

Your script will use the automatic channels configured at the time you execute the script. Use ALLOCATE CHANNEL commands in the script if you need to override the configured channels. Because of the RUN block, if an RMAN command in the script fails, subsequent RMAN commands in the script will not execute.

Run EXECUTE SCRIPT. This command requires a RUN block, as shown in the following example:

RUN

{

  EXECUTE SCRIPT full_backup;

}

The preceding command invokes a local script if one is with the name specified. If no local script is found, but there is a global script with the name specified, then RMAN executes the global script.

You can also use EXECUTE GLOBAL SCRIPT to control which script is invoked if a local and a global script have the same name. If there is no local script called global_full_backup, the following two commands have the same effect:

RUN

{

  EXECUTE GLOBAL SCRIPT global_full_backup;

}

RUN

{

  EXECUTE SCRIPT global_full_backup;

}

Creating and Executing Dynamic Stored Scripts

You can specify substitution variables in the CREATE SCRIPT command. When you start RMAN on the command line, the USING clause specifies one or more values for use in substitution variables in a command file. As in SQL*Plus, &1 indicates where to place the first value, &2 indicates where to place the second value, and so on.

To create and use a dynamic stored script:

Create a command file that contains a CREATE SCRIPT statement with substitution variables for values that must be dynamically updated.

The following example uses substitution variables for the name of the tape set, for a string in the FORMAT specification, and for the name of the restore point.

CREATE SCRIPT quarterly {

  ALLOCATE CHANNEL c1

    DEVICE TYPE sbt

    PARMS ‘ENV=(OB_MEDIA_FAMILY=&1)’;

  BACKUP

    TAG &2

    FORMAT ‘/disk2/bck/&1%U.bck’

    KEEP FOREVER

    RESTORE POINT &3

    DATABASE;

}

Connect RMAN to a target database (which must be mounted or open) and recovery catalog, specifying the initial values for the recovery catalog script.

For example, enter the following command:

% rman TARGET / CATALOG rman@catdb USING arc_backup bck0906 FY06Q3

A recovery catalog is required for KEEP FOREVER, but is not required for any other KEEP option.

Run the command file created in the first step to create the stored script.

For example, run the /tmp/catscript.rman command file as follows:

RMAN> @/tmp/catscript.rman

Note that this step creates but does not execute the stored script.

Every quarter, execute the stored script, passing values for the substitution variables.

The following example executes the recovery catalog script named quarterly. The example specifies arc_backup as the name of the media family (set of tapes), bck1206 as part of the FORMAT string and FY06Q4 as the name of the restore point.

RUN

{

  EXECUTE SCRIPT quarterly

    USING arc_backup

          bck1206

          FY06Q4;

}

Printing Stored Scripts

The PRINT SCRIPT command displays a stored script or writes it out to a file.

To print stored scripts:

Start RMAN and connect to a target database and recovery catalog.

Run the PRINT SCRIPT command as follows:

PRINT SCRIPT full_backup;

To send the contents of a script to a file, use this form of the command:

PRINT SCRIPT full_backup 

  TO FILE ‘/tmp/my_script_file.txt’;

For global scripts, the analogous syntax would be as follows:

PRINT GLOBAL SCRIPT global_full_backup;

PRINT GLOBAL SCRIPT global_full_backup

  TO FILE ‘/tmp/my_script_file.txt’;

Listing Stored Script Names

Use the LIST … SCRIPT NAMES command to display the names of scripts defined in the recovery catalog. LIST GLOBAL SCRIPT NAMES and LIST ALL SCRIPT NAMES are the only commands that work when RMAN is connected to a recovery catalog without connecting to a target instance; the other forms of the LIST … SCRIPT NAMES command require a recovery catalog connection.

To list stored script names:

Start RMAN and connect to a target database and recovery catalog.

Run the LIST … SCRIPT NAMES command.

For example, run the following command to list the names of all global and local scripts that can be executed for the currently connected target database:

LIST SCRIPT NAMES;

The following example lists only global script names:

LIST GLOBAL SCRIPT NAMES;

To list the names of all scripts stored in the current recovery catalog, including global scripts and local scripts for all target databases registered in the recovery catalog, use the following form of the command:

LIST ALL SCRIPT NAMES;

The output will indicate for each script listed which target database the script is defined for (or whether a script is global).

Deleting Stored Scripts

Use the DELETE GLOBAL SCRIPT command to delete a stored script from the recovery catalog.

To delete a stored script:

Start RMAN and connect to a target database and recovery catalog.

Enter the DELETE SCRIPT command.

If you use DELETE SCRIPT without GLOBAL, and there is no stored script for the target database with the specified name, then RMAN looks for a global stored script by the specified name and deletes the global script if it exists. For example, suppose you enter the following command:

DELETE SCRIPT ‘global_full_backup’;

In this case, RMAN looks for a script global_full_backup defined for the connected target database, and if it did not find one, it searches the global scripts for a script called global_full_backup and delete that script.

To delete a global stored script, use DELETE GLOBAL SCRIPT:

DELETE GLOBAL SCRIPT ‘global_full_backup’;

Executing a Stored Script at RMAN Startup

To run the RMAN client and start a stored script in the recovery catalog on startup, use the SCRIPT argument when starting the RMAN client. For example, you could enter the following command to execute script /tmp/fbkp.cmd:

% rman TARGET / CATALOG rman@catdb SCRIPT ‘/tmp/fbkp.cmd’;

You must connect to a recovery catalog, which contains the stored script, and target database, to which the script will apply, when starting the RMAN client.

If local and global stored scripts are defined with the same name, then RMAN always executes the local script.

Tips – RMAN Restore Preview

Posted in Uncategorized by Muhammad Nadeem Chaudhry on February 9, 2011

The PREVIEW option of the RESTORE command allows you to identify the backups required to complete a specific restore operation. The output generated by the command is in the same format as the LIST command. In addition the PREVIEW SUMMARY command can be used to produce a summary report with the same format as the LIST SUMMARY command. The following examples show how these commands are used:

# Preview
RESTORE DATABASE PREVIEW;
RESTORE TABLESPACE users PREVIEW;

# Preview Summary
RESTORE DATABASE PREVIEW SUMMARY;
RESTORE TABLESPACE users PREVIEW SUMMARY;

Error ORA-01152: file 1 was not restored from a sufficiently old backup

Posted in Uncategorized by Muhammad Nadeem Chaudhry on February 8, 2011

Demo:

 SQL> startup ORACLE instance started.  

Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 145488364 bytes

Database Buffers 25165824 bytes

Redo Buffers 524288 bytes

ORA-00205: error in identifying controlfile, check alert log for more info.

I don’t have automatized control file backup neither i have control file copy.What i will do
to recover control file i will restore controlfile from backup.Before doing it that i will bring
the database in mount state. 

SQL> shutdown immediate ORA-01507: database not mounted    

ORACLE instance shut down.  

SQL> startup mount ORACLE

instance started.  

Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 145488364 bytes

Database Buffers 25165824 bytes

Redo Buffers 524288 bytes

ORA-00205: error in identifying controlfile, check alert log for more info

I have two backup piece in my backupset folder you cant check control file backup
existence with list backup controlfile at RMAN prompt cause it needs database in
mount state.You will have to test both backup set pieces.

RMAN> restore controlfile from Autobackup;

Starting restore at 08-DEC-07 using channel ORA_DISK_1 channel ORA_DISK_1: restoring controlfile channel ORA_DISK_1:

restore complete output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL01.CTL output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL02.CTL output filename=C:\ORACLE10G\ORACLE\ORADATA\ORCL1\ORCL1\CONTROL03.CTL

Finished restore at 08-DEC-07  

SQL> startup ORACLE instance started.  

Total System Global Area 171966464 bytes

Fixed Size 787988 bytes

Variable Size 145488364 bytes

Database Buffers 25165824 bytes

Redo Buffers 524288 bytes Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open  

SQL> alter database open resetlogs ;

Database altered.

You can see database now comes into open mode without raising error error
ORA-01152: file 1 was not restored from a sufficiently old backup,you can see that
database shutdown with immediate which synchronized the control file with database
file.

Cause: When you shutdown the database with using the normal,transactional or immediate options not abort a full checkpoint occurs.A full checkpoint ensures that all of the dirty blocks contained in the buffer cache are written to the data files,the database comes into synchronized state at this time i.e controlfile CKPT SCN=databasefile CKPT.

Solution: Simply issue RECOVER DATABASE prior to opening database.

What is checked or what happens when we “run recover database using backup controlfile until cancel;”? What does this command do and when do we use this command?

Posted in Uncategorized by Muhammad Nadeem Chaudhry on February 7, 2011

If you were to recover the database with a current control file, Oralce will know the last SCN of the last checkpoint. So Oracle can use the information in the archived and online redo logs to apply recovery up to that SCN. This is called a “complete” recovery.

If you do not have the current control file, your option is to use a backup controlfile. Which means Oracle does not know the SCN to stop applying recovery. So you tell Oracle that you are using a “backup controlfile” and that you will tell it when to stop applying redo by replying “cancel.” When Oracle starts recovery, it looks at the datafiles to know the last time a checkpoint was performed on the datafile. Oracle now knows to start applying recovery to the datafile for all SCNs after the SCN in the datafile header. Oracle rolls forward transactions. But Oracle does not know when to stop, and eventually, Oracle applies recovery in all of your archived redo logs. You can then tell Oracle to use the redo in the online redo logs. Oracle will ask you where to find more redo. At this point, you tell it to quit applying redo by replying CANCEL.

Hopefully, you never have to use this command. This is because you multiplex your control files and have three copies of the control file, all on different disk units. So if you lose a disk unit, you use the control files on the other disk units.

The only time you need this command is when you lose all of your control files.

In this case, either use a binary backup of the control file or you re-create the control file with the CREATE CONTROLFILE command and then recover with the backup control file.