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企业级运维实战

、数据库版本情况

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.

 

GGSCI (xxx) 1>  edit params ./GLOBALS 

进入文件编辑界面

添加以下一行后保存退出

GGSCHEMA ggs

 

:ggs为数据库中的OGG用户

 

 

4.4创建mgr文件

在OGG控制台中执行以下命令来创建manager进程的参数文件

GGSCI (xxrac3) 1> edit params mgr

 

进入文件编辑界面后添加以下内容后保存退出:

源库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

GGSCI (xxrac3) 1> start mgr

 

Manager started.

 

GGSCI (xxrac3) 2> info all

 

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING

 

GGSCI (xxrac3) 3> info mgr

 

Manager is running (IP port xxrac3.7840)

这里manager状态为running是正常状态。

 

如果mgr启动失败,请通过view ggsevt或者退出ggsci控制台,查看安装目录下的ggserr.log错误日志

 

5、OGG参数配置

5.1创建OGG软件用户

创建ggs用户

进入OGG安装目录下sqlplus登入进行下列操作

:目标端和源端都需要进行

[oracle@xxrac3 ogg]$ pwd

/goldengate/ogg

[oracle@xxrac3 ogg]$ sqlplus / as sysdba

 

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

SQL> CREATE tablespace ogg datafile '+DATA' size 2G autoextend off; --创建OGG表空间

SQL> CREATE USER ggs IDENTIFIED BY ggs default tablespace ogg;   - -创建OGG用户

 

User created.      

 

SQL> GRANT CONNECT,RESOURCE,DBA TO ggs;  - -给OGG用户授权

 

Grant succeeded.

 

:这里的ggs用户可以自行定义,需要与OGG 参数中设置的对象一致,但不能是数据库保留的SCHEMA;赋予ggs用户的权限需要有对目标数据进行操作的权限,一般直接授予DBA权限,具体情况请与用户协商决定。

 

5.2执行sequence脚本

在源端和目标端都需要执行sequence.sql脚本,注意是否有报错。

SQL> @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

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;

 

Database altered.

 

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (PRIMARY KEY) COLUMNS;

 

Database altered.

 

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (UNIQUE) COLUMNS;

 

Database altered.

 

SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA (FOREIGN KEY) COLUMNS;

 

Database altered.

 

SQL> ALTER SYSTEM SWITCH LOGFILE;

 

System altered.

 

SQL> ALTER DATABASE FORCE LOGGING;

 

Database altered.

 

:需要在源端添加supplemental日志来保证数据能正常同步。

5.4源端授权给ggs用户

SQL> GRANT EXECUTE ON utl_file TO ggs;

 

Grant succeeded.

5.5源端执行脚本

marker_setup.sql

SQL> @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>

SQL>  select * from recyclebin;        - -查看回收站中是否存在数据

 

no rows selected

 

SQL> alter system set recyclebin=off;            - -关闭回收站

 

System altered.

 

SQL> show parameter recyc                 - -查看回收站状态

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

buffer_pool_recycle                  string

db_recycle_cache_size                big integer         0

recyclebin                           string           OFF

 

:如果回收站中存在信息,请确保其中的信息没有用之后再执行接下来的操作。

 

执行ddl_setup.sql脚本的时候如果报错,把这个脚本cp到home/oracle目录下,在登录sqlplus下执行该脚本,执行的时候加上目录。即

SQL> grant create table,CREATE SEQUENCE TO ggs;

 

Grant succeeded.

 

SQL> @ddl_setup.sql

 

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

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.

 

SQL> GRANT GGS_GGSUSER_ROLE TO ggs;

 

Grant succeeded.

 

这个脚本创建了一个role执行完脚本后需要把创建的role grant给创建的OGG用户(ggs)。

ddl_enable.sql

SQL> @ddl_enable.sql

 

Trigger altered.

ddl_pin

SQL> @?/rdbms/admin/dbmspool.sql

 

Package created.

Grant succeeded.

View created.

Package body created.

 

 

 

SQL> @ddl_pin ggs

 

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

SQL>

:这里需要在要执行的脚本后直接加上创建的OGG用户(即ggs)。

6、OGG进程配置

6.1配置源端extract抽取进程

:在源端确认能够正常登录到数据库

[oracle@xxrac3 ~]$ cd /goldengate/ogg

[oracle@xxrac3 ogg]$ pwd

/goldengate/ogg

