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

🕗 发布于 2024-12-09 00:53 oracle 数据库

1. 不完全恢复的几种常用方法

01. recover database using backup controlfile

如果丢失当前控制文件，用冷备份的控制文件恢复的时候，用来告诉 oracle，不要以 controlfile 中的 scn 作为恢复的终点；

02. recover database until cancel

如果丢失 current/active redo 的时候，手动指定终点。

recover database until cancel; --SQLPlus 使用 
recover database until time '2022-08-09:14:20:45' --SQLPlus 与 RMAN 都支持 
recover database unitl time '2022-08:14:20:45' using backup controlfile 
recover database until change 55555 --SQLPlus 使用 
recover database until scn 55555 --RMAN 使用 
recover database until sequence 20 --RMAN 使用

03. recover database using backup controlfile until cancel;

如果丢失当前 controlfile 并且 current/active redo 都丢失，会先去自动应用归档日志,可以实现最大的恢复；

04. recover database until cancel using backup controlfile;

如果丢失当前 controlfile 并且 current/active redo 都丢失，以旧的 redo 中的 scn为恢复终点。因为没有应用归档日志，所以会丢失数据。

2. 实操 1 基于【until time】不完全恢复

说明：数据库需要在归档模式下面操作

在数据库冷备份后，在表中插入一条数据，提交，记录当前数据库的时间，dorp掉表，删除数据库的dbf文件，控制文件和日志文件还在，然后把数据库冷备份拷贝过来，用recover database until time '2023-03-28 22:05:05';恢复数据库到插入数据之后，用 alter database open RESETLOGS;启动数据库，查询最后提交的数据没有丢失。

--先做冷备 
shutdown immediate; 
SQL> host rm -rf /backup/hfzcdb/*.* 
*/
SQL> host cp /oradata/hfzcdb/* /backup/hfzcdb/ --先做冷备 */ 
set time on; 

SYS@hfzcdb> shutdown immediate
SYS@hfzcdb> host cp /oradata/hfzcdb/* /backup/hfzcdb/
*/
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> set time on
22:02:35 SYS@hfzcdb> conn hfedu/hfedu123
Connected.
22:03:01 hfedu@hfzcdb> insert into hfedu3 values(44,'hfedu44');

1 row created.

22:03:22 hfedu@hfzcdb> commit；
22:04:38 hfedu@hfzcdb> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,' yyyy-mm-dd hh24:mi:ss')  
-------------------
2023-03-28 22:05:05

22:05:05 hfedu@hfzcdb> drop table hfedu3;

Table dropped.

22:05:28 hfedu@hfzcdb> select * from hfedu3;
select * from hfedu3
*
ERROR at line 1:
ORA-00942: table or view does not exist
22:05:33 hfedu@hfzcdb> conn / as sysdba
Connected.
22:05:41 SYS@hfzcdb> shutdown immedaite
SP2-0717: illegal SHUTDOWN option
22:05:54 SYS@hfzcdb> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
22:06:34 SYS@hfzcdb> host rm /oradata/hfzcdb/*.dbf    
*/

22:07:06 SYS@hfzcdb> host cp /backup/hfzcdb/*.dbf /oradata/hfzcdb/
*/
22:08:45 SYS@hfzcdb> startup mount
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.
22:09:11 SYS@hfzcdb> select file#,checkpoint_change# from v$datafile;

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

6 rows selected.

22:09:14 SYS@hfzcdb> select file#,checkpoint_change# from v$datafile_header;

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

6 rows selected.

22:09:33 SYS@hfzcdb> recover database until time '2023-03-28 22:05:05';
Media recovery complete.
22:10:40 SYS@hfzcdb> alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open


22:10:53 SYS@hfzcdb> alter database open RESETLOGS;

Database altered.

22:11:21 SYS@hfzcdb> select *from hfedu.hfedu3;

ID NAME
---------- ----------------------------------------
1 hfedu01
2 hfedu02
3 hfedu03
4 hfedu04
5 hfedu5
6 hfedu6
44 hfedu44

7 rows selected.

22:11:41 SYS@hfzcdb>

3. 实操 2 基于【Until Cancel】不完全恢复

在数据库冷备份后，在表中插入一条数据，把数据刷到日志文件，在checkpoint到数据文件，再插入一条,没有刷到日志文件，删除/oradata/hfzcdb/下面所有的数据包括控制文件和日志文件，再把之前冷备份的数据拷贝回来，把数据库启动到nomount状态，重建控制文件，使用recover database using backup controlfile until cancel; 在原来的控制文件基础上面恢复，实现最大的恢复，选择自动恢复，然后再alter database open RESETLOGS;数据库，数据能恢复到插入的第一条数据，第二条没有写入归档日志的，恢复不了。

hfeduSQL> conn hfedu/hfedu123
Connected.
hfedu@hfzcdb> select *from hfedu;

ID NAME
---------- ----------------------------------------
1 hfedu01
55 hfedu05
66 hfedu06
77 hfedu07
88 hfedu08


hfedu@hfzcdb> insert into hfedu values(99,'hfedu09');

