'오라클 골든게이트'에 해당되는 글 3건

2019.03.02 Oracle Golden Gate 사전 점검 스크립트
2013.04.08 Oracle Golden Gate Configuration(단방향 DDL,DML)
2013.04.08 Oracle Golden Gate 개념 및 Installaton 2

Oracle Golden Gate 사전 점검 스크립트

1. IT Story/Scripts 2019. 3. 2. 21:29

Oracle Golden Gate 사전 점검 스크립트

-- Example of running the script:

-- sqlplus <userid>/<pw> @schemaCheckOracle.sql

set null "NULL VALUE"

set feedback off

set heading off

set linesize 132

set pagesize 9999

set echo off

set verify off

col table_name for a30

col column_name for a30

col data_type for a15

col object_type for a20

col constraint_type_desc for a30

--spool schemaCheckOracle.&&schema_name.out

ACCEPT schema_name char prompt 'Enter the Schema Name > '

variable b0 varchar2(50)

exec :b0 := upper('&schema_name');

spool schemaCheckOracle.&&schema_name..out

SET Heading off

SELECT '------ System Info for: '||:b0

FROM dual;

set heading on

select to_char(sysdate, 'MM-DD-YYYY HH24:MI:SS') "DateTime: " from dual

select banner from v$version

SET Heading off

SELECT '------ Database Level Supplemental Log Check - 9i and 10g: '

FROM dual;

SET Heading on

SELECT SUPPLEMENTAL_LOG_DATA_MIN MIN, SUPPLEMENTAL_LOG_DATA_PK PK, SUPPLEMENTAL_LOG_DATA_UI UI

FROM V$DATABASE

select name, log_mode "LogMode",

supplemental_log_data_min "SupLog: Min", supplemental_log_data_pk "PK",

supplemental_log_data_ui "UI", force_logging "Forced",

supplemental_log_data_fk "FK", supplemental_log_data_all "All",

to_char(created, 'MM-DD-YYYY HH24:MI:SS') "Created"

from v$database

select

platform_name

from v$database

set heading off

SELECT '------ Objects stored in Tablespaces with Compression are not supported in the current release of OGG '

FROM dual;

SET Heading on

select

TABLESPACE_NAME,

DEF_TAB_COMPRESSION

from DBA_TABLESPACES

where

DEF_TAB_COMPRESSION <> 'DISABLED';

set heading off

SELECT '------ Distinct Object Types and their Count in the Schema: '||:b0

FROM dual;

SET Heading on

SELECT object_type, count(*) total

FROM all_objects

WHERE OWNER = :b0

GROUP BY object_type

SET Heading off

SELECT '------ Distinct Column Data Types and their Count in the Schema: '||:b0

FROM dual;

SET Heading on

SELECT data_type, count(*) total

FROM all_tab_columns

WHERE OWNER = :b0

GROUP BY data_type

SET Heading off

SELECT '------ Tables With No Primary Key or Unique Index in the Schema: '||:b0

FROM dual;

SET Heading on

SELECT distinct(table_name)

FROM all_tables

WHERE owner = :b0

MINUS

(SELECT obj1.name

FROM SYS.user$ user1,

SYS.user$ user2,

SYS.cdef$ cdef,

SYS.con$ con1,

SYS.con$ con2,

SYS.obj$ obj1,

SYS.obj$ obj2

WHERE user1.name = :b0

AND cdef.type# = 2

AND con2.owner# = user2.user#(+)

AND cdef.robj# = obj2.obj#(+)

AND cdef.rcon# = con2.con#(+)

AND obj1.owner# = user1.user#

AND cdef.con# = con1.con#

AND cdef.obj# = obj1.obj#

UNION

SELECT idx.table_name

FROM all_indexes idx

WHERE idx.owner = :b0

AND idx.uniqueness = 'UNIQUE')

SET Heading off

SELECT '------ Tables with no PK or UI and rowsize > 1M in the Schema: '||:b0

FROM dual;

SET Heading on

SELECT distinct(table_name)

FROM all_tab_columns

WHERE owner = :b0

group by table_name

HAVING sum(data_length) > 1000000

MINUS

(SELECT obj1.name

FROM SYS.user$ user1,

SYS.user$ user2,

SYS.cdef$ cdef,

SYS.con$ con1,

SYS.con$ con2,

SYS.obj$ obj1,

SYS.obj$ obj2

WHERE user1.name = :b0

AND cdef.type# = 2

AND con2.owner# = user2.user#(+)

AND cdef.robj# = obj2.obj#(+)

AND cdef.rcon# = con2.con#(+)

AND obj1.owner# = user1.user#

AND cdef.con# = con1.con#

AND cdef.obj# = obj1.obj#

UNION

SELECT idx.table_name

FROM all_indexes idx

WHERE idx.owner = :b0

AND idx.uniqueness = 'UNIQUE')

set heading off

SELECT '------ Tables with NOLOGGING setting ' FROM dual;

SELECT '------ This may cause problems with missing data down stream. Schema: ' ||:b0 FROM dual;

set heading on

select owner, table_name, ' ' "PARTITION_NAME", ' ' "PARTITIONING_TYPE", logging from DBA_TABLES

where logging <> 'YES' and OWNER = :b0