[oracle@xxrac3 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.

 

GGSCI (xxrac3) 1> DBLOGIN USERID ggs, PASSWORD ggs

 

Successfully logged into database.

GGSCI (xxrac3) 2>

 

 

创建extract进程

源端ggsci控制台中执行以下命令创建extract进程

GGSCI (xxrac3) 2> ADD EXTRACT bbext01, TRANLOG, BEGIN NOW, THREADS 2

EXTRACT added.

GGSCI (xxrac3) 3>

 

:这里threads 后的2为rac节点的数量,bbext01为源端的extract进程名,具体值请根据实际情况更改,确保添加成功。

指定用于存放extract提取内容的trail文件

源端ggsci控制台中执行以下命令创建extract进程使用的trail文件

GGSCI (xxrac3) 3> ADD EXTTRAIL /backup/ogg/dirdat/bb, EXTRACT bbext01,megabytes 100

EXTTRAIL added.

GGSCI (xxrac3) 4>

 

:这里的/backup/ogg/dirdat/bb中的dirdat为创建的工作区中的一个目录bb为定义的文件名只能用两个小写字母),该命令中指定了使用该文件的是bbext01进程megabytes指定的是文件的大小具体实施请根据实际情况调整

 

修改extract进程的参数文件

源端ggsci控制台中执行以下命令来创建extract进程的参数

GGSCI (xxrac3) 4> EDIT PARAMS bbext01

进入文件编辑界面将以下内容添加到文件中后保存退出

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:

$ cat /u01/app/oracle/product/11.2.0/db_1/network/admin/tnsnames.ora

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进程:

GGSCI(xxrac3)5>ADD EXTRACT bbpump01, EXTTRAILSOURCE /backup/ogg/dirdat/bb,BEGIN now

EXTRACT added. 

bbpump01进程也属于extract进程,所以这里创建的时候也是ADD EXTRACT,bbpump01为进程名,EXTTRAILSOURCE后面为要投递的trail文件,请根据实际情况进行更改。

 

添加mypump传输的目录文件

源端ggsci控制台中执行以下命令来指定pump进程的传输目录

GGSCI (xxrac3) 6> ADD RMTTRAIL /app/ogg/dirdat/bb, EXTRACT bbpump01

RMTTRAIL added.

 

:这里的/app/ogg/dirdat/bb是目标端的文件因为这里采用的是将trail文件投递到目标端再到目标库进行实施

 

修改pump的参数文件

源端ggsci控制台中执行以下命令来创建并且修改pump进程的参数文件

GGSCI (xxrac3) 7> EDIT PARAMS bbpump01

进入编辑界面后将以下内容添加到文件中并保存:

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控制台中执行以下命令来启动对应进程:

 

GGSCI (xxrac3) 8> info all

 

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

 

 

GGSCI (xxrac3) 9> start bbext01

 

Sending START request to MANAGER ...

EXTRACT BBEXT01 starting

 

GGSCI (xxrac3) 10> start bbpump01

 

Sending START request to MANAGER ...

EXTRACT BBPUMP01 starting

 

GGSCI (xxrac3) 11> info all

 

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进程

GGSCI (slnode3) 7> ADD REPLICAT rtesp01, EXTTRAIL /app/ogg/dirdat/bb, nodbcheckpoint

REPLICAT added.

 

:这里的rtesp01为进程名,/app/ogg/dirdat/bb为源端pump进程指定的文件,即replicat进程使用pump进程传输过来的文件进行数据应用

2、修改replicat的参数文件

目标端ggsci控制台中执行以下命令来修改replicat进程的参数文件

GGSCI (slnode3) 8> EDIT PARAMS rtesp01

进入编辑界面后将以下内容添加到文件中并保存:

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对象

set linesize 500 pagesize 500

select owner,t.TABLE_NAME,t.LOGGING from dba_tables t where t.LOGGING = 'NO' and owner in ('RPTUSER','JKUSER','MES','TTA','RGESM1','RGCRM1','EPRP','ES','I2','XCOMPROD','W_DIAG');




select sum(bytes)/1024/1024/1024 from dba_segments where segment_name='TEXT3' and owner='RGESM1';

 

2、查询触发器(备库需禁用)

set linesize 500 pagesize 2500

SELECT 'alter trigger '||owner||'.'||trigger_name||' disable'||';' from dba_triggers where owner in ('RPTUSER','JKUSER','MES','TTA','RGESM1','RGCRM1','EPRP','ES','I2','XCOMPROD','W_DIAG');

 

--备库需禁用

alter trigger RGESM1.DBBACK01SPCRE_TRIG disable;

