To Check ORA-Error on Oracle Database using Shell Scripts on Solaris/Unix.

To Check ORA-Error on Oracle Database using Shell Scripts on Solaris/Unix.

Following Scripts will check ORA Error and send alert on mail.

#!/bin/bash

Emails="mention email"

message="******ORA Error@`uname -n` ******"

export message

# an error message is displayed and the script exits with a status of 1

#if [ $1 ]

#then

    ORACLE_SID=ORCL; export ORACLE_SID

##Set the Environment##

. /arch/oracle/devora/ora10g/ORCL.env

cd $ORACLE_HOME/admin/ORCL/bdump

# Copy the current alert log into a temporary file and empty the original

tail -10 alert_$ORACLE_SID.log /arch/oracle/orcl/testscript/logs/alert_$ORACLE_SID.log.temp

# Check the copy in the temporary file for ORA- errors

grep  ORA- /arch/oracle/orcl/testscript/logs/alert_$ORACLE_SID.log.temp >> /arch/oracle/orcl/testscript/logs/ORA_Error.log

grep Shut /arch/oracle/devora/testscript/logs/alert_$ORACLE_SID.log.temp >> /arch/oracle/orcl/testscript/logs/ORA_Error.log

# If found, email the Oracle user with the contents of the alert log

if [ $? = 0 ]

then

    mailx -s  "$message" $Emails < \

    /arch/oracle/orcl/testscript/logs/ORA_Error.log

fi

#remove the temp file.

rm /arch/oracle/orcl/testscript/logs/alert_$ORACLE_SID.log.temp

rm /arch/oracle/orcl/testscript/logs/ORA_Error.log

=====================================================================

This scripts checks for any ORA errors at alert log file and if found any ORA error send an  alert e-mail.

Schedule it with crontab to run every 20 minutes to check error.

Schedule shell script for tablespace report in oracle database

In this article we will explain 'how to generate Tablespace reprot' in oracle database and automate alert on mail using mailx.

To pull out tablespace report in oracle database . We will prepare the shell script as below and schedule it using crontab.

Step 1:-

copy the below scripts in shell scripts as tablespacerpt.sh and grant the permission to execute shell script.

$chmod a+x tablespacerpt.sh


#!/sbin/sh
ORACLE_SID=ORCL; export ORACLE_SID
. /oracle/home/ora10g/ORCL.env

/oracle/home/ora10g/ora10g/bin/sqlplus -S -M "HTML ON TABLE 'BORDER="8"'" user/password @/oracle/home/ora10g/tablespacereport.sql

cat /oracle/home/ora10g/mailheader.log TABLESPACE_REPORT.html | mailx -t;

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

SET SERVEROUTPUT ON SIZE 1000000;
SET LINESIZE 9999;
set pagesize 400;
COLUMN TABLESPACE HEADING 'TABLESPACE' ENTMAP OFF
COLUMN TOTAL_SIZE(MB) HEADING 'TOTAL_SIZE(MB)'
COLUMN USED_SIZE(MB)  HEADING 'USED_SIZE(MB)'
COLUMN LARGEST_EXTENTS HEADING 'LARGEST_EXTENTS'
COLUMN FREE_SIZE(MB) HEADING 'FREE_SIZE(MB)'
COLUMN USED_USAGE HEADING 'USED_USAGE'
COLUMN FREE_USAGE HEADING 'FREE_USAGE'

SPOOL ABLESPACE_REPORT.html

