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

Oracle installation Step Throughs

Simple instructions for installation of Oracle in Linux
http://www.mike-devlin.com/oracle/oracle_install.htm

How to Install and Configure Oracle on Linux

Installation gotchas for Oracle Linux , Oracle RAC 10gR2 and VMware ESX 3.0.1


Installing Oracle 10g Release 2 Clusterware on a 2-node Windows 2003 Enterprise Edition Server

TAF Transparent App Failover in non-RAC standby database instances

Nice article on using Transparent Application Failover in non-RAC standby instances

http://www.freelists.org/post/oracle-l/TAF-and-node-failover-non-RAC-oracle-102,15

Transparent Application Failover in non-RAC

Start Oracle when OS server reboots

http://www.orafaq.com/wiki/Unix_FAQ
Make sure the entry for your database SID in the ORATAB file ends in a capital "Y". Eg:

# $ORACLE_SID:$ORACLE_HOME:[N|Y]
#
ORCL:/u01/app/oracle/product/8.0.5:Y
#

The scripts for starting and stopping databases are: $ORACLE_HOME/bin/dbstart and dbshut. SQL*Net (or Net8) is started with the lsnrctl command. Add the following entries to your /etc/rc2.d/S99dbstart (or equivalent - for example HP-UX uses /sbin/rc2.d/*) file:

su - oracle -c "/path/to/$ORACLE_HOME/bin/dbstart" # Start DB's
su - oracle -c "/path/to/$ORACLE_HOME/bin/lsnrctl start" # Start listener

Return value from sqlplus to shell variable

http://www.orafaq.com/wiki/Unix_FAQ
For returning single value use this ...
yourvalue=`sqlplus -s $LOGON_NAME/$LOGON_PASSWD << EOF
set heading off;
select col2 from table_a where col1 = 10;
exit;
EOF`

For returning multiple values use this ...
sqlplus -s /nolog |& # Open a pipe to SQL*Plus

print -p -- 'connect user/password@instance'
print -p -- 'set feed off pause off pages 0 head off veri off line 500'
print -p -- 'set term off time off'
print -p -- "set sqlprompt "

print -p -- "select sysdate from dual;"
read -p SYSDATE

print -p -- "select user from dual;"
read -p USER

print -p -- "select global_name from global_name;"
read -p GLOBAL_NAME

print -p -- exit

echo SYSDATE: $SYSDATE
echo USER: $USER
echo GLOBAL_NAME: $GLOBAL_NAME


For passing error value back to shell use the following

http://www.sap-img.com/oracle-database/passing-values-back-to-unix-script-from-sqlplus.htm

write following code in sql script
set echo on
whenever sqlerror exit 1
whenever oserror exit 2
--your code--
exit 0
write following code in shell script to receive the value
--call sql script
ret_val=$?
now ret val will have value 0 if sql script did not fail and 1 or 2 otherwise

Filing a iTAR with Oracle

Information one needs to file a Oracle TAR

Oracle dbms_redefinition

Oracle Tables and Indexes may be rebuild online using dbms_redefinition utility package. Exceptions are those tables on which Materialized views are defined.

While making drastic changes to the table structure using the CAN_REDEF_TABLE may be useful to findout whether online rebuilding is possible or not.

The following is an useful article

Oracle dbms_redefinition

DB Link creation format

Just for reference, even if there is no tns_names entry, we can use the complete connect string.

create database link MY_DBLINK connect to schema_user identified by pswd using '(DESCRIPTION =(ADDRESS_LIST =(ADDRESS =(COMMUNITY = tcp.world)(PROTOCOL = TCP)(Host = myserver.unix.myorg.org)(Port = 1521)))(CONNECT_DATA = (SID = MYDB_SID)))'


With tns entry: create database link MY_DBLINK connect to schema_user identified by pswd using 'TNS_CONNECT_ENTRY'

ORA-01034: ORACLE not available error

While logging into db through sqlplus (9.2.0.5.0), got the following error.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> select 1 from dual;
select 1 from dual
*
ERROR at line 1:
ORA-01034: ORACLE not available

ORA-01034: ORACLE not available error
ORA-27101: shared memory realm does not exist
HP-UX Error: 2: No such file or directory

The ORACLE_SID env variable was checked and found ok.
It was found that TWO_TASK: parameter not set
Same set to the proper SID.

Now able to connect to db.


http://www.orafaq.com/wiki/ORA-01034

Find the Tablespace usage/freespace information

Tablespace size

Scripts for disabling and enabling constraints

Running the following statements in sqlplus will create two sql files with statements for disabling and enabling FK constraints in current schema.

set echo on
set verify on
set feedback off
set heading off
set pagesize 0
set linesize 1000
spool disable_constraints_script.sql
select 'alter table '||table_name||' disable constraint '||constraint_name||';' from user_constraints where constraint_type in ('R');

spool enable_constraints_script.sql
select 'alter table '||table_name||' modify constraint '||constraint_name||' enable novalidate;' from user_constraints where constraint_type in ('R');