close

export PATH=/u01/app/ogg:$PATH
export LD_LIBRARY_PATH=/u01/app/ogg:$LD_LIBRARY_PATH
export GGATE=/u01/app/ogg
 
su - oracle
mkdir /u01/app/ogg
 
tar xvf fbo_ggs_Linux_x64_ora11g_64bit.tar -C /u01/app/ogg
 
chown -R oracle:oinstall /u01/app/ogg
 
create tablespace ogg datafile '/u01/app/oracle/oradata/ogg1/ogg.dbf' size 50m autoextend on;
create user ogg identified by ogg default tablespace ogg ;
grant dba to ogg;
要給於grant create table,create sequence to ogg; 否則執行ddel_setup.sql 會報錯
 
 
 
alter database force logging #強制紀錄日至紀錄
alter database add supplemental log data;如果你更新了主鍵中的部分字段，那補充log的作用就是把???其余的?成部分
的數據也傳輸到目標機，否則目標機就存在不確定性
select name , open_mode,force_logging,supplemental_log_data_min from v$database;
 
alter system set recyclebin=off scope=spfile;
 
SQL> @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>
 

SQL> @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
---------------------------------------- -----------------------------------------------------------------
1453/9                                   PL/SQL: SQL Statement ignored
1455/23                                  PL/SQL: ORA-00942: table or view does not exist
1464/9                                   PL/SQL: SQL Statement ignored
1466/23                                  PL/SQL: ORA-00942: table or view does not exist
1478/9                                   PL/SQL: SQL Statement ignored
1480/23                                  PL/SQL: ORA-00942: table or view does not exist
1485/9                                   PL/SQL: SQL Statement ignored
1487/23                                  PL/SQL: ORA-00942: table or view does not exist
1492/9                                   PL/SQL: SQL Statement ignored
1494/23                                  PL/SQL: ORA-00942: table or view does not exist
1499/9                                   PL/SQL: SQL Statement ignored
 
Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
1501/23                                  PL/SQL: ORA-00942: table or view does not exist
1581/4                                   PL/SQL: SQL Statement ignored
1582/18                                  PL/SQL: ORA-00942: table or view does not exist
1584/4                                   PL/SQL: SQL Statement ignored
1585/18                                  PL/SQL: ORA-00942: table or view does not exist
1600/25                                  PL/SQL: ORA-00942: table or view does not exist
1600/4                                   PL/SQL: SQL Statement ignored
1602/25                                  PL/SQL: ORA-00942: table or view does not exist
1602/4                                   PL/SQL: SQL Statement ignored
 

DDL IGNORE TABLE
-----------------------------------
FAILED: Table does not exist
 
DDL IGNORE LOG TABLE
-----------------------------------
FAILED: Table does not exist
 
DDLAUX  PACKAGE STATUS:
 
Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
No errors                                No errors
 
DDLAUX PACKAGE BODY STATUS:
 
Line/pos                                 Error
---------------------------------------- -----------------------------------------------------------------
0/0                                      PL/SQL: Compilation unit analysis terminated
1/21                                     PLS-00304: cannot compile body of 'DDLAUX' without its
                                         specification
 
1/21                                     PLS-00905: object OGG.DDLAUX is invalid
 

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
-----------------------------------
FAILED: Table does not exist
 
DDL HISTORY TABLE(1)
-----------------------------------
FAILED: Table does not exist
 
DDL DUMP TABLES
-----------------------------------
FAILED: Table does not exist
 
DDL DUMP COLUMNS
-----------------------------------
FAILED: Table does not exist
 
DDL DUMP LOG GROUPS
-----------------------------------
FAILED: Table does not exist
 
DDL DUMP PARTITIONS
-----------------------------------
FAILED: Table does not exist
 
DDL DUMP PRIMARY KEYS
-----------------------------------
FAILED: Table does not exist
 
DDL SEQUENCE
-----------------------------------
FAILED: Sequence does not exist
 
GGS_TEMP_COLS
-----------------------------------
FAILED: Table does not exist
 
GGS_TEMP_UK
-----------------------------------
FAILED: Table does not exist
 
DDL TRIGGER CODE STATUS:
 
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
 

DDL TRIGGER INSTALL STATUS
-----------------------------------
OK
 
