Oracle 表空间时间点恢复(TSPITR)

最新推荐文章于 2022-11-12 23:22:03 发布
最新推荐文章于 2022-11-12 23:22:03 发布
阅读量852 收藏 2
点赞数
分类专栏： ORACLE 11G ORACLE 10G
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/jolly10/article/details/82386986
版权

    表空间时点恢复，是Oracle在基于冷备，热备恢复以外的一种以表空间为粒度的，不完全恢复的形式来将表空间恢复到过去某个特定的时间点的一种恢复方式。它整合了RMAN以及DataPump这2个备份恢复工具来实现时点恢复。

  表空间时点恢复适用用以下场景：

  错误的批处理作业或数据操作语言DML

  恢复数据定义语言（DDL）后丢失的数据操作，改变表的结构。不能使用闪回表将表倒回结构更改点之前，例如截断表(truncate)操作。

  恢复drop时使用了purge选项的表

  恢复存在逻辑错误的表

  恢复被删除的表空间，RMAN可以在被drop的表空间上面执行TSPITR

与全库级别闪回相比，表空间时点恢复停留在表空间级别，影响较全库闪回较小。其次，数据库闪回功能需要承担维护闪回日志开启的相关性能开销。

 

实际操作：

大概思路：数据库的全备以及所有归档都完整的情况下，truncate掉一张表的数据，通过tspitr来指定时间点恢复整个表空间，以达到恢复表的目的。

环境：RHEL 6.3 + Oracle 11.2.0.3

SQL> select * from v$version where rownum<2;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

确保是归档模式：

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

建立测试的表空间，tbs_tspitr1存放data,tbs_tspitr2存放index

SQL> create tablespace tbs_tspitr1 datafile '/u01/oradata/orcl/tbs_tspitr1_01.dbf' size 20m;

Tablespace created.


SQL> create tablespace tbs_tspitr2 datafile '/u01/oradata/orcl/tbs_tspitr2_01.dbf' size 20m;

Tablespace created.


SQL> create table tb_test tablespace tbs_tspitr1 as select * from all_tables;

Table created.


SQL> create unique index pk_tb_test on tb_test(OWNER,TABLE_NAME) tablespace tbs_tspitr2;

Index created.

SQL> alter table tb_test add primary key(OWNER,TABLE_NAME) using index pk_tb_test;

Table altered.


SQL> select count(1) from tb_test;

  COUNT(1)
----------
      4240

验证表空间的依赖性关系：
 

SQL> col obj1_owner for a10
SQL> col obj2_owner for a10
SQL> select OBJ1_OWNER, OBJ1_NAME, TS1_NAME, OBJ2_OWNER, OBJ2_NAME, TS2_NAME
  2  from TS_PITR_CHECK
  3  where (ts1_name = 'TBS_TSPITR1' and ts2_name != 'TBS_TSPITR1') or (ts1_name != 'TBS_TSPITR1' and ts2_nam                            e = 'TBS_TSPITR1');


OBJ1_OWNER OBJ1_NAME                      TS1_NAME                       OBJ2_OWNER OBJ2_NAME                      TS2_NAME
---------- ------------------------------ ------------------------------ ---------- ------------------------------ ------------------------------
SYS        TB_TEST                        TBS_TSPITR1                    SYS        PK_TB_TEST                     TBS_TSPITR2
SYS        TB_TEST                        TBS_TSPITR1                                                              -1

 

SQL> select OBJ1_OWNER, OBJ1_NAME, TS1_NAME, OBJ2_OWNER, OBJ2_NAME, TS2_NAME
  2  from TS_PITR_CHECK
  3  where (ts1_name in ('TBS_TSPITR1','TBS_TSPITR2') and ts2_name not in   ('TBS_TSPITR1','TBS_TSPITR2')) or (ts1_name not in ('TBS_TSPITR1','TBS_TSPITR2') and ts2_name in ('TBS_TSPITR1','TBS_TSPITR2'));

OBJ1_OWNER OBJ1_NAME                      TS1_NAME                       OBJ2_OWNER OBJ2_NAME                      TS2_NAME
---------- ------------------------------ ------------------------------ ---------- ------------------------------ ------------------------------
SYS        PK_TB_TEST                     TBS_TSPITR2                                                              -1
SYS        TB_TEST                        TBS_TSPITR1                                                              -1

也可以通过 DBMS_TTS.TRANSPORT_SET_CHECK检查

