Reviewed by Oracle Certified Master Korea Community

( http://www.ocmkorea.com http://cafe.daum.net/oraclemanager )

OORRAACCLLEE 1100GG BBAACCKKUUPP AANNDD RREECCOOVVEERR NNEEWW FFEEAATTUURREESS

INTRODUCTION Two improvements have been made in the Backup and Recovery areas in Oracle 10g. When user errors and logical corruptions occur in the database, flashback functionalities provide fast and flexible data recovery. When physical or media corruption occurs in the database, RMAN delivers an improved and simplified recovery method.

Here is a list of extended flashback features:

• Flashback Database • Flashback Drop • Flashback Table • Flashback Version Query • Flashback Transaction Query Here is a list of enhanced RMAN features:

• Automated Channel Failover • Automated File Creation During Recovery • Simplified Backups to Disk • Proxy Copy Backup of Archivelogs • Incrementally Updated Backups • Simplified Recovery Through Resetlogs • Full Database Begin Backup Command • Changes to the ALTER DATABASE END BACKUP Command • Change-Aware Incremental Backups • Automated Disk-Based Backup and Recovery • RMAN Database Dropping and Deregistration • Automated TSPITR Instantiation • Simplified Recovery Manager Cataloging of Backup Files

EXTENDED FLASHBACK FUNCTIONS In Oracle 10g, the flashback functionality has been greatly extended.

FLASHBACK DATABASE

Flashback Database is faster than traditional point-in-time recovery. Traditional recovery method uses backups and redo log files; Flashback Database is implemented using a new type of log file called Flashback Database logs. The Oracle database server periodically logs before images of data blocks in the Flashback Database logs. The data block images are used to quickly back out changes to the database during Flashback Database.

Flashback Database reduces the time required to recover the database to a point in time. The time to restore a database is proportional to the number of changes that need to be backed out, not the size of the database.

RVWR BACKGROUND PROCESS When Flashback Database is enabled, a new RVWR background process is started. This process is similar to the LGWR (log writer) process. The new process writes Flashback Database data to the Flashback Database logs.

D ATAB AS E

LG W R

FlashbackD atabase

Logs

R edoLogFiles

R VW R

Figure 1: RVWR Background process and Flashback Database Logs

The list below shows all the background processes for ‘grid’ instance. $ ps -ef | grep grid oracle 25124 1 0 16:32:05 ? 0:00 ora_s000_grid oracle 25116 1 0 16:32:04 ? 0:00 ora_reco_grid oracle 25169 1 0 16:32:22 ? 0:00 ora_rvwr_grid oracle 25112 1 0 16:32:04 ? 0:00 ora_ckpt_grid oracle 25110 1 0 16:32:04 ? 0:00 ora_lgwr_grid oracle 25108 1 0 16:32:04 ? 0:00 ora_dbw0_grid oracle 25114 1 0 16:32:04 ? 0:00 ora_smon_grid oracle 25118 1 0 16:32:04 ? 0:00 ora_cjq0_grid oracle 25120 1 0 16:32:04 ? 0:00 ora_rbal_grid oracle 25122 1 0 16:32:04 ? 0:00 ora_d000_grid oracle 25106 1 0 16:32:04 ? 0:00 ora_pmon_grid

ENABLING FLASHBACK DATABASE You can enable Flashback Database using the following steps:

1. Make sure the database is in archive mode. 2. Configure the recovery area by setting the two parameters:

• DB_RECOVERY_FILE_DEST • DB_RECOVERY_FILE_DEST_SIZE

3. Open the database in MOUNT EXCLUSIVE mode and turn on the flashback feature: SQL> STARTUP MOUNT EXCLUSIVE; SQL> ALTER DATABASE FLASHBACK ON;

4. Set the Flashback Database retention target: • DB_FLASHBACK_RETENTION_TARGET

DETERMINE IF FLASHBACK DATABASE IS ENABLED Issue the following command:

SQL> select flashback_on from v$database; FLASHBACK_ON ------------ YES

DISABLING FLASHBACK DATABASE Issue the following command to disable Flashback Database: SQL> ALTER DATABASE FLASHBACK OFF;

You can also perform the same task in Enterprise Manger:

MONITORING FLASHBACK DATABASE • Monitor logging in the Flashback Database logs:

SQL> select begin_time, flashback_data, 2 db_data, redo_data, ESTIMATED_FLASHBACK_SIZE 3 from v$flashback_database_stat; BEGIN_TIME FLASHBACK_DATA DB_DATA REDO_DATA ESTIMATED_FLASHBACK_SIZE -------------------- -------------- ---------- ---------- ------------------------ Oct 08 2003 14:17:34 753664 5324800 970752 0 Oct 08 2003 13:17:32 1720320 4751360 3124224 21749760 Oct 08 2003 12:17:31 1802240 4833280 3168256 21774336 Oct 08 2003 11:17:28 1867776 4587520 3146752 21774336 Oct 08 2003 10:17:24 1835008 4800512 3115008 21749760

Oct 08 2003 09:17:22 1785856 4702208 3120128 21749760 Oct 08 2003 08:17:17 1703936 4571136 3102720 21749760 Oct 08 2003 07:17:14 2768896 5767168 3237888 21798912 Oct 08 2003 06:17:11 1753088 4636672 3142656 21479424 Oct 08 2003 04:47:09 2686976 7143424 4862976 21479424 Oct 08 2003 03:47:05 1703936 4685824 3145728 21479424 Oct 08 2003 02:46:57 1785856 4653056 3137536 21528576 Oct 08 2003 01:46:52 1785856 4620288 3107840 21528576 Oct 08 2003 00:46:47 1769472 4964352 3245056 21528576 Oct 07 2003 23:46:44 1720320 4587520 3130368 21528576 Oct 07 2003 22:46:40 1769472 4669440 3112960 21577728 Oct 07 2003 21:46:38 1703936 4800512 3161088 21577728 Oct 07 2003 20:46:35 1785856 4653056 3155968 21626880 Oct 07 2003 19:46:30 1802240 4784128 3164160 21651456 Oct 07 2003 18:46:28 1753088 4685824 3120128 21528576 Oct 07 2003 17:46:26 1687552 4718592 3143680 21553152 Oct 07 2003 16:46:24 1851392 4603904 3120128 21577728 Oct 07 2003 15:46:21 1720320 4816896 3154944 21577728 Oct 07 2003 14:46:18 1736704 4587520 3196928 21577728 Oct 07 2003 13:46:16 1736704 4685824 3194880 21602304 25 rows selected.

• Monitor the Flashback Database retention target SQL> select * 2 from v$flashback_database_log; OLDEST_FLASHBACK_SCN OLDEST_FLASHBACK_TIME RETENTION_TARGET FLASHBACK_SIZE

ESTIMATED_FLASHBACK_SIZE -------------------- --------------------- ---------------- -------------- ------

------------------ 2.2029E+12 Oct 06 2003 09:44:42 1440 48316416

21774336 Note: The default value for flashback retention time is 1400 minutes.

• Adjust recovery area disk quota: SQL> select estimated_flashback_size 2 from v$flashback_database_log; ESTIMATED_FLASHBACK_SIZE ------------------------ 21823488

EXAMPLE 1: FLASHBACK A DATABASE USING RMAN

RMAN> FLASHBACK DATABASE 2> TO TIME = TO_DATE 3> (‘06/25/03 12:00:00’,’MM/DD/YY HH:MI:SS’);

EXAMPLE 2: FLASHBACK A DATABASE USING SQL COMMAND The database must be in mount state to issue these commands:

SQL> FLASHBACK DATABASE TO TIMESTAMP (SYSDATE – 5/24); SQL> FLASHBACK DATABASE TO SCN 76239;

You must issue the follow command afterwards: SQL> ALTER DATABASE RESETLOGS;

FLASHBACK DROP Prior to Oracle 10g, a DROP command permanently removed objects from the database. In Oracle 10g, a DROP command places the object in the recycle bin. The extents allocated to the segment are not reallocated until you purge the object. You can restore the object from the recycle bin at any time. This feature eliminates the need to perform a point-in-time recovery operation. Therefore, it has minimum impact to other database users.

Recycle Bin A recycle bin contains all the dropped database objects until,

You permanently drop them with the PURGE command.

Recover the dropped objects with the UNDROP command.

There is no room in the tablespace for new rows or updates to existing rows.

The tablespace needs to be extended.

You can view the dropped objects in the recycle bin from two dictionary views:

USER_RECYCLEBIN – list all dropped user objects

DBA_RECYCLEBIN – list all dropped system-wide objects

Example 1: Dropping an Object In the example below, when you drop an object and it is moved to the recycle bin, the name of the object is changed. The recycle bin also keeps the original name of the object. This feature allows you to create a new object of the same name and then drop it again. SQL> create table test (col_a varchar(4)); Table created. SQL> select object_name, original_name, type, createtime, droptime from user_recyclebin; no rows selected SQL> drop table test; Table dropped. SQL> select object_name, original_name, type, createtime, droptime from user_recyclebin; OBJECT_NAME ORIGINAL_NAME TYPE CREATETIME DROPTIME ------------------------------ ---------------- ------ ------------------- ------------------- BIN$0+ktoVCgEmXgNAAADiUEHQ==$0 TEST TABLE 2004-02-21:19:04:03 2004-02-21:19:04:41 SQL> create table test (col_b varchar(4)); Table created. SQL> select object_name, original_name, type, createtime, droptime from user_recyclebin; OBJECT_NAME ORIGINAL_NAME TYPE CREATETIME DROPTIME ------------------------------ ---------------- ------ ------------------- ------------------- BIN$0+ktoVChEmXgNAAADiUEHQ==$0 TEST TABLE 2004-02-21:19:07:33 2004-02-21:19:08:17

SQL> drop table test; Table dropped. SQL> select object_name, original_name, type, createtime, droptime from user_recyclebin; OBJECT_NAME ORIGINAL_NAME TYPE CREATETIME DROPTIME ------------------------------ ---------------- ------ ------------------- ------------------- BIN$0+ktoVCgEmXgNAAADiUEHQ==$0 TEST TABLE 2004-02-21:19:04:03 2004-02-21:19:04:41 BIN$0+ktoVChEmXgNAAADiUEHQ==$0 TEST TABLE 2004-02-21:19:07:33 2004-02-21:19:08:17

SQL> show recyclebin ORIGINAL NAME RECYCLEBIN NAME OBJECT TYPE DROP TIME ---------------- ------------------------------ ------------ ------------------- TEST BIN$0+ktoVChEmXgNAAADiUEHQ==$0 TABLE 2004-02-21:19:08:17 TEST BIN$0+ktoVCgEmXgNAAADiUEHQ==$0 TABLE 2004-02-21:19:04:41

Example 2: Restoring a Dropped Object This example will restore a dropped table test. SQL> flashback table “BIN$0+ktoVChEmXgNAAADiUEHQ==$0” to before drop; Flashback complete.

Example 3: Dropping a Table Permanently This statement puts the table in the recycle bin: SQL> drop table test purge; Table dropped.

This statement removes the table permanently: SQL> purge table "BIN$0+ktoVChEmXgNAAADiUEHQ==$0"; Table purged.

Example 4: Dropping a Tablespace You can only issue this command when the tablespace users is empty. Object in the recycle bin of tablespace users will be purged: SQL> drop tablespace users;

When you issue this command, objects in the tablespace users are dropped. They are not placed in the recycle bin. Any objects in the recycle bin belonging to the tablespace users are purged. SQL> drop tablespace users including contents;

Example 5: Purging the Recycle Bin This statement purges the user recycle bin: SQL> purge recyclebin; Recyclebin purged.

This statement removes all objects from the recycle bin: SQL> purge dba_recyclebin;

DBA Recyclebin purged.

This statement purges all objects from tablespace users in the recycle bin: SQL> purge tablespace users; Tablespace purged.

FLASHBACK TABLE

Flashback Table allows you to recover a table or tables to a specific point in time without restoring a backup. When you use the Flashback Table feature to restore a table to a specific point in time, all associated objects, such as, indexes, constraints, and triggers will be restored. Flashback Table operations are not valid for the following object types:

Tables that are part of a cluster

Materialized views

Advanced Queuing tables

Static data dictionary tables

System tables

Partitions of a table

Remote tables (via database link)

Flashback Table is extremely useful when a user accidentally inserts, deletes, or updates the wrong rows in a table. It provides a way for users to easily and quickly recover a table to a point in time in the past. However, if the following DDL commands are issued, the flashback table command does not work:

ALTER TABLE ... DROP COLUMN

ALTER TABLE ... DROP PARTITION

CREATE CLUSTER

TRUNCATE TABLE

ALTER TABLE ... MOVE

UNDO_RETENTION Parameter Data used to recover a table is stored in the undo tablespace. You can use the parameter UNDO_RETENTION to set the amount of time you want undo information retained in the database. To create an undo tablespace with the RETENTION GUARANTEE option, issue the following command: CREATE UNDO TABLEAPCE undo_tbs DATAFIEL ‘/u02/oradata/grid/undo_tbs01.dbf’ SIZE 1 G RETENTION GUARANTEE;

Guaranteed Retention When an active transaction uses all the undo tablespace, the system will start reusing undo space that would have been retained unless you have specified RETENTION GUARANTEE for the tablespace.

Flashback Table Privileges You must have the FLASHBACK TABLE or FLASHBACK ANY TABLE system privilege to use the Flashback Table feature.

Example 1: Flashback Table using SCN This statement brings a table ‘billing’ back to a certain SCN number; table row movement must be enabled as a prerequisite: SQL> FLASHBACK TABLE billing TO SCN 76230;

Example 2: Flashback Table using TIMESTAMP This statement brings a table ‘billing’ back to a certain timestamp: SQL> FLASHBACK TABLE billing TO TIMESTAMP TO_TIMESTAMP(‘06/25/03 12:00:00’,’MM/DD/YY HH:MI:SS’);

FLASHBACK VERSIONS QUERY

Flashback Query was first introduced in Oracle9i, to provide a way for you to view historical data. In Oracle 10g, this feature has been extended. You can now retrieve all versions of the rows that exist or ever existed between the time the query was issued and a point back in time. This type of query is called Flashback Versions Query. You can use the VERSIONS BETWEEN clauses to retrieve all historical data related to a row. Let’s take a look at the example below: SQL> create table emp (name varchar2(10), salary number(8,2)); Table created. SQL> insert into emp values ('DANIEL',2000); 1 row created. SQL> commit; Commit complete. SQL> update emp set salary = 3000 where name = 'DANIEL'; 1 row updated. SQL> commit;

Commit complete. SQL> select * from emp; NAME SALARY ---------- ---------- DANIEL 3000

SQL> select * from emp versions between scn minvalue and maxvalue; NAME SALARY ---------- ---------- DANIEL 3000 DANIEL 2000

As you can see, the Flashback Versions Query feature retrieves all committed occurrences of the row. It provides you with a way to view and repair historical data. In addition, it also provides a new way to audit the rows of a table and retrieve information about the transactions that changed the rows. You can use the transaction ID obtained from Flashback Versions Query to perform transaction mining using LogMiner or Flashback Transaction Query (see next section) to obtain additional information about the transaction. The VERSION BETWEEN clause does not change the query plan. You can use the clause in a SELECT statement against a view. However, you cannot use the VERSION BETWEEN clause in a view definition. The row history data is stored in the undo tablespace. The undo_retention initialization parameter specifies how long the database will keep the amount of committed undo information. If a new transaction need to use undo space and there is not enough free space left, any undo information older than the specified undo retention period will be overwritten. Therefore, you may not be able to see all the row histories. However, you can set the undo tablespace option to RETENTION GUARANTEE to retain all row histories. To verify the retention value for the tablespace, you can issue the following statement: SQL> select tablespace_name, retention 2 From dba_tablespaces; TABLESPACE_NAME RETENTION ------------------------------ ----------- SYSTEM NOT APPLY UNDOTBS1 NOGUARANTEE SYSAUX NOT APPLY TEMP NOT APPLY EXAMPLE NOT APPLY USERS NOT APPLY

6 rows selected.

FLASHBACK TRANSACTION QUERY

The Flashback Transaction Query feature provides a way for you to view changes made to the database at the transaction level. It allows you to diagnose problems in your database and perform analysis and audit transactions. You can use this feature in conjunction with the Flash Versions Query feature to roll back the changes made by a transaction. You can also use this feature to audit user and application transactions. The Flashback Transaction Query provides a faster way to undo a transaction than LogMiner. You can retrieve the transaction history from flashback_transaction_query view: SQL> desc dba_transaction_query Name Null? Type ------------------------------------ -------- ---------------- XID RAW(8) START_SCN NUMBER START_TIMESTAMP DATE COMMIT_SCN NUMBER COMMIT_TIMESTAMP DATE LOGON_USER VARCHAR2(30) UNDO_CHANGE# NUMBER OPERATION VARCHAR2(32) TABLE_NAME VARCHAR2(256) TABLE_OWNER VARCHAR2(32) ROW_ID VARCHAR2(19) UNDO_SQL VARCHAR2(4000)

SQL> select versions_xid, name, salary 2 from emp 3 versions between scn minvalue and maxvalue; VERSIONS_XID NAME SALARY ---------------- ---------- ---------- 0003000E00000FE2 DANIEL 3000 DANIEL 2000 SQL> select * 2 from dba_transaction_query

3 where xid = '0003000E00000FE2'; SQL> select xid, start_scn, start_timestamp, table_name, undo_sql 2 from flashback_transaction_query 3 where xid = '0009001F000000B2' 4 / XID START_SCN START_TIMESTAMP TABLE_NAME UNDO_SQL ---------------- ---------- -------------------- ---------- ------------------------------------------------------------------------------- 0009001F000000B2 714980 Feb 21 2004 23:30:31 EMP update "ORACLE"."EMP" set "SALARY" = '4000' where ROWID = 'AAAMWJAAEAAAAFsAAA';

RMAN ENHANCEMENTS The second part of this paper, I will discuss the new features of Recovery Manager (RMAN) in Oracle 10g.

AUTOMATED CHANNEL FAILOVER FOR BACKUP AND RESTORE In Oracle 10g, when multiple channels are allocated for a backup and restore operation, and one of the channels fails during the operation, RMAN continues the job on the remaining channels. RMAN does not restart the backup or restore process for the failed channel. Such problem is typical when the media manager encounters problems with one tape drive. RMAN reports a message in v$rman_output and in the output to the terminal or log file when it encounters such problems.

AUTOMATED FILE CREATION DURING RECOVERY This feature enhances RMAN recovery by automatically creating and recovering datafiles that have never been backed up. In order to recover a data file that has never been backed up, you need the archive log files from the time of the data file creation until the time at which you want to stop the recovery process and a copy of the control file with the information regarding the data file.

data file#1

data file#2

Missingdata file

#3

Control fle

RMAN

Archive log files

Figure 2: Automated File Creation During Recovery

SIMPLIFIED BACKUPS TO DISK In previous releases of Oracle, RMAN had two separate commands to backup data files: BACKUP and COPY. The BACKUP command backed up the data file only to backup set, which is a proprietary format recognized by RMAN only. You must use RMAN to restore a data file from a backup set. The COPY command generated image copies, which are bit-by-bit copies of data files. You do not need RMAN to restore a database from an image copy. The BACKUP DATABASE command can backup a whole database to backup sets without specifying each individual data files. However, there is no corresponding COPY DATABASE command. Therefore, you must run the REPORT SCHEMA command to determine the file names of the data files, and then you need to specify each data files in your COPY command. RMAN> copy current controlfile to ‘dba/backup/grid/ctlfile.copy’, 2> datafile 1 to ‘dba/backup/grid/df1.copy’, 3> datafile 2 to ‘dba/backup/grid/df2.copy’, 4> datafile 3 to ‘dba/backup/grid/df3.copy’, 5> datafile 4 to ‘dba/backup/grid/df4.copy’, 6> datafile 5 to ‘dba/backup/grid/df5.copy’, 7> datafile 6 to ‘dba/backup/grid/df6.copy’;

In Oracle 10g, the COPY command is disfavored in favor of an enhanced BACKUP command that enables you to specify where RMAN should create copies or backup sets. You can use the new BACKUP AS COPY command to copy an entire database or multiple tablespaces, data files, archived logs. Here is an example to backup an entire database as an image copy to the recovery area. RMAN> backup as copy tag “weekly_backup” database;

PROXY COPY BACKUP OF ARCHIVELOGS

The proxy functionality was first introduced in Oracle8i Release 1 (8.1.5). A proxy copy is a special type of backup in which RMAN turns over control of the data transfer to a media manager that supports this feature. The PROXY option of the BACKUP command specifies that a backup should be a proxy copy. For each file that you attempt to back up using the BACKUP PROXY command, RMAN queries the media manager to determine whether it can perform a proxy copy. If the media manager cannot proxy copy the file, then RMAN uses conventional backup sets to perform the backup. An exception occurs when you use the PROXY ONLY option, which causes Oracle to issue an error message when it cannot proxy copy. Prior to Oracle 10g, you could RMAN to perform proxy backups of data files and data file copies. However, you could not perform proxy backup of archive log files or control file copies. You can now use RMAN to perform proxy backups of archive log files. You can use the rc_proxy_archivelog dictionary view to determine the proxy backups recorded in the catalog. RMAN> backup device type sbt proxy only 2> archivelog from logseq 35 thread 1;

INCREMENTALLY UPDATED BACKUPS

In Oracle 10g, you can apply incremental backups to data file image copy backups to roll them forward to a specified point in time. This new feature provides the following benefits:

By periodically updating image copy of data file with incremental backups, the updated image copy of data file is moving forward to a more recent state (with more current SCN). This could potentially result in reduce recovery time.

Avoid performing a full image copy after incremental backups.

Image copyof data fileSCN = 2345

UpdatedImage copyof data fileSCN = 2765

RMAN

Incremental backup files

apply incremental backup filesRolling forward Image Copies

Figure 3: Incrementally Updated Backups

Example The following statement roll forward an image copy of data file /dba/backup/grid/data01.imgcopy: RMAN> recover datafilecopy ‘/dba/backup/grid/data15.imgcopy’;

Or, you can issue this command to perform the same operation: RMAN> RECOVERY COPY OF DATAFILE 15;

SIMPLIFIED RECOVERY THROUGH RESETLOGS After you performed an incomplete (Point-in-time) recovery, you need to open the database with RESETLOGS option: SQL> alter database open resetlogs;

This RESETLOGS operation creates a new incarnation of the database and resets the logs. Prior to Oracle 10g, the newly generated redo log files could not be used with the backups taken in the past. Therefore, it was important to take an immediate backup since all previous backups became invalid. In addition, if you used RMAN catalog for future backups, you needed to issue the following command to make the RMAN catalog aware of the new incarnation of the database. RMAN> reset database;

In Oracle 10g, you no longer have to back up your database following an incomplete recovery and OPEN RESETLOGS operations. This new feature is also applicable for the following two scenarios:

When you perform a recovery using a backup control file and open the database with the RESETLOGS operation.

When you need to reinstantiate the old primary database following a failover (see Flashback Reinstantiation section for details).

Benefits of Simplified Recovery Through Resetlogs The Simplified Recovery Through Resetlogs feature provides the following benefits:

There is no need to perform a full backup after an incomplete recovery.

There is no need to recreate a new standby database after a failover operation.

There is need to change any backup scripts as there are no changes to the recovery commands to take advantage of this functionality.

You can take incremental backups based on full backups of a previous incarnation when you use RMAN.

Block media recovery can restore backups from parent incarnation backups and recover the corrupted blocks through a RESETLOGS operation.

How does it work? You may wonder how can you use the newly generated logs with an earlier incarnation of the database. Oracle 10g introduces a new format specification for archived log files. This new format avoids overwriting archived redo log files with the same sequence number across incarnations. SQL> show parameter log_archive_format NAME TYPE VALUE ---------------------------- ----------- ---------------- log_archive_format string %t_%s_%r.dbf

The format specification is %r and represents the resetlogs id. It is included in the default format for the LOG_ARCHIVE_FORMAT initialization parameter. It will ensure that a unique name is constructed for the archived redo log file during RMAN restore and SQL*plus auto recovery mode. During the RESETLOGS operation, the information in V$LOG_HISTORY and V$OFFLINE_RANGE records are no longer cleared. In addition, two new columns have been added to indicate the incarnation the records belong to: RESETLOGS_CHANGE# and RESETLOGS_TIME. Example: SQL> select recid, thread#, sequence#, resetlogs_change#,resetlogs_time 2 from v$log_history 3 where rownum < 20; RECID THREAD# SEQUENCE# RESETLOGS_CHANGE# RESETLOGS_TIME ---------- ---------- ---------- ----------------- -------------------- 1 1 1 1 Aug 12 2003 18:48:54 2 1 2 1 Aug 12 2003 18:48:54 3 1 3 1 Aug 12 2003 18:48:54 4 1 4 1 Aug 12 2003 18:48:54 5 1 5 1 Aug 12 2003 18:48:54 6 1 6 1 Aug 12 2003 18:48:54 7 1 7 1 Aug 12 2003 18:48:54 8 1 8 1 Aug 12 2003 18:48:54 9 1 9 1 Aug 12 2003 18:48:54 10 1 10 1 Aug 12 2003 18:48:54 11 1 11 1 Aug 12 2003 18:48:54 12 1 12 1 Aug 12 2003 18:48:54 13 1 13 1 Aug 12 2003 18:48:54 14 1 14 1 Aug 12 2003 18:48:54 15 1 15 1 Aug 12 2003 18:48:54 16 1 16 1 Aug 12 2003 18:48:54 17 1 17 1 Aug 12 2003 18:48:54 18 1 18 1 Aug 12 2003 18:48:54 19 1 19 1 Aug 12 2003 18:48:54 19 rows selected.

FULL DATABASE BEGIN BACKUP COMMAND In Oracle 10g, you can place all of the data files in the database in online backup mode using a single command: SQL> ALTER DATABASE BEGIN BACKUP;

You no longer need to place each tablespace in online backup mode individually: SQL> ALTER TABLESPACE user BEGIN BACKUP; SQL> ALTER TABLESPACE example BEGIN BACKUP;

The ALTER DATABASE BEGIN BACKUP command places all data files in a database in online backup mode. The database must be in mounted or open mode when you issue this command.

Example 1: Issue BEGIN BACKUP command when data files belonging to a tablespace are in READ-ONLY mode

SQL> alter tablespace users read only; Tablespace altered. SQL> alter tablespace users begin backup; alter tablespace users begin backup * ERROR at line 1: ORA-01642: begin backup not needed for read only tablespace 'USERS' SQL> alter database begin backup; Database altered.

Example 2: Issue BEGIN BACKUP command when data files belonging to a tablespace are in OFFLINE mode SQL> alter tablespace example offline; Tablespace altered. SQL> alter tablespace example begin backup; alter tablespace example begin backup * ERROR at line 1: ORA-01128: cannot start online backup - file 5 is offline ORA-01110: data file 5: 'C:\ORACLE\ORADATA\GRID\EXAMPLE01.DBF' SQL> alter database begin backup; Database altered.

The two examples above demonstrate that when you issue the ALTER DATABASE BEGIN BACKP command, any read-only and offline data files are simply skipped and processing continues.

CHANGES TO THE ALTER DATABASE END BACKUP COMMAND You can run the ALTER DATABASE END BACKUP command when you have multiple tablespaces still in backup mode. You can issue the statement to take all datafiles currently in backup mode out of backup mode. However, you can use this statement only when the database is mounted but not open in Oracle9i. If the database is open, you can only use ALTER TABLESPACE ... END BACKUP or ALTER DATABASE DATAFILE ... END BACKUP for each affected tablespace or datafile. In Oracle 10g, you can issue the ALTER DATABASE END BACKUP command when the database is open. If you issue the command while one of the datafiles is offline or in read-only mode, a warning message will return: SQL> alter database end backup; alter database end backup * ERROR at line 1: ORA-01260: warning: END BACKUP succeeded but some files found not to be in backup mode

CHANGE-AWARE INCREMENTAL BACKUPS In previous releases of the Oracle database, when you perform an incremental backup, RMAN has to examine every block in the data file to determine which blocks have been changed. The time to perform an incremental backup is proportional to the size of the data files. Therefore, to perform an incremental backup on a very large database can take some time even if you have just changed a few blocks. In Oracle 10g, you can create a block change tracking file that records the blocks modified since the last backup. RMAN uses the tracking file to determine which blocks to include in the incremental backup. RMAN no longer

needs to examine the entire data file. The time to perform an incremental backup is now proportional to the amount of content modified since the last backup. Here are the steps RMAN will perform to do an incremental backup:

1. Read the Block Change Tracking File to determine which blocks in the data file need to be read.

2. Only scan the changed blocks (See Figure 12.1) in the data file and then back them up.

Block Change Tracking File

Data File

RMAN Step 1

Step 2

Figure 4: Incremental Backup using Block Change Tracking File

How big is the Block Change Tracking File? The size of the block change tracking file is proportional to:

The database size in bytes: the block change tracking file contains data representing every data file block in the database. The data is approximately 1/250000 of the total size of the database.

The number of enabled threads: In a Real Application Cluster (RAC) environment, the instances update different areas of the tracking file without any locking or inter-node block swapping. You enable block change tracking for the entire database and not for individual instances.

The number of old backups: The block change tracking file keeps a record of all changes between previous backups, in addition to the modification since the last backup. It retains the change history for a maximum of eight backups.

The size of the file is calculated by the following formula: Size of the Block Change Tracking File =

( (Threads*2) + number of old backups ) * database size in bytes 250,000

The minimum size for the block change tracking file is 10 MB.

By using this formula, a 2 TB database with only one thread, and having five backups in the RMAN repository will require a block change tracking file of 59 MB. Enabling, Disabling and Monitoring Block Change Tracking By default, Oracle will not record block change information. To enable this feature, you need to issue the following command: SQL> alter database enable block change tracking;

To disable this feature, you issue this command: SQL> alter database disable block change tracking;

To monitor the status of block change tracking, you type: SQL> select file, status, bytes 2 from v$block_change_tracking; STATUS FILE BYTES ----------- ---------------------------- --------------- ENABLED /dba/backup/01_mf_yzmrr7.chg 10,000,000

AUTOMATED DISK-BASED BACKUP AND RECOVERY

Prior to Oracle 10g, disk files that were created by RMAN utility or ARCH process had no knowledge of one another. Furthermore, they were not aware of the sizes of the file system on which they created files. Database administrators need to routinely clean up the old archive logs or old RMAN files. It is nice to have a unified disk storage location where you can manage all recovery related files. Now you can achieve this in Oracle 10g by specifying a Recovery Area.

Recovery Area The recovery area is a unified disk storage location for all recovery related files and activities in an Oracle Database. Those files include:

Control file

Online redo log files

Archived log files

Flashback logs

Control file autobackups

Data file copies

RMAN files

Setting up a Recovery Area The recovery area is defined by setting two initialization parameters. These two parameters can be dynamically altered or disabled.

The db_recovery_file_dest_size sets the disk limit, expressed in bytes

The db_recovery_file_dest sets the location for the recovery area

Enabling a Recovery Area This statement sets the disk limit for recovery area to 100 GB: SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 100G

This statement sets the recovery area destination: SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = ‘/dba/backup/’;

Alter a Recovery Area This statement alters the size of the recovery area: SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST_SIZE = 200G;

Disabling a Recovery Area This statement disables a recovery area: SQL> ALTER SYSTEM SET DB_RECOVERY_FILE_DEST = ‘ ’;

Recovery Area Space Management When the recovery area is less than 90% full, Oracle does not delete eligible files immediately, to minimize the need to restore recent files from tape during recovery. The recovery area can thus serve as a kind of cache for tape. Once the recovery area is 90% full, Oracle will issue a warning to users. The Oracle database server and RMAN will continue to create files in the recovery area until 100% of the disk limit is reached. Once the recovery area is 100% full, the RMAN retention policy is used to indicate what files will be deleted in order to make space for newer files. The db_flashback_retention_target parameter specifies how long Oracle will keep the flashback logs in the flashback recovery area. However, flashback logs won't be deleted even if it exceeds the duration specified by the flashback retention period unless more space is needed in the recovery area for other files. New RMAN command for Recovery Area RMAN> BACKUP RECOVERY AREA; RMAN> BACKUP RECOVERY FILES;

New Recovery Area Dictionary View Oracle 10g has a new dynamic performance view for monitoring the recovery area: SQL> desc v$recovery_file_dest Name Null? Type ---------------------------------------- -------- ----------- NAME VARCHAR2(513) SPACE_LIMIT NUMBER SPACE_USED NUMBER SPACE_RECLAIMABLE NUMBER NUMBER_OF_FILES NUMBER

Column Description: NAME: Recovery area name, indicating location string. SPACE_LIMIT: used space by recovery area files in bytes SPACE_RECLAIMABILE: amount of space that can be created by deleting obsolete, redundant, and other low priority files through the space management algorithm. NUMBER_OF_FILE: number of files Example: SQL> select * from v$recovery_file_dest; NAME SPACE_LIMIT SPACE_USED SPACE_RECLAIMABLE NUMBER_OF_FILES ------------ ------------- ------------ ----------------- ---------------- /dba/backup 2147483648 458219520 0 49

RMAN DATABASE DROPPING AND DEREGISTRATION In Oracle 10g, you can drop a database and remove its entry from the RMAN catalog. The following statement drops the entire database and removes the database files: RMAN> drop database;

The following statement drops the entire database, removes the database files, and deletes all backup copies of the database and the archive log files: RMAN> drop database including backups;

The above two statements drop the database and delete the database files. However, they do not unregister the database from the RMAN catalog. The following statement will remove the database information from the RMAN catalog: RMAN> unregister database grid;

AUTOMATED TSPITR INSTANTIATION

In previous release of Oracle database, you can use RMAN to perform tablespace point in time recovery (TSPITR). However, you need to create an auxiliary instance manually. In Oracle 10g, when you perform tablespace point in time recovery, RMAN creates an auxiliary instance automatically on the same target database server. RMAN removes the auxiliary instance after you complete the tablespace recovery. RMAN creates the data files required by the auxiliary instance as specified by SET NEWNAME, CONFIGURE AUXNAME, and DB_FILE_NAME_CONVERT. RMAN removes these files when the recovery tasks are completed.

SIMPLIFIED RECOVERY MANAGER CATALOGING OF BACKUP FILES Every time RMAN backup a database into backup sets and backup pieces, it updates RMAN catalog to reflect the backup information. If a user remove, relocate, or rename a backup piece, you cannot make changes to the RMAN catalog accordingly in the earlier versions of the oracle database. However, in Oracle 10g, you can use CATALOG command enter the new filename or location for backup piece in the RMAN catalog. In addition, you can also use CATALOG command to enter user-managed data file copies. The UNCATALOG command is used to remove the backup pieces from the catalog.

Example 1: To catalog a backup piece RMAN> catalog backuppiece 2> ‘/dba/backup/rman/data01.bkp’;

Example 2: To catalog a user-managed data file copy RMAN> catalog backup 2> ‘/dba/backup/grid/data01.dbf’;

Example 3: To uncatalog a backup piece RMAN> change backupiece 2> ‘/dba/backup/grid/data01.bkp’ uncatalog;

CONCLUSION In this paper, we have examined the new and improved features related to backup and recovery in Oracle 10g. Specially, we have looked at two areas: extended flashback functions and enhanced RMAN utility. The new features simplify database backup and deliver fast and flexible data recovery.

REFERENCES

Oracle Database 10g New Features, Ault, Liu and Tumma; Rampant Techpress; Recovery Manager Reference, 10g Release; Backup and Recovery Advanced Users Guide, 10g Release; I would also like to acknowledge the assistance of Larry Bailey of FARES, Tammy Bednar, Larry Carpenter, Roger Peterson, Schwinn Ulrike of Oracle Corporation. All companies and product names are trademarks or registered trademarks of the respective owners. Please report errors in this article to the author. Neither FARES nor the author warrants that this document is error-free.