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;
----------------------------------------------------------------------------------------------------------