Smart Database Technology

After a switch-over or fail-over to an Oracle Standby database using Dbvisit Standby, the Oracle Database Administrator (DBA) faces the challenge of switching clients to the new activated standby database which is now running as the new primary database. Previously these clients were connected to the former primary database, and so they must be reconnected to the new primary database.
Dbvisit Standby as well as Oracle Data Guard does not provide a feature to support this.

Generally there are two options for the DBA to achieve this:

1. Change of the database host using either the
   1. connect string or
   2. DNS.
2. Use of the NET8 listener fail-over.

1 – Change of database host
To begin lets look at option 1. Our examples use the following host and service names:

host name primary server: dbprod
host name standby server: dbstandby
service name:             orcl
net service name:         orcl_ha

In the case of example 1-a, we are going to change the host name within the TNS connect string after switch-over/fail-over or change the IP from primary server’s to standby server’s.
The tnsnames.ora entry:

orcl_ha=
	(description=
		(address = (host = dbprod)(protocol = tcp)(port = 1521))
		(connect_data = (service_name = orcl))
	)

will be changed after the switch-over/fail-over into:

orcl_ha=
	(description=
		(address = (host = dbstandby)(protocol = tcp)(port = 1521))
		(connect_data = (service_name = orcl))
	)

Or alternatively by using a JDBC-connect:

jdbc:oracle:thin:@dbprod:1521/orcl

will be changed after the switch-over/fail-over into

jdbc:oracle:thin:@dbstandby:1521/orcl

This solution seems to be the easiest way if the changes to be made are limited, or on a single point. This type of configuration is mostly found with clients that have a:

1. centralised tnsnames.ora, or
2. net service resolution via Oracle Internet Directory, Active Directory or
3. any connect string at the Application Server.

This method is only efficient in a centralised names configuration.
The DBA could switch to option 1-b if there is a DNS alias used for the name services. In addition to the host names defined above we define an extra DNS CNAME as dbha, which initially points at the host dbprod.
The tnsnames.ora as well as the JDBC connect string stay as they are after switch-over/fail-over:

orcl_ha=
	(description=
		(address = (host = dbha)(protocol = tcp)(port = 1521))
		(connect_data = (service_name = orcl))
	)

or:

jdbc:oracle:thin:@dbha:1521/orcl

After failover the DNS CNAME dbha is redirected to the host name dbstandby.
The advantage of this method is that it works well for centralised as well as for the de-centralised names configuration services. The disadvantage with this method is that the connect fail-over can have serious time delays, because the new DNS configuration first has to be replicated to the DNS servers. The name service caches of clients also may need to be flushed. So even though the database might be online and available, some of the clients may still not be able to connect because of the DNS update being delayed.

2 – Listener fail-over
The most recommended and most flexible solution is using a dynamic connect listener fail-over. To achieve this the DBA defines dbprod as well as dbstandby into a common connect string and to establish a fail-over between them both.
The related tnsnames.ora entry would be as follows:

orcl_ha=
	(description=
		(address = (host = dbprod)(protocol = tcp)(port = 1521))
		(address = (host = dbstandby)(protocol = tcp)(port = 1521))
		(failover = yes)
		(connect_data = (service_name = orcl))
	)

Also for JDBC the DBA would have to use the complete connect string:

jdbc:oracle:thin:@(description=(address=(host=dbprod)(protocol=tcp)(port=1521))(address=(host=dbstandby)(protocol=tcp)(port=1521))(failover=yes)(connect_data=(service_name=orcl)))

In this configuration it’s essentially to ensure that only the listener at the present primary server is running. Otherwise it’s possible that you run into the problem that the client connections are directed to the standby database, which produces the following error message output:

ORA-01033: ORACLE initialisation or shut down in progress

If the listener is controlled by either Oracle Clusterware, Oracle Grid Infrastructure or Oracle Restart it would be possible to fully automate the start and shut downs of the listeners. This is done by integrating a control resource at the primary and standby server.
For example, create an action script, which is saved as /usr/bin/dbv_lsnr_ctrl.sh and that can be run by the Oracle user:

#!/bin/sh
# Listener status control script for use with Dbvisit standby
# - prevents listener from starting if the corresponding instance has standby role
#
unset DEFAULT_DBV_HOME PRIMARY_ROLE_EXPR INSTANCE_NAME
STANDBY_ROLE_EXPR="^Standby Database"
DEFAULT_DBV_HOME=/u01/app/dbvisit
INSTANCE_NAME=${_USR_ORA_SRV}
# set default Dbvisit Standby home path, if not already set in environment
if [ -z "$DBV_HOME" ]; then
        DBV_HOME=${DEFAULT_DBV_HOME}
