RMAN> connect target
connected to target database: ECENTRIC (DBID=1755705917)
RMAN> connect catalog
connected to recovery catalog database
RMAN> list backup;
starting full resync of recovery catalog
RMAN-00571: ==========================================================
RMAN-00569: ===== ERROR MESSAGE STACK FOLLOWS ===========
RMAN-00571: ===============================================
RMAN-03002: failure of list command at 09/02/2008 08:58:46
RMAN-03014: implicit resync of recovery catalog failed
RMAN-03009: failure of full resync command on default channel at 09/02/2008 08:
8:46
ORA-01653: unable to extend table RMAN.ROUT by 128 in tablespace RMAN_DATA
Connect to catalog database ie. Atlantis
SQL> alter tablespace rman_data coalesce;
Tablespace altered.
SQL> alter database datafile ‘e:\oradata\atlantis\rman_data.dbf’
autoextend on
maxsize unlimited;
Database altered.
RMAN> crosscheck backup;
allocated channel: ORA_SBT_TAPE_1
channel ORA_SBT_TAPE_1: sid=132 devtype=SBT_TAPE
channel ORA_SBT_TAPE_1: Tivoli Data Protection for Oracle: version 5.2.0.
allocated channel: ORA_SBT_TAPE_2
channel ORA_SBT_TAPE_2: sid=24 devtype=SBT_TAPE
channel ORA_SBT_TAPE_2: Tivoli Data Protection for Oracle: version 5.2.0.
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=86 devtype=DISK
Crosschecked 20 objects
Tags:ORA-01653: unable to extend table, recovery catalog, RMAN
Posted in RMAN | Leave a Comment »
BASICS OF RMAN
September 2, 2008
WHAT IS RMAN ?
Recovery Manager is a tool that: manages the process of creating backups and also manages the process of restoring and recovering from them.
WHY USE RMAN ?
- No extra costs …Its available free
- RMAN introduced in Oracle 8 it has become simpler with newer versions and easier than user managed backups
- Proper security
- You are 100% sure your database has been backed up.
- Its contains detail of the backups taken etc in its central repository
- Facility for testing validity of backups also commands like crosscheck to Check the status of backup.
- Faster backups and restores compared to backups without RMAN
- RMAN is the only backup tool which supports incremental backups.
- Oracle 10g has got further optimized incremental backup which has resulted in improvement of performance during backup and recovery time
- Parallel operations are supported
- Better querying facility for knowing different details of backup
- No extra redo generated when backup is taken..compared to online backup without RMAN which results in saving of space in hard disk
- RMAN an intelligent tool
- Maintains repository of backup metadata
- Remembers backup set location
- Knows what need to backed up
- Knows what is required for recovery
- Knows what backup are redundant
UNDERSTANDING THE RMAN ARCHITECTURE
- An oracle RMAN comprises of RMAN EXECUTABLE This could be present and fired even through client side TARGET DATABASE This is the database which needs to be backed up
- RECOVERY CATALOG Recovery catalog is optional otherwise backup details are stored in target database controlfile
- It is a repository of information queried and updated by Recovery Manager
- It is a schema or user stored in Oracle database
- One schema can support many databases
- It contains information about physical schema of target database datafile and archive log, backup sets and pieces
Recovery catalog is a must in following scenarios
- In order to store scripts
- For tablespace point in time recovery
Media Management Software
Media Management software is a must if you are using RMAN for storing backup in tape drive directly.
Backups in RMAN
Oracle backups in RMAN are of the following type
RMAN complete backup OR RMAN incremental backup
These backups are of RMAN proprietary nature
IMAGE COPY
Its again a kind of backup. The advantage of uing Image copy is its not in RMAN proprietary format.
Backup Format
RMAN backup is not in oracle format but in RMAN format. Oracle backup comprises of backup sets and it consists of backup pieces. Backup sets are logical entity. In oracle 9i it gets stored in a default location.
There are two type of backup sets
- Datafile backup sets,
- Archivelog backup sets
One more important point of data file backup sets is it do not include empty blocks. A backup set would contain many backup pieces. A single backup piece consists of physical files which are in RMAN proprietary format.
You can go to RMAN prompt by just typing rman. RMAN executable is present in ORACLE_HOME/bin location.
bash-2.05$ rmanRecovery Manager: Release 10.2.0.1.0 – Production on Mon Jul 23 02:16:55 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.RMAN>You can use target connect to connect to database. The database it will connect to depends on the environment variable ORACLE_HOME.
RMAN> connect target
connected to target database: ORCL (DBID=1156435946)
Alternatively you can use “rman TARGET SYS/oracle@test NOCATALOG” to connect to the RMAN of “test” instance.
Here we will be using target database control file to store all the information required for RMAN, like backupsets and backup image information etc.
Backup Database:
RMAN> shutdown immediate
using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down
RMAN> startup mount;
connected to target database (not started)
Oracle instance started
database mountedTotal System Global Area 1258291200 bytesFixed Size 1978336 bytes
Variable Size 318771232 bytes
Database Buffers 922746880 bytes
Redo Buffers 14794752 bytes
RMAN> backup database;
Starting backup at 23-JUL-07
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/dy/oracle/product/oradata/orcl/system01.dbf
input datafile fno=00003 name=/dy/oracle/product/oradata/orcl/sysaux01.dbf
input datafile fno=00005 name=/dy/oracle/product/oradata/orcl/example01.dbf
input datafile fno=00002 name=/dy/oracle/product/oradata/orcl/undotbs01.dbf
input datafile fno=00004 name=/dy/oracle/product/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 23-JUL-07
channel ORA_DISK_1: finished piece 1 at 23-JUL-07
piece handle=/dy/oracle/product/flash_recovery_area/ORCL/backupset/2007_07_23/
o1_mf_nnndf_TAG20070723T031355_3b8zv57d_.bkp tag=TAG20070723T031355 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:36
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset
channel ORA_DISK_1: starting piece 1 at 23-JUL-07
channel ORA_DISK_1: finished piece 1 at 23-JUL-07
piece handle=/dy/oracle/product/flash_recovery_area/ORCL/backupset/2007_07_23/
o1_mf_ncsnf_TAG20070723T031355_3b8zy7xr_.bkp tag=TAG20070723T031355 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:06
Finished backup at 23-JUL-07
Creating RMAN Catalog
You can also create a repository for RMAN to store all this information. Repository will be just another small database which can store the catalog information. Creating a catalog is a 3 step process
1) Create database which will hold the catalog. Else you can use the existing database also. All you need is to create a seperate tablespace for holding the information about RMAN catalog.
2) Create RMAN tablespace and RMAN user
SQL> create tablespace rman_tbs datafile ‘/dy/oracle/product/db10g/dbf/rman01.dbf’ size 500M EXTENT MANAGEMENT LOCAL segment SPACE MANAGEMENT AUTO ;
Tablespace created.
SQL> create user rman identified by rman
2 default tablespace rman_tbs
3 TEMPORARY TABLESPACE TEMPTS1
4 QUOTA UNLIMITED ON rman_tbs account unlock;
User created.
After creating user, you need to grant RECOVERY_CATALOG_OWNER role to that user.
SQL> grant recovery_catalog_owner to rman;
Grant succeeded.
3)Now Create RMAN catalog.
bash-2.05$ rman catalog rman/rman@testRecovery Manager: Release 10.2.0.1.0 – Production on Mon Jul 23 04:37:10 2007Copyright (c) 1982, 2005, Oracle. All rights reserved.connected to recovery catalog database
RMAN> create catalog
recovery catalog created
For registering the database, you need to get connected to database as well as catalog at the same time. Here is how you can do.
RMAN> connect target
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20001: target database not found in recovery catalog
The above error is because the database we connected to is not found in the catalog database. We can register the database in catalog.
RMAN> register database;
database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
Registering database will also resynch the information present in the target database control file and catalog database. Since we have taken 1 backup early, it will synchup that information with RMAN catalog. We can check the same using LIST BACKUP command at RMAN prompt.
RMAN> list backup;List of Backup Sets
===================o1_mf_nnndf_TAG20070723T031355_3b8zv57d_.bkp
List of Datafiles in backup set 18
File LV Type Ckp SCN Ckp Time Name
—- — —- ———- ——— —-
1 Full 562487 23-JUL-07 /dy/oracle/product/oradata/orcl/system01.dbf
2 Full 562487 23-JUL-07 /dy/oracle/product/oradata/orcl/undotbs01.dbf
3 Full 562487 23-JUL-07 /dy/oracle/product/oradata/orcl/sysaux01.dbf
4 Full 562487 23-JUL-07 /dy/oracle/product/oradata/orcl/users01.dbf
5 Full 562487 23-JUL-07 /dy/oracle/product/oradata/orcl/example01.dbfBS Key Type LV Size Device Type Elapsed Time Completion Time
——- —- — ———- ———– ———— —————
19 Full 6.80M DISK 00:00:05 23-JUL-07
BP Key: 21 Status: AVAILABLE Compressed: NO Tag: TAG20070723T031355
Piece Name: /dy/oracle/product/flash_recovery_area/ORCL/backupset/2007_07_23/o1_mf_ncsnf_TAG20070723T031355_3b8zy7xr_.bkp
Control File Included: Ckp SCN: 562487 Ckp time: 23-JUL-07
SPFILE Included: Modification time: 23-JUL-07
RMAN Command line
Backing up the Controlfile and Spfile
The control file can be automatically backed up after each RMAN backup and database structure change as a way to protect the RMAN repository (when we are not using a seperate catalog for RMAN).
RMAN> configure controlfile autobackup on;
Backing up control file
RMAN> backup current controlfile;
Creating Image copy of all datafiles in database
These are the image copies and are stored in ORACLE format and not in RMAN format. Backupsets and backuppieces are stored in internal RMAN format. Hence these image copies can be used for manual restore and recovery as well.
RMAN> backup as copy database;
Creating backupsets of all datafiles in database
If you specify BACKUP AS BACKUPSET, then RMAN stores its backups in backup sets. A backup set, consisting of one or more backup pieces, contains the physical file data being backed up. This backupset is written in a format that only RMAN can access. Only RMAN can create and restore backup sets. Backup sets can be written to disk or tape, and they are the only type of backup which RMAN can use to write backups to tape.
RMAN> backup as backupset database;
Backup individual tablespace as backupsets
RMAN> backup tablespace system, HTMLDB;
Backup individual tablespace as image copies
RMAN> backup as copy tablespace system;
Backup individual files as image copies
RMAN> backup as copy datafile ‘/dy/oracle/product/db10g/dbf/system01.dbf’;
Backup individual files as Backupsets
RMAN> backup datafile ‘/dy/oracle/product/db10g/dbf/system01.dbf’;
Backup archivelogs
We can backup the archive logs according to the output of some search condition. Example we want to backup only those archivelogs which starts with “ARCH_616814159_”.
RMAN> backup acrchivelog like ‘%ARCH_616814159_%’;
Copy archivelogs from some time stamp.
Suppose we want to copy the archivelogs of last 2 days, then we can use the following commands.
RMAN> BACKUP ARCHIVELOG from time ’sysdate-2′;
Backup all archivelog files
RMAN> Backup archivelog all;
Backup archivelog between some time.
RMAN> BACKUP ARCHIVELOG FROM TIME ‘SYSDATE-30′ UNTIL TIME ‘SYSDATE-7′;
Specifying copies while backing up.
RMAN> backup copies 2 datafile ‘/dy/oracle/product/db10g/dbf/cs_tbs01.dbf’;
Remember that copies option cannot be used with image copies. It can be used only with backupsets.
Giving tags to backups
RMAN> BACKUP TAG ‘weekly_full_db_bkup’ DATABASE MAXSETSIZE 100M;
Backing up backupsets
RMAN> BACKUP BACKUPSET ALL;
Backup imagecopies
RMAN> Backup as copy backupset all;
List Imagecopies
RMAN> list copy;
List Backupsets
RMAN> list backup;
Restoring and Recovering the database
Use the RESTORE and RECOVER commands for RMAN restore and recovery of physical database files.
RMAN> STARTUP FORCE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN;
Recovering Current Tablespaces
RMAN> SQL ‘ALTER TABLESPACE users OFFLINE’;
RESTORE TABLESPACE users;
RECOVER TABLESPACE users;
SQL ‘ALTER TABLESPACE users ONLINE;
Recovering Current Datafiles
RMAN> SQL ‘ALTER DATABASE DATAFILE 7 OFFLINE’;
RESTORE DATAFILE 7;
RECOVER DATAFILE 7;
SQL ‘ALTER DATABASE DATAFILE 7 ONLINE’;
Recovering Individual Data Blocks
RMAN can recover individual corrupted datafile blocks. When RMAN performs a complete scan of a file for a backup, any corrupted blocks are listed in V$DATABASE_BLOCK_CORRUPTION. Corruption is usually reported in alert logs, trace files or results of SQL queries. Use BLOCKRECOVER to repair all corrupted blocks:
RMAN> BLOCKRECOVER CORRUPTION LIST;
You can also recover individual blocks, as shown in this example:
RMAN> BLOCKRECOVER DATAFILE 7 BLOCK 233, 235 DATAFILE 4 BLOCK 101;
Managing RMAN repository
As you know that if you do not use a recovery catalog, then control file of the target database is used as RMAN repository and eventually after some time the control file records for RMAN information will get overwritten.
Set this initialization parameter in the parameter file of the target database to determine how long records are kept:
CONTROL_FILE_RECORD_KEEP_TIME =
You can configure a retention policy to be used by RMAN to determine which backups are considered obsolete. This allows you to remove files from the repository that are no longer needed to meet your retention requirements. This policy can be based on a recovery window (the maximum number of days into the past for which you can recover) or redundancy (how many copies of each backed-up file to keep).
Two Parameters are used to set retention policies.
You can specify the days days between the current time and the earliest point of recoverability, this is called RECOVERY WINDOW. RMAN does not consider any full or level 0 incremental backup as obsolete if it falls within the recovery window.
Alternatively the REDUNDANCY parameter will instruct to store the number of copies of backup in RMAN repository. If the number of backups for a specific datafile or control file exceeds the REDUNDANCY setting considers the extra backups as obsolete.
RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
This will make the REDUNDANCY setting to 3. Meaning that it will at max store 3 copies of backups and ikmages of datafile. Any more images or backups are consider obsolete.
When we run the below command
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
it will configure new retention policy based on the RECOVERY WINDOW of 7 days. This means that all the backups which falls outside this window will be considered obsolute. So in this case you need to have backup scheduled every week to have atleast 1 valid backup.
Remember that at any point of time, only one policy can be active. It can be either REDUNDANCY or RECOVERY WINDOW.
When you change the retention policy to another one, it will suspend the previous policy as shown below.
RMAN> CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
old RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO REDUNDANCY 3;
new RMAN configuration parameters:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete
Cross checking the available backups
RMAN> CROSSCHECK BACKUP;
Crosscheck is needed when an archivelog file or backup is manually removed, i.e., not deleted by RMAN. This command ensures that data about backups in the recovery catalog or control file is synchronized with corresponding data on disk or in the media management catalog. The CROSSCHECK command operates only on files that are recorded in the recovery catalog or the control file.
The CROSSCHECK command does not delete any files that it is unable to find, but updates their repository records to EXPIRED. Then, you can run DELETE EXPIRED to remove the repository records for all expired files as well as any existing physical files whose records show the status EXPIRED.
If some backup pieces or copies were erroneously marked as EXPIRED, for example, because the media manager was misconfigured, then after ensuring that the files really do exist in the media manager, run the CROSSCHECK BACKUP command again to restore those files to AVAILABLE status.
RMAN> CROSSCHECK COPY;
This will validate the image copies.
Deleting the backups
The DELETE command removes RMAN backups and copies from DISK marks the records in control file as DELETED or removes the records from the recovery catalog (if you use a catalog).
RMAN> DELETE BACKUPSET 101, 102, 103;
RMAN> delete controlfilecopy ‘%ctl’; # Pattern search is allowed.
RMAN> delete archivelog until sequence 20;
This will delete all the archives from the oldest one till the sequence we have specified.
RMAN> delete backup of tablespace system;
RMAN> DELETE ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE ENGINE DISK;
List commands
RMAN> list backup;
RMAN> list copy;
RMAN> list incarnation of database;
RMAN> list expired backupset;
RMAN> list expired copy;
RMAN> list backup of tablespace sysaux;
RMAN> list copy of datafile 3;
Reporting in RMAN
RMAN> report need backup;
Reports which database files need to be backed up to meet a configured or specified retention policy
RMAN> report unrecoverable;
Reports which database files require backup because they have been affected by some NOLOGGING operation such as a direct-path insert
RMAN> report need backup recovery window of 7 days;
Displays objects requiring backup to satisfy a recovery window-based retention policy.
Suppose in the above command we want to skip a perticular tablespace like perfstat then we can use the below command.
RMAN> report need backup recovery window of 2 days database skip tablespace perfstat;
RMAN> report need backup redundancy 3;
Displays objects requiring backup to satisfy a redundancy-based retention policy.
RMAN> report need backup days 7;
Displays files that require more than n days’ worth of archived redo log files for recovery.
RMAN> report need backup incremental 7;
Displays files that require application of more than n incremental backups for recovery.
RMAN> report need backup redundancy 2 datafile 3;
Gives report of files with less than 2 redundant backups.
RMAN> report need backup tablespace htmldb;
Report of files that must be backed up to satisfy current retention policy for this tablespace (htmldb)
RMAN> report need backup incremental 2;
Report of files that need more than 2 incrementals during recovery
RMAN> report need backup device type disk;
Report of files that must be backed up to satisfy current retention policy for database
RMAN> report obsolete;
Shows backups that are obsolete according to the current retention policy.
You can add the options RECOVERY WINDOW and REDUNDANCY with this command as given below.
RMAN> report obsolete recovery window of 3 days;
RMAN> report obsolete redundancy 2;
RMAN> report schema;
This command lists and displays information about the database files.
RMAN> report schema at time ’sysdate – 14′;
This command gives report on schema 14 days ago.
RMAN> report schema at SCN 10000;
This gives report on schema at scn 10000.
RMAN> report schema at sequence 55 thread 1;
Gives report of schema at sequence 55.
RMAN configuration
RMAN> show all;
RMAN configuration parameters are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘%F’; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM ‘AES128′; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO ‘/dy/oracle/product/db10g/dbs/snapcf_test.f’; # default
We can change each of these parameters as per our requirements.
You can return any setting to its default value by using CONFIGURE… CLEAR
RMAN> CONFIGURE BACKUP OPTIMIZATION CLEAR;
RMAN> CONFIGURE RETENTION POLICY CLEAR;
By default, RMAN sends all backups to an operating system specific directory on disk. So default setting for DEVICE TYPE is DISK. You can configure to make backups by default on tape or any other device as given below.
RMAN> CONFIGURE DEFAULT DEVICE TYPE TO sbt;
You can configure backup sets or image copies as the default for a perticular device type, using either of the following commands:
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COPY; # Default becomes image copies
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET; # Default becomes uncompressed
You can configure RMAN to use compressed backupsets by default on a particular device type, by using the CONFIGURE DEVICE TYPE command with the BACKUP TYPE TO COMPRESSED BACKUPSET option, as shown in the following examples.
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET;
RMAN> CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO COMPRESSED BACKUPSET;
To disable compression you can use below command
RMAN> CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO BACKUPSET;
RMAN> CONFIGURE DEVICE TYPE sbt BACKUP TYPE TO BACKUPSET;
You can configure the parallelism for a device type.
RMAM> CONFIGURE DEVICE TYPE sbt PARALLELISM 2;
You can turn on/off controlfile autobackup using
RMAN> configure controlfile autobackup off;
RMAN> configure controlfile autobackup on;
We can configure RMAN channel to write backups and images in a specific format.
The following command configures RMAN to write disk backups to the /backup directory
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT ‘/backup/ora_df%t_s%s_s%p’;
%t is replaced with a four byte time stamp,
%s with the backup set number, and
%p with the backup piece number.
You can also configure format for controlfile autobackup as well.
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE deviceSpecifier TO ’string’;
For example, you can run the following command:
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO ‘?/oradata/cf_%F’;
Here %F will be replaced by following
c-IIIIIIIIII-YYYYMMDD-QQ, where:
IIIIIIIIII stands for the DBID.
YYYYMMDD is a time stamp of the day the backup is generated
QQ is the hex sequence that starts with 00 and has a maximum of FF
You can clear the format using following command.
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK CLEAR;