oracle goldengate 配置DML&DDL實驗

序號

程式名

說明

1

Manager

Goldengate軟體的管理程式，用於管理生產端或者容災端OGG相關程式。以下簡稱為MGR。

2

Extract

GoldenGate軟體的抽取程式，又叫Capture程式，一般用於抽取資料庫日誌抓取資料變化或將本地佇列中資料傳遞到目標端。以下簡稱為ext_ylqa。

3

Replicat

GoldenGate軟體的投遞程式，又稱為Delivery程式，用於將佇列檔案中的資料變化轉換為sql應用到目標庫。以下簡稱為repylqa。

4

Data Pump

專指將本地佇列中資料傳遞到目標端的Extract程式，區別於讀取日誌的主Extract程式。以下簡稱為dpylqa。

-- Create tablespace

SQL>  create tablespace ggs datafile '/home/app/oradata/node1/ggs01.dbf' size 50m;

-- Create the user

CREATE USER ggate IDENTIFIED BY ggate default tablespace ggs temporary tablespace temp quota unlimited on ggs ;

-- Grant/Revoke role privileges

grant resource,connect,dba to ggate;

--檢視是否歸檔

Archive log list;

--修改歸檔模式

shutdown immediate

startup mount;

alter database archivelog;

alter database open;

export OGG_HOME=/home/oracle/ggate

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$OGG_HOME:$LD_LIBRARY_PATH

select

SUPPLEMENTAL_LOG_DATA_MIN

,SUPPLEMENTAL_LOG_DATA_PK

,SUPPLEMENTAL_LOG_DATA_UI

,SUPPLEMENTAL_LOG_DATA_FK

,SUPPLEMENTAL_LOG_DATA_ALL from v$database;

alter database add supplemental log data;

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

alter system switch logfile;

--回退

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

alter database drop supplemental log data;

alter database force logging;

--安裝軟體

cd /home/oracle/ggate

tar zxvf *.gz

--建立子目錄

在/home/oracle/ggate目錄下，執行以下命令：  --如果沒有修改oracle環境變數，會在此步驟報錯

./ggsci

--在GGSCI命令列為複製表新增transdata

（Enabling schema-level supplemental logging）

DBLOGIN USERID ggate, PASSWORD ggate

ADD TRANDATA SCOTT.*   

--在sqlplus下檢視是否完整新增

select table_name from all_tables where owner='SCOTT' and table_name not in (select distinct table_name from dba_log_group_columns where owner='SCOTT');

GGSCI>EDIT PARAMS ./GLOBALS

GGSCHEMA ggate—指定進行DDL複製的資料庫使用者，DML可不配置

CHECKPOINTTABLE ggate.oggchkpt

GGSCI>edit params mgr

PORT 7839

DYNAMICPORTLIST  7840-7849

--AUTOSTART EXTRACT *

--AUTORESTART EXTRACT *, RETRIES 5, WAITMINUTES 7

PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 7

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

以下兩句用於DDL，單獨配置DML時，可不新增

PURGEDDLHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 7

PURGEMARKERHISTORY MINKEEPDAYS 3, MAXKEEPDAYS 7

GGSCI> edit params ext_scot

EXTRACT ext_scot

SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")

DBOPTIONS   ALLOWUNUSEDCOLUMN

USERID ggate, PASSWORD ggate

--GETTRUNCATES這個引數可以在開啟DDL複製時登出掉

REPORTCOUNT EVERY 1 MINUTES, RATE

NUMFILES 5000

DISCARDFILE ./dirrpt/ext_scot.dsc, APPEND, MEGABYTES 100

DISCARDROLLOVER AT 2:00

WARNLONGTRANS 2h, CHECKINTERVAL 3m

EXTTRAIL ./dirdat/ga, MEGABYTES 200

DYNAMICRESOLUTION

TRANLOGOPTIONS CONVERTUCS2CLOBS

TRANLOGOPTIONS RAWDEVICEOFFSET 0

以下部分是DDL的配置，DML複製可以不配置

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

DDL &

INCLUDE MAPPED OBJTYPE 'table' &

INCLUDE MAPPED OBJTYPE 'index' &

EXCLUDE OPTYPE COMMENT

DDLOPTIONS  NOCROSSRENAME  REPORT

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

TABLE      SCOTT.* ;

GGSCI>edit params dpescot

EXTRACT dpescot

RMTHOST 192.168.150.129, MGRPORT 7839, COMPRESS