select substr(A.tablespace_name,1,16) "TABLESPACE",SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024
"TOTAL_SIZE(MB)",(SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024)-(ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID)))
"USED_SIZE(MB)",ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID)) "FREE_SIZE(MB)",MAX(A.max_extents)
"LARGEST_EXTENTS",TO_CHAR(100-(SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%'
"USED_USAGE",
TO_CHAR((SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "FREE_USAGE"
from dba_tablespaces A,DBA_DATA_FILES B,DBA_FREE_SPACE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
AND A.TABLESPACE_NAME=C.TABLESPACE_NAME
GROUP BY A.TABLESPACE_NAME
order by 6 desc;

SPOOL OFF
exit:

-------------------
Step 2:- In this step we will create the mailheader file to send tablespace report on email.

Copy the below content in file name mailheader.log


From:abc@bc.com
To:abcd@bc.com
Cc:
Subject:Tablespace Report
Content-Type: text/html

after that schedule it using crontab as below.

Edit crontab :- $crontab -e

00 04 * * * /oracle/home/ora10g/tablespacerpt.sh

save it by press Esc + : then wq!.

How to manage Temporary Table space in Oracle Database

                                

Temporary tablespace are providing temporary space for database sort operations and for storing global temporary tables.

To Create user with default tablespace and temporary tablespace.

SQL> CREATE USER scott DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp;

SQL> ALTER USER scott TEMPORARY TABLESPACE temp;



Note:- The  temporary tablespace cannot contain permanent objects and therefore doesn't need to be backed up.


What are TEMPFILES?

TEMPFILES are not fully initialized (sparse). When you create a TEMPFILE, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a TEMPFILE than to create a normal database file.

TEMPFILES are not recorded in the database's control file. This implies that one can just recreate them whenever you restore the database, or after deleting them by accident. This opens interesting possibilities like having different TEMPFILE configurations between permanent and standby databases, or configure TEMPFILES to be local instead of shared in a RAC environment.

One cannot remove datafiles from a tablespace until you drop the entire tablespace. However, one can remove a TEMPFILE from a database. Look at his example:



SQL> ALTER DATABASE TEMPFILE '/oradata/temp02.dbf' DROP INCLUDING DATAFILES;




If you remove all tempfiles from a temporary tablespace, you may encounter error: ORA-25153: Temporary Tablespace is Empty. Use the following statement to add a TEMPFILE to a temporary tablespace:


SQL> ALTER TABLESPACE temp ADD TEMPFILE '/oradata/temp03.dbf' SIZE 100M;



Except for adding a tempfile, as illustrated in the above example, you cannot use the ALTER TABLESPACE statement for a locally managed temporary tablespace (operations like rename, set to read only, recover, etc. will fail).





How does one create Temporary Tablespaces?

Oracle provides various ways of creating TEMPORARY tablespaces (mainly to provide backward compatibility). One should use the most recent method available:

- Prior to Oracle 7.3 - CREATE TABLESPACE temp DATAFILE ...;
- Oracle 7.3 & 8.0 - CREATE TABLESPACE temp DATAFILE ... TEMPORARY;
- Oracle 8i and above - CREATE TEMPORARY TABLESPACE temp TEMPFILE ...;



Oracle 8i and 9i example:


SQL> CREATE TEMPORARY TABLESPACE temp TEMPFILE '/oradata/mytemp01.tmp' SIZE 20M
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 16M;



For best performance, the UNIFORM SIZE must be a multiple of the SORT_AREA_SIZE parameter.



Oracle 9i example using OMF (Oracle Managed Files):

SQL> CREATE TEMPORARY TABLESPACE temp;



Default Temporary Tablespaces:

In Oracle 9i and above, one can define a Default Temporary Tablespace at database creation time, or by issuing an "ALTER DATABASE" statement:


SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;



The default Default Temporary Tablespace is SYSTEM. Each database can be assigned one and only one Default Temporary Tablespace. Using this feature, a Temporary Tablespace is automatically assigned to users. The following restrictions apply to default temporary tablespaces:

- The Default Temporary Tablespace must be of type TEMPORARY
- The DEFAULT TEMPORARY TABLESPACE cannot be taken off-line
- The DEFAULT TEMPORARY TABLESPACE cannot be dropped until you create         another one.


To see the default temporary tablespace for a database, execute the following query:

SQL> SELECT * FROM DATABASE_PROPERTIES where PROPERTY_NAME='DEFAULT_TEMP_TABLESPACE';


All new users that are not explicitly assigned a TEMPORARY TABLESPACE, will get the Default Temporary Tablespace as its TEMPORARY TABLESPACE. Also, when you assign a TEMPORARY tablespace to a user, Oracle will not change this value next time you change the Default Temporary Tablespace for the database.



Other Considerations:

Some performance considerations for temporary tablespaces:

- Always use temporary tablespaces instead of permanent content tablespaces for sorting (no logging and uses one large sort segment to reduce recursive SQL and ST space management enqueue contention).
- Ensure that you create your temporary tablespaces as locally managed instead of dictionary managed (Use sort space bitmap instead of sys.fet$ and sys.uet$ for allocating space).
- Always use TEMPFILEs instead of DATAFILEs (reduce backup and recovery time + other advantages as described above)
- Stripe your temporary tablespaces over multiple disks to alleviate possible disk contention and to speed-up sorting operations (user processes can read/write to it directly).



Monitoring Temporary Tablespaces and Sorting:

Unlike datafiles, tempfiles are not listed in V$DATAFILE and DBA_DATA_FILES. Use V$TEMPFILE and DBA_TEMP_FILES instead.

One can monitor temporary segments from V$SORT_SEGMENT and V$SORT_USAGE

DBA_FREE_SPACE does not record free space for temporary tablespaces. Use V$TEMP_SPACE_HEADER instead:



SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

TABLESPACE_NAME                BYTES_USED BYTES_FREE
------------------------------ ---------- ----------
TEMP                             52428800   52428800