通过恢复目录（Catalogue）进行PDB级别的PITR恢复
2019-08-05 06:22 askscuti 阅读(598) 评论(0) 编辑 收藏 举报数据库版本：Oracle 12.2.0.1
本篇为《执行PDB的PITR恢复失败的说明 (文档 ID 2435452.1)》的证明篇，通过当前控制文件，无法在PDB级别进行PITR（Point In Time Recovery）恢复出被DROP的表空间，因为当前控制文件没有记录该表空间（数据文件），必须想办法对控制文件下手，但是控制文件是CDB共享的，意味着将要影响所有数据库。具体是否要通过恢复控制文件副本（或手工重建），根据实际情况自行决定。
官方给出的意见是用目录库进行恢复。在不动控制文件的前提下，本试验采用 Catalogue 看是否能对 PDB 级别进行 PITR - 恢复出被DROP的表空间。
目录
1. 创建恢复目录
2. 创建表空间
3. 执行全备
4. 删除表空间
5. 执行PDB PITR
1. 创建恢复目录库（Recovery Catalog）
CDB2 创建表空间
SQL> create tablespace rmanbak datafile '/u01/app/oracle/oradata/CDB2/rmanbak01.dbf' size 10m; Tablespace created.
CDB2 创建用户
SQL> create user c##rmanbak identified by rmanbak default tablespace rmanbak; User created.
CDB2 用户授权
SQL> grant dba,recovery_catalog_owner to c##rmanbak; Grant succeeded.
连接 CDB2 创建 Catalog
[oracle@henry ~]$ rman target sys/oracle@cdb catalog c##rmanbak/rmanbak@cdb2 Recovery Manager: Release 12.2.0.1.0 - Production on Sun Aug 4 12:27:01 2019 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. connected to target database: CDBOCP (DBID=357991829) connected to recovery catalog database RMAN> create catalog tablespace rmanbak; recovery catalog created
CDB2 上注册 CDB 数据库
RMAN> register database; database registered in recovery catalog starting full resync of recovery catalog full resync complete
2. 创建表空间
CDBOCP：PDB1 上创建表空间
SQL> show pdbs CON_ID CON_NAME OPEN MODE RESTRICTED ---------- --------- ---------- ---------- 2 PDB$SEED READ ONLY NO 3 PDB1 READ WRITE NO
SQL> alter session set container=pdb1; Session altered. SQL> select name from v$datafile; SQL> create tablespace askscuti datafile '/u01/app/oracle/oradata/CDBOCP/PDB1/askscuti01.dbf' size 10m; Tablespace created.
CDBOCP 切日志生归档
SQL> conn / as sysdba Connected. SQL> alter system switch logfile; System altered. SQL> / System altered. SQL> / System altered.
3. 执行全备
CDB2 目录库执行备份
RMAN> backup database format '/u01/app/oracle/backup/%s_%t_%U.full' tag='scuti';
4. 删除表空间
删除 CDBOCP：PDB1 上创建的表空间
SQL> alter session set container=pdb1; Session altered. SQL> select current_scn from v$database; CURRENT_SCN ----------- 7746014 SQL> drop tablespace askscuti including contents and datafiles; Tablespace dropped.
5. 执行PDB PITR
关闭 CDBOCP：PDB1数据库；
连接 CDB2 目录库进行时间点恢复。
SQL> alter pluggable database pdb1 close immediate; Pluggable database altered. RMAN> run{ set until scn 7746014; restore pluggable database pdb1; recover pluggable database pdb1 auxiliary destination='/u01/app/oracle/au'; alter pluggable database pdb1 open resetlogs; }
过程选段（44号物理数据文件被 Restore 出来）
... ... ... channel ORA_DISK_1: restoring datafile 00038 to /u01/app/oracle/oradata/CDBOCP/bbb01.dbf channel ORA_DISK_1: restoring datafile 00043 to /u01/app/oracle/oradata/CDBOCP/PDB1/rman01.dbf channel ORA_DISK_1: restoring datafile 00044 to /u01/app/oracle/oradata/CDBOCP/PDB1/askscuti01.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/259_1015418683_83u8c3pr_1_1.full channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/259_1015418683_83u8c3pr_1_1.full tag=SCUTI channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:16 ... ... ...
查看实际路径
SQL> !ls /u01/app/oracle/oradata/CDBOCP/PDB1/askscuti01.dbf /u01/app/oracle/oradata/CDBOCP/PDB1/askscuti01.dbf
查看控制文件
SQL> select file# "A",checkpoint_change# "B",name from v$datafile where con_id=3; A B NAME --- --------- ------------------------------------------------------- 9 7747033 /u01/app/oracle/oradata/CDBOCP/PDB1/system01.dbf 10 7747033 /u01/app/oracle/oradata/CDBOCP/PDB1/sysaux01.dbf 11 7747033 /u01/app/oracle/oradata/CDBOCP/PDB1/undotbs01.dbf 12 7747033 /u01/app/oracle/oradata/CDBOCP/PDB1/users01.dbf 21 7747033 /u01/app/oracle/oradata/CDBOCP/PDB1/tbs_c01.dbf 22 7747033 /u01/app/oracle/oradata/CDBOCP/PDB1/henry01.dbf 23 7747033 /u01/app/oracle/oradata/CDBOCP/PDB1/henry02.dbf 24 7747033 /u01/app/oracle/oradata/CDBOCP/PDB1/test01.dbf 25 7747033 /u01/app/oracle/oradata/CDBOCP/PDB1/test02.dbf 31 7747033 /u01/app/oracle/oradata/CDBOCP/PDB1/impdata01.dbf 37 7747033 /u01/app/oracle/oradata/CDBOCP/PDB1/flash_arc01.dbf A B NAME --- --------- ------------------------------------------------------- 38 7747033 /u01/app/oracle/oradata/CDBOCP/bbb01.dbf 43 7747033 /u01/app/oracle/oradata/CDBOCP/PDB1/rman01.dbf 44 0 /u01/app/oracle/product/12.2.0/db_1/dbs/MISSING00044
在PDB PITR后，物理文件已经被正确的还原到了原来位置，但是控制文件里面信息记录有误，可通过手工进行修改。
连接 CDBOCP：PDB1 将表空间脱机
SQL> alter session set container=pdb1; Session altered. SQL> alter tablespace askscuti offline; alter tablespace askscuti offline * ERROR at line 1: ORA-01191: file 44 is already offline - cannot do a normal offline ORA-01111: name for data file 44 is unknown - rename to correct file ORA-01110: data file 44: '/u01/app/oracle/product/12.2.0/db_1/dbs/MISSING00044'
更新控制文件中记录的数据文件信息
SQL> alter database rename file '/u01/app/oracle/product/12.2.0/db_1/dbs/MISSING00044' to '/u01/app/oracle/oradata/CDBOCP/PDB1/askscuti01.dbf'; Database altered.
再次对 44 号文件进行单独还原和恢复
RMAN> run{ restore datafile 44; recover datafile 44; } Starting restore at 2019-08-04 13:01:07 starting full resync of recovery catalog full resync complete using channel ORA_DISK_1 datafile 44 is already restored to file /u01/app/oracle/oradata/CDBOCP/PDB1/askscuti01.dbf restore not done; all files read only, offline, excluded, or already restored Finished restore at 2019-08-04 13:01:13 Starting recover at 2019-08-04 13:01:13 using channel ORA_DISK_1 starting media recovery media recovery complete, elapsed time: 00:00:01 Finished recover at 2019-08-04 13:01:15
将 CDBOCP：PDB1 表空间联机
SQL> alter tablespace askscuti online; Tablespace altered.
所以采用 Catalogue 针对 PDB 级别进行 PITR - 可以恢复出被DROP的表空间。
