--环境介绍
源端: 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
源端: 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/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/10972173/viewspace-2126171/
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