SQL>  BEGIN
  2  DBMS_TTS.TRANSPORT_SET_CHECK('TBS_TSPITR1', TRUE,TRUE);
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM  TRANSPORT_SET_VIOLATIONS;

VIOLATIONS
-------------------------------------------------------------------------------------------
ORA-39908: Index SYS.PK_TB_TEST in tablespace TBS_TSPITR2 enforces primary constraints  of table SYS.TB_TEST in tablespace TBS_TSPITR1.

 

SQL>  BEGIN
  2  DBMS_TTS.TRANSPORT_SET_CHECK('TBS_TSPITR1,TBS_TSPITR2', TRUE,TRUE);
  3  END;
  4  /

PL/SQL procedure successfully completed.

SQL> SELECT * FROM  TRANSPORT_SET_VIOLATIONS;

no rows selected

 必须这两个表空间同时恢复才会没有问题。

下面开始做一次全备份：

[oracle@qht115 rman_script]$ ./rman0.sh

 建立auxiliary所需要的恢复路径：

[oracle@qht115 rman_script]$ mkdir -p /u01/orabak/auxiliary

 删除表tb_test的内容，并记录下时间点：

SQL> set time on;
09:17:56 SQL> truncate table tb_test;

Table truncated.

09:19:32 SQL>  set time off;

 查询执行TSPITR后会丢失的对象

select owner, name, tablespace_name, creation_time
from TS_PITR_OBJECTS_TO_BE_DROPPED
where tablespace_name in ('TBS_TSPITR1','TBS_TSPITR2')
and creation_time > to_date('2018-09-05 09:17:56','yyyy-mm-dd hh24:mi:ss');

如果这个查询有数据的话，最好先expdp导出这些对象的备份，待恢复表空间后，再导入这些对象。
当然如果确定这些对象是没有用的，可以直接忽略。

开始全自动的恢复：

RMAN> recover tablespace tbs_tspitr1,tbs_tspitr2 until time "to_date('2018-09-05 09:17:56','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/u01/orabak/auxiliary';

Starting recover at 05-SEP-18
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=237 device type=DISK
allocated channel: ORA_DISK_2
channel ORA_DISK_2: SID=14 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 UNDOTBS1

--通过默认的参数建立clone的instance

Creating automatic instance, with SID='Ezsz'

initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=Ezsz_tspitr_ORCL
compatible=11.2.0
db_block_size=8192
db_files=200
sga_target=280M
processes=50
db_create_file_dest=/u01/orabak/auxiliary
log_archive_dest_1='location=/u01/orabak/auxiliary'
#No auxiliary parameter file used


starting up automatic instance ORCL

Oracle instance started

Total System Global Area     292278272 bytes

Fixed Size                     2227744 bytes
Variable Size                100663776 bytes
Database Buffers             184549376 bytes
Redo Buffers                   4837376 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('2018-09-05 09:17:56','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 05-SEP-18
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=58 device type=DISK
allocated channel: ORA_AUX_DISK_2
channel ORA_AUX_DISK_2: SID=10 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 /u01/RMAN0/c-1166343071-20180905-01
channel ORA_AUX_DISK_1: piece handle=/u01/RMAN0/c-1166343071-20180905-01 tag=TAG20180905T090849
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/orabak/auxiliary/ORCL/controlfile/o1_mf_frydc53k_.ctl
Finished restore at 05-SEP-18

sql statement: alter database mount clone database

sql statement: alter system archive log current

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

--将正式库的TBS_TSPITR1和TBS_TSPITR2 offline

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2018-09-05 09:17:56','yyyy-mm-dd hh24:mi:ss')";
plsql <<<-- tspitr_2
declare
  sqlstatement       varchar2(512);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
  sqlstatement := 'alter tablespace '||  'TBS_TSPITR1' ||' offline immediate';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement);
exception
  when offline_not_needed then
    null;
end; >>>;
plsql <<<-- tspitr_2
declare
  sqlstatement       varchar2(512);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
  sqlstatement := 'alter tablespace '||  'TBS_TSPITR2' ||' offline immediate';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement);
exception
  when offline_not_needed then
    null;
end; >>>;

--恢复datafiles到clone database

# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile  1 to new;
set newname for clone datafile  2 to new;
set newname for clone datafile  3 to new;
set newname for clone tempfile  1 to new;
set newname for datafile  8 to
 "/u01/oradata/orcl/tbs_tspitr1_01.dbf";
set newname for datafile  11 to
 "/u01/oradata/orcl/tbs_tspitr2_01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile  1, 2, 3, 8, 11;
