Yaping's Weblog

August 30, 2008

How to recover is better

Filed under: Oracle — Yaping @ 2:45 am
Tags:

Imagine that there’s one 2TB database with one physical data guard, this DB was taken full backup at last weekend. Now one tablespace was dropped by human error, it was found several minutes late and stop primary database service immediately, some data was loaded into the DB by application during this time. Assuming this tablespace is about 200 GB, we want to use the current primary host to provide service finally.

How should we recover the data?

What we must consider are: data lost, DB downtime and data guard rebuild time.

According to different type application, the clients may have different considerations. Some may care data integrity, but others may care downtime.

How much are the disk IO capacity and network bandwidth? In 1 Gb LAN environment, generally, clients can get about 100 MB/s transmission speed. If the IO is 30 MB/s, means it can deal with about 100 GB data every hour. If the IO is 100 MB/s, it can deal with about 350 GB data every hour.

How long will it take to restore 2 TB or 200 GB data?

The following solutions can be considered when it happens.

Solution 1

If the archive log which contains dropped tablespace doesn’t be applied on the data guard, activate data guard as primary database.

Shutdown data guard, replace the control of data guard, do incomplete recover until the point before drop tablespace and open it resetlog.

Or manual recover data guard to the point and activate it.

If the clients can accept data lost, now it can provide service.

If no data can be lost, we can extract user sql statements from redo log since tablespace was dropped via LogMiner tool. This data size is maybe several hundred MB or less. After these sql statements are submitted on the previous data guard, this DB can act as primary DB to provide service.

Now we can consider rebuilding data guard. Restore the last weekend backup to the old primary site and do incomplete recover until the same point as data guard did. Then do recovery through resetlog and convert it to data guard.

Solution 2

If the archive log which contains dropped tablespace doesn’t be applied on the data guard, we do TSPITR. Put all data files offline excluding the data files within the dropped tablespace and system/undo files. Then do incomplete recover until the point before tablespace was dropped and open it resetlog.

If objects within the dropped tablesapce are self-contained, we can use transportable tablespace to plug it to primary database.

If not so, we can use exp tool to export objects within this dropped tablespace and import them into primary database.

After the lost data is back, we can rebuild data guard. We just need restore the data files within the dropped tablespace and system/undo data files from backup to data guard site, and put all rest data files online, do recover and convert it to data guard.

Solution 3

If the archive log which contains dropped tablespace doesn’t be applied on the data guard, we switch it to do manual recover until the point before the tablespace was dropped. Then data guard is opened read only. We export objects which are in the dropped tablespace and import them into primary database.

Data guard doesn’t need be rebuilt.

Solution 4

It’s similar with solution 2, but it restores data from last weekend backup to do TSPITR, and it doesn’t need rebuild data guard.

After primary database and data guard run stably, if necessarily, we can consider switching roles.

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: