ON CALL DBA SUPPORT

— Database blog

FLUSING THE SINGLE SQL STATEMENT FROM THE SHARED POOL

Posted by ssgottik on 12/06/2015

FLUSING THE SINGLE SQL STATEMENT FROM THE SHARED POOL:

Till 10.2.0.2:
————–
SQL > ALTER SYSTEM FLUSH SHARED_POOL;

In 10.2.0.4:
————
The enhanced DBMS_SHARED_POOL package with the PURGE procedure is included in the 10.2.0.4 patchset release.

NOTE : RDBMS patch 5614566

FROM 11g:
———
SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID = ‘9k2thjfjykcb%’;

ADDRESS HASH_VALUE
—————- ———-
000000085FD77CF0 808321886

SQL> exec DBMS_SHARED_POOL.PURGE (‘0000000090DA1C68, 809121806’, ‘C’);

PL/SQL procedure successfully completed.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID like ‘9k2thjfjykcb%’;

No rows selected

Posted in ADMINISTRATION | Leave a Comment »

CLONE ORACLE 11g HOME

Posted by ssgottik on 09/04/2015

STEPS TO CLONE ORACLE 11g ORACLE_HOME  FROM ONE SERVER TO ANOTHER

 

STEP 01 : ZIP THE ORACLE HOME FILE FROM THE SOURCE

cd /u01/app/oracle/product

$tar  -pcvf   11204.tar   11.2.0.4

STEP 02 : SCP  .tar file to the target server

scp -p username@target.com:/u01/app/oracle/product/ .

STEP 03 : UNTAR THE .tar FILE

$tar -xvf 11204.tar

STEP04 : RUN CLONE.PL FROM ORACLE_HOME

vmlinux01::/u01/app/oracle/product/11.2.0.4/clone/bin> perl clone.pl ORACLE_BASE=’/u01/app/oracle/’
ORACLE_HOME=’/u01/app/oracle/product/11.2.0.4′ ORACLE_HOME_NAME=’11204_HOME’
./runInstaller -silent -clone -waitForCompletion “ORACLE_HOME=/u01/app/oracle/product/11.2.0”
“ORACLE_HOME_NAME=11204_HOME” -noConfig -nowait
Starting Oracle Universal Installer…

No pre-requisite checks found in oraparam.ini, no system pre-requisite checks will be executed.
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2012-12-02_03-22-12PM. Please wait …Oracle
Universal Installer, Version 11.2.0.4.0 Production
Copyright (C) 1999, 2008, Oracle. All rights reserved.

You can find a log of this install session at:
/u01/app/oraInventory/logs/cloneActions2012-12-02_03-22-12PM.log
………………………………………………………………………………………. 100% Done.

Installation in progress (Tuesday, December 2, 2012 3:22:29 PM EST)
……………………………………………………………….
73% Done.
Install successful

Linking in progress (Tuesday, December 2, 2012 3:22:39 PM EST)
Link successful

Setup in progress (Tuesday, December 2, 2012 3:24:50 PM EST)
Setup successful

End of install phases.(Tuesday, December 2, 2012 3:24:56 PM EST)
WARNING:A new inventory has been created in this session. However, it has not yet been registered as the central
inventory of this system.
To register the new inventory please run the script ‘/u01/app/oraInventory/orainstRoot.sh’ with root privileges.
If you do not register the inventory, you may not be able to update or patch the products you installed.
The following configuration scripts need to be executed as the “root” user.
#!/bin/sh
#Root script to run
/u01/app/oraInventory/orainstRoot.sh
/u01/app/oracle/product/11.2.0/root.sh
To execute the configuration scripts:
1. Open a terminal window
2. Log in as “root”
3. Run the scripts

The cloning of 11204_HOME was successful.
Please check ‘/u01/app/oraInventory/logs/cloneActions2012-12-02_03-22-12PM.log’ for more details.
vmlinux01::/u01/app/oracle/product/11.2.0/clone/bin> cat /etc/oratab
#

STEP 05 : RUN orainstRoot.sh and root.sh as root user

