Category Archives: Oracle

异构数据库初始化大字段处理

Posted on by

一、source端

SOURCEISTABLE
SOURCEDB oracle
RMTHOST 127.0.0.1, MGRPORT 7820
RMTFILE D:\ogg\oracle\dirdat\i1
table dbo.t_v;
table dbo.t_t;

二、target端

SPECIALRUN
END RUNTIME
SETENV (NLS_LANG =AMERICAN_AMERICA.ZHS16GBK)  
userid ogg,password xifenfei   
EXTFILE D:\ogg\oracle\dirdat\i1
SOURCEDEFS D:\ogg\oracle\dirdef\t_v.def
discardfile D:\ogg\oracle\dirtmp\repsz.dsc,append,megabytes 100 
MAP "dbo.t_v", target mssql.t_v;
MAP "dbo.t_t", target mssql.t_t
, colmap ( id = id , text_t = @binary(t_text));

三、执行
extract paramfile dirprm\einit1.prm reportfile dirrpt\einit1.rpt
replicat paramfile dirprm\rinit1.prm reportfile dirrpt\rinit1.rpt

四、试验说明
如果大字段数据库过长，如这里的text字段类型过长是，在l1中有数据，但是target端的数据库中无对应数据，初步分析原因可能有：
1、defgen定义文件中确定文件长度导致
2、target端编码和clob列相关限制有关（可能性不大）
3、goldengate软件内在机制导致，还需要彻底的阅读官网文档
4、现在好像到target端的最终长度为4000byte（根据数据库编码不同区分汉字、字母、数字），在本测试中，如果source端使用ntext，target只能接收1000个汉字，如果是text类型，可以接受1333个汉字。
5、试验环境说明：sql server 2005 to oracle 11g，source端表（id int primary key auto，t_text text)，target端（id number primary key,text_t clob)

—出现异常原因已经找到(2011年2月22日23:27:51)
When the size of a large object exceeds 4K, Oracle GoldenGate stores the data in segments within the Oracle GoldenGate trail. The first 4K is stored in the base segment, and the rest is stored in a series of 2K segments. Oracle GoldenGate does not support the filtering, column mapping, or manipulation of large objects of this size. Full Oracle GoldenGate functionality can be used for objects that are 4K or smaller.
大致意思就是当内容超过4k时，就不能使用过滤、列映射、或者操作大字段，当内容等于或者小于4k时，所有的goldengate函数都可以使用。上面问题是当t_text内容大于4k时，不能进行大对象操作。
疑问：那当有数据超过4k该怎么处理

—试验测试（2011年2月23日23:34:13）
初始化后，同步数据库过程中，异构数据库不同列类型（如：mssql中的text到oracle中的clob），也必须使用sourcedefs，使用适当的函数对列类型进行转换（如：colmap ( id = id , t_lob = @binary(t_lob))），对于大于4k的数据，还是和初始化一样不能被处理。该问题等待寻找解决方案

goldengate同步sql server to oracle

Posted on by

准备工作，在sql server机器上建立odbc连接
一、初始化加载数据
1、source端
1)添加extract进程

ADD EXTRACT einito, SOURCEISTABLE
edit param einito
--以下添加到einito.prm文件中
EXTRACT einito
SOURCEDB mssql_test
RMTHOST 127.0.0.1, MGRPORT 7815
RMTTASK REPLICAT, GROUP rinitm
TABLE dbo.t1;

2)生成defgen文件

edit params defgen
---以下为defgen.prm中内容
defsfile F:\ogg\mssql\dirdef\t1.def
sourcedb mssql_test
table dbo.t1;

--退出ggsci(ogg安装目录dos下)
exit
defgen paramfile F:\ogg\mssql\dirprm\defgen.prm

2、target端
1)replicat 进程

ADD REPLICAT rinitm, SPECIALRUN
edit params rinitm
--以下内容在rinitm.prm文件中
replicat rinitm
sourcedefs F:\ogg\oracle\dirdef\t1.def
SETENV (NLS_LANG =AMERICAN_AMERICA.ZHS16GBK)
USERID chf, PASSWORD xifenfei
DISCARDFILE F:\ogg\oracle\dirrpt\RINItm.dsc, append
MAP "dbo.t1", TARGET CHF.T1_1;

二、数据同步
1、source端
1)添加附件日志

dblogin sourcedb mssql_test
add trandata dbo.t1

