介绍
通过实操案例,熟悉oracle数据库在归档模式下,非系统数据文件丢失后,怎么完全恢复数据的过程和需要注意的地方,希望可以对读者带来帮助。
1. 环境准备
查询数据库有没有需要恢复的文件
SYS@hfzcdb> select * from v$recover_file;
关库冷备
su - oracle
shutdown immediate;
mkdir /backup/hfzcdb1
mkdir /backup/hfzcdb2
mkdir /backup/hfzcdbhot
cp /oradata/hfzcdb/* /backup/hfzcdb1
startup
归档打开
SYS@hfzcdb> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 12
Next log sequence to archive 16
Current log sequence 16
准备环境
create tablespace hfedu datafile '/oradata/hfzcdb/hfedu01.dbf' size 20m;
create user hfedu identified by hfedu123 default tablespace hfedu;
grant dba to hfedu;
sqlplus "/as sysdba";
conn hfedu/hfedu123
create table hfedu(id int,name varchar2(10));
insert into hfedu values(1,'hfedu01');
commit;
conn /as sysdba;
shutdown immediate;
再冷备一份
host cp /oradata/hfzcdb/* /backup/hfzcdb2
*/
startup
conn hfedu/hfedu123;
select * from hfedu;
insert into hfedu values(2,'hfedu02');
commit;
select * from hfedu;
ID NAME
---------- ----------
1 hfedu01
2 hfedu02
修改表空间的备份模式,备份已经修改的数据文件
!mkdir /backup/hfzcdbhot
conn /as sysdba;
alter tablespace hfedu begin backup;
host cp /oradata/hfzcdb/hfedu01.dbf /backup/hfzcdbhot
alter tablespace hfedu end backup;
conn hfedu/hfedu123;
insert into hfedu values(3,'hfedu03');
commit;
select * from hfedu;
ID NAME
---------- ----------
1 hfedu01
2 hfedu02
3 hfedu03
切换归档日志
alter system switch logfile;
2. 数据库关闭下如何完全恢复丢失的数据文件
--【只丢失数据文件,归档文件和重做日志文件都在】
包括系统表空间(系统数据文件)、Undo表空间、整个数据库
步骤:
hfedu@hfzcdb> conn / as sysdba
SYS@hfzcdb> host rm -f /oradata/hfzcdb/*.dbf
SYS@hfzcdb> shutdown abort
[oracle@hfzcdb91:/oradata/hfzcdb]$ls -lsa
hfeduSQL> 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'
hfeduSQL> select * from v$recovery_log; 【数据库中需要恢复的日志】
hfeduSQL> select * from v$recover_file;【数据库中需要恢复的文件】
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ------- ------- ----------------------------------------------------------------- ---------- ------------------- ----------
1 ONLINE ONLINE FILE NOT FOUND 0 0
2 ONLINE ONLINE FILE NOT FOUND 0 0
3 ONLINE ONLINE FILE NOT FOUND 0 0
4 ONLINE ONLINE FILE NOT FOUND 0 0
6 ONLINE ONLINE FILE NOT FOUND 0 0
hfeduSQL> host cp /backup/hfzcdb2/*.dbf /oradata/hfzcdb/
hfeduSQL> host cp /backup/hfzcdbhot/hfedu02.dbf /oradata/hfzcdb
hfeduSQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/oradata/hfzcdb/system01.dbf'
hfeduSQL> recover database; 【重做日志中恢复的日志,就不需要归档日志,直接用redo日志恢复】
alter日志:
Media Recovery Start
Started logmerger process
2023-03-28T18:14:34.360123+08:00
Parallel Media Recovery started with 4 slaves
2023-03-28T18:14:34.743870+08:00
Recovery of Online Redo Log: Thread 1 Group 6 Seq 41 Reading mem 0
Mem# 0: /oradata/hfzcdb/redo06.rdo
2023-03-28T18:14:37.404648+08:00
Recovery of Online Redo Log: Thread 1 Group 1 Seq 42 Reading mem 0
Mem# 0: /oradata/hfzcdb/redo01.rdo
2023-03-28T18:14:38.275438+08:00
Recovery of Online Redo Log: Thread 1 Group 2 Seq 43 Reading mem 0
Mem# 0: /oradata/hfzcdb/redo02.rdo
2023-03-28T18:14:39.471615+08:00
Recovery of Online Redo Log: Thread 1 Group 3 Seq 44 Reading mem 0
Mem# 0: /oradata/hfzcdb/redo03.rdo
2023-03-28T18:14:39.587248+08:00
Recovery of Online Redo Log: Thread 1 Group 4 Seq 45 Reading mem 0
Mem# 0: /oradata/hfzcdb/redo04.rdo
2023-03-28T18:14:40.161213+08:00
Recovery of Online Redo Log: Thread 1 Group 5 Seq 46 Reading mem 0
Mem# 0: /oradata/hfzcdb/redo05.rdo
2023-03-28T18:14:43.988793+08:00
Media Recovery Complete (hfzcdb)
Completed: ALTER DATABASE RECOVER database
2023-03-28T18:14:56.818204+08:00
alter database open
hfeduSQL> recover database;【重做日志中没有,需要用归档日志文件恢复】
ORA-00279: change 1986594 generated at 03/27/2023 21:05:38 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_03_27/o1_mf_1_41_l237wpsm_.arc
hfeduSQL> select * from v$recovery_log;
THREAD# SEQUENCE# TIME ARCHIVE_NAME CON_ID
---------- ---------- ------------------- ---------------------------------------- ----------
1 42 2023-03-27 21:52:22 0
1 43 2023-03-28 11:43:39 /archive/HFZCDB/archivelog/2023_03_28/o1 0
_mf_1_43_l24skhoc_.arc1 44 2023-03-28 11:59:43 /archive/HFZCDB/archivelog/2023_03_28/o1 0
_mf_1_44_l24skkz4_.arc1 45 2023-03-28 11:59:45 /archive/HFZCDB/archivelog/2023_03_28/o1 0
_mf_1_45_l24w22vq_.arc1 46 2023-03-28 12:42:42 /archive/HFZCDB/archivelog/2023_03_28/o1 0
_mf_1_46_l25hk27b_.arc1 47 2023-03-28 18:14:57 /archive/HFZCDB/archivelog/2023_03_28/o1 0
_mf_1_47_l25jl6ll_.arc1 48 2023-03-28 18:32:38 /archive/HFZCDB/archivelog/2023_03_28/o1 0
_mf_1_48_l25jl86d_.arc1 49 2023-03-28 18:32:39 /archive/HFZCDB/archivelog/2023_03_28/o1 0
_mf_1_49_l25jlk31_.arc
hfeduSQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME CON_ID
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------------- ------------ ------------------- ----------
1 1 54 268435456 512 1 YES INACTIVE 2711950 2023-03-28 18:32:54 2711955 2023-03-28 18:33:03 0
2 1 55 268435456 512 1 NO CURRENT 2711955 2023-03-28 18:33:03 1.8447E+19 0
6 1 53 268435456 512 1 YES INACTIVE 2711947 2023-03-28 18:32:54 2711950 2023-03-28 18:32:54 0
4 1 51 268435456 512 1 YES INACTIVE 2711941 2023-03-28 18:32:50 2711944 2023-03-28 18:32:52 0
5 1 52 268435456 512 1 YES INACTIVE 2711944 2023-03-28 18:32:52 2711947 2023-03-28 18:32:54 0
3 1 50 268435456 512 1 YES INACTIVE 2711938 2023-03-28 18:32:48 2711941 2023-03-28 18:32:50 06 rows selected.
【如果备份之后,还增加了表空间的话,还需要重建控制文件,再恢复】
Media recovery complete.
hfeduSQL> alter database open;
3. 数据库打开下如何完全恢复丢失的非系统数据文件
【系统文件先offline打开数据库后,再进行恢复】
hfeduSQL> host rm /oradata/hfzcdb/hfedu03.dbf
hfeduSQL> conn hfedu/hfedu123
hfedu@hfzcdb> insert into hfedu3 values(5,'hfedu5');
insert into hfedu3 values(5,'hfedu5')
*
ERROR at line 1:
ORA-01116: error in opening database file 6
ORA-01110: data file 6: '/oradata/hfzcdb/hfedu03.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
hfedu@hfzcdb> select *from v$recover_file;
no rows selected
hfedu@hfzcdb> alter system checkpoint;
ERROR:
ORA-03114: not connected to ORACLE
hfeduSQL> 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 6 - see DBWR trace file
ORA-01110: data file 6: '/oradata/hfzcdb/hfedu03.dbf'
只要不是系统文件,这时候是可以把这个文件offline,打开数据库的
hfeduSQL> alter database datafile 6 offline; Database altered.
再打开数据库
hfeduSQL> alter database open;
Database altered.
再查询哪个文件需要恢复
hfeduSQL> select * from v$recover_file; -- 6号文件需要恢复
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ------- ------- ----------------------------------------------------------------- ---------- ------------------- ----------
6 OFFLINE OFFLINE FILE NOT FOUND 0 0
将之前备份的文件拷贝过来,进行恢复。
hfeduSQL> host cp /backup/hfzcdb1/hfedu03.dbf /oradata/hfzcdb/
hfeduSQL> recover datafile 6;
ORA-00279: change 1724884 generated at 03/28/2023 20:36:10 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_03_28/o1_mf_1_26_l25rbmdp_.arc
ORA-00280: change 1724884 for thread 1 is in sequence #26Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1725794 generated at 03/28/2023 20:45:07 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_03_28/o1_mf_1_27_l25rbwpm_.arc
ORA-00280: change 1725794 for thread 1 is in sequence #27ORA-00279: change 1725808 generated at 03/28/2023 20:45:16 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_03_28/o1_mf_1_28_l25rc1o6_.arc
ORA-00280: change 1725808 for thread 1 is in sequence #28ORA-00279: change 1725814 generated at 03/28/2023 20:45:21 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_03_28/o1_mf_1_29_l25rc4p9_.arc
ORA-00280: change 1725814 for thread 1 is in sequence #29Log applied.
Media recovery complete.
再将 6 号文件 online,查询备份之后插入的数据还在。
hfeduSQL> conn hfedu/hfedu123
Connected.
hfedu@hfzcdb> select * from hfedu3;
select * from hfedu3
*
ERROR at line 1:
ORA-00376: file 6 cannot be read at this time
ORA-01110: data file 6: '/oradata/hfzcdb/hfedu03.dbf'
hfedu@hfzcdb> alter database datafile 6 online;Database altered.hfedu@hfzcdb> select * from hfedu3;ID NAME
---------- ----------------------------------------
1 hfedu01
2 hfedu02
3 hfedu03
4 hfedu04
4. 数据文件无备份情况下的如何完全恢复
【无备份的数据文件,需要在开启归档之前备份,或者是需要全部的归档文件才可以恢复】
SYS@hfzcdb> conn hfedu/hfedu123
Connected.
hfedu@hfzcdb> insert into hfedu3 values(5,'hfedu5');1 row created.hfedu@hfzcdb> alter system checkpoint;System altered.hfedu@hfzcdb> alter system switch logfile;System altered.hfedu@hfzcdb> host rm /oradata/hfzcdb/hfedu03.dbfhfedu@hfzcdb> insert into hfedu3 values(6,'hfedu6');1 row created.hfedu@hfzcdb> commit
2 ;Commit complete.hfedu@hfzcdb> alter system checkpoint;System altered.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 6 - see DBWR trace file
ORA-01110: data file 6: '/oradata/hfzcdb/hfedu03.dbf'
将非 system 文件,offline 掉。
SYS@hfzcdb> alter database datafile 6 offline;Database altered.
查询数据库中需要恢复的文件
SYS@hfzcdb> select * from v$recover_file;
FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ------- ------- ----------------------------------------------------------------- ---------- ------------------- ----------
6 OFFLINE OFFLINE FILE NOT FOUND 0 0
手动创建 6 号文件
SYS@hfzcdb> alter database create datafile 6;Database altered.SYS@hfzcdb> select * from v$recover_file;FILE# ONLINE ONLINE_ ERROR CHANGE# TIME CON_ID
---------- ------- ------- ----------------------------------------------------------------- ---------- ------------------- ----------
6 OFFLINE OFFLINE 1723336 2023-03-28 20:25:34 0
启动数据库
SYS@hfzcdb> alter database open;
Database altered.SYS@hfzcdb> select * from v$recovery_log;
SYS@hfzcdb> host ls -ls /oradata/hfzcdb/
total 2734828
43920 -rw-r-----. 1 oracle oinstall 44974080 Mar 28 21:16 control01.ctl
43920 -rw-r-----. 1 oracle oinstall 44974080 Mar 28 21:16 control02.ctl
102416 -rw-r----- 1 oracle oinstall 104873984 Mar 28 21:15 hfedu02.dbf
51216 -rw-r----- 1 oracle oinstall 52445184 Mar 28 21:14 hfedu03.dbf
204804 -rw-r-----. 1 oracle oinstall 209715712 Mar 28 21:15 redo01.log
204804 -rw-r-----. 1 oracle oinstall 209715712 Mar 28 21:15 redo02.log
204804 -rw-r-----. 1 oracle oinstall 209715712 Mar 28 21:15 redo03.log
675856 -rw-r----- 1 oracle oinstall 692076544 Mar 28 21:15 sysaux01.dbf
860176 -rw-r----- 1 oracle oinstall 880820224 Mar 28 21:15 system01.dbf
4960 -rw-r----- 1 oracle oinstall 20987904 Mar 28 20:50 temp01.dbf
332816 -rw-r----- 1 oracle oinstall 340803584 Mar 28 21:15 undotbs01.dbf
5136 -rw-r----- 1 oracle oinstall 5259264 Mar 28 21:15 users01.dbf
再通过归档和在线日志文件进行恢复
这个时候的归档日志文件要有创建 6 号文件以后的所有归档才可以恢复
SYS@hfzcdb> recover datafile 6 【开启归档之后创建的文件才可以恢复】
ORA-00279: change 1723336 generated at 03/28/2023 20:25:34 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_03_28/o1_mf_1_26_l25rbmdp_.arc
ORA-00280: change 1723336 for thread 1 is in sequence #26Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 1725794 generated at 03/28/2023 20:45:07 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_03_28/o1_mf_1_27_l25rbwpm_.arc
ORA-00280: change 1725794 for thread 1 is in sequence #27ORA-00279: change 1725808 generated at 03/28/2023 20:45:16 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_03_28/o1_mf_1_28_l25rc1o6_.arc
ORA-00280: change 1725808 for thread 1 is in sequence #28ORA-00279: change 1725814 generated at 03/28/2023 20:45:21 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_03_28/o1_mf_1_29_l25rc4p9_.arc
ORA-00280: change 1725814 for thread 1 is in sequence #29ORA-00279: change 1725820 generated at 03/28/2023 20:45:24 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_03_28/o1_mf_1_30_l25rpj1z_.arc
ORA-00280: change 1725820 for thread 1 is in sequence #30ORA-00279: change 1925835 generated at 03/28/2023 20:51:28 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_03_28/o1_mf_1_31_l25s4850_.arc
ORA-00280: change 1925835 for thread 1 is in sequence #31ORA-00279: change 2026748 generated at 03/28/2023 20:58:48 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_03_28/o1_mf_1_32_l25svkt3_.arc
ORA-00280: change 2026748 for thread 1 is in sequence #32Log applied.
Media recovery complete.
打开数据库,查询数据已经恢复
SYS@hfzcdb> alter database datafile 6 online;Database altered.SYS@hfzcdb> select * from hfedu.hfedu3;ID NAME
---------- ----------------------------------------
1 hfedu01
2 hfedu02
3 hfedu03
4 hfedu04
5 hfedu5
6 hfedu66 rows selected.SYS@hfzcdb>