awk remove file with filenumber less then

I’m restoring a large amount of oracle archived redo logs into one directory. …

The archived redolog filenames have the following names ‘1_116133_820730017.dbf’ – ‘1_{log_sequence_number}_820730017.dbf’. The log sequence number is reflected in the filename, it is the second number pair – between the first and the second underscore. …

So it is like: …

1_116133_820730017.dbf
is
1_<log_sequence_number>_820730017.dbf 

# log sequence number is 116133

Newest restored archived logfiles ( log seq number like 119,…) ; …

$ ls -lat *.dbf  | head -n2
-rw-r-----    1 oracle   oinstall   15843840 Dec 06 13:34 1_119570_820730017.dbf 
-rw-r-----    1 oracle   oinstall   10368512 Dec 06 13:34 1_119571_820730017.dbf
$ 

Oldest restored archived logfiles ( log seq number like 116,…) ; …

$ ls -lart *.dbf  | head -n2
-rw-r-----    1 oracle   oinstall   30214656 Dec 06 12:06 1_116134_820730017.dbf
-rw-r-----    1 oracle   oinstall   30930944 Dec 06 12:06 1_116133_820730017.dbf
$ 

Now I want to remove all restored archived logs that have a log sequence number less than 118133. I do not need them anymore. …

The awk snippet below does that;
It extracts the log sequence number into an awk variable ‘seq_no’, does a simple if then comparison and echos the rm command if seq_no is less then 118133. …

awk 'BEGIN { FS = "_"} { seq_no = $2; if ( seq_no < 118133 ) print "rm ./" $0; }'

Output of the ‘ls awk ‘: …

# oldest files that can me removed
Read more →

Hello Oracle Golden Gate – Super simple Golden Gate monitor shellscript

We are setting up a test site for oracle golden gate. I missed a extract process that was abended so I wrote this little script to send me an email once one ore more process in Golden Gate show status of anbended – or better when ‘grep ABENDED’ returns anything. …

Nothing shocking really – it’s just grep. …

#!/usr/bin/ksh

# constants
# -------------------------------------------------------------------------
GOLDENGATE_HOME=/ora/product/goldengate/112101
ORACLE_HOME=/ora/product/db/11203
MAILTO=remivisser@domain.com


# no more editting pas this point
# -------------------------------------------------------------------------
LD_LIBRARY_PATH=${ORACLE_HOME}/lib
export LD_LIBRARY_PATH

# for some utter reason you need to cd to Golden Gate home or else i got
# Could not load program /ora/product/goldengate/112101/ggsci:
#         Dependent module libgglog.a could not be loaded.
# Could not load module libgglog.a.
# System error: No such file or directory

cd ${GOLDENGATE_HOME}

# start golden gate with eof and execute 'info all' and 
golden_gate_infoall=`${GOLDENGATE_HOME}/ggsci << EOF
info all
EOF`

# grep for ABENDED string
golden_gate_infoall=`echo $golden_gate_infoall | grep ABENDED`


if [[ "" = ${golden_gate_infoall} ]] ;
then
  exit 0
else
  # found 'ABENDED' - send email
  echo $golden_gate_infoall | mailx -s "One or more Golden Gate process abended at $(hostname)" ${MAILTO}
  exit 2
fi
# set permission
$ chmod u+x ggscs-status.sh

# execute the script
$ ggscs-status.sh

# query the exit code
$ echo $?
0

Example email

                                                        
Read more →

Trace you own session using dbms_system / set events

For debugging … Make sure the session has a direct execute grant on dbms_system package. …

Level 12 makes sure to include the waits and bind data. dbms_system …

begin
for i in (select sid, serial# from v$session where audsid=SYS_CONTEXT('USERENV','SESSIONID'))
loop
	sys.dbms_system.set_ev ( si => i.sid, se => i.serial#, ev => 10046, LE => 12, nm => '');
end loop;
end;
/
ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';
Read more →

SQL query to show tables vs indexes in your tablespaces

SQL query below displays a tables / indexes breakdown of your tablespaces. …

SELECT tablespaces.tablespace_name, 
			NVL( tables, 0) as tables, 
			NVL( indexes, 0) as indexes
FROM 	dba_tablespaces tablespaces
	LEFT OUTER JOIN ( 
				SELECT COUNT(*) tables, tablespace_name 
				FROM dba_segments 
				WHERE segment_type = 'TABLE' 
				GROUP BY tablespace_name ) tables
		ON tablespaces.tablespace_name = tables.tablespace_name
	LEFT OUTER JOIN ( 
				SELECT COUNT(*) indexes, tablespace_name 
				FROM dba_segments 
				WHERE segment_type = 'INDEX' 
				GROUP BY tablespace_name ) indexes
		ON tablespaces.tablespace_name = indexes.tablespace_name
/

Sample output; …

SQL> /
TABLESPACE_NAME                    TABLES    INDEXES
------------------------------ ---------- ----------
***************                         5          3
*******                                11         28
************                           56        118
************                           64        120
SYSAUX     
Read more →

Why Oracle May Really Be Doomed This Time

http://pandodaily.com/2012/02/11/why-oracle-may-really-be-doomed-this-time/
I was laying awake early this morning thinking about Oracle. …

O my …

Typically I think big public companies are inept, but I would not count Oracle out. Ellison has long shown he viscerally gets where the stock market, the customer and the technology are going. He may be better at this than anyone leading a technology company today. He has pulled off stunning and dramatic turn arounds of Oracle in the past. He can force the company to shift out of sheer force of will, uncowed by the near term pain he may inflict on customers, employees or Wall Street in the process. That’s something that only Steve Jobs and Jeff Bezos can do well. …

But to win, Oracle will have to change its strategy as dramatically as it did in 2006 when Ellison famously announced that software innovation was dead and just started to buy everything. Buying once-hot companies like Taleo and RightNow isn’t going to cut it this time when there are better products in the market like Workday and Salesforce. …

Wole article here;
http://pandodaily.com/2012/02/11/why-oracle-may-really-be-doomed-this-time/ …

Read more →