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

在前面的章节中，完成了单实例主库和rac物理备库的环境的构建，本节中将介绍下如何完成主备库的手动角色切换，在这个场合如果使用data guard broker进行切换，会由于crs的原因而切换失败，因为数据库还未向crs进行注册，回顾下在rac环境上的使用data guard broker的使用条件：在rac环境下还需要配置DB_BROKER_CONFIG_FILEn参数，将该参数指定共享存储上，需要在OCR中要设定start_options参数为mount；因而在本例中必须采用手动切换，在开始之前，需要确保主库和备库的日志应用正常！

一：查看主库和备库的switchover状态，同时关闭节点2数据库

SQL> select name,db_unique_name,open_mode,database_role,switchover_status from gv$database; NAME       DB_UNIQUE_NAME  OPEN_MODE    DATABASE_ROLE   SWITCHOVER_STAT ---------- --------------- ------------ --------------- --------------- ORCL       primary         READ WRITE   PRIMARY         SESSIONS ACTIVE [oracle@rac2 ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 9 20:31:14 2012 Copyright (c) 1982, 2010, Oracle.  All Rights Reserved. SQL> conn /as sysdba Connected. SQL> shutdown immediate ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> select name,db_unique_name,open_mode,database_role,switchover_status from gv$database; NAME       DB_UNIQUE_ OPEN_MODE    DATABASE_ROLE     SWITCHOVER_STATUS ---------- ---------- ------------ ----------------- -------------------- ORCL       standby    MOUNTED      PHYSICAL STANDBY  SESSIONS ACTIVE

二：primary数据库库上运行下列的命令后，重新将数据库启动到mount状态，并开启应用日志模式

SQL> alter database commit to switchover to physical standby with session shutdown; Database altered. SQL> shutdown immediate ORA-01507: database not mounted ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area  213909504 bytes Fixed Size                  2095152 bytes Variable Size             125831120 bytes Database Buffers           79691776 bytes Redo Buffers                6291456 bytes SQL> select name,db_unique_name,open_mode,database_role,switchover_status from gv$database; NAME       DB_UNIQUE_NAME  OPEN_MODE    DATABASE_ROLE    SWITCHOVER_STAT ---------- --------------- ------------ ---------------- --------------- ORCL       primary         MOUNTED      PHYSICAL STANDBY TO PRIMARY SQL> alter database recover managed standby database cancel; Database altered.

三：将standby数据库转换为主库，出现下面的错误信息，需要手动rename下在线日志组

SQL> alter database commit to switchover to primary; alter database commit to switchover to primary * ERROR at line 1: 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 SQL> alter system set db_create_file_dest='+DATA'; System altered. SQL> alter system set db_create_online_log_dest_1='+DATA'; System altered. SQL> alter system set standby_file_management=manual; System altered. SQL> alter database rename file '/u01/app/oracle/oradata/orcl/redo01.log' to '+DATA/ORCL/ONLINELOG/redo01.log'; Database altered. SQL> alter system set standby_file_management=auto; System altered. SQL> alter database commit to switchover to primary; Database altered. SQL> alter database open; Database altered. SQL> select name,db_unique_name,open_mode,database_role,switchover_status from gv$database; NAME       DB_UNIQUE_ OPEN_MODE    DATABASE_ROLE     SWITCHOVER_STATUS ---------- ---------- ------------ ----------------- -------------------- ORCL       standby    READ WRITE   PRIMARY           SESSIONS ACTIVE SQL> alter database recover managed standby database disconnect from session; Database altered. SQL> select name,db_unique_name,open_mode,database_role,switchover_status from gv$database; NAME       DB_UNIQUE_NAME  OPEN_MODE    DATABASE_ROLE    SWITCHOVER_STAT ---------- --------------- ------------ ---------------- --------------- ORCL       primary         MOUNTED      PHYSICAL STANDBY SESSIONS ACTIVE

四：启动节点2数据库实例

[root@rac2 ~]# su - oracle [oracle@rac2 ~]$ sqlplus /nolog SQL*Plus: Release 10.2.0.5.0 - Production on Mon Jan 9 21:13:28 2012 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  213909504 bytes Fixed Size                  2095152 bytes Variable Size             125831120 bytes Database Buffers           79691776 bytes Redo Buffers                6291456 bytes Database mounted. Database opened.

五：测试

SQL> select name,db_unique_name,open_mode,database_role,switchover_status from gv$database; NAME       DB_UNIQUE_ OPEN_MODE    DATABASE_ROLE     SWITCHOVER_STATUS ---------- ---------- ------------ ----------------- -------------------- ORCL       standby    READ WRITE   PRIMARY           SESSIONS ACTIVE ORCL       standby    READ WRITE   PRIMARY           SESSIONS ACTIVE SQL> select owner,table_name from dba_tables where owner like 'TEST%';   OWNER                TABLE_NAME -------------------- -------------------- TEST1                SOURCE TEST1                MIGRATE TEST2                SOURCE2 SQL> select count(*) from test1.source;   COUNT(*) ----------    2363928 SQL> select count(*) from test1.migrate;   COUNT(*) ----------          1 SQL> select count(*) from test2.source2;   COUNT(*) ----------    1181964 节点1数据库实例： SQL> archive log list; Database log mode              Archive Mode Automatic archival             Enabled Archive destination            +FRA/orcl/archivelog Oldest online log sequence     47 Next log sequence to archive   48 Current log sequence           48 节点2数据库实例： SQL> archive log list; Database log mode              Archive Mode Automatic archival             Enabled Archive destination            +FRA/orcl/archivelog Oldest online log sequence     22 Next log sequence to archive   24 Current log sequence           24 备库： SQL> select first_time,next_time,sequence#,applied,thread# from v$archived_log where thread#=1 and sequence# >40 ; FIRST_TIME       NEXT_TIME         SEQUENCE# APP    THREAD# ---------------- ---------------- ---------- --- ---------- 2012-01-20:24:52 2012-01-20:24:58         41 YES          1 2012-01-20:24:52 2012-01-20:24:58         41 YES          1 2012-01-20:24:58 2012-01-20:25:51         42 YES          1 2012-01-20:24:58 2012-01-20:25:51         42 YES          1 2012-01-20:25:51 2012-01-20:40:18         43 YES          1 2012-01-20:25:51 2012-01-20:40:18         43 YES          1 2012-01-20:40:18 2012-01-20:45:09         44 YES          1 2012-01-20:40:18 2012-01-20:45:09         44 YES          1 2012-01-20:45:09 2012-01-20:49:01         45 YES          1 2012-01-20:45:09 2012-01-20:49:01         45 NO           1 2012-01-20:49:01 2012-01-20:49:43         46 YES          1 FIRST_TIME       NEXT_TIME         SEQUENCE# APP    THREAD# ---------------- ---------------- ---------- --- ---------- 2012-01-20:49:01 2012-01-20:49:43         46 NO           1 SQL> select first_time,next_time,sequence#,applied,thread# from v$archived_log where thread#=2 and sequence# >20 ; FIRST_TIME       NEXT_TIME         SEQUENCE# APP    THREAD# ---------------- ---------------- ---------- --- ---------- 2012-01-20:45:11 2012-01-20:49:41         21 YES          2 2012-01-20:45:11 2012-01-20:49:41         21 NO           2 主库上切换日志测试日志是否能成功应用到备库 SQL> alter system archive log current; System altered. SQL> alter system archive log current; System altered. 备库查询： SQL> select first_time,next_time,sequence#,applied,thread# from v$archived_log where thread#=2 and sequence# >20 ; FIRST_TIME       NEXT_TIME         SEQUENCE# APP    THREAD# ---------------- ---------------- ---------- --- ---------- 2012-01-20:45:11 2012-01-20:49:41         21 YES          2 2012-01-20:45:11 2012-01-20:49:41         21 NO           2 2012-01-21:09:32 2012-01-21:10:58         22 YES          2 2012-01-21:14:02 2012-01-21:14:13         23 YES          2 2012-01-21:14:13 2012-01-21:25:03         24 YES          2 2012-01-21:25:03 2012-01-21:29:51         25 NO           2 SQL> select first_time,next_time,sequence#,applied,thread# from v$archived_log where thread#=1 and sequence# >40 ; FIRST_TIME       NEXT_TIME         SEQUENCE# APP    THREAD# ---------------- ---------------- ---------- --- ---------- 2012-01-20:24:52 2012-01-20:24:58         41 YES          1 2012-01-20:24:52 2012-01-20:24:58         41 YES          1 2012-01-20:24:58 2012-01-20:25:51         42 YES          1 2012-01-20:24:58 2012-01-20:25:51         42 YES          1 2012-01-20:25:51 2012-01-20:40:18         43 YES          1 2012-01-20:25:51 2012-01-20:40:18         43 YES          1 2012-01-20:40:18 2012-01-20:45:09         44 YES          1 2012-01-20:40:18 2012-01-20:45:09         44 YES          1 2012-01-20:45:09 2012-01-20:49:01         45 YES          1 2012-01-20:45:09 2012-01-20:49:01         45 NO           1 2012-01-20:49:01 2012-01-20:49:43         46 YES          1 FIRST_TIME       NEXT_TIME         SEQUENCE# APP    THREAD# ---------------- ---------------- ---------- --- ---------- 2012-01-20:49:01 2012-01-20:49:43         46 NO           1 2012-01-21:09:32 2012-01-21:10:51         47 YES          1 2012-01-21:10:51 2012-01-21:25:05         48 YES          1 2012-01-21:25:05 2012-01-21:29:48         49 YES          1 主库建表测试，同时切换日志 SQL> create table test1.maa as select * from test1.migrate; Table created. SQL> alter system archive log current; System altered. 备库的alert日志摘要信息 [oracle@server49 ~]$ tail -f /u01/app/oracle/admin/orcl/bdump/alert_orcl.log Mon Jan 09 21:33:39 CST 2012 Primary database is in MAXIMUM PERFORMANCE mode Mon Jan 09 21:33:40 CST 2012 Primary database is in MAXIMUM PERFORMANCE mode Mon Jan 09 21:33:41 CST 2012 Media Recovery Log /u01/app/oracle/flash_recovery_area/PRIMARY/archivelog/2012_01_09/o1_mf_1_50_7jotz6nc_.arc Media Recovery Log /u01/app/oracle/flash_recovery_area/PRIMARY/archivelog/2012_01_09/o1_mf_2_26_7jotz6wr_.arc Media Recovery Waiting for thread 2 sequence 27 (in transit) 将备库启动到只读状态，查看数据 SQL> alter database recover managed standby database cancel; Database altered. SQL> alter database open read only; Database altered. SQL> select * from test1.maa; A -------------------- successful SQL> alter database recover managed standby database disconnect from session; Database altered.

六：收尾工作

SQL> @$ORACLE_HOME/rdbms/admin/catclust.sql; PL/SQL procedure successfully completed. [oracle@rac1 ~]$ srvctl add database -d orcl -o $ORACLE_HOME [oracle@rac1 ~]$ srvctl add instance -d orcl -i orcl1 -n rac1 [oracle@rac1 ~]$ srvctl add instance -d orcl -i orcl1 -n rac2 [oracle@rac1 ~]$ srvctl add instance -d orcl -i orcl2 -n rac2 [oracle@rac1 ~]$ crs_stat -t -v Name           Type           R/RA   F/FT   Target    State     Host        ---------------------------------------------------------------------- ora.orcl.db    application    0/0    0/1    ONLINE    ONLINE    rac1        ora....l1.inst application    0/5    0/0    ONLINE    ONLINE    rac1        ora....l2.inst application    0/5    0/0    ONLINE    ONLINE    rac2        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

本文出自 “斩月” 博客，谢绝转载！