-- =====================================================
-- == OGG 동기화 테이블 추가
-- == 대상 : SCOTT.STB_PROD_STOCK_OPT
-- =====================================================
-- == 1. supplemental logging (ogg에서)
-- == 2. extract 파라미터 대상 테이블선언
-- == 3. extract 재기동
-- == 4. 테이블 exp/imp scn
-- == 5. replicate 생성
-- == 6. scn base replicat start
-- == 7. replicat 병합
-- =====================================================
-- =====================================================
-- 소스 OGG
-- =====================================================
-- 대상 테이블 : SCOTT.STB_PROD_STOCK_OPT
-- == 1. supplemental logging (ogg에서)
-- 디비 로그인
GGSCI> dblogin UserID oraogg, password oraogg01
GGSCI> add trandata SCOTT.STB_PROD_STOCK_OPT
-- == 2. extract 파라미터 대상 테이블선언
-- dext001 파라미터에 SCOTT.STB_PROD_STOCK_OPT 테이블 추가
GGSCI (testdb02p as oraogg@TEST2) 19> edit param dext001
TABLE SCOTT.STB_PROD_STOCK_OPT;
GGSCI (testdb02p as oraogg@TEST2) 27> send dext001 showtrans
Sending SHOWTRANS request to EXTRACT DEXT001 ...
------------------------------------------------------------
XID: 0.3114.6.4282796
Items: 0
Extract: DEXT001
Redo Thread: 2
Start Time: 2022-01-13:13:10:49
SCN: 8795.3898902885 (37778136271205)
Redo Seq: 150728
Redo RBA: 8026364444
Status: Running
-- ogg extract 빠른 재기동을 위한 체크포인트 생성
GGSCI (testdb02p as oraogg@TEST2) 31> send dext001 br brcheckpoint immediate
GGSCI (testdb02p as oraogg@TEST2) 34> SEND EXTRACT DEXT001, FORCESTOP
-- == 3. extract 재기동
-- ogg extract 시작
GGSCI (testdb02p as oraogg@TEST2) 43> start dext001
GGSCI (testdb02p as oraogg@TEST2) 60> sh tail -f ./dirrpt/DEXT001.rpt
GGSCI (testdb02p as oraogg@TEST2) 63> stats zpmp001 table SCOTT.STB_PROD_STOCK_OPT
stats dext001 table SCOTT.STB_PROD_STOCK_OPT
-- =====================================================
-- expdp/impdp
-- =====================================================
-- == 4. 테이블 exp/imp scn
소스DB에서 해당 테이블을 scn 포함하여 expdp 수행
타겟DB에 remap_schema, remap_tablespace 사용하여 impdp 수행
-- TEST2 현재 scn 조회
SYS@TEST2> col CURRENT_SCN for 99999999999999999999999
SYS@TEST2> select current_scn from v$database;
CURRENT_SCN
------------------------
38106140088422
1 row selected.
-- TEST2 expdp
cd /DBMS/LFMP/BACKUP_PUMP_DIR
vi exp_ogg.par
-- ----------------------------------------------
DIRECTORY=BACKUP_PUMP_DIR
DUMPFILE=expogg.dmp
TABLES=SCOTT.STB_PROD_STOCK_OPT
FLASHBACK_SCN=38106140088422
-- ----------------------------------------------
expdp mon/mon PARFILE=exp_ogg.par
scp ./expogg.dmp oraREPLTEST@192.168.0.123:/DBMS/REPLTEST/dump/
-- REPLTEST impdp
cd /DBMS/REPLTEST/dump
vi imp_ogg.par
-- ----------------------------------------------
DIRECTORY=DIR1029
DUMPFILE=expogg.dmp
LOGFILE=impogg.log
REMAP_SCHEMA=(SCOTT:SCOTTREAL)
REMAP_TABLESPACE=(TS_TEST_DAT:TS_REAL_DAT ,TS_TEST_IDX:TS_REAL_DAT )
-- ----------------------------------------------
impdp mon/mon PARFILE=imp_ogg.par
-- =====================================================
-- 타겟 OGG
-- =====================================================
-- == 5. replicate 생성
-- 기존 replicate (이름 zrep011) 복제 하여 신규 replicate (이름 ztmp) 생성
retestdt:/DBMS/REPLTEST/ogg19c/dirprm> cp zrep011.prm ztmp.prm
GGSCI (retestdt) 4> add rep ztmp exttrail ./dirdat/zw
REPLICAT added.
-- 신규 replicate (이름 ztmp) 파라미터 수정
GGSCI (retestdt) 6> edit param ztmp
MAP SCOTT.STB_PROD_STOCK_OPT, TARGET SCOTTREAL.STB_PROD_STOCK_OPT;
GGSCI (retestdt) 34> sh ls -lrt ./dirdat/
total 22524288
-rw-r----- 1 oraREPLTEST dba 1023999912 Apr 3 17:43 zw000006421
-rw-r----- 1 oraREPLTEST dba 1023999687 Apr 3 17:43 zw000006422
-rw-r----- 1 oraREPLTEST dba 1023999977 Apr 3 18:43 zw000006423
-rw-r----- 1 oraREPLTEST dba 1023999820 Apr 3 18:44 zw000006424
-rw-r----- 1 oraREPLTEST dba 1023999944 Apr 3 19:44 zw000006425
-rw-r----- 1 oraREPLTEST dba 1023999839 Apr 3 19:51 zw000006426
-rw-r----- 1 oraREPLTEST dba 1023999759 Apr 3 20:03 zw000006427
-rw-r----- 1 oraREPLTEST dba 1023999979 Apr 3 21:40 zw000006428
-rw-r----- 1 oraREPLTEST dba 1023999760 Apr 3 22:05 zw000006429
-rw-r----- 1 oraREPLTEST dba 1023999941 Apr 4 00:33 zw000006430
-rw-r----- 1 oraREPLTEST dba 1023999979 Apr 4 01:53 zw000006431
-rw-r----- 1 oraREPLTEST dba 1023999866 Apr 4 02:50 zw000006432
-rw-r----- 1 oraREPLTEST dba 1023999646 Apr 4 04:13 zw000006433
-rw-r----- 1 oraREPLTEST dba 1023999758 Apr 4 07:43 zw000006434
-rw-r----- 1 oraREPLTEST dba 1023999824 Apr 4 07:43 zw000006435
-rw-r----- 1 oraREPLTEST dba 1023999767 Apr 4 08:02 zw000006436
-rw-r----- 1 oraREPLTEST dba 1023999924 Apr 4 08:03 zw000006437
-rw-r----- 1 oraREPLTEST dba 1023999962 Apr 4 08:39 zw000006438
-rw-r----- 1 oraREPLTEST dba 1023999749 Apr 4 08:43 zw000006439
-rw-r----- 1 oraREPLTEST dba 1023999985 Apr 4 09:46 zw000006440
-rw-r----- 1 oraREPLTEST dba 1023999864 Apr 4 09:49 zw000006441
-rw-r----- 1 oraREPLTEST dba 1023999722 Apr 4 09:56 zw000006442
-rw-r----- 1 oraREPLTEST dba 407555339 Apr 4 10:34 zw000006443
-- 리스트 중 expdp 시점보다 이전에 생성된 번호 사용
GGSCI (retestdt) 35> alter ztmp extseqno 6439 extrba 0
2022-04-04 10:36:30 INFO OGG-06594 Replicat ZTMP has been altered. Even the start up position might be updated, duplicate suppression remains active in next startup. To override duplicate suppression, start ZTMP with NOFILTERDUPTRANSACTIONS option.
REPLICAT altered.
-- == 6. scn base replicat start
-- expdp시 사용한 scn값 사용하여 ztmp 시작
GGSCI> start ztmp aftercsn 38106140088422
GGSCI> dblogin UserID oraogg, password oraogg01
GGSCI (retestdt as ORAOGG@REPLTEST) 115> stats ztmp total
-- == 7. replicat 병합
-- =====================================================
-- 소스 OGG
-- =====================================================
-- 소스 전송프로세스 중지
GGSCI (testdb02p as oraogg@TEST2) 69> stop zpmp001
Sending STOP request to EXTRACT ZPMP001 ...
Request processed.
-- =====================================================
-- 타겟 OGG
-- =====================================================
-- 타켓 프로세스 중지
GGSCI (retestdt as ORAOGG@REPLTEST) 124> stop ztmp
Sending STOP request to REPLICAT ZTMP ...
Request processed.
GGSCI (retestdt as ORAOGG@REPLTEST) 125> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING ZREP001 00:00:00 00:00:06
REPLICAT RUNNING ZREP002 00:00:00 00:00:06
REPLICAT RUNNING ZREP003 00:00:00 00:00:06
REPLICAT RUNNING ZREP004 00:00:00 00:00:06
REPLICAT RUNNING ZREP005 00:00:00 00:00:06
REPLICAT RUNNING ZREP006 00:00:00 00:00:06
REPLICAT RUNNING ZREP007 00:00:00 00:00:06
REPLICAT RUNNING ZREP008 00:00:00 00:00:06
REPLICAT RUNNING ZREP009 00:00:00 00:00:06
REPLICAT RUNNING ZREP010 00:00:00 00:00:06
REPLICAT RUNNING ZREP011 00:00:00 00:00:06
REPLICAT RUNNING ZREPMK 00:00:00 00:00:06
REPLICAT STOPPED ZTMP 00:00:00 00:00:01
-- 병합 할 타켓 프로세스 중지
GGSCI (retestdt as ORAOGG@REPLTEST) 126> stop zrep001
Sending STOP request to REPLICAT ZREP001 ...
Request processed.
GGSCI (retestdt as ORAOGG@REPLTEST) 127> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED ZREP001 00:00:00 00:00:01
REPLICAT RUNNING ZREP002 00:00:00 00:00:00
REPLICAT RUNNING ZREP003 00:00:00 00:00:00
REPLICAT RUNNING ZREP004 00:00:00 00:00:00
REPLICAT RUNNING ZREP005 00:00:00 00:00:00
REPLICAT RUNNING ZREP006 00:00:00 00:00:00
REPLICAT RUNNING ZREP007 00:00:00 00:00:00
REPLICAT RUNNING ZREP008 00:00:00 00:00:00
REPLICAT RUNNING ZREP009 00:00:00 00:00:00
REPLICAT RUNNING ZREP010 00:00:00 00:00:00
REPLICAT RUNNING ZREP011 00:00:00 00:00:00
REPLICAT RUNNING ZREPMK 00:00:00 00:00:00
REPLICAT STOPPED ZTMP 00:00:00 00:00:15
GGSCI (retestdt as ORAOGG@REPLTEST) 128> view param ztmp
replicat ztmp
--# Setenv
USERID ORAOGG, PASSWORD oraogg01
DISCARDFILE ./dirout/ztmp.dsc, APPEND, MEGABYTES 2000
DISCARDROLLOVER AT 00:01 ON MONDAY
GETTRUNCATES
--ERROR SKIP PARAMETER
--HANDLECOLLISIONS
REPERROR 1403 DISCARD
REPERROR 1 DISCARD
--# Tune
EOFDELAYCSECS 10
MAP SCOTT.STB_PROD_STOCK_OPT, TARGET SCOTTREAL.STB_PROD_STOCK_OPT;
GGSCI (retestdt as ORAOGG@REPLTEST) 129> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED ZREP001 00:00:00 00:00:10
REPLICAT RUNNING ZREP002 00:00:00 00:00:00
REPLICAT RUNNING ZREP003 00:00:00 00:00:00
REPLICAT RUNNING ZREP004 00:00:00 00:00:00
REPLICAT RUNNING ZREP005 00:00:00 00:00:00
REPLICAT RUNNING ZREP006 00:00:00 00:00:00
REPLICAT RUNNING ZREP007 00:00:00 00:00:00
REPLICAT RUNNING ZREP008 00:00:00 00:00:00
REPLICAT RUNNING ZREP009 00:00:00 00:00:00
REPLICAT RUNNING ZREP010 00:00:00 00:00:00
REPLICAT RUNNING ZREP011 00:00:00 00:00:00
REPLICAT RUNNING ZREPMK 00:00:00 00:00:00
REPLICAT STOPPED ZTMP 00:00:00 00:00:24
-- 병합 할 타겟 프로세스 파라미터 변경
-- MAP SCOTT.STB_PROD_STOCK_OPT, TARGET SCOTTREAL.STB_PROD_STOCK_OPT; 추가
GGSCI (retestdt as ORAOGG@REPLTEST) 130> edit param zrep001
replicat zrep001
--# Setenv
USERID ORAOGG, PASSWORD oraogg01
DISCARDFILE ./dirout/zrep001.dsc, APPEND, MEGABYTES 2000
DISCARDROLLOVER AT 00:01 ON MONDAY
GETTRUNCATES
--ERROR SKIP PARAMETER
--HANDLECOLLISIONS
REPERROR 1403 DISCARD
REPERROR 1 DISCARD
--# Tune
EOFDELAYCSECS 10
--22EA
... 중략 ...
MAP SCOTT.STB_PROD_STOCK_OPT, TARGET LFMREAL.STB_PROD_STOCK_OPT;
"dirprm/zrep001.prm" 43L, 2067C written
GGSCI (retestdt as ORAOGG@REPLTEST) 131> info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER RUNNING
REPLICAT STOPPED ZREP001 00:00:00 00:00:30
REPLICAT RUNNING ZREP002 00:00:00 00:00:09
REPLICAT RUNNING ZREP003 00:00:00 00:00:09
REPLICAT RUNNING ZREP004 00:00:00 00:00:09
REPLICAT RUNNING ZREP005 00:00:00 00:00:00
REPLICAT RUNNING ZREP006 00:00:00 00:00:09
REPLICAT RUNNING ZREP007 00:00:00 00:00:09
REPLICAT RUNNING ZREP008 00:00:00 00:00:09
REPLICAT RUNNING ZREP009 00:00:00 00:00:09
REPLICAT RUNNING ZREP010 00:00:00 00:00:09
REPLICAT RUNNING ZREP011 00:00:00 00:00:09
REPLICAT RUNNING ZREPMK 00:00:00 00:00:10
REPLICAT STOPPED ZTMP 00:00:00 00:00:44
-- 병합 대상 타겟 프로세스의 체크포인트 일치 확인
GGSCI (retestdt as oraogg@REPLTEST) 30> info zrep001
REPLICAT ZREP001 Last Started 2022-03-31 07:00 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:01:24 ago)
Log Read Checkpoint File /DBMS/REPLTEST/ogg19c/dirdat/zw000006444
2022-04-04 10:49:13.001638 RBA 990214542
GGSCI (retestdt as oraogg@REPLTEST) 31> info ztmp
REPLICAT ZTMP Last Started 2022-04-04 10:36 Status STOPPED
Checkpoint Lag 00:00:00 (updated 00:01:50 ago)
Log Read Checkpoint File /DBMS/REPLTEST/ogg19c/dirdat/zw000006444
2022-04-04 10:49:13.001638 RBA 990214542
-- 병합 된 타겟 프로세스 기동
GGSCI (retestdt as ORAOGG@REPLTEST) 135> start zrep001
-- 병합 이전 타겟 프로세스 삭제
GGSCI (retestdt as ORAOGG@REPLTEST) 138> delete ztmp
Deleted REPLICAT ZTMP.
-- =====================================================
-- 소스 OGG
-- =====================================================
-- 소스 전송프로세스 기동
GGSCI (testdb02p as oraogg@TEST2) 43> start zpmp001
Sending START request to MANAGER ...
EXTRACT ZPMP001 starting
'OraclE' 카테고리의 다른 글
| SPM - 튜닝된 SQL의 실행계획을 등록하고 적용 하는 방법 (0) | 2022.06.13 |
|---|---|
| SPM(SQL PLAN MANAGEMENT) (0) | 2022.06.13 |
| active session history (0) | 2022.01.30 |
| orange plan 테이블 생성 (0) | 2022.01.30 |
| 벌크 insert 빠르게 하는 방법 (0) | 2018.03.21 |