Dbvisit – Standby Database Technology Blog

We have released a new online service desk to further improve our support for Dbvisit. Tickets can be created online for any Dbvisit support issue. Tickets can be viewed and tracked at any stage to ensure progress is being made.

Any updates to the tickets will be automatically emailed to the person who created the ticked.

To create a new ticket in our service desk please go to: http://www.dbvisit.com/support.php

We often get asked what is the difference between switchover and failover? The easiest way to understand is that:

Switchover – This is done when both primary and standby databases are available. It is pre-planned.

Failover – This is done when the primary database is NO longer available (ie in a Disaster). It is not pre-planned.

A switchover (or graceful switchover) is a planned role reversal between the primary and the standby databases. This is used when there is a planned outage on the primary database or primary server and you do not want to have extended downtime on the primary database. The switchover allows you to  switch the roles of the databases so that the standby databases now becomes a primary databases and all your users and applications can continue operations on the “new” primary database (on the standby server). During the switchover operation there is a small outage. How long the outage lasts, depends on a number of factors including the network, the number and sizes of the redo logs. The switchover operation happens on both the primary and standby database.

A failover operation is what happens when the primary database is no longer available. The failover operation only happens on the standby database. The failover operation activates the standby database and turns this into a primary database. This process cannot be reversed so the decision to failover should be carefully made. The failover process is initiated during a real disaster or severe outage.

Automatic Failover

Automatic failover is where the software determines when the standby database should be activated to become the new primary database. There are numerous conditions that can occur (ie: network glitches/outages) in any system which theoretically could disrupt communications between the primary and standby sites. Because of the importance of this decision and the number of variances, we believe it is best not to automate this process but to leave it in the hands of a human.

More information on what happens during a failover: http://blog.dbvisit.com/activating-standby-database-failover/

Dbvisit and failover/switchover

In Dbvisit both failover and switchover is possible. The Dbvisit commands are as follows:

Failover:
dbv_oraStartStop activate orcl1
(where orcl1 is the name of the database)

This command is run on the standby server. Now the standby database has been activated and is now the new primary database.

Switchover:
dbv_oraStartStop switchover orcl1
(where orcl1 is the name of the database)

This command is run on both the primary and standby servers. The primary database will become the standby database, and the standby database becomes the primary database. Dbvisit will continue to operate to keep the new standby database up to date from the new primary database. No configuration changes need to be made.

We have just released Dbvisit 5.2.20 for all platforms.

This version allows for passwords to be stored in encrypted form in the Dbvisit Database Configuration (DDC) file.

The full details of this release are:

New features:

1. Add ENCRYPT_PASSWDS variable to specify if all passwords should be stored encrypted in the DDC file or not. To change the setting run dbvisit_setup and choose option 6.
2. Add SWITCHOVER_WAIT_IN_SEC to control how long Dbvisit waits between each checkpoint iteration for graceful switchover. Total time Dbvisit waits for each checkpoint is set by SWITCHOVER_TIMEOUT_IN_SEC.

Fixes:

1. When standby redo logs are using OMF, graceful switchover may fail. This has been fixed.
2. When log_archive_format on Windows contains %S, the sequence number can be truncated by Oracle. Ensure that Dbvisit also truncates the number the same way.
3. Running dbvisit_setup: Update Dbvisit Database configuration (DDC) file may error for older DDC files.
4. Improve error message for ORA-27048.
5. Improve error message for creation of standby when datafile contains spaces.
6. Improve message for error 5005 when standby controlfile does not contain the archive log sequence.

Dbvisit can be downloaded from our website http://www.dbvisit.com.

From the team at Dbvisit we hope you have a fantastic festive season, and a relaxing and enjoyable New Year.

We had our Dbvisit Christmas party and although not everyone was there because our team spans several countries, we had a great time.

2010. The Year Ahead.

We are excited about what’s in store for 2010. Work is already underway on a number of exciting plans and projects, which will bring significant benefit to new and existing customers. So what do we have in mind?

• A web based interface for Dbvisit
• 24×7 support services
• Helpdesk ticketing system

We look forward to communicating with you in 2010.

Warm regards.
the Dbvisit team

Recently, when running Dbvisit with Oracle 11g, we encountered the following error under certain circumstances with one of our standby databases:

 ORA-27048: skgfifi: file header information is invalid

