Tag Archives: Rajasekhar Tirupathi

Zero Downtime Migration

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 the environment
     b) Login to Database
     c) Configure Manager
     d) Create Extract Group
     e) Create Pump Group
     f) Add supplemental logging

6. OGG Configuration on Target Oracle 11gR2
     a) Set the environment
     b) Login to Database
     c) Configure Manager
     d) Set up the Checkpoint table
          i) Create a GLOBALS file
         ii) Activate the GLOBALS parameters
        iii) Add a Replicat checkpoint table

7) Create the Replicat Group on Target 11gR2
8. On Source Start Manager, Extract and Pump
9. On Target start the Manager only
10. Export data from source Oracle 10gR2
11. Transfer the dumpfile to 11g DATA_PUMP_DIR
12. Generate data on source Oracle 10gR2 (After Export)
13. Import Data and Start Replicat on Target 11gR2

14. Test and Verify the Results

INSERT OPERATION

       a1) Execute INSERT again on the Source Oracle Database 10gR2
       a2) Verify Insert Changes on Target Oracle Database 11gR2

UPDATE OPERATION

       b1) Execute update on the Source Oracle Database 10gR2
       b2) Verify Update Changes on Target Oracle Database 11gR2

DELETE OPERATION

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

15. Verify using GGSCI


1. Environment

Source Oracle 10gR2

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

Target Oracle 11gR2

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


2. GoldenGate Installation on Source Oracle 10gR2


3. GoldenGate Installation on Target Oracle 11gR2


4. Create CDS User (Application user) for testing

On Source (10gR2)

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>

On Target (11gR2)

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

User created.

SQL>


5. OGG Configuration on Source Oracle 10gR2


a) Set the environment

 . 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


b) Login to Database

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


c) Configure Manager

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>


d) Create Extract Group

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>


e) Create Pump Group

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>


f) Add supplemental logging

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>


6. OGG Configuration on Target Oracle 11gR2


a) Set the environment

[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


b) Login to Database

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


c) Configure Manager

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>


d) Set up the Checkpoint table


i) Create a GLOBALS file

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>


ii) Activate the GLOBALS parameters

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]$


iii) Add a Replicat checkpoint table

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


7) Create the Replicat Group

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.


8. On Source Start Manager, Extract and Pump

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>


9. On Target start the Manager ONLY

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>


10. Export data from source Oracle 10gR2

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 ~]$


11. Transfer the dumpfile to 11g DATA_PUMP_DIR

[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]$


12. Generate data on source Oracle 10gR2 (After Export)

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>


13. Import Data and Start Replicat on Target Database 11gR2

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


14. Test and Verify the Results

INSERT OPERATION

a1) Execute INSERT again on the Source Oracle Database 10gR2

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>


a2) Verify Insert Changes on Target Oracle Database 11gR2

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>


UPDATE OPERATION

b1) Execute update on the Source Oracle Database 10gR2

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>


b2) Verify Update Changes on Target Oracle Database 11gR2

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>


DELETE OPERATION

c1) Execute Delete on the Source Oracle Database 10gR2

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>


c2) Verify Delete Changes on Target Oracle Database 11gR2

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>


15. Verify using GGSCI

on Source Oracle 10gR2

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>

on Target Oracle 11gR2

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>

It’s working !!! 🙂

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.

Thank you,
Rajasekhar Amudala

RAJASEKHAR AMUDALA

Welcome to Bright DBA website.  

Please use the menu bar (or) Index below to navigate for Oracle/MS SQL Server/MySQL/MariaDB/DB2 LUW, PostgreSQL DBA articles !!!

