How to Recover Deleted Oracle Datafiles with No Downtime - Good article

Nice article on recovering from a deleted datafile without downtime

http://www.pythian.com/blogs/656/how-to-recover-deleted-oracle-datafiles-with-no-downtime

ps -ef | grep dbw0_SID_OF_YOUR_DB

ls -l /proc/_PID_OF_DBWRITER/fd | grep deleted
lrwx------ 1 oracle oinstall 64 Oct 15 11:24 23 -> /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k6xzjpm_.dbf (deleted)

Create a symbolic link to your datafile with the original name.
ln -s /proc/PID_OF_DBWRITER/fd/23 /ra5a/orabkp/test/TEST/datafile/o1_mf_lost_3k6xzjpm_.dbf

alter system checkpoint;

rman target /
report schema;
backup as copy datafile YOUR_DATAFILE_NUMBER format '/location_of_your_database/new_name_for_File.dbf';
sql 'alter database datafile YOUR_DATAFILE_NUMBER offline';
switch datafile YOUR_DATAFILE_NUMBER to copy;
recover datafile YOUR_DATAFILE_NUMBER;
sql 'alter database datafile YOUR_DATAFILE_NUMBER online';
exit;

Restore file for NoArchivelog:
select distinct tablespace_name from dba_data_files where file_name = 'YOUR_DELETED_FILE';
alter tablespace THE_TABLESPACE read only;

Copy the file from the symlink to a new name
cp SIM_LINK_DATA_FILE NEW_NAME_FOR_DATAFILE.dbf
/*WAIT FOR COPY!!!*/
shutdown abort;
rm -i SIM_LINK_DATA_FILE
mv NEW_NAME_FOR_DATAFILE.dbf SIM_LINK_DATA_FILE
startup
alter tablespace THE_TABLESPACE read write;

Diagnosis of Database Hang

Capturing the status when database hangs is useful for getting Oracle Support

CHECK FOR WAITING SESSION
set lines 132 pages 999
column event format a30
select sid,event,seq#,p1,p2,p3,wait_time from V$session_wait where SID = &&SID;
select sid,event,seq#,p1,p2,p3,wait_time from V$session_wait where SID = &&SID;
select sid,event,seq#,p1,p2,p3,wait_time from V$session_wait where SID = &&SID;
See: Note 43718.1 VIEW “V$SESSION_WAIT” Reference Note

TRACE 10046
10046 trace gathers tracing information about a session.
alter session set timed_statistics = true;
alter session set statistics_level=all;
alter session set max_dump_file_size = unlimited;
alter session set events ‘10046 trace name context forever,level 12′;

TAKE HANGANALYZE DUMP
SQL>sqlplus “/ as sysdba”
SQL> oradebug setmypid
SQL> oradebug unlimit;
SQL> oradebug hanganalyze 3
SQL>oradebug tracefile_name
[or]
SQL>alter session set max_dump_file_size = unlimited;
SQL>ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME HANGANALYZE LEVEL 3′;

TAKE SYSTEMSTATE DUMP
SQL> oradebug setmypid
SQL> oradebug unlimit;
SQL> oradebug dump systemstate 266
SQL>oradebug tracefile_name
[or]
SQL>alter session set max_dump_file_size = unlimited;
SQL>ALTER SESSION SET EVENTS ‘IMMEDIATE TRACE NAME SYSTEMSTATE LEVEL 266′;
For Oracle 9.2.0.5 and less use level 10 instead of 266
alter session set events ‘10998 trace name context forever, level 1′;
alter session set events ‘immediate trace name systemstate level 10′;

Note 121779.1-Taking a SYSTEMSTATE dump when you cannot CONNECT to Oracle.
In 10g login using sqlplus -prelim “/ as sysdba” and run systemstate or hanganalyze
In Solaris
(gdb) file $ORACLE_HOME/bin/oracle
(gdb) attach your_pid
(gdb) call ksudss(10)
(gdb) detach

TAKE ERRORSTACK DUMP
oradebug setospid 9834
oradebug unlimit
oradebug event 10046 trace name context forever,level 12
oradebug dump errorstack 3


http://askdba.org/weblog/?p=81

ORA-600 Errors

Immediately refer to the following metalink site followed by raising a TAR
http://metalink.oracle.com/metalink/plsql/showdoc?db=NOT&id=153788.1&blackframe=0

SRVCTL - RAC Management

Set the following OS parameter, to enable debug of srvctl.

$export SRVM_TRACE=true

If there is any error starting up any resource, we'll get detailed debugging information regarding failures.

Use srvctl for starting and shuttingdown of all resources including nodeapps (VIP, GSD, Listener, ONS), ASM, database and node instances and services.

srvctl start nodeapps -n node1
srvctl status nodeapps -n node1
srvctl start asm -n node1
srvctl start database -d db_glb_name
srvctl start instance -d db_glb_name -i instance_name