This article shows you steps to create an Oracle database 11g manually from the command line on Linux.
Operating System Version: Red Hat Enterprise Linux AS release 4 (Nahant Update 8)
Oracle Database Version: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0
In this database creation we will be using the below features:
a. OMF (Oracle Managed File) for datafiles, redolog files and controlfiles
b. FRA (Flash Recovery Area) for Archivelog or backup files
1. Decide on Your Instance Identifier (SID)
> An instance is made up of the system global area (SGA) and the background processes of an Oracle Database. Decide on a unique Oracle system identifier (SID) for your instance and set the ORACLE_SID environment variable accordingly. This identifier is used to distinguish this instance from other Oracle Database instances that you may create later and run concurrently on your system.
2. Set Environment Variables such as ORACLE_HOME, ORACLE_SID
The following sets the SID for the instance and database we are about to create and the ORACLE_HOME:
[oracle@oracle11g ~]$ export ORACLE_SID=ORCL11G
[oracle@oracle11g ~]$ export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_2
3. Create Required Directories
- For TRACE FOLDER,
[oracle@oracle11g ~]$ mkdir -p /u01/app/oracle
- For Default Datafile Location,
[oracle@oracle11g ~]$ mkdir -p /u02/oradata
- For Flash Recovery Area (FRA)
[oracle@oracle11g ~]$ mkdir -p /u02/flash_recovery_area
4. Choose a Database Administrator Authentication Method
There are two authentication methods that we can use.
- Password Authentication method
We need to create PASSWORD file through ORAPWD utility.
- OS Authentication method
For OS Authentication method OS user must be member of OS DBA Group.
And SQLNET.AUTHENTICATION_SERVICES= (NTS) is set in SQLNET.ORA file
Note: We will use Password Authentication method
5. Create the password file for the database.
Syntax: orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=<password> entries=5
[oracle@oracle11g ~]$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=5
6. Create the Initialization Parameter File
The instance for any Oracle Database is started using an initialization parameter file.
For convenience, store your initialization parameter file in the Oracle Database default location, using the default name.
[oracle@oracle11g ~]$ vi $ORACLE_HOME/dbs/init$ORACLE_SID.ora
7. Connect to the Instancedb_name = 'ORCL11G'
#Database Name
db_create_file_dest='/u02/oradata/'
#OMF Configuration for Datafiles and Controlfiles
db_create_online_log_dest_1='/u02/oradata/'
#OMF Configuration for Redolog file
db_recovery_file_dest='/u02/flash_recovery_area/'
db_recovery_file_dest_size=1G
#FRA (Flash Recovery Area Configuration)
diagnostic_dest='/u01/app/oracle'
#It is new feature with 11g for trace files (bdump,udump,cdump or many others
#folder created in "DIAG" folder inside "/u01/app/oracle" folder.
Start SQL*Plus and connect to your Oracle Database instance AS SYSDBA.
[oracle@oracle11g ~]$ sqlplus '/as sysdba'
8. Create a Server Parameter File (Recommended)
SQL> CREATE SPFILE='/u01/app/oracle/product/11.2.0/dbhome_2/dbs/spfileORCL11G.ora' FROM PFILE='/u01/app/oracle/product/11.2.0/dbhome_2/dbs/initORCL11G.ora';
9. Start the InstanceSQL> CREATE SPFILE='/u01/app/oracle/product/11.2.0/dbhome_2/dbs/spfileORCL11G.ora' FROM PFILE='/u01/app/oracle/product/11.2.0/dbhome_2/dbs/initORCL11G.ora';
File created.
SQL>
Start an instance without mounting a database.
SQL> startup nomount;
10. Create the DatabaseSQL> startup nomount;
ORACLE instance started.
Total System Global Area 146472960 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 2531328 bytes
SQL>
Issue the CREATE DATABASE Statement.
The following statement creates database ORCL11G:
SQL> CREATE DATABASE ORCL11G USER SYS IDENTIFIED BY &&SYS_SYSTEM_Password USER SYSTEM IDENTIFIED BY &&SYS_SYSTEM_Password UNDO TABLESPACE UNDOTBS1 DEFAULT TABLESPACE users DEFAULT TEMPORARY TABLESPACE TEMP;
11. Run Scripts to Build Data Dictionary ViewsSQL> CREATE DATABASE ORCL11G USER SYS IDENTIFIED BY &&SYS_SYSTEM_Password USER SYSTEM IDENTIFIED BY &&SYS_SYSTEM_Password UNDO TABLESPACE UNDOTBS1 DEFAULT TABLESPACE users DEFAULT TEMPORARY TABLESPACE TEMP;
Enter value for sys_system_password: oracle
old 1: CREATE DATABASE ORCL11G USER SYS IDENTIFIED BY &&SYS_SYSTEM_Password USER SYSTEM IDENTIFIED BY &&SYS_SYSTEM_Password UNDO TABLESPACE UNDOTBS1 DEFAULT TABLESPACE users DEFAULT TEMPORARY TABLESPACE TEMP
new 1: CREATE DATABASE ORCL11G USER SYS IDENTIFIED BY oracle USER SYSTEM IDENTIFIED BY oracle UNDO TABLESPACE UNDOTBS1 DEFAULT TABLESPACE users DEFAULT TEMPORARY TABLESPACE TEMP
Database created.
SQL>
[oracle@oracle11g ]$ sqlplus '/as sysdba'
SQL> set echo on
SQL> spool ?/log/CreateDBCatalog.log
SQL> @?/rdbms/admin/catalog.sql
SQL> @?/rdbms/admin/catblock.sql
SQL> @?/rdbms/admin/catproc.sql
SQL> connect system/&&systemPassword
SQL> @?/sqlplus/admin/pupbld.sql
SQL> @?/sqlplus/admin/help/hlpbld.sql helpus.sql
SQL> spool off
SQL> exit
catalog.sql - Creates the views of the data dictionary tables, the dynamic performance views, and public synonyms for many of the views. Grants PUBLIC access to the synonyms.
catproc.sql - Runs all scripts required for or used with PL/SQL.
12. Update oratab
Add a new entry in /etc/oratab
[oracle@oracle10g ~]$ vi /etc/oratab
Syntax: $ORACLE_SID:$ORACLE_HOME:<N|Y>
ORCL11G:/u01/app/oracle/product/11.2.0/dbhome_2:N
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production