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)
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 node:
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
Thnaks.. very usefull.