Creating a RAC Database manaully

Creating a RAC Database.


DB Name : layatest
Instance 1 : layatest1
Instance 2 : layatest2

ASM Diskgroups - DATA1 and DATA3




1) Create the Required directories in both the nodes

mkdir -p /u01/app/oracle/admin/layatest/adump
mkdir -p /u01/app/oracle/admin/layatest/bdump
mkdir -p /u01/app/oracle/admin/layatest/cdump
mkdir -p /u01/app/oracle/admin/layatest/udump
mkdir -p /u01/app/oracle/admin/layatest/pfile


2) Create a parameter file.


###########################################
log_archive_dest_1='LOCATION=+DATA2/'
log_archive_format=%t_%s_%r.dbf
db_block_size=8192
db_file_multiblock_read_count=16
open_cursors=300
db_name=layatest
background_dump_dest=/u01/app/oracle/admin/layatest/bdump
core_dump_dest=/u01/app/oracle/admin/layatest/cdump
user_dump_dest=/u01/app/oracle/admin/layatest/udump
db_create_file_dest=+DATA1
compatible=10.2.0.3.0
processes=150
sga_target=285212672
audit_file_dest=/u01/app/oracle/admin/layatest/adump
remote_login_passwordfile=exclusive
pga_aggregate_target=94371840
undo_management=AUTO
control_files=("+DATA1","+DATA3")
cluster_database_instances=2
#cluster_database=true
layatest1.instance_number=1
layatest2.instance_number=2
layatest2.thread=2
layatest1.undo_tablespace=UNDOTBS1
layatest1.thread=1


3) Create password file in one node and copy that to all the nodes.


rhelrac1-> cd $ORACLE_HOME/dbs
rhelrac1-> orapwd file=orapwlayatest1 password=admin entries=5
rhelrac1-> scp orapwlayatest1 oracle@rhelrac2:/u01/app/oracle/product/10.2.0/db_1/dbs/orapwlayatest2


4) Create spfile using the pfile and the edit the init ora parameter to point the spfile in both the instance.



rhelrac1-> export ORACLE_SID=+ASM1

rhelrac1-> asmcmd

ASMCMD> lsdg

State Type Rebal Unbal Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Name
MOUNTED EXTERN N N 512 4096 1048576 3858 3760 0 3760 0 DATA1/
MOUNTED NORMAL N N 512 4096 1048576 3765 3483 1255 1114 0 DATA2/
MOUNTED EXTERN N N 512 4096 1048576 3000 2903 0 2903 0 DATA3/
MOUNTED EXTERN N N 512 4096 1048576 3858 3760 0 3760 0 FLASH/
MOUNTED EXTERN N N 512 4096 1048576 1137 1044 0 1044 0 ORADATA/
MOUNTED NORMAL N N 512 4096 1048576 1928 1742 0 871 0 ORANORMAL/

ASMCMD> cd data2
ASMCMD> ls
ASMCMD> mkdir layatest
ASMCMD> cd layatest
ASMCMD> mkdir spfile
ASMCMD> cd spfile
ASMCMD> pwd
+data2/layatest/spfile

Crate the spfile in the above location +DATA2/layatest/spfile

rhelrac1-> export ORACLE_SID=layatest1
rhelrac1-> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 17 14:47:25 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> create spfile='+DATA2/layatest/spfile/spfilelayatest.ora' from pfile='/u01/app/oracle/admin/layatest/pfile/initlayatest.ora';

File created.

SQL> exit

Now edit the pfiles in the oracle home location and point to the spfile. Repeat the same in all the nodes.

rhelrac1-> cd $ORACLE_HOME/dbs
rhelrac1-> vi initlayatest1.ora
spfile=+DATA2/layatest/spfile/spfilelayatest.ora

:wq!


rhelrac2-> cd $ORACLE_HOME/dbs
rhelrac2-> vi initlayatest2.ora
spfile=+DATA2/layatest/spfile/spfilelayatest.ora



4) Startup the instance in node1.


rhelrac1-> export ORACLE_SID=layatest1
rhelrac1-> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 17 14:52:30 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 92277380 bytes
Database Buffers 188743680 bytes
Redo Buffers 2924544 bytes

SQL> create database layatest
2 datafile '+DATA1' size 500m extent management local
3 sysaux datafile '+DATA1' size 500m
4 default temporary tablespace temp tempfile '+DATA1' size 100m
5 undo tablespace undotbs1 datafile '+DATA1' size 100m
6 logfile
7 group 1 ('+DATA1','+DATA2') size 10m,
8 group 2 ('+DATA1','+DATA2') size 10m
9 /


Database created.


SQL> @?/rdbms/admin/catalog.sql;

PL/SQL procedure successfully completed.

SQL> @?/rdbms/admin/catproc.sql;

PL/SQL procedure successfully completed.

-- Create undo tablespace for the 2nd node.

SQL> create undo tablespace undotbs2 datafile '+DATA1' size 100m;

Tablespace created.

SQL> alter system set undo_tablespace='UNDOTBS2' scope=spfile sid='layatest2';

System altered.

-- Enable the cluster database.