select trigger_name,status from dba_triggers  where owner in ('RPTUSER','JKUSER','MES','TTA','RGESM1','RGCRM1','EPRP','ES','I2','XCOMPROD','W_DIAG');

 

3、查询外键约束(备库需禁用)

set linesize 500 pagesize 2500

SELECT 'alter table ' || owner ||'.'||table_name||' disable constraint '||constraint_name||';' from dba_constraints where constraint_type='R' and owner in ('RPTUSER','JKUSER','MES','TTA','RGESM1','RGCRM1','EPRP','ES','I2','XCOMPROD','W_DIAG');

 

--备库需禁用

alter table RGESM1.TSI0099 disable constraint SYS_C0049733;

 

4、查询JOG(备库需禁用)

set linesize 500 pagesize 2500

select job_name,enabled,owner from dba_scheduler_jobs where owner not in ('SYS','SYSTEM');




select job,schema_user,last_date,next_date,broken,failures from dba_jobs;

 

--备库需禁用

exec dbms_scheduler.disable('EXFSYS.RLM$SCHDNEGACTION');

exec dbms_scheduler.disable('EXFSYS.RLM$EVTCLEANUP');

exec dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');

exec dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB');

exec dbms_scheduler.disable('RGESM1.PCMZ_SP');

exec dbms_scheduler.disable('RGESM1.TTY_JOB_SP');

exec dbms_scheduler.disable('RGESM1.TQMTS26SET1');

exec dbms_scheduler.disable('RGESM1.J_HC_SFC');

 

--备库需禁用

sqlplus rgesm1/xxxxx

exec DBMS_JOB.BROKEN(job=>4,broken=>TRUE);

commit;

exec DBMS_JOB.BROKEN(job=>7,broken=>TRUE);

commit;

 

5、查询无效对象

set linesize 500 pagesize 500

col owner format a12

col object_name format a30

col object_type format a20

select owner,object_name,object_type,status from dba_objects where owner in ('RPTUSER','JKUSER','MES','TTA','RGESM1','RGCRM1','EPRP','ES','I2','XCOMPROD','W_DIAG') and status!='VALID' order by object_name;

 

6查询源库自有dblink情况

set linesize 500 pagesize 500

col owner format a8

col db_link format a12

col username format a12

col host format a40

select * from dba_db_links;

7.3源库expdp导数

--源库创建导出目录

create directory expdir as '/backup/expdp/';

 

--源库记录导出SCN

set linesize 500 pagesize 500

set numwidth 30

select current_scn from v$database;

 

--源库记录用户所属表空间情况

set linesize 500 pagesize 500

select distinct tablespace_name from dba_segments where owner in ('RPTUSER','JKUSER','MES','TTA','RGESM1','RGCRM1','EPRP','ES','I2','XCOMPROD','W_DIAG');

 

--源库导出业务用户数据(SCN一致性数据)

expdp "xxx/xxx" directory=expdir dumpfile=esp%U.dmp schemas=RPTUSER,JKUSER,TTA,MES,EPRP,ES,I2,XCOMPROD,RGESM1 FLASHBACK_SCN=16851281504035 parallel=4 cluster=n logfile=esp_expdp.log




expdp "xxx/xxx" directory=expdir dumpfile=TMMSM31A.dmp tables=RGESM1.TMMSM31A FLASHBACK_SCN=16851281504035 cluster=n logfile=TMMSM31A_expdp.log

 

8、目标库数据导入

8.1创建业务表空间

esp:创建表空间

create tablespace DS_IDX01 datafile '+DATAVG' size 30G autoextend off;

create tablespace DS_TM01 datafile '+DATAVG' size 30G autoextend off;

create tablespace DS_GC01 datafile '+DATAVG' size 2G autoextend on;

create tablespace DS_SM01 datafile '+DATAVG' size 2G autoextend on;

create tablespace DS_XCOM01 datafile '+DATAVG' size 30G autoextend off;

alter tablespace DS_XCOM01 add datafile '+DATAVG' size 30G autoextend off;

alter tablespace DS_XCOM01 add datafile '+DATAVG' size 30G autoextend off;

create tablespace DS_YM01 datafile '+DATAVG' size 2G autoextend on;

create tablespace DS_QM01 datafile '+DATAVG' size 20G autoextend on;

create tablespace DS_LARGE01 datafile '+DATAVG' size 30G autoextend off;

alter tablespace DS_LARGE01 add datafile '+DATAVG' size 30G autoextend off;

alter tablespace DS_LARGE01 add datafile '+DATAVG' size 30G autoextend off;

