Creating Manual Database in Two NODE RAC with ASM without Using DBCA

Creating Manual Database in Two NODE RAC with ASM without Using DBCA:

 

 

NOTE: In below whole database creation steps shark, shark1 and shark2 are considered as database name, instance1 on node1 and instance2 on node2 respectively.

STEP1:
If Listener is not configured then create listener.ora file on both NODES as below:

NODE-1:

LISTENER_ORA1-PUB1 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora1-vip)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.125.1.110)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)

NODE-2:

LISTENER_ORA1-PUB1 =
DESCRIPTION_LIST =

(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora2-vip)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.125.1.220)(PORT = 1521))
)
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC))
)
)

STEP2:
Create tnsnames.ora file on appropriate locations on both nodes as:

SHARK2 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora2-vip)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = shark)
(INSTANCE_NAME = shark2)
)
)

shark1 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora1-vip)(PORT = 1521))

(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = shark)
(INSTANCE_NAME = shark1)
)
)

SHARK =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = ora2-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = shark)
)
)

DB.SHARK.COM =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = ora1-vip)(PORT = 1521))
(ADDRESS = (PROTOCOL = TCP)(HOST = ora2-vip)(PORT = 1521))
(LOAD_BALANCE = yes)
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = db.shark.com)
)
)

STEP3:
Create password files on both nodes:

STEP4:
Create PFILE on node1 at appropriate location:

NODE 1:

shark1.__db_cache_size=373293056
shark2.__db_cache_size=872415232

shark2.__java_pool_size=16777216
shark1.__java_pool_size=4194304
shark2.__large_pool_size=16777216
shark1.__large_pool_size=4194304
shark1.__shared_pool_size=654311424
shark2.__shared_pool_size=738197504
shark1.__streams_pool_size=8388608
shark2.__streams_pool_size=16777216
*.audit_file_dest='/opt/oracle/admin/shark/adump'
*.background_dump_dest='/opt/oracle/admin/shark/bdump'
*.cluster_database_instances=2
*.cluster_database=false
*.compatible='10.2.0.3.0'
*.control_files='+DATA01/shark/controlfile/control01.dbf','+FLASH/shark/controlfile/control02.dbf'
*.core_dump_dest='/opt/oracle/admin/shark/cdump'
*.db_block_size=8192
*.db_create_file_dest='+DATA01'
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='shark'
*.db_recovery_file_dest='+FLASH'
*.db_recovery_file_dest_size=2147483648
*.dispatchers='(PROTOCOL=TCP) (SERVICE=sharkXDB)'
shark1.instance_number=1
shark2.instance_number=2
*.job_queue_processes=10
*.open_cursors=300
*.pga_aggregate_target=150m
*.processes=150
*.remote_listener='LISTENERS_shark'
*.remote_login_passwordfile='exclusive'
*.sga_target=3000m
shark1.sga_target=300m
shark2.sga_target=300m
shark2.thread=2
shark1.thread=1
*.undo_management='AUTO'
shark1.undo_tablespace='UNDOTBS1'
shark2.undo_tablespace='UNDOTBS2'
*.user_dump_dest='/opt/oracle/admin/shark/udump'

STEP5:
Create appropriate directories as adump,udump etc. as mentioned in pfile.
Startup instance1 on node1 in nomount state:

#Export ORACLE_SID=shark1

#Sqlplus “/as sysdba”

Sql> startup nomount
2 pfile='/opt/oracle/product/db/10.2.0.3/dbs/initshark1.ora';
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 2029720 bytes
Variable Size 100665192 bytes
Database Buffers 201326592 bytes
Redo Buffers 10551296 bytes

Sql> CREATE DATABASE shark
2 MAXINSTANCES 8
3 MAXLOGHISTORY 1
4 MAXLOGFILES 16
5 MAXLOGMEMBERS 3
6 MAXDATAFILES 100
7 DATAFILE '+DATA01/shark/datafile/system01.dbf' SIZE 1G REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL
8 SYSAUX DATAFILE '+DATA01/shark/datafile/sysaux01.dbf' SIZE 500M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
9 DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA01/shark/datafile/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
10 UNDO TABLESPACE "UNDOTBS1" DATAFILE '+DATA01/shark/datafile/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
11 CHARACTER SET UTF8
12 NATIONAL CHARACTER SET AL16UTF16
13 LOGFILE GROUP 1 ('+DATA01/SHARK/ONLINELOG/redo01.log') SIZE 20M,
14 GROUP 2 ('+DATA01/SHARK/ONLINELOG/redo02.log') SIZE 20M,
15 GROUP 3 ('+DATA01/SHARK/ONLINELOG/redo03.log') SIZE 20M
16 USER SYS IDENTIFIED BY shark USER SYSTEM IDENTIFIED BY shark;

STEP6:
Run following Data Dictionary Scripts:

Sql>@$ORACLE_HOME/rdbms/admin/catalog.sql

Sql>@$ORACLE_HOME/rdbms/admin/catproc.sql

Sql>@$ORACLE_HOME/rdbms/admin/catclust.sql

Sql>@$ORACLE_HOME/rdbms/admin/utlrp.sql

STEP7:
Do Following steps:

Sql>create spfile from pfile;
File created.

Sql>shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Sql>startup
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 2029720 bytes
Variable Size 100665192 bytes
Database Buffers 201326592 bytes
Redo Buffers 10551296 bytes
Database mounted.
Database opened.

Sql>alter system set cluster_database=true scope=spfile;
System altered.

Sql>shu immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

Sql>startup
ORACLE instance started.

Total System Global Area 314572800 bytes
Fixed Size 2029720 bytes
Variable Size 109053800 bytes
Database Buffers 192937984 bytes
Redo Buffers 10551296 bytes
Database mounted.
Database opened.

Sql>alter database add logfile thread 2
group 4
('+DATA01/SHARK/ONLINELOG/redo04a.log',
'+DATA01/SHARK/ONLINELOG/redo04b.log.dbf') size 20M reuse,
group 5
('+DATA01/SHARK/ONLINELOG/redo05a.log',
'+DATA01/SHARK/ONLINELOG/redo05b.log') size 20M reuse;

Sql>alter database enable public thread 2;

Sql>create undo tablespace UNDOTBS2 datafile '+DATA01/shark/datafile/undotbs02.dbf' SIZE 200M;

Sql>create pfile from spfile;
File created.

STEP8:
Now copy pfile and password file from node1 to node2 at appropriate location and required changes on node2 if needed.

STEP9:
On NODE2:

# export ORACLE_SID=shark2

#sqlplus “/as sysdba”

Sql> startup
ORACLE instance started.
Total System Global Area 314572800 bytes
Fixed Size 2029752 bytes
Variable Size 100665160 bytes
Database Buffers 205520896 bytes
Redo Buffers 6356992 bytes
Database mounted.
Database opened.

STEP10:
Now Update srvctl:

# srvctl add database -d shark -o $ORACLE_HOME

# srvctl add instance -d shark -i shark1 -n ora1-pub1

# srvctl add instance -d shark -i shark2 -n ora2-pub1

# srvctl enable database -d shark

# srvctl enable instance -d shark -i shark1

# srvctl enable instance -d shark -i shark2

# srvctl stop database -d shark

# srvctl start database -d shark

Now check state of both node instances:

# $CRS_HOME/bin/crs_stat –t
 

 

You can skip to the end and leave a response. Pinging is currently not allowed.

Leave a Reply