ITPub博客

首页 > 数据库 > Oracle > Oracle 12C 新特性用rman备份恢复表

Oracle 12C 新特性用rman备份恢复表

原创 Oracle 作者:pingdanorcale 时间:2019-03-07 17:06:10 0 删除 编辑

  在12c之前对于用户因DDL(DROP TRUNCATE )等操作引起的数据丢失,一般通过数据库时间点不完全恢复、基于表空间的时间点恢复(TSPITR)、闪回技术。

12c推出了rman基于时间点对表的恢复。原理如下:

Recover Table新特性是利用创建辅助临时实例加数据泵工具来实现的。通常在进行Recover Table之前应该准备好两个目录(AUXILIARY DESTINATION和DATAPUMP DESTINATION),AUXILIARY DESTINATION用来临时存放辅助实例的数据文件,DATAPUMP DESTINATION用来临时存放数据泵导出的文件。

一:测试过程如下:

1.

SQL> set lines 60

SQL> desc tb ;

 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


 

SQL> insert into  tb  select * from tb ;


72633 rows created.


SQL> commit  ;


Commit complete.


SQL> 

SQL> conn / as sysdba

Connected.


2.切换日志使数据写到磁盘上。

SQL> alter system switch logfile ;

3.rman备份数据库

Recovery Manager: Release 12.2.0.1.0 - Production on Fri Jun 29 11:41:03 2018

Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.

connected to target database: MISDB (DBID=4279250949)

RMAN> run 

2> {

3> crosscheck archivelog all;

4> crosscheck  backup;

5> delete noprompt obsolete; 

6> delete noprompt expired backup;

7> allocate channel d1  type disk  ;

8> allocate channel d2 type disk ;

9> backup format '/u01/bk/%T_%d_%s_%p.dat' tag 'full_data' database ;          

10> backup format '/u01/bk/%T_%d_%s_%p.arc' tag 'full_arc' archivelog all delete all input ;     

11> backup format '/u01/bk/%T_%d_%s_%p.ctl' tag 'full_ctl' current controlfile; 

12> release channel d1;

13> release channel d2;

14> };

released channel: d1

released channel: d2

RMAN> 

SQL> select count(*) from rita.tb ;

  COUNT(*)

----------

    145266

SQL> select current_scn from v$database;


CURRENT_SCN

-----------

2074690

4. 删除表

SQL> drop table rita.tb  purge;

SQL>  select count(*) from rita.tb ;

 select count(*) from rita.tb

                           *

ERROR at line 1:

ORA-00942: table or view does not exist


5.建辅助目录

[oracle@host01 u01]$ mkdir  recover/

[oracle@host01 u01]$ mkdir  dumpfiles

[oracle@host01 u01]$ ls

app  bk  dumpfiles  lost+found  recover

[oracle@host01 u01]$ cd dumpfiles/

[oracle@host01 dumpfiles]$ ls

[oracle@host01 dumpfiles]$ cd  ..

[oracle@host01 u01]$ 



6.查看数据库备份:


list backup of database;

connected to target database: MISDB (DBID=4279250949)


RMAN> 

using target database control file instead of recovery catalog


7.恢复删除的表:

rman target  /


Recovery Manager: Release 12.2.0.1.0 - Production on Fri Jun 29 15:22:20 2018


Copyright (c) 1982, 2017, Oracle and/or its affiliates.  All rights reserved.


connected to target database: MISDB (DBID=4279250949)


RMAN> run{

2> RECOVER TABLE rita.tb

3> UNTIL SCN 2074690

4> AUXILIARY DESTINATION '/u01/recover'

5> datapump destination '/u01/dumpfiles';

6> };


Starting recover at 29-JUN-18

using target database control file instead of recovery catalog

current log archived

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=52 instance=misdb1 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='ygBi'自动创建一个数据库实例

oracle@host01 datafile]$ ps -ef |grep smon

grid       5050      1  0 14:57 ?        00:00:00 asm_smon_+ASM1

root       5144      1  1 14:58 ?        00:00:30 /u01/app/12.2.0/grid/bin/osysmond.bin

oracle     7730      1  0 15:00 ?        00:00:00 ora_smon_misdb1

oracle    36939      1  0 15:28 ?        00:00:00 ora_smon_ygBi

reating automatic instance, with SID='ygBi'


initialization parameters used for automatic instance:

db_name=MISDB

db_unique_name=ygBi_pitr_MISDB

compatible=12.2.0

db_block_size=8192

db_files=200

diagnostic_dest=/u01/app/oracle

_system_trig_enabled=FALSE

