1、数据库迁移

1.1概述

客户要求把oracle11.2.0.4数据库单机环境迁移到19.20的pdb环境中，停机时间要求比较短（算上应用切换共半小时）且只迁移一个schema用户，数据量约为500G左右，于是选用ogg的迁移方式。

1.2安装介质

OGG软件

V983658-01.zip（Oracle GoldenGate 19.1.0.0.4 for Oracle on Linux x86-64）（目标端/源端）

1.3源端OGG安装配置

注：由于需要考虑回退操作，所以需要配置正向同步和反向同步两条OGG链路， 当迁移同步时，只启动正向同步，不启动反向同步，当业务割接时，停止正向同步，启动反向同步，正反向同步链路如下：

正向：emr_ext >> emr_dpn >> emr_rep  (trail文件名ex)

反向：emr_rxt >> emr_rdp >> emr_rrp (trail文件名er)

1.3.1配置源端数据库

创建OGG用户及表空间，授权，添加附加日志：

create user ogg identified by ogg default tablespace ogg;

grant resource,connect,dba to ogg;

alter database add supplemental log data;

alter database add supplemental log data(primary key,unique,foreign key) columns;

alter database force logging;

1.3.2配置OGG环境变量

cat /home/oracle/.bash_profile


export GG_HOME=/u01/app/oracle/ogg

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORA_CRS_HOME/lib:/lib:$ORACLE_HOME/rdbms:/usr/lib:/usr/X11R6/lib:$GG_HOME/lib

export PATH=$PATH:$ORACLE_HOME/bin:$ORA_CRS_HOME/bin:$ORACLE_HOME/OPatch:$GG_HOME

1.3.3安装OGG软件

解压ogg软件：

Unzip OGG_19.1.zip

安装

./runInstaller  -silent -showProgress  -responseFile /oracle/fbo_ggs_Linux_x64_Oracle_shiphome/Disk1/response/oggcore.rsp


创建相关目录：

$GG_HOME/ggsci

GGSCI> Create subdirs


1.3.4配置及启动管理进程

edit param mgr

Port 7809

数据库迁移11g到19c_SQL

数据库迁移11g到19c_数据库_02

GGSCI> start mgr


1.3.5配置表附加日志

每张表均添加附加日志：

Add trandata username.table_name


批量生成添加语句：

SELECT 'ADD TRANDATA '||OWNER||'.’||TABLE_NAME||’'  FROM DBA_TABLES WHERE OWNER=’HIS40’;


1.3.6配置OGG抽取进程

配置抽取进程参数：

EXTRACT emr_ext

setenv (ORACLE_SID=emr)

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

userid ogg,password ogg

DISCARDFILE ./dirrpt/emr_ext.dsc,APPEND,MEGABYTES 1024

DBOPTIONS  ALLOWUNUSEDCOLUMN

EXTTRAIL ./dirdat/ex

ddl include all


TABLE HIS40.*;


数据库迁移11g到19c_oracle_03

创建抽取进程：

add extract emr_ext  tranlog ,threads 2,begin now

add exttrail ./dirdat/ex extract emr_ext ,megabytes 512


1.3.7配置投递进程

配置投递进程参数：

EXTRACT emr_dpn

PASSTHRU

RMTHOST 10.22.104.11, MGRPORT 7809

RMTTRAIL ./dirdat/sx


TABLE HIS40.*;


创建投递进程：

add extract emr_dpn exttrailsource ./dirdat/ex

add rmttrail ./dirdat/ex extract emr_dpn ,megabytes 1024



1.3.8支持sequence同步

无需配置，业务切割的时候重建一次sequence即可。

1.3.9启动抽取进程


GGSCI> start emr_ext


1.3.10数据泵expdp以SCN导出数据

获取数据库当前scn，复制进程启动时指明从此scn开始复制：

SQL> select to_char(dbms_flashback.get_system_change_number) from dual;


数据库迁移11g到19c_SQL_04

创建dump目录：

create directory oggdump as '/u01/oggdump';

grant read,write on directory oggdump to ogg;

数据库迁移11g到19c_SQL_05

expdp导出数据：

expdp ogg/ogg directory=oggdump dumpfile=emr_full.dmp schemas=HIS40 flashback_scn=60959432002 logfile=emr_fulldmp.log

数据库迁移11g到19c_数据库_06

1.3.11启动投递进程


GGSCI> start emr_dpn

1.4目标端OGG安装配置

1.4.1配置目标端数据库

创建OGG用户，表空间，授权

在容器中创建C##GGADMIN用户并授权

SQL> create user C##GGADMIN identified by ggadmin;

User created.


SQL> exec dbms_goldengate_auth.grant_admin_privilege('C##GGADMIN',container=>'ALL');

PL/SQL procedure successfully completed.


SQL> grant dba to c##ggadmin container=all;

Grant succeeded.


在target pdb中创建ogg管理用户并授权

alter session set container=emr;

CREATE TABLESPACE ogg datafile '+data' size 200m autoextend on ;

create user ogg identified by ogg default tablespace ogg;

grant resource,connect,dba to ogg;