SQL> alter system set cluster_database=true scope=spfile sid='*';

System altered.

--Create online redo log file for the 2nd node.

SQL> alter database add logfile thread 2 group 3 ('+DATA1','+DATA3') size 10m;

Database altered.

SQL> alter database add logfile thread 2 group 4 ('+DATA1','+DATA3') size 10m;

Database altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.



--Startup the database.

SQL> startup
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 96471684 bytes
Database Buffers 184549376 bytes
Redo Buffers 2924544 bytes
Database mounted.
Database opened.


-- Enable the thread 2

SQL> alter database enable public thread 2;

Database altered.

--Create RAC specific Views.

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

PL/SQL procedure successfully completed.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.


SQL> exit


-- Register the databse in OCR.

rhelrac1-> srvctl add database -d layatest -o /u01/app/oracle/product/10.2.0/db_1 -p +DATA2/layatest/spfile/spfilelayatest.ora

rhelrac1-> srvctl add instance -d layatest -i layatest1 -n rhelrac1

rhelrac1-> srvctl add instance -d layatest -i layatest2 -n rhelrac2

rhelrac1-> srvctl config database -d layatest

rhelrac1 layatest1 /u01/app/oracle/product/10.2.0/db_1
rhelrac2 layatest2 /u01/app/oracle/product/10.2.0/db_1


rhelrac1-> srvctl start database -d layatest

rhelrac1-> srvctl status database -d layatest

Instance layatest1 is running on node rhelrac1
Instance layatest2 is running on node rhelrac2

--End of Database creation.


--Enable the archive log mode.


SQL> archive log list;
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination +DATA2
Oldest online log sequence 42
Current log sequence 43

--Disable the cluster database.

SQL> alter system set cluster_database = false scope=spfile sid='layatest1';

System altered.

SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> exit

-- Stop the database and mount the database in the node1

rhelrac1-> srvctl stop database -d layatest

-- Mount the database exclusively in node 1

rhelrac1-> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 17 15:32:47 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.

Connected to an idle instance.

SQL> startup mount;
ORACLE instance started.

Total System Global Area 285212672 bytes
Fixed Size 1267068 bytes
Variable Size 96471684 bytes
Database Buffers 184549376 bytes
Redo Buffers 2924544 bytes
Database mounted.

--enable archive log

SQL> alter database archivelog;

Database altered.

--Enable the cluster database.

SQL> alter system set cluster_database = true scope=spfile sid='layatest1';

System altered.

SQL> shut immediate;
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> exit
Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
rhelrac1-> srvctl start database -d layatest
rhelrac1-> sqlplus / as sysdba

SQL*Plus: Release 10.2.0.4.0 - Production on Sat Mar 17 15:35:01 2012

Copyright (c) 1982, 2007, Oracle. All Rights Reserved.


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options

SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination +DATA2
Oldest online log sequence 42
Next log sequence to archive 43
Current log sequence 43
SQL>exit

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

Setting local listeners:-
------------------------


alter system set local_listener = 'listener_layatest1' scope=spfile sid='layatest1';
alter system set local_listener = 'listener_layatest2' scope=spfile sid='layatest2';

srvctl stop database -d layatest


tnsnames.ora on both nodes
--------------------------

layatest1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = rhelrac1-vip) (PORT = 1532))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = layatest) (INSTANCE_NAME = layatest1))
)

listener_layatest1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = rhelrac1-vip) (PORT = 1532))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = layatest) (INSTANCE_NAME = layatest1))
)
layatest2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = rhelrac2-vip) (PORT = 1532))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = layatest) (INSTANCE_NAME = layatest2))
)
listener_layatest2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = rhelrac2-vip) (PORT = 1532))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = layatest) (INSTANCE_NAME = layatest2))
)
layatest =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP) (HOST = rhelrac1-vip) (PORT = 1532))
(ADDRESS = (PROTOCOL = TCP) (HOST = rhelrac2-vip) (PORT = 1532))
(LOAD_BALANCE = yes)
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = layatest) (INSTANCE_NAME = layatest1))
(CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = layatest2 (INSTANCE_NAME = layatest2))
)

Listener.ora in node1
---------------------

lsnrlayatest_rhelrac1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC) (KEY = extproc_layatest1))
(ADDRESS = (PROTOCOL = TCP)(HOST = rhelrac1-vip)(PORT = 1532) (IP = FIRST))
)
)
ADMIN_RESTRICTIONS_lsnrlayatest_rhelrac1=ON


--------------------------------------------
Start the listener:-

lsnrctl start lsnrlayatest_rhelrac1



Listener.ora in node2
---------------------

lsnrlayatest_rhelrac2 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC) (KEY = extproc_layatest2))
(ADDRESS = (PROTOCOL = TCP) (HOST = rhelrac2-vip) (PORT = 1532) (IP = FIRST))
)
)
ADMIN_RESTRICTIONS_lsnrlayatest_rhelrac2=ON

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

Start the Listener

lsnrctl start lsnrlayatest_rhelrac2
-----------------------------------


start the database.
-------------------

srvctl start database -d layatest