2015年3月 – 第2页 – 但行好事莫问前程

RMAN复制数据库（三）

Posted on 2015年3月27日2015年3月27日 by YongJing(敬勇)

只使用duplicate参数来转换文件名
这种方法是使用duplicate命令的db_file_name_convert参数来对复制的数据文件进行重命名，并且使用logfile子句来指定联机重做日志文件名和其大小。

1.创建辅助实例的密码文件(这里辅助实例名为dup)

[oracle@jingyong1 dbs]$ orapwd file=/u01/app/oracle/product/10.2.0/db/dbs/orapwdup password=oracle entries=10;
[oracle@jingyong1 dbs]$ ls -lrt
-rw-r----- 1 oracle oinstall    2560 Mar 24 14:47 orapwdup

2.创建辅助实例网络连接,修改监听文件，使用静态监听来监听辅助实例

[oracle@jingyong1 admin]$ vi listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (SID_NAME = dup)
      (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
    )
  )

给辅助实例增加网络服务名

[oracle@jingyong1 admin]$ vi tnsnames.ora
dup =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =dup)
      (UR=A)
    )
  )

测试网络连接

[oracle@jingyong1 admin]$ export ORACLE_SID=dup
[oracle@jingyong1 admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 14:57:08 2015

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

SQL> conn sys/oracle@dup as sysdba
Connected to an idle instance.

3.创建辅助实例的参数文件

[oracle@jingyong1 dbs]$ vi initdup.ora

db_name=dup
db_unique_name=dup
control_files= /u01/app/oracle/oradata/test/control01.ctl
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



[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 21:13:44 2015

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

Connected to an idle instance.

SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db/dbs/initdup.ora';

File created.

4.启动辅助实例

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

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015

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


SQL> conn sys/oracle@dup as sysdba
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> show parameter spfile

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
spfile                               string                 /u01/app/oracle/product/10.2.0
                                                            /db/dbs/spfiledup.ora

断开连接

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

这里一定要断开启动辅助实例的会话否则在执行复制操作时会出现如下错误：

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@oracle11g admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/10.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

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

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )




dup =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =dup)
      (UR=A)
    )
  )

[oracle@oracle11g admin]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015

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

SQL> conn sys/oracle@dup as sysdba
Connected.

5.加载或打开目标数据库

