TB级别库迁移操作

一、场景
因一套RAC库的机器需要更换机房，必须在原先机房保留一套与之一样的数据库，以便应用不断的情形做机器的搬迁:
1)、应用暂停为零最好，这种情形用goldengate做，可以做到零停机，成本太高；
2)、利用rman+dg来做，应用暂停时间大约在切应用和apply最后一批日志，因为是TB级库，如果顺利，控制在30分钟-50分钟左右；否则启用第二套方案，这里不作说明。

二、原理
采用10g RMAN duplicate方式将primary最近的备份集clone成auxiliary机器配的"standby"库，在clone完成到应用切这段时间做apply归档日志应用
注意事项：
a、standby库是理论上的备库，不是真正dg的备库；
b、primary库不自动同步日志，需要定期手工apply日志到备库上；
c、做完备库之后，应用一条最近的归档日志，open read only数据；
d、没有将停完应用的最后一批日志apply到standby库里，一定不能切换role，否则一定会丢数据；
e、停应用之后primary库的oracle网络及实例都掐断，防止有人连接；
f、切完standby为primary后，修改参数文件，去了相关参数，正常关闭启动数据，验证库有没有问题

三、primary及standby配置
1)、primary机器配置
a、双节点10.2.0.5.0版本的RAC
b、存储文件系统:ASM，47块盘做磁盘阵列
c、存储大小：大约8TB
d、数据量：3.2TB
e、最近一次备份集大小：350GB
f、无效对象：无
g、无效索引：3条，历史表，与实际生产数据没有关系
h、每周归档大约为1TB以上：归档采用压缩方式
i、redhat 5.5 x86_64
j、内存32GB,8cpu
2)、standby机器配置：
a、单实例10.2.0.5.0数据
b、存储文件系统:本地存储，30多块盘做磁盘阵列
c、存储大小：大约8.5TB
d、最近一次备份集大小：350GB
e、redhat 5.8 x86_64
f、内存194GB,8cpu

四、standby端配置
注：主库无需变动，因为不是真正上的dg，只要求做个physical standby库，手工同步日志
1)、配置密码及参数文件
create pfile='/tmp/initgpsdb.ora' from spfile;
orapwd file=orapwgpsdb password=zm_321Tl;
将产生的文件放置
/u01/app/oracle/product/10.2.0/db_1/dbs下
[oracle@gpsdb dbs]$ ll
-rw-r--r-- 1 oracle oinstall    12920 May  3  2001 initdw.ora
-rw-r--r-- 1 oracle oinstall     1179 Jun  4 10:03 initgpsdb.ora
-rw-r----- 1 oracle oinstall     8385 Sep 11  1998 init.ora
-rw-r----- 1 oracle oinstall     1536 May 26 15:32 orapwgpsdb

参数文件配置
[oracle@gpsdb dbs]$ more initgpsdb.ora
gpsdb.__db_cache_size=13824425984
gpsdb.__java_pool_size=33554432
gpsdb.__large_pool_size=16777216
gpsdb.__shared_pool_size=7516192768
gpsdb.__streams_pool_size=33554432
gpsdb._kghdsidx_count=3
*.audit_file_dest='/u01/app/oracle/admin/gpsdb/adump'
*.background_dump_dest='/u01/app/oracle/admin/gpsdb/bdump'
*.compatible='10.2.0.5.0'
*.control_files='/oradata/gpsdb/control1.ctl','/oradata/gpsdb/control2.ctl'
*.core_dump_dest='/u01/app/oracle/admin/gpsdb/cdump'
*.db_block_size=8192
*.db_cache_size=12884901888
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='gpsdb'
*.db_recovery_file_dest='/oradata/archivelog_dest'
*.db_recovery_file_dest_size=1152642973696
*.dispatchers='(PROTOCOL=TCP) (SERVICE=gpsdbXDB)'
*.java_pool_size=25165824
*.job_queue_processes=10
*.large_pool_size=10485760
*.log_archive_format='%t_%s_%r.arc'
*.open_cursors=300
*.pga_aggregate_target=8589934592
*.processes=500
*.remote_login_passwordfile='exclusive'
*.sessions=555
*.sga_max_size=22296920064
*.sga_target=22296920064
*.shared_pool_size=7516192768
*.streams_pool_size=33554432
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/u01/app/oracle/admin/gpsdb/udump'
*.LOG_ARCHIVE_DEST_1= 'LOCATION=/oradata/archivelog_dest  VALID_FOR=(ALL_LOGFILES,ALL_ROLES)  DB
_UNIQUE_NAME=gpsdb'
*.LOG_ARCHIVE_DEST_2= 'SERVICE=gpsdb_net LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_
UNIQUE_NAME=gpsdb'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.LOG_ARCHIVE_MAX_PROCESSES=30
*.db_file_name_convert='+DATADG/gpsdb','/oradata/gpsdb','+FLASHAREA_DG/gpsdb','/oradata/gpsdb_fl
asharea_dg'
*.log_file_name_convert='+DATADG/gpsdb','/oradata/gpsdb'
*.FAL_SERVER=gpsdb_net
*.FAL_CLIENT=gpsdb
*.STANDBY_FILE_MANAGEMENT=AUTO

