女性门户网站模板,wordpress友链审核插件,a00000网站建设丽丽,口碑好的南昌网站建设1. 不完全恢复的几种常用方法
01. recover database using backup controlfile 如果丢失当前控制文件#xff0c;用冷备份的控制文件恢复的时候#xff0c;用来告诉 oracle#xff0c;不要以 controlfile 中的 scn 作为恢复的终点#xff1b; 02. recover database until …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; SYShfzcdb shutdown immediate
SYShfzcdb host cp /oradata/hfzcdb/* /backup/hfzcdb/
*/
SYShfzcdb 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.
SYShfzcdb set time on
22:02:35 SYShfzcdb conn hfedu/hfedu123
Connected.
22:03:01 hfeduhfzcdb insert into hfedu3 values(44,hfedu44);1 row created.22:03:22 hfeduhfzcdb commit
22:04:38 hfeduhfzcdb 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:0522:05:05 hfeduhfzcdb drop table hfedu3;Table dropped.22:05:28 hfeduhfzcdb select * from hfedu3;
select * from hfedu3
*
ERROR at line 1:
ORA-00942: table or view does not exist
22:05:33 hfeduhfzcdb conn / as sysdba
Connected.
22:05:41 SYShfzcdb shutdown immedaite
SP2-0717: illegal SHUTDOWN option
22:05:54 SYShfzcdb shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
22:06:34 SYShfzcdb host rm /oradata/hfzcdb/*.dbf
*/22:07:06 SYShfzcdb host cp /backup/hfzcdb/*.dbf /oradata/hfzcdb/
*/
22:08:45 SYShfzcdb 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 SYShfzcdb select file#,checkpoint_change# from v$datafile;FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2136506
2 2136506
3 2136506
4 2136506
5 2136506
6 21365066 rows selected.22:09:14 SYShfzcdb select file#,checkpoint_change# from v$datafile_header;FILE# CHECKPOINT_CHANGE#
---------- ------------------
1 2135155
2 2135155
3 2135155
4 2135155
5 2135155
6 21351556 rows selected.22:09:33 SYShfzcdb recover database until time 2023-03-28 22:05:05;
Media recovery complete.
22:10:40 SYShfzcdb alter database open;
alter database open
*
ERROR at line 1:
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open22:10:53 SYShfzcdb alter database open RESETLOGS;Database altered.22:11:21 SYShfzcdb select *from hfedu.hfedu3;ID NAME
---------- ----------------------------------------
1 hfedu01
2 hfedu02
3 hfedu03
4 hfedu04
5 hfedu5
6 hfedu6
44 hfedu447 rows selected.22:11:41 SYShfzcdb
3. 实操 2 基于【Until Cancel】不完全恢复 在数据库冷备份后在表中插入一条数据把数据刷到日志文件在checkpoint到数据文件再插入一条,没有刷到日志文件删除/oradata/hfzcdb/下面所有的数据包括控制文件和日志文件再把之前冷备份的数据拷贝回来把数据库启动到nomount状态重建控制文件使用recover database using backup controlfile until cancel; 在原来的控制文件基础上面恢复实现最大的恢复选择自动恢复然后再alter database open RESETLOGS;数据库数据能恢复到插入的第一条数据第二条没有写入归档日志的恢复不了。
hfeduSQL conn hfedu/hfedu123
Connected.
hfeduhfzcdb select *from hfedu;ID NAME
---------- ----------------------------------------
1 hfedu01
55 hfedu05
66 hfedu06
77 hfedu07
88 hfedu08hfeduhfzcdb insert into hfedu values(99,hfedu09);1 row created.hfeduhfzcdb commit;Commit complete.hfeduhfzcdb alter system switch logfile;System altered.hfeduhfzcdb alter system checkpoint;System altered.
-----------------------
[oraclehfzcdb91:/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
[oraclehfzcdb91:/archive/HFZCDB/archivelog/2023_12_25]$strings o1_mf_1_1_lrlg7o4n_.arc |grep hfedu09
hfedu09
-----------------------
hfeduhfzcdb insert into hfedu values(991,hfedu091);1 row created.hfeduhfzcdb commit;Commit complete.hfeduhfzcdb alter system checkpoint;System altered.hfeduhfzcdb host ll /oradata/hfzcdb/**/
/bin/bash: ll: command not foundhfeduhfzcdb host ll /oradata/hfzcdb/
/bin/bash: ll: command not foundhfeduhfzcdb 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.dbfhfeduhfzcdb host rm -f /oradata/hfzcdb/*
*/
hfeduhfzcdb conn / as sysdba
Connected.
SYShfzcdb shutdown abort
ORACLE instance shut down.
SYShfzcdb exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
exit[oraclehfzcdb91:/home/oracle]$sqlplus / as sysdbaSQL*Plus: Release 19.0.0.0.0 - Production on Mon Dec 25 16:14:07 2023
Version 19.3.0.0.0Copyright (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 #4Specify log: {RETsuggested | filename | AUTO | CANCEL}
auto
ORA-00283: recovery session canceled due to errors
ORA-19906: recovery target incarnation changed during recoveryORA-01112: media recovery not startedhfeduSQL 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 #1Specify log: {RETsuggested | 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 recoverySpecify log: {RETsuggested | 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 hfedu086 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.
hfeduhfzcdb
hfeduhfzcdb insert into hfedu3 values(66,hfedu66);1 row created.hfeduhfzcdb commit
2 ;Commit complete.hfeduhfzcdb alter system switch logfile;System altered.hfeduhfzcdb /System altered.hfeduhfzcdb /
/System altered.hfeduhfzcdb
System altered.hfeduhfzcdb hfeduhfzcdb alter system checkpoint
2 ;System altered.hfeduhfzcdb select current_scn from v$database;CURRENT_SCN
-----------
2182325hfeduhfzcdb alter system checkpoint
2 ;System altered.hfeduhfzcdb alter system checkpoint;System altered.hfeduhfzcdb select current_scn from v$database;CURRENT_SCN
-----------
2182335hfeduhfzcdb alter system checkpoint;System altered.hfeduhfzcdb select current_scn from v$database;CURRENT_SCN
-----------
2182342hfeduhfzcdb insert into hfedu3 values(77,hfedu77);1 row created.hfeduhfzcdb commit
2 ;Commit complete.hfeduhfzcdb delete from hfedu where id1;
delete from hfedu where id1
*
ERROR at line 1:
ORA-00942: table or view does not existhfeduhfzcdb delete from hfedu3 where id1;1 row deleted.hfeduhfzcdb commit
2 ;Commit complete.hfeduhfzcdb alter system checkpoint;System altered.hfeduhfzcdb select current_scn from v$database;CURRENT_SCN
-----------
2182385hfeduhfzcdb select * from hfedu.hfedu3;ID NAME
---------- ----------------------------------------
66 hfedu66
77 hfedu77
2 hfedu02
3 hfedu03
4 hfedu04
5 hfedu5
6 hfedu6
44 hfedu448 rows selected.hfeduhfzcdb conn / as sysdba
Connected.
SYShfzcdb shutdown abort
ORACLE instance shut down.
SYShfzcdb host cp /backup/hfzcdb/*.dbf /oradata/hfzcdb/ 【覆盖现在日志就和删除恢复一样】
*/
SYShfzcdb 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
SYShfzcdb alter database mount
2 ;Database altered.SYShfzcdb 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 #35Specify log: {RETsuggested | 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 recoveryORA-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 #1ORA-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 #2Log applied.
Media recovery complete.
SYShfzcdb alter database open resetlogs; 【数据库只要是不完全恢复都需要resetlogs】Database altered.SYShfzcdb select * from hfedu.hfedu3;ID NAME
---------- ----------------------------------------
66 hfedu66
1 hfedu01
2 hfedu02
3 hfedu03
4 hfedu04
5 hfedu5
6 hfedu6
44 hfedu448 rows selected.SYShfzcdb
