Archive

Posts Tagged ‘non-asm to asm’

Convert non-asm standalone database to RAC with ASM – 11gR2

August 21, 2013 Leave a comment

In this post we will see how to convert a standalone database to a RAC database with ASM.

The Setup

  • 11gR2 GI has been installed and configured.
  • 2 Oracle homes have been installed, one is RAC aware and the other is for standalone database. A standalone database is also created.
  • Oracle home & Grid homes have been installed 2 seperate users oracle & grid respectively

Overview of Non-RAC environment

Hostname Database Name Instance Name Database Storage
rac1 racdb racdb ext3

Overview of RAC environment

Hostname Database Name Instance Name Database Storage
rac1 racdb1 racdb1 ASM
rac2 racdb2 racdb2 ASM

The Process

1. Create 2 diskgroups DATA & FRA

 SQL> create diskgroup DATA external redundancy disk ‘/dev/asm-disk2’;
 Diskgroup created.

 SQL> create diskgroup FRA external redundancy disk ‘/dev/asm-disk3’;
 Diskgroup created.

 2. Set the following parameters related to OMF & FRA to a shared location

SQL> alter system set db_recovery_file_dest_size=1G scope=both;
System altered.

SQL> alter system set db_recovery_file_dest=’+FRA’ scope=both;
System altered.

SQL> alter system set db_create_file_dest=’+DATA’ scope=both;
System altered.

3.  Create redo and undo for second instance

 Each individual instance requires an independent set of redo and undo segments as redo and undo are handled on a per instance basis

Drop log groups 1,2,3 & recreate log groups 1,2 under thread 1 & 3,4 under thread 2.

SQL> alter database drop logfile group 1;
Database altered.

SQL> alter database add logfile thread 1 group 1 (‘+DATA’) size 10M;
Database altered.

SQL> alter database drop logfile group 2;
Database altered.

SQL> alter database add logfile thread 1 group 2 (‘+DATA’) size 10M;
Database altered.

SQL> alter database drop logfile group 3;
Database altered.

SQL> alter database add logfile thread 2 group 3(‘+DATA’) size 10M;
Database altered.

SQL>  alter database add logfile thread 2 group 4 (‘+DATA’) size 10M;
Database altered.

3. Enable thread 2

SQL> alter database enable thread 2;
Database altered.

4.  Create new undo tablespace for the second instance

SQL> create undo tablespace UNDOTBS2 datafile ‘+DATA’ size 5M;
Tablespace created.

5.  Moving control files to ASM now

a) SQL> shut immediate

b) As grid home user

[grid@rac1 ~]$ asmcmd
ASMCMD> cp /u02/oradata/racdb/control01.ctl +DATA/RACDB/DATAFILE/
copying /u02/oradata/racdb/control01.ctl -> +DATA/RACDB/DATAFILE/control01.ctl

ASMCMD> cp /u02/oradata/racdb/control02.ctl +DATA/RACDB/DATAFILE/
copying /u02/oradata/racdb/control02.ctl -> +DATA/RACDB/DATAFILE/control02.ctl

c) AS oracle user

[oracle@rac1 ~]$ sqlplus / as sysdba
SQL> startup nomount
SQL> alter system set control_files=’+DATA/RACDB/DATAFILE/control01.ctl’,’+DATA/RACDB/DATAFILE/control02.ctl’ scope=spfile;
System altered.
SQL> shut immediate
SQL> startup

6. Moving database files to ASM. Take a image copy backup to shared location using RMAN

SQL> shut immediate

[oracle@rac1 ~]$ rman target /
RMAN> startup mount
RMAN> backup as copy database format ‘+DATA’;

Starting backup at 19-AUG-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=8 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00001 name=/u02/oradata/racdb/system01.dbf
output file name=+DATA/racdb/datafile/system.260.823906197 tag=TAG20130819T224955 RECID=1 STAMP=823906213
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile copy
input datafile file number=00003 name=/u02/oradata/racdb/undotbs1.dbf
output file name=+DATA/racdb/datafile/undotbs1.264.823906223 tag=TAG20130819T224955 RECID=2 STAMP=823906227
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00002 name=/u02/oradata/racdb/sysaux01.dbf
output file name=+DATA/racdb/datafile/sysaux.265.823906229 tag=TAG20130819T224955 RECID=3 STAMP=823906233
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
copying current control file
output file name=+DATA/racdb/controlfile/backup.266.823906237 tag=TAG20130819T224955 RECID=4 STAMP=823906241
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile copy
input datafile file number=00004 name=/u02/oradata/racdb/users01.dbf
output file name=+DATA/racdb/datafile/users.267.823906245 tag=TAG20130819T224955 RECID=5 STAMP=823906245
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting datafile copy
input datafile file number=00005 name=+DATA/racdb/datafile/undotbs2.258.823905699
output file name=+DATA/racdb/datafile/undotbs2.268.823906247 tag=TAG20130819T224955 RECID=6 STAMP=823906246
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current SPFILE in backup set
channel ORA_DISK_1: starting piece 1 at 19-AUG-13
channel ORA_DISK_1: finished piece 1 at 19-AUG-13
piece handle=+DATA/racdb/backupset/2013_08_19/nnsnf0_tag20130819t224955_0.269.823906247 tag=TAG20130819T224955 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-AUG-13

