生產端為一套RAC，容災端為單庫，資料量幾個TB，異地容災，資料透過異地硬碟傳輸，網路速度太慢
0、備份原來的/goldengate目錄，包括備份引數檔案
tar -cvf ogg.tar /goldengate
cp /goldengate/dirprm /tmp/dirprm
cp GLOBALS /tmp/GLOBALS
$ more GLOBALS
GGSCHEMA goldengate
0.1、解除安裝原來的DDL配置
如果之前曾經安裝過ddl，需要重新進行安裝，則需要先將ddl的一些元件解除安裝掉，然後再重新安裝：
依次在OGG的根目錄執行
1)切換到Goldengate安裝目錄下，並且按照順序以SYS使用者依次執行以下指令碼：
2)SQLPLUS SYS/ORACLE@ORCL @./ddl_disable.sql
3)SQL>/goldengate/ddl_remove.sql
4)SQL>/goldengate/marker_remove.sql
注：在執行上述指令碼的時候，會提示輸入安裝時所指定的schema名稱。
0.2、刪除原來的OGG軟體
rmdir /goldengate
mkdir /goldengate
1、安裝OGG軟體
ftp 192.168.2.3
bin
prompt
cd
put ggs11.2.1.0.4_HPUX_ia64_ora10g_64bit.tar
透過SRT登入到192.168.2.3
su - oracle
cd /goldengate
tar -xvf ggs11.2.1.0.4_HPUX_ia64_ora10g_64bit.tar
./ggsci
create subdirs
2、資料庫建表空間和使用者
create tablespace goldengate datafile '/xx/goldengate01.dbf' size 10240M ; -----不需要做
create tablespace goldengate datafile '/dev/vgdlwwqxp2/rvgdlwwqxp_20g_469' size 10240M ; -----不需要做
create user goldengate identified by password_10 default tablespace goldengate quota unlimited on goldengate; -----不需要做
grant CONNECT, RESOURCE ,DBA to goldengate;-----不需要
3、資料庫開歸檔模式和強制日誌模式
Select log_mode from v$database;
開啟歸檔模式：
shutdown immediate; -----不需要
startup mount; -----不需要
alter database archivelog;-----不需要
alter database open; -----不需要

檢查強制日誌情況，使用以下SQL語句：
SELECT force_logging FROM v$database;
開啟強制日誌模式：
alter database force logging;
4、資料庫開附加日誌
檢查附加日誌情況,使用以下sql語句檢查資料庫附加日誌的開啟狀況:
Select
SUPPLEMENTAL_LOG_DATA_MIN
,SUPPLEMENTAL_LOG_DATA_PK
,SUPPLEMENTAL_LOG_DATA_UI
,SUPPLEMENTAL_LOG_DATA_FK
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
開啟資料庫附加日誌,開啟附加日誌並切換日誌（保證Online redo log和Archive log一致）
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 ;
alter database drop supplemental log data (primary key, unique,foreign key) columns;

