Some remain constantly imbued with the Love of the One Lord; I am forever a sacrifice to them. I dedicate my body, mind and wealth to them; bowing low, I fall at their feet. Meeting them, the mind is satisfied, and one.s hunger and thirst all depart. O Nanak, those who are attuned to the Naam are happy forever; they lovingly focus their awareness on the True Lord.
Index to all the posts of Gurpartap Singh's Blog
References:
http://www.oracle.com/technetwork/middleware/goldengate/overview/ggzerodowntimedatabaseupgrades-174928.pdf
Prerequisites:
You need to have the following prerequisites in place before you can start setting up replication for zero-downtime
migration:
1. The Oracle GoldenGate software installed on the source and target servers as described in post 16.
i.e.
http://gurpartapblog.blogspot.com/2010/11/installing-oracle-goldengate-part-16-of.html
2. The Oracle GoldenGate database User ID created on the source and target databases as in post 16.
3. The server name or IP address of the target database server
Source:
10gR1 database on Linux 32 bit running as a single instance on top of ASM.
ASM connects to the luns on SAN running openfiler.
the name of the SAN server is "filer01" and the ip is 192.168.1.116
The name of the database server is dg01 with ip 192.168.1.117
The name of the source database is "test"
Target:
11gR2 database on Red Hat Linux 64 bit running as a single instance RAC on top of Grid Infrastructure under Oracle
Restart Configuration.
ASM connects to the luns on SAN running Red Hat Linux 64 bit.
The name of the SAN server is "san2" and the ip is 192.168.1.195
The name of the database server is restart.rac.meditate.com with ip 192.168.1.196
The name of the target database is "KHEMKARAN"
You can use first 4 posts of my blog to create this machine
4. The Oracle GoldenGate Manager process up and running on the source and the target
5. TCP/IP network connectivity open from the source server to the target server's Oracle GoldenGate manager port
and visa versa
6. An understanding of the business and technical replication requirements of the planned migration
7. A few additional prerequisites are important for zero-downtime migration replication:
Backups of your Oracle GoldenGate software and working directories on the source and target servers
SQL scripts to grant any necessary object privileges required by your application during the cutover or fallback
procedure
8. Processes and scripts to move your application and users from the old to the new database and back again
Here I am creating 2 users on the source database that we will like to migrate to the new database
The name of the users are:
migrate and
source
SQL> conn / as sysdba
connected
SQL> create user migrate identified by migrate default tablespace users;
User created.
SQL> grant dba to migrate;
Grant succeeded.
SQL> conn migrate/migrate
Connected.
SQL> create table objects as select * from dba_objects;
Table created.
SQL> create table tables as select * from dba_tables;
Table created.
SQL> create table indexes as select * from dba_indexes;
Table created.
SQL>
create user source identified by source default tablespace users;
grant dba to source;
conn source/source
create table users as select * from dba_users;
create table sessions as select * from v$session;
create table sqls as select * from v$sql;
Output is as:
SQL> create user source identified by source default tablespace users;
User created.
SQL> grant dba to source;
Grant succeeded.
SQL> conn source/source
Connected.
SQL> create table users as select * from dba_users;
Table created.
SQL> create table sessions as select * from v$session;
Table created.
SQL> create table sqls as select * from v$sql;
Table created.
SQL>
-----------------------------------------------------------------------------------------
Now, I will create the same user on the target side as a part of initial load and will migrate the data on the target side.
There are different methods of doing initial load like :
You can use goldengate (Post 18),
Transfer over DB links as I will do here and you can do in in parallel if the data set is large .
Transfer with export import
Transfer with option using transportable tablespaces (Post 18)
etc...
SQL> create user migrate identified by migrate default tablespace users;
User created.
SQL> grant dba to migrate;
Grant succeeded.
SQL> conn migrate/migrate
Connected.
SQL>
SQL> create database link TEST connect to migrate identified by migrate using 'TEST';
Database link created.
SQL> select table_name from user_tables@test;
TABLE_NAME
--------------------------------------------------------------------------------
TABLES
INDEXES
OBJECTS
SQL>
SQL> create table TABLES as select * from TABLES@test;
Table created.
SQL>
SQL> create table INDEXES as select * from INDEXES@test;
Table created.
SQL> create table OBJECTS as select * from OBJECTS@test;
Table created.
SQL>
SQL> show user
USER is "MIGRATE"
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
OBJECTS
INDEXES
TABLES
SQL>
So, we have data on both on source and target sides i.e. initial load is done
create user source identified by source default tablespace users;
grant dba to source;
conn source/source
create database link SOURCE_TEST connect to source identified by source using 'TEST';
create table users as select * from users@SOURCE_TEST;
create table sessions as select * from sessions@SOURCE_TEST;
create table sqls as select * from sqls@SOURCE_TEST;
-------------------------------------------------------------------------------------------------
Output is as:
SQL> create user source identified by source default tablespace users;
User created.
SQL> grant dba to source;
Grant succeeded.
SQL> conn source/source
Connected.
SQL>
SQL> create database link SOURCE_TEST connect to source identified by source using 'TEST';
Database link created.
SQL> create table users as select * from users@SOURCE_TEST;
Table created.
SQL> create table sessions as select * from sessions@SOURCE_TEST;
Table created.
SQL> create table sqls as select * from sqls@SOURCE_TEST;
Table created.
SQL>
---------------------------------------------------------------------------------------------------------
Lets create the extract and replicats with ddl replication.
for that lets do the pre-steps for DDL replication in following post:
http://gurpartapblog.blogspot.com/2010/11/configure-ddl-synchronizationreplicatio.html
This time I hit this error so fixed it as well.
===============================================================================================
SQL> @ddl_pin GOLDENGATE
BEGIN dbms_shared_pool.keep('GOLDENGATE .DDLReplication', 'P'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.KEEP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
BEGIN dbms_shared_pool.keep('GOLDENGATE .trace_put_line', 'P'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.KEEP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
BEGIN dbms_shared_pool.keep('SYS. GGS_DDL_TRIGGER_BEFORE', 'R'); END;
*
ERROR at line 1:
ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_SHARED_POOL.KEEP' must be declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored
SQL> @$ORACLE_HOME/rdbms/admin/dbmspool.sql
Package created.
Grant succeeded.
View created.
Package body created.
SQL> @ddl_pin GOLDENGATE
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
SQL>
===============================================================================================
Source side:
GGSCI (dg01) 25> dblogin userid migrate, password migrate
Successfully logged into database.
GGSCI (dg01) 26> add trandata migrate.*;
ERROR: No viable tables matched specification.
GGSCI (dg01) 27>
GGSCI (dg01) 36> DBLOGIN USERID source, password source
Successfully logged into database.
GGSCI (dg01) 37> ADD TRANDATA source.*;
ERROR: No viable tables matched specification.
GGSCI (dg01) 38>
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
SQL> alter system set recyclebin=OFF scope=spfile;
System altered.
SQL>
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
DBLOGIN USERID goldengate, PASSWORD goldengate
ADD EXTRACT EMIGRATE,TRANLOGS,BEGIN NOW
ADD EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/mg, extract emigrate
ADD EXTRACT pmigrate, EXTTRAILSOURCE /u01/app/oracle/product/goldengate/dirdat/mg
ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/mg, EXTRACT PMIGRATE
edit params emigrate
EXTRACT emigrate
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
TRANLOGOPTIONS ASMUSER sys@DG01ASM, ASMPASSWORD AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI, ENCRYPTKEY default
EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/mg
REPORTCOUNT EVERY 5 MINUTES, RATE
DDL INCLUDE OBJNAME "migrate.*", INCLUDE OBJNAME "source.*"
TABLE migrate.*;
TABLE source.*;
edit params pmigrate
EXTRACT pmigrate
RMTHOST restart, MGRPORT 7809
RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/mg
PASSTHRU
TABLE migrate.*;
TABLE source.*;
start extract EMIGRATE
start extract pmigrate
In logs we have:
2011-10-27 23:18:02 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract EMIGRATE.
2011-10-27 23:18:02 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.117 (START EXTRACT EMIGRATE ).
2011-10-27 23:18:02 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT EMIGRATE starting.
2011-10-27 23:18:02 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, emigrate.prm: EXTRACT EMIGRATE starting.
2011-10-27 23:18:02 INFO OGG-01635 Oracle GoldenGate Capture for Oracle, emigrate.prm: BOUNDED RECOVERY: reset to initial or altered checkpoint.
2011-10-27 23:18:03 INFO OGG-01515 Oracle GoldenGate Capture for Oracle, emigrate.prm: Positioning to begin time Oct 27, 2011 11:08:50 PM.
2011-10-27 23:18:05 INFO OGG-01516 Oracle GoldenGate Capture for Oracle, emigrate.prm: Positioned to Sequence 35, RBA 17320976, Oct 27, 2011 11:08:50 PM.
2011-10-27 23:18:05 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, emigrate.prm: EXTRACT EMIGRATE started.
2011-10-27 23:18:05 INFO OGG-01052 Oracle GoldenGate Capture for Oracle, emigrate.prm: No recovery is required for target file /u01/app/oracle/product/goldengate/dirdat/mg000000, at RBA 0 (file not opened).
2011-10-27 23:18:05 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, emigrate.prm: Output file /u01/app/oracle/product/goldengate/dirdat/mg is using format RELEASE 10.4/11.1.
2011-10-27 23:18:05 INFO OGG-01517 Oracle GoldenGate Capture for Oracle, emigrate.prm: Position of first record processed Sequence 35, RBA 17320976, SCN 0.1229585, Oct 27, 2011 11:08:54 PM.
2011-10-27 23:18:25 INFO OGG-00987 Oracle GoldenGate Command Interpreter for Oracle: GGSCI command (oracle): start extract pmigrate.
2011-10-27 23:18:25 INFO OGG-00963 Oracle GoldenGate Manager for Oracle, mgr.prm: Command received from GGSCI on host 192.168.1.117 (START EXTRACT PMIGRATE ).
2011-10-27 23:18:25 INFO OGG-00975 Oracle GoldenGate Manager for Oracle, mgr.prm: EXTRACT PMIGRATE starting.
2011-10-27 23:18:25 INFO OGG-00992 Oracle GoldenGate Capture for Oracle, pmigrate.prm: EXTRACT PMIGRATE starting.
2011-10-27 23:18:25 INFO OGG-00993 Oracle GoldenGate Capture for Oracle, pmigrate.prm: EXTRACT PMIGRATE started.
2011-10-27 23:18:30 INFO OGG-01226 Oracle GoldenGate Capture for Oracle, pmigrate.prm: Socket buffer size set to 27985 (flush size 27985).
2011-10-27 23:18:41 INFO OGG-01052 Oracle GoldenGate Capture for Oracle, pmigrate.prm: No recovery is required for target file /u01/app/oracle/product/goldengate/dirdat/mg000000, at RBA 0 (file not opened).
2011-10-27 23:18:41 INFO OGG-01478 Oracle GoldenGate Capture for Oracle, pmigrate.prm: Output file /u01/app/oracle/product/goldengate/dirdat/mg is using format RELEASE 10.4/11.1.
and
GGSCI (dg01) 63> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ELORD1 00:00:00 00:00:04
EXTRACT RUNNING EMIGRATE 00:00:00 00:00:06
EXTRACT RUNNING ETRUTH 00:00:00 00:00:02
EXTRACT RUNNING PMIGRATE 00:00:00 00:00:04
EXTRACT RUNNING PTRUTH 00:00:00 00:00:09
GGSCI (dg01) 64>
On taget side we do:
ADD REPLICAT RMIGRATE, EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/mg
REPLICAT rmigrate
ASSUMETARGETDEFS
USERID goldengate@khemkaran, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
ALLOWDUPTARGETMAP
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/product/goldengate/dirdat/KHEMKARAN/MIGRATE/discard_migrate, purge, MEGABYTES 100
DISCARDROLLOVER AT 20:00
REPERROR (DEFAULT, DISCARD)
GETINSERTS
GETUPDATES
GETDELETES
MAP migrate.*, TARGET migrate.*;
MAP source.*, TARGET source.*;
==================================================================================================================
Now, we will test the replication as:
On target:
GGSCI (dg01) 16> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
EXTRACT RUNNING ELORD1 00:00:00 00:00:00
EXTRACT RUNNING EMIGRATE 00:00:00 00:00:00
EXTRACT RUNNING ETRUTH 00:00:00 00:00:09
EXTRACT RUNNING PMIGRATE 00:00:00 00:00:03
EXTRACT RUNNING PTRUTH 00:00:00 00:00:07
GGSCI (dg01) 17>
-------------------------------------------------------------------------------------------
On source:
GGSCI (restart.rac.meditate.com) 11> info all
Program Status Group Lag Time Since Chkpt
MANAGER RUNNING
REPLICAT RUNNING RLORD1 00:00:00 00:00:02
REPLICAT RUNNING RMIGRATE 00:00:00 00:00:07
REPLICAT RUNNING RTRUTH 00:00:00 00:00:07
GGSCI (restart.rac.meditate.com) 12>
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
On source:
SQL> desc objects
Name Null? Type
----------------------- -------- ----------------
OWNER VARCHAR2(30)
OBJECT_NAME VARCHAR2(128)
SUBOBJECT_NAME VARCHAR2(30)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(19)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(19)
STATUS VARCHAR2(7)
TEMPORARY VARCHAR2(1)
GENERATED VARCHAR2(1)
SECONDARY VARCHAR2(1)
SQL> select count(*) from objects where SECONDARY is not null;
COUNT(*)
----------
50418
-------------------------------------------------------------------------------
On target:
SQL> conn migrate/migrate
Connected.
SQL> select count(*) from objects where SECONDARY is not null;
COUNT(*)
----------
50418
SQL> desc objects
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(90)
OBJECT_NAME VARCHAR2(384)
SUBOBJECT_NAME VARCHAR2(90)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(57)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(57)
STATUS VARCHAR2(21)
TEMPORARY VARCHAR2(3)
GENERATED VARCHAR2(3)
SECONDARY VARCHAR2(3)
SQL>
--------------------------------------------------------------------------------------------
On Source:
SQL> alter table objects drop column SECONDARY;
Table altered.
SQL>
On target:
In goldengate log:
2011-11-03 10:41:38 INFO OGG-01407 Oracle GoldenGate Delivery for Oracle, rmigrate.prm: Setting current schema for DDL operation to [MIGRATE].
2011-11-03 10:42:03 INFO OGG-01408 Oracle GoldenGate Delivery for Oracle, rmigrate.prm: Restoring current schema for DDL operation to [GOLDENGATE].
In scehma on target we have:
SQL> desc objects
Name Null? Type
----------------------------------------- -------- ----------------------------
OWNER VARCHAR2(90)
OBJECT_NAME VARCHAR2(384)
SUBOBJECT_NAME VARCHAR2(90)
OBJECT_ID NUMBER
DATA_OBJECT_ID NUMBER
OBJECT_TYPE VARCHAR2(57)
CREATED DATE
LAST_DDL_TIME DATE
TIMESTAMP VARCHAR2(57)
STATUS VARCHAR2(21)
TEMPORARY VARCHAR2(3)
GENERATED VARCHAR2(3)
SQL>
------------------------------------------------------------------------------------------------------
Also, the stats from target and sourcea are:
GGSCI (dg01) 18> STATS EXT EMIGRATE
Sending STATS request to EXTRACT EMIGRATE ...
Start of Statistics at 2011-11-02 17:52:31.
DDL replication statistics (for all trails):
*** Total statistics since extract started ***
Operations 1.00
Mapped operations 1.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
Output to /u01/app/oracle/product/goldengate/dirdat/mg:
Extracting from GOLDENGATE.GGS_MARKER to GOLDENGATE.GGS_MARKER:
*** Total statistics since 2011-11-02 17:33:49 ***
No database operations have been performed.
*** Daily statistics since 2011-11-02 17:33:49 ***
No database operations have been performed.
*** Hourly statistics since 2011-11-02 17:33:49 ***
No database operations have been performed.
*** Latest statistics since 2011-11-02 17:33:49 ***
No database operations have been performed.
End of Statistics.
GGSCI (dg01) 19>
On target:
GGSCI (restart.rac.meditate.com) 16> STATS REP RMIGRATE
Sending STATS request to REPLICAT RMIGRATE ...
No active replication maps
DDL replication statistics:
*** Total statistics since replicat started ***
Operations 1.00
Mapped operations 1.00
Unmapped operations 0.00
Other operations 0.00
Excluded operations 0.00
Errors 0.00
Retried errors 0.00
Discarded errors 0.00
Ignored errors 0.00
.
GGSCI (restart.rac.meditate.com) 17>
-------------------------------------------------------------------------------------------------------
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
So, DDL replication is working.
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Now, lets setup fall back extracts that we will start then application will be switched to target database.
So, on target do the following on ggsci command prompt:
DBLOGIN USERID goldengate, PASSWORD goldengate
ADD EXTRACT efallm,TRANLOGS,BEGIN NOW
ADD EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/fm, extract efallm
ADD EXTRACT pfallm, EXTTRAILSOURCE /u01/app/oracle/product/goldengate/dirdat/fm
ADD RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/fm, EXTRACT pfallm
edit params efallm
EXTRACT efallm
USERID goldengate, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
TRANLOGOPTIONS ASMUSER sys@DG01ASM, ASMPASSWORD AACAAAAAAAAAAAJASAOBIHRGNFGAYCKBHJPAGIXFGGBJUAXI, ENCRYPTKEY default
EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/fm
REPORTCOUNT EVERY 5 MINUTES, RATE
DDL INCLUDE OBJNAME "migrate.*", INCLUDE OBJNAME "source.*"
TABLE migrate.*;
TABLE source.*;
On target the following:
mkdir -p /u01/app/oracle/product/goldengate/dirdat/KHEMKARAN/MIGRATE
ADD REPLICAT RFALLM, EXTTRAIL /u01/app/oracle/product/goldengate/dirdat/fm
edit params RFALLM
REPLICAT rfallm
ASSUMETARGETDEFS
USERID goldengate@khemkaran, PASSWORD AACAAAAAAAAAAAKAPATACEHBIGQGCFZCCDIGAEMCQFFBZHVC, ENCRYPTKEY default
ALLOWDUPTARGETMAP
ASSUMETARGETDEFS
DISCARDFILE /u01/app/oracle/product/goldengate/dirdat/KHEMKARAN/MIGRATE/discard_migrate, purge, MEGABYTES 100
DISCARDROLLOVER AT 20:00
REPERROR (DEFAULT, DISCARD)
GETINSERTS
GETUPDATES
GETDELETES
MAP migrate.*, TARGET migrate.*;
MAP source.*, TARGET source.*;
---------------------------------------------------------------------------------------------------------------
edit params pfallm
EXTRACT pfallm
RMTHOST restart, MGRPORT 7909
RMTTRAIL /u01/app/oracle/product/goldengate/dirdat/fm
PASSTHRU
TABLE migrate.*;
TABLE source.*;
---------------------------------------------------------------------------------------------------------------
On source we will allocate the fall back replicat as we did on target side.
==================================================================================================================
Now we are ready for change stop application and make sure all the changes have been applied to the target side and
stop orignal extracts and replicats and start the fallback extracts and replicats and point the application
to the target side. This gives you the option if you want to move back as well.
=====================================================================================================================
The Lord has blessed His servant with His Name. What can any poor mortal do to someone who has the Lord as his Savior and Protector? 1 Pause He Himself is the Great Being; He Himself is the Leader. He Himself accomplishes the tasks of His servant. Our Lord and Master destroys all demons; He is the Inner-knower, the Searcher of hearts. 1 He
Himself saves the honor of His servants; He Himself blesses them with stability. From the very beginning of time, and throughout the ages, He saves His servants. O Nanak, how rare is the person who knows God.
No comments:
Post a Comment