oracle日志文件恢复
1.SQL> shutdown immediate
2.ORA-01109: 数据库未打开
3.
4.
5.已经卸载数据库。
6.ORACLE 例程已经关闭。
7.SQL> startup
8.ORACLE 例程已经启动。
9.
10.Total System Global Area 426852352 bytes
11.Fixed Size 1333648 bytes
12.Variable Size 264242800 bytes
13.Database Buffers 155189248 bytes
14.Redo Buffers 6086656 bytes
15.数据库装载完毕。
16.ORA-00313: 无法打开日志组 1 (用于线程 1) 的成员
17.ORA-00312: 联机日志 1 线程 1: 'C:\ORACLE\ORADATA\ORCL\REDO01.LOG'
18.
19.
20.SQL> select * from v$log;
21.
22. GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
23.---------- ---------- ---------- ---------- ---------- --- ----------------
24.FIRST_CHANGE# FIRST_TIME
25.------------- --------------
26. 1 1 4 52428800 1 NO CURRENT
27. 928724 23-4月 -09
28.
29. 3 1 3 52428800 1 NO INACTIVE
30. 908643 23-4月 -09
31.
32. 2 1 2 52428800 1 NO INACTIVE
33. 894721 23-4月 -09
34.
35.
36.SQL> select status from v$instance;
37.
38.STATUS
39.------------
40.MOUNTED
41.
42.SQL> alter database drop logfile group 1;
43.alter database drop logfile group 1
44.*
45.第 1 行出现错误:
46.ORA-01623: 日志 1 是实例 orcl (线程 1) 的当前日志 - 无法删除
47.ORA-00312: 联机日志 1 线程 1: 'C:\ORACLE\ORADATA\ORCL\REDO01.LOG'
48.
49.
50.SQL> shutdown immediate
51.ORA-01109: 数据库未打开
52.
53.
54.已经卸载数据库。
55.ORACLE 例程已经关闭。
56.SQL> startup
57.ORACLE 例程已经启动。
58.
59.Total System Global Area 426852352 bytes
60.Fixed Size 1333648 bytes
61.Variable Size 264242800 bytes
62.Database Buffers 155189248 bytes
63.Redo Buffers 6086656 bytes
64.数据库装载完毕。
65.ORA-00313: 无法打开日志组 1 (用于线程 1) 的成员
66.ORA-00312: 联机日志 1 线程 1: 'C:\ORACLE\ORADATA\ORCL\REDO01.LOG'
67.
68.
69.SQL> alter database clear logfile group 2;
70.
71.数据库已更改。
72.
73.SQL> select * from v$log;
74.
75. GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
76.---------- ---------- ---------- ---------- ---------- --- ----------------
77.FIRST_CHANGE# FIRST_TIME
78.------------- --------------
79. 1 1 4 52428800 1 NO CURRENT
80. 928724 23-4月 -09
81.
82. 3 1 3 52428800 1 NO INACTIVE
83. 908643 23-4月 -09
84.
85. 2 1 0 52428800 1 NO UNUSED
86. 894721 23-4
月 -09
87.
88.
89.SQL> alter database clear logfile group 1;
90.
91.数据库已更改。
92.
93.SQL> select * from v$log;
94.
95. GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
96.---------- ---------- ---------- ---------- ---------- --- ----------------
97.FIRST_CHANGE# FIRST_TIME
98.------------- --------------
99. 1 1 0 52428800 1 NO UNUSED
100. 928724 23-4月 -09
101.
102. 3 1 3 52428800 1 NO INACTIVE
103. 908643 23-4月 -09
104.
105. 2 1 5 52428800 1 NO CURRENT
106. 944619 24-4月 -09
107.
108.
109.SQL> alter database open;
110.alter database open
111.*
112.第 1 行出现错误:
113.ORA-00313: 无法打开日志组 3 (用于线程 1) 的成员
114.ORA-00312: 联机日志 3 线程 1: 'C:\ORACLE\ORADATA\ORCL\REDO03.LOG'
115.
116.
117.SQL> shutdown immediate
118.ORA-01109: 数据库未打开
119.
120.
121.已经卸载数据库。
122.ORACLE 例程已经关闭。
123.SQL> startup
124.ORACLE 例程已经启动。
125.
126.Total System Global Area 426852352 bytes
127.Fixed Size 1333648 bytes
128.Variable Size 264242800 bytes
129.Database Buffers 155189248 bytes
130.Redo Buffers 6086656 bytes
131.数据库装载完毕。
132.ORA-00313: 无法打开日志组 3 (用于线程 1) 的成员
133.ORA-00312: 联机日志 3 线程 1: 'C:\ORACLE\ORADATA\ORCL\REDO03.LOG'
134.
135.
136.SQL> alter database clear logfile group 3;
137.
138.数据库已更改。
139.
140.SQL> alter database open;
141.
142.数据库已更改。
Oracle数据库联机日志文件丢失处理方法
试验一：用命令清空日志组方法
1、 查看原来表中数据
SQL>; conn test/test
Connected.
SQL>; select * from test;
TEL
----------
1
2
3
2、插入新数据
SQL>; insert into test values(4);
1 row created.
SQL>; commit;
Commit complete.
SQL>;
3、 正常关闭数据库
4、 利用os command删除所有redo文件
5、 启动数据库
SQL>; startup
ORACLE instance started.
Total System Global Area 353862792 bytes
Fixed Size 730248 bytes
Variable Size 285212672 bytes
Database Buffers 67108864 bytes
Redo Buffers 811008 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'
6、 查看当前日志状态
SQL>; select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS
---------- ---------- ---------- ---------- ---------- --- ----------------
FIRST_CHANGE# FIRST_TIME
------------- ----------
1 1 2 104857600 1 YES INACTIVE
487837 01-9
月 -05
2 1 4 104857600 1 NO CURRENT
487955 01-9月 -05
3 1 3 104857600 1 YES INACTIVE
487839 01-9月 -05
看来redo01.log不是当前日志，对于这类非当前日志可以直接clear,系统会重新自动生成一个redo文件
7、SQL>; alter database clear logfile group 1;
Database altered.
7、 继续启动db
SQL>; 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: '/T3/ORACLE/oradata/ORA9/redo02.log'
8、 看来redo也得恢复，但是redo02是当前redo,直接clear是不行的
SQL>; alter database clear logfile group 2;
alter database clear logfile group 2
*
ERROR at line 1:
ORA-00350: log 2 of thread 1 needs to be archived
ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log'
尝试clear unarchived logfile group ,报错：
SQL>; alter database clear unarchived logfile group 2;
alter database clear unarchived logfile group 2
*
ERROR at line 1:
ORA-00313: open failed for members of log group 2 of thread 1
ORA-00312: online log 2 thread 1: '/T3/ORACLE/oradata/ORA9/redo02.log'
ORA-27037: unable to obtain file status
SVR4 Error: 2: No such file or directory
Additional information: 3
看来他是因为找不到这个文件，从有效的备份中cp一个过来看看
SQL>; host cp /T3/ORACLE/oradatabak/redo02* /T3/ORACLE/oradata/ORA9
SQL>; alter database clear unarchived logfile group 2;
Database altered.
搞定……….
9、 按照oracle的某些做法也是可以的
SQL>; alter database clear unarchived logfile group 1 unrecoverable datafile;
Database altered.
10、但是对于非当前日志就都可以，下面看看redo03
SQL>; alter database clear logfile group 3;
Database altered.
结论：
如果数据库是正常shutdown,非当前日志都可以直接clear来重新生成，而且不丢失数据，因为正常关闭db,数据已经写入dbf文件了。唯独当前日志不可以，当前日志必须首先从有效的备份中拷贝一个日志文件过来，然后用
alter database clear unarchived logfile group n 或alter database clear unarchived logfile group n,除此之外，还可以用下面的方法来做
方法二：用cancel模式恢复数据库
前面的出错提示，步骤都一样，唯独恢复的方法不一样
SQL>; startup
ORACLE instance started.
Total System Global Area 353862792 bytes
Fixed Size 730248 bytes
Variable Size 285212672 bytes
Database Buffers 67108864 bytes
Redo Buffers 811008 bytes
Database mounted.
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/T3/ORACLE/oradata/ORA9/redo01.log'
看看丢失了哪些redo
SQL>; host ls /T3/ORACLE/oradarta/ORA9/redo*
/T3/ORACLE/oradarta/ORA9/redo*: No such file or directory
看来redo都丢了
直接recover
alter system set "_allow_resetlogs_corruption"=true scope=spfile;
1.SQL>create pfile='/opt/oracle/product/11g/db/dbs/init.ora' from spfile;
2.修改/opt/oracle/product/11g/db/dbs/init.ora在文件最后加上一行:_allow_resetlogs_corruption=true
3.SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup pfile='/opt/oracle/product/11g/db/dbs/init.ora'
alter system set events '10046 trace name context off';
alter system set timed_statistics=false；