2)、目录
[oracle@gpsdb gpsdb]$ pwd
/u01/app/oracle/admin/gpsdb
[oracle@gpsdb gpsdb]$ ll
total 24
drwxr-x--- 2 oracle oinstall 4096 May 20 12:36 adump
drwxr-x--- 2 oracle oinstall 4096 May 20 12:41 bdump
drwxr-x--- 2 oracle oinstall 4096 May 13 12:26 cdump
drwxr-x--- 2 oracle oinstall 4096 May 13 12:27 dpdump
drwxr-x--- 2 oracle oinstall 4096 May 20 13:43 pfile
drwxr-x--- 2 oracle oinstall 4096 May 20 12:36 udump

3)、网络配置：
tnsnames.ora
##连接primary库
gpsdb_p =
  (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.118.51)(PORT = 1521))
      (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.118.52)(PORT = 1521))
      (LOAD_BALANCE = yes)
    (CONNECT_DATA =
        (SERVER = DEDICATED)
        (SERVICE_NAME = gpsdb.trsen.zhang.com)
    )
  )
##连接stanby库
gpsdb_s =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.19.88)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = gpsdb)
     )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

listener.ora
##配置静态监听和动态监听
SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME=gpsdb)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = gpsdb)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = gpsdb)(PORT = 1521))
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

/etc/hosts配置
[oracle@gpsdb admin]$ more /etc/hosts
# Do not remove the following line, or various programs
# that require network functionality will fail.
127.0.0.1               localhost.localdomain localhost
::1             localhost6.localdomain6 localhost6
10.1.19.88     gpsdb

查看监听配置是否成功
[oracle@gpsdb admin]$ lsnrctl status
LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 04-JUN-2014 14:57:53
Copyright (c) 1991, 2010, Oracle.  All rights reserved.
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=gpsdb)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date                26-MAY-2014 15:09:25
Uptime                    8 days 23 hr. 48 min. 27 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=gpsdb)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "PLSExtProc" has 1 instance(s).
  Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "gpsdb" has 2 instance(s).
  Instance "gpsdb", status UNKNOWN, has 1 handler(s) for this service...
  Instance "gpsdb", status READY, has 1 handler(s) for this service...
Service "gpsdbXDB" has 1 instance(s).
  Instance "gpsdb", status READY, has 1 handler(s) for this service...
Service "gpsdb_XPT" has 1 instance(s).
  Instance "gpsdb", status READY, has 1 handler(s) for this service...
The command completed successfully

测试rman duplicate连接
[oracle@gpsdb admin]$rman target sys/zm_321Tl@gpsdb_p AUXILIARY sys/zm_321Tl@gpsdb_s
Recovery Manager: Release 10.2.0.5.0 - Production on Fri May 30 22:29:33 2014
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
connected to target database: GPSDB (DBID=201808262)
connected to auxiliary database: GPSDB (not mounted)
RMAN>

五、做standby数据库
1)、脚本
[oracle@gpsdb ~]$ more duplicate.sh
#!/bin/bash

. /etc/profile
. ~/.bash_profile
date
rman target sys/zm_321Tl@gpsdb_p AUXILIARY sys/zm_321Tl@gpsdb_s <<EOF
run
{
allocate auxiliary channel aux1 device type disk;
allocate auxiliary channel aux2 device type disk;
allocate auxiliary channel aux3 device type disk;
DUPLICATE TARGET DATABASE FOR STANDBY;
}
EOF
date

2)、duplicate日志,整个过程花费将近7小时
[oracle@gpsdb ~]$ more duplicate.log
Fri May 30 22:29:33 CST 2014

Recovery Manager: Release 10.2.0.5.0 - Production on Fri May 30 22:29:33 2014

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

connected to target database: GPSDB (DBID=201808262)
connected to auxiliary database: GPSDB (not mounted)

RMAN> 2> 3> 4> 5> 6> 7>
using target database control file instead of recovery catalog
allocated channel: aux1
channel aux1: sid=536 devtype=DISK

allocated channel: aux2
channel aux2: sid=535 devtype=DISK

allocated channel: aux3
channel aux3: sid=534 devtype=DISK

Starting Duplicate Db at 30-MAY-14

