(Transfer) Oracle RMAN Quick Start Guide

zhaozj2021-02-16  102

Foreword

This article mainly introduces the common method of RMAN, which contains some of its own experience, and the experiment inside is basically testing in Win 2K and Oracle 8.1.6 (because this environment is easier to implement).

This article draws on the related articles on some masters on the Internet, I hope that the heroes don't want to see it, thank you here.

This article is mainly written during the Beijing business trip. After returning home, it is more rushing, and the time is more rushing. At the same time, because of the limited space, some technical details cannot be covered by one, and only hope to help the newcomer to get the role of entrance, want to be truly skilled Mastering RMAN, you must have a long-term practice to hone, especially in engineering to get valuable troubleshooting experience.

What is RMAN?

RMAN can be used to back up and restore database files, archive logs, and control files. It can also be used to perform complete or incomplete database recovery.

Note: RMAN cannot be used to back up initialization parameter files and password files.

The RMAN launches the Oracle server process on the database to back up or restore. Backup, restore, recovery is driven by these processes.

RMAN can be controlled by OEM's Backup Manager GUI, but does not focus on this article.

2. Terminology Professional Vocabulary Explanation

2.1. Backup Sets Backup Collection

The backup collection has the following features: including one or more data files or archive logs Save all of the complete backup tablets in Oracle's proprietary format constitutes a full backup or incremental backup

2.2. Backup PIECES Backup

A backup set consists of several backup sheets. Each backup piece is a separate output file. The size of a backup piece is limited; if there is no size limit, the backup set is constructed only by one backup sheet. The size of the backup sheet cannot be greater than the maximum value of the file support supported by the file system.

2.3. Image Copies Mirror Backup

Mirror backup is a backend of a separate file (data file, archive log, control file). It is very similar to operating system-level file backup. It is not a backup set or a backup film, nor is it compressed.

2.4. Full Backup Sets Full Backup Collection

Full backup is a backup of a data block used in one or more data files. The data blocks that have not been used are not backed up, that is, Oracle is compressed by backup collection.

2.5. Incremental Backup Sets Increment Backup Collection

Incremental backups refer to data blocks that have been modified since the last level of data files or lower levels of backups. The same is the same as fully backed up, and the incremental backup is also compressed.

2.6. File Multiplexing

Different data blocks of multiple data files can be mixed backup in a backup set.

2.7. Recovery Catalog RESYNCING Restore Directory Synchronization

When using the recovery manager to execute the backup, copy, restore, the restore directory automatically updates, but the log and archive log information do not automatically log in to the recovery directory. Directory synchronization needs to be done. Synchronize with the resync catalog command.

RMAN> RMAN-03022: Run command: resync rman-03023: Execute the command: resync rman-08002: Run all restored directory Resync RMAN-08004: Complete all RESYNC

3. Restore the directory

3.1. Restore the concept of the directory

The recovery directory is used by RMAN and maintained a repository for placing backup information. RMAN uses the information recorded in the recovery directory to determine how to perform the required backup recovery operations.

The recovery directory can exist in the plan of the Oracle database.

Although the recovery directory can be used to back up multiple databases, it is recommended to create a separate database for the recovery directory database.

Recovering Directory Database cannot use the recovery directory backup itself.

3.2. Establish a recovery directory

The first step is to create a table space used in the directory database: SQL> CREATE TABLESPACE RMAN_TS DATAFILE "D: /Oracle/oradata/rman_ts.dbf" size 20m; table space has been created.

Step 2, create RMAN users and authorize in the directory database:

SQL> CREATE User Rman Identified by Rman Default TableSpace Rman_ts Temporary TableSpace Temp Quota Unlimited On Rman_TS; User has created. SQL> Grant Recovery_catalog_owner to rman; authorized success. SQL> Grant Connect, Resource To Rman; Authorized Success.

Step 3, create a recovery directory in the directory database

C: /> RMAN Catalog RMAN / RMAN Recovery Manager: Version 8.1.0.0 - Production RMAN-06008: Connect to Recovery Directory Database RMAN-06428: Unset Recovery Directory RMAN> Create Catalog TableSpace Rman_ts; RMAN-06431: Recovery Directory has been created