PASSTHRU

NUMFILES 5000

RMTTRAIL ./dirdat/ga

DYNAMICRESOLUTION

TABLE      SCOTT.* ;

GGSCI>edit params repscob

REPLICAT repscob

SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")

USERID ggate, PASSWORD ggate

REPORT AT 00:59

REPORTCOUNT EVERY 1 MINUTES, RATE

REPORTROLLOVER AT 02:00

REPERROR DEFAULT, ABEND

NUMFILES 5000

GROUPTRANSOPS 10000

--HANDLECOLLISIONS

ASSUMETARGETDEFS

DISCARDFILE ./dirrpt/repylqb.dsc, APPEND, MEGABYTES 200

DISCARDROLLOVER AT 02:00

GETTRUNCATES

ALLOWNOOPUPDATES

以下部分是DDL的配置，DML複製可以不配置

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

DDL include mapped

DDLOPTIONS report

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

MAP SCOTT.*, TARGET  SCOTT.*;

進入OGG安裝目錄下

cd /home/oracle/ggate

sqlplus "/ as sysdba"

SQL> @marker_setup.sql

Enter GoldenGate schema name:ggate

SQL> @ddl_setup.sql

Enter GoldenGate schema name:ggate

SQL> @role_setup.sql

Grant this role to each user assigned to the Extract, Replicat, GGSCI, and Manager processes, by using the following SQL command:

GRANT GGS_GGSUSER_ROLE TO

where is the user assigned to the GoldenGate processes.

手動執行以上授權SQL

GRANT GGS_GGSUSER_ROLE TO ggate;

啟動DDL觸發器（在OGG安裝目錄下）

SQL>@ddl_enable.sql

SQL> @ddl_pin ggate

GGSCI> ADD EXTRACT ext_scot,tranlog,begin now

GGSCI> ADD EXTTRAIL ./dirdat/ga, EXTRACT ext_scot, megabytes 100

GGSCI> ADD EXTRACT dpescot, EXTTRAILSOURCE ./dirdat/ga

GGSCI> ADD RMTTRAIL ./dirdat/ga, EXTRACT dpescot, MEGABYTES 100

GGSCI> start mgr

GGSCI> start extract ext_scot

SQL> select to_char(sysdate,'yyyy-mm-dd hh24:mi:ss') from dual;

SQL>select  start_time  from v$transaction where to_date(start_time, 'yyyy-mm-dd hh24:mi:ss')

$ rman target /

rman> run {

ALLOCATE CHANNEL ch00 TYPE DISK;

ALLOCATE CHANNEL ch01 TYPE DISK;

CROSSCHECK BACKUPSET;

DELETE NOPROMPT EXPIRED BACKUPSET;

sql 'alter system archive log current';

BACKUP AS BACKUPSET SKIP INACCESSIBLE;

TAG hot_db_bk_level0 FORMAT '/rmanbak/ogg/full_%s_%p_%t';

FULL DATABASE;

RELEASE CHANNEL ch00;

RELEASE CHANNEL ch01;

}

run {

ALLOCATE CHANNEL ch00 TYPE DISK;

ALLOCATE CHANNEL ch01 TYPE DISK;

sql 'alter system switch logfile';

sql 'alter system switch logfile';

sql 'alter system switch logfile';

sql 'alter system archive log current';

BACKUP ARCHIVELOG ALL FORMAT '/rmanbak/ogg/ARCH_%U';

BACKUP CURRENT CONTROLFILE FORMAT /rmanbak/ogg/bk_controlfile';

RELEASE CHANNEL ch00;

RELEASE CHANNEL ch01;

}

$ rman target /

RMAN> list backup;

$ rman target /

RMAN> startup nomount;

RMAN> restore controlfile from ‘/rmanbak/ogg/bk_controlfile’;

RMAN> alter database mount;

RMAN> crosscheck backup;

RMAN> restore database;

RMAN> run {

set UNTIL SCN ***;   --該SCN為第二步中得到的SCN號

recover database;

}

SQL> SELECT CHECKPOINT_CHANGE#,CHECKPOINT_TIME FROM V$DATAFILE_HEADER;

Alter database open resetlogs;

archive log list;

shutdown immediate;

startup mount;

alter database noarchivelog;

atler database open;

alter database no force logging;

declare

v_sql varchar2(2000);

