Temp tablespace usage in oracle

Script to monitor temp tablespace usage in oracle

Create a folder

mkdir -p /u01/app/oracle/script/chk_tmp_usage

copy below scripts and replace <SID> with your SID and paste these scripts in /u01/app/oracle/script/chk_tmp_usage

Now you will have three scripts in /u01/app/oracle/script/chk_tmp_usage folder

tmp_usage_<SID>.sh
select_<SID>.sql
temp_<SID>.sql

crontab entry

*/15 * * * * /u01/app/oracle/script/chk_tmp_usage/tmp_usage_<SID>.sh <SID> <ORACLE_HOME> >> /u01/app/oracle/script/chk_tmp_usage/mail_<SID>.log 2>&1

vi /u01/app/oracle/script/chk_tmp_usage/tmp_usage_<SID>.sh
#!/usr/bin/bash

export DATO=`date +%Y%m%d.%H%M%S`
export ORACLE_SID=$1
export ORACLE_HOME=$2
export PATH=$ORACLE_HOME/bin:$PATH
PATH=$PATH:$ORACLE_HOME/bin
SCRIPT_DIR=/u01/app/oracle/script/chk_tmp_usage
LOGFILE=${SCRIPT_DIR}/temp_${ORACLE_SID}.log
MAIL_TO="xyz@gmail.com"
cd ${SCRIPT_DIR}

${ORACLE_HOME}/bin/sqlplus '/ as sysdba' @${SCRIPT_DIR}/select_${ORACLE_SID}.sql ${SCRIPT_DIR}
VAL=`cat ${SCRIPT_DIR}/select_${ORACLE_SID}.log`
for i in $VAL
do
        if [ $i -gt 70 ]
        then
                ${ORACLE_HOME}/bin/sqlplus '/ as sysdba' @${SCRIPT_DIR}/temp_${ORACLE_SID}.sql ${SCRIPT_DIR}
        else
                echo "temp usage normal"
        fi
done

VAR=`ls ${SCRIPT_DIR}`
if [ -f  "temp_${ORACLE_SID}.log" ]
then

/bin/mailx  -a ${LOGFILE} -s "Temp Usage - DB : ${ORACLE_SID} on ${DATO}" $MAIL_TO <<EOF
Hi,

Please find attached logfile
This is an automated email please do not reply to this. For any information please contact xyz@gmail.com.


Regards,
XYZ
EOF

rm -f ${SCRIPT_DIR}/temp_${ORACLE_SID}.log
fi

~]$ vi select_<SID>.sql
spool &1/select_<SID>.log
set head off
SELECT ROUND(((TABLESPACE_SIZE - NVL(FREE_SPACE,0)) / TABLESPACE_SIZE) * 100) Used from dba_temp_free_space;
spool off;
exit

~]$ vi temp_<SID>.sql
spool &1/temp_<SID>.log
set lines 9999
set feedback off
set trimspool on trimout on
col MODULE for a40
col MACHINE for a20
col SID_N_SERIAL for a12
col SPID for a10
col PROGRAM for a25
col OSUSER for a12
col TBLSPC for a6
col USERNAME for a10
select
   s.sid || ',' || s.serial# sid_n_serial,
   s.username,
   s.osuser,
   p.spid,
   s.module,
   s.machine,
   p.program,
   (sum (t.blocks) * tbs.block_size / 1024 / 1024) AS mb_used,
   t.tablespace as "TBLSPC"
 from
   v$sort_usage    t,
   v$session       s,
   dba_tablespaces tbs,
   v$process       p
 where
   t.session_addr = s.saddr
and
   s.paddr = p.addr
and
   t.tablespace = tbs.tablespace_name
group by
   s.sid,
   s.serial#,
   s.username,
   s.osuser,
   p.spid,
   s.module,
   s.machine,
   p.program,
   tbs.block_size,
   t.tablespace
having (sum (t.blocks) * tbs.block_size / 1024 / 1024) > 1000
order by
   mb_used desc;
spool off;
exit

RAC Listener part 1 / Services in oracle database

In my previous blog, I have explained Clustering, Grid Infrastructure. To understand what is a Scan/RAC Listener? First of all, one should know what is RAC? So kindly go through my previous blog if you haven't read the blog. Ok, Let's begin our journey...

What is a Database service in oracle?

For application users to connect database they need a connection string.

(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=MyHost)(PORT=MyPort)))(CONNECT_DATA=(Sid=MyOracleSID))) 

Here we are talking about services for Standalone Database:

In the above string, we have used Sid to connect the Database. Now suppose we have two different applications and user A of application 1 is connected to Instance similarly User B of application 2 is also connected to our instance. We have observed High load on the database now as we are using SID to connect the database there is no way to know who is the culprit application. Now comes in picture services.

Let us take another example where Application A is connected to the instance using Service 1 and Application B is connected to the instance using Service 2. Now we observe high load on the database through AWR Reports we can easily find the culprit because of service.

Question is How services are useful in RAC Database?

