现在ORACLE多租户使用越来越多，如果某个PDB的某个表空间中表出现了误操作，如何能快速处理呢？
我们知道，从ORACLE 11G开始, ORACLE就提供了表空间基于时间点的恢复，那么，如果是12C以上，使用了PDB，PDB中的表空间是否支持PITR呢 ？
目录
1.2 创建两个表空间 tsp_test1,tsp_test2
答案是肯定的，下午以实际例子说明：
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-timeList of tablespaces expected to have UNDO segments
Tablespace SYSTEM
Tablespace PDBORCL:SYSTEM
Tablespace UNDOTBS1
Tablespace PDBORCL:UNDOTBS1Creating 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 ORCLCDBOracle 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 successfullycontents 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 Scriptexecuting 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=DISKchannel 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:55sql 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 Scriptexecuting 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 fileStarting restore at 2024-05-19 10:46:00
using channel ORA_AUX_DISK_1creating 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:23datafile 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_.dbfcontents 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 Scriptexecuting 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_1Executing: 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 recoveryarchived 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:28database opened
contents of Memory Script:
{
sql clone 'alter pluggable database PDBORCL open';
}
executing Memory Scriptsql 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 Scriptsql 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:26RMAN>
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 OFFLINE7 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.youkuaiyun.com/cqsztech/article/details/138989790
扫一扫
1461
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
