Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Slideshows
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

Oracle

Posted Oct 6, 2010

Moving to Oracle RAC One Node - Page 10

By Vincent Chan

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!



Oracle Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
Oracle Forum
Topic By Replies Updated
Oracle Data Mining: Classification jan.hasller 0 July 5th, 07:19 AM
Find duplicates - Unique IDs Lava 5 July 2nd, 08:30 AM
no matching unique or primary key rcanter 1 April 25th, 12:32 PM
Update values of one table based on condition of values in other table using Trigger Gladiator 3 February 29th, 06:01 PM