Oracle 用户管理模式下的恢复案例-完全恢复

🕗 发布于 2024-12-10 04:32 oracle 数据库 备份恢复

介绍

通过实操案例，熟悉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_.arc

1         44 2023-03-28 11:59:43 /archive/HFZCDB/archivelog/2023_03_28/o1          0
_mf_1_44_l24skkz4_.arc

1         45 2023-03-28 11:59:45 /archive/HFZCDB/archivelog/2023_03_28/o1          0
_mf_1_45_l24w22vq_.arc

1         46 2023-03-28 12:42:42 /archive/HFZCDB/archivelog/2023_03_28/o1          0
_mf_1_46_l25hk27b_.arc

1         47 2023-03-28 18:14:57 /archive/HFZCDB/archivelog/2023_03_28/o1          0
_mf_1_47_l25jl6ll_.arc

1         48 2023-03-28 18:32:38 /archive/HFZCDB/archivelog/2023_03_28/o1          0
_mf_1_48_l25jl86d_.arc

1         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        0

6 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 #26


Specify 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 #27


ORA-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 #28


ORA-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 #29


Log 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.dbf

hfedu@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 #26


Specify 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 #27


ORA-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 #28


ORA-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 #29


ORA-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 #30


ORA-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 #31


ORA-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 #32


Log 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 hfedu6

6 rows selected.

SYS@hfzcdb>

原文地址：https://blog.csdn.net/hf191850699/article/details/144280542

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！

上一篇：XiYan-SQL：⼀种多⽣成器集成的Text-to-SQL框架
下一篇：Ubuntu中安装配置交叉编译工具并进行测试

.NET(C#) 如何配置用户首选项及保存用户设置
.NET(C#) 如何配置用户首选项及保存用户设置
阅读更多2024-12-14
【最新】北大数字普惠金融指数数据集-省市县（2011-2023年）
郭峰,王靖一,王芳,孔涛,张勋,程志云.测度中国数字普惠金融发展:指数编制与空间特征[J].经济学(季刊),2020,19(04):1401-1418.时间跨度：省级和城市级指数时间跨度为2011-2
阅读更多2024-12-14
GESP202412 四级【Recamán】题解（AC）
a11ak−1−kkakak−1−kak−1k小杨想知道 Recamán 数列的前n项从小到大排序后的结果。手动计算非常困难，小杨希望你能帮他解决这个问题。
阅读更多2024-12-14
IDEA遇到EasyConnect中的网络资源无法访问的问题
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。原文链接：https://blog.csdn.net/wanshanyu_/article/de
阅读更多2024-12-14
双目摄像头标定方法
此时已经完成标定，左下角为反投影误差，右边为外参可视化。将双目左右目拍的图像上传（左右目最好不少于20张）此时回到主页面，即可看到成功导出。把这些误差大的删除即可。
阅读更多2024-12-14
Servlet、omcat服务器架构与工作原理
Servlet是运行在服务器端的Java程序，它的主要职责之一是接收并处理来自客户端（如浏览器）的HTTP请求。当客户端发送一个请求到服务器时，Servlet可以解析请求中的信息，例如请求的URL路径
阅读更多2024-12-14
Vue生命周期钩子函数：深入解析与实践
作为高级Vue前端开发人员，对Vue组件的生命周期钩子函数有着深刻的理解是至关重要的。生命周期钩子函数是指在Vue组件的创建、更新、销毁等过程中，Vue自动调用的一系列方法。通过这些钩子函数，我们可以
阅读更多2024-12-14
安卓开发--使用android studio发布APP
app发布
阅读更多2024-12-14
数据结构与算法学习笔记----拓扑排序
@ author: 明月清了个风。
阅读更多2024-12-14
python 将数据保存到现有的Excel文件的新工作表
out_file = ‘query.xlsx’df1 = pd.DataFrame(out_data)若直接写入：df1.to_excel(out_file, index=False, sheet_n
阅读更多2024-12-14

Oracle 用户管理模式下的恢复案例-完全恢复

介绍

1. 环境准备

2. 数据库关闭下如何完全恢复丢失的数据文件

3. 数据库打开下如何完全恢复丢失的非系统数据文件

4. 数据文件无备份情况下的如何完全恢复

相关文章