[oracle@oracle11g ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size              92275944 bytes
Database Buffers           71303168 bytes
Redo Buffers                2920448 bytes
Database mounted.
Database opened.

6.确保有需要的备份和归档重做日志
对目标数据库（被复制的数据库）进行备份（包含数据文件和归档重做日志）

[oracle@oracle11g admin]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
[oracle@oracle11g admin]$ rman target sys/zzh_2046@test catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 24 15:15:52 2015

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

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

RMAN> backup as backupset database plus archivelog delete all input;


Starting backup at 2015-03-24 15:18:57
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=49 recid=56 stamp=874583978
input archive log thread=1 sequence=50 recid=57 stamp=874583996
input archive log thread=1 sequence=51 recid=58 stamp=874585387
input archive log thread=1 sequence=52 recid=59 stamp=874587721
input archive log thread=1 sequence=53 recid=60 stamp=874588386
input archive log thread=1 sequence=54 recid=61 stamp=874657003
input archive log thread=1 sequence=55 recid=62 stamp=874667860
input archive log thread=1 sequence=56 recid=63 stamp=874667943
input archive log thread=1 sequence=57 recid=64 stamp=874668125
input archive log thread=1 sequence=58 recid=65 stamp=874668205
input archive log thread=1 sequence=59 recid=66 stamp=874841642
input archive log thread=1 sequence=60 recid=67 stamp=874842111
input archive log thread=1 sequence=61 recid=68 stamp=875091192
input archive log thread=1 sequence=62 recid=69 stamp=875177310
input archive log thread=1 sequence=63 recid=70 stamp=875200740
channel ORA_DISK_1: starting piece 1 at 2015-03-24 15:19:03
channel ORA_DISK_1: finished piece 1 at 2015-03-24 15:19:19
piece handle=/u02/ora_test875200742_711 tag=TAG20150324T151901 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:17
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/1_49_870806981.dbf recid=56 stamp=874583978
archive log filename=/u02/1_50_870806981.dbf recid=57 stamp=874583996
archive log filename=/u02/1_51_870806981.dbf recid=58 stamp=874585387
archive log filename=/u02/1_52_870806981.dbf recid=59 stamp=874587721
archive log filename=/u02/1_53_870806981.dbf recid=60 stamp=874588386
archive log filename=/u02/1_54_870806981.dbf recid=61 stamp=874657003
archive log filename=/u02/1_55_870806981.dbf recid=62 stamp=874667860
archive log filename=/u02/1_56_870806981.dbf recid=63 stamp=874667943
archive log filename=/u02/1_57_870806981.dbf recid=64 stamp=874668125
archive log filename=/u02/1_58_870806981.dbf recid=65 stamp=874668205
archive log filename=/u02/1_59_870806981.dbf recid=66 stamp=874841642
archive log filename=/u02/1_60_870806981.dbf recid=67 stamp=874842111
archive log filename=/u02/1_61_870806981.dbf recid=68 stamp=875091192
archive log filename=/u02/1_62_870806981.dbf recid=69 stamp=875177310
archive log filename=/u02/1_63_870806981.dbf recid=70 stamp=875200740
Finished backup at 2015-03-24 15:19:20

Starting backup at 2015-03-24 15:19:20
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 2015-03-24 15:19:21
channel ORA_DISK_1: finished piece 1 at 2015-03-24 15:20:46
piece handle=/u02/ora_test875200761_721 tag=TAG20150324T151920 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup at 2015-03-24 15:20:46

Starting backup at 2015-03-24 15:20:47
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=64 recid=71 stamp=875200847
channel ORA_DISK_1: starting piece 1 at 2015-03-24 15:20:49
channel ORA_DISK_1: finished piece 1 at 2015-03-24 15:20:50
piece handle=/u02/ora_test875200848_731 tag=TAG20150324T152048 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/1_64_870806981.dbf recid=71 stamp=875200847
Finished backup at 2015-03-24 15:20:50

Starting Control File and SPFILE Autobackup at 2015-03-24 15:20:50
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_24/o1_mf_s_875200851_bk242now_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2015-03-24 15:20:55

RMAN> list backup;


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

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
4452    61.69M     DISK        00:00:15     2015-03-24 15:19:17
        BP Key: 4453   Status: AVAILABLE  Compressed: NO  Tag: TAG20150324T151901
        Piece Name: /u02/ora_test875200742_711

  List of Archived Logs in backup set 4452
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    49      833130     2015-03-17 11:59:13 833176     2015-03-17 11:59:38
  1    50      833176     2015-03-17 11:59:38 833183     2015-03-17 11:59:56
  1    51      833183     2015-03-17 11:59:56 834037     2015-03-17 12:23:07
  1    52      834037     2015-03-17 12:23:07 835281     2015-03-17 13:02:01
  1    53      835281     2015-03-17 13:02:01 835767     2015-03-17 13:13:06
  1    54      835767     2015-03-17 13:13:06 867877     2015-03-18 08:16:39
  1    55      867877     2015-03-18 08:16:39 873825     2015-03-18 11:17:38
  1    56      873825     2015-03-18 11:17:38 873875     2015-03-18 11:19:03
  1    57      873875     2015-03-18 11:19:03 873988     2015-03-18 11:22:05
  1    58      873988     2015-03-18 11:22:05 874075     2015-03-18 11:23:25
  1    59      874075     2015-03-18 11:23:25 907518     2015-03-20 11:33:59
  1    60      907518     2015-03-20 11:33:59 928331     2015-03-20 11:41:50
  1    61      928331     2015-03-20 11:41:50 960300     2015-03-23 08:53:08
  1    62      960300     2015-03-23 08:53:08 998814     2015-03-24 08:48:24
  1    63      998814     2015-03-24 08:48:24 1009277    2015-03-24 15:18:57

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
4479    Full    624.66M    DISK        00:01:24     2015-03-24 15:20:45
        BP Key: 4495   Status: AVAILABLE  Compressed: NO  Tag: TAG20150324T151920
        Piece Name: /u02/ora_test875200761_721
  List of Datafiles in backup set 4479
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 1009303    2015-03-24 15:19:21 /u01/app/oracle/oradata/test/system01.dbf
  2       Full 1009303    2015-03-24 15:19:21 /u01/app/oracle/oradata/test/undotbs01.dbf
  3       Full 1009303    2015-03-24 15:19:21 /u01/app/oracle/oradata/test/sysaux01.dbf
  4       Full 1009303    2015-03-24 15:19:21 /u01/app/oracle/oradata/test/users01.dbf
  5       Full 1009303    2015-03-24 15:19:21 /u01/app/oracle/oradata/test/example01.dbf
  6       Full 1009303    2015-03-24 15:19:21 /u01/app/oracle/oradata/test/tspitr01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
4511    3.00K      DISK        00:00:01     2015-03-24 15:20:49
        BP Key: 4518   Status: AVAILABLE  Compressed: NO  Tag: TAG20150324T152048
        Piece Name: /u02/ora_test875200848_731

  List of Archived Logs in backup set 4511
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    64      1009277    2015-03-24 15:18:57 1009334    2015-03-24 15:20:47

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
4529    Full    6.89M      DISK        00:00:02     2015-03-24 15:20:53
        BP Key: 4531   Status: AVAILABLE  Compressed: NO  Tag: TAG20150324T152051
        Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_24/o1_mf_s_875200851_bk242now_.bkp
  Control File Included: Ckp SCN: 1009356      Ckp time: 2015-03-24 15:20:51
  SPFILE Included: Modification time: 2015-03-24 08:48:23

将上面的备份传输到远程主机的相同目录中：

[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/ora_test875200742_711 /u02
The authenticity of host '192.168.56.2 (192.168.56.2)' can't be established.
RSA key fingerprint is fb:1d:33:a6:9e:25:86:6a:a0:44:76:d4:cf:eb:c9:c4.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.56.2' (RSA) to the list of known hosts.
oracle@192.168.56.2's password:
ora_test875200742_711                                                                                                                100%   62MB  10.3MB/s   00:06
[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/ora_test875200761_721 /u02
oracle@192.168.56.2's password:
ora_test875200761_721                                                                                                                100%  625MB   7.7MB/s   01:21
[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/ora_test875200848_731 /u02
oracle@192.168.56.2's password:
ora_test875200848_731                                                                                                                100% 3584     3.5KB/s   00:00
[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_24/o1_mf_s_875200851_bk242now_.bkp /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_24/
oracle@192.168.56.2's password:
o1_mf_s_875200851_bk242now_.bkp

7.执行duplicate命令，如果没有配置自动通道，那么至少手动分配一个辅助实例。使用duplicate命令的db_file_name_convert参数来对复制的数据文件进行重命名(/u01/app/oracle/oradata/test/ to/u01/app/oracle/oradata/dup/)，并且使用logfile子句来指定联机重做日志文件名和其大小。如果是使用PFILE参数文件启动辅助实例需要指定pfile参数文件，且pfile参数文件必须存储在运行RMAN执行复制的主机上。这里辅助实例使用SPFILE参数文件来启动，并使用自动通道:

[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 21:36:04 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
2> db_file_name_convert=('/u01/app/oracle/oradata/test/','/u01/app/oracle/oradata/dup/')
3> logfile
4> '/u01/app/oracle/oradata/dup/redo01.log' size 50M,
5> '/u01/app/oracle/oradata/dup/redo02.log' size 50M,
6> '/u01/app/oracle/oradata/dup/redo03.log' size 50M;

Starting Duplicate Db at 2015-03-24 21:36:45
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/dup/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/dup/undotbs01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/dup/sysaux01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/dup/users01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/dup/example01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/dup/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 21:36:46
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/dup/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/dup/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/dup/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/dup/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/dup/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/dup/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:06
Finished restore at 2015-03-24 21:37:51
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/dup/redo01.log' SIZE 50 M ,
  GROUP  2 '/u01/app/oracle/oradata/dup/redo02.log' SIZE 50 M ,
  GROUP  3 '/u01/app/oracle/oradata/dup/redo03.log' SIZE 50 M
 DATAFILE
  '/u01/app/oracle/oradata/dup/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=875223483 filename=/u01/app/oracle/oradata/dup/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=875223483 filename=/u01/app/oracle/oradata/dup/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=875223483 filename=/u01/app/oracle/oradata/dup/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=875223483 filename=/u01/app/oracle/oradata/dup/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=875223483 filename=/u01/app/oracle/oradata/dup/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 21:37:53
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:02
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=875223486
media recovery complete, elapsed time: 00:00:01
Finished recover at 2015-03-24 21:37:58

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/dup/redo01.log' SIZE 50 M ,
  GROUP  2 '/u01/app/oracle/oradata/dup/redo02.log' SIZE 50 M ,
  GROUP  3 '/u01/app/oracle/oradata/dup/redo03.log' SIZE 50 M
 DATAFILE
  '/u01/app/oracle/oradata/dup/system01.dbf'
 CHARACTER SET ZHS16GBK


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

executing command: SET NEWNAME

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

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

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

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

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

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

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

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

database opened
Finished Duplicate Db at 2015-03-24 21:38:32


[oracle@jingyong1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:39: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> select instance_name from v$instance;

INSTANCE_NAME
----------------
dup

RMAN复制数据库（二）

Posted on 2015年3月27日2015年3月27日 by YongJing(敬勇)

使用不同的目录结构在远程主机上创建复制数据库
如果在远程主机上使用不同于目标数据库（被复制的数据库）的目录结构来创建复制数据库，那么为了在新的目录中为复制数据库的数据文件生成新的文件名必须修改一些参数。下面分别来介绍生成新文件名的各种方法。

只使用初始化参数来转换文件名
这种方法是只使用初始化方法来对复制数据库的数据文件和日志文件进行重命名。
1.创建辅助实例的密码文件(这里辅助实例名为dup)

[oracle@jingyong1 dbs]$ orapwd file=/u01/app/oracle/product/10.2.0/db/dbs/orapwdup password=oracle entries=10;
[oracle@jingyong1 dbs]$ ls -lrt
-rw-r----- 1 oracle oinstall    2560 Mar 24 14:47 orapwdup

2.创建辅助实例网络连接,修改监听文件，使用静态监听来监听辅助实例

[oracle@jingyong1 admin]$ vi listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (SID_NAME = dup)
      (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
    )
  )

给辅助实例增加网络服务名

[oracle@jingyong1 admin]$ vi tnsnames.ora
dup =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =dup)
      (UR=A)
    )
  )

测试网络连接

[oracle@jingyong1 admin]$ export ORACLE_SID=dup
[oracle@jingyong1 admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 14:57:08 2015

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

SQL> conn sys/oracle@dup as sysdba
Connected to an idle instance.

3.创建辅助实例的参数文件
检查所有以_DEST结束的参数并且指定完整路径名。有一些可能需要修改。设置db_file_name_convert参数，RMAN就能捕获目标数据库的所有数据文件并将其转换成合适的文件名。设置log_file_name_convert参数，RMAN能捕获目标数据库的所有联机重做日志并将其转换成合适的文件名。db_file_name_convert和log_file_name_convert参数可以设置多个转换对。下面将目标数据库的数据文件和日志文件从/u01/app/oracle/oradata/test/目录转换到/u01/app/oracle/oradata/dup/目录中

[oracle@jingyong1 dbs]$ vi initdup.ora

db_name=dup
db_unique_name=dup
control_files= /u01/app/oracle/oradata/test/control01.ctl
db_file_name_convert=('/u01/app/oracle/oradata/test/','/u01/app/oracle/oradata/dup/')
log_file_name_convert=('/u01/app/oracle/oradata/test/','/u01/app/oracle/oradata/dup/')
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



[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 21:13:44 2015

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

Connected to an idle instance.

SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db/dbs/initdup.ora';

File created.

4.启动辅助实例

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

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015

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


SQL> conn sys/oracle@dup as sysdba
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> show parameter spfile

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
spfile                               string                 /u01/app/oracle/product/10.2.0
                                                            /db/dbs/spfiledup.ora

断开连接

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

这里一定要断开启动辅助实例的会话否则在执行复制操作时会出现如下错误：

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@oracle11g admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/10.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

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

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )




dup =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =dup)
      (UR=A)
    )
  )

[oracle@oracle11g admin]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015

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

SQL> conn sys/oracle@dup as sysdba
Connected.

5.加载或打开目标数据库

[oracle@oracle11g ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:13:44 2015

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size              92275944 bytes
Database Buffers           71303168 bytes
Redo Buffers                2920448 bytes
Database mounted.
Database opened.

6.确保有需要的备份和归档重做日志
对目标数据库（被复制的数据库）进行备份（包含数据文件和归档重做日志）

[oracle@oracle11g admin]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
[oracle@oracle11g admin]$ rman target sys/zzh_2046@test catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 24 15:15:52 2015

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

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

RMAN> backup as backupset database plus archivelog delete all input;


Starting backup at 2015-03-24 15:18:57
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=49 recid=56 stamp=874583978
input archive log thread=1 sequence=50 recid=57 stamp=874583996
input archive log thread=1 sequence=51 recid=58 stamp=874585387
input archive log thread=1 sequence=52 recid=59 stamp=874587721
input archive log thread=1 sequence=53 recid=60 stamp=874588386
input archive log thread=1 sequence=54 recid=61 stamp=874657003
input archive log thread=1 sequence=55 recid=62 stamp=874667860
input archive log thread=1 sequence=56 recid=63 stamp=874667943
input archive log thread=1 sequence=57 recid=64 stamp=874668125
input archive log thread=1 sequence=58 recid=65 stamp=874668205
input archive log thread=1 sequence=59 recid=66 stamp=874841642
input archive log thread=1 sequence=60 recid=67 stamp=874842111
input archive log thread=1 sequence=61 recid=68 stamp=875091192
input archive log thread=1 sequence=62 recid=69 stamp=875177310
input archive log thread=1 sequence=63 recid=70 stamp=875200740
channel ORA_DISK_1: starting piece 1 at 2015-03-24 15:19:03
channel ORA_DISK_1: finished piece 1 at 2015-03-24 15:19:19
piece handle=/u02/ora_test875200742_711 tag=TAG20150324T151901 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:17
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/1_49_870806981.dbf recid=56 stamp=874583978
archive log filename=/u02/1_50_870806981.dbf recid=57 stamp=874583996
archive log filename=/u02/1_51_870806981.dbf recid=58 stamp=874585387
archive log filename=/u02/1_52_870806981.dbf recid=59 stamp=874587721
archive log filename=/u02/1_53_870806981.dbf recid=60 stamp=874588386
archive log filename=/u02/1_54_870806981.dbf recid=61 stamp=874657003
archive log filename=/u02/1_55_870806981.dbf recid=62 stamp=874667860
archive log filename=/u02/1_56_870806981.dbf recid=63 stamp=874667943
archive log filename=/u02/1_57_870806981.dbf recid=64 stamp=874668125
archive log filename=/u02/1_58_870806981.dbf recid=65 stamp=874668205
archive log filename=/u02/1_59_870806981.dbf recid=66 stamp=874841642
archive log filename=/u02/1_60_870806981.dbf recid=67 stamp=874842111
archive log filename=/u02/1_61_870806981.dbf recid=68 stamp=875091192
archive log filename=/u02/1_62_870806981.dbf recid=69 stamp=875177310
archive log filename=/u02/1_63_870806981.dbf recid=70 stamp=875200740
Finished backup at 2015-03-24 15:19:20

Starting backup at 2015-03-24 15:19:20
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 2015-03-24 15:19:21
channel ORA_DISK_1: finished piece 1 at 2015-03-24 15:20:46
piece handle=/u02/ora_test875200761_721 tag=TAG20150324T151920 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup at 2015-03-24 15:20:46

Starting backup at 2015-03-24 15:20:47
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=64 recid=71 stamp=875200847
channel ORA_DISK_1: starting piece 1 at 2015-03-24 15:20:49
channel ORA_DISK_1: finished piece 1 at 2015-03-24 15:20:50
piece handle=/u02/ora_test875200848_731 tag=TAG20150324T152048 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/1_64_870806981.dbf recid=71 stamp=875200847
Finished backup at 2015-03-24 15:20:50

Starting Control File and SPFILE Autobackup at 2015-03-24 15:20:50
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_24/o1_mf_s_875200851_bk242now_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2015-03-24 15:20:55

RMAN> list backup;


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

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
4452    61.69M     DISK        00:00:15     2015-03-24 15:19:17
        BP Key: 4453   Status: AVAILABLE  Compressed: NO  Tag: TAG20150324T151901
        Piece Name: /u02/ora_test875200742_711

  List of Archived Logs in backup set 4452
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    49      833130     2015-03-17 11:59:13 833176     2015-03-17 11:59:38
  1    50      833176     2015-03-17 11:59:38 833183     2015-03-17 11:59:56
  1    51      833183     2015-03-17 11:59:56 834037     2015-03-17 12:23:07
  1    52      834037     2015-03-17 12:23:07 835281     2015-03-17 13:02:01
  1    53      835281     2015-03-17 13:02:01 835767     2015-03-17 13:13:06
  1    54      835767     2015-03-17 13:13:06 867877     2015-03-18 08:16:39
  1    55      867877     2015-03-18 08:16:39 873825     2015-03-18 11:17:38
  1    56      873825     2015-03-18 11:17:38 873875     2015-03-18 11:19:03
  1    57      873875     2015-03-18 11:19:03 873988     2015-03-18 11:22:05
  1    58      873988     2015-03-18 11:22:05 874075     2015-03-18 11:23:25
  1    59      874075     2015-03-18 11:23:25 907518     2015-03-20 11:33:59
  1    60      907518     2015-03-20 11:33:59 928331     2015-03-20 11:41:50
  1    61      928331     2015-03-20 11:41:50 960300     2015-03-23 08:53:08
  1    62      960300     2015-03-23 08:53:08 998814     2015-03-24 08:48:24
  1    63      998814     2015-03-24 08:48:24 1009277    2015-03-24 15:18:57

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
4479    Full    624.66M    DISK        00:01:24     2015-03-24 15:20:45
        BP Key: 4495   Status: AVAILABLE  Compressed: NO  Tag: TAG20150324T151920
        Piece Name: /u02/ora_test875200761_721
  List of Datafiles in backup set 4479
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 1009303    2015-03-24 15:19:21 /u01/app/oracle/oradata/test/system01.dbf
  2       Full 1009303    2015-03-24 15:19:21 /u01/app/oracle/oradata/test/undotbs01.dbf
  3       Full 1009303    2015-03-24 15:19:21 /u01/app/oracle/oradata/test/sysaux01.dbf
  4       Full 1009303    2015-03-24 15:19:21 /u01/app/oracle/oradata/test/users01.dbf
  5       Full 1009303    2015-03-24 15:19:21 /u01/app/oracle/oradata/test/example01.dbf
  6       Full 1009303    2015-03-24 15:19:21 /u01/app/oracle/oradata/test/tspitr01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
4511    3.00K      DISK        00:00:01     2015-03-24 15:20:49
        BP Key: 4518   Status: AVAILABLE  Compressed: NO  Tag: TAG20150324T152048
        Piece Name: /u02/ora_test875200848_731

  List of Archived Logs in backup set 4511
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    64      1009277    2015-03-24 15:18:57 1009334    2015-03-24 15:20:47

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
4529    Full    6.89M      DISK        00:00:02     2015-03-24 15:20:53
        BP Key: 4531   Status: AVAILABLE  Compressed: NO  Tag: TAG20150324T152051
        Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_24/o1_mf_s_875200851_bk242now_.bkp
  Control File Included: Ckp SCN: 1009356      Ckp time: 2015-03-24 15:20:51
  SPFILE Included: Modification time: 2015-03-24 08:48:23

将上面的备份传输到远程主机的相同目录中：


[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/ora_test875200742_711 /u02
The authenticity of host '192.168.56.2 (192.168.56.2)' can't be established.
RSA key fingerprint is fb:1d:33:a6:9e:25:86:6a:a0:44:76:d4:cf:eb:c9:c4.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.56.2' (RSA) to the list of known hosts.
oracle@192.168.56.2's password:
ora_test875200742_711                                                                                                                100%   62MB  10.3MB/s   00:06
[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/ora_test875200761_721 /u02
oracle@192.168.56.2's password:
ora_test875200761_721                                                                                                                100%  625MB   7.7MB/s   01:21
[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/ora_test875200848_731 /u02
oracle@192.168.56.2's password:
ora_test875200848_731                                                                                                                100% 3584     3.5KB/s   00:00
[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_24/o1_mf_s_875200851_bk242now_.bkp /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_24/
oracle@192.168.56.2's password:
o1_mf_s_875200851_bk242now_.bkp

7.执行duplicate命令，如果没有配置自动通道，那么至少手动分配一个辅助实例。给duplicate命令指定nofilenamecheck参数。如果是使用PFILE参数文件启动辅助实例需要指定pfile参数文件，且pfile参数文件必须存储在运行RMAN执行复制的主机上。这里辅助实例使用SPFILE参数文件来启动，并使用自动通道:

[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 21:19: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;

Starting Duplicate Db at 2015-03-24 21:19:54
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=37 devtype=DISK

contents of Memory Script:
{
   set until scn  1009334;
   set newname for datafile  1 to
 "/u01/app/oracle/oradata/dup/system01.dbf";
   set newname for datafile  2 to
 "/u01/app/oracle/oradata/dup/undotbs01.dbf";
   set newname for datafile  3 to
 "/u01/app/oracle/oradata/dup/sysaux01.dbf";
   set newname for datafile  4 to
 "/u01/app/oracle/oradata/dup/users01.dbf";
   set newname for datafile  5 to
 "/u01/app/oracle/oradata/dup/example01.dbf";
   set newname for datafile  6 to
 "/u01/app/oracle/oradata/dup/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 21:19:54
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/dup/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/dup/undotbs01.dbf
restoring datafile 00003 to /u01/app/oracle/oradata/dup/sysaux01.dbf
restoring datafile 00004 to /u01/app/oracle/oradata/dup/users01.dbf
restoring datafile 00005 to /u01/app/oracle/oradata/dup/example01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/dup/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:25
Finished restore at 2015-03-24 21:21:20
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/dup/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u01/app/oracle/oradata/dup/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u01/app/oracle/oradata/dup/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/dup/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=875222496 filename=/u01/app/oracle/oradata/dup/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=875222496 filename=/u01/app/oracle/oradata/dup/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=875222496 filename=/u01/app/oracle/oradata/dup/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=875222496 filename=/u01/app/oracle/oradata/dup/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=875222496 filename=/u01/app/oracle/oradata/dup/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 21:21:24
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=875222496
media recovery complete, elapsed time: 00:00:03
Finished recover at 2015-03-24 21:21:30

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/dup/redo01.log' ) SIZE 50 M  REUSE,
  GROUP  2 ( '/u01/app/oracle/oradata/dup/redo02.log' ) SIZE 50 M  REUSE,
  GROUP  3 ( '/u01/app/oracle/oradata/dup/redo03.log' ) SIZE 50 M  REUSE
 DATAFILE
  '/u01/app/oracle/oradata/dup/system01.dbf'
 CHARACTER SET ZHS16GBK


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

executing command: SET NEWNAME

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

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

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

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

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

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

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

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

database opened
Finished Duplicate Db at 2015-03-24 21:22:03


[oracle@jingyong1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 21:22:52 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> select instance_name from v$instance;

INSTANCE_NAME
----------------
dup

RMAN复制数据库（一）

Posted on 2015年3月27日2015年3月27日 by YongJing(敬勇)

使用RMAN的duplicate命令使用目标数据库（主库）的备份来创建一个副本数据库同时保留原目标数据库副本数据库可以和目标数据库完全相同或者只包含目标数据库中一部分表空间。目标数据库和副本数据库可以是在单独的机器上，也可以是在相同的机器上。

副本数据库是目标数据库的副本可以用于各种目的。例如，可以用来:
.测试备份和恢复过程
.导出那些无意中从生产数据库中删除的表数据并重新导入到生产数据库中

例如，可以将主机host1上的生产数据库复制到主机host2上，并使用主机host2上的副本数据库来练习还原和恢复的同时主机host1上的生产数据库处于运行状态。

复制的副本数据库与备库是有差别的，虽然两种类型的数据库都是使用duplicate命令创建。备库是主库的一个副本可以使用主库的归档重做日志连续或定期地更新。如果主库被损坏或破坏，那么可以对备库执行故障转移将它转换为主库。复制的副本数据库是不能这样使用的，它不能执和故障转移并且不支持各种备库恢复和故障转移选项。

为了进行数据库复制，首先得创建一个辅助实例。为了执行复制，必须使用RMAN连接到目标数据库（主库）和一个启动到nomount状态的辅助实例。

在辅助实例上至少分配一个辅助通道。执行复制的主要工作就是通过辅助通道来完成的，在复制的副本主机上启动一个服务会话。这个辅助通道然后会还原所需要的主库备份，使用它们来创建一个副本数据库，并执行恢复。

只要RMAN可以连接到主数据库和辅助实例，RMAN客户端可以在任何主机上运行。所有用来创建和恢复复制的副本数据库的备份和归档重做日志都必须能被副本主机上的服务会话所访问。如果副本主机与目标主机相同，那么对一目标主机上的备份必须使用主数据库的完整路径名来让副本主机能使用。

当使用磁盘备份时，可以使用以下任意方式来完成这个任务：
.从主数据库主机上将备份手动传输到副本数据库主机上的相同目录下。例如，如果在主数据库主机上备份存储在/dsk1/bkp，那么将它们传输到副本数据库主机上的/dsk1/bkp中。

.从主数据库主机上将备份手动传输到副本数据库主机上的新目录下。例如，如果在主数据库主机上备份存储在/dsk1/bkp，然后将备份传输到副本数据库主机上的/dsk2/dup中。在这里新路径 /dsk2/dup必须在目标主机和副本主机上都能访问。执行catalog命令将这些备份副本登记在副本数据库主机上的RMAN档案库中。

.使用NFS或共享磁盘并确保在副本数据库主机上有相同的路径。例如，NFS的挂载点在所有主机上都是/home/file_server

当使用磁带备份时，必须让存储备份的磁带能让副本数据库主机所访问。通过物理移动磁带上的备份到副本数据库主机所附加的设备上或者通过网络访问磁带服务

作为复制操作的一部分，RMAN自动执行以下步骤：
.为复制的副本数据库创建控制文件
.通过使用所有可用的增量备份和归档重做日志来对副本数据库还原目标数据文件并执行不完全恢复
.重启辅助实例.
.在不完全恢复后使用resetlogs选项来打开数据库并创建联机重做日志（当运行在duplicate … for standby之下时RMAN不会打开数据库)

在执行复制时，RMAN必须执行不完全恢复因为目标数据库的联机重做日志没有被备份并且不能应用到复制的副本数据库。RMAN可以将副本数据库恢复到目标数据库最后归档重做日志所对应的时间点。

当复制数据库时可以执行以下选项:
.可以使用也可以不使用恢复目录来执行duplicate命令
.使用skip readonly子句跳过只读表空间，缺省情况下包含只读表空间。
.使用skip tablespace子句来排除表空间。可以排除system表空间或包含回滚或undo段的表空间之外的任何表空间。
.可以在新主机上创建副本数据库。如果新主机的目录结构与目标主机相同，那么指定nofilenamecheck选项并对复制的数据文件使用目标数据库数据文件名。
.可以将存储在传统文件系统上的目标数据库复制到ASM或OMF中
.缺省情况下，duplicate命令将使用目标数据库的最近备份来创建副本数据库并恢复到归档日志文件所对应的一致性时间点。通过使用set until命令的RUN块或通过有until子句的duplicate命令来让RMAN将复制的副本数据库恢复到当前数据库化身的过去某个时间点。（然而不能使用duplicate命令将复制的副本数据库恢复到目标数据库之前化身的过去某个时间点）
.在相同恢复目录中可以像目标数据库一样来注意复制的副本数据库。因为RMAN给复制的副本数据库生成了新的DBID。注意：如果使用操作系统工具复制目标数据库，那么复制生成的副本数据库的DBID与目标数据库一样。为了在相同的恢复目录中注册副本数据库必须使用DBNEWID工具来改变副本数据库的DBID。
.在有些情况下，可以设置复制的副本数据库的DB_NAME不同于目标数据库的DB_NAME。具体来说，如果在目标数据库相同的Oracle Home目录中复制一个数据库时，那么DB_NAME参数必须不同。如果在与目标数据库不同的Oracle Home目录中复制一个数据库，那么DB_NAME参数在它的Oracle Home中必须是唯一的。

重命名RMAN复制数据库的数据库文件
在复制一个数据库时，RMAN会对数据库文件生成文件名，其中包括控制文件，联机重做日志文件，数据文件和临时文件。

重命名复制数据库的控制文件
确定复制数据库控制文件名称的优先级规则与create controlfile语句使用的一样。当给复制数据库的控制文件选择名称时，确保对辅助数据库设置了正确的参数，否则，可能会覆盖目标数据库的控制文件。

重命名复制数据库的联机重做日志文件
RMAN需要为复制生成的数据库使用新的联机重做日志文件名。可以在duplicate命令中显式指定，也可以让RMAN根据下面的规则来生成。

表1-1 生成联机重做日志文件名的优先级规则

-------------------------------------------------------------------------------------------
优先级       方法                                   结果
-------------------------------------------------------------------------------------------
1        在duplicate命令中指定logfile子句         根据指定条件生成联机重做日志文件

2        设置log_file_name_convert参数            转换目标文件名，例如从log_*转换为duplog_*
                                                  注意可以指定多个转换对

3        设置一个OMF参数db_create_file_dest,      基于设置的参数来转换目标文件名。这些参数
         db_create_online_dest_n或者              的优先级与alter database add logfile语句
         db_recovery_file_dest                    一样

4        没有执行前面的设置                       复制数据库的文件名与目标数据库的文件名相
                                                  同。当使用这种方法时必须指定
                                                  nofilenamecheck选项，并且要将数据库复制到
                                                  不同的主机上这样联机重做日志文件才不会与
                                                  目标数据库的联机重做日志文件冲突
-------------------------------------------------------------------------------------------

优先级规则中优先级高的会覆盖优先级低的。例如，如果指定了logfile子句和log_file_name_convert参数，那么RMAN将会使用logfile子句。如果目标数据库和复制的数据库在相同主机上，那么不要对复制的数据库使用与目标数据库相同名称的联机重做日志。如果复制的数据库与目标数据库在不同主机上，但没有使用nofilenamecheck选项，那么不要对复制的数据库使用与目标数据库相同名称的联机重做日志

重命名RMAN复制数据库的数据文件名
有几种方法用来指定复制数据库的数据文件名。按优先级依次为:
1.在RUN块中使用set newname for datafile命令，包含set newname与duplicate命令。
2.对已经存在的数据文件使用configure auxname来指定新名称。在duplicate命令之前执行configure auxname命令。
3.在duplicate命令中指定db_file_name_convert参数来为没有使用set newname或configure auxname进行重命名的任何数据文件指定文件名转换的规则。在目标实例使用OMF时有db_file_name_convert子句的duplicate命令不能对复制的副本实例生成新的文件名。
4.设置db_file_name_convert参数。在目标实例使用OMF时db_file_name_convert参数不能对复制的副本实例生成新的文件名。它的语法与duplicate命令中的db_file_name_convert参数一样。
5.设置db_create_file_dest参数在指定目录中生成OMF的数据文件

如果没有使用上面的任何规则选项，那么复制的数据库将重用目标数据库的数据文件目录。

阻止对复制数据库的文件名检查
如果可能使用configure auxname,set newname或db_file_name_convert来生成复制的副本数据库文件名在这种情况下，在duplicate命令中指定nofilenamecheck选项可以避免出错。

例如，假设主机A上数据库有两个文件:数据文件1被命名为/oracle/data/file1.f，数据文件2被命名为/oracle/data/file2.f。当将数据库复制到主机B上时，使用配置的通道进行复制：

RUN
{
SET NEWNAME FOR DATAFILE 1 TO /oracle/data/file2.f; # rename df 1 as file2.f
SET NEWNAME FOR DATAFILE 2 TO /oracle/data/file1.f; # rename df 2 as file1.f
DUPLICATE TARGET DATABASE TO newdb;
}

即使你对所有数据文件执行set newname命令，因为复制的文件名仍然会使用目标数据库的数据文件名所以duplicate命令会失败。虽然数据文件1在目标数据库中没有使用/oracle/data/file2.f并且数据文件2在目标数据库中没有使用/oracle/data/file1.f，目标数据库名被另一个复制的数据文件所使用。因此必须指定duplicate … nofilenamecheck来避免错误。

重命名RMAN复制数据库的临时文件
作为复制一个数据库的一部分RMAN会重新创建临时表空间。有几种方法来为复制数据库的临时文件指定目录。按优先级别依次为：
1.在RUN块中使用set newname for tempfile命令这样同时使用set newname和duplicate命令。
2.在duplicate命令中使用db_file_name_convert子句来为没有使用set newname或configure auxname进行重命名的临时文件指定转换规则。当目标实例使用了OMF,db_file_name_convert子句不能用来生成复制数据库的文件名。
3.设置db_file_name_convert参数。db_file_name_convert参数与duplicate命令中的
db_file_name_convert参数有相同的语法和限制
4.设置db_create_file_dest参数来创建OMF的临时文件

RMAN复制数据库时跳过指定表空间
不总是需要复制数据库中的所有表空间。例如，可能只需要从目标数据库中复制一组表空间来生成报告。RMAN的duplicate database可以跳过只读表空间或者当前offline normal的表空间。

复制数据库时跳过只读取表空间
当指定skip readonly时，RMAN不会复制只读表空间的数据文件。在复制完成后，可以查询复制的副本数据库中的视图来判断那些数据文件被跳过。status和enabled列描述了复制数据文件的当前状态。

使用相同目录结构在远程主机上创建复制数据库
在不同主机上复制数据库的最简单情况就是使用相同目录结构。在这种情况下不需要改变初始化参数文件或者不需要为复制的数据文件设置新的文件名。

1.创建辅助实例的密码文件(这里辅助实例名为dup)

[oracle@jingyong1 dbs]$ orapwd file=/u01/app/oracle/product/10.2.0/db/dbs/orapwdup password=oracle entries=10;
[oracle@jingyong1 dbs]$ ls -lrt
-rw-r----- 1 oracle oinstall    2560 Mar 24 14:47 orapwdup

2.创建辅助实例网络连接
修改监听文件，使用静态监听来监听辅助实例

[oracle@jingyong1 admin]$ vi listener.ora

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
     (SID_NAME = dup)
      (ORACLE_HOME =/u01/app/oracle/product/10.2.0/db)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.11)(PORT = 1521))
    )
  )

给辅助实例增加网络服务名

[oracle@jingyong1 admin]$ vi tnsnames.ora
dup =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =dup)
      (UR=A)
    )
  )

测试网络连接

[oracle@jingyong1 admin]$ export ORACLE_SID=dup
[oracle@jingyong1 admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 14:57:08 2015

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

SQL> conn sys/oracle@dup as sysdba
Connected to an idle instance.

3.创建辅助实例的参数文件

[oracle@jingyong1 dbs]$ vi initdup.ora

db_name=dup
db_unique_name=dup
control_files= /u01/app/oracle/oradata/test/control01.ctl
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



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

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 15:05:51 2015

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

SQL> conn sys/oracle@dup as sysdba

SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db/dbs/initdup.ora';

File created.

4.启动辅助实例

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

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 15:05:51 2015

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

SQL> conn sys/oracle@dup as sysdba
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> show parameter spfile

NAME                                 TYPE                   VALUE
------------------------------------ ---------------------- ------------------------------
spfile                               string                 /u01/app/oracle/product/10.2.0
                                                            /db/dbs/spfiledup.ora

断开连接

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

这里一定要断开启动辅助实例的会话否则在执行复制操作时会出现如下错误：

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@oracle11g admin]$ vi tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/10.2.0/db/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

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

TEST =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.2)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = test)
    )
  )




