Monday , September 25 2017
Home / Oracle DBA / Oracle RMAN Backup Commands

Oracle RMAN Backup Commands

1.Allocate Channel:

To manually allocate a channel, which is a connection between RMAN and a database instance. Each connection initiates an Oracle server session on the target or auxiliary instance this server session performs the work of backing up, restoring or recovering backup sets and copies. Each channel operates on one backup set at a time (for BACKUP, RESTORE, or RECOVER) or one image copy at a time (for COPY). RMAN automatically releases the channel at the end of the job.

Example 1:

This command allocates a tape channel for a whole database backup.

RMAN > RUN
{
ALLOCATE CHANNEL dev1 DEVICE TYPE sbt;
BACKUP DATABASE;
}

Example 2:

When creating multiple copies of a backup set, you can specify the SET BACKUP COPIES command. The following example generates a single backup of the database, and then creates four identical backups of datafile 1 to four file systems;

RMAN > RUN

{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK MAXPIECESIZE 20M; BACKUP DATABASE;
SET BACKUP COPIES = 4;
BACKUP DATAFILE 1 FORMAT ‘/w001/oradata/ORCL/rman/bp1/%U’,
‘/w001/oradata/ORCL/rman/bp2/%U’, ‘/w001/oradata/ORCL/rman/bp3/%U’,
‘/w001/oradata/ORCL/rman/bp4/%U’ ;
}

2.BACKUP

To backup a database, tablespace, datafile (current or copy), control file current or copy), archived log, or backup set. You can back up a target or standby database.

When performing a backup, specify the files that you want to back up. RMAN puts the input files into one or more backup sets, which are RMAN- specific logical structures. The backup set is the smallest unit of a backup. RMAN only records backup set in the repository that complete successfully. Each backup set contains at least one backup piece, which is a physical file containing the backed up data.

  • FULL– Copies all blocks into the backup set, skipping only datafile blocks that have never been used. RMAN makes full backups by default if neither FULL nor INCREMENTAL is specified. The server session does not skip blocks when backing up archived redo logs or control files. A full backup has no effect on subsequent incremental backups, so it is not considered a part of the incremental backup strategy.
  • INCREMENTAL LEVEL = Integer – copies only those data blocks that have changed since the last incremental integer backup, where integer is any integer from 1 to 4. for example, in a level 2 backup RMAN backs up all blocks used since the most recent level 2, level 1, or level 0 backup. This type of incremental backup is also called a differential backup to distinguish it from a cumulative backup.
  • If you specify INCREMENTAL, then in the backupSpec clause you must set one of the following parameter DATAFILE, DATAFILECOPY, TABLESPACE, or DATABASE, RMAN does not support incremental backups of control files, archived redo logs, or backup sets.
  • BACKUPSET- Backs up either ALL backup sets or backup sets specified by primary_key or completion time. Use this parameter in conjunction with the DEVICE TYPE sbt clause to back up all backups on disk to tape. You cannot back up from tape to tape or from tape to disk. Only from disk to disk or disk to tape.
  • DATAFILE – Specifies a list of one or more datafiles.
  • DATAFILECOPY ‘filename’ – Specifies the filenames of one or more datafile image copies.
  • TABLESPACE tablespace_name – Specifies the names of one or more tablespaces. RMAN backs up all datafiles that are currently part of the tablespaces.
  • DATABASE – Specifies datafiles in the database. To include the current control file in the backup set, specify the INCLUDE CURRENTCONTROLFILE clause.
  • CURRENT CONTROLFILE – Specifies the current control file.
  • CHANNEL channel_id – Specifies the case – sensitive name of a channel to use when creating backup sets. Use any name that is meaningful, for example ch1 or dev1.
  • CUMULATIVE – Copies the data blocks used since the most recent backup at level n-1 or lower, where n is an integer from 1 to 4. For example, in a cumulative level 2 backup RMAN backs up all blocks used since the most recent level 1 or level 0 backup.
  • MAXSETSIZE = Integer – Specifies a maximum size for a backup set in bytes (default), kilobytes (K), megabytes (M), and Gigabytes (G). Thus, to limit a backup set to 3MB, specify MAXSETSIZE = 3M.
  • FILEPERSET= Integer – Specifies the maximum number of input files in each backup set. If you set FILESPERSET =n, then RMAN never includes more than n files in a backup set.
  • NOT BACKED UP – Backs up only those files (of the files specified on the command ) that RMAN has not backed up since the specified time. If SINCE TIME is not specified, then only those files that have never been backed up will be backed up. SINCE TIME =’ data_string’
  • Specifies the date after which RMAN should back up files that have no backups. the date_string is either a date in the current NLS_DATE_FORMAT, or a SQL date expression such as ‘SYSDATE-1’.
  • DEVICE TYPE – Allocates automatc channels for the specified device type only.
  • BACKUP DEVICE TYPE DISK DATABASE;
  • INCLUDE CURRENT CONTROLFILE – creates a snapshot of the current control file and places it into each backup set produced by this clause.
  • DELETE INPUT – deletes the input files upon successful creation of the backup set. Specify this option only when backing up archived logs, datafile copies, or backup sets.
  • FORMAT – Specifies a filename to use for the backup piece. If you do not Specify the FORMAT parameter, RMAN stores the backup pieces in a pro-specific directory ($ORACLE_HOME/dbs on UNIX).
