Steps for Create RAC Database Manually

Manual Creation of Database is a big task for DBA. This is the initial steps for setup the IT project. We know that we can create the database in two ways one is DBCA and another is  Manual. Creation of Database in RAC environment different from the Standalone database. In this post we are going have look on those steps which are used to Create RAC Database Manually.

It is always recommended to create a database in RAC using DBCA only (GUI). But for some reason, if  you want to create manually, then follow below steps:

In this article, we will create a database in a 2 node RAC. Database name will be DBATST with instance names DBATST1,DBATST2.

1. Prepare a init file:(as below)

cat /oracle/app/oracle/admin/DBATST/scripts/init.ora

log_archive_dest_1='LOCATION=+B2BWMARC/'
log_archive_format=%t_%s_%r.dbf
db_block_size=8192
open_cursors=300
db_domain=""
db_name="DBATST"
control_files=("+DATA/DBATST/control01.ctl", "+DATA/DBATST/control02.ctl")
compatible=12.1.0.2.0
diagnostic_dest=/oracle/app/oracle
memory_target=11151m
processes=1200
audit_file_dest="/oracle/app/oracle/admin/DBATST/adump"
audit_trail=db
remote_login_passwordfile=exclusive
DBATST2.instance_number=2
DBATST1.instance_number=1
DBATST2.thread=2
DBATST1.thread=1
DBATST1.undo_tablespace=UNDOTBS1
DBATST2.undo_tablespace=UNDOTBS2

2. Start the db in Nomount:

export ORACLE_SID=DBATST1

SQL> startup nomount pfile=/oracle/app/oracle/admin/DBATST/scripts/init.ora
ORACLE instance started.

Total System Global Area 1.1710E+10 bytes
Fixed Size 7645328 bytes
Variable Size 6241130352 bytes
Database Buffers 5435817984 bytes
Redo Buffers 25903104 bytes

3. Create the database :

CREATE DATABASE "DBATST"
MAXINSTANCES 32
MAXLOGHISTORY 1
MAXLOGFILES 192
MAXLOGMEMBERS 3
MAXDATAFILES 1024
DATAFILE '+DATA/DBATST/system01.dbf' SIZE 700M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '+DATA/DBATST/sysaux01.dbf' SIZE 550M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
SMALLFILE DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '+DATA/DBATST/temp01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
SMALLFILE UNDO TABLESPACE "UNDOTBS1" DATAFILE '+DATA/DBATST/undotbs101.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
CHARACTER SET AR8ISO8859P6
NATIONAL CHARACTER SET UTF8
LOGFILE GROUP 1 ('+DATA/DBATST/redo01.log') SIZE 50M,
GROUP 2 ('+DATA/DBATST/redo02.log') SIZE 50M
USER SYS IDENTIFIED BY oracle#123 USER SYSTEM IDENTIFIED BY oracle#123;

Database created.

4. Create another undo tablespace for other nodes

SQL> create SMALLFILE UNDO TABLESPACE "UNDOTBS2" DATAFILE '+DATA/DBATST/undotbs201.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED;

Tablespace created.

5. Create USERS tablespace:

SQL> CREATE SMALLFILE TABLESPACE "USERS" LOGGING DATAFILE '+DATA/DBATST/users01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 1280K MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT
AUTO;

Tablespace created.

SQL> ALTER DATABASE DEFAULT TABLESPACE "USERS";

Database altered.

6. Create CATALOG and CATPROC components:

conn / as sysdba

@/oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catalog.sql;
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catproc.sql;
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catoctk.sql;
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/owminst.plb;

7. Run below additional scripts:

connect system/oracle#123

@/oracle/app/oracle/product/12.1.0.2/dbhome_1/sqlplus/admin/pupbld.sql;
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/sqlplus/admin/help/hlpbld.sql helpus.sql;

8. Create JVM component:

@/oracle/app/oracle/product/12.1.0.2/dbhome_1/javavm/install/initjvm.sql;
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/xdk/admin/initxml.sql;
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/xdk/admin/xmlja.sql;
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catjava.sql;
@/oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catxdbj.sql;

9. Create cluster related views:

@/oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/catclust.sql;

10. Enable archive log mode:

shutdown immediate;
startup mount pfile="/oracle/app/oracle/admin/WMBPRE/scripts/init.ora";
alter database archivelog;
alter database open;

11. Add the redo thread for another node :

ALTER DATABASE ADD LOGFILE THREAD 2 GROUP 3 ('+DATA/DBATST/redo03.log') SIZE 50M,
GROUP 4 ('+DATA/DBATST/redo04.log') SIZE 50M;ALTER DATABASE ENABLE PUBLIC THREAD 2;

12. add the cluster_database parameter in init file.

echo cluster_database=true >>/oracle/app/oracle/admin/DBATST/scripts/init.ora

13. Create spfile in diskgroup:

create spfile='+DATA/DBATST/spfileDBATST.ora' FROM pfile='/oracle/app/oracle/admin/DBATST/scripts/init.ora';

14.Update the initDBATST1.ora in dbs location

echo "SPFILE='+DATA/DBATST/spfileDBATST.ora'" > /oracle/app/oracle/product/12.1.0.2/dbhome_1/dbs/initDBATST1.ora

15. Run utlrp.sql

@/oracle/app/oracle/product/12.1.0.2/dbhome_1/rdbms/admin/utlrp.sql;

16. Shutdown database:

shutdown immediate;

17. add the database to CRS:

orapwd file=+DATA/DBATST/orapwDBATST force=y format=12 dbuniquename=DBATST password=oracle
/crsapp/app/oracle/product/grid12c/bin/setasmgidwrap o=/oracle/app/oracle/product/12.1.0.2/dbhome_1/bin/oracle

srvctl add database -d DBATST -pwfile +DATA/DBATST/orapwDBATST -o /oracle/app/oracle/product/12.1.0.2/dbhome_1 -p +DATA/DBATST/spfileDBATST.ora -n DBATST -a "B2BWMDB,B2BWMARC"
srvctl add instance -d DBATST -i DBATST1 -n DBATSTdb1
srvctl add instance -d DBATST -i DBATST2 -n DBATSTdb2

18. Start the database:

srvctl start database -d DBATST

Thank you for giving your valuable time to read the above information.

If you want to be updated with all our articles send us the Invitation or Follow us:

Skant Gupta’s LinkedIn: www.linkedin.com/in/skantali/

Joel Perez’s LinkedIn: www.linkedin.com/in/SirDBaaSJoelPerez

Anuradha’s LinkedIn: https://www.linkedin.com/in/dbaanuradhamudgal/

LinkedIn Group: Oracle Cloud DBAAS

Facebook Page: OracleHelp