dup =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =dup)
      (UR=A)
    )
  )

[oracle@oracle11g admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 15:49:11 2015

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

SQL> conn sys/oracle@dup as sysdba
Connected.

5.加载或打开目标数据库

[oracle@oracle11g ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 08:48:03 2015

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

SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area  167772160 bytes
Fixed Size                  1272600 bytes
Variable Size              92275944 bytes
Database Buffers           71303168 bytes
Redo Buffers                2920448 bytes
Database mounted.
Database opened.

6.确保有需要的备份和归档重做日志
对目标数据库（被复制的数据库）进行备份（包含数据文件和归档重做日志）

[oracle@oracle11g admin]$ export NLS_DATE_FORMAT='yyyy-mm-dd hh24:mi:ss'
[oracle@oracle11g admin]$ rman target sys/zzh_2046@test catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Tue Mar 24 15:15:52 2015

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

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

RMAN> backup as backupset database plus archivelog delete all input;


Starting backup at 2015-03-24 15:18:57
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=49 recid=56 stamp=874583978
input archive log thread=1 sequence=50 recid=57 stamp=874583996
input archive log thread=1 sequence=51 recid=58 stamp=874585387
input archive log thread=1 sequence=52 recid=59 stamp=874587721
input archive log thread=1 sequence=53 recid=60 stamp=874588386
input archive log thread=1 sequence=54 recid=61 stamp=874657003
input archive log thread=1 sequence=55 recid=62 stamp=874667860
input archive log thread=1 sequence=56 recid=63 stamp=874667943
input archive log thread=1 sequence=57 recid=64 stamp=874668125
input archive log thread=1 sequence=58 recid=65 stamp=874668205
input archive log thread=1 sequence=59 recid=66 stamp=874841642
input archive log thread=1 sequence=60 recid=67 stamp=874842111
input archive log thread=1 sequence=61 recid=68 stamp=875091192
input archive log thread=1 sequence=62 recid=69 stamp=875177310
input archive log thread=1 sequence=63 recid=70 stamp=875200740
channel ORA_DISK_1: starting piece 1 at 2015-03-24 15:19:03
channel ORA_DISK_1: finished piece 1 at 2015-03-24 15:19:19
piece handle=/u02/ora_test875200742_711 tag=TAG20150324T151901 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:17
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/1_49_870806981.dbf recid=56 stamp=874583978
archive log filename=/u02/1_50_870806981.dbf recid=57 stamp=874583996
archive log filename=/u02/1_51_870806981.dbf recid=58 stamp=874585387
archive log filename=/u02/1_52_870806981.dbf recid=59 stamp=874587721
archive log filename=/u02/1_53_870806981.dbf recid=60 stamp=874588386
archive log filename=/u02/1_54_870806981.dbf recid=61 stamp=874657003
archive log filename=/u02/1_55_870806981.dbf recid=62 stamp=874667860
archive log filename=/u02/1_56_870806981.dbf recid=63 stamp=874667943
archive log filename=/u02/1_57_870806981.dbf recid=64 stamp=874668125
archive log filename=/u02/1_58_870806981.dbf recid=65 stamp=874668205
archive log filename=/u02/1_59_870806981.dbf recid=66 stamp=874841642
archive log filename=/u02/1_60_870806981.dbf recid=67 stamp=874842111
archive log filename=/u02/1_61_870806981.dbf recid=68 stamp=875091192
archive log filename=/u02/1_62_870806981.dbf recid=69 stamp=875177310
archive log filename=/u02/1_63_870806981.dbf recid=70 stamp=875200740
Finished backup at 2015-03-24 15:19:20

Starting backup at 2015-03-24 15:19:20
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 2015-03-24 15:19:21
channel ORA_DISK_1: finished piece 1 at 2015-03-24 15:20:46
piece handle=/u02/ora_test875200761_721 tag=TAG20150324T151920 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup at 2015-03-24 15:20:46

Starting backup at 2015-03-24 15:20:47
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=64 recid=71 stamp=875200847
channel ORA_DISK_1: starting piece 1 at 2015-03-24 15:20:49
channel ORA_DISK_1: finished piece 1 at 2015-03-24 15:20:50
piece handle=/u02/ora_test875200848_731 tag=TAG20150324T152048 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/1_64_870806981.dbf recid=71 stamp=875200847
Finished backup at 2015-03-24 15:20:50

Starting Control File and SPFILE Autobackup at 2015-03-24 15:20:50
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_24/o1_mf_s_875200851_bk242now_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2015-03-24 15:20:55

RMAN> list backup;


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

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
4452    61.69M     DISK        00:00:15     2015-03-24 15:19:17
        BP Key: 4453   Status: AVAILABLE  Compressed: NO  Tag: TAG20150324T151901
        Piece Name: /u02/ora_test875200742_711

  List of Archived Logs in backup set 4452
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    49      833130     2015-03-17 11:59:13 833176     2015-03-17 11:59:38
  1    50      833176     2015-03-17 11:59:38 833183     2015-03-17 11:59:56
  1    51      833183     2015-03-17 11:59:56 834037     2015-03-17 12:23:07
  1    52      834037     2015-03-17 12:23:07 835281     2015-03-17 13:02:01
  1    53      835281     2015-03-17 13:02:01 835767     2015-03-17 13:13:06
  1    54      835767     2015-03-17 13:13:06 867877     2015-03-18 08:16:39
  1    55      867877     2015-03-18 08:16:39 873825     2015-03-18 11:17:38
  1    56      873825     2015-03-18 11:17:38 873875     2015-03-18 11:19:03
  1    57      873875     2015-03-18 11:19:03 873988     2015-03-18 11:22:05
  1    58      873988     2015-03-18 11:22:05 874075     2015-03-18 11:23:25
  1    59      874075     2015-03-18 11:23:25 907518     2015-03-20 11:33:59
  1    60      907518     2015-03-20 11:33:59 928331     2015-03-20 11:41:50
  1    61      928331     2015-03-20 11:41:50 960300     2015-03-23 08:53:08
  1    62      960300     2015-03-23 08:53:08 998814     2015-03-24 08:48:24
  1    63      998814     2015-03-24 08:48:24 1009277    2015-03-24 15:18:57

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
4479    Full    624.66M    DISK        00:01:24     2015-03-24 15:20:45
        BP Key: 4495   Status: AVAILABLE  Compressed: NO  Tag: TAG20150324T151920
        Piece Name: /u02/ora_test875200761_721
  List of Datafiles in backup set 4479
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 1009303    2015-03-24 15:19:21 /u01/app/oracle/oradata/test/system01.dbf
  2       Full 1009303    2015-03-24 15:19:21 /u01/app/oracle/oradata/test/undotbs01.dbf
  3       Full 1009303    2015-03-24 15:19:21 /u01/app/oracle/oradata/test/sysaux01.dbf
  4       Full 1009303    2015-03-24 15:19:21 /u01/app/oracle/oradata/test/users01.dbf
  5       Full 1009303    2015-03-24 15:19:21 /u01/app/oracle/oradata/test/example01.dbf
  6       Full 1009303    2015-03-24 15:19:21 /u01/app/oracle/oradata/test/tspitr01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
4511    3.00K      DISK        00:00:01     2015-03-24 15:20:49
        BP Key: 4518   Status: AVAILABLE  Compressed: NO  Tag: TAG20150324T152048
        Piece Name: /u02/ora_test875200848_731

  List of Archived Logs in backup set 4511
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    64      1009277    2015-03-24 15:18:57 1009334    2015-03-24 15:20:47

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
4529    Full    6.89M      DISK        00:00:02     2015-03-24 15:20:53
        BP Key: 4531   Status: AVAILABLE  Compressed: NO  Tag: TAG20150324T152051
        Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_24/o1_mf_s_875200851_bk242now_.bkp
  Control File Included: Ckp SCN: 1009356      Ckp time: 2015-03-24 15:20:51
  SPFILE Included: Modification time: 2015-03-24 08:48:23

将上面的备份传输到远程主机的相同目录中：

[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/ora_test875200742_711 /u02
The authenticity of host '192.168.56.2 (192.168.56.2)' can't be established.
RSA key fingerprint is fb:1d:33:a6:9e:25:86:6a:a0:44:76:d4:cf:eb:c9:c4.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added '192.168.56.2' (RSA) to the list of known hosts.
oracle@192.168.56.2's password:
ora_test875200742_711                                                                                                                100%   62MB  10.3MB/s   00:06
[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/ora_test875200761_721 /u02
oracle@192.168.56.2's password:
ora_test875200761_721                                                                                                                100%  625MB   7.7MB/s   01:21
[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u02/ora_test875200848_731 /u02
oracle@192.168.56.2's password:
ora_test875200848_731                                                                                                                100% 3584     3.5KB/s   00:00
[oracle@jingyong1 u02]$ scp -r oracle@192.168.56.2:/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_24/o1_mf_s_875200851_bk242now_.bkp /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_24/
oracle@192.168.56.2's password:
o1_mf_s_875200851_bk242now_.bkp

[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 19:19:31 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 19:19:46
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 19:19:46
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 19:20:52
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=875215264 filename=/u01/app/oracle/oradata/test/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=875215264 filename=/u01/app/oracle/oradata/test/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=875215264 filename=/u01/app/oracle/oradata/test/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=875215264 filename=/u01/app/oracle/oradata/test/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=875215264 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 19:20:53
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=875215263
media recovery complete, elapsed time: 00:00:01
Finished recover at 2015-03-24 19:20:57

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=875215374

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

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

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

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

datafile 2 switched to datafile copy
input datafile copy recid=1 stamp=875215374 filename=/u01/app/oracle/oradata/test/undotbs01.dbf
datafile 3 switched to datafile copy
input datafile copy recid=2 stamp=875215374 filename=/u01/app/oracle/oradata/test/sysaux01.dbf
datafile 4 switched to datafile copy
input datafile copy recid=3 stamp=875215374 filename=/u01/app/oracle/oradata/test/users01.dbf
datafile 5 switched to datafile copy
input datafile copy recid=4 stamp=875215374 filename=/u01/app/oracle/oradata/test/example01.dbf
datafile 6 switched to datafile copy
input datafile copy recid=5 stamp=875215374 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:23:05


[oracle@jingyong1 dbs]$ sqlplus / as sysdba

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 24 19:26:06 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> select instance_name from v$instance;

INSTANCE_NAME
----------------
dup

RMAN表空间按时间点恢复（五)

Posted on 2015年3月27日 by YongJing(敬勇)

使用自己的辅助实例来执行TSPITR
在执行TSPITR时Oracle建议使用RMAN来管理辅助实例的创建和推毁。然而，创建和使用自己的辅助实例也是支持的。在执行TSPITR时想要练习控制通道就会这样做。RMAN自动辅助实例使用目标数据库配置的通道来作为辅助实例的通道并在还原时使用。如果需要不同的通道设置，并且不想在目标数据库上使用configure来改变原来的通道设置，可以创建自己的辅助实例。

创建一个合适的Oracle实例来作为辅助实例要求你执行以下步骤:
1.创建辅助实例密码文件
2.创建辅助实例初始化参数文件
3.检查辅助实例网络连接

步骤1：创建辅助实例密码文件

[oracle@oracle11g dbs]$ orapwd file='/u01/app/oracle/10.2.0/db/dbs/orapwaux' password=system entries=10

步骤2：创建辅助实例参数文件
在要运行辅助实例的机器上为辅助实例创建一个客户端初始化参数文件。例如，假设参数文件存储在/u01/app/oracle/product/10.2.0/db/dbs/initaux.ora。要注意的是在参数文件中像db_file_name_convert,log_file_name_convert和control_files都是引用服务端的路径。

下面的例子显示了执行TSPITR可能要对辅助实例所设置的一些参数:

[oracle@oracle11g dbs]$ vi initaux.ora

db_name=test
db_unique_name=_test
control_files= /u01/app/oracle/oradata/auxiliary/control01.ctl
db_file_name_convert=('/u01/app/oracle/oradata/test/','/u01/app/oracle/oradata/auxiliary/')
log_file_name_convert=('/u01/app/oracle/oradata/test/','/u01/app/oracle/oradata/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

步骤3：检查辅助实例的网络连接
辅助实例必须要有一个有效的网络服务名。在处理之前使用SQL*Plus来检查是否能连接到辅助实例。编辑tnsnames.ora文件增加网络服务名aux_test

[oracle@jingyong1 admin]$ vi tnsnames.ora

aux_test =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =aux)
    )


[oracle@oracle11g dbs]$ export ORACLE_SID=aux
[oracle@oracle11g dbs]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 17 12:40:52 2015

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

SQL> conn / as sysdba
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> show parameter service

NAME             TYPE                     VALUE
---------------- ----------------------   ---------
service_names    string                    _test


[oracle@oracle11g dbs]$ export ORACLE_SID=aux
[oracle@oracle11g dbs]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 17 12:51:52 2015

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

SQL> conn sys/system@aux_test as sysdba
ERROR:
ORA-12528: TNS:listener: all appropriate instances are blocking new connections

数据库在nomount状态下动态监听无法识别连接者身份，会拒绝一切连接，所以报ORA-12528错误，解决此问题有两种方法，一种是去掉默认的动态监听然后创建静态监听，第二种方法是修改tnsname.ora中的内容增加(UR=A)，相对而言，第二种方法简单实用。

[oracle@jingyong1 admin]$ vi  tnsnames.ora
aux_test =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST =192.168.56.11)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME =_test)
      (UR=A)
    )
  )

