DB 迁移到Data Guard 实施方案
原DB是?
搬迁的方案：
?
1.??????2.??????3.??????4.???????
说明：因为原来的服务器为4G内存。?
该方案只需要宕机一次。1.??????2.??????3.??????4.???????
?
风险评估：
?????????????
?
搬迁的操作步骤如下：
?
一．?
导出导入命令：
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?
2.1.1?
Oraclebinutils-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
?
fromhttp://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
???????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?
将第一步导出的dump?
?
三．?
3.1?
1.?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.添加一个新的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';
--orapwd file=/u01/app/oracle/product/10.2.0/db_1/dbs/orapworcl password=admin
如果已经存在，就不用创建了。?
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.?
[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 =
?????????????????????????
ORCL_PD =
?????????????????????????
?
[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 =
?????????????????????????????????????????????
LISTENER =
???????????????????
SID_LIST_LISTENER?
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.??
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 Area?Fixed Size?????????????????Variable Size?????????????Database Buffers?????????Redo Buffers???????????????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
admin?[oracle@dg2 oracle]$ cd admin/
[oracle@dg2 admin]$ ls
orcl
[oracle@dg2 admin]$ cd orcl/
[oracle@dg2 orcl]$ ls
adump??
2.?
[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 =
?????????????????????????
ORCL_PD =
?????????????????????????
?
[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 =
???????????????????????
?????????????????????)
?
LISTENER =
???????????????????
?
3.?
说明一点，这个控制文件是我们自己创建的standby?
[oracle@dg1 dbs]$ ls
hc_orcl.dat?[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.ora?????????????????????????????????????????????????????[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:
orapworcl??????????????????????????????????[oracle@dg1 dbs]$
?
[oracle@dg1 orcl]$ pwd
/u01/app/oracle/oradata/orcl
[oracle@dg1 orcl]$ ls
control01.ctl?control02.ctl?control03.ctl?[oracle@dg1 orcl]$ scp * 192.168.6.3://u01/app/oracle/oradata/orcl
oracle@192.168.6.3's password:
control01.ctl????????????????????????????????????????????????????control02.ctl????????????????????????????????????????????????????control03.ctl????????????????????????????????????????????????????redo01.log?????????????redo02.log???????????????????????????????????????????????????????redo03.log???????????????????????????????????????????????????????redo04.log???????????????????????????????????????????????????????redo05.log???????????????????????????????????????????????????????redo06.log?????????????????????????redo07.log???????????????????????????????????????????????????????sysaux01.dbf?????????????????????????????????????????????????????system01.dbf?????????????????????????????????????????????????????temp01.dbf???????????????????????????????????????????????????????undotbs01.dbf??????????????????????????????????users01.dbf??????????????????????????????????????????????????????[oracle@dg1 orcl]$
?
这里注意一点，我们一起复制过来的控制文件是主库的。?
[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.ctl????????????????????????????????????????????????????[oracle@dg1 u01]$
?
[root@dg2 orcl]# cp control01.ctl control02.ctl
[root@dg2 orcl]# cp control01.ctl control03.ctl
[root@dg2 orcl]# ls
control01.ctl?control02.ctl?control03.ctl?[root@dg2 orcl]#
?
?
4.?
在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.?
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?
在备库将实例启动到mountSQL> 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#)
--------------
????????????
备库：
SQL> startup nomount
ORACLE instance started.
?
Total System Global Area?Fixed Size????????????Variable Size?????????????Database Buffers?????????Redo Buffers???????????????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
---------- ---
???????????????????????????????
SQL> /
?
?SEQUENCE# APP
---------- ---
??????????????????????????????????????
SQL>
?
?
?
?
主备查询结果一致，Data Guard?
?
注意：如果在主库执行?alter database clear unarchived logfile或alter database open resetlogs , 则dataguard要重建。
