reaches 85% you will recieve alert in the mailbox/blackberry and mount point 90% and it depends how do you set it.
1)Tablespace issue's.
I copied this tablepsace scipt from SAM ALAPATHI.
column file_name format a46
column tablespace_name format a11
set verify off
accept tbs prompt 'What is the Tablespace Name: '
SELECT dts.tablespace_name,
NVL(ddf.bytes / 1024 / 1024, 0) avail,
NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024 used,
NVL(dfs.bytes / 1024 / 1024, 0) free,
TO_CHAR(NVL((ddf.bytes - NVL(dfs.bytes, 0)) / ddf.bytes * 100, 0), '990.00')
"Used %" ,
TO_CHAR(NVL((ddf.bytes - NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)) / ddf.bytes
* 100, 0), '990.00') free_pct,
decode(sign(
(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.85 - NVL(ddf.bytes / 1024 /
1024, 0)),-1,0,(NVL(ddf.bytes - NVL(dfs.bytes, 0), 0)/1024/1024)/0.85 - NVL(ddf.bytes / 1024 / 1024, 0)) "Required MB"
FROM
sys.dba_tablespaces dts,
(select tablespace_name, sum(bytes) bytes
from dba_data_files group by tablespace_name) ddf,
(select tablespace_name, sum(bytes) bytes
from dba_free_space group by tablespace_name) dfs
WHERE
dts.tablespace_name = ddf.tablespace_name(+)
AND dts.tablespace_name = dfs.tablespace_name(+)
and dts.tablespace_name = '&tbs';
select file_name,bytes/1024/1024 from dba_data_files
where tablespace_name = '&tbs';
Above script will give you the output in the below fashion.
TABLESPACE_ AVAIL USED FREE Used % FREE_PC Required MB
----------- ---------- ---------- ---------- ------- ------- -----------
APPS_TS_TX_ 5680.125 5122.375 557.75 90.18 9.82 346.198529
DATA
FILE_NAME BYTES/1024/1024
---------------------------------------------- ---------------
/u01/db/apps_st/data/a_txn_data03.dbf 1430.25
/u01/db/apps_st/data/a_txn_data02.dbf 1985.375
/u01/db/apps_st/data/a_txn_data01.dbf 1380.5
/u01/db/apps_st/data/a_txn_data04.dbf 884
i)How to resize a datafile and what are the things you need to check before adding the space in the tablepsace?
First check mount pont space i .e Avail 42G now i can add it.
SQL> !df -h /u01/db/apps_st/data
Filesystem Size Used Avail Use% Mounted on
/dev/sda3 97G 51G 42G 56% /u01
SQL> alter database datafile '/u01/db/apps_st/data/a_txn_data01.dbf' resize 1500m;
Database altered.
Below is the ouput after adding the space before it was 1380.5
TABLESPACE_ AVAIL USED FREE Used % FREE_PC Required MB
----------- ---------- ---------- ---------- ------- ------- -----------
APPS_TS_TX_ 5799.625 5122.375 677.25 88.32 11.68 226.698529
DATA
FILE_NAME BYTES/1024/1024
---------------------------------------------- ---------------
/u01/db/apps_st/data/a_txn_data03.dbf 1430.25
/u01/db/apps_st/data/a_txn_data02.dbf 1985.375
/u01/db/apps_st/data/a_txn_data01.dbf 1500
/u01/db/apps_st/data/a_txn_data04.dbf 884
ii)how to add a datafile and what are the pre requisites?
1)First check rman backup is running or not.
ps -ef|grep -i backup
ps -ef|grep -i rman
If your backup is running then it will show ACTIVE.
SQL> select count(*),status from v$backup group by status;
COUNT(*) STATUS
---------- ------------------
44 NOT ACTIVE
SQL> select name from v$database;
NAME
---------
PROD
SQL>
once the pre requisite done .now add a datafile.
SQL> select file_name from dba_data_files where file_name like '%/u01/db/apps_st/data/a_txn_data04.dbf%';
FILE_NAME
----------------------------------------------
/u01/db/apps_st/data/a_txn_data04.dbf
SQL> !ls -ld /u01/db/apps_st/data/a_txn_data04.dbf
-rw-r----- 1 oracle dba 926949376 Feb 13 11:27 /u01/db/apps_st/data/a_txn_data04.dbf
SQL> select file_name from dba_data_files where file_name like '%/u01/db/apps_st/data/a_txn_data05.dbf%';
no rows selected
File doesnot exists now you can add it safely.
Adding a datafile
===========
SQL> alter tablespace APPS_TS_TX_DATA add datafile '/u01/db/apps_st/data/a_txn_data05.dbf' size 2000m;
Tablespace altered.
SQL> @tsusage
What is the Tablespace Name: APPS_TS_TX_DATA
TABLESPACE_ AVAIL USED FREE Used % FREE_PC Required MB
----------- ---------- ---------- ---------- ------- ------- -----------
APPS_TS_TX_ 7799.625 5123.375 2676.25 65.69 34.31 0
DATA
FILE_NAME BYTES/1024/1024
---------------------------------------------- ---------------
/u01/db/apps_st/data/a_txn_data03.dbf 1430.25
/u01/db/apps_st/data/a_txn_data02.dbf 1985.375
/u01/db/apps_st/data/a_txn_data01.dbf 1500
/u01/db/apps_st/data/a_txn_data04.dbf 884
/u01/db/apps_st/data/a_txn_data05.dbf 2000
SQL>
2)Rman backup and archive monitoring.
To check rman backup/archive is running or not use below command.
ps -ef|grep -i rman
ps -ef|grep -i arch
More information can also be found through rman logfile.
3)Lock Holding sessions/inactive session in the database.(Degrade performance)
Will coming up shortly with details .
SQL> select sid,serial#,sql_hash_value,status from v$session where username like '%SCOTT%';
SID SERIAL# SQL_HASH_VALUE STATUS
---------- ---------- -------------- --------
372 59 0 INACTIVE
SQL> alter system kill session '372,59';
System altered.
SQL>
4)Mount point issue.
Below are the mount points will get fill due to huge number of transaction or becasue of req/out/log/trc/dbg/.t and .tmp files and many other.As you get the alert in your mailbox you have
to remove the unwanted files or the files which are not in used
To check all mount point of server.
[oracle@apps ~]$ df -kh
Filesystem Size Used Avail Use% Mounted on
/dev/sda1 15G 7.4G 6.4G 54% /
none 3.5G 0 3.5G 0% /dev/shm
/dev/sda6 5.0G 331M 4.4G 7% /tmp
/dev/sda3 97G 53G 39G 58% /u01
/dev/sda2 97G 78G 14G 85% /u02
/dev/sda7 70G 47G 20G 71% /u03
[oracle@apps ~]$
Use below command according to situation.
To check the files more than 10 mb.
[applmgr@apps log]$ find . -name "*.req" -size +10000000c -mtime +0 -exec ls -ltrh {} \;
[applmgr@apps log]$find . -name "*.out" -size +10000000c -mtime +0 -exec ls -ltrh {} \;
[applmgr@apps log]$find . -name "*.pdf" -size +10000000c -mtime +0 -exec ls -ltrh {} \;
To check files more than 30 days.
find . -name "*" -mtime +30 -exec ls -lth {} \;
To check files more than two days.
[applmgr@apps log]$ find . -name "*.req" -mtime +0 -exec ls -tlrh {} \;
-rw-r--r-- 1 applmgr dba 1.5K Feb 11 14:42 ./l388255.req
-rw-r--r-- 1 applmgr dba 2.3K Feb 11 14:46 ./l388257.req
-rw-r--r-- 1 applmgr dba 1.4K Feb 11 14:18 ./l388235.req
-rw-r--r-- 1 applmgr dba 1.5K Feb 11 14:30 ./l388247.req
-rw-r--r-- 1 applmgr dba 1.9K Feb 11 20:29 ./l388285.req
-rw-r--r-- 1 applmgr dba 1.9K Feb 11 14:58 ./l388266.req
-rw-r--r-- 1 applmgr dba 1.2K Feb 11 14:33 ./l388250.req
-rw-r--r-- 1 applmgr dba 1.2K Feb 11 14:35 ./l388252.req
-rw-r--r-- 1 applmgr dba 1.2K Feb 11 14:44 ./l388258.req
-rw-r--r-- 1 applmgr dba 464 Feb 7 19:29 ./l387477.req
-rw-r--r-- 1 applmgr dba 1.4K Feb 8 19:08 ./l388223.req
-rw-r--r-- 1 applmgr dba 1.2K Feb 11 14:30 ./l388248.req
-rw-r--r-- 1 applmgr dba 1.9K Feb 7 20:33 ./l388206.req
-rw-r--r-- 1 applmgr dba 1.2K Feb 11 14:40 ./l388256.req
-rw-r--r-- 1 applmgr dba 1.9K Feb 11 14:54 ./l388264.req
-rw-r--r-- 1 applmgr dba 7.0K Feb 11 14:12 ./l388220.req
-rw-r--r-- 1 applmgr dba 2.3K Feb 8 16:55 ./l388222.req
-rw-r--r-- 1 applmgr dba 1.3K Feb 11 13:57 ./l388201.req
-rw-r--r-- 1 applmgr dba 6.0K Feb 11 15:01 ./l388210.req
-rw-r--r-- 1 applmgr dba 1.4K Feb 8 19:08 ./l388229.req
-rw-r--r-- 1 applmgr dba 1.8K Feb 11 14:02 ./l388227.req
-rw-r--r-- 1 applmgr dba 1.9K Feb 11 18:57 ./l388282.req
[applmgr@apps log]$ date
Wed Feb 13 12:30:25 IST 2013
[applmgr@apps log]$
To check files are in used or not.
find . -name "*.req" -mtime +0 -exec fuser {} \;
find . -name "*.req" -mtime +0 -exec fuser {} \;
To find the files more than 200MB.
find . -name "*" -size +500000 -exec ls -ltrh {} \;
5)Monitoring long Running concurrent Request.
6)Resolving the status of suspended agent .
switch to oemgrid user.
./emctl status agent
./emctl stop agent
cd $AGENT_HOME/sysman/emd/upload
rm *
cd $AGENT_HOME/sysman/emd/state
rm *
./emctl clearstate agent
./emctl secure agent
./emctl start agent
./emctl pingOMS
switch to oemgrid user.
./emctl status agent
./emctl stop agent
cd $AGENT_HOME/sysman/emd/upload
rm *
cd $AGENT_HOME/sysman/emd/state
rm *
./emctl clearstate agent
./emctl secure agent
./emctl start agent
./emctl pingOMS
7)Checking alert logfile of production for ORA-00* errors.
1)ORA-00600
2)ORA-00745
The above two errors are more critical and precarious for the health of database andfor the same you will recieve alert in your mailbox.You have to login to metalink and you have
to search for the cause and symtomps and you have to send a mail to seniors with the
explanation they will take appropiate action on it.
Informatoin alerts
=============
MWA server bounced on theASCP prodcution.
Apache server bounce on production.
user using Apps password.
Terminated request still running ( As they will take time to release the momory)
Production backup reports for all the database
No comments:
Post a Comment