How to Create an Oracle 11g Database manually on Linux32 Platform

August 12, 2009
  In some cases, we may want to create an Oracle database manually. This article will tell you how to.
 
1. Pre-creation
   We suppose the Oracle 11g software has been installed in the machine.
   The following environment variables have been set:
   ORACLE_BASE (for example, /u01/oracle)
   ORACLE_HOME (for example, /u01/oracle/product/11.1.0/db_1)
   ORACLE_SID  (for example, sindb1)
   NLS_LANG    (for example, American_america.UTF8)
   LD_LIBRARY_PATH (must including $ORACLE_HOME/lib)
   PATH        (must including $ORACLE_HOME/bin)
   You also know the SID name. Then you can begin the manual creation of your database.
  
2. Prepare the directories.
   2.1. Admin directories for your instances
      mkdir -p $ORACLE_BASE/admin/sindb1/adump  #audit log directory
      mkdir -p $ORACLE_BASE/admin/sindb1/script #your manual creation scripts are stored here.
     
   2.2. Diagnostic directories.
      Actually, the only thing you should do is to set the DIAGNOSTIC_DEST initial parameter in your pfile or spfile.
      Here, we set it to $ORACLE_BASE, that is /u01/oracle. And you need not to manually create any directories for
      udump,cdump,bdump, and so on. Oracle will automatically create these directories while you create the database.
     
   2.3. Database datafiles directory
      Here, we set it to $ORACLE_BASE/oradata/sindb1.
      mkdir -p $ORACLE_BASE/oradata/sindb1
 
   2.4. Checkpoint
      Make sure all above directories have been created correctly.
     
3. Prepare the scripts.
   To manually create an Oracle database, you should take the following steps:
   Change directory to $ORACLE_BASE/admin/sindb1/script.
     
   3.1. Create the controlling script
   The content of the $ORACLE_BASE/admin/sindb1/script/control.sql could like this:
   ----------------------------------------
   --connect sys as sysdba--
   --nomount--
   spool 1.log;
   @createdb.sql;
   spool off;
  
   --open--
   spool 2.log;
   @?/rdbms/admin/catalog.sql;
   spool off;
  
   spool 3.log;
   @?/rdbms/admin/catproc.sql;
   spool off;
  
   --connect system--
   --open--
   spool 4.log;
   @?/sqlplus/admin/pupbld.sql;
   spool off;
   ----------------------------------------
  
   3.2. Create the createdb.sql
   The content of the $ORACLE_BASE/admin/sindb1/script/createdb.sql could like this:
   ----------------------------------------
   CREATE DATABASE sindb1
   USER SYS IDENTIFIED BY oracle
   USER SYSTEM IDENTIFIED BY oracle
   LOGFILE GROUP 1 ('/u01/oracle/oradata/sindb1/redo1a.log') SIZE 64M REUSE,
           GROUP 2 ('/u01/oracle/oradata/sindb1/redo2a.log') SIZE 64M REUSE
   MAXLOGFILES 32
   MAXLOGMEMBERS 3
   MAXLOGHISTORY 1
   MAXDATAFILES 200
   CHARACTER SET AL32UTF8
   NATIONAL CHARACTER SET AL16UTF16
   EXTENT MANAGEMENT LOCAL
   DATAFILE '/u01/oracle/oradata/sindb1/system01.dbf' SIZE 325M REUSE
   SYSAUX
   DATAFILE '/u01/oracle/oradata/sindb1/sysaux01.dbf' SIZE 128M REUSE
   DEFAULT TABLESPACE users
   DATAFILE '/u01/oracle/oradata/sindb1/users01.dbf'  SIZE 32M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
   DEFAULT TEMPORARY TABLESPACE tempts1
   TEMPFILE '/u01/oracle/oradata/sindb1/temp01.dbf' SIZE 32M REUSE
   UNDO TABLESPACE undotbs1
   DATAFILE '/u01/oracle/oradata/sindb1/undotbs01.dbf' SIZE 128M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;
   ----------------------------------------
   Here, for better efficiency, we don't multiply the online redo logfiles.

4. Create an Oracle database manually.
   Change directory to $ORACLE_BASE/admin/sindb1/script.
   4.1. Create a password file
   OS>orapwd file=$ORACLE_HOME/dbs/orapwsindb1 entries=8
   It will prompt you to input the password of SYS user. We assume it is "oracle".
  
   4.2. Create the initial parameter file
   The content of the $ORACLE_BASE/admin/sindb1/script/initsindb1.ora could be:
   ######################################################
   db_name='sindb1'
   #memory_target=1G
   sga_target=300M
   log_buffer=4194304
   processes = 200
   audit_file_dest='/u01/oracle/admin/sindb1/adump'
   audit_trail ='db'
   db_block_size=8192
   db_domain=''
   #db_recovery_file_dest='<ORACLE_BASE>/flash_recovery_area'
   #db_recovery_file_dest_size=2G
   diagnostic_dest='/u01/oracle'
   #dispatchers='(PROTOCOL=TCP) (SERVICE=sindb1XDB)'
   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/oracle/oradata/sindb1/control1.ctl')
   control_files = ('/u01/oracle/oradata/sindb1/control2.ctl')
   compatible ='11.1.0'
   fast_start_mttr_target=1200
   ######################################################
  
   You can use the MEMORY_TARGET parameter, omit SGA_TARGET and LOG_BUFFER, to simplify the memory management.
   This is a new feature of Oracle11g.
   The DIAGNOSTIC_DEST parameter is also a wonderful new feature of Oracle11g.
   The control_files parameter has many lines, Oracle will merge these lines as many values for this parameter.
  
   Copy $ORACLE_BASE/admin/sindb1/script/initsindb1.ora to $ORACLE_HOME/dbs/.
  
   To verify your pfile, you should do the following:
   OS>sqlplus "/as sysdba"
   SQL>create spfile='/u01/oracle/product/11.1.0/db_1/dbs/spfilesindb1.ora' from pfile='/u01/oracle/product/11.1.0/db_1/dbs/initsindb1.ora';
   SQL>startup nomount;
   Then you should check the alert_sindb1.log, located in /u01/oracle/diag/rdbms/sindb1/sindb1/trace.
   If any error occurs, revise your pfile and re-startup nomount your instance.

   4.3. Run CREATE DATABASE
  
   SQL>connect /as sysdba
   SQL>startup nomount;
   SQL>spool 1.log;
   SQL>@createdb.sql;
   SQL>spool off;
  
   Make sure no error message appears in the alert_sindb1.log.
  
   4.4. Run catalog.sql and catproc.sql
   Change directory to $ORACLE_BASE/admin/sindb1/script.
   SQL>spool 2.log;
   SQL>@?/rdbms/admin/catalog.sql;
   SQL>spool off;
  
   SQL>spool 3.log;
   SQL>@?/rdbms/admin/catproc.sql;
   SQL>spool off;
  
   These two sql will take about twenty minutes, depending upon your hardware.
   Make sure there are no error message in the alert_sindb1.log. Some warnings can be igored.
  
   4.5. Run pupbld.sql
   SQL>connect system/oracle
   SQL>spool 4.log;
   SQL>@?/sqlplus/admin/pupbld.sql;
   SQL>spool off;
   Make sure there are no error message in the alert_sindb1.log. Some warnings can be igored.
  
   OK. Now your database has been created!
  
5. Configure listener.ora and tnsnames.ora
   Here's the listener.ora and tnsnames.ora, located in $ORACLE_HOME/network/admin.
   ####################################
   ###########listener.ora#############
   LISTENER=
  (ADDRESS_LIST=
    (ADDRESS=(PROTOCOL=TCP)(HOST=sinh1)(PORT=1521))
    (ADDRESS=(PROTOCOL=IPC)(KEY=NPNKEY))
  )

SID_LIST_LISTNER=
  (SID_LIST=
     (SID_DESC=
          (GLOBAL_NAME=sindb1)
          (SID_NAME=sindb1)
          (ORACLE_HOME=/u01/oracle/product/11.1.0/db_1)
     )
  )
   ###################################
  

   ####################################
   ###########tnsnames.ora#############
sindb1=
  (DESCRIPTION=
    (ADDRESS=(PROTOCOL=TCP)(HOST=sinh1)(PORT=1521))
    (CONNECT_DATA=(SERVICE_NAME=sindb1))
  )
   ################################### 
  
  
   You can use lsnrctl tool to start the listener and check if it works using tnsping and sqlplus tools.
   OS>lsnrctl start
   Make sure no error message appears
  
   OS> tnsping sindb1
   Make sure no error message appears
  
   SQL>connect system/oracle@sindb1
   Make sure no error message appears
  
6. Perform a cold backup of your new database.
  
   Shutdown the database and listener.
   SQL>connect /as sysdba
   SQL>shutdown immediate
   SQL>startup
   SQL>shutdown immediate
  
   Make sure no error message adds to alert_sindb1.log.
   Copy all file in $ORACLE_BASE/oradata/sindb1/ to your backup directory, e.g. /u02/bak/.
   Copy the $ORACLE_HOME/dbs/spfilesindb1 to your backup directory, e.g. /u01/bak/.