[oracle@jingyong1 admin]$ export ORACLE_SID=aux

[oracle@jingyong1 admin]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 17 10:00:50 2015

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

SQL> conn sys/system@aux_test  as sysdba
Connected.
SQL> show parameter service

NAME             TYPE                     VALUE
---------------- ----------------------   ---------
service_names    string                    _test

经过测试通过网络服务名aux_test可以连接到自己所创建的辅助实例。

下面使用自己创建的辅助实例来执行tspitr来将表空间tspitr恢复到logseq=51的时间点
步骤1：启动辅助实例为nomount状态
在开始执行RMAN TSPITR之前，使用SQL*Plus连接到辅助实例并启动到nomount状态，如果需要可以指定参数文件。

[oracle@oracle11g dbs]$ export ORACLE_SID=aux
[oracle@oracle11g dbs]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 17 12:40:52 2015

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.
SQL> conn / as sysdba
Connected to an idle instance
SQL> startup nomount pfile='/u01/app/oracle/product/10.2.0/db/dbs/initaux.ora'
ORACLE instance started.

Total System Global Area  113246208 bytes
Fixed Size                  1272288 bytes
Variable Size              58721824 bytes
Database Buffers           50331648 bytes
Redo Buffers                2920448 bytes

因为辅助实例没有控制文件，只能将辅助实例启动到nomount状态。执行TSPITR不会创建控制文件或者尝加载或打开辅助实例。

步骤2：连接到目标实例，辅助实例和恢复目录
启动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 12:59:22 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)

步骤3：执行recover tablespace命令
现在可以执行TSPITR命令。在最简单的情况下，只需要执行recover tablespace … until命令:
再次对表tspitr执行truncate操作:

SQL> select sequence#,status from v$log;

 SEQUENCE# STATUS
---------- ----------------
        49 INACTIVE
        50 INACTIVE
        51 CURRENT

SQL> truncate table tspitr;

Table truncated.

SQL> select count(*) from tspitr;

  COUNT(*)
----------
         0

执行recover tablespace tspitr until logseq 51;
RMAN> run
2> {
3> allocate auxiliary channel c1 device type disk;
4> recover tablespace tspitr until logseq 51;
5> }

allocated channel: c1
channel c1: sid=35 devtype=DISK

Starting recover at 17-MAR-15

contents of Memory Script:
{
# set the until clause
set until  logseq 51 thread 1;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog after controlfile restore
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 17-MAR-15

channel c1: starting datafile backupset restore
channel c1: restoring control file
channel c1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_17/o1_mf_s_874583957_bjh9npcf_.bkp
channel c1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_17/o1_mf_s_874583957_bjh9npcf_.bkp tag=TAG20150317T115917
channel c1: restore complete, elapsed time: 00:00:02
output filename=/u01/app/oracle/oradata/auxiliary/control01.ctl
Finished restore at 17-MAR-15

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

starting full resync of recovery catalog
full resync complete

contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until  logseq 51 thread 1;
plsql < <<-- tspitr_2
declare
  sqlstatement       varchar2(512);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
  sqlstatement := 'alter tablespace '||  'TSPITR' ||' offline for recover';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement);
exception
  when offline_not_needed then
    null;
end; >>>;
# set a destination filename for restore
set newname for datafile  1 to
 "/u01/app/oracle/oradata/auxiliary/system01.dbf";
# set a destination filename for restore
set newname for datafile  2 to
 "/u01/app/oracle/oradata/auxiliary/undotbs01.dbf";
# set a destination tempfile
set newname for tempfile  1 to
 "/u01/app/oracle/oradata/auxiliary/temp01.dbf";
# set a destination filename for restore
set newname for datafile  6 to
 "/u01/app/oracle/oradata/test/tspitr01.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile  1, 2, 6;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile  1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile  2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile  6 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace  "TSPITR", "SYSTEM", "UNDOTBS1" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
#           datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace TSPITR offline for recover

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME


Starting restore at 17-MAR-15

channel c1: starting datafile backupset restore
channel c1: specifying datafile(s) to restore from backup set
restoring datafile 00001 to /u01/app/oracle/oradata/auxiliary/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/auxiliary/undotbs01.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/test/tspitr01.dbf
channel c1: reading from backup piece /u02/ora_test874583877_571
channel c1: restored backup piece 1
piece handle=/u02/ora_test874583877_571 tag=TAG20150317T115756
channel c1: restore complete, elapsed time: 00:01:05
Finished restore at 17-MAR-15

datafile 6 switched to datafile copy
input datafile copy recid=21 stamp=874588453 filename=/u01/app/oracle/oradata/test/tspitr01.dbf

sql statement: alter database datafile  1 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  6 online

Starting recover at 17-MAR-15

starting media recovery

archive log thread 1 sequence 49 is already on disk as file /u02/1_49_870806981.dbf
archive log thread 1 sequence 50 is already on disk as file /u02/1_50_870806981.dbf
channel c1: starting archive log restore to default destination
channel c1: restoring archive log
archive log thread=1 sequence=48
channel c1: reading from backup piece /u02/ora_test874583954_581
channel c1: restored backup piece 1
piece handle=/u02/ora_test874583954_581 tag=TAG20150317T115914
channel c1: restore complete, elapsed time: 00:00:01
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_48_870806981.dbf thread=1 sequence=48
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_48_870806981.dbf recid=56 stamp=874588454
archive log filename=/u02/1_49_870806981.dbf thread=1 sequence=49
archive log filename=/u02/1_50_870806981.dbf thread=1 sequence=50
media recovery complete, elapsed time: 00:00:02
Finished recover at 17-MAR-15

database opened

contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"sys/system@aux_test as sysdba\" point_in_time_recover=y tablespaces=
 TSPITR file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"sys/zzh_2046@test as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace TSPITR online";
sql "alter tablespace  TSPITR offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog after tspitr finished
resync catalog;
}
executing Memory Script


Export: Release 10.2.0.5.0 - Production on Tue Mar 17 13:14:50 2015

Copyright (c) 1982, 2007, 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
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
For tablespace TSPITR ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                         TSPITR
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully without warnings.
host command complete

database closed
database dismounted
Oracle instance shut down


Import: Release 10.2.0.5.0 - Production on Tue Mar 17 13:15:12 2015

Copyright (c) 1982, 2007, 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

Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing TSPITR's objects into TSPITR
. . importing table                       "TSPITR"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete

sql statement: alter tablespace TSPITR online

sql statement: alter tablespace  TSPITR offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

starting full resync of recovery catalog
full resync complete
auxiliary instance file /u01/app/oracle/oradata/auxiliary/control01.ctl deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/system01.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/undotbs01.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/temp01.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/redo01.log deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/redo02.log deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/redo03.log deleted
Finished recover at 17-MAR-15

RMAN> sql 'alter tablespace tspitr online';

sql statement: alter tablespace tspitr online
starting full resync of recovery catalog
full resync complete

SQL> select count(*) from tspitr;

  COUNT(*)
----------
     50641