switch clone datafile all;
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace TBS_TSPITR1 offline immediate

sql statement: alter tablespace TBS_TSPITR2 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

renamed tempfile 1 to /u01/orabak/auxiliary/ORCL/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 05-SEP-18
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2

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 00008 to /u01/oradata/orcl/tbs_tspitr1_01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00011 to /u01/oradata/orcl/tbs_tspitr2_01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/RMAN0/db0d4tcb6q1_420_1.bak
channel ORA_AUX_DISK_2: starting datafile backup set restore
channel ORA_AUX_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_2: restoring datafile 00001 to /u01/orabak/auxiliary/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_2: reading from backup piece /u01/RMAN0/db0d5tcb6tk_421_1.bak
channel ORA_AUX_DISK_1: piece handle=/u01/RMAN0/db0d4tcb6q1_420_1.bak tag=TAG20180905T085009
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:03
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 00002 to /u01/orabak/auxiliary/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/RMAN0/db0d6tcb6vb_422_1.bak
channel ORA_AUX_DISK_1: piece handle=/u01/RMAN0/db0d6tcb6vb_422_1.bak tag=TAG20180905T085009
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:35
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 00003 to /u01/orabak/auxiliary/ORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/RMAN0/db0d7tcb71c_423_1.bak
channel ORA_AUX_DISK_2: piece handle=/u01/RMAN0/db0d5tcb6tk_421_1.bak tag=TAG20180905T085009
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:01:13
channel ORA_AUX_DISK_1: piece handle=/u01/RMAN0/db0d7tcb71c_423_1.bak tag=TAG20180905T085009
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:25
Finished restore at 05-SEP-18

datafile 1 switched to datafile copy
input datafile copy RECID=30 STAMP=986031082 file name=/u01/orabak/auxiliary/ORCL/datafile/o1_mf_system_frydch5x_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=31 STAMP=986031082 file name=/u01/orabak/auxiliary/ORCL/datafile/o1_mf_undotbs1_frydcl46_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=32 STAMP=986031082 file name=/u01/orabak/auxiliary/ORCL/datafile/o1_mf_sysaux_fryddo82_.dbf

--recover clone database，之后resetlogs打开clone数据库

contents of Memory Script:
{
# set requested point in time
set until  time "to_date('2018-09-05 09:17:56','yyyy-mm-dd hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile  1 online";
sql clone "alter database datafile  2 online";
sql clone "alter database datafile  3 online";
sql clone "alter database datafile  8 online";
sql clone "alter database datafile  11 online";
# recover and open resetlogs
recover clone database tablespace  "TBS_TSPITR1", "TBS_TSPITR2", "SYSTEM", "UNDOTBS1", "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  2 online

sql statement: alter database datafile  3 online

sql statement: alter database datafile  8 online

sql statement: alter database datafile  11 online

Starting recover at 05-SEP-18
using channel ORA_AUX_DISK_1
using channel ORA_AUX_DISK_2

starting media recovery

archived log for thread 1 with sequence 22 is already on disk as file /u01/app/oracle/admin/orcl/flash_recovery_area/ORCL/archivelog/2018_09_05/o1_mf_1_22_frydcds2_.arc
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=20
channel ORA_AUX_DISK_1: reading from backup piece /u01/RMAN0/archdrtcb7kv_443_1.bak
channel ORA_AUX_DISK_2: starting archived log restore to default destination
channel ORA_AUX_DISK_2: restoring archived log
archived log thread=1 sequence=21
channel ORA_AUX_DISK_2: reading from backup piece /u01/RMAN0/archdstcb7l0_444_1.bak
channel ORA_AUX_DISK_1: piece handle=/u01/RMAN0/archdrtcb7kv_443_1.bak tag=TAG20180905T085948
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/orabak/auxiliary/1_20_985534347.dbf thread=1 sequence=20
channel clone_default: deleting archived log(s)
archived log file name=/u01/orabak/auxiliary/1_20_985534347.dbf RECID=436 STAMP=986031085
channel ORA_AUX_DISK_2: piece handle=/u01/RMAN0/archdstcb7l0_444_1.bak tag=TAG20180905T085948
channel ORA_AUX_DISK_2: restored backup piece 1
channel ORA_AUX_DISK_2: restore complete, elapsed time: 00:00:02
archived log file name=/u01/orabak/auxiliary/1_21_985534347.dbf thread=1 sequence=21
channel clone_default: deleting archived log(s)
archived log file name=/u01/orabak/auxiliary/1_21_985534347.dbf RECID=435 STAMP=986031085
archived log file name=/u01/app/oracle/admin/orcl/flash_recovery_area/ORCL/archivelog/2018_09_05/o1_mf_1_22_frydcds2_.arc thread=1 sequence=22
media recovery complete, elapsed time: 00:00:05
Finished recover at 05-SEP-18

database opened

--将clone的tablespace设为readonly,在正式库和clone库都建立一个dump_dir，以用来expdp和impdp操作

contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace  TBS_TSPITR1 read only';
sql clone 'alter tablespace  TBS_TSPITR2 read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/orabak/auxiliary''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/orabak/auxiliary''";
}
executing Memory Script

