ORA-23515: materialized views and/or their indices exist in the tablespace

Was trying to drop a tablespace that was not needed anymore and I receive this error:

drop tablespace ts1 including contents and datafiles cascade constraints;

ORA-23515: materialized views and/or their indices exist in the tablespace

Solution:
Run the following query to get the materialized views that it is talking about and drop them first and then you can drop the tablespace without any issues.

select 'drop materialized view '||owner||'.'||name||' PRESERVE TABLE;'
  from dba_registered_snapshots
 where name in (select table_name from dba_tables where tablespace_name = 'TS1');

RMAN Slow performance with "control file sequential read" wait event

We have a database which is about 40TB and the control file is about 900MB and we have about 1.5TB Archivelogs generation everyday.

So, was thinking it was because of all the above why "show all" takes about 45 minutes in RMAN...

But, for some reason I was not able to convince my self that it should take that long for just SHOW ALL command!!!.
Not only that, it takes way too long to just allocate channels and that was making our backups to take for ever...

Anyways, digging deeper found the following sql that just sits in there with "control file sequential read" wait event... This made me to hunt for the sql:

SELECT RECID, STAMP, TYPE OBJECT_TYPE, OBJECT_RECID, OBJECT_STAMP, OBJECT_DATA,
       TYPE, (CASE
                WHEN TYPE = 'DATAFILE RENAME ON RESTORE' THEN DF.NAME
WHEN TYPE = 'TEMPFILE RENAME' THEN TF.NAME
ELSE TO_CHAR (NULL)
 END)
  OBJECT_FNAME, (CASE
WHEN TYPE = 'DATAFILE RENAME ON RESTORE' THEN DF.CREATION_CHANGE#
WHEN TYPE = 'TEMPFILE RENAME' THEN TF.CREATION_CHANGE#
ELSE TO_NUMBER (NULL)
 END)
    OBJECT_CREATE_SCN, SET_STAMP, SET_COUNT
  FROM V$DELETED_OBJECT, V$DATAFILE DF, V$TEMPFILE TF
 WHERE OBJECT_DATA = DF.FILE#(+)
   AND OBJECT_DATA = TF.FILE#(+)
   AND RECID BETWEEN :b1 AND :b2
   AND (STAMP >= :b3 OR RECID = :b2)
   AND STAMP >= :b4
 ORDER BY RECID

That brought me into this metalink id# Rman Backup Very Slow After Mass Deletion Of Expired Backups (Disk, Tape) [ID 465378.1]

Work around solution made SHOW ALL to return the results in seconds from 45 minutes...

*** Please check with Oracle Support prior to deploying this workaround in your database to make user it is OK ***

Solution

Bug is fixed in 11g but until then, use the workaround of clearing the controlfile section which
houses v$deleted_object:

SQL> execute dbms_backup_restore.resetcfilesection(19);

Then clear the corresponding high water mark in the catalog :

SQL> select * from rc_database;
     --> note the 'db_key' and 'dbinc_key' of your target based on dbid

For pre-11G catalog schemas:

SQL> update dbinc set high_do_recid = 0 where db_key = '' and dbinc_key=;
SQL> commit;

For 11G+ catalog schemas:

SQL> update node set high_do_recid = 0 where db_key = '' and dbinc_key=;
SQL> commit;

ORA-00845: MEMORY_TARGET not supported on this system

We had one of our RAC Node crashed and all databases shutdown and as usual they should come back once the ASM is up.

But this one database would not start at all.

Grepping for PMON for that database returns nothing:

So, trying to start manually:

SQL*Plus: Release 11.2.0.2.0 Production on Mon May 27 04:55:22 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORA-00845: MEMORY_TARGET not supported on this system

Now that is weird, why would I get that? /dev/shm has no space? Really? but why? database is not started yet to utilize that!!!!

Grepping for database name gives me:

db2:11.2.0:node2:/opt/oracle>ps -ef |grep db2
oracle     493     1  0 Apr28 ?        00:00:00 oracledb2 (LOCAL=NO)
oracle   24841     1  0 04:51 ?        00:00:00 oracledb2 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   24846     1  0 04:51 ?        00:00:00 oracledb2 (DESCRIPTION=(LOCAL=YES)(ADDRESS=(PROTOCOL=beq)))
oracle   31322 26885  0 05:02 pts/1    00:00:00 grep db2
oracle   32553     1  0 Apr28 ?        00:00:00 oracleras2 (LOCAL=NO)

Hmm... I get nothing when I grep for PMON but there is still something running in the backgroud as hung process!!!

Time to kill those process...
db2:node2:/opt/oracle>kill -9 493 24841 24846 32553

Now do startup and database comes up without any complaints...