RMAN表空间按时间点恢复（四）

Posted on 2015年3月27日2015年3月27日 by YongJing(敬勇)

使用镜像副本提高RMAN TSPITR的性能
在执行TSPITR时，通过让RMAN使用磁盘上已经存在的恢复集和辅助集数据文件镜像副本，而不是从备份中还原它们将会显著提高TSPITR的性能。可以使用configure auxname命令来使用恢复集数据文件或辅助集数据文件的镜像副本或者使用set newname命令使用辅助集数据文件镜像副本，来告诉RMAN可能存在数据文件的镜像副本。

具体的详细细节依赖于使用的命令和文件是否是辅助集或恢复集文件，通常来说，在指定目录中存在合适的镜像副本，那么在执行TSPITR时，RMAN会从目标实例中RMAN档案库中注消镜像副本并将其登记到辅助实例的控制文件中。辅助实例将使用镜像副本来执行按时间点恢复。

使用configure auxname来使用恢复集镜像副本执行TSPITR
在执行TSPIRT时，RMAN在指定的AUXNAME目录查找数据文件，使用比目标时间还早的检查点SCN来查看是否存在数据文件的镜像备份。如果找到了镜像备份，将会被TSPITR使用。否则，数据文件会被还原和恢复到原始目录中，通过auxname指定目录中的任何文件都不会被改变或删除。

SQL> truncate table tspitr;

Table truncated.

SQL> select count(*) from tspitr;

  COUNT(*)
----------
         0

执行恢复命令

RMAN> run
2> {
3> configure auxname for datafile '/u01/app/oracle/oradata/test/tspitr01.dbf' to '/u01/app/oracle/oradata/auxiliary/tspitr01.dbf';
4> recover tablespace tspitr until logseq 32 auxiliary destination '/u01/app/oracle/oradata/auxiliary';
5> }

auxiliary name for datafile 6 set to: /u01/app/oracle/oradata/auxiliary/tspitr01.dbf
new RMAN configuration parameters are successfully stored
starting full resync of recovery catalog
full resync complete

Starting recover at 2015-03-16 21:11:05
using channel ORA_DISK_1

Creating automatic instance, with SID='okpE'

initialization parameters used for automatic instance:
db_name=TEST
compatible=10.2.0.5.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_TEST_okpE
sga_target=180M
processes=50
#No auxiliary parameter file used
db_create_file_dest=/u01/app/oracle/oradata/auxiliary
control_files=/u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_okpE.f


starting up automatic instance TEST

Oracle instance started

Total System Global Area     188743680 bytes

Fixed Size                     1272720 bytes
Variable Size                 62915696 bytes
Database Buffers             121634816 bytes
Redo Buffers                   2920448 bytes
Automatic instance created

contents of Memory Script:
{
# set the until clause
set until  logseq 32 thread 1;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog after controlfile restore
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 2015-03-16 21:11:10
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=47 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp tag=TAG20150316T103739
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_okpE.f
Finished restore at 2015-03-16 21:11:13

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

starting full resync of recovery catalog
full resync complete

contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until  logseq 32 thread 1;
plsql < <<-- tspitr_2
declare
  sqlstatement       varchar2(512);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
  sqlstatement := 'alter tablespace '||  'TSPITR' ||' offline for recover';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement);
exception
  when offline_not_needed then
    null;
end; >>>;
# set an omf destination filename for restore
set newname for clone datafile  1 to new;
# set an omf destination filename for restore
set newname for clone datafile  2 to new;
# set an omf destination tempfile
set newname for clone tempfile  1 to new;
# set a destination filename for restore
set newname for datafile  6 to
 "/u01/app/oracle/oradata/test/tspitr01.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile  1, 2, 6;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile  1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile  2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile  6 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace  "TSPITR", "SYSTEM", "UNDOTBS1" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
#           datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace TSPITR offline for recover

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_OKPE/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 2015-03-16 21:11:21
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/auxiliary/TSPITR_TEST_OKPE/datafile/o1_mf_system_%u_.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_OKPE/datafile/o1_mf_undotbs1_%u_.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/test/tspitr01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test874492578_361
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test874492578_361 tag=TAG20150316T103617
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:55
Finished restore at 2015-03-16 21:12:17

datafile 1 switched to datafile copy
input datafile copy recid=20 stamp=874530737 filename=/u01/app/oracle/oradata/auxiliary/TSPITR_TEST_OKPE/datafile/o1_mf_system_bjfomtoh_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=21 stamp=874530737 filename=/u01/app/oracle/oradata/auxiliary/TSPITR_TEST_OKPE/datafile/o1_mf_undotbs1_bjfomttc_.dbf

sql statement: alter database datafile  1 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  6 online

Starting recover at 2015-03-16 21:12:17
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 31 is already on disk as file /u02/1_31_870806981.dbf
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=30
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test874492656_371
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test874492656_371 tag=TAG20150316T103735
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_30_870806981.dbf thread=1 sequence=30
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_30_870806981.dbf recid=38 stamp=874530741
archive log filename=/u02/1_31_870806981.dbf thread=1 sequence=31
media recovery complete, elapsed time: 00:00:01
Finished recover at 2015-03-16 21:12:23

database opened

contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/u01/app/oracle/10.2.0/db/bin/oracle\)\(ARGV0=oracleokpE\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=okpE^'\)\)\(CONNECT_DATA=\(SID=okpE\)\)\) as sysdba\" point_in_time_recover=y tablespaces=
 TSPITR file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"sys/zzh_2046@test as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace TSPITR online";
sql "alter tablespace  TSPITR offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog after tspitr finished
resync catalog;
}
executing Memory Script


Export: Release 10.2.0.5.0 - Production on Mon Mar 16 21:12:50 2015

Copyright (c) 1982, 2007, 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
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
For tablespace TSPITR ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                         TSPITR
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully without warnings.
host command complete

database closed
database dismounted
Oracle instance shut down


Import: Release 10.2.0.5.0 - Production on Mon Mar 16 21:13:12 2015

Copyright (c) 1982, 2007, 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

Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing TSPITR's objects into TSPITR
. . importing table                       "TSPITR"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete

sql statement: alter tablespace TSPITR online

sql statement: alter tablespace  TSPITR offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

starting full resync of recovery catalog
full resync complete

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_okpE.f deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_OKPE/datafile/o1_mf_system_bjfomtoh_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_OKPE/datafile/o1_mf_undotbs1_bjfomttc_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_OKPE/datafile/o1_mf_temp_bjfop5sn_.tmp deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_OKPE/onlinelog/o1_mf_1_bjfooq75_.log deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_OKPE/onlinelog/o1_mf_2_bjfootjb_.log deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_OKPE/onlinelog/o1_mf_3_bjfooxd8_.log deleted
Finished recover at 2015-03-16 21:13:17

从上面的auxiliary name for datafile 6 set to: /u01/app/oracle/oradata/auxiliary/tspitr01.dbf
信息可以看到确实为恢复集数据文件使用了镜像副本来执行tspitr。

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TSPITR                         OFFLINE

7 rows selected.

RMAN> sql 'alter tablespace tspitr online';

sql statement: alter tablespace tspitr online
starting full resync of recovery catalog
full resync complete

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TSPITR                         ONLINE

7 rows selected.

SQL> select count(*) from tspitr;

  COUNT(*)
----------
     50678

configure auxname的主要作用是一种基本技术通过消除还原时间来提高TSPITR的性能。如果有预计要执行TSPITR操作的表空间，可以在备份程序中维护一组表空间相关数据文件的镜像副本，并定期将镜像副本更新到TSPITR所要恢复到的目标时间之前的时间点：
1.当设置这种策略时，为文件配置一次auxname
2.定期执行 backup as copy datafile n format auxname来维护镜像副本的更新，或者为了更好的性能使用增量更新备份策略。
3.当需要执行TSPITR时，指定自上次更新镜像副本之后的一个目标时间。

在计划使用镜像副本执行TSPIRT时，记住你不可能提前知道表空间将要使用的镜像副本。在要执行TSPITR的表空间和其它表空间之间存在关联时，要将相关的表空间加入到最终的恢复集中并且其它表空间可能存在于辅助集中。应该为每个数据文件配置一个auxname让其成为恢复集中的一部分并经常更新所有数据文件的镜像副本。

如果没有正确的估计恢复集所包含的表空间或者因为开销原因不想对恢复集中所有表空间维护镜像副本，可以使用只维护所有数据文件的一个子集的策略。如果只准备了所有数据文件的一个子集所对应的镜像副本，TSPITR的处理仍然相同。这个处理过程时间会变长，因为RMAN必须将没有镜像副本的恢复集中的数据文件恢复到它们原来的目录中。

当使用configure auxname来重命名恢复集文件时重命名方法的优先级仍然是要遵守的。对于相同文件的set newname命令将会覆盖configure auxname命令行为。

执行set newname和configure auxname来使用辅助集镜像副本执行TSPITR与恢复集数据文件一样，configure auxname命令给辅助集数据文件镜像副本设置一个永久的替代目录，
set newname在RUN块中设置替代目录。然而，RMAN对辅助集数据文件的处理不同于恢复集数据文件。

如果使用set newname来给辅助集数据文件指定新目录并且在这个目录中存在镜像副本且镜像副本的SCN是可以用来执行TSPITR操作，那么镜像副本会被使用。如果在指定的新目录中没有可用的镜像副本，那么RMAN会从备份中还原一个可用的副本（如果存在的镜像副本的SCN号在TSPITR的目标时间之后，那么会使用还原的文件覆盖存在数据文件）。

如果使用configure auxname来给辅助集数据文件指定新目录并且这个目录中存在镜像副本且镜像副本的SCN可以用来执行TSPITR操作，那么镜像副本会被使用。如果在指定的新目录中没有可用的镜像副本，那么RMAN会从备份中还原数据文件。

不管镜像副本是在执行TSPITR之前被创建，还是在执行TSPITR时从备份中还原生成，与所有辅助集文件一样，在TSPITR成功执行完后会被删除或者当TSPITR失败后保留用来进行诊断。

RMAN表空间按时间点恢复（三)

Posted on 2015年3月27日 by YongJing(敬勇)

重命名TSPITR辅助集数据文件
不像恢复集数据文件通常会被存储在它们原始目录中，辅助集数据文件不能覆盖目标数据库中相关文件。如果不对辅助集文件指定一个新目录而是使用原始目录，那么当RMAN试图覆盖原始目录中的相关文件时，会发现这些文件正在使用，那么TSPITR操作会失败。

为辅助集数据文件指定目录最简单的方法是为TSPITR指定一个辅助目录。然而RMAN支持两种方法来控制辅助集数据文件的目录：一是使用set newname来对单个文件指定新文件名，二是使用db_file_name_convert参数提将目标数据库中的数据文件名转换为辅助数据库中的数据文件名。

尽管你打算使用这些方法来给特定文件指定目录，仍然建议你给recover tablespace命令使用auxliary destination参数。如果你忽略了重命名一些辅助集数据文件，这将确保TSPITR操作仍然执行成功。存储在辅助目录中的任何文件不会被重命名。

使用set newname来重命名TSPITR辅助集数据文件
为了使用set newname命令来为辅助集数据文件指定新的文件名，在RUN块中使用recover tablespace命令，并且使用set newname命令来重命名文件。例如,下面我们对表空间tspitr执行tspitr将表空间恢复到logseq=32的时间点，并使用set newname将辅助集中的数据文件’ /u01/app/oracle/oradata/test/system01.dbf’重命名为’ /u01/app/oracle/oradata/auxiliary/system01.dbf’:

再次对表tspitr执行truncate操作:

SQL> truncate table tspitr;

Table truncated.

SQL> select count(*) from tspitr;

  COUNT(*)
----------
         0

执行恢复命令

RMAN> run
2> {
3> set newname for datafile '/u01/app/oracle/oradata/test/system01.dbf' to '/u01/app/oracle/oradata/auxiliary/system01.dbf';
4> recover tablespace tspitr until logseq 32 auxiliary destination '/u01/app/oracle/oradata/auxiliary';
5> }

executing command: SET NEWNAME

Starting recover at 2015-03-16 19:34:58
using channel ORA_DISK_1

Creating automatic instance, with SID='pqny'

initialization parameters used for automatic instance:
db_name=TEST
compatible=10.2.0.5.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_TEST_pqny
sga_target=180M
processes=50
#No auxiliary parameter file used
db_create_file_dest=/u01/app/oracle/oradata/auxiliary
control_files=/u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_pqny.f


starting up automatic instance TEST

Oracle instance started

Total System Global Area     188743680 bytes

Fixed Size                     1272720 bytes
Variable Size                 62915696 bytes
Database Buffers             121634816 bytes
Redo Buffers                   2920448 bytes
Automatic instance created

contents of Memory Script:
{
# set the until clause
set until  logseq 32 thread 1;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog after controlfile restore
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 2015-03-16 19:34:59
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=47 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp tag=TAG20150316T103739
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_pqny.f
Finished restore at 2015-03-16 19:35:02

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

starting full resync of recovery catalog
full resync complete

contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until  logseq 32 thread 1;
# set a destination filename for restore
set newname for datafile  1 to
 "/u01/app/oracle/oradata/auxiliary/system01.dbf";
# set an omf destination filename for restore
set newname for clone datafile  2 to new;
# set an omf destination tempfile
set newname for clone tempfile  1 to new;
# set a destination filename for restore
set newname for datafile  6 to
 "/u01/app/oracle/oradata/test/tspitr01.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile  1, 2, 6;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile  1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile  2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile  6 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace  "TSPITR", "SYSTEM", "UNDOTBS1" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
#           datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script

executing command: SET until clause

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PQNY/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 2015-03-16 19:35:09
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/auxiliary/system01.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PQNY/datafile/o1_mf_undotbs1_%u_.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/test/tspitr01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test874492578_361
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test874492578_361 tag=TAG20150316T103617
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:07
Finished restore at 2015-03-16 19:36:18

datafile 1 switched to datafile copy
input datafile copy recid=19 stamp=874524979 filename=/u01/app/oracle/oradata/auxiliary/system01.dbf
datafile 2 switched to datafile copy
input datafile copy recid=20 stamp=874524979 filename=/u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PQNY/datafile/o1_mf_undotbs1_bjfhzg5k_.dbf

sql statement: alter database datafile  1 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  6 online

Starting recover at 2015-03-16 19:36:21
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 31 is already on disk as file /u02/1_31_870806981.dbf
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=30
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test874492656_371
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test874492656_371 tag=TAG20150316T103735
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_30_870806981.dbf thread=1 sequence=30
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_30_870806981.dbf recid=38 stamp=874524983
archive log filename=/u02/1_31_870806981.dbf thread=1 sequence=31
media recovery complete, elapsed time: 00:00:03
Finished recover at 2015-03-16 19:36:27

database opened

contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/u01/app/oracle/10.2.0/db/bin/oracle\)\(ARGV0=oraclepqny\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=pqny^'\)\)\(CONNECT_DATA=\(SID=pqny\)\)\) as sysdba\" point_in_time_recover=y tablespaces=
 TSPITR file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"sys/zzh_2046@test as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace TSPITR online";
sql "alter tablespace  TSPITR offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog after tspitr finished
resync catalog;
}
executing Memory Script


