PDB表空间如何实现基于时间点恢复PITR呢

本文主要是介绍PDB表空间如何实现基于时间点恢复PITR呢，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！

现在ORACLE多租户使用越来越多，如果某个PDB的某个表空间中表出现了误操作，如何能快速处理呢？

我们知道，从ORACLE 11G开始, ORACLE就提供了表空间基于时间点的恢复，那么，如果是12C以上，使用了PDB，PDB中的表空间是否支持PITR呢 ？

目录

1.模拟环境

1.1 环境说明

1.2 创建两个表空间 tsp_test1,tsp_test2

1.3 创建测试表

1.4 获取当前时间

2.模拟故障

3.使用TSPITR恢复表空间到故障之前时间

3.1 检查恢复表空间自包含性

3.2 连接到CDB$ROOT进行表空间恢复

3.3 设置恢复的表空间为读写

3.4 验证数据已经恢复

---

答案是肯定的，下午以实际例子说明：

1.模拟环境

1.1 环境说明

   ORACLE 19C 19.23+LINUX 9.4

   DB:ORCLDB

   PDB: pdborcl

   

SQL> show pdbs

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 PDBORCL                        READ WRITE NO
         。。。

1.2 创建两个表空间 tsp_test1,tsp_test2

SQL> conn sys/oracle_4U@192.168.133.120:1521/pdborcl as sysdba;

SQL> create tablespace tsp_test1 datafile '/u01/app/oracle/oradata/ORCLCDB/pdborcl/tsp_test1.dbf' size 10m ;

SQL> create tablespace tsp_test2 datafile '/u01/app/oracle/oradata/ORCLCDB/pdborcl/tsp_test2.dbf' size 10m ;

1.3 创建测试表

SQL> conn hr/hr@192.168.133.120:1521/pdborcl

SQL> create table dept2 tablespace tsp_test2 as select * from departments;

SQL> create table emp1 tablespace tsp_test1 as select * from emp;

SQL> create index idx_dept on dept2(department_id) tablespace tsp_test2;

SQL> alter table dept2  modify (department_id constraint pk_dept2 primary key);

SQL> alter table emp1 add constraint fk_emp1_deptid foreign key (department_id) 
          references dept2;

SQL> select count(*) from emp1;

  COUNT(*)
----------
       107

SQL> select count(*) from dept2;

  COUNT(*)
----------
        27

1.4 获取当前时间

SQL> select systimestamp from dual;

SYSTIMESTAMP
---------------------------------------------------------------------------
19-MAY-24 10.23.32.420449 AM +08:00

2.模拟故障

--截断表

SQL> truncate table emp1;

Table truncated.

--验证数据

SQL> select count(*) from dept2;

  COUNT(*)
----------
        27

SQL> select count(*) from emp1;

  COUNT(*)
----------
         0

SQL> exit

EMP1数据已经截断

3.使用TSPITR恢复表空间到故障之前时间

3.1 检查恢复表空间自包含性

SQL> CONN sys/oracle_4U@192.168.133.120:1521/pdborcl as sysdba
Connected.
SQL> exec dbms_tts.transport_set_check('TSP_TEST1',TRUE);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

VIOLATIONS
--------------------------------------------------------------------------------
ORA-39906: Constraint FK_EMP1_DEPTID between table HR.DEPT2 in tablespace TSP_TE
ST2 and table HR.EMP1 in tablespace TSP_TEST1.

--以上表明：只恢复 TSP_TEST1表空间不行，因为EMP1的外键引用了DEPT2的主键，而DEPT2在TSP_TEST2表空间，具体可以看  transport_set_violations 的内容。

--如果只想恢复某一个表空间，那么，要把相关联的表空间一起恢复，或者，去掉之间的关系。

--我们这里，把两个表空间一起恢复

SQL> exec dbms_tts.transport_set_check('TSP_TEST1,TSP_TEST2',TRUE);

PL/SQL procedure successfully completed.

SQL> select * from transport_set_violations;

no rows selected
 

--现在看来没有输出，说明两个表空间是自保函的，这个内容和以前的NONCDB是一样的。

3.2 连接到CDB$ROOT进行表空间恢复

--如果连接到PDB进行恢复，会报错RMAN-06617，如下：