fi
case $1 in
    # handle start request and check equally
    # (start/report ok, except when on standby side and instance is running)
    start|check)
        ! ${DBV_HOME}/dbv_oraStartStop status ${INSTANCE_NAME}|grep "${STANDBY_ROLE_EXPR}" >/dev/null 2>&1
        exit $?
        ;;
    # stop request always can be fulfilled
    stop)
        exit 0
        ;;
esac
# paranoia exit
exit 1

Within the script (or alternatively at the environment variable DEFAULT_DBV_HOME) the DBA should define the Dbvisit Standby installation path. In addition the following control resource should also be created on the primary:

crs_profile \
	-create dbv_lsnr_ctrl \
	-t application \
	-a /usr/bin/dbv_lsnr_ctrl.sh \
	-h dbprod  \
	-p restricted \
	-o as=restore,ci=60,ft=0

On the standby we use dbstandby:

crs_profile \
	-create dbv_lsnr_ctrl \
	-t application \
	-a /usr/bin/dbv_lsnr_ctrl.sh \
	-h dbstandby  \
	-p restricted \
	-o as=restore,ci=60,ft=0

In directory $CRS_HOME/crs/public the following .cap file will be created: dbv_lsnr_ctrl.cap.
The database name needs to be entered at line USR_ORA_SRV= exactly the same way that Dbvisit Standby commands are executed:

USR_ORA_SRV=orcl (e.g. at "dbvisit orcl")

The action script (/usr/bin/dbv_lsnr_ctrl.sh) is called with a parameter which is used to call the Dbvisit Standby command to obtain the database status:

dbv_oraStartStop status

To add the control resource to Oracle Clusterware:

crs_register dbv_lsnr_ctrl

Finally the dependency of listener and control resource needs to be defined:

crs_profile -update ora.LISTENER.lsnr -r dbv_lsnr_ctrl

After restarting the listener the dependency on the control resource is created. This determines the status of the database as reported by dbv_oraStartStop, whether it is started or not. If dbv_oraStartStop detects a database in standby role, the control resource goes down and prevents the listener from starting. Otherwise it starts and the listener is allowed to run.

Thanks to author: Thilo Solbrig (Oracle Certified Master) at Aspicon – Dbvisit Partner

Oracle provides the Database Upgrade Assistant (DBUA) to automate the upgrade process and makes appropriate recommendations for configuration options such as tablespaces and redo logs. The DBUA also provides support for Real Application Clusters (RAC) and Automatic Storage Management (ASM).

One of the options of DBUA is to temporary switch to NOARCHIVELOG mode during the upgrade to speed up the upgrade. However if the database has an associated standby database, then switching on NOARCHIVELOG will invalidate the standby database with Oracle SE and SE1. In Oracle EE, the redo logs can be used by Data Guard to keep the standby database up to date and this may not be an issue.

The reason that it invalidates the standby database is that there will be a gap in the archivelogs during NOARCHIVELOG and the standby database requires all archives logs in sequence to keep the standby database up to date.

Example:
During an upgrade with DBUA the database was switched to NOARCHIVELOG mode as can be seen from the following query:

SQL> select sequence#,name from v$archived_log where sequence#>4380;
SEQUENCE#	NAME
4381	E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\2011_10_23\O1_MF_1_4381_7B5PN1LT_.ARC
4382	E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\2011_10_23\O1_MF_1_4382_7B5T4KQK_.ARC
4383	E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\2011_10_23\O1_MF_1_4383_7B5XO1LJ_.ARC
4384	E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\2011_10_23\O1_MF_1_4384_7B615KV4_.ARC
4385	E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\2011_10_23\O1_MF_1_4385_7B64P1KQ_.ARC
4386	E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\2011_10_23\O1_MF_1_4386_7B686KYQ_.ARC
4387	E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\2011_10_23\O1_MF_1_4387_7B6CQ1D2_.ARC
4388	E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\2011_10_23\O1_MF_1_4388_7B6H7O6B_.ARC
4389	E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\2011_10_23\O1_MF_1_4389_7B6KST7V_.ARC
4402	E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\ARC0000004402_0749585225.0001
4403	E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\ARC0000004403_0749585225.0001
4404	E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\ARC0000004404_0749585225.0001
4405	E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\ARC0000004405_0749585225.0001
4406	E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\ARC0000004406_0749585225.0001
4407	E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\ARC0000004407_0749585225.0001
4408	E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\ARC0000004408_0749585225.0001
4409	E:\ORACLE\FLASH_RECOVERY_AREA\PCSJP\ARCHIVELOG\ARC0000004409_0749585225.0001

The logs for sequence 4390-4401 have been not been generated. This means that the standby database cannot be kept up to date. Since the archive logs are not created by Oracle, and you cannot skip archive logs with a standby database, it means the standby database has to be rebuild.

