Manually Creating a RAC Database ( RAC Database Creation without DBCA)

Manually Creating a RAC Database ( RAC Database Creation without DBCA)

The article assumes that you already have a clustered ASM installation up and running on Linux, so let's get going.

My database is called DEMO, instances are DEMO1 and DEMO2. My hostnames are rac1 and rac2

1.Initial init.ora
Go to $ORACLE_HOME/dbs and create a simple init.ora file as follows:

control_files='+DATA/DEMO/CONTROLFILE/control01.ctl'
*.audit_file_dest='/u01/app/oracle/admin/DEMO/adump'
*.background_dump_dest='/u01/app/oracle/admin/DEMO/bdump'
*.compatible='10.2.0.1.0'
*.core_dump_dest='/u01/app/oracle/admin/DEMO/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='DEMO'
*.db_recovery_file_dest='+DATA'
*.db_recovery_file_dest_size=2G
*.sga_target = 250M
*.job_queue_processes=10
*.log_checkpoints_to_alert=TRUE
*.pga_aggregate_target=100M
*.processes=500
*.remote_listener='LISTENERS_DEMO'
*.remote_login_passwordfile='exclusive'
*.sessions=200
*.undo_management='AUTO'
*.user_dump_dest='/u01/app/oracle/admin/DEMO/udump'
DEMO1.instance_name = DEMO1

2.You also need to create a password file

[oracle@rac1 dbs]$ pwd
/u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@rac1 dbs]$ orapwd file=orapwDEMO1 password=oracle entries=5

also in node rac2 machine,

[oracle@rac2 dbs]$ orapwd file=orapwDEMO2 password=oracle entries=5

add a entries in /etc/oratab file

[oracle@rac1 dbs]$ vi /etc/oratab
DEMO1:/u01/app/oracle/product/10.2.0/db_1:N

also in node rac2 /etc/oratab file
DEMO2:/u01/app/oracle/product/10.2.0/db_1:N

3.Network configuration

add the following entries in both machines rac1 and rac2.

Now go to $ORACLE_HOME/network/admin and edit tnsnames.ora to add entries for your instances, the database and the listeners.

add in the listener.ora file entry on both nodes.

LISTENERS_DEMO =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
)

add in the tnsnames.ora file entry on both nodes.

DEMO =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DEMO)
)
)
DEMO2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DEMO)
(INSTANCE_NAME = DEMO2)
)
)

DEMO1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = DEMO)
(INSTANCE_NAME = DEMO1)
)
)

LISTENERS_DEMO =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = rac1-vip.localdomain)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = rac2-vip.localdomain)(PORT = 1521))
)

4.Start the instance and create the database

