无备份有完全归档日志情况下恢复数据文件

如果一个数据文件创建时到当前所有的归档日志都存在的话,那么可以通过offlinedatafile,recoverdatafile,onlinedatafile的方式直接恢复数据文件,此种恢复属于介质恢复。

创新互联专注为客户提供全方位的互联网综合服务,包含不限于网站设计制作、成都网站建设、奈曼网络推广、微信小程序定制开发、奈曼网络营销、奈曼企业策划、奈曼品牌公关、搜索引擎seo、人物专访、企业宣传片、企业代运营等,从售前售中售后,我们都将竭诚为您服务,您的肯定,是我们最大的嘉奖;创新互联为所有大学生创业者提供奈曼建站搭建服务,24小时服务热线:18982081108,官方网址:www.cdcxhl.com

下面我们做一个实验:

 

  1. 查看当前所有的归档日志的初始scn以及终止scn:(status为 A表示available,D表示已删除)

 

SQL> col name for a100

SQL> select sequence#,name,first_change#,next_change#,status from v$archived_log;

 

 SEQUENCE# NAME                                                                                                                                                           FIRST_CHANGE# NEXT_CHANGE# S

---------- ----------------------------------------------------------------------------------------------------                                                      ------------- -----------------

         5 /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_5_cqztt6no_.arc                     990220      1001021 A

         6 /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_6_cqzwnzqb_.arc                    1001021      1006241 A

         7 /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_7_cqzwz0z5_.arc                    1006241      1006650 A

         8 /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_8_cqzybtrv_.arc                    1006650      1009513 A

         9 /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_9_cqzybvt8_.arc                    1009513      1009663 A

        10 /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_10_cqzycr5z_.arc                   1009663      1009860 A

        11 /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_11_cqzycw9l_.arc                   1009860      1010001 A

        12 /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_12_cqzycxn6_.arc                   1010001      1010136 A

        13 /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_13_cqzycyyl_.arc                   1010136      1010268 A

 

9 rows selected.

  1. 查看各数据文件的创建SCN,以及checkpoint SCN:

SQL> select file#,name,status,fuzzy,creation_change#,checkpoint_change# from v$datafile_header;

 

     FILE# NAME                                                                                                 STATUS  FUZ CREATION_CHANGE# CHECKPOINT_CHANGE#

---------- ---------------------------------------------------------------------------------------------------- ------- --- ---------------- ------------------

         1 /u01/app/oracle/oradata/ora11g/system01.dbf                                                          ONLINE  YES                7            1010268

         2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf                                                          ONLINE  YES             1834            1010268

         3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf                                                         ONLINE  YES           923328            1010268

         4 /u01/app/oracle/oradata/ora11g/users01.dbf                                                           ONLINE  YES            16143            1010268

         5 /u01/app/oracle/oradata/ora11g/example01.dbf                                                         ONLINE  YES           952919            1010268

         6 /u01/app/oracle/oradata/ora11g/users02.dbf                                                           ONLINE  YES          1005138            1010268

 

6 rows selected.

  1. 可以发现当前只有datafile      6是在第一个归档日志后创建的,因此,如果这个文件corruption的话,可以直接通过介质恢复的方式来恢复该数据文件,而不需要使用备份。

SQL> select file#,name,status,fuzzy,creation_change#,checkpoint_change# from v$datafile_header where creation_change# > 990220;

 

     FILE# NAME                                                                                                 STATUS  FUZ CREATION_CHANGE# CHECKPOINT_CHANGE#

---------- ---------------------------------------------------------------------------------------------------- ------- --- ---------------- ------------------

         6 /u01/app/oracle/oradata/ora11g/users02.dbf                                                           ONLINE  YES          1005138            1010268

  1. 删除datafile      6文件

[oracle@ora11g scripts]$ rm -f /u01/app/oracle/oradata/ora11g/users02.dbf

  1. 重启数据库暴露数据文件丢失问题(生产环境切勿操作)

SQL> startup force

ORACLE instance started.

 

Total System Global Area  730714112 bytes

Fixed Size                  2256832 bytes

Variable Size             486539328 bytes

Database Buffers          239075328 bytes

Redo Buffers                2842624 bytes

Database mounted.

ORA-01157: cannot identify/lock data file 6 - see DBWR trace file

ORA-01110: data file 6: '/u01/app/oracle/oradata/ora11g/users02.dbf'

  1. 查看数据文件状态,可以发现数据库无法识别数据文件6了

   

SQL> select file#,name,status,fuzzy,creation_change#,checkpoint_change# from v$datafile_header;

 

     FILE# NAME                                                                                                                             STATUS  FUZ CREATION_CHANGE# CHECKPOINT_CHANGE#