Note: Although the use of RMAN does not necessarily restore the directory, it is recommended. Because most of the information recorded in the recovery directory can be recorded by control files, RMAN uses this information when restoring the database. Do not use the recovery directory will be restricted to the backup recovery operation.

3.3. Use the advantages of the recovery directory

Can store scripts;

Remember a long-term backup recovery operation;

4. Start RMAN

RMAN is the interactive command line processing interface or can be run from the Enterprise Manager.

In order to use the following example, first check the environment in line with:

the target database is called "his" and has the same TNS alias user rman has been granted "recovery_catalog_owner" connecting user privileges target database is internal account, or other SYSDBA types account connection the recovery catalog database is called "rman" and has The Same TNS Alias ​​The Schema Containing The Recovery Catalog IS "RMAN" (Same Password)

Before using RMAN, set NLS_DATE_FORMAT and NLS_LANG environment variables, and many RMAN List commands are related to date-time, which is also important in users who wish to perform time-based recovery work.

The following example is a demonstration of environment variables:

NLS_LANG = Simplified Chinese_China.zHS16GBKNLS_DATE_FORMAT = DD-MON-YYYY HH24: MI: SS

To ensure that the RMAN can be connected to the recovery directory, the recovery directory database must be opened, the target database is at least started (unmount), otherwise the RMAN returns an error, and the target database must be placed in archiving mode.

4.1. Use the RMAN without recovery directory

Set the Oracle_SID of the target database, execute:

% RMAN Nocatalog Rman> Connect Target Rman> Connect Target InterNal / @ His4.2. Using RMAN with Recovery Directory

% RMAN RMAN_TS RMAN / RMAN @ Rman Rman> Connect Target% RMAN RMAN_TS RMAN / RMAN @ Rman Target Internal / @HIS

4.3. Using RMAN

Once connected to the target database, you can perform the specified RMAN command by an interactive interface or the pre-stored script, the following is an example of using the RMAN interactive interface:

RMAN> RMAN-03022: Run command: resync rman-03023: Execute the command: resync rman-08002: Run all restored directory Resync RMAN-08004: Complete all RESYNC

Example of using a script:

RMAN> EXECUTE Script Alloc_1_disk;

Create or replace the stored script:

Rman> replace script alloc_1_disk {2> Allocate Channel D1 Type Disk; 3>}

5. Register or log out the target database

5.1. Registration Target Database

Database status:

Restore directory status: Open

Target Database: Load or open

The target database must be registered in the recovery directory before using RMAN for the first time:

The first step, start the recovery manager, and connect the target database:

C: /> Rman Target InterNal / Oracle @ His Catalog RMAN / RMAN @ RMAN Recovery Manager: Version 8.1.0.0 - Production RMAN-06005: Connect to Target Database: His (DBID = 3021445076) RMAN-06008: Connect to Restore directory database

Step 2, registration database:

RMAN> Register Database; RMAN-03022: Compiling Command: Register RMAN-03023: User command: register rman-08006: Register in the recovery directory database RMAN-03023: Is executing command: Full Resync rman-08002: startup All recovery of the content RMAN-08004: Complete all RESYNC

5.2. Log out of the target database

RMAN provides a logout tool called a DBMS_RCVCAT Toolkit. Note that once the target database is logged out, you cannot recover the database using the backup set contained in the recovery directory.

In order to log out of the database, you need to obtain the identification code (DB_ID) of the database (DB_ID) (DB_KEY). The DB_ID will be obtained when connecting the target database.

C: /> Rman Target InterNal / Oracle @ His Catalog RMAN / RMAN @ RMAN Recovery Manager: Version 8.1.0.0 - Production RMAN-06005: Connect to Target Database: His (DBID = 3021445076) RMAN-06008: Connect to Restore the directory database where DBID = 3021445076 uses DBID = 3021445076 to query the database key value code, connect to the target database, query DB table: SQL> SELECT * from DB; db_key db_id curr_dbinc_key ---------- --- -------------------- 1 3021445076 2

Get db_key = 1, this, the target database DB_KEY = 1, DBID = 3021445076, use two values ​​to use the DBMS_RCVCAT toolkit package to log out of the database:

