d

海量数据库被drop/truncate表的不完全恢复

在实际情况中，数据库经常会由于工作人员的误操作造成一些表被删除或者截断，
如果数据库的数据量小是很方便做时间点恢复的，但如果数据量非常大。或者10T
的数据量，那是不可能再安排一台拥有这么大的存储空间的测试机让你做恢复的，

这是在去年，一电信的核心业务数据库一张非常重要的表被删除掉的情况，由于
当时并没有足够的空间在做全库的时间点恢复，因此采用了以下方法来,全过程
如下：

该库中一张表被用户使用drop table p操作,而p表位于proc表空间中。
这张表大约有2GB的数据量，并且用户尝试了重建该表，
由于种种原因无法补齐数据，只能从备份进行恢复，而该库有200GB的数据量，
而这张表的数据在4小时之后必须使用，

恢复过程：在异机配置dp 环境，并安装oracle

rman target /

Recovery Manager: Release 9.2.0.4.0 - Production

Copyright (c) 1995, 2002, Oracle Corporation. All rights reserved.

connected to target database (not started)

这里也可以先使用dummy来nomount;

RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 236000356 bytes

Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes

把spfile 还原

RMAN> restore spfile to '/u03/arch/spfileorcl.ora'
2> from '/u03/arch/backuparch_6398607364';

Starting restore at 28-NOV-07

using channel ORA_DISK_1
channel ORA_DISK_1: autobackup found: /u03/arch/backuparch_6398607364
channel ORA_DISK_1: SPFILE restore from autobackup complete
Finished restore at 28-NOV-07

RMAN> shutdown immediate;

使用刚刚恢复的spfile启动到nomount,主要为了让rman识别controlfile信息。
RMAN> startup nomount;

connected to target database (not started)
Oracle instance started

Total System Global Area 236000356 bytes

Fixed Size 451684 bytes
Variable Size 201326592 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes

RMAN> restore controlfile from '/u03/arch/backuparch_6398607364';

Starting restore at 28-NOV-07

using channel ORA_DISK_1
channel ORA_DISK_1: restoring controlfile
channel ORA_DISK_1: restore complete
replicating controlfile
input filename=/u01/app/oracle/oradata/orcl/control01.ctl
output filename=/u01/app/oracle/oradata/orcl/control02.ctl
output filename=/u01/app/oracle/oradata/orcl/control03.ctl
Finished restore at 28-NOV-07

RMAN> alter database mount;

database mounted
RMAN> crosscheck backup;

using target database controlfile instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=12 devtype=DISK
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u03/arch/arch_ORCL6398607033 recid=3 stamp=639860704
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u03/arch/backuparch_6398607364 recid=4 stamp=639860738
crosschecked backup piece: found to be 'AVAILABLE'
backup piece handle=/u03/arch/arch_ORCL6398608945 recid=5 stamp=639860895
Crosschecked 3 objects

还原system和undo两个必须的表空间，当然，由于p表在proc表空间中，因此也需要还原
RMAN> restore tablespace system,UNDOTBS1,proc;

Starting restore at 29-NOV-07

using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/orcl/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/orcl/undotbs01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/orcl/proc.dbf
channel ORA_DISK_1: restored backup piece 1
piece handle=/u03/arch/backuparch_6398607364 tag=TAG20071128T190535 params=NULL
channel ORA_DISK_1: restore complete
Finished restore at 29-NOV-07

打开sqlplus，

列中datafile信息：
SQL> /

FILE# NAME
---------- --------------------------------------------------
1 /u01/app/oracle/oradata/orcl/system01.dbf
2 /u01/app/oracle/oradata/orcl/undotbs01.dbf
3 /u01/app/oracle/oradata/orcl/drsys01.dbf
4 /u01/app/oracle/oradata/orcl/users01.dbf
5 /u01/app/oracle/oradata/orcl/xdb01.dbf
6 /u01/app/oracle/oradata/orcl/proc.dbf
7 /u01/app/oracle/oradata/orcl/t_data.dbf
8 /u01/app/oracle/oradata/orcl/t_data2.dbf
.....
.....

SQL> alter database datafile 3,4,5,7,8..... offline drop;

Database altered.

