Only One Node Starts in a 2 RAC Cluster.

Recently we had a server move from one DC to other.Posting the issue faced on the one of the system.

Configuration as below,

1).AIX Machine
2).11.1.0.7 Database
3).2 Node Cluster.

We had a clear shutdown before the move.And upon starting the Cluster & Database, only one Node comes up.Which ever starts first will have full running CRS/CSS Stack ,the other node fails to start CSS and throws following error in the crsd.log.

20xx-xx-xx xx:xx:xx.xxx: [ COMMCRS][903]clsc_connect: (600000000033e030) no listener at (ADDRESS=(PROTOCOL=ipc)(KEY=OCSSD_LL_njzdb12_crs))
20xx-xx-xx xx:xx:xx.xxx: [ CSSCLNT][1]clsssInitNative: connect failed, rc 9
20xx-xx-xx xx:xx:xx.xxx: [ CRSRTI][1] CSS is not ready. Received status 3 from CSS. Waiting for good.

Googling for the above error mostly points to the issue with /tmp/.oracle or /var/tmp/.oracle directory.The files under which has been deleted when the CRS was running.It was true that one of my colleague has deleted those log files.The easy fix told was to reboot both the machine.We tried but no luck.

Upon checking the cssd logs i could see the below error.

ocssd.log:[ CSSD]2010-03-05 17:48:21.908 [84704144] >TRACE: clssnmReadDskHeartbeat: node 3, vm-lnx-rds1173, has a disk HB, but no network HB, DHB has rcfg 0, wrtcnt, 2, LATS 1185024, lastSeqNo 2, timestamp 1267791501/1961474

Which just flashed to my brain to check the cluster interconnect.Yes.The Cluster Interconnect is not pinging between the servers.Informed to Sysadmins and got it fixed.Then it came out quite well.

Simple UTL_FILE Script to create & write in a file

Below PL/SQL block creates a file named 'sample.txt' with 'welcome' word in it under the Oracle Directory Named UTL_DIR [Can be created using CREATE DIRECTORY]

We can use utl_file_dir instead of CREATE DIRECTORY in that case we need to specify the whole path of the UNIX directory instead of the just the Directory name[i.e.., UTL_DIR]

Make sure you have necessary permissions.

Using utl_file_dir is not a recommended one.

Declare
file1 utl_file.file_type;
Begin
file1:= utl_file.fopen('UTL_DIR','sample.txt','w');
utl_file.put_line(file1,'Welcome' );
utl_file.fclose(file1);
end;
/

Parameters to Format the Output from DBA_SOURCE.

Set the Below Variables:

set verify off
set feedback off
set lines 132
set pages 0
set heading off
set space 0
set recsep off
column text format a79
column line noprint

select DECODE(line,1,'create or replace ','')||text, line
from dba_source where owner = upper('&&1')
and type = upper('&&2')
and name = upper('&&3')


REFER MOS NOTE:SCRIPT:REGENERATING PACKAGE and PROCEDURE CODE from DBA_SOURCE 
[ID 1012473.7]

Handy SQLs for DBA's

Find SQL from SID:

SELECT s.sid, s.serial#, st.sql_text sql_text
FROM v$session s, v$sql st, v$process p
WHERE s.sql_hash_value = st.hash_value
AND s.sql_address = st.address
AND s.paddr = p.addr
and ( s.sid='&which_sid' )

To the full SQL Text:

select x.sql_text from v$session s,v$sqltext x
where s.sql_address=x.address and s.sid = &which_sid order by sid, piece asc;


Find the Session details :

SELECT s.saddr, s.sid, s.serial#, s.username,
s.osuser, s.machine, s.program, s.logon_time, s.status,
p.program, p.spid
FROM v$session s, v$process p
WHERE s.paddr = p.addr
AND p.spid IN (&OS_PROCESS_ID);

To set the Time to in a required format:

alter session set nls_date_format='DD:MON:YYYY:HH24:MI:SS';

Then execute the required sql.

To Monitor the Progress of RMAN Backup:

SELECT sid, serial#, context, sofar, totalwork,
round(sofar/totalwork*100,2) "% Complete"
FROM v$session_longops
WHERE opname LIKE 'RMAN:%'
AND opname NOT LIKE 'RMAN: aggregate%';

Good Metalink Notes on Ebiz

1).Note:443699.1 How to check if certain Oracle Applications product/module is implemented?
2).Note:228779.1 How to Merge Patches Using admrgpch
3).Note: 343987.1 How to uninstall backout an Oracle Applications 11i Patch
4).Note:181665.1 Release 11i Adpatch Basics
5).Note:415129.1 How to change IP Address for server with Oracle Applications; Is it supported to use DNS address resolution instead of IP
6).Note:337762.1 How to Remove an Oracle Applications 11i node.
7).Note:400920.1 What Affect Will IP Address Change Have On Oracle Applications?
8).Note:375130.1 How to apply patch without enabling Maintenance Mode
9).Note:Oracle Applications Patching FAQ

How to Configure 32/64 Bit Kernel for Solaris OS

The below link helps you to convert 32 bit OS to 64 Bit and Vise Versa.


http://developers.sun.com/solaris/articles/64_bit_booting.html#Q6

How to Check the installed Oracle Software is 32/64 bit ?

You can check the Oracle Bit by 2 methods