union

select owner, table_name, ' ', partitioning_type, DEF_LOGGING "LOGGING" from DBA_part_tables

where DEF_LOGGING != 'YES' and owner = :b0

union

select table_owner, table_name, PARTITION_NAME, ' ', logging from DBA_TAB_PARTITIONS

where logging <> 'YES' and table_owner = :b0

union

select table_owner, table_name, PARTITION_NAME, ' ', logging from DBA_TAB_SUBPARTITIONS

where logging <> 'YES' and table_owner <> :b0

;

set heading off

SELECT '------ Tables with Deferred constraints.Deferred constraints may cause TRANDATA to chose an incorrect Key ' FROM dual;

SELECT '------ Tables with Deferred constraints should be added using KEYCOLS in the trandata statement. Schema: ' ||:b0 FROM dual;

set heading on

SELECT c.TABLE_NAME,

c.CONSTRAINT_NAME,

c.CONSTRAINT_TYPE,

c.DEFERRABLE,

c.DEFERRED,

c.VALIDATED,

c.STATUS,

i.INDEX_TYPE,

c.INDEX_NAME,

c.INDEX_OWNER

FROM dba_constraints c,

dba_indexes i

WHERE

i.TABLE_NAME = c.TABLE_NAME

AND i.OWNER = c.OWNER

AND c.owner = :b0

AND c.DEFERRED = 'DEFERRED';

set heading off

SELECT '------ Tables Defined with Rowsize > 2M in the Schema: '||:b0

FROM dual;

SET Heading on

SELECT table_name, sum(data_length) row_length_over_2M

FROM all_tab_columns

WHERE OWNER = :b0

GROUP BY table_name

HAVING sum(data_length) > 2000000

SET Heading off

SELECT '------ Tables that will Fail Add Trandata (Only an issue for Oracle versions below Oracle 10G) in the Schema: '||:b0

FROM dual;

SET Heading on

SELECT distinct(table_name)

FROM dba_tab_columns

WHERE owner = :b0

AND column_id > 32

AND table_name in

(SELECT distinct(table_name)

FROM all_tables

WHERE owner = :b0

MINUS

(SELECT obj1.name

FROM SYS.user$ user1,

SYS.user$ user2,

SYS.cdef$ cdef,

SYS.con$ con1,

SYS.con$ con2,

SYS.obj$ obj1,

SYS.obj$ obj2

WHERE user1.name = :b0

AND cdef.type# = 2

AND con2.owner# = user2.user#(+)

AND cdef.robj# = obj2.obj#(+)

AND cdef.rcon# = con2.con#(+)

AND obj1.owner# = user1.user#

AND cdef.con# = con1.con#

AND cdef.obj# = obj1.obj#

UNION

SELECT idx.table_name

FROM all_indexes idx

WHERE idx.owner = :b0

AND idx.uniqueness = 'UNIQUE'))

SET Heading off

SELECT '------ Tables With CLOB, BLOB, LONG, NCLOB or LONG RAW Columns in the Schema: '||:b0

FROM dual;

SET Heading on

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE

FROM all_tab_columns

WHERE OWNER = :b0

AND data_type in ('CLOB', 'BLOB', 'LONG', 'LONG RAW', 'NCLOB')

SET Heading off

SELECT '------ Tables With Columns of UNSUPPORTED Datatypes in the Schema: '||:b0

FROM dual;

SET Heading on

SELECT TABLE_NAME, COLUMN_NAME, DATA_TYPE

FROM all_tab_columns

WHERE OWNER = :b0

AND (data_type in ('ORDDICOM', 'BFILE', 'TIMEZONE_REGION', 'BINARY_INTEGER', 'PLS_INTEGER', 'UROWID', 'URITYPE', 'MLSLABEL', 'TIMEZONE_ABBR', 'ANYDATA', 'ANYDATASET', 'ANYTYPE')

or data_type like 'INTERVAL%')

SET Heading off

SELECT '----- Tables with Compressed data is not supported - in the Schema: '||:b0

FROM dual;

SET Heading on

SELECT TABLE_NAME, COMPRESSION

FROM all_all_tables

WHERE OWNER = :b0

AND (COMPRESSION = 'ENABLED');

SELECT TABLE_NAME, COMPRESSION

FROM ALL_TAB_PARTITIONS

WHERE TABLE_OWNER = :b0

AND (COMPRESSION = 'ENABLED');

SET Heading off

SELECT '----- Cluster (DML and DDL supported for 9i or later) or Object Tables (DML supported for 10G or later, no DDL) - in the Schema: '||:b0

FROM dual;

SET Heading on

SELECT TABLE_NAME, CLUSTER_NAME, TABLE_TYPE

FROM all_all_tables

WHERE OWNER = :b0

AND (cluster_name is NOT NULL or TABLE_TYPE is NOT NULL)

SET Heading off

SELECT '------ IOT (Fully support for Oracle 10GR2 (with or without overflows) using GGS 10.4 and higher) - in the Schema: '||:b0

FROM dual;

SET Heading on

SELECT TABLE_NAME, IOT_TYPE, TABLE_TYPE

FROM all_all_tables

WHERE OWNER = :b0

