Oracle DBA: 09/20/14

RMAN(RECOVERY MANAGER)

Installed with the database, Recovery Manager (RMAN) is an Oracle database client which performs backup and recovery tasks on your databases and automates administration of your backup strategies. The RMAN environment consists of the utilities and databases that play a role in backing up your data.

· The target database to be backed up.

· The RMAN client, which interprets backup and recovery commands, directs server sessions to execute those commands, and records your backup and recovery activity in the target database control file.

Some environments will also use these optional components:

· A flash recovery area, a disk location in which the database can store and manage files related to backup and recovery.

· Media management software, required for RMAN to interface with backup devices such as tape drives.

· A recovery catalog database, a separate database schema used to record RMAN activity against one or more target databases.

About the Target Database

The target database is the database that you are backing up, restoring, or recovering with RMAN.

About the RMAN Client

· RMAN is a command-line-oriented database client, much like SQL*Plus, with its own command syntax. From the RMAN client you can issue RMAN commands and SQL statements to perform and report on backup and recovery operations.

· RMAN then communicates with one or more server processes on the target database server which actually perform the work. You can also access RMAN through the Enterprise Manager(OEM).

· RMAN executable is typically installed in the same directory as the other database executables. On Unix systems, for example, the RMAN executable is located in $ORACLE_HOME/bin.

About the RMAN Repository

· RMAN maintains metadata about the target database and its backup and recovery operations in the RMAN repository.

· RMAN stores information about its own configuration settings, the target database schema, archived redo logs, and all backup files on disk or tape.

· The primary store for RMAN repository data is always the control file of the target database.

· The CONTROL_FILE_RECORD_KEEP_TIME initialization parameter controls how long backup records are kept in the control file.

· Another copy of the RMAN repository data can also be saved in the recovery catalog.

About the Recovery Catalog

· Using a recovery catalog maintains RMAN repository information if the control file is lost, making it much easier to restore and recover following the loss of the control file.

· A backup control file may not contain complete information about recent available backups.

· The recovery catalog can also store a much more extensive history of your backups than the control file, due to limits on the number of control file records.

· In addition to RMAN repository records, the recovery catalog can also hold RMAN stored scripts, sequences of RMAN commands for common backup tasks. Centralized storage of scripts in the recovery catalog can be more convenient than working with command files.

· Except for stored scripts, all of RMAN's features work equally well with or without a recovery catalog.

About the Flash Recovery Area

· The Automatic Disk-Based Backup and Recovery feature simplifies managing disk space and files related to backup and recovery, by managing all backup and recovery related files in a flash recovery area.

· You set the flash recovery area location and size on disk, using the DB_RECOVERY_FILE_DEST and DB_RECOVERY_FILE_DEST_SIZE initialization parameters.

· RMAN then manages your backup storage, deleting obsolete backups and backups already copied to tape when space is needed, but keeping as many backups on disk as space permits.

· This minimizes restores from tape during data recovery operations to shorten restore and recovery times.

About Media Managers

· To access sequential media devices like tape libraries, RMAN uses third-party media management software.

· A media manager controls these devices during backup and recovery, managing the loading, labeling and unloading of media, among other functions.

· Media management devices are sometimes called SBT (system backup to tape) devices.

· The Oracle Backup Solutions Program (BSP) works with vendors to help them produce media management software for their devices.

· Contact your media management software vendor for details about whether they participate in the BSP and have an RMAN-compatible media management layer.

About RMAN Channels

· RMAN uses server sessions on the target database instance to perform all backup, restore and recovery operations. Each server session used by RMAN is known as an RMAN channel.

· A channel can be either a disk channel, used for backup tasks that perform disk I/O, or an sbt channel, which is used to interact with media managers.

· At the beginning of a series of RMAN commands, you can use the RMAN ALLOCATE CHANNEL command to allocate channels, specifying the number of server sessions to use for the tasks, and settings that affect the behavior of each server session.

· You can also use the RMAN CONFIGURE command to configure channels, specifying persistent settings for the channels RMAN should allocate by default if you do not explicitly allocate channels for a particular task.

· The number of channels that are used for an RMAN job controls the amount of work that is run in parallel. When backing up or restoring files, RMAN automatically schedules the work to make the most use of all the available channels.

· One disk channel is immediately started when RMAN first connects to the target database, and remains as long as RMAN is connected. This channel, known as the default channel, is not used for tasks involving bulk data transfer, such as backup or restore of database files.

About Backup pieces/backup sets:

A backup set consists of one or more files in an RMAN-specific format, known as backup pieces. These backup pieces are stored physically. RMAN can also store backup information a logical structure called a backup set. A backup set contains the data from one or more datafiles, archived redo logs, or control files or SPFILE. Datafiles and archivelogs cannot be mixed together in the same backup set. You can also back up existing backup sets into another backup set.

