OGG for Oracle to Oracle迁移实施笔记
一、拓扑结构
实施流程简介:
1、搭建主备库mgr进程并启用
2、搭建主库extract、pump进程并启用
3、备库建库
4、搭建备库replicat进程
5、expdp备份主库
6、impdp恢复备库
7、启用replicat进程进行主备同步
二、环境要求
1、参考文档
Benefits and Usage of RMAN with Standby Databases (文档 ID 602299.1)
How to set up basic Oracle GoldenGate(OGG) and implement high availability(HA) in Oracle Database Appliance(ODA) environment using Oracle Clusterware (文档 ID 1391398.1)
GoldenGate企业级运维实战
2、数据库版本情况
RAC主库的版本为11.2.0.4.0 双节点
RAC备库的版本为11.2.0.4.0双节点
三、实施步骤
1、主备库添加hosts解析
使用cat /etc/hosts命令查看hosts文件
2、确保主备库网络通畅
在服务器上使用ping命令查看网络通畅性
3、主备库上传OGG安装包
AIX平台软件:AIX-OGG-11.2.1.0.1-for-oracle11g.zip
Linux平台软件:ogg112101_fbo_ggs_Linux_x64_ora11g_64bit.tar
主库安装目录:/backup/ogg/
备库安装目录:/app/ogg/
4、OGG安装
4.1环境变量修改
oracle用户环境变量添加以下变量
Aix平台:export LIBPATH=/backup/ogg:$ORACLE_HOME/lib
4.2解压软件并创建目录
$ unzip AIX-OGG-11.2.1.0.1-for-oracle11g.zip
$ tar -xvf ggs_AIX_ppc_ora11g_64bit.tar
$ ./ggsci
GGSCI (xxx) 1> create subdirs
4.3创建GLOBALS
[oracle@xxx ogg]$ ggsci
Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
进入文件编辑界面 添加以下一行后保存退出 GGSCHEMA ggs
注:ggs为数据库中的OGG用户 |
4.4创建mgr文件
在OGG控制台中执行以下命令来创建manager进程的参数文件
进入文件编辑界面后添加以下内容后保存退出: 源库rac集群添加以下参数: PORT 7840 DYNAMICPORTLIST 7840-7939 --AUTOSTART ER myext01 --AUTOSTART ER myext02 --AUTOSTART ER mypump01 --AUTOSTART ER mypump02 --AUTORESTART ER myext01,RETRIES 5,WAITMINUTES 3 --AUTORESTART ER myext02,RETRIES 5,WAITMINUTES 3 --AUTORESTART ER mypump01,RETRIES 5,WAITMINUTES 3 --AUTORESTART ER mypump02,RETRIES 5,WAITMINUTES 3 PURGEOLDEXTRACTS /backup/ogg/dirdat/*,usecheckpoints,minkeepdays 7 LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45
目标库添加以下参数: PORT 7840 DYNAMICPORTLIST 7840-7939 --AUTOSTART ER rept01 --AUTOSTART ER rept02 --AUTORESTART ER rept01,RETRIES 5,WAITMINUTES 3 --AUTORESTART ER rept02,RETRIES 5,WAITMINUTES 3 PURGEOLDEXTRACTS /app/ogg/dirdat/*,usecheckpoints,minkeepdays 7 LAGREPORTHOURS 1 LAGINFOMINUTES 30 LAGCRITICALMINUTES 45
注:一定不要让rept进程随mgr进程自动启动!!! 其中PORT 后的是默认启用的端口号 AUTOSTART是启动manager进程后要自动启动的进程 AUTORESTART是manager启动后自动重启相关的设置 在源库添加的PURGEOLDEXTRACTS参数是关于源端trail文件保存删除的参数,这里保留七天,这个参数需要根据客户空间实际情况,以及客户需求来进行设置 LAG开头的参数是设置lag预警的,将来可以在日志中看到相应的info、waring、error 这里只给出最基本的参数,具体参数内容需要根据实际情况进行对应的调整。 |
4.5启动mgr并查看状态
源端目标端编辑完参数后都需要执行以下操作
通过以下方式来启动和查看manager
Manager started.
Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING
Manager is running (IP port xxrac3.7840) 这里manager状态为running是正常状态。
注:如果mgr启动失败,请通过view ggsevt或者退出ggsci控制台,查看安装目录下的ggserr.log错误日志 |
5、OGG参数配置
5.1创建OGG软件用户
创建ggs用户
进入OGG安装目录下sqlplus登入进行下列操作
注:目标端和源端都需要进行
/goldengate/ogg
SQL*Plus: Release 11.2.0.4.0 Production on Thu Mar 19 09:01:51 2015 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options
User created.
Grant succeeded.
注:这里的ggs用户可以自行定义,需要与OGG 参数中设置的对象一致,但不能是数据库保留的SCHEMA;赋予ggs用户的权限需要有对目标数据进行操作的权限,一般直接授予DBA权限,具体情况请与用户协商决定。 |
5.2执行sequence脚本
在源端和目标端都需要执行sequence.sql脚本,注意是否有报错。
Please enter the name of a schema for the GoldenGate database objects: ggs Setting schema name to GGS UPDATE_SEQUENCE STATUS: Line/pos -------------------------------------------------------------------------------- Error ----------------------------------------------------------------- No errors No errors GETSEQFLUSH Line/pos -------------------------------------------------------------------------------- Error ----------------------------------------------------------------- No errors No errors SEQTRACE Line/pos -------------------------------------------------------------------------------- Error ----------------------------------------------------------------- No errors No errors REPLICATE_SEQUENCE STATUS: Line/pos -------------------------------------------------------------------------------- Error ----------------------------------------------------------------- No errors No errors STATUS OF SEQUENCE SUPPORT -------------------------------------------------------------------------------- SUCCESSFUL installation of Oracle Sequence Replication support SQL> |
5.3源端添加supplemental log
Database altered.
Database altered.
Database altered.
Database altered.
System altered.
Database altered.
注:需要在源端添加supplemental日志来保证数据能正常同步。 |
5.4源端授权给ggs用户
Grant succeeded. |
5.5源端执行脚本
marker_setup.sql
Marker setup script You will be prompted for the name of a schema for the Oracle GoldenGate database objects. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter Oracle GoldenGate schema name:ggs Marker setup table script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GGS MARKER TABLE -------------------------------- OK MARKER SEQUENCE -------------------------------- OK Script complete. SQL> |
ddl_setup.sql
注:10g执行该脚本前需要关闭数据库的回收站,并确保回收站中没有数据
11g不需要关闭数据库的回收站
SQL> show parameter recyc
NAME TYPE ------------------------------------ -------------------------------- VALUE ------------------------------ buffer_pool_recycle string db_recycle_cache_size big integer 0 recyclebin string on SQL>
no rows selected
System altered.
NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ buffer_pool_recycle string db_recycle_cache_size big integer 0 recyclebin string OFF
注:如果回收站中存在信息,请确保其中的信息没有用之后再执行接下来的操作。 |
执行ddl_setup.sql脚本的时候如果报错,把这个脚本cp到home/oracle目录下,在登录sqlplus下执行该脚本,执行的时候加上目录。即
Grant succeeded.
Oracle GoldenGate DDL Replication setup script Verifying that current user has privileges to install DDL Replication... You will be prompted for the name of a schema for the Oracle GoldenGate database objects. NOTE: For an Oracle 10g source, the system recycle bin must be disabled. For Oracle 11g and later, it can be enabled. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter Oracle GoldenGate schema name:ggs Working, please wait ... Spooling to file ddl_setup_spool.txt Checking for sessions that are holding locks on Oracle Golden Gate metadata tables ... Check complete. Using GGS as a Oracle GoldenGate schema name. Working, please wait ... DDL replication setup script complete, running verification script... Please enter the name of a schema for the GoldenGate database objects: Setting schema name to GGS CLEAR_TRACE STATUS: Line/pos ------------------------------------------------------------------------------------------------------------------------ Error ----------------------------------------------------------------- No errors No errors CREATE_TRACE STATUS: Line/pos ------------------------------------------------------------------------------------------------------------------------ Error ----------------------------------------------------------------- No errors No errors TRACE_PUT_LINE STATUS: Line/pos ------------------------------------------------------------------------------------------------------------------------ Error ----------------------------------------------------------------- No errors No errors INITIAL_SETUP STATUS: Line/pos ------------------------------------------------------------------------------------------------------------------------ Error ----------------------------------------------------------------- No errors No errors DDLVERSIONSPECIFIC PACKAGE STATUS: Line/pos ------------------------------------------------------------------------------------------------------------------------ Error ----------------------------------------------------------------- No errors No errors DDLREPLICATION PACKAGE STATUS: Line/pos ------------------------------------------------------------------------------------------------------------------------ Error ----------------------------------------------------------------- No errors No errors DDLREPLICATION PACKAGE BODY STATUS: Line/pos ------------------------------------------------------------------------------------------------------------------------ Error ----------------------------------------------------------------- No errors No errors DDL IGNORE TABLE ------------------------------------------------------------------------------------------------------------------------ OK DDL IGNORE LOG TABLE ------------------------------------------------------------------------------------------------------------------------ OK DDLAUX PACKAGE STATUS: Line/pos ------------------------------------------------------------------------------------------------------------------------ Error ----------------------------------------------------------------- No errors No errors DDLAUX PACKAGE BODY STATUS: Line/pos ------------------------------------------------------------------------------------------------------------------------ Error ----------------------------------------------------------------- No errors No errors SYS.DDLCTXINFO PACKAGE STATUS: Line/pos ------------------------------------------------------------------------------------------------------------------------ Error ----------------------------------------------------------------- No errors No errors SYS.DDLCTXINFO PACKAGE BODY STATUS: Line/pos ------------------------------------------------------------------------------------------------------------------------ Error ----------------------------------------------------------------- No errors No errors DDL HISTORY TABLE ------------------------------------------------------------------------------------------------------------------------ OK DDL HISTORY TABLE(1) ------------------------------------------------------------------------------------------------------------------------ OK DDL DUMP TABLES ------------------------------------------------------------------------------------------------------------------------ OK DDL DUMP COLUMNS ------------------------------------------------------------------------------------------------------------------------ OK DDL DUMP LOG GROUPS ------------------------------------------------------------------------------------------------------------------------ OK DDL DUMP PARTITIONS ------------------------------------------------------------------------------------------------------------------------ OK DDL DUMP PRIMARY KEYS ------------------------------------------------------------------------------------------------------------------------ OK DDL SEQUENCE ------------------------------------------------------------------------------------------------------------------------ OK GGS_TEMP_COLS ------------------------------------------------------------------------------------------------------------------------ OK GGS_TEMP_UK ------------------------------------------------------------------------------------------------------------------------ OK DDL TRIGGER CODE STATUS: Line/pos ------------------------------------------------------------------------------------------------------------------------ Error ----------------------------------------------------------------- No errors No errors DDL TRIGGER INSTALL STATUS ------------------------------------------------------------------------------------------------------------------------ OK DDL TRIGGER RUNNING STATUS ------------------------------------------------------------------------------------------------------------------------ ENABLED STAYMETADATA IN TRIGGER ------------------------------------------------------------------------------------------------------------------------ OFF DDL TRIGGER SQL TRACING ------------------------------------------------------------------------------------------------------------------------ 0 DDL TRIGGER TRACE LEVEL ------------------------------------------------------------------------------------------------------------------------ 0 LOCATION OF DDL TRACE FILE ------------------------------------------------------------------------------------------------------------------------ /u01/app/oracle/diag/rdbms/pcdb/pcdb3/trace/ggs_ddl_trace.log Analyzing installation status... STATUS OF DDL REPLICATION ------------------------------------------------------------------------------------------------------------------------ SUCCESSFUL installation of DDL Replication software components Script complete. SQL> 注:请注意查看执行过程中是否存在报错,如果存在报错则需要解决后再继续接下来的步骤。 |
role_setup.sql
GGS Role setup script This script will drop and recreate the role GGS_GGSUSER_ROLE To use a different role name, quit this script and then edit the params.sql script to change the gg_role parameter to the preferred name. (Do not run the script.) You will be prompted for the name of a schema for the GoldenGate database objects. NOTE: The schema must be created prior to running this script. NOTE: Stop all DDL replication before starting this installation. Enter GoldenGate schema name:ggs Wrote file role_setup_set.txt PL/SQL procedure successfully completed. Role setup script complete Grant this role to each user assigned to the Extract, GGSCI, and Manager processes, by using the following SQL command: GRANT GGS_GGSUSER_ROLE TO <loggedUser> where <loggedUser> is the user assigned to the GoldenGate processes.
Grant succeeded.
注:这个脚本创建了一个role,执行完脚本后需要把创建的role grant给创建的OGG用户(ggs)。 |
ddl_enable.sql
Trigger altered. |
ddl_pin
Package created. Grant succeeded. View created. Package body created.
PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. PL/SQL procedure successfully completed. SQL> 注:这里需要在要执行的脚本后直接加上创建的OGG用户(即ggs)。 |
6、OGG进程配置
6.1配置源端extract抽取进程
注:在源端确认能够正常登录到数据库
/goldengate/ogg
Oracle GoldenGate Command Interpreter for Oracle Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO Linux, x64, 64bit (optimized), Oracle 11g on Apr 23 2012 08:32:14 Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.
Successfully logged into database. GGSCI (xxrac3) 2>
|
创建extract进程
在源端ggsci控制台中执行以下命令创建extract进程
EXTRACT added. GGSCI (xxrac3) 3>
注:这里threads 后的2为rac节点的数量,bbext01为源端的extract进程名,具体值请根据实际情况更改,确保添加成功。 |
指定用于存放extract提取内容的trail文件
在源端ggsci控制台中执行以下命令创建extract进程使用的trail文件
EXTTRAIL added. GGSCI (xxrac3) 4>
注:这里的/backup/ogg/dirdat/bb中的dirdat为创建的工作区中的一个目录,bb为定义的文件名(只能用两个小写字母),该命令中指定了使用该文件的是bbext01进程,megabytes指定的是文件的大小,具体实施请根据实际情况调整 |
修改extract进程的参数文件
在源端ggsci控制台中执行以下命令来创建extract进程的参数
进入文件编辑界面将以下内容添加到文件中后保存退出 EXTRACT bbext01 SETENV (ORACLE_HOME ="/app/oracle/product/11.2.0/db_1") SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK") SETENV (ORACLE_SID=ESP1) USERID ggs@espogg, PASSWORD ggs THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 3000 TRANLOGOPTIONS ASMUSER sys@ASM, ASMPASSWORD ipioaf70 EXTTRAIL /backup/ogg/dirdat/bb DISCARDFILE /backup/ogg/dirrpt/bbext01.dsc APPEND,MEGABYTES 100 WARNLONGTRANS 2h,CHECKINTERVAL 3m DBOPTIONS ALLOWUNUSEDCOLUMN CHECKPOINTSECS 3 EOFDELAY 3 FLUSHSECS 3 DDL INCLUDE ALL TABLEEXCLUDE XCOMPROD.CMP4$112497; TABLE RPTUSER.*; TABLE JKUSER.*; TABLE MES.*; TABLE TTA.*; TABLE RGESM1.*; TABLE EPRP.*; TABLE ES.*; TABLE I2.*; TABLE XCOMPROD.*;
注:这里需要注意 ORACLE_HOME需要确保正确 USERID 后为OGG数据库中的用户@service name,PASSWORD为该用户登录数据库pssword 请确保其中填写目录的地方目录的正确性 TABLE USER.*为要同步的数据范围 一定要确保有NLS_LANG环境变量 具体参数不做解说,需要根据实际情况进行详细的配置。
注:这里修改了rac集群的tnsnames.ora文件 内容如下: 节点1:
ASM = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.198.141)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME=+ASM) (INSTANCE_NAME = +ASM1) (UR=A) ) )
节点2: ASM = (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = 10.1.198.143)(PORT = 1521)) ) (CONNECT_DATA = (SERVICE_NAME=+ASM) (INSTANCE_NAME = +ASM2) (UR=A) ) ) |
6.2配置源端pump投递进程
创建pump进程
在源端ggsci控制台中执行以下命令来创建pump进程:
EXTRACT added. 注:bbpump01进程也属于extract进程,所以这里创建的时候也是ADD EXTRACT,bbpump01为进程名,EXTTRAILSOURCE后面为要投递的trail文件,请根据实际情况进行更改。 |
添加mypump传输的目录文件
在源端ggsci控制台中执行以下命令来指定pump进程的传输目录
RMTTRAIL added.
注:这里的/app/ogg/dirdat/bb是目标端的文件,因为这里采用的是将trail文件投递到目标端再到目标库进行实施。 |
修改pump的参数文件
在源端ggsci控制台中执行以下命令来创建并且修改pump进程的参数文件
进入编辑界面后将以下内容添加到文件中并保存: EXTRACT bbpump01 RMTHOST mesdba03, MGRPORT 7840,TCPBUFSIZE 100000,TCPFLUSHBYTES 300000 EOFDELAY 3 FLUSHSECS 5 PASSTHRU RMTTRAIL /app/ogg/dirdat/bb TABLE RPTUSER.*; TABLE JKUSER.*; TABLE MES.*; TABLE TTA.*; TABLE RGESM1.*; TABLE EPRP.*; TABLE ES.*; TABLE I2.*; TABLE XCOMPROD.*;
注:这里mesdba03为目标库主机名,/app/ogg/dirdat/bb为目标库文件名 传输的表为*用户下所有表,具体参数请根据实际情况设定。 |
6.3源端启动extract和pump进程
在源端ggsci控制台中执行以下命令来启动对应进程:
Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT STOPPED BBEXT01 00:00:00 00:27:19 EXTRACT STOPPED BBPUMP01 00:00:00 00:05:38
Sending START request to MANAGER ... EXTRACT BBEXT01 starting
Sending START request to MANAGER ... EXTRACT BBPUMP01 starting
Program Status Group Lag at Chkpt Time Since Chkpt MANAGER RUNNING EXTRACT RUNNING BBEXT01 00:00:00 00:27:34 EXTRACT RUNNING BBPUMP01 00:00:00 00:05:53 GGSCI (xxrac3) 12>
|
6.4目标端配置replicate进程
创建一个replicat进程
在目标端ggsci控制台中执行以下命令来添加一个replicat进程
REPLICAT added.
注:这里的rtesp01为进程名,/app/ogg/dirdat/bb为源端pump进程指定的文件,即replicat进程使用pump进程传输过来的文件进行数据应用 |
2、修改replicat的参数文件
在目标端ggsci控制台中执行以下命令来修改replicat进程的参数文件
进入编辑界面后将以下内容添加到文件中并保存: REPLICAT rtesp01 SETENV (ORACLE_HOME ="/app/oracle/product/11.2.0/db_1") SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK") USERID ggs@esp,PASSWORD ggs ASSUMETARGETDEFS APPLYNOOPUPDATES REPERROR (DEFAULT, ABEND) DDL include mapped DDLERROR DEFAULT DISCARD DDLOPTIONS REPORT ALLOWNOOPUPDATES DISCARDFILE /app/ogg/rtesp01.dsc,append,megabytes 100 MAP RPTUSER.*, TARGET RPTUSER.*; MAP JKUSER.*, TARGET JKUSER.*; MAP MES.*, TARGET MES.*; MAP TTA.*, TARGET TTA.*; MAP RGESM1.*, TARGET RGESM1.*; MAP EPRP.*, TARGET EPRP.*; MAP ES.*, TARGET ES.*; MAP I2.*, TARGET I2.*; MAP XCOMPROD.*, TARGET XCOMPROD.*;
注:这里进程名为rtesp01,需要注意的是目录需要填写正确,@后面的是目标库的service名 具体参数请根据实际情况来修改设定; 一定要确保有nls_lang环境变量!! |
7、源库数据导出
select username,account_status from dba_users;
7.1查询业务用户
7.2重要检查项
1、查询nologging对象
2、查询触发器(备库需禁用)
--备库需禁用
3、查询外键约束(备库需禁用)
--备库需禁用
4、查询JOG(备库需禁用)
--备库需禁用
--备库需禁用
5、查询无效对象
6查询源库自有dblink情况
|
7.3源库expdp导数
--源库创建导出目录
--源库记录导出SCN
--源库记录用户所属表空间情况
--源库导出业务用户数据(SCN一致性数据)
|
8、目标库数据导入
8.1创建业务表空间
esp:创建表空间
|
8.2目标库impdp导入数据
|
8.3目标库数据导入后配置修改
1.查看禁用触发器
--备库需禁用
2.查看禁用外键约束
--备库需禁用
3.查看禁用job
--备库需禁用
--备库需禁用
4.查看无效对象情况
5、查询dblink情况
6、对象数量检查
|
8.4目标库数据权限校验
DBA_ROLE_PRIVS--角色权限
dba_sys_privs--系统权限
dba_tab_privs--对象权限,重点检查
--sys对象权限,重点检查
|
--赋权
|
8.5目标库启动replicate应用进程
start rtesp01,aftercsn 16851281504035
附录
本文来自博客园,作者:Eddie小陈,转载请注明原文链接:https://www.cnblogs.com/orachen/p/15871844.html