修改允许OGG复制参数：

SQL> alter system set enable_goldengate_replication = true scope=both sid='*';


alter table schema.table_name add supplemental log data(all) columns

1.4.2配置OGG环境变量

vi /home/oracle/.bash_profile

export GG_HOME=/ogg


1.4.3安装OGG软件

unzip OGG_19.1.zip -d /ogg


./runInstaller (图形化界面安装，略)

1.4.4配置及启动管理进程

Vi /ogg/dirprm/mgr.prm

Port 7809


GGSCI> start mgr


1.4.5数据泵impdp导入数据


传输dmp文件到19c端：

scp emr_full.dmp oracle@10.22.104.11:/home/oracle/oggdump/

SQL> alter session set container=emr;

SQL> create directory oggdump as '/home/oracle/oggdump';

SQL> grant read,write on directory to ogg;

数据库迁移11g到19c_SQL_07

19g端执行导入：

impdp ogg/ogg@pdb_emr directory=oggdump dumpfile=emr_full.dmp schemas=his40 logfile=emr_imp.log


数据库迁移11g到19c_数据库_08

注：

impdp日志中存在错误，主要包括授权，存储过程、函数和视图编译错误，完整错误信息参考附件/home/oracle/oggdump/emr_imp.log

1，由于新库缺少相关用户，授权错误，请应用端排查梳理。

存储过程，函数，视图编译错误，均是因为源库存储无效对象引起，请业务排查。

1.4.6禁用触发器，外键，定时job等

批量生成禁用触发器语句：

set lines 300 pages 300

col owner for a30

col TRIGGER_NAME for a30

col TRIGGER_TYPE for a30

col STATUS for a30

select OWNER,TRIGGER_NAME,TRIGGER_TYPE,STATUS from DBA_TRIGGERS WHERE OWNER='HIS40';


OWNER                          TRIGGER_NAME                   TRIGGER_TYPE                   STATUS

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

HIS40                          TRI_IP_REF                     BEFORE EACH ROW                ENABLED

HIS40                          HIS_IN_PATI_REG_TRIGGER        BEFORE EACH ROW                ENABLED

HIS40                          TRI_MDM_USER_NAME              BEFORE EACH ROW                ENABLED


SELECT 'ALTER TRIGGER HIS40.'||TRIGGER_NAME||' DISABLE;'  FROM DBA_TRIGGERS WHERE OWNER='HIS40';


SELECT 'ALTER TRIGGER HIS40.'||TRIGGER_NAME||' ENABLE;'  FROM DBA_TRIGGERS WHERE OWNER='HIS40';

批量生成禁用外键语句：

set lines 300 pages 300

col owner for a30

col table_name for a30

col constraint_name for a30

col CONSTRAINT_TYPE for a30

col STATUS for a30

col INVALID for a30

col LAST_CHANGE for a30

select owner,table_name,constraint_name,CONSTRAINT_TYPE,STATUS,INVALID,LAST_CHANGE from dba_constraints where constraint_type in ('R') and owner = 'HIS40';

批量生成禁用job语句：

set lines 300 pages 5000

col owner for a20

col job_name for a20

col JOB_ACTION for a40

col COMMENTS for a50

col startdate for a20

col LAST_RUN_DURATION for a20

col NEXT_RUN_DATE for a20

col LAST_START_DATE for a40

select owner,job_name,job_action,to_char(start_date,'yyyy-mm-dd hh24:mi:ss') startdate,to_char(NEXT_RUN_DATE,'yyyy-mm-dd hh24:mi:ss') NEXT_RUN_DATE,ENABLED,STATE,COMMENTS from dba_scheduler_jobs where  owner='HIS40';


begin

DBMS_SCHEDULER.DISABLE('HIS40.QUEST_PPCM_JOB_PM_1',true);

end;

/

1.4.7配置OGG复制进程

目标端配置复制进程参数：

GGSCI> edit param emr_rep

replicat emr_rep

userid ogg@pdb_emr,password ogg

assumetargetdefs

allownoopupdates

discardfile ./dirrpt/emr_rep.dsc,append,megabytes 1024

ddl include all


map his40.*,target emr.his40.*;

创建复制进程：

add replicat emr_rep exttrail ./dirdat/ex,nodbcheckpoint


1.4.8以SCN启动OGG复制进程

/ogg/ggsci

start replicat emr_rep aftercsn 60959432002

数据库迁移11g到19c_SQL_09

1.5数据比对

核心表数据比对（比对核心数据表）：

set lines 200 pages 3000

col txt for a150

select 'select '||''''||OWNER||'.'||TABLE_NAME||''' as tabname'||',count(*) as cnt from '||OWNER||'.'||TABLE_NAME||';' as txt from dba_tables where owner='HIS40';

1.6配置OGG反向同步

ogg反向同步用来做为ogg迁移方式的应急预案，一旦迁移完成后出现不可控的数据库问题，可以反向切换到原来的数据库。

ogg反向同步的过程同1.4

1.6.1配置抽取进程

配置抽取进程参数：

EXTRACT emr_rxt

setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)

