Category Archives: Geek Stuff

Computers, Linux, Oracle, whatever I happen to be working on at the moment.

Mind The Gap…

During some scheduled data centre maintenance an Oracle Dataguard standby database got out of sync with its primary, after the standby was restarted it failed to apply the logs and catch up. I eventually got to the bottom of the problem thanks to Steve Harville The primary had it’s weekly full backup on Friday AFTER the standby was shut down and having backed up the archive log files, deleted them. Of course when the standby was restarted it was missing a few archive logs and couldn’t catch up with the primary. A quick rman recovery of the logs from the backup and Dataguard sprang back into life!

The clue to the problem was here:
SQL> SELECT PROCESS, STATUS, THREAD#, SEQUENCE#, BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CLOSING               1      43606      53249        568
RFS       IDLE                  1      43607      34518          9
RFS       IDLE                  0          0          0          0
MRP0      WAIT_FOR_GAP          1      43289          0          0
RFS       IDLE                  0          0          0          0

The MRP0 (Managed Recovery Process) process status ‘WAIT_FOR_GAP’ is what indicates the missing logs and Steve’s helpful script told me what to recover on the primary.

Advertisements

Removing duplicate lines with awk

Often I’ve found the need to manipulate text files in various ways and I’ve found the powerful awk/gawk program invaluable saving me many hours of repetitive work. The latest one-liner I used though left me stumped as to how it actually works. Very simply I had an unsorted list of several hundred email addresses and I needed to remove the duplicates from the file, this was the solution I found on a number of websites…

awk '!x[$0]++' mail-list.txt

It look good and worked great, but nowhere could I find an explanation of what it’s actually doing until I came across this thread on unix.com.

Sorting is not necessary. All it does is create an (associative) array element with the entire line as the index without a value (or 0 is you will). The exclamation mark negates that value so the outcome is 1 (true). The value of 1 in awk means perform the default action which is {print $0} so the entire line gets printed. Afterwards the ++ comes into action and 1 is added to the array value, which now becomes 1. So that next time the same line is encountered the value returned by the array is 1 which is then negated to 0 by the exclamation mark, so nothing will get printed

Thanks to Scrutinizer on unix.com

Tracking row changes in Oracle tables

It’s been a while since I posted anything work related but here’s one I discovered today.

Have you ever needed to find out when a particular row was changed? From Oracle 10g there’s a feature which records the SCN number of a row change within the table in a pseudo-column called ora_rowscn. So for example:


SQL> SELECT ora_rowscn FROM sometable WHERE id = '123';

ORA_ROWSCN
----------
45738231

Then you can convert the SCN number to a timestamp using the scn_to_timestamp function


SQL> SELECT scn_to_timestamp(45738231) AS timestamp FROM dual;

TIMESTAMP
------------------------------
05-JUL-11 10.44.42.000000000

The only caveat with this feature is the tables have to be created with the ROWDEPENDENCIES option although it seems to be on as default in 11g.

Credits: http://oracle-tns.com/track-the-row-change-time/