文档课题：使用自定义辅助实例执行基于时间点的表空间恢复.
系统：centos 7.9
数据库：oracle 11.2.0.4
1、相关概念
执行TSPITR时，要求表空间必须是自包含.
TSPITR在以下情况使用：
a、恢复因错误的TRUNCATE TABLE而丢失的数据
b、从表逻辑损坏中恢复
c、撤消只影响部分数据库的批作业或DML语句的结果
d、将逻辑方案恢复到与物理数据库其余部分不同的时间点
TSPITR使用可移动表空间和数据泵，提供了以下新功能和特性：
TSPITR可用于恢复已删除的表空间
可反复执行TSPITR恢复到表空间联机之前的多个时间点，而无需使用恢复目录
在Oracle Database 11g发行版2之前，TSPITR 使用导出和导入进行处理.TSPITR现在使用可移动表空间和数据泵.由于底层技术的这一变化，现在可使用TSPITR来恢复已删除的表空间.此外可反复执行TSPITR 恢复到不同的时间点，而无需使用恢复目录.
2、前期准备
2.1、创建表空间和用户
[oracle@leo-11g-ogg ~]$ sqlplus / as sysdba
SYS@orcl> select tablespace_name from dba_tablespaces;
TABLESPACE_NAME
------------------------------
SYSTEM
SYSAUX
UNDOTBS1
TEMP
USERS
LEO_TS
6 rows selected.
SYS@orcl> create tablespace tsp_acc datafile '/u01/app/oracle/oradata/orcl/tcp_acc01.dbf' size 20m autoextend on;
SYS@orcl> create tablespace tsp_fin datafile '/u01/app/oracle/oradata/orcl/tcp_fin01.dbf' size 20m autoextend on;
SYS@orcl> create user acc identified by acc default tablespace tsp_acc;
SYS@orcl> create user fin identified by fin default tablespace tsp_fin;
SYS@orcl> grant resource,connect to acc;
SYS@orcl> grant resource,connect to fin;
SYS@orcl> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
LEO_TS ONLINE
TSP_ACC ONLINE
TSP_FIN ONLINE
8 rows selected.
2.2、备份数据库
RMAN> delete backup;
RMAN> delete copy;
RMAN> run{
2> delete noprompt obsolete;
3> crosscheck backup;
4> delete noprompt expired backup;
5> crosscheck archivelog all;
6> delete noprompt expired archivelog all;
7> backup incremental level=0 database format '/home/oracle/rmanbak/full-%T-%U.bak';
8> backup archivelog all format '/home/oracle/rmanbak/arch-%T-%U.bak';
9> backup current controlfile format '/home/oracle/rmanbak/ctl-%T-%U.bak';
10> backup spfile format '/home/oracle/rmanbak/spf-%T-%U.bak';
11> delete noprompt archivelog all completed before 'SYSDATE-7';
12> }
RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
no obsolete backups found
using channel ORA_DISK_1
specification does not match any backup in the repository
using channel ORA_DISK_1
specification does not match any backup in the repository
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
specification does not match any archived log in the repository
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
specification does not match any archived log in the repository
Starting backup at 2022-12-23 14:51:37
using channel ORA_DISK_1
channel ORA_DISK_1: starting incremental level 0 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 2022-12-23 14:51:37
channel ORA_DISK_1: finished piece 1 at 2022-12-23 14:51:44
piece handle=/home/oracle/rmanbak/full-20221223-091g3vbp_1_1.bak tag=TAG20221223T145137 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting incremental level 0 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 2022-12-23 14:51:45
channel ORA_DISK_1: finished piece 1 at 2022-12-23 14:51:46
piece handle=/home/oracle/rmanbak/full-20221223-0a1g3vc0_1_1.bak tag=TAG20221223T145137 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2022-12-23 14:51:46
Starting backup at 2022-12-23 14:51:46
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=68 RECID=35 STAMP=1124203906
channel ORA_DISK_1: starting piece 1 at 2022-12-23 14:51:46
channel ORA_DISK_1: finished piece 1 at 2022-12-23 14:51:47
piece handle=/home/oracle/rmanbak/arch-20221223-0b1g3vc2_1_1.bak tag=TAG20221223T145146 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2022-12-23 14:51:47
Starting backup at 2022-12-23 14:51:47
using channel ORA_DISK_1
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
channel ORA_DISK_1: starting piece 1 at 2022-12-23 14:51:48
channel ORA_DISK_1: finished piece 1 at 2022-12-23 14:51:49
piece handle=/home/oracle/rmanbak/ctl-20221223-0c1g3vc3_1_1.bak tag=TAG20221223T145147 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2022-12-23 14:51:49
Starting backup at 2022-12-23 14:51:49
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 2022-12-23 14:51:50
channel ORA_DISK_1: finished piece 1 at 2022-12-23 14:51:51
piece handle=/home/oracle/rmanbak/spf-20221223-0d1g3vc6_1_1.bak tag=TAG20221223T145149 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2022-12-23 14:51:51
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=11 device type=DISK
2.3、准备测试数据
--在表空间tsp_acc、tsp_fin上创建测试用的数据
[oracle@leo-11g-ogg ~]$ sqlplus acc/acc@orcl
ACC@orcl> create table tb_test(i int);
ACC@orcl> begin
2 for i in 1..1000 loop
3 insert into tb_test(i) values (i);
4 end loop;
5 commit;
6 end;
7 /
ACC@orcl> select count(*) from tb_test;
COUNT(*)
----------
1000
ACC@orcl> conn fin/fin
FIN@orcl> create table tb_lab (i int);
FIN@orcl> begin
2 for i in 1..1000 loop
3 insert into tb_lab (i) values (i);
4 end loop;
5 commit;
6 end;
7 /
FIN@orcl> select count(*) from tb_lab;
COUNT(*)
----------
1000
2.4、恢复时间点
--查询当前数据库时间点.
FIN@orcl> conn / as sysdba
SYS@orcl> select current_scn,to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from v$database;
CURRENT_SCN TO_CHAR(SYSDATE,'YY
----------- -------------------
3200997 2022-12-23 14:59:37
2.5、删除表
--删除测试表空间tsp_acc上的表tb_test
SYS@orcl> select count(*) from acc.tb_test;
COUNT(*)
----------
1000
SYS@orcl> drop table acc.tb_test;
Table dropped.
SYS@orcl> col owner for a15
SYS@orcl> set line 200
SYS@orcl> select owner,object_name,type,createtime,original_name from dba_recyclebin;
OWNER OBJECT_NAME TYPE CREATETIME ORIGINAL_NAME
---------- ------------------------------ ---------- ------------------- --------------------
ACC BIN$8HpirFNMRpHgU2eFqMCxnQ==$0 TABLE 2022-12-23:14:55:27 TB_TEST
SYS@orcl> commit;
2.6、增加数据
说明：在删除tsp_acc表空间中的tb_test表后，给另一个表空间tsp_fin中的tb_lab表添加数据.若将tsp_acc表空间恢复后，如果tsp_fin表空间上的表数据没丢失的话，则证明基于时间点的表空间恢复只会恢复要操作的表空间.
SYS@orcl> conn fin/fin
FIN@orcl> begin
2 for i in 1001..2000 loop
3 insert into TB_LAB (i) values (i);
4 end loop;
5 commit;
6 end;
7 /
FIN@orcl> select count(*) from tb_lab;
COUNT(*)
----------
2000
说明：准备工作完成.
3、表空间恢复
3.1、创建参数文件
--创建辅助实例的初始化参数文件.
SYS@orcl> show parameter spfile
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string /u01/app/oracle/product/11.2.0
/db/dbs/spfileorcl.ora
SYS@orcl> create pfile from spfile;
[oracle@leo-11g-ogg dbs]$ vi initaux.ora
添加如下内容：
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/aux/control01.ctl','/u01/app/oracle/fast_recovery_area/aux/control02.ctl'
*.db_block_size=8192
*.db_name=orcl --必须和目标库一样
*.db_unique_name=aux --必须和目标库不一样
*.remote_login_passwordfile='EXCLUSIVE'
*.db_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/aux/'
*.log_file_name_convert='/u01/app/oracle/oradata/orcl/','/u01/app/oracle/oradata/aux/','/u01/app/oracle/fast_recovery_area/orcl/','/u01/app/oracle/oradata/aux/','/u01/app/oracle/oradata/ORCL/onlinelog/','/u01/app/oracle/oradata/aux/','/u01/app/oracle/fast_recovery_area/ORCL/','/u01/app/oracle/oradata/aux/'
参数解析：
db_name：必须有,该值须和目标数据库一样.
db_unique_name：必须有, 需和目标库不一样.
compatible：必须有，该值需和目标库一致.
db_block_size：必须有，该值需和目标库一致.
remote_login_passwordfile：必须有，当用一个密码文件连接辅助实例时，该值必须设置成EXCLUSIVE或为NONE.
control_files：非必须参数，该值指定文件名只要不和目标数据库或者其它任意文件名冲突即可.
db_file_name_convert：非必须参数，通过指定该值自动转换路径生成辅助实例所需的数据文件.
log_file_name_convert：非必须参数，该值基于目标数据库的日志文件名，转换路径后生成辅助实例的联机日志文件.
3.2、创建密码文件
创建辅助实例的密码文件.
[oracle@leo-11g-ogg dbs]$ cp orapworcl orapwaux
3.3、创建tns服务名
--创建连接辅助数据库的服务名.
[oracle@leo-11g-ogg admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = leo-11g-ogg)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
AUX =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.103)(PORT = 1521))
)
(CONNECT_DATA =
(SID = AUX)
)
)
说明：蓝色高亮部分为添加内容.
--测试tns服务名.
[oracle@leo-11g-ogg admin]$ tnsping aux
TNS Ping Utility for Linux: Version 11.2.0.4.0 - Production on 23-DEC-2022 15:34:57
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
/u01/app/oracle/product/11.2.0/db/network/admin/sqlnet.ora
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.103)(PORT = 1521))) (CONNECT_DATA = (SID = AUX)))
OK (0 msec)
3.4、创建监听服务
--创建并启动辅助实例的监听服务.
[oracle@leo-11g-ogg admin]$ vi listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/11.2.0/db/network/admin/listener.ora
# Generated by Oracle configuration tools.
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = leo-11g-ogg)(PORT = 1521))
)
)
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = aux)
(ORACLE_HOME = /u01/app/oracle/product/11.2.0/db)
(SID_NAME = aux)
)
)
ADR_BASE_LISTENER = /u01/app/oracle
说明：蓝色高亮部分为添加内容.
[oracle@leo-11g-ogg admin]$ lsnrctl reload
[oracle@leo-11g-ogg admin]$ lsnrctl status
LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 23-DEC-2022 15:31:22
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.4.0 - Production
Start Date 23-DEC-2022 11:26:31
Uptime 0 days 4 hr. 4 min. 51 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/db/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/leo-11g-ogg/listener/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=leo-11g-ogg)(PORT=1521)))
Services Summary...
Service "aux" has 1 instance(s).
Instance "aux", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
Service "orclXDB" has 1 instance(s).
Instance "orcl", status READY, has 1 handler(s) for this service...
The command completed successfully
3.5、启动辅助实例到nomount
--启动辅助数据库到nomount状态.
[oracle@leo-11g-ogg admin]$ echo $ORACLE_SID
aux
[oracle@leo-11g-ogg admin]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 23 15:44:54 2022
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> create spfile from pfile='/u01/app/oracle/product/11.2.0/db/dbs/initaux.ora';
File created.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 1670221824 bytes
Fixed Size 2253824 bytes
Variable Size 1275071488 bytes
Database Buffers 385875968 bytes
Redo Buffers 7020544 bytes
SQL> show parameter control_files
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
control_files string /u01/app/oracle/oradata/aux/co
ntrol01.ctl, /u01/app/oracle/f
ast_recovery_area/aux/control0
2.ctl
SQL> show parameter instance_name
NAME TYPE VALUE
------------------------------------ --------------------------------- ------------------------------
instance_name string aux
3.6、执行恢复
--连接目标数据库和辅助数据库
[oracle@leo-11g-ogg ~]$ echo $ORACLE_SID
orcl
[oracle@leo-11g-ogg ~]$ rman target sys/oracle_4U@orcl auxiliary sys/oracle_4U@192.168.133.103:1521/aux
Recovery Manager: Release 11.2.0.4.0 - Production on Fri Dec 23 15:51:46 2022
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: ORCL (DBID=1650612057)
connected to auxiliary database: ORCL (not mounted)
--开始基于时间点的表空间恢复
RMAN> run{
2> allocate channel c1 device type disk;
3> allocate auxiliary channel a1 device type disk;
4> recover tablespace tsp_acc until time "to_date('2022-12-23 14:59:37','yyyy-mm-dd hh24:mi:ss')";
5> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=13 device type=DISK
allocated channel: a1
channel a1: SID=5 device type=DISK
Starting recover at 23-DEC-22
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
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-23 14:59:37','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 23-DEC-22
channel a1: starting datafile backup set restore
channel a1: restoring control file
channel a1: reading from backup piece /home/oracle/rmanbak/ctl-20221223-0c1g3vc3_1_1.bak
channel a1: piece handle=/home/oracle/rmanbak/ctl-20221223-0c1g3vc3_1_1.bak tag=TAG20221223T145147
channel a1: restored backup piece 1
channel a1: restore complete, elapsed time: 00:00:01
output file name=/u01/app/oracle/oradata/aux/control01.ctl
output file name=/u01/app/oracle/fast_recovery_area/aux/control02.ctl
Finished restore at 23-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-23 14:59:37','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 '|| 'TSP_ACC' ||' 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 datafile 1 to
"/u01/app/oracle/oradata/aux/system01.dbf";
set newname for datafile 3 to
"/u01/app/oracle/oradata/aux/undotbs01.dbf";
set newname for datafile 2 to
"/u01/app/oracle/oradata/aux/sysaux01.dbf";
set newname for tempfile 1 to
"/u01/app/oracle/oradata/aux/temp01.dbf";
set newname for datafile 6 to
"/u01/app/oracle/oradata/orcl/tcp_acc01.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, 6;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
sql statement: alter tablespace TSP_ACC offline immediate
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 23-DEC-22
channel a1: starting datafile backup set restore
channel a1: specifying datafile(s) to restore from backup set
channel a1: restoring datafile 00001 to /u01/app/oracle/oradata/aux/system01.dbf
channel a1: restoring datafile 00003 to /u01/app/oracle/oradata/aux/undotbs01.dbf
channel a1: restoring datafile 00002 to /u01/app/oracle/oradata/aux/sysaux01.dbf
channel a1: restoring datafile 00006 to /u01/app/oracle/oradata/orcl/tcp_acc01.dbf
channel a1: reading from backup piece /home/oracle/rmanbak/full-20221223-091g3vbp_1_1.bak
channel a1: piece handle=/home/oracle/rmanbak/full-20221223-091g3vbp_1_1.bak tag=TAG20221223T145137
channel a1: restored backup piece 1
channel a1: restore complete, elapsed time: 00:00:15
Finished restore at 23-DEC-22
datafile 6 switched to datafile copy
input datafile copy RECID=2 STAMP=1124213409 file name=/u01/app/oracle/oradata/orcl/tcp_acc01.dbf
contents of Memory Script:
{
# set requested point in time
set until time "to_date('2022-12-23 14:59:37','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 6 online";
# recover and open resetlogs
recover clone database tablespace "TSP_ACC", "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 6 online
Starting recover at 23-DEC-22
starting media recovery
archived log for thread 1 with sequence 68 is already on disk as file /u01/app/oracle/oradata/archivelog/1_68_1122831323.dbf
archived log for thread 1 with sequence 69 is already on disk as file /u01/app/oracle/oradata/archivelog/1_69_1122831323.dbf
archived log file name=/u01/app/oracle/oradata/archivelog/1_68_1122831323.dbf thread=1 sequence=68
archived log file name=/u01/app/oracle/oradata/archivelog/1_69_1122831323.dbf thread=1 sequence=69
media recovery complete, elapsed time: 00:00:00
Finished recover at 23-DEC-22
database opened
contents of Memory Script:
{
# make read only the tablespace that will be exported
sql clone 'alter tablespace TSP_ACC read only';
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/product/11.2.0/db/dbs''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/u01/app/oracle/product/11.2.0/db/dbs''";
}
executing Memory Script
sql statement: alter tablespace TSP_ACC read only
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/product/11.2.0/db/dbs''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/app/oracle/product/11.2.0/db/dbs''
Performing export of metadata...
EXPDP> FLASHBACK automatically enabled to preserve database integrity.
EXPDP> Starting "SYS"."TSPITR_EXP_guoc":
EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
EXPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
EXPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
EXPDP> Master table "SYS"."TSPITR_EXP_guoc" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_guoc is:
EXPDP> /u01/app/oracle/product/11.2.0/db/dbs/tspitr_guoc_35464.dmp
EXPDP> ******************************************************************************
EXPDP> Datafiles required for transportable tablespace TSP_ACC:
EXPDP> /u01/app/oracle/oradata/orcl/tcp_acc01.dbf
EXPDP> Job "SYS"."TSPITR_EXP_guoc" successfully completed at Fri Dec 23 17:30:37 2022 elapsed 0 00:00:21
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone immediate
# drop target tablespaces before importing them back
sql 'drop tablespace TSP_ACC including contents keep datafiles cascade constraints';
}
executing Memory Script
database closed
database dismounted
Oracle instance shut down
sql statement: drop tablespace TSP_ACC including contents keep datafiles cascade constraints
Performing import of metadata...
IMPDP> Master table "SYS"."TSPITR_IMP_guoc" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_guoc":
IMPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
IMPDP> Processing object type TRANSPORTABLE_EXPORT/TABLE
IMPDP> Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
IMPDP> Job "SYS"."TSPITR_IMP_guoc" successfully completed at Fri Dec 23 17:30:51 2022 elapsed 0 00:00:02
Import completed
contents of Memory Script:
{
# make read write and offline the imported tablespaces
sql 'alter tablespace TSP_ACC read write';
sql 'alter tablespace TSP_ACC offline';
# enable autobackups after TSPITR is finished
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
}
executing Memory Script
sql statement: alter tablespace TSP_ACC read write
sql statement: alter tablespace TSP_ACC offline
sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;
auxiliary instance file /u01/app/oracle/oradata/aux/temp01.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/aux/onlinelog/o1_mf_15_ks0qyrn9_.log deleted
auxiliary instance file /u01/app/oracle/oradata/aux/o1_mf_15_ks0qyrlw_.log deleted
auxiliary instance file /u01/app/oracle/oradata/aux/onlinelog/o1_mf_14_ks0qyrkw_.log deleted
auxiliary instance file /u01/app/oracle/oradata/aux/o1_mf_14_ks0qyrj9_.log deleted
auxiliary instance file /u01/app/oracle/oradata/aux/onlinelog/o1_mf_13_ks0qyrhd_.log deleted
auxiliary instance file /u01/app/oracle/oradata/aux/o1_mf_13_ks0qyrgj_.log deleted
auxiliary instance file /u01/app/oracle/oradata/aux/onlinelog/o1_mf_12_ks0qyrfo_.log deleted
auxiliary instance file /u01/app/oracle/oradata/aux/o1_mf_12_ks0qyrdj_.log deleted
auxiliary instance file /u01/app/oracle/oradata/aux/onlinelog/o1_mf_11_ks0qyrcj_.log deleted
auxiliary instance file /u01/app/oracle/oradata/aux/o1_mf_11_ks0qyrbf_.log deleted
auxiliary instance file /u01/app/oracle/oradata/aux/redo03.log deleted
auxiliary instance file /u01/app/oracle/oradata/aux/redo02.log deleted
auxiliary instance file /u01/app/oracle/oradata/aux/redo01.log deleted
auxiliary instance file /u01/app/oracle/oradata/aux/sysaux01.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/aux/undotbs01.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/aux/system01.dbf deleted
auxiliary instance file /u01/app/oracle/fast_recovery_area/aux/control02.ctl deleted
auxiliary instance file /u01/app/oracle/oradata/aux/control01.ctl deleted
Finished recover at 23-DEC-22
released channel: c1
4、验证数据
[oracle@leo-11g-ogg ~]$ export ORACLE_SID=orcl
[oracle@leo-11g-ogg ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Fri Dec 23 17:33:03 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
SQL> select tablespace_name,status from dba_tablespaces;
TABLESPACE_NAME STATUS
------------------------------ ---------
SYSTEM ONLINE
SYSAUX ONLINE
UNDOTBS1 ONLINE
TEMP ONLINE
USERS ONLINE
LEO_TS ONLINE
TSP_ACC OFFLINE
TSP_FIN ONLINE
8 rows selected.
SQL> alter tablespace tsp_acc online;
Tablespace altered.
SQL> select count(*) from fin.tb_lab;
COUNT(*)
----------
2000
SQL> select count(*) from acc.tb_test;
COUNT(*)
----------
1000
说明：如上所示，acc.tb_test表成功恢复，fin.tb_lab未改变.
使用自定义辅助实例执行基于时间点的表空间恢复
原创Liujun_Deng 博主文章分类：Oracle ©著作权
文章标签 自定义辅助实例 基于时间点的表空间恢复 文章分类 Oracle 数据库
©著作权归作者所有：来自51CTO博客作者Liujun_Deng的原创作品，请联系作者获取转载授权，否则将追究法律责任
提问和评论都可以，用心的回复会被更多人看到
评论
发布评论
相关文章
-
Springboot自定义变量的使用
java自定义参数设定
java springboot -
基于时间点的不完全恢复—表空间恢复&表恢复
基于时间点的不完全恢复—表空间恢复&表恢复
sql sed 表空间 -
TSPITR基于时间点的表空间自动恢复——恢复truncate后的单表
TSPITR基于时间点的表空间自动恢复——恢复truncate后的单表
恢复truncate后的单表 TSPITR 基于时间点的表空间恢复 logminer LAST_DDL_TIME
