单实例数据库迁移到rac环境(四)上-阿里云开发者社区

在前面的3个小节中，分别介绍了使用expdp，传输表空间以及rman的异机恢复等方式实现单实例数据库向rac环境的迁移，本节中将介绍下使用physical dataguard实现这一过程，核心思想是构建基于单实例数据库的物理备库(rac环境)，而后进行主备库的切换，尽可能的减少迁移数据的时间消耗和对生产环境的影响！但同前3种方式相比，迁移的难度也增加了不少！由于文章篇幅限制，本节演示将分为上下两篇文章。本文主要参考下列的文章，感谢作者分享！
http://space.itpub.net/4227/viewspace-622322

一：环境介绍
primary数据库(单实例环境)
IP:192.168.1.49/24
操作系统版本：rhel5.4 64位
数据库版本：10.2.0.5

physical standby数据库(rac环境，2个节点)
节点1 IP:192.168.1.41/24
节点2 IP:192.168.1.42/24
操作系统版本：centos4.8 64位
数据库版本：10.2.0.5

二：配置primary数据库的tnsnames.ora文件以及dataguard相关的设置，由于即将构建的备库为rac环境，因而需要增加undotbs2表空间和thread 2日志组

[oracle@server49 ~]$ cd $ORACLE_HOME/network/admin
[oracle@server49 ~]$ cat tnsnames.ora
PRIMARY =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server49.yang.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary.yang.com)
    )
)

STANDBY =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.yang.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SID = orcl1)
      (SERVICE_NAME = standby.yang.com)
    )
)

SQL> show parameter name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_file_name_convert                 string
db_name                              string      orcl
db_unique_name                       string      primary
global_names                         boolean     FALSE
instance_name                        string      orcl
lock_name_space                      string
log_file_name_convert                string
service_names                        string      primary.yang.com

SQL> select name,open_mode,log_mode,force_logging from v$database;

NAME OPEN_MODE LOG_MODE FOR
--------- ---------- ------------ ---
ORCL READ WRITE ARCHIVELOG NO

SQL> alter database force logging;
Database altered.

SQL> select bytes/(1024*1024) MB,autoextensible from dba_data_files where tablespace_name='UNDOTBS1';

MB AUT
---------- ---
30 YES

SQL> create undo tablespace undotbs2 datafile '/u01/app/oracle/oradata/orcl/undotbs02.dbf' size 30M autoextend on maxsize 10G;
Tablespace created.

SQL> select bytes/(1024*1024) MB,autoextensible from dba_data_files where tablespace_name in ('UNDOTBS1','UNDOTBS2');

        MB AUT
---------- ---
        30 YES
        30 YES

QL> select a.group#,a.member,b.bytes/(1024*1024) MB,b.thread# from v$logfile a,v$log b where a.group#=b.group#;

SQL> alter database add logfile thread 2 group 4 '/u01/app/oracle/oradata/orcl/redo04.log' size 50M;
Database altered.

SQL> alter database add logfile thread 2 group 5 '/u01/app/oracle/oradata/orcl/redo05.log' size 50M;
Database altered.

SQL> alter database add logfile thread 2 group 6 '/u01/app/oracle/oradata/orcl/redo06.log' size 50M;
Database altered.

SQL> select a.group#,a.member,b.bytes/(1024*1024) MB,b.thread# from v$logfile a,v$log b where a.group#=b.group#;

    GROUP# MEMBER                                           MB    THREAD#
---------- ---------------------------------------- ---------- ----------
         3 /u01/app/oracle/oradata/orcl/redo03.log          50          1
         2 /u01/app/oracle/oradata/orcl/redo02.log          50          1
         1 /u01/app/oracle/oradata/orcl/redo01.log          50          1
         4 /u01/app/oracle/oradata/orcl/redo04.log          50          2
         5 /u01/app/oracle/oradata/orcl/redo05.log          50          2
         6 /u01/app/oracle/oradata/orcl/redo06.log          50          2

SQL> alter database enable thread 2;
Database altered.

