Introduction:
Zero downtime is used to perform a cross platform database
migration (in our scenario Linux to CentOS)
and also a database upgrade (in our scenario from 10g to 11g Release 2) which
is attained with zero downtime using a combination of RMAN, Cross Platform Transportable
Tablespace and Golden Gate.
migration (in our scenario Linux to CentOS)
and also a database upgrade (in our scenario from 10g to 11g Release 2) which
is attained with zero downtime using a combination of RMAN, Cross Platform Transportable
Tablespace and Golden Gate.
Advantage:
Zero Down Time – The advantage of Zero Down time of
Production can be achieve while migration/upgrade process by creating clone
db(duplicate database) of source, move all the database structure and table
spaces meta data from clone database to target database and then migrate the
business transactions which are created in source database in between the
target database creation using transportable tablespace, So in the hole
scenario production will be up and running only and no production down time is
required to achieve the migration/upgrade process.
Production can be achieve while migration/upgrade process by creating clone
db(duplicate database) of source, move all the database structure and table
spaces meta data from clone database to target database and then migrate the
business transactions which are created in source database in between the
target database creation using transportable tablespace, So in the hole
scenario production will be up and running only and no production down time is
required to achieve the migration/upgrade process.
Environment:
|
|
IP Address
|
OS
|
DB Name
|
DB Version
|
Host Name
|
Golden Gate Location
|
|
SOURCE
|
192.168.1.178
|
Linux 5.4
(32 bit) |
Ora10gs
|
10..2.0.1.0
|
dbgs
|
/u01/app/GGS/
|
|
Clonedb
|
192.168.1.178
|
Linux 5.4
(32 bit) |
Ora10gsd
|
10..2.0.1.0
|
dbgs
|
/u01/app/GGS/
|
|
TARGET
|
192.168.1.179
|
CentOS 6.2
(64 bit) |
Ora11gt
|
11.2.0.1
|
dbgt
|
/u01/app/GGS/
|
Zero
Downtime Steps:
Downtime Steps:
1) Create the GoldenGate Extract
process on source Linux DB10g and start the same. This extract process will be
capturing changes as they occur on the 10g Linux database in the remote trail
files located on the CentOS target system. Since the replicat process is not
running on the target at this time, the source database changes will accumulate
in the extract trail files.
process on source Linux DB10g and start the same. This extract process will be
capturing changes as they occur on the 10g Linux database in the remote trail
files located on the CentOS target system. Since the replicat process is not
running on the target at this time, the source database changes will accumulate
in the extract trail files.
Prepare data on source database
to replicat on target:
to replicat on target:
sqlplus ‘/as
sysdba’
sysdba’
SQL>CREATE
TABLESPACE hr DATAFILE ‘/u01/app/oracle/oradata/ora10gs/hr.dbf’ SIZE 1G
AUTOEXTEND ON NEXT 1G MAXSIZE 2G BLOCKSIZE 8192 NOLOGGING ONLINE EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO FLASHBACK OFF;
TABLESPACE hr DATAFILE ‘/u01/app/oracle/oradata/ora10gs/hr.dbf’ SIZE 1G
AUTOEXTEND ON NEXT 1G MAXSIZE 2G BLOCKSIZE 8192 NOLOGGING ONLINE EXTENT
MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO FLASHBACK OFF;
SQL>
CREATE TABLESPACE pa DATAFILE
‘/u01/app/oracle/oradata/ora10gs/pa.dbf’ SIZE 1G AUTOEXTEND ON NEXT 1G
MAXSIZE 2G BLOCKSIZE 8192 NOLOGGING ONLINE EXTENT MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT AUTO FLASHBACK OFF;
CREATE TABLESPACE pa DATAFILE
‘/u01/app/oracle/oradata/ora10gs/pa.dbf’ SIZE 1G AUTOEXTEND ON NEXT 1G
MAXSIZE 2G BLOCKSIZE 8192 NOLOGGING ONLINE EXTENT MANAGEMENT LOCAL SEGMENT
SPACE MANAGEMENT AUTO FLASHBACK OFF;
SQL>
create user hr identified by oracle default tablespace hr temporary tablespace
temp;
create user hr identified by oracle default tablespace hr temporary tablespace
temp;
User
created.
created.
SQL>
grant connect,resource, unlimited tablespace to hr;
grant connect,resource, unlimited tablespace to hr;
Grant
succeeded.
succeeded.
SQL>
create user pa identified by oracle default tablespace pa temporary tablespace
temp;
create user pa identified by oracle default tablespace pa temporary tablespace
temp;
User
created.
created.
SQL>
grant connect,resource, unlimited tablespace to pa;
grant connect,resource, unlimited tablespace to pa;
Grant
succeeded.
succeeded.
SQL> conn
hr/oracle
hr/oracle
SQL>
create table emp_info(id number(6), name varchar2(50), sex varchar2(1), address
varchar2(100), designation varchar2(50), constraint pk_id PRIMARY KEY(id));
create table emp_info(id number(6), name varchar2(50), sex varchar2(1), address
varchar2(100), designation varchar2(50), constraint pk_id PRIMARY KEY(id));
SQL> conn
pa/oracle
pa/oracle
SQL>
create table pay_info(emp_id number(6), basic number(6), hra number(6), others
number(6), designation varchar2(50), constraint pk_emp_id PRIMARY KEY(emp_id));
create table pay_info(emp_id number(6), basic number(6), hra number(6), others
number(6), designation varchar2(50), constraint pk_emp_id PRIMARY KEY(emp_id));
Start Extract related activities:
Download Golden Gate V32406—01.zip from edelivery site
for Oracle Database 10g on Oracle Linux 32 bit and setup the same as by
following process of ‘Installation of Golden Gate Software’, ‘Preparation of
database for replication’ upto 3 a) in our
post ‘Oracle Golden Gate – Unidirectional’
for Oracle Database 10g on Oracle Linux 32 bit and setup the same as by
following process of ‘Installation of Golden Gate Software’, ‘Preparation of
database for replication’ upto 3 a) in our
post ‘Oracle Golden Gate – Unidirectional’
sqlplus ‘/as
sysdba’
sysdba’
SQL>
create user ggs_owner identified by oracle default tablespace users temporary
tablespace temp;
create user ggs_owner identified by oracle default tablespace users temporary
tablespace temp;
User
created.
created.
Grant the
following privileges to ggs_owner user
following privileges to ggs_owner user
grant
connect, resource, unlimited tablespace to ggs_owner;
connect, resource, unlimited tablespace to ggs_owner;
grant
execute on utl_file to ggs_owner;
execute on utl_file to ggs_owner;
grant connect, resource to ggs_owner;
grant select any dictionary, select any table to ggs_owner;
grant create table to ggs_owner;
grant flashback any table to ggs_owner;
grant execute on dbms_flashback to ggs_owner;
grant execute on utl_file to ggs_owner;
grant create any table to ggs_owner;
grant insert any table to ggs_owner;
grant update any table to ggs_owner;
grant delete any table to ggs_owner;
grant drop any table to ggs_owner;
Go to Golden Gate installation location and
run scripts for creating all necessary objects for support ddl replication:
run scripts for creating all necessary objects for support ddl replication:
SQL> @marker_setup.sql
SQL>
@ddl_setup.sql
@ddl_setup.sql
SQL>
@role_setup.sql
@role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggate
SQL> @ddl_enable.sql
cd
/u01/app/GGS
/u01/app/GGS
$. ggsci
GGSCI (dbgs.doyensys.com)
3> start mgr
3> start mgr
Manager
started.
started.
GGSCI (dbgs.doyensys.com)
8> edit params ext_gg1
8> edit params ext_gg1
–extract
group–
group–
EXTRACT
ext_gg1
ext_gg1
–connection
to database–
to database–
USERID
ggs_owner, PASSWORD oracle
ggs_owner, PASSWORD oracle
DBOPTIONS
ALLOWNOLOGGING
ALLOWNOLOGGING
SETENV
(ORACLE_HOME = “/u01/app/oracle/product/10.2.0/db_1”)
(ORACLE_HOME = “/u01/app/oracle/product/10.2.0/db_1”)
SETENV
(ORACLE_SID = “ora10gs”)
(ORACLE_SID = “ora10gs”)
–hostname
and port for trail–
and port for trail–
RMTHOST
192.168.1.179, MGRPORT 7809
192.168.1.179, MGRPORT 7809
–path and
name for remote trail–
name for remote trail–
RMTTRAIL
/u01/app/GGS/dirdat/lt
/u01/app/GGS/dirdat/lt
DISCARDFILE
discard.txt, APPEND
discard.txt, APPEND
–DDL
support
support
DDL INCLUDE
ALL
ALL
DDLOPTIONS
ADDTRANDATA
ADDTRANDATA
table hr.*;
table pa.*;
Save and Quit
2)
Create
a skeleton database(Install ORACLE_HOME and then create an empty database using dbca) on the TARGET
Centos platform in the 11g Release 2 environment – DB11g
Create
a skeleton database(Install ORACLE_HOME and then create an empty database using dbca) on the TARGET
Centos platform in the 11g Release 2 environment – DB11g
Download Golden Gate V34339-01.zip from
edelivery site for Oracle 11g on Linux Platform 64 bit and Install and setup
the same as by following process of ‘Installation of Golden Gate Software’ in our
post ‘Oracle Golden Gate – Unidirectional’
edelivery site for Oracle 11g on Linux Platform 64 bit and Install and setup
the same as by following process of ‘Installation of Golden Gate Software’ in our
post ‘Oracle Golden Gate – Unidirectional’
Start the Replicat manager.
3)
Backup the SOURCE database and then Start the extract on SOURCE 10g
Backup the SOURCE database and then Start the extract on SOURCE 10g
GGSCI
(dbg.doyensys.com) 9> start extract ext_gg1
(dbg.doyensys.com) 9> start extract ext_gg1
Sending
START request to MANAGER …
START request to MANAGER …
EXTRACT
EXT_GG1 starting
EXT_GG1 starting
GGSCI
(dbg.doyensys.com) 11> info extract ext_gg1
(dbg.doyensys.com) 11> info extract ext_gg1
EXTRACT EXT_GG1
Initialized 2014-06-06
10:42 Status STOPPED
Initialized 2014-06-06
10:42 Status STOPPED
Checkpoint
Lag 00:00:00 (updated 00:09:57 ago)
Lag 00:00:00 (updated 00:09:57 ago)
Log Read
Checkpoint Oracle Redo Logs
Checkpoint Oracle Redo Logs
2014-06-06 10:42:11 Seqno 0, RBA 0
SCN 0.0 (0)
4)
Using
RMAN create a duplicate database in the source Linux environment (Clonedb) –
this database will be used as the source for the export of database structure
(no rows export) and tablespace meta data.
Using
RMAN create a duplicate database in the source Linux environment (Clonedb) –
this database will be used as the source for the export of database structure
(no rows export) and tablespace meta data.
***********ON SOURCE – TRANSACTION 1**********
sqlplus hr/oracle
SQL> update emp_info set
designation = ‘LEAD CONSULTANT’ where id=1,15,39,45,59;
designation = ‘LEAD CONSULTANT’ where id=1,15,39,45,59;
5)
Take
a full export of the database without any table data to get just the structure
of the database – this is now taken from the clonedb duplicate database created
in step 4
Take
a full export of the database without any table data to get just the structure
of the database – this is now taken from the clonedb duplicate database created
in step 4
sqlplus ‘/as sysdba’
SQL>create directory dumpdir as
‘/u01/dumpdir’;
‘/u01/dumpdir’;
Username: sys as sysdba
expdp system/oracle
dumpfile=full_norows.dmp directory=dumpdir content=metadata_only
exclude=tables,index full=y
dumpfile=full_norows.dmp directory=dumpdir content=metadata_only
exclude=tables,index full=y
6)
scp
the exported dump into target server to the location of dumpdir and then Import the dumpfile into the target 11g
database DB11g which has the database structure without the table data – this
will create all the users, roles, synonyms etc
scp
the exported dump into target server to the location of dumpdir and then Import the dumpfile into the target 11g
database DB11g which has the database structure without the table data – this
will create all the users, roles, synonyms etc
We had to
create a role and also create the directory before doing the full database
import.
create a role and also create the directory before doing the full database
import.
Note: Ignore the errors during the
import as it will pertain to objects which already exist in the scratch
database.
import as it will pertain to objects which already exist in the scratch
database.
[oracle@dbgt temp]$ scp -r
oradup@192.168.1.178:/u01/dumpdir/full_norows.dmp .
oradup@192.168.1.178:/u01/dumpdir/full_norows.dmp .
SQL>
create role xdbwebservices;
create role xdbwebservices;
Role
created.
created.
SQL>
create directory dumpdir as ‘/u01/dumpdir’;
create directory dumpdir as ‘/u01/dumpdir’;
Directory
created.
created.
[oracle@dbgt temp]$ impdp dumpfile=full_norows.dmp
directory=dumpdir full=y
directory=dumpdir full=y
7)
On the clonedb database, we now will export the tablespace meta
data – make the required tablespaces read only. Note that the original source
10g database is in read write mode and is being accessed by the users with no
downtime as yet.
On the clonedb database, we now will export the tablespace meta
data – make the required tablespaces read only. Note that the original source
10g database is in read write mode and is being accessed by the users with no
downtime as yet.
sqlplus ‘/as
sysdba’
sysdba’
SQL>
alter tablespace hr read only;
alter tablespace hr read only;
SQL>
alter tablespace pa read only;
alter tablespace pa read only;
[oradup@dbgs
dumpdir]$ expdp dumpfile=tts_meta.dmp directory=dumpdir transport_tablespaces=hr,pa
dumpdir]$ expdp dumpfile=tts_meta.dmp directory=dumpdir transport_tablespaces=hr,pa
8)
Copy the
datafiles from the read only tablespaces ( from clonedb) to the target Centos
system and using RMAN convert the datafiles from the Linux platform to the
Centos platform (To identify the platform use the query select
d.platform_name db_platform, t.platform_name tts_platform, t.endian_format from
v$transportable_platform t, v$database d where t.platform_name =
d.platform_name;)
Copy the
datafiles from the read only tablespaces ( from clonedb) to the target Centos
system and using RMAN convert the datafiles from the Linux platform to the
Centos platform (To identify the platform use the query select
d.platform_name db_platform, t.platform_name tts_platform, t.endian_format from
v$transportable_platform t, v$database d where t.platform_name =
d.platform_name;)
[oracle@dbgt temp]$ scp -r
oradup@192.168.1.178:/u01/dumpdir/tts_meta.dmp .
oradup@192.168.1.178:/u01/dumpdir/tts_meta.dmp .
[oracle@dbgt temp]$ scp -r
oradup@192.168.1.178:/u01/dupdb/oracle/oradata/ora10gsd/hr.dbf .
oradup@192.168.1.178:/u01/dupdb/oracle/oradata/ora10gsd/hr.dbf .
[oracle@dbgt temp]$ scp -r
oradup@192.168.1.178:/u01/dupdb/oracle/oradata/ora10gsd/pa.dbf .
oradup@192.168.1.178:/u01/dupdb/oracle/oradata/ora10gsd/pa.dbf .
RMAN> CONVERT DATAFILE
‘/u01/temp/hr.dbf’
‘/u01/temp/hr.dbf’
2> FROM PLATFORM=’Linux IA (32-bit)’
3> FORMAT
‘/u01/app/oracle/oradata/ora11gt/hr.dbf’;
‘/u01/app/oracle/oradata/ora11gt/hr.dbf’;
RMAN> CONVERT DATAFILE
‘/u01/temp/pa.dbf’
‘/u01/temp/pa.dbf’
2> FROM PLATFORM=’Linux IA (32-bit)’
3> FORMAT
‘/u01/app/oracle/oradata/ora11gt/pa.dbf’;
‘/u01/app/oracle/oradata/ora11gt/pa.dbf’;
9)
Import
the tablespace meta data into the 11g database and plug in the tablespaces
-make the tablespaces read write
Import
the tablespace meta data into the 11g database and plug in the tablespaces
-make the tablespaces read write
[oracle@dbgt temp]$ impdp dumpfile=tts_meta.dmp
directory=dumpdir transport_datafiles=”/u01/app/oracle/oradata/ora11gt/hr.dbf”,” /u01/app/oracle/oradata/ora11gt/pa.dbf”
directory=dumpdir transport_datafiles=”/u01/app/oracle/oradata/ora11gt/hr.dbf”,” /u01/app/oracle/oradata/ora11gt/pa.dbf”
Note: Before doing import the users of respective transportable
tablespace should be created on target. [in our case hr and pa]
tablespace should be created on target. [in our case hr and pa]
Username: sys as sysdba
SQL> alter tablespace hr read write;
Tablespace altered.
SQL> alter tablespace pa read write;
Tablespace altered.
***********ON SOURCE – TRANSACTION
2**********
2**********
sqlplus pa/oracle
SQL> update pay_info set basic
=50000, hra=25000 where designation = ‘LEAD CONSULTANT’;
=50000, hra=25000 where designation = ‘LEAD CONSULTANT’;
Note: As we make changes in the source
database, the trail files on the target start getting populated. These are
located in the destination we specified when creating the RMTTRAIL.
database, the trail files on the target start getting populated. These are
located in the destination we specified when creating the RMTTRAIL.
10)
On
the target Centos environment now we create and start the GoldenGate Replicat
process/processes. They will now start reading from the Extract trail files
created in Step 1 and will start applying them to the 11g database.
On
the target Centos environment now we create and start the GoldenGate Replicat
process/processes. They will now start reading from the Extract trail files
created in Step 1 and will start applying them to the 11g database.
SQL>
create user ggt_owner identified by oracle default tablespace users temporary
tablespace temp;
create user ggt_owner identified by oracle default tablespace users temporary
tablespace temp;
User
created.
created.
Grant the following privileges to
ggt_owner user
ggt_owner user
grant
connect, resource, unlimited tablespace to ggt_owner;
connect, resource, unlimited tablespace to ggt_owner;
grant
execute on utl_file to ggt_owner;
execute on utl_file to ggt_owner;
grant connect, resource to ggt_owner;
grant select any dictionary, select any table to
ggt_owner;
ggt_owner;
grant create table to ggt_owner;
grant flashback any table to ggt_owner;
grant execute on dbms_flashback to ggt_owner;
grant execute on utl_file to ggt_owner;
grant create any table to ggt_owner;
grant insert any table to ggt_owner;
grant update any table to ggt_owner;
grant delete any table to ggt_owner;
grant drop any table to ggt_owner;
Since using different golden gate user on target, run the
following to prepare the target for DDL
and provide the value for schema name input as ggt_owner
following to prepare the target for DDL
and provide the value for schema name input as ggt_owner
Go to Golden
Gate installation location and run scripts for creating all necessary objects
for support ddl replication:
Gate installation location and run scripts for creating all necessary objects
for support ddl replication:
SQL> @marker_setup.sql
SQL> @ddl_setup.sql
SQL> @role_setup.sql
SQL> grant GGS_GGSUSER_ROLE to ggate
SQL> @ddl_enable.sql
GGSCI
(dbgt.doyensys.com) 1> edit params ./GLOBAL
(dbgt.doyensys.com) 1> edit params ./GLOBAL
CHECKPOINTTABLE
ggt_owner.checkpoint
ggt_owner.checkpoint
GGSCI
(dbgt.doyensys.com) 2> dblogin userid ggt_owner, Password oracle
(dbgt.doyensys.com) 2> dblogin userid ggt_owner, Password oracle
Successfully logged into database.
GGSCI
(dbgt.doyensys.com) 3>add
checkpointtable ggt_owner.checkpoint
(dbgt.doyensys.com) 3>add
checkpointtable ggt_owner.checkpoint
Successfully created checkpointtable ggt_owner.CHECKPOINT.
GGSCI
(dbgt.doyensys.com) 4>add
replicat rep_gg2, exttrail
/u01/app/GGS/dirdat/lt, checkpointtable ggt_owner.checkpoint
(dbgt.doyensys.com) 4>add
replicat rep_gg2, exttrail
/u01/app/GGS/dirdat/lt, checkpointtable ggt_owner.checkpoint
REPLICAT added.
GGSCI (dbgt.doyensys.com) 5> edit params rep_gg2
–Replicat group
replicat rep_gg2
–source and target definitions
ASSUMETARGETDEFS
–target database login –
userid ggt_owner, password oracle
SETENV (ORACLE_HOME =
“/u01/app/oracle/product/11.2.0/db_1”)
“/u01/app/oracle/product/11.2.0/db_1”)
SETENV (ORACLE_SID = “ora11gt”)
DDL INCLUDE ALL
DDLERROR DEFAULT IGNORE RETRYOP
–file for dicarded transaction –
discardfile /u01/app/GGS/discard/rep_gg2_discard.txt, append,
megabytes 10
megabytes 10
–Specify table mapping —
map hr.*, TARGET hr.*;
map pa.*, TARGET pa.*;
11) Once all the changes in the trail
files have been applied by the Replicat process and we confirm that both source
and target are in sync (we can use another GoldenGate product called Veridata
for this), we can now point the users and application to the 11g Linux database
with no or minimal downtime which will depend on the infrastructure.
files have been applied by the Replicat process and we confirm that both source
and target are in sync (we can use another GoldenGate product called Veridata
for this), we can now point the users and application to the 11g Linux database
with no or minimal downtime which will depend on the infrastructure.
12) TEST
Query the following for the confirmation of data migration on target
which are done as part of TRANSACTION1 and TRANSACTION2 in source database for
testing purpose
which are done as part of TRANSACTION1 and TRANSACTION2 in source database for
testing purpose
sqlplus hr/oracle
SQL> select designation from emp_info
where id=1,15,39,45,59;
where id=1,15,39,45,59;
SQL> conn pa/oracle
SQL> select basic, hra from pay_info
where designation = ‘LEAD CONSULTANT’
where designation = ‘LEAD CONSULTANT’
Recommended Posts
