Backup & Recovery
Tablespaces and datafiles in backup mode
User Rating: 
/ 1
- Details
-
Parent Category: Articles
-
Created on Tuesday, 16 March 2010 22:16
-
Last Updated on Tuesday, 08 January 2013 10:34
-
Published on Tuesday, 16 March 2010 22:16
-
Written by Guy Lambregts
-
Hits: 17706
These days RMAN is the recommended utility to backup your Oracle Database. ( see "Why RMAN" ). We can however still make a user managed backup. This involves the need to have put your tablespaces or data files in backup mode BEFORE backing up data files with an OS utility. ( with "alter tablespace tablespace_name begin backup" ) Starting from release 10G R1 the entire database can be put in backup mode ( with "alter database begin backup" ).
I appreciate very much Misconception : hot backup mode stops writing to the datafiles an article from Jeremiah Wilton.
Whether a data file is in backup mode or not can be tracked down with a select from v$backup.
Time for some gym
DB: / USER:SYSTEM > alter tablespace users begin backup;
Tablespace altered.
DB: / USER:SYSTEM > select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- --------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 ACTIVE 8876055 03/07/06 >>> This is the datafile from tablespace USERS ( in backup mode )
6 NOT ACTIVE 0
7 NOT ACTIVE 0
8 NOT ACTIVE 0
9 NOT ACTIVE 0
DB: / USER:SYSTEM > alter tablespace users end backup;
DB: / USER:SYSTEM > select * from v$backup;
FILE# STATUS CHANGE# TIME
---------- ------------------ ---------- --------
1 NOT ACTIVE 0
2 NOT ACTIVE 0
3 NOT ACTIVE 0
4 NOT ACTIVE 0
5 NOT ACTIVE 8876055 03/07/06 >>> The datafile is not in backup mode anymore.
6 NOT ACTIVE 0
7 NOT ACTIVE 0
It apparently can happen that a tablespace stays in backup mode, mostly due to badly written scripts. I know however one particular interesting case : a backup agent didn' t take a huge tablespace out off backup mode after backing up its data files and this for a not known reason. The tablespace stayed in backup mode for more than a month. ( only possible with limited dba supervision off course ). The end users where not aware of it, until .... until the next start up triggered the need for recovery. Oracle asked for archivelogs starting from the moment the tablespace was brought in backup mode. ( because the data file headers of the data files of that tablespace were not check pointed anymore ). Our customer was in big trouble since the archives were not available anymore. I heard people were wondering the database asked for archives of 1 month ago whereas they didn' t notice any problem with the database until the shutdown of it. I asked for the alert log of the database instance. ( the alert log of the instance has always been an important source of information ) In stead of applying the archives ( since they were not available anymore ) we were able to open the database in a very simple way.
sql > startup mount;
sql > alter tablespace "tablespace_name" end backup;
sql > alter database open;
The data blocks of the tablespace concerned didn' t need recovery, only the data file headers were not up to date. By applying the alter tablespace end command we adjusted the data file headers and the database could be opened. If you wanna still take user managed backups you should start and end with the "alter system archive log current" command and NOT with "alter system switch log file". As a latest step take a backup of the control file with the "alter database backup current control file" command. Always test your backups. Make yourself confidential with recovery on your test platform BEFORE putting something into production.