Export: Release 10.2.0.5.0 - Production on Mon Mar 16 19:36:56 2015

Copyright (c) 1982, 2007, 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
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
For tablespace TSPITR ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                         TSPITR
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully without warnings.
host command complete

database closed
database dismounted
Oracle instance shut down


Import: Release 10.2.0.5.0 - Production on Mon Mar 16 19:37:17 2015

Copyright (c) 1982, 2007, 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

Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing TSPITR's objects into TSPITR
. . importing table                       "TSPITR"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete

sql statement: alter tablespace TSPITR online

sql statement: alter tablespace  TSPITR offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

starting full resync of recovery catalog
full resync complete

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_pqny.f deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/system01.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PQNY/datafile/o1_mf_undotbs1_bjfhzg5k_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PQNY/datafile/o1_mf_temp_bjfj2d21_.tmp deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PQNY/onlinelog/o1_mf_1_bjfj1w22_.log deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PQNY/onlinelog/o1_mf_2_bjfj1z44_.log deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PQNY/onlinelog/o1_mf_3_bjfj249p_.log deleted
Finished recover at 2015-03-16 19:37:26

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TSPITR                         OFFLINE

7 rows selected.

RMAN> sql 'alter tablespace tspitr online';

sql statement: alter tablespace tspitr online
starting full resync of recovery catalog
full resync complete

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TSPITR                         ONLINE

7 rows selected.

SQL> select count(*) from tspitr;

  COUNT(*)
----------
     50678

从上面的结果可以看到对表空间tspitr恢复成功。

使用db_file_name_convert来重命名辅助集数据文件
如果不想对所有辅助集数据文件使用一个辅助目录，但也不想为每个文件单独命名，可以在辅助实例中设置db_file_name_convert参数。只有在以下两种情况下使用这个参数:
1.如果会为RMAN自动管理辅助实例创建自己的参数文件。
2.如果将要创建单独的辅助实例。
在辅助实例中的db_file_name_convert参数的作用是用来根据目标实例相关文件的原始文件名在辅助实例中生成相应的文件名。这个参数值由字符对列表组成。
例如，假设目标实例包含以下文件:
.system表空间中的/u01/app/oracle/oradata/test/system01.dbf
.undotbs表空间中的/u01/app/oracle/oradata/test/undotbs01.dbf

并且想将这些相关文件重新存储在辅助实例的’/u01/app/oracle/oradata/auxiliary ‘目录中，那么在辅助实例参数文件中增加以下记录：
DB_FILE_NAME_CONVERT=(‘/u01/app/oracle/oradata/test/ ‘, ‘/u01/app/oracle/oradata/auxiliary ‘)

在辅助实例中相关文件的新文件名就是如下形式:
/u01/app/oracle/oradata/auxiliary /system01.dbf和/u01/app/oracle/oradata/auxiliary /undotbs01.dbf

最重要的是要记住db_file_name_convert参数必须在辅助实例参数文件中进行设置。

如果辅助实例是手动创建的，将db_file_name_convert参数加入到辅助实例参数文件中。

注意仍然可以使用set newname或configure auxname来重命名单个辅助集数据文件。如果文件不匹配由db_file_name_convert参数所提供的匹配模式，那么文件是不会被重命名的。可以使用有auxiliary destination参数的recover tablespace命令来确保所有辅助集数据文件被发送到指定目录中。如果没有对辅助实例中的文件提供新文件名的命名方法，那么执行TSPITR将会失败。

使用db_file_name_convert重命名ASM OMF数据文件
当目标实例使用OMF管理数据文件对于辅助实例使用db_file_name_convert参数不能被用来控制文件的新文件名。当目标实例使用OMF管理数据文件时，通过替目标实例OMF文件名的相应字符串不能在辅助实例上生成有效的OMF文件名。当使用ASM OMF管理数据文件时，RMAN会将这些无效的文件名转换为有效的文件名。

为了避免这个问题，使用其它的支持选项来为OMF文件生成新文件名（包括存储在ASM中的数据文件）：
1.使用辅助目录
2.在辅助实例中使用db_create_file_dest参数来为没有使用set newname或configure auxname指定新文件名的所有辅助实例文件指定目录
3.对于ASM文件，可以使用set newname为单个文件指定辅助实例可以访问的磁盘组（并允许数据库使用指定的磁盘组生成文件名）。例如:

RUN {
SET NEWNAME FOR DATAFILE 1 TO "+DISK2";
SET NEWNAME FOR DATAFILE 2 TO "+DISK3";
RECOVER TABLESPACE users, tools
UNTIL LOGSEQ 1300 THREAD 1
AUXILIARY DESTINATION '/disk1/auxdest';
}

执行TSPITR时重命名临时文件
临时文件被认为数据库辅助集的一部分。当创建辅助实例时，可以使用set newname for tempfile,db_file_name_convert或auxiliary destination来重命名临时文件。当打开辅助数据库时，会使用适用的重命名规则来创建临时文件。当清除辅助实例时，临时文件会和其它的辅助实例文件一起被删除。

TSPITR文件重命名方法之间的优先顺序
不同的重命名文件方法遵守以下的优先顺序：
1.set newname
2.configure auxname
3.db_file_name_convert
4.recover tablespace命令中的auxiliary destination参数

在同时使用几种重命名文件方法时，优先级高的会覆盖优先级低的重命名方法（例如，在目标数据库上执行recover tablespace … auxiliary destination时，也使用configure auxname为一些辅助集数据文件重命名文件名时，configure auxname会覆盖recover tablespace … auxiliary destination）。
可以使用show auxname命令来查看任何当前的configure auxname设置。

设置辅助实例控制文件目录
如果使用客户端参数文件可以为辅助实例设置控制文件目录。可以设置control_files参数来指定你要存储控制文件的目录。

如果没有为控制文件显式地指定目录，如果在执行TSPITR时使用了auxiliary destination参数，RMAN将会把控制文件存放在辅助目录中。如果在执行TSPITR时没有使用auxiliary destination参数，辅助实例控制文件将会存储在操作系统特定目录中（在Unix中，ORACLE_HOME/rdbms/admin/params_auxinit.ora)

不管将辅助实例控制文件存储在什么目录中，在TSPITR操作成功执行完后都是会被删除的。因为控制文件相对较小，RMAN在创建辅助控制文件遇到错误的概率是很小的，但如果创建控制文件的目录没有足够的空间，TSPITR操作将会失败。

设置辅助实例联机重做日志目录
如果在辅助实例参数文件中设置了log_file_name_convert参数，这个参数将会决定联机重做日志文件的存储目录。否则，如果RMAN正使用辅助目录和管理辅助实例，将会在辅助目录中创建联机重做日志文件。
注意，如果没有使用log_file_name_convert或auxiliary destination来指定联机重做日志文件目录，在试图创建联机重做日志文件时TSPITR操作会失败。即使在参数文件中设置了db_file_create_dest或者log_file_create_dest参数，在执行TSPITR时也不会对辅助实例创建联机重做日志文件。

使用log_file_name_convert重命名ASM OMF重做日志文件
当目标实例使用OMF时，在辅助实例中设置log_file_name_convert参数不能用来为重做日志文件生成新文件名。当目标实例使用OMF时，不能通过替换目标实例OMF文件名中的相关字符串来为辅助实例生成有效的OMF文件名。当使用ASM OMF时，RMAN在指定磁盘组中使用模式匹配的磁盘组名来生成有效的文件名。

为了避免这种问题，可以使用其它支持的方法为OMF重做日志文件生成新文件名（包括存储在ASM中的文件）：
1.使用辅助目录
2.在辅助实例中设置db_create_file_dest,db_recovery_file_dest或db_create_online_log_dest_n参数来指定目录

RMAN表空间按时间点恢复（二)

Posted on 2015年3月27日 by YongJing(敬勇)

使用自定义自动辅助实例执行TSPITR
1.重命名或重新放置恢复集的数据文件，因此在TSPITR执行完成后，被恢复表空间所包含的数据文件将不会存储在其原始位置（例如当包含表空间的原始磁盘不可使用时）。
2.对一些或所有的辅助数据文件指定辅助目录之外的目录。如果在单个磁盘上没有足够的空间来处理所有辅助集文件时可以这样做。
3.提前设置镜像副本备份数据文件，来避免从备份中还原而提高TSPITR的速度。
4.对辅助实例使用不同的通道配置
5.为RMAN管理的辅助实例指定不同的初始化参数

使用set newname来重命名TSPITR恢复集中的数据文件
如果不想恢复集中的数据文件被还原和恢复到它们原来的目录中，在RUN块中使用set newname命令来为从备份中还原和恢复的数据文件指定新的存储目录。

例如下面我们将为恢复集中表空间tspitr的数据文件‘/u01/app/oracle/oradata/test/tspitr01.dbf’重命名为
’ /u01/app/oracle/oradata/auxiliary/tspitr01.dbf’

再次执行truncate table tspitr操作:

SQL> truncate table tspitr;

Table truncated.

SQL> select count(*) from tspitr;

  COUNT(*)
----------
         0

下面再次将表空间tspitr恢复到logseq=32的时间点，我这里因为使用了恢复目录，在第一次执行tspitr后，可以再次恢复到logseq=32这个时间点。

RMAN> run
2> {
3> set newname for datafile '/u01/app/oracle/oradata/test/tspitr01.dbf' to ' /u01/app/oracle/oradata/auxiliary/tspitr01.dbf';
4> recover tablespace tspitr until logseq 32 auxiliary destination '/u01/app/oracle/oradata/auxiliary';
5> }

executing command: SET NEWNAME

Starting recover at 2015-03-16 17:42:10
using channel ORA_DISK_1

Creating automatic instance, with SID='pjzc'

initialization parameters used for automatic instance:
db_name=TEST
compatible=10.2.0.5.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_TEST_pjzc
sga_target=180M
processes=50
#No auxiliary parameter file used
db_create_file_dest=/u01/app/oracle/oradata/auxiliary
control_files=/u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_pjzc.f


starting up automatic instance TEST

Oracle instance started

Total System Global Area     188743680 bytes

Fixed Size                     1272720 bytes
Variable Size                 62915696 bytes
Database Buffers             121634816 bytes
Redo Buffers                   2920448 bytes
Automatic instance created

contents of Memory Script:
{
# set the until clause
set until  logseq 32 thread 1;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog after controlfile restore
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 2015-03-16 17:42:11
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=47 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp tag=TAG20150316T103739
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_pjzc.f
Finished restore at 2015-03-16 17:42:15

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

starting full resync of recovery catalog
full resync complete

contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until  logseq 32 thread 1;
plsql < <<-- tspitr_2
declare
  sqlstatement       varchar2(512);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
  sqlstatement := 'alter tablespace '||  'TSPITR' ||' offline for recover';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement);
exception
  when offline_not_needed then
    null;
end; >>>;
# set an omf destination filename for restore
set newname for clone datafile  1 to new;
# set an omf destination filename for restore
set newname for clone datafile  2 to new;
# set an omf destination tempfile
set newname for clone tempfile  1 to new;
# set a destination filename for restore
set newname for datafile  6 to
 "/u01/app/oracle/oradata/test/tspitr01.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile  1, 2, 6;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile  1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile  2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile  6 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace  "TSPITR", "SYSTEM", "UNDOTBS1" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
#           datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace TSPITR offline for recover

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 2015-03-16 17:42:23
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/auxiliary/TSPITR_TEST_PJZC/datafile/o1_mf_system_%u_.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/datafile/o1_mf_undotbs1_%u_.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/test/tspitr01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test874492578_361
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test874492578_361 tag=TAG20150316T103617
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:56
Finished restore at 2015-03-16 17:43:21

datafile 1 switched to datafile copy
input datafile copy recid=19 stamp=874518201 filename=/u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/datafile/o1_mf_system_bjf9d12k_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=20 stamp=874518201 filename=/u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/datafile/o1_mf_undotbs1_bjf9d14c_.dbf

sql statement: alter database datafile  1 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  6 online

Starting recover at 2015-03-16 17:43:22
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 31 is already on disk as file /u02/1_31_870806981.dbf
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=30
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test874492656_371
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test874492656_371 tag=TAG20150316T103735
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_30_870806981.dbf thread=1 sequence=30
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_30_870806981.dbf recid=38 stamp=874518204
archive log filename=/u02/1_31_870806981.dbf thread=1 sequence=31
media recovery complete, elapsed time: 00:00:02
Finished recover at 2015-03-16 17:43:27

database opened

contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/u01/app/oracle/10.2.0/db/bin/oracle\)\(ARGV0=oraclepjzc\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=pjzc^'\)\)\(CONNECT_DATA=\(SID=pjzc\)\)\) as sysdba\" point_in_time_recover=y tablespaces=
 TSPITR file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"sys/zzh_2046@test as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace TSPITR online";
sql "alter tablespace  TSPITR offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog after tspitr finished
resync catalog;
}
executing Memory Script


Export: Release 10.2.0.5.0 - Production on Mon Mar 16 17:43:51 2015

Copyright (c) 1982, 2007, 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
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
For tablespace TSPITR ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                         TSPITR
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully without warnings.
host command complete

database closed
database dismounted
Oracle instance shut down


Import: Release 10.2.0.5.0 - Production on Mon Mar 16 17:44:12 2015

Copyright (c) 1982, 2007, 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

Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing TSPITR's objects into TSPITR
. . importing table                       "TSPITR"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete

sql statement: alter tablespace TSPITR online

sql statement: alter tablespace  TSPITR offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

starting full resync of recovery catalog
full resync complete

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_pjzc.f deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/datafile/o1_mf_system_bjf9d12k_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/datafile/o1_mf_undotbs1_bjf9d14c_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/datafile/o1_mf_temp_bjf9gdqh_.tmp deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/onlinelog/o1_mf_1_bjf9fz9y_.log deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/onlinelog/o1_mf_2_bjf9g25c_.log deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_PJZC/onlinelog/o1_mf_3_bjf9g4yj_.log deleted
Finished recover at 2015-03-16 17:44:21

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TSPITR                         OFFLINE

7 rows selected.

将表空间tspitr联机

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TSPITR                         ONLINE

7 rows selected.

SQL> select count(*) from tspitr;

  COUNT(*)
----------
     50678

如果set newname命令指定的数据文件名与目标数据库中的有效数据文件名存在冲突，那么RMAN在执行recover命令时没返回错误信息。有效的数据文件不会被覆盖。

注意直到真实执行recover tablespace … until操作为止，RMAN不会检测使用set newname所设置的文件名与当前数据文件名之间的冲突。在真实执行recover tablespace … until操作时会检测这种冲突。

RMAN表空间按时间点恢复（一)

Posted on 2015年3月26日2015年3月27日 by YongJing(敬勇)

一.表空间按时间点恢复
RMAN的自动表空间按时间点恢复（TSPITR）能让你将数据库中的一个或多个表空间快速恢复到之前的某个时间点，而不会影响数据库中的其它表空间和对象。

为了有效地使用表空间按时间点恢复（TSPITR），需要理解你需要解决的问题是什么，使用TSPITR的主要原因是什么，在执行TSPITR时RMAN将会执行那些操作，何时以及如何使用TSPITR，使用TSPITR有那些限制。

