OGG安裝測試

一 檢查生產庫




1、建立附加日誌：
SQL> select supplemental_log_data_min from v$database;


SUPPLEME
--------
YES                       -------------若不是YES，需要開啟


開啟語句：
alter database add supplemental log data;
ALTER SYSTEM SWITCH LOGFILE;


2、開啟force log
SQL> select force_logging from v$database;


FOR
---
YES


SQL> ALTER DATABASE FORCE LOGGING;


3、查詢壓縮表
select owner,table_name from dba_tables where owner = 'HOSTDB' and compression='ENABLED';
select table_owner,table_name from dba_tab_partitions where table_owner = 'HOSTDB' and  compression='ENABLED';


4、
查詢不支援的列的表格
select owner,table_name,column_name,data_type from dba_tab_columns where owner = 'HOSTDB' and data_type in ('ANYDATA','ANYDATASET','ANYTYPE','BFILE','BINARY_INTEGER','MLSLABEL','PLS_INTEGER','TIMEZONE_ABBR','TIMEZONE_REGION','URITYPE,UROWID');


5、
select owner,table_name from dba_tables where owner='HOSTDB' and logging='NO' and temporary='N';
alter table INTERP.tt1291 logging;


6、
--建立管理使用者
SQL> create tablespace odc_tps datafile '/oradata/c5cdb/odc01.dbf' size 100M autoextend on;


SQL> create user odc identified by odc default tablespace odc_tps;


--授權，DBA許可權在安裝成功後可以收回
GRANT CONNECT TO odc;
GRANT ALTER ANY TABLE TO odc;
GRANT ALTER SESSION TO odc;
GRANT CREATE SESSION TO odc;
GRANT FLASHBACK ANY TABLE TO odc;
GRANT SELECT ANY DICTIONARY TO odc;
GRANT SELECT ANY TABLE TO odc;
GRANT RESOURCE TO odc;
GRANT DBA TO odc;
--安裝成功後可以收回dba許可權，但是要授予UNLIMITED TABLESPACE許可權。
GRANT UNLIMITED TABLESPACE TO odc;
或者alter user odc quota unlimited on users;




--在ddl_setup時報錯，通過授予以下許可權解決。
grant create any table to odc;
grant create any view to odc;
grant create any procedure to odc;
grant create any sequence to odc;
grant create any index to odc;
grant create any trigger to odc;
grant create any view to odc;




二 安裝軟體（生產和備份端安裝步驟一樣，只有軟體及環境變數可能不一樣）
1、建立安裝目錄或檔案系統，例如/odc
2、配置library環境變數
HP-UX : SHLIB_PATH
AIX : LIBPATH
LINUX :　LD_LIBRARY_PATH
例如：
export LD_LIBRARY_PATH=/opt/odc:$LD_LIBRARY_PATH
export LD_LIBRARY_PATH=/opt/odc:$ORACLE_HOME/lib:$LD_LIBRARY_PATH


3、安裝軟體
$ cd /odc
$ tar xf ggs_AIX_ppc_ora11g_64bit_v11_1_1_0_11_001.tar


./ggsci
ggsci>create subdirs


ggsci>edit param mgr
輸入：
port 7809
PURGEOLDEXTRACTS /bmsdata/odc/dirdat/yt*, USECHECKPOINTS, MINKEEPHOURS 24




ggsci>edit param ./GLOBALS
輸入：
GGSCHEMA odc
CHECKPOINTTABLE odc.ggs_checkpoint ---儲存退出 


start mgr


三 生產庫安裝DDL相關元件
1. 確定DDL SCHEMA
需要配置在GLOBALS中：GGSCHEMA odc 
2. 
GRANT EXECUTE ON UTL_FILE TO odc;
3.
為odc_DDL_HIST、 odc_MARKER等表準備表空間，如果空間不足，會導致資料庫無法進行DDL，從而影響業務。
alter user odc default tablespace QUERY_SERVER;


4. 退出所有ORACLE會話，不允許新會話產生
5.sqlplus "/as sysdba"            ----必須sysdba連入


6.@marker_setup


7. @ddl_setup
  該指令碼會提示你所有開啟的會話，要求關閉。
  提示輸入DDL SCHEMA；odc
  選擇安裝型別：INITIALSETUP


8. @role_setup 
    提示你執行一條GRANT語句，執行即可！
9. @ddl_enable.sql


提高DDL效能指令碼： -----需要dbms_shared_pool包
SQL> @ddl_pin odc




四、 為表格新增supplement log