RMAN> recover tablespace tsp_test1,tsp_test2
2>      until time "to_date('2024-05-19 10:23:32','yyyy-mm-dd hh24:mi:ss')"
3>     auxiliary destination '/tmp/aux';

Starting recover at 2024-05-19 10:36:25
using target database control file instead of recovery catalog
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 05/19/2024 10:36:26
RMAN-06617: UNTIL TIME (2024-05-19 10:23:32) is ahead of last NEXT TIME in archived logs (2024-05-19 09:49:55)

RMAN> exit

--连接到CDB$root后，不再报错。

[oracle@dbserver ~]$ rman target /

Recovery Manager: Release 19.0.0.0.0 - Production on Sun May 19 10:44:38 2024
Version 19.22.0.0.0

Copyright (c) 1982, 2019, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCLCDB (DBID=2931055914)

RMAN> recover tablespace pdborcl:tsp_test1,pdborcl:tsp_test2
2>      until time "to_date('2024-05-19 10:23:32','yyyy-mm-dd hh24:mi:ss')"
3>       auxiliary destination '/tmp/aux';
 

--自动构造辅助库，整个库恢复到指定时间

Starting recover at 2024-05-19 10:45:27
using target database control file instead of recovery catalog
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=398 device type=DISK
RMAN-05026: warning: presuming following set of tablespaces applies to specified point-in-time

List of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace PDBORCL:SYSTEM
Tablespace UNDOTBS1
Tablespace PDBORCL:UNDOTBS1

Creating automatic instance, with SID='jvmt'

initialization parameters used for automatic instance:
db_name=ORCLCDB
db_unique_name=jvmt_pitr_pdborcl_ORCLCDB
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_pdb_name_case_sensitive=false
_system_trig_enabled=FALSE
sga_target=2240M
processes=200
db_create_file_dest=/tmp/aux
log_archive_dest_1='location=/tmp/aux'
enable_pluggable_database=true
_clone_one_pdb_recovery=true
#No auxiliary parameter file used

starting up automatic instance ORCLCDB

Oracle instance started

Total System Global Area    2348807320 bytes

Fixed Size                     9180312 bytes
Variable Size                520093696 bytes
Database Buffers            1811939328 bytes
Redo Buffers                   7593984 bytes
Automatic instance created
Running TRANSPORT_SET_CHECK on recovery set tablespaces
TRANSPORT_SET_CHECK completed successfully

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2024-05-19 10:23:32','yyyy-mm-dd hh24:mi:ss')";
# restore the controlfile
restore clone controlfile;
 
# mount the controlfile
sql clone 'alter database mount clone database';
 
# archive current online log 
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
}
executing Memory Script

executing command: SET until clause

Starting restore at 2024-05-19 10:45:49
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=91 device type=DISK

channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ORCLCDB/AUTOBACKUP/2024_05_19/s_1169374577.287.1169374577
channel ORA_AUX_DISK_1: piece handle=+FRA/ORCLCDB/AUTOBACKUP/2024_05_19/s_1169374577.287.1169374577 tag=TAG20240519T101617
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:05
output file name=/tmp/aux/ORCLCDB/controlfile/o1_mf_m4lsz285_.ctl
Finished restore at 2024-05-19 10:45:55

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2024-05-19 10:23:32','yyyy-mm-dd hh24:mi:ss')";
plsql <<<--
declare
  sqlstatement       varchar2(512);
  pdbname            varchar2(128);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
   pdbname := 'pdborcl'; -- pdbname
  sqlstatement := 'alter tablespace '||  'TSP_TEST1' ||' offline immediate';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement, 0, pdbname);
exception
  when offline_not_needed then
    null;
end; >>>;
plsql <<<--
declare
  sqlstatement       varchar2(512);
  pdbname            varchar2(128);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
   pdbname := 'PDBORCL'; -- pdbname
  sqlstatement := 'alter tablespace '||  'TSP_TEST2' ||' offline immediate';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement, 0, pdbname);
exception
  when offline_not_needed then
    null;
end; >>>;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  9 to new;
set newname for clone datafile  4 to new;
set newname for clone datafile  11 to new;
set newname for clone datafile  3 to new;
set newname for clone datafile  10 to new;
set newname for clone tempfile  1 to new;
set newname for clone tempfile  3 to new;
set newname for datafile  21 to 
 "/u01/app/oracle/oradata/ORCLCDB/pdborcl/tsp_test1.dbf";