SQL> alter system set log_archive_config='DG_CONFIG=(PRIMARY,STANDBY)';
System altered.

SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     19
Next log sequence to archive   21
Current log sequence           21

SQL> !mkdir -p /u01/app/oracle/archive_log
SQL> alter system set log_archive_dest_1='LOCATION=/u01/app/oracle/archive_log VALID_FOR=ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=ORCL';
System altered.

SQL> alter system set log_archive_dest_2='SERVICE=standby LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby';
System altered.

SQL> alter system set fal_server=standby;
System altered.

SQL> alter system set fal_client=primary;
System altered.

SQL> alter system set standby_file_management=auto;
System altered.SQL> !mkdir -p /home/oracle/rman_bak

三：在primar库上生成pfile，并使用rman备份数据库,将备份的数据复制到rac节点1上

SQL> create pfile='/home/oracle/rman_bak/initorcl.ora' from spfile;
File created.

[oracle@server49 ~]$ rman target /
Recovery Manager: Release 10.2.0.5.0 - Production on Sun Jan 8 14:49:21 2012
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: ORCL (DBID=1299224612)

RMAN> run {
2> allocate channel c1 device type disk;
3> allocate channel c2 device type disk;
4> backup incremental level 0
5> format '/home/oracle/rman_bak/inr0_%U'
6> tag 'full_bak_for_rac_standby'
7> database plus archivelog delete all input;
8> release channel c1;
9> release channel c2;
10> }

RMAN> backup format '/home/oracle/rman_bak/control01.ctl' current controlfile for standby;
[oracle@server49 ~]$ scp -rp rman_bak/ rac1:$(pwd)

四：standby(节点1)数据库上，同样配置tnsnames.ora文件，创建密码文件，修改参数文件

[oracle@rac1 ~]$ cat $ORACLE_HOME/network/admin/tnsnames.ora
ORCL1 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.yang.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby.yang.com)
      (INSTANCE_NAME = orcl1)
    )
)

ORCL2 =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.yang.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby.yang.com)
      (INSTANCE_NAME = orcl2)
    )
)

ORCL =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.yang.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.yang.com)(PORT = 1521))
    (LOAD_BALANCE = yes)
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl.yang.com)
    )
)

LISTENERS_ORCL =
(ADDRESS_LIST =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.yang.com)(PORT = 1521))
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.yang.com)(PORT = 1521))
)
PRIMARY =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = server49.yang.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = primary.yang.com)
      (INSTANCE_NAME = orcl)
    )
)

STANDBY =
(DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = rac1.yang.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = standby.yang.com)
      (INSTANCE_NAME = orcl1)
    )
)

[oracle@rac1 ~]$ crs_stat -t -v
Name           Type           R/RA   F/FT   Target    State     Host
----------------------------------------------------------------------
ora....SM1.asm application    0/5    0/0    ONLINE    ONLINE    rac1
ora....C1.lsnr application    0/5    0/0    ONLINE    ONLINE    rac1
ora.rac1.gsd   application    0/5    0/0    ONLINE    ONLINE    rac1
ora.rac1.ons   application    0/3    0/0    ONLINE    ONLINE    rac1
ora.rac1.vip   application    0/0    0/0    ONLINE    ONLINE    rac1
ora....SM2.asm application    0/5    0/0    ONLINE    ONLINE    rac2
ora....C2.lsnr application    0/5    0/0    ONLINE    ONLINE    rac2
ora.rac2.gsd   application    0/5    0/0    ONLINE    ONLINE    rac2
ora.rac2.ons   application    0/3    0/0    ONLINE    ONLINE    rac2
ora.rac2.vip   application    0/0    0/0    ONLINE    ONLINE    rac2

