This blog is migrating

I've been contemplating this for a while and after a couple of tries have finally moved to hosted wordpress setup.
http://www.fuadarshad.com and http://www.fuadarshad.net are live. The RSS feeds are already shifted in Feedburner. So People subscribed to the RSS feeds will see no difference.
Still tweaking my website and will be updating it with the new stuff i find and do .
PS As part of my import you might see this here and on my new blog.

Oracle Buys Sun

There was an interesting development this week with Oracle Announcing that it has entered into a definitive agreement to purchase Sun Micro system. A couple of weeks a go it was all but sure that IBM would nab Sun. But last week the announcement from Oracle Surprised a lot of people. With the purchase of Sun Oracle gets MySQL , Java , Solaris , Sun Hardware and a whole bunch of Open source.
Details are at http://www.oracle.com/sun
and a twitter search for oracle buys sun brings up lots of entries . #oracle also became a trended topic on twitter. It will be interesting to see how the deal works out for both sun and oracle and most importantly for customers of both.

User Defined Metrics in Oracle EM Grid Control

Everyone has various ways to manage scripts. I have found UDM's in Oracle Enterprise manager to be a nice way to catalog and standardize scripts across various platforms.
UDM's or User Defined Metrics are a great way to track, alert and look at trending information using Oracle Enterprise manager . Oracle provides UDM's on a server level as well as an instance level. UDM's can be cataloged and saved into a library for reuse.

I've been using SQL UDM's to monitor and alert on SQL Apply Lag times in minutes as well as restarting the logical dataguard environment .
To see if Sql Apply has stopped i check select count(*) from v$dataguard_stats and based on the count 3 for good and 0 for bad run a corrective action to start the sql apply again. If the corrective action fails . The alert is then escalated to the pager for support. Corrective Actions based on UDM's if scripted right can help reduce support calls and keep environments in sync.
I've used UDM's to pick up data from DBA_LOGSTDBY_EVENTS and execute alter database datafiles commands to keep my primary and standby in sync.
The UDM creation screen is fairly simple and the wizard walks the user through all the information . If using external scripts i would definitely recommend creating user that manages the scripts and then setting preferred credentials at that user level. This makes it easier to change passwords using the emcli command line interface and be compliant with audit policies.

Applying Datafile resize in logical standby

Well,
with the record_unsupported_operations-true . I was able to capture the commands that oracle skipped. using that info i was able to write a simple piece of code to use that information to get
Alter database datafile commands executed on my logical standby database which in this case has exactly the same structure as my primary.

/* Program Name : Datafile Alter command capture
Author : Fuad Arshad
Purpose : To capture alter database datafile commands from dba_logstdby_events
and execute on logical standby
Prerequisites : dbms_logstdby.apply_set('RECORD_UNSUPPORTED_OPERATIONS','TRUE');
dbms_LOGSTDBY.apply_set('MAX_EVENTS','a value of your choosing based on events ');
Licensed : Creative Commons.
*/

set serveroutput on
declare
p_errbuf varchar2(500);
p_retcode number(1);
cursor dba_events is
SELECT to_char(event) event1 FROM dba_logstdby_events WHERE status_code='16226'and event_timestamp>systimestamp-1
and event like 'ALTER DATABASE DATAFILE%';
begin
for
dbevent in dba_events LOOP
DBMS_OUTPUT.PUT_LINE('Statement : ' || dbevent.event1 );
execute immediate dbevent.event1;
end LOOP;
EXCEPTION
when no_data_found then
null;
when others then
p_errbuf := sqlerrm;
p_retcode := 1;
DBMS_OUTPUT.PUT_LINE('Error : ' || p_errbuf|| 'Error Code' || p_retcode );
end;

This is a very simple way of making sure all the datafiles on the standby look exactly the same as the Primary. I'm sure there are many other ways to do this and if there are please share how u would have taken care of the alter database datafile issue. Comments and improvements as well as ideas are always welcome.

