Create RAC database manually

[binhminhitc]

It is always recommended to create 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)

Code: Select all

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:

Code: Select all

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:

Code: Select all

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 node:

Code: Select all

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:

Code: Select all

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:

Code: Select all

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:

Code: Select all

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:

Code: Select all

@/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:

Code: Select all

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

10. Enable archive log mode:

Code: Select all

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:

Code: Select all

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.

Code: Select all

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

13. Create spfile in diskgroup:

Code: Select all

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

14.Update the initDBATST1.ora in dbs location

Code: Select all

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

15. Run utlrp.sql

Code: Select all

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

16. Shutdown database:

Code: Select all

shutdown immediate;

17. add the database to CRS:

Code: Select all

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:

Code: Select all

srvctl start database -d DBATST