OCA/OCP Oracle Database 12c Examination Guide Reading Notes: Chapter 24: Restore and Recover with RMAN

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:

  1. Take corrupted files offline
  2. resotre
  3. recover
  4. Bring files online

Incomplete recovery is divided into four steps:

  1. mount database
  2. restore all data files
  3. recover database to a point in time
  4. 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:

  1. Take the tablespace containing non critical data files offline
  2. Use rman restore to restore data files
  3. Using rman recovery to recover data files
  4. 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:

  1. Determine the point in time to recover
  2. If time-based recovery is required, set the NLS environment variable at the OS level
  3. Stop and start the database to mount status
  4. Using the RMAN command block, run SET UNTIL, RESTORE, and RECOVER
  5. (Optional) Open the database in read-only status to verify the recovery point
  6. 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:

  1. Put the damaged data file offline through V$RECOVER_FILE, V$DATAFILE_HEADER or V$TABLESPACE determines which files need to be recovered.
  2. Use RMAN SWITCH.. TO COPY to point to the image copy corresponding to the damaged data file
  3. recover data file
  4. 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:

  1. set dbid ########
  2. startup force nomount
  3. restore spfile from autobackup;
  4. 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:

  1. startup nomount
  2. restore
  3. mount database
  4. recover
  5. 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

  1. https://oracle-base.com/articles/12c/multitenant-rman-backup-recovery-cdb-and-pdb-12cr1#pdb-backup

Tags: ocp

Posted by system on Sun, 25 Sep 2022 21:45:09 +0530