%c Specifies the copy number of the backup piece within a set of duplexed backup

pieces.

%d Specifies the name of the database.
%D Specifies the current day of the month from the Gregorian calendar in format DD
%F Combines the DBID, day, month, year, and sequence into a unique and

repeatable generated name.

%M Specifies the month in the Gregorian calendar in format MM.
%n Specifies the name of the database, padded on the right with x characters to a

total length of eight characters.

%p Specifies the piece number within the backup set.
%s Specifies the backup set number.
%t Specifies the backup set time-stamp, which is a 4-byte value derived as the

number of seconds elapsed since a fixed reference time.

%T Specifies the year, month, and day in this format: YYYYMMDD.
%u Specifies an 8-character name constituted by compressed representations of the

backup set number and the time the backup set was create.

%U Specifies convenient shorthand for %u_%p_%c that guarantees uniqueness in

generated backup filenames.

%Y Specifies the year in this format:YYYY.
%% Specifies the ‘%’ character. For example, %%Y translates to the string %Y

Example1:
Backing Up a Database:
This command backs up the database to tape and then backs up the control file that contains the record of the database backup:

BACKUP DATABASE;
BACKP CURRENT CONTROLFILE;

Example2:
Backing Up a Tablespaces and Datafiles: This command uses two backupSpec clauses to backup tablespaces and datafiles and lets RMAN perform automatic parallelization of the backup:

 RUN
{
ALLOCATE CHANNEL dev1 DEVICE TYPE DISK FORMAT ‘/w001/backup/rman/bp1/%U’; ALLOCATE CHANNEL dev2 DEVICE TYPE DISK FORMAT ‘/w001/backup/rman/bp2/%U’; BACKUP
(TABLESPACE USERS_TS,DATA0,DATA1,DATA2 FILESPERSET 20) (DATAFILE 12,13,14,15);
}

Example 3:
Backing up Multiple Copies of Archived Logs and Deleting the input.

BACKUP ARCHIVELOG LIKE ‘/u002/oradata/ORCL/arch/ARCH%’ DELETE ALL INPUT;

Example 4:
Backing up Backup Sets to Tapes: In this example, you want to keep recent backup sets on disk and older backup sets on tape. You do not want backup sets to exist on disk and tape simultaneously. Hence, you execute this command to back up older backups created more than two weeks ago to tape and then delete the input backup pieces.

BACKUP DEVICE TYPE sbt BACKUP SET CREATED BEFORE ‘SYSDATE-14’ DELETE INPUT;

Example 5:
Specifying DEVICE TYPE on the BACKUP Command: This example configures DISK as the default device type, and then backs up archived logs to tape:

CONFIGURE DEFAULT DEVICE TYPE TO DISK; BACKUP DEVICE TYPE sbt ARCHIVELOG ALL;

Example 6:
Performing a Cumulative Incremental Backup:This example backs up all blocks changed in the database since the most recent level 0 or level 1 backup.

BACKUP INCREMENTAL LEVEL 2 CUMULATIVE SKIP INACCESSIBLE DATABASE;

Example 7:
Checking for Corruption: This example backs up datafile 3 and specifies that no more than two blocks with corruption should be tolerated:

RUN
{
SET MAXCORRUPT FOR DATAFILE 3 TO 2;
BACKUP CHECK LOGICAL DATAFILE 3;
}

Example 8:
Creating a Long-Term Backup: This example creates a consistent backup of the database that is exempt from the retention policy and tells RMAN to keep the backup for the next year, but not to keep the archived logs necessary to recover it:

SHUTDOWN; STARTUP
MOUNT;
BACKUP DATABASE UNTIL ‘SYSDATE+365’ NOLOGS;

Example 9:
Backing Up Files with No Recent Backups: This example backs up all database files and archived logs that have not been backed up in the last month.

