Month: February 2016

Oracle Free Table Space Across Files

define tsname undefine tsname # Put your tablespace name in here define tsname=”%USER%” select df.tablespace_name, fs.GB_FS_BefAuto, df.GB_Tot_Space_BefAuto “Total Space Allocated”, round(100*(fs.GB_FS_BefAuto/df.GB_Tot_Space_BefAuto),2) “Percent Free”, df.GB_Tot_MaxBytes “MaxBytes To Allocate”,GB_Rem_ToBe_AutoAllocated “Remaining To AutoAllocate” from (select tablespace_name,round(sum(bytes)/(1024*1024*1024),2) GB_FS_BefAuto from dba_free_space where tablespace_name like ‘&&tsname’ group by tablespace_name) fs, (select tablespace_name, round(sum(bytes)/(1024*1024*1024),2)  GB_Tot_Space_BefAuto, round(sum(decode(autoextensible,’YES’,MAXBYTES,’NO’,BYTES)) / (1024*1024*1024), 2) GB_Tot_MaxBytes, round(sum(decode(autoextensible,’YES’,MAXBYTES,’NO’,BYTES)-BYTES)/ (1024*1024*1024), …

Oracle Free Table Space Across Files Read More »

Show RedoLog Generation Per Hour

alter session set nls_date_format=’dd-mon-yy hh24:mi’; SELECT A.*, Round(A.Count#*B.AVG#/1024/1024) HOURLY_Avg_Mb FROM (SELECT To_Char(First_Time,’YYYY-MM-DD HH24′) HOUR, Count(1) Count#, Min(RECID) Min#, Max(RECID) Max# FROM v$log_history GROUP BY To_Char(First_Time,’YYYY-MM-DD HH24′) ORDER BY 1 DESC) A, (SELECT Avg(BYTES) AVG#, Count(1) Count#, Max(BYTES) Max_Bytes, Min(BYTES) Min_Bytes FROM v$log) B;

Guarantee a select will execute on a standby only within allowable lag

–set the SLA? –Use a logon trigger to set the maximum delay –whenever a user logs into the standby — works on 11gR2 with real time apply on — selects will succeed in this example if lag is less than 120 seconds / 2 minutes — if lag is more that 120 seconds – the …

Guarantee a select will execute on a standby only within allowable lag Read More »

Showing Oracle Data Guard Standby Lag Via A Histogram

The following view shows Data Guard standby lag via a histogram updated every second since the instance was last booted. Note *: It appears this view is only available with Active Data Guard (DB open read only) – standard Data Guard in “MOUNTED” mode will not return rows. select * from v$standby_event_histogram; Note this guy …

Showing Oracle Data Guard Standby Lag Via A Histogram Read More »

Apply 2016 PSU 22191676 to a Grid / Oracle Stand Alone 12.1.0.2 Using opatchauto

Overview Here is the process I used to apply the Oracle 2016 PSU 22191676 to Grid / Oracle Stand Alone 12.1.0.2 environment using opatchauto.  Effectively bringing the environment to 12.1.0.2.6 (12.1.0.2.160119) My environment has a 12.1.0.2.0 database ASM based standalone database – installed an administered with the grid infrastructure / ASM / listener owned by …

Apply 2016 PSU 22191676 to a Grid / Oracle Stand Alone 12.1.0.2 Using opatchauto Read More »

12.1.0.2 Stand Alone Database Install with ASM Oracle Linux 6.X on Virtual Box

Purpose How to setup a device on Oracle Virtual Box – and use that device for a Oracle stand alone ASM database install. Assumptions Assumptions Oracle Enterprise Linux 6.6 with oracle–rdbms–server–12cR1–preinstall RPM installed – with at least 35GB free space on a pre-existing file system.  Other 6.X releases of Oracle Linux should work.  BTW, this rpm …

12.1.0.2 Stand Alone Database Install with ASM Oracle Linux 6.X on Virtual Box Read More »

Strip Bash History Line #s, & you want more bash history than the default – try this in your .bashrc

Want more bash history than the default – try this in your .bashrc.  Then save them to a file with no line #s. [grid@prim12102 ~]$ cat .bashrc # .bashrc # Source global definitions if [ -f /etc/bashrc ]; then . /etc/bashrc fi # add these to lines to your .bashrc 1st stops overwrites, 2nd line …

Strip Bash History Line #s, & you want more bash history than the default – try this in your .bashrc Read More »

How to turn on Oracle Data Guard Real Time And Determine Status Of

Want to know for certain if your dataguard standby is in REAL TIME APPLY mode? Here is how – execute the following query from the primary side: select DEST_ID,dest_name,status,type,srl,recovery_mode from v$archive_dest_status where status <>’INACTIVE’;   To turn on real time apply do this: If you are currently recovering then cancel that: alter database recover managed …

How to turn on Oracle Data Guard Real Time And Determine Status Of Read More »