sql statement: alter tablespace  TBS_TSPITR1 read only

sql statement: alter tablespace  TBS_TSPITR2 read only

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/orabak/auxiliary''

sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/orabak/auxiliary''

--expdp操作

Performing export of metadata...
   EXPDP> Starting "SYS"."TSPITR_EXP_Ezsz":
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/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/TABLE_STATISTICS
   EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   EXPDP> Master table "SYS"."TSPITR_EXP_Ezsz" successfully loaded/unloaded
   EXPDP> ******************************************************************************
   EXPDP> Dump file set for SYS.TSPITR_EXP_Ezsz is:
   EXPDP>   /u01/orabak/auxiliary/tspitr_Ezsz_31484.dmp
   EXPDP> ******************************************************************************
   EXPDP> Datafiles required for transportable tablespace TBS_TSPITR1:
   EXPDP>   /u01/oradata/orcl/tbs_tspitr1_01.dbf
   EXPDP> Datafiles required for transportable tablespace TBS_TSPITR2:
   EXPDP>   /u01/oradata/orcl/tbs_tspitr2_01.dbf
   EXPDP> Job "SYS"."TSPITR_EXP_Ezsz" successfully completed at 09:35:58
Export completed

--删除正式库的指定表空间

contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
# drop target tablespaces before importing them back
sql 'drop tablespace  TBS_TSPITR1 including contents keep datafiles';
sql 'drop tablespace  TBS_TSPITR2 including contents keep datafiles';
}
executing Memory Script

database closed
database dismounted
Oracle instance shut down

sql statement: drop tablespace  TBS_TSPITR1 including contents keep datafiles

sql statement: drop tablespace  TBS_TSPITR2 including contents keep datafiles

--impdp操作

Performing import of metadata...
   IMPDP> Master table "SYS"."TSPITR_IMP_Ezsz" successfully loaded/unloaded
   IMPDP> Starting "SYS"."TSPITR_IMP_Ezsz":
   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/TABLE_STATISTICS
   IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
   IMPDP> Job "SYS"."TSPITR_IMP_Ezsz" successfully completed at 09:38:53
Import completed

--将导入的表空间设为read write并且offline

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

sql statement: alter tablespace  TBS_TSPITR1 read write

sql statement: alter tablespace  TBS_TSPITR1 offline

sql statement: alter tablespace  TBS_TSPITR2 read write

sql statement: alter tablespace  TBS_TSPITR2 offline

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

--最后移除clone instnace的相关数据

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/orabak/auxiliary/ORCL/datafile/o1_mf_temp_frydk0tb_.tmp deleted
auxiliary instance file /u01/orabak/auxiliary/ORCL/onlinelog/o1_mf_6_frydjpdj_.log deleted
auxiliary instance file /u01/orabak/auxiliary/ORCL/onlinelog/o1_mf_5_frydjhrt_.log deleted
auxiliary instance file /u01/orabak/auxiliary/ORCL/onlinelog/o1_mf_4_frydj946_.log deleted
auxiliary instance file /u01/orabak/auxiliary/ORCL/onlinelog/o1_mf_3_frydj2cj_.log deleted
auxiliary instance file /u01/orabak/auxiliary/ORCL/onlinelog/o1_mf_2_frydhw24_.log deleted
auxiliary instance file /u01/orabak/auxiliary/ORCL/onlinelog/o1_mf_1_frydholy_.log deleted
auxiliary instance file /u01/orabak/auxiliary/ORCL/datafile/o1_mf_sysaux_fryddo82_.dbf deleted
auxiliary instance file /u01/orabak/auxiliary/ORCL/datafile/o1_mf_undotbs1_frydcl46_.dbf deleted
auxiliary instance file /u01/orabak/auxiliary/ORCL/datafile/o1_mf_system_frydch5x_.dbf deleted
auxiliary instance file /u01/orabak/auxiliary/ORCL/controlfile/o1_mf_frydc53k_.ctl deleted
Finished recover at 05-SEP-18

