TABLESPACE MONITORING
Unable to extend tablespace for database:


Step 1. Below query can be used for check the freespace in the tablespace

select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
round((1-(b.fsz/a.tsz))*100) "Pct Used”, round ((b.fsz/a.tsz)*100) "Pct Free" from
(select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
where tablespace_name ='TABLESPACE_NAME' group by tablespace_name) a,
(select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
where tablespace_name ='TABLESPACE_NAME' group by tablespace_name) b
Where a.tbl=b.tblsp;


Step 2: Below query can be used for get the file name and file size.


set pages 2000
set lines 120
col file_name for a65

select file_name, bytes/1024/1024 "Size (M)", maxbytes/1024/1024 "Max Size",autoextensible
from dba_data_files
where tablespace_name = upper('&tablespace')
order by substr(file_name,-7);


!df -k |grep dmx3 |sort


Step 3: Use below query to add the space and change the file system according to the requirement.



ttitle off
btitle off
set pagesize 0
set heading off
set feedback off
set verify off
set echo off
set linesize 240
set pause off

rem def tablespace = &&1
ACC tablespace CHAR PROMPT 'Enter tablespace:’


col prog_text format a70 newline

spool add_df

alter system set db_create_file_dest='/d1001/oradata/dwdevl';

alter tablespace &tablespace add datafile size 11m autoextend on next 10M maxsize 16001m;

Fix unusable indexes:

We can use below query to find unusable indexes:

col index_name for a30
col partition_name for a30
col tablespace_name for a30


PROMPT *******************************************
PROMPT UNUSABLE NON-PARTITIONED INDEXES
PROMPT *******************************************

select OWNER, INDEX_NAME, TABLESPACE_NAME, STATUS
from dba_indexes
where status = 'UNUSABLE';

PROMPT *******************************************
PROMPT UNUSABLE PARTITIONED INDEXES
PROMPT *******************************************

select INDEX_NAME, PARTITION_NAME, TABLESPACE_NAME, STATUS
from dba_ind_partitions
where status != 'USABLE'
order by 1,2;

Here is the syntax for rebuilding a non partitioned index:

alter index schema.index_name rebuild tablespace tablespace_name;

Note: replace schema.index_name and tablespace_name with the appropiate values

Here is the syntax for rebuild a partitioned index:

alter index schema.index_name rebuild partition partition_name tablespace tablespace_name;

Note: replace schema.index_name, tablespace_name, partition_name with the appropiate values



1. I use the following query to query to free rollback extents:

SELECT SEGMENT_TYPE, SEGMENT_NAME,TABLESPACE_NAME,
(MAX_EXTENTS - EXTENTS) FREE,
MAX_EXTENTS, EXTENTS, BLOCKS, BYTES
FROM DBA_SEGMENTS
WHERE SEGMENT_TYPE = 'ROLLBACK'
ORDER BY FREE;

2. Use the following query to find out that RBS tablespace is full:

select tbs.tablespace_name,
tot.bytes/1024 total,
tot.bytes/1024-sum(nvl(fre.bytes,0))/1024 used,
sum(nvl(fre.bytes,0))/1024 free,
(1-sum(nvl(fre.bytes,0))/tot.bytes)*100 pct,
decode(
greatest((1-sum(nvl(fre.bytes,0))/tot.bytes)*100, &max_pct),
&max_pct, '', '*'
) pct_warn
from dba_free_space fre,
(select tablespace_name, sum(bytes) bytes
from dba_data_files
group by tablespace_name) tot,
dba_tablespaces tbs
where tot.tablespace_name = tbs.tablespace_name
and fre.tablespace_name(+) = tbs.tablespace_name
group by tbs.tablespace_name, tot.bytes/1024, tot.bytes
order by 5, 1 ;

DAB_DATA_FILES

col tablespace_name format a20
col file_name format a45
col bytes format 9999999
select tablespace_name,file_name,bytes/1024/1024 from dba_data_files
where tablespace_name='TALLY_BIG_DATA';


DAB_FREESPACE

select tablespace_name,SUM(bytes)/1024/1024 from dba_free_space
where tablespace_name='SCBIDAT01' group by tablespace_name;

select index_name from dba_indexes where tablespace_name='SCBIDAT01';



Rebuild tablespace

select 'alter index '||owner||'.'||index_name||' rebuild tablespace PMFREQIDX01;' from dba_indexes where tablespace_name='PMFREQDAT01';


select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
(select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
where tablespace_name ='TALLY_BIG_DATA' group by tablespace_name) a,
(select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
where tablespace_name ='TALLY_BIG_DATA' group by tablespace_name) b
Where a.tbl=b.tblsp;

select 'alter database datafile '||''''||b.file_name||''''||' resize
'||round(((c.max_blockid+a.blocks) * 4096)/1024/1024)||'M ;'
from dba_extents a, dba_data_files b ,
(select file_id, max(block_id) max_blockid from dba_extents group by
file_id) c
where a.file_id=b.file_id
and a.file_id=c.file_id
and a.block_id = c.max_blockid
and b.file_name like lower('%&file_name%');

ALTER TABLESPACE PMFREQIDX01 COALESCE;

alter database datafile '/tstcpq/u011/oradata/tstcpq/sltc_pricedat0101.dbf' resize 1600M;

select index_name,table_name,tablespace_name from dba_indexes where owner='PMFREQ' AND INDEX_NAME='SYS_C00132902';

alter tablespace temp default storage(pctfree 0);

alter tablespace add datafile '/devl3/u09/oradata/devl3/jldl102.dbf' size 50M;