In RAC Database we have two Different instances each having Different SID then why to use services. We can easily find through AWR Reports who is the culprit. But One thing to understand Services is the Underlying Framework of RAC Databases. Service is the first step to achieve high availability, scalability, and failover in RAC Environment. But How?

Let us consider two node RAC database. We know both Instances Sid will be different. How can application know it is connecting to the same database🤔 as SIDs are different and so far we know different SIDs mean different databases. To overcome this we make the same service in both the databases and instead of SIDs we use this service in our connection string. Now whether it connects Node 1 or Node 2 it is connecting to the same RAC database. Also, we have now two nodes for a single database that means scalability, but why not high availability. For that read till the end...

What about Failover?

Suppose application A is making a connection with Instance 1 using service and due to some reason instance 1 has crashed. Now application A will switch to 2nd Instance as another instance is having the same service. In this way, we can switch to another in case of a failover. But the question is how can an Application A switch over.

Agree, Both are using the same service but if node 1 is not available, the application request once send if will not receives any reply, for that application database is not available. That's why the concept of VIP was introduced by oracle and Clusterware manages the VIPs.

What is VIP?

As we know IP is connected to a device but if that device is not available due to any reason then our IP would not be able to tell the application that Hey I am not available. That's why the concept of VIP came in the picture as if any node goes down VIP of that node switch over to the other node.

Now here the point. That node has two VIPs,  suppose application tries to connect Node 1 it will send a request to Node 1 VIP and it has switched over to Node 2 as soon as the request reaches Node 1 it's VIP would reply back to the application that Hey I am not available. Kindly connect to another node. Now it will again check its connection string and connect the second VIP. If available it will fulfill the request or else will check the next VIP and so on. In this way for application database is available. Hence achieved high availability and failover.

Connect string for RAC databases prior to 11GR2.