dblogin userid odc@ytyy password odc   
add trandata interp.*




五、 建立挖掘程式
add extract yt_ext tranlog begin now
add exttrail ./dirdat/yt extract yt_ext


edit param yt_ext


引數：
extract yt_ext


setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid odc@ytyy,password odc
exttrail ./dirdat/yt 
tranlogoptions altarchivelogdest /bak9i/arch
FETCHOPTIONS FETCHPKUPDATECOLS
ddl include objname interp.* exclude objtype 'TRIGGER'
ddloptions addtrandata
table interp.*;
tableexclude interp.SYS_EXPORT_SCHEMA* ;
tableexclude interp.dbms_tabcomp_temp* ;


start yt_ext


--觀察，能夠挖掘再繼續下面的操作




六、增長pump程式
add extract yt_dmp EXTTRAILSOURCE ./dirdat/yt
ADD RMTTRAIL ./dirdat/yt, EXTRACT yt_dmp


edit param yt_dmp


引數：
extract yt_dmp
rmthost 10.18.120.33, mgrport 7809
rmttrail ./dirdat/yt
passthru
table interp.* ;


start yt_dmp


./extract paramfile ./dirprm/yt_dmp.prm reportfile ./dirrpt/yt_dmp.rpt 2>&1 &




七、備份庫建立使用者


SQL> create tablespace odc_tps datafile '/data/oradata/ytyyogg/odc01.dbf' size 100M autoextend on;


Tablespace created.


SQL> create user odc identified by odc default tablespace odc_tps;


GRANT CONNECT TO odc;
GRANT ALTER ANY TABLE TO odc;
GRANT ALTER SESSION TO odc;
GRANT CREATE SESSION TO odc;
GRANT FLASHBACK ANY TABLE TO odc;
GRANT SELECT ANY DICTIONARY TO odc;
GRANT SELECT ANY TABLE TO odc;
GRANT RESOURCE TO odc;
GRANT DBA TO odc;


八、備份庫建立checkpoint table


cd /opt/odc
$ sqlplus odc/odc@ytyyogg
SQL> @chkpt_ora_create.sql
DROP TABLE ggs_checkpoint
           *
ERROR at line 1:
ORA-00942: table or view does not exist


Table created.




九、初始化同步
1、
--生產端查詢表格所在表空間
select distinct tablespace_name from dba_tables where owner  in ('INTERP');
TABLESPACE_NAME
------------------------------
USERS
INTERP
select distinct tablespace_name from dba_tab_partitions where table_owner  in ('INTERP');


--備份端查詢是否存在以上表空間，若沒有則建立，如果不想建立，在後面的impdp中需要配置remap_tablespace引數。


--生產端索引所在表空間
select distinct tablespace_name from dba_indexes where owner in ('INTERP');
TABLESPACE_NAME
------------------------------
USERS
INTERP


select distinct tablespace_name from dba_ind_partitions where index_owner in ('INTERP');


--生產端使用者預設臨時表空間：
select distinct temporary_tablespace from dba_users
TEMPORARY_TABLESPACE
------------------------------
TEMP




2、
檢查undo保留時間及表空間大小
SQL> show parameter undo
SQL> select tablespace_name,sum(bytes/1024/1024) from dba_data_files group by tablespace_name; 
SQL> select tablespace_name,sum(bytes/1024/1024) from dba_free_space group by tablespace_name;


3、備份庫建立一個連線到生產端得DBLINK
create database link oralink connect to odc identified by odc using 'YTYY';


4、生產端查詢SCN
select to_char(current_scn) from v$database;
9874606502


export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK
expdp odc/odc@ytyy directory




5、目標端做
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK


impdp odc/odc network_link=oralink schemas=INTERP flashback_scn=9874606502 logfile=imp_tmp1.log


--匯入前檢查目標端表空間的大小
select segment_type,count(*),sum(bytes/1024/1024) from dba_segments  where owner='BSRUN' group by segment_type;






6、
備份庫禁用觸發器：
select 'alter trigger '||owner||'.'||trigger_name||' disable;' from dba_triggers where owner='INTERP' and status='ENABLED';