1 row created.

hfedu@hfzcdb> commit;

Commit complete.

hfedu@hfzcdb> alter system switch logfile;

System altered.

hfedu@hfzcdb> alter system checkpoint;

System altered.
-----------------------
[oracle@hfzcdb91:/archive/HFZCDB/archivelog/2023_12_25]$ll -lst
total 92196
416 -rw-r----- 1 oracle oinstall   422400 Dec 25 16:10 o1_mf_1_1_lrlg7o4n_.arc
396 -rw-r----- 1 oracle oinstall   405504 Dec 25 16:01 o1_mf_1_1_lrlfpvlc_.arc
16 -rw-r----- 1 oracle oinstall    15872 Dec 25 15:46 o1_mf_1_3_lrldszlc_.arc
28 -rw-r----- 1 oracle oinstall    26112 Dec 25 15:43 o1_mf_1_2_lrldo70m_.arc
1512 -rw-r----- 1 oracle oinstall  1544704 Dec 25 15:38 o1_mf_1_1_lrldbxly_.arc
89828 -rw-r----- 1 oracle oinstall 91983872 Dec 25 14:51 o1_mf_1_19_lrl9n0wk_.arc
[oracle@hfzcdb91:/archive/HFZCDB/archivelog/2023_12_25]$strings o1_mf_1_1_lrlg7o4n_.arc |grep hfedu09
hfedu09
-----------------------
hfedu@hfzcdb> insert into hfedu values(991,'hfedu091');

1 row created.

hfedu@hfzcdb> commit;

Commit complete.

hfedu@hfzcdb> alter system checkpoint;

System altered.

hfedu@hfzcdb> host ll /oradata/hfzcdb/*

*/
/bin/bash: ll: command not found

hfedu@hfzcdb> host ll /oradata/hfzcdb/
/bin/bash: ll: command not found

hfedu@hfzcdb> host ls /oradata/hfzcdb/
control01.ctl  hfedu01.dbf  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf
control02.ctl  redo01.log   redo03.log  system01.dbf  undotbs01.dbf

hfedu@hfzcdb> host rm -f /oradata/hfzcdb/*
*/
hfedu@hfzcdb> conn / as sysdba
Connected.
SYS@hfzcdb> shutdown abort
ORACLE instance shut down.
SYS@hfzcdb> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
exit[oracle@hfzcdb91:/home/oracle]$sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 25 16:14:07 2023
Version 19.3.0.0.0

Copyright (c) 1982, 2019, Oracle.  All rights reserved.

Connected to an idle instance.

hfeduSQL> host cp /backup/hfzcdb/*.dbf /oradata/hfzcdb/
*/
STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "HFZCDB" RESETLOGS  ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 8192
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/oradata/hfzcdb/redo01.log'  SIZE 200M BLOCKSIZE 512,
GROUP 2 '/oradata/hfzcdb/redo02.log'  SIZE 200M BLOCKSIZE 512,
GROUP 3 '/oradata/hfzcdb/redo03.log'  SIZE 200M BLOCKSIZE 512
-- STANDBY LOGFILE
DATAFILE
'/oradata/hfzcdb/system01.dbf',
'/oradata/hfzcdb/sysaux01.dbf',
'/oradata/hfzcdb/undotbs01.dbf',
'/oradata/hfzcdb/users01.dbf',
'/oradata/hfzcdb/hfedu01.dbf'
CHARACTER SET AL32UTF8
;
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
hfeduSQL>   2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18   19
Control file created.

hfeduSQL>  recover database using backup controlfile until cancel;
ORA-00279: change 1428569 generated at 12/25/2023 15:49:24 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_12_25/o1_mf_1_4_%u_.arc
ORA-00280: change 1428569 for thread 1 is in sequence #4


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-19906: recovery target incarnation changed during recovery


ORA-01112: media recovery not started


hfeduSQL>  recover database using backup controlfile until cancel;
ORA-00279: change 1428571 generated at 12/25/2023 16:05:05 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_12_25/o1_mf_1_1_lrlg7o4n_.arc
ORA-00280: change 1428571 for thread 1 is in sequence #1


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/archive/HFZCDB/archivelog/2023_12_25/o1_mf_1_1_lrlg7o4n_.arc
ORA-00279: change 1429593 generated at 12/25/2023 16:10:29 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_12_25/o1_mf_1_2_%u_.arc
ORA-00280: change 1429593 for thread 1 is in sequence #2
ORA-00278: log file '/archive/HFZCDB/archivelog/2023_12_25/o1_mf_1_1_lrlg7o4n_.arc' no longer needed for this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
hfeduSQL> alter database open resetlogs;

Database altered.

hfeduSQL> alter database open RESETLOGS;

hfeduSQL> select * from hfedu.hfedu;

ID NAME
---------- ----------------------------------------
   99 hfedu09
1 hfedu01
55 hfedu05
66 hfedu06
77 hfedu07
88 hfedu08

6 rows selected.

hfeduSQL>

4. 实操 3 基于【until scn】不完全恢复