二.执行TSPITR的操作步骤
为了使用RMAN和自动辅助实例来对恢复集执行TSPITR,需要对TSPITR进行一些准备工作，然后执行recover tablespace命令来指定要恢复的表空间和按时间点恢复的目标时间和辅助目标（如果需要的话）。
执行TSPITR，RMAN将会执行以下步骤:
1.如果没有连接到辅助实例，RMAN将创建一个辅助实例，启动并连接到辅助实例。
2.在目标数据库中将要被恢复的表空间置于脱机状态。
3.对辅助实例使用恢复目标时间之前的控制文件备份来还原控制文件。
4.从恢复集和辅助集中将数据文件还原到辅助实例。还原的数据文件可以存储在你指定的目录中或者是文件的原始目录中（对于恢复集文件）或者在辅助目录中（对于辅助集文件，如果你使用了有auxliary destination参数的recover tablespace命令)。
5.在辅助实例中将数据文件还原和恢复到指定的目标时间点。
6.使用resetlogs选项将辅助实例打开
7.导出目标数据库中要恢复表空间的数据字典元数据
8.关闭辅助实例
9.在目标数据库中执行switch命令，因为目标数据库的控制文件现在指示的是在辅助实例中被恢复的恢复集中的数据文件
10.将辅助数据库实的数据字典元数据导入到目标数据库中让恢复的对象可以被访问。
11.删除所有辅助集数据文件

执行完以上步骤，TSPITR处理就已经完成了。对于目标数据库恢复集中的数据文件已经被返回到指定的恢复目标时间点。

三.TSPITR的执行方式
选择好要恢复的表空间和恢复目标时间后，就可以执行RMAN的TSPITR。在执行TSPITR时可以有以下不同的选择:
1.完全自动执行TSPITR–在指定辅助目录后就可以让RMAN来管理TSPITR的所有方面。这是执行TSPITR最简单的方式，除非你需要在执行TSPITR后更好的控制恢复集文件的目录或者在执行TSPITR时控制辅助集文件或者控制通道配置或者控制辅助实例的一些其它方面，否则建议使用这种方式来执行TSPITR。
2.使用自定义自动辅助实例执行TSPITR–在完全自动TSPITR的基础上，自定义一个或多个方面的RMAN行为，比如辅助集目录或者恢复集文件，或者指定初始化参数或者辅助实例的通道配置等。
3.使用自己的辅助实例执行TSPITR–在这种情况下要手动设置，启动，停止和清除辅助实例，也可以使用自动辅助实例自定义TSPITR中的一些方法来管理TSPITR。

完全自动执行TSPITR
当完全自动执行TSPITR时，让RMAN来管理整个过程，并且有两个条件:
1.必须给RMAN指定辅助目录让辅助实例的辅助集数据文件和其它文件使用。
2.必须在目标实例上配置任何需要的通道（当目标实例执行TSPITR时，辅助实例使用相同的通道配置）。
对于TSPITR的基础配置在目标数据库上进行。当执行TSPITR时，恢复集数据文件会被写到目标数据库的当前目录。当从备份中还原文件时在目标数据库上有效的相同配置通道将被辅助实例所用。辅助集数据文件和其它辅助实例文件，会被存储在辅助目录中。
自动TSPITR的辅助目录
oracle建议在使用辅助实例时使用辅助目录。即使你使用其它的方法来重命名一些或者所有的辅助集数据文件，通过设置auxliary destination参数来为没有指定名称的辅助集数据文件提供一个默认的目录。为了指定辅助目录，在磁盘上找到有足够空间的目录来处理辅助集数据文件。在recover tablespace命令中使用auxliary destination参数来指定辅助目录。
这里我使用oracle 10g进行完全自动执行TSPITR的实验,下面来准备实验环境：

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - Prod
PL/SQL Release 10.2.0.5.0 - Production
CORE    10.2.0.5.0      Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

SQL> select log_mode from v$database;

LOG_MODE
------------
ARCHIVELOG

创建实验表空间tspitr，用户tspitr,实验表tspitr:

SQL> create tablespace   tspitr datafile '/u01/app/oracle/oradata/test/tspitr01.dbf' size 100M autoextend off extent management local segment space management auto;

Tablespace created.

SQL> create user tspitr identified by "tspitr" default tablespace tspitr temporary tablespace temp;

User created.

SQL> grant connect,resource to tspitr;

Grant succeeded.

这里为了实例的方便使用给tspitr用户授予了DBA权限

SQL> grant dba to tspitr;

Grant succeeded.

SQL> conn tspitr/tspitr
Connected.
SQL> create table tspitr as select * from dba_objects;

Table created.
SQL> select count(*) from tspitr;

  COUNT(*)
----------
     50678

SQL> select sequence#, status from v$log;

 SEQUENCE# STATUS
---------- ----------------
        28 INACTIVE
        29 CURRENT
        27 INACTIVE

在创建完实验表tspitr后，记录一下当前的logseq号为29。并对数据库进行一次全备份。

[root@oracle11g ~]# su - oracle
[oracle@oracle11g ~]$ export NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
[oracle@oracle11g ~]$ rman target sys/zzh_2046@test catalog rman/rman@jy

Recovery Manager: Release 10.2.0.5.0 - Production on Mon Mar 16 10:34:12 2015

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

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

RMAN> backup as backupset database plus archivelog delete all input;


Starting backup at 16-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=13 recid=20 stamp=872692763
input archive log thread=1 sequence=14 recid=21 stamp=873390068
input archive log thread=1 sequence=15 recid=22 stamp=873477705
input archive log thread=1 sequence=16 recid=23 stamp=873557208
input archive log thread=1 sequence=17 recid=24 stamp=873628193
input archive log thread=1 sequence=18 recid=25 stamp=873881475
input archive log thread=1 sequence=19 recid=26 stamp=873882506
input archive log thread=1 sequence=20 recid=27 stamp=873886639
input archive log thread=1 sequence=21 recid=28 stamp=873886719
input archive log thread=1 sequence=22 recid=29 stamp=873969562
input archive log thread=1 sequence=23 recid=30 stamp=873969604
input archive log thread=1 sequence=24 recid=31 stamp=873974555
input archive log thread=1 sequence=25 recid=32 stamp=873974650
input archive log thread=1 sequence=26 recid=33 stamp=874234950
input archive log thread=1 sequence=27 recid=34 stamp=874315417
input archive log thread=1 sequence=28 recid=35 stamp=874486326
input archive log thread=1 sequence=29 recid=36 stamp=874492547
channel ORA_DISK_1: starting piece 1 at 16-MAR-15
channel ORA_DISK_1: finished piece 1 at 16-MAR-15
piece handle=/u02/ora_test874492549_351 tag=TAG20150316T103548 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/1_13_870806981.dbf recid=20 stamp=872692763
archive log filename=/u02/1_14_870806981.dbf recid=21 stamp=873390068
archive log filename=/u02/1_15_870806981.dbf recid=22 stamp=873477705
archive log filename=/u02/1_16_870806981.dbf recid=23 stamp=873557208
archive log filename=/u02/1_17_870806981.dbf recid=24 stamp=873628193
archive log filename=/u02/1_18_870806981.dbf recid=25 stamp=873881475
archive log filename=/u02/1_19_870806981.dbf recid=26 stamp=873882506
archive log filename=/u02/1_20_870806981.dbf recid=27 stamp=873886639
archive log filename=/u02/1_21_870806981.dbf recid=28 stamp=873886719
archive log filename=/u02/1_22_870806981.dbf recid=29 stamp=873969562
archive log filename=/u02/1_23_870806981.dbf recid=30 stamp=873969604
archive log filename=/u02/1_24_870806981.dbf recid=31 stamp=873974555
archive log filename=/u02/1_25_870806981.dbf recid=32 stamp=873974650
archive log filename=/u02/1_26_870806981.dbf recid=33 stamp=874234950
archive log filename=/u02/1_27_870806981.dbf recid=34 stamp=874315417
archive log filename=/u02/1_28_870806981.dbf recid=35 stamp=874486326
archive log filename=/u02/1_29_870806981.dbf recid=36 stamp=874492547
Finished backup at 16-MAR-15

Starting backup at 16-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 16-MAR-15
channel ORA_DISK_1: finished piece 1 at 16-MAR-15
piece handle=/u02/ora_test874492578_361 tag=TAG20150316T103617 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:15
Finished backup at 16-MAR-15

Starting backup at 16-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=30 recid=37 stamp=874492654
channel ORA_DISK_1: starting piece 1 at 16-MAR-15
channel ORA_DISK_1: finished piece 1 at 16-MAR-15
piece handle=/u02/ora_test874492656_371 tag=TAG20150316T103735 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02
channel ORA_DISK_1: deleting archive log(s)
archive log filename=/u02/1_30_870806981.dbf recid=37 stamp=874492654
Finished backup at 16-MAR-15

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

RMAN> list backup ;


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

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
1197    106.98M    DISK        00:00:23     2015-03-16 10:36:12
        BP Key: 1198   Status: AVAILABLE  Compressed: NO  Tag: TAG20150316T103548
        Piece Name: /u02/ora_test874492549_351

  List of Archived Logs in backup set 1197
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    13      479270     2015-02-13 11:15:05 507890     2015-02-26 14:39:21
  1    14      507890     2015-02-26 14:39:21 537468     2015-03-03 16:21:06
  1    15      537468     2015-03-03 16:21:06 565660     2015-03-04 16:41:41
  1    16      565660     2015-03-04 16:41:41 597853     2015-03-05 14:46:22
  1    17      597853     2015-03-05 14:46:22 627695     2015-03-06 10:29:51
  1    18      627695     2015-03-06 10:29:51 662451     2015-03-09 08:51:08
  1    19      662451     2015-03-09 08:51:08 663522     2015-03-09 09:08:25
  1    20      663522     2015-03-09 09:08:25 666107     2015-03-09 10:17:18
  1    21      666107     2015-03-09 10:17:18 666153     2015-03-09 10:18:39
  1    22      666153     2015-03-09 10:18:39 692585     2015-03-10 09:19:20
  1    23      692585     2015-03-10 09:19:20 692853     2015-03-10 09:20:04
  1    24      692853     2015-03-10 09:20:04 695060     2015-03-10 10:42:34
  1    25      695060     2015-03-10 10:42:34 695112     2015-03-10 10:44:09
  1    26      695112     2015-03-10 10:44:09 726710     2015-03-13 11:02:26
  1    27      726710     2015-03-13 11:02:26 759353     2015-03-14 09:23:34
  1    28      759353     2015-03-14 09:23:34 786212     2015-03-16 08:52:00
  1    29      786212     2015-03-16 08:52:00 789814     2015-03-16 10:35:43

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1226    Full    612.91M    DISK        00:01:14     2015-03-16 10:37:32
        BP Key: 1244   Status: AVAILABLE  Compressed: NO  Tag: TAG20150316T103617
        Piece Name: /u02/ora_test874492578_361
  List of Datafiles in backup set 1226
  File LV Type Ckp SCN    Ckp Time            Name
  ---- -- ---- ---------- ------------------- ----
  1       Full 789843     2015-03-16 10:36:18 /u01/app/oracle/oradata/test/system01.dbf
  2       Full 789843     2015-03-16 10:36:18 /u01/app/oracle/oradata/test/undotbs01.dbf
  3       Full 789843     2015-03-16 10:36:18 /u01/app/oracle/oradata/test/sysaux01.dbf
  4       Full 789843     2015-03-16 10:36:18 /u01/app/oracle/oradata/test/users01.dbf
  5       Full 789843     2015-03-16 10:36:18 /u01/app/oracle/oradata/test/example01.dbf
  6       Full 789843     2015-03-16 10:36:18 /u01/app/oracle/oradata/test/tspitr01.dbf

BS Key  Size       Device Type Elapsed Time Completion Time
------- ---------- ----------- ------------ -------------------
1268    2.50K      DISK        00:00:01     2015-03-16 10:37:37
        BP Key: 1275   Status: AVAILABLE  Compressed: NO  Tag: TAG20150316T103735
        Piece Name: /u02/ora_test874492656_371

  List of Archived Logs in backup set 1268
  Thrd Seq     Low SCN    Low Time            Next SCN   Next Time
  ---- ------- ---------- ------------------- ---------- ---------
  1    30      789814     2015-03-16 10:35:43 789871     2015-03-16 10:37:33

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ -------------------
1286    Full    6.80M      DISK        00:00:01     2015-03-16 10:37:40
        BP Key: 1288   Status: AVAILABLE  Compressed: NO  Tag: TAG20150316T103739
        Piece Name: /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp
  Control File Included: Ckp SCN: 789893       Ckp time: 2015-03-16 10:37:39
  SPFILE Included: Modification time: 2015-03-16 08:52:02

模拟误操作:
记录当前时间点的logseq号为31

SQL> select sequence#, status from v$log;

 SEQUENCE# STATUS
---------- ----------------
        31 CURRENT
        29 INACTIVE
        30 INACTIVE

进行日志切换，记录truncate操作之前的logseq号为32，并执行truncate操作

SQL> alter system switch logfile;

System altered.

SQL> select sequence#, status from v$log;

 SEQUENCE# STATUS
---------- ----------------
        31 ACTIVE
        32 CURRENT
        30 INACTIVE

SQL> truncate table tspitr;

Table truncated.

SQL> alter system switch logfile;

System altered.

SQL> select sequence#, status from v$log;

 SEQUENCE# STATUS
---------- ----------------
        31 ACTIVE
        32 ACTIVE
        33 CURRENT

在执行truncate table tspitr操作之后，进行日志切换，并记录truncate操作之后的当前logseq号为33。我们这里的恢复目标是将表空间tspitr恢复到logseq=32的时间点。为了执行自动RMAN TSPITR，启动RMAN客户端，连接到目标数据库和恢复目录（如果有的话）。注意，为了使用自动TSPITR在启动RMAN客户端时不要连接到辅助实例。如果在执行recover tablespace命令时，RMAN连接到一个辅助实例，RMAN会假设你将试管理自己单独的辅助实例。

创建辅助目录:

[oracle@oracle11g oradata]$ mkdir auxiliary
[oracle@oracle11g oradata]$ ls -lrt
total 8
drwxr-x--- 2 oracle oinstall 4096 Mar 16 10:22 test
drwxr-xr-x 2 oracle oinstall 4096 Mar 16 10:55 auxiliary

如果已经配置了通道，RMAN可以在主实例上从备份中执行还原，那么通过执行recover tablespace …until …命令就能执行TSPITR了。这里使用目标数据库中的缺省磁盘通道。

执行下面的命令将表空间tspitr恢复到logseq=32的时间点:

RMAN> recover tablespace tspitr until logseq 32 auxiliary destination '/u01/app/oracle/oradata/auxiliary';

Starting recover at 2015-03-16 11:27:37
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=143 devtype=DISK

Creating automatic instance, with SID='hswB'

initialization parameters used for automatic instance:
db_name=TEST
compatible=10.2.0.5.0
db_block_size=8192
db_files=200
db_unique_name=tspitr_TEST_hswB
sga_target=180M
processes=50
#No auxiliary parameter file used
db_create_file_dest=/u01/app/oracle/oradata/auxiliary
control_files=/u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_hswB.f