This error suggests that Oracle is possibly trying to use a non-database file as a database file. However we were pretty sure that this was not the case.

As part of normal Dbvisit processing, Dbvisit can issue the statement “SQL>recover standby database …” to bring the standby database up to date. It was at this point that the error was occurring. Our thanks goes to one our team members, Vit Spinka who determined the cause of the error:

When the “recover standby database …” SQL statement is issued in 11g, Oracle scans the entire archive log:
/oracle/app/oracle/flash_recovery_area/<dbname>/archivelog
directory when Flash Recover Area (FRA) is used.

Any files that are not Oracle related will cause the ORA-27048 error. So any archive files that are compressed will cause this error, even if it is an old archive log that is completely irrelevant.
So this error is occurring because there are archives in FRA that are compressed. Uncompressing those archives will stop the error.

With Dbvisit this error only occurs once Graceful Switchover has been initiated twice. It is only at this point that there will be compressed archive logs in FRA on the standby server. Before switchover has occurred, the standby database will not have any archive files in its FRA as the standby database does not produce archive log files. After switchover the new primary (old standby) will start producing archive log files.  Then when another switchover occurs to bring the configuration back to its orginal state, the original standby will have archive log files in its FRA. If those archives are compressed then the ORA-27048 error will occur.

So, what is the solution?
There are several options to solve this issue:

1. Stop Oracle from scanning the FRA by setting the following command in the Dbvisit Database configuration (DDC) file:

    _SQL_ALTER_SESSION_1 = set logsource "/oracle/oraarch/xxxx"

   (It does not really matter what logsource is set to)
   You can add this to the end of the DDC file. Setting this does not have any impact, nor do you lose any Dbvisit functionality. Remember to alway edit the DDC file on the primary server only. Dbvisit will replicate the DDC file to the standby server when changes are detected.

2. Leave the archives uncompressed on the servers. The archives will be still be compressed before transfer.
   Update the DDC file and set the following:

    LEAVE_COMPRESS_SOURCE = No

    LEAVE_COMPRESS_DEST = No

It is always interesting to see how Oracle can modify its behaviour between different releases, and sobering to consider how changes like this really have the potential to catch you out. One of the great benefits of having a team like Dbvisit Support on your side is that they keep up to date with the latest Oracle changes, and are committed to ensuring that Dbvisit functions seamlessly with the database – maintaining the same great high level of protection and continuity for your systems. And this safety net equals peace of mind, for you, the customer.

We have just released Dbvisit 5.2.18 for all platforms.

This release incorporates a number of suggestions from our customers, which will improve usability of the software. We would like to thank those of you who have made suggestions, and we greatly appreciate your feedback.

From this release forward, all Dbvisit production releases will be an even release number (18, 20, 22, etc) and the development or stage releases will be an uneven number (17, 19, 21 etc).
So 5.2.18 is the production release of 5.2.17.

Some of the highlights in this release are:

• Allow for multiple archive log destinations to be managed by Dbvisit.
• Archive logs can now be managed by specifying partial days. For example keep the archive log files for 3.5 days.
• Improve remote checksum processing. If remote checksum process fails, then only checksum process is repeated, not the whole transfer process.
• Allow for Database which has been recovered with point-in-time recovery with resetlogs where SCN may be lower than current SCN.

The full details of this release are:
New features:

1. Allow for multiple archive log destinations in AMM (Archive Management Module) processing (AMM_PROCESS_MULTIPLE_ARCH_DEST).
2. Improve restartability of standby database creation. Standby Database is now shutdown after all the standby database parameters are obtained.
3. Graceful switchover synchronisation process now includes retries if there are network issues.
4. Improve remote checksum processing. If remote checksum process fails, then only checksum process is repeated, not the whole transfer process.
5. Allow for decimal numbers for DAYS_TO_KEEP_ARCHSOURCE and DAYS_TO_KEEP_ARCHDEST. For example 3.5 days.
6. Detect Oracle XE admin location for Linux.
7. Check for Standby archive log destination during graceful switchover. If location does not exist, then exit as switchover will not be successful.
8. Reduce number of trace file that Dbvisit keeps on server:
   NUM_TRACE_TO_KEEP = 100, DAYS_TO_KEEP_TRACE = 10

