How To Create Oracle 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