您好,登錄后才能下訂單哦!
OGG安裝
fbo_ggs_Linux_x64_shiphome.zip---------------------同時(shí)支持11g和12c
添加用戶
useradd -u 1003 -g oinstall -G dba ogg
配置環(huán)境變量
export ORACLE_BASE=/u01/app/oracle;
export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/db_1;
export ORACLE_SID=racdb1;
export OGG_HOME=$ORACLE_BASE/ogg
export PATH=$ORACLE_HOME/bin:$PATH:/home/oracle/bin:$OGG_HOME/;
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/ogg/:/lib:/usr/lib;
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib;
安裝時(shí)注意目錄
2、找不到ggMessage
Cannot load ICU resource bundle'ggMessage', error code 2 - No such file or directory
Aborted (core dumped)
解決方法:oracle goldengate的HOME目錄下執(zhí)行
GGSCI (oggtarget) 2> help
GGSCI Command Summary:
Object: Command:
SUBDIRS CREATE
DATASTORE ALTER, CREATE, DELETE, INFO, REPAIR
ER INFO, KILL, LAG, SEND, STATUS,START, STATS, STOP
EXTRACT ADD, ALTER, CLEANUP, DELETE, INFO,KILL,
LAG, REGISTER, SEND, START,STATS, STATUS, STOP
UNREGISTER
EXTTRAIL ADD, ALTER, DELETE, INFO
GGSEVT VIEW
JAGENT INFO, START, STATUS, STOP
MANAGER INFO, SEND, START, STOP, STATUS
MARKER INFO
PARAMETERS EDIT, VIEW, SET EDITOR, INFO,GETPARAMINFO
REPLICAT ADD, ALTER, CLEANUP, DELETE, INFO,KILL, LAG, REGISTER, SEND,
START, STATS, STATUS, STOP,SYNCHRONIZE, UNREGISTER
REPORT VIEW
RMTTRAIL ADD, ALTER, DELETE, INFO
TRACETABLE ADD, DELETE, INFO
TRANDATA ADD, DELETE, INFO
SCHEMATRANDATA ADD, DELETE, INFO
CHECKPOINTTABLE ADD, DELETE, CLEANUP, INFO, UPGRADE
WALLET CREATE, OPEN, PURGE
MASTERKEY ADD, INFO, RENEW, DELETE, UNDELETE
CREDENTIALSTORE ADD, ALTER, INFO, DELETE
HEARTBEATTABLE ADD, DELETE, ALTER, INFO
HEARTBEATENTRY DELETE
Commands without an object:
(Database) DBLOGIN, LIST TABLES, ENCRYPT PASSWORD,FLUSH SEQUENCE
MININGDBLOGIN, SET NAMECCSID
(DDL) DUMPDDL
(Miscellaneous) ! ,ALLOWNESTED | NOALLOWNESTED, CREATESUBDIRS,
DEFAULTJOURNAL, FC, HELP,HISTORY, INFO ALL, OBEY, SHELL,
SHOW, VERSIONS, VIEW GGSEVT,VIEW REPORT
(note: type the word COMMANDafter the ! to display the
! help topic, for example:GGSCI (sys1)> help ! command
實(shí)驗(yàn)規(guī)劃
項(xiàng)目 | ||
操作系統(tǒng) | ||
主機(jī)名 | ||
數(shù)據(jù)庫(kù)版本 | ||
數(shù)據(jù)庫(kù)字符集 | ||
Oracle版本 | ||
Ogg版本 | ||
Oracle sid | dbdream | stream |
主庫(kù)進(jìn)行全備
$ rman target /
run {
allocate channel d0 type disk;
allocate channel d1 type disk;
backup format'/u01/backup/full_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup format '/u01/backup/arc_t%t_s%s_p%p'archivelog all;
release channel d0;
release channel d1;
}
2.3.1 主庫(kù)備份
主庫(kù)進(jìn)行全備
$ rman target /
run {
allocate channel d0 type disk;
allocate channel d1 type disk;
backup format'/u01/backup/full_t%t_s%s_p%p' database;
sql 'alter system archive log current';
backup format '/u01/backup/arc_t%t_s%s_p%p'archivelog all;
release channel d0;
release channel d1;
}
創(chuàng)建備用控制文件
RMAN> backup current controlfile forstandby format '/u01/backup/control01.ctl';
scp * 192.168.120.203:/u01/backup
恢復(fù)
[oracle@oggtarget ~]$ export ORACLE_SID=stream
[oracle@oggtarget ~]$ rman target /
Recovery Manager: Release 11.2.0.4.0 -Production on Sun Aug 27 09:54:43 2017
Copyright (c) 1982, 2011, Oracle and/or itsaffiliates. All rights reserved.
connected to target database (not started)
RMAN> set dbid=2496948349
RMAN> startup nomount
startup failed: ORA-01078: failure inprocessing system parameters
LRM-00109: could not open parameter file'/u01/app/oracle/product/11.2.0/db_1/dbs/initstream.ora'
starting Oracle instance without parameterfile for retrieval of spfile
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2260088 bytes
Variable Size 281019272 bytes
Database Buffers 780140544 bytes
Redo Buffers 5517312 bytes
注意:在rman下即使沒有參數(shù)文件,默認(rèn)也會(huì)啟動(dòng)一個(gè)DUMMY實(shí)例,以便能夠恢復(fù)參數(shù)文件。
1、恢復(fù)spfile
RMAN> restore spfile from'/u01/backup/full_t953113531_s4_p1';
RMAN> sql "create pfile from spfile"
修改pfile參數(shù)
dbdream.__java_pool_size=4194304
dbdream.__large_pool_size=8388608
dbdream.__oracle_base='/u01/app/oracle'#ORACLE_BASEset from environment
dbdream.__pga_aggregate_target=314572800
dbdream.__sga_target=465567744
dbdream.__shared_io_pool_size=0
dbdream.__shared_pool_size=117440512
dbdream.__streams_pool_size=0
*.audit_file_dest='/u01/app/oracle/admin/stream/adump'-----------------------修改創(chuàng)建目錄
*.audit_trail='db'
*.compatible='11.2.0.4.0'
*.control_files='/u01/app/oracle/oradata/stream/control01.ctl','/u01/app/oracle/fast_recovery_area/stream/control02.ctl'----修改
*.db_block_size=8192
*.db_domain=''
*.db_name='stream'-----修改
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4385144832
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP)(SERVICE=streamXDB)'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=780140544
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'
~
RMAN> shutdown abort
用新修改的文件啟動(dòng) nomount
RMAN> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initstream.ora'
創(chuàng)建pfile
RMAN> sql "create spfile from pfile";
新spfile啟動(dòng)
RMAN> startup nomount;
RMAN> startup force nomount;
2、恢復(fù)控制文件
RMAN> restore controlfile from '/u01/backup/full_t953113527_s3_p1';
Starting restore at 27-AUG-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=19 device type=DISK
channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete,elapsed time: 00:00:01
output filename=/u01/app/oracle/oradata/stream/control01.ctl
output filename=/u01/app/oracle/fast_recovery_area/stream/control02.ctl
Finished restore at 27-AUG-17
3、啟動(dòng)數(shù)據(jù)庫(kù)到加載狀態(tài)
RMAN> alter database mount;
RMAN> catalog start with '/backup/';
RMAN> restore database;
RMAN> recover database;
RMAN>alter database open resetlogs 打開數(shù)據(jù)庫(kù)
nid target=/as sysdba dbname=stream
[oracle@oggtarget dbs]$ nid target=/assysdba dbname=stream
DBNEWID: Release 11.2.0.4.0 - Production onSun Aug 27 12:31:08 2017
Copyright (c) 1982, 2011, Oracle and/or itsaffiliates. All rights reserved.
Password:
Connected to database DBDREAM (DBID=2496948349)
Connected to server version 11.2.0
Control Files in database:
/u01/app/oracle/oradata/stream/control01.ctl
/u01/app/oracle/fast_recovery_area/stream/control02.ctl
Change database ID and database nameDBDREAM to STREAM? (Y/[N]) => y
Proceeding with operation
Changing database ID from 2496948349 to1719130576
Changing database name from DBDREAM toSTREAM
Control File /u01/app/oracle/oradata/stream/control01.ctl - modified
Control File /u01/app/oracle/fast_recovery_area/stream/control02.ctl -modified
Datafile /u01/app/oracle/oradata/dbdream/system01.db - dbid changed,wrote new name
Datafile /u01/app/oracle/oradata/dbdream/sysaux01.db - dbid changed,wrote new name
Datafile /u01/app/oracle/oradata/dbdream/undotbs01.db - dbid changed,wrote new name
Datafile /u01/app/oracle/oradata/dbdream/users01.db - dbid changed,wrote new name
Datafile /u01/app/oracle/oradata/dbdream/temp01.db - dbid changed, wrotenew name
Control File /u01/app/oracle/oradata/stream/control01.ctl - dbidchanged, wrote new name
Control File /u01/app/oracle/fast_recovery_area/stream/control02.ctl -dbid changed, wrote new name
Instance shut down
Database name changed to STREAM.
Modify parameter file and generate a newpassword file before restarting.
Database ID for database STREAM changed to1719130576.
All previous backups and archived redo logsfor this database are unusable.
Database is not aware of previous backupsand archived logs in Recovery Area.
Database has been shutdown, open databasewith RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
如果沒有修改參數(shù)文件中的DB_NAME參數(shù),那么在MOUNT的時(shí)候,會(huì)報(bào)ORA-01103錯(cuò)誤。
修改DB_NAME參數(shù),嘗試直接打開數(shù)據(jù)庫(kù)。
提示必須使用RESETLOGS的方式才能打開數(shù)據(jù)庫(kù)。
默認(rèn)情況下,db_unique_name 和service_names都會(huì)伴隨著DB NAME一起改變,此時(shí)由于service_names發(fā)生了變化,正常情況下應(yīng)用是無(wú)法連接數(shù)據(jù)庫(kù)的(以SID連接方式除外)。
設(shè)置環(huán)境變量(oracle用戶)
PATH=$ORACLE_HOME/bin:$PATH:$HOME/bin:/ggs
export LD_LIBRARY_PATH=/ggs:$ORACLE_HOME/lib
SQL> select log_mode fromgv$database;
SQL> archive log list; ----注意歸檔路徑需要是共享路徑
selectforce_logging,supplemental_log_data_min,supplemental_log_data_all,flashback_onfrom v$database;
開啟:
alter database force logging;
alter database add supplemental logdata;
alter system archive log current;
select dic.table_owner,
dic.table_name,
dic.index_name,
di.uniqueness,
dic.column_name
from dba_ind_columns dic, dba_indexes di, dba_tab_columns dtc
where dic.table_owner = '自行添加用戶' -----修改用戶名
and dtc.OWNER = '自行添加用戶' -----修改用戶名
AND dic.table_owner = di.table_owner
and dic.TABLE_NAME = di.table_name
and dic.index_name = di.index_name
and di.uniqueness = 'UNIQUE'
and dtc.owner = di.table_owner
and dtc.TABLE_NAME = di.table_name
and dic.column_name = dtc.COLUMN_NAME
and dtc.nullable = ' Y '
anddic.TABLE_NAME = dtc.TABLE_NAME;
不應(yīng)該返回行,如果返回了,修改:要么變?yōu)榉俏ㄒ凰饕?,要么在保留唯一索引的情況下,將列的定義置為 not null。
create user ogg identified by ogg default tablespaceusers;
grant dba to ogg;
ALTER SYSTEM SETENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;
select owner,table_name,logging fromdba_tables where logging='NO' AND owner='用戶名';
修改為logging的表的語(yǔ)法:alter table 表名 logging;
注意:在ext進(jìn)程的參數(shù)文件里添加 dboptions allownologging可以讓ext進(jìn)程繼續(xù)運(yùn)行,但是會(huì)導(dǎo)致數(shù)據(jù)丟失。
進(jìn)入ogg安裝路徑:
ggsci
dblogin userid ogg password ogg
GGSCI (oggsource) 1> dblogin userid ogg password ogg
Successfully logged into database.
GGSCI (oggsource as ogg@dbdream) 2>create subdirs
Creating subdirectories under current directory/u01/app/oracle/ogg
Parameter files /u01/app/oracle/ogg/dirprm:already exists
Report files /u01/app/oracle/ogg/dirrpt:already exists
Checkpoint files /u01/app/oracle/ogg/dirchk:already exists
Process status files /u01/app/oracle/ogg/dirpcs: alreadyexists
SQL script files /u01/app/oracle/ogg/dirsql:already exists
Database definitions files /u01/app/oracle/ogg/dirdef: already exists
Extract data files /u01/app/oracle/ogg/dirdat:already exists
Temporary files /u01/app/oracle/ogg/dirtmp:already exists
Credential store files /u01/app/oracle/ogg/dircrd: alreadyexists
Masterkey wallet files /u01/app/oracle/ogg/dirwlt: alreadyexists
Dump files /u01/app/oracle/ogg/dirdmp: already exists
GGSCI (oggsource as ogg@dbdream) 2>addtrandata lm.testogg
-------------------為表添加附加日志,以便goldengate進(jìn)行redo的抽取以及應(yīng)用。
使用ogg作為存儲(chǔ)DDL objects的用戶給ogg授權(quán):
SQL> GRANT EXECUTE ON UTL_FILE TO ogg;
ggsci
edit param ./GLOBALS中加入:
GGSCHEMA goldengate
如果是10g需要停用 recyclebin,11g就不需要了
退出所有的oracle連接后執(zhí)行:
cd /ggs
sqlplus / as sysdba
@marker_setup.sql
SQL> @marker_setup.sql
Marker setup script
You will be prompted for the name of aschema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior torunning this script.
NOTE: Stop all DDL replication beforestarting this installation.
Enter Oracle GoldenGate schema name:
Setting schema name to OGG
MARKER TABLE
-------------------------------
OK
MARKER SEQUENCE
-------------------------------
OK
Script complete.
SQL>@ddl_setup.sql
Oracle GoldenGate DDL Replication setupscript
Verifying that current user has privilegesto install DDL Replication...
You will be prompted for the name of aschema for the Oracle GoldenGate database objects.
NOTE: For an Oracle 10g source, the systemrecycle bin must be disabled. For Oracle 11g and later, it can be enabled.
NOTE: The schema must be created prior torunning this script.
NOTE: Stop all DDL replication beforestarting this installation.
Enter Oracle GoldenGate schema name:ogg
Working, please wait ...
Spooling to file ddl_setup_spool.txt
Checking for sessions that are holdinglocks on Oracle Golden Gate metadata tables ...
Check complete.
Using OGG as a Oracle GoldenGate schemaname.
Working, please wait ...
DDL replication setup script complete,running verification script...
Please enter the name of a schema for theGoldenGate 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
----------------------------------------------------------------------
NONE
LOCATION OF DDL TRACE FILE
------------------------------------------------------------------------------------------------------------------------
/u01/app/oracle/diag/rdbms/dbdream/dbdream/trace/ggs_ddl_trace.log
Analyzing installation status...
VERSION OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
OGGCORE_12.2.0.1.0_PLATFORMS_151211.1401
STATUS OF DDL REPLICATION
------------------------------------------------------------------------------------------------------------------------
SUCCESSFUL installation of DDL Replicationsoftware components
Script complete.
SQL>
SQL> @role_setup.sql
GGS Role setup script
This script will drop and recreate the roleGGS_GGSUSER_ROLE
To use a different role name, quit thisscript and then edit the params.sql script to change the gg_role parameter tothe preferred name. (Do not run the script.)
You will be prompted for the name of aschema for the GoldenGate database objects.
NOTE: The schema must be created prior torunning this script.
NOTE: Stop all DDL replication beforestarting this installation.
Enter GoldenGate schema name:ogg
SP2-0606: Cannot create SPOOL file"role_setup_spool.txt"
SP2-0606: Cannot create STORE file"role_setup_set.txt"
PL/SQL procedure successfully completed.
Role setup script complete
Grant this role to each user assigned tothe Extract, GGSCI, and Manager processes, by using the following SQL command:
GRANT GGS_GGSUSER_ROLE TO<loggedUser>
where <loggedUser> is the userassigned to the GoldenGate processes.
SQL> grantggs_ggsuser_role to ogg;
SQL> @ddl_enable.sql
如果是有災(zāi)備演練的需求,需要配置sequence同步
cd /ggs --ogg安裝目錄
sqlplus / as sysdba
@sequence.sql
GRANT EXECUTE on goldengate.updateSequenceTO goldengate;
su - grid
vi$ORACLE_HOME/network/admin/listener.ora
SID_LIST_LISTENER =
(SID_LIST =
(SID_DESC =
(GLOBAL_DBNAME = +ASM)
(ORACLE_HOME=/u01/app/11.2.0/grid)
(SID_NAME = +ASM1)
. )
.)
.
.
.su - oracle
.cd $ORACLE_HOME/network/admin
.vi tnsnames.ora
.ASM =
. (DESCRIPTION =
. (ADDRESS = (PROTOCOL = TCP)(HOST = 186.168.100.3)(PORT = 1521))
. (CONNECT_DATA =
. (SERVER = DEDICATED)
. (SERVICE_NAME = +ASM)
. (SID_NAME = +ASM1)
. )
. )
.
GGSCI(NDSCDB1) 1> edit param mgr
port 7809
-- DYNAMICPORTLIST 7830-7835
autostart extract *
autorestart extract *, waitminutes 1,retries 60, RESETMINUTES 60
PURGEOLDEXTRACTS /ggs/dirdat/sd*,USECHECKPOINTS, MINKEEPHOURS 2
~ MANAGER進(jìn)程參數(shù)配置說明:
PORT:指定服務(wù)監(jiān)聽端口;這里以7839為例,默認(rèn)端口為7809
DYNAMICPORTLIST:動(dòng)態(tài)端口:可以制定最大256個(gè)可用端口的動(dòng)態(tài)列表,當(dāng)指定的端口不可用時(shí),管理進(jìn)程將會(huì)從列表中選擇一個(gè)可用的端口,源端和目標(biāo)段的Collector、Replicat、GGSCI進(jìn)程通信也會(huì)使用這些端口;
COMMENT:注釋行,也可以用--來代替;
AUTOSTART:指定在管理進(jìn)程啟動(dòng)時(shí)自動(dòng)啟動(dòng)哪些進(jìn)程;
AUTORESTART:自動(dòng)重啟參數(shù)設(shè)置:本處設(shè)置表示每3分鐘嘗試重新啟動(dòng)所有EXTRACT進(jìn)程,共嘗試5次;
PURGEOLDEXTRACTS:定期清理trail文件設(shè)置:本處設(shè)置表示對(duì)于超過3天的trail文件進(jìn)行刪除。
LAGREPORT、LAGINFO、LAGCRITICAL:
定義數(shù)據(jù)延遲的預(yù)警機(jī)制:本處設(shè)置表示MGR進(jìn)程每隔1小時(shí)檢查EXTRACT的延遲情況,如果超過了30分鐘就把延遲作為信息記錄到錯(cuò)誤日志中,如果延遲超過了45分鐘,則把它作為警告寫到錯(cuò)誤日志中。
add extract extfull, tranlog,begin now
edit param extfull
extract extfull
setenv ( NLS_LANG = " AMERICAN_AMERICA.ZHS16GBK " )------添加報(bào)錯(cuò)
TRANLOGOPTIONS ASMUSERSYS@ASM, ASMPASSWORD oracle
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY80000 IOLATENCY 160000
DBOPTIONS ALLOWUNUSEDCOLUMN
userid goldengate, password goldengate
ddl include mapped
ddloptions addtrandata RETRYOP MAXRETRIES1000 RETRYDELAY 10, REPORT
WARNLONGTRANS 1h, CHECKINTERVAL 5m
exttrail /ggs/dirdat/sd
gettruncates
dynamicresolution
NOCOMPRESSUPDATES
NOCOMPRESSDELETES
table LM.testogg;
add exttrail /u01/app/oracle/ogg/dirdat/sd, extractextfull, MEGABYTES 50
添加傳輸進(jìn)程
addextract dpfull exttrailsource /ggs/dirdat/sd
創(chuàng)建遠(yuǎn)程隊(duì)列文件并將其指定給傳輸進(jìn)程
addrmttrail /u01/app/oracle/ogg/td, extract dpfull, MEGABYTES 50
配置傳輸進(jìn)程參數(shù)
editparam dpfull
==================================
extractdpfull
passthru
rmthost 186.168.100.22, mgrport 7809
rmttrail /ggs/dirdat/td
gettruncates
table LM.testogg;
dblogin userid ogg password ogg
GGSCI (oggsource as ogg@dbdream) 11>start mgr
Manager started.
查看進(jìn)程狀態(tài)可發(fā)現(xiàn) MANAGER狀態(tài)為 RUNNING:
GGSCI(NDSCDB1) 3> info all
/u01/app/oracle/ogg/dirrpt
GGSCI (oggsource as ogg@dbdream) 12> addextract extnd,tranlog,begin now
EXTRACT added.
GGSCI (oggsource as ogg@dbdream) 13> addexttrail ./dirdat/nd,extract extnd,megabytes 100
EXTTRAIL added.
Megabytes:指定隊(duì)列大小,本處設(shè)置表示100M。
添加傳輸進(jìn)程,配置參數(shù)
GGSCI(NDSCDB1) 15> edit params extnd
EXTRACT extnd
setenv(NLS_LANG ="AMERICAN_AMERICA.ZHS16GBK")
SETENV(ORACLE_HOME ="/u01/app/oracle/product/11.2.0/db_1")
SETENV(ORACLE_SID ="dbdream")
USERID ogg, PASSWORD ogg
--GETTRUNCATES
REPORTCOUNTEVERY 1 MINUTES, RATE
DISCARDFILE./dirrpt/extnd.dsc,APPEND,MEGABYTES1024
--THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000 IOLATENS60000
DBOPTIONS ALLOWUNUSEDCOLUMN
WARNLONGTRANS2h,CHECKINTERVAL 3m
EXTTRAIL./dirdat/nd
--TRANLOGOPTIONSEXCLUDEUSER USERNAME
FETCHOPTIONSNOUSESNAPSHOT
TRANLOGOPTIONS CONVERTUCS2CLOBS
TABLE olive.ol$_objects
GGSCI(NDSCDB1) 15>add extract dpend,exttrailsource ./dirdat/nd
EXTRACT added.
GGSCI(NDSCDB1) 15>add rmttrail /u01/app/oracle/ogg/nd, EXTRACT DPEND
RMTTRAIL added.
edit params dpend
EXTRACT dpend
SETENV(NLS_LANG ="AMERICAN_AMERICA.ZHS16GBK")
USERID ogg, PASSWORD ogg
PASSTHRU
RMTHOST10.122.0.113, MGRPORT 7839, compress
RMTTRAILF:/u01/app/oracle/ogg/dirdat/nd
TABLE olive.ol$_objects;
抽取進(jìn)程和傳輸進(jìn)程其實(shí)都是EXTRACT進(jìn)程,也可以配置在一個(gè)進(jìn)程完成這兩個(gè)功能,但是當(dāng)網(wǎng)絡(luò)傳輸有問題時(shí),這樣抽取也就不能繼續(xù)運(yùn)行了,所以推薦分開配置為兩個(gè)進(jìn)程;
EXTRACT進(jìn)程參數(shù)配置說明:
SETENV:配置系統(tǒng)環(huán)境變量
USERID/ PASSWORD:指定OGG連接數(shù)據(jù)庫(kù)的用戶名和密碼,這里使用3.4部分中創(chuàng)建的數(shù)據(jù)庫(kù)用戶OGG;
COMMENT:注釋行,也可以用--來代替;
TABLE:定義需復(fù)制的表,后面需以;結(jié)尾
TABLEEXCLUDE:定義需要排除的表,如果在TABLE參數(shù)中使用了通配符,可以使用該參數(shù)指定排除掉得表。
GETUPDATEAFTERS|IGNOREUPDATEAFTERS:
是否在隊(duì)列中寫入后影像,缺省復(fù)制
GETUPDATEBEFORES| IGNOREUPDATEBEFORES:
是否在隊(duì)列中寫入前影像,缺省不復(fù)制
GETUPDATES|IGNOREUPDATES:
是否復(fù)制UPDATE操作,缺省復(fù)制
GETDELETES|IGNOREDELETES:
是否復(fù)制DELETE操作,缺省復(fù)制
GETINSERTS|IGNOREINSERTS:
是否復(fù)制INSERT操作,缺省復(fù)制
GETTRUNCATES|IGNORETRUNDATES:
是否復(fù)制TRUNCATE操作,缺省不復(fù)制;
RMTHOST:指定目標(biāo)系統(tǒng)及其GoldengateManager進(jìn)程的端口號(hào),還用于定義是否使用壓縮進(jìn)行傳輸,本例中的compress為壓縮傳輸;
RMTTRAIL:指定寫入到目標(biāo)斷的哪個(gè)隊(duì)列;
EXTTRAIL:指定寫入到本地的哪個(gè)隊(duì)列;
SQLEXEC:在extract進(jìn)程運(yùn)行時(shí)首先運(yùn)行一個(gè)SQL語(yǔ)句;
PASSTHRU:禁止extract進(jìn)程與數(shù)據(jù)庫(kù)交互,適用于DataPump傳輸進(jìn)程;
REPORT:定義自動(dòng)定時(shí)報(bào)告;
STATOPTIONS:定義每次使用stat時(shí)統(tǒng)計(jì)數(shù)字是否需要重置;
REPORTCOUNT:報(bào)告已經(jīng)處理的記錄條數(shù)統(tǒng)計(jì)數(shù)字;
TLTRACE:打開對(duì)于數(shù)據(jù)庫(kù)日志的跟蹤日志;
DISCARDFILE:定義discardfile文件位置,如果處理中油記錄出錯(cuò)會(huì)寫入到此文件中;
DBOPTIONS:指定對(duì)于某種特定數(shù)據(jù)庫(kù)所需要的特殊參數(shù);
TRANLOGOPTIONS:指定在解析數(shù)據(jù)庫(kù)日志時(shí)所需要的特殊參數(shù),例如:對(duì)于裸設(shè)備,可能需要加入以下參數(shù) rawdeviceoggset 0
WARNLONGTRANS:指定對(duì)于超過一定時(shí)間的長(zhǎng)交易可以在gsserr.log里面寫入警告信息,本處配置為每隔3分鐘檢查一次場(chǎng)交易,對(duì)于超過2小時(shí)的進(jìn)行警告;~
~
~
備庫(kù):
1.創(chuàng)建ogg用戶并授權(quán)
create user goldengatet identified bygoldengatet default tablespace tbs_ogg;
grant dba to ogg;
execdbms_streams_auth.grant_admin_privilege(grantee => 'ogg',grant_privileges=> true);
ALTER SYSTEM SETENABLE_GOLDENGATE_REPLICATION = TRUE SCOPE=BOTH;
2.配置環(huán)境變量(oracle用戶下)
export LD_LIBRARY_PATH
export PATH=
GGSCI (oggtarget) 1> create subdirs
Creating subdirectories under currentdirectory /u01/app/oracle/ogg
Parameter files /u01/app/oracle/ogg/dirprm:already exists
Report files /u01/app/oracle/ogg/dirrpt:already exists
Checkpoint files /u01/app/oracle/ogg/dirchk:already exists
Process status files /u01/app/oracle/ogg/dirpcs: alreadyexists
SQL script files /u01/app/oracle/ogg/dirsql:already exists
Database definitions files /u01/app/oracle/ogg/dirdef: already exists
Extract data files /u01/app/oracle/ogg/dirdat:already exists
Temporary files /u01/app/oracle/ogg/dirtmp:already exists
Credential store files /u01/app/oracle/ogg/dircrd: alreadyexists
Masterkey wallet files /u01/app/oracle/ogg/dirwlt: alreadyexists
Dump files /u01/app/oracle/ogg/dirdmp: already exists
SQL> create tablespace ogg datafile'/u01/app/oracle/oradata/dbdream/ogg.dbf' size 50M autoextend on;
SQL> create user ogg identified by oggdefault tablespace ogg;
grant connect,resource,unlimited tablespaceto ogg;
grant execute on utl_file to ogg;
grant select any dictionary,select anytable 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 delete any table to ogg;
grant update any table to ogg;
SQL> select NAME,OPEN_MODE,FORCE_LOGGING,SUPPLEMENTAL_LOG_DATA_MINfrom v$database;
SQL> alter database force logging;
SQL> alter database add SUPPLEMENTAL log data;
配置參數(shù)文件
MGR:
edit param mgr
PORT 7839
autostart replicat *
autorestart replicat *, waitminutes 1,retries 60, RESETMINUTES 60
PURGEOLDEXTRACTS/u01/app/oracle/ogg/dirdat/td*, USECHECKPOINTS, MINKEEPHOURS 2
dblogin userid goldengate,password goldengate
ADD CHECKPOINTTABLE goldengate.ckptfull
add replicat repfull, exttrail/u01/app/oracle/ogg/dirdat/td, CHECKPOINTTABLE ogg.ckptfull
edit params repfull
replicat repfull
setenv ( NLS_LANG = "AMERICAN_AMERICA.ZHS16GBK" )
assumetargetdefs
userid ogg, password ogg
DBOPTIONS DEFERREFCONST, SUPPRESSTRIGGERS
gettruncates
ALLOWNOOPUPDATES
ddl include mapped
discardfile ./dirrpt/repfull.dsc, append,megabytes 4000
map scott.t1, target scott.t1;
GGSCI (oggtarget as ogg@stream) 74>start mgr
GGSCI (oggtarget as ogg@stream) 75> infoall
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING REPFULL 00:00:00 00:00:02
參數(shù)介紹:
REPLICAT RINI_1:說明這是REPLICAT應(yīng)用進(jìn)程,名字叫RINI_1
SETENV:語(yǔ)言變量,同捕獲進(jìn)程EINI_1
ASSUMETARGETDEFS:告訴OGG目標(biāo)端和源端需要同步的表的結(jié)構(gòu)完全一致,不需要OGG去檢查表的結(jié)構(gòu),包括表名、字段名、字段類型、字段長(zhǎng)度等,如果目標(biāo)端和源端同步的表的結(jié)構(gòu)不一樣,需要使用SOURCEDEFS參數(shù),詳見OGG官方文檔。
USERID、PASSWORD:同捕獲進(jìn)程EINI_1參數(shù)介紹
DISCARDFILE:錯(cuò)誤信息存放位置及命名規(guī)則
MAP:源端捕獲的表的名字
TARGET:目標(biāo)端同步的表的名字,可以不在同一SCHEMA。
info all---------------------檢查進(jìn)程
info 進(jìn)程名,detail
stats REPFULL-----------------------------檢查數(shù)據(jù)傳輸狀態(tài)
view report 進(jìn)程名
數(shù)據(jù)庫(kù)登錄檢查數(shù)據(jù)是否有變化
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場(chǎng),如果涉及侵權(quán)請(qǐng)聯(lián)系站長(zhǎng)郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。