Database Point-in-Time Recovery_dbpitr-优快云博客

本文链接：https://blog.youkuaiyun.com/linsuhangoracle/article/details/119134560

DBPITR与TSPITR均为不完全恢复，本文为数据库的时间点恢复，表空间级单列了文档

Database Point-in-Time Recovery(DBPITR) is sometimes called incomplete recovery because it does not use all of the available redo or completely recover all changes to your database.

If unwanted database changes are extensive but confined to specific tablespaces, then you can use tablespace point-in-time recovery (TSPITR) to return these tablespaces to an earlier system change number (SCN) while the unaffected tablespaces remain available.

关于数据库在需要使用OPEN RESETLOGS打开前是否可使用OPEN READ ONLY打开

FLASHBACK DATABASE可以先以READ ONLY打开查看恢复状态，不符合要求可以重新向前FLASHBACK DATABASE或向后RECOVER DATABASE
使用当前控制文件做DBPITR只能以OPEN RESETLOGS打开(控制文件与数据文件不符)
使用备份控制文件进行完全或不完全恢复，可以先以READ ONLY打开查看恢复状态

When you perform DBPITR with a backup control file, before opening the database with RESETLOGS, you can open the database read-only using SQL*Plus and run queries as needed to verify that the effects of the logical corruption have been reversed. If you are satisfied with the results, then you can open the database with RESETLOGS.

（一）Overview of Database Point-in-Time Recovery

数据库时间点恢复与闪回数据库均为把数据库回到过去某一时间点，但使用技能不同，前者使用备份后者使用flashback log。Oracle优先建议使用FLASHBACK

The result of flashback database is like the result of a DBPITR, including the OPEN RESETLOGS, but Flashback Database is typically faster because it does not require you to restore data files and requires only limited application of redo compared to media recovery.

Oracle recommends that you perform Flashback Database rather than database point-in-time recovery if possible. Media recovery with backups are the last option when flashback technologies cannot be used to undo the most recent changes.

Typically, the following situations call for flashback features or point-in-time recovery:

A user error or corruption removes needed data or introduces corrupted data. For example, a user or DBA might erroneously delete or update the contents of one or more tables, drop database objects that are still needed during an update to an application, or run a large batch update that fails midway.
A database upgrade fails or an upgrade script goes awry.
A complete database recovery after a media failure cannot succeed because you do not have all of the needed redo logs or incremental backups.

RMAN DBPITR has the following disadvantages:

You cannot return selected objects to their earlier state, only the entire database.
Your entire database is unavailable during the DBPITR.
DBPITR can be time-consuming because RMAN must restore all data files. Also, RMAN may need to restore redo logs and incremental backups to recover the data files.

（二）Performing Database Point-in-Time Recovery

The prerequisites for database point-in-time recovery (DBPITR) are as follows:

Your database must be running in ARCHIVELOG mode.
You must have backups of all data files from before the target SCN for DBPITR and archived logs for the period between the SCN of the backups and the target SCN.

Determine the end of recovery.

You can recover to an SCN, time, log sequence number, or restore point. If specifying a time, then use the date format specified in the NLS_LANG and NLS_DATE_FORMAT environment variables.

Oracle recommends that you create restore points at important times to make point-in-time recovery more manageable if it ever becomes necessary.

use Flashback Query to identify when the logical corruption occurred
use the alert log to try to determine the time of the event from which you must recover.

You can use a SQL query to determine the log sequence number that contains the target SCN

For example, run the following query to list the logs in the current database incarnation:

SELECT RECID, STAMP, THREAD#, SEQUENCE#, FIRST_CHANGE# FIRST_TIME, NEXT_CHANGE#

FROM V$ARCHIVED_LOG

WHERE RESETLOGS_CHANGE# =

( SELECT RESETLOGS_CHANGE#

FROM V$DATABASE_INCARNATION

WHERE STATUS = 'CURRENT');