App_serv=
(DESCRIPTION=
(ADDRESS_LIST=
(ADDRESS = (PROTOCOL = TCP)(HOST=VIP1)(PORT=1521))
(ADDRESS = (PROTOCOL = TCP)(HOST=VIP2)(PORT=1521))
(ADDRESS = (PROTOCOL = TCP)(HOST=VIP3)(PORT=1521)))
(CONNECT_DATA=(SERVICE_NAME=service1)) -- single service to connect

In the above connection string, we have 3 RAC nodes. If one goes down the connection string itself check another node availability, but it again has some drawbacks as if we have to add another node then we have to append over-application connection string. To overcome this concept of SCAN came into the picture.  Which we will discuss in the second part.

Thanks & Enjoy learning!!

Cluster in oracle / What is RAC / Grid Infrastructure

What is a cluster in oracle?

The business works w.r.t to SLA ( Service level agreement) i.e. an application/website must be available 99-100% of the time. How to achieve that?

We have an application that is making a connection with the database through an instance. If due to any reason may be because of the network, instance crash that instance goes down, we will not able to achieve the SLA required by the business. We have a profound DBA team enough capable to solve the issue in 1 hour still, our SLA will be breached. What to do now 🤔 ?

As we are paying too much to the oracle, so why not to use services they have provided us.

Here comes in picture "Cluster". Cluster means a group in laymen language.

As per definition "Cluster is a group of nodes which together works as a single system and RAC is the implementation of instances/nodes running in a cluster"

Now How the group can solve the issue? We have a group of capable DBA as well 😕.

Let's come to the point of the cluster. What if we have two Instances, can this solve our problem?

As we have a problem that if our only instance goes down, an application would not able to access the database. This is the magic of the cluster. Now instead of Single instance, we have two instances.

If one instance goes down, the application user still can access the database through 2nd instance. 

The crux is through clustering we have achieved High availability and also we SLA will not get breached. Meanwhile, Our DBA's can solve the issue on the First node 😌. And how oracle manage this clustering, through Clusterware.

But why just for high availability we are using a Clustering (another overhead on us DBA's).

Here comes another advantage of using Clustering. That one is Scalability. To understand scalability we have to go into the past where we have only one Instance, Due to business growth we have more traffic on our application it means money 😁 , but we have only a single instance having fewer resources. What to do now? One-off a DBA from our team gave us a great idea that we should increase resources of that instance means increase memory, CPU, etc. Oh God but what about High availability if that single goes down we are big trouble that we discussed earlier. We need two instances that are for sure. But if we think about these instances in broader prospect we have two instances they both provide us scalability as we haven't decreased resources on our 1st instance, we have added a 2nd instance having the same resources as that of First. This way we have achieved High availability as well as Scalability.

RAC (Real Application Cluster) 

Why I have written RAC in Heading?

We missed something. What? We have two instances that are for sure, but we have a single Database. Two instances are working on a single database. This is "RAC (Real Application Cluster) where we have multiple instances but all instances are working on a single database simultaneously".

But lots of questions must have arisen due to this. What if User A is working is performing any DML on Table A at the same time User B is doing DML on that same table. 

For managing all this we have Grid infrastructure.

What is Grid Infrastructure?

For Standalone database i.e for single instance database.

Oracle Grid Infrastructure for a standalone server is the software that includes Oracle Restart and Oracle ASM. Oracle combined the two infrastructure products into a single set of binaries that are installed as the Oracle Grid Infrastructure home.

Oracle ASM is a volume manager and a file system for Oracle database files that supports single-instance Oracle Database and Oracle Real Application Clusters (Oracle RAC) configurations. Oracle ASM also supports a general-purpose file system for your application needs including Oracle Database binaries. Oracle ASM is Oracle's recommended storage management solution that provides an alternative to conventional volume managers, file systems, and raw devices.

Oracle Restart improves the availability of your Oracle database by providing the following:

• When there is a hardware or a software failure, Oracle Restart automatically starts all Oracle components, including Oracle database instance, Oracle Net Listener, database services, and Oracle ASM.
• Oracle Restart starts up components in the proper order when the database host is restarted.
• Oracle Restart runs periodic checks to monitor the health of Oracle components. If a check operation fails for a component, then the component is shut down and restarted.

For RAC Databases i.e. Databases having multiple instances.

Oracle Real Application Clusters

Oracle Real Application Clusters enables a single database to run across multiple clustered nodes in a grid, pooling the processing resources of several standard machines.

Oracle Clusterware

Oracle Clusterware is a portable cluster software that allows the clustering of single servers so that they cooperate as a single system. Oracle Clusterware also provides the required infrastructure for Oracle Real Application Clusters (RAC). Oracle Clusterware also enables the protection of any Oracle application or any other kind of application within a cluster.

In simple terms

Underlying foundation with which oracle implement RAC is Clusterware in Grid infrastructure. Clusterware is software with which oracle manages clustering. Its work is to manage the node eviction etc.

Oracle Automatic Storage Management

Oracle Automatic Storage Management (ASM) provides a virtualization layer between the database and storage. It treats multiple disks as a single disk group and lets you dynamically add or remove disks while keeping databases online.

Thanks & Enjoy Learning!!

Create database in oracle

How to create database in oracle :

To create database in oracle, first of all, binaries should have been installed. Here our binaries are present at /u02/app/oracle/product/12201 location.

Steps to create database in oracle are below:-

Step 1 : Go to /u02/app/oracle/product/12201/bin directory.

]$ ./dbca 

Then Create a database.

Step 2: Click Advance configuration.

Step 3: Select the Database type as a Single instance database.

Step 4: Give the Global Database name as well as SID.

Here in Single Instance Database we usually keep both names as same, whereas in RAC both are different. Also, SID is the database name that you see in cat /etc/oratab.

Step 5: Now, you have to select Storage for your database. 

So before creating a database in oracle you have to do grid installation (ASM) where the actual data is stored. Here I have already installed ASM. So I have selected ASM as my storage type and the Create file destination is +DATA ( It is Diskgroup where actual data is present, also basic Notation for data in ASM that we basically use is +DATA and for Archives we use +FLASH/FRA).

Step 6: Select Size and Destination of Fash Recovery area.

As Discussed above Fast recovery area is place where we store archives of the database. Also, select the size of the fast recovery area as per your database size and retention (For how many days you want to keep your archives in the database). For longer retention use recovery catalog and save archive or backup data on tapes, as tape storage is much cheaper.

Step 7: Listener Configuration

The default port for Listener is 1521. Here we just selected that. Now the question arises what is the listener? As we know user's with the help of the Application server connect the database. The listener's work is to make a connection of application users with the database. Once the connection is established, now if the listener goes down connection still remains.

Step 8: Memory Configurations

Now select the size of SGA and PGA, as per your application configurations.

Step 9: Process Configuration

How many users concurrently can make connections with database ( That depends on the type of application for which you are making the database). Also, whether you are using dedicated server mode or Shared server mode will decide how many processes you gonna require.

Step 10: Character set

Just select the language of the database. You can see these values in (v$nls_parameters).

Step 11: Connection Mode

Select as per your application design and configuration. Also, remember one thing the most resource-consuming task for the database is to make as well as remove connections. So to improve the performance of the database if so many connections are coming and leaving, it is a big overhead. So use dedicated mode if database is not going to be used by too many users concurrently.

Step 12: Sample schemas 

Just uncheck this, as we don't require this to create database in oracle.

Step 13: OEM configuration

This can be done in the future if required.

Step 14: Set sys user password.

Step 15: Create database

Step 16: Set all initialization parameters.

One major thing to remember is if you do not have a license for the control management pack. Then replace TUNING+DIAGNOSTIC to NONE in the initialization parameters.

Step 17: Custom storage Location and management.

In this Set Minimum datafiles to 512. As this is not dynamic parameters so in the future if you have to change this you have to bounce the database. Better to increase this now, while creating database.

Step 18: Redo Log Group size

Redo log group size should be tuned because if its size is small then database has to do too many log switches, which might increase overhead on I/O due to too many write operations. So tuned/determine the redo log group size as per the size of the database and how fastly users are updating, writing into the database.

Step 19: Click next 

Step 20: Recheck all configurations you have done till now.

Step 21: Finish

You have successfully created database in oracle.

Once installed set environment variable and you can use the vanilla database. 

Thanks and Enjoy Learning!!