Recovery includes two phases: restore and recover. The former refers to restoring files, and the latter refers to applying redo to files to roll them forward to the time before the disaster. Both RMAN are done automatically.
PERFORM COMPLETE AND INCOMPLETE RECOVERY
Need to enable archive mode
Introduction to Restore and Recovery
complete recovery does not lose data. Incomplete recovery will lose data. Most recoveries are the former.
Full recovery is divided into four steps:
- Take corrupted files offline
- resotre
- recover
- Bring files online
Incomplete recovery is divided into four steps:
- mount database
- restore all data files
- recover database to a point in time
- Open the database with the resetlogs option
The database can usually be in the open state during full recovery. When the database is in the mount state during incomplete recovery, the online redo log will be rebuilt.
Use the RMAN RESTORE and RECOVER commands
The general process is:
SQL> shutdown immediate; SQL> startup mount; $ rman target / catalog rman@rcat RMAN> restore database; RMAN> recover database; RMAN> sql 'alter database open';
Non critical data files can be restored online, and tablespaces must be restored online.
Online recovery of non critical data files
Key data files refer to the data files related to SYSTEM and UNDO tablespaces.
At this time, the database can be open.
The process is as follows:
- Take the tablespace containing non critical data files offline
- Use rman restore to restore data files
- Using rman recovery to recover data files
- Put tablespaces online
SQL> select table_name,tablespace_name from all_tables where table_name = 'TEST';
TABLE_NAME TABLESPACE_NAME
---------------- ------------------------------
TEST USERS
SQL> select file_name, tablespace_name from dba_data_files where tablespace_name ='USERS';
FILE_NAME TABLESPACE_NAME
------------------------------------------------------------ ------------------------------
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf USERS
SQL> select * from appuser1.test;
A
----------
1
SQL> ! rm /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
Log in at this time, and an error is reported:
$ rlwrap sqlplus appuser1/oracle@orclpdb1 SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 9 20:06:19 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle. All rights reserved. ERROR: ORA-01109: database not open SQL> alter database open; alter database open * ERROR at line 1: ORA-01157: cannot identify/lock data file 12 - see DBWR trace file ORA-01110: data file 12: '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf'
Recovery process:
$ rman target / catalog rcat_owner/Welcome1@rcat RMAN> restore pluggable database orclpdb1; RMAN> recover pluggable database orclpdb1;
Recovery successfully verified:
SQL> alter database open;
Database altered.
SQL> select * from appuser1.test;
A
----------
1
SQL> show con_name;
CON_NAME
------------------------------
ORCLPDB1
Complete recovery of key data files
The process is:
- shutdown abort
- startup mount
- rman restore
- rman recover
- alter database open
Incomplete recovery with rman
Logical error recovery, which can be used for point in time recovery. restore point (hereinafter referred to as recovery point) can be used for incomplete recovery or Flashback.
Create a recovery point
The time point can be a specified time or SCN. If none is specified, it defaults to the current SCN.
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
4143033
To create a recovery point:
create restore point good_for_now; create restore point good_for_now as of scn 4143000;
The retention period of the recovery point is determined by the parameter CONTROL_FILE_RECORD_KEEP_TIME decides:
SQL> show parameter CONTROL_FILE_RECORD_KEEP_TIME NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ control_file_record_keep_time integer 30
If you want to keep it permanently, you can use preserve:
SQL> create restore point good_for_now preserve; Restore point created. SQL> drop restore point good_for_now; Restore point dropped.
Perform managed incomplete recovery
The process is:
- Determine the point in time to recover
- If time-based recovery is required, set the NLS environment variable at the OS level
- Stop and start the database to mount status
- Using the RMAN command block, run SET UNTIL, RESTORE, and RECOVER
- (Optional) Open the database in read-only status to verify the recovery point
- Open the database in RESETLOGS mode
The NLS environment variable must be set correctly to correctly parse the time format, for example:
export NLS_LANG=american_america.us7ascii expport NLS_DATE_FORMAT="Mon DD YYYY HH24:MI:SS"
Example:
SQL> create restore point before_disaster; Restore point created. SQL> drop table hr.job_history; Table dropped. SQL> shutdown immediate Pluggable Database closed.
Because it is a PDB, it is already in the mount state:
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 ORCLPDB1 MOUNTED
Restore:
$ rman target / catalog rcat_owner/Welcome1@rcat
run {
set until restore point before_disaster;
restore pluggable database orclpdb1;
recover pluggable database orclpdb1;
}
Open database:
SQL> alter pluggable database orclpdb1 open resetlogs; Pluggable database altered.
Confirm recovered:
SQL> alter session set container=orclpdb1;
Session altered.
SQL> select count(*) from hr.job_history;
COUNT(*)
----------
10
After restoring to the point in time, all subsequent modifications are lost.
Backup recovery using incremental updates
The image copy maintains the original format of the data file, so there is no conversion during recovery, which can speed up recovery. Image copy also supports incremental backup, so recovery can be faster.
Restore Image Copy
When updating the image copy through incremental backup, you need to apply the archive and redo since the last backup addition.
Implement Image Copy Policy
Example policy:
run {
recover copy of database with tag 'inc_upd';
backup incremental level 1 for recovery of copy with tag 'inc_upd' database;
}
The first time, a level 0 backup is generated.
During the second execution, a level 1 backup is generated.
The third time, the image copy will be updated with the previously generated backup
Fast recovery with image copy
The image copy can be used directly, that is, it can be recover ed directly without the need for a remote.
It can be realized through SET NEWNAME in RMAN.
Quickly switch to Image copy
Suppose you have an image copy and all the subsequent redo s and archives.
The process is:
- Put the damaged data file offline through V$RECOVER_FILE, V$DATAFILE_HEADER or V$TABLESPACE determines which files need to be recovered.
- Use RMAN SWITCH.. TO COPY to point to the image copy corresponding to the damaged data file
- recover data file
- Put data files online
RMAN SWITCH is equivalent to the alter database rename file in the database
For example, first make an image copy:
RMAN> backup as copy database;
Delete a tablespace in the operating system:
rm /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf
Confirm the damaged file:
SQL> show con_name;
CON_NAME
------------------------------
ORCLPDB1
SQL>
select file#, status, error, recover, tablespace_name, name from v$datafile_header
where recover = 'YES'
3 or (recover is null and error is not null);
FILE# STATUS ERROR REC TABLESPACE_NAME NAME
---------- ------- -------------------------------- --- ------------------------------ --------------------
12 ONLINE CANNOT OPEN FILE
SQL> select file_name, file_id, tablespace_name from dba_data_files where file_id = 12;
FILE_NAME FILE_ID TABLESPACE_NAME
---------------------------------------------------------------- ---------- ------------------------------
/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf 12 USERS
Take the data file offline:
SQL> alter database datafile 12 offline; Database altered.
Restore:
$ rman target / catalog rcat_owner/Welcome1@rcat Recovery Manager: Release 19.0.0.0.0 - Production on Mon Dec 9 21:28:09 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCLCDB (DBID=2795391422) connected to recovery catalog database RMAN> switch datafile 12 to copy; datafile 12 switched to datafile copy "/u02/fra/backups/ORCLCDB_20191209_26uj1642" starting full resync of recovery catalog full resync complete RMAN> recover datafile 12; Starting recover at 09-DEC-19 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=49 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=60 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=280 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=68 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 09-DEC-19
Online data files:
SQL> alter database datafile 12 online; Database altered.
At this time, the recovery is completed, but the location of the data file changes:
SQL> select file_name, file_id, tablespace_name from dba_data_files where file_id=12; FILE_NAME FILE_ID TABLESPACE_NAME ---------------------------------------------------------------- ---------- ------------------------------ /u02/fra/backups/ORCLCDB_20191209_26uj1642 12 USERS
To restore to the previous location, do the following:
$ ls /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf ls: cannot access /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf: No such file or directory $ rman target / catalog rcat_owner/Welcome1@rcat RMAN> backup as copy datafile 12 format '/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf';
To take a file offline:
$ rman target=sys/Welcome1@orclpdb1 catalog rcat_owner/Welcome1@rcat RMAN> alter database datafile 12 offline; Statement processed
Restore to original location and restore:
$ rman target / catalog rcat_owner/Welcome1@rcat Recovery Manager: Release 19.0.0.0.0 - Production on Mon Dec 9 21:45:00 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCLCDB (DBID=2795391422) connected to recovery catalog database RMAN> switch datafile 12 to copy; datafile 12 switched to datafile copy "/opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf" starting full resync of recovery catalog full resync complete RMAN> recover datafile 12; Starting recover at 09-DEC-19 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=60 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=49 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=280 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=68 device type=DISK starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 09-DEC-19
Online data files:
$ rman target=sys/Welcome1@orclpdb1 catalog rcat_owner/Welcome1@rcat RMAN> alter database datafile 12 online; Statement processed
Verify that you have recovered to the original location:
SQL> select file_name, file_id, tablespace_name from dba_data_files where file_id=12; FILE_NAME FILE_ID TABLESPACE_NAME ---------------------------------------------------------------- ---------- ------------------------------ /opt/oracle/oradata/ORCLCDB/ORCLPDB1/users01.dbf 12 USERS
Make a backup at last, and you can use this recovery next time:
RMAN> backup as copy datafile 12;
Recovery of SPFILES, CONTROLFILES and ONLINE REDO
The automatic backup of RMAN controlfile will back up spfile and controlfile. The controlfile is usually placed in multiple places, and the metadata information in the controlfile is included in the recovery catalog. The redo is protected by multiplex. RMAN does not back up the redo.
Recover spfile from Autobackup
Steps:
- set dbid ########
- startup force nomount
- restore spfile from autobackup;
- startup force
Restore Controlfile
If the control file is multiplex ed, the recovery is to copy the remaining copy. If all are lost, the recovery process is:
- startup nomount
- restore
- mount database
- recover
- open resetlogs
Step 1: Since there is no controlfile, you can only start on nomount.
In the second step, because the database cannot be mount ed, RMAN does not know which backup the controlfile is included in. You can use the methods in 3 to restore from autobackup, catalog, and specified backup (if you saved the output of the backup before).
Step 3: After the mount, you must recover the controlfile because the previously restore d controlfile is not up-to-date. Step 5 is also for this reason.
Example:
SQL> shutdown abort ORACLE instance shut down. SQL> startup force mount ORACLE instance started. Total System Global Area 1207955552 bytes Fixed Size 9134176 bytes Variable Size 452984832 bytes Database Buffers 738197504 bytes Redo Buffers 7639040 bytes ORA-00205: error in identifying control file, check alert log for more info
$ rman target / catalog rcat_owner/Welcome1@rcat Recovery Manager: Release 19.0.0.0.0 - Production on Tue Dec 10 22:55:56 2019 Version 19.3.0.0.0 Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved. connected to target database: ORCLCDB (not mounted) connected to recovery catalog database RMAN> restore controlfile from autobackup; Starting restore at 10-DEC-19 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=255 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=256 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=22 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=257 device type=DISK recovery area destination: /u02/fra database name (or database unique name) used for search: ORCLCDB channel ORA_DISK_1: AUTOBACKUP /u02/fra/ORCLCDB/autobackup/2019_12_09/o1_mf_s_1026597056_gywnb0wk_.bkp found in the recovery area channel ORA_DISK_1: looking for AUTOBACKUP on day: 20191210 recovery area destination: /u02/fra database name (or database unique name) used for search: ORCLCDB channel ORA_DISK_2: AUTOBACKUP /u02/fra/ORCLCDB/autobackup/2019_12_09/o1_mf_s_1026597056_gywnb0wk_.bkp found in the recovery area channel ORA_DISK_2: looking for AUTOBACKUP on day: 20191209 recovery area destination: /u02/fra database name (or database unique name) used for search: ORCLCDB channel ORA_DISK_3: AUTOBACKUP /u02/fra/ORCLCDB/autobackup/2019_12_09/o1_mf_s_1026597056_gywnb0wk_.bkp found in the recovery area recovery area destination: /u02/fra database name (or database unique name) used for search: ORCLCDB channel ORA_DISK_4: AUTOBACKUP /u02/fra/ORCLCDB/autobackup/2019_12_09/o1_mf_s_1026597056_gywnb0wk_.bkp found in the recovery area channel ORA_DISK_4: skipped, AUTOBACKUP already found channel ORA_DISK_2: skipped, AUTOBACKUP already found channel ORA_DISK_1: skipped, AUTOBACKUP already found channel ORA_DISK_3: restoring control file from AUTOBACKUP /u02/fra/ORCLCDB/autobackup/2019_12_09/o1_mf_s_1026597056_gywnb0wk_.bkp channel ORA_DISK_3: control file restore from AUTOBACKUP complete output file name=/opt/oracle/oradata/ORCLCDB/control01.ctl output file name=/opt/oracle/oradata/ORCLCDB/control02.ctl Finished restore at 10-DEC-19 RMAN> alter database mount 2> ; released channel: ORA_DISK_1 released channel: ORA_DISK_2 released channel: ORA_DISK_3 released channel: ORA_DISK_4 Statement processed RMAN> recover database; Starting recover at 10-DEC-19 Starting implicit crosscheck backup at 10-DEC-19 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=24 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=259 device type=DISK allocated channel: ORA_DISK_3 channel ORA_DISK_3: SID=25 device type=DISK allocated channel: ORA_DISK_4 channel ORA_DISK_4: SID=260 device type=DISK Crosschecked 14 objects Crosschecked 13 objects Crosschecked 15 objects Crosschecked 15 objects Finished implicit crosscheck backup at 10-DEC-19 Starting implicit crosscheck copy at 10-DEC-19 using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 Crosschecked 2 objects Crosschecked 2 objects Crosschecked 3 objects Crosschecked 3 objects Finished implicit crosscheck copy at 10-DEC-19 searching for all files in the recovery area cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u02/fra/ORCLCDB/archivelog/2019_12_10/o1_mf_1_32_gyz40zvk_.arc File Name: /u02/fra/ORCLCDB/autobackup/2019_12_09/o1_mf_s_1026597056_gywnb0wk_.bkp using channel ORA_DISK_1 using channel ORA_DISK_2 using channel ORA_DISK_3 using channel ORA_DISK_4 starting media recovery archived log for thread 1 with sequence 32 is already on disk as file /u02/fra/ORCLCDB/archivelog/2019_12_10/o1_mf_1_32_gyz40zvk_.arc archived log for thread 1 with sequence 33 is already on disk as file /opt/oracle/oradata/ORCLCDB/redo03.log archived log file name=/u02/fra/ORCLCDB/archivelog/2019_12_10/o1_mf_1_32_gyz40zvk_.arc thread=1 sequence=32 archived log file name=/opt/oracle/oradata/ORCLCDB/redo03.log thread=1 sequence=33 media recovery complete, elapsed time: 00:00:01 Finished recover at 10-DEC-19 ## This step takes a long time RMAN> alter database open resetlogs; Statement processed new incarnation of database registered in recovery catalog starting full resync of recovery catalog full resync complete
Recover from the lost Redo Log Group
Check whether all or part of the log group is lost and the current status of the log group. The loss of a log group or log group member may or may not cause data loss.
The status of the log file can be queried from the v$log. The status is divided into:
- CURRENT: This log group is being written and needs to be used for instance recovery
- ACTIVE: It is not being written. This log group needs to be used for instance recovery
- INACTIVE: No need for instance recovery, there may be no archive
- UNUSED: Not used
- CLEANING: being cleaned by the alter database clear logfile command, and the status will be UNUSED after completion
- CLEARING_CURRENT: An error occurred while the alter database clear logfile command was cleaning
The most common statuses are the first three.
SQL> select group#, sequence#, archived, status from v$log;
GROUP# SEQUENCE# ARC STATUS
---------- ---------- --- ----------------
1 1 YES INACTIVE
2 2 YES INACTIVE
3 3 NO CURRENT
Note that the logs of the above two INACTIVE statuses have been archived.
Recovering from Log Group member errors
If a member of the log group fails, LGWR will continue to write to another member, and the data will not be lost, but it should be repaired as soon as possible.
SQL> select group#, status, member from v$logfile;
GROUP# STATUS MEMBER
---------- ------- ----------------------------------------------------------------
3 /opt/oracle/oradata/ORCLCDB/redo03.log
2 /opt/oracle/oradata/ORCLCDB/redo02.log
1 /opt/oracle/oradata/ORCLCDB/redo01.log
1 /opt/oracle/oradata/ORCLCDB/redo01_2.log
2 /opt/oracle/oradata/ORCLCDB/redo02_2.log
3 /opt/oracle/oradata/ORCLCDB/redo03_2.log
6 rows selected.
SQL> !rm /opt/oracle/oradata/ORCLCDB/redo02_2.log
If the status is INVALID, delete and rebuild:
alter database drop logfile member '/opt/oracle/oradata/ORCLCDB/redo03.log'; alter database add logfile member '/opt/oracle/oradata/ORCLCDB/redo03.log' to group 1;
Recovery when the entire Log Group in INACTIVE status is lost
This log group does not need to be used for sample recovery, so the entire loss has little impact.
You can use alter database clear logfile to clean.
Specific commands can be queried from the archive column in the v$log according to the archive status.
If it has been archived, there will be no data loss. The process is as follows, and clear will be re established:
alter database clear logfile group 1;
If the transaction is not archived, the committed transaction will not be deleted, but a full backup must be made after the clear log. Otherwise, the archive files will be discontinuous, resulting in incomplete recovery. The treatment is as follows:
alter database clear unarchived logfile group 1;
Recovery when the Log Group in ACTIVE status is lost
If the status is active, it means that no log is currently written to it, but this log group needs to be used for instance recovery.
First, execute the alter system checkpoint to flush the dirty data in the buffer to the data file, and then clear the log file, as shown in the above example.
Recovery when the Redo Log Group in CURRENT status is lost
This is the only case where data will be lost. The instance will crash and can only be recovered incompletely.
Backup and restore password files
The password file is located in ORACLE_HOME/dbs, RMAN cannot be managed. The operating system backup command is required. For example, the backup of the Oracle Home directory. However, if the password file is stored in ASM, ASMCMD must be used to copy it to the file system. If the password file is damaged, you can simply copy it back, or use orapwd to rebuild it.
Recover from lost temporary files
Temporary files are also data files, but belong to temporary tablespaces, which have little impact on damage and can be recovered online.
Missing temporary files
You can use alter tablespace to rebuild.
Example:
-- View the original temporary file location select file#, name from v$tempfile; -- Create new temporary documents alter tablespace temp add tempfile '/u01/app/oracle/oradata/orclpdb1/temp02.dbf' size 25m; -- Delete the original temporary file drop tablespace temp drop '/u01/app/oracle/oradata/orclpdb1/temp01.dbf'; select file#, name from v$tempfile;
Start without temporary files
The database will be automatically rebuilt in the original location. If the original location is unavailable, refer to the previous processing method.
reference resources
- https://oracle-base.com/articles/12c/multitenant-rman-backup-recovery-cdb-and-pdb-12cr1#pdb-backup