分类： Oracle
2016-05-21 15:55:47
使用Oracle Dataguard实现从单机到RAC转换
由于原有单实例服务器不能满足需求，需要把数据库从把单机实例迁移到RAC上.
迁移的方式有很多种，但是主要可以分为逻辑迁移和物理迁移.
逻辑迁移使用的技术有expdp，goldengate，dblink等.物理方式主要的技术有transport tablespace,
rconfig方式转换,Dataguard(DG)等．从数据可靠性和停机时间来考虑，使用DG转换的方式最为
合适.
环境:oracle 12.1.0.2,单实例IP 192.168.56.5,RAC1节点的VIP 192.168.56.71,RAC2节点的
VIP 192.168.56.72.在RAC两个节点上安装好GI和ORACLE RDBMS软件(此处省略过程了).
在RAC1节点上，编辑一个文本文件，用来启动一个单实例．参数主要分为两个部分，一是所有节点
上需要相同的参数，二是每个实例对应特殊的参数.
在GI的diskgroup中，确保相对应的目录提前建好,比如+DG_DATA/rac/controlfile．
[oracle@rac1]$ vi /home/oracle/tmp.ora
*.db_name=c12
*.db_unique_name=c12stby
*.control_files='+DG_DATA/rac/controlfile/control01.ctl','+DG_DATA/rac/controlfile/control02.ctl'
*.compatible=12.1.0.2.0
*.memory_target=1G
*.cluster_database=true
*.enable_pluggable_database=true
*.db_file_name_convert='/u01/app/oracle/oradata/c12','+DG_DATA'
*.log_file_name_convert='/u01/app/oracle/oradata/c12','+DG_DATA'
*.db_create_file_dest='+DG_DATA'
*.db_create_online_log_dest_1='+DG_DATA'
*.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=c12stby'
rac1.instance_name=rac1
rac2.instance_name=rac2
rac1.instance_number=1
rac2.instance_number=2
rac1.undo_tablespace='UNDOTBS1'
rac2.undo_tablespace='UNDOTBS2'
rac1.thread=1
rac2.thread=2
rac1.local_listener='(address=(protocol=TCP)(HOST=192.168.56.71)(PORT=1521))'
rac1.remote_listener='(address=(protocol=TCP)(HOST=192.168.56.73)(PORT=1521))'
rac2.local_listener='(address=(protocol=TCP)(HOST=192.168.56.72)(PORT=1521))'
rac2.remote_listener='(address=(protocol=TCP)(HOST=192.168.56.73)(PORT=1521))'
在单实例和RAC的两个节点，增加TNS条目
c12 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.5)(PORT = 1521))
)
(CONNECT_DATA =
(SID = c12)
(SERVER = DEDICATED)
)
)
12cstby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.71)(PORT = 1521))
)
(CONNECT_DATA =
(service_name = c12)
(SERVER = DEDICATED)
)
)
listener需要静态注册相关实例，在RAC1节点上的GI拥有者$ORACLE_HOME/network/admin
中的listener.ora中增加如下内容(非RDBMS拥有者)，单实例中也需要静态注册，然后listner reload
生效，这个过程不会对现有的连接产生影响.
SID_LIST_LISTENER =
(SID_DESC =
(GLOBAL_DBNAME = c12)
(ORACLE_HOME = /u01/app/oracle/12.1.0.2/db_1)
(SID_NAME = rac1)
)
从单机上复制密码文件到RAC上的两个节点上
[oracle@localhost dbs]$ scp orapwc12 192.168.56.61:/u01/app/oracle/12.1.0.2/db_1/dbs/orapwrac1
oracle@192.168.56.61's password:
orapwc12
100% 7680 7.5KB/s 00:00
[oracle@localhost dbs]$ scp orapwc12 192.168.56.62:/u01/app/oracle/12.1.0.2/db_1/dbs/orapwrac2
oracle@192.168.56.62's password:
orapwc12
在RAC1节点上使用RAMN 执行如下脚本
[oracle@rac1]$ORACLE_HOME/rman target sys/sys123@c12 auxiliary sys/sys123@c12stdby
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel c3 device type disk;
allocate auxiliary channel c4 device type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'c12','c12stby'
set log_archive_max_processes='5'
set standby_file_management='AUTO'
set db_unique_name='c12stby'
set db_file_name_convert='/u01/app/oracle/oradata/c12','+DG_DATA'
set log_file_name_convert='/u01/app/oracle/oradata/c12','+DG_DATA'
set db_create_file_dest='+DG_DATA'
set db_create_online_log_dest_1='+DG_DATA'
set db_recovery_file_dest='+DG_DATA'
set audit_file_dest='/u01/app/oracle/audit'
set control_files='+DG_DATA/rac/controlfile/control01.ctl','+DG_DATA/rac/controlfile/control02.ctl'
set fal_client='c12stby'
set fal_server='c12'
set log_archive_config='dg_config=(c12,c12stby)'
nofilenamecheck;
sql channel c2 "alter system set log_archive_config=''dg_config=(c12,c12stby)''";
sql channel c2 "alter system set log_archive_dest_2=''service=c12stby valid_for=(online_logfiles,primary_role) db_unique_name=c12stby''";
sql channel c2 "alter system set standby_file_management=AUTO";
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
脚本执行完成后，在RAC1节点上新建密码文件,并保存到diskgroup中.
SQL> create spfile='+DG_DATA/spfilec12.ora' from pfile='/home/oracle/tmp.ora';
File created.
在RAC1节点上增加实例和数据库资源.
[oracle@rac1 ~]$ srvctl add database -db c12 -oraclehome /u01/app/oracle/12.1.0.2/db_1
[oracle@rac1 ~]$ srvctl add instance -db c12 -instance rac1 -node rac1
[oracle@rac1 ~]$ srvctl add instance -db c12 -instance rac2 -node rac2
[oracle@rac1 ~]$ srvctl modify database -db c12 -spfile +DG_DATA/spfilec12.ora -startoption mount
[oracle@rac1 ~]$ srvctl config database -db c12
Database unique name: c12
Database name:
Oracle home: /u01/app/oracle/12.1.0.2/db_1
Oracle user: oracle
Spfile: +DG_DATA/spfilec12.ora
...............................
[oracle@rac1 ~]$ srvctl status database -db c12
Instance rac1 is running on node rac1
Instance rac2 is running on node rac2
在RAC1节点上增加standby logfile group
alter database add standby logfile thread 1 group 9 '+DG_DATA' size 100m;
alter database add standby logfile thread 2 group 10 '+DG_DATA' size 100m;
alter database add standby logfile thread 2 group 11 '+DG_DATA' size 100m;
alter database add standby logfile thread 2 group 12 '+DG_DATA' size 100m;
alter database add standby logfile thread 2 group 13 '+DG_DATA' size 100m;
alter database add standby logfile thread 2 group 14 '+DG_DATA' size 100m;
alter database add standby logfile thread 1 group 15 '+DG_DATA' size 100m;
alter database add standby logfile thread 1 group 16 '+DG_DATA' size 100m;
alter database add standby logfile thread 1 group 17 '+DG_DATA' size 100m;
alter database add standby logfile thread 1 group 18 '+DG_DATA' size 100m;
停止单实例上的应用程序，开始切换
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
在RAC1节点上转为主库
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
SQL> alter database open;
Database altered.
SQL> select thread#,status from v$thread;
THREAD# STATUS
---------- ------
1 OPEN
2 CLOSED
thread 2现在还是closed状态，需要在RAC2节点上再次打开数据库
SQL> alter database open;
Database altered.
两个thread都是处于打开状态
SQL> select thread#,status from v$thread;
THREAD# STATUS
---------- ------
1 OPEN
2 OPEN
在RAC2节点上打开数据库时，在alert.log中，我们可以看到实例会自动创建相关的undo tablespace表空间,
而不需要手动再去创建undo表空间了.
create SMALLFILE UNDO TABLESPACE UNDOTBS2 datafile SIZE 225443840 AUTOEXTEND ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
Pluggable Database PDB$SEED Dictionary check complete
Database Characterset for PDB$SEED is AL32UTF8
Completed: create SMALLFILE UNDO TABLESPACE UNDOTBS2 datafile SIZE 225443840 AUTOEXTEND ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32
修改数据库启动方式为open
[oracle@rac1 ~]$ srvctl modify database -db c12 -startoption open
从单实例到RAC的转换到此基本完成了.
由于原有单实例服务器不能满足需求，需要把数据库从把单机实例迁移到RAC上.
迁移的方式有很多种，但是主要可以分为逻辑迁移和物理迁移.
逻辑迁移使用的技术有expdp，goldengate，dblink等.物理方式主要的技术有transport tablespace,
rconfig方式转换,Dataguard(DG)等．从数据可靠性和停机时间来考虑，使用DG转换的方式最为
合适.
环境:oracle 12.1.0.2,单实例IP 192.168.56.5,RAC1节点的VIP 192.168.56.71,RAC2节点的
VIP 192.168.56.72.在RAC两个节点上安装好GI和ORACLE RDBMS软件(此处省略过程了).
在RAC1节点上，编辑一个文本文件，用来启动一个单实例．参数主要分为两个部分，一是所有节点
上需要相同的参数，二是每个实例对应特殊的参数.
在GI的diskgroup中，确保相对应的目录提前建好,比如+DG_DATA/rac/controlfile．
[oracle@rac1]$ vi /home/oracle/tmp.ora
*.db_name=c12
*.db_unique_name=c12stby
*.control_files='+DG_DATA/rac/controlfile/control01.ctl','+DG_DATA/rac/controlfile/control02.ctl'
*.compatible=12.1.0.2.0
*.memory_target=1G
*.cluster_database=true
*.enable_pluggable_database=true
*.db_file_name_convert='/u01/app/oracle/oradata/c12','+DG_DATA'
*.log_file_name_convert='/u01/app/oracle/oradata/c12','+DG_DATA'
*.db_create_file_dest='+DG_DATA'
*.db_create_online_log_dest_1='+DG_DATA'
*.log_archive_dest_1='location=/u01/app/oracle/arch valid_for=(all_logfiles,all_roles) db_unique_name=c12stby'
rac1.instance_name=rac1
rac2.instance_name=rac2
rac1.instance_number=1
rac2.instance_number=2
rac1.undo_tablespace='UNDOTBS1'
rac2.undo_tablespace='UNDOTBS2'
rac1.thread=1
rac2.thread=2
rac1.local_listener='(address=(protocol=TCP)(HOST=192.168.56.71)(PORT=1521))'
rac1.remote_listener='(address=(protocol=TCP)(HOST=192.168.56.73)(PORT=1521))'
rac2.local_listener='(address=(protocol=TCP)(HOST=192.168.56.72)(PORT=1521))'
rac2.remote_listener='(address=(protocol=TCP)(HOST=192.168.56.73)(PORT=1521))'
在单实例和RAC的两个节点，增加TNS条目
c12 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.5)(PORT = 1521))
)
(CONNECT_DATA =
(SID = c12)
(SERVER = DEDICATED)
)
)
12cstby =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.71)(PORT = 1521))
)
(CONNECT_DATA =
(service_name = c12)
(SERVER = DEDICATED)
)
)
listener需要静态注册相关实例，在RAC1节点上的GI拥有者$ORACLE_HOME/network/admin
中的listener.ora中增加如下内容(非RDBMS拥有者)，单实例中也需要静态注册，然后listner reload
生效，这个过程不会对现有的连接产生影响.
SID_LIST_LISTENER =
(SID_DESC =
(GLOBAL_DBNAME = c12)
(ORACLE_HOME = /u01/app/oracle/12.1.0.2/db_1)
(SID_NAME = rac1)
)
从单机上复制密码文件到RAC上的两个节点上
[oracle@localhost dbs]$ scp orapwc12 192.168.56.61:/u01/app/oracle/12.1.0.2/db_1/dbs/orapwrac1
oracle@192.168.56.61's password:
orapwc12
100% 7680 7.5KB/s 00:00
[oracle@localhost dbs]$ scp orapwc12 192.168.56.62:/u01/app/oracle/12.1.0.2/db_1/dbs/orapwrac2
oracle@192.168.56.62's password:
orapwc12
在RAC1节点上使用RAMN 执行如下脚本
[oracle@rac1]$ORACLE_HOME/rman target sys/sys123@c12 auxiliary sys/sys123@c12stdby
run{
allocate channel c1 type disk;
allocate channel c2 type disk;
allocate auxiliary channel c3 device type disk;
allocate auxiliary channel c4 device type disk;
duplicate target database for standby from active database
spfile
parameter_value_convert 'c12','c12stby'
set log_archive_max_processes='5'
set standby_file_management='AUTO'
set db_unique_name='c12stby'
set db_file_name_convert='/u01/app/oracle/oradata/c12','+DG_DATA'
set log_file_name_convert='/u01/app/oracle/oradata/c12','+DG_DATA'
set db_create_file_dest='+DG_DATA'
set db_create_online_log_dest_1='+DG_DATA'
set db_recovery_file_dest='+DG_DATA'
set audit_file_dest='/u01/app/oracle/audit'
set control_files='+DG_DATA/rac/controlfile/control01.ctl','+DG_DATA/rac/controlfile/control02.ctl'
set fal_client='c12stby'
set fal_server='c12'
set log_archive_config='dg_config=(c12,c12stby)'
nofilenamecheck;
sql channel c2 "alter system set log_archive_config=''dg_config=(c12,c12stby)''";
sql channel c2 "alter system set log_archive_dest_2=''service=c12stby valid_for=(online_logfiles,primary_role) db_unique_name=c12stby''";
sql channel c2 "alter system set standby_file_management=AUTO";
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
脚本执行完成后，在RAC1节点上新建密码文件,并保存到diskgroup中.
SQL> create spfile='+DG_DATA/spfilec12.ora' from pfile='/home/oracle/tmp.ora';
File created.
在RAC1节点上增加实例和数据库资源.
[oracle@rac1 ~]$ srvctl add database -db c12 -oraclehome /u01/app/oracle/12.1.0.2/db_1
[oracle@rac1 ~]$ srvctl add instance -db c12 -instance rac1 -node rac1
[oracle@rac1 ~]$ srvctl add instance -db c12 -instance rac2 -node rac2
[oracle@rac1 ~]$ srvctl modify database -db c12 -spfile +DG_DATA/spfilec12.ora -startoption mount
[oracle@rac1 ~]$ srvctl config database -db c12
Database unique name: c12
Database name:
Oracle home: /u01/app/oracle/12.1.0.2/db_1
Oracle user: oracle
Spfile: +DG_DATA/spfilec12.ora
...............................
[oracle@rac1 ~]$ srvctl status database -db c12
Instance rac1 is running on node rac1
Instance rac2 is running on node rac2
在RAC1节点上增加standby logfile group
alter database add standby logfile thread 1 group 9 '+DG_DATA' size 100m;
alter database add standby logfile thread 2 group 10 '+DG_DATA' size 100m;
alter database add standby logfile thread 2 group 11 '+DG_DATA' size 100m;
alter database add standby logfile thread 2 group 12 '+DG_DATA' size 100m;
alter database add standby logfile thread 2 group 13 '+DG_DATA' size 100m;
alter database add standby logfile thread 2 group 14 '+DG_DATA' size 100m;
alter database add standby logfile thread 1 group 15 '+DG_DATA' size 100m;
alter database add standby logfile thread 1 group 16 '+DG_DATA' size 100m;
alter database add standby logfile thread 1 group 17 '+DG_DATA' size 100m;
alter database add standby logfile thread 1 group 18 '+DG_DATA' size 100m;
停止单实例上的应用程序，开始切换
SQL> alter database commit to switchover to physical standby with session shutdown;
Database altered.
在RAC1节点上转为主库
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
SQL> alter database open;
Database altered.
SQL> select thread#,status from v$thread;
THREAD# STATUS
---------- ------
1 OPEN
2 CLOSED
thread 2现在还是closed状态，需要在RAC2节点上再次打开数据库
SQL> alter database open;
Database altered.
两个thread都是处于打开状态
SQL> select thread#,status from v$thread;
THREAD# STATUS
---------- ------
1 OPEN
2 OPEN
在RAC2节点上打开数据库时，在alert.log中，我们可以看到实例会自动创建相关的undo tablespace表空间,
而不需要手动再去创建undo表空间了.
create SMALLFILE UNDO TABLESPACE UNDOTBS2 datafile SIZE 225443840 AUTOEXTEND ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
Pluggable Database PDB$SEED Dictionary check complete
Database Characterset for PDB$SEED is AL32UTF8
Completed: create SMALLFILE UNDO TABLESPACE UNDOTBS2 datafile SIZE 225443840 AUTOEXTEND ON NEXT 5242880 MAXSIZE 34359721984 ONLINE
Due to limited space in shared pool (need 6094848 bytes, have 3981120 bytes), limiting Resource Manager entities from 2048 to 32
修改数据库启动方式为open
[oracle@rac1 ~]$ srvctl modify database -db c12 -startoption open
从单实例到RAC的转换到此基本完成了.