AND (IOT_TYPE is not null or TABLE_TYPE is NOT NULL)

SET Heading off

SELECT '------ Tables with Domain or Context Indexes - in the Schema: '||:b0

FROM dual;

SET Heading on

SELECT TABLE_NAME, index_name, index_type

FROM dba_indexes WHERE OWNER = :b0

and index_type = 'DOMAIN'

SET Heading off

SELECT '------ Types of Constraints on the Tables in the Schema: '||:b0

FROM dual;

SET Heading on

SELECT DECODE(constraint_type,'P','PRIMARY KEY','U','UNIQUE', 'C', 'CHECK', 'R',

'REFERENTIAL') constraint_type_desc, count(*) total

FROM all_constraints

WHERE OWNER = :b0

GROUP BY constraint_type

SET Heading off

SELECT '------ Cascading Deletes on the Tables in the Schema: '||:b0

FROM dual;

SET Heading on

SELECT table_name, constraint_name

FROM all_constraints

WHERE OWNER = :b0 and constraint_type = 'R' and delete_rule = 'CASCADE'

SET Heading off

SELECT '------ Tables Defined with Triggers in the Schema: '||:b0

FROM dual;

SET Heading on

SELECT table_name, COUNT(*) trigger_count

FROM all_triggers

WHERE OWNER = :b0

GROUP BY table_name

SET Heading off

SELECT '------ Performance issues - Reverse Key Indexes Defined in the Schema: '||:b0

FROM dual;

col Owner format a10

col TABLE_OWNER format a10

col INDEX_TYPE format a12

SET Heading on

select

OWNER,

INDEX_NAME,

INDEX_TYPE,

TABLE_OWNER,

TABLE_NAME,

TABLE_TYPE,

UNIQUENESS,

CLUSTERING_FACTOR,

NUM_ROWS,

LAST_ANALYZED,

BUFFER_POOL

from dba_indexes

where index_type = 'NORMAL/REV'

And OWNER = :b0

SET Heading off

SELECT '------ Sequence numbers: '||:b0

FROM dual;

COLUMN SEQUENCE_OWNER FORMAT a15

COLUMN SEQUENCE_NAME FORMAT a30

COLUMN INCR FORMAT 999

COLUMN CYCLE FORMAT A5

COLUMN ORDER FORMAT A5

SET Heading on

SELECT SEQUENCE_OWNER,

SEQUENCE_NAME,

MIN_VALUE,

MAX_VALUE,

INCREMENT_BY INCR,

CYCLE_FLAG CYCLE,

ORDER_FLAG "ORDER",

CACHE_SIZE,

LAST_NUMBER

FROM DBA_SEQUENCES

WHERE SEQUENCE_OWNER=UPPER(:b0)

set linesize 132

col "Avg Log Size" format 999,999,999

select sum (BLOCKS) * max(BLOCK_SIZE)/ count(*)"Avg Log Size" From gV$ARCHIVED_LOG;

Prompt Table: Frequency of Log Switches by hour and day

SELECT SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),1,5) DAY,

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'00',1,0)),'99') "00",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'01',1,0)),'99') "01",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'02',1,0)),'99') "02",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'03',1,0)),'99') "03",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'04',1,0)),'99') "04",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'05',1,0)),'99') "05",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'06',1,0)),'99') "06",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'07',1,0)),'99') "07",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'08',1,0)),'99') "08",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'09',1,0)),'99') "09",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'10',1,0)),'99') "10",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'11',1,0)),'99') "11",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'12',1,0)),'99') "12",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'13',1,0)),'99') "13",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'14',1,0)),'99') "14",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'15',1,0)),'99') "15",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'16',1,0)),'99') "16",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'17',1,0)),'99') "17",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'18',1,0)),'99') "18",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'19',1,0)),'99') "19",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'20',1,0)),'99') "20",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'21',1,0)),'99') "21",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'22',1,0)),'99') "22",

TO_CHAR(SUM(DECODE(SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),10,2),'23',1,0)),'99') "23"

FROM V$LOG_HISTORY

GROUP BY SUBSTR(TO_CHAR(FIRST_TIME, 'MM-DD-YY HH24:MI:SS'),1,5)

order by 1;

SET Heading off

SELECT '------ Summary of log volume processed by day for last 7 days: '

FROM dual;

SET Heading on

select to_char(first_time, 'mm/dd') ArchiveDate,

sum(BLOCKS*BLOCK_SIZE/1024/1024) LOGMB

from v$archived_log

where first_time > sysdate - 7

group by to_char(first_time, 'mm/dd')

order by to_char(first_time, 'mm/dd');

SET Heading off

SELECT '------ Summary of log volume processed per hour for last 7 days: '

FROM dual;

SET Heading on

select to_char(first_time, 'MM-DD-YYYY') ArchiveDate,

to_char(first_time, 'HH24') ArchiveHour,

sum(BLOCKS*BLOCK_SIZE/1024/1024) LogMB

from v$archived_log

where first_time > sysdate - 7

group by to_char(first_time, 'MM-DD-YYYY'), to_char(first_time, 'HH24')

order by to_char(first_time, 'MM-DD-YYYY'), to_char(first_time, 'HH24');

set heading off

