Zero Downtime Upgrade of Oracle 10g to Oracle 11g Using GoldenGate — 1

浏览数：30 / 时间：2015年06月12日

Source Database

DB Name: zwc

Schemas: HR,OE,PM

Version: 10.2.0.4

RAC: No

OS Version: Red Hat Enterprise Linux Server release 5.9 x86_64

OGG Version: Oracle GoldenGate V11.2.1.0.6 for Oracle 10g on Linux x86-64

Target Database

DB Name: zwc

Schemas: HR,OE,PM

Version: 11.2.0.2

RAC: Yes

Nodes: 2

OS Version: Red Hat Enterprise Linux Server release 5.9 x86_64

OGG Version: Oracle GoldenGate V11.2.1.0.22 for Oracle 11g on Linux x86-64

On 10g Source Database

Check Size of Data

SQL> select owner,round(sum(bytes/1024/1024)) MB from dba_segments where owner not in (‘SYS‘,‘SYSTEM‘) group by rollup (owner) order by owner;

OWNER MB
—————————— ———-
CTXSYS 5
DBSNMP 2
DMSYS 0
EXFSYS 4
HR 2
IX 2
MDSYS 41
OE 9
OLAPSYS 16
ORDSYS 1
OUTLN 1
PM 3
SCOTT 0
SH 56
SYSMAN 49
TSMSYS 0
WMSYS 7
XDB 49
244

Check Special Data-types

SQL> select distinct owner,data_type from dba_tab_cols where owner in (‘HR‘,‘OE‘,‘PM‘) order by 1,2;

OWNER DATA_TYPE
—————————— ————————————
HR CHAR
HR DATE
HR NUMBER
HR VARCHAR2
OE ACTIONS_T
OE ACTION_V
OE CATEGORY_TYP
OE CHAR
OE CLOB
OE CORPORATE_CUSTOMER_TYP
OE CUSTOMER_TYP
OE CUST_ADDRESS_TYP
OE DATE
OE INTERVAL YEAR(2) TO MONTH
OE INVENTORY_LIST_TYP
OE INVENTORY_TYP
OE LINEITEMS_T
OE LINEITEM_V
OE NUMBER
OE NVARCHAR2
OE ORDER_ITEM_LIST_TYP
OE ORDER_LIST_TYP
OE ORDER_TYP
OE PHONE_LIST_TYP
OE PRODUCT_INFORMATION_TYP
OE PRODUCT_REF_LIST_TYP
OE PURCHASEORDER_T
OE RAW
OE REJECTION_T
OE SDO_ELEM_INFO_ARRAY
OE SDO_GEOMETRY
OE SDO_ORDINATE_ARRAY
OE SHIPPING_INSTRUCTIONS_T
OE SUBCATEGORY_REF_LIST_TYP
OE TIMESTAMP(6) WITH LOCAL TIME ZONE
OE VARCHAR2
OE WAREHOUSE_TYP
OE XDB$RAW_LIST_T
OE XMLTYPE
OE XMLTYPEEXTRA
OE XMLTYPEPI
PM ADHEADER_TYP
PM BFILE
PM BLOB
PM CLOB
PM DATE
PM NCLOB
PM NUMBER
PM ORDAUDIO
PM ORDDOC
PM ORDIMAGE
PM ORDIMAGESIGNATURE
PM ORDVIDEO
PM RAW
PM TEXTDOC_TAB
PM VARCHAR2

Check Cascade Delete Constraints

SQL> select owner,table_name,constraint_name,delete_rule,deferred,deferrable,status from dba_constraints where delete_rule<>‘NO ACTION‘ and owner in (‘HR‘,‘OE‘,‘PM‘);

OWNER TABLE_NAME CONSTRAINT_NAME DELETE_RU DEFERRED DEFERRABLE STATUS
—————————— —————————— —————————— ——— ——— ————– ——–
OE ORDER_ITEMS ORDER_ITEMS_ORDER_ID_FK CASCADE IMMEDIATE NOT DEFERRABLE ENABLED
OE ORDERS ORDERS_CUSTOMER_ID_FK SET NULL IMMEDIATE NOT DEFERRABLE ENABLED
OE CUSTOMERS CUSTOMERS_ACCOUNT_MANAGER_FK SET NULL IMMEDIATE NOT DEFERRABLE ENABLED
OE WAREHOUSES WAREHOUSES_LOCATION_FK SET NULL IMMEDIATE NOT DEFERRABLE ENABLED
OE ORDERS ORDERS_SALES_REP_FK SET NULL IMMEDIATE NOT DEFERRABLE ENABLED

Check Triggers

SQL> select table_owner,table_name,owner,trigger_name,status,trigger_type from dba_triggers where owner in (‘HR‘,‘OE‘,‘PM‘);

