Smart Database Technology

I have been using Windows for a long time for my personal desktop although for development platforms I always preferred Linux. One of our developers has been a Mac fan for a long time and as the team grew more and more people started to use Mac’s. So there was no stopping the momentum and I took the plunge.

So I have been using Mac OS X for about 5 months and am very happy I have made the switch. Mac OS X is not perfect, I think it has some shortcomings but overall I think it is a much better OS to Windows, especially for a development platform.

Mac OS X advantages:

1. Much more complete OS. All the major tools that you need are there, such as ssh, ftp, scp, vi etc.
2. Full bash terminal available allowing you automate daily tasks such as backups.
3. Time machine is a great backup utility.
4. Some great Mac software available (see below).
5. No more frequent updates and daily virus scans.
6. It is fast and efficient.
7. Instant on.
8. Much better insight into what the OS is doing and where it is spending its resources, allowing you to take action if things slow down too much.
9. Security model is much better than Windows.
10. No more Windows services and registry!
11. Level of frustration with personal computers has gone down.
12. I really like the Dock.

Mac OS X disadvantages:

1. I am not a fan of Finder. I prefer Windows Explorer that has the folder tree on the left and the file tree on the right. I know there are different options for Finder, but I have not found an option that works for me.
2. No “send to” option allowing you to send a document to an email or other application. I have yet to figure out how to attach a document to an email outside of Thunderbird.
3. No “New document” option allowing you to quickly add a new document in a specific location.
4. Inconsistencies in keys. “End” key goes to the bottom of the document in some apps rather than the end of the line.
5. “Alt-tab” only cycles through the applications and not the windows of each application.

As you can see my disadvantages are relatively small compared to the advantages. There is no going back for me. I did find the following shortcut keys for Mac OS X helpful: http://support.apple.com/kb/ht1343

Favourite Mac software:

1. Open Office
2. Thunderbird
3. SynchroSVNClient
4. UltraEdit for Mac
5. OmniGraffle (Visio for Mac)
6. Things (todo list)

X11

One of the benefits of Mac OS X is that it comes with a full X Windows System. This means that we can install Oracle on a remote system with full GUI interface. Previously it was very tricky to connect to a remote server to install Oracle through the full GUI interface. On Mac this is now really easy.

Start X11, connect to the remote server using ssh, and type “dbca”. The output is as below:

Running dbca through X11 on Mac OSX

Still to try:

There are still a few more things I want to try on the Mac. This includes Virtual Box for Mac using Oracle’s virtual box templates.

Dbvisit version Dbvisit 5.3.12 has been released. The complete list of new features and fixes are below.

New features:

1. Automatically run Dbvisit archive log gap (dbvisit -i) when Dbvisit is run on the primary server. Can be turned off with variable RUN_INSPECT.
2. Remove temporary Dbvisit files and closing shared database session after creation of standby database.
3. Archive management log name can now be set in DDC file with ARCHSOURCE_LOG and ARCHDEST_LOG. If not set then default value is _arch_management.log
4. Global RECOVERY_PARALLELISM introduced to control parallelism for database recovery.

Fixes:

1. Misleading message regarding the DDC file name when BINDIR set to wrong location – fixed.
2. Extra comma removed from an sql query to obtain DDR version.
3. Dbvisit errors when the last non archived sequence cannot be obtained from data dictionary on the primary – fixed by setting sequence as max archived sequence +1.
4. Improved locking introduced to control locking when previous version of dbvisit executable is still running.
5. Do not send log gap report when TRANSFER_LOG table empty or with -R option – this disables copying installation to the standby.
6. Global RECOVER_NOPARALLEL11 made redundant and removed.
7. A query to obtain log_archive_dest for Oracle 8/9 changed to accommodate various values that optimizer_mode can be set to.
8. Global variables related to a server role (primary or standby) rather than a server name, are now not reversed after Graceful Switchover (GS).
9. Dbvisit does not handle creating standby database with filesystem OMF files using transportable media – fixed. Now uses RMAN COPY command instead of operating system COPY command.
10. Bug with with managing trace file. Managing archives and traces should be separate and should not interfere or be dependent upon each other – fixed.

Dbvisit can be downloaded from http://www.dbvisit.com/dbvisit_download.php

If you memory dates back to the pre-10g times, you will maybe recall the steps to enable archivelog mode on the database at that time. Perhaps the greatest catch was that the online logs did not get archived automatically by default – you had to do it manually.

Or, let Oracle come to rescue, and let the ARCH process(es) do it for you by starting them with log_archive_start = true (or even dynamically with alter system archive log start/stop). You could even start ARCH in noarchivelog mode, so they did completely nothing, just writing error messages to alert.log.

One of the new features of 10g was getting rid of all of this – ARCH starting is governed solely by (no)archivelog mode and the DBA does not need to fiddle with anything else to get it right. Great applause, many thanks, scene over.

Well, all of the above is just a short transcript of my memory of those great times, and I lived with it happily and well till this week.

