Oracle RAC to Single Instance Migration with ASM

Oracle RAC to Single Instance Migration with ASM: The Complete Enterprise DBA Guide

Transform your Oracle infrastructure: Reduce licensing costs by up to 60% while maintaining high-performance ASM storage. The most comprehensive, copy-paste ready guide trusted by enterprise DBAs worldwide.

Table of Contents

Executive Summary & Business Case

Oracle Real Application Clusters (RAC) delivers unmatched high availability and scalability, but many organizations discover their workloads no longer justify the complexity and substantial licensing costs. Converting to a single-instance database while retaining Automatic Storage Management (ASM) provides the optimal balance: simplified operations with preserved storage performance.

Financial Impact Analysis:

The cost savings from RAC conversion can be substantial. Oracle RAC licensing follows this formula:

Annual RAC Cost = Database License \times RAC Option License \times Number of Nodes \times 1.22 (Support)

Potential Annual Savings = (Node Count - 1) \times Per Node License Cost \times 1.22

For a typical two-node RAC environment, organizations can achieve 40-60% reduction in Oracle licensing costs while maintaining storage performance and reliability through ASM.

What This Migration Achieves:

Massive cost reduction: Eliminate RAC licensing on removed nodes
Operational simplification: Remove cluster interconnects, rolling upgrades, and multi-instance complexity
Preserved performance: Keep ASM's intelligent storage management, striping, and mirroring
Easier maintenance: Simplified backup, patching, and troubleshooting procedures

Architecture Transformation Overview

Current RAC Architecture

┌─────────────────────────────────────────────────────────────────┐
│                    ORACLE RAC ENVIRONMENT                       │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  ┌─────────────────┐    ┌─────────────────┐                     │
│  │     NODE 1      │    │     NODE 2      │                     │
│  │  ┌───────────┐  │    │  ┌───────────┐  │                     │
│  │  │ PRODDB1   │  │    │  │ PRODDB2   │  │                     │
│  │  │ Thread 1  │  │◄──►│  │ Thread 2  │  │                     │
│  │  │ UNDOTBS1  │  │    │  │ UNDOTBS2  │  │                     │
│  │  └───────────┘  │    │  └───────────┘  │                     │
│  │ Grid Infrastructure│    │ Grid Infrastructure│               │
│  └─────────┬───────┘    └─────────┬───────┘                     │
│            │                       │                            │
│            └───────────┬───────────┘                            │
│                        │                                        │
│         ┌──────────────▼──────────────┐                         │
│         │        ASM DISK GROUPS      │                         │
│         │  ┌──────────┐ ┌───────────┐ │                         │
│         │  │  +DATA   │ │   +FRA    │ │                         │
│         │  │ Datafiles│ │ Archivelogs│ │                        │
│         │  │Controlfiles│ │ Backups   │ │                       │
│         │  │ Redo Logs│ │ Flashback │ │                         │
│         │  └──────────┘ └───────────┘ │                         │
│         └─────────────────────────────┘                         │
└─────────────────────────────────────────────────────────────────┘

Target Single Instance Architecture

┌─────────────────────────────────────────────────────────────────┐
│              SINGLE INSTANCE + ASM ENVIRONMENT                  │
├─────────────────────────────────────────────────────────────────┤
│                                                                 │
│  ┌─────────────────────────────────────────────────────────────┐│
│  │                    NODE 1 ONLY                              ││
│  │  ┌─────────────────────────────────────────────────────┐    ││
│  │  │         PRODDB (Single Instance)                    │    ││
│  │  │         Thread 1 Only                               │    ││
│  │  │         UNDOTBS1 Only                               │    ││
│  │  │         cluster_database = FALSE                    │    ││
│  │  └─────────────────────────────────────────────────────┘    ││
│  │  Grid Infrastructure (ASM Support)                          ││
│  │  Local Listener Only                                        ││
│  └─────────────────────┬───────────────────────────────────────┘│
│                        │                                        │
│         ┌──────────────▼──────────────┐                         │
│         │   SAME ASM DISK GROUPS      │                         │
│         │  ┌──────────┐ ┌───────────┐ │                         │
│         │  │  +DATA   │ │   +FRA    │ │                         │
│         │  │SAME FILES│ │SAME FILES │ │                         │
│         │  │SAME PERF │ │SAME PERF  │ │                         │
│         │  └──────────┘ └───────────┘ │                         │
│         └─────────────────────────────┘                         │
└─────────────────────────────────────────────────────────────────┘

