7.8 Check the imported objects on the target database
Compare the objects between the source and the target databases
to verify that we have the exact replica of the source schemas on the target
database.
On the source database (finsdb)
SQL> select object_type, count(*)
2 from dba_objects
3 where owner='GL'
4 group by object_type
5 order by 1
6 /
OBJECT_TYPE COUNT(*)
------------------- ----------
INDEX 19
PROCEDURE 2
SEQUENCE 3
SYNONYM 1
TABLE 7
VIEW 1
6 rows selected.
SQL> select object_type, count(*)
2 from dba_objects
3 where owner='PO'
4 group by object_type
5 order by 1
6 /
OBJECT_TYPE COUNT(*)
------------------- ----------
INDEX 4
LOB 1
TABLE 2
TRIGGER 2
TYPE 3
TYPE BODY 1
6 rows selected.
SQL> select constraint_type, count(*)
2 from dba_constraints
3 where owner='GL'
4 group by constraint_type
5 order by 1
6 /
C COUNT(*)
- ----------
C 15
P 7
R 10
U 1
4 rows selected.
SQL> select constraint_type, count(*)
2 from dba_constraints
3 where owner='PO'
4 group by constraint_type
5 order by 1
6 /
C COUNT(*)
- ----------
C 10
P 2
R 1
U 2
4 rows selected.
SQL> select owner, count(*)
2 from dba_objects
3 where owner in ('GL','PO')
4 group by owner
5 /
OWNER COUNT(*)
------------------------------ ----------
PO 13
GL 33
SQL> select count(*)
2 from dba_objects
3 where owner in ('GL','PO')
4 and status='INVALID';
COUNT(*)
----------
0
On the target database (appsdb)
SQL> select object_type, count(*)
2 from dba_objects
3 where owner='GL'
4 group by object_type
5 order by 1
6 /
OBJECT_TYPE COUNT(*)
------------------- ----------
INDEX 19
TABLE 7
2 rows selected.
SQL> select object_type, count(*)
2 from dba_objects
3 where owner='PO'
4 group by object_type
5 order by 1
6 /
OBJECT_TYPE COUNT(*)
------------------- ----------
INDEX 4
LOB 1
TABLE 2
TRIGGER 2
4 rows selected.
Notice from the above that Oracle
Transportable Tablespace does not import procedure, sequence, synonym , view
and type objects. We have to manually create these objects.
SQL> select constraint_type, count(*)
2 from dba_constraints
3 where owner='GL'
4 group by constraint_type
5 order by 1
6 /
C COUNT(*)
- ----------
C 15
P 7
R 10
U 1
4 rows selected.
SQL> select constraint_type, count(*)
2 from dba_constraints
3 where owner='PO'
4 group by constraint_type
5 order by 1
6 /
C COUNT(*)
- ----------
C 10
P 2
R 1
U 2
4 rows selected.
7.9 Create missing objects on the target database
We will run the queries below on the source database to extract
the DDLs of the missing objects and privileges and execute the script on the
target database. Lastly, perform a check on the objects count and make sure
there are no invalid objects on the target database.
On the source database (finsdb)
select dbms_metadata.get_ddl(object_type,object_name,owner)||'/'
from dba_objects
where object_type in ('PROCEDURE','SEQUENCE','SYNONYM','VIEW')
and owner='GL'
/
select dbms_metadata.get_ddl(object_type,object_name,owner)||'/'
from dba_objects
where object_type in ('TYPE')
and owner='PO'
/
select 'grant '||
privilege||
' on '||
owner||
'.'||
table_name||
' to '||
grantee||
' '||
decode(grantable,'YES',' with grant option ')||
decode(hierarchy,'YES',' with hierarchy option ')||
';'
from dba_tab_privs
where grantee in ('GL','PO')
/
On the target database (appsdb)
select owner, count(*)
from dba_objects
where owner in ('GL','PO')
group by owner
/
select count(*)
from dba_objects
where owner in ('GL','PO')
and status='INVALID'
/
7.10 Create the service name (finsdb) in RAC One Node database
After the successful completion of the data migration, we will
create a new service name, finsdb in Oracle RAC One Node database to support
the migrated applications.
oracle@gurnard1-> srvctl add service -d appsdb -s finsdb -r appsdb_1 -e select -m basic -w 5 -z 180
oracle@gurnard1-> srvctl start service -s finsdb -d appsdb
oracle@gurnard1-> srvctl status service -d appsdb
Service finsdb is running on instance(s) appsdb_1
SQL> select name,failover_method,failover_type,failover_retries,failover_delay
2 from dba_services
3 where name='finsdb'
4 /
FAILOVER FAILOVER FAILOVER FAILOVER
NAME METHOD TYPE RETRIES DELAY
------------ ---------- ---------- -------- --------
finsdb BASIC SELECT 180 5
oracle@gurnard1-> sqlplus system/Oracle#101@gurnard-cluster-scan.gurnard. vcconsult.com:1521/finsdb
SQL*Plus: Release 11.2.0.1.0 Production on Sat Mar 27 23:47:09 2010
Copyright (c) 1982, 2009, Oracle. All rights reserved.
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
SQL> select service_name,failover_type, failover_method, failed_over
2 from gv$session where username='SYSTEM'
3 /
SERVICE_NAME FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
--------------- ------------- ---------------- ------------
finsdb SELECT BASIC NO
7.11 Add the client-side TNS service name for failover
As the oracle user on gurnard1 and gurnard2, add the TNS entry
for finsdb in /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/tnsnames.ora.
FINSDB =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = gurnard-cluster-scan.gurnard.vcconsult.com)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = finsdb)
(FAILOVER_MODE =
(TYPE = SELECT)
(METHOD = BASIC)
(RETRIES = 180)
(DELAY = 5)
)
)
)
SQL> connect system@finsdb
Enter password:
Connected.
SQL> select service_name,failover_type, failover_method, failed_over
2 from gv$session where username='SYSTEM'
3 /
SERVICE_NAME FAILOVER_TYPE FAILOVER_METHOD FAILED_OVER
--------------- ------------- ---------------- ------------
finsdb SELECT BASIC NO
This is a simplistic overview of applications migration to
illustrate the use of Oracle Transportable Tablespace feature to move a subset
of an Oracle database to Oracle RAC One Node database. Proper planning and a
comprehensive and systematic testing are the keys to successful data
migration.
Congratulations, your applications are now
running on Oracle RAC One Node!
» 
IE 7
Firefox 2.0
Comment and Contribute
