Friday, May 31, 2013

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





RMAN-20035: invalid high recid

While moving our database backups to use new version of HP Data Protector software, I got a hick up on one database with "RMAN-20035: invalid high recid" error.

RMAN-03014: implicit resync of recovery catalog failed
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20035: invalid high recid

Also, we were moving our RMAN catalog from one database to another database to resolve another performance issue. So, it might be due to sync issue.

Solution to that was to un-register the database from RMAN catalog and re-register.

More on this topic/issue in metalink id#273446.1

Note, you can unregister using dbms_rcvcat.unregisterdatabase procedure also but I am showing what I did.

To Unregister:
Connect to your RMAN catalog as how you generally do take your db backup and run:

RMAN> unregister database;

To Register:
RMAN> register database;

After this I was able to backup the database successfully...

Friday, May 10, 2013

Finding Bind variables in SQL's executed...

v$sql_bind_capture --> this view shows the bind variables passed to the sql at the run time
DBA_HIST_SQLBIND --> historical information

Ex:

select b.last_captured, a.sql_text, b.name, b.position, b.datatype_string, b.value_string
  from dba_hist_sqlbind b,
       --gv$sql_bind_capture b,
       v$sqlarea a
 where b.sql_id = '66j3kkqnv3m2f' --> sqlid that you are interested in looking for bind variable passed.
   and b.sql_id = a.sql_id

From Oracle Docs on DBA_HIST_SQLBIND:

DBA_HIST_SQLBIND displays historical information on bind variables used by SQL cursors. This view contains snapshots of V$SQL_BIND_CAPTURE.
LAST_CAPTUREDDATE Date when the bind value was captured. Bind values are captured when SQL statements are executed. To limit the overhead, binds are captured at most every 15 minutes for a given cursor.