Oracle DBA Day to Day Scripts

Tuesday, May 3, 2016

Oracle database size sql query

How to find an oracle database size allocated and how much is occupied by the actual data..
my own query ..:) not copied from any where... :)

set serveroutput on
set linesize 99
col DATABASE_NAME format a20
select * from
(SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW" FROM DUAL)m ,
(select name "DATABASE_NAME" from v$database) f,
(select a.data_size+b.temp_size+c.redo_size "ALLOCATED_SIZE_IN_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 ) t,
(select sum(bytes) /1024 / 1024 /1024 "ACTUAL_DATA_OCCUPIED_IN_GB" from dba_segments ) z;

Output would be similar as below ...

NOW DATABASE_NAME ALLOCATED_SIZE_IN_GB ACTUAL_DATA_OCCUPIED_IN_GB
------------------- -------------------- -------------------- --------------------------
05-03-2016 15:18:39 RDTEST 60.9462891 2.46868896

Thanks

Rajesh R D

Wednesday, April 6, 2016

Rman duplicate database to another server ASM to ASM

Duplicating database to another server ASM to ASM

We have two options, were we can use db_convert_files and log convert files parameter if this is at a different structure ( different filesystem / different disk group name )
second one is we can use the set new name for datafile option. i felt this set newname for datafile option is easiest .

The below query can be used to produce the set newname file from the source machine.

SELECT 'SET NEWNAME FOR DATAFILE '||FILE#||' TO '''||'+DATAFILE_Diskgroupname'||
SUBSTR(NAME,
INSTR(NAME, '/', -1, 1),
INSTR(NAME, '.',1,2) - INSTR(NAME, '/',-1,1)) ||'.dbf'';'
FROM V$DATAFILE;

Then, let the target db started in nomount , and also add the tns entries for the source database on the target server

connect auxiliary /
CONNECT target sys/xxxxx@BOORAxxxx20
CONNECT CATALOG RMAN/detaxx@BORxxxxxOD;
run
{
SET UNTIL TIME "to_date('16-OCT-2013 04:00:00','DD-MON-YYYY HH24:MI:SS')";
allocate channel C1 device type disk;
allocate auxiliary channel C2 device type disk;
allocate auxiliary channel C3 device type disk;
allocate auxiliary channel C4 device type disk;
SET NEWNAME FOR DATAFILE 1 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 2 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 3 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 4 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 5 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 6 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 7 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 8 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 9 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 10 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 11 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 12 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 13 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 14 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 15 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 16 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 17 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 18 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 19 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 20 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 21 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 22 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 23 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 24 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 25 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 26 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 27 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 28 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 29 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 30 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 31 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 32 TO '+DATAFILE_COORADEVL20';
SET NEWNAME FOR DATAFILE 33 TO '+DATAFILE_COORADEVL20';
duplicate target database to ORDEVL20 NOFILENAMECHECK
LOGFILE
GROUP 1 ('+LOG1', '+LOG2') SIZE 100M,
GROUP 2 ('+LOG1', '+LOG2') SIZE 100M,
GROUP 3 ('+LOG1', '+LOG2') SIZE 100M,
GROUP 4 ('+LOG1', '+LOG2') SIZE 100M,
GROUP 5 ('+LOG1', '+LOG2') SIZE 100M,
GROUP 6 ('+LOG1', '+LOG2') SIZE 100M;
}

Saturday, March 12, 2016

How to kill all sessions for a given user in oracle

set head off 
spool tmp.tmp 
select 'Alter system kill session '''||sid||','||serial#||''';' 
from v$session where username='USER_TO_KILL_IN_CAPS' ;
spoof off
@tmp.tmp

kill all sessions for a user

set head off 
spool tmp.tmp 
select 'Alter system kill session '''||sid||','||serial#||''';' 
from v$session where username='USER_TO_KILL_IN_CAPS' ;
spoof off
@tmp.tmp

Sunday, July 27, 2014

awr snap id listings

column begin_interval_time format a25
column end_interval_time format a25
SELECT dbid, snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by begin_interval_time;

http://www-03.ibm.com/systems/tools/databaseinsight/

Tuesday, July 22, 2014

Wifi connected but no internet on linux (ubuntu)

Hi

This is how i resolved ,

Please try the below commands as executed one by one followed by a reboot (sudo reboot)

sudo apt-get install linux-firmware-nonfree
sudo apt-get remove --purge bcmwl-kernel-source
sudo rm /etc/modprobe.d/blacklist-bcm43.conf
sudo rm /etc/modprobe.d/broadcom-sta-common.conf
sudo rm /etc/modprobe.d/broadcom-sta-dkms.conf 
sudo sed -i "s/blacklist b43/#blacklist b43/g" $(egrep -lo 'blacklist b43' /etc/modprobe.d/*)
sudo sed -i "s/blacklist ssb/#blacklist ssb/g" $(egrep -lo 'blacklist ssb' /etc/modprobe.d/*)
sudo sed -i "s/blacklist bcma/#blacklist bcma/g" $(egrep -lo 'blacklist bcma' /etc/modprobe.d/*)

Thanks

Thursday, May 1, 2014

Oracle environmental variable setup (Unix)

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/12.1.0/dbhome_1
export ORACLE_SID=rd2
export ORAENV_ASK=NO
export PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/$ORACLE_HOME/bin:/$ORACLE_HOME/OPatch

export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/12.1.0/grid/bin
export ORACLE_GRID=rd2
export ORAENV_ASK=NO
export PATH=/usr/lib64/qt-3.3/bin:/usr/local/bin:/bin:/usr/bin:/usr/local/sbin:/usr/sbin:/sbin:/home/oracle/bin:/$ORACLE_HOME/bin:/$ORACLE_HOME/OPatch