RMAN> recover database until time "to_DATE('2007-11-28 19:10:00', 'yyyy-mm-dd hh24:mi:ss')"
2> skip tablespace DRSYS,USERS,XDB,T_DATA,TEMP;

Starting recover at 28-NOV-07
using channel ORA_DISK_1

starting media recovery
media recovery failed
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/28/2007 21:26:20
ORA-00283: recovery session canceled due to errors
RMAN-11003: failure during parse/execution of SQL statement: alter database recover if needed
start until time 'NOV 28 2007 19:10:00'
ORA-00283: recovery session canceled due to errors
ORA-00313: open failed for members of log group 3 of thread 1
ORA-00312: online log 3 thread 1: '/u01/app/oracle/oradata/orcl/redo03.log'
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

RMAN>

因为没有redo日志，因此在恢复过程中会报错误，不过没有关系。
接下来退出rman

进入sqlplus

alter database backup controlfile to trace;

生成重建控制文件脚本。

并使用以下方式重建：

STARTUP NOMOUNT
CREATE CONTROLFILE REUSE DATABASE "ORCL" RESETLOGS FORCE LOGGING ARCHIVELOG
-- SET STANDBY TO MAXIMIZE PERFORMANCE
MAXLOGFILES 5
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 226
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/orcl/redo01.log' SIZE 10M,
GROUP 2 '/u01/app/oracle/oradata/orcl/redo02.log' SIZE 10M,
GROUP 3 '/u01/app/oracle/oradata/orcl/redo03.log' SIZE 10M
-- STANDBY LOGFILE
DATAFILE
'/u01/app/oracle/oradata/orcl/system01.dbf',
'/u01/app/oracle/oradata/orcl/undotbs01.dbf',
'/u01/app/oracle/oradata/orcl/drsys01.dbf',
'/u01/app/oracle/oradata/orcl/users01.dbf',
'/u01/app/oracle/oradata/orcl/xdb01.dbf',
'/u01/app/oracle/oradata/orcl/proc.dbf',
'/u01/app/oracle/oradata/orcl/t_data.dbf',
'/u01/app/oracle/oradata/orcl/t_data2.dbf'
....
....
CHARACTER SET ZHS16GBK
;

alter database mount;

alter database open resetlogs;

注意这里必须以resetlogs打开数据库。
SQL> select count(*) from p;

COUNT(*)
---------------
47104001

P表已经恢复，exp出来这张表，并导入到生产库。
这里只花了前后不到2个小时的时间就完成了恢复。

from metalink

Recover from Missing Datafile that is Never Backed Up (RMAN-06026)

Problem Description
-------------------

Full restore via RMAN of a database when a datafile is missing and never
backed up results in the following errors:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure during compilation of command
RMAN-03013: command type: restore
RMAN-03002: failure during compilation of command
RMAN-03013: command type: IRESTORE
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 2 found to restore

Apparently file# 2 is never backed up.

Solution Description
--------------------

In the following examples database recovery is done before the database is
opened. Of course you can adjust the scripts in order to open the database
as soon as possible and then recover the datafile(s) as needed.

A. The controlfiles do not have to be restored
-----------------------------------------------
Make sure the database is mounted.
In all cases you need the name of the missing datafile. Because the
controlfile is up-to-date you can retrieve this information with the
following query:
select name from v$datafile where file#=2;
=> /u02/oradata/target/users_target01.dbf

1. The missing datafile is the only datafile that needs recovery.
In this case you do not have to restore anything.
Archivelogs are restored automatically by RMAN as they are needed for the
recover command.

run {
allocate channel d1 type disk;
sql "alter database create datafile
''/u02/oradata/target/users_target01.dbf'' " ;
recover database;
sql "alter database open";
release channel d1;
}

2. If other datafiles need recovery too, you have two choices:

- Specify each datafile that must be restored:

run {
allocate channel d1 type disk;
sql "alter database create datafile
''/u02/oradata/target/users_target01.dbf'' " ;
restore datafile '/u02/oradata/target/sys_target01.dbf';
.....
recover database;
sql "alter database open";
release channel d1;
}

You can get a list of datafiles by querying v$datafile.
Instead of specifying the datafile by name, you can also specify it by
number:
restore datafile 1;

