|
‘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
Introduction
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.
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 Auto-backups, Data Files, and RMAN files.
Defining 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’;
The V$RECOVERY_FILE_DEST
view
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;
Normal and Critical
Warining
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;
Backup the Flash Recovery
Area
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 auto-backups, archive logs, and
datafile copies.
The best practice
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;
Monitoring 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.
The V$BACKUP_DATAFILE view
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.
Performing backup using
RMAN
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.
Performing backup using
SQLPLUS
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-Setting
Recovery Area
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
Setting the Flash Recovery
Area
The following SQL statement
set your Flash Recovery Area.
SQL> ALTER SYSTEM SET
db_recovery_file_dest_size = 100G;
SQL> ALTER SYSTEM SET
db_recovery_file_dest = ‘/u10/oradata/school’;
Gathering information
The following SQL statement
gather information regarding the flash recovery area.
SQL> SELECT name,
space_limit, space_used,
space_reclaimable,
number_of_files
FROM v$recovery_file_dest;
Enabling Fast Incremental
The following SQL statement
enables the Fast Incremental backup.
SQL> ALTER DATABASE
enable BLOCK CHANGE TRACKING;
Display it.
SQL> SELECT filename,
status, bytes
FROM
v$block_change_tracking;
|
“A man who won't
die for something is not fit to live.” Martin Luther King Jr. |
Questions:
Questions on
Backup and
Recovery Enhancements in the Oracle 10g
database
Q: What is the Flash
Recovery Area?
Q: How do you define a
Flash Recovery Area?
Q: How do you use the
V$RECOVERY_FILE_DEST view to display information regarding the flash
recovery area?
Q: How can you display
warning messages?
Q: How do you backup the
Flash Recovery Area?
Q: How to use the best
practice to use Oracle Managed File (OMF) to let Oracle database to
create and manage the underlying operating system files of a database?
Q: How to enable Fast
Incremental Backup to backup only those data blocks that have changed?
Q: How do you monitor block
change tracking?
Q: How do you use the
V$BACKUP_DATAFILE view to display how effective the block change
tracking is in minimizing the incremental backup I/O?
Q: How do you backup the
entire database?
Q: How do you backup an
individual tablespaces?
Q: How do you backup
datafiles and control files?
Q: Use a fast recovery
without restoring all backups from their backup location to the
location specified in the controlfile.
Q: How can you begin and
end backup on the database level?
Q: How do you set the flash
recovery area?
Q: How do you gather
information regarding the flash recovery area?
|