基于oracle 11.2.0.4 rman data file copy以及增量scn备份数据文件持续变化系列之一

测试结论
1,可以基于初次data file copy以及基于其scn的增量SCN的增量RMAN备份对于数据文件进行还原及恢复操作


2,data file copy的RMAN命令为


backup as copy datafile 9 tag 'datafile 9 copy note' format '/home/oracle/datafile_9_%u_%d.bak';


3，显示data file copy的RMAN命令为
list copy of datafile 9;


或者


list copy;


或者


list copy of datafile 9 tag '';




4，上述的同等命令为
select * from v$datafile_copy




5,基于其scn的增量SCN的增量RMAN备份 的命令为
backup incremental from scn 5915956 datafile 9 format '/home/oracle/incr_bak_%u_%d.bak';


6，上述的增量SCN源于
  第一次增量RMAN备份的SCN源于 v$datafile_copy之checkpoint_change#




7,具体还原及恢复数据文件的具体过程为


7.1，初次对数据文件进行datafile copy
  backup as copy datafile 9 tag 'datafile 9 copy note' format '/home/oracle/datafile_9_%u_%d.bak';


7.2,数据库变更变化


7.3，基于DATA FILE COPY的SCN进行增量RMAN备份
  backup incremental from scn 5915956 datafile 9 format '/home/oracle/incr_bak_%u_%d.bak';






8，可以对restore之还原数据文件进行指定tag
  restore datafile 9 from tag 'DATAFILE 9 COPY NOTE';




9,同理可以对recover之恢复数据文件进行指定tag


recover datafile 9 from tag 'TAG20170611T170303';




测试明细
1，数据库版本
SQL> select * from v$version where rownum=1;


BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production


2，创建测试表并插入数据
SQL> conn user_obj/system
Connected.
SQL> create table t_first(a int);


Table created.




SQL> insert into t_first values(1);


1 row created.


SQL> commit;


Commit complete.


3,DATA FILE COPY数据文件9即表空间tbs_obj
[oracle@mygirl ~]$  rman target /


Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jun 11 16:57:01 2017


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


connected to target database: MYGIRL (DBID=2527996082)


RMAN> backup as copy datafile 9 tag 'datafile 9 copy note' format '/home/oracle/datafile_9_%u_%d.bak';


Starting backup at 11-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=/oracle/mygirl/tbs_obj01.dbf
output file name=/home/oracle/datafile_9_0ns6hr1i_MYGIRL.bak tag=DATAFILE 9 COPY NOTE RECID=12 STAMP=946400307
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 11-JUN-17




RMAN> list copy of datafile 9;


List of Datafile Copies
=======================


Key     File S Completion Time Ckp SCN    Ckp Time       
------- ---- - --------------- ---------- ---------------
12      9    A 11-JUN-17       5915956    11-JUN-17      
        Name: /home/oracle/datafile_9_0ns6hr1i_MYGIRL.bak
        Tag: DATAFILE 9 COPY NOTE


10      9    A 11-JUN-17       5913749    11-JUN-17      
        Name: /oracle/product/11.2.0/db_1/dbs/data_D-MYGIRL_I-2527996082_TS-TBS_OBJ_FNO-9_0gs6hotj
        Tag: TAG20170611T162211




RMAN> list copy of datafile 9 tag 'DATAFILE 9 COPY NOTE';


List of Datafile Copies
=======================


Key     File S Completion Time Ckp SCN    Ckp Time       
------- ---- - --------------- ---------- ---------------
12      9    A 11-JUN-17       5915956    11-JUN-17      
        Name: /home/oracle/datafile_9_0ns6hr1i_MYGIRL.bak
        Tag: DATAFILE 9 COPY NOTE






4,对数据库进行增量变化
[oracle@mygirl ~]$ sqlplus user_obj/system


SQL*Plus: Release 11.2.0.4.0 Production on Sun Jun 11 17:00:24 2017


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> create table t_diff_x(a int);


Table created.


SQL> insert into t_diff_x values(1);


1 row created.


SQL> commit;


Commit complete.


5，对数据文件9进行基于scn的增量RMAN备份
SQL> set linesize 300
SQL> col name for a50
SQL> select recid,name,file#,incremental_level,checkpoint_time,checkpoint_change# from v$datafile_copy where file#=9;


     RECID NAME                                                    FILE# INCREMENTAL_LEVEL CHECKPOIN CHECKPOINT_CHANGE#
---------- -------------------------------------------------- ---------- ----------------- --------- ------------------
        10 /oracle/product/11.2.0/db_1/dbs/data_D-MYGIRL_I-25          9                   11-JUN-17            5913749
           27996082_TS-TBS_OBJ_FNO-9_0gs6hotj


        11                                                             9                   11-JUN-17            5914455
        12 /home/oracle/datafile_9_0ns6hr1i_MYGIRL.bak                 9                   11-JUN-17            5915956


SQL> 




[oracle@mygirl ~]$  rman target /


Recovery Manager: Release 11.2.0.4.0 - Production on Sun Jun 11 17:03:00 2017


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


connected to target database: MYGIRL (DBID=2527996082)


RMAN>  backup incremental from scn 5915956 datafile 9 format '/home/oracle/incr_bak_%u_%d.bak';


