How to realize RMAN remote backup and recovery by setting up recovery catalog 03/31 Update SLTechnology News&Howtos

How to realize RMAN remote backup and recovery by setting up recovery catalog

2025-03-31 Update From: SLTechnology News&Howtos shulou NAV: SLTechnology News&Howtos > Database >

Shulou(Shulou.com)05/31 Report--

This article is about how to build a recovery directory to achieve RMAN remote backup and recovery. The editor thinks it is very practical, so share it with you as a reference and follow the editor to have a look.

First, why do you need to restore the directory

The repository for RMAN backups always exists in the control file of the RMAN target database, so there are two problems:

1. RMAN relies too much on the target database control file. If all copies of the control file are corrupted, the repository will be lost. In this case, even if the backup is intact, the RMAN cannot use the backup because the metadata information of the RMAN backup is lost, so RMAN cannot know they exist.

2. The repository located in the control file of the target database can only save and restore the data specified by the control_file_record_keep_time parameter, which is retained for 7 days by default.

Therefore, it is necessary to restore the catalog, mainly reflected in the following points:

1. If the recovery directory is adopted, the backup of RMAN no longer depends on the target database control files. In this way, even if all copies of the control file are corrupted, the backup can be easily used by restoring the directory. It is better to store the recovery catalog database on a different machine than the target database.

2. The recovery directory can store RMAN scripts. If there is no recovery directory, the script must be stored as an operating system file on the machine running the RMAN executable.

3. The recovery directory can store the metadata of multiple databases and serve as a centralized repository for backup and recovery information of multiple databases. And a directory can be used for databases on any platform, that is, its connection process is cross-platform.

4. Using the recovery directory can also simplify some operations. For example, the recovery process does not need to be in MOUNT mode for the target database. When connecting to the recovery directory, RMAN can automatically locate the backup of SPFILE and control files and restore them, simplifying the boot process of restoring the seriously damaged database.

5. There is no limit to the length of time limit for the recovery directory to save metadata. The length of time that the repository based on the control file retains the data is completely determined by the instance parameter control_file_record_keep_time.

Recovery catalog databases are usually not particularly large or busy databases, do not have high resource requirements, but can significantly enhance RMAN capabilities.

The RMAN executable can connect to up to three database instances at the same time:

1. A target database to which backup or restore and restore operations will be applied

2. A recovery catalog database that stores metadata that describes the target database and all available backups

3. A secondary database created using a backup of the target database.

Second, using the recovery catalog database to realize remote backup

Sometimes we want to save RMAN backups in different places for better disaster recovery. Here's how to implement it in a Windows environment.

1. Modify the local database Oracle backend service

Modify the login method of the Oracle snooping service and database service to log in with a determined account instead of the default local system account.

Restart the two services after modification.

2. Create a service name to access the local target database on a remote machine to ensure a normal connection.

Mes =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP) (HOST = vm-ora11g-1) (PORT = 1521))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = mes)

)

3. Set up the recovery catalog database on the remote machine, which is specially used for the metadata management of RMAN backup. It is not necessary to create a recovery directory, but in order to better remote disaster recovery and prevent the loss of all control files caused by the destruction of the local machine, it is difficult to use even if there is a RMAN backup.

Creating a recovery directory on a remote machine is divided into the following steps:

1) create a database to use as the recovery directory. For example, create a database named catdb.

2) create a tablespace to store directory objects

SQL > create tablespace rmancat datafile'd:\ oradata\ catdb\ rmancat01.dbf' size 200m

3) create a user who uses the storage directory object tablespace by default

SQL > create user rman identified by rman default tablespace rmancat quota unlimited on rmancat

4) Grant the user the recovery_catalog_owner role, which includes the necessary object permissions

SQL > grant recovery_catalog_owner to rman

5) use RMAN to connect to the catalog database and use the create catalog command to create a directory

C:\ > rman catalog rman/rman@catdb

RMAN > create catalog

6) use RMAN to connect to the target database as SYS

RMAN > connect target sys/mesHz2@mes

7) register the target database in the directory to copy the relevant information from the control file of the target database to the directory

RMAN > register database

8) connect to both the directory and the target database for backup and restore operations

C:\ > rman target sys/mesHz2@mes catalog rman/rman

This command requires the RMAN version relationship of the environment in which the target and catalog databases reside, that is, the version of the RMAN executable must be the same as the version of the target database, and the RMAN version that creates the recovery directory must be equal to or higher than the version of any database registered in it.

4. Create a remote backup script

After a successful RMAN connection, you can create a commonly used backup script, which is automatically saved in the recovery catalog database.

