Dbvisit – Standby Database Technology Blog

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. Complete Oracle software installation.
2. The databases admin directories like the bdump, cdump, udump.
3. The pfile/spfile (this can have different settings to the primary database pfile/spfile).
4. The database password file.
5. Database datafile directories.
6. Redo and archive directories.
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 highlevel 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 not 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 our documentation that explains it in a step by step the process:

• 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!