分类：oracle

通过RMAN的duplicate命令将目标数据库在另一主机上复制一个副本数据库时报以下错误信息：

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/24/2015 18:28:32
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01013: user requested

目标数据库与复制的副本数据库在不同主机上，但有相同的目录结构，复制命令如下:

[oracle@oracle11g admin]$ rman target sys/zzh_2046@test auxiliary sys/oracle@dup catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 24 17:26:51 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2168949517)
connected to recovery catalog database
connected to auxiliary database: DUP (not mounted)

RMAN> duplicate target database to dup nofilenamecheck;

Starting Duplicate Db at 2015-03-24 17:27:06
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=35 devtype=DISK

contents of Memory Script:
{
   set until scn  1009334;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/test/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/test/undotbs01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/test/sysaux01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/test/users01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/test/example01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/test/tspitr01.dbf";
   restore
   check readonly
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 2015-03-24 17:27:07
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/test/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/test/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/test/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/test/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/test/tspitr01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test875200761_721
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test875200761_721 tag=TAG20150324T151920
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 2015-03-24 17:28:12
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUP" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/u01/app/oracle/oradata/test/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u01/app/oracle/oradata/test/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u01/app/oracle/oradata/test/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/test/system01.dbf'
 CHARACTER SET ZHS16GBK


contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=875208502 filename=/u01/app/oracle/oradata/test/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=875208502 filename=/u01/app/oracle/oradata/test/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=875208502 filename=/u01/app/oracle/oradata/test/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=875208502 filename=/u01/app/oracle/oradata/test/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=875208502 filename=/u01/app/oracle/oradata/test/tspitr01.dbf

contents of Memory Script:
{
   set until scn  1009334;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 2015-03-24 17:28:14
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=64
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test875200848_731
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test875200848_731 tag=TAG20150324T152048
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_64_870806981.dbf thread=1 sequence=64
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_64_870806981.dbf recid=1 stamp=875208502
media recovery complete, elapsed time: 00:00:01
Finished recover at 2015-03-24 17:28:17

contents of Memory Script:
{
   shutdown clone;
   startup clone nomount ;
}
executing Memory Script

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 03/24/2015 18:28:32
RMAN-03015: error occurred in stored script Memory Script
RMAN-06136: ORACLE error from auxiliary database: ORA-01013: user requested cancel of current operation

这里出错是因为启动辅助实例后没有断开连接

[oracle@jingyong1 dbs]$ export ORACLE_SID=dup
[oracle@jingyong1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 16:19:29 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272624 bytes
Variable Size              58721488 bytes
Database Buffers          104857600 bytes
Redo Buffers                2920448 bytes

断开连接

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

重新执行复制命令:

[oracle@oracle11g admin]$ rman target sys/zzh_2046@test auxiliary sys/oracle@dup catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 24 18:58:33 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2168949517)
connected to recovery catalog database
connected to auxiliary database: DUP (not mounted)

RMAN> duplicate target database to dup nofilenamecheck;

Starting Duplicate Db at 2015-03-24 18:58:47
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=36 devtype=DISK

contents of Memory Script:
{
   set until scn  1009334;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/test/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/test/undotbs01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/test/sysaux01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/test/users01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/test/example01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/test/tspitr01.dbf";
   restore
   check readonly
   clone database
   ;
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 2015-03-24 18:58:47
using channel ORA_AUX_DISK_1

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/test/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/test/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/test/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/test/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/test/tspitr01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test875200761_721
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test875200761_721 tag=TAG20150324T151920
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 2015-03-24 18:59:53
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUP" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/u01/app/oracle/oradata/test/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u01/app/oracle/oradata/test/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u01/app/oracle/oradata/test/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/test/system01.dbf'
 CHARACTER SET ZHS16GBK


contents of Memory Script:
{
   switch clone datafile all;
}
executing Memory Script

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=875214004 filename=/u01/app/oracle/oradata/test/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=875214004 filename=/u01/app/oracle/oradata/test/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=875214004 filename=/u01/app/oracle/oradata/test/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=875214004 filename=/u01/app/oracle/oradata/test/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=875214004 filename=/u01/app/oracle/oradata/test/tspitr01.dbf

contents of Memory Script:
{
   set until scn  1009334;
   recover
   clone database
    delete archivelog
   ;
}
executing Memory Script

executing command: SET until clause

Starting recover at 2015-03-24 18:59:56
using channel ORA_AUX_DISK_1

starting media recovery

channel ORA_AUX_DISK_1: starting archive log restore to default destination
channel ORA_AUX_DISK_1: restoring archive log
archive log thread=1 sequence=64
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test875200848_731
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test875200848_731 tag=TAG20150324T152048
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_64_870806981.dbf thread=1 sequence=64
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/product/10.2.0/db/dbs/arch1_64_870806981.dbf recid=1 stamp=875214007
media recovery complete, elapsed time: 00:00:01
Finished recover at 2015-03-24 19:00:00

contents of Memory Script:
{
   shutdown clone;
   startup clone nomount ;
}
executing Memory Script

database dismounted
Oracle instance shut down

connected to auxiliary database (not started)
Oracle instance started

Total System Global Area     167772160 bytes

Fixed Size                     1272624 bytes
Variable Size                 58721488 bytes
Database Buffers             104857600 bytes
Redo Buffers                   2920448 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "DUP" RESETLOGS ARCHIVELOG
  MAXLOGFILES     16
  MAXLOGMEMBERS      3
  MAXDATAFILES      100
  MAXINSTANCES     8
  MAXLOGHISTORY      292
 LOGFILE
  GROUP  1 ( '/u01/app/oracle/oradata/test/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u01/app/oracle/oradata/test/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u01/app/oracle/oradata/test/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/test/system01.dbf'
 CHARACTER SET ZHS16GBK


contents of Memory Script:
{
   set newname for tempfile  1 to
 "/u01/app/oracle/oradata/test/temp01.dbf";
   switch clone tempfile all;
   catalog clone datafilecopy  "/u01/app/oracle/oradata/test/undotbs01.dbf";
   catalog clone datafilecopy  "/u01/app/oracle/oradata/test/sysaux01.dbf";
   catalog clone datafilecopy  "/u01/app/oracle/oradata/test/users01.dbf";
   catalog clone datafilecopy  "/u01/app/oracle/oradata/test/example01.dbf";
   catalog clone datafilecopy  "/u01/app/oracle/oradata/test/tspitr01.dbf";
   switch clone datafile all;
}
executing Memory Script

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/oradata/test/temp01.dbf in control file

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/test/undotbs01.dbf recid=1 stamp=875214019

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/test/sysaux01.dbf recid=2 stamp=875214019

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/test/users01.dbf recid=3 stamp=875214019

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/test/example01.dbf recid=4 stamp=875214019

cataloged datafile copy
datafile copy filename=/u01/app/oracle/oradata/test/tspitr01.dbf recid=5 stamp=875214019

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=875214019 filename=/u01/app/oracle/oradata/test/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=875214019 filename=/u01/app/oracle/oradata/test/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=875214019 filename=/u01/app/oracle/oradata/test/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=875214019 filename=/u01/app/oracle/oradata/test/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=875214019 filename=/u01/app/oracle/oradata/test/tspitr01.dbf

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 2015-03-24 19:00:36

执行成功

总结：出现这个错误的原因是因为，启动辅助实例的会话没有断开连接，RMAN在执行duplicate
命令时不能有任何其它会话连接到辅助实例，否则就是会执行失败。

存储脚本是命令文件的一种替方法用来管理频繁使用的一系列RMAN命令。存储脚本相比命令文件来说它的优点就是对于任何连接到目标数据库和恢复目录的RMAN客户端都能够使用它。而命令文件只有能够访问它的RMAN客户端能够使用。

存储脚本可以是全局或者局部脚本。一个局存储脚本与创建该脚本时RMAN所连接的目标数据库相关，只能当连接到该目标数据库时才能执行存储脚本。一个全局的存储脚本能在恢复目录中所注册的所有数据库中执行。注意执行存储脚本，即使是全局存储脚本，也必须连接到恢复目录和目标实例才能执行。

创建存储脚本
确保RMAN连接到正确的目标数据库和恢复目录，然后执行create script命令来创建局部存储脚本。例如:

create script full_backup
{
backup as backupset database plus archivelog;
delete obsolete;
}


RMAN> create script full_backup
2> {
3> backup as backupset database plus archivelog;
4> delete obsolete;
5> }

created script full_backup

执行创建命令如果没有输出错误信息，那么该存储脚本full_backup就创建成功并存储在恢复目录中。

下面创建一个全局存储脚本，如下所示:

create global script global_full_backup
{
backup as backupset database plus archivelog;
delete obsolete;
}


RMAN> create global script global_full_backup
2> {
3> backup as backupset database plus archivelog;
4> delete obsolete;
5> }

created global script global_full_backup

还可以提供了comment来描述全局存储脚本:

create global script global_full_backup
comment 'use only with archivelog mode database'
{
backup as backupset database plus archivelog;
delete obsolete;
}

RMAN> create global script global_full_backup
2> comment 'use only with archivelog mode database'
3> {
4> backup as backupset database plus archivelog;
5> delete obsolete;
6> }

created global script global_full_backup

还可以读取文本文件来创建局部或全局存储脚本:

RMAN> create script test_script from file '/u01/app/oracle/test_script_file.txt';

script commands will be loaded from file /u01/app/oracle/test_script_file.txt
created script test_script

RMAN> create global script global_test_script from file '/u01/app/oracle/test_script_file.txt';

script commands will be loaded from file /u01/app/oracle/test_script_file.txt
created global script global_test_script

文本文件内宾3必须以{开始，再接RUN块中的一系列有效的命令，最后以}结束。

执行存储脚本
为了执行存储脚本，连接到目标数据库和恢复目录，使用execute script命令，execute script命令要求使用RUN块：

RMAN> run
2> {
3> execute script full_backup;
4> }

executing script: full_backup


Starting backup at 18-MAR-15
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=145 devtype=DISK
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=1 recid=9 stamp=871547115
input archive log thread=1 sequence=2 recid=10 stamp=872692777
input archive log thread=1 sequence=3 recid=11 stamp=873390010
input archive log thread=1 sequence=4 recid=12 stamp=873477689
input archive log thread=1 sequence=5 recid=13 stamp=873557205
input archive log thread=1 sequence=6 recid=14 stamp=873628154
input archive log thread=1 sequence=7 recid=15 stamp=873881476
input archive log thread=1 sequence=8 recid=16 stamp=873969596
input archive log thread=1 sequence=9 recid=17 stamp=874234871
input archive log thread=1 sequence=10 recid=18 stamp=874315436
input archive log thread=1 sequence=11 recid=19 stamp=874486364
input archive log thread=1 sequence=12 recid=20 stamp=874574023
input archive log thread=1 sequence=13 recid=21 stamp=874579545
input archive log thread=1 sequence=14 recid=22 stamp=874657043
input archive log thread=1 sequence=15 recid=23 stamp=874663015
channel ORA_DISK_1: starting piece 1 at 18-MAR-15
channel ORA_DISK_1: finished piece 1 at 18-MAR-15
piece handle=/u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_annnn_TAG20150318T095657_bjkpvd26_.bkp tag=TAG20150318T095657 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:27
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=44 recid=6 stamp=870948358
input archive log thread=1 sequence=45 recid=7 stamp=870948359
input archive log thread=1 sequence=46 recid=8 stamp=870948359
channel ORA_DISK_1: starting piece 1 at 18-MAR-15
channel ORA_DISK_1: finished piece 1 at 18-MAR-15
piece handle=/u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_annnn_TAG20150318T095657_bjkpw6cl_.bkp tag=TAG20150318T095657 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 18-MAR-15

Starting backup at 18-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/jy/system01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/jy/undotbs01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/jy/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/jy/example01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/jy/users01.dbf
channel ORA_DISK_1: starting piece 1 at 18-MAR-15
channel ORA_DISK_1: finished piece 1 at 18-MAR-15
piece handle=/u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_nnndf_TAG20150318T095727_bjkpw9xz_.bkp tag=TAG20150318T095727 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 18-MAR-15

Starting backup at 18-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=16 recid=24 stamp=874663125
channel ORA_DISK_1: starting piece 1 at 18-MAR-15
channel ORA_DISK_1: finished piece 1 at 18-MAR-15
piece handle=/u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_annnn_TAG20150318T095845_bjkpyqdc_.bkp tag=TAG20150318T095845 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 18-MAR-15

Starting Control File and SPFILE Autobackup at 18-MAR-15
piece handle=/u01/app/oracle/flash_recovery_area/JY/autobackup/2015_03_18/o1_mf_s_874663128_bjkpytxr_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-MAR-15

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log          3769   06-FEB-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_02_06/o1_mf_1_44_bf88d537_.arc
Archive Log          3770   06-FEB-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_02_06/o1_mf_1_45_bf88d73o_.arc
Archive Log          3771   06-FEB-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_02_06/o1_mf_1_46_bf88d7cv_.arc
Archive Log          3772   13-FEB-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_02_13/o1_mf_1_1_bftk375y_.arc
Archive Log          3773   26-FEB-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_02_26/o1_mf_1_2_bgxhx2ww_.arc
Archive Log          3774   03-MAR-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_03/o1_mf_1_3_bhbvorcp_.arc
Archive Log          3775   04-MAR-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_04/o1_mf_1_4_bhfk9mdr_.arc
Archive Log          3776   05-MAR-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_05/o1_mf_1_5_bhhyxwnp_.arc
Archive Log          3777   06-MAR-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_06/o1_mf_1_6_bhl47mgr_.arc
Archive Log          3778   09-MAR-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_09/o1_mf_1_7_bhsvmzby_.arc
Archive Log          3779   10-MAR-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_10/o1_mf_1_8_bhwkos6m_.arc
Archive Log          3780   13-MAR-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_13/o1_mf_1_9_bj4nqm2j_.arc
Archive Log          3781   14-MAR-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_14/o1_mf_1_10_bj73f7y4_.arc
Archive Log          3782   16-MAR-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_16/o1_mf_1_11_bjdbbrvm_.arc
Archive Log          3783   17-MAR-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_17/o1_mf_1_12_bjgzxz92_.arc
Archive Log          3784   17-MAR-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_17/o1_mf_1_13_bjh5bqyo_.arc
Archive Log          3785   18-MAR-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_18/o1_mf_1_14_bjkk0h4p_.arc
Backup Set           3786   06-FEB-15
  Backup Piece       3788   06-FEB-15          /u01/app/oracle/flash_recovery_area/JY/autobackup/2015_02_06/o1_mf_s_870945982_bf861zo6_.bkp
Datafile Copy        3794   03-MAR-15          /u01/app/oracle/users01.dbf
Backup Set           3787   06-FEB-15
  Backup Piece       3789   06-FEB-15          /u01/app/oracle/flash_recovery_area/JY/autobackup/2015_02_06/o1_mf_s_870948381_bf88dyom_.bkp
Archive Log          3806   18-MAR-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_18/o1_mf_1_15_bjkpv6py_.arc
Backup Set           3813   18-MAR-15
  Backup Piece       3817   18-MAR-15          /u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_annnn_TAG20150318T095657_bjkpvd26_.bkp
Backup Set           3814   18-MAR-15
  Backup Piece       3818   18-MAR-15          /u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_annnn_TAG20150318T095657_bjkpw6cl_.bkp

Do you really want to delete the above objects (enter YES or NO)? YES
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_02_06/o1_mf_1_44_bf88d537_.arc recid=6 stamp=870948358
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_02_06/o1_mf_1_45_bf88d73o_.arc recid=7 stamp=870948359
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_02_06/o1_mf_1_46_bf88d7cv_.arc recid=8 stamp=870948359
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_02_13/o1_mf_1_1_bftk375y_.arc recid=9 stamp=871547115
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_02_26/o1_mf_1_2_bgxhx2ww_.arc recid=10 stamp=872692777
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_03/o1_mf_1_3_bhbvorcp_.arc recid=11 stamp=873390010
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_04/o1_mf_1_4_bhfk9mdr_.arc recid=12 stamp=873477689
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_05/o1_mf_1_5_bhhyxwnp_.arc recid=13 stamp=873557205
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_06/o1_mf_1_6_bhl47mgr_.arc recid=14 stamp=873628154
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_09/o1_mf_1_7_bhsvmzby_.arc recid=15 stamp=873881476
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_10/o1_mf_1_8_bhwkos6m_.arc recid=16 stamp=873969596
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_13/o1_mf_1_9_bj4nqm2j_.arc recid=17 stamp=874234871
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_14/o1_mf_1_10_bj73f7y4_.arc recid=18 stamp=874315436
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_16/o1_mf_1_11_bjdbbrvm_.arc recid=19 stamp=874486364
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_17/o1_mf_1_12_bjgzxz92_.arc recid=20 stamp=874574023
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_17/o1_mf_1_13_bjh5bqyo_.arc recid=21 stamp=874579545
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_18/o1_mf_1_14_bjkk0h4p_.arc recid=22 stamp=874657043
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/JY/autobackup/2015_02_06/o1_mf_s_870945982_bf861zo6_.bkp recid=4 stamp=870948272
deleted datafile copy
datafile copy filename=/u01/app/oracle/users01.dbf recid=2 stamp=873392067
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/JY/autobackup/2015_02_06/o1_mf_s_870948381_bf88dyom_.bkp recid=5 stamp=870948382
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_18/o1_mf_1_15_bjkpv6py_.arc recid=23 stamp=874663015
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_annnn_TAG20150318T095657_bjkpvd26_.bkp recid=6 stamp=874663020
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_annnn_TAG20150318T095657_bjkpw6cl_.bkp recid=7 stamp=874663046
Deleted 23 objects

上面的命令将调用指定名称的局部脚本full_script。如果没有找到局部脚本，但存在相同名称的全局脚本，RMAN将执行同名的全局存储脚本。如果存在同名的局部和全局脚本可以使用execute global script来控制只执行全局脚本。在恢复目录中没有名叫global_full_backup的局部脚本存在，那么执行下面两个命令的结果是一样的：

RMAN> run
2> {
3> execute global script global_full_backup;
4> }

executing global script: global_full_backup


Starting backup at 18-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=16 recid=24 stamp=874663125
input archive log thread=1 sequence=17 recid=25 stamp=874663571
channel ORA_DISK_1: starting piece 1 at 18-MAR-15
channel ORA_DISK_1: finished piece 1 at 18-MAR-15
piece handle=/u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_annnn_TAG20150318T100613_bjkqdppq_.bkp tag=TAG20150318T100613 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 18-MAR-15

Starting backup at 18-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/jy/system01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/jy/undotbs01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/jy/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/jy/example01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/jy/users01.dbf
channel ORA_DISK_1: starting piece 1 at 18-MAR-15
channel ORA_DISK_1: finished piece 1 at 18-MAR-15
piece handle=/u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_nnndf_TAG20150318T100615_bjkqds89_.bkp tag=TAG20150318T100615 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 18-MAR-15

Starting backup at 18-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=18 recid=26 stamp=874663651
channel ORA_DISK_1: starting piece 1 at 18-MAR-15
channel ORA_DISK_1: finished piece 1 at 18-MAR-15
piece handle=/u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_annnn_TAG20150318T100732_bjkqh6cv_.bkp tag=TAG20150318T100732 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 18-MAR-15

Starting Control File and SPFILE Autobackup at 18-MAR-15
piece handle=/u01/app/oracle/flash_recovery_area/JY/autobackup/2015_03_18/o1_mf_s_874663655_bjkqh9db_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-MAR-15

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log          3845   18-MAR-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_18/o1_mf_1_16_bjkpynv3_.arc
Backup Set           3846   18-MAR-15
  Backup Piece       3850   18-MAR-15          /u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_nnndf_TAG20150318T095727_bjkpw9xz_.bkp
Backup Set           3864   18-MAR-15
  Backup Piece       3870   18-MAR-15          /u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_annnn_TAG20150318T095845_bjkpyqdc_.bkp
Archive Log          3913   18-MAR-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_18/o1_mf_1_17_bjkqdmto_.arc
Backup Set           3880   18-MAR-15
  Backup Piece       3882   18-MAR-15          /u01/app/oracle/flash_recovery_area/JY/autobackup/2015_03_18/o1_mf_s_874663128_bjkpytxr_.bkp
Backup Set           3925   18-MAR-15
  Backup Piece       3928   18-MAR-15          /u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_annnn_TAG20150318T100613_bjkqdppq_.bkp

Do you really want to delete the above objects (enter YES or NO)? yes
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_18/o1_mf_1_16_bjkpynv3_.arc recid=24 stamp=874663125
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_nnndf_TAG20150318T095727_bjkpw9xz_.bkp recid=8 stamp=874663049
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_annnn_TAG20150318T095845_bjkpyqdc_.bkp recid=9 stamp=874663127
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_18/o1_mf_1_17_bjkqdmto_.arc recid=25 stamp=874663571
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/JY/autobackup/2015_03_18/o1_mf_s_874663128_bjkpytxr_.bkp recid=10 stamp=874663130
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_annnn_TAG20150318T100613_bjkqdppq_.bkp recid=11 stamp=874663574
Deleted 6 objects



RMAN> run
2> {
3> execute script global_full_backup;
4> }

executing global script: global_full_backup


Starting backup at 18-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=18 recid=26 stamp=874663651
input archive log thread=1 sequence=19 recid=27 stamp=874664008
channel ORA_DISK_1: starting piece 1 at 18-MAR-15
channel ORA_DISK_1: finished piece 1 at 18-MAR-15
piece handle=/u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_annnn_TAG20150318T101329_bjkqtbtn_.bkp tag=TAG20150318T101329 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 18-MAR-15

Starting backup at 18-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/jy/system01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/jy/undotbs01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/jy/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/jy/example01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/jy/users01.dbf
channel ORA_DISK_1: starting piece 1 at 18-MAR-15
channel ORA_DISK_1: finished piece 1 at 18-MAR-15
piece handle=/u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_nnndf_TAG20150318T101331_bjkqtdoh_.bkp tag=TAG20150318T101331 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:05
Finished backup at 18-MAR-15

Starting backup at 18-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=20 recid=28 stamp=874664078
channel ORA_DISK_1: starting piece 1 at 18-MAR-15
channel ORA_DISK_1: finished piece 1 at 18-MAR-15
piece handle=/u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_annnn_TAG20150318T101439_bjkqwjl4_.bkp tag=TAG20150318T101439 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 18-MAR-15

Starting Control File and SPFILE Autobackup at 18-MAR-15
piece handle=/u01/app/oracle/flash_recovery_area/JY/autobackup/2015_03_18/o1_mf_s_874664082_bjkqwmhg_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-MAR-15

RMAN retention policy will be applied to the command
RMAN retention policy is set to redundancy 1
using channel ORA_DISK_1
Deleting the following obsolete backups and copies:
Type                 Key    Completion Time    Filename/Handle
-------------------- ------ ------------------ --------------------
Archive Log          3944   18-MAR-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_18/o1_mf_1_18_bjkqh3ov_.arc
Backup Set           3945   18-MAR-15
  Backup Piece       3948   18-MAR-15          /u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_nnndf_TAG20150318T100615_bjkqds89_.bkp
Backup Set           3967   18-MAR-15
  Backup Piece       3973   18-MAR-15          /u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_annnn_TAG20150318T100732_bjkqh6cv_.bkp
Archive Log          4036   18-MAR-15          /u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_18/o1_mf_1_19_bjkqt8p5_.arc
Backup Set           3988   18-MAR-15
  Backup Piece       3990   18-MAR-15          /u01/app/oracle/flash_recovery_area/JY/autobackup/2015_03_18/o1_mf_s_874663655_bjkqh9db_.bkp
Backup Set           4053   18-MAR-15
  Backup Piece       4056   18-MAR-15          /u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_annnn_TAG20150318T101329_bjkqtbtn_.bkp

Do you really want to delete the above objects (enter YES or NO)? YES
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_18/o1_mf_1_18_bjkqh3ov_.arc recid=26 stamp=874663651
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_nnndf_TAG20150318T100615_bjkqds89_.bkp recid=12 stamp=874663576
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_annnn_TAG20150318T100732_bjkqh6cv_.bkp recid=13 stamp=874663654
deleted archive log
archive log filename=/u01/app/oracle/flash_recovery_area/JY/archivelog/2015_03_18/o1_mf_1_19_bjkqt8p5_.arc recid=27 stamp=874664008
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/JY/autobackup/2015_03_18/o1_mf_s_874663655_bjkqh9db_.bkp recid=14 stamp=874663657
deleted backup piece
backup piece handle=/u01/app/oracle/flash_recovery_area/JY/backupset/2015_03_18/o1_mf_annnn_TAG20150318T101329_bjkqtbtn_.bkp recid=15 stamp=874664010
Deleted 6 objects

在执行全局脚本只影响RMAN所连接到的目标数据库；为了跨多个数据库执行全局脚本，必须单独连接到每一个数据库并执行全局存储脚本。

在执行脚本时将会使用配置的自动通道来执行脚本。如果需要覆盖配置的自动通道可以在脚本中使用allocate channel命令来手动分配通道。注意因为使用的是RUN块，如果脚本中的一个RMAN命令失败，脚本中的后续命令将不会执行。

查看存储脚本内容
print script命令用来查看存储脚本或将脚本内容写入到新文件。使用RMAN连接到目标数据库和恢复目录。执行print script:

RMAN> print script full_backup;

printing stored script: full_backup
 {backup as backupset database plus archivelog;
delete obsolete;
}

显示全局脚本内容:
RMAN> print global script global_full_backup;

printing stored global script: global_full_backup
 {backup as backupset database plus archivelog;
delete obsolete;
}

为将脚本内容写入到新文件中，执行以下命令:

RMAN> print script full_backup to file '/u01/app/oracle/full_backup.txt';

script full_backup written to file /u01/app/oracle/full_backup.txt

将全局脚本内容写入文件:

RMAN> print global script global_full_backup to file '/u01/app/oracle/global_full_backup.txt';

global script global_full_backup written to file /u01/app/oracle/global_full_backup.txt

查看恢复目录中的存储脚本:list script names
使用list script names命令来显示恢复目录中的存储脚本名。这个命令会显示所有存储脚本名。

RMAN> list script names;

List of Stored Scripts in Recovery Catalog


    Scripts of Target Database JY

       Script Name
       Description
       -----------------------------------------------------------------------
       full_backup


    Global Scripts


       Script Name
       Description
       -----------------------------------------------------------------------
       global_full_backup
       use only with archivelog mode database

只显示全局存储脚本名:

RMAN> list global script names;

List of Stored Scripts in Recovery Catalog


    Global Scripts


       Script Name
       Description
       -----------------------------------------------------------------------
       global_full_backup
       use only with archivelog mode database

为了查看当前恢复目录中所有存储脚本的名称，包括在恢复目录中所有已经注册的目标数据库的全局和局部脚本，执行以下命令:

RMAN> list all script names;

List of Stored Scripts in Recovery Catalog


    Global Scripts


       Script Name
       Description
       -----------------------------------------------------------------------
       global_full_backup
       use only with archivelog mode database


    Scripts of Target Database JY

       Script Name
       Description
       -----------------------------------------------------------------------
       full_backup


    Scripts of Target Database TEST

       Script Name
       Description
       -----------------------------------------------------------------------
       test_full_backup

可以看到上面显示了两个目标数据库（jy,test)中的所有存储脚本

更新存储脚本
为了更新存储脚本，连接到目标数据库和恢复目录并使用replace script命令来执行更新。如果脚本不存在，那么RMAN将会创建一个新存储脚本。

下面的命令用来更新脚本full_backup的内容:
先来查看恢复目录中所有存储脚本名来检查是否存在full_backup脚本

RMAN> list all script names;

List of Stored Scripts in Recovery Catalog


    Global Scripts


       Script Name
       Description
       -----------------------------------------------------------------------
       global_full_backup
       use only with archivelog mode database


    Scripts of Target Database JY

       Script Name
       Description
       -----------------------------------------------------------------------
       full_backup


    Scripts of Target Database TEST

       Script Name
       Description
       -----------------------------------------------------------------------
       test_full_backup

查看脚本full_backup的内容

RMAN> print script full_backup;

printing stored script: full_backup
 {backup as backupset database plus archivelog;
delete obsolete;
}

更新脚本full_backup
RMAN> replace script full_backup
2> {
3> backup as backupset database plus archivelog;
4> }

replaced script full_backup

检查脚本full_backup是否已经被更新

RMAN> print script full_backup;

printing stored script: full_backup
 {backup as backupset database plus archivelog;
}

从脚本内容可知full_backup内容确实已经被更新.

下面来更新一个不存在的脚本full_backup_bak

RMAN> replace script full_backup_update
2> {
3> backup as backupset database plus archivelog;
4> }

replaced script full_backup_update

从下面的信息中可以看到当更新一个不存在的脚本，RMAN确实会创建这个脚本

RMAN> list all script names;

List of Stored Scripts in Recovery Catalog


    Global Scripts


       Script Name
       Description
       -----------------------------------------------------------------------
       global_full_backup
       use only with archivelog mode database


    Scripts of Target Database JY

       Script Name
       Description
       -----------------------------------------------------------------------
       full_backup

       full_backup_update


    Scripts of Target Database TEST

       Script Name
       Description
       -----------------------------------------------------------------------
       test_full_backup



RMAN> print script full_backup_update;

printing stored script: full_backup_update
 {backup as backupset database plus archivelog;
}

全局存储脚本可以使用replace global script命令来进行更新:
查看更新前global_full_backup脚本的内容

RMAN> print script global_full_backup;

printing stored global script: global_full_backup
 {backup as backupset database plus archivelog;
delete obsolete;
}

更新全局脚本global_full_backup

RMAN> replace global script global_full_backup
2> {
3> backup as backupset database plus archivelog;
4> }

replaced global script global_full_backup

显示更新后全局脚本global_full_backup的内容

RMAN> print global script global_full_backup;

printing stored global script: global_full_backup
 {backup as backupset database plus archivelog;
}

与create script命令一样，可以使用文件来更新局部或全局存储脚本。

更新前查看全局脚本global_full_backup的内容

RMAN> print global script global_full_backup;

printing stored global script: global_full_backup
 {backup as backupset database plus archivelog;
}

使用global_full_backup.txt文件来更新全局脚本global_full_backup

RMAN> replace global script global_full_backup from file '/u01/app/oracle/global_full_backup.txt';

script commands will be loaded from file /u01/app/oracle/global_full_backup.txt
replaced global script global_full_backup

查看更新后的脚本内容:

RMAN> print global script global_full_backup;

printing stored global script: global_full_backup
 {backup as backupset database plus archivelog;
delete obsolete;
}

更新前查看局部脚本full_backup的内容

RMAN> print script full_backup;

printing stored script: full_backup
 {backup as backupset database plus archivelog;
}

使用full_backup.txt文件来更新局部脚本full_backup

RMAN> replace script full_backup from file '/u01/app/oracle/full_backup.txt';

script commands will be loaded from file /u01/app/oracle/full_backup.txt
replaced script full_backup

查看更新后的脚本内容:

RMAN> print script full_backup;

printing stored script: full_backup
 {backup as backupset database plus archivelog;
delete obsolete;
}

删除存储脚本
为了从恢复目录中删除存储脚本，连接恢复目录和目标数据库后执行delete script命令:

RMAN> list script names;

List of Stored Scripts in Recovery Catalog


    Scripts of Target Database JY

       Script Name
       Description
       -----------------------------------------------------------------------
       full_backup

       full_backup_update


    Global Scripts


       Script Name
       Description
       -----------------------------------------------------------------------
       global_full_backup

为了从恢复目录中删除局部存储脚本full_backup

RMAN> delete script 'full_backup';

deleted script: full_backup

为了删除全局存储脚本global_full_backup，执行delete global script:

RMAN> delete global script 'global_full_backup';

deleted global script: global_full_backup

如果没有给delete script命令指定global参数，并且目标数据库中没有指定名称的存储脚本存在，RMAN将使用指定名称来查找全局存储脚本如果找到就将其删除。因此为了删除全局存储脚本也可以执行以下的命令:

RMAN> delete script 'global_full_backup';

deleted script: global_full_backup

对于delete script ‘global_full_backup’命令，RMAN会在连接到的目标数据库中查找脚本’global_full_backup’，如果没有找到，将会搜索名为’global_full_backup’的全局脚本，如果找到就将其删除。

启动RMAN客户端并执行存储脚本
为了运行RMAN客户端并执行存储在恢复目录中的存储脚本，可以使用script参数来启动RMAN客户端：

[oracle@oracle11g ~]$ rman target sys/zzh_2046@test catalog rman/rman@jy script 'global_full_backup'

Recovery Manager: Release 10.2.0.5.0 - Production on Wed Mar 18 11:22:04 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2168949517)
connected to recovery catalog database

executing global script: global_full_backup


Starting backup at 18-MAR-15
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=141 devtype=DISK
skipping archive log file /u02/1_49_870806981.dbf; already backed up 1 time(s)
skipping archive log file /u02/1_50_870806981.dbf; already backed up 1 time(s)
skipping archive log file /u02/1_51_870806981.dbf; already backed up 1 time(s)
skipping archive log file /u02/1_52_870806981.dbf; already backed up 1 time(s)
skipping archive log file /u02/1_53_870806981.dbf; already backed up 1 time(s)
skipping archive log file /u02/1_54_870806981.dbf; already backed up 1 time(s)
skipping archive log file /u02/1_55_870806981.dbf; already backed up 1 time(s)
skipping archive log file /u02/1_56_870806981.dbf; already backed up 1 time(s)
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=57 recid=64 stamp=874668125
channel ORA_DISK_1: starting piece 1 at 18-MAR-15
channel ORA_DISK_1: finished piece 1 at 18-MAR-15
piece handle=/u02/ora_test874668127_671 tag=TAG20150318T112206 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 18-MAR-15

Starting backup at 18-MAR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/u01/app/oracle/oradata/test/system01.dbf
input datafile fno=00003 name=/u01/app/oracle/oradata/test/sysaux01.dbf
input datafile fno=00005 name=/u01/app/oracle/oradata/test/example01.dbf
input datafile fno=00006 name=/u01/app/oracle/oradata/test/tspitr01.dbf
input datafile fno=00002 name=/u01/app/oracle/oradata/test/undotbs01.dbf
input datafile fno=00004 name=/u01/app/oracle/oradata/test/users01.dbf
channel ORA_DISK_1: starting piece 1 at 18-MAR-15
channel ORA_DISK_1: finished piece 1 at 18-MAR-15
piece handle=/u02/ora_test874668130_681 tag=TAG20150318T112209 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 18-MAR-15

Starting backup at 18-MAR-15
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archive log backupset
channel ORA_DISK_1: specifying archive log(s) in backup set
input archive log thread=1 sequence=58 recid=65 stamp=874668205
channel ORA_DISK_1: starting piece 1 at 18-MAR-15
channel ORA_DISK_1: finished piece 1 at 18-MAR-15
piece handle=/u02/ora_test874668206_691 tag=TAG20150318T112326 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
Finished backup at 18-MAR-15

Starting Control File and SPFILE Autobackup at 18-MAR-15
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_18/o1_mf_s_874668209_bjkvxl3b_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 18-MAR-15

Recovery Manager complete.

在启动RMAN客户端时必须要连接到恢复目录（存储脚本存储在恢复目录中）和目标数据库。

存储脚本命名限制
RMAN是如何解决脚本名称，尤其是在局部和全局脚本共享相同名称时存在的一些问题：
.RMAN允许但一般不要求将存储脚本名使用引号括起来。然而，存储脚本名以数字或RMAN的保留关键开头就需要将存储脚本名称使用引号括起来。对于存储脚本名称要考虑避免使用A-Z之外的字符或RMAN的保留关键字作为名称的开始字符。
.当在命令行中使用script参数来启动RMAN客户端，如果存在相同名称的局部和全局存储脚本，那么RMAN将总是执行局部脚本。
.对于execute script,delete script和print script命令，如果脚本名称作为一个参数被传递并且在连接到的目标数据库中没有该相同名称的脚本存在，RMAN将执行，删除或显示相同名称的全局脚本。例如，如果在恢复目录中存在一个名叫global_full_backup的全局存储脚本，但在目标数据库中没有名叫global_full_backup的局部存储脚本，下面的命令将会删除全局脚本:
DELETE SCRIPT global_full_backup;

在给oracle 10g搭建dg使用rman复制数据库时，在连接辅助实例时出错，错误信息如下所示:

[oracle@oracle11g admin]$ rman target sys/zzh_2046@test auxiliary sys/system@aux_test catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 17 10:50:22 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2168949517)
connected to recovery catalog database
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04006: error from auxiliary database: ORA-00600: internal error code, arguments: [15435], [SYS], [X$STANDARD], [], [], [], [], []
ORA-04031: unable to allocate 1040 bytes of shared memory ("shared pool","X$STANDARD","PL/SQL DIANA","PAR.C:parapt:Page")
RMAN-04015: error setting target database character set to ZHS16GBK

从上面的错误信息可以看到ORA-04031是因为不能给共享内存分配1040 bytes大小的内存。如是直接用rman连接辅助实例也出现错误，错误信息如下所示:

[oracle@jingyong1 ~]$ rman target sys/system@aux_test

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 17 10:51:28 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-00600: internal error code, arguments: [15435], [SYS], [X$STANDARD], [], [], [], [], []
ORA-04031: unable to allocate 1040 bytes of shared memory ("shared pool","X$STANDARD","PL/SQL DIANA","PAR.C:parapt:Page")
RMAN-04015: error setting target database character set to ZHS16GBK

在MOS上找到了ORA-4031 During Startup Nomount using RMAN without parameter file [ID 1176443.1]内容如下:

Applies to:
Oracle Database - Enterprise Edition - Version 11.2.0.1 and later
Information in this document applies to any platform.
***Checked for relevance on 08-May-2013***
Symptoms
RMAN startup nomount failed with ORA-4031

Customer was testing RMAN backup/restore in Exadata.
Customer firstly backup the database to tape and then remove all the datafiles, spfile, controlfiles for testing.
Then during the recover, customer connected RMAN with nocatalog and try to "startup nomount", then ORA-4031 occured.



==================== Log ========================
oracle@hkfop011db01:/home/oracle
$ export ORACLE_SID=TEST
oracle@test011db01:/home/oracle
$ rman target / nocatalog

Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jul 8 20:45:10 2010
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
connected to target database (not started)

RMAN> startup nomount

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/oracle/product/11.2.0/db_1/dbs/initTEST.ora'

starting Oracle instance without parameter file for retrieval of spfile
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of startup command at 07/08/2010 20:45:19
RMAN-04014: startup failed: ORA-04031: unable to allocate 111264 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","KEWS sesstat values")
Cause
RMAN has failed to start a dummy instance without pfile.
Default values used for the dummy instance are not enough to start the instance up.

This is reported in Bug 9680987 - RMAN CANNOT START DATABASE WITHOUT PARAMETER FILE

Solution
There are two possible solutions:
1- Create temporary init.ora file (/oracle/product/11.2.0/db_1/dbs/initTEST.ora) with the following parameters:

    db_name=
    large_pool_size=100m
    shared_pool_size=250m
    db_cache_size=10m

2- Set environment variable  ORA_RMAN_SGA_TARGET before executing rman. For example:

    $ export ORA_RMAN_SGA_TARGET=350

这篇文章给出了两种解决方法，一种是增加内存参数，一种是设置环境变量，适用版本是11.2.0.1及其以后版本，我这里是oracle 10.2.0.5,因为第二种方法简单，所以尝试使用设置环境变量的方法:

[oracle@jingyong1 ~]$ export ORA_RMAN_SGA_TARGET=350
[oracle@jingyong1 ~]$ rman target sys/system@aux_test

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 17 10:51:46 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-06003: ORACLE error from target database:
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 16 bytes of shared memory ("shared pool","select ks.inst_id,ksuxsins,k...","sql area","ub1[]: qkexrXformVal")

我这里的版本是10.2.0.5，使用第二种方法不行。只能采取第一种增加内存参数的方法。

[oracle@jingyong1 dbs]$ vi inittest.ora

db_name=test
db_unique_name=_test
control_files= /u01/app/oracle/auxiliary/control01.ctl
db_file_name_convert=(' /u01/app/oracle/oradata/test/',' /u01/app/oracle/auxiliary')
log_file_name_convert=(' /u01/app/oracle/oradata/test/',' /u01/app/oracle/auxiliary')
remote_login_passwordfile=exclusive
compatible = 10.2.0.5.0
db_block_size=8192
sga_target=160M
sga_max_size=160M
pga_aggregate_target=16M

SQL> shutdown immediate
ORA-01507: database not mounted


ORACLE instance shut down.
SQL> startup nomount
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272624 bytes
Variable Size              58721488 bytes
Database Buffers          104857600 bytes
Redo Buffers                2920448 bytes

[oracle@jingyong1 dbs]$ export ORACLE_SID=test
[oracle@jingyong1 dbs]$ rman target/

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 17 11:00:47 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: test (not mounted)

[oracle@oracle11g admin]$ rman target sys/zzh_2046@test auxiliary sys/system@aux_test catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 17 11:01:00 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2168949517)
connected to recovery catalog database
connected to auxiliary database: TEST (not mounted)