userid ogg@pdb_emr,password ogg

DISCARDFILE ./dirrpt/emr_rxt.dsc,APPEND,MEGABYTES 1024

DBOPTIONS  ALLOWUNUSEDCOLUMN

EXTTRAIL ./dirdat/er

ddl include all


TABLE HIS40.*;


数据库迁移11g到19c_oracle_10

创建抽取进程：

add extract emr_rxt  tranlog ,threads 2,begin now 

add exttrail ./dirdat/er extract emr_rxt ,megabytes 512

1.6.2配置投递进程

配置投递进程参数：

GGSCI (nk-zd1-comp) 5> view params EMR_RDP


EXTRACT emr_rdp

PASSTHRU

RMTHOST 160.168.101.60, MGRPORT 7809

RMTTRAIL ./dirdat/er


TABLE HIS40.*;


数据库迁移11g到19c_SQL_11

创建投递进程：

add extract emr_rdp exttrailsource ./dirdat/er 

add rmttrail ./dirdat/er extract emr_rdp ,megabytes 1024

1.6.3配置复制进程

配置复制进程参数：

replicat emr_rrp

userid ogg,password ogg

assumetargetdefs

allownoopupdates

discardfile ./dirrpt/emr_rrp.dsc,append,megabytes 1024


MAP HIS40.*,TARGET HIS40.*;

数据库迁移11g到19c_SQL_12


创建复制进程：

add replicat emr_rrp exttrail ./dirdat/er,nodbcheckpoint

数据库迁移11g到19c_oracle_13


2、业务割接

2.1停止所有业务

停止所有业务，检查数据库连接，确认源库没有链接进来。


2.2 锁定源库应用账号

alter user his40 account lock;

2.3 源库杀掉应用用户会话

select  'alter system kill session '''||sid||','||serial#||''' immediate -- ' ||username||'@'||machine||' ('||program||');' commands_to_verify_and_run from v$session where username=’HIS40’;

2.4 源库停止Jobs

select 'execute DBMS_IJOB.BROKEN('||job||',TRUE); commit;' from dba_jobs WHERE schema_user=’HIS40’;

2.5 验证源库是否存在业务账号连接

set lines 300 pages 5000

col INST_ID for a30

col username for a20

col machine for a20

col PROGRAM for a30

select INST_ID,username,status,machine,PROGRAM from gv$session where username='HIS40';

2.6 源库连续切换日志

--两个实例都要执行

alter system switch logfile;

alter system checkpoint global;


2.7 检查OGG同步进度

--检查OGG同步进度，lag time没有延迟即同步完毕。

-- 源库

lag emr_ext

lag emr_dpn

-- 源库

/ogg/ggsci

GGSCI> lag emr_rep

2.8 源与目标库数据对比

--将源与目标端执行的结果复制到excel 进行对比

@ emr_cnt.sql


2.9  停止OGG正向同步

--数据一致，停止源端OGG 正向同步

stop emr_ext

stop emr_dpn

-- 停止 目标端OGG 复制进程

stop emr_rep


2.10 源端禁用外键、触发器

SELECT 'ALTER TRIGGER HIS40.'||TRIGGER_NAME||' DISABLE;'  FROM DBA_TRIGGERS WHERE OWNER='HIS40' and TRIGGER_NAME in('TRI_IP_REF','HIS_IN_PATI_REG_TRIGGER','TRI_MDM_USER_NAME');

select 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name||';' from dba_constraints where constraint_type in ('R') and owner = 'HIS40';

2.11 开启反向抽取

--目标端：

add extract emr_rxt  tranlog ,threads 2,begin now 

add exttrail ./dirdat/er extract emr_rxt ,megabytes 512

register extract emr_rxt database container(his40)


add extract emr_rdp exttrailsource ./dirdat/er 

add rmttrail ./dirdat/er extract emr_rdp ,megabytes 1024


start emr_rxt

start emr_rdp


--源端：

add replicat emr_rrp exttrail ./dirdat/er,nodbcheckpoint



2.12 重建序列

--从原主库生成目标库序列重建语句：

set lines 300 pages 3000

select

'create or replace sequence '||sequence_owner||'.'||sequence_name||' start with '||last_number||'  maxvalue 9999999999999 increment by 1 cache '||cache_size||';' from (select * from dba_sequences where sequence_owner='HIS40')


2.13 启动触发器、外键、定时Job

--新环境启用触发器和外键：

SELECT 'ALTER TRIGGER HIS40.'||TRIGGER_NAME||' ENABLE;'  FROM DBA_TRIGGERS WHERE OWNER='HIS40';


select 'alter table '||owner||'.'||table_name||' enable constraint '||constraint_name||';' from dba_constraints where constraint_type in ('R') and owner = 'HIS40';


select 'execute DBMS_IJOB.BROKEN('||job||',FALSE); commit;' from dba_jobs WHERE schema_user='HIS40';


2.14 开启OGG反向同步

新目标端：

GGSCI> start emr_rrp

2.15 目标端解锁业务账号

-- 解锁业务账号，业务接入

alter user his40 account unlock;