Smart Database Technology

We definitely promote using RMAN for backups of your Oracle database. With RMAN you are able to recover up to the last moment before failure (if you have archive log mode enabled). This is as opposed to a facility like export dumps where you are only able to recover to the point when the export was made (or at some point in time before).
With RMAN you can also restore individual files and tablespaces. With export dumps you can restore individual tables but the transactional integrity is not guaranteed if you restore individual objects.

Recovery catalogue
RMAN can be used with or without a recovery catalogue. A recovery catalogue is a repository in a separate database. Usually this recovery catalogue holds the RMAN catalogue of several databases. If you use RMAN without a recovery catalogue, then the recovery information is held in the control files of the database. Even with the recovery catalogue, backup information is held in the control files of the database (incase the recovery catalogue is not available).

RMAN and standby database
If you are not replicating the RMAN catalogue database, then you can use RMAN without a recovery catalogue for initial backups when you failover to the standby database. When a standby database is failed over and becomes the primary database, a resetlog operation occurs and this invalidates all previous backups. After the initial RMAN backups on the new primary server have taken place and things have settled down, you can recreate a RMAN catalogue database to continue RMAN backups with a catalogue.

This may be better explained with the following steps:
ServerA -> primary server
ServerB -> standby server
1) Normal RMAN backups of primary database on ServerA using catalogue.
2) Failover occurs to standby database on ServerB. This now becomes primary database.
3) RMAN initial backups of new primary database on ServerB, using no catalogue.
4) Create new RMAN catalogue database.
5) RMAN backups of primary database on ServerB using catalogue.

The other alternative is to use RMAN without a catalogue for normal backups. This will simplify the fail over process as the re-creation of the catalogue is not needed.

There are no precautions for using RMAN in conjunction with Dbvisit Standby on the primary server. However, there are the following to points consider:

1) If you are using Dbvisit Standby to manage the archive log files on the primary server, you have to let RMAN know that Dbvisit Standby has deleted archive log files. For more information please see:http://www.dbvisit.com/forums/showthread.php?t=13

2) If you set LEAVE_COMPRESS_SOURCE=No (in the Dbvisit DDC file) then Dbvisit Standby will leave the archive files in an uncompressed state and RMAN will be able to find them and backup them up.

3) If you use RMAN with nocatalog, then the RMAN backup information is stored in the primary database controlfile. If you use graceful switchover (or role reversal), then the backup history information in the control file will be lost as a new control file is built during the role reversal process. The only way around that is to use a catalog.

We recently carried out some research among some of our clients in order to gain a better insight into the acceptable levels of data loss in a standby environment. We asked two questions:

1. How often is Dbvisit scheduled on your primary server. For example is this every 1, 2, 5, 10, 15 or 20 minutes?
2. What type of database is Dbvisit running on? I.e. OLTP, Data Warehouse  or other?

We got a great response and would like to thank everyone who participated.

The responses (see chart below) showed that Dbvisit is scheduled on average every 12 minutes with the shortest interval reported as 2 minutes

Dbvisit is actually capable of providing a minimum of just 60 seconds data loss although this needs to be considered against how critical the database in question is and the possible subsequent impact on database performance. We would love to hear from any of our customers who schedule Dbvisit every 60 seconds or who have any queries surrounding Dbvisit scheduling, so we better understand your data availability needs.

We also found that OLTP databases are by far the most common, outnumbering all others by 2:1.

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.

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
Or:
 ORA-27047: unable to read the header block of file

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.

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. 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.

Activating the standby database to become the primary database is also refered to as a failover. Failover happens in case of a disaster or severe failure on the primary site and users are no longer able to connect to the primary database. The standby database is activated to become the new primary database so that users can connect to the new primary database on the standby site.

The decision for failover or activating the standby database should be made carefully as this process cannot be reversed so it is crucial that this only happens in case of 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 this variability the decision to perform a failover, and activate the standby database, is left in the hands of the operator(s), and is not built in to Dbvisit itself.

However it is possible to automate this failover process, if one choses to do so, and was aware of the associated risk inherent in this. This could be achieved with some scripts or a basic app, created to run on the standby server or a third “observer” server.

What happens during activation?
What happens when the Oracle standby database is activated to become a primary database?
To activate the standby database one of the following commands can be given:
1) Manual SQL comand:

SQL> alter database activate standby database;
SQL> shutdown immediate;
SQL> startup

2) Dbvisit command:

dbv_oraStartStop activate database_name

Ensure that all archives have been applied to the standby database before activating the standby database.

Activating the standby database initiates an OPEN RESETLOGS operation on the standby database to create the redo logs. The redo logs are newly created because in its normal standby database state, the redo log files are not present and are not needed. Note Oracle Enterprise Edition uses standby redo logs, but these are different to normal redo logs.
When you create a standby database, you actually do not need to copy the redo logs from the primary database.

So the OPEN RESETLOGS operation creates the physical redo log files. If Oracle cannot create the redologs then the operation will fail and the database will still be a standby database. Oracle uses the information from the controlfile to determine where to create the redo log files. The redo log file information in the standby controlfile is therefore just a reference to where the redolog files should be created if OPEN RESETLOGS operation is given.