通过向参数文件中增加内存参数解决了这个故障。

nls_date_format参数用于设置日期显示格式，设置的方式有多种，不同的方式也会带来不同的结果。参数的设置是有优先级的，日期格式的参数设置也不例外。优先级如下(低到高)：初始化参数 < 系统环境变量 < 会话级(session)< 函数
下面来演示参数设置的优先级
1.用初始化参数来设置nls_date_format
检查是否设置了nls_date_format环境变量，从下面的结果可知并没有设置

[oracle@oracle11g ~]$ export nls_date_format
SQL>show parameter nls_date_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string

修改参数nls_date_format

SQL> alter system set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;

System altered.

SQL> shutdow immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area  327155712 bytes
Fixed Size                  1273516 bytes
Variable Size             138412372 bytes
Database Buffers          184549376 bytes
Redo Buffers                2920448 bytes
Database mounted.
Database opened.
SQL> show parameter nls_date_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      yyyy-mm-dd hh24:mi:ss

SQL> select sysdate from dual;

SYSDATE
------------
04-FEB-15

在修改参数nls_date_format后，我们查询系统时间并没有以’yyyy-mm-dd hh24:mi:ss’这种格式显式,因为NLS_LANG环境变量同样会影响到nls_date_format参数，只要存在NLS_LANG环境变量，Oracle就会使用环境变量的值(即使nls_date_format)，根据上面我们提到的优先级，所以初始化参数文件设置的值会被忽略，但由于环境变量中nls_date_format并没有设置，所以Oracle还是使用了默认的格式。