Starting backup at 11-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 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=00009 name=/oracle/mygirl/tbs_obj01.dbf
channel ORA_DISK_1: starting piece 1 at 11-JUN-17
channel ORA_DISK_1: finished piece 1 at 11-JUN-17
piece handle=/home/oracle/incr_bak_0os6hra7_MYGIRL.bak tag=TAG20170611T170303 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
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 11-JUN-17
channel ORA_DISK_1: finished piece 1 at 11-JUN-17
piece handle=/home/oracle/incr_bak_0ps6hra8_MYGIRL.bak tag=TAG20170611T170303 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-JUN-17


6,模拟删除数据文件9
SQL> set linesize 300
SQL> col name for a50
SQL> select file#,name from v$datafile where file#=9;


     FILE# NAME
---------- --------------------------------------------------
         9 /oracle/mygirl/tbs_obj01.dbf




SQL> host mv /oracle/mygirl/tbs_obj01.dbf /oracle/mygirl/tbs_obj01.dbf.origbak


SQL> 


SQL> conn user_obj/system
Connected.
SQL> create table t_mod(a int);


Table created.


SQL> commit;


Commit complete.


SQL> insert into t_mod values(1);
insert into t_mod values(1)
            *
ERROR at line 1:
ORA-01116: error in opening database file 9
ORA-01110: data file 9: '/oracle/mygirl/tbs_obj01.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3


7,基于上述的数据文件9 DATA FILE COPY及基于SCN的增量RMAN备份进行还原及恢复数据文件9


RMAN> sql 'alter database datafile 9 offline';


sql statement: alter database datafile 9 offline






RMAN> restore datafile 9 from tag 'DATAFILE 9 COPY NOTE';


Starting restore at 11-JUN-17
using channel ORA_DISK_1


channel ORA_DISK_1: restoring datafile 00009
input datafile copy RECID=12 STAMP=946400307 file name=/home/oracle/datafile_9_0ns6hr1i_MYGIRL.bak
destination for restore of datafile 00009: /oracle/mygirl/tbs_obj01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00009
output file name=/oracle/mygirl/tbs_obj01.dbf RECID=0 STAMP=0
Finished restore at 11-JUN-17




RMAN> recover datafile 9 from tag 'TAG20170611T170303';


Starting recover at 11-JUN-17
using channel ORA_DISK_1


starting media recovery
media recovery complete, elapsed time: 00:00:01


Finished recover at 11-JUN-17




RMAN> sql 'alter database datafile 9 online';


sql statement: alter database datafile 9 online




8,验证数据库的一致性
[oracle@mygirl ~]$ sqlplus user_obj/system


SQL*Plus: Release 11.2.0.4.0 Production on Sun Jun 11 17:09:55 2017


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 * from t_diff_x;


         A
----------
         1






9,
SQL> alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';


Session altered.




SQL> set linesize 300
SQL> select recid,file#,incremental_level,incremental_change#,checkpoint_change#,COMPLETION_TIME from v$backup_datafile where file#=9


     RECID      FILE# INCREMENTAL_LEVEL INCREMENTAL_CHANGE# CHECKPOINT_CHANGE# COMPLETION_TIME
---------- ---------- ----------------- ------------------- ------------------ -------------------
        11          9                 1             5889250            5914431 2017-06-11 16:29:44
        12          9                 1             5914431            5914455 2017-06-11 16:30:25


SQL> 




RMAN>  backup incremental from scn 5915956 datafile 9 format '/home/oracle/incr_bak_%u_%d.bak';


Starting backup at 11-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 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=00009 name=/oracle/mygirl/tbs_obj01.dbf
channel ORA_DISK_1: starting piece 1 at 11-JUN-17
channel ORA_DISK_1: finished piece 1 at 11-JUN-17
piece handle=/home/oracle/incr_bak_0os6hra7_MYGIRL.bak tag=TAG20170611T170303 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
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 11-JUN-17
channel ORA_DISK_1: finished piece 1 at 11-JUN-17
piece handle=/home/oracle/incr_bak_0ps6hra8_MYGIRL.bak tag=TAG20170611T170303 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 11-JUN-17




测试期间的报错
1,backup as copy datafile 9;如不指定format之datafile copy会存储到$ORACLE_HOME/dbs
RMAN> backup as copy datafile 9;


Starting backup at 11-JUN-17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=/oracle/mygirl/tbs_obj01.dbf
output file name=/oracle/product/11.2.0/db_1/dbs/data_D-MYGIRL_I-2527996082_TS-TBS_OBJ_FNO-9_0gs6hotj tag=TAG20170611T162211 RECID=10 STAMP=946398131
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 11-JUN-17




2,backup incremental from scn必须指定format
RMAN> backup incremental from scn 5913749 datafile 9;


Starting backup at 11-JUN-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00009 name=/oracle/mygirl/tbs_obj01.dbf
channel ORA_DISK_1: starting piece 1 at 11-JUN-17
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/11/2017 16:33:38
RMAN-20045: must specify FORMAT for BACKUP INCREMENTAL FROM SCN command
continuing other job steps, job failed will not be re-run
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 11-JUN-17
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 06/11/2017 16:33:39
RMAN-20045: must specify FORMAT for BACKUP INCREMENTAL FROM SCN command

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

转载于:http://blog.itpub.net/9240380/viewspace-2140590/