Mani's Blog

May 31, 2012

Migrate Oracle RAC database into Single instance

Filed under: Uncategorized — mani @ 10:24 pm

Objective

We had a requirement to Migrate RAC database into Non-RAC single instance.

Assumptions

The RAC Cluster has 2 nodes called xnodbcontr1 and 2, instance names are XNODBCNT1,and XNODBCNT2 and the DB service name is XNODDBCNT. The goal is to remove the 2nd instance and convert the DB into Non-Cluster DB.

Steps involved

Following steps are followed to convert the RAC DB to Non-RAC.

  1. Stop the DB server and remove instance from cluster
    [oracle@xnodbcontr1 ~]$ srvctl status service -d XNODBCNT
    Service xno_contact is running on instance(s) XNODBCNT1, XNODBCNT2
    [oracle@xnodbcontr1 ~]$ srvctl stop database -d XNODBCNT
    }
    # Remove the instance
    {
    [oracle@xnodbcontr1 ~]$ srvctl remove instance -d XNODBCNT -i XNODBCNT2
    Remove instance XNODBCNT2 from the database XNODBCNT? (y/[n]) y
    You have new mail in /var/spool/mail/oracle
    [oracle@xnodbcontr1 ~]$ srvctl remove instance -d XNODBCNT -i XNODBCNT1
    Remove instance XNODBCNT1 from the database XNODBCNT? (y/[n]) y
    PRKP-1075 : Instance XNODBCNT1 is the last preferred instance for service xno_contact.
  2. Reset the cluster database value
    [oracle@xnodbcontr1 ~]$ dba
    SQL*Plus: Release 11.1.0.7.0 - Production on Thu May 31 16:57:15 2012
    Copyright (c) 1982, 2008, Oracle. All rights reserved.
    Connected to an idle instance.
    SQL> startup
    ORACLE instance started.
    Total System Global Area 5.9861E+10 bytes
    Fixed Size 2165248 bytes
    Variable Size 2.5233E+10 bytes
    Database Buffers 3.4360E+10 bytes
    Redo Buffers 266256384 bytes
    Database mounted.
    Database opened.
    SQL> alter system set cluster_database=false scope=spfile;
    System altered.
    SQL> alter system set cluster_database_instances=1 scope=spfile;
    System altered.
    SQL> alter database disable thread 2;
    Database altered.
  3. Drop unnecessary log files
    SQL> select thread#, group# from v$log;
       THREAD# GROUP#
    ---------- ----------
    1 11
    1 12
    1 13
    1 14
    1 15
    2 21
    2 22
    2 23
    2 24
    2 25
    10 rows selected.
    SQL> alter database drop logfile group 21;
    Database altered.
    SQL> alter database drop logfile group 22;
    Database altered.
    SQL> alter database drop logfile group 23;
    Database altered.
    SQL> alter database drop logfile group 24;
    Database altered.
    SQL> alter database drop logfile group 25;
    Database altered.
    SQL>
  4. Remove unnecessary UNDO tablespace.
    SQL> drop tablespace UNDOTBS2 including contents and datafiles;
    Tablespace dropped.
  5. Create pfile and remove parameters refer to 2nd instance.
    SQL> create pfile from spfile;
    File created.
    SQL> shutdown immediate;
    Database closed.
    Database dismounted.
    ORACLE instance shut down.
    SQL>
    ## Removed the reference to instance2 from pfile.
        
    SQL> create spfile from pfile;
    File created.
  6. Start up the database with new spfile.
    SQL> startup
    ORACLE instance started.
    Total System Global Area 5.9861E+10 bytes
    Fixed Size 2165248 bytes
    Variable Size 2.7917E+10 bytes
    Database Buffers 3.1675E+10 bytes
    Redo Buffers 266256384 bytes
    Database mounted.
    Database opened.
  7. Verified the transaction is flowing in.
    SQL> select max(id) from xn_contacts.profile2;
       MAX(ID)
    ----------
     180701340
    SQL> /
       MAX(ID)
    ----------
     180701413
    SQL>
Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: