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 = <number_of_days_to_keep>
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 TYPE 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;