BACKUP DATABASE ARCHIVELOG ALL NOT BACKED UP SINCE TIME ‘SYSDATE-31’;

Example 10:
This example mounts the database, takes a whole database backup, then opens the database. At the RMAN prompt enter:

STARTUP MOUNT;
BACKUP DATABASE;

#Now that the backup is complete, open the database.

ALTER DATABASE OPEN;

Example 11:
To restore the control file to its default location enters the following:

STARTUP NOMOUNT;
RESTORE CONTROLFILE;
ALTER DATABASE MOUNT;

Example 12:
This example restores backup archived redo log files from tape that fail within a range of SCNs:

RESTORE ARCHIVELOG SCN BETWEEN 550 AND 700;

Example 13:
This example backs up only archived log 1372 of thread 1 and then deletes it:

BACKUP ARCHIVELOG SEQUENCE 1372 DELETE INPUT;

Example 14:
This example backs up all archived logs from sequence 288 to sequence 301 on thread 1 and deletes the archived logs after the backup is complete. If the backup fails, the logs are not deleted.

RUN
{
ALLOCATE CHANNEL dev1 DEVICE TPYE sbt; BACKUP ARCHIVELOG
SEQUNECE BETWEEN 288 AND 301 THREAD 1
#Delete original archived redo logs after backup completes
DELETE INPUT;
}

3. CREATE SCRIPT:
To create a script and store it in the recovery catalog for future reference. Stored scripts provide a common repository for frequently executed collections of RMAN commands. Any command that is legal within a RUN command is permitted in the stored script. The script is not executed immediately, run the script with @.
Example 1:
Creating a Script: This example creates a script called b_whole_10 that backs up the database and archived redo logs, then executes it: (If catalog exists)

CREATE script b_whole_10
{
ALLOCATE CHANNEL d1 DEVICE TYPE sbt;
BACKUP INCREMENTAL LEVEL 0 TAG b_whole_10 DATABASE PLUS ARCHIVELOG;
}
RUN { EXECUTE script_b_whole_10};

4. DELETE SCRIPT:
To delete a stored script from the recovery catalog Deleting a script: This example deletes the script b_whole_10:

DELETE SCRIPT b_whole_10;

5. RESTORE:
To restore files from backups or image copies. By default, RMAN restores files to their default location. Typically, you restore when a media failure has damaged a current datafile, controlfile, or archived log or prior to performing a point-in-time recovery. The RESTORE command restores full backups, incremental backups (level 0 only), or copies of datafiles, control files, and archived redo logs. Because the RECOVER command automatically restores archived logs as needed, you should seldom need to restore logs manually.
NOTE: In Oracle 9i, unlike in previous RMAN releases, RMAN by default does not restore a datafile if the file is in the correct place and its header contains the expected data (RMAN does not scan the datafile body for corrupt blocks).
When you perform a restore operation by using a backup control file and use a recovery catalog, RMAN automatically adjusts the control file to reflect the structure of the restored database.

  • CONTROLFILE – Restores the current control file to the default location and automatically replicates it to all CONTROL_FILES locations in the initialization parameter file. The default location is the first filename specified in the CONTROL_FILES parameter.
  • DATABASE – Restores all datafiles in the database except those that are offline or read-only. Unlike BACKUP DATABASE, RESTORE DATABASE does not automatically include the control file – you must issue an additional RESTORE command to perform this operation.
  • DATAFILE datafileSpec – Restores the datafiles specified by filename or absolute datafile number.
  • ABLESPACE ‘tablespace_name’ – Restores all datafiles in the specified tablespaces

Example 1:
Restoring a Tablespace: This example takes a tablespace offline, restores it, and then performs media recovery:

SQL “ALTER TABLESPACE TBS_1 OFFLINE IMMEDIATE”; RESTORE TABLESPACE tbs_1;
RECOVER TABLESPACE tbs_1;
SQL “ALTER TABLESPACE TBS_1 ONLINE;

Example 2:
Restoring the control file: This example restores the control file to its default location, replicates it to all multiplexed locations, and mounts the database:

RUN
{
STARTUP FORCE NOMOUNT; RESTORE CONTROL FILE; ALTER DATABASE MOUNT;
}

Example 3:
Restoring the database using a Backup Control File: This Example restores the control file, replicates it to all control file locations specified in the parameter file, and then mounts the control file in order to restore the database:

STARTUP NOMOUNT; RUN
{
ALLOCATE CHANNEL c1 DEVICE TYPE sbt; RESTORE CONTROL FILE;
ALTER DATABASE MOUNT; RESTORE DATABASE;
}