確認附加日誌情況,使用以下sql語句檢查資料庫附加日誌的開啟狀況:
Select
SUPPLEMENTAL_LOG_DATA_MIN
,SUPPLEMENTAL_LOG_DATA_PK
,SUPPLEMENTAL_LOG_DATA_UI
,SUPPLEMENTAL_LOG_DATA_FK
,SUPPLEMENTAL_LOG_DATA_ALL from v$database;
注：確保最小附加日誌,pk,uk,fk附加日誌開啟。而all columns的附加日誌關閉；
如果all columns的附加日誌開啟的話，則需要使用以下語句予以關閉：
alter database drop supplemental log data (ALL) columns;
5、關閉資料庫的recyclebin (僅實施DDL時進行配置)
SQL> show parameter recyclebin
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
recyclebin string OFF
SQL>
如不是off，需要關閉recyclebin：
SQL>alter system set recyclebin=off scope=both;
如果需要回退，開啟recyclebin的話，可以使用以下語句：
SQL>alter system set recyclebin=off scope=both;
6、開表級補全日誌（僅實施DML時進行配置，開啟DDL複製不需要做）
./ggsci
DBLOGIN USERID goldengate, PASSWORD password_10
ADD TRANDATA sgwwqxp.*
INFO TRANDATA SGwwqxp.A_RCVBL_ENTRY
ADD TRANDATA SGwwqxp.A_RCVBL_ENTRY
delete trandata SGwwqxp.A_RCVBL_ENTRY
ADD TRANDATA SGwwqxp.A_RCVBL_ENTRY
7、編輯GLOBALS引數檔案
這裡直接建立在goldengate使用者下：
Ggsci>EDIT PARAMS ./GLOBALS --在該檔案中新增以下內容
GGSCHEMA goldengate --指定的進行DDL複製的資料庫使用者
8、配置管理程式
./ggsci
ENCRYPT PASSWORD password_10 blowfish encryptkey default ----獲取OGG密文密碼
AACAAAAAAAAAAANAPATACEHBIGQGCFZCOBTATGXCDGTIUISJ
./ggsci
EDIT PARAMS MGR
PORT 7839
DYNAMICPORTLIST 7840-7914
USERID goldengate, PASSWORD AACAAAAAAAAAAANAPATACEHBIGQGCFZCOBTATGXCDGTIUISJ, ENCRYPTKEY default ---密碼需要重新生成
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 7
PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
start mgr
info all
9、配置抽取程式
./ggsci
DBLOGIN USERID goldengate, PASSWORD password_10
EXTRACT extoa
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID goldengate, PASSWORD AACAAAAAAAAAAANAPATACEHBIGQGCFZCOBTATGXCDGTIUISJ, ENCRYPTKEY default
GETTRUNCATES
DDL &
INCLUDE MAPPED OBJTYPE 'table' &
INCLUDE MAPPED OBJTYPE 'index' &
EXCLUDE OPTYPE COMMENT
DDLOPTIONS NOCROSSRENAME REPORT
DDLOPTIONS ADDTRANDATA REPORT
REPORTCOUNT EVERY 1 MINUTES, RATE
DISCARDFILE ./dirrpt/extoa.dsc,APPEND,MEGABYTES 1024
THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 90000 IOLATENCY 180000
DBOPTIONS ALLOWUNUSEDCOLUMN
WARNLONGTRANS 2h,CHECKINTERVAL 3m
EXTTRAIL ./dirdat/oa
TRANLOGOPTIONS EXCLUDEUSER goldengate
FETCHOPTIONS USESNAPSHOT
TRANLOGOPTIONS CONVERTUCS2CLOBS
TRANLOGOPTIONS altarchivelogdest primary instance dcwwqxp1 /backup1 altarchivelogdest instance dcwwqxp2 /backup2
table CIM.*;
table EPBI.*;
table EPBI_DXP.*;
table PSDSS_BUFF.*;
table PSDSS_DCHK.*;
table PSDSS_JCJK.*;
table PSDSS_MID.*;
table PSDSS_MID_1.*;
table wwqxpYQ.*;
table MW_ANHUI.*;
table MW_IQ.*;
table MW_ZTK.*;
table MW_APP.*;
table MW_RTM.*;
table MW_SYS.*;
table STAT.*;
新增相應的程式和佇列檔案
add extract extoa,tranlog, threads 2,begin now
add exttrail ./dirdat/oa,extract extoa,megabytes 100
start extsa
info all
info *
10、配置傳輸程式
./ggsci
DBLOGIN USERID goldengate, PASSWORD password_10
extract dpeoa
passthru
rmthost 192.168.2.18, mgrport 7839,compress
rmttrail ./dirdat/oa
table CIM.*;
table EPBI.*;
table EPBI_DXP.*;
table PSDSS_BUFF.*;
table PSDSS_DCHK.*;
table PSDSS_JCJK.*;
table PSDSS_MID.*;
table PSDSS_MID_1.*;
table wwqxpYQ.*;
table MW_ANHUI.*;
table MW_IQ.*;
table MW_ZTK.*;
table MW_APP.*;
table MW_RTM.*;
table MW_SYS.*;
table STAT.*;
新增相應的程式和佇列檔案
add ext dpeoa,exttrailsource ./dirdat/oa
add rmttrail ./dirdat/oa,ext dpeoa,megabytes 100
start dpeoa
info all
info *
傳輸程式需要等到容災端管理程式配置好才可以啟動
11、配置DDL複製
OGG的DDL物件安裝時不能有執行的sessoin存在，請DBA執行停機清理或者殺死所有資料庫中的Session。
建議有條件先執行停止業務，並關閉Oracle的Listener。
注：時間大約需要30分鐘左右，在這段時間需要完成下面所有操作步驟。此後的所有步驟，都可以在業務正常執行時進行操作。
./ggsci
stop mgr
stop extsa
stop dpesa
---在安裝DDL指令碼時，應該停止所有OGG程式---
sqlplus "/ as sysdba"
SQL> @marker_setup.sql
Enter GoldenGate schema name:goldengate
SQL> @ddl_setup.sql
Enter GoldenGate schema name:goldengate
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 <loggedUser>
where <loggedUser> is the user assigned to the GoldenGate processes.
注意這裡的提示：需要手工將這個GGS_GGSUSER_ROLE指定給extract所使用的資料庫使用者（即引數檔案裡面透過userid指定的使用者），可以到sqlplus下執行類似的sql:
GRANT GGS_GGSUSER_ROLE TO goldengate;
注：這裡的goldengate是extract使用的使用者。如果你有多個extract，使用不同的資料庫使用者，則需要重述以上過程全部賦予GGS_GGSUSER_ROLE許可權。
執行以下指令碼，使觸發器生效
SQL> @ddl_enable.sql
啟動OGG程式
start mgr
start *
9、RMAN備份資料庫
備份資料檔案
more rman.sh
rman target / cmdfile=/oggtmp/rmanbak/backdatafile.sql
more backdatafile.sql
run
{
allocate channel d1 type disk maxpiecesize 10G;
allocate channel d2 type disk maxpiecesize 10G;
allocate channel d3 type disk maxpiecesize 10G;
allocate channel d4 type disk maxpiecesize 10G;
allocate channel d5 type disk maxpiecesize 10G;
allocate channel d6 type disk maxpiecesize 10G;
allocate channel d7 type disk maxpiecesize 10G;
allocate channel d8 type disk maxpiecesize 10G;
backup format '/oggtmp/rmanbak/wwqxp_full_%s_%p.dbf' database;
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
release channel d7;
release channel d8;
}
備份歸檔檔案
SQL>set line 200
SQL>select * from Gv$log;
SQL> select inst_id,group#,thread#,sequence#,bytes,members,archived,status,to_char(FIRST_CHANGE#),FIRST_TIMe from Gv$log;
INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
2 1 1 7417 524288000 2 YES INACTIVE 12620301743415 03-JAN-14
2 2 1 7418 524288000 2 NO CURRENT 12620301747657 03-JAN-14
2 3 1 7415 524288000 2 YES INACTIVE 12620301742955 03-JAN-14
2 4 1 7416 524288000 2 YES INACTIVE 12620301743085 03-JAN-14
2 5 2 9185 524288000 2 NO CURRENT 12620301748197 03-JAN-14
2 6 2 9182 524288000 2 YES INACTIVE 12620301746544 03-JAN-14
2 7 2 9183 524288000 2 YES INACTIVE 12620301746591 03-JAN-14
2 8 2 9184 524288000 2 YES INACTIVE 12620301747299 03-JAN-14
1 1 1 7417 524288000 2 YES INACTIVE 12620301743415 03-JAN-14
1 2 1 7418 524288000 2 NO CURRENT 12620301747657 03-JAN-14
1 3 1 7415 524288000 2 YES INACTIVE 12620301742955 03-JAN-14
1 4 1 7416 524288000 2 YES INACTIVE 12620301743085 03-JAN-14
1 5 2 9185 524288000 2 NO CURRENT 12620301748197 03-JAN-14
1 6 2 9182 524288000 2 YES INACTIVE 12620301746544 03-JAN-14
1 7 2 9183 524288000 2 YES INACTIVE 12620301746591 03-JAN-14
1 8 2 9184 524288000 2 YES INACTIVE 12620301747299 03-JAN-14
SQL> set line 200
SQL> select inst_id,group#,thread#,sequence#,bytes,members,archived,status,to_char(FIRST_CHANGE#),FIRST_TIMe from Gv$log;
INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
2 1 1 7537 524288000 2 NO CURRENT 12620341389385 04-JAN-14
2 2 1 7534 524288000 2 YES ACTIVE 12620341384329 04-JAN-14
2 3 1 7535 524288000 2 YES ACTIVE 12620341386860 04-JAN-14
2 4 1 7536 524288000 2 YES ACTIVE 12620341387356 04-JAN-14
2 5 2 9253 524288000 2 YES ACTIVE 12620341370135 04-JAN-14
2 6 2 9254 524288000 2 YES ACTIVE 12620341377321 04-JAN-14
2 7 2 9255 524288000 2 YES ACTIVE 12620341386918 04-JAN-14
2 8 2 9256 524288000 2 NO CURRENT 12620341389031 04-JAN-14
1 1 1 7537 524288000 2 NO CURRENT 12620341389385 04-JAN-14
1 2 1 7534 524288000 2 YES ACTIVE 12620341384329 04-JAN-14
1 3 1 7535 524288000 2 YES ACTIVE 12620341386860 04-JAN-14

INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
1 4 1 7536 524288000 2 YES ACTIVE 12620341387356 04-JAN-14
1 5 2 9253 524288000 2 YES ACTIVE 12620341370135 04-JAN-14
1 6 2 9254 524288000 2 YES ACTIVE 12620341377321 04-JAN-14
1 7 2 9255 524288000 2 YES ACTIVE 12620341386918 04-JAN-14
1 8 2 9256 524288000 2 NO CURRENT 12620341389031 04-JAN-14

16 rows selected.
SQL>
INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
2 1 1 7537 524288000 2 NO CURRENT 12620341389385 04-JAN-14
2 2 1 7534 524288000 2 YES ACTIVE 12620341384329 04-JAN-14
2 3 1 7535 524288000 2 YES ACTIVE 12620341386860 04-JAN-14
2 4 1 7536 524288000 2 YES ACTIVE 12620341387356 04-JAN-14
2 5 2 9253 524288000 2 YES ACTIVE 12620341370135 04-JAN-14
2 6 2 9254 524288000 2 YES ACTIVE 12620341377321 04-JAN-14
2 7 2 9255 524288000 2 YES ACTIVE 12620341386918 04-JAN-14
2 8 2 9256 524288000 2 NO CURRENT 12620341389031 04-JAN-14
1 1 1 7537 524288000 2 NO CURRENT 12620341389385 04-JAN-14
1 2 1 7534 524288000 2 YES ACTIVE 12620341384329 04-JAN-14
1 3 1 7535 524288000 2 YES ACTIVE 12620341386860 04-JAN-14

INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
1 4 1 7536 524288000 2 YES ACTIVE 12620341387356 04-JAN-14
1 5 2 9253 524288000 2 YES ACTIVE 12620341370135 04-JAN-14
1 6 2 9254 524288000 2 YES ACTIVE 12620341377321 04-JAN-14
1 7 2 9255 524288000 2 YES ACTIVE 12620341386918 04-JAN-14
1 8 2 9256 524288000 2 NO CURRENT 12620341389031 04-JAN-14

16 rows selected.
SQL>
SQL> set line 200
SQL> select inst_id,group#,thread#,sequence#,bytes,members,archived,status,to_char(FIRST_CHANGE#),FIRST_TIMe from Gv$log;

INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
1 1 1 7541 524288000 2 YES INACTIVE 12620341953012 04-JAN-14
1 2 1 7542 524288000 2 YES INACTIVE 12620342069182 04-JAN-14
1 3 1 7543 524288000 2 YES ACTIVE 12620342187234 04-JAN-14
1 4 1 7544 524288000 2 NO CURRENT 12620342309187 04-JAN-14
1 5 2 9257 524288000 2 YES INACTIVE 12620341618880 04-JAN-14
1 6 2 9258 524288000 2 YES INACTIVE 12620341734410 04-JAN-14
1 7 2 9259 524288000 2 YES INACTIVE 12620341995892 04-JAN-14
1 8 2 9260 524288000 2 NO CURRENT 12620342199395 04-JAN-14
2 1 1 7541 524288000 2 YES INACTIVE 12620341953012 04-JAN-14
2 2 1 7542 524288000 2 YES INACTIVE 12620342069182 04-JAN-14
2 3 1 7543 524288000 2 YES ACTIVE 12620342187234 04-JAN-14

