ORA-00312: online log 1 thread 1 ... and resolve for Today

Oracle TEST
V. 11.1.0.6.0

ไม่สามารถ start database


SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16038: log 1 sequence# 100 cannot be archived
ORA-19502: write error on file "", block number (block size=)
ORA-00312: online log 1 thread 1: '/oradata/testdb2/redo01.log'

ดังนั้น recover database:

SQL> recover database until cancel;
Media recovery complete.

SQL> ALTER DATABASE OPEN RESETLOGS;

ตัวอย่าง ORA-00313 เมื่อบาง member ใน group ถูกลบ

SQL> alter database add logfile member '/tmp/redo033.log' to group 3;

Database altered.

SQL> ho
$ ls -la /tmp/redo033.log
-rw-r----- 1 oracle oinstall 52429312 May 19 17:13 /tmp/redo033.log


SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 436 52428800 2 YES ACTIVE 32611337 19-MAY-08
2 1 437 52428800 2 NO CURRENT 32611361 19-MAY-08
3 1 435 52428800 3 YES INACTIVE 32611335 19-MAY-08


$ rm /tmp/redo033.log

SQL> alter system switch logfile;
-----------
NO ERROR:
-----------

Check alert log:

Errors in file testdb_arc0_25296.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/tmp/redo033.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory


SQL> alter database drop logfile member '/tmp/redo033.log';

Database altered.

SQL> alter database add logfile member '/tmp/redo033.log' to group 3;

Database altered.


------------------------------


SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 463 52428800 2 YES ACTIVE 32612535 19-MAY-08
2 1 462 52428800 2 YES ACTIVE 32612521 19-MAY-08
3 1 464 52428800 3 NO CURRENT 32612545 19-MAY-08

SQL> ho

$ rm /tmp/redo033.log


SQL> alter system switch logfile;
-----------
NO ERROR:
-----------

Check alert log:
Errors in file testdb_m000_14312.trc:
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/tmp/redo033.log'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Thread 1 cannot allocate new log, sequence 466


SQL> alter database drop logfile member '/tmp/redo033.log';

Database altered.

SQL> alter database add logfile member '/tmp/redo033.log' to group 3;

Database altered.

--------------------------------

อยากรู้ว่า Oracle Database ใช้spfile หรือไม่

ตอบง่ายๆ ว่านี่ไง
SQL> show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string +DATA/testdb/spfiletestdb.ora

จากนั้นก็เกิดคำถามว่ามีวิธีอื่นอีกไหมละนี่
มีครับ เราสามารถเช็คได้ที่ V$SPPARAMETER View ตัวนี้เลยครับ

Column	Datatype	Description
SID	VARCHAR2(80)	SID for which the parameter is defined
NAME	VARCHAR2(80)	Name of the parameter
VALUE	VARCHAR2(255)	Parameter value (null if a server parameter file was not used to start the instance)
DISPLAY_VALUE	VARCHAR2(255)	Parameter value in a user-friendly format. For example, if the VALUE column shows the value 262144 for a big integer parameter, then the DISPLAY_VALUE column will show the value 256K.
ISSPECIFIED	VARCHAR2(6)	Indicates whether the parameter was specified in the server parameter file (TRUE) or not (FALSE)
ORDINAL	NUMBER	Position (ordinal number) of the parameter value (0 if a server parameter file was not used to start the instance). Useful only for parameters whose values are lists of strings.
UPDATE_COMMENT	VARCHAR2(255)	Comments associated with the most recent update (null if a server parameter file was not used to start the instance)

V$SPPARAMETER จะบอกข้อมูลเกี่ยวกับ spfile, แล้วจะใช้ไม่ใช้ spfile ตอน startup database นี่ ดูที่ ISSPECIFIED .

ตัวอย่าง query

SQL> select isspecified, count(*) from v$spparameter group by
isspecified;

ISSPEC COUNT(*)
------ ----------
TRUE 40
FALSE 254

ผลลัพธ์คือใช้ spfile ครับ
ทำไมละครับ ก็เพราะว่ามี บางค่าใน view มี ISSPECIFIED = TRUE
แล้วถ้าไม่ใช้ละ

SQL> shutdown immediate;

Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup pfile=/tmp/pfile;
ORACLE instance started.

Total System Global Area 1703624704 bytes
Fixed Size 2145064 bytes
Variable Size 1291846872 bytes
Database Buffers 402653184 bytes
Redo Buffers 6979584 bytes
Database mounted.
Database opened.

SQL> select isspecified, count(*) from v$spparameter group by
isspecified;

ISSPEC COUNT(*)
------ ----------
FALSE 289
ไม่มี TRUE ตรงนี้เลยไม่ใช้ spfile ครับ

---> กลับไปใช้ spfile:

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.

Total System Global Area 1703624704 bytes
Fixed Size 2145064 bytes
Variable Size 1291846872 bytes
Database Buffers 402653184 bytes
Redo Buffers 6979584 bytes
Database mounted.
Database opened.

SQL> select isspecified, count(*) from v$spparameter group by
isspecified; 2

ISSPEC COUNT(*)
------ ----------
TRUE 40
FALSE 254

หรือนี่เลยครับ

SQL> select decode(count(*), 1, 'spfile', 'pfile' )
from v$spparameter
where rownum=1
and isspecified='TRUE';

DECODE
------
spfile



Enjoy!