alter database datafile '/u33/oradata/matprod/enpidat11.dbf' resize 4000m;


!find /u*/oradata/matprod/

!ls /u24/oradata/matprodenpidat12.dbf

select segment_name from dba_ROLLBACK_segs where tablespace_name='RBS';

SELECT SEGMENT_NAME,TABLESPACE_NAME from dba_ROLLBACK_segs;

SELECT USN,EXTENTS FROM V$ROLLSTAT;

select tablespace_name,owner,SUM(BYTES)/1024/1024 from dba_segments where tablespace_name LIKE 'UCMDATA%' GROUP
BY TABLESPACE_NAME,OWNER;

SELECT DISTINCT TABLESPACE_NAME FROM DBA_SEGMENTS WHERE OWNER='UCM';

select tablespace_name from DBA_DATA_FILES WHERE TABLESPACE_NAME LIKE 'UCM%';

select object_type,count(*) from dba_objects where OWNER='UCM' group by object_type;
COL OWNER FOR A6
COL TABLESPACE_NAME FOR A10
SELECT OWNER,TABLESPACE_NAME,SEGMENT_TYPE,COUNT(*) FROM DBA_SEGMENTS
WHERE TABLESPACE_NAME LIKE '%UCM%'
GROUP BY OWNER,TABLESPACE_NAME,SEGMENT_TYPE;

SELECT 'ALTER INDEX UCM.'|| INDEX_NAME||' REBUILD TABLESPACE UCMINDEX;'
FROM DBA_INDEXES WHERE TABLESPACE_NAME='UCMDATA'; AND OWNER='UCM';


qiomkfile -e 51200k /glbake/u38/oradata/gltest/gl_balances_indx16.dbf

alter database datafile '/glbake/u38/oradata/gltest/gl_balances_indx16.dbf' resize 174144k;

alter tablespace MSDATADAT01 coalesce;

select snap_id,to_char(snap_time,'DD:MON:YYYY-HH24:MI') from stats$snapshot order by snap_id;


Adding space to ASM instance
alter tablespace x add datafile ‘+TIER2_DG’ size 128m autoextend on next 128m maxsize 32001m

Agent management in 10g

emctl start agent : Starts the Management Agent

emctl stop agent : Stops the Management Agent

emctl status agent: If the Management Agent is running, this command displays status information about the Management Agent, including the Agent Home, the process ID, and the time and date of the last successful upload to the Management Repository.


{orclprd}/root$ su - oracle
orclprd-> . oraenv
ORACLE_SID = [oracle] ? AGENT
orclprd-> emctl status agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
Agent Version : 10.2.0.3.0
OMS Version : 10.2.0.3.0
Protocol Version : 10.2.0.2.0
Agent Home : /u01/app/oracle/product/10.2.0/agent10g
Agent binaries : /u01/app/oracle/product/10.2.0/agent10g
Agent Process ID : 2642014
Parent Process ID : 4358362
Agent URL : https://orclprd:3872/emd/main/
Repository URL : https://swopapsu2-sv2:1159/em/upload
Started at : 2009-07-24 22:30:44
Started by user : oracle
Last Reload : 2009-08-06 04:02:22
Last successful upload : 2009-08-12 09:51:43
Total Megabytes of XML files uploaded so far : 7133.93
Number of XML files pending upload : 0
Size of XML files pending upload(MB) : 0.00
Available disk space on upload filesystem : 25.52%
Last successful heartbeat to OMS : 2009-08-12 09:51:47
---------------------------------------------------------------
Agent is Running and Ready

Archivelog space usage query

The folliwng query will give the usage details of FRA.

COLUMN DUMMY NOPRINT
COMPUTE SUM OF percent_space_used ON DUMMY
COMPUTE SUM OF number_of_files ON DUMMY
COMPUTE SUM OF percent_space_reclaimable ON DUMMY
BREAK ON DUMMY
SELECT null dummy, file_type, number_of_files,percent_space_used, percent_space_reclaimable
FROM v$flash_recovery_area_usage
order by 4 desc;

Finding Locks in a database (Stand-alone/RAC)

Select * from v$lock;
For RAC databases the GV$ and GV_$ views will show information on the inst_id


Set lines 136
Set pages 200
Select * from gv_$lock; *amended with ‘where type=’TX’;
Which session is the blocker and which sessions are waiting to get the lock?

This SELECT will return no rows if there are no waiters and blockers. You can
never find situations when there is only blocker(s) or waiter(s). One session
can block many others.

column Username format A15 column Sid format 9990 heading SID
column Type format A4 column Lmode format 990 heading 'HELD'
column Request format 990 heading 'REQ' column Id1 format 9999990
column Id2 format 9999990 break on Id1 skip 1 dup
SELECT SN.Username, M.Sid, M.Type,
DECODE(M.Lmode, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(Lmode,'990'))) Lmode,
DECODE(M.Request, 0, 'None', 1, 'Null', 2, 'Row Share', 3, 'Row
Excl.', 4, 'Share', 5, 'S/Row Excl.', 6, 'Exclusive',
LTRIM(TO_CHAR(M.Request, '990'))) Request,
M.Id1, M.Id2
FROM V$SESSION SN, V$LOCK M
WHERE (SN.Sid = M.Sid and M.Request ! = 0)
or (SN.Sid = M.Sid and M.Request = 0 and Lmode != 4 and (id1, id2)
in (select S.Id1, S.Id2 from V$LOCK S where Request != 0 and S.Id1
= M.Id1 and S.Id2 = M.Id2) ) order by Id1, Id2, M.Request;