2)摄取进程（extract）

add extract extm,tranlog,begin now
ADD EXTTRAIL F:\ogg\mssql\dirdat\ms, EXTRACT EXTM
edit param extm
--以下为extm.prm内容
extract extm
SOURCEDB mssql_test
exttrail F:\ogg\mssql\dirdat\ms
dynamicresolution
gettruncates
tranlogoptions managesecondarytruncationpoint
TABLE dbo.t1;

3)传递进程（data pump extract ）

ADD EXTRACT pump1, EXTTRAILSOURCE F:\ogg\mssql\dirdat\ms, BEGIN now
add rmttrail F:\ogg\oracle\dirdat\or extract pump1
edit params pump1
--以下为pump1.prm内容
extract pump1
SOURCEDB mssql_test  --需要，不然不能获得数据
rmthost 127.0.0.1, mgrport 7815
rmttrail F:\ogg\oracle\dirdat\or
PASSTHRU
gettruncates
TABLE dbo.t1;

2、target端
1)设置检查点表

edit params ./GLOBALS
--下面一句为GLOBALS文件中内容
CHECKPOINTTABLE ogg.chkpoint

dblogin userid ogg,password xifenfei 
ADD CHECKPOINTTABLE ogg.chkpoint

2)replicat进程

add replicat repl exttrail F:\ogg\oracle\dirdat\or,begin now,checkpointtable ogg.chkpoint 
edit  params repl
--以下为repl.prm中内容
replicat repl          
SETENV (NLS_LANG =AMERICAN_AMERICA.ZHS16GBK)                     
userid ogg,password xifenfei                    
sourcedefs F:\ogg\oracle\dirdef\t1.def                         
reperror default,discard                      
discardfile F:\ogg\oracle\dirtmp\repsz.dsc,append,megabytes 100   
gettruncates                                                      
MAP "dbo.t1", TARGET CHF.T1_1;

note：
因为defgen中的表名为小写，所以在replicat相关进程中，map表需要使用双引号小写

oracle修改表增加列删除列修改列

Posted on by

1.增加列

ALTER TABLE table_name ADD( column datatype [DEFAULT EXPR][,column datatype…]);

例如:

SQL>ALTER TABLE emp01 ADD eno NUMBER(4);

2.修改列定义

例如:

SQL>ALTER TABLE emp01 MODIFY job VARCHAR2(15)

2 DEFAULT ‘CLERK’

3.删除列

例如:

SQL> ALTER TABLE emp01 DROP COLUMN dno;

4.修改列名

例如:

SQL>ALTER TABLE emp01 RENAME COLUMN eno TO empno;

5.修改表名

例如:

SQL>RENAME emp01 TO employee;

6.增加注释

例如:

SQL>COMMENT ON TABLE employee IS ‘存放雇员信息’;

goldengate 异常处理

Posted on by

异常处理一（异常表通用型）
新建异常处理表

create table ogg.exception_log
    ( replicat_name varchar2(10),
      table_name varchar2(100),
      errno number,
      dberrmsg varchar2(4000),
      optype varchar2(20),
      errtype varchar2(20),
      logrba number,
      logposition number,
     committimestamp timestamp,
     primary key(logrba,logposition,committimestamp)
);

REPLICAT添加异常处理

