Manual database creation in oracle 11g

Manual Database creation steps as below.
1-Create environment files on all nodes for all instances..
(target instance name will be normally <sourceinstance_name>n,where n=1,2,.. Node of nodes)

oracle@Rac1[joshi1]> echo $ORACLE_SID
joshi1
oracle@Rac1[joshi1]> echo $ORACLE_HOME
/prod/product/oracle/11.2.0


2- a- Verify exist RAC recource detail.
        crs_stat -t
       
Rac1[oracle]_+ASM1> crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora.ARCH.dg    ora....up.type ONLINE    ONLINE    host...b020
ora.DATA.dg    ora....up.type ONLINE    ONLINE    host...b020
ora....ER.lsnr ora....er.type ONLINE    ONLINE    host...b020
ora....N1.lsnr ora....er.type ONLINE    ONLINE    host...b021
ora....N2.lsnr ora....er.type ONLINE    ONLINE    host...b020
ora....N3.lsnr ora....er.type ONLINE    ONLINE    host...b020
ora....C1.lsnr ora....er.type ONLINE    ONLINE    host...b020
ora....VOTE.dg ora....up.type ONLINE    ONLINE    host...b020
ora.asm        ora.asm.type   ONLINE    ONLINE    host...b020
ora....SM1.asm application    ONLINE    ONLINE    host...b020
ora....20.lsnr application    ONLINE    ONLINE    host...b020
ora....20.lsnr application    ONLINE    ONLINE    host...b020
ora....020.gsd application    OFFLINE   OFFLINE
ora....020.ons application    ONLINE    ONLINE    host...b020
ora....020.vip ora....t1.type ONLINE    ONLINE    host...b020
ora....SM2.asm application    ONLINE    ONLINE    host...b021
ora....21.lsnr application    ONLINE    ONLINE    host...b021
ora....21.lsnr application    ONLINE    ONLINE    host...b021
ora....021.gsd application    OFFLINE   OFFLINE
ora....021.ons application    ONLINE    ONLINE    host...b021
ora....021.vip ora....t1.type ONLINE    ONLINE    host...b021
ora.cvu        ora.cvu.type   ONLINE    ONLINE    host...b020
ora.gsd        ora.gsd.type   OFFLINE   OFFLINE
ora....network ora....rk.type ONLINE    ONLINE    host...b020
ora.oc4j       ora.oc4j.type  ONLINE    ONLINE    host...b020
ora.ons        ora.ons.type   ONLINE    ONLINE    host...b020
ora.scan1.vip  ora....ip.type ONLINE    ONLINE    host...b021
ora.scan2.vip  ora....ip.type ONLINE    ONLINE    host...b020
ora.scan3.vip  ora....ip.type ONLINE    ONLINE    host...b020
ora....arc1.db ora....se.type ONLINE    ONLINE    host...b020
       
   b- check the avilable node name for this cluster.
      olsnodes -n -i -s
   
Rac1[oracle]_+ASM1> olsnodes -n -i -s
Rac1    1       Rac1-vip        Active
Rac2    2       Rac2-vip        Active
   
3-Verify and make sure we are entho space on asm diskgroup.
Rac1[oracle]_+ASM1> asmcmd
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576     65542    65275                0           65275              0             N  ARCH/
MOUNTED  EXTERN  N         512   4096  1048576    131084    80266                0           80266              0             N  DATA/
MOUNTED  EXTERN  N         512   4096  1048576     17408    17012                0           17012              0             Y  OCR_VOTE/


4-Create the directories specified in environment file on all nodes and for diagnostic_dest and audit_file_dest

5-Set environment to target instance on primary node -- from here we are going to start creating DB.

6-Copy the pfile from  anyof db to the  node, and update value for below database. 
 -Initialization parameter file
db_block_size=8192
cluster_database_instances=2
open_cursors=300
db_name=joshi
db_create_file_dest=+DATA
db_recovery_file_dest_size=2147483648
diagnostic_dest=/data01/app/oracle
processes=500
sga_target=3G
audit_file_dest=/data01/app/oracle/admin/joshi/adump
audit_trail=db
pga_aggregate_target=100M
undo_tablespace=UNDOTBS1
log_archive_dest=+ARCH
db_unique_name=joshi
--Need to change below parameter as per Env.
---diagnostic_dest=---
--audit_file_dest=---
--control_files=+DATA,+DATA 
---log_archive_dest=+ARCH
---db_create_file_dest='+DATA'
-----Above db_unique_name is Very importemnet parameter ASM will create file inside root of data Diskgroup , Carefully setup this parametere value .
7-Start the instance to nomount state
oracle@Rac1[joshi1]> sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.3.0 Production on Sun Jul 20 13:50:28 2014

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

