Oracle Database Administration

SQL to find RMAN Backup Duration

2012-01-23T08:49:00.002-05:00

select TO_CHAR(start_time,'yyyy-mm-dd hh24:mi:ss') Start_Time,
TO_CHAR(end_time,'yyyy-mm-dd hh24:mi:ss') End_Time , INPUT_TYPE, round(ELAPSED_SECONDS/60) MINUTES
from v$rman_backup_job_details order by Start_Time asc
/

START_TIME END_TIME INPUT_TYPE MINUTES
------------------------------ ------------------------------ ------------- ----------
2012-01-02 01:00:24 2012-01-02 01:43:34 DB INCR 43
2012-01-02 18:01:07 2012-01-02 19:10:20 ARCHIVELOG 69
2012-01-06 14:52:48 2012-01-06 20:11:54 DB FULL 319

ASM DG to Physical Disk Mapping

2011-09-29T12:59:00.001-04:00

#!/bin/ksh
for i in `/etc/init.d/oracleasm listdisks`
do
v_asmdisk=`/etc/init.d/oracleasm querydisk -d $i | awk '{print $2}'`
v_minor=`/etc/init.d/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk '{print $1}'`
v_major=`/etc/init.d/oracleasm querydisk -d $i | awk -F[ '{print $2}'| awk -F] '{print $1}' | awk '{print $2}'`
v_device=`ls -la /dev | grep $v_minor | grep $v_major | awk '{print $10}'`
echo "ASM disk $v_asmdisk based on /dev/$v_device [$v_minor $v_major]"
done

CRS Diagnostic Data Gathering

2011-09-21T16:35:00.000-04:00

CRS Diagnostic Data Gathering

For 10gR2
=========
Ensure that the environment variable ORA_CRS_HOME is set to the CRS home
Ensure that the environment variable ORACLE_BASE is set
Ensure that the environment variable HOSTNAME is set to the name of the host.
$./diagcollection.pl -collect

For 11gR1
=========
Execute diagcollection.pl by passing the crs_home as the following
export ORA_CRS_HOME=/u01/crs
$ORA_CRS_HOME/bin/diagcollection.pl -crshome=$ORA_CRS_HOME --collect

For 11gR2
=========
Execute /bin/diagcollection.sh

NOTE: --nocore This option significantly reduces the size of the final file by excluding the core files

OS Watcher (OSW)
================
For platforms where Cluster Health Monitor is not available, OS Watcher can collect OS performance statistics.
The OS Watcher guide for Windows is found in Oracle Metalink Document 433472.1 - OS Watcher For Windows (OSWFW) User Guide. However, CHM for Windows is far superior to OS Watcher for Windows and should be used wherever possible.

For all other platforms, the OS Watcher user guide can be found in Document 301137.1

The OS Watcher output or the compressed output can be manually collected from the osw installation directories. Browsing the OSW output will show the server performance profile.
If OS Watcher is not running, then you can start the data collection manually from the osw installation directory:

nohup ./startOSW.sh &

OS Watcher should be in init.d to ensure that it starts automatically at server start.
The script tarupfiles.sh should be run regularly to compress the OS watcher data collection output. This should be configured in crontab.

Find out about dropped network packets

2011-09-21T13:38:00.002-04:00

$ netstat -s
OR
$ ifconfig -a
the above gives information about "dropped network packets"

List files for a particular date

2011-07-29T12:03:00.001-04:00

ls -latR | grep 'Jul 23'

Perl script to run any UNIX/LINUX command and email the output

2011-07-27T09:38:00.002-04:00

The following script would run command "lsof -u oracle | wc -l" and then check for
the threshold value and if the threshold is exceeded, it will email the output.

#!/usr/bin/perl -w
use POSIX 'strftime';
my $date = strftime '%m-%d-%Y %H:%M:%S', localtime;
my $command = `/usr/sbin/lsof -u oracle | wc -l `;
my $host = `hostname`; chomp($host);
my $to = "abc\@yahoo.com";
my $title = "LSOF Threshold Exceeded" ;
my $from = "DBA\@yahoo.com";
my $subject = "Threshold lsof exceeded";
my $thresh = 10;

if( $command ge $thresh ) {
open(MAIL, "|/usr/sbin/sendmail -t ");

print MAIL "To: $to\n";
print MAIL "From: $from\n";
print MAIL "Subject: $title for host : $host\n";

print MAIL "$date\n HOSTNAME: $host\n LSOF Count: $command\n\n";
print MAIL "LSOF Count has Exceeded the threshold of $thresh";

close(MAIL);

}

Remove Job from another user : DBMS_IJOB.REMOVE

2011-06-15T08:39:00.000-04:00

SQL> exec dbms_job.remove(40682);
BEGIN dbms_job.remove(40682); END;

*
ERROR at line 1:
ORA-23421: job number 40682 is not a job in the job queue
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_IJOB", line 687
ORA-06512: at "SYS.DBMS_JOB", line 174
ORA-06512: at line 1

SQL> EXECUTE SYS.DBMS_IJOB.REMOVE (40682);

PL/SQL procedure successfully completed.

SQL>

Find session activity

2011-06-03T15:49:00.001-04:00

select event,'/usr/ucb/ps -aux | grep'||spid,pga_used_mem,sid,a.serial#,b.inst_id,logon_time,a.username,module,last_call_et/60,subst
r(machine,1,20),process,sql_id
from gv$session a,gv$process b where addr=paddr
and status='ACTIVE'
and a.username is not null
and a.username = 'GCP_USER'
and a.inst_id=b.inst_id
and last_call_et/60 > 1
order by b.inst_id
/

Who's using the UNDO segments

2011-06-03T07:27:00.001-04:00