RMAN> switch database to copy;

datafile 1 switched to datafile copy “+DATA/racdb/datafile/system.260.823906197”
datafile 2 switched to datafile copy “+DATA/racdb/datafile/sysaux.265.823906229”
datafile 3 switched to datafile copy “+DATA/racdb/datafile/undotbs1.264.823906223”
datafile 4 switched to datafile copy “+DATA/racdb/datafile/users.267.823906245”
datafile 5 switched to datafile copy “+DATA/racdb/datafile/undotbs2.268.823906247”

RMAN> alter database open;
database opened

7.  Create temporary tablespace

SQL> create temporary tablespace temp01 tempfile ‘+DATA’;
Tablespace created.

SQL> alter database default temporary tablespace temp01;
Database altered.

SQL> drop tablespace temp including contents and datafiles;
Tablespace dropped.

8.Set the required cluster parameters

SQL> alter system set cluster_database=true scope=spfile;
System altered.

SQL>  alter system set cluster_database_instances=2 scope=spfile;
System altered.

SQL> alter system set thread=1 scope=spfile sid=’racdb1′;
System altered.

SQL>  alter system set thread=2 scope=spfile sid=’racdb2′;
System altered.

SQL> alter system set undo_tablespace=UNDOTBS1 scope=spfile sid=’racdb1′;
System altered.

SQL>  alter system set undo_tablespace=UNDOTBS2 scope=spfile sid=’racdb2′;
System altered.

SQL> alter system set instance_number=1 scope=spfile sid=’racdb1′;
System altered.

SQL> alter system set instance_number=2 scope=spfile sid=’racdb2′;
System altered.

SQL> alter system set remote_listener=’rac-scan.localdomain:1521′ scope=spfile sid=’racdb1′;
System altered.

SQL> alter system set remote_listener=’rac-scan.localdomain:1521′ scope=spfile sid=’racdb2′;
System altered.

9. Create spfile for each node

on Node1:

SQL> create pfile=’/u01/app/oracle/product/11.2.0/db_3/dbs/initracdb1.ora’ from spfile;
File created.

SQL> shut immediate

Set your new environment

Set your ORACLE_HOME to the home which is installed with RAC feature

[oracle@rac1 admin]$ export ORACLE_SID=racdb1

[oracle@rac1 admin]$ sqlplus / as sysdba
SQL> startup pfile=’/u01/app/oracle/product/11.2.0/db_3/dbs/initracdb1.ora’;

SQL> create spfile=’+DATA’ from pfile;
File created.

SQL> shut immediate

[oracle@rac1 dbs]$ echo “spfile=’+data/racdb/PARAMETERFILE/spfile.271.823907679′” > $ORACLE_HOME/dbs/initracdb1.ora

SQL> startup

On Node2:

Set your new environment

Set ORACLE_SID=racdb2 and ORACLE_HOME pointing to the home with RAC feature

echo “spfile=’+data/racdb/PARAMETERFILE/spfile.271.823907679′” > $ORACLE_HOME/dbs/initracdb2.ora

SQL> startup

10. Create the dictionary views needed for RAC

@?/rdbms/admin/catclust.sql

11.Register the instances with crs

As ORACLE_HOME owner,

[oracle@rac1 dbs]$ srvctl add database -d racdb -o /u01/app/oracle/product/11.2.0/db_3 -y AUTOMATIC
[oracle@rac1 dbs]$ srvctl add instance -d racdb -i racdb1 -n rac1
[oracle@rac1 dbs]$ srvctl add instance -d racdb -i racdb2 -n rac2

12. Start and stop the database with srvctl to check the configuration is valid or not

[grid@rac1 ~]$ srvctl start database -d racdb
[grid@rac1 ~]$ srvctl stop database -d racdb

13. Use the following command to check the status of all instances converted RAC database

SQL> select * from v$active_instances;

INST_NUMBER INST_NAME
———– ——————————
1 rac1.localdomain:racdb1
2 rac2.localdomain:racdb2

14. Make relevant db entries in /etc/oratab on both nodes

15. Setting up the listener

a. Create a new listener

[grid@rac1 ~]$ srvctl add listener -l LISTENER_RACDB -p TCP:1522
[grid@rac1 ~]$ srvctl start listener -l LISTENER_RACDB

b. Set local_listener parameter in database (because listener isn’t on default port)

sql> alter system set LOCAL_LISTENER=LISTENER_RACDB scope=both sid=’*’;
System altered.

c. Resolve this local_listener in the tnsnames.ora on both nodes

on Node 1:
LISTENER_RACDB=(ADDRESS = (PROTOCOL=TCP)(HOST=10.0.0.31)(PORT=1522))

on Node 2:
LISTENER_RACDB=(ADDRESS = (PROTOCOL=TCP)(HOST=10.0.0.32)(PORT=1522))

 

This completes the conversion process 🙂

Advertisements
Oracle database internals by Riyaj

Discussions about Oracle performance tuning, RAC, Oracle internal & E-business suite.

StartupForce

Where all the Action Is !!!!

Pavan DBA's Blog

The DBA Knowledge Store

ORACLE-BASE - Latest Articles

Where all the Action Is !!!!