[oracle@rac1 ~]$ . oraenv
ORACLE_SID = [DEMO1] ? DEMO1
[oracle@rac1 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 23 12:00:23 2010

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


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

SQL> startup nomount
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                2973696 bytes

Then run the create database script, an example is given below:

CREATE DATABASE DEMO
MAXINSTANCES 8
MAXLOGHISTORY 100
MAXLOGFILES 64
MAXLOGMEMBERS 3
MAXDATAFILES 150
DATAFILE SIZE 300M AUTOEXTEND ON NEXT 10240K MAXSIZE 1024M EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE SIZE 200M AUTOEXTEND ON NEXT 10240K MAXSIZE 800M
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE 1000M
EXTENT MANAGEMENT LOCAL
UNDO TABLESPACE UNDOTBS1 DATAFILE SIZE 200M AUTOEXTEND ON NEXT 10M MAXSIZE 1000M
LOGFILE
GROUP 1 SIZE 50M,
GROUP 2 SIZE 50M,
GROUP 3 SIZE 50M
/

SQL> @/home/oracle/Desktop/create.sql

Database created.

You should create a user tablespace and make it the database default tablespace to avoid objects "accidentally" stored
in SYSTEM or SYSAUX.

SQL> CREATE TABLESPACE USERS DATAFILE SIZE 5M;

Tablespace created.

SQL> alter database default tablespace users;

Database altered.

A few more steps are now necessary to convert your single instance to a RAC database. Edit initDEMO1.ora and add
the cluster parameters:

*.cluster_database_instances=2
*.cluster_database=true
DEMO1.instance_number=1
DEMO2.instance_number=2
DEMO2.thread=2
DEMO1.thread=1
*.undo_management='AUTO'
DEMO1.undo_tablespace='UNDOTBS1'
DEMO2.undo_tablespace='UNDOTBS2'
DEMO1.instance_name = DEMO1
DEMO1.instance_name = DEMO2

This concludes the init.ora moification. In our case we have both information about the local and remote node in it, which makes it easy to copy the file across to node2 (and rename it to initDEMO2.ora). Now create the second undo tablespace:

SQL> CREATE UNDO TABLESPACE UNDOTBS2 DATAFILE SIZE 200M;

Tablespace created.

SQL> ALTER DATABASE ADD LOGFILE THREAD 2
2  GROUP 4 SIZE 50M;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE THREAD 2
2  GROUP 5 SIZE 50M,
3  GROUP 6 SIZE 50M;

Database altered.

Issue a "shutdown immediate" now, then start the instance. It should come up ok, you'll find additional information in the alert.log about RAC specifics. The important bit is the message stating that the database is mounted in shared mode
(CLUSTER_DATABASE=TRUE).

SQL> SHUT IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP MOUNT
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                2973696 bytes
Database mounted.
SQL> SELECT NAME FROM V$CONTROLFILE;

NAME
--------------------------------------------------------------------------------
+DATA/demo/controlfile/control01.ctl

SQL> ALTER DATABASE ENABLE PUBLIC THREAD 2;
ALTER DATABASE ENABLE PUBLIC THREAD 2
*
ERROR at line 1:
ORA-01109: database not open


SQL> SHOW PARAMETER CLUSTER_DATABASE;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
cluster_database                     boolean     TRUE
cluster_database_instances           integer     2
SQL> ALTER DATABASE OPEN;

Database altered.

SQL> ALTER DATABASE ENABLE PUBLIC THREAD 2;

Database altered.


SQL> SELECT INSTANCE_NAME FROM gv$instance;

INSTANCE_NAME
----------------
DEMO1
DEMO2

SQL> archive log list;
Database log mode              No Archive Mode
Automatic archival             Disabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1
Current log sequence           1
SQL>


[oracle@rac2 ~]$ . oraenv
ORACLE_SID = [oracle] ? DEMO2
[oracle@rac2 ~]$ dbhome
/u01/app/oracle/product/10.2.0/db_1
[oracle@rac2 ~]$ sqlplus '/as sysdba'

SQL*Plus: Release 10.2.0.1.0 - Production on Fri Jul 23 12:17:38 2010

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

Connected to an idle instance.

SQL> startup mount
ORACLE instance started.

Total System Global Area  264241152 bytes
Fixed Size                  1218868 bytes
Variable Size              88082124 bytes
Database Buffers          171966464 bytes
Redo Buffers                2973696 bytes
ORA-01618: redo thread 2 is not enabled - cannot mount


SQL> alter database mount;

Database altered.

SQL> select instance_name from gv$instance;

INSTANCE_NAME
----------------
DEMO1
DEMO2

SQL>

I don't recmmend running RAC with pfiles, that never has been a good idea. Rather, I'd create the spfile in ASM:

SQL> create spfile='+DATA/DEMO/spfiledemo.ora' from pfile;

File created.

Run Data Dictionary Scripts:

@$ORACLE_HOME/rdbms/admin/catalog.sql
@$ORACLE_HOME/rdbms/admin/catproc.sql
@$ORACLE_HOME/rdbms/admin/catclust.sql
@$ORACLE_HOME/rdbms/admin/utlrp.sql

Shut down both instances next and register the database and its instances with Clusterware as oracle:

[oracle@rac1 bdump]$ cd /u01/crs/oracle/product/10.2.0/crs/bin/
[oracle@rac1 bin]$ ./srvctl add database -d DEMO -o $ORACLE_HOME
[oracle@rac1 bin]$ ./srvctl add instance -d DEMO -i DEMO1 -n rac1
[oracle@rac1 bin]$ ./srvctl add instance -d DEMO -i DEMO2 -n rac2
[oracle@rac1 bin]$ ./srvctl start database -d DEMO
[oracle@rac1 bin]$

[root@rac2 bin]# ./crs_stat -t
Name           Type           Target    State     Host
------------------------------------------------------------
ora....O1.inst application    ONLINE    ONLINE    rac1
ora....O2.inst application    ONLINE    ONLINE    rac2
ora.DEMO.db    application    ONLINE    ONLINE    rac2
ora.jay.db     application    ONLINE    ONLINE    rac1
ora....y1.inst application    ONLINE    ONLINE    rac1
ora....y2.inst application    ONLINE    ONLINE    rac2
ora....SM1.asm application    ONLINE    ONLINE    rac1
ora....C1.lsnr application    ONLINE    ONLINE    rac1
ora.rac1.gsd   application    ONLINE    ONLINE    rac1
ora.rac1.ons   application    ONLINE    ONLINE    rac1
ora.rac1.vip   application    ONLINE    ONLINE    rac1
ora....SM2.asm application    ONLINE    ONLINE    rac2
ora....C2.lsnr application    ONLINE    ONLINE    rac2
ora.rac2.gsd   application    ONLINE    ONLINE    rac2
ora.rac2.ons   application    ONLINE    ONLINE    rac2
ora.rac2.vip   application    ONLINE    ONLINE    rac2
[root@rac2 bin]#

hope, this will helps you,