DDL TRIGGER RUNNING STATUS
------------------------------------------------------------------------------------------------------------------------
ENABLED
FROM "OGG" ."GGS_SETUP"
            *
ERROR at line 2:
ORA-00942: table or view does not exist
 

FROM "OGG" ."GGS_SETUP"
            *
ERROR at line 2:
ORA-00942: table or view does not exist
 

FROM "OGG" ."GGS_SETUP"
            *
ERROR at line 2:
ORA-00942: table or view does not exist
 
 
 
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/ogg1/ogg1/trace/ggs_ddl_trace.log
 
Analyzing installation status...
 

STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
ERRORS detected in installation of DDL Replication software components (6)
 
Script complete.
 

SQL> @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> GRANT GGS_GGSUSER_ROLE TO ogg
  2  ;
 
Grant succeeded.
 

SQL> @ddl_enable.sql;
 
Trigger altered.
 
 
 
 
 
測試
 
源和目標數據庫上創建測試用戶
primary database
create tablespace test datafile '/u01/app/oracle/oradata/ogg/test.dbf' size 100m autoextend on;
create user test identified by test default tablespace test temporary tablespace temp
grant connect,resource,dba to test
conn test/test
create table dave as select * from sys.all_users;
 
standby databsae
create tablespace test1 datafile '/u01/app/oracle/oradata/ogg1/test1.dbf' size 100m autoextend on;
create user test1 identified by test1 default tablespace test1 temporary tablespace temp
grant connect,resource,dba to test1
conn test1/test1
create table jiade as select * from sys.all_users where 1=2 ;
 
開始設定ogg
 

GGSCI (ogg.localdomain) 3> 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: already exists
Temporary files                /u01/app/ogg/dirtmp: created
Stdout files                   /u01/app/ogg/dirout: created
 
設定將兩個表格同步
 
source database
GGSCI (ogg.localdomain) 1> dblogin userid ogg, password ogg
Successfully logged into database.
 
GGSCI (ogg.localdomain) 2>
 
GGSCI (ogg.localdomain) 2> add trandata test.dave
 
2016-03-06 11:52:26  WARNING OGG-00869  No unique key is defined for table 'DAVE'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
 
Logging of supplemental redo data enabled for table TEST.DAVE.
 
設定MGR管理進程 P+S
GGSCI (ogg.localdomain) 2> edit params mgr
 
PORT 7809
USERID ogg, PASSWORD ogg
PURGEOLDEXTRACTS /u01/app/ogg/dirdat/, USECHECKPOINTS # 清除不需要的trail文件 ,並檢查對列
 
-------------------------------
port 7839
DYNAMICPORTLIST 7840-7850 指定port可以動態端口
AUTOSTART EXTRACT * 自動開啟extract
AUTORESTART EXTRACT*,RETRIES 5,WAITMINUTES 2,RESETMINUTES 5  * 自動重啟extract每2分鐘重啟一次 重試5次
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7 本處設置表示對於超過7天的?跡文件進行刪除。
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
定義數據延遲的預警機制：本處設置表示MGR進程每隔1小時檢查EXTRACT的延遲情況，如果超過了30分鐘就把延遲作為信息記錄到錯誤日誌中，如果延遲超過了45分鐘，則把它作為警告寫到錯誤日誌中。
-----------------------------------------------------------
配置初始化數據進程
來源端
GGSCI (ogg.localdomain) 8> add extract send, sourceistable
EXTRACT added.
 
增加進程
GGSCI (ogg.localdomain) 10> edit params send
extract send
userid ogg, password ogg
rmthost ogg1, mgrport 7809
rmttask replicat, group revice
table test.*;
 
目標端
 
GGSCI (ogg1.localdomain) 7> add replicat revice, specialrun
REPLICAT added.
 
GGSCI (ogg1.localdomain) 7> edit params revice
replicat revice
assumetargetdefs
userid ogg, password ogg
discardfile /u01/app/ogg/revice.dsc, purge
map test.DAVE, target test1.jiade;
 

----------------------------------------------------同步後
Report at 2016-03-06 12:17:34 (activity since 2016-03-06 12:17:25)
 
Output to revice:
 