For example, if you discover that a user accidentally dropped a tablespace at 9:02 a.m., then you can recover to 9 a.m., just before the drop occurred. You lose all changes to the database made after this time.

Performing DBPITR Using Current ControlFile

This section explains the basic steps of DBPITR. The procedure makes the following assumptions:

You are performing DBPITR within the current database incarnation.
The control file is current.
Your database is using the current server parameter file.

To perform DBPITR:

Connect RMAN to the target database and, if applicable, the recovery catalog database. Bring the database to a mounted state:

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

Perform the following operations within a RUN block:

查看INCARNATION及备份信息

LIST INCARNATION;

LIST BACKUP BY FILE;

If automatic channels are not configured, then manually allocate disk and tape channels as needed. Restore and recover the database.

RUN{

SET UNTIL SCN 1000;

RESTORE DATABASE;

RECOVER DATABASE;}

其它UNTIL的时间格式

SET UNTIL TIME 'Nov 15 2013 09:00:00';

SET UNTIL TIME "TO_DATE('2013-4-6 00:00:00','YYYY-MM-DD HH24:MI:SS')";

SET UNTIL SEQUENCE 9923;

SET TO RESTORE POINT before_update;

Perform either of the following mutually exclusive actions:

在恢复后只能使用OPEN RESETLOGS打开数据库，你可以直接使用或把数据导出，再将数据库恢复到进行DBPITR前原来状态

Open your database for read/write, abandoning all changes after the target SCN:

ALTER DATABASE OPEN RESETLOGS;

Export one or more objects from your database with Data Pump Export. You can then recover the database to the current point in time and reimport the exported objects.

Note: The OPEN RESETLOGS operation fails if a data file is offline unless the data file went offline normally or is read-only. You can bring files in read-only or offline normal tablespaces online after the RESETLOGS because they do not need any redo.

Performing DBPITR Using Backup ControlFile

数据库的时间点恢复通常不能使用当前控制文件，在恢复到ancestor incarnation或恢复已删除的表空间时需要使用备份的控制文件来恢复。数据库的时间点恢复时要注意以下场景：

恢复到当前INCARNATION，恢复点至当前时间未出现DROP TABLESPACE操作，则使用当前控制文件即可恢复
恢复到当前INCARNATION，恢复点至当前时间出现DROP TABLESPACE操作，则需要使用DROP TABLESPACE前的备份控制文件恢复
恢复到非当前INCARNATION需要相应INCARNATION的备份控制文件恢复

NOTE: 使用SET UNTIL还原备份的SPFILE或CONTROLFILE(RESTORE CONTROLFILE/SPFILE FROM AUTOBACK)时只能使用SET UNTIL TIME不能使用SET UNTIL SCN或SEQUENCE

以下为想使用DBPITR恢复到DROP TABLESPACE前的数据库操作过程：

创建测试环境

SQL> create tablespace test datafile '/u01/app/oracle/oradata/orcl/test01.dbf' size 20m;

SQL> select file_id, file_name from dba_data_files where tablespace_name='TEST';

FILE_ID FILE_NAME

--------------------------------------------------------------------------------

17 /u01/app/oracle/oradata/orcl/test01.dbf

SQL> create table t1(id number) tablespace test;

SQL> insert into t1 values(111);

SQL> commit;

RMAN> backup database;

SQL> select sysdate from dual; ---2021-06-28 16:29:20

DROP TABLESAPCE

SQL> drop tablespace test including contents and datafiles;

对数据库进行时间点恢复

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP NOMOUNT

RMAN> SET DBID 1589350487

RMAN> RUN{

SET UNTIL TIME '2021-06-28 16:29:20';

RESTORE CONTROLFILE FROM AUTOBACKUP;}

RMAN> ALTER DATABASE MOUNT;

RMAN> RUN{

SET UNTIL TIME '2021-06-28 16:29:20';

RESTORE DATABASE;

RECOVER DATABASE;}