Re-creating the standby database can be easily done with Dbvisit Standby.

Recommendation
Do not switch on NOARCHIVELOG mode during a database upgrade, as this may invalidate your standby database.

When upgrading an Oracle database that has an associated standby database, we recommend the following approach is followed: Upgrading or patching Oracle with a standby database.

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.

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.

Down here in New Zealand we love DIY. In fact, it’s part of our national identity to ‘do it yourself’ and us Kiwis are widely purported to be able to fashion almost anything out of a length of number 8 wire.  This is why at Dbvisit we are respectful of those companies (who are lucky enough to have such creative and resourceful staff) that decide to develop their own standby database solution (manual log shipping) as part of their Oracle disaster recovery strategy.

Unfortunately DIY projects don’t always stick to plan (or budget) and can be somewhat experimental, often costing more to manage and support than originally anticipated. When it comes to disaster recovery, if your data is critical to the business continuing to function it is essential to to have the best solution you can afford in place. If you’re weighing up DIY versus ‘off the shelf’, here are some important considerations to look at:

• Has the DBA covered all the bases? (bearing in mind it is very difficult to test and simulate all the issues that can go wrong).
• Is it going to be reliable, stable and easy to support in the long run?
• What happens when the DBA that developed the script leaves your organisation, is there someone else who knows how it works?
• Will a new DBA understand how the script works and be confident enough to use it (and fix it) when things go wrong?
• What will happen when you upgrade or patch Oracle? Will you be confident that it still works?
• What will happen if you migrate to another platform?
• What happens if there is a temporary glitch. Will it automatically recover and continue without intervention from the DBA?
• Does it support the Oracle features such as RAC, OMF, ASM?
• Does it create the standby database automatically?

Using a proven, reliable and stable solution like Dbvisit can really pay off in the long term. Dbvisit works on most platforms that are supported by Oracle including Windows, Linux and Unix and is backed by world class support. The team behind Dbvisit specialises in Oracle Disaster Recovery and as such are fully committed to ensuring you have full confidence in your Disaster Recovery solution.

Whichever path you decide to take, the most important things to think about are: Have I really done the right thing by my data? Does my solution tick all the boxes and protect my data the way it needs to be  protected? Have I thought about the long term effect of my decision on my departmental budget?

www.dbvisit.com

During recent testing, a datafile from one of our development databases was deleted. At the same time the same datafile was also deleted from the standby database. So this meant that our development primary and standby databases were no longer available.

However Dbvisit came to our rescue, and the functionality we have been recommending to our customers for a number of years now actually saved us! We employed Dbvisit to successfully recreate our primary and standby databases.

We have the following development servers:
Dev servers 1
dbvisit11 – primary database server
dbvisit12 – standby database server for dbvisit11

Dev servers 2
avisit31 – primary database server
avisit32 – standby database server for avisit31

Dev servers 2 are a clone of dev servers 1 with the same databases.
Due to the deletetion of the datafiles, the databases on dbvisit11 and dbvisit12 were gone.

Here is what we did to get the databases back on dbvisit11 and dbvisit12:

1. Logged onto avisit32 and used dbvisit_setup to automatically create a new standby database on dbvisit12.
2. Activated the standby database on dbvisit12 to become a new primary database.
3. Used dbvisit_setup on dbvisit12 to create a new standby database on dbvisit11.
4. Used graceful switchover to switch the roles between dbvisit12 and dbvisit11.

And so in 4 easy steps we had our databases back on our Dev servers 1: dbvisit11 (primary database) and dbvisit12 (standby database).

It is very rewarding to be able to use our own technology to recover a primary and standby database.

A good location for the Disaster Recovery (DR) site could be current primary site. The primary site then becomes the remote location.

The reason for this is, that in the event of a disaster at the primary site (housed in the remote location), your current staff will be on hand and available to manage the standby site (which will now become the primary site).

Distance between primary and disaster recovery sites: How Far Away is Far Enough?
An interagency white paper by the SEC, Federal Reserve and other agencies that came out after 9/11 suggested a 200-mile or 320-km plus separation between the primary and secondary facilities. The two locations should be in a geographically different location. So if one location is prone to earth quakes or floods, then the other location should not be prone to the same conditions. Ideally they should be on a different power grid.

One of the first things to go when a disaster strikes is cellphones or mobile phones. Even if the cellular infrastructure is still intact, there will be some many cellphone calls made, that the system could easily be overloaded. So make sure your DR plan has sufficient contigencies in place without the use of mobile phones.

Every time I use Perl, I am amazed at this programming language created by Larry Wall. I have used several languages in the past, but Perl is now my favourite. It takes a little getting used to, but once you are used to it, it is great.

