目录
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的表空间。