[oracle@rac1 ~]$ orapwd file=$ORACLE_HOME/dbs/orapworcl1 password=123456
[oracle@rac1 ~]$ cat /home/oracle/rman_bak/initorcl.ora
*.__db_cache_size=79691776
*.__java_pool_size=4194304
*.__large_pool_size=4194304
*.__shared_pool_size=109051904
*.__streams_pool_size=8388608
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.background_dump_dest='/u01/app/oracle/admin/orcl/bdump'
*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'
*.user_dump_dest='/u01/app/oracle/admin/orcl/udump'
*.compatible='10.2.0.5.0'
*.control_files='+DATA/ORCL/CONTROLFILE/CONTROL01.CTL','+FRA/ORCL/CONTROLFILE/CONTROL02.CTL'
*.db_block_size=8192
*.db_domain='yang.com'
*.db_file_multiblock_read_count=16
*.db_name='orcl'
*.db_recovery_file_dest='+FRA'
*.db_recovery_file_dest_size=21474836480
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='STANDBY'
*.fal_server='PRIMARY'
*.job_queue_processes=10
*.log_archive_config='DG_CONFIG=(PRIMARY,STANDBY)'
*.log_archive_dest_1='LOCATION=+FRA/ORCL/ARCHIVELOG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=STANDBY'
*.log_archive_dest_2='SERVICE=PRIMARY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PRIMARY'
*.log_archive_format='%t_%s_%r.dbf'
*.open_cursors=300
*.pga_aggregate_target=71303168
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=213909504
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.cluster_database=true
*.cluster_database_instances=2
*.db_unique_name='standby'
*.log_file_name_convert=('/u01/app/oracle/flash_recover_area', '+FRA/ORCL/ARCHIVELOG')
*.db_file_name_convert=('/u01/app/oracle/oradata/orcl', '+DATA/ORCL/DATAFILE')
orcl1.undo_tablespace='UNDOTBS1'
orcl2.undo_tablespace='UNDOTBS2'
orcl1.instance_number=1
orcl2.instance_number=2
orcl1.thread=1
orcl2.thread=2
orcl1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.41)(PORT=1521))'
orcl2.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.1.42)(PORT=1521))'

六：在节点1数据库上恢复控制文件

[oracle@rac1 ~]$ rman target /
connected to target database: orcl (not mounted)
RMAN> restore controlfile from '/home/oracle/rman_bak/control01.ctl';
备注：ASM磁盘内的相应目录要先创建好

ASMCMD> pwd
+FRA/ORCL/controlfile
ASMCMD> ls
control02.ctl
ASMCMD> cd +DATA/ORCL/controlfile
ASMCMD> ls
control01.ctl

七：节点1数据库连接primary进行duplicate操作

[oracle@rac1 ~]$ rman target sys/123456@primary auxiliary /
connected to target database: ORCL (DBID=1299224612)
connected to auxiliary database: ORCL (not mounted)

RMAN> duplicate target database for standby;
输出省略

[oracle@rac1 ~]$ rman target /
connected to target database: ORCL (DBID=1299224612, not open)

RMAN> report schema;

Starting implicit crosscheck backup at 2012-01-09 15:35:29
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
Crosschecked 9 objects
Finished implicit crosscheck backup at 2012-01-09 15:35:31

Starting implicit crosscheck copy at 2012-01-09 15:35:31
using channel ORA_DISK_1
Finished implicit crosscheck copy at 2012-01-09 15:35:31

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

RMAN-06139: WARNING: control file is not current for REPORT SCHEMA
Report of database schema

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    450      SYSTEM               ***     +DATA/orcl/datafile/system01.dbf
2    30       UNDOTBS1             ***     +DATA/orcl/datafile/undotbs01.dbf
3    260      SYSAUX               ***     +DATA/orcl/datafile/sysaux01.dbf
4    5        USERS                ***     +DATA/orcl/datafile/users01.dbf
5    100      EXAMPLE              ***     +DATA/orcl/datafile/example01.dbf
6    410      EXP_RAC              ***     +DATA/orcl/datafile/exp_rac01.dbf
7    300      EXP_RAC_INDEX        ***     +DATA/orcl/datafile/exp_rac_index01.dbf
8    300      EXP_RAC1             ***     +DATA/orcl/datafile/exp_rac1_01.dbf
9    300      EXP_RAC1_INDEX       ***     +DATA/orcl/datafile/exp_rac1_index_01.dbf
10   30       UNDOTBS2             ***     +DATA/orcl/datafile/undotbs02.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 0 TEMP 32767 +DATA/orcl/datafile/temp01.dbf

