归档模式下一个或多个数据文件损坏恢复

已于 2024-12-09 09:11:57 修改
阅读量386 收藏 6
点赞数 4
分类专栏： Oracle 文章标签： oracle 数据库
于 2024-12-09 08:40:59 首次发布
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.youkuaiyun.com/hf191850699/article/details/144337277
版权

1. 介绍

归档模式下一个或多个数据文件损坏，数据库恢复操作。

关闭数据库，冷备份数据库文件sys* undo* user*文件，启动数据库，创建itpux001 表，插入数据，提交，插入4条数据，插入一次switch logfile，使每个归档文件都有。插入第五条数据，没有提交，在第二窗口上面删除数据库文件sys* undo* user*文件，启动数据库，报错。这时候通过在线重做日志文件和归档日志文件恢复数据。

2. 操作

关闭数据库

SYS@hfzcdb> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

冷备数据库

[oracle@hfzcdb91:/oradata/hfzcdb]$cp sys* undo* user* /backup/hfzcdbhot/

打开数据库

SYS@hfzcdb> startup
ORACLE instance started.

Total System Global Area 2466249672 bytes
Fixed Size                  8899528 bytes
Variable Size             671088640 bytes
Database Buffers         1778384896 bytes
Redo Buffers                7876608 bytes
Database mounted.
Database opened.

创建表，插入数据

SYS@hfzcdb> conn hfedu/hfedu123
Connected.
hfedu@hfzcdb> create table itpux001(id int,scn int) tablespace users;

Table created.

hfedu@hfzcdb> insert into itpux001 values (1,dbms_flashback.get_system_change_number);

1 row created.

hfedu@hfzcdb> commit;

Commit complete.

查询重做日志情况

hfedu@hfzcdb> select * from v$log;
GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME             CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- ----------
1          1          1  209715200        512          1 NO  CURRENT                2182326 2023-03-29 16:22:20   1.8447E+19           0
2          1          0  209715200        512          1 YES UNUSED                       0                                0           0
3          1          0  209715200        512          1 YES UNUSED                       0                                0           0

切换日志

hfedu@hfzcdb> alter system switch logfile;

System altered.

查询重做日志情况

hfedu@hfzcdb> select * from v$log;
GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME             CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- ----------
1          1          1  209715200        512          1 YES ACTIVE                 2182326 2023-03-29 16:22:20      2190614 2023-03-29 18:46:04        0
2          1          2  209715200        512          1 NO  CURRENT                2190614 2023-03-29 18:46:04   1.8447E+19           0
3          1          0  209715200        512          1 YES UNUSED                       0                                0           0

再插入一条数据

hfedu@hfzcdb> insert into itpux001 values (2,dbms_flashback.get_system_change_number);

1 row created.

hfedu@hfzcdb> alter system switch logfile;

System altered.

hfedu@hfzcdb> select * from v$log;
GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME             CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- ----------
1          1          1  209715200        512          1 YES ACTIVE                 2182326 2023-03-29 16:22:20      2190614 2023-03-29 18:46:04        0
2          1          2  209715200        512          1 YES ACTIVE                 2190614 2023-03-29 18:46:04      2190629 2023-03-29 18:46:23        0
3          1          3  209715200        512          1 NO  CURRENT                2190629 2023-03-29 18:46:23   1.8447E+19           0

hfedu@hfzcdb> insert into itpux001 values (3,dbms_flashback.get_system_change_number);

1 row created.

hfedu@hfzcdb> alter system switch logfile;

System altered.

hfedu@hfzcdb> select * from v$log;

GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME             CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- ----------
1          1          4  209715200        512          1 NO  CURRENT                2190643 2023-03-29 18:46:47   1.8447E+19           0
2          1          2  209715200        512          1 YES INACTIVE               2190614 2023-03-29 18:46:04      2190629 2023-03-29 18:46:23        0
3          1          3  209715200        512          1 YES INACTIVE               2190629 2023-03-29 18:46:23      2190643 2023-03-29 18:46:47        0

hfedu@hfzcdb> insert into itpux001 values (4,dbms_flashback.get_system_change_number);

1 row created.

hfedu@hfzcdb> alter system switch logfile;

System altered.

hfedu@hfzcdb> commit
2  ;

Commit complete.

hfedu@hfzcdb> select * from itpux001;

ID        SCN
---------- ----------
1    2190591
2    2190624
3    2190633
4    2190650

插入第五条数据没有提交

hfedu@hfzcdb> insert into itpux001 values (5,dbms_flashback.get_system_change_number);

1 row created.

hfedu@hfzcdb> select * from itpux001;

ID        SCN
---------- ----------
1    2190591
2    2190624
3    2190633
4    2190650
5    2190696

另外一个窗口上面删除数据库文件

[oracle@hfzcdb91:/oradata/hfzcdb]$rm sys* undo* user*

打开数据库，报文件丢失