Example 4:
Restoring Archived Redo Logs to a New Location: This example restores all archived redo logs to the /u002/oradata/ORCL/temp_restore directory:

RUN
{
SET ARCHIVELOG DESTINATION TO ‘/u002/oradata/ORCL/temp_restore’; RESTORE ARCHIVELOG ALL;
}

Example 5:
To restore the control file to its default location enters the following:

STARTUP NOMOUNT;
RESTORE CONTROL FILE;
ALTER DATABASE MOUNT;

RECOVER
To apply redo logs or incremental backups to one or more restored datafiles in order to update them to a specified time. RMAN uses online redo records and restores backup sets of archived redo logs as needed to perform the media recovery. RMAN first looks for the original archived logs or image copies, and if none are available, then it restores backups.

If RMAN has a choice between applying an incremental backup or applying redo, then it always chooses the incremental backup. If overlapping levels of incremental backup are available, then RMAN automatically chooses the one covering the longest period of time. Note that RMAN can apply incremental backups to restored files that were not created as part of an incremental backup.

Note: When RMAN applies incremental backups, it recovers changes to objects created with the NOLOGGING option. Applying archived redo logs to datafiles does not recover these changes.

  • DEVICE TYPE deviceSpecifier – Allocates automatic channels for the specified device type only. For example, if you configure automatic disk and tape channels, and issue RECOVER…DEVICE TYPE DISK, then RMAN allocates only disk channel.
  • DATABASE – Specifies that the entire database is to be recovered. Unless you specify an Until Clause, RMAN performs complete recovery. Until Clause Specifies a noncurrent time, SCN, or log sequence number for termination of the RECOVER command. You must open the database with the RESETLOGS option after incomplete recovery.
  • TABLESPACE – ‘Tablespace_name’ – Specifies tablespace by tablespace name.
  • DATAFILE datafileSpce – Specifies a list of one or more datafiles to recover. Specify datafiles by either filename (by using a quoted string ) or absolute datafile number (by using an integer).

Example 1:
Recovering a Tablespace in an open database: following example takes tablespace tbs_1 offline, uses automatic channels to restore and recover it (deleting the logs that it restored from tape), then brings it back online.

SQL “ALTER TABLESPACE tbs_1 OFFLINE IMMEDIATE”;
RESTORE TABLESPACE tbs_1;
RECOVER TABLESPACE tbs_1 DELETE ARCHIVELOG;
SQL “ALTER TABLESPACE TBS_1 ONLINE;

Example 2:
Recovering Datafiles Restored to new locations: The following example uses the preconfigured disk channel and manually allocates one media management channel to use datafile copies on disk and backups on tape, and restores one of the datafiles in tablespace tbs_1 to a different location:

RUN
{
ALLOCATE CHANNEL dev2 DEVICE TYPE sbt;
SQL “ALTER TABLESPACE tbs_1 OFFLINE IMMEDIATE”; SET NEWNAME FOR DATAFILE ‘/a001/oradata/ORCL/tbs11.f’ TO ‘/a002/oradata/ORCL/tbs11.f’;
RESTORE TABLESPACE tbs_1; SWITCH DATAFILE ALL; RECOVER TABLESPACE tbs_1;
SQL “ALTER TABLESPACE TBS_1 ONLINE;
}

Example 3:
Replicating a Restored Control File: This example restores a control file to a temporary location and then replicates it manually:

STARTUP NOMOUNT; RUN
{
SET UNITL TIME ‘Jun 24 2007 16:32:36’; RESTORE CONTROL FILE TO ‘/tmp/cf.tmp’;
REPLACE CONTROLFILE FROM ‘/tmp/cf.tmp’;
STARTUP FORCE MOUNT;
}

Example 4:
Performing Incomplete Recovery by Using a Backup Control file: Assume that both the database and archived redo log 1234 were lost due to a disk crash. Because you do not have incremental backups, you need to recover the database by using available archived redo logs. You do not need to restore tablespace readonly1because it has not changed since log 1234.

RUN
{
SET UNITL SEQUENCE 1234 THREAD 1; #Recover database until log sequence 1234
RESTORE CONTROL FILE TO ‘/u001/app/oracle/product/10.2.0/dbs/cf1.f’;
#Because you specified a restore destination, manually replicate the control file.
#RMAN replicates automatically when no destination is specified.
REPLACE CONTROLFILE FROM ‘/u001/app/oracle/product/10.2.0/dbs/cf1.f’; ALTER DATABASE MOUNT;
RESTORE DATABASE SKIP TABLESPACE temp1, readonly1; RECOVER DATABASE SKIP FOREVER TABLESPACE temp1; ALTER DATABASE OPEN RESETLOGS;
SQL “DROP TABLESPACE temp1”;
SQL “CREATE TABLESPACE temp1 DATAFILE ‘/u001/app/oracle/product/10.2.0/dbs/temp1.f’
SIZE 10M TEMPORARY”;
}

