|
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;
|
