Saturday, March 30, 2013

Data Guard Real time apply check

Physical standby can be in two modes. Real-time apply or Archive log shipping mode.
In real-time apply mode, the LGWR from the primary writes to the standby redo log at the target site.
Comparing archive log sequence numbers is not the correct way to check if the dataguard is operating correctly.
I have written the below script to check if the real time apply is working properly.

This works on the dataguard physical standby database.
It checks two columns (value and Datum_time) in V$dataguard_stats.


#!/bin/bash
################################################################################
## File Name : /adg_lag_check.sh #
## Description : Job to check gap in real time apply for DG #
## Author : Harris #
## Call Syntax : No parameters, writes local log file #
## Last Modified: 28/Mar/2013 By Harris #
################################################################################
export ORACLE_HOME=/u01/app/mydb01/oracle/product/11.2.0.3/db
export ORACLE_SID=mydb01
export PATH=$ORACLE_HOME/bin:$PATH
DATE=`date '+%m%d%Y%H%M%S'`
DBA_GROUP='harris@company.com'
sqlplus -s / as sysdba << EOF > adg_lag.log
set head off
set numf 99999999999
set feedback off
set echo off
set serveroutput on
select CASE WHEN
((extract(second from to_dsinterval(value)) + extract(minute from to_dsinterval(value)) * 60
+ extract(hour from to_dsinterval(value)) *60*60 + extract(day from to_dsinterval(value)) *60*60*24)
> 900) THEN 'Critical Data Guard lag more than 15mins'
WHEN value is null THEN ' Critical Data Broken status'
WHEN (((sysdate - to_date(DATUM_TIME,'MM/DD/YYYY HH24:MI:SS'))*24*60) > 15) THEN 'Critical Data Guard Network broken'
ELSE 'Data Guard OK'
END
from v\$dataguard_stats where name='apply lag';

EOF

if grep -c 'Critical' adg_lag.log; then
cat adg_lag.log | mailx -s "DataGuard Problem" $DBA_GROUP
fi;


Wednesday, March 27, 2013

Redo logs on Physical Standby

Redo logs on Physical Standby Online redo logs on a physical standby database is not really required. For real-time apply , standby redo logs are a must on standby site.

Standby redo logs are optional at the source site, but always advisable to be prepared for switchover or failovers.

If DB_CREATE_ONLINE_LOG_DEST_1 is not set and
DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST is specified,
the redo logs are created multiplexed each under DB_CREATE_FILE_DEST and DB_RECOVERY_FILE_DEST

If you need to move the redo log to another diskgroup or another filesystem on standby database, these are the steps


--cancel the recovery
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

--change the Standby File Management
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';

--Clear the redo logs
ALTER DATABASE CLEAR LOGFILE GROUP 2;

--Drop the redo logs
ALTER DATABASE DROP LOGFILE GROUP 2;

--Add the new logs at the correct locations
ALTER DATABASE ADD LOGFILE THREAD 1 GROUP 2 ('+REDO_DG1','+REDO_DG2') size 1024M;

-- If you need to add standby redo logs, do so...
ALTER DATABASE ADD STANDBY LOGFILE THREAD 1 GROUP 5 ('+REDO_DG','+REDO_DG2') size 1024M;



Put the standby database back into action


ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Wednesday, March 20, 2013

EMACS and SQLPLUS

I have recently discovered EMACS thanks to the unfaultering effort by my co-brother Roy. I have only one word for EMACS- fan-"bleedy"-tastic.
Although not very intuitive, it is immensely powerful and extremely great to improve productivity.

I will break this blog into three sections for easier reading.

Section 1 - How does this fit into my work?




Remember the days spent with a SQL in a notepad, and server has no scripts. The day drags on with you running Ctrl+C, Ctrl+V , then right click into the Putty Console.


Now here is how it happens in Emacs.

So start the SQL-Plus buffer in Emacs. This should be inbuilt inside the 24.2 version onwards.

M-x sql-oracle
It should prompt for a userid, password and host name. Hostnames is the TNS names entry in your local tnsnames.ora file.

If you get the below error, like I did
ORA-12557: TNS:protocol adapter not loadable

Set ORACLE_HOME and PATH to same Oracle home(I have two Oracle Homes in my laptop) .Restart EMacs
Try M-x sql-oracle, again
This time hopefully you should get connected.
You are on the command prompt so you try any of the SQLs.

Now the interesting bit of working efficiently.
C-X, C-f

Then enter a existing or new SQL file name.

Your screen should split with the SQL file and the SQLPlus buffer in another.

Use C- to set the mark and move the keys to select the whole SQL

To send the selected SQL into SQLPlus buffer use the C-C C-r

Section 2 - Errors I encountered



Some of the errors I encountered were --

If you get the below error,

No SQL process started

You can try below

M-x sql-set-sqli-buffer
select *SQL* ( should be the default)

That lead me to another error

There is not suitable SQLi buffer

To fix this error, note the bottom of the opened SQL file. The status bar should say SQL[Oracle].

If it says SQL[ANSI], it needs to be changed by doing

M-x sql-set-product
key in "oracle"

Now the "No SQL process started" error can be fixed by using the M-x sql-set-sqli-buffer like above.

Now use C-X C-r to send the SQL from the file to the SQL buffer.

Section 3 - Couple of other hacks.




You must have noticed that pressing the backspace deletes the "SQL>" prompt. To fix this , add this to your .emacs file

(setq comint-prompt-regexp "^SQL>")

(custom-set-variables
'(comint-prompt-read-only t)
'(comint-use-prompt-regexp t))
(custom-set-faces)




PS:

This blog was written in EMACS

Roy's awesome blog on technology can be found here