Transcript Oracle 10g Backup and Recovery New Features

NoCOUG 2005 Winter Conference
Oracle 10g Backup and Recovery
New Features
Daniel T. Liu
Senior Technical Consultant
First American Real Estate Solutions
Date: Tuesday, February 8, 2005 @ 2:15 PM - 3:15 PM
Venue: Room 103, Oracle Conference Center, Redwood Shores, CA
Agenda
• Introduction
– Oracle Database 10g
– Challenges for Backup
and Recovery
– Types of Database
Errors
– Backup and Recovery
Solutions
NoCOUG 2005, Daniel T. Liu
2
Agenda
• Extended Flashback
Features
• Enhanced RMAN
Features
• Summary
• Q&A
NoCOUG 2005, Daniel T. Liu
3
Introduction to Oracle 10g
• Grid Computing Concept
– Utilizing computing resource as utility
– Software level
– Hardware level
• Grid Computing Challenges
– Standardization
– Security
– Reliability
NoCOUG 2005, Daniel T. Liu
4
Introduction to Oracle 10g
• Oracle Database 10g New Features
–
–
–
–
–
–
Real Application Clusters
Oracle Streams
Cross-platform transportable tablespaces
Automatic Storage Management (ASM)
Self-Managing Database
New Backup and Recovery Features
NoCOUG 2005, Daniel T. Liu
5
Challenges for Backup and Recovery
• Database size is getting bigger and bigger
• Database Backup Time
– A DLT tape writes 6 MB/Second, or 21
GB/hour
– A 2 TB database with one tape driver will
take 97 hours to backup
• Database Recovery Time
• Is the backup good?
• Trouble shooting time
NoCOUG 2005, Daniel T. Liu
6
Types of Database Errors
• Corruptions
– Logical corruptions
– Physical corruptions
• Human Errors
– Accidentally drop, or truncate a table
– Accidentally delete, update rows in a table
– Accidentally delete a data file or drop a tablespace
• Disasters
–
–
–
–
War, terrorism
Earthquake, flood, fire or hurricane
No power for a long period of time
Server crash, malfunction of hardware
NoCOUG 2005, Daniel T. Liu
7
Backup and Recovery Solutions
• Logical Methods
– Export and Import
– Data Pump
– Flashback Functions
• Physical Methods
– O/S Backup and Restore
– RMAN Backup and Restore
• High Availability Methods
– Data Guard
– RAC
– Oracle Streams
NoCOUG 2005, Daniel T. Liu
8
Extended Flashback Functions
•
•
•
•
•
Flashback Database
Flashback Drop
Flashback Table
Flashback Versions Query
Flashback Transaction Query
NoCOUG 2005, Daniel T. Liu
9
Flashback Database
• 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.
NoCOUG 2005, Daniel T. Liu
10
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.
• Flashback Database is faster than
traditional point-in-time recovery.
NoCOUG 2005, Daniel T. Liu
11
Flashback Database
DATABASE
LGWR
RVWR
Redo
Log
Files
Flashback
Database
Logs
NoCOUG 2005, Daniel T. Liu
12
Flashback Database
• The list below shows all the background
processes for ‘grid’ instance.
$ ps -ef
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle
oracle
| grep grid
25124
1
25116
1
25169
1
25112
1
25110
1
25108
1
25114
1
25118
1
25120
1
25122
1
25106
1
0
0
0
0
0
0
0
0
0
0
0
16:32:05
16:32:04
16:32:22
16:32:04
16:32:04
16:32:04
16:32:04
16:32:04
16:32:04
16:32:04
16:32:04
?
?
?
?
?
?
?
?
?
?
?
NoCOUG 2005, Daniel T. Liu
0:00
0:00
0:00
0:00
0:00
0:00
0:00
0:00
0:00
0:00
0:00
ora_s000_grid
ora_reco_grid
ora_rvwr_grid
ora_ckpt_grid
ora_lgwr_grid
ora_dbw0_grid
ora_smon_grid
ora_cjq0_grid
ora_rbal_grid
ora_d000_grid
ora_pmon_grid
13
Enabling Flashback Database
• Make sure the database is in archive mode.
• Configure the recovery area by setting the two
parameters:
– DB_RECOVERY_FILE_DEST
– DB_RECOVERY_FILE_DEST_SIZE
• Open the database in MOUNT EXCLUSIVE
mode and turn on the flashback feature:
SQL> STARTUP MOUNT EXCLUSIVE;
SQL> ALTER DATABASE FLASHBACK ON;
• Set the Flashback Database retention target:
– DB_FLASHBACK_RETENTION_TARGET
NoCOUG 2005, Daniel T. Liu
14
Disabling Flashback Database
• Disabling Flashback Database
SQL> ALTER DATABASE FLASHBACK OFF;
• Determine if Flashback Database is enabled
SQL> select flashback_on
2 from
v$database;
FLASHBACK_ON
-----------YES
NoCOUG 2005, Daniel T. Liu
15
Monitoring Flashback Database
• Monitoring Flashback Database
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
-------------------- -------------- ---------- ---------- -----------------------Feb 22 2004 01:05:14
147456
2719744
92160
0
Feb 22 2004 00:05:09
3891200
5857280
2537984
252788736
Feb 21 2004 23:05:04
7979008
13615104
3385344
254877696
Feb 21 2004 22:05:00
14893056
19857408
17463296
255737856
Feb 21 2004 21:04:55
4210688
6422528
2598912
254361600
Feb 21 2004 20:04:51
4333568
8962048
2775552
256475136
Feb 21 2004 19:04:46
4431872
7028736
2804736
258588672
Feb 21 2004 18:04:41
4202496
8511488
2635264
260726784
Feb 21 2004 17:04:37
4030464
6938624
2546688
263012352
Feb 21 2004 16:04:32
4005888
7479296
2512384
265420800
Feb 21 2004 15:04:27
3874816
6864896
2471936
267927552
Feb 21 2004 14:04:23
4153344
7028736
2578944
270532608
Feb 21 2004 13:04:18
3825664
7675904
2497536
273113088
13 rows selected.
NoCOUG 2005, Daniel T. Liu
16
Flashback Database Retention
• 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
• The default value for flashback retention
time is 1440 minutes.
NoCOUG 2005, Daniel T. Liu
17
Flashback Database
• 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
SQL> FLASHBACK DATABASE TO TIMESTAMP
(SYSDATE – 5/24);
SQL> FLASHBACK DATABASE TO SCN 76239;
SQL> ALTER DATABASE RESETLOGS;
NoCOUG 2005, Daniel T. Liu
18
Flashback Drop
• Prior to Oracle 10g, a DROP command
permanently removed objects from the
database.
SQL> DROP TABLE CUSTOMERS;
• 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.
NoCOUG 2005, Daniel T. Liu
19
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
FLASHBACK TABLE command.
– There is no room in the tablespace for new rows
or updates to existing rows.
– The tablespace needs to be extended.
NoCOUG 2005, Daniel T. Liu
20
Recycle Bin
• 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 systemwide objects
NoCOUG 2005, Daniel T. Liu
21
Example 1: Dropping an Object
SQL> create table test (col_a varchar(4));
Table created.
SQL> select object_name, original_name,
2
type, createtime, droptime
3 from
user_recyclebin;
no rows selected
SQL> drop table test;
Table dropped.
NoCOUG 2005, Daniel T. Liu
22
Example 1: Dropping an Object
SQL> select object_name, original_name,
2
type, createtime, droptime
3 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
NoCOUG 2005, Daniel T. Liu
23
Example 1: Dropping an Object
SQL> create table test (col_b varchar(4));
Table created.
SQL> select object_name, original_name,
2
type, createtime, droptime
3 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> drop table test;
Table dropped.
NoCOUG 2005, Daniel T. Liu
24
Example 1: Dropping an Object
SQL> select object_name, original_name,
2
type, createtime, droptime
3 from
user_recyclebin;
OBJECT_NAME
ORIGINAL_NAME
TYPE
CREATETIME
DROPTIME
------------------------------ -------------------- ------------------- -----------------BIN$0+ktoVChEmXgNAAADiUEHQ==$0 TEST
TABLE
2004-02-21:19:07:33 2004-0221:19:08:17
BIN$0+ktoVCgEmXgNAAADiUEHQ==$0 TEST
TABLE
2004-02-21:19:04:03 2004-0221:19:04:41
NoCOUG 2005, Daniel T. Liu
25
Example 1: Dropping an Object
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
NoCOUG 2005, Daniel T. Liu
26
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.
NoCOUG 2005, Daniel T. Liu
27
Example 3: Dropping a Table Permanently
• This statement
permanently:
removes
the
table
SQL> drop table test purge;
Table dropped.
• This statement removes the table in the
recycle bin:
SQL> purge table "BIN$0+ktoVChEmXgNAAADiUEHQ==$0";
Table purged.
NoCOUG 2005, Daniel T. Liu
28
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;
NoCOUG 2005, Daniel T. Liu
29
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.
NoCOUG 2005, Daniel T. Liu
30
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.
NoCOUG 2005, Daniel T. Liu
31
Flashback Table
• 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)
NoCOUG 2005, Daniel T. Liu
32
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.
• The default value for UNDO_RETENTION
is 900 seconds (15 minutes).
NoCOUG 2005, Daniel T. Liu
33
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.
• To create an undo tablespace with the
RETENTION GUARANTEE option, issue the
following command:
SQL> CREATE UNDO TABLEAPCE undo_tbs
DATAFIEL ‘/u02/oradata/grid/undo_tbs01.dbf’ SIZE 1 G
RETENTION GUARANTEE;
NoCOUG 2005, Daniel T. Liu
34
Flashback Table Privileges
• You must have the FLASHBACK TABLE
or FLASHBACK ANY TABLE system
privilege to use the Flashback Table feature.
NoCOUG 2005, Daniel T. Liu
35
Example 1: Flashback Table using SCN
• Table row movement must be enabled to
flashback a table:
SQL> ALTER TABLE billing ENABLE
ROW MOVEMENT;
• This statement brings a table ‘billing’ back
to a certain SCN number;
SQL> FLASHBACK TABLE billing TO
SCN 76230;
NoCOUG 2005, Daniel T. Liu
36
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’);
NoCOUG 2005, Daniel T. Liu
37
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.
NoCOUG 2005, Daniel T. Liu
38
Flashback Versions Query
• You can use the VERSIONS BETWEEN
clauses to retrieve all historical data related
to a row.
• The Flashback Versions Query feature
retrieves all committed occurrences of the
row.
• The row history data is stored in the undo
tablespace.
NoCOUG 2005, Daniel T. Liu
39
Flashback Versions Query
• The undo_retention initialization parameter
specifies how long the database will keep the
amount of committed undo information.
• If a new transaction needs 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.
• You can set the undo tablespace option to
RETENTION GUARANTEE to retain all
row histories.
NoCOUG 2005, Daniel T. Liu
40
Example 1
• 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
NoCOUG 2005, Daniel T. Liu
41
Example 2
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.
NoCOUG 2005, Daniel T. Liu
42
Example 2
SQL> update emp set salary = 3000
where name = 'DANIEL';
1 row updated.
SQL> commit;
Commit complete.
NoCOUG 2005, Daniel T. Liu
43
Example 2
SQL> select * from emp;
NAME
SALARY
---------- ---------DANIEL
3000
SQL> select * from
emp
versions between scn minvalue and maxvalue;
NAME
SALARY
---------- ---------DANIEL
3000
DANIEL
2000
NoCOUG 2005, Daniel T. Liu
44
Flashback Transaction Query
• It 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.
NoCOUG 2005, Daniel T. Liu
45
Flashback Transaction Query
• You can retrieve the transaction history from
flashback_transaction_query view:
Name
Null?
------------------------------------ -------XID
START_SCN
START_TIMESTAMP
COMMIT_SCN
COMMIT_TIMESTAMP
LOGON_USER
UNDO_CHANGE#
OPERATION
TABLE_NAME
TABLE_OWNER
ROW_ID
UNDO_SQL
NoCOUG 2005, Daniel T. Liu
Type
-------------RAW(8)
NUMBER
DATE
NUMBER
DATE
VARCHAR2(30)
NUMBER
VARCHAR2(32)
VARCHAR2(256)
VARCHAR2(32)
VARCHAR2(19)
VARCHAR2(4000)
46
Example
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
flashback_transaction_query
3 where xid = '0003000E00000FE2';
NoCOUG 2005, Daniel T. Liu
47
Example
SQL> select xid, start_scn, start_timestamp,
2
table_name, undo_sql
3 from
flashback_transaction_query
4 where xid = '0009001F000000B2‘;
XID
START_SCN
START_TIMESTAMP
TABLE_NAME
UNDO_SQL
---------------- -----------------------------------------------------------------------------------------------------------------0009001F000000B2
714980
Feb 21 2004 23:30:31
EMP
update "ORACLE"."EMP" set "SALARY" = ‘2000' where ROWID =
'AAAMWJAAEAAAAFsAAA';
NoCOUG 2005, Daniel T. Liu
48
Summary - Flashback
•
•
•
•
•
Flashback Database
Flashback Drop
Flashback Table
Flashback Versions Query
Flashback Transaction Query
NoCOUG 2005, Daniel T. Liu
49
Enhanced RMAN Features
• Automated File Creation During
Recovery
• Simplified Recovery Through Resetlogs
• Change-Aware Incremental Backups
• Automated Disk-Based Backup and
Recovery
• RMAN Database Dropping and
Deregistration
NoCOUG 2005, Daniel T. Liu
50
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.
NoCOUG 2005, Daniel T. Liu
51
Automated File Creation During Recovery
data file
#1
data file
#2
Missing
data file
#3
RMAN
Control fle
NoCOUG 2005, Daniel T. Liu
Archive log files
52
Simplified Recovery Through Resetlogs
• After performing an incomplete (Point-intime) recovery, you need to open the
database with RESETLOGS option:
SQL> alter database open resetlogs;
NoCOUG 2005, Daniel T. Liu
53
Simplified Recovery Through 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;
NoCOUG 2005, Daniel T. Liu
54
Simplified Recovery Through Resetlogs
• 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 to 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.
NoCOUG 2005, Daniel T. Liu
55
Simplified Recovery Through Resetlogs
• Benefits of Simplified Recovery Through
Resetlogs
– 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 no need to change any backup
scripts as there are no changes to the
recovery commands to take advantage of
this functionality.
NoCOUG 2005, Daniel T. Liu
56
Simplified Recovery Through Resetlogs
• Benefits of Simplified Recovery Through
Resetlogs
– 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.
NoCOUG 2005, Daniel T. Liu
57
Simplified Recovery Through Resetlogs
• How does it work?
– 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
NoCOUG 2005, Daniel T. Liu
58
Simplified Recovery Through Resetlogs
• 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.
NoCOUG 2005, Daniel T. Liu
59
Simplified Recovery Through Resetlogs
• 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#
– RESETLOGS_TIME.
NoCOUG 2005, Daniel T. Liu
60
Simplified Recovery Through Resetlogs
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
NoCOUG 2005, Daniel T. Liu
61
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.
NoCOUG 2005, Daniel T. Liu
62
Change-Aware Incremental Backups
• 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.
NoCOUG 2005, Daniel T. Liu
63
Change-Aware Incremental Backups
• Here are the steps RMAN will perform to
do an incremental backup:
– Read the Block Change Tracking File to
determine which blocks in the data file need
to be read.
– Only scan the changed blocks in the data file
and then back them up.
NoCOUG 2005, Daniel T. Liu
64
Change-Aware Incremental Backups
RMAN
Step 2
Step 1
Block Change Tracking File
Data File
NoCOUG 2005, Daniel T. Liu
65
Change-Aware Incremental Backups
• How big is the Block Change Tracking File?
– 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.
NoCOUG 2005, Daniel T. Liu
66
Change-Aware Incremental Backups
• How big is the Block Change Tracking
File?
– 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.
NoCOUG 2005, Daniel T. Liu
67
Change-Aware Incremental 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
NoCOUG 2005, Daniel T. Liu
68
Change-Aware Incremental Backups
• 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.
NoCOUG 2005, Daniel T. Liu
69
Change-Aware Incremental Backups
• Enabling 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;
NoCOUG 2005, Daniel T. Liu
70
Change-Aware Incremental Backups
• Disabling Block Change Tracking
SQL> alter database disable block
change tracking;
• Monitoring the status of Block Change
Tracking:
SQL> select file, status, bytes
2
from
v$block_change_tracking;
STATUS
FILE
----------- ---------------------------ENABLED
/dba/backup/01_mf_yzmrr7.chg
NoCOUG 2005, Daniel T. Liu
BYTES
--------------10,000,000
71
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
NoCOUG 2005, Daniel T. Liu
72
Automated Disk-Based Backup and
Recovery
• 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
NoCOUG 2005, Daniel T. Liu
73
Automated Disk-Based Backup and
Recovery
• 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
NoCOUG 2005, Daniel T. Liu
74
Automated Disk-Based Backup and
Recovery
• Set the disk limit for recovery area to 100
GB:
SQL> ALTER SYSTEM SET
DB_RECOVERY_FILE_DEST_SIZE = 100G;
• Set the recovery area destination:
SQL> ALTER SYSTEM SET
DB_RECOVERY_FILE_DEST = ‘/dba/backup/’;
NoCOUG 2005, Daniel T. Liu
75
Automated Disk-Based Backup and
Recovery
• Alter a Recovery Area
SQL> ALTER SYSTEM SET
DB_RECOVERY_FILE_DEST_SIZE = 200G;
• Disabling a Recovery Area
SQL> ALTER SYSTEM SET
DB_RECOVERY_FILE_DEST = ‘ ’;
NoCOUG 2005, Daniel T. Liu
76
Automated Disk-Based Backup and
Recovery
• 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.
NoCOUG 2005, Daniel T. Liu
77
Automated Disk-Based Backup and
Recovery
• Recovery Area Space Management
– 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.
NoCOUG 2005, Daniel T. Liu
78
Automated Disk-Based Backup and
Recovery
• New RMAN command for Recovery Area
RMAN> BACKUP RECOVERY AREA;
RMAN> BACKUP RECOVERY FILES;
NoCOUG 2005, Daniel T. Liu
79
Automated Disk-Based Backup and
Recovery
• Oracle 10g has a new dynamic performance
view for monitoring the recovery area:
SQL> desc v$recovery_file_dest
Name
Null?
------------------------------- -------NAME
SPACE_LIMIT
SPACE_USED
SPACE_RECLAIMABLE
NUMBER_OF_FILES
NoCOUG 2005, Daniel T. Liu
Type
----------VARCHAR2(513)
NUMBER
NUMBER
NUMBER
NUMBER
80
Automated Disk-Based Backup and
Recovery
• Example:
SQL> select * from v$recovery_file_dest;
NAME
SPACE_LIMIT
SPACE_USED
SPACE_RECLAIMABLE NUMBER_OF_FILES
------------ ---------------------------------------- ---------------/dba/backup
2147483648
458219520
0
49
NoCOUG 2005, Daniel T. Liu
81
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;
NoCOUG 2005, Daniel T. Liu
82
RMAN Database Dropping and
Deregistration
• 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;
NoCOUG 2005, Daniel T. Liu
83
Summary - RMAN Features
• Automated File Creation During
Recovery
• Simplified Recovery Through Resetlogs
• Change-Aware Incremental Backups
• Automated Disk-Based Backup and
Recovery
• RMAN Database Dropping and
Deregistration
NoCOUG 2005, Daniel T. Liu
84
Summary
•
•
•
•
•
•
•
Oracle Database 10g
Grid Computing
Challenges For Backup and Recovery
Different Types of Database Errors
Solutions for Backup and Recovery
Oracle 10g Extended Flashback Features
Oracle 10g Enhanced RMAN Features
NoCOUG 2005, Daniel T. Liu
85
Questions
&
Answers
NoCOUG 2005, Daniel T. Liu
86
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.
NoCOUG 2005, Daniel T. Liu
87
Thanks For Coming !!
Daniel Liu Contact Information
Phone: (714) 701-3346
Email: [email protected]
Email: [email protected]
Company Web Site:
http://www.firstam.com
NoCOUG 2005, Daniel T. Liu
88