Day 2 activity of the DBA’s, after setting up the database server and configured all the required privileges and applying the patch, we will create a new database
Developers and SysAdmins also should know to create database.
Creating database from DBCA by using the GUI is very straight forward and simple. But if you don’t have console access to the server to launch DBCA or the Xterm setup is not in your OS, you will relay on command like tool to create database
Setup the appropirate envionment variables
Mainly you need to setup the ORACLE_BASE and ORACLE_HOME, refer my recent blog post to set the environment variable in your operating system
In addition to that, one main variable is ORACLE_SID, which will have the name of the oracle database that you like to create
In this post, i use vignesh as database name
Create the init file ( INI file)
Next, create an ora file for your new database, this is the initialisation file for your new database
Depending on the version of the installed oracle, you might see a sample init.ora file in your $ORACLE_HOME/dbs location, you can use that as a baseline for your ora file
cd $ORACLE_HOME/dbs cp init.ora initvignesh.ora
Note: Make sure the file name must be init<ORACLESID>.ora, so in my case initvignesh.ora
Copy and edit the file
- Make sure you set the db_name same as ORACLE_SID, in my case it is vignesh
- The name you provide for the undo_tablespace should be used exactly same in create command
- Change the directory location according to your server
Here is my sample ora file
db_name='vignesh' memory_target=1G processes = 150 audit_file_dest='/u01/app/oracle/db/admin/vignesh/adump' audit_trail ='db' db_block_size=8192 db_domain='' db_recovery_file_dest='/u01/app/oracle/db/fast_recovery_area' db_recovery_file_dest_size=2G diagnostic_dest='/u01/app/oracle/db' dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)' open_cursors=300 remote_login_passwordfile='EXCLUSIVE' undo_tablespace='vigneshundo' You may want to ensure that control files are created on separate physical devices control_files = (ora_control1, ora_control2) compatible ='11.2.0'
Create Serve Parameter file (spfile)
SP file stands for Server Parameter file. Unlike the init file sp file is a binary file, and you can’t edit the spfile manually. You can change the initialization parameter values after you start the database using the ALTER SYSTEM command.
To create a SP file for our new database, use the following command.
[[email protected] dbs]$ sqlplus / as sysdba SQL*Plus: Release 18.104.22.168.0 Production on Mon Feb 3 12:55:37 2020 Copyright (c) 1982, 2016, Oracle. All rights reserved. Connected to an idle instance. SQL>
SQL> CREATE SPFILE FROM PFILE; File created.
Start the Idle Instance using the initvignesh.ora file
SQL> STARTUP NOMOUNT ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 8628936 bytes Variable Size 738198840 bytes Database Buffers 318767104 bytes Redo Buffers 8146944 bytes SQL>
At this point, there is no database. Only an SGA and background processes are started in preparation for the creation of a new database.
During the above command, it will read the default spfile with the name spfile<ORACLE_SID>.ora ( in my case initvignesh.ora ) from the default spfile location $ORACLE_HOME/dbs. If the spfile is not there, it will use the default init file init.ora
Some common error and fix
If you are getting ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
Make sure the folder audit_file_dest value exist and writable
If you are getting error ORA-01261: Parameter db_recovery_file_dest destination string cannot be translated
Make sure the folder exist and writable, still facing the same error Comment the db_recovery_file_dest line or delete the line and try again
Create a new database
When you execute a CREATE DATABASE statement, Oracle performs the following operations:
- Creates the data files for the database
- Creates the control files for the database
- Creates the redo log files for the database
- Creates the SYSTEM tablespace and the SYSTEM rollback segment
- Creates the data dictionary
- Creates the users SYS and SYSTEM
- Specifies the character set that stores data in the database
- Mount and Open the database for use
CREATE DATABASE vignesh UNDO TABLESPACE vigneshundo DATAFILE '/u02/oradata/vignesh/vigneshundo01.dbf' SIZE 100M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
You can specify the log file and data file location and size as well, make sure that the datafile and redo log file names that you specify do not conflict with files of another database. example
CREATE DATABASE vignesh DATAFILE 'vignesh_system' SIZE 10M LOGFILE GROUP 1 ('vignesh_log1a', 'vignesh_log1b') SIZE 500K, GROUP 2 ('vignesh_log2a', 'vignesh_log2b') SIZE 500K;
Triggered the SHUTDOWN command to dismount and shutdown and startup
SQL> SHUTDOWN IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. SQL>
SQL> STARTUP ORACLE instance started. Total System Global Area 1073741824 bytes Fixed Size 8628936 bytes Variable Size 738198840 bytes Database Buffers 318767104 bytes Redo Buffers 8146944 bytes Database mounted. Database opened.
Also published on Medium.