cancel
Showing results for 
Search instead for 
Did you mean: 

Snapshot an Oracle DB for fast backup & recovery - without a DB quiesce?

0 Kudos
889

Hi all

I'm trying to understand the inner workings of various flash copy / split mirror type backup solutions for an Oracle database.

We've been looking at an IBM offering that does an SVC level flash copy / snapshot. Problem is that it needs to 'quiesce' the database.

We have an exceptionally busy 24/7 system and we fear that an Oracle level quiesce may not be possible. From Oracle documentation:

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/admin/starting-up-and-shutting-down....

"The ALTER SYSTEM QUIESCE RESTRICTED statement may wait a long time for active sessions to become inactive. You can determine the sessions that are blocking the quiesce operation by querying the V$BLOCKING_QUIESCE view. This view returns only a single column: SID (Session ID). You can join it with V$SESSION to get more information about the session"

"For both dedicated and shared server connections, all non-DBA logins after this statement is issued are queued by the Database Resource Manager, and are not allowed to proceed. To the user, it appears as if the login is hung. The login will resume when the database is unquiesced."

We cannot afford for this busy Production database to 'hang'.

I was thinking that a split mirror type snapshot may not require a quiesce but I'm being informed it will still need this else the database won't be in a consistent state.

Anyone know of some way to have a consistent snapshot (that will give us fast recovery) without having to perform an Oracle level quiesce? I've been looking into ZFS and NetApp SnapCenter but not been able to get down to the nitty gritty level of how these work - although I have found reference to quiescing in some NetApp documentation now, but it's not clear still what it's doing.

Thanks

Ross

Accepted Solutions (0)

Answers (1)

Answers (1)

Reagan
Product and Topic Expert
Product and Topic Expert
0 Kudos

Used both, IBM Flashcopy in the past and NetApp in the present environment. Flashcopy and NetApp need the database in backup mode (at least) before you start the copy (based on my experience). So that means you will need the archive logs in case of recovery. I have even seen in places where the database is stopped (leaving the SAP instance to run), disks get cloned to the flashcopy server and immediately the database is started. All these are done within 30 seconds to 1 minute of time. During this time all the SAP work processes will be in reconnect status. This is basically an offline backup but in a strange way.

0 Kudos

Thanks Reagan. I've now had confirmation from IBM that it doesn't quiesce the database at Oracle level as feared, it interfaces with brbackup and indeed does as you say - puts the database into backup mode. So it's not going to be as instantaneous as they implied (1-2 seconds snapshot) but perhaps a few minutes. That's fine as far as I'm concerned - better to have the DB in backup mode and generate a little extra redo logs information/potentially impact performance for a couple of minutes, rather than 'hang' the database for who knows how long trying to achieve a quiesce that it may never be able to achieve 🙂

Reagan
Product and Topic Expert
Product and Topic Expert

We never had to put the database into "quiesce" mode. The backup script was scripted in a way that it takes the DB into backup mode and calls the script to mirror the disks and finally takes the DB out of the backup mode. Putting the DB into backup mode is more than enough, as in the past, I could manually get then disks mirrored to the flash copy server just by putting the DB into backup made and then recover it. The EMC BCV solution works in the same way. Initially when we were using the EMC solution we were doing the annual system refreshes using this option.