9. Check that Database is in archivelog mode.
10. Allow for Database which has been recovered with point-in-time recovery with resetlogs where SCN may be lower than current SCN.
11. Allow for “-” in the DDC file. Example: dbv_prod-vm2.env

Fixes:

1. Improve duplicate archive log handling for OMF files. Include a unique identifier to ensure the same file does not get renamed more than once.
2. Add check for ORA-00342: archived log does not have expected resetlogs SCN.
3. If same archive log is needed more than once by standby database for non RAC, ensure archive log can be compressed.
4. Allow for multiple NLS_LANGUAGE setting during RAC processing.
5. Add 24hour time clock to trace file.
6. Fixed calling AMM from dbv_functions -A
7. Improve error message in Windows if during standby database creation the standby database fails to start.
8. Fixed issue if first_change# is displayed as float, then graceful switchover was not successful.
9. Allow for different instance_name to database_name for graceful switchover.
10. Notify if create standby database process is not able to remove file on standby database.
11. Allow for %d log_archive_format.
12. Check and report if “ORA-00332: archived log is too small” is found.

Dbvisit can be downloaded from our website http://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.

Creating standby databases is not something that is done everyday so even experienced DBA’s sometimes forget the exact steps that are involved. Also the concepts behind a standby database are not always well understood.

What is a standby database?
A standby database is a clone of a normal or primary database, but is most often not open for normal READ-WRITE operations like the primary database. A standby database is usually used for disaster recovery or high availability purposes when the primary database is no longer available. In this situation the standby database is activated to become a primary database and all the users can connect to this database to continue operations. The standby database can also be used for reporting or backup purposes to offset loads from the primary database.

A standby database is held up to date by applying all the latest changes from the primary database to the standby database. Typically the standby database is always a little behind the primary database. Most sites have around 10-15 minutes lag times between the primary and the standby database.

Two types of standby databases:
The two types of standby databases are logical standby and physical standby. For disaster recovery and high availability, the physical standby database is most often used as it is the simplest and most robust. Physical standby databases are held up to date by applying redo or archived redo logs to the standby database. Logical standby databases are held up to date by applying SQL to the standby database. There are no limitations to the physical standby database, whereas the logical standby database has some limitations as to what can be replicated. With logical standby some data types like function based indexes will not be applied to the standby database.

The standby server is what we call the server that will host the standby database.

How to create a physical standby database?
A physical standby database must be a clone from the primary database and must follow a certain creation method. If this method is not followed then it is not a true standby database, and you will not be able to keep in it sync with the primary database.

Before the standby database can be created the following must first be in place on the standby server:

1. The databases admin directories like the bdump, cdump, udump.
2. The pfile/spfile (this can have different settings to the primary database pfile/spfile).
3. The database password file.
4. Database datafile directories.
5. Redo and archive directories.
6. Complete Oracle software installation.
7. The primary database must be in ARCHIVE LOG mode.

Once the Oracle environment is created, then the standby database can be created. The overall steps to creating the standby database are:

1. Create a standby controlfile and copy this to the standby server.
2. Create a backup of the primary database. This can be done through normal hot or cold backup methods or through RMAN.
3. Copy the backup of the primary database to the standby server.
4. Copy the standby controlfile to the correct location(s) on the standby server.
5. Copy the backup database files to the correct location(s) on the standby server.
Copy any archives files that have been created during the hot backup process to the standby server.
6. Start and mount the database as a standby database.
7. Apply the latest changes from the primary database.

Normally the physical standby database does have redo logs. If using Data Guard with Enterprise Edition, then standby redo log files can be created.

Creating the standby database sounds complicated? Fortunately Dbvisit has automated the whole standby creation process including the preliminary steps. Dbvisit copies the pfile/spfile and the password file to the standby server and will check the standby server to ensure the Oracle environment is setup correct and will let you know the outstanding items that are still needed. For example if you have not yet created all the Oracle directories, Dbvisit will let you know which ones are still required.

If you do not have access to Dbvisit or prefer to setup the standby database manually, here is some documentation that explain the steps involved:

• Creating a standby database for Linux/Unix (PDF)
• Creating a standby database for Windows (PDF)

What things can go wrong when creating a standby database?
When using the hot-backup method, the tablespace must be in hot backup mode when copying the tablespace datafile. Do not forget to take the tablespace out of backup mode once the tablespace is finished.
The standby database controlfile SCN number must be higher than any of the datafiles of the standby database. If this is not the case then the error ORA-01152: file 1 was not restored from a sufficiently old backup, and also ORA-01547: warning: RECOVER succeeded but OPEN RESETLOGS would get error below, will result.