Example
In the example below the redo log is renamed to an invalid filename to emulate what happens when the standby database is activated:

SQL>  alter database rename file '/oracle/oradata/dbvisitp/redo01.log'
 to '/oracleX/oradata/dbvisitp/redo01.log';
Database altered.

SQL> alter database activate standby database;
alter database activate standby database
*
ERROR at line 1:
ORA-00344: unable to re-create online log
'/oracleX/oradata/dbvisitp/redo01.log'
ORA-27040: file create error, unable to create file
Linux Error: 2: No such file or directory

The database is still a standby database as the activation failed. We cannot manually create the redo logs, as this is an Oracle internal operation. The only thing we can do is to give the correct location for the redo log files.

Rename the redo log to the correct location:

SQL>  alter database rename file '/oracleX/oradata/dbvisitp/redo01.log' to
'/oracle/oradata/dbvisitp/redo01.log';

Database altered.
SQL> alter database activate standby database;

Database altered.

SQL>

The standby database has been activated and is now a primary database. Oracle has created the redo logs at the location specified.
Note: all previous backups of this database are now invalid and cannot be used to restore this database. This is because of the RESETLOGS command which resets the archive sequence number (SEQUENCE#) and invalidates all previous archive logs. The SCN number of the database is not reset.
Now is a good time to make a new backup of your database!

One of the questions we get asked a lot is:
In terms of ORACLE licensing, do we need a separate Orcacle license for the DR / Standby database?

Yes, you require a separate Oracle license for the DR / Standby database. This license has to be the same license metric as the primary database. This means that if you license the primary database per CPU, then the standby database must also be licensed per CPU (although it does not have to be the same number of CPU).

Or if the primary database is licensed per user then the standby database must also be licensed per user.

It must be the same license type, so if your primary database is Standard Edition, then the standby database must also be Standard Edition.

We might go for Standard Edition Processor based licensing option, which allows us to have up to 4 CPU in total. If we have a production / primary server with 4 CPU, will the licensing work with standby database?

You will require a separate Oracle Standard Edition license for the standby database which is also processor based. With Dbvisit we include the standby database with the Dbvisit license for the primary database.

How would the licensing work for Multiple standby databases?
Oracle is licensed per server not per database, so you can have as many databases as you wish on the same server. With Dbvisit we have 2 types of licenses, Single Instance or Multiple Instance (or server license). If you have Dbvisit for 1 primary database, then the Single Instance license is needed. If you have Dbvisit for more than 1 primary database on the same server, then the Multiple Instance license is required. With all Dbvisit licenses you can have unlimited standby databases.

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.

A Standby Database is a duplicate copy of an operational database on another server. The standby database is being kept up to date by a replication mechanism that ensures each transaction that is applied to the operational or primary database is also applied to the standby database. In most cases there is some lag between when the transaction is applied to the primary database and when it is applied to the standby database. This is typically between 1 and 10 minutes which is acceptable for most businesses.

A standby database is far superior to a normal backup as it is instantly available in the event of a disaster or failure. To restore a backup takes time, and during the restore time the system is not available. A restore may also cause too much impact on other systems. With a standby database there is nothing to restore in the event of a disaster or failure as the standby database is always available. It is possible to switch applications over to the standby database in a matter of minutes to allow business continuity.

There are several replication mechanisms to keep a standby database up to date:

1. Physical Standby using redo or archive logs
2. Logical Standby using SQL
3. Oracle replication using streams

Physical standby is the most commonly used for disaster recovery or failure. Conceptually the mechanism for keeping the standby database updated is not difficult, but putting this into practice to get a robust solution is complex and requires a lot of work. The reason is that there are so many things that can go wrong at each step of the process and you have to ensure that the standby database keeps up to date no matter what. And if it is not keeping up to date then alerts should be raised. The last thing you want to happen is that the standby database is old, when you need it most.

We have seen instances where the companies have used home grown solutions to keep the standby database up to date, but when the DBA left that wrote them, the new DBA’s were too scared to touch it incase it broke. So no DR test was even done. In the case of an actual hard disk failure, the DBA recommended to restore from backup, rather than switch to the standby database, because they were not sure if it was kept up to date and how the process could be reversed once the primary server was operational again. At another company, the standby database was last updated 2 years ago!

The standby database can be used for other purposes other than just for disaster recovery:

1. Reporting database. READ ONLY queries can be performed on the standby database to offset loads from the primary database.
2. Shadow environment which can be used to investigate issues like database bugs or inconsistencies that arise on the primary database and the primary database can continue to run risk free while the issues are investigated on the shadow database.
3. Capturing snapshots for batch transactions (point in time). The standby database can be used for rollback purposes in case of errors during batch processing.
4. Perform backup on the standby database rather than the primary database to offset loads to the primary server. Backups can be done in the middle of the day to ensure staff are available to resolve any backup issues.

Oracle licenses
You will need a full Oracle license for the standby database. This needs to be the same metric as the primary database. So if you primary database is Enterprise Edition, then your standby database also needs to be Enterprise Edition. If the primary database is licensed per CPU, then the standby database aslo needs to be licenced per CPU.