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

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

阅读量：7214 次

发布时间：2019-06-29

本文共 5264 字，大约阅读时间需要 17 分钟。

Prepare 10g Database for OGG

Create GGS and GGS_MON Database Users

SQL> create tablespace ggs_tbs datafile '/u01/app/oracle/oradata/zwc/gg_tbs01.dbf' size 100M;

Tablespace created.

SQL> create user ggs identified by ggs default tablespace ggs_tbs temporary tablespace temp;

User created.

SQL> grant dba to ggs;

Grant succeeded.

SQL> create user ggs_mon identified by ggs_mon default tablespace ggs_tbs temporary tablespace temp;

User created.

SQL> grant connect,resource to ggs_mon;

Grant succeeded.

Enable Database Level Supplemental Logging

SQL> select name,supplemental_log_data_min from v$database;

NAME SUPPLEME

——— ——–

ZWC NO

SQL> alter database add supplemental log data;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> select name,supplemental_log_data_min from v$database;

NAME SUPPLEME

——— ——–

ZWC YES

Enable Force Logging

SQL> select force_logging from v$database;

FOR

—

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 281018368 bytes

Fixed Size 2083336 bytes

Variable Size 155190776 bytes

Database Buffers 117440512 bytes

Redo Buffers 6303744 bytes

Database mounted.

SQL> alter database force logging;

Database altered.

SQL> alter database open;

Database altered.

SQL> select force_logging from v$database;

FOR

—

YES

Check Table-Level Supplemental Logging

SQL> select t.owner,

2 t.tbl_cnt,

3 s.sup_log_grp_cnt,

4 t.tbl_cnt – s.sup_log_grp_cnt "Diff"

5 from (select owner, count(*) tbl_cnt from dba_tables group by owner) t,

6 (select owner, count(*) sup_log_grp_cnt

7 from dba_log_groups

8 group by owner) s

9 where t.owner = s.owner(+)

10 and t.owner in ('HR', 'OE', 'PM');

OWNER TBL_CNT SUP_LOG_GRP_CNT Diff

—– ———- ————— ———-

HR 7

PM 2

OE 12

If you are planning to use sqlplus then you can use commands like:

alter database <table_name> add supplemental log data (all) columns;

alter database <table_name> add supplemental log data (primary key) columns;

For this demo,we will use Oracle GoldenGate command interface to add table level supplemental logging.The command from ggsci interface is "add trandata <table_name>".

[oracle@zwc ggs]$ sqlplus ggs

SQL*Plus: Release 10.2.0.4.0 – Production on Thu Jun 5 22:01:53 2014

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 – 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> spool add_trandata.oby

SQL> set linesize 150 pagesize 0 feedback off

SQL> spool add_missing_trandata.oby

SQL> select 'add trandata ' || t.owner || '.' || t.table_name stmt

2 from (select owner, table_name from dba_tables) t,

3 (select owner, table_name from dba_log_groups) s

4 where t.owner = s.owner(+)

5 and t.table_name = s.table_name(+)

6 and s.table_name is null

7 and t.owner in ('HR', 'OE', 'PM');

add trandata HR.REGIONS

add trandata HR.LOCATIONS

add trandata HR.DEPARTMENTS

add trandata HR.JOBS

add trandata OE.WAREHOUSES

add trandata OE.ORDER_ITEMS

add trandata OE.ORDERS

add trandata OE.PRODUCT_INFORMATION

add trandata OE.PROMOTIONS

add trandata OE.SYS_IOT_OVER_52810

add trandata OE.SYS_IOT_OVER_52815

add trandata OE.PRODUCT_REF_LIST_NESTEDTAB

add trandata OE.SUBCATEGORY_REF_LIST_NESTEDTAB

add trandata HR.COUNTRIES

add trandata PM.ONLINE_MEDIA

add trandata PM.PRINT_MEDIA

add trandata OE.CUSTOMERS

add trandata HR.JOB_HISTORY

add trandata OE.PRODUCT_DESCRIPTIONS

add trandata OE.INVENTORIES

add trandata HR.EMPLOYEES

SQL> spool off

[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> dblogin userid ggs password ggs

Successfully logged into database.

GGSCI (zwc) 2> obey ./diroby/add_missing_trandata.oby

GGSCI (zwc) 3> add trandata HR.REGIONS

Logging of supplemental redo data enabled for table HR.REGIONS.

GGSCI (zwc) 4> add trandata HR.LOCATIONS

Logging of supplemental redo data enabled for table HR.LOCATIONS.

GGSCI (zwc) 5> add trandata HR.DEPARTMENTS

Logging of supplemental redo data enabled for table HR.DEPARTMENTS.

GGSCI (zwc) 6> add trandata HR.JOBS

Logging of supplemental redo data enabled for table HR.JOBS.

GGSCI (zwc) 7> add trandata OE.WAREHOUSES

SQL> select t.owner,

2 t.tbl_cnt,

3 s.sup_log_grp_cnt,

4 t.tbl_cnt – s.sup_log_grp_cnt "Diff"

5 from (select owner, count(*) tbl_cnt from dba_tables group by owner) t,

6 (select owner, count(*) sup_log_grp_cnt

7 from dba_log_groups

8 group by owner) s

9 where t.owner = s.owner(+)

10 and t.owner in ('HR', 'OE', 'PM');

OWNER TBL_CNT SUP_LOG_GRP_CNT Diff

—————————— ———- ————— ———-

HR 7 7 0

OE 12 8 4

PM 2 2 0

Create Tables for Heartbeat

SQL> create table ggs_mon.ggs_heartbeat(id number,ts date);

Table created.

SQL> insert into ggs_mon.ggs_heartbeat values(1,sysdate);

1 row created.

SQL> commit;

Commit complete.

SQL> create table ggs_mon.ggs_lagtime

2 (id number,

3 ts date,

4 committime date,

5 groupname varchar2(8),

6 host varchar2(60),

7 local_insert_time date);

Table created.

你可能感兴趣的文章

谈谈NITE 2的第一个程序HandViewer

Java_InvokeAll_又返回值_多个线程同时执行，取消超时线程

/*10个filter的属性*/ ---毛玻璃效果