select '* This output may be found in file: schemaCheckOracle.&&schema_name..out' from dual

spool off

undefine b0

-- exit

저작자표시 동일조건

'1. IT Story > Scripts' 카테고리의 다른 글

오라클 RAC Estd.interconnect Traffic 관리 스크립트 (0)	2019.03.06
오라클 물리적 IO 관리 스크립트 (0)	2019.03.05
오라클 인덱스 상세 조회 스크립트 (0)	2019.03.04
Oracle 수행 시간 기준 SQL 추출 스크립트 (0)	2019.03.03
Oracle Session_cached_cursors, Open_cursors 점검 스크립트 (0)	2019.02.27
Oracle Invalid Compile 스크립트 (0)	2019.02.26
Oracle Session별 Memory 사용량 확인 (0)	2019.02.23
Oracle 암/복호화 (DBMS_CRYPTO) 스크립트 (0)	2019.02.22

운명을바꾸는자

IT와 함께 살아가는 삶

Oracle Golden Gate Configuration(단방향 DDL,DML)

1. IT Story/DB 2013. 4. 8. 23:30

1.OGG Configuration(단방향 DDL,DML)

1-1 Create and start Manager

OGG MGR 포트 및 Trail파일 설정(Source)

GGSCI (ocp.mycorpdomain.com) 2> dblogin userid oggmgr, password oracle

Successfully logged into database.

GGSCI (ocp.mycorpdomain.com) 3> edit params mgr

PORT 7809

userid oggmgr password oracle

PURGEOLDEXTRACTS ./dirdat/et*, USECHECKPOINTS, MINKEEPDAYS 3

PURGEOLDEXTRACTS ./dirdat/rt*, USECHECKPOINTS, MINKEEPDAYS 3

GGSCI (ocp.mycorpdomain.com) 4>start mgr

GGSCI (ocp.mycorpdomain.com) 5> info mgr

Manager is running (IP port ocp.mycorpdomain.com.7809).

OGG MGR 포트 및 Trail파일 설정(Target)
GGSCI (ocp.mycorpdomain.com) 2> dblogin userid oggmgr, password oracle

Successfully logged into database.

GGSCI (ocp.mycorpdomain.com) 5> edit params mgr

PORT 7810

PURGEOLDEXTRACTS ./dirdat/et*, USECHECKPOINTS, MINKEEPDAYS 3

PURGEOLDEXTRACTS ./dirdat/rt*, USECHECKPOINTS, MINKEEPDAYS 3

GGSCI (ocp.mycorpdomain.com) 7> start mgr

GGSCI (ocp.mycorpdomain.com) 7> info mgr

Manager is running (IP port ocp.mycorpdomain.com.7810).

1-2 Create extract/Data-pump group(Source)

EXTRACT
GGSCI (ocp.mycorpdomain.com) 8> add extract extora, tranlog, begin now

GGSCI (ocp.mycorpdomain.com) 9> edit params EXTORA

-- Extract Parameter File

EXTRACT EXTORA

USERID oggmgr, PASSWORD oracle

TRANLOGOPTIONS OPENARCHIVEIMMEDIATE

THREADOPTIONS MAXCOMMITPROPAGATIONDELAY 60000 IOLATENCY 60000

TRANLOGOPTIONS EXCLUDEUSER OGGMGR, EXCLUDEUSERID 54

EXTTRAIL ./dirdat/et

REPORTCOUNT EVERY 5 MINUTES, RATE

-- Use DDL parameter to configure support for all mapped DDL operations

DDL INCLUDE MAPPED

DDLOPTIONS ADDTRANDATA, REPORT

-- Table/Sequence List

TABLE SCOTT.*;

SEQUENCE SCOTT.*;

GGSCI (ocp.mycorpdomain.com) 10> add exttrail ./dirdat/et, extract extora

Data-PUMP
GGSCI (ocp.mycorpdomain.com) 11>add extract pumpora, exttrailsource ./dirdat/et

GGSCI (ocp.mycorpdomain.com) 12> edit params PUMPA

-- Data Pump Parameter File

EXTRACT PUMPA

PASSTHRU

RMTHOST 192.168.22.10, MGRPORT 7810

RMTTRAIL ./dirdat/rt

REPORTCOUNT EVERY 5 MINUTES, RATE

-- DDL Option

--DDL INCLUDE MAPPED

--DDLOPTIONS REPORT

--DDLERROR DEFAULT ABEND

-- Table/Sequence List

TABLE SCOTT.*;

SEQUENCE SCOTT.*;

GGSCI (ocp.mycorpdomain.com) 13> add rmttrail ./diradat/rt, extract pumpora

[주의사항]

- Trail File명이 혼돈하지 않고 적절하게 사용할 수 있도록 주의 해야 한다.

