恢复重做日志
丢失INACTIVE的日志,进行恢复,分两种状况,一种是联机,另一种为非联机
大致恢复的思路
首先先准备好相对应的环境,确保日志的状态,并看一下redo相对应的状态
1. CURRENT指当前的日志文件,在进行实例恢复时是必须的;
2. ACTIVE是指活动的非当前日志,在进行实例恢复时会被用到。Active状态意味着,Checkpoint尚未完成,因此该日志文件不能被覆盖。
3. INACTIVE是非活动日志,在实例恢复时不再需要,但在介质恢复时可能需要。
4. UNUSED表示该日志从未被写入,可能是刚添加的,或RESETLOGS后被重
SQL> alter system switch logfile;
System altered.
SQL> select * from v$log;
1.联机情况
丢失current状态日志文件,如果是在联机状态下,我们可以进行切换日志,让日志先写入归档的方法,我们也可以将数据库转化为非归档模式,然后重建日志文件,当然我们也可以修改参数文件相对应的隐含参数alter system set "_allow_resetlogs_corruption"=true scope=spfile进行修改
首先先确定相对的日志状态以及,相对的日志的位置
SQL> select group#,status from v$log;
GROUP# STATUS
---------- ----------------
1 CURRENT
2 INACTIVE
3 INACTIVE
SQL> select group#,member from v$logfile order by group#;
GROUP# MEMBER
1 /u01/app/oracle/oradata/PROD/redo01.log
2 /u01/app/oracle/oradata/PROD/redo02.log
3 /u01/app/oracle/oradata/PROD/redo03.log
删除第二组日志
SQL> !rm /u01/app/oracle/oradata/PROD/redo02.log
SQL> !ls /u01/app/oracle/oradata/PROD/*.log
/u01/app/oracle/oradata/PROD/redo01.log
/u01/app/oracle/oradata/PROD/redo03.log
发现文件丢失后立刻进行恢复
SQL> alter database drop logfile group 2;
Database altered.
SQL> select group#,status,bytes/1024/1024 as m from v$log;
GROUP# STATUS M
---------- ---------------- ----------
1 CURRENT 50
3 INACTIVE 50
由于该日志已经归档,直接新建相对应得日志和日志组就行了
SQL> alter database add logfile group 2('/u01/app/oracle/oradata/PROD/redo02.log') size 50m;
SQL> select group#,status,bytes/1024/1024 as m from v$log;
GROUP# STATUS M
---------- ---------------- ----------
1 CURRENT 50
2 UNUSED 50
3 INACTIVE 50
SQL> alter system switch logfile ;
System altered.
SQL> select group#,status,bytes/1024/1024 as m from v$log;
GROUP# STATUS M
---------- ---------------- ----------
1 ACTIVE 50
2 CURRENT 50
3 INACTIVE 50
2.非联机情况
先查看相对应日志状态
SQL> select group#,status,bytes/1024/1024 as m from v$log;
GROUP# STATUS M
---------- ---------------- ----------
1 ACTIVE 50
2 CURRENT 50
3 INACTIVE 50
删除相对应的日志
SQL> !rm /u01/app/oracle/oradata/PROD/redo03.log
SQL> !ls /u01/app/oracle/oradata/PROD/*.log;
/u01/app/oracle/oradata/PROD/redo01.log
/u01/app/oracle/oradata/PROD/redo02.log
关闭数据库并重启后发现重启出数据库,在open时出错
Database mounted.
ORA-03113: end-of-file on communication channel
Process ID: 2993
Session ID: 1 Serial number: 5
此时去查找alert日志,在alter读到以下内容 位置在/u01/app/oracle/diag/rdbms/prod/PROD/trace
Errors in file /u01/app/oracle/diag/rdbms/prod/PROD/trace/PROD_lgwr_2973.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/PROD/redo03.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory。
起到mount状态清除日志组group3
SQL> alter database clear logfile group 3;
Database altered.
查看日志以及物理文件是否恢复
SQL> select group#,members,status from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 INACTIVE
3 1 UNUSED
2 1 CURRENT
SQL> !ls /u01/app/oracle/oradata/PROD/*.log;
/u01/app/oracle/oradata/PROD/redo01.log
/u01/app/oracle/oradata/PROD/redo02.log
/u01/app/oracle/oradata/PROD/redo03.log
至此数据库可以正常启动了
SQL> alter database open;
Database altered.
查看日志状态
SQL> select GROUP#, MEMBERS,STATUS from v$log;
GROUP# MEMBERS STATUS
---------- ---------- ----------------
1 1 INACTIVE
2 1 CURRENT
3 1 UNUSED
SQL> select GROUP#, MEMBERS,STATUS,archived from v$log;
GROUP# MEMBERS STATUS ARC
---------- ---------- ---------------- ---
1 1 INACTIVE YES
2 1 CURRENT NO
3 1 UNUSED YES
进行dml操作
SQL> create table scott.t tablespace users as select * from dba_objects where 1=2;
Table created.
SQL> insert into scott.t select * from dba_objects;
86259 rows created.
删除current状态的日志文件
SQL> !ls /u01/app/oracle/oradata/PROD/*.log;
/u01/app/oracle/oradata/PROD/redo01.log
/u01/app/oracle/oradata/PROD/redo02.log
/u01/app/oracle/oradata/PROD/redo03.log
SQL> !rm /u01/app/oracle/oradata/PROD/redo02.log
SQL> !ls /u01/app/oracle/oradata/PROD/*.log;
/u01/app/oracle/oradata/PROD/redo01.log
/u01/app/oracle/oradata/PROD/redo03.log
切换日志,发现2号日志文件状态变为active
SQL> alter system switch logfile;
System altered.
SQL> select group#,members,archived,status from v$log;
GROUP# MEMBERS ARC STATUS
---------- ---------- --- ----------------
1 1 YES INACTIVE
2 1 NO ACTIVE
3 1 NO CURRENT
尝试删除日志组,发现并不能删除
SQL> alter database drop logfile group 2;
alter database drop logfile group 2
*
ERROR at line 1:
ORA-00350: log 2 of instance PROD (thread 1) needs to be archived
ORA-00312: online log 2 thread 1: '/u01/app/oracle/oradata/PROD/redo02.log'
关闭数据库,起到mount状态,改为非归档
SQL> alter database noarchivelog;
Database altered.
删除相对应的日志组并进行重新添加
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 ('/u01/app/oracle/oradata/PROD/redo02.log')size 50m;
新增好后再次查看状态
SQL> select group#,members,archived,status from v$log;
SQL> !rm /u01/app/oracle/oradata/PROD/redo02.log
打开数据起到open状态,查看之前的t表
SQL> select count(*) from scott.t;
COUNT(*)
----------
86259
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/30604784/viewspace-1978426/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/30604784/viewspace-1978426/