SELECT TO_CHAR (s.SID) || ',' || TO_CHAR (s.serial#) sid_serial,
NVL (s.username, 'None') orauser, s.program, r.NAME undoseg,
t.used_ublk * TO_NUMBER (x.VALUE) / 1024 || 'K' "Undo"
FROM SYS.v_$rollname r,
SYS.v_$session s,
SYS.v_$transaction t,
SYS.v_$parameter x
WHERE s.taddr = t.addr
AND r.usn = t.xidusn(+)
AND x.NAME = 'db_block_size'
/

Find out who's locking the accounts

2011-05-18T14:40:00.001-04:00

set lines 200
set pages 200

column USERNAME format a12
column OS_USERNAME format a12
column USERHOST format a25
column EXTENDED_TIMESTAMP format a40

SELECT USERNAME, OS_USERNAME, USERHOST, EXTENDED_TIMESTAMP
FROM SYS.DBA_AUDIT_SESSION WHERE returncode != 0 and username = '&Account_Locked'
and EXTENDED_TIMESTAMP > (systimestamp-1) order by 4 desc
/

Query to find HISTOGRAMS

2011-05-11T14:59:00.000-04:00

select owner,table_name,histogram from DBA_TAB_COL_STATISTICS where
owner='SCOTT' and table_name='EMPLOYEE'

Default STATS Collection in 11g

2011-05-09T16:04:00.002-04:00

- The GATHER_STATS_JOB Oracle’s default stats collection job does not exist in
11g (the name does not exist) as it was there in 10g. Instead it has been
included in Automatic Maintenance Tasks

- How to check, Oracle’s default stats collection job is enable or disabled

SQL> select CLIENT_NAME,status from DBA_AUTOTASK_CLIENT;

CLIENT_NAME STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection DISABLED
auto space advisor ENABLED
sql tuning advisor ENABLED

- How to disable if it is enabled (run below query to disable it). Below PL/SQL block has to be executed by SYS

BEGIN
DBMS_AUTO_TASK_ADMIN.DISABLE(
client_name => 'auto optimizer stats collection',
operation => NULL,
window_name => NULL);
END;

EXPDP - EXCLUDE Multiple TABLES and SCHEMAS

2011-04-24T13:00:00.001-04:00

The below example gives syntax to EXCLUDE multiple tables and multiple schemas while doing a full database export using expdp

=== BEGIN expdp_exclude.par

DIRECTORY=DATA_PUMP_DIR
DUMPFILE=abc.dmp
LOGFILE=abc.log
FULL=Y
EXCLUDE=STATISTICS
EXCLUDE=TABLE:"IN ('NAME', 'ADDRESS' , 'EMPLOYEE' , 'DEPT')"
EXCLUDE=SCHEMA:"IN ('WMSYS', 'OUTLN')"

=== END expdp_exclude.par

In the above example parameter file; tables NAME and ADDRESS are owned by SCOTT and tables EMPLOYEE and DEPT are owned by HR
EXCLUDE=TABLE => You do not have to prefix the OWNER name, in fact, if you put the OWNER.TABLE_NAME, it would not work.
It will EXCLUDE all TABLES having the name mentioned in the list, even if more than one owner has the same object name.
For example: If ADDRESS table is owned by user SCOTT and user HR, that table will be EXCLUDED from both the users.

The above commands would work only via parameter file and would not work on the command line.

COMMAND LINE SYNTAX for EXPDP

expdp system/password DIRECTORY=DATA_PUMP_DIR DUMPFILE=abc.dmp FULL=Y
EXCLUDE=TABLE:\"IN $\'NAME\', \'ADDRESS\' , \'EMPLOYEE\' , \'DEPT\'$\"
EXCLUDE=SCHEMA:\"IN $\'WMSYS\', \'OUTLN\'$\"

Find Current CPU or PSU Applied

2011-03-28T13:49:00.005-04:00

mylx1:product/11.1.0/OPatch/ $ ./opatch lsinv -bugs_fixed | grep -i 'database psu'
8833297 9352179 Mon Sep 13 22:00:34 EDT 2010 DATABASE PSU 11.1.0.7.1 (INCLUDES CPUOCT2009)
9209238 9352179 Mon Sep 13 22:00:34 EDT 2010 DATABASE PSU 11.1.0.7.2 (INCLUDES CPUJAN2010)
9352179 9352179 Mon Sep 13 22:00:34 EDT 2010 DATABASE PSU 11.1.0.7.3 (INCLUDES CPUAPR2010)
mylx1:product/11.1.0/OPatch/ $

REM This script outputs the current CPU applied on the database.column action format a15
column action_time format a30
column comments format a35
column action format a20
set linesize 300

select comments,action_time,action
from
(select action,action_time,comments
from sys.registry$history
where action in ('CPU','APPLY')
order by action_time desc)
where comments <> 'view recompilation'
and rownum < 2
/
-- Output from above script --
COMMENTS ACTION_TIME ACTION
----------------------------------- ------------------------------ --------------------
PSU 11.1.0.7.3 09-AUG-10 09.14.20.562314 AM APPLY

Expdp Options

2011-02-23T08:33:00.002-05:00

expdp system/******** schemas=SCOTT directory=SCOTT_DUMP dumpfile=scott.dmp logfile=scott.log EXCLUDE=TABLE:\"LIKE \'EMP%\'\", TABLE:\"LIKE \'%ABC%\'\"

if you just type EXCLUDE=TABLE:"LIKE 'EMP%'", TABLE:"LIKE '%ABC%'":

you will get the following error.

ORA-39001: invalid argument value
ORA-39071: Value for EXCLUDE is badly formed.
ORA-00911: invalid character

you need to include escape characters in the statement, e.g.:
EXCLUDE=TABLE:\"LIKE \'EMP%\'\", TABLE:\"LIKE \'%ABC%\'\" ,
this would exclude tables starting with EMP and any tables having ABC in their table name.

Using the NOT IN OPERATOR
EXCLUDE=TABLE:\"NOT IN $\'ABC\',\'XYZ\'$\"

Using the IN OPERATOR
EXCLUDE=TABLE:\"IN $\'ABC\',\'XYZ\'$\"

Find Unindexes FK Constraints

2010-11-22T10:12:00.002-05:00

col table_name format a32
col columns format a40
set lines 140
set pages 200
select table_name, constraint_name,
cname1 || nvl2(cname2,','||cname2,null) ||
nvl2(cname3,','||cname3,null) || nvl2(cname4,','||cname4,null) ||
nvl2(cname5,','||cname5,null) || nvl2(cname6,','||cname6,null) ||
nvl2(cname7,','||cname7,null) || nvl2(cname8,','||cname8,null)
columns
from ( select b.table_name,
b.constraint_name,
max(decode( position, 1, column_name, null )) cname1,
max(decode( position, 2, column_name, null )) cname2,
max(decode( position, 3, column_name, null )) cname3,
max(decode( position, 4, column_name, null )) cname4,
max(decode( position, 5, column_name, null )) cname5,
max(decode( position, 6, column_name, null )) cname6,
max(decode( position, 7, column_name, null )) cname7,
max(decode( position, 8, column_name, null )) cname8,
count(*) col_cnt
from (select substr(table_name,1,30) table_name,
substr(constraint_name,1,30) constraint_name,
substr(column_name,1,30) column_name,
position
from user_cons_columns ) a,
user_constraints b
where a.constraint_name = b.constraint_name
and b.constraint_type = 'R'
group by b.table_name, b.constraint_name
) cons
where col_cnt > ALL
( select count(*)
from user_ind_columns i
where i.table_name = cons.table_name
and i.column_name in (cname1, cname2, cname3, cname4,
cname5, cname6, cname7, cname8 )
and i.column_position <= cons.col_cnt
group by i.index_name
)
order by table_name
/
(Credit goes to the original author, found it somewhere on internet)

FTS with Table Name

2010-11-05T12:12:00.001-04:00

select distinct a.sql_id,b.object_name
--dbms_lob.substr(a.sql_text)
from dba_hist_sqltext a,
(select SQL_ID,object_name from dba_hist_sql_plan where object_owner='SCOTT'and OPERATION = 'TABLE ACCESS' and OPTIONS =
'FULL') b
where a.sql_id = b.sql_id
order by 1
/

Find SQLs doing Full Table Scans

2010-11-03T14:29:00.000-04:00

select sql_id,sql_text from dba_hist_sqltext
where sql_id in (select distinct SQL_ID from dba_hist_sql_plan where object_owner='SCOTT'
and OPERATION = 'TABLE ACCESS' and OPTIONS = 'FULL')
/

crsctl.bin: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory

2010-10-11T10:06:00.000-04:00

After upgrading the CRS to 11g (11.1.0.7) and at the time of running the root111.sh (at 11.1.0.7), got the below error

/usr/local/opt/oracrs/bin/crsctl.bin: error while loading shared libraries: libclntsh.so.11.1: cannot open shared object file: No such file or directory

And found the workaround in the below note.

After Installing Patchset Crsctl Fails To Load Libclntsh.so [ID 333233.1]

Workaround was to manually change the permission of libclntsh.so.11.1 and After applying the workaround all services in the cluster were ONLINE.

Oracle RAC Commands

2010-10-04T11:24:00.000-04:00

To shutdown RDBMS on all nodes run the following command:

$ORACLE_HOME/bin/srvctl stop database -d dbname

To shutdown RDBMS instance on the local node run the following command:

$ORACLE_HOME/bin/srvctl stop instance -d dbname -i instance_name

To shutdown ASM instances run the following command on each node:

$ORACLE_HOME/bin/srvctl stop asm -n ;

To shutdown listeners run the following command on each node:

$ORACLE_HOME/bin/srvctl stop listener -n ;

To shutdown nodeapps run the following comand on each node:

$ORA_CRS_HOME/bin/srvctl stop nodeapps -n ;

To shutdown CRS daemons on each node by running as root:

# crsctl stop crs

How to suppress Oracle Banner

2010-09-27T13:16:00.002-04:00

Disabling "Banner" assumes significance in case of Oracle RAC Install. The result of not temporarily removing the banner is that the dba will see errors that say, "User equivalence failed for user oracle".

• Log in (or sudo to) user oracle
• cd ~/.ssh
• Modify (or create) a file named “config” in this directory, to add the following line (case-sensitive, left-justified):

LogLevel QUIET

• Save and close the file.
• Test to ensure that oracle can ssh to all other RAC nodes in the cluster, without being presented with a banner.

What is displayed as Banner is stored under /usr/localcw/opt/tcpwrapper/banners/

How to start Oracle runInstaller in TRACING mode

2010-09-27T10:12:00.001-04:00

Launch the installer with tracing turned on

./runInstaller -J-DTRACING.ENABLED=true -J-DTRACING.LEVEL=2

How to Check OCR and Voting Disk

2010-09-24T15:12:00.003-04:00

How to find out which raw devices are used for OCR and which ones are used for Voting Disk.

mylxd1->ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 487980
Used space (kbytes) : 3884
Available space (kbytes) : 484096
ID : 2006423852
Device/File Name : /dev/raw/raw1
Device/File integrity check succeeded
Device/File Name : /dev/raw/raw2
Device/File integrity check succeeded

Cluster registry integrity check succeeded

Logical corruption check succeeded

mylxd1->crsctl query css votedisk
0. 0 /dev/raw/raw3
1. 0 /dev/raw/raw4
2. 0 /dev/raw/raw5
Located 3 voting disk(s).

Database restart on HOST reboot

2010-08-25T15:25:00.002-04:00

Create a script to stop/start the database

Execute these as ROOT.

cp {script to stop/start the database to} /etc/init.d/oracle
chmod 755 /etc/init.d/oracle
ln –s /etc/init.d/oracle /etc/rc0.d/K05oracle
ln –s /etc/init.d/oracle /etc/rc3.d/S90oracle

Delete archivelogs using RMAN until date

2010-08-25T14:33:00.000-04:00

RMAN> run
{
DELETE archivelog until time "to_date('2010-08-23:10:00:00','YYYY-MM-DD:hh24:mi:ss')";
}

ORA-27054: NFS file system where the file is created or resides is not mounted with correct options

2010-08-24T11:27:00.001-04:00

lxtestbox:/exp/expdp/ $ impdp system/password parfile=imp_from_test.par

Import: Release 10.2.0.4.0 - 64bit Production on Tuesday, 24 August, 2010 9:43:41

Copyright (c) 2003, 2007, Oracle. All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Data Mining and Real Application Testing options
ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-31640: unable to open dump file "/exp/expdp/test01.dmp" for read
ORA-27054: NFS file system where the file is created or resides is not mounted with correct options
Additional information: 3

Solution

Mount the file system with the following option

rw,noac,bg,intr,hard,timeo=600,wsize=32768,rsize=32768,nfsvers=3,tcp

RMAN Restore

2010-08-23T19:36:00.001-04:00

run {
allocate channel t1 type disk ;
allocate channel t2 type disk ;
allocate channel t3 type disk ;
allocate channel t4 type disk ;
set until time "to_date('2010-08-23 08:15:00','YYYY-MM-DD HH24:MI:SS')" ;
restore database ;
recover database ;
sql 'alter database open resetlogs' ;
release channel t1;
release channel t2;
release channel t3;
release channel t4;
}

Script to find foreign key constraints

2010-05-14T13:50:00.001-04:00

Script to find foreign key constraints
select owner,constraint_name,constraint_type,table_name,r_owner,r_constraint_name
from all_constraints
where constraint_type='R'
and r_constraint_name in (select constraint_name from all_constraints
where constraint_type in ('P','U') and table_name='&TABLE_NAME')
/

Find Oracle Database Character Set

2010-05-14T11:59:00.001-04:00

Character Sets
(Ordinary) character set
The (ordinary) character set for a database can be determined with:

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

National character set
The national character set for a database can be determined with:

SQL> select value from nls_database_parameters
where parameter = 'NLS_NCHAR_CHARACTERSET';

How to find number of sessions per hour for EACH INSTANCE in a RAC

2010-04-26T10:27:00.000-04:00

SELECT
to_char(TRUNC(s.begin_interval_time,'HH24'),'DD-MON-YYYY HH24:MI:SS') snap_begin,
r.instance_number instance,
r.current_utilization sessions
FROM
dba_hist_resource_limit r,
dba_hist_snapshot s
WHERE ( TRUNC(s.begin_interval_time,'HH24'),s.snap_id ) IN
(
--Select the Maximum of the Snapshot IDs within an hour if all of the snapshot IDs
--have the same number of sessions
SELECT TRUNC(sn.begin_interval_time,'HH24'),MAX(rl.snap_id)
FROM dba_hist_resource_limit rl,dba_hist_snapshot sn
WHERE TRUNC(sn.begin_interval_time) >= TRUNC(sysdate-1)
AND rl.snap_id = sn.snap_id
AND rl.resource_name = 'sessions'
AND rl.instance_number = sn.instance_number
AND ( TRUNC(sn.begin_interval_time,'HH24'),rl.CURRENT_UTILIZATION ) IN
(
--Select the Maximum no.of sessions for a given begin interval time
SELECT TRUNC(s.begin_interval_time,'HH24'),MAX(r.CURRENT_UTILIZATION) "no_of_sess"
FROM dba_hist_resource_limit r,dba_hist_snapshot s
WHERE r.snap_id = s.snap_id
AND TRUNC(s.begin_interval_time) >= TRUNC(sysdate-1)
AND r.instance_number=s.instance_number
AND r.resource_name = 'sessions'
GROUP BY TRUNC(s.begin_interval_time,'HH24')
)
GROUP BY TRUNC(sn.begin_interval_time,'HH24'),CURRENT_UTILIZATION
)
AND r.snap_id = s.snap_id
AND r.instance_number = s.instance_number
AND r.resource_name = 'sessions'
ORDER BY snap_begin,instance

How to find number of sessions per hour

2010-04-26T10:26:00.000-04:00

SELECT
to_char(TRUNC(s.begin_interval_time,'HH24'),'DD-MON-YYYY HH24:MI:SS') snap_begin,
sum(r.current_utilization) sessions
FROM
dba_hist_resource_limit r,
dba_hist_snapshot s
WHERE ( TRUNC(s.begin_interval_time,'HH24'),s.snap_id ) IN
(
--Select the Maximum of the Snapshot IDs within an hour if more than one snapshot IDs
--have the same number of sessions within that hour , so then picking one of the snapIds
SELECT TRUNC(sn.begin_interval_time,'HH24'),MAX(rl.snap_id)
FROM dba_hist_resource_limit rl,dba_hist_snapshot sn
WHERE TRUNC(sn.begin_interval_time) >= TRUNC(sysdate-1)
AND rl.snap_id = sn.snap_id
AND rl.resource_name = 'sessions'
AND rl.instance_number = sn.instance_number
AND ( TRUNC(sn.begin_interval_time,'HH24'),rl.CURRENT_UTILIZATION ) IN
(
--Select the Maximum no.of sessions for a given begin interval time
-- All the snapshots within a given hour will have the same begin interval time when TRUNC is used
-- for HH24 and we are selecting the Maximum sessions for a given one hour
SELECT TRUNC(s.begin_interval_time,'HH24'),MAX(r.CURRENT_UTILIZATION) "no_of_sess"
FROM dba_hist_resource_limit r,dba_hist_snapshot s
WHERE r.snap_id = s.snap_id
AND TRUNC(s.begin_interval_time) >= TRUNC(sysdate-1)
AND r.instance_number=s.instance_number
AND r.resource_name = 'sessions'
GROUP BY TRUNC(s.begin_interval_time,'HH24')
)
GROUP BY TRUNC(sn.begin_interval_time,'HH24'),CURRENT_UTILIZATION
)
AND r.snap_id = s.snap_id
AND r.instance_number = s.instance_number
AND r.resource_name = 'sessions'
GROUP BY
to_char(TRUNC(s.begin_interval_time,'HH24'),'DD-MON-YYYY HH24:MI:SS')
ORDER BY snap_begin

Linux GUI

2010-03-25T08:23:00.001-04:00

How to find system resource utilization in Linux

$ export DISPLAY=90.30.212.197:0.0
$ gnome-system-monitor

Kernel Parameters for RedHat Linux

2010-01-06T10:58:00.004-05:00

$ ipcs -l

------ Shared Memory Limits --------
max number of segments = 4096 // SHMMNI
max seg size (kbytes) = 66046570 // SHMMAX
max total shared memory (kbytes) = 66046568 // SHMALL
min seg size (bytes) = 1

------ Semaphore Limits --------
max number of arrays = 128 // SEMMNI
max semaphores per array = 250 // SEMMSL
max semaphores system wide = 32000 // SEMMNS
max ops per semop call = 100 // SEMOPM
semaphore max value = 32767

------ Messages: Limits --------
max queues system wide = 16 // MSGMNI
max size of message (bytes) = 65536 // MSGMAX
default max size of queue (bytes) = 65536 // MSGMNB

>> Set shmmax to 0.5 * Total Memory (free -b)

>> SHMMAX is the maximum size of a shared memory segment on a Linux system
whereas SHMALL is the maximum allocation of shared memory pages on a system.

>> SHMALL is set to 8 GB by default (8388608 KB = 8 GB). If you have more physical memory than this,
and it is to be used for oracle database, then this parameter should be increased to approximately
80% of the physical memory. For instance, if you have a server with 16 GB of memory to be used primarily
for oracle, then 80% of 16 GB is 12.8 GB divided by 4 KB (the base page size). The ipcs output has converted
SHMALL into kilobytes. The kernel requires this value as a number of pages.

>> The next section "Semaphore Limits" covers the amount of semaphores available to the operating system.
The kernel parameter semaphore consists of 4 tokens, SEMMSL, SEMMNS, SEMOPM and SEMMNI.
SEMMNS is the result of SEMMSL multiplied by SEMMNI.
The database manager requires that the number of arrays (SEMMNI) be increased as necessary.
Typically, SEMMNI should be twice the maximum number of connections allowed (MAXAGENTS) multiplied by the
number of logical partitions on the database server plus the number of local application connections
on the database server.

>> Section "Messages: Limits" covers messages on the system.

MSGMNI affects the number of agents that can be started, MSGMAX affects the size of the message that can be
sent in a queue, and MSGMNB affects the size of the queue.

To modify these kernel parameters, we need to edit the /etc/sysctl.conf file.
for example:
kernel.msgmnb = 65536
kernel.msgmax = 65536
kernel.shmmax = 67631687680
kernel.sem=250 32000 100 128
kernel.shmmni=4096
kernel.shmall=16511642

Name Description
------ --------------------------------------------------------
SHMMAX Maximum size of shared memory segment (bytes)
SHMMIN Minimum size of shared memory segment (bytes)
SHMALL Total amount of shared memory available (bytes or pages)
SHMSEG Maximum number of shared memory segments per process
SHMMNI Maximum number of shared memory segments system-wide
SEMMNI Maximum number of semaphore identifiers (that is, sets)
SEMMNS Maximum number of semaphores system-wide
SEMMSL Maximum number of semaphores per set
SEMMAP Number of entries in semaphore map
SEMVMX Maximum value of semaphore

(APEX) & the Embedded PL/SQL Gateway (EPG) in an 11G

2009-11-05T15:02:00.002-05:00

After installing Oracle 11g, run the following to configure APEX
Run apxconf.sql from $ORACLE_HOME/apex
When prompted, enter the port for the Oracle XML DB HTTP server. The default port number is 8080.
Unlock the anonymous user
SQL> ALTER USER ANONYMOUS ACCOUNT UNLOCK;

You should be able to log into apex as the admin user from a browser using -> http://machine.domain:port/apex

The machine is the DB host and the port is the one input during configure step.

If you get an error and can't log in, verify the EPG is up by running the following in your browser ->

http://machine.domain:port

If it's up, you should be prompted for a username and password for XDB.

If the EPG is not up, accomplish the following to start it:

1. Log in as SYS as SYSDBA
2. Run the following statement:
3. EXEC DBMS_XDB.SETHTTPPORT(port); ==>> Where port is the plsql gatway port.
4. COMMIT;

For example:
EXEC DBMS_XDB.SETHTTPPORT(8080);
COMMIT;

How to find size of LOB

2009-11-02T08:45:00.001-05:00

Select b.table_name,b.Column_name,c.data_type,a.Segment_name,a."size"
from
(Select Segment_name , (bytes/(1024*1024*1024)) "size"
from User_Segments
where (bytes/(1024*1024*1024))>0.5 )a,
(Select Table_name,Column_name,Segment_name
from User_Lobs)b,
(Select table_name,Column_Name,Data_type from User_Tab_Columns
Where Data_Type in ('CLOB','BLOB','LONG','LONG RAW') ) c
Where a.segment_name=b.segment_name
and b.table_name=c.table_name
and b.column_name=c.column_name
Order by c.data_type
/

RMAN Backup on the Standby Database

2009-10-26T11:03:00.001-04:00

Running RMAN Backup on the Standby Database

We can put the standby database in good use by running the RMAN backups there along with all the good reasons we have the standby database in place.

. If your Standby database is a Physical Standby database and you are taking backups ONLY on the physical standby database.

. The data file directories on the primary and standby database are identical.

. RMAN recovery catalog is required. Since the standby database has the same DBID as the primary database and is always from the same incarnation, the RMAN datafile backups are interchangeable.

. RMAN will connect to the standby database as target database. The backups taken can be used to restore the Primary Database.

. Primary database should not use Oracle Managed Files (OMF) for this to work. If we are using OMF then the file names of Primary and Standby could differ.

Configuration required on Primary and Standby Database.

. Configure Flash Recovery Area
. Use of SPFILE

Split the file in two

2009-10-23T21:33:00.001-04:00

I have a file with 10 lines and want to split the file in two but with even rows in one file and odd rows in one file.

sed -n '2,${p;n;}' stat1.sql > even.sql
sed -n '1,${p;n;}' stat1.sql > odd.sql

Update table and commit every n rows

2009-10-06T13:14:00.001-04:00

Declare

i integer;
x NUMBER ;
v_min NUMBER ;
v_max NUMBER ;

begin

select max(EMPID) into x from EMPLOYEE ;

v_min :=0 ;
v_max :=25000 ;

loop
update EMPLOYEE set CIO_NAME = 'JOHN' where EMPID >= v_min and EMPID < v_max ;
commit ;
v_min := v_min+25000 ;
v_max := v_min+25000 ;
if v_max > (x+30000) then
commit ;
dbms_output.put_line('All rows updated successfully ....') ;
exit ;
end if ;
end loop ;

Exception When others then
dbms_output.put_line('Error Occured ...') ;

end ;
/

Sequence cache misses were consuming significant database time

2009-10-05T15:56:00.002-04:00

Many times looking at the AWR Report, you come across "Sequence cache misses were consuming significant database time" when there is a slow performance on inserts.

Try increasing the cache size of the Sequence and use noorder if you are running a RAC database. Increasing the cache size would help improve the performance of inserts.

More details to follow on this topic ......

Flashback Table to a time in the past

2009-09-28T16:59:00.001-04:00

Scenario: Someone deleted some data accidently from a database accidently and now wants to get back the data erroneously deleted.

Solution: You can do flashback table to a particular point in time.
(Flashback Table uses undo segments to retrieve data, so all depends if the data is still there)

Login as schema owner and enable the row movement.

SQL> alter table EMPLOYEE enable row movement;

Get time stamp to which you want to go back and then

SQL> flashback table EMPLOYEE to timestamp to_timestamp('Jan 15 2009 10:00:00','Mon DD YYYY HH24:MI:SS');

Find all files having the string in Linux

2009-09-28T16:36:00.000-04:00

To Find all files having the string "SPECIALMAIL"

find . -exec grep -i -l "SPECIALMAIL" {} \;

-i => Ignore Case

-l => List file names only

DataPump Command EXCLUDE/INCLUDE/REMAP_SCHEMA

2009-09-28T16:30:00.004-04:00

Export the schema but leave two of the big tables out.

expdp scott/tiger DIRECTORY=DATA_PUMP dumpfile=scott%u.dmp filesize=5G JOB_NAME=SCOTT_J1 SCHEMAS=SCOTT EXCLUDE=TABLE:\"IN $\'EMPLOYEE\', \'DEPT\'$\"

You exported from SCOTT schema and now wanted to import some tables into a different schema (SMITH) and into different tablespaces

impdp SMITH/PASSWORD directory=data_pump dumpfile=scott%u.dmp REMAP_SCHEMA=SCOTT:SMITH REMAP_TABLESPACE=SCOTT_DATA:SMITH_DATA REMAP_TABLESPACE=SCOTT_IDX:SMITH_IDX TABLES=TABLE1, TABLE2, TABLE3

'gcs log flush sync' resolution

2009-09-28T16:27:00.001-04:00

1)You fired an update statement on Instance-2.
2)However, the request for desired blocks was gone to Instance-1. So Instance-2 was waiting on 'gc cr request'.
3)Instance-1 had the requested blocks but before it ships the blocks to Instance-2, it need to flush the changes from current block to redo logs on disks. Until this is done Instance-2 waits on event - 'gcs log flush sync'.

The cause of this wait event 'gcs log flush sync' is mainly - Redo log IO performance.

To avoid this problem you need to =
1)Improve the Redo log I/o performance.
2) Set undersore parameter "_cr_server_log_flush" =false.

Performance - Isolating Waits in a RAC environment

2009-09-28T16:24:00.004-04:00

Performance - Isolating Waits in a RAC environment.

Determine the snap IDs you are interested in

For example, to obtain a list of snap IDs from the previous day, execute the following SQL:

SQL> SELECT snap_id, begin_interval_time FROM dba_hist_snapshot WHERE TRUNC(begin_interval_time) = TRUNC(sysdate-1) ;

Step 1 :
--------
Identify the Wait Class

select wait_class_id, wait_class, count(*) cnt
from dba_hist_active_sess_history
where snap_id between &1 and &2
group by wait_class_id, wait_class
order by 3;
2723168908 Idle 1
3290255840 Configuration 9
3386400367 Commit 90
4108307767 System I/O 149
3875070507 Concurrency 182
1740759767 User I/O 184
1893977003 Other 244
4217450380 Application 365
2000153315 Network 475
[NULL] [NULL] 916
3871361733 Cluster 1844

Step 2
-------
Identify the event_id associated with above wait class ID
select event_id, event, count(*) cnt from dba_hist_active_sess_history
where snap_id between 18231 and 18232 and wait_class_id=3871361733
group by event_id, event
order by 3;
EVENT_ID EVENT COUNT(*)
1742950045 gc current retry 1
3897775868 gc current multi block request 1
512320954 gc cr request 4
661121159 gc cr multi block request 9
2685450749 gc current grant 2-way 11
3201690383 gc cr grant 2-way 18
1457266432 gc current split 27
3046984244 gc cr block 3-way 41
111015833 gc current block 2-way 62
3570184881 gc current block 3-way 62
737661873 gc cr block 2-way 67
2277737081 gc current grant busy 95
1520064534 gc cr block busy 235
2701629120 gc current block busy 396
1478861578 gc buffer busy 815

Step 3
-------
Identify the SQL_ID associated with the above event_id
select 'gc buffer busy' ,sql_id, count(*) cnt from dba_hist_active_sess_history
where snap_id between 18231 and 18232
and event_id in (1478861578)
group by sql_id having count(*) > 55
UNION
select 'gc current block busy',sql_id, count(*) cnt from dba_hist_active_sess_history
where snap_id between 18231 and 18232
and event_id in (2701629120)
group by sql_id having count(*) > 55
UNION
select 'gc cr block busy',sql_id, count(*) cnt from dba_hist_active_sess_history
where snap_id between 18231 and 18232
and event_id in (1520064534)
group by sql_id having count(*) > 55
order by 2 ;

Wait Event SQL ID waits
--------------------------------------------------------------------------------------
gc buffer busy 5qwhj3nru2jtq 765
gc current block busy 5qwhj3nru2jtq 332

Step 4 :
--------
Identify the SQL statement associated with the above SQL ID
select sql_id,sql_text from dba_hist_sqltext where sql_id in ('5qwhj3nru2jtq')
Output:
INSERT INTO Component_attrMap (Component_id, key, value) VALUES (:1, :2, :3)
Step 5 :
--------
Identify the object associated with the above statement
select current_obj#, count(*) cnt from dba_hist_active_sess_history
where snap_id between 18231 and 18232
and event_id in (1478861578,2701629120)and sql_id='5qwhj3nru2jtq'
group by current_obj#
order by 2;

Obj # Count(*)
67818 1
67988 1096

Step 6 :
-------
Identify the Object associated with the above Object ID
select object_id, owner, object_name, subobject_name, object_type from dba_objects
where object_id in (67988);
OBJECT_ID OWNER OBJECT_NAME SUBOBJECT_NAME
--------- ----- ------------- --------------
67988 SCOTT COMP_ID_INDX1 INDEX

In this case creating a REVERSE KEY index provided the required solution.

Copy CRS Home from one node to another

2009-09-25T15:14:00.005-04:00

It for some reason your CRS_HOME is messed up on node node and you unable to bring up the 'crs' then you can copy the CRS_HOME from the working node and copy it onto the node having non-working CRS_HOME.

tar the CRS_HOME from working node to non-working node, un-tar it and make the following change.
In the file $CRS_HOME/inventory/ContentsXML/oraclehomeproperties.xml
look for LOCAL_NODE NAME and change it to the node name where you have un-tar'ed the CRS_HOME

CLUSTER_INFO>
LOCAL_NODE NAME="rklx2"/>
NODE_LIST>
NODE NAME="rklx1"/>
NODE NAME="rklx2"/>

Also make sure under /etc/init.d the following files have fully qualified CRS and ORACLE Home values, if not, replace the variables with actual values.

init.cssd
init.evmd
init.crsd

How to Manually remove the OEM Agent

2009-09-25T11:51:00.003-04:00

How to Manually remove the OEM Agent

Purpose and Scope: You are trying to de-install the old agent and then re-install the newer version of the Agent and running into various issues.

Best Method:
1) Stop the Agent (all nodes in case of RAC)
2) Go to OEM Grid Control and remove all targets associated with the host/database to be removed, which includes databases, listeners, hosts, agents.
3) Make sure to verify from the OEM Grid Control that the targets you were trying to remove are completely removed. Try looking up from Targets=>All Targets and search for the name and you should NOT see any results.
4) Go to the $AGENT_HOME and remove Agent home from Linux/Unix box.
a) $ rm –rf agent10g
b) $ rm –rf agent10gInventory
c) $ rm –f $TNS_ADMIN/oraInst.agent10g..loc

If you want to re-install the agent, the re-install of the agent should work without issues but if for some reason the above does not work, try executing the following steps to manually remove the agent.

You can also remove the Agent by following the Metalink Note:436679.1 on
How to Remove an Orphaned Target Using the EMDiag Kit 10.2

The above should do the trick but sometimes it is not completely removed from the repository and when you install the newer version it gives various erros.

The following method should remove it completely

1) Login to oemdb as SYS and -

select * from sysman.mgmt_targets_delete
where delete_complete_time is NULL
order by target_name

This lists all target deletions that did not complete for some reason (normally the delet_complete_time would show the timestamp of when the target was removed.

2) Find all targets that belong to the system you are having issues with

3) Run the following to complete the deletion –

exec mgmt_admin.delete_target('rklx1_rk_crs','cluster');
where ‘'rklx1_rk_crs' is the clustername and also is the “target_name column from the earlier query and “cluster” is the target_type

4) If this doesn’t work then do this –
Log into oemdb and run –

alter index SYSMAN.MGMT_STRING_METRIC_HISTORY_PK rebuild;

5) Then remove it using this procedure –
exec mgmt_admin.delete_target_internal('rklx1_rk_crs','cluster');

For the agent you could try –

exec mgmt_admin.cleanup_agent(’host.domain:3872’);

Script to find elapsed time based on a SQL_ID from DBA_HIST Tables

2009-07-29T14:57:00.003-04:00

Script to find execution time based on a SQL_ID

SET LINESIZE 120
SET PAGESIZE 1000
COL executions FOR 999,999,999
COL elapsed_time FOR 999,999,999,999
COL avg_ms FOR 999999.99
COL min_ms FOR 999999.99
COL max_ms FOR 999999.99
SELECT
TO_CHAR(TRUNC(snapshot.begin_interval_time + 1/8,'HH24'),'DD-MON-YYYY HH24:MI:SS') || ' EDT' SNAP_BEGIN,
sqlstat.instance_number,
SUM(sqlstat.executions_delta) executions,
MIN(sqlstat.elapsed_time_delta / sqlstat.executions_delta / 1000) min_ms,
MAX(sqlstat.elapsed_time_delta / sqlstat.executions_delta / 1000) max_ms,
SUM(sqlstat.elapsed_time_delta) / SUM(sqlstat.executions_delta) / 1000 avg_Ms
FROM
dba_hist_sqlstat sqlstat,
dba_hist_snapshot snapshot
WHERE
sqlstat.dbid = snapshot.dbid
AND sqlstat.instance_number = snapshot.instance_number
AND sqlstat.snap_id = snapshot.snap_id
AND sqlstat.sql_id = '&1'
AND snapshot.begin_interval_time >= TO_DATE('27-JUL-2009 21:00:00','DD-MON-YYYY HH24:MI:SS')
GROUP
BY TO_CHAR(TRUNC(snapshot.begin_interval_time + 1/8,'HH24'),'DD-MON-YYYY HH24:MI:SS') || ' EDT',
sqlstat.instance_number
ORDER
BY snap_begin
/

Hash-Partitioned Reverse-Key Index

2009-07-01T08:27:00.003-04:00

Hash Paritioned global indexes provides higher throughput for applications with large numbers of concurrent insertions. In some applications, new insertions into the indexes are towards the right side of the index, usually this happends when you have an index column that is a monotonically increasing sequence number. Hash Partitioned indexes can improve performance in situations where a small number of nonpartitioned index's leaf blocks are experiencing high contention in an OLTP environment. Queries that use with an equality or IN operator in the WHERE clause can benefit significantly from a hash-partitioned global index.

For monotonically increasing key situatins, reverse keying the index will spread the activity, but only across the highest partition. Hash Partitioning will distribute the workload across all the index partitions, but still with contention at each index's right edge, reverse-key hash partitioning will not only distribute the activity across all the partitions, but also spread it within each partition.

Create Index CUSTOMER_IDX1 on CUSTOMER(ZIP_CODE)
global partition by hash(ZIP_CODE)
(partition P1 tablespace TBS_INDEX_1,
partition P2 tablespace TBS_INDEX_2,
partition P3 tablespace TBS_INDEX_3,
partition P4 tablespace TBS_INDEX_4)
REVERSE
/

Setup LISTENER (listener.ora)

2009-04-03T09:57:00.005-04:00

Query to find un-indexed Foreigh Key Constraints

2009-02-25T08:34:00.001-05:00

SELECT * FROM (SELECT c.table_name, cc.column_name, cc.position column_position FROM user_constraints c, user_cons_columns cc
WHERE c.constraint_name = cc.constraint_name AND c.constraint_type = 'R' MINUS SELECT i.table_name, ic.column_name, ic.column_position
FROM user_indexes i, user_ind_columns ic
WHERE i.index_name = ic.index_name )
ORDER BY table_name, column_position ;

Oracle Interconnect RAC

2008-11-24T13:30:00.003-05:00

Cluster Private Interconnect

The cluster interconnect is a high bandwidth, low latency communication facility that connects each node to other nodes in the cluster and routes messages among the nodes. It is a key component in building the RAC system.

In case of RAC database, the cluster interconnect is used for the following high-level functions:

Monitoring Health, Status, and Synchronize messages
Transporting lock management or resource coordination messages
Moving the Cache Buffers (data blocks) from node to node.

High performance database computing involves distributing the processing across an array of cluster nodes. It requires that the cluster interconnect provide high-data rates and low-latency communication between node processes.

Here's a few ways to find information about interconnect and troubleshoot any issues...

1. select * from gv$cluster_interconnects ;

2. Using the clusterware command oifcfg:
$ oifcfg getif
eth2 100.100.90.0 global public
eth0 192.168.10.0 global cluster_interconnect
eth1 192.168.11.0 global cluster_interconnect

3. Using oradebug ipc:
sqlplus “/ as sysdba”
SQL> oradebug setmypid
SQL> oradebug ipc Information written to trace file.

The above command would dump a trace to user_dump_dest. The last few lines of the trace would indicate the IP of the cluster interconnect. Below is a sample output.
From the trace file on node1:

SSKGXPT 0×5edf558 flags SSKGXPT_READPENDING socket no 9 IP 192.168.11.1 UDP 18852
From the trace file on node2:

SSKGXPT 0×5edf558 flags SSKGXPT_READPENDING socket no 9 IP 192.168.10.2 UDP 38967

Who is Locking my Object ?

2008-09-03T11:10:00.001-04:00

Find out using this script....

SELECT a.sid,a.serial#, a.username,c.os_user_name,a.terminal,c.process,
b.object_id,substr(b.object_name,1,40) object_name
from v$session a, dba_objects b, v$locked_object c
where a.sid = c.session_id
and b.object_id = c.object_id
/

How to find Database Import Speed

2008-06-20T15:33:00.002-04:00

SELECT
SUBSTR(sql_text, INSTR(sql_text,'INTO "'),30) table_name
, rows_processed
, ROUND( (sysdate-TO_DATE(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60,1) minutes
, TRUNC(rows_processed/((sysdate-to_date(first_load_time,'yyyy-mm-dd hh24:mi:ss'))*24*60)) rows_per_minute
FROM
sys.v_$sqlarea
WHERE
sql_text like 'INSERT %INTO "%'
AND command_type = 2
AND open_versions > 0;

Oracle Security White Papers

2008-06-11T13:51:00.001-04:00

Nice Reading about Oracle Security

www.ngssoftware.com/research/papers

How to find "user commits" during a user session

2008-05-07T07:48:00.000-04:00

You can find number of "user commits" during a particular user session. Just get the SID of the user for which you want to track the number of commits.

set lines 120
set pages 1000
col name format a25
select a.*,b.* from sys.v_$sesstat a, sys.v_$statname b
where a.STATISTIC#=b.STATISTIC#
and b.name like '%user%'
and a.sid=&input_sid
/

Basic VCS Commands

2008-02-29T22:51:00.002-05:00

BASIC VCS COMMANDS

SERVICE GROUPS AND RESOURCE OPERATIONS

Configuring service groups hagrp –add|-delete|-online|-offline group_name
Modifying resources hares –add|-delete res_name type group
hares –online|-offline res_name –sys system_name
Modifying agents haagent –start|-stop agent_name –sys system_name
BASIC CONFIGURATION OPERATIONS
Service Goups hagrp -modify group_name attribute_name value
hagrp –list group_name
hagrp –value attribute_name
hares -modify res_name attribute_name value
hares -link res_name res_name
Agents haagent -display agent_name –sys system_name
hatype –modify
VCS ENGINE OPERATIONS
Starting had hastart –force|–stale system_name
hasys –force system_name
Stopping had hastop –local|-all|-force|-evacuate
hastop –sys system_name
Adding Users hauser –add user_name
STATUS AND VERIFICATION
Group Status/Verification hagrp -display group_name|–state|–resource group_name
Resources Status/Verification hares -display res_name
hares –list
hares -probe res_name –sys system_name
Agents Status/Verification haagent –list
haagent -display agent_name –sys system_name
ps –ef|grep agent_name
VCS Status hastatus –group
LLT Status/Verification lltconfig –a list
lltstat|lltshow|lltdump
GAB Status/Verification gabconfig –a
gabdiskhb –l
COMMUNICATION
Starting and Stopping LLT lltconfig –c|U
Starting and Stopping GAB gabconfig –c –n #seed number
gabconfig –U
ADMINISTERATION
Administering Group Services hagrp –clear|-flush|-switch group_name –sys system_name
Administering Resources hares –clear|-probe res_name –sys system_name
Administering Agents haagent -list
haagent -display agent_name –sys system_name
Verify Configuration hacf –verify

How to find name of your cluster in RAC environment

2008-01-28T09:56:00.001-05:00

To find out the value of your CLUSTER_NAME from CRS (OCR), do the following from the CRS_HOME:

$CRS_HOME/bin/cemutlo -n

==>> OR

cd $CRS_HOME/bin
./ocrdump

=> this will create a text file called OCRDUMPFILE
open that file and look for this entry
[SYSTEM.css.clustername]
ORATEXT : crs_cluster

In this case, "crs_cluster" is the cluster name.

Foreign Key Constraint Error ORA-02298

2007-12-19T13:54:00.001-05:00

While trying to create FK on cust_status got the following error.

ALTER TABLE CUST_STATUS ADD (CONSTRAINT CUST_STATUS_FK FOREIGN KEY (LOGIN) REFERENCES USERS(LOGIN))
*
ERROR at line 1:
ORA-02298: cannot validate (SCOTT.CUST_STATUS_FK) - parent keys not found

SQL> desc cust_status
Name Null? Type
----------------------------------------- -------- ----------------------------
CUSTID NOT NULL NUMBER(8)
TIMESTAMP NOT NULL DATE
STATUS NOT NULL VARCHAR2(35)
LOGIN VARCHAR2(20) ID NUMBER

SQL> desc users
Name Null? Type
----------------------------------------- -------- ----------------------------
LOGIN NOT NULL VARCHAR2(20) PASSWORD VARCHAR2(30)
LAST_NAME VARCHAR2(50)
FIRST_NAME VARCHAR2(50)
EMAIL VARCHAR2(100)
ACTIVE CHAR(1)

To find out which rows are the problem rows,

SQL> select custid,login from cust_status a
where not exists (select 'x' from users where login = a.login);

You can delete the data using...

SQL> delete from cust_status
where login is null or login not in ( select login from users )

Now you should be able to create the FK constraint.

How to test Oracle Apps after a patch

2007-12-07T15:03:00.000-05:00

How to test Oracle Applications after any maintenance, for example patch

Access the Application Home Page (http://host_name:port)
This verifies Oracle HTTP Server is up and running.

Login to Apps as "SYSADMIN"
If you can get to that page, it verifies JSERV is up and running as this page is served by JServ

Once you are able to login to the Apps, this verifies your connection to the database is working.

Click on Help button on the top right portion, if you can access the help page, it verifies you connection from the front end to the database is working as that page is served by the database.

Click on the Concurrent Manager, if you get to the page where it shows all the jobs, it verifies Forms is up and running. Try submitting a job and see if it runs successfully, it proves Concurrent Manager is up and running.

DG Failover Steps

2007-12-06T15:10:00.000-05:00

Purpose : Failover to standby database in case the primary database server crashes.
Assumptions : Primary Database SID : CHDP1
Standby Database SID : CHDS1
flashback is on

Now the Primary datbase server crashes and no longer accessible.

Step by Step Instructions.

Login to standby database (CHDS1)
SQL> alter database recover managed standby database finish force;
SQL> alter database commit to switchover to primary;

In very urgent situations when you can not wait for some of the logs to be applied.
SQL> alter database activate standby database;

Now at this point in time your standby database (CHDS1) becomes primary databse.

After few hours, your original primary database server (which has CHDP1 database) comes back up and you wanted to make it (CHDP1) a standby database.

Login to CHDS1

SQL> select to_char(standby_became_primary_scn) from v$database;
TO_CHAR(STANDBY_BECAME_PRIMARY_SCN)
--------------------------------------------------
1234567

Login to CHDP1
SQL> startup mount;
SQL> flashback database to scn 1234567;
SQL> alter database convert to physical standby;
SQL> shutdown immediate;
SQL> startup mount;

Check Kernel Parameters before installing software

2007-12-05T10:37:00.000-05:00

To check Kernel parameter for Linux before installing oracle(you can add additional stuff as needed)
----- start check_OS_linux.sh -----------
echo "Checking kernel parameters..."
/sbin/sysctl -a 2>&1 | grep sem | grep -v error
/sbin/sysctl -a 2>&1 | grep shm | grep -v error
/sbin/sysctl -a 2>&1 | grep file-max | grep -v error
/sbin/sysctl -a 2>&1 | grep ip_local_port_range | grep -v error
/sbin/sysctl -a 2>&1 | grep rmem_ | grep -v error
/sbin/sysctl -a 2>&1 | grep wmem_ | grep -v error

echo "Checking OS components ..."
rpm -q binutils
rpm -q libaio
rpm -q gcc
rpm -q libstdc++
rpm -q libstdc++-devel
rpm -q gcc-c++
rpm -q glibc
rpm -q gnome-libs
rpm -q make
rpm -q pdksh
rpm -q sysstat
----------------- end script check_OS_linux.sh -----------------

To check kernel parameters for Solaris

---------------- start script check_OS_solaris.sh ----------------
/sbin/sysctl -a 2>&1 | grep sem | grep -v error

/sbin/sysctl -a 2>&1 | grep shm | grep -v error

/sbin/sysctl -a 2>&1 | grep file-max | grep -v error

/sbin/sysctl -a 2>&1 | grep ip_local_port_range | grep -v error

/sbin/sysctl -a 2>&1 | grep rmem_ | grep -v error

/sbin/sysctl -a 2>&1 | grep wmem_ | grep -v error
----------------- end script check_OS_solaris.sh ----------------

RMAN Backup types

2007-12-05T08:34:00.000-05:00

Full
A backup of a datafile that includes every allocated block in the file being backed up. A full backup of a datafile can be an image copy, in which case every data block is backed up. It can also be stored in a backup set, in which case datafile blocks not in use may be skipped, according to certain rules.

A full backup cannot be part of an incremental backup strategy; that is, it cannot be the parent for a subsequent incremental backup.

Incremental
An incremental backup is either a level 0 backup, which includes every block in the file except blocks compressed out because they have never been used, or a level 1 backup, which includes only those blocks that have been changed since the parent backup was taken.

A level 0 incremental backup is physically identical to a full backup. The only difference is that the level 0 backup is recorded as an incremental backup in the RMAN repository, so it can be used as the parent for a level 1 backup.

How to setup VIP (Virtual IP) in a DG setup

2007-11-17T06:24:00.001-05:00

I will write soon...

How to start databases in a DG setup

2007-11-17T06:06:00.000-05:00

Scenario : Power outage at your data center brought down the databases in the DG configuration.

Solution : Start up the primary database
SQL> startup
: startup mount the standby database
SQL> startup mount

Login to Primary node and start dgmgrl
DGMGRL> connect sys/password
DGMGRL> enable configuration;
DGMGRL> enable fast_start failover;
DGMGRL> show configuration;

It should show you the primary database and physical standby database

Some Important Linux/Unix Commands

2007-11-01T14:29:00.000-04:00

How to find bit level in Unix
$ isainfo -kv

In Linux
$ uname -a
gives you OS, version and bit level.

Find out KDE Desktop version:
konqueror --version

Find out Gnome Desktop version:
gnome-panel --version

Find out Mozilla browser version:
mozilla --version

Find out Firefox browser version:
firefox --version

Find out current Language:
set | egrep '^(LANG|LC_)'

Find out disk space usage:
df -h

Find/Estimate file space usage:
du -h

Find out version of Linux glibc:
ls -l /lib/libc-*.so /lib/libc.so*

Find out user limits:
ulimit -a

Find out installed device drivers (modules)
lsmod

Find out information about an X server:
xdpyinfo
It can find out:
• Name of display:
• Version number
• Vendor name (such as The XFree86 Project)
• Vendor release number
• And XFree86 version number

Find out information about Linux CPU
cat /proc/cpuinfo

Find out information about Linux Memory
cat /proc/meminfo
OR
free -m
OR
free -g

Find out user shell name:
ps -p $$ | tail -1 | awk '{ print $4 }'

Dump Linux kernel variables
/sbin/sysctl -a

Find out running Linux kernel version:
uname -mrs
uname -a
cat /proc/version

Dump or display memory information and swap information:
free -m

Network card and IP address information:
ifconfig -a
ifconfig -a|less

Debian / Ubuntu Linux network configuration file (all interface eth0,eth1,…ethN)
more /etc/network/interfaces
Redhat / CentOS / Fedora Linux network configuration file (eth0)
more /etc/sysconfig/network-scripts/ifcfg-eth0
Note replace eth1 for 2nd network card and so on.

Display routing information
route -n
route

Display list of all open ports
netstat -tulpn

View login related logs
tail -f /var/log/secure
vi /var/log/secure
grep 'something' /var/log/secure
View mail server related logs
tail -f /var/log/maillog
vi /var/log/maillog
grep 'something' /var/log/maillog

Find how long the system has been running
uname
w

Show who is logged on and what they are doing
w
who

Display list of tasks
top

Display all running process
ps aux
ps aux | grep process-name

Display list of all installed software on Redhat / CentOS / Fedora
rpm -qa
rpm -qa | grep 'software-name'
rpm -qa | less

Display list of all installed software on Debian / Ubuntu
dpkg --list

Once information collected it can be easily send as an email to help desk. You can use all above command to gathers information about a remote Linux system over secure ssh session (see related functions that gathers up information about a Linux and FreeBSD system). Best part is all above commands runs in non privileged

Update rows using replace in SQL*Plus

2007-10-31T14:16:00.000-04:00

For Example

SQL> desc rk
Name Null? Type
----------------------------------------- -------- ----------------------------
CONTENT_VALUE_SMALL VARCHAR2(512 CHAR)

SQL> select * from rk;

http://i.a.rk.net/v5cache/TBS/veryf/images/i0/3telecom_expo_400_304.jpg

Now you want to replace rk.net with xyz.net

SQL> update rk set CONTENT_VALUE_SMALL=replace(CONTENT_VALUE_SMALL,'http://i.a.rk','http://i.a.xyz');

Manual installation of Oracle Text 10g

2007-10-31T14:12:00.000-04:00

Manual installation of Text 10gR1 (10.1.0.x) and (10.2.0.x)

1. Text dictionary, schema name CTXSYS, is created by calling following script from SQL*Plus connected as SYSDBA:

SQL> connect SYS/password as SYSDBA
SQL>@?/ctx/admin/catctx.sql CTXSYS SYSAUX TEMP NOLOCK

Where:CTXSYS - is the ctxsys user password
SYSAUX - is the default tablespace for ctxsys
TEMP - is the temporary tablespace for ctxsys
LOCK|NOLOCK - ctxsys user account will be locked or not

2. The next step is to install appropriate language-specific default preferences.To manually install US default preferences, for example, log into sqlplus as CTXSYS, and run 'drdefus.sql' as described below:

SQL> connect CTXSYS/password
SQL>@?/ctx/admin/defaults/drdefus.sql

CPIO Command

2007-10-30T12:45:00.001-04:00

Extract the file:
cpio -idmv < file_name

Find out last sql statement issued from the same session

2007-10-25T12:44:00.000-04:00

select sql_text from v$sql
where hash_value = ( select prev_hash_value from v$session
where sid = (select sid from v$mystat where rownum = 1))
/

How to find object/schema size

2007-09-27T11:39:00.002-04:00

Find SCHEMA SIZE or USER SIZE

SELECT tablespace_name,
Sum(bytes)/1024/1024 AS total_size_mb
FROM dba_segments
WHERE owner = Upper('&User_Name')
GROUP BY owner, rollup(tablespace_name)

Find OBJECT Size

select segment_name, sum(bytes/(1024*1024)) size_mb from user_segments
where segment_type = 'TABLE' and segment_name='&SEGMENT_NAME'
group by segment_name

Simple Unix Commands AWK, SED etc....

2007-09-27T11:07:00.000-04:00

Find files greater than 1M

$> find . -depth -xdev -size +1000000c -print

Delete files older than 3 days

$> find . -mtime +3 -print -exec rm * {} ;

To find out what is running on a port

$> lsof -i tcp grep port number

Change contents of file from lower case to upper case

$> cat x.lst tr [a-z] [A-Z] > x.sql

AWK COMMANDS

To print the first two fields in opposite order, enter:

$> awk '{ print $2, $1 }' chapter1

To display all lines between the words start and stop, including "start" and "stop", enter:

$> awk '/start/,/stop/' chapter1

To run an awk command program, sum2.awk, that processes the file, abc1, enter:

$> awk -f sum2.awk abc1

contents of file ‘abc1’
2 2 2 2
3 3 3 3
4 4 4 4
5 5 5 5
------- Start of Program sum2.awk ------
{
sum += $2
}
END {
print "Sum: ", sum;
print "Average:", sum/NR;
}
------- End of Program sum2.awk ------
Explanation: The first action adds the value of the second field of each line to the variable sum. All variables are initialized to the numeric value of 0 (zero) when first referenced. The pattern END before the second action causes those actions to be performed after all of the input file has been read. The NR special variable, which is used to calculate the average, is a special variable specifying the number of records that have been read.

****************************************************************************************
Repeat everyline in the file and prefix it with some word

Example :

Source : a.sh

abc
def
ghi

Output : b.sh

echo abc
tnsping abc
echo def
tnsping def
echo ghi
tnsping ghi

Solution

for line in `cat a.sh`
do
echo echo "$line"
echo tnsping $line
done >> b.sh

*********************************************************************************

Bring the 1st line at the end of 2nd line and then 3rd line at the end of 4th line and so on…..

Example :

Input

ADMINDB1
SYSTEM/manager@
ADSERVD1
SYSTEM/manager@

Output

SYSTEM/manager@ADMINDB1
SYSTEM/manager@ADSERVD1

Solution

awk 'NR % 2 == 0' testfile > file1
awk 'NR % 2 ' testfile > file2
paste file1 file2 tr -d 't'

******************************************************

DBMS_JOB

2007-09-26T11:02:00.000-04:00

Here's few examples of how to submit the jobs.

The following would create a job to refresh a MV everyday at Midnight starting from tomorrow

SQL> variable jno number;
SQL> exec dbms_job.submit(:jno,'DBMS_MVIEW.REFRESH(''MV_TESTING'',''C'');',trunc(sysdate)+1,'sysdate+1');

Here's another example

declare
jno number;
begin
dbms_job.submit(job => jno,
what => 'my_proc(abc);'
next_date => sysdate+1,
interval => 'sysdate+1');
end;
/

next_date = > next date/time you want to run the job to run. An argument of DATE datatype must be passed in.

Example : to_date('09/30/2007 11:24 AM','MM/DD/YYYY HH:MI AM');
OR
sysdate+1 Same time tomorrow
trunc(sysdate)+1 Midnight tomorrow
trunc(sysdate)+19/24 7 pm (1900 Hrs) today

interval => at which frequency the job should be run . The argument passed is in varchar2.
Example
'sysdate+1' 24 hours after the job's current run time
'trunc(sysdate)+1' Midnight after the day the job is run
'trunc(sysdate)+19/24' 7 PM on the day the job is run

Data Guard Tips

2007-09-24T11:35:00.001-04:00

Shutting Down a Physical Standby Database

To shut down a physical standby database, use the SQL*Plus SHUTDOWN command. If the database is performing managed recovery, you must cancel managed recovery operations before issuing the SHUTDOWN command.

If the primary database is up and running, defer the archive log destination on the primary database and perform a log switch operation (to make the defer operation take effect) before shutting down the standby database. Otherwise, log transport services will not be able to transmit redo data to this standby site.

The following steps show you how to shut down a standby database:

Find out if the standby database is performing managed recovery. If the MRP0 or MRP process exists, then the standby database is performing managed recovery.

You can find out which instance is the apply-instance from the ps -eaf | grep mrp command at the OS Level OR using SQL*Plus at the standby database

SQL> select b.host_name,b.INSTANCE_NAME,a.PROCESS,a.STATUS
from gv$managed_standby a, gv$instance b where a.inst_id=b.inst_id

Cancel managed recovery operations.
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Shut down the standby database.
SQL> SHUTDOWN IMMEDIATE;

Generate Test Data

2007-09-10T14:54:00.000-04:00

Came across this website for generating test data.

http://www.generatedata.com/#about

http://www.sqledit.com/dg/

http://www.igs-edv.de/tdg_e/

Where is your Query stuck

2007-09-10T10:51:00.000-04:00

If you know the process ID you can find the SID, SERIAL#, SQL_ID and other details using the following query: (process id can be taken from the unix TOP command)

-- To find the SID, username and SQL_ID associated with a process id taken from unix
select a.sid,a.serial#,a.username,a.status,a.sql_id
from v$session a,v$process b
where b.spid='&PROCESS_ID'
and a.paddr=b.addr
/

You can take a look at the actual explain_plan by executing the following

select operation,options,object_name,object_alias,object_type
from v$sql_plan where sql_id=''

Select from v$session_longops to find out where your query is

select qcsid,sid,username,opname,target,sofar,totalwork,units,start_time,
time_remaining,elapsed_seconds,message
from v$session_longops
order by qcsid
/

Refreshing a Materialized View owned by other user

2007-09-06T15:02:00.000-04:00

Suppost USER_A owns the MV and USER_B wants the privileges to execute it.

While trying to refresh a materialized view owned by other users you might get

SQL> EXECUTE DBMS_MVIEW.REFRESH('SALES_SUMMARY','F');
ORA-01031: insufficient privileges
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2255
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2461
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2430
ORA-06512: at line 2

In order to refresh a materialized view owned by other user, you must have the following privileges in addition to privileges on objects owned by USER_A which are being used in the MV.

SQL> GRANT ALTER ANY MATERIALIZED VIEW TO &USER_B

The DBMS_MVIEW package can manually invoke either a fast refresh or a complete refresh. F means Fast Refresh and C means Complete Refresh:

EXECUTE DBMS_MVIEW.REFRESH('SALES_SUMMARY','F');

**********
The privileges required to create a materialized view log directly relate to the privileges necessary to create the underlying objects associated with a materialized view log.

If you own the master table, you can create an associated materialized view log if you have the CREATE TABLE privilege.

If you are creating a materialized view log for a table in another user's schema, you must have the CREATE ANY TABLE and COMMENT ANY TABLE system privileges, as well as either the SELECT object privilege on the master table or the SELECT ANY TABLE system privilege.

In either case, the owner of the materialized view log must have sufficient quota in the tablespace intended to hold the materialized view log or must have the UNLIMITED TABLESPACE system privilege.

How does one clone database with RMAN

2007-09-04T16:46:00.000-04:00

How does one clone/duplicate a database with RMAN?

The first step to clone or duplicate a database with RMAN is to create a new INIT.ORA and password file (use the orapwd utility) on the machine you need to clone the database to. Review all parameters and make the required changed. For example, set the DB_NAME parameter to the new database's name.

Secondly, you need to change your environment variables, and do a STARTUP NOMOUNT from sqlplus. This database is referred to as the AUXILIARY in the script below.

Lastly, write a RMAN script like this to do the cloning, and call it with "rman cmdfile dupdb.rcv":

connect target sys/secure@origdb

connect catalog rman/rman@catdb

connect auxiliary /

run {

set newname for datafile 1 to '/ORADATA/u01/system01.dbf';

set newname for datafile 2 to '/ORADATA/u02/undotbs01.dbf';

set newname for datafile 3 to '/ORADATA/u03/users01.dbf';

set newname for datafile 4 to '/ORADATA/u03/indx01.dbf';

set newname for datafile 5 to '/ORADATA/u02/example01.dbf';

allocate auxiliary channel dupdb1 type disk;

set until sequence 2 thread 1;

duplicate target database to dupdb

logfile

GROUP 1 ('/ORADATA/u02/redo01.log') SIZE 200k REUSE,

GROUP 2 ('/ORADATA/u03/redo02.log') SIZE 200k REUSE;

}

The above script will connect to the "target" (database that will be cloned), the recovery catalog (to get backup info), and the auxiliary database (new duplicate DB). Previous backups will be restored and the database recovered to the "set until time" specified in the script.

Notes: the "set newname" commands are only required if your datafile names will different from the target database.

The newly cloned DB will have its own unique DBID.

PL/SQL Procedure to update values

2007-09-04T16:06:00.000-04:00

PL/SQL Procedure to update values in a table based on values taken from another table.

EXAMPLE :
SQL> desc change_log

Name Null? Type
----------------------------------------- -------- ----------------------------
LOG_ID NOT NULL NUMBER(11)
POLL_ID NOT NULL NUMBER(11)
QUESTION_ID NOT NULL NUMBER(11)
ANSWER_ID NOT NULL NUMBER(11)
COUNT NOT NULL NUMBER(11)
TIME NOT NULL DATE

SQL> desc answers

Name Null? Type
----------------------------------------- -------- ----------------------------
ANSWER_ID NOT NULL NUMBER(11)
QUESTION_ID NOT NULL NUMBER(11)
POLL_ID NOT NULL NUMBER(11)
ANSWER_TEXT NOT NULL VARCHAR2(512)
ANSWER_VOTES NOT NULL NUMBER(11)

## Update answers table based on certain conditons on the change_log table.

EXECUTE DBMS_SESSION.SET_NLS('NLS_DATE_FORMAT','''DD-MON-YYYY HH24:MI:SS''');
SET SERVEROUTPUT ON;
execute dbms_output.enable(1000000000);

Declare
Begin
for rec in (
select sum(NVL(change_log.count,0)) as count_total , POLL_ID,QUESTION_ID,ANSWER_ID
from change_log
group by POLL_ID,QUESTION_ID,ANSWER_ID
)
LOOP
begin
update answers
set answers.ANSWER_VOTES= NVL(rec.count_total,0)
where answers.ANSWER_ID=rec.ANSWER_ID
and answers.QUESTION_ID=rec.QUESTION_ID
and answers.POLL_ID=rec.POLL_ID
and answers.answer_id=3;
exception when Others then
dbms_output.put_line ( rec.ANSWER_ID || ' - ' ||rec.ANSWER_ID||' - '||rec.POLL_ID);
end;
END LOOP;
End;
/

Load Test Data

2007-09-04T12:00:00.000-04:00

Many times development teams asks DBAs to populate test data so that they can perform some performance test.

Below you will find some examples to load test data.

SQL> create table sales
(trans_date date, cust_id int, sales_amount number );

SQL> insert /*+ APPEND */ into sales
select trunc(sysdate,'year')+mod(rownum,366) TRANS_DATE,
mod(rownum,100) CUST_ID,
abs(dbms_random.random)/100 SALES_AMOUNT
from all_objects;
SQL> commit;

SQL> begin
for i in 1 .. 4
loop
insert /*+ APPEND */ into sales
select trans_date, cust_id, abs(dbms_random.random)/100
from sales;
commit;
end loop;
end;
/
PL/SQL procedure successfully completed.

SQL> commit;

## SELECT A RANDOM VALUE ##
select cust_id from (select cust_id from sales order by dbms_random.value) where rownum=1;

Some other commands using DBMS_RANDOM to generate and populate values

SELECT dbms_random.normal FROM dual;
SELECT ABS(dbms_random.normal) FROM dual;
SELECT (1+ABS(MOD(dbms_random.random,100000))) FROM dual; ==>> force output to +ve values only
SELECT dbms_random.value(2, 3) FROM dual; ==>> Get a value between 2 and 3
## Random String ##
dbms_random.string(opt IN CHAR, len IN NUMBER)
RETURN VARCHAR2;

select dbms_random.string('A', 12) from dual; ==>> random alphanumeric values

opt seed values:
'a','A' alpha characters only (mixed case)
'l','L' lower case alpha characters only
'p','P' any printable characters
'u','U' upper case alpha characters only
'x','X' any alpha-numeric characters (upper)

-- create test data == another example ==
CREATE TABLE test (
col1 VARCHAR2(20),
col2 VARCHAR2(20));

DECLARE
x VARCHAR2(20);
y VARCHAR2(20);
BEGIN
FOR i IN 1..100
LOOP
x := dbms_random.string('A', 20);
y := dbms_random.string('A', 20);

INSERT INTO test
(col1, col2)
VALUES
(x,y);
END LOOP;
COMMIT;
END;
/

RAC Architecture Overview

2007-07-30T10:58:00.002-04:00

RAC Architecture Overview

Let's begin with a brief overview of RAC architecture.

A cluster is a set of 2 or more machines (nodes) that share or coordinate resources to perform the same task.

A RAC database is 2 or more instances running on a set of clustered nodes, with all instances accessing a shared set of database files.

Depending on the O/S platform, a RAC database may be deployed on a cluster that uses vendor clusterware plus Oracle's own clusterware (Cluster Ready Services), or on a cluster that solely uses Oracle's own clusterware.

Thus, every RAC sits on a cluster that is running Cluster Ready Services. srvctl is the primary tool DBAs use to configure CRS for their RAC database and processes.

Cluster Ready Services and the OCR

Cluster Ready Services, or CRS, is a new feature for 10g RAC. Essentially, it is Oracle's own clusterware. On most platforms, Oracle supports vendor clusterware; in these cases, CRS interoperates with the vendor clusterware, providing high availability support and service and workload management. On Linux and Windows clusters, CRS serves as the sole clusterware. In all cases, CRS provides a standard cluster interface that is consistent across all platforms.

CRS consists of four processes (crsd, occsd, evmd, and evmlogger) and two disks:
the Oracle Cluster Registry (OCR), and the voting disk.
CRS manages the following resources:
The ASM instances on each node
Databases
The instances on each node
Oracle Services on each node

The cluster nodes themselves, including the following processes, or "nodeapps":
VIP
GSD
The listener
The ONS daemon

CRS stores information about these resources in the OCR. If the information in the OCR for one of these resources becomes damaged or inconsistent, then CRS is no longer able to manage that resource. Fortunately, the OCR automatically backs itself up regularly and frequently.
Interacting with CRS and the OCR: srvctl
srvctl is the tool Oracle recommends that DBAs use to interact with CRS and the cluster registry. Oracle does provide several tools to interface with the cluster registry and CRS more directly, at a lower level, but these tools are deliberately undocumented and intended only for use by Oracle Support. srvctl, in contrast, is well documented and easy to use. Using other tools to modify the OCR or manage CRS without the assistance of Oracle Support runs the risk of damaging the OCR.
Using srvctl
Even if you are experienced with 9i srvctl, it's worth taking a look at this section; 9i and 10g srvctl commands are slightly different.
srvctl must be run from the $ORACLE_HOME of the RAC you are administering. The basic format of a srvctl command is
srvctl [options]
where command is one of
enabledisablestartstoprelocatestatusaddremovemodifygetenvsetenvunsetenvconfig
and the target, or object, can be a database, instance, service, ASM instance, or the nodeapps.
The srvctl commands are summarized in this table:
Table 1. Summary of srvctl commands.
Command
Targets
Description
srvctl addsrvctl modifysrvctl remove
databaseinstanceservicenodeapps
srvctl add / remove adds/removes target's configuration information to/from the OCR.
srvctl modify allows you to change some of target's configuration information in the OCR without wiping out the rest.
srvctl relocate
service
Allows you to reallocate a service from one named instance to another named instance.
srvctl config
databaseservicenodeappsasm
Lists configuration information for target from the OCR.
srvctl disablesrvctl enable
databaseinstanceserviceasm
srvctl disable disables target, meaning CRS will not consider it for automatic startup, failover, or restart. This option is useful to ensure an object that is down for maintenance is not accidentally automatically restarted.
srvctl enable reenables the specified object.
srvctl getenvsrvctl setenvsrvctl unsetenv
databaseinstanceservicenodeapps
srvctl getenv displays the environment variables stored in the OCR for target.
srvctl setenv allows these variables to be set, and unsetenv unsets them.
srvctl startsrvctl statussrvctl stop
databaseinstanceservicenodeappsasm
Start, stop, or display status (started or stopped) of target.
As you can see, srvctl is a powerful utility with a lot of syntax to remember. Fortunately, there are only really two commands to memorize: srvctl -help displays a basic usage message, and srvctl -h displays full usage information for every possible srvctl command.

Examples for using srvctl

Example 1. Bring up the MYSID1 instance of the MYSID database.
[oracle@myserver oracle]$ srvctl start instance -d MYSID -i MYSID1

Example 2. Stop the MYSID database: all its instances and all its services, on all nodes.
[oracle@myserver oracle]$ srvctl stop database -d MYSID

Example 3. Stop the nodeapps on the myserver node. NB: Instances and services also stop.
[oracle@myserver oracle]$ srvctl stop nodeapps -n myserver

Example 4. Add the MYSID3 instance, which runs on the myserver node, to the MYSID clustered database.
[oracle@myserver oracle]$ srvctl add instance -d MYSID -i MYSID3 -n myserver

Example 5. Add a new node, the mynewserver node, to a cluster.[oracle@myserver oracle]$ srvctl add nodeapps -n mynewserver -o $ORACLE_HOME -A 149.181.201.1/255.255.255.0/eth1
(The -A flag precedes an address specification.)

Example 6. To change the VIP (virtual IP) on a RAC node, use the command
[oracle@myserver oracle]$ srvctl modify nodeapps -A new_address

Example 7. Find out whether the nodeapps on mynewserver are up.[oracle@myserver oracle]$ srvctl status nodeapps -n mynewserverVIP is running on node: mynewserverGSD is running on node: mynewserverListener is not running on node: mynewserverONS daemon is running on node: mynewserver

Example 8. Disable the ASM instance on myserver for maintenance.
[oracle@myserver oracle]$ srvctl disable asm -n myserver
Debugging srvctl
Debugging srvctl in 10g couldn't be easier. Simply set the SRVM_TRACE environment variable.
[oracle@myserver bin]$ export SRVM_TRACE=true
Let's repeat Example 6 with SRVM_TRACE set to true:[oracle@myserver oracle]$ srvctl status nodeapps -n mynewserver/u01/app/oracle/product/10.1.0/jdk/jre//bin/java -classpath /u01/app/oracle/product/10.1.0/jlib/netcfg.jar:/u01/app/oracle/product/10.1.0/jdk/jre//lib/rt.jar:/u01/app/oracle/product/10.1.0/jdk/jre//lib/i18n.jar:/u01/app/oracle/product/10.1.0/jlib/srvm.jar:/u01/app/oracle/product/10.1.0/jlib/srvmhas.jar:/u01/app/oracle/product/10.1.0/jlib/srvmasm.jar:/u01/app/oracle/product/10.1.0/srvm/jlib/srvctl.jar -DTRACING.ENABLED=true -DTRACING.LEVEL=2 oracle.ops.opsctl.OPSCTLDriver status nodeapps -n mynewserver[main] [19:53:31:778] [OPSCTLDriver.setInternalDebugLevel:165] tracing is true at level 2 to file null[main] [19:53:31:825] [OPSCTLDriver.:94] Security manager is set[main] [19:53:31:843] [CommandLineParser.parse:157] parsing cmdline args[main] [19:53:31:844] [CommandLineParser.parse2WordCommandOptions:900] parsing 2-word cmdline[main] [19:53:31:866] [GetActiveNodes.create:212] Going into GetActiveNodes constructor...[main] [19:53:31:875] [HASContext.getInstance:191] Module init : 16[main] [19:53:31:875] [HASContext.getInstance:216] Local Module init : 19...[main] [19:53:32:285] [ONS.isRunning:186] Status of ora.ganges.ons on mynewserver is trueONS daemon is running on node: mynewserver[oracle@myserver oracle]$

Pitfalls

A little impatience when dealing with srvctl can corrupt your OCR, ie, put it into a state where the information for a given object is inconsistent or partially missing. Specifically, the srvctl remove command provides the -f option, to allow you to force removal of an object from the OCR. Use this option judiciously, as it can easily put the OCR into an inconsistent state.

Restoring the OCR from an inconsistent state is best done with the assistance of Oracle Support, who will guide you in using the undocumented $CRS_HOME/bin/crs_* tools to repair it. The OCR can also be restored from backup.

Error messages

srvctl errors are PRK% errors, which are not documented in the 10gR1 error messages manual. However, for those with a Metalink account, they are documented on Metalink here.
Conclusion

srvctl is a powerful tool that will allow you to administer your RAC easily and effectively. In addition, it provides a valuable buffer between the DBA and the OCR, making it more difficult to corrupt the OCR.

Global Cache Service GCS and Global Enqueue Service GES together manage the Cache Fusion processes, resource transfer and resource escalation among the instances. Enqueues are internal oracle locks; GCS to handle the buffer cache across all instances.

GCS and GES together maintain Global Resource Directory (GRD). GRD remains in the memory and is stored on all instances. GRD records current status of the data blocks.
It is distributed across all instances in a cluster and is located in the variable or shared pool section of the SGA.

RAC Processes : LMON – Global Enqueue Service Monitor (LMON) monitors the entire cluster. It manages instance and process failures and associated recovery for GCS and GES.

LMDx – Global Enqueue Service Daemon (LMD) is the lock agent. It also handles dead lock detection and remote enqueue requests.

LMSx – Global Cache Service Processes are the processes that handle remote GCS messages. Can have upto 10 in number. LMS is interconnect process and monitors the block transfer between instances.

LCKx – It manages Global Enqueue requests and the cross-instance broadcast.

DIAG – Diagnosability Daemon – Monitors the health of the instance and captures the data for instance process failures. Can be in non-rac database too after 9i.

The use of single SPFILE provide administrative ease and SPFILE has to be located on a clustered file system.

OCR contains cluster and database configuration information for RAC and CRS, instance and

Voting Disk is a file on a shared cluster system; used to maintain cluster integrity or cluster membership.

CRS starts up first and at that time ASM is not up, so we can not have OCR and voting disk on ASM.
$CRS_HOME/log contains the alert log and

ASM – has its init.ora file which tells which instances it has.

Nodeapps : VIP/GSD/Listener/Oracle Notification Service (ONS).

VIP (virtual IP) – CRS resource associated with an ip address.

TAF – Transparent Application Failover

ASM

ASM stores the metadata that is required to make available the files stored within the ASM storage system to non-ASM oracle databases.

Two additional Processes : RBAL – Rebalancer Process to rebalance activity for ASM disk groups and ARBn – Actual rebalancer process to rebalance of data extent movements.

wmos=
(DESCRIPTION=
(LOAD_BALANCE=yes)
(FAILOVER=on)
(ADDRESS= (PROTOCOL=tcp)(HOST=wmsdevrac1-vip.acme.com)(PORT=1526))
(ADDRESS= (PROTOCOL=tcp)(HOST=wmsdevrac2-vip.acme.com)(PORT=1526))
(CONNECT_DATA=
(SERVICE_NAME=wmos)
(FAILOVER_MODE=
(TYPE=select)
(METHOD=basic))))

Overview of Transparent Application Failover
Uncommitted insert, update, and delete commands are rolled-back and must be resubmitted after reconnection. Again, use of the OCI packages should be utilized to have the DML operations reissued.

The Oracle Net process carries out TAF functionality. The failover is configured in the tnsnames file. The TAF settings are placed in the net service name area, within the connect_data section of the tnsnames, using the failover_mode and instance_role parameters.

Load Balancing

The listener connection load-balancing feature improves connection performance by balancing the number of active connections among multiple dispatchers and instances. In a single-instance environment, the listener selects the least-loaded dispatcher to handle the incoming client requests. In an Oracle Database 10g Real Application Clusters environment, connection load balancing also has the capability to balance the number of active connections among multiple instances. Due to dynamic service registration, a listener is always aware of all instances, and in the case of the multi-threaded server (MTS), a listener is aware of the dispatchers, regardless of their locations. Depending on the load information, a listener decides which instance, and if the multi-threaded server (shared server) is configured, it decides which dispatcher to send the incoming client request. In a MTS configuration, a listener selects a dispatcher in the following order: