CASE 7: DATABASE CRASH DURING HOT BACKUPS:
On Friday afternoon, while taking hot backups the machine crashed bringing the database down. Once the machine was booted, he tried to start the database and Oracle asked for media recovery starting from log file sequence number 540. The current online log file had sequence number 565. which meant that about 25 logfiles needed to be applied before the database could be opened.
SOLUTION: Oracle gives the DBAs the ability to end the backups of the the data files that are in hot backup mode by using the alter database data file ‘file_name’ end backup command. You still need to apply recovery when the database crashes while taking online backups.
CONN /AS SYS DBA STARTUP ALTER TABLESPACE USER_ORCL BEGIN BACKUP; $ cp /a001/oradata/ORCL/user_orcl01.dbf /w001/oradata/backup/hot DELETE FROM emp_hot WHERE ROWNUM 101; ALTER SYSTEM SWITCH LOGFILE; SHUTDOWN ABORT
Now we can perform two methods. The first method involves attempting to open the database with the current data file user_orcl01.dbf, the second method involves replacing the current data file user_orcl01.dbf with the backup version and performing recovery.
METHOD 1: Using the current data fileSTARTUP MOUNT ALTER DATABASE DATAFILE ‘/a001/oradata/ORCL/user_orcl01.dbf’ END BACKUP; ALTER DATABASE OPEN;
METHOD 2: Using the Backup Data File$ cp /w001/oradata/backup/hot/user_orcl01.dbf /a001/ oradata/ORCL STARTUP MOUNT ALTER DATABASE DATAFILE ‘/a001/oradata/ORCL/user_orcl01.dbf’ END BACKUP; RECOVER DATABASE; ALTER DATABASE OPEN;
OBSERVATION: When a tablespace is in hot backup mode. Only some data structures in the data file header (of headers if multiple data files exist for that tablespace) are updated while the others are frozen.
For example, if upadate is done on a table in this data file the update is not blocked. But when a checkpoint is done the checkpointed at SCN value is not written to the file header. This means that if a crash occurs during a hot backup. Oracle really needs to update only the file header and not the contents of the data file while opening the database.
If you want to know if any data files are in hot backup mode use the V$BACKUP view when the status says ACTIVE then the file is in hot backup mode.