Oracle 19C OGG基礎運維-02資料初始化

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> register extract einit_01 database container(cjcpdb)

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(*)

----------

14

SQL> select count(*) from dept;

  COUNT(*)

----------

4

SQL> select count(*) from bonus;

  COUNT(*)

----------

0

SQL> select count(*) from salgrade;

  COUNT(*)

----------

5

SQL> select count(*) from dummy;

  COUNT(*)

----------

1

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 <<EOF 

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"，共同學習，共同成長！！！

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29785807/viewspace-2685056/，如需轉載，請註明出處，否則將追究法律責任。