3. Create Database with Command Line in Oracle 11g

Here we will create database in Oracle 11g with Command Line tools. These are the same commands runs in back-end during "dbca" running.

INGREDIENTS:

        OS                                     =    Oracle Linux 6.8  
        Database software            =    Oracle 11g
        
Now we have a machine installed with Oracle 11g software. Now check the following steps ...

1. Create a environment file for our database "DB200".
    
        [oracle@prod oracle]$    vi incDB200.env
    
 export ORACLE_BASE=/u01/app/oracle
 export ORACLE_HOME=$ORACLE_BASE/product/11.2.0/dbhome_1                   export PATH=$ORACLE_HOME/bin:$PATH                   export ORACLE_SID=DB200    

        [oracle@prod oracle]$  . incDB200.env

2. Create a password file for SYS user.....
        [oracle@prod ~]$ cd $ORACLE_HOME/dbs
        [oracle@prod dbs]$ orapwd file=orapwDB200
                      Enter password for SYS:                                    // Enter password here for "SYS" user
        [oracle@prod dbs]$

3. Create directory environment for database DB200....
       
        [oracle@prod dbs]$  mkdir -p /u01/app/oracle/admin/DB200/adump
        [oracle@prod dbs]$  mkdir -p /u01/app/oracle/oradata/DB200/controlfile
        [oracle@prod dbs]$  mkdir -p /u01/app/oracle/flash_recovery_area/DB200/controlfile

4. Create pfile for database DB200 from sample file init.ora ........
       [oracle@prod dbs]$ cp init.ora initDB200.ora
       [oracle@prod dbs]$ vi initDB200.ora
       
            db_name='DB200'
            memory_target=700m
            processes = 150
            audit_file_dest='/u01/app/oracle/admin/DB200/adump'
            audit_trail ='db'
            db_block_size=8192
            db_domain=''
            db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'
            db_recovery_file_dest_size=2G
            diagnostic_dest='/u01/app/oracle'
            dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'
            open_cursors=300 
            remote_login_passwordfile='EXCLUSIVE'
            undo_tablespace='UNDOTBS1'
 # You may want to ensure that control files are created on separate physical devices
 control_files = (/u01/app/oracle/oradata/DB200/controlfile/control01.ctl, /u01/app/oracle/flash_recovery_area/DB200/controlfile/control02.ctl )
            compatible ='11.2.0'


5. Create script for database creation...

        [oracle@prod dbs]$ vi db11g.sql

    CREATE DATABASE "test1"
    USER SYS IDENTIFIED BY password      
//* If you have created password file, then you do not have to write it.*//
    USER SYSTEM IDENTIFIED BY password
    MAXLOGFILES 5
    MAXLOGMEMBERS 5
    MAXLOGHISTORY 1
    MAXDATAFILES 100
    CHARACTER SET WE8MSWIN1252
    EXTENT MANAGEMENT LOCAL
  DATAFILE '/u01/app/oracle/oradata/test1/system01.dbf' SIZE    350M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
SYSAUX DATAFILE '/u01/app/oracle/oradata/test1/sysaux01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
DEFAULT TABLESPACE USERS DATAFILE '/u01/app/oracle/oradata/test1/users01.dbf' SIZE 50M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/u01/app/oracle/oradata/test1/temp01.dbf' SIZE 100M REUSE AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/u01/app/oracle/oradata/test1/undotbs01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/test1/redo0101.log', '/u01/app/oracle/oradata/test1/redo0102.log') SIZE 50M,
GROUP 2 ('/u01/app/oracle/oradata/test1/redo0201.log', '/u01/app/oracle/oradata/test1/redo0202.log')    SIZE 50M,
GROUP 3 ('/u01/app/oracle/oradata/test1/redo0301.log', '/u01/app/oracle/oradata/test1/redo0302.log')   SIZE 50M;
6. Now create the database... and run the above scripts in SQL terminal.....

        Here "db11g.sql" is named as "db.sql".........
     

After db.sql script you may receive error for AUTOEXTEND is out of range        OR exceeded the range. Then in script reduce the file size for every file (less then 16 GB). 

7.  Now execute the script to build data dictionary views and public synonyms...

     SQL> @?/rdbms/admin/catalog.sql;                         
       
           If you receive the file open issue then give the full path..

    SQL> /u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catalog.sql;

8.  Now execute the script to build the PL/SQL package.....

     SQL> @?/rdbms/admin/catproc.sql;            OR

     SQL>/u01/app/oracle/product/11.2.0/dbhome_1/rdbms/admin/catproc.sql;


 Now database creation is completed. You can check it with following command:

      SQL> select name, open_mode from v$database;

            NAME                OPEN_MODE
        ___________         ________________

            DB200               READ WRITE

 If you want to configure the Enterprise Manager Repository the       first create a listener and start it. Then follow the below commands...

    SQL>    @?/sysman/admin/emdrep/sql/emreposcre $ORACLE_HOME SYSMAN password TEMP ON;
    SQL>     exit;

         [oracle@prod dbs]$ $ORACLE_HOME/bin/emca -config dbcontrol db
                       Database SID: DB200
                       Listener port number: assigned in Listener service
                       Password for SYS user:
                       Password for SYSMAN user: same as SYS user 

Now open in browser this URL and login with SYS user.