TABLE_OWNER TABLE_NAME OWNER TRIGGER_NAME STATUS TRIGGER_TYPE
—————————— —————————— —————————— —————————— ——– —————-
HR EMPLOYEES HR SECURE_EMPLOYEES DISABLED BEFORE STATEMENT
HR EMPLOYEES HR UPDATE_JOB_HISTORY ENABLED AFTER EACH ROW
OE ORDER_ITEMS OE INSERT_ORD_LINE ENABLED BEFORE EACH ROW
OE PURCHASEORDER OE PURCHASEORDER$xd ENABLED BEFORE EACH ROW
OE OC_ORDERS OE ORDERS_TRG ENABLED INSTEAD OF
OE OC_ORDERS OE ORDERS_ITEMS_TRG ENABLED INSTEAD OF

Check Sequences

SQL> select sequence_owner,sequence_name,cycle_flag,increment_by,last_number from dba_sequences where sequence_owner in (‘HR‘,‘OE‘,‘PM‘);

SEQUENCE_OWNER SEQUENCE_NAME C INCREMENT_BY LAST_NUMBER
—————————— —————————— – ———— ———–
HR LOCATIONS_SEQ N 100 3300
HR DEPARTMENTS_SEQ N 10 280
HR EMPLOYEES_SEQ N 1 207
OE ORDERS_SEQ N 1 1000

Check Services

SQL> select name,failover_method,enabled from dba_services;

NAME FAILOVER_METHOD ENA
—————————————————————- —————————————————————- —
SYS$BACKGROUND NO
SYS$USERS NO
seeddataXDB NO
seeddata NO
zwcXDB NO
zwc NO

Check Jobs

SQL> select job,schema_user,broken,what,interval from dba_jobs order by schema_user,job;

no rows selected

SQL> select owner,job_name,enabled,state from dba_scheduler_jobs order by owner,enabled,state;

OWNER JOB_NAME ENABL STATE
—————————— —————————— —– —————
EXFSYS RLM$SCHDNEGACTION TRUE SCHEDULED
EXFSYS RLM$EVTCLEANUP TRUE SCHEDULED
ORACLE_OCM MGMT_CONFIG_JOB TRUE SCHEDULED
ORACLE_OCM MGMT_STATS_CONFIG_JOB TRUE SCHEDULED
SYS FGR$AUTOPURGE_JOB FALSE DISABLED
SYS GATHER_STATS_JOB TRUE SCHEDULED
SYS AUTO_SPACE_ADVISOR_JOB TRUE SCHEDULED
SYS PURGE_LOG TRUE SCHEDULED

Check Character Set

SQL> select parameter,value from nls_database_parameters where parameter in (‘NLS_CHARACTERSET‘,‘NLS_NCHAR_CHARACTERSET‘);

PARAMETER VALUE
—————————— ——————————————————————————–
NLS_CHARACTERSET ZHS16GBK
NLS_NCHAR_CHARACTERSET AL16UTF16

Install Oracle GoldenGate

Configuration Environment for GoldenGate

export GG_HOME=/ggs
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/10.2.0/db_1
export ORACLE_SID=zwc
export PATH=$ORACLE_HOME/bin:$GG_HOME:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$$ORACLE_HOME/lib32:$GG_HOME:$LD_LIBRARY_PATH
export NLS_LANG=AMERICAN_AMERICA.ZHS16GBK

Create GoldenGate Directories

[oracle@zwc ggs]$ ggsci

Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.6 16211226 OGGCORE_11.2.1.0.6_PLATFORMS_130418.1829_FBO
Linux, x64, 64bit (optimized), Oracle 10g on Apr 18 2013 22:43:23

GGSCI (zwc) 1> create subdirs

Creating subdirectories under current directory /ggs

Parameter files /ggs/dirprm: already exists
Report files /ggs/dirrpt: created
Checkpoint files /ggs/dirchk: created
Process status files /ggs/dirpcs: created
SQL script files /ggs/dirsql: created
Database definitions files /ggs/dirdef: created
Extract data files /ggs/dirdat: created
Temporary files /ggs/dirtmp: created
Stdout files /ggs/dirout: created

Create “diroby”,"dirdsc" directories for OGG obey scripts and discard filesrespectively

[oracle@zwc ggs]$ pwd
/ggs
[oracle@zwc ggs]$ mkdir diroby dirdsc

郑重声明：本站内容如果来自互联网及其他传播媒体，其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享，并不代表本站赞同其观点和对其真实性负责，也不构成任何其他建议。

Zero Downtime Upgrade of Oracle 10g to Oracle 11g Using GoldenGate — 1