How to Create Manual Database in Oracle ASM File-system using Cloud Instance

Description:

The recommended method of creating your database is to use database configuration assistant (dbca). however, if you create your database manually using the create database statement, then oracle asm enables you to create a database.
We have oracle binaries and Grid infrastructure already installed in this server.
We are going to create database on existing Asm file system
We are using Create Database command to create a new oracle database on ASM file system from the command line.
This method is helpful when we don’t have console access to the server to use the DBCA .

Steps for Manual Oracle 12cR2 Database creation on ASM filesystem

Step 1: Check the Services are up and running

crsctl status resource -t

Step 2: Login as ASM instance and check the filesystem is mounted .

Firstly we have to check the ASM file system is mounted or not if its not mounted the asm file system we do not create the database so it’s must started the CRS services then start the database creation.

SQL> select name,block_size,allocation_unit_size,state,type,total_mb,free_mb from v$asm_diskgroup;

Step 3: Create the Initialization Parameter File in dbs location.

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.audit_trail='db'

*.compatible='12.2.0'

*.control_files='+DATA'

*.db_block_size=8192

*.db_create_file_dest='+DATA'

*.db_name='orcl'

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'

*.filesystemio_options='setall'

*.local_listener=LISTENER_ORCL

*.job_queue_processes=1000

*.log_buffer=10485760

*.nls_language='AMERICAN'

*.nls_territory='AMERICA'

*.open_cursors=300

*.pga_aggregate_target=2981m

*.processes=500

*.remote_login_passwordfile='EXCLUSIVE'

*.session_cached_cursors=300

*.sga_target=8940m

*.undo_tablespace='UNDOTBS1'

[oracle@inba ~]$ cd /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs

[oracle@inba ~]$ export ORACLE_SID=orcl

[oracle@inba ~]$ export ORACLE_HOME=$DB_HOME

[oracle@inba ~]$ export PATH=$ORACLE_HOME/bin:$BASE_PATH

[oracle@inba ~]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

[oracle@inba ~]$ export

CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

[oracle@inba ~]$

[oracle@inba ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Tue May 25 15:31:14 2021

Connected to an idle instance.

SQL> startup nomount pfile=’/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initorcl.ora’

ORA-09925: Unable to create audit trail file

Linux-x86_64 Error: 2: No such file or directory

Additional information: 9925

Step 4: create audit trail file adump directory because we are facing that error ORA-09925

[oracle@inba ~]$ cd /u01/app/oracle/admin/orcl

[oracle@inba orcl]$ ls

xdb_wallet

[oracle@inba orcl]$ mkdir adump

[oracle@inba orcl]$ ls -lrth

total 8.0K

drwxr-xr-x. 2 oracle asmadmin 4.0K May 25 11:07 xdb_wallet

drwxr-xr-x. 2 oracle oinstall 4.0K May 25 15:41 adump

[oracle@inba orcl]$ cd

Step 5: startup the database in no mount stage. Though we haven’t created database yet, here we’re checking if database starts up on created pfile.

export ORACLE_SID=orcl

export ORACLE_HOME=$DB_HOME

export PATH=$ORACLE_HOME/bin:$BASE_PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib:/usr/lib

export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib

sqlplus / as sysdba

SQL> startup nomount pfile=’/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initorcl.ora’

Step 6: Now the database to add in ASM .

[oracle@inba ~]$ srvctl add database -d orcl -o /u01/app/oracle/product/12.2.0.1/dbhome_1

Step 7: Execute CREATE DATABASE command to creates data files, control files, redo log files, the system tablespace along with the data file associated with it.

CREATE DATABASE orcl

MAXLOGFILES 5

MAXLOGMEMBERS 3

MAXDATAFILES 200

MAXINSTANCES 1

LOGFILE

GROUP 1 (‘+DATA’) SIZE 100M,

GROUP 2 (‘+DATA’) SIZE 100M,

GROUP 3 (‘+DATA’) SIZE 100M

DATAFILE ‘+DATA’ SIZE 3000M EXTENT MANAGEMENT LOCAL SYSAUX DATAFILE ‘+DATA’ SIZE 2000M UNDO TABLESPACE UNDOTBS1 DATAFILE ‘+DATA’ SIZE 300M AUTOEXTEND OFF DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE ‘+DATA’ SIZE 200M REUSE AUTOEXTEND OFF

CHARACTER SET AL32UTF8;

Step 8: Check the database mode and created datafile & logfile

select name,open_mode from v$database;

select name from v$datafile;

select member from v$logfile;

Now the database contains data files , control files, redo log files, the SYS and SYSTEM users, and all the internal data dictionary tables, but No data dictionary views created.so we do run the scripts to build data dictionary views

Step 9 : In that ORACLE_HOME/rdbms/admin directory, a scripts need to be run to complete the database data dictionary creation.so below scripts to build data dictionary views.

@?/rdbms/admin/catalog.sql; —–>CATALOG.sql creates the data dictionary views

@?/rdbms/admin/catproc.sql; ——>CATPROC.sql creates the objects required to use PL/SQL.

@?/sqlplus/admin/pupbld.sql; ——>pupbld.sql creates the default roles and profiles.

Step 10:Create a Server parameter file because the database created in pfile.

create spfile from pfile=’/u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/initorcl.ora’;

Step 11: Add a datafile in ASM instance .

create tablespace users datafile ‘+DATA’ SIZE 300M;

select name from v$datafile;

Step 12: Check the status of database running or not in ASM service

[oracle@inba ~]$ srvctl status database -d orcl

Database is not running.

Step 13 : To start the database in ASM service

[oracle@inba ~]$ srvctl start database -d orcl

[oracle@inba ~]$ srvctl status database -d orcl

Database is running.

Step 14: Again to check the status of database in ASM and there are two database created running in ASM.

[oracle@inba ~]$ . grid.env

[oracle@inba ~]$ crsctl status resource -t

[oracle@inba ~]$ ps -ef|grep pmon

We have successfully created manual database in ASM.

Inbaraj Asirvatham