Backup & Recovery

Disk snapshotting to hot "backup" a database

User Rating:

/ 0

Details: Parent Category: Articles; Created on Thursday, 18 March 2010 20:37; Last Updated on Monday, 12 March 2012 13:54; Published on Thursday, 18 March 2010 20:37; Written by Guy Lambregts; Hits: 5834

Disk snapshotting to hot backup an Oracle database

Databases become bigger and bigger. Backup of these databases lasts longer. The more processors you have the more RMAN channels you can use to increase backup parallelism, the faster it goes. Even though the more you increase parallelism the more resources RMAN uses, the lesser resources there are left for other databases tasks. It becomes a challenge to backup a multiple Tb database.

There is however a nice workaround at the cost of additional hardware. There are third party tools which allows to mirror disks, logical volumes as well allows these mirrors to be temporary splitted and to be resynchronized afterwards.

What actually done is

at t0 the mirrors(s) is splitted ( takes less than a second )

at t1 the online database operations continue, the mirror is mounted on another host and a normal database backup is done. RMAN is still preferred, but the resources necessary to RMAN backup are not those from the online database server(s) since you mount your mirror on a backup server.

at t2 the mirror is resynchronized. It is said this resynchronization occurs in background.

Even though the split mirror operation is very very fast we wonder whether there is something to be done at the database level, beacuse oracle writes very often to disk.

It apparently can depend on the exact third party technology used but my --- I admit limited --- experience learns

1. you can shutdown immediate your database prior to the split, which obviously decreases high availability

2. you can put all of your data tablespaces in backup mode AND you can temporary suspend any Oracle IO with the "alter system suspend" command. I think to know Oracle has introduced this command exactly for this pupose ( disk / storage snapshotting ) Note from 10G R1 onwards there is the "alter database begin backup" and "alter database end backup". Putting your database in backup mode is a technology as old as Methusalem and allows you copy your datafiles with an os utility while online databases operations goes on. But putting your database in backup mode does not halt the IO at the level of control files nor at redo logs. It can occur your instance crashes because at the moment you take your snapshot there is an SCN update or commit in controlfiles and redo log files. So even though extremely short to be 100% sure you should use "alter system suspend" to disable IO and "alter system resume" to enable it afterwards.

Below we notice an instance crashed at the moment a snapshot was done even though all the tablespaces were in backup mode.

Thread 1 advanced to log sequence 2779
Current log# 1 seq# 2779 mem# 0: D:\ORACLE\ORADATA\PLATINUM\REDO01.LOG
Thu Jan 29 04:46:43 2009
alter tablespace SYSTEM begin backup
Thu Jan 29 04:46:43 2009
Completed: alter tablespace SYSTEM begin backup
Thu Jan 29 04:46:43 2009
alter tablespace UNDOTBS1 begin backup
Completed: alter tablespace UNDOTBS1 begin backup
Thu Jan 29 04:46:43 2009
alter tablespace SYSAUX begin backup
Completed: alter tablespace SYSAUX begin backup
Thu Jan 29 04:46:43 2009
alter tablespace USERS begin backup
Completed: alter tablespace USERS begin backup
Thu Jan 29 04:46:43 2009
alter tablespace PLATINUM begin backup
Completed: alter tablespace PLATINUM begin backup
Thu Jan 29 04:46:43 2009
alter tablespace PALLADIUM begin backup
Completed: alter tablespace PALLADIUM begin backup
Thu Jan 29 04:46:44 2009
Errors in file d:\oracle\product\10.2.0\admin\PLATINUM\bdump\PLATINUM_lgwr_224.trc:
ORA-00345: redo log write error block 19771 count 2
ORA-00312: online log 1 thread 1: 'D:\ORACLE\ORADATA\PLATINUM\REDO01.LOG'
ORA-27070: async read/write failed
OSD-04016: Error queuing an asynchronous I/O request.
O/S-Error: (OS 1784) The supplied user buffer is not valid for the requested operation

Thu Jan 29 04:46:48 2009

Errors in file d:\oracle\product\10.2.0\admin\PLATINUM\udump\PLATINUM_ora_2452.trc:
ORA-00603: ORACLE server session terminated by fatal error
ORA-00340: IO error processing online log of thread

Below we notice how a snapshot did not interrupt database operations, since we used alter system suspend" to disable IO and "alter system resume" to enable it afterwards.

Private strand flush not complete
Current log# 1 seq# 2830 mem# 0: D:\ORACLE\ORADATA\PLATINUM\REDO01.LOG
Thread 1 advanced to log sequence 2831
Current log# 2 seq# 2831 mem# 0: D:\ORACLE\ORADATA\PLATINUM\REDO02.LOG
Sat Jan 31 05:23:52 2009
alter tablespace SYSTEM begin backup
Sat Jan 31 05:23:52 2009
Completed: alter tablespace SYSTEM begin backup
Sat Jan 31 05:23:52 2009
alter tablespace UNDOTBS1 begin backup
Completed: alter tablespace UNDOTBS1 begin backup
Sat Jan 31 05:23:52 2009
alter tablespace SYSAUX begin backup
Completed: alter tablespace SYSAUX begin backup
Sat Jan 31 05:23:53 2009
alter tablespace USERS begin backup
Completed: alter tablespace USERS begin backup
Sat Jan 31 05:23:53 2009
alter tablespace PLATINUM begin backup
Completed: alter tablespace PLATINUM begin backup
Sat Jan 31 05:23:53 2009
alter tablespace PALLADIUM begin backup
Completed: alter tablespace PALLADIUM begin backup
Sat Jan 31 05:23:53 2009
Suspending database after alter system suspend command
Resuming database after alter system resume command
Sat Jan 31 05:23:54 2009
alter tablespace SYSTEM end backup
Sat Jan 31 05:23:54 2009
Completed: alter tablespace SYSTEM end backup
Sat Jan 31 05:23:54 2009
alter tablespace UNDOTBS1 end backup
Completed: alter tablespace UNDOTBS1 end backup
Sat Jan 31 05:23:55 2009
alter tablespace SYSAUX end backup
Completed: alter tablespace SYSAUX end backup
Sat Jan 31 05:23:55 2009
alter tablespace USERS end backup
Completed: alter tablespace USERS end backup
Sat Jan 31 05:23:55 2009
alter tablespace PLATINUM end backup
Completed: alter tablespace PLATINUM end backup
Sat Jan 31 05:23:55 2009
alter tablespace PALLADIUM end backup
Completed: alter tablespace PALLADIUM end backup
Sat Jan 31 08:00:30 2009
Thread 1 advanced to log sequence 2832
Current log# 3 seq# 2832 mem# 0: D:\ORACLE\ORADATA\PLATINUM\REDO03.LOG
Sat Jan 31 23:01:49 2009
Thread 1 advanced to log sequence 2833
Current log# 1 seq# 2833 mem# 0: D:\ORACLE\ORADATA\PLATINUM\REDO01.LOG
Sun Feb 01 01:00:14 2009
Thread 1 advanced to log sequence 2834