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:
- Complete Oracle software installation.
- The databases admin directories like the bdump, cdump, udump.
- The pfile/spfile (this can have different settings to the primary database pfile/spfile).
- The database password file.
- Database datafile directories.
- Redo and archive directories.
- 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:
- Create a standby controlfile and copy this to the standby server.
- Create a backup of the primary database. This can be done through normal hot or cold backup methods or through RMAN.
- Copy the backup of the primary database to the standby server.
- Copy the standby controlfile to the correct location(s) on the standby server.
- Copy the backup database files to the correct location(s) on the standby server.
- Start and mount the database as a standby database.
- Apply the latest changes from the primary database.
Copy any archives files that have been created during the hot backup process to the standby server.
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:
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.