Key Insight: The storage layer remains completely unchanged. Only the database instance logic and cluster registration are modified.

Prerequisites & Risk Assessment

Technical Requirements

Infrastructure Prerequisites:

Oracle RAC database currently running on ASM storage
Grid Infrastructure operational on target surviving node
Target node has sufficient CPU/RAM for entire workload
SYSDBA and SYSASM privileges available
Minimum 4-hour maintenance window approved

Safety Requirements:

Complete RMAN backup verified and tested for restoration
SPFILE backup saved to operating system filesystem
Non-production environment testing completed successfully
Documented rollback procedure tested and approved
Application connection strings prepared for update

Pre-Migration Validation Commands

# Verify cluster health
crsctl check crs
olsnodes -n -i -s

# Check database and ASM status
srvctl status database -d PRODDB -v
srvctl status asm -a

# Verify ASM disk group health
export ORACLE_SID=+ASM1
sqlplus / as sysasm
SELECT name, state, total_mb, free_mb FROM v$asm_diskgroup;

Phase 1: Backup & Documentation

Step 1.1: Execute Comprehensive RMAN Backup

This is the most critical step. Never proceed without a complete, verified backup.

rman target /

-- Configure backup optimization
CONFIGURE RETENTION POLICY TO REDUNDANCY 2;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE COMPRESSION ALGORITHM 'MEDIUM';

-- Execute full backup
RUN {
  ALLOCATE CHANNEL ch1 DEVICE TYPE DISK FORMAT '+FRA/%d/backup/%U';
  ALLOCATE CHANNEL ch2 DEVICE TYPE DISK FORMAT '+FRA/%d/backup/%U';
  BACKUP AS COMPRESSED BACKUPSET DATABASE PLUS ARCHIVELOG;
  BACKUP CURRENT CONTROLFILE;
  BACKUP SPFILE;
  RELEASE CHANNEL ch1;
  RELEASE CHANNEL ch2;
}

-- Verify backup completeness
LIST BACKUP SUMMARY;
VALIDATE BACKUPSET ALL;

Step 1.2: Document Current Configuration

sqlplus / as sysdba

-- Create parameter file backup
CREATE PFILE='/tmp/init_rac_backup_$(date +%Y%m%d).ora' FROM SPFILE;

-- Document RAC-specific parameters
SELECT name, value, isdefault FROM v$parameter 
WHERE name IN (
  'cluster_database','cluster_database_instances','instance_number',
  'thread','undo_tablespace','instance_name','remote_listener','local_listener'
) ORDER BY name;

-- Document redo log configuration
SELECT l.thread#, l.group#, l.status, l.members, 
       ROUND(l.bytes/1024/1024, 2) AS size_mb, lf.member
FROM v$log l JOIN v$logfile lf ON l.group# = lf.group#
ORDER BY l.thread#, l.group#;

-- Document undo tablespaces
SELECT tablespace_name, status FROM dba_tablespaces WHERE contents = 'UNDO';

Phase 2: Parameter File Transformation {#phase-2}

Step 2.1: Create Working Parameter File

-- Create editable PFILE for single instance conversion
CREATE PFILE='/tmp/init_single_instance.ora' FROM SPFILE;

Step 2.2: Critical Parameter Modifications

Open /tmp/init_single_instance.ora and apply these essential changes:

Before (RAC Configuration):

# RAC CONFIGURATION - TO BE MODIFIED
*.cluster_database=TRUE
*.cluster_database_instances=2
PRODDB1.instance_number=1
PRODDB2.instance_number=2
PRODDB1.thread=1
PRODDB2.thread=2
PRODDB1.undo_tablespace='UNDOTBS1'
PRODDB2.undo_tablespace='UNDOTBS2'
*.remote_listener='PRODDB-SCAN:1521'
PRODDB1.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=node1-vip)(PORT=1521))'
PRODDB2.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=node2-vip)(PORT=1521))'

After (Single Instance Configuration):