INST_ID GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS TO_CHAR(FIRST_CHANGE#) FIRST_TIM
---------- ---------- ---------- ---------- ---------- ---------- --- ---------------- ---------------------------------------- ---------
2 4 1 7544 524288000 2 NO CURRENT 12620342309187 04-JAN-14
2 5 2 9257 524288000 2 YES INACTIVE 12620341618880 04-JAN-14
2 6 2 9258 524288000 2 YES INACTIVE 12620341734410 04-JAN-14
2 7 2 9259 524288000 2 YES INACTIVE 12620341995892 04-JAN-14
2 8 2 9260 524288000 2 NO CURRENT 12620342199395 04-JAN-14

16 rows selected.
SQL>
12620341618880
獲取不活動的已歸檔日誌的最後一個SCN號,如果有多個已歸檔的INACTIVE的組，取最大的FIRST_CHANGE#，這裡取218412
more rmanlog.sh
rman target / cmdfile=/oggtmp/archbak/backarchlog.sql
more backarchlog.sql
run
{
allocate channel d1 type disk maxpiecesize 10G;
allocate channel d2 type disk maxpiecesize 10G;
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system switch logfile';
sql 'alter system archive log current';
backup format '/oggtmp/archbak/wwqxp_archive_%s_%p.dbf' archivelog from time "sysdate-2";
release channel d1;
release channel d2;
}
備份控制檔案
backup current controlfile format '/oggtmp/archbak/wwqxp_controlfil_%s_%p.dbf';
備份引數檔案
create pfile='/oggtemp/rmanbak/initwwqxp.0816' from spfile;
備份資料檔名稱
sqlplus /nolog
spool /oggtmp/0814/file0816.txt
select file_id,file_name from dba_data_files order by file_id asc;
spool /oggtmp/0814/tempfile0816.txt
select file_id,tablespace_name,file_name from dba_temp_files;
spool /oggtmp/0814/redofile0816.txt
select group#,status,member from gv$logfile;
10、ftp傳輸rman備份集到行動硬碟
ftp 192.168.2.3
bin
prmopt
get *.dbf
11、ftp傳輸rman備份集到災備伺服器
ftp 192.168.2.18
bin
prmopt
put *.dbf
12、恢復資料庫
1）恢復引數檔案
startup nomount pfile='/oracle/app/product/10.2.0/dbs/initpmdb.ora';
2）恢復控制檔案
restore controlfile from '/oggtmp/archbak/wwqxp_controlfil_%s_%p.dbf';
alter database mount;
3）註冊RMAN備份集
catalog backuppiece '/rmanbak/wwqxp_archivelog_24230_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_archivelog_24231_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_archivelog_24232_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_archivelog_24233_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_archivelog_24240_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_10.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_11.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_12.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_13.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_14.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_15.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_16.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_17.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_18.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_19.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_2.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_3.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_4.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_5.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_6.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_7.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_8.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24223_9.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_10.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_11.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_12.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_13.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_14.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_15.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_16.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_17.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_18.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_19.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_2.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_3.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_4.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_5.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_6.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_7.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_8.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24224_9.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_10.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_11.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_12.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_13.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_14.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_15.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_16.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_17.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_18.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_2.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_3.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_4.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_5.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_6.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_7.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_8.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24225_9.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24226_1.dbf';
catalog backuppiece '/rmanbak/wwqxp_full_24227_1.dbf';
4）、恢復資料檔案,dba_data_files輸出的資料檔案
run{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
allocate channel d6 type disk;
restore database;
switch datafile all;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
}
more recover.sh
rman target / cmdfile=/wwqxpdata_20131010/scripts/recoverfile.sql
run{
allocate channel d1 type disk;
allocate channel d2 type disk;
allocate channel d3 type disk;
allocate channel d4 type disk;
allocate channel d5 type disk;
allocate channel d6 type disk;
set until scn=12432504957403;
recover database;
release channel d1;
release channel d2;
release channel d3;
release channel d4;
release channel d5;
release channel d6;
}
6）更改redo日誌檔案路徑
7）更改臨時表空間檔案路徑
8）開啟資料庫
alter database open resetlogs;
9)容災端資料庫禁用JOB、觸發器、外來鍵
在dba_jobs中，有些job/schedule，會在複製物件中產生DML的操作，必須禁用掉，否則會造成DML語句在目標端的重複執行，
注：在目標端恢復的時候，可以在pfile檔案中將job_queue_processes設定為0，然後將帶有dml的job停掉以後，再修改job_queue_processes引數至正常值，如job_queue_processes=10；