---------- ---------------------------------------------------------------------------------------------------- ------- --- ---------------- ------------------

         1 /u01/app/oracle/oradata/ora11g/system01.dbf                                                          ONLINE  YES                     7            1013668

         2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf                                                          ONLINE  YES              1834            1013668

         3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf                                                         ONLINE  YES           923328            1013668

         4 /u01/app/oracle/oradata/ora11g/users01.dbf                                                             ONLINE  YES             16143            1013668

         5 /u01/app/oracle/oradata/ora11g/example01.dbf                                                         ONLINE  YES           952919            1013668

         6                                                                                                                                                  ONLINE                            0                       0

  1. 先offline      datafile 6来打开数据库

      SQL> alter database datafile 6 offline;

 

      Database altered.

 

      SQL> alter database open;

 

      Database altered.

  1. 创建数据文件6

 

      SQL> alter database create datafile '/u01/app/oracle/oradata/ora11g/users02.dbf';

 

      Database altered.

  1. 恢复数据库文件6

SQL> recover datafile 6;

ORA-00279: change 1005138 generated at 07/09/2016 02:33:49 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_6_cqzwnzqb_.arc

ORA-00280: change 1005138 for thread 1 is in sequence #6

 

 

Specify log: {=suggested | filename | AUTO | CANCEL}

 

ORA-00279: change 1006241 generated at 07/09/2016 02:36:15 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_7_cqzwz0z5_.arc

ORA-00280: change 1006241 for thread 1 is in sequence #7

 

 

Specify log: {=suggested | filename | AUTO | CANCEL}

 

ORA-00279: change 1006650 generated at 07/09/2016 02:41:36 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_8_cqzybtrv_.arc

ORA-00280: change 1006650 for thread 1 is in sequence #8

 

 

Specify log: {=suggested | filename | AUTO | CANCEL}

 

ORA-00279: change 1009513 generated at 07/09/2016 03:04:58 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_9_cqzybvt8_.arc

ORA-00280: change 1009513 for thread 1 is in sequence #9

 

 

Specify log: {=suggested | filename | AUTO | CANCEL}

 

ORA-00279: change 1009663 generated at 07/09/2016 03:04:59 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_10_cqzycr5z_.arc

ORA-00280: change 1009663 for thread 1 is in sequence #10

 

 

Specify log: {=suggested | filename | AUTO | CANCEL}

 

ORA-00279: change 1009860 generated at 07/09/2016 03:05:28 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_11_cqzycw9l_.arc

ORA-00280: change 1009860 for thread 1 is in sequence #11

 

 

Specify log: {=suggested | filename | AUTO | CANCEL}

 

ORA-00279: change 1010001 generated at 07/09/2016 03:05:32 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/fast_recovery_area/ORA11G/archivelog/2016_07_09/o1_mf_1_12_cqzycxn6_.arc

ORA-00280: change 1010001 for thread 1 is in sequence #12

 

 

Specify log: {=suggested | filename | AUTO | CANCEL}

 

 

Log applied.

Media recovery complete.

SQL> SQL>

恢复完成

  1. 查看数据文件状态

SQL> select file#,name,status,fuzzy,creation_change#,checkpoint_change# from v$datafile_header;

 

     FILE# NAME                                                                                                                    STATUS  FUZ CREATION_CHANGE# CHECKPOINT_CHANGE#

---------- ---------------------------------------------------------------------------------------------------- ------- --- ---------------- ------------------

         1 /u01/app/oracle/oradata/ora11g/system01.dbf                                                          ONLINE  YES                  7            1034000

         2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf                                                          ONLINE  YES             1834            1034000

         3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf                                                         ONLINE  YES           923328            1034000

         4 /u01/app/oracle/oradata/ora11g/users01.dbf                                                             ONLINE  YES            16143            1034000

         5 /u01/app/oracle/oradata/ora11g/example01.dbf                                                         ONLINE  YES           952919            1034000

         6 /u01/app/oracle/oradata/ora11g/users02.dbf                                                              OFFLINE NO           1005138            1034000

  1. online数据文件6

SQL> alter database datafile 6 online;

 

Database altered.

  1. 恢复完成,确认数据文件状态

SQL> select file#,name,status,fuzzy,creation_change#,checkpoint_change# from v$datafile_header;

 

     FILE# NAME                                                                                                 STATUS  FUZ CREATION_CHANGE# CHECKPOINT_CHANGE#

---------- ---------------------------------------------------------------------------------------------------- ------- --- ---------------- ------------------

         1 /u01/app/oracle/oradata/ora11g/system01.dbf                                                          ONLINE  YES                 7            1034000

         2 /u01/app/oracle/oradata/ora11g/sysaux01.dbf                                                          ONLINE  YES             1834            1034000

         3 /u01/app/oracle/oradata/ora11g/undotbs01.dbf                                                         ONLINE  YES           923328            1034000

         4 /u01/app/oracle/oradata/ora11g/users01.dbf                                                             ONLINE  YES            16143            1034000

         5 /u01/app/oracle/oradata/ora11g/example01.dbf                                                         ONLINE  YES           952919            1034000

         6 /u01/app/oracle/oradata/ora11g/users02.dbf                                                              ONLINE  YES          1005138            1034717

 

 

 

    

 

 

 


本文题目:无备份有完全归档日志情况下恢复数据文件
转载注明:http://azwzsj.com/article/jcshgj.html