Shulou(Shulou.com)06/01 Report--

[TOC] RMAN backup 1. What is RMAN?

Official definition of RMAN:

First of all, RMAN is a program that integrates with the Oracle database, which is equivalent to a function of the database that does not need to be installed separately.

The smallest unit of a RMAN is a file, which means that it backs up a file rather than a table.

The connection method of RMAN:

1) Local connection:

[oracle@server1 app] $echo $ORACLE_ SIDProe [oracle @ server1 app] $rman target / Recovery Manager: Release 11.2.0.4.0-Production on Tue Jul 7 16:48:27 2020Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.connected to target database: PROE (DBID=485315595) RMAN >

2) Network connection:

[oracle@server1 ~] $rman target sys/oracle@192.168.0.50:1521/server1

3) Service name connection:

[oracle@server1] $rman target sys/oracle@proe2. Several categories of RMAN backups:

1) backupset: backup set, which is only for data backup.

-compress buckupset, compress the backup set, extract only the data part of the backup target file, and skip places that do not contain data.

Backup set: backup set is logical, it is a collection of all backup slices, backup slices are physical, piece handle.

2) image copy: mirror copy, including the same structure as the original data, so that the backup file can directly replace the original file.

Test the final size of these categories:

Mirror copy: RMAN > backup as copy tablespace example format'/ u01max app backup to examplecopy% T' Starting backup at 07-JUL-20using channel ORA_DISK_1channel ORA_DISK_1: starting datafile copyinput datafile file number=00005 name=/u01/app/oracle/oradata/proe/example01.dbfoutput file name=/u01/app/backup/example_copy_20200707 tag=TAG20200707T171203 RECID=3 STAMP=1045156330channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07Finished backup at 07-JUL-20 backup set: RMAN > backup as backupset tablespace example format'/ u01amp Backup Starting backup at 07-JUL-20using channel ORA_DISK_1channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile (s) in backup setinput datafile file number=00005 name=/u01/app/oracle/oradata/proe/example01.dbfchannel ORA_DISK_1: starting piece 1 at 07-JUL-20channel ORA_DISK_1: finished piece 1 at 07-JUL-20piece handle=/u01/app/backup/example_bs_20200707 tag=TAG20200707T171524 comment=NONEchannel ORA_DISK_1: backup set complete Elapsed time: 00:00:03Finished backup at 07-JUL-20 can do without as backupset This is the default. Compressed backup set: RMAN > backup as compressed backupset tablespace example format'/ u01xapplash _ backup _ example _ backup _ bsls% T' Starting backup at 07-JUL-20using channel ORA_DISK_1channel ORA_DISK_1: starting compressed full datafile backup setchannel ORA_DISK_1: specifying datafile (s) in backup setinput datafile file number=00005 name=/u01/app/oracle/oradata/proe/example01.dbfchannel ORA_DISK_1: starting piece 1 at 07-JUL-20channel ORA_DISK_1: finished piece 1 at 07-JUL-20piece handle=/u01/app/backup/example_c_bs_20200707 tag=TAG20200707T171834 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:03Finished backup at 07-JUL-20

Check the size of these three backups:

There are still some gaps.

View backups:

Backup set:

Mirror backup:

Note: if this backup file has been deleted at the system level, it will still exist here.

In this way, it will not exist again:

3. Which file backups are supported by RMAN: SYS@proe > select * from v$rman_backup_type WEIGHT INPUT_TYPE--1 BACKUPSET backup set 2 SPFILE spfile 3 CONTROLFILE control file 4 ARCHIVELOG archive log file 5 DATAFILE INCR data file incremental backup 6 DATAFILE FULL data file full backup 7 DB INCR entire library Incremental backup 8 RECVR AREA flash zone backup 9 DB FULL database complete (level 0) 9 rows selected4. Backup operations for all kinds of files in the database:

Backup and recovery of parameter files:

The parameter file in Oracle is divided into dynamic parameter file and static parameter file according to whether it can be modified directly or not. The dynamic parameter file is binary and cannot be modified directly, that is, spfile can be backed up through RMAN, and the static parameter file pfile can only be done in the form of physical backup copy.

As above, the command and the location of the final backup piece.

The scene where the simulation parameter file is missing:

