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

2024-05-26 19:44

本文主要是介绍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呢的文章就介绍到这儿,希望我们推荐的文章对编程师们有所帮助!



http://www.chinasem.cn/article/1005413

相关文章

分布式锁在Spring Boot应用中的实现过程

《分布式锁在SpringBoot应用中的实现过程》文章介绍在SpringBoot中通过自定义Lock注解、LockAspect切面和RedisLockUtils工具类实现分布式锁,确保多实例并发操作... 目录Lock注解LockASPect切面RedisLockUtils工具类总结在现代微服务架构中,分布

Java使用Thumbnailator库实现图片处理与压缩功能

《Java使用Thumbnailator库实现图片处理与压缩功能》Thumbnailator是高性能Java图像处理库,支持缩放、旋转、水印添加、裁剪及格式转换,提供易用API和性能优化,适合Web应... 目录1. 图片处理库Thumbnailator介绍2. 基本和指定大小图片缩放功能2.1 图片缩放的

Python使用Tenacity一行代码实现自动重试详解

《Python使用Tenacity一行代码实现自动重试详解》tenacity是一个专为Python设计的通用重试库,它的核心理念就是用简单、清晰的方式,为任何可能失败的操作添加重试能力,下面我们就来看... 目录一切始于一个简单的 API 调用Tenacity 入门:一行代码实现优雅重试精细控制:让重试按我

Redis客户端连接机制的实现方案

《Redis客户端连接机制的实现方案》本文主要介绍了Redis客户端连接机制的实现方案,包括事件驱动模型、非阻塞I/O处理、连接池应用及配置优化,具有一定的参考价值,感兴趣的可以了解一下... 目录1. Redis连接模型概述2. 连接建立过程详解2.1 连php接初始化流程2.2 关键配置参数3. 最大连

Python实现网格交易策略的过程

《Python实现网格交易策略的过程》本文讲解Python网格交易策略,利用ccxt获取加密货币数据及backtrader回测,通过设定网格节点,低买高卖获利,适合震荡行情,下面跟我一起看看我们的第一... 网格交易是一种经典的量化交易策略,其核心思想是在价格上下预设多个“网格”,当价格触发特定网格时执行买

python设置环境变量路径实现过程

《python设置环境变量路径实现过程》本文介绍设置Python路径的多种方法:临时设置(Windows用`set`,Linux/macOS用`export`)、永久设置(系统属性或shell配置文件... 目录设置python路径的方法临时设置环境变量(适用于当前会话)永久设置环境变量(Windows系统

Python对接支付宝支付之使用AliPay实现的详细操作指南

《Python对接支付宝支付之使用AliPay实现的详细操作指南》支付宝没有提供PythonSDK,但是强大的github就有提供python-alipay-sdk,封装里很多复杂操作,使用这个我们就... 目录一、引言二、准备工作2.1 支付宝开放平台入驻与应用创建2.2 密钥生成与配置2.3 安装ali

Spring Security 单点登录与自动登录机制的实现原理

《SpringSecurity单点登录与自动登录机制的实现原理》本文探讨SpringSecurity实现单点登录(SSO)与自动登录机制,涵盖JWT跨系统认证、RememberMe持久化Token... 目录一、核心概念解析1.1 单点登录(SSO)1.2 自动登录(Remember Me)二、代码分析三、

PyCharm中配置PyQt的实现步骤

《PyCharm中配置PyQt的实现步骤》PyCharm是JetBrains推出的一款强大的PythonIDE,结合PyQt可以进行pythion高效开发桌面GUI应用程序,本文就来介绍一下PyCha... 目录1. 安装China编程PyQt1.PyQt 核心组件2. 基础 PyQt 应用程序结构3. 使用 Q

Java获取当前时间String类型和Date类型方式

《Java获取当前时间String类型和Date类型方式》:本文主要介绍Java获取当前时间String类型和Date类型方式,具有很好的参考价值,希望对大家有所帮助,如有错误或未考虑完全的地方,... 目录Java获取当前时间String和Date类型String类型和Date类型输出结果总结Java获取