ASMCMD> pwd
+data/ORCL/datafile
ASMCMD> ls
example01.dbf
exp_rac01.dbf
exp_rac1_01.dbf
exp_rac1_index_01.dbf
exp_rac_index01.dbf
sysaux01.dbf
system01.dbf
undotbs01.dbf
undotbs02.dbf
users01.dbf

八：启动节点1数据库到应用日志模式下，中间若出现归档日志的gap，则需要在主库上restore相应的归档日志文件

[oracle@rac1 ~]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 9 15:43:34 2012
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.

SQL> conn /as sysdba
Connected.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            +FRA/orcl/archivelog
Oldest online log sequence     26
Next log sequence to archive   0
Current log sequence           28

SQL> alter database recover managed standby database disconnect from session;
Database altered.

SQL> select name,database_role from gv$database;

NAME DATABASE_ROLE
--------------------------- ------------------------------------------------
ORCL PHYSICAL STANDBY

SQL> select first_time,next_time,sequence#,applied from v$archived_log;

FIRST_TIME NEXT_TIME SEQUENCE# APPLIED
------------------- ------------------- ---------- ---------
2012-01-09 14:17:57 2012-01-09 14:52:34 27 NO

[oracle@rac1 ~]$ tail -f /u01/app/oracle/admin/orcl/bdump/alert_orcl1.log
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
Mon Jan 09 15:44:26 CST 2012
Errors in file /u01/app/oracle/admin/orcl/bdump/orcl1_mrp0_15952.trc:
ORA-00344: unable to re-create online log '/u01/app/oracle/oradata/orcl/redo01.log'
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
Clearing online redo logfile 1 complete
Media Recovery Waiting for thread 1 sequence 26
Fetching gap sequence in thread 1, gap sequence 26-26
Mon Jan 09 15:44:26 CST 2012
Completed: alter database recover managed standby database disconnect from session
Mon Jan 09 15:45:00 CST 2012
FAL[client]: Failed to request gap sequence
GAP - thread 1 sequence 26-26
DBID 1299224612 branch 771443882
FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
-------------------------------------------------------------

primary数据库操作：
RMAN> restore archivelog from sequence 24;

SQL> conn /as sysdba
Connected.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archive_log
Oldest online log sequence     26
Next log sequence to archive   28
Current log sequence           28
SQL> alter system switch logfile;
System altered.

[oracle@rac1 ~]$ tail -f /u01/app/oracle/admin/orcl/bdump/alert_orcl1.log
RFS[2]: Assigned to RFS process 28510
RFS[2]: Identified database type as 'physical standby'
Primary database is in MAXIMUM PERFORMANCE mode
Primary database is in MAXIMUM PERFORMANCE mode
Mon Jan 09 16:15:46 CST 2012
RFS[1]: Archived Log: '+FRA/orcl/archivelog/1_28_771443882.dbf'
RFS[1]: Archived Log: '+FRA/orcl/archivelog/2_6_771443882.dbf'
Mon Jan 09 16:15:50 CST 2012
Fetching gap sequence in thread 2, gap sequence 5-5
Mon Jan 09 16:16:25 CST 2012
FAL[client]: Failed to request gap sequence
GAP - thread 2 sequence 5-5
DBID 1299224612 branch 771443882
FAL[client]: All defined FAL servers have been attempted.
-------------------------------------------------------------
Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization
parameter is defined to a value that is sufficiently large
enough to maintain adequate log switch information to resolve
archivelog gaps.
-------------------------------------------------------------

SQL>select first_time,next_time,sequence#,applied from v$archived_log;

FIRST_TIME          NEXT_TIME            SEQUENCE# APPLIED
------------------- ------------------- ---------- ---------
2012-01-09 14:17:57 2012-01-09 14:52:34         27 NO
2012-01-09 14:13:14 2012-01-09 14:17:57         26 NO
2012-01-09 14:52:34 2012-01-09 16:15:37         28 NO
2012-01-09 14:17:56 2012-01-09 16:15:44          6 NO