As it turns out, the story with 10g is not so straightforward – although it is true that if you just set no/archivelog mode that ARCH works as expected, the manual way of archiving was in fact not removed. But the way to enable it now:

To enable manual archiving mode, use alter database archivelog manual. The rest is the same as with 9i – use alter system archive log commands.

One consequence of this change – the log_mode in v$database can be archivelog, noarchivelog (as in 9i and before) or manual (from 10g on). This also means that if you want to detect whether a database runs in archivelog mode, you have to check for manual log_mode as well.

We confirmed yesterday with Thomas Salmen, CTO at Orcon*, that he’ll be speaking at our Disaster Recovery Strategies for Oracle Standard Edition breakfast seminar on 27th April in Auckland.

Thomas will cover the thinking behind Orcon’s disaster recovery strategy and the steps they took to implement their solution.

This breakfast is one of two FREE seminars held in Auckland and Wellington on the 27th & 28th respectively. Delivered in partnership with Oracle NZ and supported by the New Zealand Oracle User Group (NZOUG), the topics covered include:

• how to develop your DR strategy
• database backup and recovery solutions available to Oracle users
• Oracle Enterprise and Standard Edition commercials
• 3rd party and cloud leveraged DR solutions
• solution cost profiles

The seminars aim to arm IT Managers, Senior DBAs and Architects in businesses that rely on Oracle to store their data, with the information they need to ensure their data is always protected and available.

For more information and to register for this event go to our Disaster Recovery Strategies for Oracle Standard Edition page.

* Orcon uses Dbvisit as part of their DR infrastructure.

One of the nice features of Oracle VM (and perhaps the most compelling one when you are evaluating different virtualization products for testing and development) is the comprehensive list of prepackaged templates available at Oracle e-delivery.

The list is not complete and probably cannot ever be, as Oracle is expanding it’s product offerings very fast. Of the missing basic configurations, there is for example no Oracle Database on Solaris template (although plain Solaris is available).

However, let’s look at the RAC templates. The list is comprehensive and you can choose between 11.1 and 11.2 on Oracle Enterprise Linux, both 32- and 64-bit.

The first-time setup is quite easy and easy if you follow supplied pdf documentation. The usual setup is 2-node RAC, but you can semi-manually add further nodes as well.

Still, there is a catch: the pdf declares that one of the prerequisities is to have two physical network cards. Strange, isn’t it? After all, a RAC node needs just one network card for the outside world, the second one connects just to the other nodes and should be virtual only, right? Well, not according to the docs. You have to set the second network interface of the RAC node to use xenbr1 (second network bridge) to connect to other nodes, but this bridge is simply not present in Oracle VM if you have just one physical network interface.

If you ignore the requirement and go on with the VM creation, you will face problems very soon: after you specify primary/secondary node (the first question after startup), the nodes don’t see each other. Or, they see each other, but after that, connectivity tests fail on eth1. (This differs on your choice of template used.)

The solution is in fact simple: if RACs need a separate xenbr1 bridge interface, just give it to them:

• create the bridge: brctl addbr xenbr1
• bring the bridge up: ifconfig xenbr1 up

And then, according to the docs, set the VIF1 interfaces to use xenbr1.

If you do it by trial and error as I did, you may up end with VM assigning the VIFs incorrectly to the bridges (both to the same one, etc.). In that case edit vm.cfg for you virtual machine (in /OVS/running_pool/) and check that VIF0 is really set to xenbr0 and VIF1 to xenbr1. But if you first setup the xenbr1 and after that start creating your RAC, you should be fine.

As usual, to make the configuration permanent, you must make sure it persists reboots. Just adding the two aforementioned commands to /etc/rc.local should do the trick.

We confirmed last night with David Hicks, Senior Oracle DBA at NZX*, that he’ll be speaking at our Disaster Recovery Strategies for Oracle Standard Edition breakfast seminar on 28th April in Wellington.

David will cover the thinking behind the strategy that they put in place and the steps they took to design the solution they ultimately put in place.

This breakfast is one of two FREE seminars held in Auckland and Wellington on the 27th & 28th respectively. Delivered in partnership with Oracle NZ the topics covered include:

• how to develop your DR strategy
• database backup and recovery solutions available to Oracle users
• Oracle Enterprise and Standard Edition commercials
• 3rd party and cloud leveraged DR solutions
• solution cost profiles

The seminars aim to arm IT Managers, Senior DBAs and Architects in businesses that rely on Oracle to store their data, with the information they need to ensure their data is always protected and available.

For more information and to register for this event go to our Disaster Recovery Strategies for Oracle Standard Edition page.

* NZX uses Dbvisit as part of their DR infrastructure.

Dbvisit version Dbvisit 5.3.10 has been released. The complete list of new features and fixes are below.

New features:

1. Introduced new variable ARCHTMP to enable temporary location of ASM archives on primary server before transport. This is different from the location specified by ARCHDEST.
2. Increased searchability for Dbvisit to find archive log files in ASM.
3. Improved performance for validating files by checking unique paths only during creation of standby database.
4. Improved algorithm of recovering new primary database during last stage of graceful switchover. Allows primary database archive logs with a different format from the standby archive logs. This is usually the case when log_archive_dest on primary points to a name of an ASM diskgroup.
5. Support of %%ORACLE_SID%% variable in log_archive_format for Windows.
6. Improved algorithm of finding archive log destinations on the primary server.