现在设置NLS_LANG环境变量

[oracle@oracle11g ~]$ export NLS_LANG
[oracle@oracle11g ~]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 4 10:00:05 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter nls_date_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      yyyy-mm-dd hh24:mi:ss
SQL> select sysdate from dual;

SYSDATE
-------------------
2015-02-04 10:01:26

从上面的结果可以看到当我们去掉环境变量nls_lang后，查询系统时间就是以初始化参数nls_date_format的格式(yyyy-mm-dd hh24:mi:ss)来显示的

2.我们将参数nls_date_format设置为’yyyy-mm-dd hh24:mi:ss’，将环境变量设置为’yy-mm-dd hh24:mi:ss’来观察它们的优先级

[oracle@oracle11g ~]$ export nls_date_format='yy-mm-dd hh24:mi:ss'
[oracle@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 4 09:44:07 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter nls_date_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      yyyy-mm-dd hh24:mi:ss
SQL> select sysdate from dual;

SYSDATE
-----------------
15-02-04 09:44:31

可以看到查询系统时间以是环境变量所设置的格式（’yy-mm-dd hh24:mi:ss’）来显示的。这就说明nls_date_format在环境变量中的设置比初始化参数的优先级高。

3.我们将参数nls_date_format设置为’yyyy-mm-dd hh24:mi:ss’，环境变量设置为’yy-mm-dd hh24:mi:ss’，并在会话级将nls_date_format设置为’dd-mm-yy hh24:mi:ss’

[oracle@oracle11g ~]$ export nls_date_format='yy-mm-dd hh24:mi:ss'
[oracle@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 4 09:44:07 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


SQL> alter session set nls_date_format='dd-mm-yy hh24:mi:ss';

Session altered.

SQL> show parameter nls_date_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      yyyy-mm-dd hh24:mi:ss
SQL> select sysdate from dual;

SYSDATE
-----------------
04-02-15 09:50:01

从上面的结果可以看到查询系统时间是以会话级参数nls_date_format的格式（=’dd-mm-yy hh24:mi:ss’）来显示的，这就说明了nls_date_format参数的优先级为:会话级>环境变量>初始化参数

4.我们将参数nls_date_format设置为’yyyy-mm-dd hh24:mi:ss’，环境变量设置为’yy-mm-dd hh24:mi:ss’，并在会话级将nls_date_format设置为’dd-mm-yy hh24:mi:ss’，使用函数来转换日期格式为’mm-dd-yy hh24:mi:ss’

[oracle@oracle11g ~]$ export nls_date_format='yy-mm-dd hh24:mi:ss'
[oracle@oracle11g ~]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Feb 4 09:44:07 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.


SQL> alter session set nls_date_format='dd-mm-yy hh24:mi:ss';

Session altered.

SQL> show parameter nls_date_format

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_date_format                      string      yyyy-mm-dd hh24:mi:ss

SQL> select to_char(sysdate,'mm-dd-yy hh24:mi:ss') from dual;

TO_CHAR(SYSDATE,'
-----------------
02-04-15 10:11:44

从上面的结果可以看到函数的优先级最高，这就证明了日期格式的优先级为：初始化参数 < 系统环境变量 < 会话级(session)< 函数

oracle中scn与时间之间可以进行相互转换，朋友的数据库是10.2.0.5，之前通过scn_to_timestamp将scn转换为相对应的时间时一切正常，但今天执行却报错了.

SQL>select to_char(scn_to_timestamp(3111823),'yyyy-mm-dd hh24:mi:ss') from dual;
select to_char(scn_to_timestamp(3111823),'yyyy-mm-dd hh24:mi:ss') from dual;
       *
ERROR at line 1:
ORA-00904: "SCN_TO_TIMESTAMP": invalid identifier

很奇怪,scn_to_timestamp函数的状态通过下面的查询语句来查询

SQL> select owner,object_name,object_type,last_ddl_time,status from dba_objects where object_name='SCN_TO_TIMESTAMP';

OWNER        OBJECT_NAME           OBJECT_TYPE         LAST_DDL_TIME STATUS
------------ --------------------- ------------------- ------------- -------
SYS          SCN_TO_TIMESTAMP      FUNCTION            2014/11/26 12 VALID
PUBLIC       SCN_TO_TIMESTAMP      SYNONYM             2014/11/26 12 VALID

后来我把scn_to_timestamp函数的创建语句重新执行一次。

create or replace function scn_to_timestamp(query_scn IN NUMBER)
return TIMESTAMP
IS EXTERNAL
NAME "ktfexscntot"
WITH CONTEXT
PARAMETERS(context,
           query_scn OCINUMBER,
           RETURN)
LIBRARY DBMS_TRAN_LIB;

在执行完后执行对scn_to_timestamp函数的查询

SQL> select to_char(scn_to_timestamp(3111823),'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SCN_TO_TIMESTAMP(31118
------------------------------
2015-02-03 16:26:27

但是却新创建了一个scn_to_timestamp函数并没有替换掉原来的

SQL> select owner,object_name,object_type,last_ddl_time,status from dba_objects where object_name='SCN_TO_TIMESTAMP';

OWNER       OBJECT_NAME              OBJECT_TYPE         LAST_DDL_TIME STATUS
----------- ------------------------ ------------------- ------------- -------
SYS         SCN_TO_TIMESTAMP         FUNCTION            2014/11/26 12 VALID
PUBLIC      SCN_TO_TIMESTAMP         SYNONYM             2014/11/26 12 VALID
SYS         SCN_TO_TIMESTAMP         FUNCTION            2015/2/3 16:5 VALID

这个问题还没找到原因，也许是oracle的bug,因为我在oracle 10.2.0.4中重新创建scn_to_timestamp函数后，查询dba_objects并没有显示生成的scn_to_timestamp函数

Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.4.0
Connected as SYS

SQL>
SQL> create or replace function scn_to_timestamp(query_scn IN NUMBER)
  2  return TIMESTAMP
  3  IS EXTERNAL
  4  NAME "ktfexscntot"
  5  WITH CONTEXT
  6  PARAMETERS(context,
  7             query_scn OCINUMBER,
  8             RETURN)
  9  LIBRARY DBMS_TRAN_LIB;
 10  /

Function created

SQL> select owner,object_name,object_type,last_ddl_time,status from dba_objects where object_name='SCN_TO_TIMESTAMP';

OWNER      OBJECT_NAME              OBJECT_TYPE         LAST_DDL_TIME STATUS
---------- ------------------------ ------------------- ------------- -------
SYS        SCN_TO_TIMESTAMP         FUNCTION            2015/2/3 21:2 VALID
PUBLIC     SCN_TO_TIMESTAMP         SYNONYM             2008/4/23 12: INVALID

并且在10.2.0.4中重建scn_to_timestamp函数后，对应的同义词失效了，这才是正确而在10.2.0.5中重建scn_to_timestamp函数后，却产生了一个新的同名对象，且对应的同义仍然为有效状态。

按时间点恢复
数据库按时间点恢复，可以从要恢复的目标时间之前的备份中还的数据库，然后使用增量备份和重做日志来将数据库前滚到目标时间点,按时间点恢复也叫不完全恢复因为不使用所有的日志或者不完全恢复对数据库的所有改变。

数据库按时间点恢复所要满足的条件
1.数据库必须运行在archivelog模式下
2.必须要有恢复目标时间点之前所有数据文件的备份和在备份SCN与目标SCN之间所有的归档重做日志

每次以resetlogs选项打开数据库时一个新的数据库incarnation就会被创建。执行open resetlogs操作时就会对当前的联机重做日志文件进行归档。incarnation会将重做日志序列号设置为1,并且指联机重做日志一个新的时间戳。它也会增加incarnation的序号，它被用来唯一标记和识另重做日志流。

incarnation可能存在的几种关系
1.current incarnation是由那个incarnation执行open resetlog操作产生的，那个incarnation就是current incarnation的parent incarnation

2.parent incarnation和它parent incarnation的incarnation就叫作current incarnation的ancestor incarnations

3.如果两个incarnation共享相同的ancestor那么它们就是sibling incarnations

SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TI PRIOR_RESETLOGS_CHANGE# PRIOR_RESETL STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------------ ----------------- ------------ ----------------------- ------------ ------- ------------ ------------------ --------------------------
           1                 1 30-JUN-05                          0              PARENT     562360180                  0 NO
           2            446075 05-SEP-14                          1 30-JUN-05    PARENT     857466832                  1 NO
           3           2849317 27-JAN-15                     446075 05-SEP-14    PARENT     870102602                  2 NO
           4           2880152 27-JAN-15                    2849317 27-JAN-15    PARENT     870133266                  3 NO
           5           3017109 01-FEB-15                    2880152 27-JAN-15    PARENT     870550288                  4 NO
           6           3041066 01-FEB-15                    3017109 01-FEB-15    PARENT     870563157                  5 NO
           7           3041350 01-FEB-15                    3041066 01-FEB-15    PARENT     870564201                  6 YES
           8           3111834 03-FEB-15                    3041350 01-FEB-15    ORPHAN     870724654                  7 YES
           9           3111834 03-FEB-15                    3041350 01-FEB-15    ORPHAN     870726369                  7 YES
          10           3114665 03-FEB-15                    3041350 01-FEB-15    ORPHAN     870726883                  7 YES
          11           3114664 03-FEB-15                    3041350 01-FEB-15    CURRENT    870729934                  7 YES

11 rows selected.

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       TEST     2155613261       PARENT  1          30-JUN-05
2       2       TEST     2155613261       PARENT  446075     05-SEP-14
3       3       TEST     2155613261       PARENT  2849317    27-JAN-15
4       4       TEST     2155613261       PARENT  2880152    27-JAN-15
5       5       TEST     2155613261       PARENT  3017109    01-FEB-15
6       6       TEST     2155613261       PARENT  3041066    01-FEB-15
7       7       TEST     2155613261       PARENT  3041350    01-FEB-15
8       8       TEST     2155613261       ORPHAN  3111834    03-FEB-15
9       9       TEST     2155613261       ORPHAN  3111834    03-FEB-15
11      11      TEST     2155613261       CURRENT 3114664    03-FEB-15
10      10      TEST     2155613261       ORPHAN  3114665    03-FEB-15

要执行按时间点恢复需要准备好以下两个条件：
1.决定要恢复到的目标时间，SCN，还原点或者日志序列号。闪回查询，闪回版本查询和闪回事务查询可能帮助你来识别逻辑错误。也可以检查alert.log的信息来帮助你判断恢复的目标时间点。另外也可以判断包含目标SCN的日志序列号然后通过日志进行恢复。例如，查询v$log_history来查看已经归档的日志信息。

SQL> select * from v$log_history;

     RECID      STAMP    THREAD#  SEQUENCE# FIRST_CHANGE# FIRST_TIME   NEXT_CHANGE# RESETLOGS_CHANGE# RESETLOGS_TI
---------- ---------- ---------- ---------- ------------- ------------ ------------ ----------------- ------------
       231  870563592          1          2       3041294 01-FEB-15         3041343           3041066 01-FEB-15
       232  870564201          1          3       3041343 01-FEB-15         3041349           3041066 01-FEB-15
       233  870597597          1          1       3041350 01-FEB-15         3063719           3041350 01-FEB-15
       234  870684680          1          2       3063719 02-FEB-15         3097923           3041350 01-FEB-15
       235  870724659          1          3       3097923 03-FEB-15         3114664           3041350 01-FEB-15
       236  870726371          1          1       3111834 03-FEB-15         3112739           3111834 03-FEB-15
       237  870726883          1          1       3111834 03-FEB-15         3114664           3111834 03-FEB-15
       238  870729935          1          1       3114665 03-FEB-15         3116367           3114665 03-FEB-15
       239  870769788          1          1       3114664 03-FEB-15         3135728           3114664 03-FEB-15

例如，如果你发在上午10点1分一个用户意外删除了一个表空间，那么可以将数据库恢复到上午10点，就是在删除表空间前的时间点。在恢复之后在上午10点之后的所有改变都会丢失.

2.如果使用目标时间表达式代替目标SCN，那么在使用RMAN之前要确保时间格式的环境变量设置合适。
NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
NLS_DATE_FORMAT=’YYYY-MM-DD HH24:MI:SS’

使用current incarnation来执行按时间点恢复
使用current incarnation执行按时间点恢复时是使用的当前版本的控制文件。当执行按时间点恢复时，可以使用set until命令来设置恢复的目标时间，而不用对restotre和recover命令单独设置until子句从而避免出错。这能确保从备份中还原的数据文件的时间戳早于后续的recover操作。

按时间点恢复的过程如下：
我们把scott用户下的表emp中的所有记录删除,并且在删除之前记录了当前系统的SCN，然后执行按时间点恢复来恢复表中的记录。

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    3142264

SQL> select to_char(scn_to_timestamp(3142264),'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SCN_TO_TIME
-------------------
2015-02-04 11:22:29

SQL>  select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ------------
         1          1          4   52428800          1 NO  CURRENT                3142228 04-FEB-15
         3          1          3   52428800          1 YES INACTIVE               3142176 04-FEB-15
         2          1          2   52428800          1 YES INACTIVE               3135728 04-FEB-15


SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

14 rows selected.

SQL> delete from emp;

14 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from emp;

no rows selected

1.连接到目标数据库或恢复目录（如果有），将数据库启动到mount状态：

[oracle@oracle11g ~]$ rman target/

Recovery Manager: Release 10.2.0.5.0 - Production on Wed Feb 4 10:25:34 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database (not started)

RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

2.运行RUN块来执行按时间点恢复。在RUN块中使用set until来指定恢复的目标时间，还原点，SCN或日志序列号。如果指定的是目标时间，那么使用NLS_LANG和NLS_DATE_FORMAT环境变量所指定的格式。如果自动通道没有配置，那么要为访问的磁盘或磁带分配通道。

RMAN> run
2> {
3>  set until scn 3142264;
4>  restore database;
5>  recover database;
6> }

executing command: SET until clause

Starting restore at 04-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

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/test/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/test/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/test/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/test/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/test/test01.dbf
restoring datafile 00007 to /u01/app/oracle/oradata/test/testbak.dbf
channel ORA_DISK_1: reading from backup piece /u02/test_df870779983_s135_s1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/test_df870779983_s135_s1 tag=TAG20150204T111943
channel ORA_DISK_1: restore complete, elapsed time: 00:02:29
Finished restore at 04-FEB-15

Starting recover at 04-FEB-15
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:02

Finished recover at 04-FEB-15

从alert日志文件中可以看到如下信息：

The input backup piece /u02/test_df870779983_s135_s1 is in compressed format.
Full restore complete of datafile 6 /u01/app/oracle/oradata/test/test01.dbf.  Elapsed time: 0:00:01
  checkpoint is 3142189
Full restore complete of datafile 7 /u01/app/oracle/oradata/test/testbak.dbf.  Elapsed time: 0:00:06
  checkpoint is 3142189
Full restore complete of datafile 4 /u01/app/oracle/oradata/test/users01.dbf.  Elapsed time: 0:00:09
  checkpoint is 3142189
  last deallocation scn is 3111848
Wed Feb 04 11:25:47 CST 2015
Full restore complete of datafile 2 /u01/app/oracle/oradata/test/undotbs01.dbf.  Elapsed time: 0:00:37
  checkpoint is 3142189
  last deallocation scn is 3106509
Wed Feb 04 11:25:58 CST 2015
Full restore complete of datafile 5 /u01/app/oracle/oradata/test/example01.dbf.  Elapsed time: 0:00:46
  checkpoint is 3142189
  last deallocation scn is 2526488
Wed Feb 04 11:26:57 CST 2015
Full restore complete of datafile 3 /u01/app/oracle/oradata/test/sysaux01.dbf.  Elapsed time: 0:01:47
  checkpoint is 3142189
  last deallocation scn is 3099893
Wed Feb 04 11:27:32 CST 2015
Full restore complete of datafile 1 /u01/app/oracle/oradata/test/system01.dbf.  Elapsed time: 0:02:20
  checkpoint is 3142189
  last deallocation scn is 3101877
Wed Feb 04 11:27:39 CST 2015
alter database recover datafile list clear
Wed Feb 04 11:27:39 CST 2015
Completed: alter database recover datafile list clear
Wed Feb 04 11:27:39 CST 2015
alter database recover datafile list
 1 , 2 , 3 , 4 , 5 , 6 , 7
Completed: alter database recover datafile list
 1 , 2 , 3 , 4 , 5 , 6 , 7
Wed Feb 04 11:27:39 CST 2015
alter database recover if needed
 start until change 3142264
Media Recovery Start
Wed Feb 04 11:27:40 CST 2015
Recovery of Online Redo Log: Thread 1 Group 3 Seq 3 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/test/redo03.log
Wed Feb 04 11:27:40 CST 2015
Recovery of Online Redo Log: Thread 1 Group 1 Seq 4 Reading mem 0
  Mem# 0: /u01/app/oracle/oradata/test/redo01.log
Wed Feb 04 11:27:40 CST 2015
Incomplete Recovery applied until change 3142277
Wed Feb 04 11:27:40 CST 2015
Media Recovery Complete (test)
Completed: alter database recover if needed
 start until change 3142264

从上面的恢复过程可以看到，首先从备份中还原数据文件，各个数据文件的checkpoint scn是3142189比我们的恢复目标SCN小，然后应用重做日志文件将数据库恢复到目标SCN所对应的时间点。

set until还可以使用时间表达式，还原点或者日志序列事情作为它的恢复目标时间点
set until time ‘2015-02-04 11:22:29’;
set until sequence 4;
set until restore point before_delete;

如果按时间点恢复成功。可以以只读方式打开数据库来检查表emp的数据是否恢复回来了。如果表emp的记录没有恢复回来，可能我们选错了恢复目标SCN。在这种情况下，可以使用新的恢复目标SCN然后重新执行按时间点恢复。

RMAN> sql 'alter database open read only';

sql statement: alter database open read only

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

14 rows selected.

从上面的结果可知表emp的记录通过按时间点恢复已经找回来了。

如果按时间点恢复经过验证后达到了恢复目标，可以有如下选择：
1.使用oracle导出工具将恢复的表emp进行逻辑导出。然后将数据库恢复到当前时间点后，再导入导出的数据。这样在不会丢失数据库的其它改变而又恢复了表emp的数据。

2.以读写方式打开数据库，这样在恢复目标SCN之后的所有改变将会丢失。当前的联机重做日志文件会被归档，日志序列号会被设置为1，并且所有的联机重做日志会被指定新的时间戳和SCN。

RMAN> alter database open resetlogs;

database opened

使用ancestor incarnation执行按时间点恢复
使用ancestor incarnation执行按时间点恢复与使用current incarnation的不同之处在于需要设置数据库的incarnation.并且必须从包含恢复目标SCN的incarnation中还原控制文件。

不使用recover catalog的情况
比如我们要将数据库恢复到scott用户的emp表被删除之后的时间点，对older incarnation执行按时间点恢复的过程如下:

1.判断要使用的incarnation。可以使用list incarnation命令来找到恢复目标时间所对应的incarnation

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       TEST     2155613261       PARENT  1          2005-06-30 19:09:40
2       2       TEST     2155613261       PARENT  446075     2014-09-05 09:13:52
3       3       TEST     2155613261       PARENT  2849317    2015-01-27 15:10:02
4       4       TEST     2155613261       PARENT  2880152    2015-01-27 23:41:06
5       5       TEST     2155613261       PARENT  3017109    2015-02-01 19:31:28
6       6       TEST     2155613261       PARENT  3041066    2015-02-01 23:05:57
7       7       TEST     2155613261       PARENT  3041350    2015-02-01 23:23:21
8       8       TEST     2155613261       ORPHAN  3111834    2015-02-03 19:57:34
9       9       TEST     2155613261       ORPHAN  3111834    2015-02-03 20:26:09
11      11      TEST     2155613261       PARENT  3114664    2015-02-03 21:25:34
10      10      TEST     2155613261       ORPHAN  3114665    2015-02-03 20:34:43
12      12      TEST     2155613261       PARENT  3142278    2015-02-04 11:40:02
13      13      TEST     2155613261       PARENT  3144077    2015-02-04 13:09:03
14      14      TEST     2155613261       CURRENT 3144537    2015-02-04 13:32:41

当前的incarnation的Inc Key为14.通过下面的查询可以找到它之前的incarnation的Inc Key为13：

SQL> select prior_incarnation# from v$database_incarnation where status ='CURRENT';

PRIOR_INCARNATION#
------------------
                13

2.将数据库启动到mount状态

RMAN> startup  mount

Oracle instance started
database mounted

Total System Global Area     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

3.将数据库test的incarnation设置为incarnation号为13,也就是current incarnation的parent incarnation。

RMAN> reset database to incarnation 13;

database reset to incarnation 13

4.执行还原与恢复，设置恢复目标时间，如果没有配置相关通道设置通道。将数据库恢复到表emp删除之后的时间点（2015-02-04 13:30:01）：

RMAN> run
2> {
3> set until time '2015-02-04 13:30:01';
4> restore database;
5> recover database;
6> }

executing command: SET until clause

Starting restore at 2015-02-04 13:54:37
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/test/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/test/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/test/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/test/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/test/test01.dbf
restoring datafile 00007 to /u01/app/oracle/oradata/test/testbak.dbf
channel ORA_DISK_1: reading from backup piece /u02/test_df870779983_s135_s1
channel ORA_DISK_1: restored backup piece 1
piece handle=/u02/test_df870779983_s135_s1 tag=TAG20150204T111943
channel ORA_DISK_1: restore complete, elapsed time: 00:02:37
Finished restore at 2015-02-04 13:57:14

Starting recover at 2015-02-04 13:57:14
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /u02/1_3_870729934.dbf
archive log thread 1 sequence 4 is already on disk as file /u02/1_4_870729934.dbf
archive log thread 1 sequence 1 is already on disk as file /u02/1_1_870781202.dbf
archive log filename=/u02/1_3_870729934.dbf thread=1 sequence=3
archive log filename=/u02/1_4_870729934.dbf thread=1 sequence=4
archive log filename=/u02/1_1_870781202.dbf thread=1 sequence=1
archive log filename=/u02/1_1_870786543.dbf thread=1 sequence=1
media recovery complete, elapsed time: 00:00:03
Finished recover at 2015-02-04 13:57:18

RMAN> alter database open resetlogs;

database opened


RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       1       TEST     2155613261       PARENT  1          2005-06-30 19:09:40
2       2       TEST     2155613261       PARENT  446075     2014-09-05 09:13:52
3       3       TEST     2155613261       PARENT  2849317    2015-01-27 15:10:02
4       4       TEST     2155613261       PARENT  2880152    2015-01-27 23:41:06
5       5       TEST     2155613261       PARENT  3017109    2015-02-01 19:31:28
6       6       TEST     2155613261       PARENT  3041066    2015-02-01 23:05:57
7       7       TEST     2155613261       PARENT  3041350    2015-02-01 23:23:21
8       8       TEST     2155613261       ORPHAN  3111834    2015-02-03 19:57:34
9       9       TEST     2155613261       ORPHAN  3111834    2015-02-03 20:26:09
11      11      TEST     2155613261       PARENT  3114664    2015-02-03 21:25:34
10      10      TEST     2155613261       ORPHAN  3114665    2015-02-03 20:34:43
12      12      TEST     2155613261       PARENT  3142278    2015-02-04 11:40:02
13      13      TEST     2155613261       PARENT  3144077    2015-02-04 13:09:03
14      14      TEST     2155613261       ORPHAN  3144537    2015-02-04 13:32:41
15      15      TEST     2155613261       CURRENT 3144674    2015-02-04 13:58:43

使用recover catalog的情况
比如我们要将数据库恢复到scott用户的emp表被删除之后的时间点，对older incarnation执行按时间点恢复的过程如下:

1.判断要使用的incarnation。可以使用list incarnation命令来找到恢复目标时间所对应的incarnation

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       8       TEST     2168949517       PARENT  1          2010-04-19 10:22:46
1       2       TEST     2168949517       PARENT  383537     2015-02-04 17:44:49
1       102     TEST     2168949517       PARENT  415176     2015-02-04 18:22:16
1       188     TEST     2168949517       CURRENT 415481     2015-02-04 18:33:17

当前的incarnation的Inc Key为188.通过下面的查询可以找到它之前的incarnation的Inc Key为102：我们将数据库恢复到2015-02-04 18:22:30，也就是在SCN:415176和SCN:415481之间。

RMAN> list backup of controlfile;


List of Backup Sets
===================

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
75      Full    6.80M      DISK        00:00:01     2015-02-04 18:11:38
        BP Key: 77   Status: AVAILABLE  Compressed: NO  Tag: TAG20150204T181137
        Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870804697_bf3w2t62_.bkp
  Control File Included: Ckp SCN: 415111       Ckp time: 2015-02-04 18:11:37

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
139     Full    6.80M      DISK        00:00:02     2015-02-04 18:22:45
        BP Key: 144   Status: AVAILABLE  Compressed: NO  Tag: TAG20150204T182243
        Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870805363_bf3wqnyv_.bkp
  Control File Included: Ckp SCN: 415288       Ckp time: 2015-02-04 18:22:43

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
236     Full    6.80M      DISK        00:00:03     2015-02-04 18:33:39
        BP Key: 242   Status: AVAILABLE  Compressed: NO  Tag: TAG20150204T183336
        Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870806016_bf3xd2wl_.bkp
  Control File Included: Ckp SCN: 415765       Ckp time: 2015-02-04 18:33:36

从上面的控制文件备份信息可以看到要恢复到2015-02-04 18:22:30这个时间点应该使用控制文件备份是o1_mf_s_870805363_bf3wqnyv_.bkp

2.将数据库强制启动到nomount状态

RMAN> startup force nomount

Oracle instance started

Total System Global Area     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

3.将数据库test的incarnation设置为incarnation号为102,也就是current incarnation的parent incarnation。

RMAN> reset database to incarnation 102;

database reset to incarnation 102

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       8       TEST     2168949517       PARENT  1          2010-04-19 10:22:46
1       2       TEST     2168949517       PARENT  383537     2015-02-04 17:44:49
1       102     TEST     2168949517       CURRENT 415176     2015-02-04 18:22:16
1       188     TEST     2168949517       ORPHAN  415481     2015-02-04 18:33:17

4.执行还原与恢复，设置恢复目标时间，如果没有配置相关通道设置通道。还原控制文件，将数据库恢复到表emp删除之后的时间点（2015-02-04 18:22:30）：

RMAN> restore controlfile;

Starting restore at 2015-02-04 18:44:23
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870805363_bf3wqnyv_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870805363_bf3wqnyv_.bkp tag=TAG20150204T182243
channel ORA_DISK_1: restore complete, elapsed time: 00:00:03
output filename=/u01/app/oracle/oradata/test/control01.ctl
output filename=/u01/app/oracle/oradata/test/control02.ctl
output filename=/u01/app/oracle/oradata/test/control03.ctl
Finished restore at 2015-02-04 18:44:29

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

RMAN> restore database until time '2015-02-04 18:22:30';

Starting restore at 2015-02-04 18:47:15
Starting implicit crosscheck backup at 2015-02-04 18:47:15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=156 devtype=DISK
Crosschecked 4 objects
Finished implicit crosscheck backup at 2015-02-04 18:47:17

Starting implicit crosscheck copy at 2015-02-04 18:47:17
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2015-02-04 18:47:17

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870805363_bf3wqnyv_.bkp
File Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_02_04/o1_mf_s_870806016_bf3xd2wl_.bkp

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/test/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/test/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/test/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/test/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/test/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/backupset/2015_02_04/o1_mf_nnndf_TAG20150204T181037_bf3w0y1f_.bkp
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TEST/backupset/2015_02_04/o1_mf_nnndf_TAG20150204T181037_bf3w0y1f_.bkp tag=TAG20150204T181037
channel ORA_DISK_1: restore complete, elapsed time: 00:01:15
Finished restore at 2015-02-04 18:48:32

将数据文件还原到2015-02-04 18:22:30这个时间点，下面执行恢复，要注意的是在执行恢复操作之前还需要执行reset database to incarnation 102否则会报错:

RMAN> recover database until time '2015-02-04 18:22:30';

Starting recover at 2015-02-04 18:49:05
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 02/04/2015 18:49:05
RMAN-06004: ORACLE error from recovery catalog database: RMAN-20011: target database incarnation is not current in recovery catalog

再次执行 reset database to incarnation 102;

RMAN> reset database to incarnation 102;

database reset to incarnation 102

RMAN> recover database until time '2015-02-04 18:22:30';

Starting recover at 2015-02-04 18:49:21
using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /u02/1_3_870803089.dbf
archive log thread 1 sequence 4 is already on disk as file /u02/1_4_870803089.dbf
archive log thread 1 sequence 1 is already on disk as file /u02/1_1_870805336.dbf
archive log filename=/u02/1_3_870803089.dbf thread=1 sequence=3
archive log filename=/u02/1_4_870803089.dbf thread=1 sequence=4
archive log filename=/u02/1_1_870805336.dbf thread=1 sequence=1
media recovery complete, elapsed time: 00:00:01
Finished recover at 2015-02-04 18:49:24

RMAN> alter database open resetlogs;

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

RMAN> list incarnation;


List of Database Incarnations
DB Key  Inc Key DB Name  DB ID            STATUS  Reset SCN  Reset Time
------- ------- -------- ---------------- --- ---------- ----------
1       8       TEST     2168949517       PARENT  1          2010-04-19 10:22:46
1       2       TEST     2168949517       PARENT  383537     2015-02-04 17:44:49
1       102     TEST     2168949517       PARENT  415176     2015-02-04 18:22:16
1       308     TEST     2168949517       CURRENT 415183     2015-02-04 18:49:41
1       188     TEST     2168949517       ORPHAN  415481     2015-02-04 18:33:17

可以看到恢复完成之后当前的incarnation对应的reset scn号在415176与415481之间，达到了我们所期待的结果。

闪回数据库
如果数据库启用了闪回日志，那么可以使用闪回数据库将数据库的内容回退到闪回窗口中的某一时间点也可以使用flashback database将数据库回退到之前定义的受保护还原点所对应的时间点。

闪回数据库使用的场景
在大多数情况下执行闪回数据库使用时间表达式，正常或受保护还原点或SCN来指定所要回退到的过去时间点。

使用RMAN执行闪回数据库的过程如下：
1.决定一个能代表flashback database命令要将数据库回退到过去时间点的SCN，还原点或时间表达式
创建一个表emp_test

SQL> create table emp_test as select * from emp;

Table created.

SQL> select count(*) from emp_test;

  COUNT(*)
----------
        14

查看当前数据库的SCN号

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    3111823

永久删除表emp_test

SQL> drop table emp_test purge;

Table dropped.

2.使用RMAN连接到目标数据库

[oracle@oracle11g ~]$ rman target/

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Feb 3 16:27:32 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: TEST (DBID=2155613261)

3.完全干净地关闭数据库后并将数据库启动到mount状态:

RMAN> shutdown immediate;

using target database control file instead of recovery catalog
database closed
database dismounted
Oracle instance shut down

RMAN> startup mount;

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

4.执行查询来判断执行闪回数据库的时间窗口。如果有些闪回数据库日志因为闪回区空间压力而被删除了，那么可能不能将数据库回退到所有指定的SCN所对应的时间点。如果flashback database指定的目标SCN超出过闪回窗口，那么flashback database命令将会报ora-38729错误。在执行闪回数据库操作时，RMAN可能需要从备份中还原一些归档重做日志。如果备份存储在SBT上并且对于要访问的SBT设备没有配置必要的通道，那么可以在RUN块中使用allocate channel命令来允许RMAN检索磁盘或磁带上的这些日志文件。

SQL> select oldest_flashback_scn,to_char(oldest_flashback_time,'YYYY-MM-DD HH24:MI:SS') from v$flashback_database_log;

OLDEST_FLASHBACK_SCN TO_CHAR(OLDEST_FLAS
-------------------- -------------------
             3069719 2015-02-02 15:38:40

这里我们的闪回窗口能将数据库回退到SCN号为3069719的时间点，但我们的表emp_test是在
2015-02-02 15:38:40以后创建，且删除的时间在SCN:3111823以后，我们想要通过闪回数据库来恢复表emp_test话恢复的窗口应该在SCN:3111823以后。SCN：3111823对应的时间为2015-02-03 16:26:27

SQL> select to_char(scn_to_timestamp(3111823),'yyyy-mm-dd hh24:mi:ss') from dual;

TO_CHAR(SCN_TO_TIME
-------------------
2015-02-03 16:26:27

5.使用RMAN来执行flashback database命令，并指定一个目标时间点：

RMAN> flashback database to scn 3111823;

Starting flashback at 03-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK


starting media recovery
media recovery complete, elapsed time: 00:00:15

Finished flashback at 03-FEB-15

也可以执行如下命令:

RMAN> flashback database to  time "to_date('2015-02-03 16:26:27','yyyy-mm-dd hh24:mi:ss') ";

Starting flashback at 03-FEB-15
using channel ORA_DISK_1


starting media recovery
media recovery complete, elapsed time: 00:00:15

Finished flashback at 03-FEB-15

当flashback database命令执行完成后，数据库仍然处于mount状态并且恢复到了指定的时间点。

通过将数据库收只读模式打开来执行某些查询来检查数据库的内容来判断是否将数据库恢复到了你所期望的状态。

RMAN> sql 'alter database open read only';

sql statement: alter database open read only

查询表emp_test是否存在且是否有14条记录

SQL> select * from emp_test;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

14 rows selected.

这说明恢复到了我们所期望的状态。

在闪回数据库操作将数据库回退到你所期望的时间点后，可以有两个选择：
1.以open resetlogs选项打开数据库,但在目标SCN号之后的改变将会丢失
RMAN>alter database open resetlogs;

2.使用Oracle的导出工具将被删除的对象导出。然后将数据库恢复到当前时间点：
RMAN>recover database;
这步操作将会撤消闪回数据库的影响，通过对数据库应用重做日志中的所有改变，将数据库恢复到最近的SCN时间点。在以读写模式打开数据库后，可以将导出的表导入数据库。

如果将数据库闪回到一个错误的时间点之后的选择
如果我们将上面的数据库使用闪回回退到表emp_test创建之前SCN:3111000，那么在闪回数据库之后表emp_test是不存在的。

 RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

RMAN> flashback database to scn 3111000;

Starting flashback at 03-FEB-15
using channel ORA_DISK_1


starting media recovery
media recovery complete, elapsed time: 00:00:15

Finished flashback at 03-FEB-15

RMAN>  sql 'alter database open read only';

sql statement: alter database open read only


SQL> select * from emp_test;
select * from emp_test
              *
ERROR at line 1:
ORA-00942: table or view does not exist

确实闪回成功后表emp_test不存在。

1.如果选择的闪回目标时间回退的不够，那么可以再次执行flashback database命令将数据库回退到比当前更前的时间

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

RMAN> flashback database to scn 3111993;

Starting flashback at 03-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK


starting media recovery
media recovery complete, elapsed time: 00:00:15

Finished flashback at 03-FEB-15

RMAN>  sql 'alter database open read only';

sql statement: alter database open read only

SQL> select * from emp_test;
select * from emp_test
              *
ERROR at line 1:
ORA-00942: table or view does not exist

回退的时间点比所期待的时间晚，所以表emp_test还是被删除了。

2.如果选择的目标SCN将数据库回退到比期望的时间点更早的时间，那么可以加载数据库，使用recover database until scn命令来恢复到所期望的时间点：

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

RMAN> flashback database to scn 3110000;

Starting flashback at 03-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK


starting media recovery
media recovery complete, elapsed time: 00:00:15

Finished flashback at 03-FEB-15

RMAN>  sql 'alter database open read only';

sql statement: alter database open read only

SQL> select * from emp_test;
select * from emp_test
              *
ERROR at line 1:
ORA-00942: table or view does not exist

回退的时间点比所期待的时间早，所以表emp_test还没有被创建,执行recover database until scn命令将数据库恢复到表emp_test删除之前（因为表emp_test删除之前系统的SCN为:3111823)

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

RMAN> recover database until scn 3111823;

Starting recover at 03-FEB-15
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:04

Finished recover at 03-FEB-15

RMAN> sql 'alter database open read only';

sql statement: alter database open read only

SQL> select * from emp_test;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

从查询结果可以看到表emp_test被恢复回来了

3.如果想完全撤消flashback database命令的影响，可以使用recover database命令对数据库执行完全恢复。

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

RMAN> recover database;

Starting recover at 03-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK

starting media recovery
media recovery complete, elapsed time: 00:00:05

Finished recover at 03-FEB-15

RMAN> sql 'alter database open';

sql statement: alter database open

SQL> select * from emp_test;
select * from emp_test
              *
ERROR at line 1:
ORA-00942: table or view does not exist

可以看到对数据库执行完全恢复后，表emp_test不存在，这也就撤消了闪回数据库的影响。

执行闪回数据库来撤消open resetlogs的影响,将数据库回退到表emp_test被删除之前.

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

RMAN> flashback database to scn 3111823;

Starting flashback at 03-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK


starting media recovery
media recovery complete, elapsed time: 00:00:15

Finished flashback at 03-FEB-15

RMAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs

SQL> select * from emp_test;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

验证闪回窗口开始时间是否比最近open resetlogs的时间早

SQL> select resetlogs_change# from v$database;

RESETLOGS_CHANGE#
-----------------
          3111834

SQL> select oldest_flashback_scn from v$flashback_database_log;

OLDEST_FLASHBACK_SCN
--------------------
             3074835

如果v$database.resetlogs_change#比v$flashback_database_log.oldest_flashback_scn大，那么可以使用闪回数据库来撤消open resetlogs操作对数据库的改变。

关闭数据库后将数据库置于mount状态，并重新检查闪回窗口。如果resetlogs的SCN仍然在闪回窗口中，那么可以执行flashback database命令来撤消open resetlogs操作对数据库的改变。

SQL> select resetlogs_change# from v$database;

RESETLOGS_CHANGE#
-----------------
          3111834

SQL> select oldest_flashback_scn from v$flashback_database_log;

OLDEST_FLASHBACK_SCN
--------------------
             3074835



RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

RMAN> flashback database to before resetlogs;

Starting flashback at 03-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=154 devtype=DISK


starting media recovery
media recovery complete, elapsed time: 00:00:03

Finished flashback at 03-FEB-15

RMAN> sql 'alter database open read only';

sql statement: alter database open read only

SQL> select * from emp_test;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

如果目标SCN在闪回数据库窗口的开始时间之前，那么flashback database命令会报错。如果闪回数据库执行成功，那么数据库还处于mount状态，并将数据库恢复到了之前数据库incarnation对应的open resetlogs操作之前最后的SCN所对应的时间点。因为数据库现在对应的resetlogs_change#为3111834，上面的闪回数据库将数据库回退到SCN为3111834之前的状态，所以以只读模式将数据库打开后表emp_test应该是存在的，来验证数据库是否恢复到你所期望的状态。为了使用数据库可以进行更新，使用alter database open resetlogs命令来打开数据库。

RMAN> alter database open resetlogs;

database opened

将数据库闪回到正确的open resetlogs状态
在有些情况下，可能需要将数据库回退到parent incarnation所对应的时间。可以使用RMAN的reset database to incarnation命令来指定flashback database to scn来引用的current incarnation

操作过程如下：
1.验证闪回日志所包含的信息能将数据库回退到的SCN：

SQL> select oldest_flashback_scn from v$flashback_database_log;

OLDEST_FLASHBACK_SCN
--------------------
             3077255

2.判断闪回数据库要使用的目标incarnation

SQL> select * from v$database_incarnation;

INCARNATION# RESETLOGS_CHANGE# RESETLOGS_TI PRIOR_RESETLOGS_CHANGE# PRIOR_RESETL STATUS  RESETLOGS_ID PRIOR_INCARNATION# FLASHBACK_DATABASE_ALLOWED
------------ ----------------- ------------ ----------------------- ------------ ------- ------------ ------------------ --------------------------
           1                 1 30-JUN-05                          0              PARENT     562360180                  0 NO
           2            446075 05-SEP-14                          1 30-JUN-05    PARENT     857466832                  1 NO
           3           2849317 27-JAN-15                     446075 05-SEP-14    PARENT     870102602                  2 NO
           4           2880152 27-JAN-15                    2849317 27-JAN-15    PARENT     870133266                  3 NO
           5           3017109 01-FEB-15                    2880152 27-JAN-15    PARENT     870550288                  4 NO
           6           3041066 01-FEB-15                    3017109 01-FEB-15    PARENT     870563157                  5 NO
           7           3041350 01-FEB-15                    3041066 01-FEB-15    PARENT     870564201                  6 YES
           8           3111834 03-FEB-15                    3041350 01-FEB-15    ORPHAN     870724654                  7 YES
           9           3111834 03-FEB-15                    3041350 01-FEB-15    ORPHAN     870726369                  7 YES
          10           3114665 03-FEB-15                    3041350 01-FEB-15    CURRENT    870726883                  7 YES


SQL> select prior_incarnation# from v$database_incarnation where status ='CURRENT';

PRIOR_INCARNATION#
------------------
                 7

从上面的信息可以知道当前的incarnation号为10,它的parent incarnation号是7

3.在RMAN在关闭数据库，并mount数据库：

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

4.将数据库的incarnation设置为它的parent incarnation:

RMAN> reset database  to incarnation 7;

database reset to incarnation 7

5.运行flashback database命令：
RMAN> flashback database to scn 3077255;

Starting flashback at 03-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=157 devtype=DISK


starting media recovery

archive log thread 1 sequence 2 is already on disk as file /u02/1_2_870564201.dbf
media recovery complete, elapsed time: 00:00:01
Finished flashback at 03-FEB-15

RMAN> sql 'alter database open read only';

sql statement: alter database open read only

当闪回操作成功后，可以验证闪回数据库的结果，如果数据库回退到你所期望的状态就以resetlogs选项打开数据库。

RMAN> shutdown immediate

database closed
database dismounted
Oracle instance shut down

RMAN> startup mount

connected to target database (not started)
Oracle instance started
database mounted

Total System Global Area     327155712 bytes

Fixed Size                     1273516 bytes
Variable Size                138412372 bytes
Database Buffers             184549376 bytes
Redo Buffers                   2920448 bytes

RMAN> alter database open resetlogs;

database opened

闪回表
oracle闪回表给予了DBA将一个或一组表快速恢复到过去的指定时间点并且不需要将数据库脱机。在大多数情况下闪回表与按时间点恢复操作相比消除了大量的操作。闪回表还原表时会自动维护与表相关的属性比如，当前索引，触发器和约束，并且不需要DBA找出和还原相关的属性。使用闪回表能将表回退到之前SCN或时间所对应的状态。

闪回表使用undo表空间中的信息来还原表。不需要从备份中还原任何数据，且在执行闪回表操作时数据库仍然可以使用。

使用闪回表的条件
使用闪回表有以下条件：
1.必须对表启用行移动。可以执行下面的语句来对表启用行移动：
alter table tablename enable row movement;

2.用户必须被授予了flashback any table的系统权限或者对特定的表有flashback对象权限

3.用户对于特定的表必须有select,insert,delete和alter权限

4.undo表空间中保留的undo信息足够能满足flashback table操作回退到所指定的时间点或SCN

下面测试闪回表
1.对表emp启用行移动

SQL> alter table emp enable row movement;

Table altered.


SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE            SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------ ---------- ---------- ----------
      7369 SMITH      CLERK           7902 17-DEC-80           800                    20
      7499 ALLEN      SALESMAN        7698 20-FEB-81          1600        300         30
      7521 WARD       SALESMAN        7698 22-FEB-81          1250        500         30
      7566 JONES      MANAGER         7839 02-APR-81          2975                    20
      7654 MARTIN     SALESMAN        7698 28-SEP-81          1250       1400         30
      7698 BLAKE      MANAGER         7839 01-MAY-81          2850                    30
      7782 CLARK      MANAGER         7839 09-JUN-81          2450                    10
      7788 SCOTT      ANALYST         7566 19-APR-87          3000                    20
      7839 KING       PRESIDENT            17-NOV-81          5000                    10
      7844 TURNER     SALESMAN        7698 08-SEP-81          1500          0         30
      7876 ADAMS      CLERK           7788 23-MAY-87          1100                    20
      7900 JAMES      CLERK           7698 03-DEC-81           950                    30
      7902 FORD       ANALYST         7566 03-DEC-81          3000                    20
      7934 MILLER     CLERK           7782 23-JAN-82          1300                    10

14 rows selected.

2.在删除表emp的记录之前记录一下时间或者SCN

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
    3077391

SQL> select sysdate from dual;

SYSDATE
-------------------
2015-02-02 20:50:39

3.删除表emp的记录

SQL> delete from emp;

14 rows deleted.

SQL> commit;

Commit complete.

SQL> select * from emp;

no rows selected

4.执行闪回表将表emp的内容回退到删除之前
使用flashback table tablename to scn的命令如下:

SQL> flashback table emp to scn 3077391;

Flashback complete.

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

14 rows selected.

使用flashback table tablename to timestamp的命令如下:

SQL> flashback table emp to timestamp to_timestamp('2015-02-02 20:50:39','yyyy-mm-dd hh24:mi:ss');

Flashback complete.

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

14 rows selected.

注意：timestamp到SCN之间的映射不总是精确的，当使用timestamp作为flashback table语句的时间点,闪回表的真实时间点与由to_timestamp所指定的时间点大约有三秒钟的差异。

默认情况下，在执行flashback table操作之前数据库对受影响的表禁用了触发器，将表闪回到所指定的时间点后，在对表执行操作之前启用触发器。如果想让表在执行flashback操作时启用触发器，那么可以在flashback table语句中加入enable triggers子句。

SQL> flashback table emp to timestamp to_timestamp('2015-02-02 20:50:39','yyyy-mm-dd hh24:mi:ss') enable triggers;

Flashback complete.

SQL> select * from emp;

     EMPNO ENAME      JOB              MGR HIREDATE                   SAL       COMM     DEPTNO
---------- ---------- --------- ---------- ------------------- ---------- ---------- ----------
      7369 SMITH      CLERK           7902 1980-12-17 00:00:00        800                    20
      7499 ALLEN      SALESMAN        7698 1981-02-20 00:00:00       1600        300         30
      7521 WARD       SALESMAN        7698 1981-02-22 00:00:00       1250        500         30
      7566 JONES      MANAGER         7839 1981-04-02 00:00:00       2975                    20
      7654 MARTIN     SALESMAN        7698 1981-09-28 00:00:00       1250       1400         30
      7698 BLAKE      MANAGER         7839 1981-05-01 00:00:00       2850                    30
      7782 CLARK      MANAGER         7839 1981-06-09 00:00:00       2450                    10
      7788 SCOTT      ANALYST         7566 1987-04-19 00:00:00       3000                    20
      7839 KING       PRESIDENT            1981-11-17 00:00:00       5000                    10
      7844 TURNER     SALESMAN        7698 1981-09-08 00:00:00       1500          0         30
      7876 ADAMS      CLERK           7788 1987-05-23 00:00:00       1100                    20
      7900 JAMES      CLERK           7698 1981-12-03 00:00:00        950                    30
      7902 FORD       ANALYST         7566 1981-12-03 00:00:00       3000                    20
      7934 MILLER     CLERK           7782 1982-01-23 00:00:00       1300                    10

14 rows selected.

RMAN在使用recover catalog的情况下在nomount状态连接实例出现以下错误信息：

RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

下面我来模拟一下这种情况
1.将数据库置于nomount状态

SQL> startup nomount
ORACLE instance started.

Total System Global Area  327155712 bytes
Fixed Size                  1273516 bytes
Variable Size             138412372 bytes
Database Buffers          184549376 bytes
Redo Buffers                2920448 bytes

2.连接目标数据库

[oracle@oracle11g ~]$ rman target sys/zzh_2046@test catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Sun Feb 1 23:01:08 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

3.检查监听状态

[oracle@oracle11g ~]$ lsnrctl status

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 01-FEB-2015 23:00:49

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date                01-FEB-2015 22:51:09
Uptime                    0 days 0 hr. 9 min. 40 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Log File         /u01/app/oracle/10.2.0/db/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=oracle11g)(PORT=1521)))
Services Summary...
Service "test" has 1 instance(s).
  Instance "test", status BLOCKED, has 1 handler(s) for this service...
Service "test_XPT" has 1 instance(s).
  Instance "test", status BLOCKED, has 1 handler(s) for this service...
The command completed successfully

从上面的信息可以看到Instance “test”,status BLOCKED，对于ora-12528错误在MOS有一篇文章描述造成这种问题的原因:

Cause

When an instance is in restricted mode, PMON updates the listener with that information and blocks new connections from being established.

The lsnrctl services output will show the handler is blocked for new connections or lsnrctl status may show the instance is in RESTRICTED mode.

解决方法如下:

The (UR=A) clause for TNS connect strings was created in response to an enhancement request.  This clause can be inserted into the "(CONNECT_DATA=" section of a TNS connect string and allow a privileged or administrative user to connect via the listener even when the service handler is blocking connections for non-privileged users.

Here's an example of a connect string configured with (UR=A):

PROD =
   (DESCRIPTION =
     (ADDRESS_LIST =
          (ADDRESS = (PROTOCOL = TCP)(HOST = myhost.oracle.com)(PORT = 1521)))
      (CONNECT_DATA =
          (UR=A)
          (SERVICE_NAME = prod10ib.oracle.com)
       )
     )



Please note that the (UR=A) clause is intended to work with a dynamically registered handler so the use of SERVICE_NAME versus SID is required when using dynamic registration (i.e. handler exists in lsnrctl output but is BLOCKED).  The use of SID in a TNS connect string may allow a connection if using a static handler in the listener.ora file under SID_DESC.

4.修改tns文件增加(UR=A)

[oracle@oracle11g admin]$ vi tnsnames.ora

test =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1521))
    )
    (CONNECT_DATA =
     (UR=A)
     (SERVICE_NAME = test)
    )
  )