(EXTRACT(./diradat/et) 파일을 기반으로 Data-PUMP(./diardata/rt)를 생성해야 함으로 파일 명을 혼돈하지 말고 사용할 것.

1-3 Create Chekpoint Table(Target)

GGSCI (ocp.mycorpdomain.com) 14> add checkpointtable oggmgr.ogg_checkpoint

Oggmgr 유저에 해당 테이블 생성 확인

1-4 Create Replicat Group(Target)

GGSCI (ocp.mycorpdomain.com) 15>

add replicat repora, exttrail ./dirdat/rt, checkpointtable oggmgr.ogg_checkpoint

GGSCI (ocp.mycorpdomain.com) 16> edit params REPORA

replicat repora

userid oggmgr, password oracle

assumetargetdefs

DDL INCLUDE MAPPED

DDLOPTIONS REPORT

DDLERROR DEFAULT ABEND

checkpointsecs 1

map scott.*, target scott.*;

[주의사항]

-Replicat 설정시에도 Source DB의 Data-Pump를 이용해야 되기 때문에 추가시 파일 명을 일치시켜주어야 합니다.(./dirdat/rt)

1-5 OGG EXTRACT,PUMP,REPLICAT Start(Source / Target)

OGG EXTRACT,PUMP (Source)

GGSCI (ocp.mycorpdomain.com) 1> Start EXTRACT EXTORA

GGSCI (ocp.mycorpdomain.com) 2> Start EXTRACT PUMPORA

GGSCI (ocp.mycorpdomain.com) 3> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

EXTRACT RUNNING EXTORA 00:00:00 00:00:07

EXTRACT RUNNING PUMPORA 00:00:00 00:00:03

GGSCI (ocp.mycorpdomain.com) 2> info EXTRACT EXTORA

EXTRACT EXTORA Last Started 2013-04-07 22:22 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:02 ago)

Log Read Checkpoint Oracle Redo Logs

2013-04-08 22:41:56 Seqno 31, RBA 8829440

SCN 0.1297938 (1297938)

GGSCI (ocp.mycorpdomain.com) 3> info EXTRACT PUMPORA

EXTRACT PUMPORA Last Started 2013-04-07 23:11 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:01 ago)

Log Read Checkpoint File ./dirdat/et000002

2013-04-07 23:43:11.000000 RBA 21412

OGG REPLICAT (Target)

GGSCI (ocp.mycorpdomain.com) 1> Start REPLICAT REPORA

GGSCI (ocp.mycorpdomain.com) 2> info all

Program Status Group Lag at Chkpt Time Since Chkpt

MANAGER RUNNING

REPLICAT RUNNING REPORA 00:00:00 00:00:00

GGSCI (ocp.mycorpdomain.com) 4> info REPLICAT REPORA

REPLICAT REPORA Last Started 2013-04-07 22:36 Status RUNNING

Checkpoint Lag 00:00:00 (updated 00:00:01 ago)

Log Read Checkpoint File ./dirdat/rt000000

2013-04-07 23:43:10.988221 RBA 21512

1-6 OGG TEST Setting 확인

OGG-Checkpoint table(Target)

REPORA 4120505024 0 21512 2013-04-07 23:43:10.988221 2013-04-07 오후 10:26:50 2013-04-07 오후 11:43:16 /ogg 1282955 7.33.914 1282955 7.33.914 1

파일 생성 확인
--Source DB

ocp@orcl : /ogg/dirdat> ls

et000000 et000001 et000002

--Target DB

ocp@orcl : /ogg/dirdat> ls

rt000000

2.OGG TEST / 동작확인(DML / DDL)

- 데이터를 동일하게 이관하여 TEST진행(SCOTT 유저의 기본 테이블을 이용하여 OGG TEST)

OGG TEST(DML/DDL)

--Source DB

select * from scott.emp;

delete from scott.emp;

SET DEFINE OFF;

Insert into SCOTT.EMP

(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)

Values

