Convert Single instance to rac to a different server

Convert Single Instance (ASM) to RAC Instance (ASM) to different Server

Assuming you have already the following environment setup and ready

    1. 11gr2 Clusterware is already installed on your target machine (XYZ)
    2. Target xyz is configured with +DATA as diskgroup
    2. Source ABC is having single instance in ASM with DATA as the disk group

##################################################
Step 1:- Take the backup in source server using RMAN
##################################################

rman> backup full database plus archivelog device type disk format '/backups/fullbackup_%t_%s_%d';

rman> backup controlfile device type disk format '/backups/controlfilebackup';

Create an pfile from spfile

##################################################
Step 2: copy the backups
scp the backup to target location
cp the pfile to target server dbs directory
cp the password file to target server dbs directory
##################################################

##################################################
Step 3: startup no mount in the target server
##################################################
with the pfile copied, set your environment as like source  note: you will need to modify /check the following params

Control_files=<to the disk it exists in the target server>
diag_dest=<to appropriate directory>
db_file_create_dest <to the diskgroup that exists in this server>
db_recovery_file_dest=<to the diskgroyp fra existing this server>

export ORACLE_SID=<sourcedbname>

sqlplus / as sysdba
startup nomount

##################################################
step 4: restore controlfile from backup
##################################################
rman> restore controlfile from '/location of the controlfile backup you have copied'

this will restore the controlfile from backup and mount the database if does so

rman> sql 'alter database mount';

##################################################
step 4: restore the database
##################################################

If the backup location is different from the existing server then you have to catalog backups,for example

primary location:/backups

target location: /u02/backups

then

rman> catalog start with '/u02/backups/';

once catalogged

rman> restore database;

-this will create the database files restore from the backup once done recover the database

rman> recover database;

-this will recover the database and you may need some archives and it will be done by restoring backups

- once this steps over, open the database either by using reset logs

sqlplus> alter database open resetlogs;

SQL> Shutdown immediate

##################################################
step 5: Preparing the database for rac (target database)
##################################################

SQL> Startup mount

a) Create redo threads

alter database add logfile thread 2
group 4 ('+DATA') size 50M,
group 5 ('+DATA') size 50M,
group 6 ('+DATA') size 50M;

b) SQL> Alter database open ;

c) SQL> alter database enable public thread 2;

d) Create multiple undo tablespaces

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '+DATA' size 25M;

e) change the following database parameters

*.cluster_database = TRUE
*.cluster_database_instances = 2
*.undo_management=AUTO
prod1.undo_tablespace=UNDOTBS1
prod1.instance_name=prod1
prod1.instance_number=1
prod1.thread=1
prod2.instance_name=prod2
prod2.instance_number=2
prod2.thread=2
prod2.undo_tablespace=UNDOTBS2

prod2.local_listener=listener_rac1
prod1.local_listener=listener_rac2

e) shutdown and startup

SQL> shut immediate

SQL> startup

SQL> ?/rdbms/admin/catclust.sql

##################################################
Step 6: Prepare node 2
##################################################

a)
bash-3.00$ export ORACLE_HOME=/u01/app/oracle/product/10.2.0/db
bash-3.00$ export ORACLE_SID=prod2

Create initprod2.ora on second node similar to node 1. I

In this case you have to copy spfile to second node as well. You can also keep spfile in shared location (/u03 in my case) and put same path in initprod2.ora

cd /u01/app/oracle/product/10.2.0/db/dbs

bash-3.00$ ls -lrt spfileprod.ora

-rw-r-----  1 oracle oinstall 3584 Feb 19 12:36 spfileprod.ora

bash-3.00$ cat initprod2.ora

spfile='/u01/app/oracle/product/10.2.0/db/dbs/spfileprod.ora'

b) Create new password file for instance 2

bash-3.00$ orapwd file=orapwprod2 password=welcome1

c) Start the second instance

SQL> startup pfile=initprod2.ora

SQL> startup pfile=initprod2.ora
ORACLE instance started.
Total System Global Area  838860800 bytes
Fixed Size                  1222168 bytes
Variable Size             213912040 bytes
Database Buffers          620756992 bytes
Redo Buffers                2969600 bytes
Database mounted.
Database opened.

##################################################
Step 6: Finally create the spfile from pfile to asm disk
##################################################
SQL> create spfile='+DATA' from pfile='/tmp/pfile.ora'

##################################################
Step 7: add the database to cluster
##################################################

bash-3.00$ srvctl add database -d prod -o <oraclehome> -p <spfile location>

bash-3.00$ srvctl add instance -d prod -i prod1 -n <hostname1>

bash-3.00$ srvctl add instance -d prod -i prod2 -n <hostname2>

-Thanks

Geek DBAGeek DBA