# SINGLE INSTANCE CONFIGURATION
# CRITICAL: Disable cluster mode
*.cluster_database=FALSE

# Remove cluster_database_instances parameter entirely

# Consolidate to single instance parameters
*.instance_number=1
*.thread=1
*.undo_tablespace='UNDOTBS1'

# Clear network parameters (will reconfigure later)
*.remote_listener=''
*.local_listener=''

# IMPORTANT: Keep all ASM paths unchanged
*.control_files='+DATA/PRODDB/controlfile/current.XXX.XXXXXXX'
*.db_create_file_dest='+DATA'
*.db_recovery_file_dest='+FRA'

Parameter Change Summary:

Parameter	RAC Value	Single Instance Value	Action Required
`cluster_database`	TRUE	FALSE	CRITICAL CHANGE
`cluster_database_instances`	2	N/A	REMOVE ENTIRELY
Instance-specific params	Per instance	Global	Consolidate
Network listeners	SCAN/VIP	Clear	Will reconfigure
ASM storage paths	Unchanged	Unchanged	NO CHANGES

Phase 3: Database Conversion {#phase-3}

Step 3.1: Graceful RAC Shutdown

# Stop all database services first
srvctl stop service -d PRODDB

# Stop the entire RAC database
srvctl stop database -d PRODDB

# Verify complete shutdown
srvctl status database -d PRODDB
ps -ef | grep pmon | grep PRODDB

Step 3.2: Set Environment on Target Node

# On the surviving node (node1)
export ORACLE_SID=PRODDB
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
export ORACLE_BASE=/u01/app/oracle
export PATH=$ORACLE_HOME/bin:$PATH

Step 3.3: Start as Single Instance

sqlplus / as sysdba

-- Start in MOUNT mode for verification
STARTUP MOUNT PFILE='/tmp/init_single_instance.ora';

-- CRITICAL VERIFICATION: Confirm cluster_database is FALSE
SHOW PARAMETER cluster_database;
-- Expected output: cluster_database = FALSE

-- Verify instance information
SELECT instance_name, host_name, parallel, status FROM v$instance;
-- 'parallel' should show 'NO' for single instance

-- Open the database
ALTER DATABASE OPEN;

-- Confirm database is open and operational
SELECT name, open_mode, log_mode FROM v$database;

Phase 4: RAC Artifact Cleanup

Step 4.1: Remove Extra Redo Threads

-- Check current thread configuration
SELECT thread#, status, enabled FROM v$thread;

-- Disable thread 2 (from removed instance)
ALTER DATABASE DISABLE THREAD 2;

-- Identify log groups for thread 2
SELECT group#, thread#, status FROM v$log WHERE thread# = 2;

-- Handle active/current logs if needed
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;

-- Drop thread 2 log groups (adjust group numbers as needed)
ALTER DATABASE DROP LOGFILE GROUP 3;
ALTER DATABASE DROP LOGFILE GROUP 4;

-- Verify only thread 1 remains
SELECT thread#, status, enabled FROM v$thread;
SELECT group#, thread#, status FROM v$log ORDER BY thread#, group#;

Step 4.2: Remove Extra Undo Tablespace

-- Verify current undo tablespace
SHOW PARAMETER undo_tablespace;

-- Check undo tablespace usage
SELECT tablespace_name, status FROM dba_tablespaces WHERE contents = 'UNDO';

-- Drop the extra undo tablespace
DROP TABLESPACE UNDOTBS2 INCLUDING CONTENTS AND DATAFILES;

-- Confirm cleanup
SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'UNDO';

Phase 5: SPFILE Creation in ASM

Step 5.1: Create SPFILE in ASM Storage

-- Create SPFILE in ASM
CREATE SPFILE='+DATA/PRODDB/spfilePRODDB.ora' 
FROM PFILE='/tmp/init_single_instance.ora';

-- Shutdown to test SPFILE startup
SHUTDOWN IMMEDIATE;

Step 5.2: Create Pointer File

# Create init file pointing to ASM SPFILE
echo "SPFILE='+DATA/PRODDB/spfilePRODDB.ora'" > $ORACLE_HOME/dbs/initPRODDB.ora

# Verify pointer file
cat $ORACLE_HOME/dbs/initPRODDB.ora

Step 5.3: Restart Using ASM SPFILE

sqlplus / as sysdba

-- Restart using ASM-resident SPFILE
STARTUP;

-- Verify SPFILE location
SHOW PARAMETER spfile;
-- Should show: +DATA/PRODDB/spfilePRODDB.ora

Phase 6: Clusterware Re-Registration

Step 6.1: Remove Old RAC Configuration

# Remove RAC database from cluster registry
srvctl remove database -d PRODDB -f

# Verify removal
srvctl config database -d PRODDB  # Should show "not found"

Step 6.2: Register as Single Instance

# Add database as single instance
srvctl add database \
  -d PRODDB \
  -o $ORACLE_HOME \
  -p '+DATA/PRODDB/spfilePRODDB.ora' \
  -r PRIMARY \
  -s OPEN \
  -t IMMEDIATE \
  -n node1 \
  -a "+DATA,+FRA"

# Add the single instance
srvctl add instance \
  -d PRODDB \
  -i PRODDB \
  -n node1

# Verify new configuration
srvctl config database -d PRODDB

Phase 7: Network & Service Configuration

Step 7.1: Create Database Services

sqlplus / as sysdba

-- Create application service
EXEC DBMS_SERVICE.CREATE_SERVICE(
  service_name => 'PRODDB_SERVICE',
  network_name => 'PRODDB_SERVICE'
);

-- Start the service
EXEC DBMS_SERVICE.START_SERVICE('PRODDB_SERVICE');

Step 7.2: Configure Listener

-- Set local listener
ALTER SYSTEM SET 
  local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=node1.domain.com)(PORT=1521))' 
  SCOPE=BOTH;