In order to achieve perfect backup effect, first modify the RMAN backup parameters of the target database and turn on automatic backup of control files and SPFILE files.

RMAN > configure controlfile autobackup on

RMAN > configure controlfile autobackup format for device type disk to'\\ vm-catdb\ e\ rman_bak\ mes\ control_bak\% F'

The following is a parallel multi-channel backup script that performs an incremental level 0 backup, automatically checks and cleans up the backup set, manually specifies the backup location to the path of the network machine vm-catdb, and the control files and SPFILE files will be backed up automatically according to configuration

Create global script bak_0 {

Sql 'alter system archive log current'

Crosscheck backup

Delete noprompt expired backup

Crosscheck archivelog all

Delete noprompt expired archivelog all

Allocate channel c1 type disk

Allocate channel c2 type disk

Backup incremental level 0 as compressed backupset

Database

Format'\ vm-catdb\ e\ rman_bak\ mes\ dcats% dudes% upright% cations% T'

Backup as compressed backupset

Archivelog all delete all input

Format'\ vm-catdb\ e\ rman_bak\ mes\ asides% dudes% ubiquitous% cations% T'

Delete noprompt obsolete

Release channel c1

Release channel c2

}

The following is the script for the corresponding incremental level 1 backup

Create global script bak_1 {

Sql 'alter system archive log current'

Crosscheck backup

Delete noprompt expired backup

Crosscheck archivelog all

Delete noprompt expired archivelog all

Allocate channel c1 type disk

Allocate channel c2 type disk

Backup incremental level 1 as compressed backupset

Database

Format'\ vm-catdb\ e\ rman_bak\ mes\ dcats% dudes% upright% cations% T'

Backup as compressed backupset

Archivelog all delete all input

Format'\ vm-catdb\ e\ rman_bak\ mes\ asides% dudes% ubiquitous% cations% T'

Delete noprompt obsolete

Release channel c1

Release channel c2

}

5. Perform remote backup

Execute a level 0 backup script in the recovery directory to complete a full backup of the target database

RMAN > run {execute script bak_0;}

When backing up again, you can execute a level 1 backup script to complete the incremental backup of the target database.

RMAN > run {execute script bak_1;}

Manage the recovery catalog

The catalog database can also be the same database as the target database. In this case, you should create a recovery catalog tablespace rmancat in the target database to hold the RMAN recovery metadata, and point the default tablespace of the user rman who manages the recovery catalog to that tablespace. Then connect to the catalog database with that user, and then connect to the target database, because they are the same library, and then create the directory

Create catalog tablespace rmancat

The catalog database, like any production database, should run in archive log mode and make full backups on a regular basis. But don't just use RMAN to back up and restore the catalog database, because if the catalog database is corrupted, any attempt to restore it will have a recursive problem: if the control file or tablespace containing the catalog information is corrupted, any operation to open the database and connect to it as a directory will fail. Therefore, it is necessary to protect the catalog database in other ways, such as user-managed backups.

If for some reason you are not connected to the directory to perform the backup, the directory repository must be resynchronized with the control file repository. The next time RMAN connects to the target database and directory at the same time, a synchronization operation is automatically performed, and the resynchronization operation transfers all information from the control file repository to the directory, thus keeping it up to date. Therefore, data about the backup will be lost only if the period in which resynchronization is not performed exceeds the control_file_record_keep_time.

Resynchronization can also be enforced by using the command to resynchronize directories in the RMAN prompt

RMAN > resync catalog

To connect to both the target database and the recovery directory, you can log out the target database from the recovery directory with the following command

RMAN > unregister database

If you want to delete the created directory, the command is

RMAN > drop catalog

Through some views provided by the catalog database, including rc_database, rc_tablespace, rc_datafile, rc_backup_set, rc_backup_piece, etc., you can query the target database information registered in the recovery directory and backup information, as well as some information to record the running status of RMAN, such as rc_rman_configuration. These views all start with the letter rc

Conn rman/rman

View RMAN configuration information in the recovery directory

Col db_unique_name for a20

Col name for a50

Col value for a70

Select db_unique_name, name, value from rc_rman_configuration

DB_UNIQUE_NAME NAME VALUE

MES CONTROLFILE AUTOBACKUP ON

MES CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO'd:\ rman_bak\ mes\ control_bak\% F'

MES CHANNEL DEVICE TYPE DISK FORMAT'd:\ rman_bak\ mes\% dudes% upright% cations% T'

View the database information in the recovery directory

Select dbid, name from rc_database

DBID NAME

2056489697 MES

View the tablespace information in the recovery catalog

Col name for a20

