Amely'sOracleDBAblog

Two(2) Nodes RAC database creation in Oracle-The simplest way

I had to create a 2 node RAC Oracle database and add it to existing RAC set up.
I have used following approach:

Please note take care of character set application team has mentioned to you.

Command to check characterset:

SQL> select * from nls_database_parameters where parameter like '%CHARACTERSET';

PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_CHARACTERSET US7ASCII
NLS_NCHAR_CHARACTERSET AL16UTF16

Step 1:

Log in to the Linux/Unix box using oracle user:

Set Environment variable (For First Node):

export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
export ORACLE_SID=rine4db1

Step 2:

Verify existing RAC setup.:

[oracle@rinerac1 dbs]$ crs_stat -t

Name Type Tarpet State Host
------------------------------------------------------------
ora....L1.inst application ONLINE ONLINE rinerac1
ora....L2.inst application ONLINE ONLINE rinerac2
ora.ORCL.db application ONLINE ONLINE rinerac1

[oracle@rinerac1 dbs]$ olsnodes -i -p
rinerac1 rinerac1-priv rinerac1-vip
rinerac2 rinerac2-priv rinerac2-vip

[oracle@rinerac1 dbs]$ . oraenv
ORACLE_SID = [+ASM1] ? +ASM1

[oracle@rinerac1 dbs]$ asmcmd
ASMCMD> ls -ltr
State Type Rebal Name
MOUNTED EXTERN N FRA/
MOUNTED EXTERN N DATA/
ASMCMD>

Note : Domain name is dbrac.com ( example : rinerac1. dbrac.com ).

Step 3 :

Prepare initialization parameter file (initrine4db.ora) and store it in $ORACLE_HOME/dbs directory.

Note: Comment all rac related parameters before running createdb script

Pfile for node-1
-------------------------------------------------------------------------------------------------------
rine4db1.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*._cursor_features_enabled=1026
*._gc_read_mostly_locking=FALSE
*.archive_lag_tarpet=1800
*.audit_file_dest='/oraaudit/rine4db1'
*.audit_sys_operations=TRUE
*.audit_trail='OS'
*.cluster_database_instances=2
*.cluster_database=TRUE
*.compatible='11.2.0.3.0'
*.control_files='+MDLWARE_DATA/rine4db/controlfile/control01.ctl','+MDLWARE_DATA/rine4db/controlfile/control02.ctl'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_cache_advice='OFF'
*.db_cache_size=5120M
*.db_domain='r5.money.pe.com'
*.db_file_multiblock_read_count=16
*.db_files=1000
*.db_keep_cache_size=500M
*.db_name='rine4db'
*.db_create_file_dest='+MDLWARE_DATA'
*.db_recovery_file_dest='+MDLWARE_FRA'
*.db_recovery_file_dest_size=150G
*.db_securefile='PERMITTED'
*.diagnostic_dest='/u01/app/oracle/admin'
*.fast_start_mttr_tarpet=300
rine4db1.instance_name='rine4db1'
rine4db2.instance_name='rine4db2'
rine4db1.instance_number=1
rine4db2.instance_number=2
*.java_pool_size=512M
*.job_queue_processes=1000
*.larpe_pool_size=512M
rine4db2.local_listener='(address=(protocol=tcp)(host=G2428INPLMWDB2P-vip.r5.money.pe.com)(port=1875))'
rine4db1.local_listener='(address=(protocol=tcp)(host=G2428INPLMWDB1P-vip.r5.money.pe.com)(port=1875))'
*.log_buffer=10485760
*.open_cursors=10000
*.os_authent_prefix=''
*.parallel_max_servers=10
*.pga_aggregate_tarpet=1024M
*.processes=1500
*.remote_listener='mwaredb-scan.r5.money.pe.com:1875'
*.remote_login_passwordfile='EXCLUSIVE'
*.service_names='rine4db.r5.money.pe.com'
*.session_cached_cursors=400
*.sessions=1000
*.shared_pool_size=2048M
*.star_transformation_enabled='TRUE'
*.streams_pool_size=200M
rine4db1.thread=1
rine4db2.thread=2
*.undo_retention=10800
rine4db1.undo_tablespace='UNDOTBS1'
rine4db2.undo_tablespace='UNDOTBS2'
------------------------------------------------------------

