RMAN复制数据库(三)

只使用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复制数据库(二)

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

只使用初始化参数来转换文件名
这种方法是只使用初始化方法来对复制数据库的数据文件和日志文件进行重命名。
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复制数据库(一)

使用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

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

[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表空间按时间点恢复(五)

使用自己的辅助实例来执行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表空间按时间点恢复(四)

使用镜像副本提高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失败后保留用来进行诊断。

Proudly powered by WordPress | Indrajeet by Sus Hill.