jy =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = jy)
    )
  )

5.再次连接目标数据库执行恢复

[oracle@oracle11g admin]$ rman target sys/zzh_2046@test catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Sun Feb 1 23:04:03 2015

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

connected to target database: test (not mounted)
connected to recovery catalog database

RMAN> restore controlfile;

Starting restore at 01-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/10.2.0/db/dbs/c-2155613261-20150201-01
channel ORA_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/10.2.0/db/dbs/c-2155613261-20150201-01 tag=TAG20150201T213315
channel ORA_DISK_1: restore complete, elapsed time: 00:00:04
output filename=/u01/app/oracle/oradata/test/control01.ctl
output filename=/u01/app/oracle/oradata/test/control02.ctl
output filename=/u01/app/oracle/oradata/test/control03.ctl
Finished restore at 01-FEB-15

RMAN> sql 'alter database mount';

sql statement: alter database mount
released channel: ORA_DISK_1

RMAN> recover database;

Starting recover at 01-FEB-15
Starting implicit crosscheck backup at 01-FEB-15
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=155 devtype=DISK
Crosschecked 8 objects
Finished implicit crosscheck backup at 01-FEB-15

Starting implicit crosscheck copy at 01-FEB-15
using channel ORA_DISK_1
Crosschecked 6 objects
Finished implicit crosscheck copy at 01-FEB-15

searching for all files in the recovery area
cataloging files...
no files cataloged

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file /u01/app/oracle/oradata/test/redo03.log
archive log thread 1 sequence 4 is already on disk as file /u01/app/oracle/oradata/test/redo01.log
archive log filename=/u01/app/oracle/oradata/test/redo03.log thread=1 sequence=3
archive log filename=/u01/app/oracle/oradata/test/redo01.log thread=1 sequence=4
media recovery complete, elapsed time: 00:00:01
Finished recover at 01-FEB-15

RMAN> sql 'alter database open resetlogs';

sql statement: alter database open resetlogs
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete

出现这个问题的原因是当使用动态注册监听时，当实例处于限制模式或受阻的情况下PMON将会更新监听程序来阻新的连接，解决方法其实有两种：
一是在tns连接描述串中增加(UR=A)子句来让动态监听程序使用service_name来进行注册
二是使用静态监听注册