From Table TEST.DAVE:
       #                   inserts:        38  新增38筆資料
       #                   updates:         0
       #                   deletes:         0
       #                  discards:         0
 

REDO Log Statistics
  Bytes parsed                    0
  Bytes output                 4193
 
 
 
配置OGG
P+S
 
GGSCI (ogg.localdomain) 10> edit params ./GLOBALS
GGSCHEMA ogg
CHECKPOINTTABLE ogg.checkpoint
 
GGSCI (ogg.localdomain) 11> dblogin userid ogg, password ogg
Successfully logged into database.
 
GGSCI (ogg.localdomain) 12> add checkpointtable ogg.checkpoint
 
Successfully created checkpoint table ogg.checkpoint.
 
P配置捕獲進程
 
GGSCI (ogg.localdomain) 14> edit params ogg1send
 
extract ogg1send
userid ogg, password ogg
exttrail /u01/app/ogg/dirdat/sd
ddl include mapped objname test.*;
table test.*;
 

GGSCI (ogg.localdomain) 21> add extract ogg1send, tranlog,begin now
EXTRACT added.
GGSCI (ogg.localdomain) 25> add exttrail /u01/app/ogg/dirdat/sd, extract ogg1send, megabytes 5 #最大文件size
EXTTRAIL added.
 
P配置 pump
 

GGSCI (ogg1.localdomain) 16> edit params pumpog1
extract pumpog1
passthru
rmthost ogg1,mgrport 7809, compress
rmttrail /u01/app/ogg/dirdat/pm
table test.*;
 

P增加進程
GGSCI (ogg.localdomain) 6> add extract pumpog1, exttrailsource /u01/app/ogg/dirdat/sd
EXTRACT added.
 
資料將會傳到目標端
GGSCI (ogg.localdomain) 2> add rmttrail /u01/app/ogg/dirdat/pm, extract pumpog1, megabytes 5
RMTTRAIL added.
 
目標端配置同步進程S
 

GGSCI (ogg1.localdomain) 7> add replicat oggreiv, exttrail /u01/app/ogg/dirdat/pm,checkpointtable ogg.checkpoint
REPLICAT added.
 

GGSCI (ogg1.localdomain) 11> edit params oggreiv
replicat oggreiv
userid ogg, password ogg
assumetargetdefs
DDL
DDLOPTIONS REPORT
discardfile /u01/app/ogg/dirrpt/oggiv.dsc, megabytes 10
map test.*, target test1.*;
 
 
 
開啟
GGSCI (ogg1.localdomain) 9> start rep oggreiv
 
Sending START request to MANAGER ...
REPLICAT OGGREIV starting
 

GGSCI (ogg1.localdomain) 10> info all
 
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
 
MANAGER     RUNNING
REPLICAT    RUNNING     OGGREIV     00:00:00      00:00:01
 
 
 
map test.dave, target test1.jiade;
 

-----------------------------------新增DDL功能
 
在ogg1send 加入
 
extract ogg1send
userid ogg, password ogg
exttrail /u01/app/ogg/dirdat/sd
DDLOPTIONS ADDTRANDATA, REPORT
tranlogoptions excludeuser ogg
ddl include mapped objname test.*;
table test.*;
 

----------------------------SQL  測試用的
 
insert into dave select * from sys.all_users;
insert into jiade select * from sys.all_users;
create table jiade as select * from sys.all_users where 1=2 ;
 

-------------------------------------------排除不要複製表格
 
在extract 進程寫下
tableexclude test.jiade;
tableexclude test.TMP_*;
 
在目標專replica 進程寫下
mapexclude test.jiade;
 

---------------------------------------
查看scn碼
select dbms_flashback.get_system_change_number current_sca from dual
使用exp
 exp test1/test1 direct=y buffer=6400000 flashback_scn=1214699 \
> constraints=n grants=n triggers=n \
> file=/u01/app/new_tab.dmp log=/u01/app/new_tab.log \
> tables = jiade
 
 
arrow
arrow
    全站熱搜
    創作者介紹
    創作者 jiade_Chen 的頭像
    jiade_Chen

    assistant engineer

    jiade_Chen 發表在 痞客邦 留言(0) 人氣()

    E-mail轉寄