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

» Database Journal Home
» Database Articles
» Database Tutorials
MS Access
SQL Scripts & Samples
» Database Forum
» Slideshows
Free Newsletters:

News Via RSS Feed

Rss Feed

Database Journal |DBA Support |SQLCourse |SQLCourse2

Featured Database Articles


Posted Oct 6, 2010

Moving to Oracle RAC One Node - Page 11

By Vincent Chan

8. Oracle Database Migration with Omotion

We will demonstrate using the Omotion utility to perform a live migration of a database from one node to an available node. The Omotion command starts and opens the target instance and then shuts down the source instance. All database sessions connected via the finsdb service name are transparently failed over to the target instance and normal transactional activities resume on the running instance. There will be a brief loss of connectivity (ORA-12514) for new connections when the finsdb service is relocated to the target instance during the cluster reconfiguration.

8.1 Connect to the database instance using the finsdb service

SQL> connect system@finsdb
Enter password:
SQL> select thread#,instance_number,instance_name,host_name,status
  2  from gv$instance
  3  /
---------- --------------- ---------------- ------------ -------
         1               1 appsdb_1         gurnard1     OPEN

8.2 Migrate the database from gurnard1 to gurnard2

oracle@gurnard1-> Omotion -v
RAC One Node databases on this cluster:
 #      Database                    Server                      Fix Required
===     ========        ==============================          ============
[1]       appsdb                              gurnard1                N
Enter number of the database to migrate [1]:
Specify maximum time in minutes for migration to complete (max 30) [30]:
appsdb Database is administrator managed .
appsdb database is running in appsdb server pool.
Current Running instance: appsdb_1
Current Active Server       : gurnard1
Available Target Server(s) :
 #            Server            Available
===     ==================      =========
[1]               gurnard2          Y
Enter number of the target node [1]:
Omotion Started...
Starting target instance on gurnard2...
Migrating sessions...
Stopping source instance on gurnard1...
Omotion Completed...
=== Current Status ===
Database appsdb is running on node gurnard2
oracle@gurnard1-> srvctl status database -d appsdb
Instance appsdb_2 is running on node gurnard2

Notice that after the migration, the appsdb_1 database instance was renamed to appsdb_2. Regardless of what the database instance was renamed to, no changes to the client configuration files or applications were necessary if the client sessions used the finsdb service name to access the database.

8.3 Verify that the session has failed over

From the same session in step 8.1, verify that the session has transparently failed over to the available target node, gurnard2.

SQL> select thread#,instance_number,instance_name,host_name,status
  2  from gv$instance
  3  /
---------- --------------- ---------------- ------------ -------
         2               2 appsdb_2         gurnard2     OPEN
SQL> select service_name,failover_type, failover_method, failed_over
  2  from gv$session where username='SYSTEM'
  3  /
--------------- ------------- --------------- ------------
finsdb          SELECT        BASIC           YES

8.4 Check Undo Tablespace and Online Redo Logs

When the appsdb_1 instance was relocated and renamed to appsdb_2 on the other node, a new set of online redo logs and an undo tablespace were created and assigned to the new instance. The undo tablespace and online redo logs associated with appsdb_1 (thread#1) will remain unused until the appsdb_2 instance is migrated back to appsdb_1.

SQL> select inst_id,thread#,bytes/1024/1024 "Size(MB)",members,status
  2  from gv$log
  3  /
------- ---------- ---------- ---------- ----------
      2          1         50          2 INACTIVE
      2          1         50          2 CURRENT
      2          1         50          2 INACTIVE
      2          2         50          2 CURRENT
      2          2         50          2 INACTIVE
      2          2         50          2 INACTIVE
6 rows selected.
SQL> select value, file_name, bytes/1024/1024 "size(MB)"
  2  from gv$parameter, dba_data_files
  3  where value=tablespace_name and value like 'UNDO%';
VALUE      FILE_NAME                                            size(MB)
---------- -------------------------------------------------- ----------
UNDOTBS2   +DATA/appsdb/datafile/undotbs2.271.715566389               95

Oracle Archives

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