DBMS_LOGSTDBY and recording unsupported operations

DBMS_LOGSTDBY is the heart and soul of the logical standby management infrastructure . While trying to figure out how to get my alter database datafile operations . i bumped in a record unsupported operations in $logstdby_stats . There seems to be no mention in the documentation and no reference to this parameter being set anywhere . If you to set it though


>exec dbms_logstdby.apply_set('RECORD_UNSUPPORTED_OPERATIONS','TRUE');

This will help capture the unsupported operations as the ORA-16226 operations. I am planning on then reading the clob and re executing the statements onto the logical standy. I still have to figure that part out but stay tuned ....

Beware of Recycle bin objects and drop table triggers

Just this morning i was asked to investigate why a simple insert was failing . The error the users were getting was


INSERT INTO "ABC" ( "ABC1", "ABC2", "ABC3", "ABC4" ) VALUES
( 'ABC1', 1234, 'N', 'NN' )

ORA-20009: Cannot drop table Drop Not Authorized
ORA-06512: at line 14)

While we knew this was a error generated by our custom trigger that we have implemented for securing our databases from malicious application table drops. We were a little puzzled as to why a simple insert would invoke our table drop prevention trigger.

After a little dose of 10046 this became apparent to us

PARSING IN CURSOR #3 len=62 dep=1 uid=0 oct=12 lid=0 This was going on
tim=48271684044651 hv=0 ad='1045a248'
drop table "APP_NAME"."BIN$YQPns5G0IL7gQzwEPI0gvg==$0" purge
END OF STMT
PARSE #3:c=0,e=450,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,tim=48271684044648



The row insert required the table to extend to a segment that was previously owned by a dropped object . To clear space Oracle actually issues a drop table purge when it needs to extend an existing object.

We are now modifying our security trigger to exclude BIN$ objects .

Edait: after a question in the comments i have now validated that an implicit commit will happen if an insert and purge recycle bin are executed . i am going to test creating an outof space and reuse situation to see if that would cause an implicit commit too. Thanks to Chen Shapiro to point ing out the implicit commits in DDL statements.

Update: Oracle manages the space management operation outside of a transaction and a commit will not happen to the operations that have happened in the outlying transaction. it seems purge dba_recyclebin and an internal drop table purge for space management by oracle are handled differently

oracle issues

Oracle Dataguard Broker and CFC

During a recent implementation of Dataguard on  a Active/Passive Cluster. I started receiving weird errors using the installation/configuration phase. The Grid control add standby wizard which is something i like to use for smaller databases started out the the process  but would never complete it leaving the primary and the standby in a weird state.
Errors like

DMON: cannot open configuration file "/usr/local/oracle/product/10.2/dbs/dr2test.dat"
  ORA-27037: unable to obtain file status

 error = ORA-16572
DG 2009-01-15-08:26:48        0 2 0 NSV0: Failed to connect to remote database p
rkmdb1. Error is ORA-12541
DG 2009-01-15-08:26:48        0 2 0 NSV0: Failed to send message to site prkmdb1
. Error code is ORA-12541.
 DMON: test unable to contact primary for DRC version check
  DMON: status ORA-12541:
~

The problem is that the dataguard broker cannot traverse through a  virtual  node name which is used in a Active/Passive or CFC  configuration,
According to Metalink Note # 413696.1 which is very appropriately named "Data Guard Broker does not support Cold Failover Clusters" 
"The Data Guard Broker does not support Cold Failover Clusters (also called Active/Passive clusters) in any version up to and including Oracle Database 10g Release 2. You cannot use the Broker nor Enterprise Manager, you have to create and manage your standby configuration using SQLPlus"

The support for  CFC's come in a limited fashion in Oracle 11gR1 only if Oracle clusterware is used.

This means that a dataguard standby has to be manually created and configured and all instances have to be monitored individually.

dataguard, oracle

Happy new year 2009

Happy New Year 2009 to everyone. Hopefully this new year gives me more time to write about the things i do and the things i like to do.