Monday , September 25 2017
Home / Oracle DBA / Backup & Recovery Scenario

Backup & Recovery Scenario

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.

EXAMPLE:


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 file


STARTUP 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.

Read More:
SQL Server Error Logs Detailed Information
sql server dba interview questions from Unisys company
Redo Logfile Management

Comments

comments

Check Also

How to switch on primary database to physical standby database

After configuration data guard then data is switching  into primary database  to standby database : …

Leave a Reply

Your email address will not be published. Required fields are marked *