Pfile for node-2
--------------------
rine4db2.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*._cursor_features_enabled=1026
*._gc_read_mostly_locking=FALSE
*.archive_lag_tarpet=1800
*.audit_file_dest='/oraaudit/rine4db2'
*.audit_sys_operations=TRUE
*.audit_trail='OS'
*.compatible='11.2.0.3.0'
*.control_files='+MDLWARE_DATA/rine4db/controlfile/control01.ctl','+MDLWARE_DATA/rine4db/controlfile/control02.ctl'
*.cursor_sharing='FORCE'
*.db_block_size=8192
*.db_cache_advice='OFF'
*.db_cache_size=5120M
*.db_domain='r5.money.pe.com'
*.db_file_multiblock_read_count=16
*.db_files=1000
*.db_keep_cache_size=500M
*.db_name='rine4db'
*.db_recovery_file_dest='+MDLWARE_FRA'
*.db_recovery_file_dest_size=150G
*.db_securefile='PERMITTED'
*.diagnostic_dest='/u01/app/oracle/admin'
*.fast_start_mttr_tarpet=300
*.java_pool_size=512M
*.job_queue_processes=1000
*.larpe_pool_size=512M
*.log_buffer=10485760
*.open_cursors=10000
*.os_authent_prefix=''
*.parallel_max_servers=10
*.pga_aggregate_tarpet=1024M
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.session_cached_cursors=400
*.sessions=1000
*.shared_pool_size=2048M
*.star_transformation_enabled='TRUE'
*.streams_pool_size=200M
*.undo_retention=10800
####################################################################
############# Cluster Parameters####################################
####################################################################
*.cluster_database_instances=2
*.cluster_database=TRUE
rine4db1.undo_tablespace='UNDOTBS1'
rine4db2.undo_tablespace='UNDOTBS2'
rine4db1.thread=1
rine4db2.thread=2
rine4db1.instance_name='rine4db1'
rine4db2.instance_name='rine4db2'
rine4db1.instance_number=1
rine4db2.instance_number=2
rine4db2.local_listener='(address=(protocol=tcp)(host=G2428INPLMWDB2P-vip.r5.money.pe.com)(port=1875))'
rine4db1.local_listener='(address=(protocol=tcp)(host=G2428INPLMWDB1P-vip.r5.money.pe.com)(port=1875))'
*.remote_listener='mwaredb-scan.r5.money.pe.com:1875'
*.service_names='rine4db.r5.money.pe.com'

Step 4:

Creating password file.

orapwd file=$ORACLE_HOME/dbs/orapwrine4db1 password=pem0ney# entries=10 ignorecase=y

Step 5 :

Configure both listener.ora and tnsnames.ora file for all nodes in cluster.

-- Node #1 Listener.ora.

LISTENER_rinerac1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rinerac1-vip)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 151.***.1.50)(PORT = 1521)(IP = FIRST))
)
)

Node #2 Listener.ora.

LISTENER_rinerac2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rinerac2-vip)(PORT = 1521)(IP = FIRST))
(ADDRESS = (PROTOCOL = TCP)(HOST = 151.***.1.51)(PORT = 1521)(IP = FIRST))
)
)

We didn’t had any local listener and was using scan listeners only so didn’t configured any listener.
tnsnames.ora file

rine4db =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = mwaredb-scan.r5.money.pe.com)(PORT = 1875))
)
(CONNECT_DATA =
(SERVICE_NAME = rine4db.r5.money.pe.com)
(SERVER = DEDICATED)
)
)

rine4db1 =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = 1.***.50.41)(PORT = 1875))
)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rine4DB.r5.money.pe.com)
)
)
LISTENERS_rine4db =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rinerac1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rinerac2-vip)(PORT = 1521))
)

