Backup
and Recovery Enhancements in the Oracle 10g database
"There
are times when you have to obey a call which is the highest of all,
i.e. the voice of conscience even though such obedience may cost many a
bitter tear, and even more, separation from friends, from family, from the
state to which you may belong, from all that you have held as dear as life
itself. For this obedience is the law of our being."
Gandhi
|
Backup
and Recovery Enhancements in the Oracle 10g database
In Oracle Database
10g a new feature to automatically inform users of performance and resource
allocation problems was added. This feature will provide suggestions how to fix
the problem or the database can fix them automatically for you. A new backup and
recovery strategy was introduced to backup data into disks rather than tape. It
is faster and cheaper. You must use RMAN.
What is the Flash
Recovery Area?
It is a unified
storage location for all recovery-related files and activities in an Oracle
Database. It includes Control File, Archived Log Files, Flashback Logs, Control
File Autobackups, Data Files, and RMAN files.
How to define a
Flash Recovery Area?
To define a Flash
Recovery Area set the following Oracle Initialization Parameters.
SQL>
ALTER SYSTEM SET db_recovery_file_dest_size = 100G;
SQL>
ALTER SYSTEM SET db_recovery_file_dest = "/u10/oradata/school";
Use the
V$RECOVERY_FILE_DEST view to display information regarding the flash recovery
area.
SQL>
SELECT name, space_limit, space_used,
space_reclaimable, number_of_files
FROM v$recovery_file_dest;
A warning will be
issued if the flash recovery area uses space at 85 percent and a critical
warning will be issued if it is at 97 percent. To see warning messages execute
the following SQL statements.
SQL>
SELECT object_type, message_type,
message_level, reason, suggested_action
FROM dba_outstanding_alerts;
To Backup the Flash
Recovery Area, just log to RMAN and run the backup command:
RMAN>
BACKUP RECOVERY FILES;
The files on disk
that have not previously been backed up will be backed up. They are: full and
incremental backup sets, control file autobackups, archive logs, and datafile
copies.
The best practice
is to use Oracle Managed File (OMF) to let Oracle database to create and manage
the underlying operating system files of a database.
SQL>
ALTER SYSTEM SET
db_create_file_dest = "/u03/oradata/school";
SQL>
ALTER SYSTEM SET
db_create_online_dest_1 = "/u04/oradata/school";
To enable Fast
Incremental Backup to backup only those data blocks that have changed perform
the following SQL statement.
SQL>
ALTER DATABASE enable BLOCK CHANGE TRACKING;
To monitor block
change tracking perform the following SQL statement.
SQL>
SELECT filename, status, bytes
FROM v$block_change_tracking;
It shows where the
block change tracking file is located, the status of it and the size.
Use the
V$BACKUP_DATAFILE view to display how effective the block change tracking is in
minimizing the incremental backup I/O.
SQL>
SELECT file#, AVG(datafile_blocks), AVG(blocks_read),
AVG (blocks_read/datafile_blocks), AVG(blocks)
FROM v$backup_datafile
WHERE used_change_tracking = "YES" AND incremental_level > 0
GROUP BY file#;
If the AVG (blocks_read/datafile_blocks)
column is high then you may have to decrease the time between the incremental
backups.
To backup the
entire database:
RMAN>
BACKUP DATABASE;
To backup an
individual tablespaces:
RMAN>
CONFIGURE DEFAULT DEVICE TYPE TO DISK;
RMAN>
BACKUP TABLESPACE system;
To backup datafiles
and control files:
RMAN>
BACKUP DATAFILE 3;
RMAN>
BACKUP CURRENT CONTROLFILE;
Now, you can use a
fast recovery without restoring all backups from their backup location to the
location specified in the controlfile.
RMAN>
SWITCH DATABASE TO COPY;
RMAN will adjust
the control file so that the data files point to the backup file location and
then starts recovery.
Also, you can begin
and end backup on the database level.
SQL>
ALTER DATABASE BEGIN BACKUP;
Copy all the
datafiles"
SQL>
ALTER DATABASE END BACKUP;
Hands-On
#1:
Set a Flash
Recovery Area:
Make sure that the
database uses the OMF method. Set the following initialization parameters.
SQL>
ALTER SYSTEM SET
db_create_file_dest = "/u03/oradata/school";
SQL>
ALTER SYSTEM SET
db_create_online_dest_1 = "/u04/oradata/school";
Make sure that the
database is in archive log mode.
SQL>
ARCHIVE LOG LIST
Set the flash
recovery area:
SQL>
ALTER SYSTEM SET db_recovery_file_dest_size = 100G;
SQL>
ALTER SYSTEM SET db_recovery_file_dest = "/u10/oradata/school";
Gather information
regarding the flash recovery area.
SQL>
SELECT name, space_limit, space_used,
space_reclaimable, number_of_files
FROM v$recovery_file_dest;
Enable Fast
Incremental Backup.
SQL>
ALTER DATABASE enable BLOCK CHANGE TRACKING;
Display it.
SQL>
SELECT filename, status, bytes
FROM v$block_change_tracking;
|