- Restore the complete database until just before the missing datafile
was created.

run {
allocate channel d1 type disk;
sql "alter database create datafile
''/u02/oradata/target/users_target01.dbf'' " ;
restore database
until scn 118247 ;
recover database;
sql "alter database open";
release channel d1;
}

An apropriate SCN can be found by querying v$datafile:
select CREATION_CHANGE# from v$datafile where file#=2;
=> 118248
Lower this value by 1 or more.

Instead of 'until scn 118247' you can use one of the following:
until logseq 662 thread 1;
until time "to_date('Dec 15 2000 10:10:00','Mon DD YYYY HH24:MI:SS')";
You can find the the logseq or time by querying the alert<SID>.log.
Choose a time which lies BEFORE the creation time of the missing datafile,
or supply a logseq which was completed before the creation time.

3. If you want to recover the database to a time in the past, but after the
creation of the missing datafile (incomplete recovery / PITR=point in time
recovery), again you have two choices:

- Specify which datafiles must be restored.
Add an until clause to the recover command.

run {
allocate channel d1 type disk;
sql "alter database create datafile
''/u02/oradata/target/users_target01.dbf'' " ;
restore datafile '/u02/oradata/target/sys_target01.dbf';
recover database
until scn 338325;
# until logseq 684 thread 1;
# until time "to_date('Dec 15 2000 15:12:00','Mon DD YYYY HH24:MI:SS')";
sql "alter database open" resetlogs;
release channel d1;
}

- Restore all datafiles until just before the missing datafile was created.
Add an until clause to the recover command as well. Note that that the
until clause for the restore command is different from the until clause
for the recover command!
Open the database with resetlogs.

run {
allocate channel d1 type disk;
sql "alter database create datafile
''/u02/oradata/target/users_target01.dbf'' " ;
restore database
until scn 118247 ;
# until logseq 662 thread 1;
# until time "to_date('Dec 15 2000 10:10:00','Mon DD YYYY HH24:MI:SS')";
recover database
until scn 338325;
# until logseq 684 thread 1;
# until time "to_date('Dec 15 2000 15:12:00','Mon DD YYYY HH24:MI:SS')";
sql "alter database open" resetlogs;
release channel d1;
}

When you opened the database successfully with resetlogs, you must create
a new database incarnation record in the recovery catalog:
RMAN> reset database;
And of course it is necessary to make a full (cold) backup immediately!

B. The controlfiles must be restored too
-----------------------------------------
Recovering a database using a backup controlfile and having a missing
datafile that is never backed up, breaks down into several steps. RMAN is
not a suitable tool for every step.
Because you use an old controlfile the name of the missing datafile cannot
be queried from v$datafile. Because the datafile was never backed up
RMAN's repository has no knowledge of this datafile either.

1. Restore the controlfile - the database must be started NOMOUNT:

run {
allocate channel d1 type disk;
restore
controlfile to '/u02/oradata/target/control01.ctl';
replicate
controlfile from '/u02/oradata/target/control01.ctl';
release channel d1;
}

2. Restore datafiles to the moment BEFORE the missing datafile was created.
Restore archivelogs from some time before the oldest datafile up to the
moment to which you want to recover the database.
The possibilities are discussed above, for instance:

run {
allocate channel d1 type disk;
restore database
until logseq 5 thread 1;
restore archivelog;
# until logseq 9 thread 1;
release channel d1;
}

3. Use svrmgrl or sqlplus (8i only) to recover the database:
SVRMGRL> recover database using backup controlfile

Supply the names of the archives until you get the following error:
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 2: '/u02/oradata/target/users_target01.dbf'

Retrieve the filename that is added to the controlfile from v$datafile:
SVRMGR> select name from v$datafile where file#=5;
=> UNNAMED0002

4. Now you have all the information to recreate the missing datafile.
SVRMGR> alter database create datafile 'UNNAMED0002'
2> as '/u02/oradata/target/users_target01.dbf';

5. Resume recovering the database:
SVRMGRL> recover database using backup controlfile
Supply the names of the archives up to the moment you want to stop or
until you recovery is finished.
Open the database (noresetlogs/resetlogs)

