原DB是Linux Heartbeat的HA系统。业务是7*24的。所以迁移要考虑宕机时间的问题。我考虑的是先将DG环境搭建好。这样迁移过程中宕机的就2部分时间。一是exp/imp的时间。还有一个就是Data Guardcopy同步的时间。因为数据库不大。所以时间应该不会太长,如果不出什么意外的话。
搬迁的方案:
1.先正在运行的Database用逻辑导出的方式导出到dump文件。这个过程需要停机。
2.在搬迁之前先将Data Guard服务的OS和数据库安装好。这样可以减少系统的宕机时间。
3.将导出的dmp文件导入新的服务器,并重建DG环境。
4.测试系统能否正常工作。
说明:因为原来的服务器为4G内存。上线之后的为8G。所以在新的DG环境上。计划SGA分4.8G内存。PGA 1.6G。剩下的给OS。
该方案只需要宕机一次。宕机时间按如下计算:
1.dmp导出时间。因为数据文件在20G左右。估计dmp需要时间在30分钟左右。
2.imp时间。这个预计一个小时。
3.一旦imp成功之后,DG同步,可以用最简单的方式来实现。就是把导入之后的数据文件直接copy到备库。这个过程估计在30分钟左右。
4.所以估计需要2个小时的宕机时间。留一点冗余时间。所以对小额支付DB搬迁考虑申请4个小时的宕机时间。时间段可以在凌晨2:00 –早上7:00.
风险评估:
1. DG环境不可用。因为原来HA环境没有破坏,如果DG不可用,可以直接将系统切换到原来的HA系统。
2. DG环境后续的性能。这个需要慢慢调整,可以更具AWR报告来调整相关参数。
搬迁的操作步骤如下:
一.导出数据
导出导入命令:
Exp user/pwd file=/u01/qishun.dmp log=/u01/dave.log full=y
Imp user/pwd file=/u01/qishun.dmp log=/u01/dave.log full=y
二.安装数据库
2.1安装Oracle
2.1.1检查相关包
Oracle的安装需要如下包:
binutils-2.17.50.0.6
compat-libstdc++-33-3.2.3
elfutils-libelf-0.125
elfutils-libelf-devel-0.125
elfutils-libelf-devel-static-0.125
gcc-4.1.2
gcc-c++-4.1.2
glibc-2.5-24
glibc-common-2.5
glibc-devel-2.5
glibc-headers-2.5
kernel-headers-2.6.18
ksh-20060214
libaio-0.3.106
libaio-devel-0.3.106
libgcc-4.1.2
libgomp-4.1.2
libstdc++-4.1.2
libstdc++-devel-4.1.2
make-3.81
numactl-devel-0.9.8.i386
sysstat-7.0.2
from:
http://download.oracle.com/docs/cd/E11882_01/install.112/e16766/toc.htm#CIHFICFD
检查相关包:
rpm -q binutils-2.17.50.0.6 /
compat-libstdc++-33-3.2.3 /
elfutils-libelf-0.125 /
elfutils-libelf-devel-0.125 /
elfutils-libelf-devel-static-0.125 /
gcc-4.1.2 /
gcc-c++-4.1.2 /
glibc-2.5-24 /
glibc-common-2.5 /
glibc-devel-2.5 /
glibc-headers-2.5 /
kernel-headers-2.6.18 /
ksh-20060214 /
libaio-0.3.106 /
libaio-devel-0.3.106 /
libgcc-4.1.2 /
libgomp-4.1.2 /
libstdc++-4.1.2 /
libstdc++-devel-4.1.2 /
make-3.81 /
numactl-devel-0.9.8.i386 /
sysstat-7.0.2
2.1.2修改相关参数
在/etc/sysctl.conf添加如下内容:
kernel.shmall = 2097152
kernel.shmmax = 2147483648
kernel.shmmni = 4096
# semaphores: semmsl, semmns, semopm, semmni
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
net.ipv4.ip_local_port_range = 1024 65000
vi /etc/sysconfig/limits.conf
oracle soft memlock 5242880
oracle hard memlock 524280
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 65536
oracle hard nofile 65536
vi /etc/pam.d/login:
session required /lib/security/pam_limits.so
使参数生效:
sysctl -p
2.1.3创建用户和目录,修改用户参数
创建用户和密码:
[root@dg1 Server]# groupadd oinstall
[root@dg1 Server]# groupadd dba
[root@dg1 Server]# groupadd oper
[root@dg1 Server]# useradd -g oinstall -G dba oracle
[root@dg1 Server]# passwd oracle
Changing password for user oracle.
New UNIX password:
BAD PASSWORD: it is based on a dictionary word
Retype new UNIX password:
passwd: all authentication tokens updated successfully.
创建相关目录:
[root@dg2 Server]# mkdir -p /u01/app/oracle/product/10.2.0/db_1
[root@dg2 Server]# chown -R oracle.oinstall /u01
[root@dg2 Server]# chmod -R 777 /u01
修改OS版本
[root@dg2 Server]# cat /etc/redhat-release
#Red Hat Enterprise Linux Server release 5.4 (Tikanga)
redhat-4
设置用户变量:
[oracle@dg2 ~]$ cat ~/.bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/bin
export PATH
# Oracle Settings
TMP=/tmp; export TMP
TMPDIR=$TMP; export TMPDIR
ORACLE_BASE=/u01/app/oracle; export ORACLE_BASE
ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1; export ORACLE_HOME
ORACLE_SID=orcl; export ORACLE_SID
ORACLE_TERM=xterm; export ORACLE_TERM
PATH=/usr/sbin:$PATH; export PATH
PATH=$ORACLE_HOME/bin:$PATH; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib; export LD_LIBRARY_PATH
CLASSPATH=$ORACLE_HOME/jre:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib; export CLASSPATH
if [ $USER = "oracle" ]; then
if [ $SHELL = "/bin/ksh" ]; then
ulimit -p 16384
ulimit -n 65536
else
ulimit -u 16384 -n 65536
fi
fi
2.1.4在主库安装软件和实例,在备库只安装软件
2.1.5修改/etc/oratab将所需启动的实例修改为Y
orcl:/u01/app/oracle/product/10.2.0/db_1:Y
将第一步导出的dump文件导入到主库中。然后开始搭建DG环境。
三.搭建DG环境
3.1主库端准备工作
1.主库设置为force logging模式
SQL> alter database force logging;
2.主库设为归档模式
SQL> archive log list;
SQL> shutdown immediate
SQL> startup mount
SQL> alter database archivelog;
SQL> archive log list;
3.添加redo log file
添加一个新的Standby Redologs组(注意组号不要与当前存在的Online Redologs组重复),并为该组指定一个成员:
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/u01/app/oracle/oradata/orcl/redo04.log') size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/u01/app/oracle/oradata/orcl/redo05.log') size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/u01/app/oracle/oradata/orcl/redo06.log') size 50M;
SQL> ALTER DATABASE ADD STANDBY LOGFILE GROUP 7 ('/u01/app/oracle/oradata/orcl/redo07.log') size 50M;
4.创建备库的密码文件和控制文件
SQL> alter database create standby controlfile as '/u01/control01.ctl';
--判断一个数据库是Primary还是Standby,就是通过控制文件来判断的。
orapwd file=/u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl password=admin
如果已经存在,就不用创建了。缺省情况下,win下口令文件的格式是pwdsid.ora,unix下的格式是orapwSID(大小写敏感)
5.修改初始化参数文件
SQL> create pfile='/u01/initorcl.ora' from spfile;
在initorcl.ora添加如下内容:
*.DB_UNIQUE_NAME='orcl_pd'
*.log_archive_dest_1='location=/u01/archive'
*.log_archive_dest_2='SERVICE=orcl_st'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=ENABLE
*.standby_file_management='AUTO'
*.standby_archive_dest='/u01/archive'
*.FAL_SERVER='orcl_st'
*.FAL_CLIENT='orcl_pd'
如果主库和备库的数据文件位置不同,还需要加如下2个参数:
*.log_file_name_convert='/u02/oradata/orcl/','/u03/oradata/orcl/'
*.db_file_name_convert='/u02/oradata/orcl/','/u03/oradata/orcl/'
6.修改tnsnames.ora和 Listener.ora文件
[oracle@dg1 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL_ST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
ORCL_PD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
[oracle@dg1 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
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 =
(SID_NAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(GLOBAL_DBNAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg1)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
SID_LIST_LISTENER配置的是静态注册,如果没有该参数,而且Data Guard启动顺序又不正确,那么在主库可能会报PING[ARC1]: Heartbeat failed to connect to standby 'orcl_st'. Error is 12514.错误,导致归档无法完成。
7.用修改之后的pfile启动数据库,并创建spfile.
[oracle@dg1 dbs]$ sqlplus /nolog
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Nov 5 03:48:19 2010
Copyright (c) 1982, 2005, Oracle.All rights reserved.
SQL> conn / as sysdba
Connected to an idle instance.
SQL> startup pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora'
ORACLE instance started.
Total System Global Area184549376 bytes
Fixed Size1218412 bytes
Variable Size62916756 bytes
Database Buffers117440512 bytes
Redo Buffers2973696 bytes
Database mounted.
Database opened.
SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora';
File created.
3.2备库配置
这里方便起见,我们和主库的目录一致。
1.创建目录
[oracle@dg2 u01]$ mkdir -p $ORACLE_BASE/oradata/orcl
[oracle@dg2 u01]$ mkdir -p $ORACLE_BASE/admin/orcl/adump
[oracle@dg2 u01]$ mkdir -p $ORACLE_BASE/admin/orcl/bdump
[oracle@dg2 u01]$ mkdir -p $ORACLE_BASE/admin/orcl/cdump
[oracle@dg2 u01]$ mkdir -p $ORACLE_BASE/admin/orcl/dpdump
[oracle@dg2 u01]$ mkdir -p $ORACLE_BASE/admin/orcl/pfile
[oracle@dg2 u01]$ mkdir -p $ORACLE_BASE/admin/orcl/udump
[oracle@dg2 u01]$ ls
app
[oracle@dg2 u01]$ cd app/
[oracle@dg2 app]$ ls
oracle
[oracle@dg2 app]$ cd oracle/
[oracle@dg2 oracle]$ ls
adminoradataoraInventoryproduct
[oracle@dg2 oracle]$ cd admin/
[oracle@dg2 admin]$ ls
orcl
[oracle@dg2 admin]$ cd orcl/
[oracle@dg2 orcl]$ ls
adumpbdumpcdumpdpdumppfileudump
2.修改备库的tnsnames.ora和Listener.ora文件
[oracle@dg2 admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.
ORCL_ST =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.3)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
ORCL_PD =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.6.1)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = orcl)
)
)
[oracle@dg2 admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.
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 =
(SID_NAME = orcl)
(ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
(GLOBAL_DBNAME = orcl)
)
)
LISTENER =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = dg2)(PORT = 1521))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)
3.将主库的密码文件,控制文件,数据文件,参数文件,日志文件copy到备库。
说明一点,这个控制文件是我们自己创建的standby控制文件。将copy过来的控制文件再复制三份就可以了。主备的控制文件是不一样的。这里除了采用直接copy文件之外,还可以采用Rman恢复来做。直接copy需要停数据库,如果采用RMAN的话,就不需要停机了。
[oracle@dg1 dbs]$ ls
hc_orcl.datinitdw.orainit.orainitorcl.oralkORCLorapworclspfileorcl.ora
[oracle@dg1 dbs]$ scp initorcl.ora 192.168.6.3://u01/app/oracle/product/10.2.0/db_1/dbs
oracle@192.168.6.3's password:
initorcl.ora100% 13321.3KB/s00:00
[oracle@dg1 dbs]$ scp orapworcl 192.168.6.3://u01/app/oracle/product/10.2.0/db_1/dbs
oracle@192.168.6.3's password:
orapworcl100% 15361.5KB/s00:00
[oracle@dg1 dbs]$
[oracle@dg1 orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@dg1 orcl]$ ls
control01.ctlredo01.logredo04.logredo07.logtemp01.dbf
control02.ctlredo02.logredo05.logsysaux01.dbfundotbs01.dbf
control03.ctlredo03.logredo06.logsystem01.dbfusers01.dbf
[oracle@dg1 orcl]$ scp * 192.168.6.3://u01/app/oracle/oradata/orcl
oracle@192.168.6.3's password:
control01.ctl100% 6896KB3.4MB/s00:02
control02.ctl100% 6896KB1.7MB/s00:04
control03.ctl100% 6896KB3.4MB/s00:02
redo01.log100%50MB 497.1KB/s01:43
redo02.log100%50MB 753.0KB/s01:08
redo03.log100%50MB 453.1KB/s01:53
redo04.log100%50MB 930.9KB/s00:55
redo05.log100%50MB 753.0KB/s01:08
redo06.log100%50MB1.6MB/s00:31
redo07.log100%50MB3.9MB/s00:13
sysaux01.dbf100%240MB3.4MB/s01:11
system01.dbf100%480MB4.8MB/s01:40
temp01.dbf100%20MB5.0MB/s00:04
undotbs01.dbf100%25MB6.3MB/s00:04
users01.dbf100% 5128KB5.0MB/s00:00
[oracle@dg1 orcl]$
这里注意一点,我们一起复制过来的控制文件是主库的。我们需要用standby的控制文件。先把复制过来的control file删除掉。在把之前的复制过来,在复制三份。
[root@dg2 orcl]# rm *.ctl
rm: remove regular file `control01.ctl'? y
rm: remove regular file `control02.ctl'? y
rm: remove regular file `control03.ctl'? y
[oracle@dg1 u01]$ scp control01.ctl 192.168.6.3://u01/app/oracle/oradata/orcl
oracle@192.168.6.3's password:
control01.ctl100% 6896KB6.7MB/s00:01
[oracle@dg1 u01]$
[root@dg2 orcl]# cp control01.ctl control02.ctl
[root@dg2 orcl]# cp control01.ctl control03.ctl
[root@dg2 orcl]# ls
control01.ctlredo01.logredo04.logredo07.logtemp01.dbf
control02.ctlredo02.logredo05.logsysaux01.dbfundotbs01.dbf
control03.ctlredo03.logredo06.logsystem01.dbfusers01.dbf
[root@dg2 orcl]#
4.修改pfile参数,并用pfile启动数据库,在创建spfile
在pfile里面添加如下内容:
*.DB_UNIQUE_NAME='orcl_st'
*.log_archive_dest_1='location=/u01/archive'
*.log_archive_dest_2='SERVICE=orcl_pd'
*.LOG_ARCHIVE_DEST_STATE_1=ENABLE
*.LOG_ARCHIVE_DEST_STATE_2=DEFER
*.standby_file_management='AUTO'
*.standby_archive_dest='/u01/archive'
*.FAL_SERVER='orcl_pd'
*.FAL_CLIENT='orcl_st'
5.启动备库至mount状态
SQL> startup mount pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora'
ORACLE instance started.
SQL> create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initorcl.ora';
至此,Data Guard的操作已经完成,下面来开始验证。
3.3验证备库
注意Data Guard启动顺序:
启动顺序:先standby ,后primary;
关闭顺序:先primary后standby;
在备库将实例启动到mount状态:
SQL> startup nomount;
SQL>alter database mount standby database ;
---SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
SQL>alter database recover managed standby database disconnect from session;
在备库启动监听:
$lsnrctl start
在主库启动实例:
SQL> startup;
在主库启动监听:
$lsnrctl start
在主库验证归档目录是否有效:
SQL> SELECT STATUS,DESTINATION, ERROR FROM V$ARCHIVE_DEST;
如果有错误,要排查原因。
主库:
SQL> alter system switch logfile;
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
12
备库:
SQL> startup nomount
ORACLE instance started.
Total System Global Area184549376 bytes
Fixed Size1218412 bytes
Variable Size62916756 bytes
Database Buffers117440512 bytes
Redo Buffers2973696 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL> select sequence#,applied from v$archived_log;
SEQUENCE# APP
---------- ---
8 YES
9 YES
10 YES
11 YES
SQL> /
SEQUENCE# APP
---------- ---
8 YES
9 YES
10 YES
11 YES
12 YES
SQL>
主备查询结果一致,Data Guard搭建结束。
注意:如果在主库执行alter database clear unarchived logfile或alter database open resetlogs , 则dataguard要重建。
------------------------------------------------------------------------------