Managing Backups and Recovery
Cold backup is taken when database is shutdown normal. The following files should be backed up.
-All data files
-All control files
-All online redo files
-The init.ora (optional)
It is easy to take a backup of datafiles if the database files architecture uses a consistent directory structure. Having all of these files backed up while the database is closed provides a complete image of the database as it existed at the time it was closed. The full set of these files could be retrieved from the backups at a later date and the database would be able to function. It is not valid to perform a file system backup of the database while it is open, unless a hot backup is performed. The steps involved in taking a cold backup of a database are as follows:
$mkdir -p /disk3/oradata/DEMO/BKUP
$vi getfiles.sql
SET ECHO OFF
SET PAUSE OFF
SET FEED OFF
SET HEAD OFF
SPOOL cold_backup.sh
SELECT 'cp'||name||'/disk3/oradata/DEMO/BKUP' FROM v$datafile;
SELECT 'cp'||name||'/disk3/oradata/DEMO/BKUP' FROM v$tempfile;
SELECT 'cp'||name||'/disk3/oradata/DEMO/BKUP' FROM v$controlfile;
SELECT 'cp'||member||'/disk3/oradata/DEMO/BKUP' FROM v$logfile;
SPOOL off
:wq
$sqlplus /nolog
SQL>connect / as sysdba
SQL>STARTUP
SQL>@getfiles.sql
SQL>SHUTDOWN
SQL>EXIT
Taking backup to BKUP directory:
$sh cold_backup.sh
Changing to BKUP directory:
$cd BKUP
Checking the contents:
$ls -l
Taking cold backup using 'tar' command:
$vi getfiles.sql
SET ECHO OFF
SET PAUSE OFF
SET FEED OFF
SET HEAD OFF
SPOOL cold.sh
SELECT name FROM v$datafile;
SELECT name FROM v$tempfile;
SELECT name FROM v$controlfile;
SELECT member FROM v$logfile;
SPOOL off
EXIT
:wq
SQL>@getfiles.sql
SQL>CONNECT /AS SYSDBA
SQL>SHUTDOWN
SQL>
$file='cat cold.sh' /* stores the file names in a variable */
$echo "tar Pcvf /disk5/oradata/DEMO/cold.tar"
$file>tar.cmd
Taking a backup:
$sh tar.cmd
To restore the database issue:
$tar Pxvf cold.tar
Hot Backup:
Whole Database Backup:
Whole database backup can be performed while the database is up and running in Archive log mode.
$mkdir -p /disk5/oradata/DEMO/HOTBKUP
$vi hot.sql
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SPOOL hot_backup.sql
SELECT 'spool hot.log' FROM dual;
SELECT 'alter database '||database_name||' begin backup;'||chr(10)||'!cp'||file_name||'/disk5/oradata/DEMO/HOTBKUP'||chr(10)||'alter database '||database_name||' end backup;' FROM dual;
SELECT 'alter database backup controlfile to '||'''/disk5/oradata/demo/HOTBKUP/control.new'''||';' FROM dual;
SELECT 'alter system switch logfile;' FROM dual;
SPOOL off
@hot_backup.sql /* execute the SQL file just generated */
EXIT
:wq
$cd /disk5/oradata/DEMO/HOTBKUP
$ls -l
Hot Backup at Tablespace Level:
Hot backup is taken when the database is up and running in Archive log mode. Hot backup can be taken on Tablespace by Tablespace mechanism. You must out the Tablespace in Begin backup mode (using alter Tablespace command) and after finishing the backup you must set it to End backup mode. It is worth to note that hot backup will generate lot of Redo entries.
$mkdir -p /disk5/oradata/DEMO/HOTBKUP
$vi hot.sql
SET ECHO OFF
SET FEEDBACK OFF
SET HEADING OFF
SPOOL hot_backup.sql
SELECT 'alter tablespace '||tablespace_name||' begin_backup;'||chr(10)||'!cp '||file_name||' /disk5/oradata/DEMO/HOTBKUP'||chr(10)||'alter Tablespace '||Tablespace_name||' end backup;' FROM dba_data_files;
SELECT 'alter database backup controlfile to '||'''/disk5/oradata/demo/HOTBKUP/control.new'''||';' FROM dual;
SELECT 'alter system switch logfile;' FROM dual;
SPOOL off
@hot_backup.sql /* execute the SQL file just generated */
EXIT
:wq
$cd /disk5/oradata/DEMO/HOTBKUP
$ls -l /* To check the contents */
Taking Hot Backups using Cursors:
$vi hot.sql
SET SERVEROUTPUT ON
SET ECHO OFF
SET HEAD OFF
SET FEED OFF
SPOOL hotbkp.sql
DECLARE
CURSOR t_tab is
SELECT DISTINCT (tablespace_name) FROM dba_data_files;
CURSOR f_tab (fs VARCHAR) is
SELECT file_name FROM dba_data_files WHERE tablespace_name = fs;
d_rec dba_data_files.file_name%type;
t_rec dba_data_files.tablespace_name%type;
BEGIN
OPEN t_tab;
LOOP
FETCH t_tab into t_rec;
exit when t_tab%NOTFOUND;
dbms_output.put_line ('alter tablespace '||t_rec||'begin backup;');
OPEN f_tab (t_rec);
LOOP
FETCH f_tab INTO d_rec;
EXIT WHEN f_tab%NOTFOUND;
dbms_output.put_line('!cp '||d_rec||'/disk5/oradata/DEMO/HOTBKUP');
END LOOP;
CLOSE f_tab;
dbms_output.put_line ('alter tablespace '||t_rec||' end backup;');
END LOOP;
CLOSE t_tab;
END;
/
SELECT 'alter system switch logfile; ' FROM dual;
SELECT ' '||'!mv /disk5/oradata/DEMO/HOTBKUP/control.new /disk5/oradata/DEMO/HOTBKUP/control.new /disk3/oradata/DEMO/HOTBKUP/control.old'||' ' FROM dual;
SELECT 'alter database backup controlfile to '||'''/disk5/oradata/DEMO/HOTBKUP/control.new'''||';' FROM dual;
SPOOL off
:wq
SQL>@hotbkup.sql
Online Backup Mode or Entire Database Backup:
If you are backing up all tablespaces datafiles at once in online backup mode, the command is:
SQL>ALTER DATABASE BEGIN BACKUP;
You no longer have the need to place each tablespace into backup mode individually unless you want to backup only one tablespace at a time. You cannot take individual tablespaces offline or in begin backup when whole database is in BEGIN BACKUP mode.
Back up all files of the online tablespaces. For example, a UNIX user might back up datafiles with the *.dbf suffix as follows:
$cp $ORACLE_HOME/oradata/trgt/*.dbf /disk2/backup/
To end the backup of the database:
SQL>ALTER DATABASE END BACKUP;
Archive the online redo logs so that the redo required to recover the tablespace backups will be available for later media recovery. For example, enter:
SQL>ALTER SYSTEM ARCHIVE LOG CURRENT;
Recovery:
Recovery is of three types. They are: online block recovery, thread recovery and media recovery. In all the three cases the algorithm that applies the redo records against an individual block is the same.
Block level:
It is automatically performed by Oracle during normal operation of the database and is transparent to the user and does not involve any action from the DBA. When a Process does while changing a buffer, Oracle reconstructs the buffer using the online redolog files for the current thread and writes it to the disk. Buffer header contains the information regarding the range of redo records that needs to be applied to it. If the PMON Process is performing block recovery, Oracle doesn't allow it to spend large amounts of time working on recovery of one buffer.
Thread Recovery:
If an instance crashes while it has a database open, it is necessary to do thread recovery. This involves applying the database on the redo changes in the thread that have occurred since the last time the thread was check pointed. The check point SCN stored in the control file for the thread instance dead will have the lost changes applied. Thread recovery is done either as a part of instance recovery or crash recovery. Instance recovery is done while the database is open and one instance detects that another instance as dead. This is possible only if you are running multiple instances instance, using parallel query option. If you restart a dead instance before instance recovery is done, then Oracle will do crash recovery. In general, the first instance to open the database after an abnormal termination (shutdown abort or database crash) does crash recovery.
Media Recovery:
Media recovery is done in response to a recovery command issued by the DBA. It is used to make backup datafiles current, or to restore changes that were lost when a datafile went offline without checkpoint. For example, if you take a Tablespace offline using the immediate option, the datafiles will go offline without a checkpoint being performed by Oracle. Media recovery can apply archived log files, as well as online log files.
Online and Offline Recovery:
Recovery Command Database Online Database Offline
Recover database No Yes
Recover Tablespace Yes No
Recover datafile Yes Yes
Syntax:
SQL>RECOVER [AUTOMATIC] [FROM 'LOCATION' ] [DATABASE] |UNTIL TIME/DATE|UNTIL CANCEL|UNTIL CHANGE USING BACKUP CONTROLFILE;
Complete Vs Incomplete Recovery:
Recovering the database from a media failure without losing any data is known as complete recovery. Other wise it is known as incomplete recovery. Complete recovery is implemented when all the redolog files, backup datafiles (for all the lost or damaged datafiles), and a current valid control file are available.
Case 1:
The database is running in NOARCHIVE mode and you lost a datafile because of Media failure and you take cold backup every night. How you'll recover the database. The scenario can be simulated as follows.
Steps:1
Take cold backup of the database. Startup the database and perform some Transactions. Now using HOST command remove one datafile at the operating system level. Now abort the instance.
Now try to open the database, you will get an Error stating that particular Datafile is missing. Now shutdown the database and restore the previous night's backup and open the database. So you lost today's transactions. This is complete recovery though you lost today's actions because as far as the database is concerned it did not loose any thing, which came from last night. It may appear to you that it is in complete, but it is still complete recovery for that time.
Note:
You just cannot restore the lost datafile from previous backup and start up the database, because the database will be in inconsistent state. So it will fail.
Case 2:
Everything is same except that is running in ARCHIVE mode. Here you restore the lost file from the previous nights backup, Mount the Database and issue the command RECOVER DATAFILE AUTOMATIC. Oracle will apply the relevant archived log files and online redo log files and then it will open the database. Here, you have lost no data hence it is complete recovery.
Case 3:
Everything is as above except that you lost the online redolog files only. In this case you have archived log files but not online redolog files. So you can restore up to the last available archived log file only by issuing the command Recover database until cancel. Cancel the media recovery immediately after applying the last archived file, open database with resetlogs option. This will invalidate the previous archived log files. This is an incomplete recovery.
Case 4:
Database is running in Archive log mode. We used to take cold backup every night. On one day a programmer accidentally dropped one important table (assume that At 11:29:45 a.m.). You realized this at 2:00 p.m. As this is a critical database without losing others data you have to recover the lost table.
Steps:
-Switch the log file.
-Shutdown the database.
-Take the cold backup of the current database (New backup).
-Restore only the datafiles from previous night's backup provided there are no structural changes to the database.
-Mount the database and issue the command.
SQL>ALTER DATABASE RECOVER AUTOMATIC USING BACKUP CONTROLFILE UNTIL TIME '2005-08-07:11:29:00';
-[using backup control file if you have restored the control file other wise use current Control file only].
-Open the database by resetting the logs.
-Export the table, which the programmer has dropped.
-Shutdown the database.
-Restore the new backup.
-Startup the database.
-Now import the table, which was exported in step 6
Case 5:
A DBA has lost both the control files of a database, which is in archive log mode. To recover the database, use CREATE CONTROLFILE command. The scenario can be simulated as follows:
SQL> !rm /disk1/oradata/demo/control1.ctl
SQL> !rm /disk2/oradata/demo/control2.ctl
Steps:2
$vi crctl.sql
create controlfile reuse database DEMO archivelog
LOGFILE
GROUP 1('/disk1/oradata/demo/redolog1.log','/disk2/oradata/demo/redolog2.log') SIZE 4M,
GROUP 2('/disk1/oradata/demo/redolog1.log','/disk2/oradata/demo/redolog2.log') SIZE 4M
DATAFILE '/disk1/oradata/demo/system01.dbf'
RESETLOGS
RECOVER DATABASE;
SQL>startup nomount
SQL>@crctl.sql
SQL>ALTER DATABASE OPEN RESETLOGS;
Note:
Do not forget to take a fresh backup now.
No comments:
Post a Comment