【12c】12c RMAN新特性之recover table（表级别恢复）
在 RMAN 中提供了表级别恢复（ RECOVER TABLE ）。在 Oracle 12c 中，在发生 drop 或 truncate 的情况下，可以从 RMAN 备份种将一个特定的表或分区恢复到某个时间点、 SCN 或归档序列号，并且可以有下面的选择：
- l 使用REMAP选项将表恢复为一个新表或者分区中，也可以恢复到其他用户中。
- l 只生成一个需要被恢复表的expdp格式的dump文件，选择后期再进行恢复。
Oracle 12c的Recover Table新特性是利用创建辅助临时实例加数据泵工具来实现的。通常在进行Recover Table之前应该准备好两个目录（AUXILIARY DESTINATION和DATAPUMP DESTINATION），AUXILIARY DESTINATION用来临时存放辅助实例的数据文件，DATAPUMP DESTINATION用来临时存放数据泵导出的文件。
只要之前创建了RMAN备份，那么就可以根据指定的的时间来进行表级和表分区级的恢复操作，而且不影响其他的数据库对象。RMAN的表级和表分区级恢复可以使用在如下场景：
① 在恢复小表或数据库中的某几张表时，但发现使用Restore Database或Tablespace的代价很高而且效率很低。也可以使用TSPITR（表空间基于时间点的恢复）的方法，但该方法效率很低，因为需要移动表空间中的所有对象。
② 恢复有逻辑损坏或者被删除的表。
③ Flashback Table不可用，例如Undo数据已经被覆盖的情况。
④ DDL操作后需要恢复数据。Flashback Table不支持表结构发生改变后的回退，例如TRUNCATE TABLE。
2 RMAN从备份中自动处理恢复表或者表分区时会执行如下步骤：
1.判断哪些备份包含需要恢复的表或表分区，然后根据指定的时间来进行恢复。
2.判断目标主机上是否有足够的空间来创建auxiliary instance，该实例用于处理表或分区的恢复。如果需要的空间不足，那么RMAN会报错并退出恢复操作。
3.创建auxiliary database，并根据指定的时间来恢复指定的表或表分区到auxiliary database中。辅助数据库的数据文件位置可以在命令中指定。
4.创建包含恢复表或表分区的数据泵文件（expdp dump file）。数据泵的名称和位置也可以在命令中指定。
5.（可选操作）将上一步生产的数据泵文件导入到目标实例中。当然也可以选择不导入，如果选择不导入就必须使用impdp手工导入。
6.（可选操作）在目标数据库中rename恢复表或表分区。
3 关于RECOVER TABLE需要注意的几个问题：
- l 目标数据库必须被置于读写模式。
- l 目标数据库必须被置于归档模式。
- l 如果要恢复表或者分区，你必须拥有这些表或者分区存在后的时间的备份。
- l 想要恢复单个表分区，COMPATIBLE初始化参数所在的目标库必须设置为11.1.0或以上。
- l SYS用户下的表或分区无法恢复。
- l 存储于SYSAUX和SYSTEM表空间下的表和分区无法恢复。
- l Standby数据库上的表或表分区不能进行恢复。
- l 在使用REMAP的情况下，有NOT NULL 约束的表不能进行恢复。(测试通过）
- l 确保对于辅助数据库在文件系统下有足够的可用空间，同时对数据泵文件也有同样保证。
- l 必须要存在一份完整的数据库备份，至少要有SYSTEM、UNDO、SYSAUX和表所在表空间相关的备份。如果恢复的表在PDB中，那么需要备份Root Container的SYSTEM，SYSAUX、UNDO和PDB的SYSTEM、SYSAUX以及包含了要恢复的表的表空间。
4 在执行“RECOVER TABLE”命令时，可以根据需要在以下三种级别指定时间：
- （1）SCN号
- （2）Sequence number（日志序列号）
- （3）Time：根据NLS_LANG和NLS_DATE_FORMAT环境变量中的格式来指定时间，也可以用SYSDATE，比如"SYSDATE-30"、"to_date('2018-04-09:13:51:48','yyyy-mm-dd hh24:mi:ss')"
5 “RECOVER TABLE”命令的一般格式为：
RMAN> connect target "username/password as SYSBACKUP";
RMAN> RECOVER TABLE username.tablename UNTIL TIME 'TIMESTAMP…'
AUXILIARY DESTINATION '/u01/tablerecovery'
DATAPUMP DESTINATION '/u01/dpump'
DUMP FILE 'tablename.dmp'
NOTABLEIMPORT -- this option avoids importing the table automatically.(此选项避免自动导入表)
REMAP TABLE 'username.tablename': 'username.new_table_name'; -- can rename table with this option.(此选项可以对表重命名)
6 脚本例子
示例1：在PDB中恢复表HR.PDB_EMP,恢复后的表命名为EMP_RECVR
RECOVER TABLE HR.PDB_EMP OF PLUGGABLE DATABASE HR_PDB
UNTIL TIME 'SYSDATE-4'
AUXILIARY DESTINATION '/tmp/backups'
REMAP TABLE 'HR'.'PDB_EMP':'EMP_RECVR';
RECOVER TABLE DB12C.T
UNTIL SCN 1932621
AUXILIARY DESTINATION '/tmp/oracle/recover'
REMAP TABLE 'DB12C'.'T':'T_HISTORY_20130717';
RECOVER TABLE LHR.TEST_RT
UNTIL TIME "to_date('2018-04-09:13:51:48','yyyy-mm-dd hh24:mi:ss')"
AUXILIARY DESTINATION '/tmp'
REMAP TABLE 'LHR'.'TEST_RT':'TEST_RT_LHR';
RECOVER TABLE HR.DEPARTMENTS, SH.CHANNELS
UNTIL TIME 'SYSDATE – 1'
AUXILIARY DESTINATION '/tmp/auxdest'
REMAP TABLE hr.departments:example.new_departments, sh.channels:example.new_channels;
示例2：从RMAN备份中恢复表SCOTT.EMP,SCOTT.DEPT,并以数据泵格式导出emp_dept_exp_dump.dat，并不进行表的导入
RECOVER TABLE SCOTT.EMP, SCOTT.DEPT
UNTIL TIME 'SYSDATE-1'
AUXILIARY DESTINATION '/tmp/oracle/recover'
DATAPUMP DESTINATION '/tmp/recover/dumpfiles'
DUMP FILE 'emp_dept_exp_dump.dat'
NOTABLEIMPORT;
示例3：恢复表的两个分区，恢复后表分区重新命名并且放置于SALES_PRE_2000_TS表空间
RECOVER TABLE SH.SALES:SALES_1998, SH.SALES:SALES_1999
UNTIL SEQUENCE 354
AUXILIARY DESTINATION '/tmp/oracle/recover'
REMAP TABLE 'SH'.'SALES':'SALES_1998':'HISTORIC_SALES_1998',
'SH'.'SALES':'SALES_1999':'HISTORIC_SALES_1999'
REMAP TABLESPACE 'SALES_TS':'SALES_PRE_2000_TS';
https://docs.oracle.com/en/database/oracle/oracle-database/12.2/bradv/rman-recovering-tables-partitions.html#GUID-98525DD5-E08F-46F0-A9D8-A7EC3EB54457
https://docs.oracle.com/database/121/BRADV/rcmresind.htm#BRADV703
https://www.cnblogs.com/andy6/p/6884320.html
http://www.xifenfei.com/2013/03/oracle-12c-rman-recover-table.html
http://www.askmaclean.com/archives/12c%E6%96%B0%E7%89%B9%E6%80%A7recover-table.html
http://www.cndba.cn/dave/article/1871
http://blog.itpub.net/26753337/viewspace-2107978/
a 实战案例1
[oracle@orcl01 data]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 18 11:11:02 2019
Version 19.5.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
SQL> create user rs identified by "ora#123";
User created.
SQL> grant connect,resource to rs;
SQL> grant unlimited tablespace to rs;
Grant succeeded.
SQL> create table rs.rbs tablespace users as select * from dba_segments;
Table created.
SQL>
[oracle@orcl01 rdbms]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 18 11:01:59 2019
Version 19.5.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
SQL> desc rbs
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(128)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(128)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(23)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
NAMESPACE NUMBER
EDITION_NAME VARCHAR2(128)
SHARING VARCHAR2(18)
EDITIONABLE VARCHAR2(1)
ORACLE_MAINTAINED VARCHAR2(1)
APPLICATION VARCHAR2(1)
DEFAULT_COLLATION VARCHAR2(100)
DUPLICATED VARCHAR2(1)
SHARDED VARCHAR2(1)
CREATED_APPID NUMBER
CREATED_VSNID NUMBER
MODIFIED_APPID NUMBER
MODIFIED_VSNID NUMBER
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
[oracle@orcl01 rdbms]$
RMAN> backup database format '/data/full_db_%u.bak';
Starting backup at 2019:12:18 11:04:15
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=18635 instance=hwtest1 device type=DISK
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=+DATA02/HWTEST/DATAFILE/system.314.1027164269
input datafile file number=00004 name=+DATA02/HWTEST/DATAFILE/undotbs1.316.1027164319
input datafile file number=00003 name=+DATA02/HWTEST/DATAFILE/sysaux.315.1027164303
input datafile file number=00005 name=+DATA02/HWTEST/DATAFILE/undotbs2.322.1027164733
input datafile file number=00007 name=+DATA02/HWTEST/DATAFILE/users.317.1027164319
channel ORA_DISK_1: starting piece 1 at 2019:12:18 11:04:16
channel ORA_DISK_1: finished piece 1 at 2019:12:18 11:04:23
piece handle=/data/full_db_02ujnplg.bak tag=TAG20191218T110416 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 2019:12:18 11:04:23
Starting Control File and SPFILE Autobackup at 2019:12:18 11:04:23
piece handle=/u01/app/oracle/product/19.3/db_1/dbs/c-294510964-20191218-00 comment=NONE
Finished Control File and SPFILE Autobackup at 2019:12:18 11:04:24
RMAN>
SQL> select current_scn from v$database;
CURRENT_SCN
-----------
2363419
SQL>
[oracle@orcl01 data]$ mkdir auxi
[oracle@orcl01 data]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Wed Dec 18 11:06:47 2019
Version 19.5.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.5.0.0.0
SQL> drop table rs.rbs purge;
Table dropped.
SQL>
recover table rs.rbs until scn 2363419 auxiliary destination '/data/auxi/' datapump destination '/data/auxi/' dump file '123.dmp' remap table rs.rbs:rbsbak;
recover table rs.rbs until scn 2360291 auxiliary destination '/data/auxi/' datapump destination '/data/auxi/' dump file '123.dmp' notableimport;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 12/18/2019 11:10:44
RMAN-05063: Cannot recover specified tables
RMAN-05056: Table SYS.RBS belongs to SYS
[oracle@orcl01 auxi]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Wed Dec 18 11:20:18 2019
Version 19.5.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
connected to target database: HWTEST (DBID=294510964)
RMAN> recover table rs.rbs until scn 2363419 auxiliary destination '/data/auxi/' datapump destination '/data/auxi/' dump file '123.dmp' remap table rs.rbs:rbsbak;
Starting recover at 2019:12:18 11:20:22
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=2785 instance=hwtest1 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
Tablespace UNDOTBS2
Creating automatic instance, with SID='Bhbi'
initialization parameters used for automatic instance:
db_name=HWTEST
db_unique_name=Bhbi_pitr_HWTEST
compatible=19.0.0
db_block_size=8192
db_files=200
diagnostic_dest=/u01/app/oracle
_system_trig_enabled=FALSE
sga_target=81152M
processes=200
db_create_file_dest=/data/auxi/
log_archive_dest_1='location=/data/auxi/'
#No auxiliary parameter file used
starting up automatic instance HWTEST
Oracle instance started
Total System Global Area 85094038456 bytes
Fixed Size 30141368 bytes
Variable Size 9663676416 bytes
Database Buffers 75161927680 bytes
Redo Buffers 238292992 bytes
Automatic instance created
contents of Memory Script:
{
# set requested point in time
set until scn 2363419;
# 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';
}
executing Memory Script
executing command: SET until clause
Starting restore at 2019:12:18 11:20:39
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=208 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/product/19.3/db_1/dbs/c-294510964-20191218-01
channel ORA_AUX_DISK_1: piece handle=/u01/app/oracle/product/19.3/db_1/dbs/c-294510964-20191218-01 tag=TAG20191218T111351
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/data/auxi/HWTEST/controlfile/o1_mf_gzm6r80t_.ctl
Finished restore at 2019:12:18 11:20:41
sql statement: alter database mount clone database
sql statement: alter system archive log current
contents of Memory Script:
{
# set requested point in time
set until scn 2363419;
# set destinations for recovery set and auxiliary set datafiles
set newname for clone datafile 1 to new;
set newname for clone datafile 4 to new;
set newname for clone datafile 5 to new;
set newname for clone datafile 3 to new;
set newname for clone tempfile 1 to new;
# switch all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 1, 4, 5, 3;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
renamed tempfile 1 to /data/auxi/HWTEST/datafile/o1_mf_temp_%u_.tmp in control file
Starting restore at 2019:12:18 11:20:50
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 /data/auxi/HWTEST/datafile/o1_mf_system_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /data/auxi/HWTEST/datafile/o1_mf_undotbs1_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /data/auxi/HWTEST/datafile/o1_mf_undotbs2_%u_.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /data/auxi/HWTEST/datafile/o1_mf_sysaux_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /data/full_db_04ujnq78.bak
channel ORA_AUX_DISK_1: piece handle=/data/full_db_04ujnq78.bak tag=TAG20191218T111343
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:07
Finished restore at 2019:12:18 11:20:57
datafile 1 switched to datafile copy
input datafile copy RECID=5 STAMP=1027336858 file name=/data/auxi/HWTEST/datafile/o1_mf_system_gzm6rlwq_.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=6 STAMP=1027336858 file name=/data/auxi/HWTEST/datafile/o1_mf_undotbs1_gzm6rlwr_.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=7 STAMP=1027336858 file name=/data/auxi/HWTEST/datafile/o1_mf_undotbs2_gzm6rlwv_.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=1027336858 file name=/data/auxi/HWTEST/datafile/o1_mf_sysaux_gzm6rlwt_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 2363419;
# online the datafiles restored or switched
sql clone "alter database datafile 1 online";
sql clone "alter database datafile 4 online";
sql clone "alter database datafile 5 online";
sql clone "alter database datafile 3 online";
# recover and open database read only
recover clone database tablespace "SYSTEM", "UNDOTBS1", "UNDOTBS2", "SYSAUX";
sql clone 'alter database open read only';
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 1 online
sql statement: alter database datafile 4 online
sql statement: alter database datafile 5 online
sql statement: alter database datafile 3 online
Starting recover at 2019:12:18 11:21:02
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 84 is already on disk as file +ARCH01/HWTEST/ARCHIVELOG/2019_12_18/thread_1_seq_84.1403.1027336565
archived log for thread 2 with sequence 4 is already on disk as file +ARCH01/HWTEST/ARCHIVELOG/2019_12_18/thread_2_seq_4.1363.1027336569
archived log file name=+ARCH01/HWTEST/ARCHIVELOG/2019_12_18/thread_1_seq_84.1403.1027336565 thread=1 sequence=84
archived log file name=+ARCH01/HWTEST/ARCHIVELOG/2019_12_18/thread_2_seq_4.1363.1027336569 thread=2 sequence=4
media recovery complete, elapsed time: 00:00:01
Finished recover at 2019:12:18 11:21:06
sql statement: alter database open read only
contents of Memory Script:
{
sql clone "create spfile from memory";
shutdown clone immediate;
startup clone nomount;
sql clone "alter system set control_files =
''/data/auxi/HWTEST/controlfile/o1_mf_gzm6r80t_.ctl'' comment=
''RMAN set'' scope=spfile";
shutdown clone immediate;
startup clone nomount;
# mount database
sql clone 'alter database mount clone database';
}
executing Memory Script
sql statement: create spfile from memory
database closed
database dismounted
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 85094038456 bytes
Fixed Size 30141368 bytes
Variable Size 9663676416 bytes
Database Buffers 75161927680 bytes
Redo Buffers 238292992 bytes
sql statement: alter system set control_files = ''/data/auxi/HWTEST/controlfile/o1_mf_gzm6r80t_.ctl'' comment= ''RMAN set'' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 85094038456 bytes
Fixed Size 30141368 bytes
Variable Size 9663676416 bytes
Database Buffers 75161927680 bytes
Redo Buffers 238292992 bytes
sql statement: alter database mount clone database
contents of Memory Script:
{
# set requested point in time
set until scn 2363419;
# set destinations for recovery set and auxiliary set datafiles
set newname for datafile 7 to new;
# restore the tablespaces in the recovery set and the auxiliary set
restore clone datafile 7;
switch clone datafile all;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
Starting restore at 2019:12:18 11:22:41
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=229 device type=DISK
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 00007 to /data/auxi/BHBI_PITR_HWTEST/datafile/o1_mf_users_%u_.dbf
channel ORA_AUX_DISK_1: reading from backup piece /data/full_db_04ujnq78.bak
channel ORA_AUX_DISK_1: piece handle=/data/full_db_04ujnq78.bak tag=TAG20191218T111343
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
Finished restore at 2019:12:18 11:22:43
datafile 7 switched to datafile copy
input datafile copy RECID=10 STAMP=1027336963 file name=/data/auxi/BHBI_PITR_HWTEST/datafile/o1_mf_users_gzm6w2b5_.dbf
contents of Memory Script:
{
# set requested point in time
set until scn 2363419;
# online the datafiles restored or switched
sql clone "alter database datafile 7 online";
# recover and open resetlogs
recover clone database tablespace "USERS", "SYSTEM", "UNDOTBS1", "UNDOTBS2", "SYSAUX" delete archivelog;
alter clone database open resetlogs;
}
executing Memory Script
executing command: SET until clause
sql statement: alter database datafile 7 online
Starting recover at 2019:12:18 11:22:43
using channel ORA_AUX_DISK_1
starting media recovery
archived log for thread 1 with sequence 84 is already on disk as file +ARCH01/HWTEST/ARCHIVELOG/2019_12_18/thread_1_seq_84.1403.1027336565
archived log for thread 2 with sequence 4 is already on disk as file +ARCH01/HWTEST/ARCHIVELOG/2019_12_18/thread_2_seq_4.1363.1027336569
archived log file name=+ARCH01/HWTEST/ARCHIVELOG/2019_12_18/thread_1_seq_84.1403.1027336565 thread=1 sequence=84
archived log file name=+ARCH01/HWTEST/ARCHIVELOG/2019_12_18/thread_2_seq_4.1363.1027336569 thread=2 sequence=4
media recovery complete, elapsed time: 00:00:01
Finished recover at 2019:12:18 11:22:47
database opened
contents of Memory Script:
{
# create directory for datapump import
sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/data/auxi/''";
# create directory for datapump export
sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''
/data/auxi/''";
}
executing Memory Script
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/data/auxi/''
sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/data/auxi/''
Performing export of tables...
EXPDP> Starting "SYS"."TSPITR_EXP_Bhbi_ljgz":
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> . . exported "RS"."RBS" 701.5 KB 4960 rows
EXPDP> Master table "SYS"."TSPITR_EXP_Bhbi_ljgz" successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_Bhbi_ljgz is:
EXPDP> /data/auxi/123.dmp
EXPDP> Job "SYS"."TSPITR_EXP_Bhbi_ljgz" successfully completed at Wed Dec 18 11:23:06 2019 elapsed 0 00:00:12
Export completed
contents of Memory Script:
{
# shutdown clone before import
shutdown clone abort
}
executing Memory Script
Oracle instance shut down
Performing import of tables...
IMPDP> Master table "SYS"."TSPITR_IMP_Bhbi_qebf" successfully loaded/unloaded
IMPDP> Starting "SYS"."TSPITR_IMP_Bhbi_qebf":
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported "RS"."RBSBAK" 701.5 KB 4960 rows
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
IMPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
IMPDP> Job "SYS"."TSPITR_IMP_Bhbi_qebf" successfully completed at Wed Dec 18 11:23:38 2019 elapsed 0 00:00:18
Import completed
Removing automatic instance
Automatic instance removed
auxiliary instance file /data/auxi/HWTEST/datafile/o1_mf_temp_gzm6s2rp_.tmp deleted
auxiliary instance file /data/auxi/BHBI_PITR_HWTEST/onlinelog/o1_mf_4_gzm6w7f6_.log deleted
auxiliary instance file /data/auxi/BHBI_PITR_HWTEST/onlinelog/o1_mf_3_gzm6w7dm_.log deleted
auxiliary instance file /data/auxi/BHBI_PITR_HWTEST/onlinelog/o1_mf_2_gzm6w7cy_.log deleted
auxiliary instance file /data/auxi/BHBI_PITR_HWTEST/onlinelog/o1_mf_1_gzm6w7bn_.log deleted
auxiliary instance file /data/auxi/BHBI_PITR_HWTEST/datafile/o1_mf_users_gzm6w2b5_.dbf deleted
auxiliary instance file /data/auxi/HWTEST/datafile/o1_mf_sysaux_gzm6rlwt_.dbf deleted
auxiliary instance file /data/auxi/HWTEST/datafile/o1_mf_undotbs2_gzm6rlwv_.dbf deleted
auxiliary instance file /data/auxi/HWTEST/datafile/o1_mf_undotbs1_gzm6rlwr_.dbf deleted
auxiliary instance file /data/auxi/HWTEST/datafile/o1_mf_system_gzm6rlwq_.dbf deleted
auxiliary instance file /data/auxi/HWTEST/controlfile/o1_mf_gzm6r80t_.ctl deleted
auxiliary instance file 123.dmp deleted
Finished recover at 2019:12:18 11:23:40
RMAN>
SQL> desc rs.rbsbak;
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(128)
SEGMENT_NAME VARCHAR2(128)
PARTITION_NAME VARCHAR2(128)
SEGMENT_TYPE VARCHAR2(18)
SEGMENT_SUBTYPE VARCHAR2(10)
TABLESPACE_NAME VARCHAR2(30)
HEADER_FILE NUMBER
HEADER_BLOCK NUMBER
BYTES NUMBER
BLOCKS NUMBER
EXTENTS NUMBER
INITIAL_EXTENT NUMBER
NEXT_EXTENT NUMBER
MIN_EXTENTS NUMBER
MAX_EXTENTS NUMBER
MAX_SIZE NUMBER
RETENTION VARCHAR2(7)
MINRETENTION NUMBER
PCT_INCREASE NUMBER
FREELISTS NUMBER
FREELIST_GROUPS NUMBER
RELATIVE_FNO NUMBER
BUFFER_POOL VARCHAR2(7)
FLASH_CACHE VARCHAR2(7)
CELL_FLASH_CACHE VARCHAR2(7)
INMEMORY VARCHAR2(8)
INMEMORY_PRIORITY VARCHAR2(8)
INMEMORY_DISTRIBUTE VARCHAR2(15)
INMEMORY_DUPLICATE VARCHAR2(13)
INMEMORY_COMPRESSION VARCHAR2(17)
CELLMEMORY VARCHAR2(24)
SQL> select count(1) from rs.rbsbak;
COUNT(1)
----------
4960
SQL>
1578
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
