Creating a RAC Database Manually (without dbca)

Step by Step Creating a RAC Database Manually 

(without dbca)

If you would like to by-pass the Database Configuration Assistant (dbca), you can use the following steps - with appropriate alterations - to create your RAC databases. This example assumes that you already have a cluster with the appropriate clusterware and shared storage, etc. configured.

If not already present, Refer below link for more details.

Oracle11gR1(11.1.0.6.0) RAC Installation Steps onLinux

Manual Database creation steps as follows.

Step 1: Environment variable Settings ( First Node).

export ORACLE_BASE=/u02/app/oracle

export ORACLE_HOME=$ORACLE_BASE/product/11.1.0/db_1

export ORACLE_SID=DEVDB1

Step 2: Verify exist RAC setup. For more details about  RAC installation guide CLICKHERE

[oracle@krac1 dbs]$ crs_stat -t

Name           Type           Target    State     Host       

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

ora....L1.inst application    ONLINE    ONLINE    krac1      

ora....L2.inst application    ONLINE    ONLINE    krac2      

ora.ORCL.db    application    ONLINE    ONLINE    krac1      

ora....SM1.asm application    ONLINE    ONLINE    krac1      

ora....C1.lsnr application    ONLINE    ONLINE    krac1      

ora.krac1.gsd  application    ONLINE    ONLINE    krac1      

ora.krac1.ons  application    ONLINE    ONLINE    krac1      

ora.krac1.vip  application    ONLINE    ONLINE    krac1      

ora....SM2.asm application    ONLINE    ONLINE    krac2      

ora....C2.lsnr application    ONLINE    ONLINE    krac2      

ora.krac2.gsd  application    ONLINE    ONLINE    krac2      

ora.krac2.ons  application    ONLINE    ONLINE    krac2      

ora.krac2.vip  application    ONLINE    ONLINE    krac2      

[oracle@krac1 dbs]$ olsnodes -i -p

krac1   krac1-priv      krac1-vip

krac2   krac2-priv      krac2-vip

[oracle@krac1 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@krac1 dbs]$ asmcmd

ASMCMD> ls -ltr

State    Type    Rebal  Name

MOUNTED  EXTERN  N      FRA/

MOUNTED  EXTERN  N      DATA/

ASMCMD>

Note : Domain name is dbprod.com  ( example : krac1. dbprod.com ).

Step 3 : Prepare initialization parameter file (initDEVDB1.ora) and store it in  $ORACLE_HOME/dbs directory.

db_block_size=8192

cluster_database_instances=2

remote_listener=LISTENERS_DEVDB

open_cursors=300

db_domain=DBPROD.COM

db_name=DEVDB

db_create_file_dest=+DATA

db_recovery_file_dest=+FRA

db_recovery_file_dest_size=2147483648

compatible=11.1.0.0.0

diagnostic_dest=/u02/app/oracle

processes=150

sga_target=635437056

audit_file_dest=/u02/app/oracle/admin/DEVDB/adump

audit_trail=db

remote_login_passwordfile=exclusive

dispatchers="(PROTOCOL=TCP) (SERVICE=DEVDBXDB)"

pga_aggregate_target=211812352

DEVDB1.instance_number=1

DEVDB2.instance_number=2

DEVDB2.thread=2

DEVDB1.thread=1

DEVDB1.undo_tablespace=UNDOTBS1

DEVDB2.undo_tablespace=UNDOTBS2

Step 4:  Creating password file.

[oracle@krac1 dbs]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle

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

-- Node #1 Listener.ora.

LISTENER_KRAC1 =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = krac1-vip)(PORT = 1521)(IP = FIRST))

      (ADDRESS = (PROTOCOL = TCP)(HOST = 152.168.1.50)(PORT = 1521)(IP = FIRST))

    )

  )

Node #2 Listener.ora.

LISTENER_KRAC2 =

  (DESCRIPTION_LIST =

    (DESCRIPTION =

      (ADDRESS = (PROTOCOL = TCP)(HOST = krac2-vip)(PORT = 1521)(IP = FIRST))

      (ADDRESS = (PROTOCOL = TCP)(HOST = 152.168.1.51)(PORT = 1521)(IP = FIRST))

    )

  )

tnsnames.ora file

LISTENERS_DEVDB =

  (ADDRESS_LIST =

    (ADDRESS = (PROTOCOL = TCP)(HOST = krac1-vip)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = krac2-vip)(PORT = 1521))

  )

DEVDB =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = krac1-vip)(PORT = 1521))

    (ADDRESS = (PROTOCOL = TCP)(HOST = krac2-vip)(PORT = 1521))

    (LOAD_BALANCE = yes)

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = DEVDB.DBPROD.COM)

    )

  )

DEVDB2 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = krac2-vip)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = DEVDB.DBPROD.COM)

      (INSTANCE_NAME = DEVDB2)

    )

  )

DEVDB1 =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = krac1-vip)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = DEVDB.DBPROD.COM)

      (INSTANCE_NAME = DEVDB1)

    )

  )

Step 6 :  Create directory for database files and diagnostic files.