REPERROR (DEFAULT, EXCEPTION)
REPERROR (DEFAULT2,discard)---abend根据需求
map chf.a_t_1, target chf.a_t_1;
map chf.a_t_1, target ogg.exception_log,
EXCEPTIONSONLY,
INSERTALLRECORDS,
COLMAP (   replicat_name = "repl"
, table_name = @GETENV ("GGHEADER", "TABLENAME")
, errno = @GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = @GETENV ("LASTERR", "DBERRMSG")
, optype = @GETENV ("LASTERR", "OPTYPE")
, errtype = @GETENV ("LASTERR", "ERRTYPE")
, logrba = @GETENV ("GGHEADER", "LOGRBA")
, logposition = @GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP"));
--实例中只处理chf.a_t_1表

异常处理二(异常表需要定制)
新建表(正常表和异常表)

--正常表
create table fei_1_1(id number , name varchar2(1000));
--异常表
create table ogg.exception_fei_1
    ( id number,
      name varchar2(1000),
      table_name varchar2(100),
      errno number,
      dberrmsg varchar2(4000),
      optype varchar2(20),
      errtype varchar2(20),
      logrba number,
      logposition number,
     committimestamp timestamp,
     primary key(logrba,logposition,committimestamp)
);

异常处理程序

map chf.fei_1, target chf.fei_1_1;
map chf.fei_1, target ogg.exception_fei_1,
EXCEPTIONSONLY,
INSERTALLRECORDS,
COLMAP ( USEDEFAULTS
, table_name = @GETENV ("GGHEADER", "TABLENAME")
, errno = @GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = @GETENV ("LASTERR", "DBERRMSG")
, optype = @GETENV ("LASTERR", "OPTYPE")
, errtype = @GETENV ("LASTERR", "ERRTYPE")
, logrba = @GETENV ("GGHEADER", "LOGRBA")
, logposition = @GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP"));

异常处理三（通配符MAPEXCEPTION）
新建异常表

create table ogg.exception_fei_all
    ( replicat_name varchar2(10),
      table_name varchar2(100),
      errno number,
      dberrmsg varchar2(4000),
      optype varchar2(20),
      errtype varchar2(20),
      logrba number,
      logposition number,
     committimestamp timestamp,
     primary key(logrba,logposition,committimestamp)
);

异常处理程序

MAP chf.fei_*, TARGET chf.*,
MAPEXCEPTION (TARGET ogg.exception_fei_all,
COLMAP (   replicat_name = "repl"
, table_name = @GETENV ("GGHEADER", "TABLENAME")
, errno = @GETENV ("LASTERR", "DBERRNUM")
, dberrmsg = @GETENV ("LASTERR", "DBERRMSG")
, optype = @GETENV ("LASTERR", "OPTYPE")
, errtype = @GETENV ("LASTERR", "ERRTYPE")
, logrba = @GETENV ("GGHEADER", "LOGRBA")
, logposition = @GETENV ("GGHEADER", "LOGPOSITION")
, committimestamp = @GETENV ("GGHEADER", "COMMITTIMESTAMP")));

处理说明：
REPERROR参数用以控制Replicat进程如何响应映射过程中发生的错误
DEFAULT参数代表一种全局错误类型，即除去所有已明确指定的错误外的一切错误
DEFAULT2参数代表当DEFAULT错误以Exception方式响应时，所有MAP映射中未定义Exception部分出现的所有错误
EXCEPTIONSONLY只能用于确定表的异常处理
MAPEXCEPTION可以用于通配符的表异常处理

oracle 中如何定位重要(消耗资源多)的SQL

Posted on by

1、查看值得怀疑的SQL

select substr(to_char(s.pct,'99.00'),2)||'%'load,
       s.executions executes,
       p.sql_text
from(select address,
               disk_reads,
               executions,
               pct,
               rank()over(order by disk_reads desc) ranking
         from(select address,
                       disk_reads,
                       executions,
                      100*ratio_to_report(disk_reads)over() pct
                 from sys.v_$sql
                where command_type!=47)
        where disk_reads>50*executions) s,
       sys.v_$sqltext p
where s.ranking<=5
  and p.address=s.address
order by 1, s.address, p.piece;

2、查看消耗内存多的sql

select b.username,
       a. buffer_gets,
       a.executions,
       a.disk_reads / decode(a.executions, 0, 1, a.executions),
       a.sql_text SQL
  from v$sqlarea a, dba_users b
 where a.parsing_user_id = b.user_id
   and a.disk_reads > 10000
 order by disk_reads desc;

3、查看逻辑读多的SQL

select*
from(select buffer_gets, sql_text
         from v$sqlarea
        where buffer_gets>500000
        order by buffer_gets desc)
where rownum<=30;

4、查看执行次数多的SQL

select sql_text, executions
  from (select sql_text, executions from v$sqlarea order by executions desc)
 where rownum < 81;

5、查看读硬盘多的SQL

select sql_text, disk_reads
from(select sql_text, disk_reads from v$sqlarea order by disk_reads desc)
where rownum<21;

6、查看排序多的SQL

select sql_text, sorts
from(select sql_text, sorts from v$sqlarea order by sorts desc)
where rownum<21;

7、分析的次数太多，执行的次数太少，要用绑变量的方法来写sql

select substr(sql_text, 1, 80) "sql", count(*), sum(executions) "totexecs"
  from v$sqlarea
 where executions < 5
 group by substr(sql_text, 1, 80)
having count(*) > 30
 order by 2;