Moving to Oracle RAC One Node - Page 9

7. Migrate database applications to Oracle RAC One Node

Now that we have our Oracle RAC One Node installed and configured, let’s migrate our database applications (GL, PO) from our existing single database instance (finsdb) to the new RAC One Node environment.

The data will be migrated using Oracle Transportable Tablespace. Oracle Transportable Tablespace is an efficient method of moving bulk of data quickly between databases.

7.1 Verify that the tablespaces are self-contained on the source database

SQL> execute dbms_tts.transport_set_check('GL_D,PO_D',TRUE);
 
PL/SQL procedure successfully completed.
 
SQL> SQL> select * from transport_set_violations;
 
no rows selected

7.2 Set the source tablespaces to be transported to read only

SQL> alter tablespace gl_d read only;
 
Tablespace altered.
 
SQL> alter tablespace po_d read only;
 
Tablespace altered.

7.3 Export the tablespaces’ metadata

oracle@plaice-> expdp dumpfile=exp_glpo.dmp logfile=exp_glpo.log directory=data_pump_dir transport_tablespaces=gl_d,po_d
 
Export: Release 11.2.0.1.0 - Production on Sun Mar 28 12:40:08 2010
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
Username: system
Password:
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning and Real Application Testing options
Starting "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01":  system/******** dumpfile=exp_glpo.dmp logfile=exp_glpo.log directory=data_pump_dir transport_tablespaces=gl_d,po_d
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Master table "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully loaded/unloaded
******************************************************************************
Dump file set for SYSTEM.SYS_EXPORT_TRANSPORTABLE_01 is:
  /u01/app/oracle/admin/appsdb/dpdump/exp_glpo.dmp
******************************************************************************
Datafiles required for transportable tablespace GL_D:
  /u01/oradata/appsdb/gl_d01.dbf
Datafiles required for transportable tablespace PO_D:
  /u01/oradata/appsdb/po_d01.dbf
Job "SYSTEM"."SYS_EXPORT_TRANSPORTABLE_01" successfully completed at 12:41:44

7.4 Transport the tablespaces’ metadata and datafiles to the target shared storage

Since the datafiles were small, we simply transferred the files using FTP to the target server (gurnard1). We could have also mounted the export directory on to the target server or set up the ASM shared storage access on the source server (plaice).

As the oracle user on the source server:

oracle@plaice-> cd /u01/oradata/finsdb
oracle@plaice-> ls gl* po*
gl_d01.dbf  po_d01.dbf
oracle@plaice-> ls /u01/app/oracle/admin/finsdb/dpdump/*dmp
/u01/app/oracle/admin/finsdb/dpdump/exp_glpo.dmp
oracle@plaice-> sftp gurnard1
Connecting to gurnard1...
sftp> cd /tmp
sftp> put gl_d01.dbf
Uploading gl_d01.dbf to /tmp/gl_d01.dbf
gl_d01.dbf                                  100%  100MB  20.0MB/s   00:05
sftp> put po_d01.dbf
Uploading po_d01.dbf to /tmp/po_d01.dbf
po_d01.dbf                                  100%  100MB  25.0MB/s   00:04
sftp> put /u01/app/oracle/admin/finsdb/dpdump/exp_glpo.dmp
Uploading /u01/app/oracle/admin/finsdb/dpdump/exp_glpo.dmp to /tmp/exp_glpo.dmp
/u01/app/oracle/admin/finsdb/dpdump/exp_glp 100%  612KB 612.0KB/s   00:00
sftp> bye

As the grid user on the target server:

grid@gurnard1-> cd /tmp
grid@gurnard1-> ls *.dmp *.dbf
exp_glpo.dmp  gl_d01.dbf  po_d01.dbf
grid@gurnard1-> asmcmd
ASMCMD> cp /tmp/gl_d01.dbf +DATA/APPSDB/DATAFILE/GL
copying /tmp/gl_d01.dbf -> +DATA/APPSDB/DATAFILE/GL
ASMCMD> cp /tmp/po_d01.dbf +DATA/APPSDB/DATAFILE/PO
copying /tmp/po_d01.dbf -> +DATA/APPSDB/DATAFILE/PO
ASMCMD> cd +DATA/appsdb/datafile
ASMCMD> ls -l
Type      Redund  Striped  Time             Sys  Name
                                            N    GL => +DATA/ASM/DATAFILE/GL.272.715702487
                                            N    PO => +DATA/ASM/DATAFILE/PO.273.715702501
DATAFILE  MIRROR  COARSE   MAR 27 22:00:00  Y    SYSAUX.257.713574807
DATAFILE  MIRROR  COARSE   MAR 27 22:00:00  Y    SYSTEM.256.713574803
DATAFILE  MIRROR  COARSE   MAR 27 00:00:00  Y    UNDOTBS1.258.713574809
DATAFILE  MIRROR  COARSE   MAR 27 00:00:00  Y    USERS.259.713574809

7.5 Create the target schemas, GL and PO

SQL> connect system/Oracle#101@appsdb
Connected.
SQL> create user po identified by oracle default tablespace users temporary tablespace temp;
 
User created.
 
SQL> create user gl identified by oracle default tablespace users temporary tablespace temp;
 
User created.
 
SQL> grant connect,resource to po,gl;
 
Grant succeeded.

7.6 Import the tablespaces set

oracle@gurnard1-> cd /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/log
oracle@gurnard1-> mv /tmp/exp_glpo.dmp .
oracle@gurnard1-> impdp dumpfile=exp_glpo.dmp 
	logfile=imp_glpo.log directory=data_pump_dir transport_datafiles='+DATA/ASM/DATAFILE/GL.272.715702487','+DATA/ASM/DATAFILE/PO.273.715702501'
 
Import: Release 11.2.0.1.0 - Production on Sun Mar 28 13:59:05 2010
 
Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.
 
Username: system@appsdb
Password:
 
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management and Real Application Testing options
Master table "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully loaded/unloaded
Starting "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01":  system/********@appsdb dumpfile=exp_glpo.dmp logfile=imp_glpo.log 
	directory=data_pump_dir transport_datafiles=+DATA/ASM/DATAFILE/GL.272.715702487,+DATA/ASM/DATAFILE/PO.273.715702501
Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
Processing object type TRANSPORTABLE_EXPORT/TABLE
Processing object type TRANSPORTABLE_EXPORT/GRANT/OWNER_GRANT/OBJECT_GRANT
Processing object type TRANSPORTABLE_EXPORT/INDEX
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/INDEX_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/COMMENT
Processing object type TRANSPORTABLE_EXPORT/CONSTRAINT/REF_CONSTRAINT
Processing object type TRANSPORTABLE_EXPORT/TRIGGER
Processing object type TRANSPORTABLE_EXPORT/TABLE_STATISTICS
Processing object type TRANSPORTABLE_EXPORT/POST_INSTANCE/PLUGTS_BLK
Job "SYSTEM"."SYS_IMPORT_TRANSPORTABLE_01" successfully completed at 14:00:02

7.7 Set the target tablespaces to read write

SQL> connect system/Oracle#101@appsdb
Connected.
SQL> alter tablespace gl_d read write;
 
Tablespace altered.
 
SQL> alter tablespace po_d read write;
 
Tablespace altered.