USERNAME SID TY LMODE REQUEST ID1 ID2
---------------- ------- -- ------------- ------------- ---------- --------
SCOTT 9 TX Exclusive None 262154 117
SCOTT 11 TX None Exclusive 262154 117

Session 9 is blocking(LMODE=Exclusive)

Session 11 is waiting(REQUEST=Exclusive)

The meaning of ID1 and ID2 depends on the lock TYPE.

• We can see situations where a session is both a Blocker and a Waiter.

• If there are only two sessions and both are Blockers and Waiters then we
got a deadlock situation (which Oracle will solve automatically).

• A fast way to check blocking/waiting situations is to run:

SELECT * FROM v$lock WHERE block > 0 OR request > 0;

Frequently Used sqls:

1. Active_sessions.sql

SET LINESIZE 500
SET PAGESIZE 1000

COLUMN username FORMAT A15
COLUMN machine FORMAT A25
COLUMN logon_time FORMAT A20

SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
s.lockwait,
s.status,
s.module,
s.machine,
s.program,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
AND s.status = 'ACTIVE'
ORDER BY s.username, s.osuser;

2.inactive_sessions.sql

SET LINESIZE 132
SET PAGESIZE 1000

COLUMN username FORMAT A12
COLUMN s.osuser FORMAT A14
COLUMN logon_time FORMAT A15
column s.sid for 9999999
column p.spid for 9999999

SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
AND s.status = 'INACTIVE'
ORDER BY s.username, s.osuser,logon_time;

3.active_session1.sql

SET LINESIZE 132
SET PAGESIZE 1000

COLUMN username FORMAT A8
COLUMN OSUSER for a8
COLUMN logon_time FORMAT A20
column s.sid for 9999999
column p.spid for 9999999

SELECT NVL(s.username, '(oracle)') AS username,
s.osuser,
s.sid,
s.serial#,
p.spid,
TO_CHAR(s.logon_Time,'DD-MON-YYYY HH24:MI:SS') AS logon_time
FROM v$session s,
v$process p
WHERE s.paddr = p.addr
AND s.status = 'ACTIVE'
ORDER BY s.username, s.osuser,logon_time;

4.longops_rac.sql

SET LINESIZE 132
COLUMN sid FORMAT 9999
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10

SELECT s.inst_id,
s.sid,
s.serial#,
s.username,
s.module,
ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM gv$session s,
gv$session_longops sl
WHERE s.sid = sl.sid
AND s.inst_id = sl.inst_id
AND s.serial# = sl.serial#;

5.top_sqls.sql

-- Description : Displays a list of SQL statements that are using the most resources.

SET LINESIZE 500
SET PAGESIZE 1000
col sql_text for a300
SET VERIFY OFF

SELECT *
FROM (SELECT Substr(a.sql_text,1,500) sql_text,
Trunc(a.disk_reads/Decode(a.executions,0,1,a.executions)) reads_per_execution,
a.buffer_gets,
a.disk_reads,
a.executions,
a.sorts,
a.address
FROM v$sqlarea a
ORDER BY 2 DESC)
WHERE rownum <= 5;

SET PAGESIZE 14

6.locked_objects.sql

SET LINESIZE 500
SET PAGESIZE 1000
SET VERIFY OFF

COLUMN owner FORMAT A20
COLUMN username FORMAT A20
COLUMN object_owner FORMAT A20
COLUMN object_name FORMAT A30
COLUMN locked_mode FORMAT A15

SELECT b.session_id AS sid,
NVL(b.oracle_username, '(oracle)') AS username,
a.owner AS object_owner,
a.object_name,
Decode(b.locked_mode, 0, 'None',
1, 'Null (NULL)',
2, 'Row-S (SS)',
3, 'Row-X (SX)',
4, 'Share (S)',
5, 'S/Row-X (SSX)',
6, 'Exclusive (X)',
b.locked_mode) locked_mode,
b.os_user_name
FROM dba_objects a,
v$locked_object b
WHERE a.object_id = b.object_id
ORDER BY 1, 2, 3, 4;

SET PAGESIZE 14
SET VERIFY ON

7.file_io.sql

SET PAGESIZE 1000

SELECT Substr(d.name,1,50) "File Name",
f.phyblkrd "Blocks Read",
f.phyblkwrt "Blocks Writen",
f.phyblkrd + f.phyblkwrt "Total I/O"
FROM v$filestat f,
v$datafile d
WHERE d.file# = f.file#
ORDER BY f.phyblkrd + f.phyblkwrt DESC;

SET PAGESIZE 18

8.db_links.sql

SET LINESIZE 132
COLUMN db_link FORMAT A30
COLUMN host FORMAT a20
COLUMN owner FORMAT a10
COLUMN USERNAME FORMAT a10
set pagesize 200


SELECT owner,
db_link,
username,
host
FROM dba_db_links
ORDER BY owner, db_link;

9.db_properties.sql
SET PAGESIZE 200
SET LINESIZE 132
COLUMN property_value FORMAT A30
COLUMN property_name FORMAT A40


SELECT property_name, property_value FROM database_properties ORDER BY property_name;

10.dispatchers.sql

SET LINESIZE 500
SET PAGESIZE 1000
COLUMN a.status for a8
SET VERIFY OFF

SELECT a.name "Name",
a.status "Status",
a.bytes "Total Bytes",
a.idle "Total Idle Time",
a.busy "Total Busy Time",
Round(a.busy/(a.busy + a.idle),2) "Load"
FROM v$dispatcher a ORDER BY 1;