RMAN CONFIGURATION

RMAN (Oracle9i and higher) now allows the DBA to perform automated database backup and recovery. This feature is supported by RMAN with its ability to define default values for a number of settings, (i.e. channel configuration). Setting RMAN settings is performed with the configure command.

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 OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1; # 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 SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/9.2.0/dbs/snapcf_TARGDB.f';

From the above listing, you can see that the RMAN allows you to set default values for some of the following:

· The default retention policy. This determines which backups and copies are eligible for deletion because they are no longer needed.

· Turning ON and OFF backup optimization. Backup optimization eliminates duplicate backups of identical datafiles (like those associated with a read-only tablespace) and archived redo logs.

· A default device type for RMAN jobs, (disk or tape [sbt])

· The default for automated backups of the control file to ON or OFF. You can also control the default format of the control file backup output files and the default device on which to create these backups.

· The number of channels (for parallelization) that are automatically allocated when performing automated backup and recovery/restore jobs.

· A tablespace exclusion policy to configure specific tablespaces to be excluded during full database backup operations. If there are no tablespace exclusion policies defined, nothing will be displayed by the show all command.

· The maximum size for any given backup piece and the size of any backup set when performing automated backups.

· The default filename for the snapshot controlfile.

This article will provide detailed information on all of these RMAN configuration parameters and how they can be defined.

RETENTION POLICY

Used to control how many backups indicates REDUNDENCY and how long RMAN will keep backups indicates RECOVERY WINDOW.

This can be configured by the "number" of backups taken,

CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

or by the numbers of "days" to keep.

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;

Note that when configuring a retention policy, RMAN will not cause backups to be automatically deleted. The retention policy will, however, mark backups as OBSOLETE that have fallen outside the retention period. RMAN commands like "REPORT OBSOLETE" and "DELETE OBSOLETE" will work with these obsolete backups. you probably will prefer a redundancy policy that is stated in terms of number of backups rather than backups later than n days old. In this case, you would use the redundancy parameter as follows:

RMAN> CONFIGURE RETENTION POLICY TO REDUNDANCY 3;

Finally, if you do not want to use a retention policy, simply use:

RMAN> CONFIGURE RETENTION POLICY TO NONE;

BACKUP OPTIMIZATION

Used to enable or disable backup optimization. Backup file optimization can assist in reducing the space used to backup the database. When performing an RMAN backup, a check is done on the file before backup to see if the file already exists in a backupset with the same information: (dbid, checkpoint, and resetlogs data). For archive logs, the same file means the same dbid, thread, sequence, and resetlogs data. If the DELETE INPUT option is used, RMAN deletes all the files that would have been backed up,

The two possible values for backup optimization is ON and OFF as shown in the following example syntax:

CONFIGURE BACKUP OPTIMIZATION OFF;

CONFIGURE BACKUP OPTIMIZATION ON;

Also note that you should consider how backup optimization works with regards to the RETENTION POLICY. RMAN will only work with files within the retention period. For example, consider performing a backup with optimization enabled and a retention period of 3 days. RMAN will only compare the datafile with backup sets within the 3 day period - even if the datafile it is about to backup hasn't changed within that 3 days.

when enabling backup optimization if you use a media manager that has an expiration policy. The media manager can expire tapes containing backups (using its media control software), and RMAN will not make new backups because of optimization. One way to protect from this is to run CROSSCHECK periodically to synchronize the repository with the media manager.

DEFAULT DEVICE TYPE

Configures the default backup / restore device type for automatic channels. The two values for now are TAPE (SBT) and DISK (DISK) - the default being DISK. By default, the BACKUP and COPY commands only allocate channels of the default device type. For example, if you configure automatic channels for DISK and sbt and set the default device type to DISK, then RMAN only allocates disk channels when you run the BACKUP DATABASE command. You can override this behavior either by manually allocating channels in a RUN command, or by specifying DEVICE TYPE on the BACKUP command itself.

The following two examples show the syntax for configuring the default device type to TAPE (SBT) - then another command to re-configure the default device type to DISK:

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

CONFIGURE DEFAULT DEVICE TYPE TO SBT;

CONTROLFILE AUTOBACKUP

Starting with Oracle9i, RMAN offers the ability to backup the control file and the database parameter file (SPFILE only) and have this take place automatically by default after EACH backup. By default, this feature is not enabled. This feature can be enabled or disabled using the example syntax below:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP OFF;

RMAN automatically backs up the current control file using the default format of %F.

You can change this format using the CONFIGURE CONTROLFILE AUTOBACKUP FORMAT and SET CONTROLFILE AUTOBACKUP FORMAT commands.