For more information about standby databases, see our other post.

Oracle Open World 2009 is now behind us. It is a massive event that attracted 32,000 people and had about 1,900 sessions. It is very well organised by Oracle and partners. It is held in 3 huge convention centres that are across the road from each other. One road is permanently closed to traffic as they have erected big tents to use as lunch and entertainment venue. Almost the whole city of San Francisco is taken over by OOW as you see delegates walking with badges on the street wherever you are in San Francisco.

It was nice to meet up again with old friends and it was also a privilege to meet so many new Oracle DBA’s and Developers. OOW is certainly a place to bring the worlds best Oracle specialists together in one place. It is a great opportunity for networking and sharing knowledge and ideas.

My two presentations went very well and had a good turnout. One presentation was on Disaster Recovery and another on Perl. For details of my presentations and to download the slides and “homework” please go to OOW2009.

I think the Perl presentation hit a chord with many DBA’s as it is a highly effective tool for DBA’s, one that is too often overlooked, and one that many DBA’s would like to learn. But as with many programming languages you need someway to get you started, to learn the fundamentals and for someone to show you the right way to learn the language so that you do not start off with “bad” habits. This presentation was about how to start learning Perl and to go over some of the language fundamentals and best practices. The presentation was very well received as I received many positive comments from the audience. I was asked to submit this presentation for Collaborate 10 in Las Vegas next April which I now have done.

The Wednesday night party had everyone (all 32,000) transported in buses to an island in San Francisco Bay that is reachable by bridge. This was a huge party with 2 large stages. The entertainment was Aerosmith, Roger Daltry, Three Dog Night and The Wailers. What a great party and great food and drinks! I think Oracle hired all the buses, taxis and limos that are available in San Francisco.

What was the top new feature of Oracle 11gR2. For me this is “Edition-based redefinition”. As Tom Kyte said in his presentation: “It is the killer feature Of Oracle Database 11g Release 2. It is worth 2 features!”. It is like version control within the database. Not version control on the data itself but version control on the schema objects. The schema objects types that are editionable include Synonyms, Views and PL/SQL object types (like triggers, functions, procedures etc). So tables are not included in this.

Edition-Based Redefinition or EBR allows significantly less downtime when you are upgrading applications as you no longer need to wait for all the users to stop using the application before upgrading. Currently if releasing new PL/SQL code, the code needs to be recompiled but you cannot recompile until all users have stopped using the code. There are always a lot of dependencies when compiling PL/SQL, so basically it means no one can use the application when this is happening. With EBR, you can now compile the new code without affecting the current application. EBR is available in all editions of Oracle 11gR2.

In summary it was an event that was well worth attending and it was a privilege to be presenting at such a great event. Hope to see you there next year!

Oracle Open World 2009 is scheduled for October 11-15 in San Francisco.

I will be presenting 2 papers at Oracle Open World.

This is my first time at OOW and I am very much looking forward to it.
It will be a great chance to meet new people and to catch up with friends and customers.

I will be presenting the following 2 papers:

1) How to Create a Technical Disaster Recovery Implementation Plan.

A technical disaster recovery implementation plan involves the actual implementation of the hardware and software at the disaster recovery location. It ensures that there are no surprises when building the disaster recovery servers and that all critical systems and their components have been accounted for. This session focuses on Oracle-centric applications in the UNIX/Linux environment.
When: Monday Oct 12 16:00 – 17:00, Moscone South, Room 270

2) Perl:DBA’s and Developers best (forgotten) friend.

This session reintroduces Perl as a language of choice for DBAs and developers for developing a range of programs and scripts. Discover what makes Perl so successful and why it is so versatile. Perl can automate all those manual tasks, and it is truly platform-independent. It may not be in the limelight like other languages, but it is a remarkable language for development and is still very current with ongoing development. Oracle and VMware still use it as part of their latest software releases.
When: Tuesday Oct 13 16:00 – 17:00, Moscone South, Room 270

If you are attending OOW and are interested in these topics, please come along.

If you are one of our Dbvisit customers or just want catch up with me during OOW – please tweet me @dbvisit or send me an email. I am looking forward to meeting with you.