alter tablespace DS_LARGE01 add datafile '+DATAVG' size 30G autoextend off;

alter tablespace DS_LARGE01 add datafile '+DATAVG' size 30G autoextend off;

alter tablespace DS_LARGE01 add datafile '+DATAVG' size 30G autoextend off;

alter tablespace DS_LARGE01 add datafile '+DATAVG' size 30G autoextend off;

alter tablespace DS_LARGE01 add datafile '+DATAVG' size 30G autoextend off;

alter tablespace DS_LARGE01 add datafile '+DATAVG' size 30G autoextend off;

alter tablespace DS_LARGE01 add datafile '+DATAVG' size 30G autoextend off;

alter tablespace DS_LARGE01 add datafile '+DATAVG' size 30G autoextend off;

alter tablespace DS_LARGE01 add datafile '+DATAVG' size 30G autoextend off;

create tablespace DS_MM01 datafile '+DATAVG' size 30G autoextend off;

alter tablespace DS_MM01 add datafile '+DATAVG' size 30G autoextend off;

create tablespace RPTDATA datafile '+DATAVG' size 10G autoextend off;

create tablespace DS_PS01 datafile '+DATAVG' size 10G autoextend on;

create tablespace JKDATA datafile '+DATAVG' size 10G autoextend on;

create tablespace DS_EP01 datafile '+DATAVG' size 30G autoextend off;

alter tablespace DS_EP01 add datafile '+DATAVG' size 30G autoextend off;

alter tablespace DS_EP01 add datafile '+DATAVG' size 30G autoextend off;

alter tablespace DS_EP01 add datafile '+DATAVG' size 30G autoextend off;

alter tablespace DS_EP01 add datafile '+DATAVG' size 30G autoextend off;

create temporary tablespace XCOM_TEMP tempfile '+DATAVG' size 10G autoextend on;

alter database tempfile 1 resize 30G;

 

 

8.2目标库impdp导入数据

impdp xxx/xxx directory=impdir dumpfile=esp01.dmp,esp02.dmp,esp03.dmp,esp04.dmp parallel=4 cluster=n logfile=esp_impdp.log

impdp xxx/xxx directory=impdir dumpfile=TMMSM31A.dmp content=metadata_only cluster=n logfile=TMMSM31A_impdp.log

impdp xxx/xxx directory=impdir dumpfile=TMMSM31A.dmp content=data_only cluster=n logfile=TMMSM31A_impdp.log

drop table RGESM1.TMMSM31A;

select count(1) from RGESM1.TMMSM31A;

 

 

 

 

 

8.3目标库数据导入后配置修改

 

1.查看禁用触发器

set linesize 500 pagesize 2500

SELECT 'alter trigger '||owner||'.'||trigger_name||' disable'||';' from dba_triggers where owner in ('RPTUSER','JKUSER','MES','TTA','RGESM1','RGCRM1','EPRP','ES','I2','XCOMPROD','W_DIAG');

 

--备库需禁用

alter trigger RGESM1.DBBACK01SPCRE_TRIG disable;

 

2.查看禁用外键约束

set linesize 500 pagesize 2500

SELECT 'alter table ' || owner ||'.'||table_name||' disable constraint '||constraint_name||';' from dba_constraints where constraint_type='R' and owner in ('RPTUSER','JKUSER','MES','TTA','RGESM1','RGCRM1','EPRP','ES','I2','XCOMPROD','W_DIAG');

 

--备库需禁用

alter table RGESM1.TSI0099 disable constraint SYS_C0049733;

 

3.查看禁用job

set linesize 500 pagesize 2500

select job_name,enabled,owner from dba_scheduler_jobs where owner not in ('SYS','SYSTEM');

 

--备库需禁用

select job,schema_user,last_date,next_date,broken,failures from dba_jobs;

exec dbms_scheduler.disable('EXFSYS.RLM$SCHDNEGACTION');

exec dbms_scheduler.disable('EXFSYS.RLM$EVTCLEANUP');

exec dbms_scheduler.disable('ORACLE_OCM.MGMT_STATS_CONFIG_JOB');

exec dbms_scheduler.disable('ORACLE_OCM.MGMT_CONFIG_JOB');

exec dbms_scheduler.disable('RGESM1.PCMZ_SP');

exec dbms_scheduler.disable('RGESM1.TTY_JOB_SP');

exec dbms_scheduler.disable('RGESM1.TQMTS26SET1');

exec dbms_scheduler.disable('RGESM1.J_HC_SFC');

 

--备库需禁用

sqlplus rgesm1/xxx