Select db_name, ts#, name, creation_time, included_in_database_backup, bigfile, temporary from rc_tablespace

DB_NAME TS# NAME CREATION_TIME INC BIG TEM

MES 0 SYSTEM 2013-10-09 18:23:41 YES NO NO

MES 1 SYSAUX 2013-10-09 18:23:44 YES NO NO

MES 2 UNDOTBS1 2013-10-09 19:09:38 YES NO NO

MES 3 TEMP 2018-04-08 13:01:44 NO NO YES

MES 4 USERS 2013-10-09 18:23:53 YES NO NO

MES 11 FDA 2018-04-24 09:17:41 YES NO NO

MES 7 CMES 2018-04-17 21:03:01 YES NO NO

MES 8 RMES 2018-04-17 21:03:34 YES NO NO

MES 9 HMES 2018-04-17 21:04:11 YES NO NO

MES 10 INDX 2018-04-17 21:04:54 YES NO NO

MES 6 EXAMPLE 2018-04-08 13:02:46 YES NO NO

View the data file information in the recovery directory

Col name for a50

Select db_name, ts#, tablespace_name, file#, name, bytes/1024/1024 size_m, creation_time from rc_datafile

DB_NAME TS# TABLESPACE_NAME FILE# NAME SIZE_M CREATION_TIME

MES 0 SYSTEM 1 D:\ ORADATA\ MES\ SYSTEM01.DBF 770 2013-10-09 18:23:41

MES 1 SYSAUX 2 D:\ ORADATA\ MES\ SYSAUX01.DBF 580 2013-10-09 18:23:44

MES 2 UNDOTBS1 3 D:\ ORADATA\ MES\ UNDOTBS01.DBF 2013-10-09 19:09:38

MES 4 USERS 4 D:\ ORADATA\ MES\ USERS01.DBF 5 2013-10-09 18:23:53

MES 6 EXAMPLE 5 D:\ ORADATA\ MES\ EXAMPLE01.DBF 331.25 2018-04-08 13:02:46

MES 7 CMES 6 D:\ ORADATA\ MES\ CMES01.DBF 2018-04-17 21:03:01

MES 8 RMES 7 D:\ ORADATA\ MES\ RMES01.DBF 2018-04-17 21:03:34

MES 9 HMES 8 D:\ ORADATA\ MES\ HMES01.DBF 2018-04-17 21:04:11

MES 10 INDX 9 D:\ ORADATA\ MES\ INDX01.DBF 100 2018-04-17 21:04:54

MES 11 FDA 10 D:\ ORADATA\ MES\ FDA01.DBF 2018-04-24 09:17:41

View backup set information in the recovery directory

Select db_id, bs_key, backup_type, incremental_level, start_time, completion_time, elapsed_seconds, controlfile_included from rc_backup_set

DB_ID BS_KEY B INCREMENTAL_LEVEL START_TIME COMPLETION_TIME ELAPSED_SECONDS CONTROL

2056489697 6303 D 0 2018-05-02 11:59:20 2018-05-02 11:59:32 12 NONE

2056489697 6304 D 0 2018-05-02 11:59:35 2018-05-02 11:59:36 1 BACKUP

2056489697 6305 D 0 2018-05-02 11:59:37 2018-05-02 11:59:37 NONE

2056489697 6306 D 0 2018-05-02 11:59:20 2018-05-02 11:59:39 19 NONE

2056489697 6343 L 2018-05-02 11:59:42 2018-05-02 11:59:42 0 NONE

2056489697 6344 L 2018-05-02 11:59:42 2018-05-02 11:59:43 1 NONE

2056489697 6345 L 2018-05-02 11:59:44 2018-05-02 11:59:44 0 NONE

2056489697 6370 D 2018-05-02 11:59:46 2018-05-02 11:59:48 2 BACKUP

View the backup slice information in the recovery directory

Col handle for a70

Select db_id, bp_key, bs_key, backup_type, incremental_level, handle, tag, start_time, completion_time, elapsed_seconds, bytes, compressed from rc_backup_piece

DB_ID BP_KEY BS_KEY B INCREMENTAL_LEVEL HANDLE TAG START_TIME COMPLETION_TIME ELAPSED_SECONDS BYTES COM

2056489697 6307 6303 D 0\\ VM-CATDB\ E\ RMAN_BAK\ MES\ D_MES_6FT1SM0O_1_20180502 TAG20180502T115920 2018-05-02 11:59:22 2018-05-02 11:59:31 9 105791488 YES

2056489697 6308 6304 D 0\\ VM-CATDB\ E\ RMAN_BAK\ MES\ D_MES_6HT1SM17_1_20180502 TAG20180502T115920 6308-05-02 11:59:36 2018-05-02 11:59:36 0 1130496 YES