When you opened the database successfully with resetlogs you must create
a new database incarnation record in the recovery catalog:
RMAN> reset database;
And of course it is necessary to make a full (cold) backup immediately!

Explanation
-----------

Automatic full restore is not possible when a datafile is missing and never
backed up. The controlfile contains all the information needed to recreate
the missing datafile. RMAN does not automatically recreate a missing datafile.
You must either recreate it manually before invoking RMAN, or add some
sql-statements to the rman script.

from metalink

RMAN: I/O Slaves and Memory Usage

TIP: Click help for a detailed explanation of this page.
Bookmark Go to End

Subject: RMAN: I/O Slaves and Memory Usage
Doc ID: Note:73354.1 Type: BULLETIN
Last Revision Date: 28-MAY-2007 Status: PUBLISHED

PURPOSE

The pupose of this note is to show how RMAN makes use of memory buffers
for backup/restore operations, and also how the use of i/o slaves can
affect this.

SCOPE & APPLICATION

This note is intended for DBAs and Support Personnel.

RMAN I/O Slaves and Memory Usage
================================

Contents:

1.0 How Does RMAN make use of memory buffers?
2.0 Size of Input/Output Buffers
3.0 Why Use I/O Slaves?
4.0 Configuring I/O Slaves

1.0 How Does RMAN make use of memory buffers?
=============================================

For each backup/restore operation, every server session (ie, RMAN channel)
allocates

a. 4 input buffers for every disk file
b. 4 output buffers for every backup piece

memory(input) = #buffers * #files * buffersize
= 4 * #files * buffersize

#files = total number of files concurrently open

To reduce the amount of memory used by RMAN set - MAXOPENFILES = <n>
EG - Before maxopenfiles
4*100(files)*8192*64
After maxopenfiles = 4
4*4(files)*8192*64

This can be illustrated by the following:

RMAN> run {
allocate channel c1 type 'SBT_TAPE';
backup datafile 1,2;
}

+-----+ file 1
|+-----+ input buffers
||+-----+
|||+-----+
|||| | output buffers
+||| |
+|| | +-----+
+| | +-----------+ |+-----+ +------+
+-----+ | server | ||+-----+ | o__o |
+-----------+ |||+-----+ +------+
|||| |
+-----+ +||| |
|+-----+ +|| |
||+-----+ +| |
|||+-----+ +-----+
|||| | file 2
+||| | input buffers
+|| |
+| |
+-----+

The server process reads data from the disk file into one of the input buffers.
A given buffer is dedicated to a file whilst a server process is operating on
that file. When one buffer fills up, the server process writes to one of the
other three. The buffers are used in a circular fashion.

The input buffers will contain blocks that do not need to be backed up, as well
as those that do.

A 'memory copy' routine is used to copy the required data from an input to an
output buffer. This is where block corruption is checked (ie, validate header,
compute checksums if enabled).

2.0 Size of Input/Output Buffers
================================

a. input buffers
----------------

NOTE : DB_FILE_DIRECT_IO_COUNT is not available in Oracle9i onwards.
In Oracle9i, it is replaced by a hidden _DB_FILE_DIRECT_IO_COUNT which
governs the size of direct I/Os in BYTES (not blocks). The default is
1Mb butwill be sized down if the max_io_size of the system is smaller.

The input buffer size is:
buffersize = db_block_size * db_file_direct_io_count

As there are 4 input buffers, the total input buffer memory use per channel is:
memory(input) = #buffers * #files * buffersize
= 4 * #files * buffersize

For example, if 2 channels are used, and each of these channels backs up 3
files, then for each channel

memory(input) = 4 * 3 * db_block_size * db_file_direct_io_count

b. output buffers
-----------------

For disk channels, the output buffer size is:
buffersize = db_block_size * db_file_direct_io_count

For SBT_TAPE channels, the output buffer size in Oracle8/8i is o/s dependant. (On Solaris,
this defaults to 64k) On 9i it defaults to 256k for all platforms. The BLKSIZE argument to 'allocate channel...' can be
used to override the default value.

As there are 4 output buffers,
memory(output) = #buffers * buffersize
= 4 * buffersize

c. Allocation of Memory
-----------------------

