When logfiles are not present in the actual locations. may have been dropped by accident.
16:33:41 SYS@eqdd011g SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-00313: open failed for members of log group 1 of thread 1
ORA-00312: online log 1 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo01.log'
I tried to add a new one, but it failed as controlfile had the information. Logs were UNUSED in v$log
Elapsed: 00:00:00.68
16:33:49 SYS@eqdd011g SQL> alter database add logfile '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo01.log' size 5m;
alter database add logfile '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo01.log' size 5m
*
ERROR at line 1:
ORA-01577: cannot add log file '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo01.log' - file already part of database
UNUSED logfiles can be dropped straight
Elapsed: 00:00:00.10
16:34:24 SYS@eqdd011g SQL> alter database drop logfile group 1;
Database altered.
Elapsed: 00:00:00.38
Then added those files again
16:34:41 SYS@eqdd011g SQL> alter database add logfile '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo01.log' size 5m;
Database altered.
Elapsed: 00:00:00.66
One more attempt to open the database
16:34:46 SYS@eqdd011g 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: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo02.log'
Elapsed: 00:00:00.13
2nd loggroup this time
16:34:49 SYS@eqdd011g SQL> alter database drop logfile group 2;
Database altered.
Elapsed: 00:00:00.33
16:34:58 SYS@eqdd011g SQL> alter database add logfile '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo02.log' size 5m;
Database altered.
Elapsed: 00:00:00.55
16:35:09 SYS@eqdd011g SQL> 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/admin/eqdd011g/oradata/eqdd011g/redo03.log'
Elapsed: 00:00:00.17
Log was current and unable to drop, it was not even archived since it was current
16:35:13 SYS@eqdd011g SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01623: log 3 is current log for instance eqdd011g (thread 1) - cannot drop
ORA-00312: online log 3 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo03.log'
Elapsed: 00:00:00.05
16:35:20 SYS@eqdd011g SQL> alter system switch logfile;
alter system switch logfile
*
ERROR at line 1:
ORA-01109: database not open
Elapsed: 00:00:00.02
16:36:22 SYS@eqdd011g SQL> ALTER DATABASE CLEAR LOGFILE GROUP 3;
ALTER DATABASE CLEAR LOGFILE GROUP 3
*
ERROR at line 1:
ORA-00350: log 3 of instance eqdd011g (thread 1) needs to be archived
ORA-00312: online log 3 thread 1: '/u01/app/oracle/admin/eqdd011g/oradata/eqdd011g/redo03.log'
Elapsed: 00:00:00.17
16:38:00 SYS@eqdd011g SQL> ALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 3;
Database altered.
Elapsed: 00:00:02.69
16:38:13 SYS@eqdd011g SQL> alter database drop logfile group 3;
Database altered.
16:39:11 SYS@eqdd011g SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ------ ------------- --------------------
2 1 0 5242880 1 YES UNUSED 0
1 1 91 5242880 1 NO CURREN 3220382 24-SEP-2009 16:38:10
2 rows selected.
Elapsed: 00:00:00.06
16:39:28 SYS@eqdd011g SQL> alter database open;
Database altered.
MMON encountered error 959, clearing the error
Poor mmon !!! is looking for a nonexistent TS and alerting for a threshold. God save him.
This is what is happening.... 959 error is legitimate and may be due to a BUG. DB restart will not take the error away. Error is coming every 10 mins since 2008 in MMON trace file.
Detailed Analysis:
Find out MMON spid. That is available in MMON trace file in bdump
Unix process pid: 25361, image: oracle@wbeqdrac03p.us.net.intra (MMON)
-- Attach this SPID above
09:07:37 SYS@gptprd1 SQL> oradebug setospid 25361;
Oracle pid: 16, Unix process pid: 25361, image: oracle@wbeqdrac03p.us.net.intra (MMON)
09:08:02 SYS@gptprd1 SQL> oradebug unlimit
Statement processed.
09:09:49 SYS@gptprd1 SQL> oradebug Event 959 trace name errorstack level 3
Statement processed.
-- From MMON trace file in background_dump_dest
Received ORADEBUG command 'unlimit' from process Unix process pid: 12666, image:
*** 2009-09-01 09:10:17.882
Received ORADEBUG command 'Event 959 trace name errorstack level 3' from process Unix process pid: 12666, image:
-- WAIT UP TO 10 MINS FOR ERROR TO BE GENERATED
-- Then ... From MMON trace file in background_dump_dest
*** 2009-09-01 09:15:14.758
ksedmp: internal or fatal error
ORA-00959: tablespace 'STREAMS_TBS' does not exist
----- Call Stack Trace -----
SQL> -- Disable trace
09:10:17 SYS@gptprd1 SQL> oradebug Event 959 trace name errorstack off
Statement processed.
-- From MMON trace file in background_dump_dest
*** 2009-09-01 09:19:37.882
Received ORADEBUG command 'Event 959 trace name errorstack off' from process Unix process pid: 12666, image:
MMON is looking for ts STREAMS_TBS which does not exist. It fetches it from ts$ table.
09:22:11 SYS@gptprd1 SQL> select * from dba_tablespaces where tablespace_name = 'STREAMS_TBS';
no rows selected
Elapsed: 00:00:00.01
09:22:30 SYS@gptprd1 SQL> select * from ts$ where name = 'STREAMS_TBS';
TS# NAME OWNER# ONLINE$ CONTENTS$ UNDOFILE# UNDOBLOCK# BLOCKSIZE INC# SCNWRP SCNBAS DFLMINEXT DFLMAXEXT DFLINIT DFLINCR DFLMINLEN
---------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
DFLEXTPCT DFLOGGING AFFSTRENGTH BITMAPPED PLUGGED DIRECTALLOWED FLAGS PITRSCNWRP PITRSCNBAS OWNERINSTANCE BACKUPOWNER GROUPNAME
---------- ---------- ----------- ---------- ---------- ------------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------------
SPARE1 SPARE2
---------- ----------
SPARE3
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SPARE4
--------------------
14 STREAMS_TBS 0 3 0 0 0 8192 2 0 0 1 2147483645 8 128 8
0 1 0 8 0 1 33 6 3112294409
0 0
Workaround:
1. Lets create a tablespace STREAMS_TBS again, remove the thresholds manually for that TS and then drop it.
Solution
1. Contact Oracle with this analysis and check if its a BUG (6922680) that ts is removed and still present in TS$
This is what is happening.... 959 error is legitimate and may be due to a BUG. DB restart will not take the error away. Error is coming every 10 mins since 2008 in MMON trace file.
Detailed Analysis:
Find out MMON spid. That is available in MMON trace file in bdump
Unix process pid: 25361, image: oracle@wbeqdrac03p.us.net.intra (MMON)
-- Attach this SPID above
09:07:37 SYS@gptprd1 SQL> oradebug setospid 25361;
Oracle pid: 16, Unix process pid: 25361, image: oracle@wbeqdrac03p.us.net.intra (MMON)
09:08:02 SYS@gptprd1 SQL> oradebug unlimit
Statement processed.
09:09:49 SYS@gptprd1 SQL> oradebug Event 959 trace name errorstack level 3
Statement processed.
-- From MMON trace file in background_dump_dest
Received ORADEBUG command 'unlimit' from process Unix process pid: 12666, image:
*** 2009-09-01 09:10:17.882
Received ORADEBUG command 'Event 959 trace name errorstack level 3' from process Unix process pid: 12666, image:
-- WAIT UP TO 10 MINS FOR ERROR TO BE GENERATED
-- Then ... From MMON trace file in background_dump_dest
*** 2009-09-01 09:15:14.758
ksedmp: internal or fatal error
ORA-00959: tablespace 'STREAMS_TBS' does not exist
----- Call Stack Trace -----
SQL> -- Disable trace
09:10:17 SYS@gptprd1 SQL> oradebug Event 959 trace name errorstack off
Statement processed.
-- From MMON trace file in background_dump_dest
*** 2009-09-01 09:19:37.882
Received ORADEBUG command 'Event 959 trace name errorstack off' from process Unix process pid: 12666, image:
MMON is looking for ts STREAMS_TBS which does not exist. It fetches it from ts$ table.
09:22:11 SYS@gptprd1 SQL> select * from dba_tablespaces where tablespace_name = 'STREAMS_TBS';
no rows selected
Elapsed: 00:00:00.01
09:22:30 SYS@gptprd1 SQL> select * from ts$ where name = 'STREAMS_TBS';
TS# NAME OWNER# ONLINE$ CONTENTS$ UNDOFILE# UNDOBLOCK# BLOCKSIZE INC# SCNWRP SCNBAS DFLMINEXT DFLMAXEXT DFLINIT DFLINCR DFLMINLEN
---------- ------------------------------ ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ----------
DFLEXTPCT DFLOGGING AFFSTRENGTH BITMAPPED PLUGGED DIRECTALLOWED FLAGS PITRSCNWRP PITRSCNBAS OWNERINSTANCE BACKUPOWNER GROUPNAME
---------- ---------- ----------- ---------- ---------- ------------- ---------- ---------- ---------- ------------------------------ ------------------------------ ------------------------------
SPARE1 SPARE2
---------- ----------
SPARE3
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SPARE4
--------------------
14 STREAMS_TBS 0 3 0 0 0 8192 2 0 0 1 2147483645 8 128 8
0 1 0 8 0 1 33 6 3112294409
0 0
Workaround:
1. Lets create a tablespace STREAMS_TBS again, remove the thresholds manually for that TS and then drop it.
Solution
1. Contact Oracle with this analysis and check if its a BUG (6922680) that ts is removed and still present in TS$
Subscribe to:
Posts (Atom)
Posts