2056489697 6309 6305 D 0\\ VM-CATDB\ E\ RMAN_BAK\ MES\ D_MES_6IT1SM19_1_20180502 TAG20180502T115920 6309-05-02 11:59:37 2018-05-02 11:59:37 081920 YES

2056489697 6352 6344 L\\ VM-CATDB\ E\ RMAN_BAK\ MES\ A_MES_6KT1SM1E_1_20180502 TAG20180502T115942 2018-05-02 11:59:42 6352-05-02 11:59:42 0 1102848 YES

2056489697 6310 6306 D 0\\ VM-CATDB\ E\ RMAN_BAK\ MES\ D_MES_6GT1SM0O_1_20180502 TAG20180502T115920 2018-05-02 11:59:22 2018-05-02 11:59:39 17 211460096 YES

2056489697 6351 6343 L\\ VM-CATDB\ E\ RMAN_BAK\ MES\ A_MES_6JT1SM1E_1_20180502 TAG20180502T115942 2018-05-02 11:59:42 6351-05-02 11:59:42 0376832 YES

2056489697 6353 6345 L\\ VM-CATDB\ E\ RMAN_BAK\ MES\ A_MES_6LT1SM1G_1_20180502 TAG20180502T115942 2018-05-02 11:59:44 11:59:44 08704 YES

2056489697 6372 6370 D\\ VM-CATDB\ E\ RMAN_BAK\ MES\ CONTROL_BAK\ C_MES_6MT1SM1I_1_20180502 TAG20180502T115946 2018-05-02 11:59:47 11:59:48 1 10141696 NO

Fourth, use the recovery directory to store RMAN scripts

RMAN scripts can be stored as operating system files, such as the following script file e:\ script1.rman content

Run {

Allocate channel c1 type disk

Allocate channel c2 type disk

Backup incremental level 1 as compressed backupset

Database plus archivelog delete all input

Release channel c1

Release channel c2

}

When executing the script, connect to the target database and recovery directory with RMAN, and connect and execute with the @ character

C:\ > rman target / catalog rman/rman@catdb @ e:\ script1.rman

You can also store the script directly in the recovery directory. Some of the following commands are about scripts, note that these commands must be connected to both the target database and the directory database to be used, and syntax validity checks are performed at the same time when the script is created. By default, the script is specific to the target database, using the keyword global to indicate that the script is visible to all target databases, otherwise the same script needs to connect to each target database separately to create it.

Create script

Create {global} script

Replace script

Replace {global} script

Run the script

Execute {global} script

Show script

Print {global} script

List script

List {global} script names

Delete script

Delete {global} script

You can save RMAN scripts stored as operating system files as scripts in the catalog database using the following command

RMAN > create script script1 from file'e:\ script1.rman'

There is no command to edit scripts directly, but you can view stored scripts by querying the views rc_stored_script and rc_stored_script_line, and you can edit them with DML. For easy editing, you can also transfer the script out to an operating system file

RMAN > print script script1 to file'e:\ script1.rman'

Query the stored scripts through the views rc_stored_script and rc_stored_script_line, and edit them directly through the DML operation if needed

Col script_name for a30

Col script_comment for a30

Select * from rc_stored_script

DB_KEY DB_NAME SCRIPT_NAME SCRIPT_COMMENT

GLOBAL bak_1

1 MES script1

GLOBAL bak_0

Col script_name for a30

Col text for a80

Select * from rc_stored_script_line where script_name = 'script1'

DB_KEY SCRIPT_NAME LINE TEXT

- -

1 script1 1 run {

1 script1 2 allocate channel c1 type disk

1 script1 3 allocate channel c2 type disk

1 script1 4 backup incremental level 1 as compressed backupset

1 script1 5 database plus archivelog delete all input

1 script1 6 release channel c1

1 script1 7 release channel c2

1 script1 8}

Finally, you can execute the stored script in the recovery directory, which first automatically performs a synchronization with the target database, and then starts the backup

RMAN > run {execute script script1;}

5. Database destruction and recovery experiment

A fault handling process is simulated here, assuming that the parameter files and control files of the target database are corrupted, and the data files are also partially corrupted. And the recovery catalog database can not be started due to failure, although the backup exists on the disk of the recovery catalog database server, but RMAN can not find these backups directly at this time, it depends on how to restore the system.

Work before the experiment:

1. Use RMAN to connect to the target database and the recovery catalog database on the recovery catalog machine, and confirm that the backup is complete

C:\ Users\ Administrator > rman target sys/mesHz2@mes catalog rman/rman

