Azure云上oracle 11g单机 升级到oracle 19c rac（物理机）

一、基本环境

源端：   windows 环境，oracle11g

目标端：linux 环境, oracle 19c rac

二、需求

需求就是将windows上的oracle11g 迁移到 oracle 19c rac环境，要求数据库停机时间不超过半小时

三、调研

1、客户的数据库里面有使用到sequence；

2、每天有新增表，有DDL操作；

3、有大表，测试导出的时候有报快照太久的情况；

四、迁移方法

通过oracle goldengate使用DDL模式，同步表以及sequence

五、实施

1、安装OGG

选择适配oracle 11g，和oracle 19c的goldengate 19.1.0.0

V1009716-01.zip (windows)
    V983658-01 （linux）

可以使用图形安装，也可以使用静默安装，我这里采用的是静默安装，以11g为例

2、  配置oggcore.rsp参数

INSTALL_OPTION=ORA11g
SOFTWARE_LOCATION=D:\ogg
START_MANAGER=TRUE
MANAGER_PORT= 7809
DATABASE_LOCATION= D:\app\oracle\product\11.2.0.4\db_1
INVENTORY_LOCATION= D:\app\oracle\oraInventory  (这个windows上可写可不写)

3、静默安装

./runInstaller -silent -responseFile D:\software\fbo_ggs_Linux_x64_shiphome\Disk1\response\oggcore.rsp

4、创建子目录

GGSCI (BRKVANA) 2> create subdirs 
Creating subdirectories under current directory  D:\\ogg
Parameter files D:\\ogg\\dirprm: already exists  
Report files D:\\ogg\\dirrpt: created  
Checkpoint files D:\\ogg\\dirchk: created  
Process status files D:\\ogg\\dirpcs: created  
SQL script files D:\\ogg\\dirsql: created  
Database definitions files D:\\ogg\\dirdef: created  
Extract data files D:\\ogg\\dirdat: created  
Temporary files D:\\ogg\\dirtmp: created  
Credential store files D:\\ogg\\dircrd: created  
Masterkey wallet files D:\\ogg\\dirwlt: created  
Dump files D:\\ogg\\dirdmp: created

5、创建同步管理用户ogg

create tablespace ogg datafile ‘D:\\app\\oracle\\oradata\\hdadb\\ogg02.dbf’ size 100m autoextend on;  
    create user ogg identified by ogg default tablespace ogg temporary tablespace temp;  
    grant connect to ogg;  
    grant alter any table to ogg;  
    grant alter session to ogg;  
    grant create session to ogg;  
    grant flashback any table to ogg;  
    grant select any dictionary to ogg;  
    grant select any table to ogg;  
    grant resource to ogg;  
    grant select any transaction to ogg;

6、数据库表、sequence开启supplemental logging

alter table sys.seq$ add supplemental log data (primary key) columns;  
alter database add supplemental log data (primary key,unique,foreign key) columns;  
alter table sys.seq$ add supplentntal log data (primary key ) columns ;

7、ogg创建alia用户

add credentialstore  
alter credentialstore add user ogg@ecology alias user
dblogin useridalias user

8、编辑mgr参数文件

PORT 7809
DYNAMICPORTLIST 7840-7914
LAGCRITICALSECONDS 60
LAGINFOMINUTES 60
PURGEOLDEXTRACTS D:\\ogg\\dirdat\\\*, usecheckpoints, minkeepfiles 10
autorestart er \*,retries 5,waitminutes 5,resetminutes 60
checkminutes 20

9、增加DDL支持

@marker\_setup.sql  
@ddl\_setup.sql  
@role\_setup.sql  
GRANT GGS\_GGSUSER\_ROLE TO ogg;  
@ddl\_enable.sql  
@marker\_status.sql

10、抽取进程

add extract e\_swp,tranlog begin now
edit params e\_swp
add exttrail .\\dirdat\\ww,extract e\_swp,megabytes 50

11 、配置抽取进程参数

EXTRACT exswp  
SETENV(ORACLE\_SID=‘swpdb’)  
DBOPTIONS ALLOWUNUSEDCOLUMN  
TRANLOGOPTIONS DBLOGREADER  
useridalias user  
EXTTRAIL D:\\ogg\\dirdat\\sw  
REPORT AT 01:59  
reportrollover at 02:00  
TRANLOGOPTIONS BUFSIZE 10000000  
discardfile D:\\ogg\\dirrpt\\sw1.dsc,append,megabytes 100  
NUMFILES 3000  
–DYNAMICRESOLUTION  
STATOPTIONS REPORTFETCH  
DDL &  
INCLUDE OBJTYPE SEQUENCE &  
INCLUDE MAPPED OPTYPE ALTER &  
INCLUDE MAPPED OPTYPE CREATE &  
INCLUDE MAPPED OPTYPE DROP &  
INCLUDE MAPPED OBJTYPE ‘TABLE’ &  
INCLUDE MAPPED OBJTYPE ‘INDEX’ &  
EXCLUDE OPTYPE TRUNCATE &  
EXCLUDE OPTYPE COMMENT  
SEQUENCE SWP.\*  
TABLE SWP.\*;

12、配置推送进程

add extract D\_SWP,exttrailsource .\\dirdat\\ww
add rmttrail ./dirdat/ww,extract D\_SWP,megabytes 50

13、参数配置

EXTRACT d\_swp  
PASSTHRU  
RMTHOST swp3.com, MGRPORT 7890  
RMTTRAIL /u01/app/ogg/dirdat/sw  
WILDCARDRESOLVE DYNAMIC  
GETTRUNCATES  
SEQUENCE SWP._;  
TABLE SWP._;  

14、启动抽取和推送进程

start e\_swp,d\_swp  

15、初始化数据导出

expdp " / as sysdba " schemas=swp directory=expdp paralell=6 dumpfile=swp\_%U.dmp flashback\_scn=2538237  

16、目标端表空间文件创建（19c）

create tablespace swp\_data;  

17、初始化数据导入(19c)

impdp " / as sysdba " schemas=swp directory=expdp paralell =6 dumpfile=swp\_%U.dmp logfile=imp0823.log 

18、ogg安装

ogg安装（略） mgr配置（参考11g配置）

19、添加复制进程

ADD CHECKPOINTTABLE ogg.checkpoint  
add replicat r\_swp, exttrail /u01/app/ogg/dirdat/ww, CHECKPOINTTABLE ogg.checkpoint 

20、编写参数

REPLICAT r\_swp  
SETENV (ORACLE\_SID=‘ora19c’)  
USERID ogg, PASSWORD ogg  
ALLOWNOOPUPDATES  
ASSUMETARGETDEFS  
SQLEXEC “ALTER SESSION SET CONSTRAINTS=DEFERRED”  
REPORT AT 01:59  
REPORTROLLOVER AT 02:00  
–HANDLECOLLISIONS  
DISCARDFILE /u01/app/ogg/dirdat/sw.dsc,PURGE  
DISCARDROLLOVER AT 02:00  
GETTRUNCATES  
–ASSUMETARGETDEFS  
–DYNAMICRESOLUTION  
ALLOWNOOPUPDATES;  
DDL INCLUDE ALL  
MAP SWP.\*_, TARGET SWP.\*;_

21、启动进程

Start replicat r\_swp,aftercsn 2538237