What makes it so great

• CPAN site. Small and large Perl modules that can be easily plugged into your code to make life easier. Some of the modules available are: logging, email, html template engines etc. There are over 70,000 modules.
• Runs on Windows, Linux, Mac and Unix as well as other OS. It is truly a multi-platform language. For each OS you do have to take certain precautions, but there are CPAN modules that allow you to deal with each OS’es peculiarities like file name conventions.
• It can be used in OO style but also conventional style.
• It is very efficient. With very few lines of code, a lot can be achieved.

I have read the following negative things being said about Perl:
- “Good for little programs, but not for large programs because you get spaghetti code.”
I think this is not true, in any language you can write spaghetti code. This is not due to the programming language but due to the programming style.

- “Things can be done in more than one way”
I think this can be a good thing or a bad thing. With Perl, I consider this a good thing as it demonstrates the power and flexibility of the language.
True this can mean that Perl can be a bit cryptic as you may be used to seeing things solved another way. However it is a great language if you are a perfectionist and always keen to learn new things. With Perl there is almost always a better way to do things and you learn all the time. At least I do.

True, Perl is not so trendy anymore as say Python, Ruby or Java. It is also cometimes difficult to compare different languages as some are more suited to a particular application than others. You also have to look at how well the language is known and how many resources are available. For example to build a website I would probably choose PHP over Perl as there are so many more PHP programmers in the world than Perl programmers ( I also like PHP).

If you are an Oracle DBA that likes to automate specific tasks, I would highly recommend using Perl. I used to do everything in Korne Shell, and I thought it was a great language. But once you learn Perl, you realise your hands are no longer tied behind your back!

Perl is great to automate some of the tasks that may be required in your disaster recovery plan. This can be changing tnsnames.ora entries, or DNS entries.

You are not in isolation if you use Perl in your organisation. Oracle 11g uses Perl to write some of their interfaces. For example asmcmd (and asmcmdcore) are witten in Perl. Vmware uses Perl for their installation routine.

CPAN

To install a cpan module, type cpan (in Windows use PPM. Start cmd and type ppm):

CPAN>i /mailer/

The i is for information, this will list all modules with the word “mailer”.

To install a particular module, you have to specify the name exactly as it appears:

CPAN> install Mail::Mailer
That’s it and you can use it.

How to learn Perl

The best way to learn Perl is to start with a small project. For example write a little Perl program that scans the Oracle alert log and send an email if an error appears. You will probably have Perl installed already because Perl comes pre-installed on Unix and Linux. On Windows go to activestate.com and download Perl.

The best Perl book in my opinion is Programming Perl by Larry Wall, Tom Christiansen, Jon Orwant.

So thanks to Larry Wall and all the great people that help support Perl and CPAN.

How much does 30 minutes of down time cost your business?

Maintenance overheads aside, think about the opportunity costs in lost productivity. Now imagine a 16 hour downtime. Will your company operations be hurt because of inaccessibility to critical data? In the US, 43% companies never reopen after a major disaster*, and only 7% survive for an additional 5 years**.

While we cannot control external forces – natural, human or technical; we can control the impact they have on our businesses. No company is immune to such catastrophes. Companies are protecting their data against disruptions more seriously today with contingency plans and standby servers, because of our excessive dependencies on machines.

Time or data loss has huge costs in terms of overheads and lost opportunities. Loosing integral data to disasters not only impacts the security and finances of your business, it jeopardizes its existence completely.

So the question is, how prepared are you to face such unforeseen situations? Do you have the peace of mind that no matter what happens, your company’s data will be protected and your business will continue without stalling even for a moment?

Your business success, bottom line, and growth depend on how prepared and secure your systems and processes are at all times.

You won’t ever have to recover from disasters, if you are ready for them.

(* US Small Business administration)
(** US Bureau of Labour)

We are very pleased that Dbvisit is bundled together with software from major vendors to offer their clients complete protection in terms of Oracle disaster recovery, data recoverability and backup protection.

Microlistics is a successful privately owned Australian Company focused on delivering Warehouse Management systems throughout the Asia Pacific and Middle East regions. We are proud to be one of their technology partners.

In North America Fiserv have bundled Dbvisit with their Credit Union platform. One-third of US credit unions depend on Fiserv to deliver technologies that serve as the backbone for their daily operations.

Alcatel-Lucent have bundled Dbvisit with their Next Generation Operational Maintenance Centre. Their senior software developer has this to say about Dbvisit: “The solution takes a complex problem and distils it down to reliable, cost effective, and easy to use product.”

Software vendors are starting to realise the importance of offering a complete solution to their customers which includes disaster recovery. Dbvisit is seen as a best-of-breed solution to offer Oracle disaster recovery.