在本文中，我们将给您介绍关于oracle函数ROW_NUMBER()的详细内容，并且为您解答Oracle函数索引的相关问题，此外，我们还将为您提供关于db2ROW_NUMBER()用法、flinkSQ
在本文中，我们将给您介绍关于oracle 函数 ROW_NUMBER ()的详细内容，并且为您解答Oracle 函数索引的相关问题，此外，我们还将为您提供关于db2 ROW_NUMBER() 用法、flinkSQL No match found for function signature ROW_NUMBER()、Hive中分组取前N个值的实现-row_number()、oracle ogg 单实例双向复制搭建（oracle-oracle）--Oracle GoldenGate的知识。
本文目录一览：- oracle 函数 ROW_NUMBER ()（Oracle 函数索引）
- db2 ROW_NUMBER() 用法
- flinkSQL No match found for function signature ROW_NUMBER()
- Hive中分组取前N个值的实现-row_number()
- oracle ogg 单实例双向复制搭建（oracle-oracle）--Oracle GoldenGate
oracle 函数 ROW_NUMBER ()（Oracle 函数索引）
【语法】ROW_NUMBER () OVER (PARTITION BY COL1 ORDER BY COL2)
【功能】表示根据 COL1 分组，在分组内部根据 COL2 排序，而这个值就表示每组内部排序后的顺序编号（组内连续的唯一的）
row_number () 返回的主要是 “行” 的信息，并没有排名
【参数】
【说明】Oracle 分析函数
主要功能：用于取前几名，或者最后几名等
【示例】
表内容如下：
name | seqno | description
A | 1 | test
A | 2 | test
A | 3 | test
A | 4 | test
B | 1 | test
B | 2 | test
B | 3 | test
B | 4 | test
C | 1 | test
C | 2 | test
C | 3 | test
C | 4 | test
我想有一个 sql 语句，搜索的结果是
A | 1 | test
A | 2 | test
B | 1 | test
B | 2 | test
C | 1 | test
C | 2 | test
实现:
select name,seqno,description
from(select name,seqno,description,row_number() over (partition by name order by seqno) id
from table_name) where id<=3;
db2 ROW_NUMBER() 用法
其它资料：http://jingyan.baidu.com/article/9989c74604a644f648ecfef3.html
这个也不错
----------------------------------------------------------------------------------------------
db2的row_number()over() 等于oracle的row_number()over()
也就是顺序排列， 1，2，3，4，5，6，7
rank()over() 是排名不连续 也就是 数据有可能是 ： 1，1，3，3，5，6，7 这样的。
下面是一个例子：
测试表/数据
CREATE
TABLE
test_course (
student_name
VARCHAR
(10),
-- 学生
course_name
VARCHAR
(10),
-- 课程
grade
INT
-- 成绩
);
INSERT
INTO
test_course
VALUES
(
''甲''
,
''语文''
, 95);
INSERT
INTO
test_course
VALUES
(
''乙''
,
''语文''
, 85);
INSERT
INTO
test_course
VALUES
(
''丙''
,
''语文''
, 75);
INSERT
INTO
test_course
VALUES
(
''丁''
,
''语文''
, 65);
INSERT
INTO
test_course
VALUES
(
''戊''
,
''语文''
, 55);
INSERT
INTO
test_course
VALUES
(
''己''
,
''语文''
, 50);
INSERT
INTO
test_course
VALUES
(
''庚''
,
''语文''
, 60);
INSERT
INTO
test_course
VALUES
(
''辛''
,
''语文''
, 70);
INSERT
INTO
test_course
VALUES
(
''壬''
,
''语文''
, 80);
INSERT
INTO
test_course
VALUES
(
''奎''
,
''语文''
, 90);
INSERT
INTO
test_course
VALUES
(
''甲''
,
''数学''
, 90);
INSERT
INTO
test_course
VALUES
(
''乙''
,
''数学''
, 80);
INSERT
INTO
test_course
VALUES
(
''丙''
,
''数学''
, 70);
INSERT
INTO
test_course
VALUES
(
''丁''
,
''数学''
, 60);
INSERT
INTO
test_course
VALUES
(
''戊''
,
''数学''
, 50);
INSERT
INTO
test_course
VALUES
(
''己''
,
''数学''
, 50);
INSERT
INTO
test_course
VALUES
(
''庚''
,
''数学''
, 60);
INSERT
INTO
test_course
VALUES
(
''辛''
,
''数学''
, 70);
INSERT
INTO
test_course
VALUES
(
''壬''
,
''数学''
, 85);
INSERT
INTO
test_course
VALUES
(
''奎''
,
''数学''
, 95);
ROW_NUMBER 顺序编号
SQL>
SELECT
2 ROW_NUMBER() OVER(
ORDER
BY
SUM
(grade)
DESC
)
AS
NO
,
3 student_name,
4
SUM
(grade)
AS
AllGrade
5
FROM
6 test_course
7
GROUP
BY
8 student_name
9
ORDER
BY
10
SUM
(grade)
DESC
11 ;
NO
STUDENT_NA ALLGRADE
---------- ---------- ----------
1 奎 185
2 甲 185
3 乙 165
4 壬 165
5 丙 145
6 辛 140
7 丁 125
8 庚 120
9 戊 105
10 己 100
已选择10行。
RANK 排名不连续
SELECT
RANK() OVER(
ORDER
BY
SUM
(grade)
DESC
)
AS
NO
,
student_name,
SUM
(grade)
AS
AllGrade
FROM
test_course
GROUP
BY
student_name
ORDER
BY
SUM
(grade)
DESC
NO
STUDENT_NA ALLGRADE
---------- ---------- ----------
1 奎 185
1 甲 185
3 乙 165
3 壬 165
5 丙 145
6 辛 140
7 丁 125
8 庚 120
9 戊 105
10 己 100
已选择10行。
还有一个
DENSE_RANK 排名连续
SELECT
DENSE_RANK() OVER(
ORDER
BY
SUM
(grade)
DESC
)
AS
NO
,
student_name,
SUM
(grade)
AS
AllGrade
FROM
test_course
GROUP
BY
student_name
ORDER
BY
SUM
(grade)
DESC
NO
STUDENT_NA ALLGRADE
---------- ---------- ----------
1 奎 185
1 甲 185
2 乙 165
2 壬 165
3 丙 145
4 辛 140
5 丁 125
6 庚 120
7 戊 105
8 己 100
已选择10行。
------------------------------------------------------------------
按组排序，获取组的首记录
select * from (
SELECT
ROW_NUMBER() OVER(partition by test_course.student_name order by test_course.student_name DESC) AS NO,
test_course.*
FROM
test_course
ORDER BY
student_name DESC
) a
--where a.no=1
;
flinkSQL No match found for function signature ROW_NUMBER()
刚学 flinkSQL，看到这个报错以为是 不支持这个函数，百度 Google 也找不到原因，问了圈里的人才知道原因
StreamExecutionEnvironment bsEnv = StreamExecutionEnvironment.getExecutionEnvironment(); EnvironmentSettings bsSettings = EnvironmentSettings.newInstance().useBlinkPlanner().inStreamingMode().build(); StreamTableEnvironment bsTableEnv = StreamTableEnvironment.create(bsEnv, bsSettings);
关键在 EnvironmentSettings 的创建那里， 要使用 blinkPlanner，一开始是用官网的第一个创建方式 userOldPlanner
更改下 环境的创建方式即可。
Hive中分组取前N个值的实现-row_number()
背景
假设有一个学生各门课的成绩的表单，应用hive取出每科成绩前100名的学生成绩。
这个就是典型在分组取Top N的需求。
解决思路
对于取出每科成绩前100名的学生成绩，针对学生成绩表，根据学科，成绩做order by排序,然后对排序后的成绩，执行自定义函数row_number(),必须带一个或者多个列参数，如ROW_NUMBER(col1, ....)，它的作用是按指定的列进行分组生成行序列。在ROW_NUMBER(a,b) 时，若两条记录的a，b列相同，则行序列+1，否则重新计数。
只要返回row_number()返回值小于100的的成绩记录，就可以返回每个单科成绩前一百的学生
create table score_table (
subject string,
student string,
score int
)
partitioned by (date string);
如果要查询2012年每科成绩前100的学生成绩，sql如下
create temporary function row_number as ''com.blue.hive.udf.RowNumber'';
select subject,score,student from
(select subject,score,student from score where dt=''2012'' order by subject,socre desc) order_score
where row_number(subject) <= 100;
com.blue.hive.udf.RowNumber是自定义函数，函数的作用是按指定的列进行分组生成行序列。这里根据每个科目的所有成绩，生成序列，序列值从1开始自增。
执行row_number函数，返回值如下
科目 成绩 学生 row_number
物理 100 张一 1
物理 90 张二 2
物理 80 张三 3
.....
数学 100 李一 1
数学 90 李二 2
数学 80 李三 3
....
row_number的源码
函数row_number(),必须带一个或者多个列参数，如ROW_NUMBER(col1, ....)，它的作用是按指定的列进行分组生成行序列。在ROW_NUMBER(a,b) 时，若两条记录的a，b列相同，则行序列+1，否则重新计数。
package com.blue.hive.udf;
import org.apache.hadoop.hive.ql.exec.UDF;
public class RowNumber extends UDF {
private static int MAX_VALUE = 50;
private static String comparedColumn[] = new String[MAX_VALUE];
private static int rowNum = 1;
public int evaluate(Object... args) {
String columnValue[] = new String[args.length];
for (int i = 0; i < args.length; i++) 『
columnValue[i] = args[i].toString();
}
if (rowNum == 1) {
for (int i = 0; i < columnValue.length; i++)
comparedColumn[i] = columnValue[i];
}
for (int i = 0; i < columnValue.length; i++) {
if (!comparedColumn[i].equals(columnValue[i])) {
for (int j = 0; j < columnValue.length; j++) {
comparedColumn[j] = columnValue[j];
}
rowNum = 1;
return rowNum++;
}
}
return rowNum++;
}
}编译后，打包成一个jar包，如/usr/local/hive/udf/blueudf.jar
然后在hive shell下使用，如下:
add jar /usr/local/hive/udf/blueudf.jar;
create temporary function row_number as ''com.blue.hive.udf.RowNumber'';
select subject,score,student from
(select subject,score,student from score where dt=''2012'' order by subject,socre desc) order_score
where row_number(subject) <= 100;
同样，这个函数可以用作去重操作。
可以替代大批量数据的DISTINCT
通过执行如：
select * from(
select type,value,row_number() as rn
from log_table
distribute by type,value
sort by type,value
)
where rn = 1;
===============注意！============================
但是使用row_number()函数需要注意一点，必须使用sort by。
测试的时候必须使用order by。
row_number()函数会假设数据有序的基础上进行的。
oracle ogg 单实例双向复制搭建（oracle-oracle）--Oracle GoldenGate
oracle ogg 单实例双向复制搭建（oracle-oracle）--Oracle GoldenGate
--继昨天的测试，这一篇实施单实例双向复制（完全重新搭建）
--环境不变
db1,db2(单实例)
10.1*.1*
orcl,ogg
centos 6.5,centos 6.5
11.2.0.4,11.2.0.4
1 检查归档，日志模式（orcl,ogg）
SCOTT@ orcl >conn / as sysdba
Connected.
SYS@ orcl >select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
NAME OPEN_MODE FOR SUPPLEME
--------- -------------------- --- --------
ORCL READ WRITE YES YES
SYS@ orcl >archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 21
Next log sequence to archive 23
Current log sequence 23
SYS@ orcl >alter system switch logfile;
System altered.
1 row selected.
SCOTT@ ogg >conn / as sysdba
Connected.
SYS@ ogg >select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MIN from v$database;
NAME OPEN_MODE FOR SUPPLEME
--------- -------------------- --- --------
OGG READ WRITE YES YES
SYS@ ogg >archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence 74
Next log sequence to archive 76
Current log sequence 76
SYS@ ogg >alter system switch logfile;
System altered.2 创建OGG表空间和OGG用户---两个服务器都要做之前已经执行过 (orcl,ogg)
---运行 OGG 支持 DDL 脚本
如果要让 OGG 支持 DDL 操作，还需要额外运行几个脚本，这些脚本是 OGG 带的而不是 ORACLE 带的，源端与目标端都需要
grant CONNECT, RESOURCE 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 insert any table to ogg;
grant update any table to ogg;
grant delete any table to ogg;
GRANT EXECUTE ON UTL_FILE TO ogg;
GRANT CREATE TABLE,CREATE SEQUENCE TO ogg;
grant create any table to ogg;
grant create any view to ogg;
grant create any procedure to ogg;
grant create any sequence to ogg;
grant create any index to ogg;
grant create any trigger to ogg;
grant create any view to ogg;
[oracle@ogg ~]$ cd /u01/app/ogg
[oracle@ogg ogg]$ sqlplus / as sysdba
---SYS@ orcl >@/u01/app/ogg/marker_setup.sql
---SYS@ orcl >@/u01/app/ogg/ddl_setup.sql
---SYS@ orcl >@/u01/app/ogg/role_setup.sql
---SYS@ orcl >@/u01/app/ogg/ddl_enable.sql如果安装过程中报错
SYS@ orcl >@/u01/app/ogg/ddl_setup.sql
Line/pos Error
---------------------------------------- -----------------------------------------------------------------
126/9 PL/SQL: SQL Statement ignored
128/23 PL/SQL: ORA-00942: table or view does not exist
133/21 PL/SQL: ORA-02289: sequence does not exist
133/5 PL/SQL: SQL Statement ignored
657/14 PLS-00905: object OGG.DDLAUX is invalid
657/5 PL/SQL: Statement ignored
919/25 PL/SQL: ORA-00942: table or view does not exist
919/4 PL/SQL: SQL Statement ignored###卸载ogg，并使支持DDL功能失效
---SYS@ orcl >@/u01/app/ogg/ddl_disable.sql
SYS@ orcl >@/u01/app/ogg/ddl_disable.sql
Trigger altered.
SYS@ orcl >@/u01/app/ogg/ddl_remove.sql
DDL replication removal script.
WARNING: this script removes all DDL replication objects and data.
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.
Enter Oracle GoldenGate schema name:scott
Working, please wait ...
Spooling to file ddl_remove_spool.txt
Script complete.
SYS@ orcl >@/u01/app/ogg/marker_remove.sql
Marker removal script.
WARNING: this script removes all marker objects and data.
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.
Enter Oracle GoldenGate schema name:scott
PL/SQL procedure successfully completed.
Sequence dropped.
Table dropped.
Script complete.--检查相应的权限，在ogg脚本下面登录/u01/app/ogg
SQL> @/u01/app/ogg/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
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.
SQL> @/u01/app/ogg/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
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 OGG 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 OGG
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/ogg/ogg/trace/ggs_ddl_trace.log
Analyzing installation status...
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replication software components
Script complete.
SQL> @/u01/app/ogg/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
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> @/u01/app/ogg/ddl_enable.sql
Trigger altered.3 ogg配置
1 创建 OGG 的管理目录(orcl,ogg)
GGSCI (DSI) 1> create subdirs
Creating subdirectories under current directory /u01/app/ogg
Parameter files /u01/app/ogg/dirprm: already exists
Report files /u01/app/ogg/dirrpt: created
Checkpoint files /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
Stdout files /u01/app/ogg/dirout: created2 添加表级 TRANDATA(orcl,ogg)
GGSCI (DSI) 2> DBLOGIN USERID ogg, PASSWORD ogg
Successfully logged into database.
GGSCI (DSI) 3> add trandata scott.emp_ogg
Logging of supplemental redo log data is already enabled for table SCOTT.EMP_OGG.
GGSCI (DSI) 4> add trandata scott.dept_ogg
Logging of supplemental redo log data is already enabled for table SCOTT.DEPT_OGG.
GGSCI (DSI) 5> add trandata scott.dept
Logging of supplemental redo log data is already enabled for table SCOTT.DEPT.
GGSCI (DSI) 6> add trandata scott.emp;
ERROR: No viable tables matched specification.
GGSCI (DSI) 7> INFO TRANDATA scott.*
Logging of supplemental redo log data is disabled for table SCOTT.BONUS.
Logging of supplemental redo log data is enabled for table SCOTT.DEPT.
Columns supplementally logged for table SCOTT.DEPT: DEPTNO.
Logging of supplemental redo log data is enabled for table SCOTT.DEPT_OGG.
Columns supplementally logged for table SCOTT.DEPT_OGG: DEPTNO.
Logging of supplemental redo log data is enabled for table SCOTT.EMP.
Columns supplementally logged for table SCOTT.EMP: EMPNO.
Logging of supplemental redo log data is enabled for table SCOTT.EMP_OGG.
Columns supplementally logged for table SCOTT.EMP_OGG: EMPNO.
Logging of supplemental redo log data is disabled for table SCOTT.SALGRADE.3 数据初始化（orcl）
SYS@ orcl >create directory dump_file_dir as ''/u01/app/oracle/dump'';
Directory created.
[oracle@DSI oracle]$ mkdir -p /u01/app/oracle/dump
[oracle@DSI oracle]$ expdp scott/*@*/orcl schemas=scott directory=dump_file_dir dumpfile=scott_schemas_20190620.dmp logfile=scott_schemas_20190620.log
[oracle@ogg ogg]$ export ORACLE_SID=ogg
[oracle@ogg ogg]$ mkdir -p /u01/app/oracle/dump
[oracle@DSI dump]$ scp scott_schemas_20190620.dmp oracle@*:/u01/app/oracle/dump/.
[oracle@ogg dump]$ impdp scott/*@*/ogg directory=dump_file_dir dumpfile=scott_schemas_20190620.dmp logfile=scott_schemas_20190620.log1 配置mgr主进程组（orcl,ogg）
> edit params mgr
port 7839
DYNAMICPORTLIST 7840-7850
AUTOSTART EXTRACT *
AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 452 配置Extract进程组(orcl,ogg)
> add extract ext1, tranlog, begin now
> add EXTTRAIL ./dirdat/r1, extract ext1,megabytes 100
> edit param ext1
EXTRACT ext1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg,password ogg
REPORTCOUNT EVERY 1 MINUTES, RATE
numfiles 5000
DISCARDFILE ./dirrpt/ext1.dsc,APPEND,MEGABYTES 1024
DISCARDROLLOVER AT 3:00
exttrail ./dirdat/r1,megabytes 100
dynamicresolution
TRANLOGOPTIONS DISABLESUPPLOGCHECK
GetTruncates
TranLogOptions ExcludeUser ogg
--DDL Include All
DDL &
INCLUDE MAPPED OBJTYPE ''table'' &
INCLUDE MAPPED OBJTYPE ''index'' &
EXCLUDE OPTYPE COMMENT
DDLOptions AddTranData Retryop Retrydelay 10 Maxretries 10
TABLE scott.EMP_OGG;
TABLE scott.DEPT_OGG;
TABLE scott.DEPT;3 配置pump进程组(orcl,ogg)
> edit param pump1
extract pump1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
userid ogg,password ogg
dynamicresolution
passthru
rmthost *, mgrport 7839, compress
rmttrail ./dirdat/t1
numfiles 5000
TABLE scott.EMP_OGG;
TABLE scott.DEPT_OGG;
TABLE scott.DEPT;
> add extract pump1 ,exttrailsource ./dirdat/r1,begin now
> add rmttrail ./dirdat/t1,extract pump1, MEGABYTES 54 添加检查表(orcl,ogg)
> edit params ./GLOBALS
GGSchema ogg
CHECKPOINTTABLE ogg.ggschkpt
> exit
> dblogin userid ogg,password ogg
> ADD CHECKPOINTTABLE5 配置replicat进程组(orcl,ogg)
> edit param rep1
REPLICAT rep1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg,PASSWORD ogg
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, ABEND
numfiles 5000
assumetargetdefs
DISCARDFILE ./dirrpt/rep1.dsc, APPEND, MEGABYTES 1000
DISCARDROLLOVER AT 3:00
ALLOWNOOPUPDATES
DBOPTIONS DEFERREFCONST
dynamicresolution
assumetargetdefs
reperror default,discard
MAP scott.emp_ogg, TARGET scott.emp_ogg;
MAP scott.dept_ogg, TARGET scott.dept_ogg;
MAP scott.dept, TARGET scott.dept;
> add replicat rep1,exttrail ./dirdat/t1,checkpointtable ogg.ggschkpt
> start rep1--orcl(先测试单向)-在orcl端启动start ext1， start pump1
GGSCI (DSI) 5> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:19:12 00:00:09
EXTRACT RUNNING PUMP1 00:00:00 00:10:20--ogg --在ogg端启动start rep1
GGSCI (ogg) 6> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REP1 00:00:00 00:00:01--单向测试
SYS@ orcl >conn scott/tiger
Connected.
SCOTT@ orcl >update emp_ogg set ename=''hq_orcl_1'' where empno=7934;
1 row updated.
SCOTT@ orcl >commit;
Commit complete.
GGSCI (DSI) 6> stats pump1
Sending STATS request to EXTRACT PUMP1 ...
Start of Statistics at 2019-06-20 15:40:11.
Output to ./dirdat/t1:
Extracting from SCOTT.EMP_OGG to SCOTT.EMP_OGG:
*** Total statistics since 2019-06-20 15:39:37 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2019-06-20 15:39:37 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2019-06-20 15:39:37 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2019-06-20 15:39:37 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.
GGSCI (ogg) 7> stats rep1
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2019-06-20 15:40:21.
Replicating from SCOTT.EMP_OGG to SCOTT.EMP_OGG:
*** Total statistics since 2019-06-20 15:39:42 ***
No database operations have been performed.
*** Daily statistics since 2019-06-20 15:39:42 ***
No database operations have been performed.
*** Hourly statistics since 2019-06-20 15:39:42 ***
No database operations have been performed.
*** Latest statistics since 2019-06-20 15:39:42 ***
No database operations have been performed.
End of Statistics.
GGSCI (DSI) 7> info pump1,detail
EXTRACT PUMP1 Last Started 2019-06-20 15:37 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:03 ago)
Log Read Checkpoint File ./dirdat/r1000000
2019-06-20 15:39:35.000000 RBA 1153
Target Extract Trails:
Remote Trail Name Seqno RBA Max MB
./dirdat/t1 0 1183 5
Extract Source Begin End
./dirdat/r1000000 2019-06-20 15:27 2019-06-20 15:39
./dirdat/r1000000 * Initialized * 2019-06-20 15:27
Current directory /u01/app/ogg
Report file /u01/app/ogg/dirrpt/PUMP1.rpt
Parameter file /u01/app/ogg/dirprm/pump1.prm
Checkpoint file /u01/app/ogg/dirchk/PUMP1.cpe
Process file /u01/app/ogg/dirpcs/PUMP1.pce
Stdout file /u01/app/ogg/dirout/PUMP1.out
Error log /u01/app/ogg/ggserr.log
GGSCI (ogg) 8> info rep1,detail
REPLICAT REP1 Last Started 2019-06-20 15:38 Status RUNNING
Checkpoint Lag 00:00:00 (updated 00:00:04 ago)
Log Read Checkpoint File ./dirdat/t1000000
2019-06-20 15:39:38.217835 RBA 1183
Extract Source Begin End
./dirdat/t1000000 * Initialized * 2019-06-20 15:39
./dirdat/t1000000 * Initialized * First Record
Current directory /u01/app/ogg
Report file /u01/app/ogg/dirrpt/REP1.rpt
Parameter file /u01/app/ogg/dirprm/rep1.prm
Checkpoint file /u01/app/ogg/dirchk/REP1.cpr
Checkpoint table ogg.ggschkpt
Process file /u01/app/ogg/dirpcs/REP1.pcr
Stdout file /u01/app/ogg/dirout/REP1.out
Error log /u01/app/ogg/ggserr.log--报错了（No database operations have been performed.）
从日志中看到ext1,pump1是正常捕获到的，问题出现在ogg端的rep1进程上
于是修改配置文件--上面rep1的配置中间的很多参数，看上去很复杂，先不加那么多，没参照官方文档，照别人的先加了（报错），不行就先取消掉，后面在研究
GGSCI (ogg) 32> view param rep1
REPLICAT rep1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg, PASSWORD ogg
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/rep1.dsc, PURGE
MAP scott.emp_ogg, TARGET scott.emp_ogg;
MAP scott.dept_ogg, TARGET scott.dept_ogg;GGSCI (ogg) 14> stats rep1
Sending STATS request to REPLICAT REP1 ...
No active replication maps.
GGSCI (ogg) 15> view report rep1
2019-06-20 15:49:56 INFO OGG-03035 Operating system character set identified as UTF-8. Locale: en_US, LC_ALL:.
REPLICAT rep1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
Set environment variable (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg,PASSWORD ***
DISCARDFILE ./dirrpt/rep1.dsc, APPEND, MEGABYTES 1000
MAP scott.emp_ogg, TARGET scott.emp_ogg;
MAP scott.dept_ogg, TARGET scott.dept_ogg;
MAP scott.dept, TARGET scott.dept;
2019-06-20 15:49:56 INFO OGG-01815 Virtual Memory Facilities for: COM
anon alloc: mmap(MAP_ANON) anon free: munmap
file alloc: mmap(MAP_SHARED) file free: munmap
target directories:
/u01/app/ogg/dirtmp.
GGSCI (ogg) 17> stop rep1
GGSCI (ogg) 18> edit param rep1
REPLICAT rep1
SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)
USERID ogg, PASSWORD ogg
HANDLECOLLISIONS
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/rep1.dsc, PURGE
MAP scott.emp_ogg, TARGET scott.emp_ogg;
MAP scott.dept_ogg, TARGET scott.dept_ogg;
GGSCI (ogg) 19> delete rep1
GGSCI (ogg) 20> add REPLICAT rep1,exttrail ./dirdat/t1,checkpointtable ogg.ggschkpt
GGSCI (ogg) 21> start rep1再次查看，进程状态正常， 数据也同步
GGSCI (ogg) 23> stats rep1
Sending STATS request to REPLICAT REP1 ...
Start of Statistics at 2019-06-20 15:56:01.
Replicating from SCOTT.EMP_OGG to SCOTT.EMP_OGG:
*** Total statistics since 2019-06-20 15:55:52 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Daily statistics since 2019-06-20 15:55:52 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Hourly statistics since 2019-06-20 15:55:52 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
*** Latest statistics since 2019-06-20 15:55:52 ***
Total inserts 0.00
Total updates 1.00
Total deletes 0.00
Total discards 0.00
Total operations 1.00
End of Statistics.开启ogg端的ext1，pump1进程，orcl的rep1进程
GGSCI (DSI) 11> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:06
EXTRACT RUNNING PUMP1 00:00:00 00:00:09
REPLICAT RUNNING REP1 00:00:00 00:00:04GGSCI (ogg) 30> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING EXT1 00:00:00 00:00:09
EXTRACT RUNNING PUMP1 00:00:00 00:00:02
REPLICAT RUNNING REP1 00:00:00 00:00:05ogg端更新测试
SCOTT@ ogg >update emp_ogg set ename=''hq_ogg_1'' where empno=7934;
1 row updated.
SCOTT@ ogg >commit;
Commit complete.
SCOTT@ ogg >select * from emp_ogg where empno=7934;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 hq_ogg_1 CLERK 7782 23-JAN-82 1300 10
1 row selected.
SCOTT@ orcl >select * from emp_ogg where empno=7934;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------
7934 hq_ogg_1 CLERK 7782 23-JAN-82 1300 10
1 row selected.单实例双向简单同步测试完成。
关于oracle 函数 ROW_NUMBER ()和Oracle 函数索引的介绍现已完结，谢谢您的耐心阅读，如果想了解更多关于db2 ROW_NUMBER() 用法、flinkSQL No match found for function signature ROW_NUMBER()、Hive中分组取前N个值的实现-row_number()、oracle ogg 单实例双向复制搭建（oracle-oracle）--Oracle GoldenGate的相关知识，请在本站寻找。
本文标签：