Connected to an idle instance.


SQL*Plus tells us that connected to an idle instance. That means that it is not yet started.
So, lets start the instance. .
SQL> startup nomount
ORACLE instance started.

Total System Global Area 3206836224 bytes
Fixed Size                  2232640 bytes
Variable Size             654315200 bytes
Database Buffers         2499805184 bytes
Redo Buffers               50483200 bytes


CREATE DATABASE joshi
MAXINSTANCES 32
MAXLOGHISTORY 1
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
DATAFILE SIZE 300M AUTOEXTEND ON NEXT  1024M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE SIZE 120M AUTOEXTEND ON NEXT  1024M MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 20M AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE SIZE 200M AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
DEFAULT TABLESPACE USERS DATAFILE SIZE 100M
CHARACTER SET UTF8
LOGFILE GROUP 1  SIZE 500M,
GROUP 2  SIZE 500M

 -- If something goes wrong with the creation, Oracle will write an error into the alert log.
8. Adding  CONTROL_FILES location in pfile.

set linesize 2048;
column ctl_files NEW_VALUE ctl_files;
select concat('control_files=''', concat(replace(value, ', ', ''','''), '''')) ctl_files from v$parameter where name ='control_files';

control_files='+DATA/joshi/controlfile/current.302.853422869'



Now lets run below script for creating data dictnery tables.

@?/rdbms/admin/catalog.sql
@?/rdbms/admin/catblock.sql
@?/rdbms/admin/catproc.sql

 Run following script as SYSTEM user.

connect system/manager
@?/sqlplus/admin/pupbld.sql
@?/sqlplus/admin/help/hlpbld.sql helpus.sql

!wow here we created DB on ASM file system .

Here we are going to convert db on RAC

logging in sys user

1-sql >@?/rdbms/admin/catclust.sql   

2-Create undo tablespaces and redo log threads for other instances and enable the newly created threads

joshi1 >CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE '+DATA' size 2048M;

Tablespace created

3-joshi1 >alter database add logfile thread 2 group 8 ('+DATA','+DATA') SIZE 105m;

Database altered.

4-joshi1 >alter database add logfile thread 2 group 9 ('+DATA','+DATA') SIZE 105m;

Database altered.

5-joshi1 >alter database enable public thread 2;

Database altered.
6-Shutdown the  instance and add below  RAC parameters to parameter file.

*.cluster_database = TRUE
*.cluster_database_instances = 2
*.undo_management=AUTO
joshi1.undo_tablespace=UNDOTBS
joshi1.instance_name=joshi1
joshi1.instance_number=1
joshi1.thread=1
joshi2.instance_name=joshi2
joshi2.instance_number=2
joshi2.thread=2
joshi2.undo_tablespace=UNDOTBS2
instance_number=2

7-Create  spfile from pfile

joshi1 >create spfile='+DATA' from pfile;

File created.

Start Database using spfile , for spfile location go into asm >'+DATA' >db_unique_name>parameterfile>spfile****

add spfile complete location in pfile and restart database and validate  new spfile location

logging into node 2 and create audit and dignostic dump location .
source instance 2 enviornment file .
add new spfile location into pfile inside $ORACLE_HOME/dbs.


8-Register the all RAC instances and database with crs

srvctl add database -d joshi -o /prod/product/oracle/product/11.2.0.2
srvctl add instance -i joshi1 -d joshi -n Rac1

srvctl add instance -i joshi2 -d joshi -n Rac2

9-Start all instances using srvctl

Rac1[oracle]_joshi1> srvctl start database -d joshi

Rac1[oracle]_joshi1> srvctl status database -d joshi
Instance joshi1 is running on node Rac1
Instance joshi2 is running on node Rac2

10-Configure local  RAC listener using netca

11.    Set  the local listener and remote listener parameters for all instance.

a.    For remote listener parameter ,please specify the scan hostname and scan port number
b.    For local listener, Please virtual IP address as hostname and local listener port number .

alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXXX-VIP)(PORT=1629))))' scope=both sid='joshi1';
alter system set local_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=XXXXX-VIP)(PORT=1629))))' scope=both sid='joshi2';
alter system set remote_listener='(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST=XXX_SCAN)(PORT=1628))))' scope=both sid='*';