ON CALL DBA SUPPORT

— Database blog

Archive for October, 2013

QUERY TO CHECK THE PID FROM THE SID

Posted by ssgottik on 22/10/2013

select sid,serial#,inst_id from gv$session;

select PID, p.PROGRAM
from gv$process p, gv$session s
where s.paddr=p.addr
and sid=<SID above>;

Advertisements

Posted in SCRIPTS | Leave a Comment »

ASMLIB INSTALLATION AND CREATING ASM DISKS USING ASMLIB

Posted by ssgottik on 20/10/2013

 REFERANCE : Doc ID 580153.1

There are two different methods to configure ASM on Linux:

ASM with ASMLib I/O: This method creates all Oracle database files on raw block devices managed by ASM using ASMLib calls. RAW devices are not required with this method as ASMLib works with block devices.
ASM with Standard Linux I/O: This method creates all Oracle database files on raw character devices managed by ASM using standard Linux I/O system calls. You will be required to create RAW devices for all disk partitions used by ASM.

You can download the ASMLIB rpm’s from below URL:

http://www.oracle.com/technetwork/server-storage/linux/downloads/index-088143.html

http://www.oracle.com/technetwork/server-storage/linux/downloads/rhel5-084877.html

STEP 01: LOG IN AS ROOT USER AND INSTALL THE RPMS

[root@node1 ASMLIB]# rpm -Uvh oracleasm-2.6.18-164.el5-2.0.5-1.el5.i686.rpm \

> oracleasmlib-2.0.4-1.el5.i386.rpm \
> oracleasm-support-2.1.8-1.el5.i386.rpm
warning: oracleasm-2.6.18-164.el5-2.0.5-1.el5.i686.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159
Preparing… ########################################### [100%]
1:oracleasm-support ########################################### [ 33%]
2:oracleasm-2.6.18-164.el########################################### [ 67%]
3:oracleasmlib ########################################### [100%]

STEP 02: CONFIGURE ASMLIB


[root@node1 ASMLIB]# /etc/init.d/oracleasm configure
Configuring the Oracle ASM library driver.

This will configure the on-boot properties of the Oracle ASM library
driver. The following questions will determine whether the driver is
loaded on boot and what permissions it will have. The current values
will be shown in brackets (‘[]’). Hitting <ENTER> without typing an
answer will keep that current value. Ctrl-C will abort.

Default user to own the driver interface []: oracle
Default group to own the driver interface []: dba
Start Oracle ASM library driver on boot (y/n) [n]: y
Scan for Oracle ASM disks on boot (y/n) [y]: y
Writing Oracle ASM library driver configuration: done
Initializing the Oracle ASMLib driver: [ OK ]
Scanning the system for Oracle ASMLib disks: [ OK ]

STEP 03 :CREATE ASM DISK 

[root@node1 ASMLIB]# /etc/init.d/oracleasm listdisks
[root@node1 ASMLIB]#
[root@node1 ~]# /etc/init.d/oracleasm createdisk VOL1 /dev/sdb1
Marking disk “VOL1” as an ASM disk: [ OK ]
[root@node1 ~]# /etc/init.d/oracleasm createdisk VOL2 /dev/sdc1
Marking disk “VOL2” as an ASM disk: [ OK ]
[root@node1 ~]# /etc/init.d/oracleasm createdisk VOL3 /dev/sdd1
Marking disk “VOL3” as an ASM disk: [ OK ]
[root@node1 ~]# /etc/init.d/oracleasm createdisk VOL4 /dev/sde1
Marking disk “VOL4” as an ASM disk: [ OK ]
[root@node1 ~]# /etc/init.d/oracleasm createdisk VOL5 /dev/sdf1
Marking disk “VOL5” as an ASM disk: [ OK ]
[root@node1 ~]# /etc/init.d/oracleasm listdisks
VOL1
VOL2
VOL3
VOL4
VOL5
[root@node1 ~]#

Posted in ASM | Leave a Comment »

SQL TRACE FOR STREAMS PROCESSES

Posted by ssgottik on 11/10/2013

STEP 01.GET THE SID OF THE PROCESSES RUNNING IN THE DATABASE USING ONE OF THE BELOW( As per your requirements):

— For the capture process:
SQL >  select SID, CAPTURE_NAME from v$streams_capture;
— For the propagation:
SQL > select QNAME, DESTINATION, PROCESS_NAME, SESSION_ID from dba_queue_schedules;
— For the apply process:
SQL > select SID, APPLY_NAME from v$streams_apply_server;

STEP 02. GET THE PID FROM THE SID :

SQL > select PID, p.PROGRAM from v$process p, v$session s where s.paddr=p.addr and sid=<SID obtained from queries above>;

STEP 03. BEGIN TARACING USING BELOW COMMANDS:

SQL > oradebug setorapid <PID from previous step>
Oracle pid: 259, Unix process pid: 99878, image: oracle@vmlinux1 (AS0B)
SQL > oradebug unlimit
SQL > oradebug Event 10046 trace name context forever, level 12

A trace file with the above PID will be genetrace . In my case the trace file looks like orcl_as01_99878.trc

STEP 04. STOP TRACING USING BELOW COMMAND:

SQL > oradebug Event 10046 trace name context off

Posted in Oracle STREAMS | Leave a Comment »