文档课题：TSPITR基于时间点的表空间自动恢复——恢复truncate后的单表.
系统：centos 7.9
数据库：oracle 11.2.0.4
相关知识点：
a、TSPITR基于时间点的表空间恢复用于将一个或多个表空间恢复到过去某个时间点，而其它表空间仍保持原有状态.
b、logminer的简单应用.
说明：此实验目标是恢复truncate后的单表，不过存在以下问题：
truncate表后，若对该表所在的表空间的其它表做过相关dml或ddl操作，该如何恢复此类表？
1、数据库信息
[oracle@leo-11g-ogg ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Sat Dec 24 09:22:25 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@orcl> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE 11.2.0.4.0 Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production
SYS@orcl> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/oradata/archivelog
Oldest online log sequence 65
Next log sequence to archive 72
Current log sequence 72
SYS@orcl> show parameter name
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
cell_offloadgroup_name string
db_file_name_convert string
db_name string orcl
db_unique_name string orcl
global_names boolean FALSE
instance_name string orcl
lock_name_space string
log_file_name_convert string
processor_group_name string
service_names string orcl
TSPITR的相关知识点归纳
TSPITR简介
2、实验操作
2.1、数据备份
RMAN> delete noprompt backup;
RMAN> delete noprompt copy;
RMAN> backup database;
Starting backup at 24-DEC-22
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/orcl/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/orcl/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/orcl/undotbs01.dbf
input datafile file number=00005 name=/u01/app/oracle/oradata/orcl/leo_ts01.dbf
input datafile file number=00006 name=/u01/app/oracle/oradata/orcl/tcp_acc01.dbf
input datafile file number=00007 name=/u01/app/oracle/oradata/orcl/tcp_fin01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/orcl/users01.dbf
channel ORA_DISK_1: starting piece 1 at 24-DEC-22
channel ORA_DISK_1: finished piece 1 at 24-DEC-22
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2022_12_24/o1_mf_nnndf_TAG20221224T094143_ktdp6q9o_.bkp tag=TAG20221224T094143 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 24-DEC-22
channel ORA_DISK_1: finished piece 1 at 24-DEC-22
piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2022_12_24/o1_mf_ncsnf_TAG20221224T094143_ktdp6zdq_.bkp tag=TAG20221224T094143 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 24-DEC-22
--查看备份集
RMAN> list backupset;
List of Backup Sets
===================
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
14 Full 1.09G DISK 00:00:04 24-DEC-22
BP Key: 14 Status: AVAILABLE Compressed: NO Tag: TAG20221224T094143
Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2022_12_24/o1_mf_nnndf_TAG20221224T094143_ktdp6q9o_.bkp
List of Datafiles in backup set 14
File LV Type Ckp SCN Ckp Time Name
---- -- ---- ---------- --------- ----
1 Full 3233945 24-DEC-22 /u01/app/oracle/oradata/orcl/system01.dbf
2 Full 3233945 24-DEC-22 /u01/app/oracle/oradata/orcl/sysaux01.dbf
3 Full 3233945 24-DEC-22 /u01/app/oracle/oradata/orcl/undotbs01.dbf
4 Full 3233945 24-DEC-22 /u01/app/oracle/oradata/orcl/users01.dbf
5 Full 3233945 24-DEC-22 /u01/app/oracle/oradata/orcl/leo_ts01.dbf
6 Full 3233945 24-DEC-22 /u01/app/oracle/oradata/orcl/tcp_acc01.dbf
7 Full 3233945 24-DEC-22 /u01/app/oracle/oradata/orcl/tcp_fin01.dbf
BS Key Type LV Size Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
15 Full 9.42M DISK 00:00:01 24-DEC-22
BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20221224T094143
Piece Name: /u01/app/oracle/fast_recovery_area/ORCL/backupset/2022_12_24/o1_mf_ncsnf_TAG20221224T094143_ktdp6zdq_.bkp
SPFILE Included: Modification time: 24-DEC-22
SPFILE db_unique_name: ORCL
Control File Included: Ckp SCN: 3233947 Ckp time: 24-DEC-22
RMAN> report schema;
Report of database schema for database with db_unique_name ORCL
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 780 SYSTEM *** /u01/app/oracle/oradata/orcl/system01.dbf
2 540 SYSAUX *** /u01/app/oracle/oradata/orcl/sysaux01.dbf
3 485 UNDOTBS1 *** /u01/app/oracle/oradata/orcl/undotbs01.dbf
4 5 USERS *** /u01/app/oracle/oradata/orcl/users01.dbf
5 100 LEO_TS *** /u01/app/oracle/oradata/orcl/leo_ts01.dbf
6 20 TSP_ACC *** /u01/app/oracle/oradata/orcl/tcp_acc01.dbf
7 20 TSP_FIN *** /u01/app/oracle/oradata/orcl/tcp_fin01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 54 TEMP 32767 /u01/app/oracle/oradata/orcl/temp01.dbf
RMAN> list backupset summary;
List of Backups
===============
Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag
------- -- -- - ----------- --------------- ------- ------- ---------- ---
14 B F A DISK 24-DEC-22 1 1 NO TAG20221224T094143
15 B F A DISK 24-DEC-22 1 1 NO TAG20221224T094143
2.2、模拟异常
2.2.1、建表和索引
--LEO_TS表空间下建测试表，并给其中一个表创建索引且指向users表空间.
SYS@orcl> select name from v$tablespace;
NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
USERS
TEMP
LEO_TS
TSP_ACC
TSP_FIN
8 rows selected.
SYS@orcl> alter system switch logfile;
System altered.
SYS@orcl> alter system switch logfile;
System altered.
SYS@orcl> create table leo.tspitr_emp tablespace leo_ts as select * from scott.emp;
Table created.
SYS@orcl> create table leo.tspitr_emp1 tablespace leo_ts as select * from scott.emp where rownum<=2;
Table created.
--创建索引指向users表空间
SYS@orcl > create index leo_tspitr_test_index on leo.tspitr_emp(empno) tablespace users;
Index created.
SYS@orcl > set line 200
SYS@orcl > select * from leo.tspitr_emp
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7369 SMITH CLERK 7902 1980-12-17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981-02-20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981-02-22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981-04-02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981-09-28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981-05-01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981-06-09 00:00:00 2450 10
7788 SCOTT ANALYST 7566 1987-04-19 00:00:00 3000 20
7839 KING PRESIDENT 1981-11-17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981-09-08 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 1987-05-23 00:00:00 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
7900 JAMES CLERK 7698 1981-12-03 00:00:00 950 30
7902 FORD ANALYST 7566 1981-12-03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982-01-23 00:00:00 1300 10
14 rows selected.
2.2.2、truncate表
注意：执行truncate前先切换下日志，否则truncate操作和备份有可能处在同一scn，导致恢复时找不到备份文件，报错RMAN-06023、RMAN-06026.
SYS@orcl 10:13:18> alter system switch logfile;
System altered.
SYS@orcl 10:19:37> set line 200
SYS@orcl 10:19:45> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TI NEXT_CHANGE# NEXT_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- -------- ------------ --------
1 1 74 52428800 512 1 YES ACTIVE 3234061 09:45:08 3235739 10:19:30
2 1 72 52428800 512 1 YES INACTIVE 3229699 09:19:29 3234056 09:44:59
3 1 73 52428800 512 1 YES INACTIVE 3234056 09:44:59 3234061 09:45:08
11 1 75 52428800 512 2 NO CURRENT 3235739 10:19:30 2.8147E+14
12 1 68 52428800 512 2 YES INACTIVE 3193114 11:27:02 3200620 14:51:46
13 1 69 52428800 512 2 YES INACTIVE 3200620 14:51:46 3206240 17:16:24
14 1 70 52428800 512 2 YES INACTIVE 3206240 17:16:24 3206704 17:29:54
15 1 71 52428800 512 2 YES INACTIVE 3206704 17:29:54 3229699 09:19:29
8 rows selected.
SYS@orcl 10:19:55> truncate table leo.tspitr_emp;
Table truncated.
SYS@orcl 10:20:54> select * from leo.tspitr_emp;
no rows selected
2.2.3、新建表以及新增行
--在truncate表空间leo_ts中的表leo.tspitr_emp后，再在该表空间新增表以及给leo.tspitr_emp1表增加行.
SYS@orcl 10:22:33> create table leo.tspitr_emp2 tablespace leo_ts as select * from scott.emp;
Table created.
SYS@orcl 10:23:32> create table leo.tspitr_emp3 tablespace leo_ts as select * from scott.emp;
Table created.
SYS@orcl 10:23:48> create table leo.tspitr_emp4 tablespace leo_ts as select * from scott.emp;
Table created.
SYS@orcl 10:24:01> select * from leo.tspitr_emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 00:00:00 800 20
7499 ALLEN SALESMAN 7698 00:00:00 1600 300 30
SYS@orcl 10:30:08> insert into leo.tspitr_emp1 select * from scott.emp where rownum<=5;
5 rows created.
SYS@orcl 10:31:59> commit;
Commit complete.
SYS@orcl 10:32:06> select * from leo.tspitr_emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 00:00:00 800 20
7499 ALLEN SALESMAN 7698 00:00:00 1600 300 30
7369 SMITH CLERK 7902 00:00:00 800 20
7499 ALLEN SALESMAN 7698 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 00:00:00 1250 500 30
7566 JONES MANAGER 7839 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 00:00:00 1250 1400 30
7 rows selected.
3、tspitr恢复
3.1、truncate时间确认
3.1.1、logminer方案
说明：由于测试库redo较少，从v$log判断truncate操作在75号日志中.此处采用logminer找回truncate误操作的具体时间.生产环境恢复时需确认到准确的时间点，以减少数据的丢失.
--确认该时间段的归档日志，其实此处可以直接分析redo日志，并不一定非要分析归档日志.
SYS@orcl 10:47:57> select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') as TIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",NAME from v$archived_log where first_time between to_date('2022-12-24 10','yyyy-mm-dd hh24') and to_date('2022-12-24 11','yyyy-mm-dd hh24') and dest_id=1 group by first_time,name,sequence# order by 1 desc;
no rows selected
SYS@orcl 10:56:34> alter system switch logfile;
System altered.
SYS@orcl 10:57:03> col name for a60
SYS@orcl 10:57:28> select sequence#,to_char(first_time,'yyyy-mm-dd hh24:mi:ss') as TIME,round(sum(blocks*block_size)/1024/1024) as "Size(M)",NAME from v$archived_log where first_time between to_date('2022-12-24 10','yyyy-mm-dd hh24') and to_date('2022-12-24 11','yyyy-mm-dd hh24') and dest_id=1 group by first_time,name,sequence# order by 1 desc;
SEQUENCE# TIME Size(M) NAME
---------- ------------------- ---------- ------------------------------------------------------------
75 2022-12-24 10:19:30 1 /u01/app/oracle/oradata/archivelog/1_75_1122831323.dbf
--进行归档日志分析.
SYS@orcl 10:57:29> execute dbms_logmnr.add_logfile('/u01/app/oracle/oradata/archivelog/1_75_1122831323.dbf',dbms_logmnr.new);
PL/SQL procedure successfully completed.
SYS@orcl 11:03:41> exec dbms_logmnr.start_logmnr(options=>dbms_logmnr.dict_from_online_catalog);
PL/SQL procedure successfully completed.
SYS@orcl 11:06:57> col sql_redo for a50
SYS@orcl 11:07:12> select a.scn,a.timestamp,a.sql_redo from v$logmnr_contents a where table_name='TSPITR_EMP' and operation='DDL' order by a.scn;
SCN TIMESTAM SQL_REDO
---------- -------- --------------------------------------------------
3235800 10:20:54 truncate table leo.tspitr_emp;
说明：由此确认到执行truncate的时间为10:20:54.
3.1.2、dba_objects方案
也可以通过视图dba_objects中的LAST_DDL_TIME字段来确认truncate时间,如下所示：
SYS@orcl 10:39:07> select owner,object_name,object_type,to_char(created,'yyyy-mm-dd hh24:mi:ss'),to_char(last_ddl_time,'yyyy-mm-dd hh24:mi:ss') from dba_objects where object_name='TSPITR_EMP';
OWNER OBJECT_NAME OBJECT_TYPE TO_CHAR(CREATED,'YY TO_CHAR(LAST_DDL_TI
---------- --------------- ------------------- ------------------- -------------------
LEO TSPITR_EMP TABLE 2022-12-24 09:51:32 2022-12-24 10:20:54
3.1.3、trace日志
--也可以在trace日志中查看到具体的truncate时间.
Sat Dec 24 10:20:54 2022
truncate table leo.tspitr_emp
说明：可以看出执行truncate的时间为10:20:54.
3.2、自包含检查
--方法a.
执行tspitr之前的检查表空间LEO_TS是否自包含.
SYS@orcl 11:17:09> begin
2 sys.dbms_tts.transport_set_check('LEO_TS',true,true);
3 end;
4 /
PL/SQL procedure successfully completed.
SYS@orcl 11:19:30> col violations for a120
SYS@orcl 11:19:38> select * from sys.transport_set_violations
VIOLATIONS
------------------------------------------------------------------------------------------------------------------------
ORA-39907: Index SYS.LEO_TSPITR_TEST_INDEX in tablespace USERS points to table LEO.TSPITR_EMP in tablespace LEO_TS.
--方法b.
SYS@orcl 11:25:09> select obj1_owner,obj1_name,obj1_type,ts1_name,obj2_name,obj2_type,obj2_owner,ts2_name,reason from ts_pitr_check where ts1_name='LEO_TS';
OBJ1_OWNER OBJ1_NAME OBJ1_TYPE TS1_NAME OBJ2_NAME OBJ2_TYPE OBJ2_OWNER TS2_NAME REASON
--------------- --------------- ---------- ---------- ------------------------- --------------- --------------- --------------- ------------------------------------------------------------------------
LEO TSPITR_EMP TABLE LEO_TS LEO_TSPITR_TEST_INDEX INDEX SYS USERS Tables and associated indexes not fully contained in the recovery set
说明：存在索引LEO_TSPITR_TEST_INDEX在users表空间.
--查索引对应的表信息.
SYS@orcl 11:40:37> col table_owner for a15
SYS@orcl 11:41:10> col index_type for a15
SYS@orcl 11:41:17> col table_name for a15
SYS@orcl 11:41:22> select INDEX_NAME,INDEX_TYPE,TABLE_OWNER,TABLE_NAME,TABLE_TYPE from user_indexes where index_name='LEO_TSPITR_TEST_INDEX';
INDEX_NAME INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE
------------------------------ --------------- --------------- --------------- -----------
LEO_TSPITR_TEST_INDEX NORMAL LEO TSPITR_EMP TABLE
--查索引对应的表以及列.
SYS@orcl 11:42:28> col column_name for a30
SYS@orcl 11:42:42> select index_name,table_name,column_name from user_ind_columns where index_name='LEO_TSPITR_TEST_INDEX';
INDEX_NAME TABLE_NAME COLUMN_NAME
------------------------------ --------------- ------------------------------
LEO_TSPITR_TEST_INDEX TSPITR_EMP EMPNO
3.2、自包含处理
先删除此索引，等恢复完成后在重建该索引.
SYS@orcl 11:42:47> drop index LEO_TSPITR_TEST_INDEX;
Index dropped.
SYS@orcl 11:44:32> begin
2 sys.dbms_tts.transport_set_check('LEO_TS',true,true);
3 end;
4 /
PL/SQL procedure successfully completed.
SYS@orcl 11:45:10> select * from sys.transport_set_violations;
no rows selected
说明：自包含问题解决.
3.3、查将被删除对象
--查询执行tspitr后将被删除的对象信息，此处其实还应该查出在执行truncate表LEO.TSPITR_EMP后做过修改的数据库表对象，如表LEO.TSPITR_EMP1.
SYS@orcl 2022-12-24 11:50:42> col name for a15
SYS@orcl 2022-12-24 11:51:15> select * from sys.ts_pitr_objects_to_be_dropped a where a.tablespace_name='LEO_TS' and a.creation_time>='2022-12-24 10:20:50';
OWNER NAME CREATION_TIME TABLESPACE_NAME
---------- --------------- ------------------- ------------------------------
LEO TSPITR_EMP2 2022-12-24 10:23:32 LEO_TS
LEO TSPITR_EMP3 2022-12-24 10:23:48 LEO_TS
LEO TSPITR_EMP4 2022-12-24 10:24:01 LEO_TS
说明：执行tspitr后leo_ts表空间的表TSPITR_EMP2、TSPITR_EMP3、TSPITR_EMP4将被删除，此时需运用expdp导出备份，等tspitr恢复完毕后在导入即可.
3.4、导出会被删除的表
说明：其实此处还应该导出truncate表LEO.TSPITR_EMP后做过修改的数据库表对象，如表LEO.TSPITR_EMP1.
SYS@orcl 11:59:28> grant read,write on directory DATA_PUMP_DIR to leo;
Grant succeeded.
[oracle@leo-11g-ogg ~]$ expdp leo/leo directory=DATA_PUMP_DIR dumpfile=tspitr_emp.dmp TABLES=tspitr_emp2,tspitr_emp3,tspitr_emp4 logfile=expdp.log
Export: Release 11.2.0.4.0 - Production on Sat Dec 24 12:00:26 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "LEO"."SYS_EXPORT_TABLE_01": leo/******** directory=DATA_PUMP_DIR dumpfile=tspitr_emp.dmp TABLES=tspitr_emp2,tspitr_emp3,tspitr_emp4 logfile=expdp.log
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type TABLE_EXPORT/TABLE/TABLE
. . exported "LEO"."TSPITR_EMP2" 8.562 KB 14 rows
. . exported "LEO"."TSPITR_EMP3" 8.562 KB 14 rows
. . exported "LEO"."TSPITR_EMP4" 8.562 KB 14 rows
Master table "LEO"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for LEO.SYS_EXPORT_TABLE_01 is:
/u01/app/oracle/admin/orcl/dpdump/tspitr_emp.dmp
Job "LEO"."SYS_EXPORT_TABLE_01" successfully completed at Sat Dec 24 12:00:30 2022 elapsed 0 00:00:04
[root@leo-11g-ogg:/u01/app/oracle/admin/orcl/dpdump]# ll
total 152
-rw-r--r-- 1 oracle oinstall 1263 Dec 24 12:00 expdp.log
-rw-r----- 1 oracle oinstall 151552 Dec 24 12:00 tspitr_emp.dmp
3.5、执行tspitr恢复
[oracle@leo-11g-ogg ~]$ mkdir auxdest
[oracle@leo-11g-ogg ~]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
[oracle@leo-11g-ogg ~]$ export NLS_LANG=AMERICAN
[oracle@leo-11g-ogg ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 - Production on Sat Dec 24 12:18:27 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1650612057)
RMAN> recover tablespace leo_ts until time "to_date('2022-12-24 10:20:50','yyyy-mm-dd hh24:mi:ss')" auxiliary destination '/home/oracle/auxdest';
Starting recover at 24-DEC-22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=765 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
Creating automatic instance, with SID='abAc'
initialization parameters used for automatic instance:
db_name=ORCL
db_unique_name=abAc_tspitr_ORCL
compatible=11.2.0.4.0
db_block_size=8192
db_files=5000
sga_target=1G
processes=80
db_create_file_dest=/home/oracle/auxdest
log_archive_dest_1='location=/home/oracle/auxdest'
#No auxiliary parameter file used
starting up automatic instance ORCL
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 285213576 bytes
Database Buffers 775946240 bytes
Redo Buffers 5517312 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('2022-12-24 10:20:50','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 24-DEC-22
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=114 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/app/oracle/fast_recovery_area/ORCL/backupset/2022_12_24/o1_mf_ncsnf_TAG20221224T094143_ktdp6zdq_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2022_12_24/o1_mf_ncsnf_TAG20221224T094143_ktdp6zdq_.bkp tag=TAG20221224T094143
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/home/oracle/auxdest/ORCL/controlfile/o1_mf_ktdzf6cy_.ctl
Finished restore at 24-DEC-22
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('2022-12-24 10:20:50','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 '|| 'LEO_TS' ||' offline immediate';
krmicd.writeMsg(6162, sqlstatement);
krmicd.execSql(sqlstatement);
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 3 to new;
set newname for clone datafile 2 to new;
set newname for clone tempfile 1 to new;
set newname for datafile 5 to
"/u01/app/oracle/oradata/orcl/leo_ts01.dbf";
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 3, 2, 5;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
sql statement: alter tablespace LEO_TS offline immediate
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /home/oracle/auxdest/ORCL/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 24-DEC-22
using channel ORA_AUX_DISK_1
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 /home/oracle/auxdest/ORCL/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /home/oracle/auxdest/ORCL/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /home/oracle/auxdest/ORCL/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/orcl/leo_ts01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/ORCL/backupset/2022_12_24/o1_mf_nnndf_TAG20221224T094143_ktdp6q9o_.bkp
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/ORCL/backupset/2022_12_24/o1_mf_nnndf_TAG20221224T094143_ktdp6q9o_.bkp tag=TAG20221224T094143
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 24-DEC-22
datafile 1 switched to datafile copy
input datafile copy RECID=4 STAMP=1124281138 file name=/home/oracle/auxdest/ORCL/datafile/o1_mf_system_ktdzfcws_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=5 STAMP=1124281138 file name=/home/oracle/auxdest/ORCL/datafile/o1_mf_undotbs1_ktdzfcwx_.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=6 STAMP=1124281138 file name=/home/oracle/auxdest/ORCL/datafile/o1_mf_sysaux_ktdzfcwv_.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-12-24 10:20:50','yyyy-mm-dd hh24:mi:ss')";
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 3 online";
sql clone "alter database datafile 2 online";
sql clone "alter database datafile 5 online";
# recover and open resetlogs
recover clone database tablespace "LEO_TS", "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 3 online
sql statement: alter database datafile 2 online
sql statement: alter database datafile 5 online
Starting recover at 24-DEC-22
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 72 is already on disk as file /u01/app/oracle/oradata/archivelog/1_72_1122831323.dbf
archived log for thread 1 with sequence 73 is already on disk as file /u01/app/oracle/oradata/archivelog/1_73_1122831323.dbf
archived log for thread 1 with sequence 74 is already on disk as file /u01/app/oracle/oradata/archivelog/1_74_1122831323.dbf
archived log for thread 1 with sequence 75 is already on disk as file /u01/app/oracle/oradata/archivelog/1_75_1122831323.dbf
archived log file name=/u01/app/oracle/oradata/archivelog/1_72_1122831323.dbf thread=1 sequence=72
archived log file name=/u01/app/oracle/oradata/archivelog/1_73_1122831323.dbf thread=1 sequence=73
archived log file name=/u01/app/oracle/oradata/archivelog/1_74_1122831323.dbf thread=1 sequence=74
archived log file name=/u01/app/oracle/oradata/archivelog/1_75_1122831323.dbf thread=1 sequence=75
media recovery complete, elapsed time: 00:00:01
Finished recover at 24-DEC-22
database opened
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace LEO_TS read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/home/oracle/auxdest''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/home/oracle/auxdest''";
}
executing Memory Script
sql statement: alter tablespace LEO_TS read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle/auxdest''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/home/oracle/auxdest''
Performing export of metadata...
EXPDP> FLASHBACK automatically enabled to preserve database integrity.
EXPDP> Starting "SYS"."TSPITR_EXP_abAc":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table "SYS"."TSPITR_EXP_abAc" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_abAc is:
EXPDP> /home/oracle/auxdest/tspitr_abAc_56909.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace LEO_TS:
EXPDP> /u01/app/oracle/oradata/orcl/leo_ts01.dbf
EXPDP> Job "SYS"."TSPITR_EXP_abAc" successfully completed at Sat Dec 24 12:19:35 2022 elapsed 0 00:00:31
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
# drop target tablespaces before importing them back
sql 'drop tablespace LEO_TS including contents keep datafiles cascade constraints';
}
executing Memory Script
database closed
database dismounted
Oracle instance shut down
sql statement: drop tablespace LEO_TS including contents keep datafiles cascade constraints
Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_abAc" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_abAc":
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job "SYS"."TSPITR_IMP_abAc" successfully completed at Sat Dec 24 12:19:50 2022 elapsed 0 00:00:02
Import completed
contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace LEO_TS read write';
sql 'alter tablespace LEO_TS offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
sql statement: alter tablespace LEO_TS read write
sql statement: alter tablespace LEO_TS offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
Removing automatic instance
Automatic instance removed
auxiliary instance file /home/oracle/auxdest/ORCL/datafile/o1_mf_temp_ktdzfohc_.tmp deleted
auxiliary instance file /home/oracle/auxdest/ORCL/onlinelog/o1_mf_15_ktdzfo50_.log deleted
auxiliary instance file /home/oracle/auxdest/ORCL/onlinelog/o1_mf_14_ktdzfo2h_.log deleted
auxiliary instance file /home/oracle/auxdest/ORCL/onlinelog/o1_mf_13_ktdzfnz5_.log deleted
auxiliary instance file /home/oracle/auxdest/ORCL/onlinelog/o1_mf_12_ktdzfnwo_.log deleted
auxiliary instance file /home/oracle/auxdest/ORCL/onlinelog/o1_mf_11_ktdzfnst_.log deleted
auxiliary instance file /home/oracle/auxdest/ORCL/onlinelog/o1_mf_3_ktdzfnqh_.log deleted
auxiliary instance file /home/oracle/auxdest/ORCL/onlinelog/o1_mf_2_ktdzfno8_.log deleted
auxiliary instance file /home/oracle/auxdest/ORCL/onlinelog/o1_mf_1_ktdzfnkt_.log deleted
auxiliary instance file /home/oracle/auxdest/ORCL/datafile/o1_mf_sysaux_ktdzfcwv_.dbf deleted
auxiliary instance file /home/oracle/auxdest/ORCL/datafile/o1_mf_undotbs1_ktdzfcwx_.dbf deleted
auxiliary instance file /home/oracle/auxdest/ORCL/datafile/o1_mf_system_ktdzfcws_.dbf deleted
auxiliary instance file /home/oracle/auxdest/ORCL/controlfile/o1_mf_ktdzf6cy_.ctl deleted
Finished recover at 24-DEC-22
3.6、online表空间
--执行完tspitr后leo_ts表空间为offline状态，需将其online.
SYS@orcl 12:21:30> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
LEO_TS OFFLINE
TSP_ACC ONLINE
TSP_FIN ONLINE
8 rows selected.
SYS@orcl 12:21:56> alter tablespace leo_ts online;
Tablespace altered.
SYS@orcl 12:22:26> set line 200
SYS@orcl 12:22:30> select * from leo.tspitr_emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 00:00:00 800 20
7499 ALLEN SALESMAN 7698 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 00:00:00 1250 500 30
7566 JONES MANAGER 7839 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 00:00:00 2850 30
7782 CLARK MANAGER 7839 00:00:00 2450 10
7788 SCOTT ANALYST 7566 00:00:00 3000 20
7839 KING PRESIDENT 00:00:00 5000 10
7844 TURNER SALESMAN 7698 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 00:00:00 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7900 JAMES CLERK 7698 00:00:00 950 30
7902 FORD ANALYST 7566 00:00:00 3000 20
7934 MILLER CLERK 7782 00:00:00 1300 10
14 rows selected.
说明：如上所示，表leo.tspitr_emp恢复成功.
4、导入丢失对象
4.1、丢失对象确认
SYS@orcl 12:23:32> select * from leo.tspitr_emp2;
select * from leo.tspitr_emp2
*
ERROR at line 1:
ORA-00942: table or view does not exist
SYS@orcl 12:23:43> select * from leo.tspitr_emp3;
select * from leo.tspitr_emp3
*
ERROR at line 1:
ORA-00942: table or view does not exist
SYS@orcl 12:23:51> select * from leo.tspitr_emp4;
select * from leo.tspitr_emp4
*
ERROR at line 1:
ORA-00942: table or view does not exist
说明：可以看到表leo.tspitr_emp2、leo.tspitr_emp3、leo.tspitr_emp4在执行tspitr后不存在,因为恢复到truncate时间点的表空间leo_ts是不存在这些表的.
4.2、导入丢失的表
--接下来导入备份的表数据.
[oracle@leo-11g-ogg ~]$ impdp leo/leo directory=DATA_PUMP_DIR dumpfile=tspitr_emp.dmp TABLES=tspitr_emp2,tspitr_emp3,tspitr_emp4 logfile=impdp.log
Import: Release 11.2.0.4.0 - Production on Sat Dec 24 12:27:13 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "LEO"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "LEO"."SYS_IMPORT_TABLE_01": leo/******** directory=DATA_PUMP_DIR dumpfile=tspitr_emp.dmp TABLES=tspitr_emp2,tspitr_emp3,tspitr_emp4 logfile=impdp.log
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "LEO"."TSPITR_EMP2" 8.562 KB 14 rows
. . imported "LEO"."TSPITR_EMP3" 8.562 KB 14 rows
. . imported "LEO"."TSPITR_EMP4" 8.562 KB 14 rows
Job "LEO"."SYS_IMPORT_TABLE_01" successfully completed at Sat Dec 24 12:27:15 2022 elapsed 0 00:00:01
SYS@orcl 12:23:56> select * from leo.tspitr_emp2;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 00:00:00 800 20
7499 ALLEN SALESMAN 7698 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 00:00:00 1250 500 30
7566 JONES MANAGER 7839 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 00:00:00 2850 30
7782 CLARK MANAGER 7839 00:00:00 2450 10
7788 SCOTT ANALYST 7566 00:00:00 3000 20
7839 KING PRESIDENT 00:00:00 5000 10
7844 TURNER SALESMAN 7698 00:00:00 1500 0 30
7876 ADAMS CLERK 7788 00:00:00 1100 20
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7900 JAMES CLERK 7698 00:00:00 950 30
7902 FORD ANALYST 7566 00:00:00 3000 20
7934 MILLER CLERK 7782 00:00:00 1300 10
14 rows selected.
SYS@orcl 12:27:47> select count(*) from leo.tspitr_emp3;
COUNT(*)
----------
14
SYS@orcl 12:28:04> select count(*) from leo.tspitr_emp4;
COUNT(*)
----------
14
SYS@orcl 12:22:30> select * from leo.tspitr_emp1;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
7369 SMITH CLERK 7902 00:00:00 800 20
7499 ALLEN SALESMAN 7698 00:00:00 1600 300 30
说明：可以看到表leo.tspitr_emp2、leo.tspitr_emp3、leo.tspitr_emp4表的数据已经恢复，而表leo.tspitr_emp1后面添加的5行数据却丢失，生产环境是绝对不允许的.
参考文档：https://blog.csdn.net/weixin_34233421/article/details/89824907
发表评论