Topics
DBA Monitor
OS
Database
Network
Storage
Install Oracle
Install Oracle 10gR2
Install Oracle Software in silent mode 11gR2
Install Oracle Software in silent mode 12cR2
Install Oracle 18c using OUI - GUI
Install Oracle 19c using OUI - GUI
Install Oracle Software in silent mode 19c
YouTube - Install Oracle Database 19c on Linux 7
Deinstall Oracle
Deinstall Oracle 11gR2
Deinstall Oracle 11gR2 Client on Windows
Deinstall Oracle 18c
Deinstall Oracle 19c
Create Database Oracle
Create Database in silent mode 11gR2
Create Non-CDB Oracle Database 12c on Linux using DBCA (GUI)
CREATE NON-CONTAINER DATABASE ON ORACLE 12C IN SILENT MODE USING DBCA
CREATE PLUGGABLE DATABASE MANUALLY USING SEED(PDB$SEED)
CREATE NEW PLUGGABLE DATABASE (PDB2) BY CLONING AN EXISTING PLUGGABLE DATABASE (PDB1)
CREATE PLUGGABLE DATABASE USING DBCA (from PDB$SEED)
Convert 12.2 Non-CDB (NONCDB12C) as PDB (NONCDB12C) into CDB (CDB1)
Create Non-CDB Oracle Database 19c on Linux using DBCA (GUI)
CREATE NON-CONTAINER DATABASE ON ORACLE 19C IN SILENT MODE USING DBCA
YouTube - How to create Oracle Database using DBCA?
Drop Database
Delete database using DBCA silent mode 11gR2
How to Drop the Database Manually
How to Drop Pluggable Database Manually 12cR2
YouTube - How to Drop Database
Database Startup/Shutdown
HOW TO STARTUP/SHUTDOWN CDB AND PDB 12cR2
YouTube - How to Start Database
Youtube - How to Shutdown Database
How to connect to Database
YouTube - How to connect to Database as SYSDBA
YouTube - How to connect to Database Remotely using SQL Developer?
Proxy User and Connect Through
Database Patching / Rollback
Patch Terminology
Apply Database Patch on 12.2 - Release Update
Oracle 18c Database Patching
Apply Database Patch on 19c - Release Update
How To Apply Database PSU Patch where Standby in Place 11gR2
ROLLBACK DATABASE RELEASE UPDATE from Oracle Database 12.2.0.1
Datapump
SCHEMA REFRESH FROM 11G TO 12C PDB
Backup and Restore - Database Refresh
How to Backup ORACLE_HOME binaries and Oracle INVENTORY
RMAN Catalog
RMAN Database Restore RAC – RAC
RMAN Database Restore from ASM (NON-RAC) TO ASM (NON-RAC)
RMAN Database Restore from ASM (NON-RAC) TO File System (NON-RAC)
RMAN ACTIVE DUPLICATION ASM TO ASM without downtime
Backup Based RMAN Duplicate Database
Database Upgrade
Upgrade Oracle Database Manually from 11.2.0.4 to 12.2.0.1
Upgrade Oracle Database from 11.2.0.4 to 12.2.0.1 using DBUA
Upgrade Oracle Database from 12.2.0.1 to 19c using DBUA
Upgrade Oracle Database Manually from 12.2.0.1 to 19c (NON-CDB)
Upgrading Oracle Database from 11.2.0.4 to 12.2.0.1 where Physical Standby in Place
Rolling Database Upgrade from 12.2.0.1 to 19c using Transient Logical standby
Dataguard - Configuration
Creating Active Data Guard Physical Standby Environment
Creating Active Data Guard Physical Standby Environment on ASM
Covert Physical Standby database to Snapshot Standby database R/W mode
Convert Snapshot database to Physical Standby database
Configure Cascading Standby Database on 11G
How to setup Data Guard Broker Configuration
DG Broker Error – ORA-16714
Disable Data Guard Broker Before Patch or Upgrade
Creating Physical Standby Active DataGuard Configuration in 12.2.0.1 Container Database
How to Create Physical Standby Database using RMAN Backup Without Using Duplicate Command
How to Create Physical Standby Database using RMAN Backup With Duplicate Command
12c DG Broker Configuration
Upgrading Oracle Database from 11.2.0.4 to 12.2.0.1 where Physical Standby in Place
Rolling Database Upgrade from 12.2.0.1 to 19c using Transient Logical standby
Create RAC Physical Standby Database using RMAN Active Duplicate Command
Dataguard - Switchover
Performing Switchover with No Broker (Manual)
Performing Switchover with DG Broker
Dataguard - Failover
Failover with DG Broker (No-Flashback)
Failover with No DG Broker (No Flashback) / Failover using Manual Method
Reinstate a failed over Data Guard using Flashback Database
Failover with No DG Broker using Flashback Database
Dataguard - Patching
How To Apply Database PSU Patch where Standby in Place
Dataguard - Other
How to Enable Real Time Apply
Refresh Standby Database using RMAN Incremental SCN Backup - Huge GAP
Resetlogs on Primary Database where Standby in place
How to drop the standby redo logs
ASM
How to move ASM SPFILE from +DATA to +VOTE diskgroup
ASM Commands - Non-RAC
Instantiating disk: failed while creating ASM disk
After server reboot, CSSD and ASM are not came up automatically
RAC
RAC Install 11.2.0.3
Upgrade RAC GI to 11.2.0.4 from 11.2.0.3
Downgrade RAC GI to 11.2.0.3 from 11.2.0.4
root.sh failed with ORA-29783 on RAC
Delete Node from Cluster in 11gR2 (11.2.0.3)
Add Node to 11gR2 Oracle RAC Cluster (11.2.0.3)
Delete Node without remove GI and RDBMS binaries
Add Node Back to Cluster which was deleted without removing GI and RDBMS binaries
How to take OCR backup on 11.2.0.4
How to display Oracle Cluster name
How to Move/Relocate OCR from +DATA to +VOTE diskgroup
Restore loss of all VOTE disks
Cluster Startup Issues
Cluster Verification Utility Command Reference
How to Configure OSWatcher
Create RAC Physical Standby Database using RMAN Active Duplicate Command
Create ACFS File System on RAC
Performance Tuning
About Oracle Statistics
Oracle Histograms
Export and Import schema statistics from one database to another
How to Disable CONTROL_MANAGEMENT_PACK_ACCESS
Generate/Customize Explain Plan
Monitoring Progress of a SQL Execution Plan / sql query is really hanged or not
STATSPACK Utility
ASH Report – ASH (Active Session History) Reports
AWR Report – AWR (Automatic Workload Repository) reports
How to mark SQL_ID as Colored
Troubleshooting Flow For Long Running Queries
How to run SQL Tuning Advisor Manually
How to Install SQLT
How to Run SQLT
How to Create Custom SQL Profile
How to Uninstall SQLT
How to Enable Trace
Trace Analyzer TRCANLZR (TRCA)
DB Generic
How to change SQL prompt to show connected user and database name
How to change Archive destination
Enable the database in Archivelog mode
How to Recover Loss of DATA (Without a Backup!) using Oracle Flash Back Query
Proxy User and Connect Through
ORA-00054 resource busy and acquire with NOWAIT specified
How to find Blocking Sessions
Create Service using DBMS_SERVICE
Error TNS-12543 while connect to a remote database on a Linux
How to Change MAX_STRING_SIZE on Physical Standby Environment
OEM
Steps to configure Oracle 11g OEM DBConsole manually for database
Enable Oracle Enterprise Manager Express 12c
Oracle Enterprise Manager Cloud Control 13c Release 2 (13.2.0.0) Installation on Oracle Linux 64 bit
Oracle Enterprise Manager Cloud Control 13c Release 3 (13.3.0.0) Installation on Oracle Linux 64 bit
Add Database Target on EM Cloud Control 13c
How to upgrade the Enterprise Manager 13.1 Cloud Control OMSPatcher to the Latest Version
Apply Oracle Enterprise Manager 13c Release 1 Patch Set Update 13.1.0.0.170718 for Oracle Management Service
Apply Patch on Oracle Management Agent 13c Release 1 (13.1.0.0.0)
Database Link
Database link
DBMS SCHEDULER
How to Create and Monitor DBMS_SCHEDULER Jobs
Enable/Disable JOBS during Maintenance
Microsoft SQL Server DBA
Steps to Install Microsoft SQL Server Express 2014
How to Install Only SQL Server Management Studio
Create Database using the SQL Server Management Studio
Enable TCP/IP Connection
Allow Remote Connection
Authentication Method
How to Create SQL Server Authenticated Login in SQL Server
How to create Windows Authentication Login in SQL Server
How to Create Windows Group Authentication Login in SQL Server
Create a new domain user account in Windows Server 2016
Microsoft SQL Server – Backup and Recovery
Recovery Models
Database Backup: FULL,DIFFERENTIAL and T-LOG
Restore & Recover Database
MySQL DBA
Install MySQL 8 on Linux 7 Using RPM Packages
Create MySQL Database
Create New User Account in MySQL
Grant/Revoke Privileges in MySQL
Create Roles in MySQL
MySQL – Backup/Restore
How To Backup MySQL Database Using mysqldump?
How to Restore Database from Single MySQL Database Backup?
How to Restore A Single Database From A Backup File Which Contains Multiple Databases?
How To Restore Single Table From Full Database Backup in MySQL?
MySQL – Replication
How To Configure MySQL Master-Slave Replication? (One-Way Replication)
MariaDB DBA
Install MariaDB with yum on Linux7 -- Coming soon !!!
Create MariaDB Database --- Coming soon !!!
Create New User Account in MairaDB --- Coming soon !!!
Grant/Revoke Privileges in MariaDB --- coming soon !!!!
Create Roles in MariaDB -- Coming soon !!!
MariaDB – Backup/Restore
Coming Soon !!!
Coming Soon !!!
MariaDB – Replication
How To Configure Maria Master-Slave Replication? (One-Way Replication) -- Coming soon !!!
MariaDB MaxScale Set up
Setup MaxScale -- Coming soon !!!
Load Balancing -- Coming soon !!!
Switchover -- Coming soon !!!
Failover -- Coming soon !!!
PostgreSQL DBA
How to Install PostgreSQL 13 ON RHEL 7?
How to START/STOP PostgreSQL 13 ON RHEL 7?
How to Create Database in PostgreSQL?
Linux
Download Oracle files on Linux via wget
How to Install SQL Developer on Linux
How to Disable the Firewall on Linux 6
DBA Scripts
Oracle DBA Sheet v5.8
Oracle DBA Sheet v6.1
DBA Daily Report
GoldenGate
Installing Oracle GoldenGate V11.2.x for Oracle 10gR2 on Linux x86-64
Unidirectional DML Replication
Zero Downtime Migration
DDL Replication
GoldenGate 12C Installation
Integrated Capture
Oracle GoldenGate Replication between MySQL 5.5 to Oracle 12c
Oracle GoldenGate Security – Credential Store for Database Logins
ADD NEW TABLE TO AN EXISTING GOLDENGATE CONFIGURATION
Installing Oracle GoldenGate 19.1.0.0 for Oracle DB 12c on Linux 7
Install Oracle GoldenGate 19.1.0.0.4 for Oracle Database 19c on Linux 7