[oracle@krac1 ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/{adump,dpdump,hdump}

[oracle@krac1 ~]$ cd $ORACLE_BASE/admin/$ORACLE_SID/

[oracle@krac1 DEVDB1]$ 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@krac1 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@krac1 dbs]$ asmcmd mkdir +DATA/DEVDB

[oracle@krac1 dbs]$ asmcmd ls +DATA

DEVDB/ -- Directory has been created.

ORCL/

[oracle@krac1 dbs]$

Step 7: Execute Create database command.

[oracle@krac1 dbs]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Oct 16 19:02:52 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount

ORACLE instance started.

Total System Global Area  640294912 bytes

Fixed Size                  1301784 bytes

Variable Size             171967208 bytes

Database Buffers          461373440 bytes

Redo Buffers                5652480 bytes

SQL> CREATE DATABASE "DEVDB"

  2  MAXINSTANCES 32

  3  MAXLOGHISTORY 1

  4  MAXLOGFILES 192

  5  MAXLOGMEMBERS 3

  6  MAXDATAFILES 1024

  7  DATAFILE SIZE 300M AUTOEXTEND ON NEXT  1024M MAXSIZE UNLIMITED

  8  EXTENT MANAGEMENT LOCAL

  9  SYSAUX DATAFILE SIZE 120M AUTOEXTEND ON NEXT  1024M MAXSIZE UNLIMITED

 10  SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 20M AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED

 11  SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 200M AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED

 12  DEFAULT TABLESPACE USERS DATAFILE SIZE 100M

 13  CHARACTER SET WE8MSWIN1252

 14  NATIONAL CHARACTER SET AL16UTF16

 15  LOGFILE GROUP 1  SIZE 500M,

 16  GROUP 2  SIZE 500M

 17  USER SYS IDENTIFIED BY ORACLE

 18  USER SYSTEM IDENTIFIED BY ORACLE

;

Database created.

Step 8: Adding  CONTROL_FILES parameter in pfile.

SQL> set linesize 2048;

SQL> column ctl_files NEW_VALUE ctl_files;