declare
v_sql varchar2(2000);
CURSOR c_trigger IS SELECT 'alter trigger '||owner||'.'||trigger_name||' disable' from
dba_triggers where owner in (
'SGPM','SGPM_OUT','AMBER','WR_AMBER','OWF_MGR','CZK','KBSD', 'OCS'); -----使用者名稱需要改
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;
/
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 in
'SGPM','SGPM_OUT','AMBER','WR_AMBER','OWF_MGR','CZK','KBSD', 'OCS'); -----使用者名稱需要改
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;

-----------trigger-----------------------------
步驟九：禁用trigger
declare
v_sql varchar2(2000);
CURSOR c_trigger IS SELECT 'alter trigger '||owner||'.'||trigger_name||' disable' from dba_triggers
where owner in ('AMBER','CZK','KBSD','OCS','OWF_MGR','SGPM','SGPM_OUT','WF_AMBER'); -----使用者名稱需要改
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;
/

------------------job-----------------------
步驟十：禁用job
在配置pfile檔案時，將JOB_QUEUE_PROCESSES引數改為0.或執行以下包。
alter system set JOB_QUEUE_PROCESSES=0;
begin
exec dbms_job.broken(21,true);
end;
/
---------------scheduler------------------
步驟十一：禁用scheduler
declare
v_sql varchar2(2000);
CURSOR c_scheduler IS SELECT 'exec dbms_scheduler.drop_job(' || '''' || job_name || '''' || ');' from dba_scheduler_jobs
where owner in ('AMBER','CZK','KBSD','OCS','OWF_MGR','SGPM','SGPM_OUT','WF_AMBER'); -----使用者名稱需要改
BEGIN
OPEN c_scheduler;
LOOP
FETCH c_scheduler INTO v_sql;
EXIT WHEN c_scheduler%NOTFOUND;
--execute immediate v_sql;
dbms_output.put_line(v_sql);
end loop;
close c_scheduler;
end;
/
exec dbms_scheduler.drop_job('ZW_DXJK_JL_1');
-----------------外來鍵---------------------
步驟十二：禁用外來鍵
declare
v_sql varchar2(2000);
CURSOR c_cons IS SELECT 'alter table '||owner||'.'||table_name||' disable constraint '||constraint_name from
dba_constraints where constraint_type='R' and owner in ('AMBER','CZK','KBSD','OCS','OWF_MGR','SGPM','SGPM_OUT','WF_AMBER'); -----使用者名稱需要改
BEGIN
OPEN c_cons;
LOOP
FETCH c_cons INTO v_sql;
EXIT WHEN c_cons%NOTFOUND;
execute immediate v_sql;
end loop;
close c_cons;
end;
/
12、配置容災端資料庫
---這步在RMAN恢復生產庫時，已經恢復生產庫的表空間和使用者，不需要再做----
create tablespace goldengate datafile '/xx/goldengate01.dbf' size 10240M ;
create user goldengate identified by password_10 default tablespace goldengate quota unlimited on goldengate;
grant CONNECT, RESOURCE ,DBA to goldengate;
13、安裝OGG軟體、配置管理程式、複製程式
1）安裝OGG軟體
su - oracle
cd /goldengate
tar -xvf ggs_AIX_ia64_ora10g_64bit.tar
./ggsci
create subdirs
2)配置管理程式
./ggsci
EDIT PARAMS MGR
PORT 7839
DYNAMICPORTLIST 7840-7914
AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3
PURGEOLDEXTRACTS ./dirdat/*,usecheckpoints, minkeepdays 30
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45

start mgr
info all
3）配置複製程式
dblogin userid goldengate,password password_10
add replicat repya checkpointtable goldengate.checktablea,exttrail ./dirdat/ya
add checkpointtable goldengate.checktablea

edit params repya
REPLICAT repya
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
USERID goldengate, PASSWORD AACAAAAAAAAAAANAPATACEHBIGQGCFZCOBTATGXCDGTIUISJ, ENCRYPTKEY default
REPORT AT 01:59
REPORTCOUNT EVERY 30 MINUTES, RATE
REPERROR DEFAULT, discard
ASSUMETARGETDEFS
DISCARDFILE ./dirrpt/repya.dsc, APPEND, MEGABYTES 1024
DISCARDROLLOVER AT 02:30
GETTRUNCATES
ALLOWNOOPUPDATES
DBOPTIONS DEFERREFCONST
map SGwwqxp.* , target SGwwqxp.*;

start replicat repya,aftercsn ******

OGG實施操作步驟（包括操作過程中的所有步驟)

相關文章