标签：11g 12 07 leo oracle ogg u01
文档课题：运用ogg迁移oracle 11g到19c.
核心思想：利用数据泵导入导出功能初始化数据后通过OGG同步增量数据.
源 端：192.168.133.103 数据库oracle 11.2.0.4 64位，实例名：orcl
目 标 端：192.168.133.109 数据库oracle 19.16.0.0 64位，实例名：simdb
ogg安装包：191004_fbo_ggs_Linux_x64_shiphome.zip
说明：源端与目标端均使用该ogg安装包.
1、安装ogg
说明：创建ogg安装目录,通常使用系统用户oracle作为ogg的安装用户.
1.1、创建目录
--源端
[root@leo-11g-ogg:~]# mkdir -p /u01/app/ogg
[root@leo-11g-ogg:~]# chown -R oracle:oinstall /u01/app/ogg
[root@leo-11g-ogg:~]# mkdir -p /u01/setup/ogg
--目标端
[root@leo-19c-ogg:~]# mkdir -p /u01/app/ogg
[root@leo-19c-ogg:~]# chown -R oracle:oinstall /u01/app/ogg
[root@leo-19c-ogg:~]# mkdir -p /u01/setup/ogg
1.2、环境变量
--源端
[oracle@leo-11g-ogg ~]$ vi .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
# OracleBegin
umask 022
export TMP=/tmp
export TMPDIR=$TMP
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export ORACLE_SID=orcl
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin:$PATH
export PERL5LIB=$ORACLE_HOME/perl/lib
alias sas='sqlplus / as sysdba'
alias awr='sqlplus / as sysdba @?/rdbms/admin/awrrpt'
alias ash='sqlplus / as sysdba @?/rdbms/admin/ashrpt'
alias alert='vi $ORACLE_BASE/diag/rdbms/*/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
#export PS1="[`whoami`@`hostname`:"'\w]$ '
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
alias adrci='rlwrap adrci'
export OGG_HOME=/u01/app/ogg
export NLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS"
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export PATH=.:$PATH:$HOME/.local/bin:$HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$OGG_HOME:/lib/usr/lib:/usr/local/lib
alias ggsci='cd $OGG_HOME;rlwrap ggsci'
说明：最后6行为新增部分.
[oracle@leo-11g-ogg:~]$ source .bash_profile
--目标端
[oracle@leo-19c-ogg ~]$ vi .bash_profile
# .bash_profile
# Get the aliases and functions
if [ -f ~/.bashrc ]; then
. ~/.bashrc
fi
# User specific environment and startup programs
PATH=$PATH:$HOME/.local/bin:$HOME/bin
export PATH
# OracleBegin
umask 022
export TMP=/tmp
export TMPDIR=$TMP
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/19.3.0/db
export ORACLE_TERM=xterm
export TNS_ADMIN=$ORACLE_HOME/network/admin
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib
export ORACLE_SID=simdb
export PATH=/usr/sbin:$PATH
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin:$PATH
export PERL5LIB=$ORACLE_HOME/perl/lib
alias sas='sqlplus / as sysdba'
alias awr='sqlplus / as sysdba @?/rdbms/admin/awrrpt'
alias ash='sqlplus / as sysdba @?/rdbms/admin/ashrpt'
alias alert='vi $ORACLE_BASE/diag/rdbms/*/$ORACLE_SID/trace/alert_$ORACLE_SID.log'
#export PS1="[`whoami`@`hostname`:"'\w]$ '
alias sqlplus='rlwrap sqlplus'
alias rman='rlwrap rman'
alias adrci='rlwrap adrci'
export OGG_HOME=/u01/app/ogg
export NLS_DATE_FORMAT="yyyy-mm-dd HH24:MI:SS"
export NLS_LANG=AMERICAN_AMERICA.AL32UTF8
export PATH=.:$PATH:$HOME/.local/bin:$HOME/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/bin:$OGG_HOME
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$ORACLE_HOME/lib32:$OGG_HOME:/lib/usr/lib:/usr/local/lib
alias ggsci='cd $OGG_HOME;rlwrap ggsci'
说明：最后6行为新增部分.
[oracle@leo-19c-ogg ~]$ source .bash_profile
1.3、安装包处理
--源端
sftp> cd /u01/setup/ogg
sftp> lcd F:\installmedium\ogg
sftp> put 191004_fbo_ggs_Linux_x64_shiphome.zip
[root@leo-11g-ogg:~]# chown -R oracle:oinstall /u01/setup
[oracle@leo-11g-ogg ogg]$ pwd
/u01/setup/ogg
[oracle@leo-11g-ogg ogg]$ unzip -q 191004_fbo_ggs_Linux_x64_shiphome.zip
[oracle@leo-11g-ogg ogg]$ ll
total 543540
-rw-r--r-- 1 oracle oinstall 556240981 Nov 30 10:41 191004_fbo_ggs_Linux_x64_shiphome.zip
drwxr-xr-x 3 oracle oinstall 19 Oct 18 2019 fbo_ggs_Linux_x64_shiphome
-rw-r--r-- 1 oracle oinstall 1413 May 29 2019 OGG-19.1.0.0-README.txt
-rw-r--r-- 1 oracle oinstall 332523 Oct 21 2019 OGG_WinUnix_Rel_Notes_19.1.0.0.4.pdf
--目标端
sftp> cd /u01/setup/ogg
sftp> lcd F:\installmedium\ogg
sftp> put 191004_fbo_ggs_Linux_x64_shiphome.zip
[root@leo-19c-ogg:~]# chown -R oracle:oinstall /u01/setup
[oracle@leo-19c-ogg ogg]$ pwd
/u01/setup/ogg
[oracle@leo-19c-ogg ogg]$ unzip -q 191004_fbo_ggs_Linux_x64_shiphome.zip
[oracle@leo-19c-ogg ogg]$ ll
total 543540
-rw-r--r-- 1 oracle oinstall 556240981 Nov 30 10:41 191004_fbo_ggs_Linux_x64_shiphome.zip
drwxr-xr-x 3 oracle oinstall 19 Oct 18 2019 fbo_ggs_Linux_x64_shiphome
-rw-r--r-- 1 oracle oinstall 1413 May 29 2019 OGG-19.1.0.0-README.txt
-rw-r--r-- 1 oracle oinstall 332523 Oct 21 2019 OGG_WinUnix_Rel_Notes_19.1.0.0.4.pdf
1.4、安装ogg软件
说明：ogg自12c以后可以图形化安装，也可以命令行安装.
--源端
[oracle@leo-11g-ogg response]$ pwd
/u01/setup/ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response
[oracle@leo-11g-ogg response]$ vi oggcore.rsp
修改如下两行：
INSTALL_OPTION=ORA11g
SOFTWARE_LOCATION=/u01/app/ogg
静默安装：
[oracle@leo-11g-ogg response]$ /u01/setup/ogg/fbo_ggs_Linux_x64_shiphome/Disk1/runInstaller -silent -responseFile /u01/setup/ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 76574 MB Passed
Checking swap space: must be greater than 150 MB. Actual 4095 MB Passed
Preparing to launch Oracle Universal Installer from /tmp/OraInstall2022-12-07_09-34-29PM. Please wait ...[oracle@leo-11g-ogg response]$ You can find the log of this install session at:
/u01/app/oraInventory/logs/installActions2022-12-07_09-34-29PM.log
Successfully Setup Software.
The installation of Oracle GoldenGate Core was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2022-12-07_09-34-29PM.log' for more details.
--目标端
[oracle@leo-19c-ogg response]$ pwd
/u01/setup/ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response
[oracle@leo-19c-ogg response]$ vi oggcore.rsp
修改如下两行:
INSTALL_OPTION=ORA19c
SOFTWARE_LOCATION=/u01/app/ogg
静默安装:
[oracle@leo-19c-ogg response]$ /u01/setup/ogg/fbo_ggs_Linux_x64_shiphome/Disk1/runInstaller -silent -responseFile /u01/setup/ogg/fbo_ggs_Linux_x64_shiphome/Disk1/response/oggcore.rsp
Starting Oracle Universal Installer...
Checking Temp space: must be greater than 120 MB. Actual 67236 MB Passed
Checking swap space: must be greater than 150 MB. Actual 8191 MB Passed
PreparYou can find the log of this install session at:mp/OraInstall2022-12-07_09-39-59PM. Please wait ...
/u01/app/oraInventory/logs/installActions2022-12-07_09-39-59PM.log
Successfully Setup Software.
The installation of Oracle GoldenGate Core was successful.
Please check '/u01/app/oraInventory/logs/silentInstall2022-12-07_09-39-59PM.log' for more details.
2、数据库配置
2.1、源端
2.1.1、开启强制生成日志&补充日志
sys@ORCL 2022-12-07 21:43:24> select log_mode,force_logging,SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
LOG_MODE FOR SUPPLEME SUP SUP
------------ --- -------- --- ---
ARCHIVELOG NO NO NO NO
sys@ORCL 2022-12-07 21:44:25> alter database add supplemental log data;
Database altered.
sys@ORCL 2022-12-07 21:44:58> alter database force logging;
Database altered.
sys@ORCL 2022-12-07 21:45:10> select log_mode,force_logging,SUPPLEMENTAL_LOG_DATA_MIN,SUPPLEMENTAL_LOG_DATA_PK,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
LOG_MODE FOR SUPPLEME SUP SUP
------------ --- -------- --- ---
ARCHIVELOG YES YES NO NO
说明：
a、 需确保数据库归档已开启.
b、 对于逻辑复制，如何将源端操作准确无误地反应到目标端？
源端数据库和目标端数据库的数据块结构可能完全不一样，存在无法通过rowid信息进行精确查找与准确定位的问题，此时就需要开启supplemental log，当启用supplemental logging，对于修改操作oracle就会同时附加一些能够唯一标识修改记录的列到rebo log中.如果表有主键或唯一键，只需附加主键或唯一建信息即可，此时生成的redo日志量最少；如果某些表无法创建主键或唯一键，或者数据表本来就不存在主键/唯一键，此时oracle会将所有列都作为附加信息记录到redo中，那么redo就可能增长很快，此时对性能会产生很大影响.所以oracle建议所有需要复制的表都需存在主键或唯一键.supplemental logging可以在数据库级设置，也可以在表级设置.对于数据库级有两种类型：minimal logging和identification key logging，其主要区别在于写入redolog中的数据详尽程度不同，
综上：GoldenGate要准确知道源端数据修改的具体列信息，就需要更为详细的日志信息，所以需开启supplemental log.
2.1.2、开启enable_ goldengate_replication
sys@ORCL 2022-12-07 21:42:25> show parameter enable_goldengate_replication
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean FALSE
sys@ORCL 2022-12-07 21:42:39> alter system set enable_goldengate_replication=true scope=both;
System altered.
sys@ORCL 2022-12-07 21:43:11> show parameter enable_goldengate_replication
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean TRUE
2.2、目标端
2.2.1、开启enable_ goldengate_replication
sys@SIMDB 2022-12-07 23:52:37> alter system set enable_goldengate_replication=true;
System altered.
sys@SIMDB 2022-12-07 23:53:02> show parameter enable_goldengate_replication
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
enable_goldengate_replication boolean TRUE
3、创建用户、表空间及授权
3.1、源端
创建ogg表空间、用户并授权：
create tablespace ogg_tbs datafile '/u01/app/oracle/oradata/orcl/tbs_ogg01.dbf' size 1G autoextend on maxsize 30G;
create user ogg identified by ogg default tablespace ogg_tbs;
grant connect,resource,unlimited tablespace to ogg;
grant create session,alter session,create sequence,create table to ogg;
grant select any dictionary,select any table to ogg;
grant alter any table to ogg;
grant flashback any table to ogg;
grant execute on dbms_flashback to ogg;
grant execute on utl_file to ogg;
grant select any transaction to ogg;
grant become user to ogg;
exec dbms_streams_auth.grant_admin_privilege('ogg');
3.2、目标端
创建ogg表空间和用户，并授权.
create tablespace tbs_ogg datafile '/u01/app/oracle/oradata/SIMDB/tbs_ogg01.dbf' size 1G autoextend on maxsize 30G;
create user ogg identified by ogg default tablespace tbs_ogg;
grant connect,resource,unlimited tablespace to ogg;
grant create session,alter session to ogg;
grant select any dictionary,select any table to ogg;
grant alter any table to ogg;
grant flashback any table to ogg;
grant execute on dbms_flashback to ogg;
grant execute on utl_file to ogg;
grant dba to ogg;
grant select any transaction to ogg;
exec DBMS_GOLDENGATE_AUTH.GRANT_ADMIN_PRIVILEGE('ogg');
4、配置DDL同步
4.1、早期版本11204之前
[oracle@leo-11g-ogg response]$ cd $OGG_HOME
[oracle@leo-11g-ogg ogg]$ pwd
/u01/app/ogg
[oracle@leo-11g-ogg ogg]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.4.0 Production on Wed Dec 7 21:59:02 2022
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, OLAP, Data Mining and Real Application Testing options
sys@ORCL 2022-12-07 21:59:02> @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:ogg --输入ogg用户
Marker setup table script complete, running verification script...
Please enter the name of a schema for the GoldenGate database objects:
Setting schema name to OGG
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
sys@ORCL 2022-12-07 21:59:21> @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:ogg --输入ogg用户
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.
……(省略若干)
VERSION OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
sys@ORCL 2022-12-07 22:00:17> @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:ogg --输入ogg用户
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.
sys@ORCL 2022-12-07 22:00:50> grant GGS_GGSUSER_ROLE to ogg;
Grant succeeded.
sys@ORCL 2022-12-07 22:01:29> @ddl_enable.sql
Trigger altered.
4.2、配置DDL捕获说明
Extract可以通过使用特殊的DDL触发器或本地通过Oracle日志挖掘服务器从源Oracle数据库捕获DDL操作.
在集成捕获模式下支持 DDL捕获
Extract的集成捕获模式支持两种DDL捕获方式：
在集成捕获模式下支持 DDL 捕获
Oracle 11.2.0.4或更高版本：数据库 COMPATIBLE 参数设置为11.2.0.4或更高版本的Oracle数据库支持通过数据库日志挖掘服务器进行DDL捕获.这种方法称为原生 DDL捕获（也称为无触发 DDL捕获）.不需要安装触发器支持对象.本机 DDL捕获是从多租户容器数据库捕获DDL的唯一受支持方法.对于下游挖掘，源数据库还必须将数据库COMPATIBLE设置为11.2.0.4或更高版本，以支持通过数据库日志挖掘服务器进行DDL捕获.
早于11.2.0.4的版本：COMPATIBLE参数设置为早于11.2.0.4的Oracle数据库需要使用Oracle GoldenGate DDL触发器.要使用基于触发器的DDL捕获，必须先安装DDL触发器和支持的数据库对象，然后才能为DDL支持配置Extract.
支持经典捕获模式下的DDL捕获
经典捕获模式需要使用Oracle GoldenGate DDL触发器从Oracle数据库捕获DDL.经典捕获模式不支持原生DDL捕获.经典捕获模式不支持从多租户容器数据库捕获 DDL.当使用经典捕获模式并 CREATE USER 使用 DDL触发器复制时，触发器所有者和Extract登录用户必须匹配以避免尝试复制 CREATE USER 命令时出现权限错误.要使用基于触发器的 DDL捕获，必须在为DDL支持配置Extract之前安装DDL触发器和支持的数据库对象.
4.3、GoldenGate DDL支持的限制
1）、DDL语句长度
2）、支持的拓扑
3）、过滤、映射和转换
4）、重命名
5）、从表中提取和DDL之间的交互
6）、SQL中的注释
7）、编译错误
8）、间隔分区
9）、在DDL触发器内执行的DML或DDL
10）、LogMiner数据字典维护
DDL语句长度
Oracle GoldenGate以字节而不是字符来衡量DDL语句的长度.支持的长度约为4 MB，允许一些内部开销的大小取决于受影响对象的名称及其DDL类型，以及其他特征.如果DDL长于支持的大小，Extract将发出警告并忽略DDL操作.
如果 Extract正在通过DDL触发器捕获DDL，则忽略的DDL将保存在标记表中.可以使用ddl_ddl2file.sql脚本捕获被忽略的Oracle DDL语句以及任何其他Oracle DDL语句，该脚本将DDL操作保存到USER_DUMP_DEST Oracle目录中的文本文件中.该脚本提示输入以下内容：
包含在 GLOBALS 文件中指定的 Oracle GoldenGate DDL对象的模式的名称.
Oracle GoldenGate标记序列号，当在Extract参数文件中使用 DDLOPTIONS 该REPORT 选项时，它会记录在 Extract 报告文件中.
输出文件的名称.
支持的拓扑
Oracle GoldenGate仅在同类配置中支持DDL同步.源和目标对象定义必须相同.
DDL复制仅支持Oracle到Oracle复制.它在不同的数据库之间不受支持，例如Oracle到Teradata，或SQL Server到Oracle.
Oracle GoldenGate不支持备用数据库上的DDL.
Oracle GoldenGate在所有受支持的单向配置中以及在两个且仅两个系统之间的双向配置中支持DDL复制.
说明：其余八项可查官方文档.
4.4、配置tns
注意若环境为多实例则需要配置tns登录创建的ogg用户,19c PDB也需要tns登录到相应的数据库.
ORCL =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = leo-11g-ogg)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
4.5、打开表级附加日志
[oracle@leo-11g-ogg response]$ cd $OGG_HOME
[oracle@leo-11g-ogg response]$ pwd
/u01/app/ogg
[oracle@leo-11g-ogg response]$ ./ggsci
GGSCI (leo-11g-ogg) 2> dblogin userid ogg@ORCL,password ogg --注意要是多实例需配置tns登录创建的ogg
Successfully logged into database.
GGSCI (leo-11g-ogg as ogg@orcl) 2> add trandata scott.* --注意不要加分号
2022-12-07 22:09:07 WARNING OGG-06439 No unique key is defined for table BONUS. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2022-12-07 22:09:07 INFO OGG-15130 No key found for table SCOTT.BONUS. All viable columns will be logged.
2022-12-07 22:09:08 INFO OGG-15132 Logging of supplemental redo data enabled for table SCOTT.BONUS.
2022-12-07 22:09:08 INFO OGG-15133 TRANDATA for scheduling columns has been added on table SCOTT.BONUS.
2022-12-07 22:09:08 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table SCOTT.BONUS.
2022-12-07 22:09:08 INFO OGG-15132 Logging of supplemental redo data enabled for table SCOTT.DEPT.
2022-12-07 22:09:08 INFO OGG-15133 TRANDATA for scheduling columns has been added on table SCOTT.DEPT.
2022-12-07 22:09:08 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table SCOTT.DEPT.
2022-12-07 22:09:08 INFO OGG-15132 Logging of supplemental redo data enabled for table SCOTT.EMP.
2022-12-07 22:09:09 INFO OGG-15133 TRANDATA for scheduling columns has been added on table SCOTT.EMP.
2022-12-07 22:09:09 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table SCOTT.EMP.
2022-12-07 22:09:09 WARNING OGG-06439 No unique key is defined for table SALGRADE. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.
2022-12-07 22:09:09 INFO OGG-15130 No key found for table SCOTT.SALGRADE. All viable columns will be logged.
2022-12-07 22:09:09 INFO OGG-15132 Logging of supplemental redo data enabled for table SCOTT.SALGRADE.
2022-12-07 22:09:09 INFO OGG-15133 TRANDATA for scheduling columns has been added on table SCOTT.SALGRADE.
2022-12-07 22:09:09 INFO OGG-15135 TRANDATA for instantiation CSN has been added on table SCOTT.SALGRADE.
说明：数据库附加日志打开后还需要对每张表执行add trandata吗？答案是肯定的，如果不执行add trandata，insert同步没有问题（ORACLE数据库），但在同步update或delete时，就会因为丢失主键报同步错误.不开启表级的最小附加日志，update的redo信息不记录没有进行更新的字段信息，如主键不更新的话主键不记录在redo中，所以会导致同步失败.
--查附加日志是否添加成功.
GGSCI (leo-11g-ogg as ogg@orcl) 3> info trandata scott.*
Logging of supplemental redo log data is enabled for table SCOTT.BONUS.
Columns supplementally logged for table SCOTT.BONUS: "COMM", "ENAME", "JOB", "SAL".
Prepared CSN for table SCOTT.BONUS: 2085828
Logging of supplemental redo log data is enabled for table SCOTT.DEPT.
Columns supplementally logged for table SCOTT.DEPT: "DEPTNO".
Prepared CSN for table SCOTT.DEPT: 2085857
Logging of supplemental redo log data is enabled for table SCOTT.EMP.
Columns supplementally logged for table SCOTT.EMP: "EMPNO".
Prepared CSN for table SCOTT.EMP: 2085886
Logging of supplemental redo log data is enabled for table SCOTT.SALGRADE.
Columns supplementally logged for table SCOTT.SALGRADE: "GRADE", "HISAL", "LOSAL".
Prepared CSN for table SCOTT.SALGRADE: 2085917
5、OGG配置
5.1、源端
5.1.1、创建目录
说明：必须在ogg的home目录下启动ggsci，这样才能把子目录建在ogg的home目录下，如果不在相应位置建立子目录，在后面编辑参数文件时会报错.
[oracle@leo-11g-ogg ogg]$ cd $GG_HOME
[oracle@leo ogg]$ ./ggsci
--创建 GG工作目录
GGSCI (leo-11g-ogg as ogg@orcl) 1> create subdirs
Creating subdirectories under current directory /u01/app/ogg
Parameter file /u01/app/ogg/dirprm: created.
Report file /u01/app/ogg/dirrpt: created.
Checkpoint file /u01/app/ogg/dirchk: created.
Process status files /u01/app/ogg/dirpcs: created.
SQL script files /u01/app/ogg/dirsql: created.
Database definitions files /u01/app/ogg/dirdef: created.
Extract data files /u01/app/ogg/dirdat: created.
Temporary files /u01/app/ogg/dirtmp: created.
Credential store files /u01/app/ogg/dircrd: created.
Masterkey wallet files /u01/app/ogg/dirwlt: created.
Dump files /u01/app/ogg/dirdmp: created.
重要目录说明：
dirchk：存放检查点(checkpoint)文件
dirdat：存放trail文件
dirdef：存放通过defgen工具生成的源或目标端数据定义文件
dirpcs：存放进程状态文件
dirprm：存放配置参数文件
dirrpt：存放进程报告文件
dirsql：存放sql脚本文件
dirtmp：当事务所需要的内存超过已分配的内存时，默认存储在此目录
5.1.2、添加端口
5.1.2.1、源端配置
GGSCI (leo-11g-ogg as ogg@orcl) 2> edit param mgr
添加如下内容
port 7809
DYNAMICPORTLIST 7810-7829
userid ogg@ORCL, PASSWORD ogg
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *
PURGEOLDEXTRACTS /u01/app/ogg/dirdat/*,usecheckpoints, minkeepdays 8
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
ACCESSRULE, PROG *, IPADDR 12.*.*.*, PRI 1, ALLOW
参数说明：
PORT:指定Manager使用的端口.
Dynamicportliist:配置捕获和复制进程使用的端口范围.
AUTORESTART:使抽取/复制进程失败后自动重启.
PURGEOLDEXTRACTS:参数指定当根据checkpoint发现已完成抽取和复制的trail文件将被自动删除，但保留最近10个.
Purgeddlhistory和purgemarkerhistory分别删除历史DD、历史表和marker表中的过期数据，以控制其不会过于庞大.
GGSCI (leo-11g-ogg as ogg@orcl) 3> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (leo-11g-ogg as ogg@orcl) 4> start mgr
Manager started.
GGSCI (leo-11g-ogg as ogg@orcl) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
5.1.2.2、目标端配置
提前在目标端添加mgr端口.
[oracle@leo-19c-ogg ~]$ cd $OGG_HOME
[oracle@leo-19c-ogg ogg]$ pwd
/u01/app/ogg
[oracle@leo-19c-ogg ogg]$ ./ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 19.1.0.0.4 OGGCORE_19.1.0.0.0_PLATFORMS_191017.1054_FBO
Linux, x64, 64bit (optimized), Oracle 19c on Oct 17 2019 21:16:29
Operating system character set identified as UTF-8.
Copyright (C) 1995, 2019, Oracle and/or its affiliates. All rights reserved.
GGSCI (leo-19c-ogg) 1> dblogin userid ogg@SIMDB,password ogg
Successfully logged into database.
GGSCI (leo-19c-ogg as ogg@simdb) 2> create subdirs
Creating subdirectories under current directory /u01/app/ogg
Parameter file /u01/app/ogg/dirprm: created.
Report file /u01/app/ogg/dirrpt: created.
Checkpoint file /u01/app/ogg/dirchk: created.
Process status files /u01/app/ogg/dirpcs: created.
SQL script files /u01/app/ogg/dirsql: created.
Database definitions files /u01/app/ogg/dirdef: created.
Extract data files /u01/app/ogg/dirdat: created.
Temporary files /u01/app/ogg/dirtmp: created.
Credential store files /u01/app/ogg/dircrd: created.
Masterkey wallet files /u01/app/ogg/dirwlt: created.
Dump files /u01/app/ogg/dirdmp: created.
GGSCI (leo-19c-ogg as ogg@simdb) 3> edit param mgr
添加以下：
port 7809
DYNAMICPORTLIST 7810-7829
userid ogg@SIMDB, password ogg
--AUTOSTART EXTRACT *
AUTORESTART EXTRACT *,RETRIES 3,WAITMINUTES 7,RESETMINUTES 60
PURGEOLDEXTRACTS /u01/app/ogg/dirdat/*,usecheckpoints, minkeepdays 8
PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
LAGREPORTHOURS 1
--LAGINFOMINUTES 30
--LAGCRITICALMINUTES 45
参数说明：
AUTORESTART:表示每7分钟尝试重新启动所有进程，共尝试三次.以后每60分钟清零，再按照每7分钟尝试一次共试三次.
GGSCI (leo-19c-ogg as ogg@simdb) 4> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
GGSCI (leo-19c-ogg as ogg@simdb) 5> start mgr
Manager started.
GGSCI (leo-19c-ogg as ogg@simdb) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
5.1.3、抓取进程
--配置extract抓取进程extu1，负责从源端数据表或日志中捕获数据.
GGSCI (leo-11g-ogg as ogg@orcl) 7> add extract extu1, TRANLOG, BEGIN NOW
EXTRACT added.
GGSCI (leo-11g-ogg as ogg@orcl) 8> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT STOPPED EXTU1 00:00:00 00:02:04
--添加本地队列ss，extu1进程捕捉到的有效数据将写入本地队列.
GGSCI (leo-11g-ogg as ogg@orcl) 9> add exttrail /u01/app/ogg/dirdat/ss, extract extu1, megabytes 1024
EXTTRAIL added.
--为extu1进程配置参数
GGSCI (leo-11g-ogg as ogg@orcl) 10> edit param extu1
添加如下：
EXTRACT extu1
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
setenv (ORACLE_SID=orcl)
setenv (ORACLE_HOME=/u01/app/oracle/product/11.2.0/db)
setenv (TNS_ADMIN=/u01/app/oracle/product/11.2.0/db/network/admin)
userid ogg@ORCL, password ogg
REPORTCOUNT EVERY 30 MINUTES, RATE
DISCARDFILE /u01/app/ogg/dirrpt/extu_ss.dsc, APPEND, MEGABYTES 1024
DISCARDROLLOVER AT 9:00
EXTTRAIL /u01/app/ogg/dirdat/ss
GETTRUNCATES
--DYNAMICRESOLUTION
TRANLOGOPTIONS EXCLUDEUSER ogg
NOCOMPRESSUPDATES
NOCOMPRESSDELETES
GETUPDATEBEFORES
STATOPTIONS REPORTFETCH
DDL INCLUDE ALL
DDLOPTIONS ADDTRANDATA
DDLOPTIONS REPORT
table SCOTT.*;
参数说明:
GETUPDATEBEFORES：是否在队列中写入后镜像，也可进行维护事务历史表.默认是复制；
GETTRUNCATES：是否在队列中进行复制truncate操作，默认是不复制；
BR BRINTERVAL：对于存在长事务恢复情况下，恢复到检查点时间界限；
CACHEMGR CACHESIZE：主要用于控制存放未提交事务的虚拟内存和文件缓存空间；
WARNLONGTRANS：长事务警告频率，用于收集监控长事务情况；
CHECKINTERVAL：同样也是作为长事务WARNLONGTRANS监控频率；
--频率格式
S|SEC|SECS|SECOND|SECONDS
M|MIN|MINS|MINUTE|MINUTES
H|HOUR|HOURS
D|DAY|DAYS
NUMFILES:控制用于存储Oracle GoldenGate有关需要处理和map表的信息的内存的初始分配，类似于缓存数目；
EOFDELAYCSECS:控制extract进程在数据源检查新数据的频度；
TRANLOGOPTIONS DBLOGREADER：该参数用于直接访问重做日志和归档日志，不需要通过连接到ASM 实例访问；
TRANLOGOPTIONS MINEFROMACTIVEDG：该参数用于从ADG 中读取到日志信息；
TRANLOGOPTIONS PURGEORPHANEDTRANSACTIONS；清除孤立的事务.解释：通过将其启动时间与节点的启动时间进行比较，事务在清除之前被验证为孤立的; 如果交易开始较早，则会被清除；
TRANLOGOPTIONS _DISABLESTREAMLINEDDBLOGREADER：这个参数在11.2.1.0.26以后才有，用于DBLOGREADER模式下，处理一些异常丢失事务的情况；
DYNAMICRESOLUTION：extract 进程启动后，快速处理指定的表和map（开启这个参数，遇到记录中的表是才加载表的源数据到内存中）；
Discardfile：指定discard目录，用于记录无法处理的日志信息 .
LOGALLSUPCOLS：该参数为extract capture进程记录补充日志中update和delete操作前的镜像信息.
--启动extu1捕获进程
GGSCI (leo-11g-ogg as ogg@orcl) 11> start extu1
Sending START request to MANAGER ...
EXTRACT EXTU1 starting
GGSCI (leo-11g-ogg as ogg@orcl) 12> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXTU1 00:08:34 00:00:03
5.1.4、投递进程
--源端数据库配置extract投递进程dpe1.
GGSCI (leo-11g-ogg as ogg@orcl) 13> add extract dpe1, exttrailsource /u01/app/ogg/dirdat/ss
EXTRACT added.
--添加远程trail文件.
GGSCI (leo-11g-ogg as ogg@orcl) 14> add rmttrail /u01/app/ogg/dirdat/ss, EXTRACT dpe1, MEGABYTES 1024
RMTTRAIL added.
GGSCI (leo-11g-ogg as ogg@orcl) 15> edit param dpe1
添加如下：
EXTRACT dpe1
DYNAMICRESOLUTION
PASSTHRU
RMTHOST 192.168.133.109, MGRPORT 7809, COMPRESS
RMTTRAIL /u01/app/ogg/dirdat/ss
TABLE scott.*;
参数说明:
PASSTHRU：不登录到数据库操作（数据投递不必登录数据库）
DYNAMICRESOLUTION：动态解析
Rmthost：远端主机（IP 或者主机名解析）
Rmttrail：目标端trail文件存储位置以及名称
--启用投递进程
GGSCI (leo-11g-ogg as ogg@orcl) 22> start dpe1
Sending START request to MANAGER ...
EXTRACT DPE1 starting
GGSCI (leo-11g-ogg as ogg@orcl) 23> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING DPE1 00:00:00 00:15:01
EXTRACT RUNNING EXTU1 00:00:00 00:00:08
注意：启动投递进程 dpe1前，目标端MGR需先启动.
5.2、目标端
5.2.1、添加checkpointtable
[oracle@leo-19c-ogg ~]$ cd $OGG_HOME
[oracle@ leo-19c-ogg ogg]$ ./ggsci
GGSCI (leo-19c-ogg as ogg@simdb) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
GGSCI (leo-19c-ogg as ogg@simdb) 7> add checkpointtable ogg.rep1_ckpt
Successfully created checkpoint table ogg.rep1_ckpt.
GGSCI (leo-19c-ogg as ogg@simdb) 8> add replicat rep1, exttrail /u01/app/ogg/dirdat/ss, checkpointtable ogg.rep1_ckpt
REPLICAT added.
GGSCI (leo-19c-ogg as ogg@simdb) 9> edit param rep1
添加如下：
REPLICAT rep1
setenv (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
--setenv (ORACLE_SID=)
userid ogg@SIMDB,password ogg
REPORT AT 08:59
REPORTCOUNT EVERY 30 MINUTES, RATE
CACHEMGR CACHESIZE 2048MB,CACHEDIRECTORY /u01/app/ogg/dirtmp
REPERROR DEFAULT, ABEND
DISCARDFILE /u01/app/ogg/dirrpt/rep1.dsc, APPEND, MEGABYTES 1024
DISCARDROLLOVER AT 3:00
GETTRUNCATES
ALLOWNOOPUPDATES
APPLYNOOPUPDATES
ASSUMETARGETDEFS
DBOPTIONS DEFERREFCONST
DBOPTIONS SUPPRESSTRIGGERS
DDL INCLUDE MAPPED
DDLOPTIONS REPORT
DDLERROR 942 IGNORE
MAP SCOTT.* TARGET SCOTT.*;
特别说明：复制进程rep1在目标端完成初始化之后再启动.
参数说明:
dbOptions IntegratedParams：设置并行度
EOFDELAYCSECS：控制 replicat 进程检查新数据的频度
Reportrollover：指定何时生成 report 文件
Reperror：控制记录 MAP 发生错误时的信息，这里指定default和abend
Default：设置对所有错误的响应记录
Abend：回滚事务并终止处理异常.ABEND是默认值
CACHEMGR CACHESIZE ：可以来控制OGG进程的内存使用.
列映射转换：
6、目标端初始化数据
19c目标端初始化数据
6.1、创建db_link
--配置tns 服务名TEST_LINK.
[oracle@leo-19c-ogg admin]$ vi tnsnames.ora
添加如下：
ORCL_LINK =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.133.103)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = orcl)
)
)
说明：192.168.133.103为源端IP.
--创建db link.以下oracle_4U为源端system密码.
sys@SIMDB 2022-12-07 23:17:06> create public database link ORCL_LINK connect to system identified by oracle_4U using 'ORCL_LINK';
Database link created.
--初始化完成后可删除此dblink.
DROP PUBLIC DATABASE LINK ORCL_LINK;
--测试db link.
sys@SIMDB 2022-12-07 23:19:22> select * from dual@ORCL_LINK;
D
-
X
sys@SIMDB 2022-12-07 23:21:25> select instance_name from v$instance@ORCL_LINK;
INSTANCE_NAME
----------------
orcl
6.2、查源端scn
sys@ORCL 2022-12-07 23:22:49> SELECT TO_CHAR(MIN(start_scn) ) AS "Please select the minimum SCN" FROM v$transaction
2 UNION ALL
3 SELECT TO_CHAR(current_scn) FROM v$database;
Please select the minimum SCN
----------------------------------------
2716340
6.3、导入数据
--数据泵导入数据，目标端创建表空间.
sys@SIMDB 2022-12-07 23:21:47> create tablespace PROD_SCFOP_TBS datafile '/u01/app/oracle/oradata/SIMDB/prod_scfop_tbs01.dbf' size 2g;
Tablespace created.
--目标端创建dump数据目录.
[oracle@leo-19c-ogg ~]$ mkdir dump
sys@SIMDB 2022-12-07 23:30:05> create directory expdp_dir as '/home/oracle/dump/';
Directory created.
sys@SIMDB 2022-12-07 23:31:09> grant read,write on directory expdp_dir to public;
Grant succeeded.
6.4、执行初始化
使用dblink初始化数据.
[oracle@leo-19c-ogg ~]$ nohup impdp system/oracle_4U@SIMDB directory=EXPDP_DIR version=11.2.0.4 NETWORK_LINK=ORCL_LINK flashback_scn=2716340 exclude=statistics parallel=4 schemas=SCOTT logfile=impdp_scott.log logtime=ALL TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y &
[oracle@leo-19c-ogg ~]$ ll
total 4
drwxr-xr-x 2 oracle oinstall 29 Dec 7 23:35 dump
-rw------- 1 oracle oinstall 629 Dec 7 23:35 nohup.out
drwxr-xr-x. 2 oracle oinstall 71 Dec 2 23:20 scripts
--导入日志
[oracle@leo-19c-ogg ~]$ tail -5000f nohup.out
Import: Release 19.0.0.0.0 - Production on Wed Dec 7 23:35:33 2022
Version 19.16.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
07-DEC-22 23:35:35.719: FLASHBACK automatically enabled to preserve database integrity.
07-DEC-22 23:35:36.169: Starting "SYSTEM"."SYS_IMPORT_SCHEMA_01": system/********@SIMDB directory=EXPDP_DIR version=11.2.0.4 NETWORK_LINK=ORCL_LINK flashback_scn=2716340 exclude=statistics parallel=4 schemas=SCOTT logfile=impdp_scott.log logtime=ALL TRANSFORM=DISABLE_ARCHIVE_LOGGING:Y
07-DEC-22 23:35:37.229: Estimate in progress using BLOCKS method...
07-DEC-22 23:35:38.390: Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
07-DEC-22 23:35:38.443: Total estimation using BLOCKS method: 192 KB
07-DEC-22 23:35:38.877: Processing object type SCHEMA_EXPORT/USER
07-DEC-22 23:35:39.034: Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
07-DEC-22 23:35:39.068: Processing object type SCHEMA_EXPORT/ROLE_GRANT
07-DEC-22 23:35:39.101: Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
07-DEC-22 23:35:39.386: Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
07-DEC-22 23:35:40.571: Processing object type SCHEMA_EXPORT/TABLE/PROCACT_INSTANCE
07-DEC-22 23:35:41.860: Processing object type SCHEMA_EXPORT/TABLE/TABLE
07-DEC-22 23:35:43.269: . . imported "SCOTT"."DEPT" 4 rows
07-DEC-22 23:35:45.259: . . imported "SCOTT"."EMP" 14 rows
07-DEC-22 23:35:45.286: . . imported "SCOTT"."BONUS" 0 rows
07-DEC-22 23:35:45.441: . . imported "SCOTT"."SALGRADE" 5 rows
07-DEC-22 23:35:45.743: Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
07-DEC-22 23:35:46.750: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
07-DEC-22 23:35:47.167: Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
07-DEC-22 23:35:50.421: Job "SYSTEM"."SYS_IMPORT_SCHEMA_01" successfully completed at Wed Dec 7 23:35:50 2022 elapsed 0 00:00:15
注意：dblink+BLOB大字段会特别慢，需先启动源端extu1抓取进程在初始化数据.
6.5、初始化后操作
初始化数据完成后相关配置.
--编译无效对象(非必须)
sys@SIMDB 2022-12-07 23:46:00> @?/rdbms/admin/utlrp.sql
--收集统计信息
sys@SIMDB 2022-12-07 23:46:36> exec dbms_stats.gather_database_stats(degree =>4);
PL/SQL procedure successfully completed.
--启动replicat进程
GGSCI (leo-19c-ogg as ogg@simdb) 17> start REP1, aftercsn 2716340 --2716340为目标端初始化之前源端所查得的scn.
Sending START request to MANAGER ...
REPLICAT REP1 starting
GGSCI (leo-19c-ogg as ogg@simdb) 18> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:01
7、测试
7.1、DML测试
源端模拟插入一条数据.
sys@ORCL 2022-12-07 23:39:29> alter user scott account unlock;
User altered.
sys@ORCL 2022-12-07 23:39:42> alter user scott identified by tiger;
User altered.
sys@ORCL 2022-12-07 23:40:00> conn scott/tiger
Connected.
scott@ORCL 2022-12-07 23:40:07> select table_name from user_tables;
TABLE_NAME
------------------------------
SALGRADE
BONUS
EMP
DEPT
scott@ORCL 2022-12-07 23:40:31> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
scott@ORCL 2022-12-07 23:41:56> insert into dept values (50,'IT','ChongQing');
1 row created.
scott@ORCL 2022-12-07 23:42:09> commit;
Commit complete.
scott@ORCL 2022-12-07 23:42:14> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
50 IT ChongQing
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
--目标端查询
scott@SIMDB 2022-12-07 23:55:32> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
50 IT ChongQing
7.2、DDL测试
源端添加字段，修改字段长度
scott@ORCL 2022-12-07 23:57:19> desc dept
Name Null? Type
----------------------- -------- ----------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
scott@ORCL 2022-12-07 23:57:22> alter table dept add dpeop number(10);
Table altered.
scott@ORCL 2022-12-07 23:59:54> alter table dept modify dpeop number(15);
Table altered.
scott@ORCL 2022-12-08 00:00:22> desc dept
Name Null? Type
----------------------- -------- ----------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
DPEOP NUMBER(15)
--目标端查看
scott@SIMDB 2022-12-08 00:01:31> desc dept
Name Null? Type
----------------------- -------- ----------------
DEPTNO NOT NULL NUMBER(2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
DPEOP NUMBER(15)
7.3、建表测试
--源端
scott@ORCL 2022-12-08 17:57:26> create table testddl (id number(10));
Table created.
scott@ORCL 2022-12-08 17:59:37> begin
2 for i in 1..1000 loop
3 insert into testddl (id) values (i);
4 end loop;
5 end;
6 /
PL/SQL procedure successfully completed.
--目标端
cott@SIMDB 2022-12-08 18:01:49> select table_name from user_tables
TABLE_NAME
---------------
DEPT
EMP
BONUS
SALGRADE
TESTDDL
scott@SIMDB 2022-12-08 18:01:50> select count(*) from testddl;
COUNT(*)
----------
0
--源端
scott@ORCL 2022-12-08 18:00:54> commit;
Commit complete.
--目标端
scott@SIMDB 2022-12-08 18:02:02> select count(*) from testddl;
COUNT(*)
----------
1000
建表语句：
sys@SIMDB 2022-12-08 18:06:01> set long 9999 pages 456 line 456
sys@SIMDB 2022-12-08 18:06:10> select dbms_metadata.get_ddl('TABLE','TESTDDL','SCOTT') from dual;
DBMS_METADATA.GET_DDL('TABLE','TESTDDL','SCOTT')
--------------------------------------------------------------------------------
CREATE TABLE "SCOTT"."TESTDDL"
( "ID" NUMBER(10,0)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MIN
EXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT)
TABLESPACE "USERS"
参考网址：
https://mp.weixin.qq.com/s/UeyOjuafGmtcPvgJEWHapw
标签：11g,
12,
07,
leo,
oracle,
ogg,
u01
From： https://blog.51cto.com/u_12991611/5923461