contents of Memory Script:
{
   restore clone standby controlfile;
   sql clone 'alter database mount standby database';
}
executing Memory Script

Starting restore at 30-MAY-14

channel aux1: restoring control file
channel aux1: copied control file copy
input filename=/tmp/ctl
output filename=/oradata/gpsdb/control1.ctl
output filename=/oradata/gpsdb/control2.ctl
Finished restore at 30-MAY-14

sql statement: alter database mount standby database

contents of Memory Script:
{
   set newname for tempfile  1 to
 "/oradata/gpsdb/temp01.dbf";
   set newname for tempfile  2 to
 "/oradata/gpsdb/gps_temp_01.dbf";
   set newname for tempfile  3 to
 "/oradata/gpsdb/gps_temp_02.dbf";
   switch clone tempfile all;
   set newname for datafile  1 to
 "/oradata/gpsdb/system01.dbf";
   set newname for datafile  2 to
 "/oradata/gpsdb/undotbs01.dbf";
   set newname for datafile  3 to
 "/oradata/gpsdb/sysaux01.dbf";
   set newname for datafile  4 to
 "/oradata/gpsdb/users01.dbf";
   set newname for datafile  5 to
 "/oradata/gpsdb/undotbs02.dbf";
   set newname for datafile  6 to
 "/oradata/gpsdb/wzt_tbs_01.dbf";
   set newname for datafile  7 to
 "/oradata/gpsdb/wzt_tbs_02.dbf";
   set newname for datafile  8 to
 "/oradata/gpsdb/wzt_tbs_03.dbf";
   set newname for datafile  9 to
 "/oradata/gpsdb/wzt_tbs_04.dbf";
   set newname for datafile  10 to
 "/oradata/gpsdb/wzt_tbs_05.dbf";
   set newname for datafile  11 to
 "/oradata/gpsdb/splex_tbs_01.dbf";
   set newname for datafile  12 to
 "/oradata/gpsdb/wzt_tbs_06.dbf";
   set newname for datafile  13 to
 "/oradata/gpsdb/wzt_tbs_07.dbf";
   set newname for datafile  14 to
 "/oradata/gpsdb/wzt_tbs_08.dbf";
   set newname for datafile  15 to
 "/oradata/gpsdb/wzt_tbs_09.dbf";
   set newname for datafile  16 to
 "/oradata/gpsdb/wzt_tbs_10.dbf";
   set newname for datafile  17 to
 "/oradata/gpsdb/dbf_130501.dbf";
   set newname for datafile  18 to
 "/oradata/gpsdb/dbf_130502.dbf";
   set newname for datafile  19 to
 "/oradata/gpsdb/dbf_130503.dbf";
   set newname for datafile  20 to
 "/oradata/gpsdb/wzt_tbs_11.dbf";
   set newname for datafile  21 to
 "/oradata/gpsdb/wzt_tbs_12.dbf";
   set newname for datafile  22 to
 "/oradata/gpsdb/wzt_tbs_13.dbf";
   set newname for datafile  23 to
 "/oradata/gpsdb/wzt_tbs_14.dbf";
   set newname for datafile  24 to
 "/oradata/gpsdb/wzt_tbs_15.dbf";
   set newname for datafile  25 to
 "/oradata/gpsdb/wzt_tbs_16.dbf";
   set newname for datafile  26 to
 "/oradata/gpsdb/wzt_tbs_17.dbf";
   set newname for datafile  27 to
 "/oradata/gpsdb/wzt_tbs_18.dbf";
   set newname for datafile  28 to
 "/oradata/gpsdb/wzt_tbs_19.dbf";
   set newname for datafile  29 to
 "/oradata/gpsdb/wzt_tbs_20.dbf";
   set newname for datafile  30 to
 "/oradata/gpsdb/idx_trenzhangmot_01.dbf";
   set newname for datafile  31 to
 "/oradata/gpsdb/idx_trenzhangmot_02.dbf";
   set newname for datafile  32 to
 "/oradata/gpsdb/idx_trenzhangmot_03.dbf";
   set newname for datafile  33 to
 "/oradata/gpsdb/idx_trenzhangmot_04.dbf";
   set newname for datafile  34 to
 "/oradata/gpsdb/idx_trenzhangmot_05.dbf";
   set newname for datafile  35 to
 "/oradata/gpsdb/idx_trenzhangmot_06.dbf";
   set newname for datafile  36 to
 "/oradata/gpsdb/idx_trenzhangmot_07.dbf";
   set newname for datafile  37 to
 "/oradata/gpsdb/idx_trenzhangmot_08.dbf";
   set newname for datafile  38 to
 "/oradata/gpsdb/idx_trenzhangmot_09.dbf";
   set newname for datafile  39 to
 "/oradata/gpsdb/idx_trenzhangmot_10.dbf";
   set newname for datafile  40 to
 "/oradata/gpsdb/dbf_06_1.dbf";
   set newname for datafile  41 to
 "/oradata/gpsdb/dbf_06_2.dbf";
   set newname for datafile  42 to
 "/oradata/gpsdb/dbf_06_3.dbf";
   set newname for datafile  43 to
 "/oradata/gpsdb/dbf_07_1.dbf";
   set newname for datafile  44 to
 "/oradata/gpsdb/dbf_07_2.dbf";
   set newname for datafile  45 to
 "/oradata/gpsdb/dbf_07_3.dbf";
   set newname for datafile  46 to
 "/oradata/gpsdb/wzt_tbs_21.dbf";
   set newname for datafile  47 to
 "/oradata/gpsdb/wzt_tbs_22.dbf";
   set newname for datafile  48 to
 "/oradata/gpsdb/wzt_tbs_23.dbf";
   set newname for datafile  49 to
 "/oradata/gpsdb/wzt_tbs_24.dbf";
   set newname for datafile  50 to
 "/oradata/gpsdb/wzt_tbs_25.dbf";
   set newname for datafile  51 to
 "/oradata/gpsdb/dbf_08_1.dbf";
   set newname for datafile  52 to
 "/oradata/gpsdb/dbf_08_2.dbf";
   set newname for datafile  53 to
 "/oradata/gpsdb/dbf_08_3.dbf";
   set newname for datafile  54 to
 "/oradata/gpsdb_flasharea_dg/dbf_08_2.dbf";
   set newname for datafile  55 to
 "/oradata/gpsdb_flasharea_dg/wzt_tbs_26.dbf";
   set newname for datafile  56 to
 "/oradata/gpsdb/dbf_06_1b.dbf";
   set newname for datafile  57 to
 "/oradata/gpsdb/dbf_06_2b.dbf";
   set newname for datafile  58 to
 "/oradata/gpsdb/dbf_06_3b.dbf";
   set newname for datafile  59 to
 "/oradata/gpsdb/dbf_07_1b.dbf";
   set newname for datafile  60 to
 "/oradata/gpsdb/dbf_07_2b.dbf";
   set newname for datafile  61 to
 "/oradata/gpsdb/dbf_07_3b.dbf";
   set newname for datafile  62 to
 "/oradata/gpsdb/dbf_08_1b.dbf";
   set newname for datafile  63 to
 "/oradata/gpsdb/dbf_08_2b.dbf";
   set newname for datafile  64 to
 "/oradata/gpsdb/dbf_08_3b.dbf";
   set newname for datafile  65 to
 "/oradata/gpsdb/wzt_tbs_26.dbf";
   set newname for datafile  66 to
 "/oradata/gpsdb/undotbs03.dbf";
   set newname for datafile  67 to
 "/oradata/gpsdb/undotbs04.dbf";
   set newname for datafile  68 to
 "/oradata/gpsdb/undotbs05.dbf";
   set newname for datafile  69 to
 "/oradata/gpsdb/undotbs06.dbf";
   set newname for datafile  70 to
 "/oradata/gpsdb/undotbs07.dbf";
   set newname for datafile  71 to
 "/oradata/gpsdb/undotbs08.dbf";
   set newname for datafile  72 to
 "/oradata/gpsdb/dbf_09_1.dbf";
   set newname for datafile  73 to
 "/oradata/gpsdb/dbf_09_2.dbf";
   set newname for datafile  74 to
 "/oradata/gpsdb/dbf_09_3.dbf";
   set newname for datafile  75 to
 "/oradata/gpsdb/dbf_10_1.dbf";
   set newname for datafile  76 to
 "/oradata/gpsdb/dbf_10_2.dbf";
   set newname for datafile  77 to
 "/oradata/gpsdb/dbf_10_3.dbf";
   set newname for datafile  78 to
 "/oradata/gpsdb/dbf_11_1.dbf";
   set newname for datafile  79 to
 "/oradata/gpsdb/dbf_11_2.dbf";
   set newname for datafile  80 to
 "/oradata/gpsdb/dbf_11_3.dbf";
   set newname for datafile  81 to
 "/oradata/gpsdb/dbf_12_1.dbf";
   set newname for datafile  82 to
 "/oradata/gpsdb/dbf_12_2.dbf";
   set newname for datafile  83 to
 "/oradata/gpsdb/dbf_12_3.dbf";
   set newname for datafile  84 to
 "/oradata/gpsdb/dbf_09_1_01.dbf";
   set newname for datafile  85 to
 "/oradata/gpsdb/wzt_tbs_27.dbf";
   set newname for datafile  86 to
 "/oradata/gpsdb/wzt_tbs_28.dbf";
   set newname for datafi