Fixes:

1. When ARCH_ALT_LOCATION is set to a valid directory on the primary server, Dbvisit should only check this directory for archives to transfer to the standby server – fixed.
2. Dbvisit failed to manage trace files on Windows platform – fixed.
3. Reverse variables ORACLE_SID_ASM and ORACLE_SID_ASM_DEST after Graceful Switchover.
4. During graceful switchover in RAC, Dbvisit may not ship all subsequent archives after RAC instance shutdown – fixed.
5. Avoid shipping duplicate archive during graceful switchover – fixed.
6. Record thread# into Dbvisit repository table for single thread databases.
7. Dbvisit no longer does multiple log switches on the RAC instance during graceful switchover when RAC_TAKEOVER=Y and one RAC node is down.
8. Allow for mixed language messages generated by Oracle server during standby database recovery.
9. Recover standby database with a APPLY_DELAY_LAG_MINUTES clause issue – fixed.
10. Verify directory existence on remote server issue – fixed.
11. Issue with WIN_SERVICE_WAIT for Windows that resulted in reducing the WIN_SERVICE_WAIT value by 5 seconds automatically – fixed.

Dbvisit can be downloaded from http://www.dbvisit.com/dbvisit_download.php

Dbman shows us his other side and manages to win over a harassed CFO in the process!

Formed in August 2004 Reeg Co. provides a range of IT consultancy services with a focus on Oracle products and database administration. They also develop custom applications, using Oracle Developer and PHP, and implement business intelligence projects.  Reeg’s Founder and Dbvisit advocate Ramon Estevez (right) is an experienced Oracle consultant having worked with their products for more than 12 years and has a bachelor’s degree in computer science.

Although the US to Dominican Peso exchange rate (37.5 DOP=1USD) places pressure on all business in The Dominican Republic, Ramon’s challenges are more to do with long sales cycles and the need to convince businesses about the necessity of having a robust disaster recovery solution in place. The financial services sector is one that understands this requirement only too well. Reeg’s latest sale to Banco Caribe, one of the Carribean’s largest financial institutes, is testament to this fact.

Ramon consulted with Banco Caribe for two years. Initially they tried replicating using RMAN but after finding and testing Dbvisit Standby he recommended this as a superior solution.

‘The ease with which the solution could be implemented, along with the price and the support provided during this period was the main draw card for the Bank. The fact that I had worked so closely with them for a long time and was endorsing Dbvisit Standby also meant they could be certain that it would provide them with a robust solution’ explains Ramon. The configuration chosen by the bank assures them maximum disaster recovery coverage with two standby databases, one in the head office data centre in Santo Domingo and the other approximately 160kms away in the  second biggest city of the country, Santiago de los Caballeros.

For more information about Reeg Co, SRL and the services they offer please see http://www.reegco.com/

We recently received a letter from one of our long standing customers, Jill Salo, Oracle DBA at Westpeak Global Advisors about her experience using Dbvisit due to a real life database failure.

Hi Arjen,
As I mentioned a few weeks ago, we lost our production database and had to failover to our standby using Dbvisit.
I thought you would appreciate some technical details. Feel free to share our experience if you feel it would be helpful to others.

OS version: Linux OEL5
Oracle version: 10.2.0.4 Standard Edition.
Storage: Lefthand Networks (now HP) iscsi SAN, mounted as ext3
Cause of crash: I suspected our mounted volume had a corruption as we were seeing a significant degradation in I/O performance. After many database checks, we decided to run Iometer to check if the disk speeds were below par.

I ran Iometer on the volume that held the database files.

We think Iometer may have hit some corruption because this caused us to lose the whole file partition. Even after rebooting, the system could not find that partition to mount. It had simply disappeared. The SAN partition was still there and available.

Using Dbvisit, we were able to switch over to the standby database in a few minutes. The switch was simple and with no errors. The only problems we encountered after that were network issues on our side.
1. We had tested with one subnet (subnet2) but as an oversight, computers on subnet3 could not see the database. This was rectified by some network configuration changes (by the network admin).
2. The firewall had the default timeout setting of 2 hours. We have some long running jobs that process mostly in Java with occasional Oracle calls. These jobs would fail with the “Connection reset by peer” error message. We set this timeout to the max of 12 hours which seemed to fix the problem.

When we first installed Dbvisit, it did not include the option to create a standby using the Dbvisit software. In a crisis situation, it was a very helpful option and once again it worked without errors. Using the menu I was able to recreate the standby by navigating a few menus. The final switch back to our production server worked seamlessly as well. It did take a couple of hours due to our large and numerous redo logs (write intensive database) but there were no problems.

Thanks again for a superb product. It keeps getting better and has once again saved the day!

Sincerely,

Jill Salo
Oracle DBA
Westpeak Global Advisors