RMAN> ALTER DATABASE OPEN RESETLOGS;

查看数据

SQL> select file_id, file_name from dba_data_files where tablespace_name='TEST';

SQL> select * from t1;

Performing Point-in-Time Recovery of CDBs and PDBs

RMAN enables you to perform point-in-time recovery (PITR) of CDBs and PDBs.

PITR for PDBs can only be performed using RMAN. If you are not using a recovery catalog, it is recommended that you turn on control file auto backups. Otherwise, PITR for PDBs may not work effectively when RMAN needs to undo data file additions or deletions.

3.1 Performing Point-in-Time Recovery of a Whole CDB

To perform point-in-time recovery of a whole CDB:

Connect RMAN to the root as a common user with the common SYSBACKUP or SYSDBA privilege. If applicable, connect to a recovery catalog.
Bring the CDB to a mounted state.

SHUTDOWN IMMEDIATE;

STARTUP MOUNT;

Perform the following operations within a RUN block:

RUN {

SET UNTIL SCN 1000;

RESTORE DATABASE;

RECOVER DATABASE;}

Perform either of the following mutually exclusive actions:

Open your CDB for read/write, abandoning all changes after the target SCN. In this case, you must shut down the CDB, mount it, and then execute the following command:

ALTER DATABASE OPEN RESETLOGS;

Export one or more objects from your CDB with Data Pump Export. You can then recover the CDB to the current point in time and reimport the exported objects, thus returning these objects to their state before the unwanted change without abandoning all other changes.

Open all the PDBs.

ALTER PLUGGABLE DATABASE ALL OPEN;

3.2 Performing Point-in-Time Recovery of PDBs

When you recover one or more PDBs to a specified point-in-time, the remaining PDBs in the CDB are not affected and they can be open and operational.

In a Data Guard environment, for the standby database to follow a primary database in which a PDB was restored to a particular point in time, you may need to either flash back the entire standby database, restore the PDB, or flash back the PDB.

About DBPITR of PDBs and the Fast Recovery Area

在使用SHARED UNDO时同样需要PDB相关的CDB$root及PDB$seed的备份，它们会还原为auxiliary database, 用于对PDB进行recover。不过我试了下使用LOCAL UNDO但未配置AUXILIARY以及闪回恢复区也会报错

When performing DBPITR on one or more PDBs in a CDB that uses shared undo, backups of the root and the CDB seed (PDB$SEED) of the CDB that contains the PDBs are required. Because the UNDO tablespace is shared by all PDBs, it cannot be recovered in-place. RMAN restores the UNDO, SYSTEM, and SYSAUX tablespaces in the root to an auxiliary database and then uses the undo information to recover the PDB to the target time.

If a fast recovery is configured, Oracle Database uses it as the auxiliary destination. If the fast recovery area is not configured, then you must use the AUXILIARY DESTINATION clause to specify the location used for auxiliary database files. Ensure that there is sufficient space in the fast recovery area to restore the root tablespaces and the undo tablespace. If the fast recovery area does not have the required space, use an alternate location by specifying the AUXILIARY DESTINATION clause.

About Flashback Database and PITR for PDBs

在使用shared undo时pdb进行resetlogs操作时同时整个CDB进行recover可能导到pdb不可用，需要对pdb进行恢复

For PDBs that use local undo, database point-in-time recovery and flashback operations are independent of each other. For PDBs that use shared undo, database point-in-time recovery and flashback operations are independent with the following caveat警告:

If you subsequently recover the entire multitenant container database (CDB) to a point in time that is in the middle of the PDB resetlogs operation, then you will receive a warning that some PDBs may not be opened.

For such PDBs, you need to perform one of the following mutually exclusive actions:

Recover the entire CDB or perform a flashback operation for the entire CDB to a different SCN
Recover all the affected PDBs or perform a flashback database operation for all the affected PDBs to a different SCN

To perform DBPITR on a PDB:

