如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!
诗檀软件专业数据库修复团队
服务热线 ： 13764045638 QQ号:47079569 邮箱：service@parnassusdata.com
作为一个数据库管理员，你会收到开发商或用户的要求，表明他们在几个小时前删除了大数据库中小表的数据，他们可能想让你尽快的恢复数据，那可能是一个重要的生产数据库，不启用Flashback ，回收站已清空，使用RMAN恢复完全的数据库可能会花费十个多小时的时间，你需要一个有着大存储量的备用服务器，对你而言，这看起来像是一个困难并且耗费时间的任务。
在 Oracle 数据库12c中，有一个方法可以使我们更高效地恢复表并且花费更低的成本，方法就是使用第一个数据库的备份创建第二个数据库 (通常被称为存根数据库) 。在这种情况下，我们复原了 SYSTEM，SYSAUX，UNDO 表空间和单个表空间，这些都包含了我们想要复原的数据，完成复原之后，我们修改没有离线复原的表空间，然后我们应用归档重做日志到我们想要恢复的时间点，复原数据库到合适的时间点之后，然后我们使用Oracle数据泵导出对象，然后将他们导入到原始数据库，再一次使用Oracle 数据泵，Oracle 数据库12c 在RMAN 里引入了一个新功能，支持单个数据库表和单个表分区的时间点恢复。这里是我测试新功能的一个例子:
1. 数据库TEST 有 9个表空间和一个叫做 Howie的架构，我用19377 哥记录创建了一个叫 TEST1 的表，存在于 DATA_HOWIE表空间中。
SQL> select * from v$instance;
INSTANCE_NUMBER INSTANCE_NAME HOST_NAME VERSION STARTUP_T STATUS PAR THREAD# ARCHIVE LOG_SWITCH_WAIT LOGINS SHU DATABASE_STATUS INSTANCE_ROLE ACTIVE_ST BLO CON_ID INSTANCE_MO EDITION FAMILY
————— —————- —————————————————————- —————– ——— ———— — ———- ——- ————— ———- — —————– —————— ——— — ———- ———– ——- ——————————————————————————–
1 TEST 12cServer1 12.1.0.1.0 17-AUG-14 OPEN NO 1 STARTED ALLOWED NO ACTIVE PRIMARY_INSTANCE NORMALNO 0 REGULAR EE
SQL> select tablespace_name from dba_tablespaces order by tablespace_name;
TABLESPACE_NAME
——————————
DATA_HOWIE
DATA_TB1
DATA_TB2
DATA_TB3
SYSAUX
SYSTEM
TEMP
UNDOTBS1
USERS
9 rows selected.
SQL> conn howie
Enter password:
Connected.
SQL> create table test1 as select * from dba_objects;
Table created.
SQL> select count(*) from test1;
COUNT(*)
———-
19377
SQL> select table_name,tablespace_name from user_tables where table_name=’TEST1′;
TABLE_NAME TABLESPACE_NAME
——————————————————————————————————————————– ——————————
TEST1 DATA_HOWIE
2. 数据库处于归档日志模式中，我对数据库进行了完全备份。
[oracle@12cServer1 RMAN]$ rman target /
Recovery Manager: Release 12.1.0.1.0 – Production on Sun Aug 17 20:16:17 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=2146502230)
RMAN> run
{
allocate channel d1 type disk format ‘/u01/app/oracle/RMAN/rmn_%d_t%t_p%p’;
backup
incremental level 0
tag backup_level0
filesperset 1
(database)
plus archivelog ;
release channel d1;
}2> 3> 4> 5> 6> 7> 8> 9> 10> 11>
3. 表 howie.test1中的数据已删除。
SQL> select sysdate,current_scn from v$database;
SYSDATE CURRENT_SCN
——————- ———–
08/17/2014 21:01:15 435599
SQL> delete test1;
19377 rows deleted.
SQL> commit;
Commit complete.
4. 我运行下列脚本，以恢复数据到时间“08/17/2014 21:01:15”的另一个表howie.test1_temp 。
[oracle@12cServer1 RMAN]$ rman target /
Recovery Manager: Release 12.1.0.1.0 – Production on Sun Aug 17 21:01:35 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to target database: TEST (DBID=2146502230)
RMAN> recover table howie.test1
until time “to_date(’08/17/2014 21:01:15′,’mm/dd/yyyy hh24:mi:ss’)”
auxiliary destination ‘/u01/app/oracle/aux’
remap table howie.test1:test1_temp;2> 3> 4>
5. 上面的脚本会处理一切，你会看到数据已经复原到 howie.test1_temp。
SQL> select count(*) from TEST1_TEMP;
COUNT(*)
———-
19377
SQL> select count(*) from TEST1;
COUNT(*)
———-
0
让我们 看一下RMAN 恢复的记录，弄明白它是如何工作的。
1. 创建辅助实例
Creating automatic instance, with SID=’ktDA’
initialization parameters used for automatic instance:
db_name=TEST
db_unique_name=ktDA_pitr_TEST
compatible=12.1.0.0.0
db_block_size=8192
db_files=200
sga_target=1G
processes=80
diagnostic_dest=/u01/app/oracle
db_create_file_dest=/u01/app/oracle/aux
log_archive_dest_1=’location=/u01/app/oracle/aux’
#No auxiliary parameter file used
2. 为辅助实例复原控制文件
contents of Memory Script:
{
# set requested point in time
set until time “to_date(’08/17/2014 21:01:15′,’mm/dd/yyyy 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’;
}
3. 在辅助实例中的复原和恢复之后，将会复原一系列数据文件。
contents of Memory Script:
{
# set requested point in time
set until time “to_date(’08/17/2014 21:01:15′,’mm/dd/yyyy 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”;
# recover and open database read only
recover clone database tablespace “SYSTEM”, “UNDOTBS1”, “SYSAUX”;
sql clone ‘alter database open read only’;
}
contents of Memory Script:
{
# set requested point in time
set until time “to_date(’08/17/2014 21:01:15′,’mm/dd/yyyy hh24:mi:ss’)”;
# online the datafiles restored or switched
sql clone “alter database datafile 8 online”;
# recover and open resetlogs
recover clone database tablespace “DATA_HOWIE”, “SYSTEM”, “UNDOTBS1”, “SYSAUX” delete archivelog;
alter clone database open resetlogs;
}
4. 通过Oracle 数据泵从辅助实例导出表
Performing export of tables…
EXPDP> Starting “SYS”.”TSPITR_EXP_ktDA_BAkw”:
EXPDP> Estimate in progress using BLOCKS method…
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
EXPDP> Total estimation using BLOCKS method: 3 MB
EXPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
EXPDP> Processing object type TABLE_EXPORT/TABLE/STATISTICS/MARKER
EXPDP> . . exported “HOWIE”.”TEST1″ 1.922 MB 19377 rows
EXPDP> Master table “SYS”.”TSPITR_EXP_ktDA_BAkw” successfully loaded/unloaded
EXPDP> ******************************************************************************
EXPDP> Dump file set for SYS.TSPITR_EXP_ktDA_BAkw is:
EXPDP> /u01/app/oracle/aux/tspitr_ktDA_70244.dmp
EXPDP> Job “SYS”.”TSPITR_EXP_ktDA_BAkw” successfully completed at Sun Aug 17 21:03:53 2014 elapsed 0 00:00:14
Export completed
5. 从数据泵导出文件中导入表，约束，索引和其他有关对象到目标数据库
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_ktDA_lube” successfully loaded/unloaded
IMPDP> Starting “SYS”.”TSPITR_IMP_ktDA_lube”:
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE
IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
IMPDP> . . imported “HOWIE”.”TEST1_TEMP” 1.922 MB 19377 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_ktDA_lube” successfully completed at Sun Aug 17 21:04:19 2014 elapsed 0 00:00:19
Import completed
6. 清除辅助实例
Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/aux/TEST/datafile/o1_mf_temp_9z2yqst6_.tmp deleted
auxiliary instance file /u01/app/oracle/aux/KTDA_PITR_TEST/onlinelog/o1_mf_3_9z2yrkqm_.log deleted
auxiliary instance file /u01/app/oracle/aux/KTDA_PITR_TEST/onlinelog/o1_mf_2_9z2yrj35_.log deleted
auxiliary instance file /u01/app/oracle/aux/KTDA_PITR_TEST/onlinelog/o1_mf_1_9z2yrh2r_.log deleted
auxiliary instance file /u01/app/oracle/aux/KTDA_PITR_TEST/datafile/o1_mf_data_how_9z2yrcnq_.dbf deleted
auxiliary instance file /u01/app/oracle/aux/TEST/datafile/o1_mf_sysaux_9z2yptms_.dbf deleted
auxiliary instance file /u01/app/oracle/aux/TEST/datafile/o1_mf_undotbs1_9z2yq9of_.dbf deleted
auxiliary instance file /u01/app/oracle/aux/TEST/datafile/o1_mf_system_9z2yp0mk_.dbf deleted
auxiliary instance file /u01/app/oracle/aux/TEST/controlfile/o1_mf_9z2yos1l_.ctl deleted
auxiliary instance file tspitr_ktDA_70244.dmp deleted
Finished recover at 17-AUG-14
Comment