恢复完成后，最好是备份一下恢复出来的表空间

RMAN> backup tablespace tbs_tspitr1,tbs_tspitr2 format '/u01/RMAN0/tbs_%d_%U';

 

SQL> select tablespace_name,status from dba_tablespaces where tablespace_name like '%TSPI%';

TABLESPACE_NAME                STATUS
------------------------------ ---------
TBS_TSPITR1                    OFFLINE
TBS_TSPITR2                    OFFLINE

 将表空间online后检查数据：

SQL> alter tablespace TBS_TSPITR1 online;

Tablespace altered.

SQL> alter tablespace TBS_TSPITR2 online;

Tablespace altered.

SQL> select count(1) from tb_test;

  COUNT(1)
----------
      4240

 数据都已正确恢复！

 

zuozhiji
关注
专栏目录
oracle 恢复用户表空间,恢复Oracle表空间的方法
weixin_42524703的博客
04-04 1293
学习Oracle时，你可能会遇到oracle表空间恢复问题，这里将介绍Oracle表空间恢复问题的解决方法，在这里拿出来和大家分享一下。Oracle表空间恢复方案一。用户表空间错误：在启动时出现ora-01157,ora-01110或操作系统级错误例如ora-07360, 在关闭数据库(使用shutdownnormal或shutdown immediate) 时将导致错误ora -01116,or...
Oracle DB 执行表空间时间点恢复
一抹曦阳-Oracle
10-13 6781
• 列出在执行表空间时间点恢复(TSPITR) 时会发生的操作 • 阐释TSPITR 使用的术语的定义 • 确定适合将TSPITR 用作解决方案的情况 • 确定时间点恢复的正确目标时间 • 确定不能使用TSPITR 的情况以及解决方法 • 执行自动TSPITR 表空间时间点恢复(TSPITR)：概念 • 通过执行TSPITR 可将一个或多个表空间快速恢复到以前的某个时间。
Oracle基于时间点的表空间恢复
weixin_34187822的博客
07-10 276
TSPITR（表空间时间点恢复）用于将一个或多个表空间恢复到过去某个时间点的状态，而其他表空间仍然保持现有状态。TSPITR 相关的概念和术语：（1） TSPITR （Tablespace Point-In-Time Recover）。TSPITR 是表空间时间点恢复的英文缩写格式，它表示将一个或多个表空间恢复到过去时间点的状态，而其他表空间仍然保持现有状态。（2） TSPI...
Oracle 表空间时点恢复(TSPITR)
乐沙弥的世界
04-25 5303
表空间时点恢复，是Oracle在基于冷备，热备恢复以外的一种以表空间为粒度的，不完全恢复的形式来将表空间恢复到过去某个特定的时间点的一种恢复方式。它整合了RMAN以及DataPump这2个备份恢复工具来实现时点恢复。那它具体的过程和逻辑是怎样的？下文是其具体的描述。
Oracle表空间时点恢复(TSPITR)详细解析
"Oracle 表空间时点恢复(TSPITR)是一种高级恢复技术，它允许用户以表空间为单位，将数据恢复到特定时间点。这种方法结合了RMAN（恢复管理器）和DataPump的功能，适用于错误数据操作、DDL误删等情况，且其影响范围比...
【RMAN】TSPITR表空间基于时间点的恢复.pdf
07-31
TSPITR是Oracle数据库恢复技术中的一项高级特性，它允许我们把一个或多个表空间恢复到数据库中其他部分所处时间点的过去状态，而不影响数据库的其他部分。这种技术特别适用于以下场景：如果某个表空间遭到破坏或者...
执行表空间时间点恢复(TSPITR)详解
"les_09_TSPITR.ppt - 讲解了如何执行表空间时间点恢复(TSPITR)，这是一种恢复技术，能够快速将一个或多个表空间恢复到过去的某个时间点，而不影响数据库中的其他表空间或对象。课程涵盖了TSPITR的操作流程、相关...
oracle表空间恢复
weixin_34212189的博客
03-18 119
如果误删除了一个表空间中test的数据文件test.dbf，然后下次重新启动数据库的时候发 数据库不能打开了（open），老是报错：SQL> alter database open;alter database open*ERROR at line 1:ORA-01157: cannot identify/lock data file 6 - see DBW...
ORACLE表空间恢复方案
08-10
主要描述几种方式下得数据恢复 一）用户表空间 　　 　　错误: 在启动数据库时出现ORA-01157,ORA-01110或操作系统级错误例如ORA-07360, 在关闭数据库(使用shutdown normal或shutdown immediate) 时将导致错误ORA -01116,ORA-01110以及操作系统级错误ORA-07368 　　 　　解决: 以下有两种解决方案: 　　 　　1.用户的表空间可以被轻易地重建 　　即最近导出的对象是可用的或表空间中的对象可以被轻易地重建等. 　　在这种情况下,最简单的方法是offline并删除该数据文件,删除表空间并重建表空间以及所有的对象. 　　svrmgrl> startup mount 　　svrmgrl> alter database datafile filename offline drop; 　　svrmgrl> alter database open; 　　svrmgrl> drop tablespace tablespace_name including contents; 　　重建表空间及所有对象.
表空间时间点恢复
爱肯的专栏
03-18 558
表空间时间点恢复 以下部分内容适只用于10g一、TSPITR简介   TSPITR（表空间时间点恢复）用于将一个或多个表空间恢复到过去某个时间点的状态，而其他表空间仍然保持现有状态。   (1)TSPITR(Tablespace Point-In-Time Recovery)，只适用于ARCHIVELOG模式。   (2)TSPITR实现方法，建议使用RMAN实现表空间时间点恢复。   (3)
oracle 时间点,Oracle表空间时间点恢复技术TSPITR
weixin_39687786的博客
04-02 157
假定Oracle数据库发生了以下情形：1、表空间中，某个表的重要数据被破坏或删除。2、误用DDL语言更改了表空间中的一个或多个表的结构，因此无法使用闪回来恢复这些表。3、表被误删，并且已不在回收站中，如使用了带purge选项的删表操作。那么我们该如何恢复呢？一种可行的方法就是利用Oracle的表空间时间点恢复技术TSPITR(Tablespace Point In Time Recover)，这是...
Oracle Study案例之--基于表空间的时间点恢复（TSPITR)
weixin_34319817的博客
12-02 133
Oracle Study案例之--基于表空间的时间点恢复（TSPITR) TSPITR(表空间时间点恢复)用于将一个或多个表空间恢复到过去某个时间点的状态，而其他表空间仍然保持现有状态。TSPITR 相关的概念和术语：(1) TSPITR (Tablespace Point-In-Time Recover)。TSPITR 是表空间时间点恢复的英文缩写格式，它表示将一...
如何恢复oracle表空间,ORACLE表空间恢复方案
weixin_33646989的博客
04-08 865
一、 用户表空间错误： 在启动数据库时出现ORA-01157,ORA-01110或操作系统级错误例如ORA-07360,在关闭数据库(使用shutdown normal或shutdown immediate) 时将导致错误ORA -01116,ORA-01110以及操作系统级错误ORA-07368解决，以下有两种解决方案：1.用户的表空间可以被轻易地重建即最近导出的对象是可用的或表空间中的对象可以...
Oracle-表空间基于时间点恢复(TSPITR)
最新发布
sinat_36757755的博客
11-12 964
Oracle在10G推出了tablespace point-in-time recovery(TSPITR)基于表空间的时间点恢复，可以快速的在目标库上根据备份恢复一个或多个表空间到指定的时间点，恢复期间不影响其他表空间的使用以及对象，可以快速的恢复误删数据。
Oracle 表空间时间点恢复 (TSPITR)
demonson的专栏
10-10 780
表空间时间点恢复 (TSPITR)： 概念： 通过执行 TSPITR 可将一个或多个表空间快速恢复到以前的某个时间。 执行 TSPITR 并不影响数据库中其它表空间或对象的状态。 术语： 目标时间：表空间恢复到的时间点或 SCN 恢复集：组成要恢复的表空间的数据文件 辅助集：对恢复集执行 TSPITR 时所必需的，但不属于恢复集的数据文件。 辅助集通常包括： SYST
oracle 恢复目录 表空间
Jane
06-25 1279
假如已经有一个新建的数据库,数据名为book1  建立恢复目录表空间sqlplus "/as sysdba"sql>create tablespace rman       2  datafile '%oracle_base%/oradata/book/rman.dbf      3  size 200M;2  建立恢复目录所有者sql>create user rman identified by rman     2  default tablespace rman;3   授予恢复目录所有者角色sql>
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值