近日测试删除current redo log的恢复,却不知引发一系列案中案,特记录如下(CentOS 6.3,Oracle 11.2.0).
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 22 52428800 512 1 YES INACTIVE 1819273 08-SEP-13 1839279 08-SEP-13
2 1 23 52428800 512 1 NO CURRENT 1839279 08-SEP-13 2.8147E+14
3 1 21 52428800 512 1 YES INACTIVE 1798901 08-SEP-13 1819273 08-SEP-13
SQL>insert into t(txt) values('aa');
SQL>commit;
-- commit后,oracle将脏数据写入到redo.log,些时未有switch logfile,故data未写入datafile和archived logfile.此时删除redo02.log,不shutdown
SQL> host rm /db/oracle/app/oracle/oradata/pvm/redo02.log;
SQL>alter system switch logfile;
-- 此时仍可switch logfile,因为switch logfile触发checkpoint,data会写入到datafile.
-- 此时继续switch logfile,而再switch到redo02.log时,因该log已删除,故
SQL>alter system switch logfile;
--会一直停在运行的状态,ctrl+c退出运行
SQL> SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
1 1 25 52428800 512 1 NO CURRENT 1851152 09-SEP-13 2.8147E+14
2 1 23 52428800 512 1 NO INACTIVE 1839279 08-SEP-13 1851142 09-SEP-13
3 1 24 52428800 512 1 NO ACTIVE 1851142 09-SEP-13 1851152 09-SEP-13
--redo02.log status变为inactive,但ARC=no. 因为寻址不到redo02.log,故无法archived.
-- 重建redo02.log
SQL> alter database clear unarchived logfile group 2;
Database altered.
-- 以上command是会重建redolog,但必须redo log非active及current status才可.
-- 因 redo02已写datafile,故data不会丢失,重启db,验证;
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 327158144 bytes
Database Buffers 88080384 bytes
Redo Buffers 6094848 bytes
Database mounted.
Database opened.
SQL> select * from t;
TXT DTE
-------------------------------------------------- ---------
aa 07-SEP-13
SQL>
--以上为redo log删除后,未shutdown的情况下的恢复,若强行shutdown后,恢复就比较麻烦一些
-- 以下再测试删除redo并shutdown后的恢复
-- 同样上面的操作,insert data/commit/rm redo/switch logfile ,此时 删除的redo log status为active,然后强行shutdown abort;,再重启,报错:
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 331352448 bytes
Database Buffers 83886080 bytes
Redo Buffers 6094848 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/db/oracle/app/oracle/oradata/pvm/redo01.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3
-- 考虑不完全恢复,并用alter database open resetlogs来重建redo log:
SQL> recover database until cancel;
ORA-00279: change 1851860 generated at 09/09/2013 01:13:56 needed for thread 1
ORA-00289: suggestion : /db/oracle/app/oracle/flash_recovery_area/PVM/archivelog/2013_09_09/o1_mf_1_25_%u_.arc
ORA-00280: change 1851860 for thread 1 is in sequence #25
Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/db/oracle/app/oracle/oradata/pvm/system01.dbf'
ORA-01112: media recovery not started
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01194: file 1 needs more recovery to be consistent
ORA-01110: data file 1: '/db/oracle/app/oracle/oradata/pvm/system01.dbf'
--至此,无法打开database.修改参数文件,加上_allow_resetlogs_corruption=true,试强行打开db.
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup pfile='/db/oracle/app/oracle/oradata/pvm/initpvm.ora';
LRM-00109: could not open parameter file '/db/oracle/app/oracle/oradata/pvm/initpvm.ora'
ORA-01078: failure in processing system parameters
SQL> startup pfile='/db/oracle/oracle/product/11.2.0/db_1/dbs/initpvm.ora';
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 306186624 bytes
Database Buffers 109051904 bytes
Redo Buffers 6094848 bytes
Database mounted.
ORA-01589: must use RESETLOGS or NORESETLOGS option for database open
SQL> alter database open resetlogs;
alter database open resetlogs
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced
ORA-00600: internal error code, arguments: [2662], [0], [1851868], [0], [1851885], [4194432], [], [], [], [], [], []
Process ID: 13554
Session ID: 1 Serial number: 5
--出现internal error,再次呜呼唉灾,再次证明,oracle的隐含参数不要轻易使用.
-- 回到linux查,却发现redo01.log生成了,@_@
--于是,退出sqlplus(一定要退出),再login sqlplus
SQL> shutdown abort;
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 422670336 bytes
Fixed Size 1336960 bytes
Variable Size 331352448 bytes
Database Buffers 83886080 bytes
Redo Buffers 6094848 bytes
Database mounted.
Database opened.
---此时可正常打开.
-- select data
SQL>select * from t;
no rows selected
-- data丢失. 不完全恢复找不到redo01.log,只能恢复到未insert data前的状态.
总结:
1.出现问题时,不要轻易shutdown
2.不要随便使用oracle的隐含参数
相关推荐
1. The instance abnormally terminates because of a power outage.... All redo log entries recorded in the current log file until the checkpoint position are applied to data files Answer: C
实例因为断电异常中止。哪些是关于 redo 日志在实例恢复期间的正确描述? A. Inactive 和 current 状态的 redo 日志需要完成...实例恢复是进程应用记录在 online redo log 的信息去重建最近一次检查点的之后的变更。
导读:本文将对Redo Nowait...检查V$LOG视图,可以获得日志状态,除了CURRENT日志组,其他日志都处于ACTIVE状态,而且后面的几组日志都是DBA最新添加的: SQL> select * from v$log; GROUP# THREAD# SEQUENCE# BYTES
6、 模拟redolog file恢复(其实下面操作在sqlplus里面,不在rman执行) (1) shutdown immediate; (2) startup mount; (3) recover database until cancel; (4) alter database resetlog;
redo log space requests:在redo logs中服务进程的等待空间,表示需要更长时间的log switch。 redo size:redo发生的总次数(以及因此写入log buffer),以byte为单位。这项统计显示出update活跃性。 ...
myBase 7.x 内容格式已由 RTF 升级为 HTML 格式,新增 HTML 表格编辑和插入JPG/PNG/GIF图片,HTML编辑支持 UNDO/REDO 操作,编辑内容支持历史修订版本, 大纲视图支持条目多选,相对独立的标签分类视图和日历关联...
Common\Graphics\bitmaps\tlbr_w95\redo.bmp Common\Graphics\bitmaps\tlbr_w95\rt.bmp Common\Graphics\bitmaps\tlbr_w95\save.bmp Common\Graphics\bitmaps\tlbr_w95\smallcap.bmp Common\Graphics\bitmaps\...
37.zip Microsecond delay 百万分子一的延时(4KB)<END><br>38,38.zip Messagebox with printf capability 有printf能力的Messagebox(4KB)<END><br>39,39.zip Multiple Level Undo/Redo 多级Undo/...
boot sector from file, volatile write support using hdimage redolog_t class, optional commit support on Bochs exit, save/restore file attributes, 1.44 MB floppy support, set file modification date/...
Current version allows preview, print and design report template under Windows and Linux platform (qt). + Added Embarcadero RAD Studio XE3 support - fixed compatibility with Fast Report FMX installed...
of a graph to the current graph (only if the user clicks OK). Fixed. + The scrollbar did not get reset to the top when the user read in a new data file. Fixed. + The structure of the code has been...
Table of Contents 1. Introduction....................................................................................................................................................2 ...