#/u01/app/oraInventory/orainstRoot.sh
#/u01/app/oracle/product/11.2.0/root.sh

 

Thanks,

Satish.G.S

Posted in ADMINISTRATION | Leave a Comment »

QUERY TO CHECK MOUNT POINT UTILIZATION BY A DATABASE

Posted by ssgottik on 06/03/2015

SQL > select substr(file_name,1,20)||’ # ‘|| ceil(sum((bytes)/(1024*1024*1024))) from dba_data_files group by substr(file_name,1,20);

 

Thanks,

Satish.G.S

Posted in SCRIPTS | Leave a Comment »

COMPILE ENTIRE SCHEMA OBJECTS

Posted by ssgottik on 13/02/2015

EXEC UTL_RECOMP.recomp_serial(‘SCOTT’);

Thanks,

Satish.G.S

Posted in SCRIPTS | Leave a Comment »

QUERY TO FIND RMAN BACKUP STATUS IN A DATABASE

Posted by ssgottik on 31/10/2014

set lines 200;
set pages 200;
SELECT INPUT_TYPE “Backup Type”,incremental_level,d.session_recid,
TO_CHAR(d.START_TIME,’mm/dd/yy hh24:mi’) “start_time”,
TO_CHAR(END_TIME,’mm/dd/yy hh24:mi’) “End_time”,
d.elapsed_seconds/3600 “Elapsed Time”,
d.status
FROM V$RMAN_BACKUP_JOB_DETAILS d,V$BACKUP_SET_DETAILS s where d.session_recid=s.session_recid and d.START_TIME>sysdate-2 order by End_time desc;

Posted in SCRIPTS | Leave a Comment »

QUERY TO FIND FRA UTILIZATION

Posted by ssgottik on 31/10/2014

set lines 200;
col “Flashback Area” for a40;
SELECT NAME “Flashback Area”, ROUND(SPACE_LIMIT / 1073741824) “Allocated space in GB”, ROUND(SPACE_USED / 1073741824) “Used space in GB”,ROUND(((SPACE_USED /1073741824) * 100) / (SPACE_LIMIT / 1073741824), 2) “USED PERCENTAGE” FROM V$RECOVERY_FILE_DEST where ROUND(((SPACE_USED / 1073741824) * 100) / (SPACE_LIMIT / 1073741824), 2)>0;

Posted in SCRIPTS | Leave a Comment »

ALERT LOG OF A PARTICULAR DATE INTO A FILE

Posted by ssgottik on 16/06/2014

vi alert_orcl.log

:set nu

:wq!

by doing above it will set the number for all the lines in the alert log. Save and come out of the log and give the below sed command.

$sed ‘12345, 56677p’ alert_orcl.og > alert_orcl_new.log

 

Posted in SCRIPTS | Leave a Comment »

Database house keeping scripts

Posted by ssgottik on 28/05/2014

find . -name “*.trc” -mtime +0|xargs rm -f “*.trc”

find . -name “*.trm” -mtime +0|xargs rm -f “*.trm”

find . -name “*.aud” -mtime +0|xargs rm -f “*.aud”

find . -name “*.xml” -mtime +0|xargs rm -f “*.xml”

find . -name “*.dat” -mtime +1|xargs rm -f “*.dat”

Posted in SCRIPTS | Leave a Comment »

DROPPING A DISK IN A ASM DISKGROUP (11g R2)

Posted by ssgottik on 28/04/2014

From 11g R2 on wards no need to unmount the diskgroup to add and drop the disks:

SQL > alter diskgroup data drop disk  ‘/ora00/oracle/disk100’ ;

SQL >rebalance power 6;

Thanks,

Satish.G.S

Posted in ASM | Leave a Comment »

ADDING DISK TO ASM(11G R2)

Posted by ssgottik on 28/04/2014

From 11g R2 on wards no need to unmount the diskgroup to add and drop the disks:

SQL > alter diskgroup data add disk  ‘/ora00/oracle/disk100’ , ‘ora00/oracle/disk101’

SQL >rebalance power 6;

Thanks,

Satish.G.S

Posted in ASM | Leave a Comment »