rine4db =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rinerac1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rinerac2-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rine4db.DBPROD.COM)
)
)

rine4db2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rinerac2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rine4db.DBPROD.COM)
(INSTANCE_NAME = rine4db2)
)
)

rine4db1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rinerac1-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = rine4db.DBPROD.COM)
(INSTANCE_NAME = rine4db1)
)
)

Step 6 :

Create directory for database files and diagnostic files.

[oracle@rinerac1 ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/{adump,dpdump,hdump}
[oracle@rinerac1 ~]$ cd $ORACLE_BASE/admin/$ORACLE_SID/
[oracle@rinerac1 rine4db1]$ ls -ltr
total 12
drwxr-xr-x 2 oracle oinstall 4096 Oct 16 18:42 hdump
drwxr-xr-x 2 oracle oinstall 4096 Oct 16 18:42 dpdump
drwxr-xr-x 2 oracle oinstall 4096 Oct 16 18:42 adump

[oracle@rinerac1 dbs]$ . oraenv
ORACLE_SID = [+ASM1] ? +ASM1
The Oracle base for ORACLE_HOME=/u02/app/oracle/product/11.1.0/asm_1 is /u02/app/oracle
[oracle@rinerac1 dbs]$ asmcmd mkdir +DATA/rine4db
[oracle@rinerac1 dbs]$ asmcmd ls +DATA
rine4db/ -- Directory has been created.
ORCL/
N.B.:-
Login as grid user
Su – grid
Export ORACLE_SID=+ASM1
Asmcmd
Create all paths mentioned in pfile.

Step 7:

Execute Create database command.

SQL> startup nomount pfile=pfilename
create database rine4db CONTROLFILE REUSE
logfile group 1 ('+MDLWARE_DATA/rine4db/logfile/redo1a.log', '+MDLWARE_DATA/rine4db/logfile/redo1b.log') size 512m reuse,
group 2 ('+MDLWARE_DATA/rine4db/logfile/redo2a.log', '+MDLWARE_DATA/rine4db/logfile/redo2b.log') size 512m reuse,
group 3 ('+MDLWARE_DATA/rine4db/logfile/redo3a.log', '+MDLWARE_DATA/rine4db/logfile/redo3b.log') size 512m reuse,
group 4 ('+MDLWARE_DATA/rine4db/logfile/redo4a.log', '+MDLWARE_DATA/rine4db/logfile/redo4b.log') size 512m reuse,
group 5 ('+MDLWARE_DATA/rine4db/logfile/redo5a.log', '+MDLWARE_DATA/rine4db/logfile/redo5b.log') size 512m reuse
character set AL32UTF8
national character set AL16UTF16
datafile '+MDLWARE_DATA/rine4db/datafile/system01.dbf'
size 1024M
autoextend on
next 100M maxsize unlimited
extent manapement local
sysaux datafile '+MDLWARE_DATA/rine4db/datafile/sysaux01.dbf'
size 1024M
autoextend on
next 500M
maxsize 10240M
undo tablespace undotbs1
datafile '+MDLWARE_DATA/rine4db/datafile/undotbs1_1.dbf'
size 1024M
default temporary tablespace temp
tempfile '+MDLWARE_DATA/rine4db/tempfile/temp1.dbf'
size 1024M;
----------------------------------------------------------------------------------------------------------------

Step 8:

Once the database is created, execute all the usual good stuff - catalog, catproc, pupbld scripts (all under /rdbms/admin)

Run following script as SYS user.

SQL> CONNECT / AS SYSDBA
Connected.
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catblock.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> SHOW PARAMETER CASE

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
sec_case_sensitive_logon boolean TRUE

SQL> ALTER SYSTEM SET sec_case_sensitive_logon=FALSE;

System altered.

Run following script as SYSTEM user.

SQL> connect system/oracle
Connected.
SQL> @?/sqlplus/admin/pupbld.sql
SQL> @?/sqlplus/admin/help/hlpbld.sql helpus.sql

Step 9:

Create a UNDO tablespace for remaining nodes.

create undo tablespace UNDOTBS2 datafile '+MDLWARE_DATA' size 100M autoextend on next 50M;

Step 10:

Adding redo log file for remaining nodes and run catclust.sqlscript as SYS.

Add new REDO thread and enable it
(otherwise it will give this error while starting the 2nd instance: ORA-01620: no public threads are available for mounting)

alter database add logfile thread 2 group 6 '+MDLWARE_DATA' size 512M;
alter database add logfile thread 2 group 7 '+MDLWARE_DATA' size 512M;
alter database enable public thread 2;
Uncomment all rac related parameters:

SQL> @?/rdbms/admin/catclust.sql

Step 11:

Login to 2nd node as oracle user:
export ORACLE_BASE=/u02/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1
export ORACLE_SID=rine4db2
mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/{adump,dpdump,hdump}
sqlplus / as sysdba
startup
Now on both node create spfile.
sqlplus / as sysdba
create spfile from pfile
shut immediate both instances and startup

We have created two separate pfile so need to keep pfile /spfile in shared location.

I didn’t run following steps in green and my rac db is running fine:

Note : Make sure that all the instance in down before continue below steps. If not bring it all the instance down.

[oracle@rinerac1 ~]$ export ORACLE_HOME=/u02/app/oracle/product/11.1.0/db_1
[oracle@rinerac1 ~]$ srvctl add database -d RINE4DB -o $ORACLE_HOME -m DBRAC.COM
[oracle@rinerac1 ~]$ srvctl add instance -d RINE4B -i rine4db1 -n rinerac1
[oracle@rinerac1 ~]$ srvctl add instance -d RINE4DB -i rine4db2 -n rinerac2

oracle@rinerac1 ~]$ crs_stat -t -v