-- Force registration
ALTER SYSTEM REGISTER;

Step 7.3: Update Client Connection Strings

# tnsnames.ora entry
PRODDB =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = node1.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = PRODDB_SERVICE.domain.com)
    )
  )

Phase 8: Comprehensive Validation

Step 8.1: Database Instance Validation

-- Verify single instance configuration
SELECT instance_name, host_name, parallel, status FROM v$instance;
SHOW PARAMETER cluster_database;  -- Must be FALSE

-- Check database health
SELECT name, open_mode, log_mode FROM v$database;

-- Verify thread and undo configuration
SELECT thread#, status, enabled FROM v$thread;  -- Only one enabled thread
SELECT tablespace_name FROM dba_tablespaces WHERE contents = 'UNDO';  -- Only one undo

Step 8.2: ASM Storage Validation

export ORACLE_SID=+ASM1
sqlplus / as sysasm

-- Verify disk group health
SELECT name, state, type, total_mb, free_mb FROM v$asm_diskgroup ORDER BY name;

-- Verify database files accessibility
SELECT name FROM v$datafile;
SELECT name FROM v$controlfile;
SELECT member FROM v$logfile;

Step 8.3: Connectivity Testing

# Test srvctl management
srvctl stop database -d PRODDB
srvctl start database -d PRODDB
srvctl status database -d PRODDB

# Test client connectivity
sqlplus username/password@PRODDB

Phase 9: Performance Optimization

Step 9.1: Memory Optimization

With all resources dedicated to a single instance, optimize memory allocation using these formulas:

Memory Sizing Calculations:

Recommended SGA = Total Server RAM \times 0.60

Recommended PGA = Total Server RAM \times 0.20

Total Oracle Memory = SGA + PGA \leq Total Server RAM \times 0.80

Example for 64GB server:

SGA = 64 \times 0.60 = 38.4 GB

PGA = 64 \times 0.20 = 12.8 GB

-- Apply optimized memory settings
ALTER SYSTEM SET sga_target=38G SCOPE=SPFILE;
ALTER SYSTEM SET pga_aggregate_target=12G SCOPE=SPFILE;

-- Optimize parallel settings
ALTER SYSTEM SET parallel_max_servers=32 SCOPE=SPFILE;

-- Apply changes
SHUTDOWN IMMEDIATE;
STARTUP;