starting up automatic instance TEST

Oracle instance started

Total System Global Area     188743680 bytes

Fixed Size                     1272720 bytes
Variable Size                 62915696 bytes
Database Buffers             121634816 bytes
Redo Buffers                   2920448 bytes
Automatic instance created

contents of Memory Script:
{
# set the until clause
set until  logseq 32 thread 1;
# restore the controlfile
restore clone controlfile;
# mount the controlfile
sql clone 'alter database mount clone database';
# archive current online log for tspitr to a resent until time
sql 'alter system archive log current';
# avoid unnecessary autobackups for structural changes during TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(FALSE); end;';
# resync catalog after controlfile restore
resync catalog;
}
executing Memory Script

executing command: SET until clause

Starting restore at 2015-03-16 11:27:38
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: sid=47 devtype=DISK

channel ORA_AUX_DISK_1: starting datafile backupset restore
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: reading from backup piece /u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874492659_bjdjhno1_.bkp tag=TAG20150316T103739
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
output filename=/u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_hswB.f
Finished restore at 2015-03-16 11:27:41

sql statement: alter database mount clone database

sql statement: alter system archive log current

sql statement: begin dbms_backup_restore.AutoBackupFlag(FALSE); end;

starting full resync of recovery catalog
full resync complete

contents of Memory Script:
{
# generated tablespace point-in-time recovery script
# set the until clause
set until  logseq 32 thread 1;
plsql < <<-- tspitr_2
declare
  sqlstatement       varchar2(512);
  offline_not_needed exception;
  pragma exception_init(offline_not_needed, -01539);
begin
  sqlstatement := 'alter tablespace '||  'TSPITR' ||' offline for recover';
  krmicd.writeMsg(6162, sqlstatement);
  krmicd.execSql(sqlstatement);
exception
  when offline_not_needed then
    null;
end; >>>;
# set an omf destination filename for restore
set newname for clone datafile  1 to new;
# set an omf destination filename for restore
set newname for clone datafile  2 to new;
# set an omf destination tempfile
set newname for clone tempfile  1 to new;
# set a destination filename for restore
set newname for datafile  6 to
 "/u01/app/oracle/oradata/test/tspitr01.dbf";
# rename all tempfiles
switch clone tempfile all;
# restore the tablespaces in the recovery set plus the auxilliary tablespaces
restore clone datafile  1, 2, 6;
switch clone datafile all;
#online the datafiles restored or flipped
sql clone "alter database datafile  1 online";
#online the datafiles restored or flipped
sql clone "alter database datafile  2 online";
#online the datafiles restored or flipped
sql clone "alter database datafile  6 online";
# make the controlfile point at the restored datafiles, then recover them
recover clone database tablespace  "TSPITR", "SYSTEM", "UNDOTBS1" delete archivelog;
alter clone database open resetlogs;
# PLUG HERE the creation of a temporary tablespace if export fails due to lack
# of temporary space.
# For example in Unix these two lines would do that:
#sql clone "create tablespace aux_tspitr_tmp
#           datafile ''/tmp/aux_tspitr_tmp.dbf'' size 500K";
}
executing Memory Script

executing command: SET until clause

sql statement: alter tablespace TSPITR offline for recover

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

renamed temporary file 1 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_temp_%u_.tmp in control file

Starting restore at 2015-03-16 11:27:54
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/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_system_%u_.dbf
restoring datafile 00002 to /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_undotbs1_%u_.dbf
restoring datafile 00006 to /u01/app/oracle/oradata/test/tspitr01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test874492578_361
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test874492578_361 tag=TAG20150316T103617
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:01:05
Finished restore at 2015-03-16 11:29:00

datafile 1 switched to datafile copy
input datafile copy recid=19 stamp=874495741 filename=/u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_system_bjdmfw0k_.dbf
datafile 2 switched to datafile copy
input datafile copy recid=20 stamp=874495741 filename=/u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_undotbs1_bjdmfw23_.dbf

sql statement: alter database datafile  1 online

sql statement: alter database datafile  2 online

sql statement: alter database datafile  6 online

Starting recover at 2015-03-16 11:29:01
using channel ORA_AUX_DISK_1

starting media recovery

archive log thread 1 sequence 31 is already on disk as file /u02/1_31_870806981.dbf
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=30
channel ORA_AUX_DISK_1: reading from backup piece /u02/ora_test874492656_371
channel ORA_AUX_DISK_1: restored backup piece 1
piece handle=/u02/ora_test874492656_371 tag=TAG20150316T103735
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:02
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_30_870806981.dbf thread=1 sequence=30
channel clone_default: deleting archive log(s)
archive log filename=/u01/app/oracle/10.2.0/db/dbs/arch1_30_870806981.dbf recid=38 stamp=874495743
archive log filename=/u02/1_31_870806981.dbf thread=1 sequence=31
media recovery complete, elapsed time: 00:00:01
Finished recover at 2015-03-16 11:29:05

database opened

contents of Memory Script:
{
# export the tablespaces in the recovery set
host 'exp userid =\"/@\(DESCRIPTION=\(ADDRESS=\(PROTOCOL=beq\)\(PROGRAM=/u01/app/oracle/10.2.0/db/bin/oracle\)\(ARGV0=oraclehswB\)\(ARGS=^'\(DESCRIPTION=\(LOCAL=YES\)\(ADDRESS=\(PROTOCOL=beq\)\)\)^'\)\(ENVS=^'ORACLE_SID=hswB^'\)\)\(CONNECT_DATA=\(SID=hswB\)\)\) as sysdba\" point_in_time_recover=y tablespaces=
 TSPITR file=
tspitr_a.dmp';
# shutdown clone before import
shutdown clone immediate
# import the tablespaces in the recovery set
host 'imp userid =\"sys/zzh_2046@test as sysdba\" point_in_time_recover=y file=
tspitr_a.dmp';
# online/offline the tablespace imported
sql "alter tablespace TSPITR online";
sql "alter tablespace  TSPITR offline";
# enable autobackups in case user does open resetlogs from RMAN after TSPITR
sql 'begin dbms_backup_restore.AutoBackupFlag(TRUE); end;';
# resync catalog after tspitr finished
resync catalog;
}
executing Memory Script


Export: Release 10.2.0.5.0 - Production on Mon Mar 16 11:29:30 2015

Copyright (c) 1982, 2007, 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
Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set
Note: table data (rows) will not be exported

About to export Tablespace Point-in-time Recovery objects...
For tablespace TSPITR ...
. exporting cluster definitions
. exporting table definitions
. . exporting table                         TSPITR
. exporting referential integrity constraints
. exporting triggers
. end point-in-time recovery
Export terminated successfully without warnings.
host command complete

database closed
database dismounted
Oracle instance shut down


Import: Release 10.2.0.5.0 - Production on Mon Mar 16 11:29:46 2015

Copyright (c) 1982, 2007, 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

Export file created by EXPORT:V10.02.01 via conventional path
About to import Tablespace Point-in-time Recovery objects...
import done in ZHS16GBK character set and AL16UTF16 NCHAR character set
. importing SYS's objects into SYS
. importing TSPITR's objects into TSPITR
. . importing table                       "TSPITR"
. importing SYS's objects into SYS
Import terminated successfully without warnings.
host command complete

sql statement: alter tablespace TSPITR online

sql statement: alter tablespace  TSPITR offline

sql statement: begin dbms_backup_restore.AutoBackupFlag(TRUE); end;

starting full resync of recovery catalog
full resync complete

Removing automatic instance
Automatic instance removed
auxiliary instance file /u01/app/oracle/oradata/auxiliary/cntrl_tspitr_TEST_hswB.f deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_system_bjdmfw0k_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_undotbs1_bjdmfw23_.dbf deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/datafile/o1_mf_temp_bjdmjfov_.tmp deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/onlinelog/o1_mf_1_bjdmj1o6_.log deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/onlinelog/o1_mf_2_bjdmj44r_.log deleted
auxiliary instance file /u01/app/oracle/oradata/auxiliary/TSPITR_TEST_HSWB/onlinelog/o1_mf_3_bjdmj6f1_.log deleted
Finished recover at 2015-03-16 11:29:59

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TSPITR                         OFFLINE

7 rows selected.

TSPITR成功执行完成，RMAN会将表空间脱机，从备份中还原并在辅助实例中将表空间恢复到指定的目标时间点。在整个过程执行完成后，表空间仍然是脱机状态。所有的辅助集数据文件和其它的辅助实例文件会从辅助目录中删除。

TSPITR执行成功后需要执行的操作:
1.必须要备份恢复的表空间然后将表空间置于联机状态。
2.在TSPITR执行成功后立即备份恢复成功的表空间是非常重要的。在对表空间执行TSPITR后，在TSPITR恢复目标时间之前的表空间备份将不能再使用。如果不对恢复的表空间进行备份就开始使用，你将在没有这些表空间备份的情况下运行数据库。对于这个例子，执行以下命令来备份tspitr表空间或者对整个数据库进行备份:

RMAN> backup tablespace tspitr;

Starting backup at 2015-03-16 11:40:53
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=00006 name=/u01/app/oracle/oradata/test/tspitr01.dbf
channel ORA_DISK_1: starting piece 1 at 2015-03-16 11:40:54
channel ORA_DISK_1: finished piece 1 at 2015-03-16 11:40:55
piece handle=/u02/ora_test874496454_391 tag=TAG20150316T114053 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2015-03-16 11:40:55

Starting Control File and SPFILE Autobackup at 2015-03-16 11:40:55
piece handle=/u01/app/oracle/flash_recovery_area/TEST/autobackup/2015_03_16/o1_mf_s_874496456_bjdn69mh_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 2015-03-16 11:40:59

然后可以安全的将表空间置于联机状态:


RMAN> sql 'alter tablespace tspitr online';

sql statement: alter tablespace tspitr online
starting full resync of recovery catalog
full resync complete

现在恢复的表空间就可以供用户使用了。

SQL> select tablespace_name,status from dba_tablespaces;

TABLESPACE_NAME                STATUS
------------------------------ ---------
SYSTEM                         ONLINE
UNDOTBS1                       ONLINE
SYSAUX                         ONLINE
TEMP                           ONLINE
USERS                          ONLINE
EXAMPLE                        ONLINE
TSPITR                         ONLINE

7 rows selected.

SQL> show user
USER is "TSPITR"
SQL> select count(*) from tspitr;

  COUNT(*)
----------
     50678

Redhat Linux两台主机之间设置NFS挂载的步骤

Posted on 2015年3月26日 by YongJing(敬勇)

为了测试Oracle RMAN使用PFILE参数文件在异机上执行duplicate命令，需要将辅助实例pfile参数文件通过NFS来让执行RMAN命令的主机应访问，所以需要配置NFS，这里只介绍了最基本的配置方法。
一、安装nfs
一般redhat是默认安装了nfs服务的，如果非默认安装且取消勾选nfs的话，需要挂载iso或下载安装包手动安装来进行安装，这里不再赘述。

二、在被共享目录所在主机配置/etc/exports
nfs允许挂载的目录及权限需在文件/etc/exports中进行定义。例如，我们要将参数文件所在目录/u01/app/oracle/product/10.2.0/db/dbs共享出来，那么我们需要编辑/etc/exports文件，追加一行/u01/app/oracle/product/10.2.0/db/dbs *(rw,sync）:

[root@jingyong1 /]# vi /etc/exports


/u01/app/oracle/product/10.2.0/db/dbs *(rw,sync)

其中
/u01/app/oracle/product/10.2.0/db/dbs是要共享的目录；
* 代表允许所有的网络段访问(仅测试中使用，实际使用应该做严格的IP限制)；
rw开启共享目录的可读写权限；
sync是资料同步写入内存和硬盘；
其他更多参数说明：
ro 只读访问
rw 读写访问sync 所有数据在请求时写入共享
async nfs在写入数据前可以响应请求
secure nfs通过1024以下的安全TCP/IP端口发送
insecure nfs通过1024以上的端口发送
wdelay 如果多个用户要写入nfs目录，则归组写入（默认）
no_wdelay 如果多个用户要写入nfs目录，则立即写入，当使用async时，无需此设置。
hide 在nfs共享目录中不共享其子目录
no_hide 共享nfs目录的子目录
subtree_check 如果共享/usr/bin之类的子目录时，强制nfs检查父目录的权限（默认）
no_subtree_check 和上面相对，不检查父目录权限
all_squash 共享文件的UID和GID映射匿名用户anonymous，适合公用目录。
no_all_squash 保留共享文件的UID和GID（默认）
root_squash root用户的所有请求映射成如anonymous用户一样的权限（默认）
no_root_squas root用户具有根目录的完全管理访问权限
anonuid=xxx 指定nfs服务器/etc/passwd文件中匿名用户的UID
anongid=xxx 指定nfs服务器/etc/passwd文件中匿名用户的GID

三、启动nfs服务
在启动nfs之前需要先启动portmap服务，否则如下报错：

[root@jingyong1 /]# service nfs start
Starting NFS services:                                     [  OK  ]
Starting NFS quotas: Cannot register service: RPC: Unable to receive; errno = Connection refused
rpc.rquotad: unable to register (RQUOTAPROG, RQUOTAVERS, udp).
                                                           [FAILED]
Starting NFS daemon:

正确方法先启动portmap再启动nfs，如下：
service portmap start
service nfs start
或
/etc/init.d/portmap start
/etc/init.d/nfs start


[root@jingyong1 /]# service portmap start
Starting portmap: [  OK  ]

[root@jingyong1 /]# service nfs start
Starting NFS services:  [  OK  ]
Starting NFS quotas: [  OK  ]
Starting NFS daemon: [  OK  ]
Starting NFS mountd: [  OK  ]

四、在客户端主机上挂载共享目录
1、挂载之前同样需要先启动portmap服务

[root@oracle11g /]# service portmap start
Starting portmap: [  OK  ]

2、在客户端使用showmount -e IP 查看nfs主机共享情况：

[root@oracle11g /]# showmount -e 192.168.56.11
Export list for 192.168.56.11:
/u01/app/oracle/product/10.2.0/db/dbs *

3、在客户端建立jingyong1文件夹，并使用mount挂载命令：

[root@oracle11g net]# mkdir /jingyong1
[root@oracle11g /]# chown -R oracle:oinstall jingyong1
[root@oracle11g /]# chmod -R 777 jingyong1

[root@oracle11g /]# mount -t nfs 192.168.56.11:/u01/app/oracle/product/10.2.0/db/dbs /jingyong1

4、若无报错，则可使用df -h 查看到挂载情况：

[root@oracle11g /]# df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/sda1              23G   12G  9.6G  55% /
/dev/sdb1             9.9G  5.6G  3.9G  60% /u02
tmpfs                 252M     0  252M   0% /dev/shm
192.168.56.11:/u01/app/oracle/product/10.2.0/db/dbs
                       17G   14G  2.3G  86% /jingyong1

五、在客户端卸载已挂载的目录

[root@oracle11g /]# umount /jingyong1

rman duplicate target database RMAN-03002 RMAN-03015 RMAN-06136

Posted on 2015年3月24日 by YongJing(敬勇)

通过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
命令时不能有任何其它会话连接到辅助实例，否则就是会执行失败。