Change the name of the spfile file to simulate spfile corruption (if pfile,spfile has one, you can start the database, so you have to change it):

Then the error will be reported when it starts.

Restore through RMAN:

Start pseudo-instance (for parameter file recovery only)

Then re-check and find that the file has been generated:

Control file backup and recovery:

Control files for backup:

View backup information about control files:

The overall recovery method is similar to the parameter file recovery, but because the changes of the database are recorded in the control file, be sure to update the SCN number and use recover before restoring.

The reason why RMAN > alter database open resetlogs; uses resetlogs is that the recover command can only repair the physical structure information of the database in the control file, but cannot modify the sequence number of the current redo log in the control file. After the execution of the recover command, the serial number of the current online log in the control file is still obsolete. Although resetlogs is used, because the committed transaction is not lost when the recover database command executes successfully, resetlogs only takes care of the restored control file, which is different from the resetlogs with incomplete recovery. Backup and recovery of data files:

The database must be in archive mode when you need to use rman backup

Types of backup data files: file level, tablespace level, entire library level

1) backup system tablespace system:

Do some actions on the tables under the system tablespace:

Switch logs several times:

Simulate system tablespace file corruption:

There is a problem with the system and crashes:

From the picture below, the system tablespace file is a critical system data file. Once the database is corrupted, it can be started to the mount state but cannot be opened. That is to say, the metadata information can be seen at this time because it is already in the mount database.

The next step is RMAN recovery.

View backup information about system tablespaces:

Restore system tablespaces:

Fix the system tablespace:

Just start it:

View the table information again:

The deleted tables after backup no longer exist, indicating that tablespace recovery does not involve tables under tablespaces. The table still needs to be backed up separately.

View data file information:

Has recovered.

2) non-critical data files:

Simulate how normal data file corruption can be recovered by RMAN backup.

First create the table needed for the simulation: testtable,testtable2 and insert some data accordingly.

You can see that there are two test tables under the TEST_1 tablespace.

Make an RMAN backup of this tablespace

Looking at the RMAN backup information, you can see that the backup was successful.

Now that the backup is complete, do some operations on the tables in this tablespace to make some changes to its contents. Let's insert some data.

Then we need to destroy the data file of the table in this tablespace.

First find out where the data file for this tablespace is.

Simulate deletion after determining the location of the file. It is recommended that you use cp replication simulation deletion.

After the data file simulation is deleted, the database cache needs to be flushed out and the memory buffer data is written back to the data file, and then the ckpt process will find an error and the instance will crash.

Check the status of the database at this time:

In order that the open state does not crash, it means that the file error is only found when the data file synchronously wants to read data from the data file to the buffer, which will not cause the instance to crash.

Enter the RMAN recovery phase

At this point, the instance does not crash, so you need to take this tablespace offline first. Note that immediate goes offline directly to avoid dirty block writes.

Perform a tablespace restore operation:

Then fix the tablespace:

Finally, put the tablespace online.

Looking at the table data, you can see that the later inserted data has also been restored.

In another case, the instance crashes directly and loses its connection when the data file is corrupted. Reconnect and start, only the database can be started to the mount state. At this time, you can directly enter RMAN to perform the above table space restore operation and table space repair operation. Then open the database.

Archive log file backup:

RMAN recognizes archive logs by default.

View all archive logs:

View the archive logs for all RMAN backups:

Back up all archive logs through RMAN:

Delete the archive log of the backup:

Determine the starting point of the backup by specifying the SCN of the backup archive log:

Specify the scn scope for archive log recovery:

5. Several recovery scenarios of RMAN backup: incomplete recovery:

First of all, do a complete database: (full for data files and parameter files and files)

Then query the current SCN of the system:

Artificial simulation of misoperation. This is the case with the current employees table.

Truncate this table:

There are some problems here. The table cannot truncate because the primary key is referenced by the foreign key of other tables. The solution is to invalidate the table primary key temporarily, execute it and then make it effective again.

HR@proe > truncate table HR.employees;truncate table HR.employees * ERROR at line 1:ORA-02266: unique/primary keys in table referenced by enabled foreign keysHR@proe > alter table employees disable primary key cascade;Table altered.HR@proe > HR@proe > truncate table employees;Table truncated. HR@proe > alter table employees enable primary key;Table altered.

