DBA Hub

📋Steps in this guide1/23

Zero downtime migration using goldengate

AIM: Zero Downtime Migration with one-way replication between an Oracle 10gR2 database and an Oracle 11gR2 database on same server. 1. Environment 2. GoldenGate Installation on Source Oracle 10gR2 3. GoldenGate Installation on Target Oracle 11gR2 4. Create CDS User (Application user) for testing 5. OGG Configuration on Source Oracle 10gR2      a) Set … Continue reading Zero Downtime Migration →

oracle configurationintermediate
by OracleDba
26 views
1

Overview

AIM: Zero Downtime Migration with one-way replication between an Oracle 10gR2 database and an Oracle 11gR2 database on same server. 1. Environment Source Oracle 10gR2

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
Server Name			:
RAC1.RAJASEKHAR.COM
OS Version			: Red Hat Enterprise Linux 5.7
Database Version		:
10.2.0.5
GoldenGate Version		: Version 11.2.1.0.1 for 10g database
Database Name			: DB10
File System			:
ASM
Golden Gate User		: GATE
Golden Gate Extract		: E10G
Golden Gate Pump		: P10G
Application User		:
CDS (Migrating this user to target)
Oracle Home			: /u01/app/oracle/product/10g
GoldenGate Home			: /u01/app/oracle/product/10gogg
2

Section 2

CDS (Migrating this user to target) Target Oracle 11gR2 2. GoldenGate Installation on Source Oracle 10gR2

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
Server Name			:
RAC1.RAJASEKHAR.COM
OS Version			: Red Hat Enterprise Linux 5.7
Database Version		:
11.2.0.4
GoldenGate Version		: Version 11.2.1.0.5 for 11g database
Database Name			: DB11
File System			:
Non-ASM
Golden Gate User		: GATE
Golden Gate Replicate	        : R11G
Application User		: CDS
Oracle Home			: /u01/app/oracle/product/11.2.0/dbhome_1
GoldenGate Home			: /u01/app/oracle/product/11gogg
3

Section 3

3. GoldenGate Installation on Target Oracle 11gR2 4. Create CDS User (Application user) for testing

Code/Command (click line numbers to comment):

1
2
3
https://www.br8dba.com/installation/

https://www.br8dba.com/installation/
4

Section 4

On Source (10gR2) On Target (11gR2)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL>
create user cds identified by cds default tablespace users temporary tablespace temp;
User created.

SQL> grant connect,resource to cds;

Grant succeeded.

SQL>
create table CDS.A as select * from dba_objects;
Table created.

SQL>
create table CDS.TEST(Name varchar2(10) PRIMARY KEY, Role varchar2(10));
Table created.

SQL>

SQL>
create user cds identified by cds default tablespace users temporary tablespace temp;
User created.

SQL>
5

Section 5

5. OGG Configuration on Source Oracle 10gR2 a) Set the environment b) Login to Database

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
. oraenv
ORACLE_SID = [DB10] ?

[oracle@rac1 ~]$ cat 10g.env
GG10=/u01/app/oracle/product/10gogg; export GG10
ORACLE_HOME=/u01/app/oracle/product/10g;  export ORACLE_HOME
ORACLE_SID=DB10 ; export ORACLE_SID
PATH=$PATH:/u01/app/oracle/product/10gogg; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/product/10gogg; export LD_LIBRARY_PATH
alias ggsci='cd $GG10; ./ggsci'
[oracle@rac1 ~]$

[oracle@rac1 ~]$ . 10g.env
6

Section 6

b) Login to Database dblogin userid gate, password gate c) Configure Manager

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
[oracle@rac1 ~]$
ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.1 OGGCORE_11.2.1.0.1_PLATFORMS_120423.0230_FBO
Linux, x64, 64bit (optimized), Oracle 10g on Apr 23 2012 07:30:46

Copyright (C) 1995, 2012, Oracle and/or its affiliates. All rights reserved.

GGSCI (rac1.rajasekhar.com) 1>
dblogin userid gate, password gate
Successfully logged into database.

GGSCI (rac1.rajasekhar.com) 2>