NOTE: You may see occasions where RMAN will sometimes automatically include the CONTROLFILE and SPFILE in the backupsets during a backup. This is normal and is documented (above). When CONTROLFILE AUTOBACKUP is not enabled, the CONTROLFILE and SPFILE, (if the database was started with an spfile), are included automatically when datafile 1 is backed up. Consider the following backup:

RMAN> backup datafile 1;

It will include the CONTROLFILE and the SPFILE (when the database is started with a spfile).

CONTROLFILE AUTOBACKUP FORMAT

Configures the default filename format for the control file autobackup on the specified device type. By default, the initial format is %F for all devices. Any default format string specified with CONFIGURE must include the %F substitution variable (see BACKUP). This variable translates into c-IIIIIIIIII-YYYYMMDD-QQ, where:

· IIIIIIIIII - stands for the DBID. (The DBID is printed in decimal so that it can be easily associated with the target database.

· YYYYMMDD - is a time stamp in the Gregorian calendar of the day the backup is generated.

· QQ - is the sequence in hexadecimal number that starts with 00 and has a maximum of 'FF' (256).

Specify CLEAR to return the format to the default %F.

NOTE: The %F tag is essential for RMAN to be able to restore the file without a recovery catalog.

Here is an example of how to set the format of the autobackup controlfile:

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '/orabackup/rman/ORA920/%F';

Format	Description
%p	specifies the backup piece number within the backup set. This value starts at 1 for each backup set and is incremented by 1 as each backup piece is created.
%s	specifies the backup set number. This number is a counter in the control file that is incremented for each backup set. The counter value starts at 1 and is unique for the lifetime of the control file. If you restore a backup control file, then duplicate values can result. Also, CREATE CONTROLFILE initializes the counter back to 1.
%d	specifies the database name.
%n	specifies the database name, padded on the right with 'x' characters to a total length of 8 characters. For example, if PROD1 is the database name, then PROD1xxx is the padded database name.
%t	specifies the backup set timestamp, which is a 4-byte value derived as the number of seconds elapsed since a fixed reference time. The combination of %s and %t can be used to form a unique name for the backup set.
%u	specifies an 8-character name constituted by compressed representations of the backup set number and the time the backup set was created.
%c	(Oracle8i or higher) - specifies the copy number of the backup piece within a set of duplexed backup pieces. If you did not issue the set duplex command, then this variable will be 1 for regular backup sets and 0 for proxy copies. If you issued set duplex, the variable identifies the copy number: 1, 2, 3, or 4.
%U	(Oracle8i or higher) - specifies a convenient shorthand for %u_%p_%c that guarantees uniqueness in generated backup filenames. If you do not specify a format, RMAN uses %U by default. %U is the default in Oracle8i.

PARALLELISM

The PARALLELISM parameter specifies the number of automatic channels of the specified device type allocated for RMAN jobs. RMAN always allocates the number of channels specified by PARALLELISM, although it may actually use only a subset of these channels. By default, PARALLELISM = 1.

Take the following example; you can set PARALLELISM for DISK backups to 3. If you configure automatic channels of type disk and tape, and set the default device type as disk, then RMAN allocates three disk channels when you run BACKUP DATABASE at the RMAN prompt.

To change the parallelism for a device type to [n], run:

CONFIGURE DEVICE TYPE [DISK | SBT] PARALLELISM [n];

Here are several examples of how to configure PARALLELISM to 3 for sbt and then change it to 2:

CONFIGURE DEVICE TYPE sbt PARALLELISM 3;

CONFIGURE DEVICE TYPE sbt PARALLELISM 2;

Another example that configures parallelism to 2 for automatic disk channel.

CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

NOTE: If you configure [n] manually numbered channels, the PARALLELISM setting can be greater than or less than [n]. For example, you can manually number 10 automatic channels and configure PARALLELISM to 2 or even 12.

DATABASE (and ARCHIVELOG) BACKUP COPIES

Specifies the number of copies of each backup set for DATAFILE (both datafiles and control files) or ARCHIVELOG files on the specified device type, from 1 (default) to 4. If duplexing is specified in the BACKUP command or in a SET BACKUP COPIES command, then the CONFIGURE setting is overridden.

Here is the general syntax:

{ARCHIVELOG| DATAFILE}

BACKUP COPIES FOR DEVICE

TYPE deviceSpecifier

TO integer;

NOTE: Control file autobackups on disk are a special case and are never duplexed. RMAN always writes one and only copy.

Here are several examples of how to set this parameter:

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 2;

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 2;

To clear this parameter, use the following:

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK CLEAR;

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK CLEAR;

MAXSETSIZE

Specifies the maximum size of each backup set created on a channel. By default MAXSETSIZE is set to UNLIMITED, meaning that it is disabled.

Here is the general syntax:

CONFIGURE MAXSETSIZE TO { integer [K|M|G] | UNLIMITED | CLEAR };

TO integer Specifies the maximum set size in bytes, kilobytes (K), megabytes (M), or gigabtyes (G).The default setting is in bytes and is rounded down to kilobtyes.

For example,

if you set MAXSETSIZE to 5000, RMAN sets the maximum set size at 4 kilobytes (that is, 4096 bytes), which is the lower kilobyte boundary of 5000. The minimum value must be greater than or equal to the database block size.

TO UNLIMITED Specifies that there is no size limit for backup sets.

CLEAR Resets the maximum set size to its default value.

The following command will set the maximum size of a backup set to 1.5GB:

RMAN> CONFIGURE MAXSETSIZE TO 1500m;

If you want to set the value backup to its default, use:

RMAN> CONFIGURE MAXSETSIZE CLEAR;

SNAPSHOT CONTROLFILE

A snapshot control file is created by RMAN during the backup process that serves as a point-in-time copy of the database control file. This will ensure that backups being taken is consistent to a given point in time. For example, if you add a datafile or tablespace to a database after the backup has started, (assuming an online backup and before the backup has ended), that datafile and/or tablespace will not be included in the backup. It is sometimes required that the location and name of the snapshot control file be changed from its default.

To set the snapshot control file to a different location (and name) use the following:

RMAN> CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/orabackup/rman/ORA920/scontrolfile.ctl';

EXCLUDE TABLESPACE

Excludes the specified tablespace from BACKUP DATABASE commands. Note that you cannot exclude the SYSTEM tablespace. By default, each tablespace is not excluded, that is, the exclude functionality is disabled.

The exclusion is stored as an attribute of the tablespace, not the individual datafiles, so the exclusion applies to any files that are added to this tablespace in the future.

If you run CONFIGURE ... CLEAR on a tablespace after excluding it, then it returns to the default configuration of "not excluded."You can still back up the configured tablespace by explicitly specifying it in a BACKUP command or by specifying the NOEXCLUDE option on a BACKUP DATABASE command.

The general syntax is:

EXCLUDE FOR TABLESPACE tablespace_name;

In this example, I want to exclude the tablespace named 'READ_ONLY_TBS' from the backup:

RMAN> CONFIGURE EXCLUDE FOR TABLESPACE READ_ONLY_TBS;

Basic Commands in RMAN
1. Start RMAN client
rman (rman executable is in $ORACLE_HOME/bin)
2. Connect to target database (start RMAN client as shown above)
RMAN> connect target sys@TNS_ALIAS_FOR_TARGET
like
RMAN> connect target sys@PROD
To connect to local database use operating system authentication
RMAN> connect target /
3. To exit from RMAN client
use EXIT like
RMAN> exit
4. To view current RMAN configuration
rman (To start RMAN Client)
RMAN> connect target / (to connect to target database)
RMAN> SHOW ALL
5. To backup database and archived redo while system is Up (Hot Backup)
Database should be in archive log mode
rman (To start RMAN Client)
RMAN> connect target / (to connect to target database)
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
6. To make consistent backup while database is not open (Database in No archive log mode)
rman (To start RMAN Client)
RMAN> connect target / (to connect to target database)
RMAN> SHUTDOWN IMMEDIATE (to shutdown database)
RMAN> STARTUP MOUNT;
RMAN> BACKUP DATABASE; (to backup database, no need to backup archived log as database is not open)
RMAN> ALTER DATABASE OPEN; (To Open Database )
7. To make incremental backup
rman (To start RMAN Client)
RMAN> connect target / (to connect to target database)
RMAN> BACKUP INCREMENTAL LEVEL 0 DATABASE; (For incremental level 0 backup)
RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE; (For incremental level 1 backup)
8. To List backup

rman (To start RMAN Client)
RMAN> connect target / (to connect to target database)
RMAN> LIST BACKUP ; (To list backup)

9. To Delete Obsolete backup

rman (To start RMAN Client)
RMAN> connect target / (to connect to target database)
RMAN> DELETE OBSOLETE ; (To DELETE obsolete backup)

Connecting RMAN

Database should be in archive log mode

1.How to connect to RMAN

Bash-3.2$rman target /

rman target sys/mydb /*******(rman target tagetusername/password)*****/

rman

rman> connect target sys/mydb

2.How to create a catalog schema

connect to recovery catalog database

In this case orcl is recovery catalog database. target database is mydb.

conn sys/orc@orcl

(i)create tablespace for catalog schema:

create tablespace rman datafile'/u02/oradata/mydb/rman.dbf' size 1g autoextend on maxsize 5g;

(ii)create catalog schema:

create user rman identified by rman default tablespace rman temporary tablespace temp;

SQL> grant connect, resource, recovery_catalog_owner to rman;

Grant succeeded.

(iii)connect schema with catalog database

conn rman/ramn

rman>catalog rman/rman@orcl

(iv)create recovery catalog with tablespace which you had created earlier

RMAN> create catalog tablespace 'RMAN';

recovery catalog created

HOW TO REGISTER TARGET DATABASE WITH CATALOG

we can register multiple databases in a single catalog schema.

-bash-3.2$ rman target sys/mydb@mydb catalog rman/rman@orcl

RMAN> register database;

RMAN>rc_database;

database registered in recovery catalog

starting full resync of recovery catalog

full resync complete

RMAN COMMANDS

1. To take full database backup

RMAN>backup database;

2. To take full database plus archive log backup

RMAN>backup database plus archivelog;

3. To take full database and archivelog backup once the archivelog backup is done, it will delete archivelogs

RMAN>backup database plus archivelog delete input;

4. To take particular tablespace

RMAN> backup tablespace SYSTEM;

5. To take Multiple tablespaces

RMAN>backup tablespace SYSTEM,TEST;

6. Backup all archivelog files

RMAN>backup archivelog all;

7. Backup particular datafile

RMAN> backup datafile 2;

RMAN> backup datafile '/u01/app/oracle/oradata/mydb/system01.dbf';

8. To see obsolete backups based on the rentention policy

RMAN> report obsolete;

9. To delete obsole backups listed in above command

RMAN> delete obsolete;

10. To see expired backups

RMAN> list expired backup;

11. To delete expired backups

RMAN> delete expired backup;

12. It will not prompt for user answer

RMAN> DELETE NOPROMPT OBSOLETE;

13. To list or see all backups taken by rman database plus archivelogs

RMAN> list backup;

14. To see only database backups

RMAN> list backup of database;

15. To see only archieved backups

RMAN> list backup of archivelog all;

16. To see list of back up of particular tablespace

RMAN> list backup of tablespace system;

17. To see list of back up of particular datafile

RMAN> list backup of datafile 1;

18. If the backup is not available it will show the status columns as 'X'

RMAN> list backup summary;

RMAN>LIST BACKUPSET TAG TAG20140126T080842;

19. CROSS CHECK

It will verify the RMAN repository backup information at physical level (os level), if the backups are available in the backup location it will display as "AVAILABLE" if it is not available then rman will display as"EXPIRED"

RMAN> crosscheck backup;

RMAN> crosscheck backup of datafile 1;

RMAN> crosscheck backup of database;

RMAN> crosscheck backup of archivelog all;

20. VALIDATE: it will check for any logical corrupts in the backups

Prior to 11g, the straight VALIDATE command could only be used to validate backup related files. In Oracle 11g onward, the VALIDATE command can also validate datafiles, tablespaces or the whole database, so you can use it in place of the BACKUP VALIDATE command.

RMAN> validate database or validate backupset 1;

21. It validates any corrupting in the database blocks

RMAN> VALIDATE DATAFILE 1;

RMAN> VALIDATE DATAFILE '/u01/app/oracle/oradata/ORCL/system01.dbf';

22. shutdown/starup db from rman

RMAN> shutdown immediate

RMAN> startup mount;

RMAN> alter database open;

23. Run sql statements through RMAN

RMAN> sql 'alter tablespace TEST offline';

RMAN> sql 'alter tablespace TEST online';

24. Resync catalog means after creating catalog schema ,we get before backup information from controlfile to catalog schema using this command

RMAN> resync catalog;

25. To take backup to a particular location using format

RMAN> backup DEVICE TYPE DISK FORMAT '/u02/rman_system%U.bkp' tablespace SYSTEM;

26. How to run multiple commands using RMAN run block

RMAN> run

2> {

3> backup tablespace "TEST";

4> backup tablespace "SYSTEM";

5> backup datafile 7;

6> }

27. How to configure multiple channels using parallelism:

CONFIGURE DEVICE TYPE DISK PARALLELISM 2;

CONFIGURE CHANNEL 1 DEVICE TYPE DISK FORMAT '/u01/app/oracle/rman_bkup1_%U.bkup';

CONFIGURE CHANNEL 2 DEVICE TYPE DISK FORMAT '/u02/rman_bkup2_%U.bkup';

28. How to allocate multiple channels and release the channels

RMAN> run

2> {

3> allocate channel c1 device type disk;

4> allocate channel c2 device type disk;

5> backup database plus archivelog;

6> release channel c1;

7> release channel c2;

8> }

29. How to run rcv files using rman

vi rmantest.rcv

run

{

allocate channel c1 device type disk;

allocate channel c2 device type disk;

backup database plus archivelog;

release channel c1;

release channel c2;

}

30. To execute rcv files from rman

bash-3.2$ rman target / cmdfile /u01/app/oracle/rmantest.rcv

31. How to create or store scripts in rman:

to create scripts you must should connect to catalog schema

bash-3.2$ rman target / catalog rman/rman@orcl

RMAN> create script test_script

{

allocate channel c1 device type disk;

allocate channel c2 device type disk;

backup database plus archivelog;

release channel c1;

release channel c2;

}2> 3> 4> 5> 6> 7> 8>

created script test_script

32. To execute scripts stored in rman:

RMAN> run

2> {

3> execute script test_script;

4> }

33. To execute rman stored script from rman command prompt:

bash-3.2$ rman target / catalog rman/rman@orcl script test_script

34. How to see stored scripts in rman:

RMAN> list script names;

35. How to see the content of the script:

RMAN> print script test_script;

printing stored script: test_script

{

allocate channel c1 device type disk;

allocate channel c2 device type disk;

backup database plus archivelog;

release channel c1;

release channel c2;

}

36. How to take incremental:

RMAN> backup incremental level 0 database; level 0 is full database incremental backup

RMAN> backup incremental level 1 database; /*** default backup is differential backup ****

RMAN> backup incremental level 1 cumulative database; /*** to take cumulative db backups ****/

37.Take backup to SBT device:

RUN

{

ALLOCATE CHANNEL c1 DEVICE TYPE sbt MAXOPENFILES 20 RATE 2M MAXPIECESIZE 800M;

BACKUP DATABASE;

}

RMAN Restore vs. Recovery - what's the difference?

RESTORE is used to restore the datafiles from a backup. You can restore up to the time of the backup or a point before using SET UNTIL. You would lose any changes made to the database after the backup by performing only a RESTORE.

RECOVER is used to roll the database forward after a RESTORE by applying the redo logs. In essence, RECOVER = RESTORE + redo logs.

The following example assumes you have lost your control files and datafiles, but still have your redo logs.

$ rman target / nocatalog
RMAN> startup nomount;
RMAN> restore controlfile from '/oback/controlfile';
RMAN> alter database mount;
RMAN> restore database;
RMAN> recover database;
RMAN> alter database open resetlogs;

POINT IN TIME RECOVERY via RMAN

Scenario:

DBA dropped the tablespace TEST which is important for application.

Requirement:

We need to restore the database before the TEST tablespace was dropped.

Solution:

SQL> drop tablespace test including contents and datafiles;

Tablespace dropped.

DBA realized the mistake;

He will refer alert log for the exact timing when tablespace was dropped.

Alert log

Sun Feb 4 10:59:43 2007

drop tablespace test including contents and datafiles

Sun Feb 4 10:59:47 2007

Completed: drop tablespace test including contents and datafiles

SQL> shutdown abort

ORACLE instance shut down.

rman target / catalog rman/cat@risl64

Recovery Manager: Release 10.2.0.2.0 - Production on Sun Feb 4 11:02:48 2007

Connected to target database (not started)

Connected to recovery catalog database

RMAN> run

{

startup nomount

set until time "to_date ('04-02-07 10:58:00', 'DD-MM-YY HH24:MI:SS')";

restore controlfile;

alter database mount;

restore database;

recover database;

alter database open resetlogs;

}

SQL> select name from v$tablespace;

NAME

------------------------------

SYSTEM

UNDOTBS1

SYSAUX

USERS

TEMP

TEST

6 rows selected.

Point in time recovery using RMAN (until a log sequence number)

Recovery Objective

SQL> conn scott/tiger

Connected.

SQL> select count(*) from myobjects;

COUNT(*)

----------

249410

Switch a logfile

SQL> conn / as sysdba

Connected.

SQL> alter system switch logfile;

System altered.

Note the current log sequence number (13)

SQL> archive log list

Database log mode Archive Mode

Automatic archival Enabled

Archive destination /u02/ORACLE/opsdba/arch

Oldest online log sequence 12

Next log sequence to archive 14

Current log sequence 14

Simulate an application failure – WRONG Delete!!

SQL> conn scott/tiger

Connected.

SQL> delete myobjects;

249410 rows deleted.

SQL> commit;

Commit complete.

The developer states that the wrong DML statement was made AFTER 8.15 AM and is positive about the same.

We need to determine the log sequence we need to recover until

SQL> select sequence#,first_change#, to_char(first_time,'HH24:MI:SS') from v$log order by 3

SEQUENCE# FIRST_CHANGE# TO_CHAR(

---------- ------------- --------

13 2760463 07:49:36

14 2761178 08:12:47

15 2766622 08:18:49

Log sequence 14 was first written to at 8:12 AM so we should recover to a log sequence before this – i.e sequence# 13

Shutdown and mount the database

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount;

ORACLE instance started.

Total System Global Area 264241152 bytes

Fixed Size 2070416 bytes

Variable Size 163580016 bytes

Database Buffers 92274688 bytes

Redo Buffers 6316032 bytes

Database mounted.

RMAN> run {

2> set until sequence=14; /********add one to the sequence number we have to recover until***/

3> restore database;

4> recover database;

5> }

RMAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs

Confirm that the recovery has worked

opsdba:/opt/tivoli/tsm/client/oracle/bin64>sqlplus scott/tiger

SQL*Plus: Release 10.2.0.2.0 - Production on Mon Jan 29 09:43:14 2007

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production

With the Partitioning, Oracle Label Security, OLAP and Data Mining options

SQL> select count(*) from myobjects;

COUNT(*)

----------

249410

INCREMENTAL BAKUPS

Making a full backup every day can consume a lot of time, and get very expensive pretty quickly. In order to minimize potential backup times while also saving money on storage media, backing up incremental changes is the only smart way to protect your data.
Incremental backup starts with the assumption that only a small portion of your data will change on any given day. Typically, a company will only modify less than 5% of their business data on any given day. If you can isolate these changes into a backup, you could theoretically cut storage costs and backup time by 95%.

Incremental backups are generally smaller and faster to create than full database backups. Recovery from an incremental backup is faster than recovery using redo logs alone.

If you specify BACKUP INCREMENTAL, RMAN will create incremental backups of your database. Incremental backups capture on a block-by-block basis changes in your database since a previous incremental backup. The starting point for an incremental backup strategy is a level 0 incremental backup, which backs up all blocks in the database. Level 1 incremental backups, taken at regular intervals, contain only changed blocks since a previous incremental backup. These can be cumulative (including all blocks changed since the most recent level 0 backup) or

differential (including only blocks changed since the most recent incremental backup, whether it is level 0 or level 1).

One of the longest-standing debates within the backup space has to do with deciding which incremental data capture methodology is best: Differential Incremental or Cumulative Incremental?
The first – and most efficient – technique would be to perform a single full backup, and then only copy the data which has changed on a daily basis. This is often called the Differential Incremental approach. The main advantage of this approach is that it keeps the amount of backup storage to an absolute minimum.
Unfortunately, differential incremental can also be a logistical nightmare. If you do a full backup on day one, and your server crashes on day 100, you’ll need to load the first full backup along with the following 100 incremental backup copies.
If any of those 100 incremental backups are corrupted, it could potentially cause major problems with the recovery. Also, this added complexity greatly increases the chance of possible human error during the recovery process.
Differential incremental backups also offer very slow recovery since you have to load a lot of redundant data during the recovery process. For example, you might load several hundred gigabytes worth of temporary files, only to delete them immediately after. Or you might need to load dozens of copies of a frequently accessed file when you only need to recover the most recent version.
There are 2 common ways to get around this problem.
One way is to perform full backups on a regular basis in order to cut down on the number of incremental backups which must be loaded in the event of a data disaster. Today, it’s common for companies to perform a full backup on the first of every month, followed by – at most – 30 daily differential incremental backups.
Another approach would be to consolidate all of the previous daily incremental backups into a single backup storage unit for easy recovery.
This is what the Cumulative Incremental approach tries to do. Every day, a backup is performed which only copies the data that’s changed since the original full backup was performed. If you need to recover your systems, you only need to load 2 sets of backup: the original full backup and the last cumulative incremental backup.
By simplifying backups in this manner, you can greatly speed up recovery while also reducing the potential for human error.
The downside of the cumulative incremental approach is the fact that the required backup storage can grow exponentially.
If your data changes only 10 GB per day, your incremental backups can grow to several terabytes pretty quickly. Because of this, a cumulative incremental backup cycle requires short cycles with frequent full backups in order to keep storage costs low.
Backup administrators have to fight a constant battle in order to minimize storage costs, backup windows, recovery speeds and handling complexity. As data growth continues to accelerate, it quickly becomes apparent that a new approach is needed.
This new approach must have the efficiency and speed of a differential incremental backup, the simplicity of a cumulative differential backup, and it should completely do away with the need for periodic full backups.
Thankfully, such a solution exists.

level 1 incremental backup can be either of the following types:

A differential backup, which backs up all blocks changed after the most recent incremental backup at level 1 or 0
A cumulative backup, which backs up all blocks changed after the most recent incremental backup at level 0

· Incremental backups are differential by default.

Note:

· Cumulative backups are preferable to differential backups when recovery time is more important than disk space, because during recovery each differential backup must be applied in succession. Use cumulative incremental backups instead of differential, if enough disk space is available to store cumulative incremental backups.

· The size of the backup file depends solely upon the number of blocks modified and the incremental backup level.

4.4.1.2 Differential Incremental Backups

In a differential level 1 backup, RMAN backs up all blocks that have changed since the most recent cumulative or differental incremental backup, whether at level 1 or level 0. RMAN determines which level 1 backup occurred most recently and backs up all blocks modified after that backup. If no level 1 is available, RMAN copies all blocks changed since the level 0 backup.

The following command performs a level 1 differential incremental backup of the database:

RMAN> BACKUP INCREMENTAL LEVEL 1 DATABASE;

If no level 0 backup is available, then the behavior depends upon the compatibility mode setting. If compatibility is >=10.0.0, RMAN copies all blocks changed since the file was created, and stores the results as a level 1 backup. In other words, the SCN at the time the incremental backup is taken is the file creation SCN. If compatibility <10.0.0, RMAN generates a level 0 backup of the file contents at the time of the backup, to be consistent with the behavior in previous releases.

Figure 4-1 Differential Incremental Backups (Default)

Description of "Figure 4-1 Differential Incremental Backups (Default)"

In the example shown in Figure 4-1, the following occurs:

Sunday

An incremental level 0 backup backs up all blocks that have ever been in use in this database.

Monday - Saturday

On each day from Monday through Saturday, a differential incremental level 1 backup backs up all blocks that have changed since the most recent incremental backup at level 1 or 0. So, the Monday backup copies blocks changed since Sunday level 0 backup, the Tuesday backup copies blocks changed since the Monday level 1 backup, and so forth.

The cycle is repeated for the next week.

4.4.1.3 Cumulative Incremental Backups

In a cumulative level 1 backup, RMAN backs up all the blocks used since the most recent level 0 incremental backup. Cumulative incremental backups reduce the work needed for a restore by ensuring that you only need one incremental backup from any particular level. Cumulative backups require more space and time than differential backups, however, because they duplicate the work done by previous backups at the same level.

The following command performs a cumulative level 1 incremental backup of the database:

RMAN>BACKUP INCREMENTAL LEVEL 1 CUMULATIVE DATABASE; # blocks changed since level 0

Figure 4-2 Cumulative Incremental Backups

Description of "Figure 4-2 Cumulative Incremental Backups"

In the example shown in Figure 4-2, the following occurs:

Sunday

An incremental level 0 backup backs up all blocks that have ever been in use in this database.

Monday - Saturday

A cumulative incremental level 1 backup copies all blocks changed since the most recent level 0 backup. Because the most recent level 0 backup was created on Sunday, the level 1 backup on each day Monday through Saturday backs up all blocks changed since the Sunday backup.

The cycle is repeated for the next week.

RESET LOGS VS NORESETLOGS

NORESETLOGS

The NORESETLOGS option does not clear the redo log files during startup and the online redo logs to be used for recovery. Only used in scenario where MANUAL RECOVERY is started, CANCEL is used, and then RECOVER DATABASE is started.

RESETLOGS

CAUTION: Never use RESETLOGS unless necessary.

Once RESETLOGS is used then the redo log files cannot be used and any completed transactions in those redo logs are lost!!

Before using the RESETLOGS option take an offline backup of the database.

The RESETLOGS option clears all the online redo logs and modifies all the online data files to indicate no recovery is needed. After resetting the redo logs none of the existing log files or data file backups can be used. In the control file, the log sequence number is modified, which is very important for recovery purposes. The recovery will be applied only to the log files whose sequence number is greater than log sequence number in the control file. One has to be very cautious when using RESETLOGS option. It is important to remember that all datafiles must be online otherwise they will become useless once the database is up

RESETLOGS | NORESETLOGS This clause determines whether Oracle Database resets the current log sequence number to 1, archives any unarchived logs (including the current log), and discards any redo information that was not applied during

recovery, ensuring that it will never be applied.

Oracle Database uses NORESETLOGS automatically except in the following specific situations, which require a setting for this clause:

1. You must specify RESETLOGS:

– After performing incomplete media recovery or media recovery using a backup controlfile

– After a previous OPEN RESETLOGS operation that did not complete

– After a FLASHBACK DATABASE operation

2. If a created controlfile is mounted, then you must specify RESETLOGS if the online logs are lost, or you must specify NORESETLOGS if they are not lost.

Oracle DBA

Saturday, 20 September 2014

RMAN Tutorial

Blog Archive

About Me