Windows Server 2003 SP2 installation

Got the following error while trying to install SP2 over Windows Server 2003

"The product key used to install Microsoft Windows may not be valid. For
more information about why you have received this error message, and steps
you can take to resolve the issue visit www.howtotell.com "

Interview Questions

Nice collection of Interview questions

http://dbaanswers.blogspot.com/2007/06/sroracle-dba-racdatagaurd-interview.html

ORA-29740 RAC Node eviction

Detailed discussion on Node eviction errors in RAC environments.

http://empo007.itpub.net/post/12076/459231

Reason 0 = No reconfiguration
Reason 1 = The Node Monitor generated the reconfiguration.
Reason 2 = An instance death was detected.
Reason 3 = Communications Failure
Reason 4 = Reconfiguration after suspend

Important files to review are:

a) Each instance's alert log
b) Each instance's LMON trace file
c) Statspack reports from all nodes leading up to the eviction
d) Each node's syslog or messages file
e) iostat output before, after, and during evictions
f) vmstat output before, after, and during evictions
g) netstat output before, after, and during evictions

Waits and Statistics for RAC Monitoring

Nice listing on Waits and statistics for RAC

http://www.dba-oracle.com/real_application_clusters_rac_grid/rac_monitoring.html

Manual Upgrade steps to V10.2

Manual Upgrade procedure steps

SQL > SPOOL upgrade.log
SQL>@$ORACLE_HOME/rdbms/admin/utlu102i.sql
SQL> spool off
Check the upgrade.log spool file to see if we meet all upgrade requirements.

Backup the database we are going to upgrade using RMAN or user-managed backup techniques

Copy the current init.ora file to its default location in the new Oracle database 10gR2 home($ORACLE_HOME/dba).

Shut down the database and restart it under the new Oracle 10g home in the STARTUP UPGRADE mode

SQL> connect sys/AS SYSDBA
SQL> STARTUP UPGRADE
SQL > CREATE TABLESPACE sysaux DATAFILE '/u10/oradata/prod/sysaux01.dbf'
SIZE 500M REUSE EXTENT MANAGEMENT LOCAL SEGMENT SPACE
MANAGEMENT AUTO;
SQL > select * from V$VERSION;
Run the catupgrd.sql upgrade script
SQL> select count(*) from dba_objects where status='INALID';
Recompile any objects that became invalid during the database upgrade
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
SQL> select count(*) dba_objects where status='INVALID';
The query should return 0 rows.
Run the Post-Upgrade Status tool
SQL>@$ORACLE_HOME/rdbms/admin/utlu102s.sql
SQL > spool OFF
SQL > SHUTDOWN IMMEDIATE
SQL> STARTUP

Moving to ASM using RMAN

Steps for moving database to ASM using RMAN

SQL> Alter system set controlfiles='+new2','+new3' scope=spfile;
SQL> shutdown immediate;
SQL>startup nomount;
Launch RMAN(RMAN client),restore controlfile from its original location:
RMAN> restore controlfile from '/origloc/contrl1.ctl';
RMAN>shutdown immediate;
RMAN> startup mount;
RMAN>backup as copy database format '+new1';
RMAN> switch database to copy;
RMAN>alter database open;
To migrate redologs
SQL>alter database add logfile member '+new2','+new3' to group 1;
SQL>alter database drop logfile member '/origloc/log1a.rdo','/origloc/log1b.rdo';
Drop and create Temp ts.

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