set newname for datafile  22 to 
 "/u01/app/oracle/oradata/ORCLCDB/pdborcl/tsp_test2.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 9, 4, 11, 3, 10, 21, 22;
 
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace TSP_TEST1 offline immediate

sql statement: alter tablespace TSP_TEST2 offline immediate

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed tempfile 1 to /tmp/aux/ORCLCDB/datafile/o1_mf_temp_%u_.tmp in control file
renamed tempfile 3 to /tmp/aux/ORCLCDB/107B00529A4E9350E0637885A8C036A8/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 2024-05-19 10:46:00
using channel ORA_AUX_DISK_1

creating datafile file number=21 name=/u01/app/oracle/oradata/ORCLCDB/pdborcl/tsp_test1.dbf
creating datafile file number=22 name=/u01/app/oracle/oradata/ORCLCDB/pdborcl/tsp_test2.dbf
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /tmp/aux/ORCLCDB/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /tmp/aux/ORCLCDB/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /tmp/aux/ORCLCDB/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ORCLCDB/BACKUPSET/2024_05_19/nnndf0_tag20240519t094853_0.262.1169372935
channel ORA_AUX_DISK_1: piece handle=+FRA/ORCLCDB/BACKUPSET/2024_05_19/nnndf0_tag20240519t094853_0.262.1169372935 tag=TAG20240519T094853
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:15
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00009 to /tmp/aux/ORCLCDB/107B00529A4E9350E0637885A8C036A8/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /tmp/aux/ORCLCDB/107B00529A4E9350E0637885A8C036A8/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00010 to /tmp/aux/ORCLCDB/107B00529A4E9350E0637885A8C036A8/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece +FRA/ORCLCDB/107B00529A4E9350E0637885A8C036A8/BACKUPSET/2024_05_19/nnndf0_tag20240519t094853_0.282.1169372965
channel ORA_AUX_DISK_1: piece handle=+FRA/ORCLCDB/107B00529A4E9350E0637885A8C036A8/BACKUPSET/2024_05_19/nnndf0_tag20240519t094853_0.282.1169372965 tag=TAG20240519T094853
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 2024-05-19 10:46:23

datafile 1 switched to datafile copy
input datafile copy RECID=133 STAMP=1169376383 file name=/tmp/aux/ORCLCDB/datafile/o1_mf_system_m4lsz95q_.dbf
datafile 9 switched to datafile copy
input datafile copy RECID=134 STAMP=1169376383 file name=/tmp/aux/ORCLCDB/107B00529A4E9350E0637885A8C036A8/datafile/o1_mf_system_m4lszr8d_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=135 STAMP=1169376383 file name=/tmp/aux/ORCLCDB/datafile/o1_mf_undotbs1_m4lsz962_.dbf
datafile 11 switched to datafile copy
input datafile copy RECID=136 STAMP=1169376383 file name=/tmp/aux/ORCLCDB/107B00529A4E9350E0637885A8C036A8/datafile/o1_mf_undotbs1_m4lszr8f_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=137 STAMP=1169376383 file name=/tmp/aux/ORCLCDB/datafile/o1_mf_sysaux_m4lsz960_.dbf
datafile 10 switched to datafile copy
input datafile copy RECID=138 STAMP=1169376383 file name=/tmp/aux/ORCLCDB/107B00529A4E9350E0637885A8C036A8/datafile/o1_mf_sysaux_m4lszr8b_.dbf

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2024-05-19 10:23:32','yyyy-mm-dd hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone 'PDBORCL' "alter database datafile 
 9 online";
sql clone "alter database datafile  4 online";
sql clone 'PDBORCL' "alter database datafile 
 11 online";
sql clone "alter database datafile  3 online";
sql clone 'PDBORCL' "alter database datafile 
 10 online";
sql clone 'PDBORCL' "alter database datafile 
 21 online";
sql clone 'PDBORCL' "alter database datafile 
 22 online";
# recover and open resetlogs
recover clone database tablespace  "pdborcl":"TSP_TEST1", "PDBORCL":"TSP_TEST2", "SYSTEM", "PDBORCL":"SYSTEM", "UNDOTBS1", "PDBORCL":"UNDOTBS1", "SYSAUX", "PDBORCL":"SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script

executing command: SET until clause