After performing the misoperation, the table is in this state:

Next, perform an incomplete recovery operation through RMAN to retrieve the correct data:

Use RMAN for consistent library closure:

Start the database to the mount status in RMAN:

Restore the database:

Repair the database to the specified SCN:

At this time, query the database:

Because the database is still in the mount state at this time, but you can't open directly, you need to execute resetlogs. Because the logs that are not fully recovered are not continuous, you need to reset the logs.

Modify the state of the database in RMAN to start it:

Re-query the information about this table to see that it has been restored:

Full recovery of the entire database:

Note: backup is in the open state of the database and recovery is in the mount state of the database.

Back up the files that need to be backed up for the entire library:

   all data files, log files, control files, parameter files

Simulation process:

Start backup:

1) determine the status of the current database: (need to be in open state)

2) enter RMAN to start backup:

RMAN > sql'alter system archive log current';sql statement: alter system archive log current## an additional explanation of the difference between 'alter system archive log current'' and 'alter system switch logfile''. On the face of it, the former performs log switching for all instances in the database, while the latter only performs log switching for the single instance database or the current instance in RAC. Therefore, the former is often used when switching logs in RAC. At a deeper level, the former requires the database to enable archiving. If archiving is not enabled, you need to specify a specific log group, and this log group cannot be the current log group. At the same time, the current in this command refers to the current log group, so log groups must be switched before archiving, so this command is accompanied by log group switching. The latter is mainly aimed at log groups that have nothing to do with whether archiving is enabled or not, and the main function is to forcibly switch log groups. If archiving is enabled and log group switching occurs, archiving is bound to occur. If database archiving is not enabled, archiving cannot happen, but log groups will still be forced to switch. RMAN > crosscheck archivelog all;# check the difference between the control file and the actual physical file. This command is often used in conjunction with the following command, and simple verification does not affect the result of the final backup. Released channel: ORA_DISK_1allocated channel: ORA_DISK_1...archived log file name=/u01/app/oracle/fast_recovery_area/PROE/archivelog/2020_07_13/o1_mf_1_1_hjr1nnno_.arc RECID=50 STAMP=1045666836Crosschecked 12 objectsRMAN > delete expired archivelog all;# synchronizes control files with physical files, and the two RMAN management and maintenance commands will be discussed in more detail later. Released channel: ORA_DISK_1allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=572 device type=DISKspecification does not match any archived log in the repositoryRMAN > sql'alter system switch logfile';# forces you to switch logs. Sql statement: alter system switch logfile

Back up the data file:

With regard to plus archivelog, this command causes RMAN to back up the archive log first, then switch the current online log and archive the log, and then back up the latest archive log.

Back up the parameter file:

Backup control files:

Note:

If a copy of the backup file is generated using db_recovery_file_dest as the path generated by the backup file, an ORA-19806 error will be generated.

RMAN-03009: failure of backup command on ORA_DISK_1 channel

ORA-19806: cannot make duplex backups in recovery area

As a workaround, specify the backup path as a directory other than db_recovery_files_dest

RMAN > backup tablespace users format'/ home/oracle/%U.dbf'

Start recovery:

Check what files are under the current backup path:

You can see that two files were generated during the data file backup, so this is why the suffix is% U, because when the database is complete, the control file will automatically back up the control file and parameter file of the current database, that is, the smaller database file above, and an error will be reported here if the name is repeated.

1) restore the parameter file first.

View the SID of the current user.

Enter RMAN, start the pseudo-instance database to nomount state, and restore the parameter file.

There may be an error here. Check https://www.cnblogs.com/plutozzl/p/13293789.html for the specific reason.

The parameter file was restored successfully, and the pseudo instance was closed and restarted to nomount state.

You can see that the report is not wrong.

2) restore control files:

The database is in the nomount state at this time. Mount will report an error.

So you still need to restore the control file:

When the restore is successful, you can mount the database and view the backup information.

3) restore the data file:

Restore the database:

Repair the database:

4) complete the recovery of the whole database and open the database:

You still need to add the resetlogs parameter here, because the control file is also restored, so the log needs to be reset.

Restore succeeded:

This approach can be used for database migration, but the path to restore RMAN backups in a new library must be consistent.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.