Name Type R/RA F/FT Tarpet State Host
----------------------------------------------------------------------
ora....B1.inst application 0/5 0/0 OFFLINE OFFLINE
ora....B2.inst application 0/5 0/0 OFFLINE OFFLINE
ora.rine4db.db application 0/0 0/1 OFFLINE OFFLINE
ora....L1.inst application 0/5 0/0 ONLINE ONLINE rinerac1
ora....L2.inst application 0/5 0/0 ONLINE ONLINE rinerac2
[oracle@rinerac1 ~]$ srvctl start database -d rine4db
[oracle@rinerac1 ~]$ crs_stat -t -v
Name Type R/RA F/FT Tarpet State Host
----------------------------------------------------------------------
ora....B1.inst application 0/5 0/0 ONLINE ONLINE rinerac1
ora....B2.inst application 0/5 0/0 ONLINE ONLINE rinerac2
ora.rine4db.db application 0/0 0/1 ONLINE ONLINE rinerac2
SQL> col host_name for a25
SQL> col instance_name for a20
SQL> select instance_name,host_name from gv$instance;

INSTANCE_NAME HOST_NAME
-------------------- -------------------------
rine4db2 rinerac2.dbrac.com
rine4db1 rinerac1.dbrac.com

[oracle@rinerac1 ~]$ srvctl status database -d rine4db
Instance rine4db1 is running on node rinerac1
Instance rine4db2 is running on node rinerac2
[oracle@rinerac1 ~]$ srvctl status asm -n rinerac1
ASM instance +ASM1 is running on node rinerac1.
[oracle@rinerac1 ~]$ srvctl status asm -n rinerac2
ASM instance +ASM2 is running on node rinerac2.
[oracle@rinerac1 ~]$ srvctl status instance -d rine4db -i rine4db1
Instance rine4db1 is running on node rinerac1
[oracle@rinerac1 ~]$ srvctl status instance -d rine4db -i rine4db2
Instance rine4db2 is running on node rinerac2
[oracle@rinerac1 ~]$ srvctl config database -d rine4db
rinerac1 rine4db1 /u02/app/oracle/product/11.1.0/db_1
rinerac2 rine4db2 /u02/app/oracle/product/11.1.0/db_1

DB Creation ends here
================================================================================================================================================