My Oracle DBA Experience !

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

...

_______________________________________________________________________

Note : Ideas & Inspirations

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