GGSCI (rac1.rajasekhar.com) 3>
edit param mgr
-- Add below entries
PORT 15500
DYNAMICPORTLIST 15510-15520
PURGEOLDEXTRACTS /u01/app/oracle/product/10gogg/dirdat/*, USECHECKPOINTS
GGSCI (rac1.rajasekhar.com) 4>
view param mgr
PORT 15500
DYNAMICPORTLIST 15510-15520
PURGEOLDEXTRACTS /u01/app/oracle/product/10gogg/dirdat/*, USECHECKPOINTS

GGSCI (rac1.rajasekhar.com) 5>
7

Section 7

edit param mgr d) Create Extract Group e) Create Pump Group

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
GGSCI (rac1.rajasekhar.com) 5>
ADD EXTRACT E10G, TRANLOG, BEGIN NOW
EXTRACT added.


GGSCI (rac1.rajasekhar.com) 6>
ADD EXTTRAIL ./dirdat/ea, EXTRACT E10G, MEGABYTES 50
EXTTRAIL added.


GGSCI (rac1.rajasekhar.com) 7>
edit param E10G
-- Add below entries
EXTRACT E10G
EXTTRAIL ./dirdat/ea
USERID gate, PASSWORD gate
TABLE CDS.*;
GGSCI (rac1.rajasekhar.com) 8>
view param e10g
EXTRACT E10G
EXTTRAIL ./dirdat/ea
USERID gate, PASSWORD gate
TABLE CDS.*;

GGSCI (rac1.rajasekhar.com) 9>
8

Section 8

f) Add supplemental logging ADD TRANDATA CDS.* Logging of supplemental redo data enabled for table CDS.A. Logging of supplemental redo data enabled for table CDS.TEST.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
GGSCI (rac1.rajasekhar.com) 9>
ADD EXTRACT P10G, EXTTRAILSOURCE
./dirdat/ea
EXTRACT added.

GGSCI (rac1.rajasekhar.com) 10>
ADD RMTTRAIL ./dirdat/pa, EXTRACT P10G, MEGABYTES 50
RMTTRAIL added.

GGSCI (rac1.rajasekhar.com) 11>
edit param p10g
-- Add below entries
EXTRACT P10G
RMTHOST 192.168.2.101, MGRPORT 15000
USERID gate, password gate
RMTTRAIL ./dirdat/pa
PASSTHRU
TABLE CDS.*;
GGSCI (rac1.rajasekhar.com) 12>
view param p10g
EXTRACT P10G
RMTHOST 192.168.2.101, MGRPORT 15000
USERID gate, password gate
RMTTRAIL ./dirdat/pa
PASSTHRU
TABLE CDS.*;

GGSCI (rac1.rajasekhar.com) 13>

GGSCI (rac1.rajasekhar.com) 13>
ADD TRANDATA CDS.*
2016-11-26 21:32:37  WARNING OGG-00869  No unique key is defined for table 'A'. All viable columns will be used to represent the key, but may not guarantee uniqueness.  KEYCOLS may be used to define the key.
Logging of supplemental redo data enabled for table CDS.A.
Logging of supplemental redo data enabled for table CDS.TEST.
GGSCI (rac1.rajasekhar.com) 14>
9

Section 9

6. OGG Configuration on Target Oracle 11gR2 a) Set the environment b) Login to Database

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [oracle] ? DB11
[oracle@rac1 ~]$ 

[oracle@rac1 ~]$
cat
11g.env
GG11=/u01/app/oracle/product/11gogg; export GG11
ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1;  export ORACLE_HOME
ORACLE_SID=DB11 ; export ORACLE_SID
PATH=$PATH:/u01/app/oracle/product/11gogg; export PATH
LD_LIBRARY_PATH=$ORACLE_HOME/lib:/u01/app/oracle/product/11gogg; export LD_LIBRARY_PATH
alias ggsci='cd $GG11; ./ggsci'
[oracle@rac1 ~]$

[oracle@rac1 ~]$
. 11g.env
10

Section 10

b) Login to Database c) Configure Manager d) Set up the Checkpoint table

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
[oracle@rac1 ~]$
ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_01 16224002 OGGCORE_11.2.1.0.6_PLATFORMS_130205.0600
Linux, x64, 64bit (optimized), Oracle 11g on Feb  5 2013 08:11:55

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.

GGSCI (rac1.rajasekhar.com) 1> dblogin userid gate, password gate
Successfully logged into database.

GGSCI (rac1.rajasekhar.com) 2>

GGSCI (rac1.rajasekhar.com) 2>
info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED


GGSCI (rac1.rajasekhar.com) 3>
edit param mgr
-- Add below entry
PORT 15000
DYNAMICPORTLIST 15010-15020
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS
GGSCI (rac1.rajasekhar.com) 4>
view param mgr
PORT 15000
DYNAMICPORTLIST 15010-15020
PURGEOLDEXTRACTS ./dirdat/*, USECHECKPOINTS


GGSCI (rac1.rajasekhar.com) 5>
11

Section 11

d) Set up the Checkpoint table i) Create a GLOBALS file ii) Activate the GLOBALS parameters

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
GGSCI (rac1.rajasekhar.com) 6>
EDIT PARAMS ./GLOBALS
-- Add below entries
GGSCHEMA GATE
ENABLEMONITORING
CHECKPOINTTABLE GATE.CHECKPOINT
GGSCI (rac1.rajasekhar.com) 7>
sh cat ./GLOBALS
GGSCHEMA GATE
ENABLEMONITORING
CHECKPOINTTABLE GATE.CHECKPOINT

GGSCI (rac1.rajasekhar.com) 8>
12

Section 12

For the GLOBALS configuration to take effect, you must exit the session in which the changes were made. Execute the following command to exit GGSCI. iii) Add a Replicat checkpoint table 7) Create the Replicat Group

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
For the GLOBALS configuration to take effect, you must exit the session in which the changes were made. Execute the following command to exit GGSCI.
GGSCI (rac1.rajasekhar.com) 8>
exit
[oracle@rac1 11gogg]$

[oracle@rac1 11gogg]$
ggsci
Oracle GoldenGate Command Interpreter for Oracle
Version 11.2.1.0.5_01 16224002 OGGCORE_11.2.1.0.6_PLATFORMS_130205.0600
Linux, x64, 64bit (optimized), Oracle 11g on Feb  5 2013 08:11:55

Copyright (C) 1995, 2013, Oracle and/or its affiliates. All rights reserved.


GGSCI (rac1.rajasekhar.com) 1>
dblogin userid gate, password gate
Successfully logged into database.

GGSCI (rac1.rajasekhar.com) 2>
ADD CHECKPOINTTABLE GATE.CHECKPOINT
Successfully created checkpoint table GATE.CHECKPOINT.

GGSCI (rac1.rajasekhar.com) 3>
13

Section 13

7) Create the Replicat Group -- The ASSUMETARGETDEFS statement means that we are assuming both source and target table structure is same. 8. On Source Start Manager, Extract and Pump

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
GGSCI (rac1.rajasekhar.com) 4>
ADD REPLICAT R11G, EXTTRAIL ./dirdat/pa,checkpointtable gate.checkpoint
REPLICAT added.

GGSCI (rac1.rajasekhar.com) 5>
edit param r11g
-- Add below
REPLICAT R11G
ASSUMETARGETDEFS
userid gate, password gate
DISCARDFILE ./dirout/receiver.dsc, purge
MAP CDS.*, TARGET CDS.*;
GGSCI (rac1.rajasekhar.com) 6>
view param r11g
REPLICAT R11G
ASSUMETARGETDEFS
userid gate, password gate
DISCARDFILE ./dirout/receiver.dsc, purge
MAP CDS.*, TARGET CDS.*;
-- The ASSUMETARGETDEFS statement means that we are assuming both source and target table structure is same.

GGSCI (rac1.rajasekhar.com) 14>
info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER
STOPPED
EXTRACT
STOPPED
E10G        00:00:00      00:38:06
EXTRACT
STOPPED
P10G        00:00:00      00:32:34


GGSCI (rac1.rajasekhar.com) 15>
start mgr
Manager started.


GGSCI (rac1.rajasekhar.com) 16>
start extract e10g
Sending START request to MANAGER ...
EXTRACT E10G starting


GGSCI (rac1.rajasekhar.com) 17>
start extract p10g
Sending START request to MANAGER ...
EXTRACT P10G starting


GGSCI (rac1.rajasekhar.com) 18>
info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
EXTRACT     RUNNING
E10G
00:00:00      00:00:03
EXTRACT     RUNNING
P10G
00:00:00      00:38:49


GGSCI (rac1.rajasekhar.com) 19>
14

Section 14

9. On Target start the Manager ONLY 10. Export data from source Oracle 10gR2

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
GGSCI (rac1.rajasekhar.com) 8>
info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     STOPPED
JAGENT      STOPPED
REPLICAT    STOPPED     R11G        00:00:00      00:14:36


GGSCI (rac1.rajasekhar.com) 9>
start mgr
Manager started.


GGSCI (rac1.rajasekhar.com) 10>
info mgr
Manager is running
(IP port rac1.rajasekhar.com.15000).


GGSCI (rac1.rajasekhar.com) 11> info all

Program     Status      Group       Lag at Chkpt  Time Since Chkpt
MANAGER     RUNNING
JAGENT      STOPPED
REPLICAT    STOPPED     R11G        00:00:00      00:15:11


GGSCI (rac1.rajasekhar.com) 12>

Connected to:
Oracle Database 10g Enterprise Edition Release
10.2.0.5.0
- 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>
SELECT DBMS_FLASHBACK.GET_SYSTEM_CHANGE_NUMBER FROM DUAL;
GET_SYSTEM_CHANGE_NUMBER
------------------------
468487  <--- Please note this
SQL>

SQL>
grant execute on DBMS_FLASHBACK to CDS;
Grant succeeded.

SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@rac1 ~]$

[oracle@rac1 ~]$
expdp  \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR SCHEMAS=CDS dumpfile=CDS.dmp logfile=cds.log FLASHBACK_SCN=
468487
Export: Release 10.2.0.5.0 - 64bit Production on Saturday, 26 November, 2016 22:28:56

Copyright (c) 2003, 2007, Oracle.  All rights reserved.

Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
FLASHBACK automatically enabled to preserve database integrity.
Starting "SYS"."SYS_EXPORT_SCHEMA_01":  "/******** AS SYSDBA" DIRECTORY=DATA_PUMP_DIR SCHEMAS=CDS dumpfile=CDS.dmp logfile=cds.log FLASHBACK_SCN=468487
Estimate in progress using BLOCKS method...
Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA
Total estimation using BLOCKS method: 6 MB
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/CONSTRAINT/CONSTRAINT
. . exported "CDS"."A"
4.720 MB   50605 rows <---
. . exported "CDS"."TEST"                                    0 KB       0 rows <---
Master table "SYS"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYS.SYS_EXPORT_SCHEMA_01 is:
/u01/app/oracle/product/10g/admin/DB10/dpdump/CDS.dmp
Job "SYS"."SYS_EXPORT_SCHEMA_01"
successfully completed
at 22:29:10

[oracle@rac1 ~]$
15

Section 15

11. Transfer the dumpfile to 11g DATA_PUMP_DIR 12. Generate data on source Oracle 10gR2 (After Export)

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
[oracle@rac1 ~]$
cd /u01/app/oracle/product/10g/admin/DB10/dpdump/
[oracle@rac1 dpdump]$
cp CDS.dmp /u01/app/oracle/admin/DB11/dpdump/
[oracle@rac1 dpdump]$

SQL>
conn cds/cds;
Connected.
SQL>
insert into test values ('&a','&b');
Enter value for a: RAJ
Enter value for b: DBA
old   1: insert into test values ('&a','&b')
new   1: insert into test values ('RAJ','DBA')

1 row created.

SQL> /
Enter value for a: SUGI
Enter value for b: DBA
old   1: insert into test values ('&a','&b')
new   1: insert into test values ('SUGI','DBA')

1 row created.

SQL> /
Enter value for a: TEJU
Enter value for b: DBA
old   1: insert into test values ('&a','&b')
new   1: insert into test values ('TEJU','DBA')

1 row created.

SQL> /
Enter value for a: VIJAY
Enter value for b: DBA
old   1: insert into test values ('&a','&b')
new   1: insert into test values ('VIJAY','DBA')

1 row created.

SQL>
SQL> insert into test values ('&a','&b');
Enter value for a: SATYA
Enter value for b: DBA
old   1: insert into test values ('&a','&b')
new   1: insert into test values ('SATYA','DBA')

1 row created.

SQL> /
Enter value for a: MOHAN
Enter value for b: DBA
old   1: insert into test values ('&a','&b')
new   1: insert into test values ('MOHAN','DBA')

1 row created.

SQL> /
Enter value for a: AMITH
Enter value for b: DBA
old   1: insert into test values ('&a','&b')
new   1: insert into test values ('AMITH','DBA')

1 row created.

SQL> /
Enter value for a: PAVAN
Enter value for b: DBA
old   1: insert into test values ('&a','&b')
new   1: insert into test values ('PAVAN','DBA')

1 row created.

SQL> /
Enter value for a: SARAVANA
Enter value for b: DBA
old   1: insert into test values ('&a','&b')
new   1: insert into test values ('SARAVANA','DBA')

1 row created.

SQL> /
Enter value for a: PREM
Enter value for b: DBA
old   1: insert into test values ('&a','&b')
new   1: insert into test values ('PREM','DBA')

1 row created.

SQL> COMMIT;

Commit complete.

SQL>
select count(*) from test;
COUNT(*)
----------
        10

SQL>
select * from test;
NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA
VIJAY      DBA
SATYA      DBA
MOHAN      DBA
AMITH      DBA
PAVAN      DBA
SARAVANA   DBA
PREM       DBA

10 rows selected.

SQL>


GGSCI (rac1.rajasekhar.com) 19>
STATS E10G, TOTAL
Sending STATS request to EXTRACT E10G ...

Start of Statistics at 2016-11-26 22:45:02.

Output to ./dirdat/ea:

Extracting from CDS.TEST to CDS.TEST:

*** Total statistics since 2016-11-26 22:38:48 ***
Total inserts                                     10.00
Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                  10.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 20>
16

Section 16

13. Import Data and Start Replicat on Target Database 11gR2 *** Note in GGSCI, the Oracle SCN number is AFTERCSN, not AFTERSCN. *** Start the REPLICAT only after successful of import. start r11g, aftercsn 468487

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [DB11] ?
[oracle@rac1 ~]$

[oracle@rac1 ~]$
impdp  \'/ as sysdba\' DIRECTORY=DATA_PUMP_DIR SCHEMAS=CDS dumpfile=CDS.dmp logfile=imp_cds.log
Import: Release 11.2.0.4.0 - Production on Sat Nov 26 22:43: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, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options
Master table "SYS"."SYS_IMPORT_SCHEMA_01" successfully loaded/unloaded
Starting "SYS"."SYS_IMPORT_SCHEMA_01":  "/******** AS SYSDBA" DIRECTORY=DATA_PUMP_DIR SCHEMAS=CDS dumpfile=CDS.dmp logfile=imp_cds.log
Processing object type SCHEMA_EXPORT/USER
ORA-31684: Object type USER:"CDS" already exists
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/TABLE_DATA
. . imported "CDS"."A"
4.720 MB   50605 rows <-----
. . imported "CDS"."TEST"                                    0 KB       0 rows <----
Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT
Job "SYS"."SYS_IMPORT_SCHEMA_01" completed with 1 error(s) at Sat Nov 26 22:43:36 2016 elapsed 0 00:00:07

[oracle@rac1 ~]$

SQL> conn cds/cds;
Connected.
SQL>
select count(*) from test;
COUNT(*)
----------
0
<-----
SQL>
*** Note in GGSCI, the Oracle SCN number is AFTERCSN, not AFTERSCN.
*** Start the REPLICAT only after successful of import.
GGSCI (rac1.rajasekhar.com) 11>
info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER     RUNNING
JAGENT      STOPPED
REPLICAT    STOPPED     R11G        00:00:00      00:15:11


GGSCI (rac1.rajasekhar.com) 12>
start r11g, aftercsn
468487
Sending START request to MANAGER ...
REPLICAT R11G starting


GGSCI (rac1.rajasekhar.com) 13>
info all
Program     Status      Group       Lag at Chkpt  Time Since Chkpt

MANAGER
RUNNING
JAGENT      STOPPED
REPLICAT
RUNNING
R11G        00:00:00      00:00:05


GGSCI (rac1.rajasekhar.com) 14>

GGSCI (rac1.rajasekhar.com) 14>
STATS R11G, TOTAL
Sending STATS request to REPLICAT R11G ...

No active replication maps.


GGSCI (rac1.rajasekhar.com) 15> STATS R11G, TOTAL

Sending STATS request to REPLICAT R11G ...

Start of Statistics at 2016-11-26 22:52:00.

Replicating from CDS.TEST to CDS.TEST:

*** Total statistics since 2016-11-26 22:51:49 ***
Total inserts                                     10.00
Total updates                                      0.00
        Total deletes                                      0.00
        Total discards                                     0.00
        Total operations                                  10.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 16>


SQL> conn cds/cds;
Connected.
SQL>
select count(*) from test;
COUNT(*)
----------
10 <----
SQL>
select * from test;
NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA
VIJAY      DBA
SATYA      DBA
MOHAN      DBA
AMITH      DBA
PAVAN      DBA
SARAVANA   DBA
PREM       DBA

10 rows selected.

SQL>
17

Section 17

14. Test and Verify the Results INSERT OPERATION a1) Execute INSERT again on the Source Oracle Database 10gR2 14. Test and Verify the Results INSERT OPERATION a1) Execute INSERT again on the Source Oracle Database 10gR2 DB10

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- ----------
DB10
READ WRITE
SQL>
conn cds/cds;
Connected.
SQL>
insert into TEST values ('&a','&b');
Enter value for a:
RAJKUMAR
Enter value for b: MANAGER
old   1: insert into TEST values ('&a','&b')
new   1: insert into TEST values ('RAJKUMAR','MANAGER')

1 row created.

SQL> /
Enter value for a:
SATISH
Enter value for b: MANAGER
old   1: insert into TEST values ('&a','&b')
new   1: insert into TEST values ('SATISH','MANAGER')

1 row created.

SQL> insert into TEST values ('&a','&b');
Enter value for a:
LOKESH
Enter value for b: DBA
old   1: insert into TEST values ('&a','&b')
new   1: insert into TEST values ('LOKESH','DBA')

1 row created.

SQL> COMMIT;

Commit complete.

SQL> select * from test;

NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA
VIJAY      DBA
SATYA      DBA
MOHAN      DBA
AMITH      DBA
PAVAN      DBA
SARAVANA   DBA
PREM       DBA

NAME       ROLE
---------- ----------
RAJKUMAR   MANAGER <---
SATISH     MANAGER <---
LOKESH     DBA     <---
13 rows selected.

SQL>
18

Section 18

a2) Verify Insert Changes on Target Oracle Database 11gR2 UPDATE OPERATION b1) Execute update on the Source Oracle Database 10gR2 UPDATE OPERATION b1) Execute update on the Source Oracle Database 10gR2

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
SQL> select name, open_mode from v$database;

NAME      OPEN_MODE
--------- --------------------
DB11
READ WRITE
SQL>
conn cds/cds;
Connected.
SQL> set pages 13
SQL> select * from test;

NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA
VIJAY      DBA
SATYA      DBA
MOHAN      DBA
AMITH      DBA
PAVAN      DBA
SARAVANA   DBA
PREM       DBA

NAME       ROLE
---------- ----------
RAJKUMAR   MANAGER
SATISH     MANAGER
LOKESH     DBA
13 rows selected.

SQL>
19

Section 19

b2) Verify Update Changes on Target Oracle Database 11gR2 DELETE OPERATION c1) Execute Delete on the Source Oracle Database 10gR2 DELETE OPERATION

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
SQL>
update test set role='
PSE
' where name='VIJAY';
1 row updated.

SQL>
update test set role='
PSE
' where name='SATYA';
1 row updated.

SQL>
update test set role='
PSE
' where name='PAVAN';
1 row updated.

SQL> commit;

Commit complete.

SQL> set pages 100
SQL> select * from test;

NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA
VIJAY      PSE
SATYA      PSE
MOHAN      DBA
AMITH      DBA
PAVAN      PSE
SARAVANA   DBA
PREM       DBA
RAJKUMAR   MANAGER
SATISH     MANAGER
LOKESH     DBA

13 rows selected.

SQL>

SQL> select * from test;

NAME       ROLE
---------- ----------
RAJ        DBA
SUGI       DBA
TEJU       DBA
VIJAY      PSE
SATYA      PSE
MOHAN      DBA
AMITH      DBA
PAVAN      PSE
SARAVANA   DBA
PREM       DBA
RAJKUMAR   MANAGER
SATISH     MANAGER
LOKESH     DBA
13 rows selected. <------
SQL>
20

Section 20

c1) Execute Delete on the Source Oracle Database 10gR2 c2) Verify Delete Changes on Target Oracle Database 11gR2 15. Verify using GGSCI

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
SQL>
delete from test where name='
SUGI
';
1 row deleted.

SQL>
delete from test where name='
TEJU
';
1 row deleted.

SQL>
delete from test where name='
RAJ
';
1 row deleted.

SQL> commit;

Commit complete.

SQL> select * from test;

NAME       ROLE
---------- ----------
VIJAY      PSE
SATYA      PSE
MOHAN      DBA
AMITH      DBA
PAVAN      PSE
SARAVANA   DBA
PREM       DBA
RAJKUMAR   MANAGER
SATISH     MANAGER
LOKESH     DBA
10 rows selected. <--- 3 rows deleted
SQL>

SQL> select * from test;

NAME       ROLE
---------- ----------
VIJAY      PSE
SATYA      PSE
MOHAN      DBA
AMITH      DBA
PAVAN      PSE
SARAVANA   DBA
PREM       DBA
RAJKUMAR   MANAGER
SATISH     MANAGER
LOKESH     DBA

10 rows selected.

SQL>
21

Section 21

15. Verify using GGSCI on Source Oracle 10gR2 STATS E10G, TOTAL on Target Oracle 11gR2

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
GGSCI (rac1.rajasekhar.com) 26>
STATS E10G, TOTAL
Sending STATS request to EXTRACT E10G ...

Start of Statistics at 2016-11-26 23:21:30.

Output to ./dirdat/ea:

Extracting from CDS.TEST to CDS.TEST:

*** Total statistics since 2016-11-26 22:38:48 ***
Total inserts                                     13.00 <---
Total updates                                      3.00 <---
Total deletes                                      3.00 <---
Total discards                                     0.00
        Total operations                                  19.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 27>
22

Section 22

on Target Oracle 11gR2 STATS R11G, TOTAL It’s working !!! 🙂 It’s working !!! 🙂

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
GGSCI (rac1.rajasekhar.com) 16>
STATS R11G, TOTAL
Sending STATS request to REPLICAT R11G ...

Start of Statistics at 2016-11-26 23:21:14.

Replicating from CDS.TEST to CDS.TEST:

*** Total statistics since 2016-11-26 22:51:49 ***
Total inserts                                     13.00 <---
Total updates                                      3.00 <---
Total deletes                                      3.00 <---
Total discards                                     0.00
        Total operations                                  19.00

End of Statistics.


GGSCI (rac1.rajasekhar.com) 17>
23

Section 23

Caution : Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using. Caution : Your use of any information or materials on this website is entirely at your own risk. It is provided for educational purposes only. It has been tested internally, however, we do not guarantee that it will work for you. Ensure that you run it in your test environment before using.

Comments (0)

Please to add comments

No comments yet. Be the first to comment!