Oracle 12C: 如何复原/恢复大数据库中的小表

如果自己搞不定可以找诗檀软件专业ORACLE数据库修复团队成员帮您恢复!

诗檀软件专业数据库修复团队

服务热线 : 13764045638    QQ号:47079569    邮箱:service@parnassusdata.com

 

作为一个数据库管理员,你会收到开发商或用户的要求,表明他们在几个小时前删除了大数据库中小表的数据,他们可能想让你尽快的恢复数据,那可能是一个重要的生产数据库,不启用Flashback ,回收站已清空,使用RMAN恢复完全的数据库可能会花费十个多小时的时间,你需要一个有着大存储量的备用服务器,对你而言,这看起来像是一个困难并且耗费时间的任务。

 Oracle 数据库12c中,有一个方法可以使我们更高效地恢复表并且花费更低的成本,方法就是使用第一个数据库的备份创建第二个数据库 (通常被称为存根数据库) 。在这种情况下,我们复原了 SYSTEMSYSAUXUNDO 表空间和单个表空间,这些都包含了我们想要复原的数据,完成复原之后,我们修改没有离线复原的表空间,然后我们应用归档重做日志到我们想要恢复的时间点,复原数据库到合适的时间点之后,然后我们使用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

*

沪ICP备14014813号-2

沪公网安备 31010802001379号