Can I "flush and lock" an Oracle database to do a filesystem backup?

Question

I'm using ZFS to take snapshots of running OpenVZ containers.

For MySQL databases I can use flush tables with read lock to put them in a flushed & locked state. This will put the underlying files in a consistent state and hold any incoming queries until I complete the snapshots, which take at most a couple seconds.

Here is a sample script, without error checking and other housekeeping code:

vz_root=/var/lib/vz/root/$veid
mysql_locked=/var/run/mysql_locked

# flush & lock MySQL, touch mysql_locked, and wait until it is removed
vzctl exec $veid mysql -NB <<-EOF &
    flush tables with read lock;
    delimiter ;;
    system touch $mysql_locked
    system while test -e $mysql_locked; do sleep 1; done
    exit
EOF

# wait for the preceding command to touch mysql_locked
while ! test -e $vz_root$mysql_locked; do sleep 1; done

# take a snapshot of the VZ filesystem, while MySQL is being held locked
zfs snapshot zpool/private/$veid@$(date +"%Y-%m-%d_%H:%M")

# unlock MySQL
rm -f $vz_root$mysql_locked

Can I do the same thing for an Oracle database?

Is there a command that will flush its tablespaces, put them in a consistent state, suitable for backup, and hold any activity until I unlock it?

I'd rather avoid having to reconfigure Oracle to use ZFS directly as a storage backend, because simple OpenVZ containers suit me very well. In fact I'd rather have some low-maintenance technique, without any complex Oracle setup, if any exists.

Answer 1

There is "no way" how to stop Oracle working. Even backups are background non-blocking processes. Use RMAN for backups (which is recommenced).

The other way would be to use very old-school approach.

• create standby controlfile for backup (it will create backup copy of Oracle's control file - having some old SCN in it)
• execute alter database begin backup. This will NOT stop IO operations against datafiles. Only SCN will not written in them
• now you can create snapshot.
• execute alter database end backup
• Now you have also backup all the archive redologs generated by the database

The restore works as:

• copy backup controlfile into the right place
• copy datafiles (or use the snapshot)
• try to start the database. the database will recognize that SCNs differ, and the datafiles are in inconsistent state. So it will re-apply all the changes from redologs.
• then you can OPEN the database

The whole procedure is more complicated, and you should read something about it. There are various articles on the internet. It really requires some knowledge.

IMHO - you request does not make to much sense. MySQL does not have any reasonable support for backups and people tend to bypass this limitation by various hacks. The commercial databases have in-build native support for backups and do offer more features then ZFS filesystem. For example you might find PITR (point-in-time-recovery) very useful. So I really would recommend you to use RMAN backups for Oracle, especially if you are not familiar with Oracle architecture.

PS: SCN stand for System Change Number. This number increases whenever something changes in the database, and is written into every Oracle datafile.

PPS: If you want to revert database state you can you use FLASHBACK DATABASE command.