oracle ogg数据格式转换,Oracle 19C OGG基础运维-02数据初始化

最新推荐文章于 2022-12-06 15:05:00 发布

制度风洞

最新推荐文章于 2022-12-06 15:05:00 发布

阅读量752

点赞数

文章标签： oracle ogg数据格式转换

Oracle 19C OGG基础运维-02数据初始化

一初始化注意事项

1 为了提高数据同步速度，目标端数据库取消归档模式。

2 为了避免可能发生的导入错误，禁用目标端数据库的外键和check约束。

3 为了避免生成重复数据，禁用目标端数据库触发器。

4 为了避免生成重复数据，禁用目标端数据库有DML操作的JOB。

1 为了提高数据同步速度，目标端数据库取消归档模式。

SQL> startup mount

SQL> alter database noarchivelog;

SQL> alter database open;

2 为了避免可能发生的导入错误，禁用目标端数据库的外键和check约束。

---disable references---

declare

v_sql varchar2(2000);

cursor c_ref is

select 'alter table ' || owner || '.' || table_name ||

' disable constraint ' || constraint_name

from dba_constraints

where constraint_type in ('R', 'C')

and owner in ('CHEN');

BEGIN

OPEN c_ref;

loop

fetch c_ref

into v_sql;

exit when c_ref%NOTFOUND;

EXECUTE IMMEDIATE v_sql;

end loop;

close c_ref;

end;

3 为了避免生成重复数据，禁用目标端数据库触发器。

---disable trigger---

declare

v_sql varchar2(2000);

cursor c_trigger is

select 'alter trigger ' || owner || '.' || trigger_name || 'disable '

from dba_triggers

where owner in ('CHENT');

BEGIN

OPEN c_trigger;

loop

fetch c_trigger

into v_sql;

exit when c_trigger%NOTFOUND;

execute immediate v_sql;

end loop;

close c_trigger;

end;

4 为了避免生成重复数据，禁用目标端数据库有DML操作的JOB。

---disable job---

select * from dba_jobs where SCHEMA_USER='CHEN';

alter system set JOB_QUEUE_PROCESSES=0;

---disable scheduler job

select job_name, owner, program_name, program_owner, state, enabled

from dba_scheduler_jobs

where owner not in ('SYS', 'SYSTEM');

exec dbms_scheduler.disable('xxxxxx');

二：初始化方式

1 initial load直接传输初始化

2 expdp/impdp

3 rman

1 initial load直接传输初始化

目标端创建表结构

---源端：导出表结构

SQL> create or replace directory expdp_dir as '/backup';

SQL> grant read,write on directory expdp_dir to public;

[oracle@cjcos01 backup]$

expdp cjc/cjc@cjcpdb DIRECTORY=expdp_dir CONTENT=METADATA_ONLY

DUMPFILE=cjc_metadata.dmp logfile=cjc_expdp_metadata.log schemas=cjc

[oracle@cjcos01 backup]$ scp cjc_metadata.dmp 192.168.31.100:/backup

---目标端：导入表结构

[

oracle@cjcos02 backup]$

impdp chen/chen@chenpdb DIRECTORY=expdp_dir

DUMPFILE=cjc_metadata.dmp logfile=cjc_impdp_metadata.log

remap_schema=cjc:chen remap_tablespace=cjctbs:chentbs

源端：mgr

