April 26, 2009
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.
April 12, 2009
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.
March 30, 2009
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.
March 23, 2009
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 ….
January 2, 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.
November 13, 2008
Recently i have had the opportunity to finally plunge into the world of Dataguard and i was pleasantly surprised by the ease of setup and use for Dataguard. The steps below were used to create a physical standby database. The setup was Oracle 10.2.0.4 on AIX .
These steps do not account for a FAL server at this point.
Prereqs
1. Primary database has to be in archivelog mode
2. Standby redo log files are beneficial and even though not required for physical standby should be created.
3. log_archive_dest_2 needs to be configured which log_archive_dest_state_2 set to defer till the standby is up.
Steps.
1. create a backup of the primary database either on disk or tape.
run {
backup database plus archivelog;
backup current controlfile for standby ; —– important step
}
2. create an init.ora file and move it to the standby host.
3. modify the init.ora to include a db_unique_name different from primary
4. add db_dile_name_convert and log_file_name_convert if the filesystem structure is not going to be identical to the primary.
5. restore the database on the standby host
On the Standby Host.
rman target abc/abc@def
conn auxiliary /
run {
startup nomount;
duplicate target database for standby dorecover;
}
6. Ensure tnsnames.ora is current and tested ont he primary and standby hosts.
7. Start shipping redo by changing log_archive_dest_state_2=enable on the primary.
You have a physical standby up and running. I’ll post some tests and monitoring scripts as well as conversion to a logical standby in future posts
October 30, 2008
I thought i should make a quick blog post of some books that have helped me alot as quick reference and as an alternative to the manuals as a fast grab and look books
I’ll start with Books on Rman in this first post on books .
Oracle RMAN Pocket Reference
is a very good handy guide to rman syntax and commands though it seems it has not seen an update in a while.
Robert Freeman has a wonderful set of books for Rman in both the 9 and 10 flavors. I keep both at hand for help supporting my 9 and 10 Databases. They have helped me tremendously in supporting backups and restores for my databases
9i Rman Backup And Recovery
10g RMAN Backup & Recovery
I would definitely recommend these as must on hand books for any recoveries or backup help.
October 28, 2008
The tweets have been pouring in about Carl Backstrom a prominent Apex evangalist and Product manager at Oracle that died in a car crash yesterday. Jow Kallman has some detail about the contact with the family and about getting a trust opened for Carl’s Daughter here .
I had the oppuruntunity to meet Carl at Open world this year and had been following and learning about APex thru Carl for a while a great loss indeed.
RIP @carlback
Soem info about carl backstrom can be found at this blog http://carlback.blogspot.com
Filed under:
misc by admin