CURSOR c_trigger IS SELECT 'alter trigger '||owner||'.'||trigger_name||' disable' from dba_triggers where owner='SCOTT';

BEGIN

OPEN c_trigger;

LOOP

FETCH c_trigger INTO v_sql;

EXIT WHEN c_trigger%NOTFOUND;

execute immediate v_sql;

end loop;

close c_trigger;

end;

/

--檢查是否全部禁用

select owner||'.'||trigger_name,status from dba_triggers where owner='SCOTT' and STATUS='ENABLED';

declare

v_sql varchar2(2000);

CURSOR c_trigger IS SELECT 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name from dba_constraints where constraint_type='R' and owner='SCOTT';

BEGIN

OPEN c_trigger;

LOOP

FETCH c_trigger INTO v_sql;

EXIT WHEN c_trigger%NOTFOUND;

execute immediate v_sql;

end loop;

close c_trigger;

end;

/

--檢視結果

select status, owner||'.'||table_name from dba_constraints where owner='SCOTT' and status='ENABLED' and  constraint_type='R';

job_queue_processes 0

SQL>@ddl_disable.sql

SQL>@ddl_remove.sql

SQL>@marker_remove.sql

GGSCI> EDIT PARAMS ./GLOBALS

GGSCHEMA ggate

CHECKPOINTTABLE ggate.oggchkpt

--新增檢查表

GGSCI> DBLOGIN USERID ggate, PASSWORD ggate

GGSCI> ADD CHECKPOINTTABLE ggate.oggchkpt

PORT 7839

DYNAMICPORTLIST 7840-7849

--AUTOSTART REPLICAT *

--AUTORESTART REPLICAT *, RETRIES 5, WAITMINUTES 1

PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS, MINKEEPDAYS 7

LAGREPORTHOURS 1

LAGINFOMINUTES 30

LAGCRITICALMINUTES 45

REPLICAT repscot

SETENV (NLS_LANG = "AMERICAN_AMERICA.AL32UTF8")

USERID ggate, PASSWORD ggate

REPORT AT 00:59

REPORTCOUNT EVERY 1 MINUTES, RATE

REPORTROLLOVER AT 02:00

REPERROR DEFAULT, ABEND

NUMFILES 5000

GROUPTRANSOPS 10000

ASSUMETARGETDEFS

DISCARDFILE ./dirrpt/repscot.dsc, APPEND, MEGABYTES 200

DISCARDROLLOVER AT 02:00

ALLOWNOOPUPDATES

--------------------------------------DDL專用-----------

DDL include mapped

DDLOPTIONS report

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

MAP SCOTT.*, TARGET  SCOTT.*;

EXTRACT ext_scob

SETENV (NLS_LANG="AMERICAN_AMERICA.AL32UTF8")

DBOPTIONS   ALLOWUNUSEDCOLUMN

USERID ggate, PASSWORD ggate

GETTRUNCATES

REPORTCOUNT EVERY 1 MINUTES, RATE

NUMFILES 5000

DISCARDFILE ./dirrpt/ext_scob.dsc, APPEND, MEGABYTES 100

DISCARDROLLOVER AT 2:00

WARNLONGTRANS 2h, CHECKINTERVAL 3m

EXTTRAIL ./dirdat/et, MEGABYTES 200

DYNAMICRESOLUTION

TRANLOGOPTIONS CONVERTUCS2CLOBS

TRANLOGOPTIONS  EXCLUDEUSER ggate

DDL &

INCLUDE MAPPED OBJTYPE 'table' &

INCLUDE MAPPED OBJTYPE 'index' &

EXCLUDE OPTYPE COMMENT

DDLOPTIONS  NOCROSSRENAME  REPORT

TABLE      SCOTT.* ;

EXTRACT dpescob

RMTHOST 192.168.150.128, MGRPORT 7839, COMPRESS

PASSTHRU

NUMFILES 5000

RMTTRAIL ./dirdat/et

DYNAMICRESOLUTION

TABLE      SCOTT.* ;

GGSCI> ADD REPLICAT repscot, EXTTRAIL ./dirdat/ga , EXTSEQNO 0, EXTRBA 0 , CHECKPOINTTABLE ggate.oggchkpt

GGSCI>start mgr

GGSCI> start ext dpescot

GGSCI>start repscot , at csn ****    --這裡的SCN就是恢復出來庫的那個SCN,本次實驗沒有使用SCN

cd /goldengate

./ggsci

GGSCI>Info all