- Oracle Database
instance is running or not
SQL> select status from v$instance;
- To shows the
time that the current instance was started.
SQL>SELECT
TO_CHAR(STARTUP_TIME,'MON-DD-RR HH24:MI:SS') AS "Inst Start Time"
FROM V$INSTANCE;
- Check Datafiles
SQL> SELECT NAME
FROM V$DATAFILE;
-Look for any new
alert log entries
SQL> select value
from v$parameter where name = 'background_dump_dest';
SQL> show
parameter background_dump_dest;
VALUE
----------------------------------------------------------------------------------------------------
C:\ORACLE\ORACLE\PRODUCT\10.2.0\ADMIN\SRBDB\BDUMP
-Track Tablespace
Used/Free Space:
SQL>SELECT /* +
RULE */ df.tablespace_name
"Tablespace", df.bytes / (1024
* 1024) "Size (MB)",
SUM(fs.bytes) / (1024 * 1024) "Free
(MB)", Nvl(Round(SUM(fs.bytes) * 100 / df.bytes),1) "% Free",
Round((df.bytes - SUM(fs.bytes)) * 100 / df.bytes) "% Used"
FROM dba_free_space fs, (SELECT
tablespace_name,SUM(bytes) bytes
FROM dba_data_files
GROUP BY tablespace_name) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY df.tablespace_name,df.bytes
UNION ALL
SELECT /* + RULE */
df.tablespace_name tspace,
fs.bytes / (1024 * 1024),
SUM(df.bytes_free) / (1024 * 1024), Nvl(Round((SUM(fs.bytes) - df.bytes_used) *
100 / fs.bytes), 1), Round((SUM(fs.bytes) - df.bytes_free) * 100 / fs.bytes)
FROM dba_temp_files fs, (SELECT
tablespace_name,bytes_free,bytes_used
FROM v$temp_space_header
GROUP BY
tablespace_name,bytes_free,bytes_used) df
WHERE fs.tablespace_name (+) = df.tablespace_name
GROUP BY
df.tablespace_name,fs.bytes,df.bytes_free,df.bytes_used
ORDER BY 4 DESC;
- Track DB datafile
used and free space:
SELECT SUBSTR
(df.NAME, 1, 40) file_name,dfs.tablespace_name, df.bytes / 1024 / 1024
allocated_mb, ((df.bytes / 1024 / 1024) -
NVL (SUM (dfs.bytes) / 1024 / 1024, 0)) used_mb,
NVL (SUM (dfs.bytes)
/ 1024 / 1024, 0) free_space_mb
FROM v$datafile df,
dba_free_space dfs
WHERE df.file# =
dfs.file_id(+)
GROUP BY dfs.file_id,
df.NAME, df.file#, df.bytes,dfs.tablespace_name
ORDER BY file_name;
-Monitor CPU Usage
Information:
SQL>select ss.username, se.SID, VALUE/100
cpu_usage_seconds
from v$session
ss, v$sesstat se, v$statname sn where se.STATISTIC# =
sn.STATISTIC#
and NAME like '%CPU
used by this session%' and se.SID = ss.SID
and ss.status='ACTIVE' and ss.username is not null order by VALUE desc;
-Track Logon time of
DB user and OS user:
SQL>Select
to_char(logon_time,'dd/mm/yyyy hh24:mi:ss'),osuser,status,schemaname,machine
from v$session where type !='BACKGROUND';
- Check the current
session and processes limits
SQL> show
parameter session
SQL> show
parameter processes
- Increase the
session and processes limits to something sensible, the important thing here is
to choose values which solve your particular issue. I tend to find that
anything around 250
is fine for most development projects, but remember that the higher the values
the more resources Oracle will try and consume when it needs to
maintain those extra
sessions and processes.
SQL> alter system
set sessions=300 scope=spfile;
System altered.
SQL> alter system
set processes=300 scope=spfile;
System altered.
4: Shutdown the
Database instance.
SQL> shutdown
immediate
Database closed.
Database dismounted.
ORACLE instance shut
down.
- Check Pfile
SQL> show
parameter pfile;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
spfile string C:\ORACLE\ORACLE\PRODUCT\10.2.
0\DB_1\DBS\SPFILESRBDB.ORA
- You can change the
size for shared pool with scope=memory and alos configure the large pool
SQL> show
parameter pfile
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
spfile string
SQL> show
parameter shared;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 5M
shared_pool_size big integer 0
shared_server_sessions integer
shared_servers integer 1
SQL> alter system
set shared_pool_size=10m scope=memory;
System altered.
SQL> show
parameter shared;
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
hi_shared_memory_address integer 0
max_shared_servers integer
shared_memory_address integer 0
shared_pool_reserved_size big integer 5M
shared_pool_size big integer 12M
shared_server_sessions integer
shared_servers integer 1
SQL> alter system
set large_pool_size=10m scope=memory;
System altered.
SQL> show
parameter large
NAME TYPE VALUE
------------------------------------
----------- ------------------------------
large_pool_size big integer 12M
- Verify DBSNMP is
running
SQL> select
username from v$session;
- Verify success of
database backup
- Verify success of
database archiving to DR.
- Verify enough
resources for acceptable performance (RAM Processor etc)
- Verify free space
in tablespaces.
SQL>SELECT r.tablespace_name,
reserved_space "RESERVED_SPACE(MB)",
reserved_space - free_space "USED_SPACE(MB)",
free_space
"FREE_SPACE(MB)"
FROM (SELECT
tablespace_name, SUM (BYTES)
/ (1024 * 1024)
reserved_space
FROM dba_data_files
GROUP BY
tablespace_name) r,
(SELECT tablespace_name,
SUM (BYTES) / (1024 * 1024) free_space
FROM dba_free_space
GROUP BY
tablespace_name) f
WHERE
r.tablespace_name = f.tablespace_name
ORDER BY
r.tablespace_name;
- Verify rollback
segment.
SQL> SELECT
segment_name, status FROM dba_rollback_segs;
SQL> select
SEGMENT_NAME, OWNER, TABLESPACE_NAME, SEGMENT_ID, status
2 from
dba_rollback_segs order by segment_name;
SQL> SELECT
rn.Name "Rollback Segment", rs.RSSize/1024 "Size (KB)",
rs.Gets "Gets",
rs.waits "Waits",
(rs.Waits/rs.Gets)*100 "% Waits",
rs.Shrinks "# Shrinks",
rs.Extends "# Extends"
FROM sys.v_$rollName rn, sys.v_$rollStat rs
WHERE rn.usn = rs.usn;
- To check session
SQL> SELECT
SUBSTR(v$session.username,1,15),
v$session.process,v$session.sid,
v$session.serial#,
SUBSTR(v$session.program,1,23),v$session.status,
v$session.terminal,
SUBSTR(v$statname.name,1,30),
ROUND(((v$timer.hsecs
- v$sesstat.VALUE)/100/60/60),2) HOURS
FROM v$timer,v$statname,v$sesstat,v$session
WHERE
v$statname.statistic# = v$sesstat.statistic# AND
v$sesstat.statistic# IN (13,14) AND
v$sesstat.sid = v$session.sid
ORDER BY
v$session.username,v$session.sid,v$sesstat.statistic#
-Track Database Host
Details:
SQL> SELECT
UTL_INADDR.GET_HOST_ADDRESS, UTL_INADDR.GET_HOST_NAME FROM DUAL;
- Track Total Size of
Database:
SQL>select
a.data_size+b.temp_size+c.redo_size "Total_Size (GB)"
from ( select
sum(bytes/1024/1024/1024) data_size
from dba_data_files ) a, ( select
nvl(sum(bytes/1024/1024/1024),0) temp_size
from dba_temp_files ) b, ( select
sum(bytes/1024/1024/1024) redo_size
from sys.v_$log ) c;
-Track Active Session
User Details:
SQL>SELECT SID,
Serial#, UserName, Status, SchemaName, Logon_Time FROM V$Session WHERE Status=
'ACTIVE' AND UserName IS NOT NULL;
- How to create
controle file to trace:
SQL>alter database
backup controlfile to trace as 'c:\shahid\shahid.txt' ;
-To list datafiles in
Tablespace
SQL> SELECT t.NAME "Tablespace", f.NAME
"Datafile"
FROM V$TABLESPACE t, V$DATAFILE f
WHERE t.TS# = f.TS#
ORDER BY t.NAME;
Comments
Post a Comment