[oracle@hfzcdb91:/oradata/hfzcdb]$
hfedu@hfzcdb> conn / as sysdba
Connected.
SYS@hfzcdb> shutdown abort
ORACLE instance shut down.
SYS@hfzcdb> startup
ORACLE instance started.

Total System Global Area 2466249672 bytes
Fixed Size                  8899528 bytes
Variable Size             671088640 bytes
Database Buffers         1778384896 bytes
Redo Buffers                7876608 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 1 - see DBWR trace file
ORA-01110: data file 1: '/oradata/hfzcdb/system01.dbf'

控制文件的CHECKPOINT_CHANGE一致的，没有问题:

SYS@hfzcdb> select file#,checkpoint_change# from v$datafile;   

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1            2190643
2            2190643
3            2190643
4            2190643
5            2190643
6            2190643

6 rows selected.

数据文件的CHECKPOINT_CHANGE不一致，丢失

SYS@hfzcdb> select file#,checkpoint_change# from v$datafile_header;
FILE# CHECKPOINT_CHANGE#
---------- ------------------
1                  0
2                  0
3                  0
4                  0
5            2190643
6            2190643

6 rows selected.

把插入数据之前冷备份的文件拷贝回来恢复

[oracle@hfzcdb91:/oradata/hfzcdb]$cp /backup/hfzcdbhot/ sys* undo* user* /oradata/hfzcdb/  
cp: omitting directory ‘/backup/hfzcdbhot/’
cp: cannot stat ‘sys*’: No such file or directory
cp: cannot stat ‘undo*’: No such file or directory
cp: cannot stat ‘user*’: No such file or directory
[oracle@hfzcdb91:/oradata/hfzcdb]$cp /backup/hfzcdbhot/sys* undo* user* /oradata/hfzcdb/
cp: cannot stat ‘undo*’: No such file or directory
cp: cannot stat ‘user*’: No such file or directory
[oracle@hfzcdb91:/oradata/hfzcdb]$cp /backup/hfzcdbhot/undo* user* /oradata/hfzcdb/
cp: cannot stat ‘user*’: No such file or directory
[oracle@hfzcdb91:/oradata/hfzcdb]$cp /backup/hfzcdbhot/user* /oradata/hfzcdb/

此时再查询， CHECKPOINT_CHANGE号回来了，但是不一致

SYS@hfzcdb> select file#,checkpoint_change# from v$datafile_header;   

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1            2189312
2            2189312
3            2189312
4            2189312
5            2190643
6            2190643

6 rows selected.

查询需要恢复的文件有哪些

SYS@hfzcdb> select * from v$recover_file;   
FILE# ONLINE  ONLINE_ ERROR                                                                CHANGE# TIME                    CON_ID
---------- ------- ------- ----------------------------------------------------------------- ---------- ------------------- ----------
1 ONLINE  ONLINE                                                                       2189312 2023-03-29 18:41:10          0
2 ONLINE  ONLINE                                                                       2189312 2023-03-29 18:41:10          0
3 ONLINE  ONLINE                                                                       2189312 2023-03-29 18:41:10          0
4 ONLINE  ONLINE                                                                       2189312 2023-03-29 18:41:10          0

查询需要恢复文件需要的日志文件有哪些

SYS@hfzcdb> select * from v$recovery_log;
THREAD#  SEQUENCE# TIME
---------- ---------- -------------------
ARCHIVE_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
CON_ID
----------
1          1 2023-03-29 16:22:20
/archive/HFZCDB/archivelog/2023_03_29/o1_mf_1_1_l285qdc4_.arc
0

1          2 2023-03-29 18:46:04
/archive/HFZCDB/archivelog/2023_03_29/o1_mf_1_2_l285qzrv_.arc
0

也可以通过change来查询

SYS@hfzcdb> select * from v$archived_log where first_change#<=2190643 and next_change#>=2189312;
RECID      STAMP NAME                                        DEST_ID    THREAD#  SEQUENCE# RESETLOGS_CHANGE# RESETLOGS_TIME      RESETLOGS_ID FIRST_CHANGE# FIRST_TIME        NEXT_CHANGE#
---------- ---------- ---------------------------------------- ---------- ---------- ---------- ----------------- ------------------- ------------ ------------- ------------------- ------------
NEXT_TIME               BLOCKS BLOCK_SIZE CREATOR REGISTR STA ARC APPLIED   DEL S COMPLETION_TIME     DIC DIC END BACKUP_COUNT ARCHIVAL_THREAD# ACTIVATION# IS_ COM FAL END_OF_RED BAC           CON_ID
------------------- ---------- ---------- ------- ------- --- --- --------- --- - ------------------- --- --- --- ------------ ---------------- ----------- --- --- --- ---------- --- ----------
23 1132771564 /archive/HFZCDB/archivelog/2023_03_29/o1          1          1          1           2182326 2023-03-29 16:22:20   1132762940     2182326 2023-03-29 16:22:20      2190614
_mf_1_1_l285qdc4_.arc
2023-03-29 18:46:04      15561        512 ARCH    ARCH    NO  YES NO        NO  A 2023-03-29 18:46:04 NO  NO  NO             0                1   799822903 YES NO  NO                   NO           0

