搭建oracle 11.2.0.4環境下的goldengate複製

--環境介紹
源端:   oracle 11.2.0.4   Red Hat Enterprise Linux Server release 6.4 (Santiago)
目標端: oracle 11.2.0.4   Red Hat Enterprise Linux Server release 6.4 (Santiago)
OGG版本: Version 11.2.1.0.1

---準備工作
1. 建立表空間、使用者
-- 建立表空間
create tablespace TBS_OGG datafile '/home/db/oracle/oradata/hndcx/tbs_ogg_01.dbf' size 1024m;

-- Create the user 
create user OGG identified by ogg
  default tablespace TBS_OGG
  temporary tablespace TEMP
  profile DEFAULT
  quota unlimited on tbs_OGG;
-- Grant/Revoke role privileges 
grant connect to OGG;
grant dba to OGG;
grant ggs_ggsuser_role to OGG;
grant resource to OGG;
-- Grant/Revoke system privileges 
grant alter session to OGG;
grant create any directory to OGG;
grant create sequence to OGG;
grant create table to OGG;
grant drop any directory to OGG;
grant unlimited tablespace to OGG;
GRANT EXECUTE ON utl_file TO OGG;
*********************************************************************************************************************************************
*********************************************************************************************************************************************
1.清除之前的環境(如果是全新安裝，略過此步)
--目標端
sqlplus scott/tiger
SQL> drop table t1;

Table dropped.

SQL> drop table t2;

Table dropped.

--源端
GGSCI (BJ-CP-7F-113-64) 1> stop er *

Sending STOP request to EXTRACT GGEXT ...
Request processed.

Sending STOP request to EXTRACT GGPUP ...
Request processed.

GGSCI (BJ-CP-7F-113-64) 2> stop mgr
Manager process is required by other GGS processes.
Are you sure you want to stop it (y/n)? y


Sending STOP request to MANAGER ...
Request processed.
Manager stopped.

SQL> @ddl_disable

Trigger altered.

SQL> @ddl_remove

DDL replication removal script.
WARNING: this script removes all DDL replication objects and data.

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.

Enter Oracle GoldenGate schema name:ogg
Working, please wait ...
Spooling to file ddl_remove_spool.txt

SQL> @marker_remove

Marker removal script.
WARNING: this script removes all marker objects and data.

You will be prompted for the name of a schema for the Oracle GoldenGate database objects.
NOTE: The schema must be created prior to running this script.

Enter Oracle GoldenGate schema name:ogg

PL/SQL procedure successfully completed.

Sequence dropped.

Table dropped.

Script complete.


GGSCI (BJ-CP-7F-113-64) 2> dblogin userid ogg password ogg
Successfully logged into database.

GGSCI (BJ-CP-7F-113-64) 5> delete trandata scott.t1

Logging of supplemental redo log data disabled for table SCOTT.T1.

GGSCI (BJ-CP-7F-113-64) 6> delete trandata scott.t2

Logging of supplemental redo log data disabled for table SCOTT.T2.


GGSCI (BJ-CP-7F-113-64) 16> delete EXTRACT GGPUP
Deleted EXTRACT GGPUP.


GGSCI (BJ-CP-7F-113-64) 17> delete EXTRACT  GGEXT
Deleted EXTRACT GGEXT.

--目標端
GGSCI (BJ-CP-7F-113-65) 8> delete CHECKPOINTTABLE ogg.checktab
This checkpoint table may be required for other installations.  Are you sure you want to delete this checkpoint table? y

Successfully deleted checkpoint table ogg.checktab.
                                     

*********************************************************************************
2.重新配置ogg環境

--源端及目標端建立目錄(源端、目標端均執行)

GGSCI (BJ-CP-7F-113-64) 2> create subdirs

Creating subdirectories under current directory /home/oracle/install/ogg

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


--源端及目標端建立manager配置檔案 (源端、目標端均執行)

EDIT PARAMS MGR

