RAC Database Creation

The database creation is done in 5 phases

Phase 1: Create Diskgroups for database

Phase 2: Create the Parameter File for Creation of spfile

Phase 3: Execute Create Database and Post Creation Scripts on Node 1

Phase 4: Enable Second Node

Phase 5: Register Database in OCR

Phase 1: Create Diskgroups for Database

Step 1: I am creating two ASM Disks DATA and RECO using partitioned disks /dev/sdc1 and /dev/sdd1. For information on how setup storage and partion a disk, refer to RAC Storage Setup

As root user execute the below commands

oracleasm createdisk DATA /dev/sdc1

oracleasm createdisk RECO /dev/sdd1

Discover the newly created disks on node2

As root user, on node 2

oracleasm scandisks

Step 2: Now we will create Disk Groups using ASM Configuration Assistant.

As the grid owner set the grid environment, execute the command asmca. To set the grid environment type below commands on Node 1

. oraenv

When prompted, set the SID to +ASM1 if not already set by default.

Run asmca in an X Terminal where Graphical User Interface is displayed

For the first time we will see only one disk group that was crated for storing OCR and Voting disk information. Click on Create button at the bottom left, to create new disk groups for storing database files and recovery related files.

Enter the desired diskgroup name, example DATA, I have selected the redundancy to External which means we are not using ASM mirroing features, to better understand ASM Srtiping and Mirroring concepts refer to "About Mirroring and Failure Groups"

Select the Disk to be assigned to this disk group. Note that the disk name you created is appended with a prefix ORCL:

Click on ok to create disk group

Similarly Create the Diskgroup RECO

Step 3: Create directory structure

Now, create a directory in disk group to store spfile and database files

As grid owner, set the grid environment and connect to asm instance as sysasm ( sqlplus / as sysasm)

SQL> alter diskgroup DATA add directory '+DATA/RACDB';

Phase 2: Create the pfile for creation of spfile

Login as oracle database owner and create an init file in any directory

cd $HOME

vi initRACDB.ora

db_name=RACDB

db_domain=charan.com

memory_target=1G

processes = 150

db_block_size=8192

diagnostic_dest=/u01/oracle/orabase

open_cursors=300

remote_login_passwordfile=EXCLUSIVE

control_files = +DATA/RACDB/control01.ctl

compatible =11.2.0

cluster_database=false

cluster_database_instances=7

global_names=true

racdb1.instance_number=1

racdb2.instance_number=2

racdb1.thread=1

racdb2.thread=2

racdb1.instance_name=racdb1

racdb2.instance_name=racdb2

racdb1.undo_tablespace=undo1

racdb2.undo_tablespace=undo2

Make sure that the cluster_database is set to false as of now.

Create an environment file for database

cd $HOME

vi db.env

unset ORACLE_HOME

export ORACLE_HOME=/u01/oracle/11.2.0.1

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0

export ORACLE_SID=racdb1

As Oracle user, connect as sysdba and create an spfile in shared storage using the pfile

. db.env

sqlplus / as sysdba

SQL> create spfile='+DATA/RACDB/spfileRACDB.ora' from pfile='/home/oracle/initRACDB.ora';

Navigate to $ORACLE_HOME/dbs

Create initracdb1.ora file that contains only one entry pointing to spfile in shared storage

vi initracdb1.ora

SPFILE='+DATA/RACDB/spfileRACDB.ora'

Phase 3: Execute Create Database and Post Creation Scripts on Node 1

Step 1: Execute the environment file db.env and start the instance on node 1

. db.env

sqlplus / as sysdba

SQL> startup nomount

Ensure that the instance name is racdb1 and status is STARTED

select instance_name,status from v$instance;

Create the db creation script and run

cd $HOME

vi createdb.sql

create database RACDB

datafile '+DATA/RACDB/system01.dbf' size 1024m autoextend on

sysaux datafile '+DATA/RACDB/sysaux01.dbf' size 1024m autoextend on

undo tablespace undo1

datafile '+DATA/RACDB/undo01.dbf' size 100m

default temporary tablespace temp

tempfile '+DATA/RACDB/temp01.dbf' size 100m

default tablespace userdata

datafile '+DATA/RACDB/userdata.dbf' size 200m

logfile

group 1 '+DATA/RACDB/thread1_redo1a.log' size 200m,

group 2 '+DATA/RACDB/thread1_redo2a.log' size 200m

controlfile reuse;

. db.env

sqlplus / as sysdba

@createdb.sql

Run the Post Creation scripts

. db.env

vi postcreate.sql

@/u01/oracle/11.2.0.1/rdbms/admin/catalog.sql

@/u01/oracle/11.2.0.1/rdbms/admin/catproc.sql

@/u01/oracle/11.2.0.1/rdbms/admin/catclust.sql

connect system/manager

@/u01/oracle/11.2.0.1/sqlplus/admin/pupbld.sql

sqlplus / as sysdba

@postcreate.sql

Now we have a database running on Node 1

Phase 4: Enable Second Node

Step 1: Copy initracdb1.ora from ORACLE_HOME/dbs in node 1 to node 2 and rename it to initracdb2.ora

cd $ORACLE_HOME/dbs

scp -r initracdb1.ora rac2:/u01/oracle/11.2.0.1/dbs

ssh rac2

vi db.env

unset ORACLE_HOME

export ORACLE_HOME=/u01/oracle/11.2.0.1

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0

export ORACLE_SID=racdb2

. db.env

cd $ORACLE_HOME/dbs

mv initracdb1.ora initracdb2.ora

Login in to node 1 as oracle user and set the db environment

. db.env

sqlplus / as sysdba

SQL>alter system set cluster_database=true scope=spfile;

SQL>create undo tablespace undo2 datafile '+DATA/RACDB/undo2.dbf' size 100m;

SQL>alter database add logfile thread 2 group 3 '+DATA/RACDB/thread2_redo3a.log' size 200m,group 4

'+DATA/RACDB/thread2_redo4a.log' size 200m;

SQL>alter database enable public thread 2;

SQL>shut immediate;

SQL>startup;

Now, Login to Node 2 as Oracle user, set the envrironment and start the instance

ssh rac2

. db.env

sqlplus / as sysdba

SQL>startup;

In my environment the database did not come up on node 2 due to disk group issue, which was DISMOUNTED. After mounting it, database started

succesfully on node 2

Phase 5: Register Database in OCR

Step 1: shut the instance on both the nodes

As Oracle user execute the below commands

srvctl add database -d RACDB -o $ORACLE_HOME

srvctl add instance -d RACDB -i racdb1 -n rac1

srvctl add instance -d RACDB -i racdb2 -n rac2

srvctl start database -d RACDB

As grid user check the cluster status and verify whether the database is registered or not

crsctl stat res -t

Thats it !!!

Regards

Charan