sga_target=1728M

processes=200

db_create_file_dest=/u01/recover

log_archive_dest_1='location=/u01/recover'---这个空间要足够大

#No auxiliary parameter file used


datafile 1 switched to datafile copy

input datafile copy RECID=4 STAMP=980090756 file name=/u01/recover/MISDB/datafile/o1_mf_system_fmcqnsc8_.dbf

datafile 4 switched to datafile copy

input datafile copy RECID=5 STAMP=980090756 file name=/u01/recover/MISDB/datafile/o1_mf_undotbs1_fmcqpx2n_.dbf

datafile 3 switched to datafile copy

input datafile copy RECID=6 STAMP=980090756 file name=/u01/recover/MISDB/datafile/o1_mf_sysaux_fmcqpx17_.dbf


contents of Memory Script:

{

# set requested point in time

set until  scn 2074690;

# 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  3 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:

{

# create directory for datapump import

sql "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/dumpfiles''";

# create directory for datapump export

sql clone "create or replace directory TSPITR_DIROBJ_DPDIR as ''

/u01/dumpfiles''";

}

executing Memory Script


sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/dumpfiles''


sql statement: create or replace directory TSPITR_DIROBJ_DPDIR as ''/u01/dumpfiles''


Performing export of tables...

   EXPDP> Starting "SYS"."TSPITR_EXP_ygBi_gcph":  

   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 "RITA"."TB"                                 19.21 MB  145266 rows

   EXPDP> Master table "SYS"."TSPITR_EXP_ygBi_gcph" successfully loaded/unloaded

   EXPDP> ******************************************************************************

   EXPDP> Dump file set for SYS.TSPITR_EXP_ygBi_gcph is:

   EXPDP>   /u01/dumpfiles/tspitr_ygBi_13106.dmp

   EXPDP> Job "SYS"."TSPITR_EXP_ygBi_gcph" successfully completed at Fri Jun 29 15:31:30 2018 elapsed 0 00:01:09

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_ygBi_yzeq" successfully loaded/unloaded

   IMPDP> Starting "SYS"."TSPITR_IMP_ygBi_yzeq":  

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   IMPDP> . . imported "RITA"."TB"                                 19.21 MB  145266 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_ygBi_yzeq" successfully completed at Fri Jun 29 15:32:44 2018 elapsed 0 00:00:41

Import completed

二:从上边的例子可以看出,恢复大体流程是

1.rman 根据指定的表确定备份。

2.rman 根据指定的recover 路径恢复一个辅助实例,恢复到指定的时间点。

Creating automatic instance, with SID='ygBi'

initialization parameters used for automatic instance:

db_name=MISDB

db_unique_name=ygBi_pitr_MISDB

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 2074690;

# 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  3 to new;

set newname for clone tempfile  1 to new;

3.用恢复好的辅助实例,数据库导出在导入生产环境

EXPDP> Starting "SYS"."TSPITR_EXP_ygBi_gcph":  

   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

Performing import of tables...

   IMPDP> Master table "SYS"."TSPITR_IMP_ygBi_yzeq" successfully loaded/unloaded

   IMPDP> Starting "SYS"."TSPITR_IMP_ygBi_yzeq":  

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE

   IMPDP> Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

   IMPDP> . . imported "RITA"."TB"                                 19.21 MB  145266 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_ygBi_yzeq" successfully completed at Fri Jun 29 15:32:44 2018 elapsed 0 00:00:41

Import completed

4.删除辅助实例

恢复完成。

三:RECOVER TABLE需要注意的几个问题:

1.目标数据库必须被置于读写模式。

2.目标数据库必须被置于归档模式。

3.SYS用户下的表或分区无法恢复。

4.存储于SYSAUX和SYSTEM表空间下的表和分区无法恢复。

5.Standby数据库上的表或表分区不能进行恢复。

6.在执行“RECOVER TABLE”命令时,可以根据需要在以下三种级别指定时间:

(1)SCN号

(2)Sequence number(日志序列号)

(3)Time:根据NLS_LANG和NLS_DATE_FORMAT环境变量中的格式来指定时间,也可以用SYSDATE,比如"SYSDATE-30"、"to_date('2018-06-29:17:51:48','yyyy-mm-dd hh24:mi:ss')"

参考文档

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/bradv/rman-recovering-tables-partitions.html#GUID-87B7F772-335F-4179-81C9-91678D026D01


来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10201716/viewspace-2637829/,如需转载,请注明出处,否则将追究法律责任。

全部评论

注册时间:2009-01-02

  • 博文量
    34
  • 访问量
    85890