SQL> select concat('control_files=''', concat(replace(value, ', ', ''','''), '''')) ctl_files from v$parameter where name ='control_files';

SQL> host echo &ctl_files >> $ORACLE_HOME/dbs/initDEVDB1.ora;

Step 9: Create a UNDO tablespace for remaining nodes.

SQL> CREATE SMALLFILE UNDO TABLESPACE "UNDOTBS2" DATAFILE SIZE 100M;   

Tablespace created.

Step 10: 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.

Step 11: 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 12: Adding redo log file for remaining nodes and run catclust.sql script as SYS.

SQL> CONNECT / AS SYSDBA    

Connected.

SQL> ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 SIZE 500M,

  2  GROUP 4  SIZE 500M;

Database altered.

SQL> ALTER DATABASE ENABLE PUBLIC THREAD 2;

Database altered.

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

Step 13: Create a SPFILE and copy those files to remaining nodes.

SQL> create spfile='+DATA/DEVDB/spfileDEVDB.ora' from pfile;

File created.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> host echo "SPFILE='+DATA/DEVDB/spfileDEVDB.ora'" >$ORACLE_HOME/dbs/initDEVDB1.ora

SQL> startup

ORACLE instance started.

Total System Global Area  640294912 bytes

Fixed Size                  1301784 bytes

Variable Size             171967208 bytes

Database Buffers          461373440 bytes

Redo Buffers                5652480 bytes

Database mounted.

Database opened.

SQL> select value from v$parameter where name='spfile';

VALUE

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

+DATA/devdb/spfiledevdb.ora

SQL> host scp $ORACLE_HOME/dbs/initDEVDB1.ora krac2:$ORACLE_HOME/dbs/initDEVDB2.ora

SQL> host scp $ORACLE_HOME/dbs/orapwDEVDB1 krac2:$ORACLE_HOME/dbs/orapwDEVDB2  

Step 14: Login as oracle user on node2(krac2) and execute below statements. 

[oracle@krac2 ~]$ export ORACLE_SID=DEVDB2

[oracle@krac2 ~]$ export ORACLE_HOME=/u02/app/oracle/product/11.1.0/db_1

[oracle@krac2 ~]$ mkdir -p $ORACLE_BASE/admin/$ORACLE_SID/{adump,dpdump,hdump}

[oracle@krac2 ~]$ sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Oct 16 20:51:19 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Total System Global Area  640294912 bytes

Fixed Size                  1301784 bytes

Variable Size             167772904 bytes

Database Buffers          465567744 bytes

Redo Buffers                5652480 bytes

Database mounted.

Database opened.

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

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

DEVDB2               krac2.dbprod.com

DEVDB1               krac1.dbprod.com

SQL> shutdown immediate

Database closed.

Database dismounted.

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

[oracle@krac1 ~]$ export ORACLE_HOME=/u02/app/oracle/product/11.1.0/db_1

[oracle@krac1 ~]$ srvctl add database -d DEVDB -o $ORACLE_HOME -m DBPROD.COM

[oracle@krac1 ~]$ srvctl add instance -d DEVDB -i DEVDB1 -n krac1

[oracle@krac1 ~]$ srvctl add instance -d DEVDB -i DEVDB2 -n krac2

[oracle@krac1 ~]$ crs_stat -t -v

Name           Type           R/RA   F/FT   Target    State     Host       

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

ora....B1.inst application    0/5    0/0    OFFLINE   OFFLINE              

ora....B2.inst application    0/5    0/0    OFFLINE   OFFLINE              

ora.DEVDB.db   application    0/0    0/1    OFFLINE   OFFLINE              

ora....L1.inst application    0/5    0/0    ONLINE    ONLINE    krac1      

ora....L2.inst application    0/5    0/0    ONLINE    ONLINE    krac2      

ora.ORCL.db    application    0/0    0/1    ONLINE    ONLINE    krac1      

ora....SM1.asm application    0/5    0/0    ONLINE    ONLINE    krac1      

ora....C1.lsnr application    0/5    0/0    ONLINE    ONLINE    krac1      

ora.krac1.gsd  application    0/5    0/0    ONLINE    ONLINE    krac1      

ora.krac1.ons  application    0/3    0/0    ONLINE    ONLINE    krac1      

ora.krac1.vip  application    0/0    0/0    ONLINE    ONLINE    krac1      

ora....SM2.asm application    0/5    0/0    ONLINE    ONLINE    krac2      

ora....C2.lsnr application    0/5    0/0    ONLINE    ONLINE    krac2      

ora.krac2.gsd  application    0/5    0/0    ONLINE    ONLINE    krac2       

ora.krac2.ons  application    0/3    0/0    ONLINE    ONLINE    krac2      

ora.krac2.vip  application    0/0    0/0    ONLINE    ONLINE    krac2      

[oracle@krac1 ~]$ srvctl start database -d DEVDB

[oracle@krac1 ~]$ crs_stat -t -v

Name           Type           R/RA   F/FT   Target    State     Host       

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

ora....B1.inst application    0/5    0/0    ONLINE    ONLINE    krac1      

ora....B2.inst application    0/5    0/0    ONLINE    ONLINE    krac2      

ora.DEVDB.db   application    0/0    0/1    ONLINE    ONLINE    krac2      

ora....L1.inst application    0/5    0/0    ONLINE    ONLINE    krac1      

ora....L2.inst application    0/5    0/0    ONLINE    ONLINE    krac2      

ora.ORCL.db    application    0/0    0/1    ONLINE    ONLINE    krac1      

ora....SM1.asm application    0/5    0/0    ONLINE    ONLINE    krac1      

ora....C1.lsnr application    0/5    0/0    ONLINE    ONLINE    krac1      

ora.krac1.gsd  application    0/5    0/0    ONLINE    ONLINE    krac1      

ora.krac1.ons  application    0/3    0/0    ONLINE    ONLINE    krac1      

ora.krac1.vip  application    0/0    0/0    ONLINE    ONLINE    krac1      

ora....SM2.asm application    0/5    0/0    ONLINE    ONLINE    krac2      

ora....C2.lsnr application    0/5    0/0    ONLINE    ONLINE    krac2      

ora.krac2.gsd  application    0/5    0/0    ONLINE    ONLINE    krac2      

ora.krac2.ons  application    0/3    0/0    ONLINE    ONLINE    krac2      

ora.krac2.vip  application    0/0    0/0    ONLINE    ONLINE    krac2      

[oracle@krac1 ~]$ sqlplus system/oracle@devdb

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Oct 16 22:52:19 2012

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

With the Partitioning, Real Application Clusters, OLAP, Data Mining

and Real Application Testing options

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

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

DEVDB2               krac2.dbprod.com

DEVDB1               krac1.dbprod.com

[oracle@krac1 ~]$ srvctl status database -d devdb

Instance DEVDB1 is running on node krac1

Instance DEVDB2 is running on node krac2

[oracle@krac1 ~]$ srvctl status asm -n krac1

ASM instance +ASM1 is running on node krac1.

[oracle@krac1 ~]$ srvctl status asm -n krac2

ASM instance +ASM2 is running on node krac2.

[oracle@krac1 ~]$ srvctl status instance -d devdb -i devdb1

Instance devdb1 is running on node krac1

[oracle@krac1 ~]$ srvctl status instance -d devdb -i devdb2

Instance devdb2 is running on node krac2

[oracle@krac1 ~]$ srvctl config database -d devdb

krac1 DEVDB1 /u02/app/oracle/product/11.1.0/db_1

krac2 DEVDB2 /u02/app/oracle/product/11.1.0/db_1

[oracle@krac1 ~]$

Step by Step Oracle 11gR2 RAC Installation Guide on Linux.

Step by Step Oracle 11gR1 RAC Installation Guide on Linux.

What is SCAN in Oracle 11gR2 RAC.

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

 

Hope! This helps...

Regards,

Kavin.

BE THE BEST!!! BE WITH THE BEST !!!.