Tuesday, May 1, 2018

ORA-01548: active rollback segment ‘_SYSSMU1_xxxx’ found, terminate dropping tablespace while deleting UNDOTBS1


Scenerio
Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production.
Database - In No archivelog Mode.
Red Hat Enterprise Linux Server release 6.5 (Santiago)

SQL> drop tablespace UNDOTBS1 including contents;
drop tablespace UNDOTBS1 including contents
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_1255349037$' found, terminate dropping tablespace

Workaround
SQL> CREATE UNDO TABLESPACE UNDOTBS DATAFILE '/database/as3phc/undo01.dbf' size 500m autoextend off;

SQL> ALTER SYSTEM SET UNDO_TABLESPACE = undotbs;

SQL> drop tablespace UNDOTBS01 including contents;
drop tablespace UNDOTBS01 including contents
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_1255349037$' found, terminate dropping tablespace

SQL> drop rollback segment '_SYSSMU1_1255349037$';
drop rollback segment '_SYSSMU1_1255349037$'
*
ERROR at line 1:
ORA-02175: invalid rollback segment name

SQL> select segment_name, status, tablespace_name from dba_rollback_segs;

SEGMENT_NAME               STATUS                                 TABLESPACE_NAME
-----------------------------------------------------------------------------------------------------
SYSTEM                                ONLINE                                 SYSTEM
_SYSSMU1_3780397527$    PARTLY AVAILABLE        UNDOTBS1
...
...

SQL> alter system set "_offline_rollback_segments="_SYSSMU1_1255349037$" scope=spfile;

SQL> Shutdown immediate;

SQL> Startup

SQL> select segment_name, status, tablespace_name from dba_rollback_segs;
SEGMENT_NAME               STATUS                                 TABLESPACE_NAME
-----------------------------------------------------------------------------------------------------
SYSTEM                                ONLINE                                 SYSTEM
_SYSSMU1_1255349037$   NEEDS RECOVERY                        UNDOTBS1
...
...

SQL> drop tablespace UNDOTBS1 including contents and datafiles;
drop tablespace UNDOTBS1 including contents and datafiles
*
ERROR at line 1:
ORA-01548: active rollback segment '_SYSSMU1_1255349037$' found, terminate
dropping tablespace

SQL> drop rollback segment "_SYSSMU1_1255349037$";
Rollback segment dropped.

SQL> drop tablespace UNDOTBS1 including contents and datafiles;
Tablespace dropped.

SQL> alter system reset "_offline_rollback_segments" scope=spfile;

SQL> shut immediate;

SQL> Startup

SQL> select segment_name,status,tablespace_name from dba_rollback_segs;

SEGMENT_NAME               STATUS        TABLESPACE_NAME
---------------------------------------------------------------------------------------------
SYSTEM                                ONLINE         SYSTEM
_SYSSMU1_1266566787$    ONLINE        UNDOTBS
...
...
...
_______________________________________________________________________


Tuesday, March 27, 2018

ORA-27546: Oracle compiled against IPC interface version 3.2 found version 3.3



Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit
Red Hat Enterprise Linux Server release 5.3 (Tikanga)
3 Node RAC Database Server

One of the nodes had 10gR2 Oracle home installed. Removed the home using 11gR2 OUI. Suddenly database stopped working. When restarted the service in that node, database was not coming up, and found the below error message in alert log.

ORA-27546: Oracle compiled against IPC interface version 3.2 found version 3.3
Process J001 died, see its trace file
kkjcre1p: unable to spawn jobq slave process

Workaround

Copied $ORACLE_HOME/lib/libskgxp11.so from the first node to the third node, same location. And then :

$ relink all

Started the services … and its there up … :-)

Note : Ideas& Inspirations

Monday, March 26, 2018

ORA-10567: Redo is inconsistent with data block



Oracle Database 12c Standard Edition Release 12.1.0.2.0 - 64bit Production.
Database - In No archivelog Mode.
Red Hat Enterprise Linux Server release 6.5 (Santiago)

Database was in MOUNT stage after restart. Below erros where found in alert log