24 1132771583 /archive/HFZCDB/archivelog/2023_03_29/o1          1          1          2           2182326 2023-03-29 16:22:20   1132762940     2190614 2023-03-29 18:46:04      2190629
_mf_1_2_l285qzrv_.arc
2023-03-29 18:46:23          7        512 ARCH    ARCH    NO  YES NO        NO  A 2023-03-29 18:46:23 NO  NO  NO             0                1   799822903 YES NO  NO                   NO           0

25 1132771607 /archive/HFZCDB/archivelog/2023_03_29/o1          1          1          3           2182326 2023-03-29 16:22:20   1132762940     2190629 2023-03-29 18:46:23      2190643
_mf_1_3_l285rqvn_.arc
2023-03-29 18:46:47         10        512 ARCH    ARCH    NO  YES NO        NO  A 2023-03-29 18:46:47 NO  NO  NO             0                1   799822903 YES NO  NO                   NO           0

26 1132771632 /archive/HFZCDB/archivelog/2023_03_29/o1          1          1          4           2182326 2023-03-29 16:22:20   1132762940     2190643 2023-03-29 18:46:47      2190654
_mf_1_4_l285sjy1_.arc
2023-03-29 18:47:12          5        512 ARCH    ARCH    NO  YES NO        NO  A 2023-03-29 18:47:12 NO  NO  NO             0                1   799822903 YES NO  NO                   NO           0

recover 数据库

SYS@hfzcdb> recover database;   -- 也可以用 recover datafile 1,2 ,3 ,4 
ORA-00279: change 2189312 generated at 03/29/2023 18:41:10 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_03_29/o1_mf_1_1_l285qdc4_.arc
ORA-00280: change 2189312 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2190614 generated at 03/29/2023 18:46:04 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_03_29/o1_mf_1_2_l285qzrv_.arc
ORA-00280: change 2190614 for thread 1 is in sequence #2


Log applied.
Media recovery complete.

查询datafile 中 scn 一致：

SYS@hfzcdb> select file#,checkpoint_change# from v$datafile;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1            2290739
2            2290739
3            2290739
4            2290739
5            2290739
6            2290739

6 rows selected.

查询数据文件头 scn 一致：

SYS@hfzcdb> select file#,checkpoint_change# from v$datafile_header;

FILE# CHECKPOINT_CHANGE#
---------- ------------------
1            2290739
2            2290739
3            2290739
4            2290739
5            2290739
6            2290739

6 rows selected.

查询需要恢复的日志：

SYS@hfzcdb> select * from v$recovery_log;

no rows selected

SYS@hfzcdb> select * from v$log;
GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME             CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- ----------
1          1          4  209715200        512          1 YES ACTIVE                 2190643 2023-03-29 18:46:47      2190654 2023-03-29 18:47:12        0
3          1          3  209715200        512          1 YES INACTIVE               2190629 2023-03-29 18:46:23      2190643 2023-03-29 18:46:47        0
2          1          5  209715200        512          1 NO  CURRENT                2190654 2023-03-29 18:47:12   1.8447E+19           0

打开数据库：

SYS@hfzcdb> alter database open;

Database altered.

查询数据恢复：

SYS@hfzcdb> select * from v$log;
GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME          NEXT_CHANGE# NEXT_TIME             CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- ----------
1          1          4  209715200        512          1 YES INACTIVE               2190643 2023-03-29 18:46:47      2190654 2023-03-29 18:47:12        0
2          1          5  209715200        512          1 YES INACTIVE               2190654 2023-03-29 18:47:12      2390741 2023-03-29 19:05:48        0
3          1          6  209715200        512          1 NO  CURRENT                2390741 2023-03-29 19:05:48   1.8447E+19           0


SYS@hfzcdb> select * from hfedu.itpux001;

ID        SCN
---------- ----------
1    2190591
2    2190624
3    2190633
4    2190650
5    2190696

SYS@hfzcdb>

3. 总结

数据库报文件文件丢失后，通过命令查询控制文件的checkpoint一致，数据文件不一致，而且删除的文件为0，这时候把之前备份的sys* undo* user*文件拷贝到数据库目录，重新查询，数据文件checkpoint不一致，这时候需要通过recover database; 恢复数据库，启动数据库前log的checkpoit还小于恢复的，启动数据库后恢复正常，查询数据，发现没有提交的第5条数据，也在表里面，说明了没有关库的时候，这条记录已经刷到重做日志里面，再到归档里面去了。通过归档恢复了。

评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包

打赏作者

莳花微语

你的鼓励将是我创作的最大动力

¥1 ¥2 ¥4 ¥6 ¥10 ¥20
扫码支付：¥1
获取中
扫码支付

您的余额不足，请更换扫码支付或充值

打赏作者

实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值