Method 1:
Go to ORACLE_HOME/bin
$ file oracle
oracle: ELF 32-bit MSB executable SPARC Version 1, dynamically linked, not stripped
Method 2:-
Login to sqlplus,
use the following query,
SQL> select metadata from sys.kopm$ ;
-----------------------
METADATA
-----------------------

0000006001240F050B0C030C0C0504050D06090708050F0505050F05050505050A050505050504050607080823472323081123081141B0230083001F07D01300000000000000000000000000000000000000000000000000000000000000000000000000
If the output has 'B023' string then the database is 32 bit else if it is B047 then it is 64 bit

R12 New Features.........

1).Jinitiator is removed.Instead native Java Plugin is used
2).Jserv is replaced by OC4J Container
3).10g Forms and reports are in place
4).Base release of Oracle AS is now 10g
5).XML Publiser is given more importance over Oracle Reports
6).R12 Comes with 10g Database
7).Change in the UI(swan Interface). Looks cool.

Oracle DBA Tool Bar for IE and Firefox

Oracle has come up with toolbar for DBA to search easily from Oracle Websites


Below is the link for to download,

http://www.oracle.com/technology/toolbar/install/index.html?msgid=5191908

It is available for both IE and FireFox.

Donno how useful it is .Will wait and see.

Trace Analyzer : The Good Tool for Performance Monitoring

Just Came across one Good Performance Analysis Tool from Oracle.Itz Trace Analyzer.
Trace Analyzer is an Improved version of Oracle TKPROF.With Proper Layout and Format.
Trace Analyzer Utility download details are given in Metalink Note:224270.1

Download from the link and follow the steps given in the instruction.txt of the trca.zip file

Steps to Trace the Concurrent Request.

1).Enable the trace for the Concurrent Program using the following Navigation

Concurrent->Program->Define->'Program Name'

And check out the Enable Trace option

this will create the *trc file in udump directory

2)Once the Concurrent Request is completed look for the trace file with the name of request id

$ ls *requestid*

3).Login to sqlplus
SQL> conn apps/
SQL>set serverout on
SQL>exec trca$i.trace_analyzer('vis_ora_14328_SYSADMIN.trc');

Once the above procedure is completed it will create the html file the local Directory.

This file can be used for analysis of Performance.

Bullet Points:- Oracle Applications DBA

1). Environment Variable APPLFENV gives the environment variables file(*env file) which is sourced in the node

2).To find the Character Set of the Database

Use,

SQL>select value from nls_database_parameters where parameter = 'NLS_CHARACTERSET'

3)To find the free disk space in ASM

Use,

SQL.select sum(free_mb) from v$asm_disk;

4).To skip the particular job when running a patch/maintenance pack
use the hidden option '8' in 'adctrl' to skip and start the job.


5)To check whether the Applications running on RAC

SQL>select is_rac_db from fnd_databases;Output would be either Y/N

6)To check the location of Voting Disk
Use ,

$crsctl query css votedisk

O/P would be somewhat like,

0 0 /dev/votediskLocated 1 Vote disk

Handy UNIX Commands for Oracle Professionals

Like to share some good and handy UNIX commands which would be really helpful support professionals


1).To find the top 10 larger files in the Directory,

$ls -lrt | awk '{print $5,$9}' | sort -nr head | xargs ls -lrt


2) To find the access and Modification details of a file

$istat

*This command is available for AIX only

3).To remove file which are older then say 7 days

$find . -mtime +7 -exec rm {} \;

4).Locate Files That Contain Certain Strings

$ find . -printxargs grep -i v\$dba_objects

5)To Delete top 10 files in directory which has huge size

use,

$du -sk sort -nr head awk '{print $2}' xargs rm -r

STORAGE:What is SAN and NAS ?

Overview of NAS and SAN

NAS(Network attached storage) :

As the data storage across the LAN grew exponentialy,the concept of File Sharing Server came into existence .This is usually a desktop with Network Operating System(NOS) installed with set of hard drives which can beaccessed by the client through LAN.Example of NOS can be Windows,UNIX,MacOS. With the Traditional File Server,When the server goes down the Storage device attached to that also be impactedand may result in corruption ,also overhead of complete operating system and other accessories are inevitable. NAS has desiged in order to overcome these drawbacks by segregating the server from the storage and only the essential part of the OS for File Sharing is used.

NAS Box has set of RAID arrays which is controlled by a devicecalled the storage is controlled by the embedded OS .NAS doesn't require any monitor,keyboard,mouse as Traditionalfile server.

On the Network,NAS will be assigned a IP address through which Clients access to data from the storage.NAS access the data by taking file name as the reference

NAS typically uses Ethernet as its network protocol for clients to access the storage.It also supports NFS,CIFS(COMMON INTERNET FILE SYSTEM),HTTP,TCP/IP protocols

SAN(Storage Area Network):

SAN is high speed special purpose network that interconnects various kinds of data storage devices associated withthe data servers.A SAN consists of a communication infrastructure, which provides physical connections and amanagement layer, which organizes the connections, storage elements, and computer systems so that data transfer is secure and robust.

SAN are generally used in large scale enterprises Unlike NAS, SAN uses the block level transfer of data between theservers storage.This gives very high I/O rate which quite critical in mission critical applications.

SAN introduces the flexibility of networking to enable one or more heterogeneous servers to share a commonstorage utility, which may comprise many storage devices, including disk, tape, and optical storage. The storage utilitycan be extended even for 10 KM.

SAN uses ethernet,Fibre Channel,SCSI as the physical data link between the server and the storage.Fibre Channelprotocol is the architecture on which SAN is built.