alter trigger INTERP.BMS_MV_ALT_AUTOFILL disable;
alter trigger INTERP.BMS_PRESENTOUT_QTY_AUTOFILL disable;
alter trigger INTERP.BMS_STI_DOC_AUTOFILL disable;
alter trigger INTERP.BMS_STI_DTL_AUTOFILL disable;
alter trigger INTERP.BMS_STO_DOC_AUTOFILL disable;
alter trigger INTERP.BMS_STO_DTL_AUTOFILL disable;
alter trigger INTERP.BMS_STO_DTL_CHECK disable;
alter trigger INTERP.BMS_ST_IO_DOC_TRIGGER disable;
alter trigger INTERP.BMS_ST_IO_DTL_TMP_TRIGGER disable;
alter trigger INTERP.BMS_ST_IO_DTL_TRIGGER disable;
alter trigger INTERP.BMS_ST_QTY_AUTOFILL disable;
alter trigger INTERP.BMS_TR_DTL_TRIGGER disable;
alter trigger INTERP.GSP_CHK_TO_SHPTYP_TRI disable;
alter trigger INTERP.GSP_CONFILE_TO_QUFILE disable;
alter trigger INTERP.GSP_MDCHK_TO_SHPKND_TRI disable;
alter trigger INTERP.PUB_GOODS_UNITCHANGE_TRI disable;
alter trigger INTERP.ZX_BMS_DEMATIC_CR_TRG disable;
alter trigger INTERP.ZX_BMS_IMSEVENT_REDUND_TR disable;
alter trigger INTERP.ZX_BMS_SA_RECENTBUSI_TRG disable;
alter trigger INTERP.ZX_BMS_SFDAIOUP_TRA_TRG disable;
alter trigger INTERP.ZX_BMS_SFDATOCOMPANY_CR_TR disable;
alter trigger INTERP.ZX_BMS_STREALQTY_IOTMP_TRG disable;
alter trigger INTERP.ZX_BMS_STREALQTY_RGPLAN_TRG disable;
alter trigger INTERP.ZX_BMS_SUCON_RECENTBUSI_TRG disable;
alter trigger INTERP.ZX_BMS_WOCOLLECT_STATUS_TRG disable;
alter trigger INTERP.ZX_LOG_COMPANYCUSTOM_TRG disable;
alter trigger INTERP.ZX_LOG_COMPANYSUPPLY_TRG disable;
alter trigger INTERP.ZX_LOG_EXAMCUSTOM_TRG disable;
alter trigger INTERP.ZX_LOG_GOODSLOT_TRG disable;
alter trigger INTERP.ZX_LOG_GOODSPACK_TRG disable;
alter trigger INTERP.ZX_LOG_SYSAPEMP_TRG disable;
alter trigger INTERP.ZX_LOG_SYSAPEXT_TRG disable;
alter trigger INTERP.ZX_LOG_SYSAPOP_TRG disable;
alter trigger INTERP.ZX_LOG_SYSAPROLE_TRG disable;
alter trigger INTERP.ZX_MVPOSIDPRIT_TRG disable;
alter trigger INTERP.ZX_LOG_GSPLICENSE_TRG disable;
alter trigger INTERP.ZX_LOG_GSPGOODSLICENSE_TRG disable;
alter trigger INTERP.ZX_LOG_COMPANYBASE_TRG disable;
alter trigger INTERP.ZX_LOG_GOODSBASE_TRG disable;
alter trigger INTERP.ZX_DEMATIC2YTYY_TR disable;
alter trigger INTERP.ZX_LOG_GSPLICENSE_TRG disable;




移除JOB：
select job,log_user,schema_user from dba_jobs;
exec dbms_job.remove();
exec dbms_ijob.remove(21);


select * from dba_scheduler_jobs where owner= 'INTERP';
禁用：
DBMS_SCHEDULER.DISABLE('job1, job2, job3, sys.jobclass1, sys.jobclass2');
或刪除：
DBMS_SCHEDULER.DROP_JOB (job_name => 'my_job1');


十、建立replicat程式


dblogin userid odc@ytyyogg password odc
add replicat yt_rep exttrail ./dirdat/yt


edit param yt_rep


引數：
replicat yt_rep
setenv (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
userid odc@ytyyogg, password odc
--handlecollisions
ASSUMETARGETDEFS
ALLOWNOOPUPDATES
DBOPTIONS DEFERREFCONST
HANDLETPKUPDATE
--batchsql
ddlerror 955 ignore
ddlerror 1917 ignore
ddlerror 24344 ignore
ddl include mapped
DISCARDFILE ./dirrpt/yt.dsc, APPEND megabytes 20
DISCARDROLLOVER on sunday
--DISCARDROLLOVER
--ddlerror 942 ignore
map INTERP.* target INTERP.* ;








十一、在初始化完成後啟動yt_rep


start yt_rep atcsn 9874606502

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25427762/viewspace-1128062/，如需轉載，請註明出處，否則將追究法律責任。