Example 5:
Performing Incomplete Recovery until a Log sequence number: This example assumes that log sequence 1234 was lost due to a disk crash and the database needs to be recovered by suing available archived redo logs.

RUN
{
SET UNITL SEQUENCE 1234 THREAD 1;
RESTORE CONTROL FILE TO ‘$ORACLE_HOME/dbs/cf1.f’; REPLACE CONTROLFILE FROM ‘$ORACLE_HOME/dbs/cf1.f’; ALTER DATABASE MOUNT;
RESTORE DATABASE;
RECOVER DATABASE;
ALTER DATABASE OPEN RESETLOGS; # recovers through log 1233
SQL “ALTER DATABASE OPEN RESETLOGS”;
}

Example 6:
Performing Incomplete recovery to a Specified SCN: This example recovers the database until a specified SCN:

STARTUP MOUNT; RUN
{
ALLOCATE CHANNEDL ch1 TYPE sbt; RESTORE DATABASE;
RECOVER DATABASE UNTIL SCN 1000; # recovers through SCN 999
SQL “ALTER DATABASE OPEN RESETLOGS”;
}

7.RELEASE CHANNEL:
To release a channel while maintaining the connection to the target database instance. Specify the channel name with the same identifier used in the ALLOCATE CHANNEL command. This command is optional because RMAN automatically releases all channels allocated when the RUN command terminates.
Example 1:
Releasing a channel: This example makes three identical backup sets of datafiles 1 through 4 to tae wth channel ch1, then releases it. RMAN then makes three identical backups of datafiles 5 and 6 to tape with channel ch2 and then releases it:

RUN
{
SET BACKUP COPIES = 3;
ALLOCATE CHANNEL ch1 DEVICE TYPE sbt FILESPERSET = 2 RATE = 1000; ALLOCATE CHANNEL ch2 DEVICE TYPE sbt MAXPIECESIZE = 1000;
BACKUP CHANNEL ch1 DATAFILE 1,2,3,4; RELEASE CHANNEL ch1;
BACKUP DATAFILE 5,6;
}

7. SHOW:
To display the current CONFIGURE command settings. The output of SHOW consists of the CONFIGURE commands used to set the configuration. RMAN default configurations are suffixed with #default.
Examples:
Showing channel configurations: This example shows commands relevant for displaying automatic channel configurations

SHOW CHANNEL; SHOW DEVICE TYPE;
SHOW DEFAULT DEVICE TYPE;
SHOW MAXSETSIZE; SHOW ALL;

Determining Datafile Backup Status:

To view the backup status of a datafile, you can use the data dictionary table V$BACKUP. This table lists all online files and gives their backup status.

Note: V$BACKUP is not useful if the controlfile currently in use is restored backup or new controlfile created since the media failure occurred. A restored or re-created controlfile does not contain the information oracle needs to fill the backup status of the older version of the file, not the most current version. Thus, this view might contain misleading information on restored files.

In the STATUS column, “INACTIVE” indicates that the file is not currently being backed. “ACTIVE” indicates that the file is marked as currently being backed.

 Propagating Information from the Control file:

The size of the target database’s controlfile will grow, depending on the number of

  • Backups performed
  • Active logs created
  • Days (minimum number) this information is stored in the control file

You can specify the minimum number of days this information is kept in the control file using the parameter CONTROL_FILE_RECORD_KEEP_TIME.   Entries older than the number of days, are candidates for overwrites, by newer information. The larger the CONTROL_FILE_RECORD_KEEP_TIME setting is, the larger the control file will be. At a minimum, you should resynchronize your recovery catalog at intervals less than the CONTROL_FILE_RECORD_KEEP_TIME setting, because after this number of days, the information in the control file will be overwritten with the most recently created information, if you have not resynchronized, and information has been overwritten, this information can not be propagated to the recovery catalog.

Note: The maximum size of the control file is port specific. See your operating system- specific oracle documentation.

Read more –>

Improving Incremental Backup Performance: Change Tracking

Backup & Recovery Scenario
Oracle database Hot Backups

Comments

comments

Check Also

How to switch on primary database to physical standby database

After configuration data guard then data is switching  into primary database  to standby database : …

Leave a Reply

Your email address will not be published. Required fields are marked *