Note: In below process i am considaring scott as my Application user.
part I) setup golden gate on source and target machine.
(below steps perform on both source and target database)
-check archive log mode of both database.
SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
-----------
NO NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
-----------
YES YES
SQL> create tablespace ggate
datafile '/u01/app/oracle/oradata/DB11G/ggate.dbf' size 5G;
Tablespace created.
SQL> create user ggate
identified by ****
default tablespace ggate;
User created.
SQL> GRANT dba TO ggate;
paert II) set up extract,datapump and replicate process on both source and targate side.
A) on source side node1:
[oracle@node1 ~]$ cd $GG_HOME
[oracle@node1 gg]$ ./ggsci
GGSCI (node1.example.com) > edit params manager
Saturday, September 30, 2017
7:12 PM
New Section 3 Page 1
----add below parameter
PORT 7809
DYNAMICPORTLIST 7810-7820
PURGEOLDEXTRACTS /u01/app/oracle/product/gg/dirdat/rx*, USECHECKPOINTS, MINKEEPHOURS 2
GGSCI (node1.example.com) > edit params CAPT01
EXTRACT CAPT01
setenv (ORACLE_SID="DB11G")
setenv (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID ggate, PASSWORD oracle
EXTTRAIL /u01/app/oracle/product/gg/dirdat/rx
TABLE scott.*;
GGSCI (node1.example.com) > edit params dp01
EXTRACT dp01
setenv (ORACLE_SID="DB11G")
setenv (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID ggate, PASSWORD ****
RMTHOST node2.example.com, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/rt
TABLE scott.*;
GGSCI (node1.example.com) > edit params frep01
REPLICAT frep01
setenv (ORACLE_SID="DB11G")
setenv (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID ggate, PASSWORD ****
ASSUMETARGETDEFS
MAP scott.*, TARGET scott.*;
GGSCI (node1.example.com) > EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE ggate.OGGCHKPT
GGSCI (node1.example.com) > start mgr
GGSCI (node1.example.com) > dblogin userid ggate, Password ***
GGSCI (node1.example.com) > add trandata scott.*
GGSCI (node1.example.com) > Add CheckpointTable
GGSCI (node1.example.com) > Add Extract capt01, TranLog, Begin Now
GGSCI (node1.example.com) > Add ExtTrail ./u01/app/oracle/product/gg/dirdat/rx, Extract capt01, Megabytes 5
GGSCI (node1.example.com) > Add Extract dp01, ExtTrailSource
/u01/app/oracle/product/gg/dirdat/rt
GGSCI (node1.example.com) > Add RmtTrail /u01/app/oracle/product/gg/dirdat/rt, Extract dp01, Megabytes 5
GGSCI (node1.example.com) > Add Replicat frep01, ExtTrail /u01/app/oracle/product/gg/dirdat/ft ----( this replicate process for fail back target to source)
GGSCI (node1.example.com) > info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED CAPT01 00:00:00 00:00:54
EXTRACT STOPPED DP01 00:00:00 00:00:46
REPLICAT STOPPED REP01 00:00:00 00:00:02
B) on target database.
[oracle@node2 ~]$ cd $GG_HOME
[oracle@node2 gg]$ ./ggsci
GGSCI (node2.example.com) > edit params mgr
----add below parameter
PORT 7809
DYNAMICPORTLIST 7810-7820
PURGEOLDEXTRACTS /u01/app/oracle/product/gg/dirdat/fx*, USECHECKPOINTS, MINKEEPHOURS 2
GGSCI (node2.example.com) > edit params FCAPT01
EXTRACT FCAPT01
setenv (ORACLE_SID="DB11G")
setenv (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID ggate, PASSWORD oracle
EXTTRAIL /u01/app/oracle/product/gg/dirdat/fx
TABLE scott.*;
GGSCI (node2.example.com) > edit params Fdp01
EXTRACT Fdp01
setenv (ORACLE_SID="DB11G")
setenv (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID ggate, PASSWORD ****
RMTHOST node2.example.com, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/ft
TABLE scott.*;
GGSCI (node2.example.com) > edit params rep01
REPLICAT rep01
setenv (ORACLE_SID="DB11G")
setenv (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID ggate, PASSWORD ****
ASSUMETARGETDEFS
MAP scott.*, TARGET scott.*;
GGSCI (node2.example.com) > EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE ggate.OGGCHKPT
GGSCI (node2.example.com) > start mgr
GGSCI (node2.example.com) > dblogin userid ggate, Password ***
GGSCI (node2.example.com) > Add CheckpointTable
GGSCI (node2.example.com) > add trandata scott.*
GGSCI (node2.example.com) > Add Extract Fcapt01, TranLog, Begin Now
GGSCI (node2.example.com) > Add ExtTrail ./u01/app/oracle/product/gg/dirdat/fx, Extract capt01, Megabytes 5 ---(process for fail back replication)
GGSCI (node2.example.com) > Add Extract Fdp01, ExtTrailSource /u01/app/oracle/product/gg/dirdat/ft
GGSCI (node2.example.com) > Add RmtTrail /u01/app/oracle/product/gg/dirdat/ft, Extract dp01, Megabytes 5 ----(process for fail back replication)
GGSCI (node2.example.com) > Add Replicat Frep01, ExtTrail /u01/app/oracle/product/gg/dirdat/rt
GGSCI (node2.example.com) > info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED FCAPT01 00:00:00 00:00:54
EXTRACT STOPPED FDP01 00:00:00 00:00:46
REPLICAT STOPPED FREP01 00:00:00 00:00:02
part III) start migration process
1)start capture process
GGSCI (node1.example.com) > start EXTRACT FCAPT01
GGSCI (node1.example.com) > start EXTRACT FDP01
2)check latest flshback_scn from source database
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1189734
3)export application schema data using import and export utility .
[oracle@node1 gg]$ expdp ggate schemas=scott directory=DATA_PUMP_DIR dumpfile=SCHEMA_scott_dp.dmp logfile=SCHEMA_scott_dp.log flashback_scn=1189734
Export: Release 11.2.0.3.0 -Production on Sat Sep 30 10:07:05 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 -64bit Production
New Section 3 Page 4
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 -64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "GGATE"."SYS_EXPORT_SCHEMA_01": ggate/******** schemas=scott directory=DATA_PUMP_DIR dumpfile=SCHEMA_scott_dp.dmp logfile=SCHEMA_scott_dp.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.984 KB 6 rows
. . exported "SCOTT"."EMP" 8.570 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.882 KB 6 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "GGATE"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for GGATE.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/DB11G/dpdump/SCHEMA_scott_dp.dmp
Job "GGATE"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:07:36
4)import Application user data usig import export utility:
[oracle@node1 gg]$ impdp ggate schemas=scott directory=DATA_PUMP_DIR dumpfile=SCHEMA_scott_dp.dmp logfile=SCHEMA_scott_dp.log
5)start replication process on target database.
GGSCI (node2.example.com) > start rep01 aftercsn 1189734;
6)wait utile source and target database in sync.
check lag both capt01 and DP01 process mass will be as follow
as EOF no more record to process
7)stop application.
8)stop exrtact, datapump,replicat process
9)start the GG failback process.
on tagate database which now source.
GGSCI (node2.example.com) > alter FCAPT01 begin now
GGSCI (node2example.com) > info FDP01 detail
GGSCI (node2.example.com) > start extract FDP01
GGSCI (node1.example.com) > alter FREP01 extseqno 2 ------(on source database)
GGSCI (node2.example.com) > alter FREP01 extrba 0
GGSCI (node2.example.com) > start EXTRACT FCAPT01
GGSCI (node2.example.com) > start EXTRACT FCAPT01
GGSCI (node2.example.com) > start FREP01
step 10)switch application to new database server.
now all Applications running on new database server , old database server is your standby database server once you
confirm about transaction , performance etc about new database server you can stop gg process.
note: during import export operation you can use extra parameter for fast performance and also change some parameter in init.ora file.
part I) setup golden gate on source and target machine.
(below steps perform on both source and target database)
-check archive log mode of both database.
SQL> SELECT log_mode FROM v$database;
LOG_MODE
------------
ARCHIVELOG
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
-----------
NO NO
SQL> ALTER DATABASE ADD SUPPLEMENTAL LOG DATA;
Database altered.
SQL> ALTER DATABASE FORCE LOGGING;
Database altered.
SQL> SELECT force_logging, supplemental_log_data_min FROM v$database;
FOR SUPPLEME
-----------
YES YES
SQL> create tablespace ggate
datafile '/u01/app/oracle/oradata/DB11G/ggate.dbf' size 5G;
Tablespace created.
SQL> create user ggate
identified by ****
default tablespace ggate;
User created.
SQL> GRANT dba TO ggate;
paert II) set up extract,datapump and replicate process on both source and targate side.
A) on source side node1:
[oracle@node1 ~]$ cd $GG_HOME
[oracle@node1 gg]$ ./ggsci
GGSCI (node1.example.com) > edit params manager
Saturday, September 30, 2017
7:12 PM
New Section 3 Page 1
----add below parameter
PORT 7809
DYNAMICPORTLIST 7810-7820
PURGEOLDEXTRACTS /u01/app/oracle/product/gg/dirdat/rx*, USECHECKPOINTS, MINKEEPHOURS 2
GGSCI (node1.example.com) > edit params CAPT01
EXTRACT CAPT01
setenv (ORACLE_SID="DB11G")
setenv (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID ggate, PASSWORD oracle
EXTTRAIL /u01/app/oracle/product/gg/dirdat/rx
TABLE scott.*;
GGSCI (node1.example.com) > edit params dp01
EXTRACT dp01
setenv (ORACLE_SID="DB11G")
setenv (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID ggate, PASSWORD ****
RMTHOST node2.example.com, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/rt
TABLE scott.*;
GGSCI (node1.example.com) > edit params frep01
REPLICAT frep01
setenv (ORACLE_SID="DB11G")
setenv (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID ggate, PASSWORD ****
ASSUMETARGETDEFS
MAP scott.*, TARGET scott.*;
GGSCI (node1.example.com) > EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE ggate.OGGCHKPT
GGSCI (node1.example.com) > start mgr
GGSCI (node1.example.com) > dblogin userid ggate, Password ***
GGSCI (node1.example.com) > add trandata scott.*
GGSCI (node1.example.com) > Add CheckpointTable
GGSCI (node1.example.com) > Add Extract capt01, TranLog, Begin Now
GGSCI (node1.example.com) > Add ExtTrail ./u01/app/oracle/product/gg/dirdat/rx, Extract capt01, Megabytes 5
GGSCI (node1.example.com) > Add Extract dp01, ExtTrailSource
/u01/app/oracle/product/gg/dirdat/rt
GGSCI (node1.example.com) > Add RmtTrail /u01/app/oracle/product/gg/dirdat/rt, Extract dp01, Megabytes 5
GGSCI (node1.example.com) > Add Replicat frep01, ExtTrail /u01/app/oracle/product/gg/dirdat/ft ----( this replicate process for fail back target to source)
GGSCI (node1.example.com) > info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED CAPT01 00:00:00 00:00:54
EXTRACT STOPPED DP01 00:00:00 00:00:46
REPLICAT STOPPED REP01 00:00:00 00:00:02
B) on target database.
[oracle@node2 ~]$ cd $GG_HOME
[oracle@node2 gg]$ ./ggsci
GGSCI (node2.example.com) > edit params mgr
----add below parameter
PORT 7809
DYNAMICPORTLIST 7810-7820
PURGEOLDEXTRACTS /u01/app/oracle/product/gg/dirdat/fx*, USECHECKPOINTS, MINKEEPHOURS 2
GGSCI (node2.example.com) > edit params FCAPT01
EXTRACT FCAPT01
setenv (ORACLE_SID="DB11G")
setenv (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID ggate, PASSWORD oracle
EXTTRAIL /u01/app/oracle/product/gg/dirdat/fx
TABLE scott.*;
GGSCI (node2.example.com) > edit params Fdp01
EXTRACT Fdp01
setenv (ORACLE_SID="DB11G")
setenv (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID ggate, PASSWORD ****
RMTHOST node2.example.com, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/gg/dirdat/ft
TABLE scott.*;
GGSCI (node2.example.com) > edit params rep01
REPLICAT rep01
setenv (ORACLE_SID="DB11G")
setenv (NLS_LANG="AMERICAN_AMERICA.WE8MSWIN1252")
USERID ggate, PASSWORD ****
ASSUMETARGETDEFS
MAP scott.*, TARGET scott.*;
GGSCI (node2.example.com) > EDIT PARAMS ./GLOBALS
CHECKPOINTTABLE ggate.OGGCHKPT
GGSCI (node2.example.com) > start mgr
GGSCI (node2.example.com) > dblogin userid ggate, Password ***
GGSCI (node2.example.com) > Add CheckpointTable
GGSCI (node2.example.com) > add trandata scott.*
GGSCI (node2.example.com) > Add Extract Fcapt01, TranLog, Begin Now
GGSCI (node2.example.com) > Add ExtTrail ./u01/app/oracle/product/gg/dirdat/fx, Extract capt01, Megabytes 5 ---(process for fail back replication)
GGSCI (node2.example.com) > Add Extract Fdp01, ExtTrailSource /u01/app/oracle/product/gg/dirdat/ft
GGSCI (node2.example.com) > Add RmtTrail /u01/app/oracle/product/gg/dirdat/ft, Extract dp01, Megabytes 5 ----(process for fail back replication)
GGSCI (node2.example.com) > Add Replicat Frep01, ExtTrail /u01/app/oracle/product/gg/dirdat/rt
GGSCI (node2.example.com) > info all
Program Status Group Lag at Chkpt Time Since Chkpt
MANAGER STOPPED
EXTRACT STOPPED FCAPT01 00:00:00 00:00:54
EXTRACT STOPPED FDP01 00:00:00 00:00:46
REPLICAT STOPPED FREP01 00:00:00 00:00:02
part III) start migration process
1)start capture process
GGSCI (node1.example.com) > start EXTRACT FCAPT01
GGSCI (node1.example.com) > start EXTRACT FDP01
2)check latest flshback_scn from source database
SQL> select dbms_flashback.get_system_change_number from dual;
GET_SYSTEM_CHANGE_NUMBER
------------------------
1189734
3)export application schema data using import and export utility .
[oracle@node1 gg]$ expdp ggate schemas=scott directory=DATA_PUMP_DIR dumpfile=SCHEMA_scott_dp.dmp logfile=SCHEMA_scott_dp.log flashback_scn=1189734
Export: Release 11.2.0.3.0 -Production on Sat Sep 30 10:07:05 2017
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
Password:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 -64bit Production
New Section 3 Page 4
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 -64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "GGATE"."SYS_EXPORT_SCHEMA_01": ggate/******** schemas=scott directory=DATA_PUMP_DIR dumpfile=SCHEMA_scott_dp.dmp logfile=SCHEMA_scott_dp.log
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 192 KB
Processing object type SCHEMA_EXPORT/USER
Processing object type SCHEMA_EXPORT/SYSTEM_GRANT
Processing object type SCHEMA_EXPORT/ROLE_GRANT
Processing object type SCHEMA_EXPORT/DEFAULT_ROLE
Processing object type SCHEMA_EXPORT/PRE_SCHEMA/PROCACT_SCHEMA
Processing object type SCHEMA_EXPORT/TABLE/TABLE
Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/REF_CONSTRAINT
Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS
. . exported "SCOTT"."DEPT" 5.984 KB 6 rows
. . exported "SCOTT"."EMP" 8.570 KB 14 rows
. . exported "SCOTT"."SALGRADE" 5.882 KB 6 rows
. . exported "SCOTT"."BONUS" 0 KB 0 rows
Master table "GGATE"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for GGATE.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/admin/DB11G/dpdump/SCHEMA_scott_dp.dmp
Job "GGATE"."SYS_EXPORT_SCHEMA_01" successfully completed at 10:07:36
4)import Application user data usig import export utility:
[oracle@node1 gg]$ impdp ggate schemas=scott directory=DATA_PUMP_DIR dumpfile=SCHEMA_scott_dp.dmp logfile=SCHEMA_scott_dp.log
5)start replication process on target database.
GGSCI (node2.example.com) > start rep01 aftercsn 1189734;
6)wait utile source and target database in sync.
check lag both capt01 and DP01 process mass will be as follow
as EOF no more record to process
7)stop application.
8)stop exrtact, datapump,replicat process
9)start the GG failback process.
on tagate database which now source.
GGSCI (node2.example.com) > alter FCAPT01 begin now
GGSCI (node2example.com) > info FDP01 detail
GGSCI (node2.example.com) > start extract FDP01
GGSCI (node1.example.com) > alter FREP01 extseqno 2 ------(on source database)
GGSCI (node2.example.com) > alter FREP01 extrba 0
GGSCI (node2.example.com) > start EXTRACT FCAPT01
GGSCI (node2.example.com) > start EXTRACT FCAPT01
GGSCI (node2.example.com) > start FREP01
step 10)switch application to new database server.
now all Applications running on new database server , old database server is your standby database server once you
confirm about transaction , performance etc about new database server you can stop gg process.
note: during import export operation you can use extra parameter for fast performance and also change some parameter in init.ora file.