sql statement: alter database datafile  1 online

sql statement: alter database datafile  9 online

sql statement: alter database datafile  4 online

sql statement: alter database datafile  11 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  10 online

sql statement: alter database datafile  21 online

sql statement: alter database datafile  22 online

Starting recover at 2024-05-19 10:46:23
using channel ORA_AUX_DISK_1

Executing: alter database datafile 7 offline
Executing: alter database datafile 5, 6, 8 offline
Executing: alter database datafile 12, 19, 20 offline
Executing: alter database datafile 13, 14, 15, 16, 17, 18 offline
starting media recovery

archived log for thread 1 with sequence 2 is already on disk as file /u01/app/oracle/arch_log/1_2_1169372078.dbf
archived log for thread 1 with sequence 3 is already on disk as file /u01/app/oracle/arch_log/1_3_1169372078.dbf
archived log file name=/u01/app/oracle/arch_log/1_2_1169372078.dbf thread=1 sequence=2
archived log file name=/u01/app/oracle/arch_log/1_3_1169372078.dbf thread=1 sequence=3
media recovery complete, elapsed time: 00:00:02
Finished recover at 2024-05-19 10:46:28

database opened

contents of Memory Script:
{
sql clone 'alter pluggable database  PDBORCL open';
}
executing Memory Script

sql statement: alter pluggable database  PDBORCL open

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'pdborcl' 'alter tablespace 
 TSP_TEST1 read only';
sql clone 'PDBORCL' 'alter tablespace 
 TSP_TEST2 read only';
# create directory for datapump import
sql 'PDBORCL' "create or replace directory 
TSPITR_DIROBJ_DPDIR as ''
/tmp/aux''";
# create directory for datapump export
sql clone 'PDBORCL' "create or replace directory 
TSPITR_DIROBJ_DPDIR as ''
/tmp/aux''";
}
executing Memory Script

--使用传输表空间从辅助库导出 TSP_TEST1,TSP_TEST2 表空间

sql statement: alter tablespace  TSP_TEST1 read only

sql statement: alter tablespace  TSP_TEST2 read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/aux''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/tmp/aux''

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_jvmt_mtlo":  
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/INDEX_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
   EXPDP> Master table "SYS"."TSPITR_EXP_jvmt_mtlo" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_jvmt_mtlo is:
   EXPDP>   /tmp/aux/tspitr_jvmt_35558.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace TSP_TEST1:
   EXPDP>   /u01/app/oracle/oradata/ORCLCDB/pdborcl/tsp_test1.dbf
   EXPDP> Datafiles required for transportable tablespace TSP_TEST2:
   EXPDP>   /u01/app/oracle/oradata/ORCLCDB/pdborcl/tsp_test2.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_jvmt_mtlo" successfully completed at Sun May 19 10:47:36 2024 elapsed 0 00:00:49
Export completed

contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
# drop target tablespaces before importing them back
sql 'PDBORCL' 'drop tablespace 
 TSP_TEST1 including contents keep datafiles cascade constraints';
sql 'PDBORCL' 'drop tablespace 
 TSP_TEST2 including contents keep datafiles cascade constraints';
}
executing Memory Script

--使用传输表空间导入表空间 TSP_TEST1,TSP_TEST2

Oracle instance shut down

sql statement: drop tablespace  TSP_TEST1 including contents keep datafiles cascade constraints

sql statement: drop tablespace  TSP_TEST2 including contents keep datafiles cascade constraints

Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_jvmt_BFoz" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_jvmt_BFoz":  
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/INDEX
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/INDEX/STATISTICS/INDEX_STATISTICS
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/TABLE_STATISTICS
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/STATISTICS/MARKER
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_jvmt_BFoz" successfully completed at Sun May 19 10:48:23 2024 elapsed 0 00:00:32
Import completed

contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'pdborcl' 'alter tablespace 
 TSP_TEST1 read write';
sql 'pdborcl' 'alter tablespace 
 TSP_TEST1 offline';
sql 'PDBORCL' 'alter tablespace 
 TSP_TEST2 read write';
sql 'PDBORCL' 'alter tablespace 
 TSP_TEST2 offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script

sql statement: alter tablespace  TSP_TEST1 read write

sql statement: alter tablespace  TSP_TEST1 offline

sql statement: alter tablespace  TSP_TEST2 read write