primary数据库操作：
RMAN> restore archivelog from sequence 5 thread 2;

节点1数据库继续查询：
SQL>select first_time,next_time,sequence#,applied from v$archived_log;

FIRST_TIME          NEXT_TIME            SEQUENCE# APPLIED
------------------- ------------------- ---------- ---------
2012-01-09 14:17:57 2012-01-09 14:52:34         27 YES
2012-01-09 14:13:14 2012-01-09 14:17:57         26 YES
2012-01-09 14:52:34 2012-01-09 16:15:37         28 YES
2012-01-09 14:17:56 2012-01-09 16:15:44          6 YES
2012-01-09 14:13:12 2012-01-09 14:17:56          5 YES
2012-01-09 16:15:37 2012-01-09 16:30:00         29 NO
6 rows selected.

上述过程没有问题后，就可以启动节点2的数据库实例到mount状态！
SQL> select name,db_unique_name,open_mode,database_role from gv$database;

NAME                 DB_UNIQUE_ OPEN_MODE            DATABASE_ROLE
-------------------- ---------- -------------------- --------------------
ORCL                 standby    MOUNTED              PHYSICAL STANDBY
ORCL                 standby    MOUNTED              PHYSICAL STANDBY

最后确定下主库和备库的日志传输和应用状况是否正常！
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            /u01/app/oracle/archive_log
Oldest online log sequence     34
Next log sequence to archive   36
Current log sequence           36

SQL> select first_time,next_time,sequence#,applied,thread# from v$archived_log order by 5,3 ;

FIRST_TIME          NEXT_TIME            SEQUENCE# APPLIED      THREAD#
------------------- ------------------- ---------- --------- ----------
2012-01-09 14:13:14 2012-01-09 14:17:57         26 YES                1
2012-01-09 14:17:57 2012-01-09 14:52:34         27 YES                1
2012-01-09 14:52:34 2012-01-09 16:15:37         28 YES                1
2012-01-09 16:15:37 2012-01-09 16:30:00         29 YES                1
2012-01-09 16:30:00 2012-01-09 18:04:06         30 YES                1
2012-01-09 18:04:06 2012-01-09 18:04:18         31 YES                1
2012-01-09 18:04:18 2012-01-09 18:06:40         32 YES                1
2012-01-09 18:06:40 2012-01-09 19:55:25         33 YES                1
2012-01-09 19:55:25 2012-01-09 19:55:38         34 YES                1
2012-01-09 19:55:38 2012-01-09 19:56:30         35 NO                 1
2012-01-09 14:13:12 2012-01-09 14:17:56          5 YES                2

FIRST_TIME          NEXT_TIME            SEQUENCE# APPLIED      THREAD#
------------------- ------------------- ---------- --------- ----------
2012-01-09 14:17:56 2012-01-09 16:15:44          6 YES                2
2012-01-09 16:15:44 2012-01-09 18:04:05          7 YES                2
2012-01-09 18:04:05 2012-01-09 18:04:17          8 YES                2
2012-01-09 18:04:17 2012-01-09 18:06:36          9 YES                2
2012-01-09 18:06:36 2012-01-09 19:55:23         10 YES                2
2012-01-09 19:55:23 2012-01-09 19:55:37         11 YES                2
2012-01-09 19:55:37 2012-01-09 19:56:26         12 YES                2
18 rows selected.

SQL> select name,db_unique_name,database_role,switchover_status from v$database;

NAME DB_UNIQUE_NAME DATABASE_ROLE SWITCHOVER_STATUS
--------- ------------------------------ ---------------- --------------------
ORCL primary PRIMARY SESSIONS ACTIVE

至此，单实例主库和rac物理备库的环境以及基本完成，下一节中将介绍下switchover过程！

本文转自斩月博客51CTO博客，原文链接http://blog.51cto.com/ylw6006/760860如需转载请自行联系原作者

ylw6006

单实例数据库迁移到rac环境(四)上

热门文章

最新文章

相关课程

相关电子书

相关实验场景