SET PAGESIZE 14
SET VERIFY ON

11. tablespace_monitoring.sql

select a.tbl "Name",a.tsz "Total Size",b.fsz "Free Space",
round((1-(b.fsz/a.tsz))*100) "Pct Used",round((b.fsz/a.tsz)*100) "Pct Free" from
(select tablespace_name tbl,sum(bytes)/1024/1024 TSZ from dba_data_files
group by tablespace_name) a,
(select tablespace_name tblsp,sum(bytes)/1024/1024 FSZ from dba_free_space
group by tablespace_name) b
Where a.tbl=b.tblsp
order by 4;

12. highly_accessed_tables.sql

select c.owner, c.name name, t.buffer_pool, c.executions, c.loads, s.bytes/1024kb,t.tablespace_name tablespace
from dba_tables t,
dba_segments s,
v$db_object_cache c
where c.type = 'TABLE'
and c.loads > 100
and c.executions >100
and c.name = t.table_name
and c.owner = t.owner
and c.name = s.segment_name
and c.owner = s.owner;

13. invalid_objects.sql

col owner for a10
set pagesize 100
set linesize 132

COLUMN object_name FORMAT A30
SELECT owner,
object_type,
object_name,
status
FROM dba_objects
WHERE status = 'INVALID'
ORDER BY owner, object_type, object_name;

14. sid_sqltext.sql

set linesize 132
set pagesize 200
col status for a10
col schemaname for a12
col OSUSER for a10
col PROGRAM for a18


SELECT s.sid,
s.status,
s.process,
s.schemaname,
s.osuser,
p.program
FROM v$session s,
v$sqlarea a,
v$process p
WHERE s.SQL_HASH_VALUE = a.HASH_VALUE
AND s.SQL_ADDRESS = a.ADDRESS
AND s.PADDR = p.ADDR;

set pagesize 200
set linesize 132
SELECT s.sid,a.sql_text,p.program
FROM v$session s,
v$sqlarea a,
v$process p
WHERE s.SQL_HASH_VALUE = a.HASH_VALUE
AND s.SQL_ADDRESS = a.ADDRESS
AND s.PADDR = p.ADDR;

col username for a15
col MACHINE for a30
col status for a10
set linesize 132

select username,MACHINE,count(1),status from v$session group by username,machine,status order by 3;
select username,MACHINE,count(1) from v$session group by username,machine orderby 3;

1. Finding Volume group

{p0104crmp05}/home/sdtsldba$ lsvg
rootvg
sh_crmp05_vg2
hb_crmp05_vg1
hb_crmp06_vg1
sh_tmspdb_203
altinst_rootvg

2. Finding the file system in which volume group
{p0104crmp05}/home/sdtsldba$ lsvg -l sh_tmspdb_203


3. Size of the file system

{p01102sbap03}/home/oracle$ lslv ssp_jx_data17
LOGICAL VOLUME: ssp_jx_data17 VOLUME GROUP: orascanlive_206
LV IDENTIFIER: 0031f1df00004c00000001122e2daed5.25 PERMISSION: read/write
VG STATE: active/complete LV STATE: opened/syncd
TYPE: raw WRITE VERIFY: off
MAX LPs: 512 PP SIZE: 128 megabyte(s)
COPIES: 2 SCHED POLICY: parallel
LPs: 112 PPs: 224
STALE PPs: 0 BB POLICY: relocatable
INTER-POLICY: minimum RELOCATABLE: yes
INTRA-POLICY: middle UPPER BOUND: 2
MOUNT POINT: N/A LABEL: None
MIRROR WRITE CONSISTENCY: off
EACH LP COPY ON A SEPARATE PV?: yes (superstrict)
Serialize IO? NO


Size: SSP IDX Actual 112x128= 14336M

Operating System commands

The following commands are a useful snapshot and quick ref for determining problems within oracle from a operating system point of view: - for example if you find process hanging such as a crsd or listener search for the pid and then piping the following commands to a trace file would provide invaluable information for Oracle Support to provide RCA Root Cause Analysis

1. Ps –ef | grep : List information of the pid – this can be used to determine a session with in the database via gv$session or find the identity of the top process from topas

2. Procmap –l : Prints the address space map of a process, this will also show the read/write/execute permissions of the library’s that are particular oracle process is using

3. Procstack : Displays the hexadecimal address and symbolic names for each stack fromes of the current thread in processes

4. Truss –o -f : Think of truss as a utility that allows you to monitor a process or command and how the system uses that process or command to call something. Invaluable to Oracle support if a processes is hanging as it may show where the error is

5. Ps –f : Show the processes currently running including cpu information

6. Proctree –a : The proctree command prints the process tree containing the specified process id’s or users – this includes any child processes.

7. Topas : This invokes the top utility to list the current performance metrics of a system

8. ps -ef | egrep "crsd|ocssd|evmd|oprocd" : Check if CRS is running

To determine the physical RAM size, enter one of the following commands:

1.Platform Command AIX # /usr/sbin/lsattr -E -l sys0 -a realmem

2.HP-UX # /usr/sbin/dmesg | grep "Physical:"

3.Linux # grep MemTotal /proc/meminfo

4.Solaris # /usr/sbin/prtconf | grep "Memory size"

5.Tru64 UNIX # /bin/vmstat -P | grep "Total Physical Memory"

