Closing Dataguard Transfer and Apply Gaps

In the last week, I had two customers that had some failures with their standby databases and contacted me about closing their DG gaps. Since this kind of problems is common, and since the solutions are fairly easy I thought it worth a post to document this for their and your use.

Before we begin, let’s understand what dataguard gaps are. There are two types of gaps: transport and apply gaps. The transport gaps problem usually starts after a network disconnection between the primary and standby databases. At this time, the archive logs are not being shipped between the databases – and this is called a transport gap. The other case is the apply gap: in this case, we have all the files but the standby didn’t finish applying them all yet.

Our problem begins when during a transport gap, the archives we need to close the gap (in the primary) are removed. In this case the dataguard will not be able to continue rolling the log files and will hang. This is exactly what happen to my customers, and their question was what to do next – preferably, without rebuilding the entire standby database.

Read more →

/3 Comments/by /Estimated Reading Time: 4 Minutes

Fixing Dataguard Wrong File Location Problem

When we add a new datafile to the primary environment of a dataguard setup, we need to add the datafile to the standby environment as well if we want the manged recover to continue working. We can do that manually if we want – but fortunately for us Oracle comes with a built in feature for automatically creating the file in the standby as well. All we need to do to set it up is to set the parameter “standby_file_management” to AUTO and create a conversion map for the files location using DB_FILE_NAME_CONVERT parameter.

All of this should be running well, until some Junior DBA (hey D., you know I’m talking about you.. 🙂 ) decides to create a new datafile in the non-default location.
Once that happens, the dataguard is unable to create the new datafile and the managed recovery will stop working.

This is a short guide I created years ago for how to identify and solve the problem.

Read more →

/1 Comment/by /Estimated Reading Time: 3 Minutes

Moving a 3TB Database Datafiles With Only 2 Minute Downtime

,

In the last week, I had couple of my customer ask me the same question: how can we move our data files between disks with minimum downtime?

This is actually a really good question. When we want to move a database around (let’s say to a new storage or a new disk device) we will need to do it when the files are closed (offline or database down) in order to keep them consistent.  We can do it tablespace by tablespace or the entire database but at the end, it all depends on the size of the files that needs to be transferred. By the way, in Oracle 12c this problem is obsolete since there is an online move command

In the example I’m giving here, the customer had a 3 TB database with most of it in the same tablespace. The downtime allowed by the customer SLA to his customers was around 5-7 minutes so we obviously couldn’t take the database down or take the tablespaces offline. We needed another solution.

Read more →

/10 Comments/by /Estimated Reading Time: 7 Minutes

How to transparent transition to the DR with few easy steps

I was asked by one of my readers about “transparent transition” between the Primary and Standby databases when using a Data Guard environment. The question sounded relatively simple: how do you configure the clients so it will automatically work with the active site at any point in time?

The truth is that the solution is a lot easier than what you might think, and I even got to implement it in on an Oracle 10g database, more than five years.

Read more →

/0 Comments/by /Estimated Reading Time: 3 Minutes