Phase 10: Final Documentation {#phase-10}

Step 10.1: Post-Conversion Backup

rman target /

-- Take fresh backup of converted database
BACKUP DATABASE PLUS ARCHIVELOG DELETE INPUT;
BACKUP CURRENT CONTROLFILE;

Step 10.2: Update System Configuration

# Update /etc/oratab
echo "PRODDB:$ORACLE_HOME:Y" >> /etc/oratab

# Create management scripts
cat > /home/oracle/scripts/start_proddb.sh << 'EOF'
#!/bin/bash
export ORACLE_SID=PRODDB
export ORACLE_HOME=/u01/app/oracle/product/19.0.0/dbhome_1
srvctl start database -d PRODDB
EOF

chmod +x /home/oracle/scripts/start_proddb.sh

Advanced Troubleshooting Guide

Issue 1: Database Startup Failures

Problem: Database fails to start after parameter changes

# Diagnose with explicit PFILE
sqlplus / as sysdba
STARTUP PFILE='/tmp/init_single_instance.ora';
# Check alert log: $ORACLE_BASE/diag/rdbms/proddb/PRODDB/trace/alert_PRODDB.log

Issue 2: Cannot Drop Redo Log Groups

Problem: ORA-01623/ORA-01624 errors when dropping log groups

-- Force log switches and checkpoints
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM SWITCH LOGFILE;
ALTER SYSTEM CHECKPOINT;
-- Wait for archival, then retry drop

Issue 3: ASM Connectivity Issues

Problem: Cannot access ASM disk groups

# Check Grid Infrastructure status
crsctl check crs
srvctl start asm -n node1

# Mount disk groups if needed
export ORACLE_SID=+ASM1
sqlplus / as sysasm
ALTER DISKGROUP DATA MOUNT;

Issue 4: Service Registration Problems

Problem: Services not registering with listener

ALTER SYSTEM SET local_listener=
  '(ADDRESS=(PROTOCOL=TCP)(HOST=node1.domain.com)(PORT=1521))' 
  SCOPE=BOTH;
ALTER SYSTEM REGISTER;

Enterprise Migration Checklist

Pre-Migration (Complete All Items)

Full RMAN backup completed and restoration tested
SPFILE/PFILE backup saved to OS filesystem
Complete RAC configuration documented
Non-production testing completed successfully
Maintenance window approved and communicated
Application teams notified and prepared
Rollback procedure documented and tested

During Migration (Execute in Order)

Post-Migration (Verify All Items)

Database operates correctly as single instance
ASM storage fully accessible and healthy
Application connectivity verified
Memory parameters optimized
Final backup completed
Documentation updated
Monitoring systems updated
Success communicated to stakeholders

Frequently Asked Questions

Q: Can I convert back to RAC if needed? A: Yes, but treat this as a one-way migration. Keep your pre-conversion RMAN backup and PFILE. To revert, restore the backup, re-enable redo threads, recreate undo tablespaces, and re-register as RAC.

Q: Do I need to move any datafiles? A: No. All datafiles, controlfiles, and redo logs remain in ASM exactly where they are. This is a logical conversion, not a physical data migration.

Q: What happens to Grid Infrastructure? A: Grid Infrastructure remains installed and running to support ASM. You're removing RAC database management while keeping ASM storage management.

Q: How long does the conversion take? A: Typically 30-60 minutes for the actual conversion, plus 1-2 hours for validation and testing. Plan for a 4-hour maintenance window.

Q: Does this affect Data Guard configurations? A: Yes. You'll need to update Data Guard configurations post-conversion to reflect the single-instance primary database.

Conclusion

Converting from Oracle RAC to a single-instance database while retaining ASM delivers the optimal balance of operational simplicity and storage performance. This migration represents one of the highest-impact infrastructure optimizations available, delivering immediate cost savings and operational benefits while preserving data integrity and storage efficiency.

Key Success Factors:

The critical parameter change is cluster_database=FALSE
ASM storage remains completely intact throughout the process
Thorough validation ensures successful conversion
Post-conversion optimization maximizes performance benefits

Business Impact:

Reduced Oracle licensing costs (potentially 40-60% savings)
Simplified database operations and maintenance
Maintained storage performance and reliability
Easier disaster recovery and backup procedures

By following this comprehensive guide, you achieve a simpler, more cost-effective Oracle database environment without sacrificing storage performance or data integrity.

Please do like and subscribe to my youtube channel: https://www.youtube.com/@foalabs If you like this post please follow,share and comment

Oracle RAC to Single Instance Migration with ASM