SQL> Execute DBMS_RCVCAT.UNREGOSTERDATABASE (1,3021445076); PL / SQL process has been successfully completed.

At this point, the logout database operation is completed.

6. Operation Existing backup

6.1. Add to Directory Database

Database status:

Recovery Directory: Open

Target Database: Load or open

If there is a backup data created by the 8.x version to register to the target database, you can use the following manual to add to the recovery directory.

Rman> Catalog DataFileCopy "/ Oracle / .... /system01.dbf";

Use the following command to display the files contained in the recovery directory

Rman> List copy of database;

6.2. Remove from the directory database

Step 1: View Backup Information:

Rman> List backup; RMAN-03022: Compiling commands: List

Backup set list

Keyword Recid tag LV collection tag collection count completion time -------------------------- ---------- ---------- ---------------------- 1104 2 5013644455 0 501364446 2 06- August -03

Backup segment list

Keyword PC # CP # status Complete time delivery ----------------------- ------------------------------ 1106 1 1 Available 06-August -03 D: / Oracle / ORA81 / DATABASE / 02EU4DMU_1_1

Data files include lists

File Name LV Type Check Point SCN Check Point Time ---- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- - - - - - ----------------------- 3 D: /oacle/oradata/his/Users01.dbf 0 Full 160052 06- August -03

The keyword of the backup set is 1104.

Step 2: Define the delete channel:

Run; rman-03022: ALLOCATE RMAN-03023: ALOCATE RMAN-03023: ALLOCATE RMAN-08030: Assign channel: delete rman-08500: channel delete: SID = 19 DevType = Disk Three steps: Delete Backupset backup set

RMAN> Change backupset 1104 delete; RMAN-03022: Change Run: Change RMAN-08073: Removed Backup Segment RMAN-08517: Backup Segment Handle = D: / Oracle / ORA81 / DATABASE / 02EU4DMU_1_1 Recid = 2 stamp = 50 1364447 RMAN -03023: Execute command: Partial resync rman-08003: Starting part Recovery Directory Resync RMAN-08005: Complete section RESYNC

Note: Some execute the command to delete the backup set, backup tablet, or maintenance recovery directory, you need to specify channels first, such as:

RMAN> Allocate Channel for Delete Type Disk; Rman> Allocate Channel for Maintenance Type Disk

7. Backup in non-archive mode

Database status:

Recovery Directory: Open

Target Database: Routine Start or Database Load

Recovering the Directory database needs to be opened, the target database must be started (or load). Because the target database is not in archive mode, the database cannot be turned on when the backup recovery operation is made. You cannot put the tablespace in the thermal backup mode, which is equivalent to the file system level. If the non-archive mode, the database is not possible to back up the data file.

7.1. Database full backup

Run> Run {2> # backup the Complete Database to Disk; Allocate Channel Dev1 Type Disk; 4> Backup 5> Full 6> Tag Full_DB_BACKUP 7> Format "/ Oracle / Backups / DB_T% T_S% S_P% P" 8> (Database); 9> Release Channel dev1; 10>}

Row # 2: Indicates that the behavior note line (# 是 注释) 3 & 9: See Section 15 - Channels Channel Definition 5: Full Backup (Default IF Full or Incremental Not Specified) Full Backup Mode (Default Mode) 6: Meaningful String <= 30 Chars) (Backup Set Sign, <= 30 Character) 7: FileName To Use for Backup Pieces, Including Substitution Variables. The file name used by the backup film can contain instead of variables. 8: INDICATES All Files Including ControlFiles Are To Be Backped UP Indicates that all data files include control files

Show the backup set information recorded in the recovery directory by the following command:

Rman> List backupset of database; 7.2. Backup table space

Run> Run {2> Allocate Channel Dev1 Type Disk; 3> Backup 4> TAG TBS_USERS_READ_ONLY 5> Format "/ Oracle / Backups / TBS_USERS_T% T_S% S" 6> (TABLESPACE USERS) 7>}

Use the following command to display the backup information of the table in the recovery directory:

Rman> List backupset of TableSpace User;

Suppose the userS table space is set to the Read Only table space after the backup, and the latter all-round backup may not be backed up, in order to achieve this, "Skip Readonly" can be specified in the later backup.

Note that the target database does not need to be turned on, as long as the load is available, because the information of the table space is stored in the control file.

7.3. Backup separate data file

RMAN> Run {2> Allocate Channel Dev1 Type "SBT_TAPE"; 3> Backup 4> Format "% D_% u" 5> (DataFile "/oracle/dbs/sysbigdb.dbf"); 6> Release Channel DEV1; 7> }

Row # 2: Assign a tape drive using MML (Media Manager Layer), you must specify type SBT_TAPE;

Note that because no identifier is specified, the identification is empty;

Use the following command to display the tablespace in the restore directory:

Rman> List backupset of datafile 1;

7.4. Backup data file

RMAN> Run {2> Allocate Channel Dev1 Type "SBT_TAPE"; 3> Copy DataFile "/oracle/dbs/temp.dbf" to "/oracle/backups/temp.dbf"; 4> Release Channel DEV1; 5>}

Use the following command to display the file copy in the recovery directory:

Rman> List copy of datafile "/oracle/dbs/temp.dbf";

Copying data files and backup data files are different, and the data file copy is a mirror of the file. The backup of the file produces a backup set.

7.5. Backup Control File

RMAN> Run {2> Allocate Channel Dev1 Type "SBT_TAPE"; 3> Backup 4> Format "CF_T% T_S% S_P% P" 5> TAG CF_MONDAY_NIGHT 6> (Current ControlFile); 7> Release Channel DEV1; 8>}

Note: The database fully backed up will automatically back up the control file.

8. Backup in archiving mode

Database status:

Recovery Directory: Open

Target Database: Routing, Database Loading or Open

The command used by the backup operation is basically the same as the non-archive mode.

8.1. Backing Up ArchiveD logs Backup Archive Log

The following script backup archive log:

RMAN> Run {2> Allocate Channel Dev1 Type Disk; 3> Backup 4> Format "/ Oracle / Backups / Log_T% T_S% S_P% P" 5> (ArchiveLog All) 6> Release Channel DEV1; 7>} The following script Archive log from # 90 to 100:

Run> Run {2> Allocate Channel Dev1 Type Disk; 3> Backup 4> Format "/ Oracle / Backups / LOG_T% T_S% S_P% P" 5> (ArchiveLog from logseq = 90 Until logseq = 100 thread 1); 6> Release channel dev1; 7>}

The following script backs up the archive log generated within 24 hours, and the archive log is automatically deleted after the backup is complete. If the backup fails, the archive log will not be deleted.

RMAN> Run {2> Allocate Channel Dev1 Type Disk; 3> Backup 4> Format "/ Oracle / Backups / Log_T% T_S% S_P% P" 5> (ArchiveLog from time "sysdate-1" all delete input); 6> Release channel dev1; 7>}

Use the following command to display the archive log in the recovery directory:

Rman> List Backupset of ArchiveLog All;

Note: RMAN will back up the specified log after RMAN find the archive log. If the log cannot be found, it will not return an error message.

8.2. Backing Up The Online logs Backup Online Log

Online logs cannot be backed up with RMAN, and they must be archived first.

In order to achieve this, the following SQL statement must be performed in the RMAN:

Run> Run {2> Allocate Channel Dev1 Type Disk; 3> SQL "ALTER SYSTEM Archive Log Current"; 4> Backup 5> Format "/ Oracle / Backups / Log_T% T_S% S_P% P" 6> (ArchiveLog from Time " Sysdate-1 "All delete Input); 7> Release Channel dev1; 8>}

The above script can be executed after performing a complete online database backup, make sure all redo logs can restore the database to a consistent state.

Note: You can not identify the archive log backup set.

9. Incremental backup

N-level incremental backup backups All changed data blocks since the nearest N-level or smaller level. Incremental backups are divided into two, one is a cumulative incremental backup, one is a non-accumulated incremental backup.

Cumulative incremental backups include all modified data blocks since the last level of backups.

Non-accumulated incremental backups include data blocks that have been changed since the previous or lower level.

9.1. LEVEL 0 - Foundation of incremental backup strategy

Level 0 is the basis for incremental backup strategies - The Basis of The Incremental Backup Strategy

Run> Run {2> Allocate Channel Dev1 Type Disk; 3> Backup 4> Incremental Level 0 5> FilesPerset 4 6> Format "/ Oracle / Backups / Sunday_level0_% T" 7> (Database); 8> Release Channel DEV1; 9 >} Line # 4: 0 Backup 5: Define the maximum number of files for each Backupset

Use the list statement to view the list display of the database backup set, "Type" will display "0", "0".

9.2. Case using incremental backup

A typical incremental backup case: Sunday night - Level 0 Backup Performed Monday night - Level 2 Backup Performed Tuesday - Level 2 Backup Performed Wednesday night - Level 1 Backup Performed Thursday - Level 2 Backup Performed Friday night - Level 2 Backup Performed Saturday Night - Level 2 Backup Performed Sunday Night - Level 0 Backup Performed

10. Restore

The recovery case is as follows:

10.1. Database Open, the DataFile deleted database is turned on, the file is deleted

Data files are deleted when the database is turned on. There are two ways to recover the open database: restore data files or tablespaces. The following two instances show this method:

(a) DataFile Recovery data file recovery

Run> Run {2> Allocate Channel Dev1 Type Disk; 3> SQL "ALTER TABLESPACE USERS OFFLINE IMMEDIT"; 4> Restore DataFile 4; 5> Recover DataFile 4; 6> SQL "Alter TableSpace Uses Online"; 7> Release Channel dev1 ; 8>}

(b) TABLESPACE Recovery Table Space Recovery

RMAN> run {2> allocate channel dev1 type disk; 3> sql "alter tablespace users offline immediate"; 4> restore tablespace users; 5> recover tablespace users; 6> sql "alter tablespace users online"; 7> release channel dev1 ; 8>}

Note: If the system tablespace file is restored, the database must be turned off because the system table space cannot be offline.

10.2. Complete Restore (Lost Online Redo) And Rollforward - Database Closed Complete Restore (Lost Journal) and the front roll - Database Off

Rman> Run {2> Allocate Channel Dev1 Type Disk; 3> Set Until LogSeq = 105 Thread = 1; 4> Restore ControlFile To "/ Oracle/dbs/ctrltargdb.ctl"; 5> Replicate ControlFile from "/ Oracle / DBS / CtrlTARGDB.CTL "; 6> RESTORE DATABASE; 7> SQL" Alter Database Mount "; 8> Recover Database; 9> SQL" ALTER DATABASE OPEN RESETLOGS "; 10> Release Channel DEV1; 11>} Notes:" SET UNTIL "command Indicate the recovered log file. This is important when the data file is recovered, otherwise the RMAN will try to recover the most recent data file, which may be before the specified log. "Replicate ControlFile" copy the restored control file to the control file specified by the init.ora.

If the database is opened using with ResetLogs, you need to use the reset database command to register the changed database. After using the resetlogs command to open the database, it is highly recommended to make a full database backup.

10.3. Restoring the subset of data files, fully recovery

Run> Run {2> Allocate Channel Dev1 Type Disk; 3> SQL "Alter Database Mount"; 4> Restore DataFile 2; 5> Restore DataFile 3; 6> Restore ArchiveLog All; 7> Recover Database; 8> SQL "ALTER DATABASE Open "; 9> Release channel dev1; 10>}

11. Script

Create or replace scripts:

RMAN> create script alloc_disk {2> # Allocates one disk 3> allocate channel dev1 type disk; 4> setlimit channel dev1 kbytes 2097150 maxopenfiles 32 readrate 200; 5>} RMAN> replace script rel_disk {2> # releases disk 3> release channel DEV1; 5>} rman> replace script backup_db_full {2> # Performs a completion backup 3> Execute script alloc_disk; 4> backup 5> .....

6> EXECUTE Script rel_disk; 7>}

The first two scripts are used to assign and reclaim channels, respectively.

Alloc_disk scripts also additionally specify the maximum megabytes of the backup film, the maximum number of input files that can be opened at the same time, and the maximum number of data buffers per input per input file when backing up.

The third script calls the two scripts previously stored for database backups.

Run the demonstration of the store script:

RMAN> Run {2> Execute script backup_db_full; 3>}

Note: The stored script must be called in the {.... execute