OGG不同表指定不同extract、dump、replicat

最新推荐文章于 2023-06-21 17:02:50 发布

原创

最新推荐文章于 2023-06-21 17:02:50 发布 · 890 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #dba #数据库 #ogg

本文详细介绍了如何在Oracle环境中配置 Extract、DUMP和Replicat进程，包括创建表空间、配置对象抽取、同步表和视图，以及添加补充日志和迁移过程。涉及对象选择、表空间管理、DDL复制和数据迁移等关键步骤。


注意：每个extract对应一个dump和replicat，有多个个extract就有多少个dump和replicat

1.1 环境准备

create tablespace test_tbs datafile '/u01/app/oracle/oradata/ggg11g/test_tbs01.dbf' size 10M autoextend on maxsize unlimited;
create user test identified by test account unlock default tablespace test_tbs;
grant  unlimited tablespace to test;
grant connect,resource to test;
grant select any dictionary to test;
grant select any table to test;
grant create view to test;

-- 创建相关测试对象
conn test/test;
create table t1 as select * from scott.emp;
create table t2 as select * from scott.dept;
create table t3 as select * from dba_objects;
create table t4 as select * from dba_users;
create index idx_t1_sal on t1(sal);
create or replace view v_sel_t1_empno_ename_sal as select empno,ename,sal from t1;

-- 查询test下的对象

col object_name for a25;
select object_name,object_type from dba_objects where owner = upper('test');

OBJECT_NAME               OBJECT_TYPE
------------------------- -------------------
T1                        TABLE
T2                        TABLE
T3                        TABLE
IDX_T1_SAL                INDEX
V_SEL_T1_EMPNO_ENAME_SAL  VIEW
T4                        TABLE

1.2 目标端创建需要被同步的对象的表空间

-- 查看test用户下对象关联的表空间，需要手动在目标端创建
col tablespace_name for a30;
select distinct owner,
       tablespace_name
from   dba_segments
where  owner in (select username
                 from   dba_users 
                 where  username = 'TEST'
                 and    account_status = 'OPEN');


OWNER      TABLESPACE_NAME
---------- ------------------------------
TEST       TEST_TBS


-- 查看对应表空间的大小

set line 121;
set pagesize 49999;
col tablespace_name format a25; 
col used_percent format a12;
col used_gb format 99999;
col size_gb format 99999;
select a.tablespace_name,
       round(a.tablespace_size * b.value / 1024 / 1024 / 1024, 2) TOTAL_GB,
       round(a.used_space * b.value / 1024 / 1024 / 1024, 2) USED_GB,
       round(a.tablespace_size * b.value / 1024 / 1024 / 1024, 2) -
       round(a.used_space * b.value / 1024 / 1024 / 1024, 2) FREE_GB,
       ROUND(A.used_percent, 2)|| '%' used_percent
 from  dba_tablespace_usage_metrics a, v$parameter b
 where b.NAME = 'db_block_size'
and    a.tablespace_name in (select distinct tablespace_name
                             from   dba_segments
                             where  owner in (select username
                                              from   dba_users 
                                              where  username = 'TEST'
                                              and    account_status = 'OPEN'))
 order by used_percent DESC;

TABLESPACE_NAME             TOTAL_GB USED_GB    FRE