(7369, 'SMITH', 'CLERK', 7902, TO_DATE('12/17/1980 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),

800, 20);

Insert into SCOTT.EMP

(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

Values

(7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('02/20/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),

1600, 300, 30);

Insert into SCOTT.EMP

(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

Values

(7521, 'WARD', 'SALESMAN', 7698, TO_DATE('02/22/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),

1250, 500, 30);

Insert into SCOTT.EMP

(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)

Values

(7566, 'JONES', 'MANAGER', 7839, TO_DATE('04/02/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),

2975, 20);

Insert into SCOTT.EMP

(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

Values

(7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('09/28/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),

1250, 1400, 30);

Insert into SCOTT.EMP

(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)

Values

(7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('05/01/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),

2850, 30);

Insert into SCOTT.EMP

(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)

Values

(7782, 'CLARK', 'MANAGER', 7839, TO_DATE('06/09/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),

2450, 10);

Insert into SCOTT.EMP

(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)

Values

(7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('04/19/1987 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),

3000, 20);

Insert into SCOTT.EMP

(EMPNO, ENAME, JOB, HIREDATE, SAL, DEPTNO)

Values

(7839, 'KING', 'PRESIDENT', TO_DATE('11/17/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),

5000, 10);

Insert into SCOTT.EMP

(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)

Values

(7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('09/08/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),

1500, 0, 30);

Insert into SCOTT.EMP

(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)

Values

(7876, 'ADAMS', 'CLERK', 7788, TO_DATE('05/23/1987 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),

1100, 20);

Insert into SCOTT.EMP

(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)

Values

(7900, 'JAMES', 'CLERK', 7698, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),

950, 30);

Insert into SCOTT.EMP

(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)

Values

(7902, 'FORD', 'ANALYST', 7566, TO_DATE('12/03/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),

3000, 20);

Insert into SCOTT.EMP

(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, DEPTNO)

Values

(7934, 'MILLER', 'CLERK', 7782, TO_DATE('01/23/1982 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),

1300, 10);

COMMIT;

Alter table scott.emp add new_test char(10)

Alter table scott.emp drop column

OGG 동작 확인
--Source DB

GGSCI (ocp.mycorpdomain.com) 5> stats EXTRACT EXTORA

Sending STATS request to EXTRACT EXTORA ...

Start of Statistics at 2013-04-08 22:51:04.

DDL replication statistics (for all trails):

*** Total statistics since extract started ***

Operations 2.00

Mapped operations 2.00

Unmapped operations 0.00

Other operations 0.00

Excluded operations 0.00

Output to ./dirdat/et:

*** Total statistics since 2013-04-07 22:22:48 ***

No database operations have been performed.

*** Daily statistics since 2013-04-08 00:00:00 ***

No database operations have been performed.

*** Hourly statistics since 2013-04-08 22:00:00 ***

No database operations have been performed.

*** Latest statistics since 2013-04-07 22:22:48 ***

No database operations have been performed.

Extracting from SCOTT.EMP to SCOTT.EMP:

*** Total statistics since 2013-04-07 22:22:48 ***

Total inserts 56.00

Total updates 0.00

Total deletes 56.00

Total discards 0.00

Total operations 112.00

*** Daily statistics since 2013-04-08 00:00:00 ***

No database operations have been performed.

*** Hourly statistics since 2013-04-08 22:00:00 ***

No database operations have been performed.

*** Latest statistics since 2013-04-07 22:22:48 ***

Total inserts 56.00

Total updates 0.00

Total deletes 56.00

Total discards 0.00

Total operations 112.00

Extracting from OGGMGR.GGS_MARKER to OGGMGR.GGS_MARKER:

*** Total statistics since 2013-04-07 22:22:48 ***

No database operations have been performed.

*** Daily statistics since 2013-04-08 00:00:00 ***

No database operations have been performed.

*** Hourly statistics since 2013-04-08 22:00:00 ***

No database operations have been performed.

*** Latest statistics since 2013-04-07 22:22:48 ***

No database operations have been performed.

End of Statistics.

GGSCI (ocp.mycorpdomain.com) 6>

GGSCI (ocp.mycorpdomain.com) 8> stats EXTRACT PUMPORA

Sending STATS request to EXTRACT PUMPORA ...

Start of Statistics at 2013-04-08 22:51:27.

DDL replication statistics (for all trails):

*** Total statistics since extract started ***

Operations 2.00

Output to ./dirdat/rt:

*** Total statistics since 2013-04-07 23:11:14 ***

No database operations have been performed.

*** Daily statistics since 2013-04-08 00:00:00 ***

No database operations have been performed.

*** Hourly statistics since 2013-04-08 22:00:00 ***

No database operations have been performed.

*** Latest statistics since 2013-04-07 23:11:14 ***

No database operations have been performed.

Extracting from SCOTT.EMP to SCOTT.EMP:

*** Total statistics since 2013-04-07 23:11:14 ***

Total inserts 56.00

Total updates 0.00

Total deletes 56.00

Total discards 0.00

Total operations 112.00

*** Daily statistics since 2013-04-08 00:00:00 ***

No database operations have been performed.

*** Hourly statistics since 2013-04-08 22:00:00 ***

No database operations have been performed.

*** Latest statistics since 2013-04-07 23:11:14 ***

Total inserts 56.00

Total updates 0.00

Total deletes 56.00

Total discards 0.00

Total operations 112.00

End of Statistics.

--Target DB

GGSCI (ocp.mycorpdomain.com) 2> stats REPLICAT REPORA

Sending STATS request to REPLICAT REPORA1 ...

Start of Statistics at 2013-04-08 22:51:55.

DDL replication statistics:

*** Total statistics since replicat started ***

Operations 2.00

Mapped operations 2.00

Unmapped operations 0.00

Other operations 0.00

Excluded operations 0.00

Errors 0.00

Retried errors 0.00

Discarded errors 0.00

Ignored errors 0.00

Replicating from SCOTT.EMP to SCOTT.EMP:

*** Total statistics since 2013-04-07 23:24:16 ***

Total inserts 56.00

Total updates 0.00

Total deletes 56.00

Total discards 0.00

Total operations 112.00

*** Daily statistics since 2013-04-08 00:00:00 ***

No database operations have been performed.

*** Hourly statistics since 2013-04-08 22:00:00 ***

No database operations have been performed.

*** Latest statistics since 2013-04-07 23:24:16 ***

Total inserts 56.00

Total updates 0.00

Total deletes 56.00

Total discards 0.00

Total operations 112.00

End of Statistics.

[주의사항]

Vmware 환경이다 보니, Source DB -> Target DB로 작업 내용(DML/DDL)이 넘어가는데 시간이 걸리는 것을 확인할 수 있다.(1~3(s))

3.OGG Logfile List / 자주사용 되는 명령어

3-1 OGG Logfile

구성시 OGG 로그를 Tail 로 열어 두고 작업하는 것이 에러를 찾는데 좋은 방법이 됩니다.

/OGG/ggserr.log

/$ORACLE_BASE/diag/…/Trace/ggs_ddl_trace.log

3-2 OGG 기타 명령어

EXTTRAIL / REPLICAT의 설정 시 좀더 명확하게 옵션을 줄수 있다.
setenv (NLS_LANG = "KOREAN_KOREA.KO16KSC5601")

setenv (ORACLE_SID = "orcl")

EXTTRAIL / REPLICAT 적용이 후, 사용되고 있는 파일 명 변경 한다.
Alter REPLICAT REPORA, exttrail ./dirdat/sa

Alter EXTTRAIL EXTORA, exttrail ./dirdat/si

OGG의 경우 Seqno 및 RBA,SCAN 이 틀어지는 경우가 종종 발생한다 / 해당 번호에 맞게 변경시 사용한다.
ALTER REPLICAT REPORA, EXTSEQNO 0, EXTRBA 0

ALTER EXTTRAIL EXTORA, EXTSEQNO 0, EXTRBA 0

컬럼 mapping(COLMAP) / 세부 옵션의 경우 추가적으로 정리할 예정입니다.

[참고자료]

http://h391106.tistory.com/277

http://gssdba.wordpress.com/category/oracle-golden-gate/

http://mwguru.tistory.com/21

http://setijoagus.wordpress.com/2010/03/27/install-oracle-goldengate-2/

http://gavinsoorma.com/2010/02/oracle-goldengate-tutorial-6-configuring-data-pump-process/

http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

'1. IT Story > DB' 카테고리의 다른 글

Oracle Datapump를 이용한 Expdp/Impdp백업!! (0)	2013.04.15
Active DataGuard 11gR2 DGMGRL (0)	2013.04.09
Active DataGuard 11gR2 Configuration (0)	2013.04.09
Active DataGuard 11gR2 개념 및 Installation (0)	2013.04.09
Oracle Golden Gate 개념 및 Installaton (2)	2013.04.08
Oracle 11gR2 TDE(Transparent Data Encryption) (0)	2013.03.31
Oracle 10g CRS 제거 (0)	2013.03.31
Oracle 11g RAC with ASM TEST (2)	2013.03.31

운명을바꾸는자

IT와 함께 살아가는 삶

Oracle Golden Gate 개념 및 Installaton

1. IT Story/DB 2013. 4. 8. 23:29

1.Oracle Golden Gate

1-1 Architecture

Manager Process
OGG 전체의 Process들에 대한 제어 및 모니터링 지원
EXTRACT Process
데이터에 대한 변경된 사항에 대하여(Redo/Archive Log로 부터 Commit된 Transaction) Read하여 Source Trail에 변경 정보 저장
REPLICAT Process
Target Trail을 Read하여 주어진 Mapping Rule 조건에 맞게 Target DB에 DML, DDL 수행(SQL문 생성), SQL 생성시 Parameter 파일의 Mapping 정보를 참조
생성된 SQL과 Target Trail의 데이터를 결합하여 SQL문 완성
Source/Target Trail
Capture된 변경정보 데이터를 저장하는 File(et00000 등)

1-2 OGG Directory 구성

Directory 명	역할	설치 시 자동생성
/dirchk	OGG Process의 Checkpoint가 기록되는 File 저장	O
/dirdat	Extract가 Capture한 Data 변경분을 저장하는 Trail File 생성	O
/dirdef	OGG 적용대상 Table들의 Definition	O
/diroby	OGG 구성을 위한 작업 File 저장
/dirout	OGG Process 운영 시 발생하는 오류 원인 및 오류 record 내용이 기록되는 Discard File 저장	O
/dirpcs	OGG process의 PID 기록되는 File 저장	O
/dirprm	OGG Process 적용테이블의 Mapping 방식 및 추가 설정 Option들을 지정하는 File 생성	O
/dirrpt	OGG Process 처리내역 및 오류 시 원인 저장	O

1-3 OGG 구성 방안

다양한 형태의 구축방안이 있지만 이번 OGG TEST에서는 단방향 형태(DML,DDL지원)로 구축

2.OGG Installation

- Environment(Vmwere)

구분

Host IP

OS Version

oracle SID

DB Version

DB Schema

기타

Source

192.168.22.3

GNU /LINUX

orcl

Oracle 11.2.0.3

OGGMGR

SCOTT

유저 사용

Target

192.168.22.10

GNU /LINUX

orcl

Oracle 11.2.0.3

OGGMGR

SCOTT

유저 사용

2-1 사전 준비사항 1 (Source / Target)

SQL> create tablespace ggs_data datafile '/oradata/orcl/ggs_data01.dbf' size 200m;

SQL> create user oggmgr identified by oracle

default tablespace ggs_data

temporary tablespace temp;

grant connect, resource to oggmgr;

grant select any dictionary, select any table to oggmgr;

grant create table to oggmgr;

grant flashback any table to oggmgr;

grant execute on dbms_flashback to oggmgr;

grant execute on utl_file to oggmgr;

grant create any table to oggmgr;

grant insert any table to oggmgr;

grant update any table to oggmgr;

grant delete any table to oggmgr;

grant drop any table to oggmgr;

2-2 OGG S/W Installation(Source / Target)

[다운로드]

http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

Source(Oracle 유저)
$ Mkdir /OGG

$ unzip ogg112101_fbo_ggs_Linux_x86_ora11g_32bit

$ tar xvf fbo_ggs_Linux_x86_ora11g_32bit.tar

Target(Oracle 유저
$ Mkdir /OGG

$ unzip ogg112101_fbo_ggs_Linux_x86_ora11g_32bit

$ tar xvf fbo_ggs_Linux_x86_ora11g_32bit.tar

OGG Installation(Source / Target)
$./ggsci

GGSCI (ocp.mycorpdomain.com) 1> create subdirs

2-3 사전 준비사항 2(Source)

Archivelog mode

SQL> ALTER SYSTEM SET LOG_ARCHIVE_START=TRUE SCOPE=SPFILE;

-- LOG_ARCHIVE_DEST 파라미터 변경

SQL> ALTER SYSTEM SET LOG_ARCHIVE_DEST_1='LOCATION=/home/oracle/arch' SCOPE=SPFILE;

-- LOG_ARCHIVE_FORMAT 파라미터 변경

SQL> ALTER SYSTEM SET log_archive_format='ora_%s_%t_%r.arc' SCOPE=SPFILE;

SQL> SHUTDOWN IMMEDIATE

SQL> STARTUP MOUNT

-- 아카이브 로그 확성화

SQL>ALTER DATABASE ARCHIVELOG;

SQL> ALTER DATABASE OPEN;

Supplemental logging

SQL> alter database add supplemental log data;

Support DDL

SQL> alter system set recyclebin=off scope=spfile;

-GoldenGate는 Oracle 9i 이후 버전에 대하여 DDL을 지원한다. 그 이전 버전에서는 DDL이 지원되지 않는다. 아래의 objects에 대해서 2 MB 이내의 모든 DDL operation을 지원한다.

-DDL replication을 수행하기 위해서는 반드시 Oracle recycle bin이 'OFF'로 설정되어야 한다. Recycle bin이 활성화되어 있을 경우, GoldenGate의 DDL tigger session이 내포된 recycle bin에 의해 tigger 실패를 야기할 수 있는 DDL operation를 받을 수 있게 된다.

-Oracle 기본 schema에 대해서는 DDL은 무시된다.

2-4 사전 준비사항 3 (Source / Target)

OGG Run Scripts
$sqlplus "/as sysdba"

SQL> @/OGG/marker_setup.sql

Enter GoldenGate schema name: OGGMGR

….

SQL> @/OGG/ddl_setup.sql

Enter GoldenGate schema name: OGGMGR

…..

SQL> @/OGG/role_setup.sql

Enter GoldenGate schema name: OGGMGR

…..

SQL> grant ggs_ggsuser_role to OGGMGR

Grant succeeded.

SQL>@/OGG/ddl_enable

Trigger altered.

SQL>@/OGG/ddl_pin OGGMGR

Additional logging(Table Level / Schema Level)

$./ggsci

GGSCI (ocp.mycorpdomain.com) 1> dblogin userid oggmgr, password oracle

Successfully logged into database.

-- Trandata 추가

GGSCI (ocp.mycorpdomain.com) 2> add trandata scott.*

(Add trandata scott.emp / add trandata scott.*)

-- 추가된 Trandata 확인

GGSCI (ocp.mycorpdomain.com) 3> info trandata scott.*

Logging of supplemental redo log data is enabled for table SCOTT.BONUS.

Columns supplementally logged for table SCOTT.BONUS: ENAME, JOB, SAL, COMM.

Logging of supplemental redo log data is enabled for table SCOTT.DEPT.

Columns supplementally logged for table SCOTT.DEPT: DEPTNO.

Logging of supplemental redo log data is enabled for table SCOTT.EMP.

Columns supplementally logged for table SCOTT.EMP: EMPNO.

Logging of supplemental redo log data is enabled for table SCOTT.SALGRADE.

Columns supplementally logged for table SCOTT.SALGRADE: GRADE, LOSAL, HISAL.

[참고자료]

http://h391106.tistory.com/277

http://gssdba.wordpress.com/category/oracle-golden-gate/

http://mwguru.tistory.com/21

http://setijoagus.wordpress.com/2010/03/27/install-oracle-goldengate-2/

http://gavinsoorma.com/2010/02/oracle-goldengate-tutorial-6-configuring-data-pump-process/

http://www.oracle.com/technetwork/middleware/goldengate/downloads/index.html

'1. IT Story > DB' 카테고리의 다른 글

Active DataGuard 11gR2 DGMGRL (0)	2013.04.09
Active DataGuard 11gR2 Configuration (0)	2013.04.09
Active DataGuard 11gR2 개념 및 Installation (0)	2013.04.09
Oracle Golden Gate Configuration(단방향 DDL,DML) (0)	2013.04.08
Oracle 11gR2 TDE(Transparent Data Encryption) (0)	2013.03.31
Oracle 10g CRS 제거 (0)	2013.03.31
Oracle 11g RAC with ASM TEST (2)	2013.03.31
Oracle VPD(Virtual Private Database) (0)	2013.03.31