exec DBMS_JOB.BROKEN(job=>4,broken=>TRUE);

commit;

exec DBMS_JOB.BROKEN(job=>7,broken=>TRUE);

commit;

 

4.查看无效对象情况

set linesize 500 pagesize 500

col owner format a12

col object_name format a30

col object_type format a20

select owner,object_name,object_type,status from dba_objects where owner in ('RPTUSER','JKUSER','MES','TTA','RGESM1','RGCRM1','EPRP','ES','I2','XCOMPROD','W_DIAG') and status!='VALID' order by object_name;

 

5、查询dblink情况

set linesize 500 pagesize 500

col owner format a8

col db_link format a12

col username format a12

col host format a40

select * from dba_db_links;

 

6、对象数量检查

select object_type,count(1) from dba_objects where owner = 'RPTUSER' group by object_type;

select object_type,count(1) from dba_objects where owner = 'JKUSER' group by object_type;

select object_type,count(1) from dba_objects where owner = 'TTA' group by object_type;

select object_type,count(1) from dba_objects where owner = 'RGESM1' group by object_type order by object_type;

col object_name format a40

select owner,object_name,object_type,status from dba_objects where owner = 'RGESM1' and object_type='TYPE';

select object_type,count(1) from dba_objects where owner = 'EPRP' group by object_type order by object_type;

select object_type,count(1) from dba_objects where owner = 'ES' group by object_type order by object_type;

select object_type,count(1) from dba_objects where owner = 'I2' group by object_type order by object_type;

select object_type,count(1) from dba_objects where owner = 'XCOMPROD' group by object_type order by object_type;

select object_type,count(1) from dba_objects where owner = 'MES' group by object_type order by object_type;

 

8.4目标库数据权限校验

DBA_ROLE_PRIVS--角色权限

select * from dba_role_privs where grantee in ('RPTUSER','JKUSER','MES','TTA','RGESM1','RGCRM1','EPRP','ES','I2','XCOMPROD','W_DIAG') order by grantee;

 

dba_sys_privs--系统权限

select * from dba_sys_privs where grantee in ('RPTUSER','JKUSER','MES','TTA','RGESM1','RGCRM1','EPRP','ES','I2','XCOMPROD','W_DIAG') order by grantee;

 

dba_tab_privs--对象权限,重点检查

select * from dba_tab_privs where grantee in ('RPTUSER','JKUSER','MES','TTA','RGESM1','RGCRM1','EPRP','ES','I2','XCOMPROD','W_DIAG') order by grantee;

 

--sys对象权限,重点检查

set linesize 500 pagesize 500

col grantee format a12

col owner format a12

select * from dba_tab_privs where grantee in ('RPTUSER','JKUSER','MES','TTA','RGESM1','RGCRM1','EPRP','ES','I2','XCOMPROD','W_DIAG') and owner='SYS' order by grantee;

 

 

 

--赋权

grant READ,WRITE on directory DTPUMP to EPRP;

grant READ,WRITE on directory DTPUMP to ES;

grant ON COMMIT REFRESH,DEBUG,QUERY REWRITE,FLASHBACK,ALTER,DELETE,INDEX,INSERT,REFERENCES,UPDATE,SELECT on sys.USER$ to I2;

grant ON COMMIT REFRESH,DEBUG,QUERY REWRITE,FLASHBACK,ALTER,DELETE,INDEX,INSERT,REFERENCES,UPDATE,SELECT on sys.SEQ$ to I2;

grant ON COMMIT REFRESH,DEBUG,QUERY REWRITE,FLASHBACK,ALTER,DELETE,INDEX,INSERT,REFERENCES,UPDATE,SELECT on sys.UNDO$ to I2;

grant SELECT on sys.LINK$ to I2;

grant READ,WRITE on directory DTPUMP to RGESM1;

grant READ,WRITE on directory DTPUMP to TTA;

grant READ,WRITE on directory DTPUMP to XCOMPROD;

grant EXECUTE on sys.DBMS_LOCK to XCOMPROD;

grant EXECUTE on sys.DBMS_LOB to XCOMPROD;

grant EXECUTE on sys.UTL_FILE to XCOMPROD;

grant READ,WRITE on directory DBLINK_LOG to XCOMPROD;




--bug

grant execute on xcomprod.xcom_pack to RGESM1;

 

 

8.5目标库启动replicate应用进程

 

start rtesp01,aftercsn 16851281504035

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

附录

posted @ 2022-02-08 16:44  Eddie小陈  阅读(830)  评论(0编辑  收藏  举报