数据库迁移方案之DG方式迁移

一：环境信息1)主库（单实例）主机平台：AIX6.1数据库版本：11.2.0.3（psu5）2)备库（部署了crs）主机平台：AIX6.1数据库版本：11.2.0.3（psu5）二：方案背景由

一：环境信息

1)主库（单实例）

主机平台：AIX6.1

数据库版本：11.2.0.3（psu5）

2)备库（部署了crs）

主机平台：AIX6.1

数据库版本：11.2.0.3（psu5）

二：方案背景

由于业务量增加，数据库需要由单实例，改成两节点rac。为了减少停机时间，采用DG方式迁移。

三迁移方案：

1.检查数据库是否支持Data Guard（只有企业版才支持DG）

SQL> select * from v$option where parameter = 'Managed Standby'; PARAMETER VALUE ---------------------------------------------------------------- ---------------------------------------------------------------- Managed Standby TRUE

2.修改主库为归档模式及force logging状态
1）

SQL> alter database force logging; Database altered.

2）
SQL> archive log list;
如果未开归档，开启归档模式

alter system set log_archive_dest_2='location=/archlog/egap'; alter system set log_archive_format='egap_%t_%s_%r.arch' scope=spfile; --静态参数，重启后生效 shutdown immediate; startup mount; alter database archivelog; alter database open; archive log list;

3.创建备库pfile文件
在主库上创建pfile，修改，并添加DG备库所有参数，然后传至备库

SQL> create pfile='/data01/pfileegap' from spfile;

1）备库需要添加的参数
DB_UNIQUE_NAME;LOG_ARCHIVE_DEST_1;FAL_SERVER;FAL_CLIENT; STANDBY_FILE_MANAGEMENT=AUTO;DB_FILE_NAME_CONVERT;LOG_FILE_NAME_CONVERT
2）根据pfile中涉及到路径需要提前在备库主机上建好（如果主备库路径不一致要修改）
如主库*.audit_file_dest='/apps/oracle/admin/egap/adump'
我们在备库需要建 mkdir -p /apps/oracle/admin/egap/adump
cd /apps/oracle/admin
chown -R oracle:oinstall egap
chmod -R 775 egap

改变前参数文件

*.__db_cache_size=27648851968 *.__java_pool_size=67108864 *.__large_pool_size=67108864 *.__oracle_base='/apps/oracle'#ORACLE_BASE set from environment *.__pga_aggregate_target=10334765056 *.__sga_target=30937186304 *.__shared_io_pool_size=0 *.__shared_pool_size=2952790016 *.__streams_pool_size=0 *.audit_file_dest='/apps/oracle/admin/egap/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/data01/egap/control01.ctl','/data01/egap/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='egap' *.diagnostic_dest='/apps/oracle' *.log_archive_dest_1='location=/archlog/egap' *.log_archive_format='egap_%t_%s_%r.arch' *.open_cursors=300 *.pga_aggregate_target=10307502080 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=30922506240 *.undo_tablespace='UNDOTBS1'

改变后参数文件

*.__db_cache_size=27648851968 *.__java_pool_size=67108864 *.__large_pool_size=67108864 *.__oracle_base='/apps/oracle'#ORACLE_BASE set from environment *.__pga_aggregate_target=10334765056 *.__sga_target=30937186304 *.__shared_io_pool_size=0 *.__shared_pool_size=2952790016 *.__streams_pool_size=0 *.audit_file_dest='/apps/oracle/admin/egapdb/adump' *.audit_trail='db' *.compatible='11.2.0.0.0' *.control_files='/data01/egapdb/control01.ctl','/data01/egapdb/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_name='egap' *.diagnostic_dest='/apps/oracle' *.log_archive_dest_1='location=/archlog/egapdb1' *.log_archive_dest_2='SERVICE=primary LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=egap' *.log_archive_format='egapdb_%t_%s_%r.arch' *.open_cursors=300 *.pga_aggregate_target=10307502080 *.processes=150 *.remote_login_passwordfile='EXCLUSIVE' *.sga_target=30922506240 *.undo_tablespace='UNDOTBS1' *.DB_UNIQUE_NAME=egapdb *.FAL_SERVER=primary *.FAL_CLIENT=standby1 *.STANDBY_FILE_MANAGEMENT=AUTO *.DB_FILE_NAME_COnVERT='/data01/egap','/data01/egapdb' *.LOG_FILE_NAME_COnVERT='/data01/egap','/data01/egapdb' *.LOG_ARCHIVE_COnFIG='DG_COnFIG=(egap,egapdb)'

###注意db_file_name_convert和log_file_name_convert参数指定的路径要存在

4）根据修改后的pfile创建备库spfile

export ORACLE_SID=egapdb1 sqlplus / as sysdba create spfile from pfile;

--使用新生成的spfile检查是否能够成功启动实例

4.生成备库的密码文件
scp主库密码文件到备库，并改名
如果主库没有密码文件，需要新建

orapwd file=/oracle/app/oracle/product/v11.2.0.3/db_1/dbs/orapwegap password=oracle entries=5 ignorecase=y

--主库密码文件传到备库以后要重启备库

5.配置主备库监听及net服务
1）listener
--主库
一般建库后都会配置监听我们无需再配置
--备库（因为安装了cluster，所以用的是cluster的监听）
lsnrctl status 查看监听文件位置，并在监听文件中加入如下类容

LISTENER_SCAN3=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN3)))) # line added by Agent LISTENER_SCAN2=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN2)))) # line added by Agent #LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER)))) # line added by Agent LISTENER_SCAN1=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER_SCAN1)))) # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN1=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN2=ON # line added by Agent ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER_SCAN3=ON # line added by Agent --注意集群安装完毕以后，上面部分内容在监听中已经存在 SID_LIST_LISTENER = (SID_LIST = (SID_DESC = (SID_NAME = PLSExtProc) (ORACLE_HOME = /apps/oracle/product/11.2.0.3/db_1) (PROGRAM = extproc) ) (SID_DESC = (ORACLE_HOME = /apps/oracle/product/11.2.0.3/db_1) (SID_NAME = egapdb1) ) ) LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.96.1)(PORT = 1521)(IP = FIRST)) ) )