RMAN > resync catalog

RMAN > list backup

If the target database is not backed up, make a full backup before the experiment.

2. Close the recovery catalog database, and the simulation recovery directory is not available.

Shutdown immediate

3. Close the target database directly with shutdown abort, delete the parameter files SPFILE, PFILE, all copies of the control file, and the data file cmes01.dbf.

Recovery experiment:

1. Using the RMAN command to connect to the target database on the recovery catalog database server and starting to nomount,RMAN will use a dumb parameter file to start the instance

C:\ Users\ Administrator > rman target sys/mesHz2@mes

Recovery Manager: Release 11.2.0.4.0-Production on Wednesday May 2 22:54:27 2018

Connected to the target database (not started)

RMAN > startup nomount

Startup failed: ORA-01078: failure in processing system parameters

LRM-00109:? 'C:\ ORACLE\ PRODUCT\ 11.2.0\ DBHOME_1\ DATABASE\ INITMES.ORA'

Start an Oracle instance without a parameter file to retrieve spfile

Oracle instance has been started

The total system global area is 1068937216 bytes

Fixed Size 2288080 byt

Variable Size 285214256 byt

Database Buffers 775946240 byt

Redo Buffers 5488640 byt

2. Because you know the location of the disk backup, you can manually specify the backup path to RMAN. First restore the parameter file, then restart the instance using the restored SPFILE, and then restore the control file

RMAN > restore spfile from'\\ vm-catdb\ e\ rman_bak\ mes\ control_bak\ Cmuri 2056489697-20180502-06'

Start restore at 22:56:06 on 2018-05-02

Replace the recovery directory with the target database control file

Assigned channel: ORA_DISK_1

Channel ORA_DISK_1: SID=396 device type = DISK

Channel ORA_DISK_1: restoring spfile from AUTOBACKUP\\ vm-catdb\ e\ rman_bak\ mes\ control_bak\ Cmuri 2056489697-20180502-06

Channel ORA_DISK_1: restore SPFILE from AUTOBACKUP completed

Completion of restore at 22:56:10 in 2018-05-02

RMAN > shutdown abort

Oracle instance is closed

RMAN > startup nomount

Connected to the target database (not started)

Oracle instance has been started

The total system global area is 1286066176 bytes

Fixed Size 2280896 byt

Variable Size 771752512 byt

Database Buffers 503316480 byt

Redo Buffers 8716288 byt

RMAN > restore controlfile from'\\ vm-catdb\ e\ rman_bak\ mes\ control_bak\ Cmuri 2056489697-20180502-06'

Start restore at 22:59:05 on 2018-05-02

Assigned channel: ORA_DISK_1

Channel ORA_DISK_1: SID=156 device type = DISK

Channel ORA_DISK_1: restoring control files

Channel ORA_DISK_1: restore completed. Time: 00:00:02

Output file name = D:\ ORADATA\ MES\ CONTROL01.CTL

Output file name = E:\ FAST_RECOVERY_AREA\ MES\ CONTROL02.CTL

Completion of restore at 22:59:08 in 2018-05-02

3. Restart the instance to the mount state with the recovered control file, and re-catalog and register the backup file in the repository according to the known storage location of the backup set

RMAN > shutdown abort

Oracle instance is closed

RMAN > startup mount

Connected to the target database (not started)

Oracle instance has been started

The database is mounted

The total system global area is 1286066176 bytes

Fixed Size 2280896 byt

Variable Size 771752512 byt

Database Buffers 503316480 byt

Redo Buffers 8716288 byt

To ensure a successful recovery, first change the path of the RMAN backup file, then use the crosscheck backup command to do a cross-check to confirm that the backup status is expired invalid, and then use the delete expired command to register and delete the backup. Of course, this only deletes the backup record, and the backup files on disk will not be deleted.

RMAN > crosscheck backup

Start implicit crosscheck backup at 23:01:55 on 2018-05-02

Assigned channel: ORA_DISK_1

Channel ORA_DISK_1: SID=189 device type = DISK

5 objects that have been cross checked

Completion of implicit crosscheck backup at 23:01:57 in 2018-05-02

Start implicit crosscheck copy at 23:01:57 on 2018-05-02

Use channel ORA_DISK_1

Completion of implicit crosscheck copy at 23:01:57 in 2018-05-02

Search for all files in the recovery area

A catalogue of documents is being compiled.

The catalogue has been compiled

A list of files that have been listed in the directory

File name: e:\ FAST_RECOVERY_AREA\ MES\ archivelog\ ARC_7A938AE1_1_975085062_5.LOG

Use channel ORA_DISK_1