sql statement: alter tablespace  TSP_TEST2 offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

Removing automatic instance
Automatic instance removed
auxiliary instance file /tmp/aux/ORCLCDB/107B00529A4E9350E0637885A8C036A8/datafile/o1_mf_temp_m4lt0cbg_.tmp deleted
auxiliary instance file /tmp/aux/ORCLCDB/datafile/o1_mf_temp_m4lt082d_.tmp deleted
auxiliary instance file /tmp/aux/ORCLCDB/onlinelog/o1_mf_3_m4lt049t_.log deleted
auxiliary instance file /tmp/aux/ORCLCDB/onlinelog/o1_mf_2_m4lt048v_.log deleted
auxiliary instance file /tmp/aux/ORCLCDB/onlinelog/o1_mf_1_m4lt0482_.log deleted
auxiliary instance file /tmp/aux/ORCLCDB/107B00529A4E9350E0637885A8C036A8/datafile/o1_mf_sysaux_m4lszr8b_.dbf deleted
auxiliary instance file /tmp/aux/ORCLCDB/datafile/o1_mf_sysaux_m4lsz960_.dbf deleted
auxiliary instance file /tmp/aux/ORCLCDB/107B00529A4E9350E0637885A8C036A8/datafile/o1_mf_undotbs1_m4lszr8f_.dbf deleted
auxiliary instance file /tmp/aux/ORCLCDB/datafile/o1_mf_undotbs1_m4lsz962_.dbf deleted
auxiliary instance file /tmp/aux/ORCLCDB/107B00529A4E9350E0637885A8C036A8/datafile/o1_mf_system_m4lszr8d_.dbf deleted
auxiliary instance file /tmp/aux/ORCLCDB/datafile/o1_mf_system_m4lsz95q_.dbf deleted
auxiliary instance file /tmp/aux/ORCLCDB/controlfile/o1_mf_m4lsz285_.ctl deleted
auxiliary instance file tspitr_jvmt_35558.dmp deleted
Finished recover at 2024-05-19 10:48:26

RMAN> 
 

3.3 设置恢复的表空间为读写

SQL> select c.name as container,tablespace_name,status from cdb_tablespaces t,v$containers c where t.con_id=c.con_id order by c.con_id;

CONTAINER                      TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ---------------------------
PDBORCL                        SYSTEM                           ONLINE
PDBORCL                        SYSAUX                           ONLINE
PDBORCL                        UNDOTBS1                       ONLINE
PDBORCL                        TEMP                                ONLINE
PDBORCL                        USERS                              ONLINE
PDBORCL                        TSP_TEST1                      OFFLINE
PDBORCL                        TSP_TEST2                      OFFLINE

7 rows selected.

SQL> alter tablespace tsp_test1 online;

Tablespace altered.

SQL> alter tablespace tsp_test2 online;

Tablespace altered.

SQL> select c.name as container,tablespace_name,status from cdb_tablespaces t,v$containers c where t.con_id=c.con_id order by c.con_id;

CONTAINER                      TABLESPACE_NAME                STATUS
------------------------------ ------------------------------ ---------------------------
PDBORCL                        SYSTEM                         ONLINE
PDBORCL                        SYSAUX                         ONLINE
PDBORCL                        UNDOTBS1                       ONLINE
PDBORCL                        TEMP                           ONLINE
PDBORCL                        USERS                          ONLINE
PDBORCL                        TSP_TEST1                      ONLINE
PDBORCL                        TSP_TEST2                      ONLINE
 

3.4 验证数据已经恢复

SQL> select count(*) from hr.emp1;

  COUNT(*)
----------
       107

SQL> select count(*) from hr.dept2;

  COUNT(*)
----------
        27

SQL> 

--通过确认，数据已经回来。

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

为了方便交流，我建立了一个微信群：水煮数据库，主要交流日常运维中用到的数据库相关问题，包含但不限于：ORACLE,PG，MYSQL，SQLSERVER，OB,TIDB，达梦，TDSQL，OPENGAUSS，人大金仓，GBASE等等，如果有兴趣交流，可以加我微信：zq24803366, 我可以拉你入群。
————————————————

版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
                        
原文链接：https://blog.csdn.net/cqsztech/article/details/138989790

这篇关于PDB表空间如何实现基于时间点恢复PITR呢的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！

---