Find Unindexes FK Constraints

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

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

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

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

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

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

Launch the installer with tracing turned on

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

How to Check OCR and Voting Disk

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

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

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

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 Point in Time Restore (PITR)

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

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

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

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

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

How to find system resource utilization in Linux

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

Kernel Parameters for RedHat Linux

$ 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