內容如下:
PORT 7809
DYNAMICPORTLIST 7840-7914
PURGEOLDEXTRACTS ./dirdat/*, usecheckpoints, minkeepdays 1
LAGREPORTHOURS 1
LAGINFOMINUTES 30
LAGCRITICALMINUTES 45
PURGEDDLHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
PURGEMARKERHISTORY MINKEEPDAYS 7, MAXKEEPDAYS 10
AUTORESTART ER *, RETRIES 3, WAITMINUTES 5, RESETMINUTES 60

GGSCI (BJ-CP-7F-113-65) 2> start mgr

Manager started.

--為了支援DDL操作，執行以下步驟---源端

edit params GLOBALS

GGSCHEMA ogg

--開啟SUPPLEMENTAL_LOG_DATA_MIN 


select SUPPLEMENTAL_LOG_DATA_MIN from v$database;
alter database add supplemental log data ;
select SUPPLEMENTAL_LOG_DATA_MIN from v$database;

--以sysdba身份
@marker_setup.sql
@ddl_setup.sql
@role_setup.sql
GRANT  GGS_GGSUSER_ROLE  TO ogg;
@ddl_enable.sql
@ddl_pin ogg

--源端建立extract
GGSCI (BJ-CP-7F-113-64) 16> add extract ggext,tranlog, begin now
EXTRACT added.

GGSCI (BJ-CP-7F-113-64) 17> add exttrail ./dirdat/aa, extract ggext
EXTTRAIL added.


GGSCI (BJ-CP-7F-113-64) 18> edit params ggext

extract ggext
SETENV (ORACLE_SID="pointdev") 
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
SETENV (ORACLE_HOME = "/home/db/oracle/product/11.2.0")
USERID ogg,PASSWORD ogg

exttrail ./dirdat/aa
PURGEOLDEXTRACTS ./dirdat/aa*, NOUSECHECKPOINTS, MINKEEPHOURS 48
DISCARDFILE ./dirrpt/ggsext.dsc,PURGE

dynamicresolution
DDL &
INCLUDE OBJNAME scott.t1 &
INCLUDE OBJNAME scott.t2 


table SCOTT.T1;
table SCOTT.T2;

--源端建立DataPump:ggpup

GGSCI (BJ-CP-7F-113-64) 19> add ext ggpup, exttrailsource ./dirdat/aa
EXTRACT added.


GGSCI (BJ-CP-7F-113-64) 20> add rmttrail ./dirdat/aa, ext ggpup, megabytes 200
RMTTRAIL added.

GGSCI (BJ-CP-7F-113-64) 21> edit params ggpup

extract ggpup
SETENV (NLS_LANG="AMERICAN_AMERICA.ZHS16GBK")
GETENV (NLS_LANG)
passthru
REPORT AT 01:59
reportrollover at 02:00
rmthost 192.168.113.65, mgrport 7809
rmttrail ./dirdat/aa

dynamicresolution

table SCOTT.T1;
table SCOTT.T2;

--目標端建立replicat

GGSCI (BJ-CP-7F-113-65) 1> edit param ./GLOBALS

checkpointtable ogg.checktab

GGSCI (BJ-CP-7F-113-65) 4> dblogin userid ogg password ogg
Successfully logged into database.

GGSCI (BJ-CP-7F-113-65) 5> add checkpointtable ogg.checktab

Successfully created checkpoint table ogg.checktab.

GGSCI (BJ-CP-7F-113-65) 8> add rep ggrep, exttrail ./dirdat/aa, nodbcheckpoint,begin now
REPLICAT added.


GGSCI (BJ-CP-7F-113-65) 9> edit param ggrep

replicat ggrep
SETENV (NLS_LANG=AMERICAN_AMERICA.ZHS16GBK)
GETENV (NLS_LANG)
SETENV (LANG=zh_CN.GBK)
GETENV (LANG)
SETENV (ORACLE_HOME=/home/db/oracle/product/11.2.0)
GETENV (ORACLE_HOME)
SETENV (ORACLE_SID=hndcx)
GETENV (ORACLE_SID)
userid ogg, password ogg
ddl include all  
ddlerror default ignore retryop maxretries 3 retrydelay 5 
REPORT AT 01:59
reportrollover at 02:00
reperror default,discard
discardfile ./dirrpt/ggrep.dsc, APPEND, MEGABYTES 1000

assumetargetdefs
allownoopupdates
dynamicresolution
numfiles 3000

map SCOTT.T1, target SCOTT.T1;
map SCOTT.T2, target SCOTT.T2;

*********************************************************************************
3.啟動源端的mgr、extract和data pump程式


GGSCI (BJ-CP-7F-113-64) 23> start extract ggext


Sending START request to MANAGER ...
EXTRACT GGEXT starting

GGSCI (BJ-CP-7F-113-64) 24> start extract ggpup

Sending START request to MANAGER ...
EXTRACT GGPUP starting

GGSCI (BJ-CP-7F-113-64) 25> info all


Program     Status      Group       Lag at Chkpt  Time Since Chkpt


MANAGER     RUNNING                                           
EXTRACT     RUNNING     GGEXT       00:10:08      00:00:07    
EXTRACT     RUNNING     GGPUP       00:00:00      00:07:57    

*********************************************************************************
4. 用帶FLASHBACK_SCN 的expdp/impdp完成初始化

--在源端查詢當前的SCN

SQL> select count(*) from t1;

  COUNT(*)
----------
    99990

SQL> select count(*) from t2;

  COUNT(*)
----------
    99990

SQL> select current_scn from v$database;

CURRENT_SCN
-----------
   16745690

--匯出t1和t2表
SQL> grant read,write on directory dump_dir to scott;

Grant succeeded.

oracle@BJ-CP-7F-113-64:/home/oracle/dump_dir$ expdp  scott/tiger directory=dump_dir dumpfile=scott.dmp logfile=scott.log tables=t1,t2 flashback_scn=16745690

Export: Release 11.2.0.4.0 - Production on Tue Oct 11 17:22:13 2016


Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Starting "SCOTT"."SYS_EXPORT_TABLE_01":  scott/******** directory=dump_dir dumpfile=scott.dmp logfile=scott.log tables=t1,t2 flashback_scn=16745690 
Estimate in progress using BLOCKS method...
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 9 MB
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."T2"                                4.000 MB   99990 rows
. . exported "SCOTT"."T1"                                2.951 MB   99990 rows
Master table "SCOTT"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /home/oracle/dump_dir/scott.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Tue Oct 11 17:22:29 2016 elapsed 0 00:00:11

******************************************************************************
Dump file set for SCOTT.SYS_EXPORT_TABLE_01 is:
  /home/oracle/dump_dir/scott.dmp
Job "SCOTT"."SYS_EXPORT_TABLE_01" successfully completed at Tue Oct 11 16:22:23 2016 elapsed 0 00:00:15


--模擬刪除部分表資料
SQL> delete from t1 where rownum<=10000;

10000 rows deleted.

SQL> delete from t2  where rownum<=10000;

10000 rows deleted.

SQL> commit;

Commit complete.

SQL> select count(*) from t1;

  COUNT(*)
----------
     89990

SQL> select count(*) from t2;

  COUNT(*)
----------
     89990

--將dump檔案傳輸到目標端,匯入到scott使用者下

SQL> grant read,write on directory dump_dir to scott;

Grant succeeded.

oracle@BJ-CP-7F-113-65:/home/oracle/backup$ impdp scott/tiger directory=dump_dir dumpfile=scott.dmp logfile=imp_scott.log tables=t1,t2 remap_tablespace=OGG_TEST:USERS table_exists_action=replace

Import: Release 11.2.0.4.0 - Production on Tue Oct 11 17:25:26 2016

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.
;;; 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Master table "SCOTT"."SYS_IMPORT_TABLE_01" successfully loaded/unloaded
Starting "SCOTT"."SYS_IMPORT_TABLE_01":  scott/******** directory=dump_dir dumpfile=scott.dmp logfile=imp_scott.log tables=t1,t2 remap_tablespace=OGG_TEST:USERS table_exists_action=replace 
Processing object type TABLE_EXPORT/TABLE/TABLE
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "SCOTT"."T2"                                4.000 MB   99990 rows
. . imported "SCOTT"."T1"                                2.951 MB   99990 rows
Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX
Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
Job "SCOTT"."SYS_IMPORT_TABLE_01" successfully completed at Tue Oct 11 17:25:40 2016 elapsed 0 00:00:07 

*********************************************************************************
5.用scn啟動replicat程式
GGSCI (BJ-CP-7F-113-65) 2> start ggrep, aftercsn 16745690

Sending START request to MANAGER ...
REPLICAT GGREP starting


GGSCI (BJ-CP-7F-113-65) 3> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING                                           
REPLICAT    RUNNING     GGREP       00:08:53      00:00:00   


*********************************************************************************
6.驗證
--源端
SQL> select count(*) from t1;

  COUNT(*)
----------
     89990

SQL> select count(*) from t2;


  COUNT(*)
----------
     89990

--目標端
SQL> select count(*) from t1;

  COUNT(*)
----------
     89990

SQL> select count(*) from t2;

  COUNT(*)
----------
     89990

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