This memory is allocated from the channel server process PGA, unless i/o slaves
are used. I/O slave memory is allocated from the SGA in order for the memory to
be shared between the I/O slave and the channel server process. In this case,
Oracle recommends the 'large pool' feature is used, i.e. Set the "init.ora"
parameter to:

LARGE_POOL_SIZE = <nnn>

where <nnn> is the size of the large pool, calculated from the above.

If the I/O slave cannot acquire the required memory from the SGA, then an
ORA-04031 error is asserted (see "alert.log"), and the operation continues
synchronously by allocating memory from the channel server's PGA.

3.0 Why Use I/O Slaves?
=======================

For optimal performance during backup/restore operations, the goal should
be to keep the tape streaming i.e. continually moving. Stopping and starting
tapes are expensive operations. Additionally, potential tape stretching will
lower the life span of the tape.

I/O slaves can be used to provide such a performance enhancement by simulating
asynchronous I/O. There are two types of I/O slaves; disk slaves and
tape slaves.

By default, all I/O to tape is synchronous. This means that the channel server
process is blocked from doing any work while waiting for a tape to complete a
write. Tape i/o slaves allow the channel server process to continue to fill and
process buffers whilst the tape write is completing.

It is also important to quickly fill the input buffers with data. On platforms
that do not support asynchronous I/O, the channel server process can be
blocked on a file read, thus preventing it from processing the buffers.
Disk I/O slaves can be used to asynchronously read from files,
thus enabling channel server process to continue to process the buffers.

This is especially important during incremental backups, or backups of 'empty'
files, where the number of modified buffers is sufficiently low that the tape
is writing faster than the output buffers are being filled.

4.0 Configuring I/O Slaves
==========================

a. Disk Slaves
--------------

For Oracle 8.0, set the "init.ora" parameter

BACKUP_DISK_IO_SLAVES = <n>

where <n> is the number of disk i/o slaves to start.

Oracle recommends that no more than 4 disk slaves are started. In this case,
extra channels should be considered.

For Oracle 8i/9i, set the "init.ora" parameter

DBWR_IO_SLAVES > 0

This causes 4 disk i/o slaves to be started.

Note that every channel server process doing a backup/restore will be assigned
this number of disk i/o slaves.

b. Tape Slaves
--------------

Set the "init.ora" parameter

BACKUP_TAPE_IO_SLAVES = true

This causes one tape I/O slave to be assigned to each channel server process.

In 8i/9i, if the DUPLEX option is specified, then tape I/O slaves must be enabled.
In this case, for DUPLCEX=<n>, there are <n> tape slaves per channel. These N slaves
all operate on the same four output buffers. Consequently, a buffer is not freed
up until all <n> slaves have finished writing to tape.

c. init.ora
-----------

Each I/O slave is an Oracle server process. The "init.ora" parameters'
processes and sessions need to be set accordingly.

How to Calculate the Memory Required by RMAN in Large Pool

from metalink

Contrary to the rman documentation at 9i, the amount of memory required by RMAN to be set aside
in LARGE POOL when asynchronous io is simulated with the use of slaves is NOT 16Mb per channel.

For a disk backup:

Each channel is allocated 4*1Mb (output buffers) plus 16*1Mb (input buffers)

For a tape backup:

Each channel is allocated 4* <tape blksize> plus 16*1Mb (input buffers)

Additionally, when the controlfile is included in the backup the memory allocated jumps by a
further 16 Mb (for the channel doing the controlfile backup) to 32Mb plus the output buffers
(tape or disk).

Bug 4513611 (still with Development) has been raised to clarify the documentation and
confirm if this is expected behaviour.

In the meantime, to cater for all circumstances, large pool should be set as follows (bearing in
mind that the controlfile can only be backed up by one channel) :

For disk channels

LARGE_POOL_SIZE = (No:channels * (16 + 4)) +16 Mb

For tape channels:

LARGE_POOL_SIZE = (No:channels * (16 + 4(<tape blksize>)) +16 Mb

-d

Problem Description
-------------------

Full restore via RMAN of a database when a datafile is missing and never
backed up results in the following errors:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure during compilation of command
RMAN-03013: command type: restore
RMAN-03002: failure during compilation of command
RMAN-03013: command type: IRESTORE
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 2 found to restore

Apparently file# 2 is never backed up.

Solution Description
--------------------

In the following examples database recovery is done before the database is
opened. Of course you can adjust the scripts in order to open the database
as soon as possible and then recover the datafile(s) as needed.

A. The controlfiles do not have to be restored
-----------------------------------------------
Make sure the database is mounted.
In all cases you need the name of the missing datafile. Because the
controlfile is up-to-date you can retrieve this information with the
following query:
select name from v$datafile where file#=2;
=> /u02/oradata/target/users_target01.dbf

1. The missing datafile is the only datafile that needs recovery.
In this case you do not have to restore anything.
Archivelogs are restored automatically by RMAN as they are needed for the
recover command.

run {
allocate channel d1 type disk;
sql "alter database create datafile
''/u02/oradata/target/users_target01.dbf'' " ;
recover database;
sql "alter database open";
release channel d1;
}

2. If other datafiles need recovery too, you have two choices:

- Specify each datafile that must be restored:

run {
allocate channel d1 type disk;
sql "alter database create datafile
''/u02/oradata/target/users_target01.dbf'' " ;
restore datafile '/u02/oradata/target/sys_target01.dbf';
.....
recover database;
sql "alter database open";
release channel d1;
}

You can get a list of datafiles by querying v$datafile.
Instead of specifying the datafile by name, you can also specify it by
number:
restore datafile 1;

- Restore the complete database until just before the missing datafile
was created.

run {
allocate channel d1 type disk;
sql "alter database create datafile
''/u02/oradata/target/users_target01.dbf'' " ;
restore database
until scn 118247 ;
recover database;
sql "alter database open";
release channel d1;
}

An apropriate SCN can be found by querying v$datafile:
select CREATION_CHANGE# from v$datafile where file#=2;
=> 118248
Lower this value by 1 or more.

Instead of 'until scn 118247' you can use one of the following:
until logseq 662 thread 1;
until time "to_date('Dec 15 2000 10:10:00','Mon DD YYYY HH24:MI:SS')";
You can find the the logseq or time by querying the alert<SID>.log.
Choose a time which lies BEFORE the creation time of the missing datafile,
or supply a logseq which was completed before the creation time.

3. If you want to recover the database to a time in the past, but after the
creation of the missing datafile (incomplete recovery / PITR=point in time
recovery), again you have two choices:

- Specify which datafiles must be restored.
Add an until clause to the recover command.

run {
allocate channel d1 type disk;
sql "alter database create datafile
''/u02/oradata/target/users_target01.dbf'' " ;
restore datafile '/u02/oradata/target/sys_target01.dbf';
recover database
until scn 338325;
# until logseq 684 thread 1;
# until time "to_date('Dec 15 2000 15:12:00','Mon DD YYYY HH24:MI:SS')";
sql "alter database open" resetlogs;
release channel d1;
}

- Restore all datafiles until just before the missing datafile was created.
Add an until clause to the recover command as well. Note that that the
until clause for the restore command is different from the until clause
for the recover command!
Open the database with resetlogs.

run {
allocate channel d1 type disk;
sql "alter database create datafile
''/u02/oradata/target/users_target01.dbf'' " ;
restore database
until scn 118247 ;
# until logseq 662 thread 1;
# until time "to_date('Dec 15 2000 10:10:00','Mon DD YYYY HH24:MI:SS')";
recover database
until scn 338325;
# until logseq 684 thread 1;
# until time "to_date('Dec 15 2000 15:12:00','Mon DD YYYY HH24:MI:SS')";
sql "alter database open" resetlogs;
release channel d1;
}

When you opened the database successfully with resetlogs, you must create
a new database incarnation record in the recovery catalog:
RMAN> reset database;
And of course it is necessary to make a full (cold) backup immediately!

B. The controlfiles must be restored too
-----------------------------------------
Recovering a database using a backup controlfile and having a missing
datafile that is never backed up, breaks down into several steps. RMAN is
not a suitable tool for every step.
Because you use an old controlfile the name of the missing datafile cannot
be queried from v$datafile. Because the datafile was never backed up
RMAN's repository has no knowledge of this datafile either.

1. Restore the controlfile - the database must be started NOMOUNT:

run {
allocate channel d1 type disk;
restore
controlfile to '/u02/oradata/target/control01.ctl';
replicate
controlfile from '/u02/oradata/target/control01.ctl';
release channel d1;
}

2. Restore datafiles to the moment BEFORE the missing datafile was created.
Restore archivelogs from some time before the oldest datafile up to the
moment to which you want to recover the database.
The possibilities are discussed above, for instance:

run {
allocate channel d1 type disk;
restore database
until logseq 5 thread 1;
restore archivelog;
# until logseq 9 thread 1;
release channel d1;
}

3. Use svrmgrl or sqlplus (8i only) to recover the database:
SVRMGRL> recover database using backup controlfile

Supply the names of the archives until you get the following error:
ORA-01244: unnamed datafile(s) added to controlfile by media recovery
ORA-01110: data file 2: '/u02/oradata/target/users_target01.dbf'

Retrieve the filename that is added to the controlfile from v$datafile:
SVRMGR> select name from v$datafile where file#=5;
=> UNNAMED0002

4. Now you have all the information to recreate the missing datafile.
SVRMGR> alter database create datafile 'UNNAMED0002'
2> as '/u02/oradata/target/users_target01.dbf';

5. Resume recovering the database:
SVRMGRL> recover database using backup controlfile
Supply the names of the archives up to the moment you want to stop or
until you recovery is finished.
Open the database (noresetlogs/resetlogs)

When you opened the database successfully with resetlogs you must create
a new database incarnation record in the recovery catalog:
RMAN> reset database;
And of course it is necessary to make a full (cold) backup immediately!

Explanation
-----------

Automatic full restore is not possible when a datafile is missing and never
backed up. The controlfile contains all the information needed to recreate
the missing datafile. RMAN does not automatically recreate a missing datafile.
You must either recreate it manually before invoking RMAN, or add some
sql-statements to the rman script.

from metalink

PURPOSE
-------

This article discusses the Oracle 10g new feature called 'block change tracking'
and how it helps accelerate RMAN incremental backups.

SCOPE & APPLICATION
-------------------

This article is intended for DBAs and Support Engineers.
Fast Incremental Backups using the Block Change Tracking feature is only available in Enterprise Edition.
http://download-uk.oracle.com/docs/cd/B19306_01/license.102/b14199/editions.htm#CJACGHEB

RMAN 10G FAST INCREMENTAL BACKUPS
----------------------------------

An incremental backup backups up only those blocks that have changed since a previous backup.

Incremental backups have provided the following benefits:

1. Reduced disk space usage (smaller backup-pieces generated)

2. Somewhat faster backup completion times – Even though the number
of blocks eventually written to the backup-piece were less, Oracle
still had to read all the blocks to determine if they changed or not.

Oracle 10g offers a new feature, called block change tracking.
This feature allows for faster creation of incremental backups.

Changes to the database blocks are now tracked using a ‘tracking file’.
When the block change tracking is enabled, Oracle logs changes to the blocks
in this tracking file. During the incremental backup, RMAN reads the tracking
file to determine changed blocks. This obviates the need to read each and
every block in a datafile and thus results in faster incremental backups.

== Enabling block change tracking ==

Block change tracking can be enabled by using the ALTER DATABASE command.
For example:

SQL> alter database enable block change tracking
using file ‘/u01/oradata/orcl/change_tracking.f’;

RESUSE clause can be specified if the file already exists.

SQL> alter database enable block change tracking
using file ‘/u01/oradata/orcl/change_tracking.f’ resuse;

Alternatively, if you have the DB_CREATE_FILE_DEST parameter set (for Oracle Managed Files),
then you can simply issue:

SQL> alter database enable block change tracking;

In this case, Oracle crates an Oracle Managed File (OMF) in the directory specified by
DB_CREATE_FILE_DEST to keep track of block changes.

If DB_CREATE_FILE_DEST is not set and you do not specify a file name,
following error will result:

ORA-19773: must specify change tracking file name

== Disabling block change tracking ==

Block change tracking can be disabled by using the ALTER DATABASE command.
For example:

SQL> alter database disable block change tracking.

This command also removes the change tracking file.

== Determining if block change tracking is enabled ==

Information about block change tracking and the tracking file is stored in the
controlfile and can be accessed using the V$BLOCK_CHANGE_TRACKING view:

SQL> desc v$block_change_tracking

Name Null? Type
------------------- -------------- ------------------
STATUS VARCHAR2(10)
FILENAME VARCHAR2(513)
BYTES NUMBER

This view always contains one record. If the STATUS is ENABLED, then the FILENAME
contains the name of the file being used for tracking and BYTES contains the size
of the file. If the STATUS is DISABLED, the other two columns are null.

The V$BACKUP_DATAFILE view contains a column called USED_CHANGE_TRACKING.
A value of YES for this column for an incremenat backup level > 0 means that RMAN used
the tracking file to speed up the incremental backup. This can help you determine how effective
the the tracking file in minimizing the I/O activity during an incremental backup. The following
query can be used:

select file#,
avg(datafile_blocks),
avg(blocks_read),
avg(blocks_read/datafile_blocks) * 100 as “% read for backup”
from v$backup_datafile
where incremental_level > 0
and used_change_tracking = ‘YES’
group by file#
order by file#;

== Other information ==

o Whenever block change tracking is enabled or disabled, a message is logged into
alert.log to indicate the creation or removal of the tracking file.

o Tracking file can be renamed using ‘alter database rename file’ comamnd.

o Tracking file is a binary file.

o RMAN does not support the backup and recovery of the tracking file.

ARCHIVELOG模式下的恢复

完全恢复:

1.从备份中还原所有数据库数据文件.
2.还原所有备份的归档的重做日志.
3.加载数据库STARTUP MOUNT;
4.恢复数据库RECOVER DATABASE.
5.应用归档日志中的重做日志,在提示符下输入AUTO
6.ALTER DATABASE OPEN;

恢复表空间和数据文件

表空间恢复:
1.使表空间脱机(ALTER TABELSPACE OFFLINE);
2.还原与要恢复的表空间的相关联的所有数据文件.
3.联机恢复表空间(RECOVER TABLESPACE);
4.完成恢复后使表空间联机(ALTER TABLESPAC ONLINE);

数据文件恢复:

1.使数据文件脱机(ALTER DATABASE DATAFILE 'FILE_NAME' OFFLINE);
2.还原所有要恢复的数据文件.
3.联机恢复数据文件(RECOVER DATABASE);
4.完成恢复后联机数据文件(ALTER DATABASE DATAFILE 'FILE_NAME' ONLINE);

时间点恢复:

执行将数据库恢复到某一时间点:
1.从一个备份中恢复所有数据库数据文件,这个备份在要恢复数据库的时间点之前结束.
2.使用如recover database until time 'yyyy-mm-dd hh24:mi:si'命令并且应用所需的重做日志,恢复选定时间点的数据库.
3.打开数据库.

基于SCN的恢复.
1.从一个备份中恢复所有数据库数据文件,这个备份在要恢复数据库的时间点之前结束.
2.使用如recover database until change 'scn' 的命令并且应用所需的重做日志,恢复选定SCN号的数据库.
3.打开数据库.
当然,第二步可以使用recover database until cancel命令并且应用所需的重做日志,恢复选定时间点的数据库.
应用了最后一个归档的重做日志后,可以执行cancel命令来结束日志应用.

RMAN与IO

使用dbwr_io_slaves参数可以配置磁盘IO从属,这个参数的值可以任意.作用是将"脏"缓冲区(即写满的缓冲区)的内容从缓冲存储区写入磁盘时唤醒其他的dbwr从属来执行磁盘写操作.
如果该参数大于0,RMAN会自动在每个通道中切换使用四个io从属来帮助将数据块读入rman内存缓冲区.
这个参数只有在OS平台不支持本地异步io或者禁止DATABASE使用异步IO时才起作用.如果支持异步,那么些这个值可以不用去理会.
如果backup_tape_io_slaves参数被设置为true.RMAN会为每个磁带通道进程分配一个io从属来帮助在磁带位置上执行写操作,与磁盘io从属不同的是, backup_tape_io_slaves
参数只对rman磁带备份起作用.由于磁盘高备不存在本地异步IO,所以这个参数设置为true是非常好的,有利用提供rman传输性能.