ORA-01548 active rollback segment , Unable drop undo tablespace

When undo get a huge rollback segment it may causing a lot trouble to shutdown and start. Oracle seems to check all active undo and do recovery. But sometime the rollback segment was bad.

To clean this up

1. find out active rollback segment. Either by SQL or try to drop undo tablespace.
   select segment_name, status from dba_rollback_segs where tablespace_name='undotbs_corrupt' and status = ‘NEEDS RECOVERY’;
2. shutdown database. It may take pretty long time to shutdown.
3. change parameter
   *.undo_management='MANUAL'
   *._offline_rollback_segments=_SYSSMU10$,_SYSSMU11$,_SYSSMU12$,_SYSSMU13$,_SYSSMU14$,_SYSSMU15$,_SYSSMU16$)
5. bring up database
6. Drop the UNDO tablespace.
7. Recreate UNDO tablespace.
8. shutdown database
9. change parameter back to original
10. bring up database

The key part is change undocumented parameter _offline_rollback_segments, this way it will allow you to drop active rollback segment.

ref: http://davidalejomarcos.wordpress.com/2010/11/09/ora-01548-active-rollback-segment-_syssmu1_xxxx-found-terminate-dropping-tablespace-while-deleting-undotbs/

ORA-12528: TNS:listener: all appropriate instances are blocking new connections

When database are in mount mode or restricted session enabled you'll get this error since 10g.

There are two way to get around it.

1. Put UR=A in your tnsnames.ora for the entry. e.g.
   

   STBY=
    (DESCRIPTION =
      (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))
      )
      (CONNECT_DATA =
        (SERVICE_NAME = STBY)
        (UR = A)
      )
    )
   

   
   
2. Create static listener to allow listener know which oracle SID to connect. e.g.
   SID_LIST_LISTENER =
   (SID_LIST =
   (SID_DESC =
   (GLOBAL_DBNAME = TESTDB.gov.edmonton.ab.ca)
   (ORACLE_HOME = /u01/app/ora10g/product/10.2.0.4/db_1)
   (SID_NAME = TESTDB1)
   )
   )
   After you change listener.ora you need reload listener to take effect.