在数据库冷备份后，插入一条数据提交，并写到日志文件，checkpoint到数据文件，记录此时scn，然后再插入一条数据提交，再删除一条数据，提交，关闭数据库，把数据文件恢复到冷备份的时候，启动数据库到nomount状态，使用 recover database until change 2182325;恢复数据库，再用 alter database open resetlogs;启动数据库后，查询数据，发现数据是插入第一条数据的状态。

--先做冷备 
shutdown immediate;
SQL> host rm -rf /backup/hfzcdb/*.* 

*/
SQL> host cp /oradata/hfzcdb/* /backup/hfzcdb/ --先做冷备 */ 
set time on; 

hfeduSQL> conn hfedu/hfedu123
Connected.
hfedu@hfzcdb>
hfedu@hfzcdb> insert into hfedu3 values(66,'hfedu66');

1 row created.

hfedu@hfzcdb> commit
2  ;

Commit complete.

hfedu@hfzcdb> alter system switch logfile;

System altered.

hfedu@hfzcdb> /

System altered.

hfedu@hfzcdb> /
/


System altered.

hfedu@hfzcdb>
System altered.

hfedu@hfzcdb> hfedu@hfzcdb> alter system checkpoint
2  ;

System altered.

hfedu@hfzcdb> select current_scn from v$database;

CURRENT_SCN
-----------
2182325

hfedu@hfzcdb> alter system checkpoint
2  ;

System altered.

hfedu@hfzcdb> alter system checkpoint;

System altered.

hfedu@hfzcdb> select current_scn from v$database;

CURRENT_SCN
-----------
2182335

hfedu@hfzcdb> alter system checkpoint;

System altered.

hfedu@hfzcdb> select current_scn from v$database;

CURRENT_SCN
-----------
2182342

hfedu@hfzcdb> insert into hfedu3 values(77,'hfedu77');

1 row created.

hfedu@hfzcdb> commit
2  ;

Commit complete.

hfedu@hfzcdb> delete from hfedu where id=1;
delete from hfedu where id=1
*
ERROR at line 1:
ORA-00942: table or view does not exist


hfedu@hfzcdb> delete from hfedu3 where id=1;

1 row deleted.

hfedu@hfzcdb> commit
2  ;

Commit complete.

hfedu@hfzcdb> alter system checkpoint;

System altered.

hfedu@hfzcdb> select current_scn from v$database;

CURRENT_SCN
-----------
2182385

hfedu@hfzcdb> select * from hfedu.hfedu3;

ID NAME
---------- ----------------------------------------
66 hfedu66
77 hfedu77
2 hfedu02
3 hfedu03
4 hfedu04
5 hfedu5
6 hfedu6
44 hfedu44

8 rows selected.

hfedu@hfzcdb> conn / as sysdba
Connected.
SYS@hfzcdb> shutdown abort
ORACLE instance shut down.
SYS@hfzcdb> host cp /backup/hfzcdb/*.dbf /oradata/hfzcdb/   【覆盖现在日志，就和删除恢复一样】
*/
SYS@hfzcdb> startup nomount
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
SYS@hfzcdb> alter database mount
2  ;

Database altered.

SYS@hfzcdb> recover database until change 2182325;
ORA-00279: change 2135155 generated at 03/28/2023 22:00:32 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_03_28/o1_mf_1_35_l25xd0yp_.arc
ORA-00280: change 2135155 for thread 1 is in sequence #35


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 2136023 generated at 03/28/2023 22:11:12 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_03_29/o1_mf_1_1_l27n4015_.arc
ORA-00280: change 2136023 for thread 1 is in sequence #1
ORA-00278: log file '/archive/HFZCDB/archivelog/2023_03_28/o1_mf_1_35_l25xd0yp_.arc' no longer needed for this recovery


ORA-00279: change 2177944 generated at 03/29/2023 15:15:29 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_03_29/o1_mf_1_1_l27wtv68_.arc
ORA-00280: change 2177944 for thread 1 is in sequence #1


ORA-00279: change 2182271 generated at 03/29/2023 16:14:19 needed for thread 1
ORA-00289: suggestion : /archive/HFZCDB/archivelog/2023_03_29/o1_mf_1_2_l27wv3w1_.arc
ORA-00280: change 2182271 for thread 1 is in sequence #2


Log applied.
Media recovery complete.
SYS@hfzcdb> alter database open resetlogs;  【数据库只要是不完全恢复，都需要resetlogs】

Database altered.

SYS@hfzcdb> select * from hfedu.hfedu3;

ID NAME
---------- ----------------------------------------
66 hfedu66
1 hfedu01
2 hfedu02
3 hfedu03
4 hfedu04
5 hfedu5
6 hfedu6
44 hfedu44

8 rows selected.

SYS@hfzcdb>

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

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

上一篇：Winform（C#）实现下拉列表显示表格（利用自定义组件）
下一篇：Linux-IIO驱动实验

.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. 实操 1 基于【until time】不完全恢复

3. 实操 2 基于【Until Cancel】不完全恢复

4. 实操 3 基于【until scn】不完全恢复

相关文章