oracle 在线重做日志恢复
回顾一下：在线重做日志的状态1.unused -- 未使用状态
2.current -- 当前状态
3.active -- 活动状态
4.inactive -- 非活动状态
1.unused & inactive 状态的日志丢失
如果是inactive状态，那么说明：
该日志记录的数据库的变化，都已经同步到数据文件；
实例恢复也不需要这个日志文件存在。
那么，这样的日志文件损坏，于数据无影响；
将日志文件本身修复即可；
SYS@orcl11g> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area422670336 bytes
Fixed Size 1345380 bytes
Variable Size 322963612 bytes
Database Buffers 92274688 bytes
Redo Buffers 6086656 bytes
Database mounted.
SYS@orcl11g> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
3 UNUSED
2 CURRENT
--破坏inactive & unused状态的日志文件
$ cd /u01/app/oracle/oradata/orcl11g
$ cp /etc/passwd redo01.log
$ cp /etc/passwd redo03.log
SYS@orcl11g> alter database open;
alter database open
*
ERROR at line 1:
ORA-03113: end-of-file on communication channel
Process ID: 10685
Session ID: 125 Serial number: 5
SYS@orcl11g> conn / as sysdba
Connected to an idle instance.
SYS@orcl11g> startup mount;
ORACLE instance started.
Total System Global Area835104768 bytes
Fixed Size 2232960 bytes
Variable Size 520097152 bytes
Database Buffers 306184192 bytes
Redo Buffers 6590464 bytes
Database mounted.
SYS@orcl11g> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
3 INACTIVE
2 CURRENT
--清理日志组即可，oracle会自动创建新的日志文件出来，状态为unused
SYS@orcl11g> alter database clear logfile group 1;
SYS@orcl11g> alter database clear logfile group 3;
--清理之后的日志状态
SYS@orcl11g> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 UNUSED
3 UNUSED
2 CURRENT
--正常打开数据库
SYS@orcl11g> alter database open;
2.active状态的日志文件损坏
--数据更新
SYS@orcl11g> update hr.employees set salary=salary+1;
SYS@orcl11g> commit;
--切换日志，更新操作保留在active状态的日志中
SYS@orcl11g> alter system switch logfile;
SYS@orcl11g> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 ACTIVE
3 UNUSED
--查看当前的归档状态
SYS@orcl11g> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /u01/app/oracle/arch
Oldest online log sequence 2
Next log sequence to archive 3
Current log sequence 3
--为了保留active状态，只能shutdown abort
SYS@orcl11g> shutdown abort;
ORACLE instance shut down.
--启动到mount
SYS@orcl11g> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area422670336 bytes
Fixed Size 1345380 bytes
Variable Size 322963612 bytes
Database Buffers 92274688 bytes
Redo Buffers 6086656 bytes
Database mounted.
SYS@orcl11g> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
3 UNUSED
2 ACTIVE
-- 破坏2号日志组日志文件
SYS@orcl11g> host cp /etc/passwd /u01/app/oracle/oradata/orcl11g/redo02.log
--打开数据库
SYS@orcl11g> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/orcl11g/redo02.log'
ORA-27048: skgfifi: file header information is invalid
Additional information: 14
--因为，存在归档，所以，可以直接恢复数据库
SYS@orcl11g> select sequence#,group#,status from v$log;
SEQUENCE# GROUP# STATUS
---------- ---------- ----------------
75 1 CURRENT
73 3 INACTIVE
74 2 ACTIVE
SYS@orcl11g> recover database until cancel;
ORA-00279: change 1763218 generated at 06/24/2013 12:02:00 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_74_816622368.dbf
ORA-00280: change 1763218 for thread 1 is in sequence #74
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/arch/1_74_816622368.dbf
ORA-00279: change 1769094 generated at 06/24/2013 13:34:43 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_75_816622368.dbf
ORA-00280: change 1769094 for thread 1 is in sequence #75
ORA-00278: log file '/u01/app/oracle/arch/1_74_816622368.dbf' no longer needed
for this recovery
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/oradata/orcl11g/redo01.log
Log applied.
Media recovery complete.
SYS@orcl11g> alter database open resetlogs;
Database altered.
SYS@orcl11g> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 UNUSED
3 UNUSED
--查看一下数据情况
--数据不会有丢失的情况，因为所有的日志都被recover了；
3.current状态的日志损坏
SYS@orcl11g> update hr.employees set salary=salary+1;
SYS@orcl11g> commit;
SYS@orcl11g> alter system switch logfile;
SYS@orcl11g> select salary from hr.employees where employee_id=100;
SALARY
----------
31948
SYS@orcl11g> update hr.employees set salary=salary+1;
SYS@orcl11g> commit;
SYS@orcl11g> select salary from hr.employees where employee_id=100;
SALARY
----------
31949
--日志状态
SYS@orcl11g> select group#,sequence#,status from v$log;
GROUP#SEQUENCE# STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 2 ACTIVE
3 3 CURRENT
--模拟实例崩溃，current状态的日志文件损坏
SYS@orcl11g> shutdown abort;
ORACLE instance shut down.
SYS@orcl11g> startup mount;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area422670336 bytes
Fixed Size 1345380 bytes
Variable Size 322963612 bytes
Database Buffers 92274688 bytes
Redo Buffers 6086656 bytes
Database mounted.
SYS@orcl11g> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 INACTIVE
3 CURRENT
2 ACTIVE
SYS@orcl11g> host cp /etc/passwd /u01/app/oracle/oradata/orcl11g/redo03.log
--open database打开数据库
SYS@orcl11g> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl11g/redo03.log'
ORA-27048: skgfifi: file header information is invalid
Additional information: 14
--查看隐藏参数
SYS@orcl11g> show parameter "_allow_resetlogs_corruption";
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_allow_resetlogs_corruption boolean TRUE
--如果这个参数为false，修改为true
--alter system set "_allow_resetlogs_corruption"=true scope=spfile;
--shutdown abort;
--startup mount;
--修复数据库
SYS@orcl11g> recover database until cancel;
ORA-00279: change 1789650 generated at 06/24/2013 13:40:21 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_2_818948248.dbf
ORA-00280: change 1789650 for thread 1 is in sequence #2
Specify log: {=suggested | filename | AUTO | CANCEL}
/u01/app/oracle/arch/1_2_818948248.dbf
ORA-00279: change 1789904 generated at 06/24/2013 13:41:02 needed for thread 1
ORA-00289: suggestion : /u01/app/oracle/arch/1_3_818948248.dbf
ORA-00280: change 1789904 for thread 1 is in sequence #3
ORA-00278: log file '/u01/app/oracle/arch/1_2_818948248.dbf' no longer needed
for this recovery
Specify log: {=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: '/u01/app/oracle/oradata/orcl11g/system01.dbf'
ORA-01112: media recovery not started
SYS@orcl11g> alter database open resetlogs;
Database altered.
--查看数据情况
SYS@orcl11g> select salary from hr.employees where employee_id=100;
SALARY
----------
31948
页:
[1]