Connect RMAN to the root as a common user with the SYSDBA or SYSBACKUP privilege. If applicable, connect to a recovery catalog.
Close the PDB that is being recovered. The other PDBs and the CDB can remain open.

ALTER PLUGGABLE DATABASE pdb1 CLOSE;

Perform the following operations within a RUN block:

RUN {

SET UNTIL SCN 1000;

RESTORE PLUGGABLE DATABASE my_pdb;

RECOVER PLUGGABLE DATABASE my_pdb;}

Open the PDB abandoning all changes after the target SCN.

ALTER PLUGGABLE DATABASE my_pdb OPEN RESETLOGS;

RESETLOGS creates a new PDB incarnation. You can query the V$PDB_INCARNATION view for the incarnation number.

Performing Point-in-Time Recovery of Application PDBs

To perform point-in-time recovery of an application PDB:

Start RMAN and connect to the application root as an application common user with the SYSDBA or SYSBACKUP privilege.
Close the application PDB that must be recovered.

ALTER PLUGGABLE DATABASE hr_appcont_pdb1 CLOSE;

Perform the following operations within a RUN block:

RUN {

SET UNTIL SCN 34506;

RESTORE PLUGGABLE DATABASE hr_appcont_pdb1;

RECOVER PLUGGABLE DATABASE hr_appcont_pdb1;}

Open the application PDB with RESETLOGS. This results in all changes after the target SCN being abandoned.

ALTER PLUGGABLE DATABASE hr_appcont_pdb1 OPEN RESETLOGS;

Recovering the Database to an Ancestor Incarnation

The procedure for DBPITR within the current incarnation is different from DBPITR to an SCN in a noncurrent incarnation. In the latter case, you must explicitly execute the RESET DATABASE to reset the database to the incarnation that was current at the target SCN. Also, you must restore a control file from the database incarnation containing the target SCN.

When RMAN is connected to a recovery catalog, a RESTORE CONTROLFILE command only searches the current database incarnation for the closest time specified in the UNTIL clause. To restore a control file from a noncurrent incarnation, you must execute LIST INCARNATION to identify the target database incarnation and specify this incarnation in the RESET DATABASE TO INCARNATION command.

To perform DBPITR to a noncurrent incarnation:

Start RMAN and connect to a target database and recovery catalog.
Determine which database incarnation was current at the time of the backup.

LIST INCARNATION OF DATABASE trgt;

List of Database Incarnations

DB Key Inc Key DB Name DB ID STATUS Reset SCN Reset Time

------- ------- ------- ------ ------- ---------- ----------

1 2 TRGT 1224038686 PARENT 1 02-OCT-13

1 582 TRGT 1224038686 CURRENT 59727 10-OCT-13

Look at the Reset SCN and Reset Time columns to identify the correct incarnation, and note the incarnation key in the Inc Key column.

Ensure that the database is started but not mounted.

STARTUP FORCE NOMOUNT;

Reset the target database to the incarnation obtained in Step 2.

RESET DATABASE TO INCARNATION 2;

Restore and recover the database, performing the following actions in the RUN command:

RUN {

SET UNTIL TIME 'Oct 8 2013 07:55:00';

RESTORE CONTROLFILE;

# without recovery catalog, use RESTORE CONTROLFILE FROM AUTOBACKUP

ALTER DATABASE MOUNT;

RESTORE DATABASE;

RECOVER DATABASE;}

ALTER DATABASE OPEN RESETLOGS;

注:上面过程为使用catalog恢复过程，不使用catalog无法在MOUNT前执行RESET DATABASE TO INCARNATION，这时要先在NOMOUNT使用UNTL与AUTOBACKUP CONTROLFILE还原控制文件，然后在MOUNT下执行RESET DATABASE TO INCARNATION

When RMAN is not connected to a recovery catalog, you cannot execute the RESET DATABASE TO INCARNATION command before the database is mounted. Thus, you must execute SET UNTIL, restore the control file from autobackup, and then mount it.