iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Oracle 10g New Features

 

 

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;

 

 

 

Google
 
Web web site