Reading datafile '/database/as3phc/undotbs01.dbf' for corruption at rdba: 0x00c42d88 (file 3, block 273800)
Reread (file 3, block 273800) found same corrupt data (logically corrupt)
Mon Mar 26 12:34:24 2018
Aborting crash recovery due to error 1172
Mon Mar 26 12:34:24 2018
Errors in file /oracle/diag/rdbms/as3phc/as3phc/trace/as3phc_ora_3766.trc:
ORA-01172: recovery of thread 1 stuck at block 273800 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed
Mon Mar 26 12:34:24 2018
Errors in file /oracle/diag/rdbms/as3phc/as3phc/trace/as3phc_ora_3766.trc:
ORA-01172: recovery of thread 1 stuck at block 273800 of file 3
ORA-01151: use media recovery to recover block, restore backup if needed
ORA-1172 signalled during: ALTER DATABASE OPEN...
...
...
Reading datafile '/database/as3phc/undotbs01.dbf' for corruption at rdba: 0x00c42d88 (file 3, block 273800)
Reread (file 3, block 273800) found same corrupt data (logically corrupt)
ERROR: ORA-00756 detected lost write of a data block
Slave exiting with ORA-756 exception
Mon Mar 26 14:17:44 2018
Errors in file /oracle/diag/rdbms/as3phc/as3phc/trace/as3phc_pr04_7333.trc:
ORA-00756: recovery detected a lost write of a data block
ORA-10567: Redo is inconsistent with data block (file# 3, block# 273800, file offset is 2242969600 bytes)
ORA-10564: tablespace UNDOTBS1
ORA-01110: data file 3: '/database/as3phc/undotbs01.dbf'
ORA-10560: block type 'KTU UNDO BLOCK'

Solution:-
Tried :-
SQL> recover database;
SQL> recover database until cancel;
SQL> recover datafile 3;

Got almost the same errors :

Reading datafile '/database/as3phc/undotbs01.dbf' for corruption at rdba: 0x00c42d88 (file 3, block 273800)
Reread (file 3, block 273800) found same corrupt data (logically corrupt)
ERROR: ORA-00756 detected lost write of a data block
Mon Mar 26 14:24:50 2018
Media Recovery failed with error 756

Finally, did :

$ rman target /
RMAN> recover database allow 1 corruption;
exit

SQL> alter database open restlogs;
---------------------------------------------------------------------------------------------------------- 

Saturday, February 7, 2015

Segmentation fault - During IMP from 11g Database - EXP & IMP Utility Version 9iR2

Scenerio 
Oracle IMP gives 'Segmentation fault' error . EXP  was taken from 11g R2 database using 9iR2 export utility. Import utility is also of 9iR2 version.
---------------------------------------------------------------------------------------------------------------
Import: Release 9.2.0.6.0 - Production on Sun Feb 8 10:23:48 2015

Copyright (c) 1982, 2002, Oracle Corporation.  All rights reserved.

Username: system/system

Connected to: Oracle9i Enterprise Edition Release 9.2.0.6.0 - Production
With the Partitioning option
JServer Release 9.2.0.6.0 - Production

Export file created by EXPORT:V09.02.00 via conventional path

Warning: the objects were exported by ME, not by you

import done in WE8ISO8859P1 character set and AL16UTF16 NCHAR character setSegmentation fault
--------------------------------------------------------------------------------------------------------------- 
Workaround 
1. Connect to database 11g as SYSDBA with SQL*Plus
2. Change the definition of view EXU9DEFPSWITCHES from:

CREATE OR REPLACE VIEW SYS.EXU9DEFPSWITCHES
(COMPFLGS, NLSLENSEM)
AS
SELECT  a.value, b.value
        FROM    sys.v$parameter a, sys.v$parameter b
        WHERE   a.name = 'plsql_compiler_flags' AND
                b.name = 'nls_length_semantics'

to:
CREATE OR REPLACE VIEW SYS.EXU9DEFPSWITCHES
(COMPFLGS, NLSLENSEM)
AS
SELECT  a.value, b.value
        FROM    sys.v$parameter a, sys.v$parameter b
        WHERE   a.name = 'plsql_code_type' AND
                b.name = 'nls_length_semantics'


3. Now take the export using exp utility 9iR2 version
4. Import using imp utility 9iR2 version

---------------------------------------------------------------------------------------------------------------
Oracle Doc ID : 550740.1