GGSCI (cjcos01 as c##ogg@cjcdb/CDB$ROOT) 52> view param mgr

PORT 7809

DYNAMICPORTLIST 7809-8000

AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3

PURGEOLDEXTRACTS ./dirdat/,usecheckpoints, minkeepdays 3

ACCESSRULE, PROG *, IPADDR 192.168.31.*, ALLOW

源端：einit_01

GGSCI (cjcos01 as c##ogg@cjcdb/CDB$ROOT) 51> view param einit_01

EXTRACT einit_01

SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

userid c##ogg@cjcdb,password oracle

RMTHOST 192.168.31.100, MGRPORT 7809

RMTTASK REPLICAT, GROUP rinit_01

TABLE cjcpdb.cjc.emp;

TABLE cjcpdb.cjc.dept;

TABLE cjcpdb.cjc.bonus;

TABLE cjcpdb.cjc.salgrade;

TABLE cjcpdb.cjc.dummy;

GGSCI (cjcos01 as c##ogg@cjcdb/CDB$ROOT) 5>

add extract einit_01,sourceistable

---sourceistable：标识这是一个initial-load抽取进程，直接从数据库的表中读取数据。

GGSCI (cjcos01 as c##ogg@cjcdb/CDB$ROOT) 42>

2020-04-07 15:56:46 INFO OGG-02003 Extract EINIT_01 successfully registered with database at SCN 3311611.

目标端：mgr

GGSCI (cjcos02 as chen@chendb/CHENPDB) 43> view param mgr

PORT 7809

DYNAMICPORTLIST 7810-8000

AUTORESTART EXTRACT *,RETRIES 5,WAITMINUTES 3

PURGEOLDEXTRACTS ./dirdat/,usecheckpoints, minkeepdays 3

ACCESSRULE, PROG *, IPADDR 192.168.31.*, ALLOW

目标端：rinit_01

GGSCI (cjcos02 as chen@chendb/CHENPDB) 44> view param rinit_01

REPLICAT rinit_01

SETENV (NLS_LANG=AMERICAN_AMERICA.AL32UTF8)

userid ogg@chenpdb,password oracle

DISCARDFILE /ogg/dirrpt/RINITXX.dsc, PURGE

MAP cjcpdb.cjc.emp, TARGET chenpdb.chen.emp;

MAP cjcpdb.cjc.dept, TARGET chenpdb.chen.dept;

MAP cjcpdb.cjc.bonus, TARGET chenpdb.chen.bonus;

MAP cjcpdb.cjc.salgrade, TARGET chenpdb.chen.salgrade;

MAP cjcpdb.cjc.dummy, TARGET chenpdb.chen.dummy;

添加组：

GGSCI (cjcos02 as c##ogg@chendb/CDB$ROOT) 3>

add replicat rinit_01,specialrun

REPLICAT added.

源端：

GGSCI (cjcos01 as c##ogg@cjcdb/CDB$ROOT) 49>

start extract einit_01

源端启动抽取进程einit_01后，不需要手动启动目标端的rinit_01进程，MGR会自动启动它，等同步结束，它会自动关闭。

目标端：查看日志

[oracle@cjcos02 ogg]$ tail -f ggserr.log

......

2020-04-07T20:43:29.562+0800 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from EXTRACT on host [192.168.31.90]:62597 (START REPLICAT rinit_01 CPU -1 PRI -1 PARAMS ).

2020-04-07T20:43:29.562+0800 INFO OGG-00960 Oracle GoldenGate Manager for Oracle, mgr.prm: Access granted (rule #1).

2020-04-07T20:43:31.120+0800 INFO OGG-01888 Oracle GoldenGate Delivery for Oracle: TCP network is configured as

......

2020-04-07T20:43:31.399+0800 INFO OGG-01025 Oracle GoldenGate Delivery for Oracle: REPLICAT task started by manager (port 7810).

2020-04-07T20:43:31.446+0800 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from RMTTASK on host [::1]:54525 (REPORT 28081 7810).

2020-04-07T20:43:31.446+0800 INFO OGG-00960 Oracle GoldenGate Manager for Oracle, mgr.prm: Access granted (rule #1).

2020-04-07T20:43:31.474+0800 INFO OGG-00973 Oracle GoldenGate Manager for Oracle, mgr.prm: Manager started replicat task process (Port 7810).

2020-04-07T20:43:36.481+0800 INFO OGG-01229 Oracle GoldenGate Delivery for Oracle: Connected to cjcos01:45948.

2020-04-07T20:43:36.664+0800 INFO OGG-00995 Oracle GoldenGate Delivery for Oracle: REPLICAT rinit_01 starting.

2020-04-07T20:43:36.754+0800 INFO OGG-03059 Oracle GoldenGate Delivery for Oracle: Operating system character set identified as UTF-8.

2020-04-07T20:43:36.754+0800 INFO OGG-02695 Oracle GoldenGate Delivery for Oracle: ANSI SQL parameter syntax is used for parameter parsing.

2020-04-07T20:43:36.754+0800 INFO OGG-02095 Oracle GoldenGate Delivery for Oracle: Successfully set environment variable NLS_LANG=AMERICAN_AMERICA.AL32UTF8.

2020-04-07T20:43:37.424+0800 INFO OGG-01360 Oracle GoldenGate Delivery for Oracle: REPLICAT is running in Remote Task mode.

2020-04-07T20:43:41.833+0800 INFO OGG-02679 Oracle GoldenGate Delivery for Oracle: The Replicat process logged on to database CHENPDB and can only apply to that database.

2020-04-07T20:43:42.651+0800 INFO OGG-06451 Oracle GoldenGate Delivery for Oracle: Triggers will be suppressed by default.

2020-04-07T20:43:42.723+0800 INFO OGG-01815 Oracle GoldenGate Delivery for Oracle: Virtual Memory Facilities for: COM

......

2020-04-07T20:43:42.757+0800 INFO OGG-06604 Oracle GoldenGate Delivery for Oracle: Database CHENPDB CPU info: CPU Count 1, CPU Core Count 1, CPU Socket Count 1.

2020-04-07T20:43:42.772+0800 INFO OGG-00996 Oracle GoldenGate Delivery for Oracle: REPLICAT rinit_01 started.

2020-04-07T20:43:43.881+0800 INFO OGG-06505 Oracle GoldenGate Delivery for Oracle: MAP resolved (entry cjcpdb.cjc.emp): MAP "CJCPDB"."CJC"."EMP", TARGET chenpdb.chen.emp.

2020-04-07T20:43:54.243+0800 WARNING OGG-06439 Oracle GoldenGate Delivery for Oracle: No unique key is defined for table EMP. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2020-04-07T20:43:54.245+0800 INFO OGG-02756 Oracle GoldenGate Delivery for Oracle: The definition for table CJCPDB.CJC.EMP is obtained from the trail file.

2020-04-07T20:43:54.248+0800 INFO OGG-06511 Oracle GoldenGate Delivery for Oracle: Using following columns in default map by name: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO.

2020-04-07T20:43:54.248+0800 INFO OGG-06510 Oracle GoldenGate Delivery for Oracle: Using the following key columns for target table CHENPDB.CHEN.EMP: EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO.

2020-04-07T20:43:55.456+0800 INFO OGG-06505 Oracle GoldenGate Delivery for Oracle: MAP resolved (entry cjcpdb.cjc.dept): MAP "CJCPDB"."CJC"."DEPT", TARGET chenpdb.chen.dept.

2020-04-07T20:43:59.795+0800 WARNING OGG-06439 Oracle GoldenGate Delivery for Oracle: No unique key is defined for table DEPT. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2020-04-07T20:43:59.796+0800 INFO OGG-02756 Oracle GoldenGate Delivery for Oracle: The definition for table CJCPDB.CJC.DEPT is obtained from the trail file.

2020-04-07T20:43:59.796+0800 INFO OGG-06511 Oracle GoldenGate Delivery for Oracle: Using following columns in default map by name: DEPTNO, DNAME, LOC.

2020-04-07T20:43:59.796+0800 INFO OGG-06510 Oracle GoldenGate Delivery for Oracle: Using the following key columns for target table CHENPDB.CHEN.DEPT: DEPTNO, DNAME, LOC.

2020-04-07T20:44:00.880+0800 INFO OGG-06505 Oracle GoldenGate Delivery for Oracle: MAP resolved (entry cjcpdb.cjc.salgrade): MAP "CJCPDB"."CJC"."SALGRADE", TARGET chenpdb.chen.salgrade.

2020-04-07T20:44:10.706+0800 WARNING OGG-06439 Oracle GoldenGate Delivery for Oracle: No unique key is defined for table SALGRADE. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2020-04-07T20:44:10.707+0800 INFO OGG-02756 Oracle GoldenGate Delivery for Oracle: The definition for table CJCPDB.CJC.SALGRADE is obtained from the trail file.

2020-04-07T20:44:10.707+0800 INFO OGG-06511 Oracle GoldenGate Delivery for Oracle: Using following columns in default map by name: GRADE, LOSAL, HISAL.

2020-04-07T20:44:10.707+0800 INFO OGG-06510 Oracle GoldenGate Delivery for Oracle: Using the following key columns for target table CHENPDB.CHEN.SALGRADE: GRADE, LOSAL, HISAL.

2020-04-07T20:44:10.784+0800 INFO OGG-06505 Oracle GoldenGate Delivery for Oracle: MAP resolved (entry cjcpdb.cjc.dummy): MAP "CJCPDB"."CJC"."DUMMY", TARGET chenpdb.chen.dummy.

2020-04-07T20:44:14.918+0800 WARNING OGG-06439 Oracle GoldenGate Delivery for Oracle: No unique key is defined for table DUMMY. All viable columns will be used to represent the key, but may not guarantee uniqueness. KEYCOLS may be used to define the key.

2020-04-07T20:44:14.918+0800 INFO OGG-02756 Oracle GoldenGate Delivery for Oracle: The definition for table CJCPDB.CJC.DUMMY is obtained from the trail file.

2020-04-07T20:44:14.918+0800 INFO OGG-06511 Oracle GoldenGate Delivery for Oracle: Using following columns in default map by name: DUMMY.

2020-04-07T20:44:14.918+0800 INFO OGG-06510 Oracle GoldenGate Delivery for Oracle: Using the following key columns for target table CHENPDB.CHEN.DUMMY: DUMMY.

2020-04-07T20:44:20.289+0800 INFO OGG-00994 Oracle GoldenGate Delivery for Oracle: REPLICAT rinit_01 stopped normally.

目标端：检查初始化数据

SQL> conn chen/chen@chenpdb

Connected.

SQL> select count(*) from emp;

COUNT(*)

----------

SQL> select count(*) from dept;

COUNT(*)

----------

SQL> select count(*) from bonus;

COUNT(*)

----------

SQL> select count(*) from salgrade;

COUNT(*)

----------

SQL> select count(*) from dummy;

COUNT(*)

----------

2 expdp/impdp

源端：

查看源端scn

SQL> select dbms_flashback.get_system_change_number from dual;

----

3417750

[oracle@cjcos01 backup]$

expdp cjc/cjc@cjcpdb DIRECTORY=expdp_dir DUMPFILE=cjc_ini.dmp

logfile=cjc_expdp_ini.log tables=cjc.DEPT,cjc.EMP,cjc.BONUS,cjc.SALGRADE,cjc.dummy flashback_scn=3417750

[oracle@cjcos01 backup]$ scp cjc_ini.dmp 192.168.31.100:/backup

目标端：

[oracle@cjcos02 ~]$

impdp chen/chen@chenpdb DIRECTORY=expdp_dir DUMPFILE=cjc_ini.dmp

logfile=cjc_impdp_ini.log remap_schema=cjc:chen remap_tablespace=cjctbs:chentbs table_exists_action=REPLACE

从(从备份时刻的scn开始启动):

GGSCI (host02) 15>

start replicat RORA_1,aftercsn 3417750

3 rman

源端：rman备份cjcpdb数据库(单个pdb)

SQL> select min(start_time) from v$transaction;

[oracle@cjcos01 backup]$ cat rman.sh

###### rman.sh begin

export NLS_DATE_FORMAT='yyyymmdd hh24:mi:ss'

rman target / log =/backup/rman/rman.log <

crosscheck archivelog all;

run{

allocate channel ch1 type disk;

allocate channel ch2 type disk;

backup pluggable database cjcpdb tag 'full_cjcpdb' format '/backup/rman/%d_full_%T_%U.bak';

sql 'alter system archive log current';

backup archivelog all tag 'arch_cjcpdb' format '/backup/rman/%d_arch_%T_%U.bak';

backup current controlfile tag 'ctl_cjcpdb' format '/backup/rman/%d_ctl_%T_%U.bak';

release channel ch1;

release channel ch2;

}

exit;

EOF

### rman.sh end

---后台执行

[oracle@cjcos01 backup]$ nohup sh rman.sh &

---查看备份文件

[oracle@cjcos01 rman]$ ll -rth CJCDB_*

-rw-r----- 1 oracle oinstall 4.3M Apr 8 16:50 CJCDB_full_20200408_25ut50m4_1_1.bak

-rw-r----- 1 oracle oinstall 639M Apr 8 16:51 CJCDB_full_20200408_24ut50m4_1_1.bak

-rw-r----- 1 oracle oinstall 1.3G Apr 8 16:53 CJCDB_arch_20200408_28ut50om_1_1.bak

-rw-r----- 1 oracle oinstall 1.5G Apr 8 16:53 CJCDB_arch_20200408_27ut50ol_1_1.bak

-rw-r----- 1 oracle oinstall 338K Apr 8 16:54 CJCDB_arch_20200408_29ut50tf_1_1.bak

-rw-r----- 1 oracle oinstall 18M Apr 8 16:54 CJCDB_ctl_20200408_2aut50tm_1_1.bak

---将备份集传到目标端

目标端：恢复cjcpdb数据库(单个pdb)(恢复操作没有测试，仅供参考)

startup nomount

---恢复控制文件

run {

allocate channel d1 device type disk;

resource controlfile from 'controlfile_backuppiece_name';

release channel d1;

}

alter database mount;

---还原数据库

show all

CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;

run {

allocate channel d1 device type disk;

restore pluggable database cjcpdb;

release channel d1;

}

---还原归档日志

run {

allocate channel d1 device type disk;

restore archivelog from logseq 34503;

release channel d1;

}

---恢复数据库

run {

allocate channel d1 device type disk;

recover pluggable database cjcpdb using backup controlfile until cancel;

release channel d1;

}

---查询并记录数据文件的scn

SQL> select checkpoint_change#,file# from v$datafile_header;

--启动replicat的scn

---打开数据库

alter database open database resetlogs;

---修改目标端为非归档模式

startup mount

alter database noarchivelog;

alter database open;

启动目标端恢复进程

GGSCI > start repya,aferscn xxxxxx

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！

制度风洞

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
oracle ogg数据格式转换,Oracle 19C OGG基础运维-02数据初始化

Oracle 19C OGG基础运维-02数据初始化一初始化注意事项1 为了提高数据同步速度，目标端数据库取消归档模式。2 为了避免可能发生的导入错误，禁用目标端数据库的外键和check约束。3 为了避免生成重复数据，禁用目标端数据库触发器。4 为了避免生成重复数据，禁用目标端数据库有DML操作的JOB。1 为了提高数据同步速度，目标端数据库取消归档模式。SQL> startup moun...
复制链接

扫一扫