Oracle DBA Day to Day Scripts

Monday, December 12, 2016

Rman backup status

Rman backup status

set linesize 999
set pagesize 500
col STATUS format a30
col START_TIME format a30
col END_TIME format a30
col hrs format 999.99
select
SESSION_KEY,
INPUT_TYPE,
STATUS,
to_char(START_TIME,'mm/dd/yy hh24:mi') start_time,
to_char(END_TIME,'mm/dd/yy hh24:mi') end_time,
elapsed_seconds/3600 hrs
from
V$RMAN_BACKUP_JOB_DETAILS
order
by
session_key;

Example out put

Sunday, December 4, 2016

To check which Exadata server complete details

To Check the exadata server complete details

command : dmidecode -s system-product-name

Oracle Exadata Database Machine	Database Server Model	Exadata Storage Server Model
Oracle Exadata Database Machine X5-2	Oracle Server X5-2 Database Server `ORACLE SERVER X5-2`	Exadata Storage Server X5-2L Servers `ORACLE SERVER X5-2L`
Oracle Exadata Database Machine X5-8	Oracle Server X5-8 Server `ORACLE SERVER X5-8`	Exadata Storage Server X5-2L Servers `ORACLE SERVER X5-2L`
Oracle Exadata Database Machine X4-2	Sun Server X4-2 Oracle Database Server `SUN SERVER X4-2`	Exadata Storage Server X4-2L Servers `SUN SERVER X4-2L`
Oracle Exadata Database Machine X3-2	Sun Server X3-2 Oracle Database Server `SUN FIRE X4170 M3`	Exadata Storage Server X3-2 Servers `SUN FIRE X4270 M3`
Oracle Exadata Database Machine X2-2	Sun Fire X4170 M2 Oracle Database Server `SUN FIRE X4170 M2 SERVER`	Exadata Storage Server with Sun Fire X4270 M2 Servers `SUN FIRE X4270 M2 SERVER`
Oracle Exadata Database Machine X4-8 Full Rack	Sun Server X4-8 Oracle Database Server `SUN SERVER X4-8`	Exadata Storage Server X4-2L Servers `SUN SERVER X4-2L`
Oracle Exadata Database Machine X3-8 Full Rack	Sun Server X2-8 Oracle Database Server `Sun Fire X4800 M2`	Exadata Storage Server X3-2 Servers `SUN FIRE X4270 M3`
Oracle Exadata Database Machine X2-8 Full Rack	Sun Fire X4800 Oracle Database Server or Sun Server X2-8 Oracle Database Server `Sun Fire X4800` or `Sun Fire X4800 M2`	Exadata Storage Server with Sun Fire X4270 M2 Servers `SUN FIRE X4270 M2 SERVER`
Oracle Exadata Database Machine	Sun Fire X4170 Oracle Database Server `SUN FIRE X4170 SERVER`	Exadata Storage Server with Sun Fire X4275 Servers `SUN FIRE X4275 SERVER`

Monitoring ambient temparatures of exadata cluster

dcli -g /opt/oracle.SupportTools/onecommand/all_group -l root 'ipmitool   \
sunoem cli "show /SYS/T_AMB" | grep value'

example :

[root@fldadbadm01 bin]# ./dcli -g /opt/oracle.SupportTools/onecommand/all_group -l root 'ipmitool   \
> sunoem cli "show /SYS/T_AMB" | grep value': value = 21.250 degree C
fldadbadm02: value = 21.000 degree C
fldadbadm03: value = 20.750 degree C
fldadbadm04: value = 21.000 degree C
fldaceladm01: value = 21.000 degree C
fldaceladm02: value = 20.750 degree C
fldaceladm03: value = 21.000 degree C
fldaceladm04: value = 20.000 degree C
fldaceladm05: value = 21.000 degree C
fldaceladm06: value = 21.000 degree C
fldaceladm07: value = 21.250 degree C

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