Tag Archives: Oracle

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.

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/

SQLPlus Command Line History


Wow, I can’t believe I’ve not considered this before! SQLPlus is a great tool, I use it all the time as it’s great for doing little bits and pieces where you don’t need the tools in SQL Developer (which is my preferred client for most big stuff) or you don’t want to go to the trouble of starting up a GUI. My major gripe with it has always been it’s almost complete lack of a command line history feature which is astounding considering how long that kind of feature has been available for other shells (bash, psql, mysql, even the Windows Command Prompt since XP).
Anyway there is a workaround to ‘add’ the feature! You’ll need to install the ‘rlwrap’ package which is a

…wrapper that uses the GNU readline library to allow the editing of keyboard input for any other command. Input history is kept between invocations, separately for each command; history completion and search work as in bash and completion word lists can be specified on the command line.

You can get that from Sourceforge or in my case I found it in the Ubuntu Universe repository (there’s rpm’s on Sourceforge) so I just did:

$ sudo apt-get install rlwrap

You can call SQLPlus from rlwrap like this:

$ rlwrap sqlplus user@database

or to make things simpler use alias:

$ alias sqlplus='rlwrap sqlplus'

all done.