1>rcp -r mqprod/* gemsdevp01:/mon01/app/oracle/admin/mqprod/.
2>rcp gems28:/u23/oradata/r201dev1/gl_ifh03.dbf /u07/oradata/glintst/.


3>awk '{print $2}' file_name
--->for getting the 2nd field from the file

4>cat file_name |cut -d "/" -f4 >new_file
--->it cuts the file as per the delimiter /

5>paste -d "\0" file1 file2 >file3
--->pastes the two files without the any gap between them.

6>egrep '\.log|\.dbf' file_name
--->for getting the lines containing the specified format.

7>egrep '\.log|\.dbf' file_name |cut f2 -d "'"
--->cut the specified lines as per the delimiter ' from the given file

chmod g+w file_name (or) dir_name --->to chang the any file permission to the group or any other
permission --- r,w,x
users owner,group,others
+ for giving the permission
- for removing the permission

10>sort according to the required field number
sort +3n -t:
Where 3 is field number in the file_name.

sort -t/ +4 ----------to sort the file contents from $th field onwards.

11)paste -d " " file1 file2 ---- to map the two files.
paste -d"\0" rcp_mqtp_mqtt.sh test
NLS_LANG=AMERICAN_AMERICA.UTF8

12)cat source* |cut -d"/" -f7

13)date +%j-%y

14)cat cold_db.sh |awk -F/ '{print $2"/.snapshot/ivrp/"$3"/"$4"/"$5}'

15) du -sk ---> to find the total space occupied by internal directories & files in a directory.

16) set disk_usage = `df -k . | tail -1 | cut -f8 -d" " | cut -b1,2`

df -kl|tr -s ' '|cut -f6 -d ' '

1.mailx -s "$ORACLE_SID Dynamic Pin ERROR" "thirupal.boreddy@thiru.com"

2.cat xy.log xy.html | mailx -s "Objects list" "Thirupal.Boreddy@thiru.com"

3.cat /tmp/thiru/xy.log | mailx -s "Objects list" "Thirupal.Boreddy@thiru.com"

4.uuencode /tmp/thiru/tally_objects_6.log xx.txt| mailx -s "Objects list" "Thirupal.Boreddy@thiru.com"

5.uuencode pwmb1s_ora_23076.trc pwmb1s_ora_23076.txt |mailx -s "Result" "Thirupal.Boreddy@thiru.com"

6.uuencode alert_psaf1.log alert_psaf1.txt |mailx -s "Alertlogfile" "Thirupal.Boreddy@thiru.com"

7.uuencode psaf1_j000_13854.trc psaf1_j000_13854.txt |mailx -s "tracefile" "Thirupal.Boreddy@thiru.com"

TAR COMMANDS
------------

1. tar cvf tarflnm * ---> copy of files to target files --

A.tar cvf /u97/orabackup/tar/817.tar *
B.tar cvf /u92/oracle/wissen/gldbs/test/dev6itarfile/gen.tar *
C.tar cvf /tmp/p8172/old817.tar *

2. tar rvf ---> append to existing target file

3. tar xvf --->untar of the file in target

4. tar cf - 8.0.6 | remsh srvr27d '(cd /u01/app/oracle/product/; tar xf - )'

5. tar tvf --> for listing files in target filename

6. tar cvf /g10/backup/oracle734.tar *

cvf -------->commond for making the file to tar

srvr28.rar------> tar file name(all files are tared in to this file)

* ------> indicates all files in this directory

7. tar tvf srvr28.tar -----------> To list the tared files

Untar of the file in target directory
-------------------------------------
1. tar xvf srvr28.tar
2. tar xvf 8172_solaris_32_patchset.tar
3. tar xvf 81730_Solaris32_Release.tar

UNIX COMMANDS:

1.To Check bit size :
-------------------------

f7p21-03:5.9:tbrdev1> /bin/isainfo -kv
64-bit sparcv9 kernel modules

2.To check oracle software bit size:
-----------------------------------------
$ORACLE_HOME/bin/file oracle

3. To check Memory size:
----------------------------------

Ensure database server has at lease 512 MB of RAM.
/usr/sbin/prtconf | grep "Memory size"
Memory size: 1792 Megabytes


4. To change oracle password:
-----------------------------------
passwd oracle


5. To check for these executables, enter the following:
------------------------------------------------------------
$ /usr/bin/which "command"

echo "\n set head off pages 0 feed off;\n select name from v\$database;" | sqlplus -s system/oraTest1

echo "\n set head off pages 0 feed off;\n select name from v\$database;" | sqlplus -s system/oraTest1


Find Command Options
--------------------

1.find . -name "*" -exec \ls -ltr {} \; |awk '{sum += $5} END {print sum}'
2.find /u99/oradata/arch/colb -mtime +3 -exec \rm {} \;
3.find *arc -mtime +3 -exec \ls -ltr {} \;
4.find /$SERVER -name oradata -exec chown oracle:dba {} \;
5.find /gemsora15d/exp01/devswb/ -name devswb*.log -type f -mtime +2 -follow -exec echo Removing file: {} ; -exec /usr/bin/rm -f {} ;
6.find ${ORACLE_ADMIN}/${ORACLE_SID}/cdump -name "core_*" -mtime +14 -exec rm -rf {} \; chown oracle:dba /opt/oracle

CRSCTL CheatSheet:
You can find below various commands which can be used to administer Oracle Clusterware using crsctl. This is for purpose of easy reference.

1.Start Oracle Clusterware
#crsctl start crs

2.Stop Oracle Clusterware#crsctl stop crs

3.Enable Oracle Clusterware

#crsctl enable crs

It enables automatic startup of Clusterware daemons

4.Disable Oracle Clusterware

#crsctl disable crs

It disables automatic startup of Clusterware daemons. This is useful when you are performing some
operations like OS patching and does not want clusterware to start the daemons automatically.

5. Checking Voting disk Location

$crsctl query css votedisk
0. 0 /dev/sda3
1. 0 /dev/sda5
2. 0 /dev/sda6

Located 3 voting disk(s).
Note: -Any command which just needs to query information can be run using oracle user. But anything which alters Oracle Clusterware requires root privileges.

6.Add Voting disk

#crsctl add css votedisk path

7.Remove Voting disk

#crsctl delete css votedisk path

8.Check CRS Status

$crsctl check crs
Cluster Synchronization Services appears healthy
Cluster Ready Services appears healthy
Event Manager appears healthy

9.You can also see particular daemon status

$crsctl check cssd

10.Cluster Synchronization Services appears healthy

$crsctl check crsd

11.Cluster Ready Services appears healthy

$crsctl check evmd
Event Manager appears healthy

12.You can also check Clusterware status on both the nodes using

$crsctl check cluster
prod01 ONLINE
prod02 ONLINE

13.Checking Oracle Clusterware Version
To determine software version (binary version of the software on a particular cluster node) use

14.$crsctl query crs softwareversion

Oracle Clusterware version on node [prod01] is [11.1.0.6.0]

For checking active version on cluster, use

$ crsctl query crs activeversion

Oracle Clusterware active version on the cluster is [11.1.0.6.0]

As per documentation, multiple versions are used while upgrading.
There are other options for CRSCTL too which can be seen using

$crsctl
Or

$crsctl help
crsctl query css votedisk

1.What is an OCRCHECK utility?

An ocrcheck utility is a diagnostic tool used for diagnosing OCR(Oracle Cluster Registry) Problems. This is used to verify the Oracle Cluster Registry(OCR) integrity.

2.What does an ocrcheck display?

The OCRCHECK utility displays the version of the OCR’s block format, total space available and used space, OCRID, and the OCR locations that we have configured.

3.How does ocrcheck perform integrity check?

OCRCHECK performs a block-by-block checksum operation for all of the blocks in all of the OCRs that we have configured. It also returns an individual status for each file as well as a result for the overall OCR integrity check.

4.Give a sample output of ocrcheck utility:-
Sample of the OCRCHECK utility output:

Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262144
Used space (kbytes) : 16256
Available space (kbytes) : 245888
ID : 1918913332
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

5.Where does an ocrcheck utility create a log file?

OCRCHECK creates a log file in the directory
CRS_home/log/hostname/client.

6.How can we change the amount of logging?
To change amount of logging, edit the file
CRS_home/srvm/admin/ocrlog.ini.

LABELS: OCRCHECK UTILITY

{orclprd001}/home/oracle$ ocrcheck
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 261916
Used space (kbytes) : 24276
Available space (kbytes) : 237640
ID : 1338974581
Device/File Name : /dev/p2_ocrdisk_1
Device/File integrity check succeeded
Device/File Name : /dev/p3_ocrdisk_1
Device/File integrity check succeeded

Cluster registry integrity check succeeded

Job scheduling from Oracle 10g with dbms_scheduler
In Oracle 10g the DBMS_JOB package is replaced by the DBMS_SCHEDULER package. The DBMS_JOB package is now depricated and in Oracle 10g it's only provided for backward compatibility. From Oracle 10g the DBMS_JOB package should not be used any more, because is could not exist in a future version of Oracle.

With DBMS_SCHEDULER Oracle procedures and functions can be executed. Also binary and shell-scripts can be scheduled.


Rights

If you have DBA rights you can do all the scheduling. For administering job scheduling you need the priviliges belonging to the SCHEDULER_ADMIN role. To create and run jobs in your own schedule you need the 'CREATE JOB' privilege.

With DBMS_JOB you needed to set an initialization parameter to start a job coordinator background process. With Oracle 10g DBMS_SCHEDULER this is not needed any more.

If you want to user resource plans and/or consumer groups you need to set a system parameter:

ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;


Getting started quickly
To quickly get a job running, you can use code like this:

begin

dbms_scheduler.create_job(
job_name => 'DEMO_JOB_SCHEDULE'
,job_type => 'PLSQL_BLOCK'
,job_action => 'begin package.procedure(''param_value''); end; '
,start_date => '01/01/2006 02:00 AM'
,repeat_interval => 'FREQ=DAILY'
,enabled => TRUE
,comments => 'Demo for job schedule.');
end;
/

This schedules a pl/sql block to be executed daily starting 1/1/2006 02:00 AM.

You can schedule things like this, but DBMS_SCHEDULER can reuse components.

You can build a schedule using components like program, schedule, job, job class and window. We will now discuss these components in detail.

Program

The program component represents program-code that can be executed. This program code can have parameters. Code example


begin
dbms_scheduler.create_program (
program_name => 'DEMO_JOB_SCHEDULE'
,program_type => 'STORED_PROCEDURE'
,program_action => 'package.procedure'
,number_of_arguments => 1
,enabled => FALSE
,comments => 'Demo for job schedule.');


dbms_scheduler.define_program_argument (
program_name => 'DEMO_JOB_SCHEDULE'
,argument_position => 1
,argument_name => 'kol1'
,argument_type => 'VARCHAR2'
,default_value => 'default'
);
dbms_scheduler.enable(name => 'DEMO_JOB_SCHEDULE');
end;
/


The parameter program_type can have one of the following values: 'PLSQL_BLOCK', 'STORED_PROCEDURE','EXECUTABLE'.

dbms_scheduler also allows to execute shell scripts (Windows: *.bat files) and executables.


Schedule

A schedule defines the frequence and date/time specifics of the start-time for the job.

example code

begin

dbms_scheduler.create_schedule(
schedule_name => 'DEMO_SCHEDULE'
, start_date => '01/01/2006 22:00:00'
, repeat_interval => 'FREQ=WEEKLY'
, comments => 'Weekly at 22:00');
END;
/


To drop the schedule:

begin

dbms_scheduler.drop_schedule(
schedule_name => 'DEMO_SCHEDULE'
, force => TRUE );
end;
/

Calendar expresions can have one of these values: 'Yearly','Monthly','Weekly','Daily','Hourly','Minutely','Secondely'


Job

A job defines when a specific task will be started. This can be done by assigning a program to one or more schedules (or to a specific date/time). A job can belong to only 1 job class. Code example

begin

dbms_scheduler.create_job(
job_name => 'DEMO_JOB1'
, program_name =>'DEMO_JOB_SCHEDULE'
, schedule_name =>'DEMO_SCHEDULE'
, enabled => FALSE
, comments => 'Run demo program every week at 22:00');
dbms_scheduler.set_job_argument_value(
job_name => 'DEMO_JOB1'
, argument_position => 1
, argument_value => 'param1');
dbms_scheduler.enable('DEMO_JOB1');
commit;
end;

/


Or start shell script


begin

dbms_scheduler.create_job

(

job_name => 'RUN_SHELL1',
schedule_name => 'DEMO_SCHEDULE',
job_type => 'EXECUTABLE',
job_action => '/home/test/run_script.sh',
enabled => true,
comments => 'Run shell-script'
);
end;
/


Monitoring job-scheduling

Jobs can be monitored using Oracle Enterprise Manager 10g. It's also possible to use a number of views that have been created in Oracle 10g. We will discuss some of these views here.


To show details on job run:


select log_date
, job_name
, status
, req_start_date
, actual_start_date
, run_duration
from dba_scheduler_job_run_details


To show running jobs:

select job_name
, session_id
, running_instance
, elapsed_time
, cpu_used
from dba_scheduler_running_jobs;

To show job history:

select log_date
, job_name
, status
from dba_scheduler_job_log;


show all schedules:

select schedule_name, schedule_type, start_date, repeat_interval from dba_scheduler_schedules;

show all jobs and their attributes:

select * from dba_scheduler_jobs

show all program-objects and their attributes

select * from dba_scheduler_programs;

show all program-arguments:

select * from dba_scheduler_program_args;

Oracle 10g - Setting up Flash Recovery Area (FRA) / flashback database


1. Ensure database is in archivelog mode


SELECT LOG_MODE FROM V$DATABASE



2. Create 'FRA' directory on appropriate mountpoint - mkdir fra



3. Set FRA size


ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE= SCOPE=SPFILE;


4. Set FRA location


ALTER SYSTEM SET DB_RECOVERY_FILE_DEST='' SCOPE=SPFILE;


5. Bounce the database


SHUTDOWN IMMEDIATE (wait for shutdown)


STARTUP


6. Check parameters have changed


SHOW PARAMETER RECOVERY


NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

db_recovery_file_dest string /data/example/oracle/SID/oradata_06/fra

db_recovery_file_dest_size big integer 3G


7. Once parameters have been checked, shutdown the database and startup in mount state


SHUTDOWN IMMEDIATE (wait for shutdown)


STARTUP MOUNT


8.Turn flashback on


ALTER DATABASE FLASHBACK ON


9. Open the database


ALTER DATABASE OPEN


10. Check flashback is on


SELECT FLASHBACK_ON FROM V$DATABASE


FLASHBACK_ON

------------------

YES


11. Test flashback database – BE AWARE, A TEST WILL REQUIRE AN 'ALTER DATABASE OPEN RESETLOGS' COMMAND


- sqlplus '/ as sysdba'


- set time on


- Create a table (flash_thirupal for example)

create table flash_thirupal (

a number,

b varchar2(10)

c varchar2(40)

)

tablespace SYSTEM

- Insert some records into the test table


INSERT INTO flash_test VALUES (1, 'ABC','THIRUPAL');

INSERT INTO flash_test VALUES (2, 'DEF','BOREDDY');


- Commit the changes and note the time


- SELECT * FROM FLASH_TEST - should output the records just inserted


- shutdown immediate

- startup mount



- Flashback the database to a time before the table was created –


FLASHBACK DATABASE TO TIMESTAMP TO_TIMESTAMP('2008-07-24 10:00:00', 'YYYY - MM-DD HH24:MI:SS')

- ALTER DATABASE OPEN RESETLOGS

- SELECT * FROM FLASH _TEST - table should not exist


How far can I flash the database back?


Below is a useful bit of script for finding out the date and time the database can be flashed back to, the retention target (mins), the size of the current flashback area (mb) and the estimated amount of flashback logs needed to meet the retention target (mb).

Set line 160


select to_char(oldest_flashback_time, 'dd/mm/yyyy hh24:mi:ss') oldest_time, retention_target,
sum((flashback_size)/1024/1024) flashback_size_mb,
sum((estimated_flashback_size)/1024/1024) estimated_flashback_size_mb
from v$flashback_database_log
group by to_char(oldest_flashback_time, 'dd/mm/yyyy hh24:mi:ss'), retention_target



This will also indicate whether space needs to be added to the flashback area in order to perform 'flashback database' to the full extent of the retention target. For example, if your retention target is 24 hours and the 'oldest_time' is only 10 hours ago, you will need to add space to the fra or reduce the retention target.


Example of output…
OLDEST_TIME RETENTION_TARGET FLASHBACK_SIZE_MB ESTIMATED_FLASHBACK_SIZE_MB
------------------- ---------------- ----------------- ---------------------------
13/08/2008 21:02:01 360 2675.75 1095.2168


In the example output above it is showing that the database can be flashed back to 13/08/08 at 21:02pm, the retention target is 6 hours, the amount of flashback data currently in the FRA is 2675mb and the estimated amount of flashback data we need to flashback the database 6 hours is 1095mb. These figures mean it is possible to perform flashback database and recover the database to the defined target, and if necessary it can be taken back even further (current date/time is 14/08/08 9:30am so it can be flashed back 12.5 hours approx.)



Viewing FRA stats
set line 300

select to_char(begin_time, 'dd/mm/yyyy hh24:mi:ss') begin_time, to_char(end_time, 'dd/mm/yyyy hh24:mi:ss') end_time,
sum((flashback_data)/1024/1024) flash_data_mb, redo_data as redo_logs_generated, db_data, sum((estimated_flashback_size)/1024/1024) estimated_flashback_size_mb
from v$flashback_database_stat
group by to_char(begin_time, 'dd/mm/yyyy hh24:mi:ss'), to_char(end_time, 'dd/mm/yyyy hh24:mi:ss'), db_data, redo_data
order by begin_time desc

This script outputs certain stats about the FRA which are broken down on an hour-to-hour basis (approximately). The stats are:

- The time interval the stats relate to (beginning and end time)

- The amount of flashback data written (mb)

- The amount of redo logs generated (bytes)

- Amount of database data read and written during the interval (bytes)

- The estimated flashback size (as in the 'How far can I flashback' section)


The example output shows there is a significantly higher amount of flashback data being written to the FRA between 22:32 and 23:32 compared to 2:32 and 3:32 – this kind of information can be useful when configuring your FRA and when reviewing sizes and retention targets.


Information relating to RAC systems

To enable flashback database in a RAC database environment, the database must be started in exclusive before flashback database is enabled. To do so, perform the following steps via SQL*PLUS:

1. Disable clustering:

SQL> alter system set CLUSTER_DATABASE=FALSE scope=spfile sid='';

2. Shutdown immediate all instances

3. From instance where clustering is disabled, mount the database:

SQL> Startup mount;

4. If database is NOT in archivelog mode, issue:

SQL> alter database archivelog;

5. Turn on flashback database:

SQL> alter database flashback on;

6. Reenable clustering:

SQL> alter system set CLUSTER_DATABASE=TRUE scope=spfile sid='';

7. Shutdown database:

SQL> shutdown immediate;

8. Open all the RAC database instances

Active Session History (ASH)

Oracle 10gr2 Introduce new option for capture performance problem.

ASH >>> Active session History <<<<

What is ASH ?

Whenever you want to know information about blocker and waiter identifiers and their associated transaction IDs and SQL.

About V$ACTIVE_SESSION_HISTORY

1. The V$ACTIVE_SESSION_HISTORY view provides sampled session activity in the instance.

2. Active sessions are sampled every second and are stored in a circular buffer in SGA.

3. Any session that is connected to the database and is waiting for an event that does not belong to the Idle wait class is considered as an active session.

4. This includes any session that was on the CPU at the time of sampling.

5. Each session sample is a set of rows and the V$ACTIVE_SESSION_HISTORY view returns one row for each active session per sample, returning the latest session sample rows first. Because the active session samples are stored in a circular buffer in SGA, the greater the system activity, the smaller the number of seconds of session activity that can be stored in the circular buffer. This means that the duration for which a session sample appears in the V$ view, or the number of seconds of session activity that is displayed in the V$ view, is completely dependent on the database activity.

6. Using the Active Session History enables you to examine and perform detailed analysis on both current data in the V$ACTIVE_SESSION_HISTORY view

7. Historical data in the DBA_HIST_ACTIVE_SESS_HISTORY view,

SQL identifier of SQL statement

.What information provide ASH view?

1. Object number, file number, and block number

2. Wait event identifier and parameters

3. Session identifier and session serial number

4. Module and action name

5. Client identifier of the session

6. Service hash identifier

How to generate ASH report?

It is New feature of 10GR2(10.2.0.1.0)

For report creation we have to use ASHRPT.SQL script.

Located: In $ORACLE_HOME/rdbms/admin folder

How to run ASHRPT.SQL script

To generate a text report of ASH information, run the ashrpt.sql script at the SQL prompt:

@$ORACLE_HOME/rdbms/admin/ashrpt.sql

/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/ashrpt.sql

First, you need to specify whether you want an HTML or a text report.

Enter value for report_type: text

Specify the time frame to collect ASH information by first specifying the begin time in minutes prior to the system date.

Enter value for begin_time: -10

Note: here you have to just put number in minutes eg: 10 for 10 minutes

Next, enter the duration in minutes that the report for which you want to capture ASH information from the begin time. The default duration of system date minus begin time is accepted in the following example:

Enter value for duration:

Note: left blank for default value. Default value is SYSDATE

The report in this example will gather ASH information beginning from 10 minutes before the current time and ending at the current time. Next, accept the default report name or enter a report name. The default name is accepted in the following example:

Enter value for report_name:

Using the report name ashrpt_1_0310_0131.txt

Note: Left it blank for default value.

The session history report is generated.