Oracle 11g R2 RAC Database creation Manually

Oracle 11g R2 RAC Database creation Manually.

Follow the below steps to create RAC database.

1.create pfile:

*.audit_file_dest='/u01/oradb/admin/u/adump'
*.audit_trail='db'
#*.cluster_database=true
#*.cluster_database_instances=2
*.compatible='11.2.0.4.0'
*.control_files='+DATADG/PUNITDB/control01.ctl','+DATADG/PUNITDB/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='PUNIT'
*.db_unique_name='PUNITDB'
*.dg_broker_start=TRUE
*.diagnostic_dest='/u01/oradb'
PUNITDB1.instance_number=1
PUNITDB2.instance_number=2
PUNITDB1.instance_name='PUNITDB1'
PUNITDB2.instance_name='PUNITDB2'
#*.log_archive_config='dg_config=(PUNITDB,PUNITSB)'
*.log_archive_dest_1='location=+ARCHIVEDG VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=PUNITDB'
#*.log_archive_dest_2='SERVICE=PUNITSB_DGSRVC ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=PUNITDB'
#*.log_archive_dest_state_2='ENABLE'
*.memory_max_target=1000M
*.memory_target=900M
*.open_cursors=300
*.processes=150
*.remote_login_passwordfile='exclusive'
#*.standby_file_management='AUTO'
PUNITDB1.thread=1
PUNITDB2.thread=2
*.undo_management='AUTO'
PUNITDB1.undo_tablespace='UNDOTBS1'
PUNITDB2.undo_tablespace='UNDOTBS2'

2.create required directories in disk group and file system.

ASMCMD [+DATA01] > mkdir PUNITDB
ASMCMD [+DATA01] > cd PUNITDB
ASMCMD [+DATA01/PUNITDB] >

3.create password file.

orapwd file=orapwPUNITDB1 password=****** entries=20



4.prepare database creation script.

createdb.sql
===========


CREATE DATABASE PUNIT
USER SYS IDENTIFIED BY ******
USER SYSTEM IDENTIFIED BY ******
DATAFILE '+DATADG/PUNITDB/ PUNITDB_system_01.dbf' 
SIZE 1G AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SYSAUX DATAFILE '+DATADG/PUNITDB/PUNITDB_sysaux_01.dbf' 
SIZE 1G AUTOEXTEND ON NEXT 100M MAXSIZE 3G
DEFAULT TABLESPACE USERS DATAFILE '+DATA01/PUNITDB/PUNITDB_users_01.dbf' 
SIZE 1G AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP1 TEMPFILE '+DATA01/PUNITDB/PUNITDB_temp1_01.dbf' 
SIZE 3G AUTOEXTEND ON MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE '+DATA01/PUNITDB/PUNITDB_undo11_01.dbf' 
SIZE 10M REUSE AUTOEXTEND ON NEXT 5M  MAXSIZE UNLIMITED
LOGFILE GROUP 1 ('+DATA01/PUNITDB/PUNITDB_REDO_1_01.rdo','+DATA01/PUNITDB/PUNITDB_REDO_1_02.rdo') SIZE 50M,
GROUP 2 ('+DATA01/PUNITDB/PUNITDB_REDO_2_01.rdo','+DATA01/PUNITDB/PUNITDB_REDO_2_02.rdo') SIZE 50M
CHARACTER SET AL32UTF8
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
MAXLOGFILES 32
MAXLOGMEMBERS 4
MAXLOGHISTORY 100
MAXDATAFILES 254
MAXINSTANCES 8

5.start the instance in nomount and execute above script.(comment out cluster parameters).

SQL>startup nomount pfile='initPUNITDB1.ora'

SQL>createdb.sql

Database Created .

SQL> Select name,open_mode from v$database;

NAME            OPEN_MODE

---------             --------------------

PUNIT             READ WRITE

6.Run the scripts necessary to build data dictionary views, synonyms, and PL/SQL packages, and to support proper functioning of SQL*Plus:


1.catalog.sql
2.catproc.sql
3.catclust.sql (Run catclust.sql. This command creates the dictionary views needed for Oracle RAC databases).
4.pupbld.sql (system user).

7.Create undo tablespace and thread for second instance.

create UNDO TABLESPACE "UNDOTBS2" DATAFILE '+DATA01/PUNITDB/PUNITDB_undo12_01.dbf' SIZE 10M REUSE AUTOEXTEND ON NEXT 5M  MAXSIZE UNLIMITED;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 ('+DATA01/PUNITDB/PUNITDB_REDO_3_01.rdo','+DATA01/PUNITDB/PUNITDB_REDO_3_02.rdo') SIZE 50M;

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 4 ('+DATA01/PUNITDB/PUNITDB_REDO_4_01.rdo','+DATA01/PUNITDB/PUNITDB_REDO_4_02.rdo') SIZE 50M;

alter database enable public thread 2;

8.Now uncomment the cluster database parameters and shut down and start the instance on node1.

SQL> startup pfile='initPUNITDB1.ora'

9.now copy the parameter file and password file to other node.And change names according as instance.

 scp initPUNITDB1.ora orapwPUNITDB1 oradb@node2:/u01/oradb/db2/dbs

10.start the second instance.

SQL> startup pfile='initPUNITDB2.ora'

ORACLE instance started

Database Opened.

Database Mounted.

10.Execute the cluster script in any of the node.

SQL>catclust.sql

11.Check the both instances status.

SQL> select instance_number,instance_name,status from gv$instance;

INSTANCE_NUMBER INSTANCE_NAME    STATUS

--------------- ---------------- ---------

              1 PUNITDB1        OPEN

              2 PUNITDB2        OPEN

12.add the database info to the cluster .

srvctl add database -d B -o /u01/oradb/db2 

srvctl add instance -d PUNITDB -i PUNITDB1 -n node1

srvctl add instance -d PUNITDB -i PUNITDB2 -n node2

srvctl start instance -d PUNITDB -i PUNITDB1

srvctl start instance -d PUNITDB -i PUNITDB2