-- Overview of Oracle Database Creation and key guidelines
-- Refer: Oracle 11gR2 XE Installation

--  1. Choose Instance Identifier (SID)

--  2. Establish the DBA Authentication Method

--  3. Create Initialization Parameter File (server parameter file - preferably 
--     binary).  Text file created by Oracle - init.ora

--  4. Connect to the instance (as SYSDBA)
       SQLPLUS /nolog
       CONNECT SYS/password AS SYSDBA

--  5. Create a Server Parameter File (SPFILE)
       CREATE SPFILE='/u01/oracle/dbs/spfileedwp01.ora' FROM
         PFILE='/u01/oracle/admin/initedwp01/scripts/init.ora';
       SHUTDOWN

--  6. Start the Instance 
       STARTUP NOMOUNT

--  7. Execute the CREATE DATABASE Statement (script)
--     Typical script for Oracle database creation is shown below

CREATE DATABASE edwp01
   USER SYS IDENTIFIED BY pgd1b4e
   USER SYSTEM IDENTIFIED BY rs0hmg1
   LOGFILE GROUP 1 ('/u01/oracle/oradata/edwp01/redo01.log') SIZE 100M,
           GROUP 2 ('/u01/oracle/oradata/edwp01/redo02.log') SIZE 100M,
           GROUP 3 ('/u01/oracle/oradata/edwp01/redo03.log') SIZE 100M
   MAXLOGFILES 5
   MAXLOGMEMBERS 5
   MAXLOGHISTORY 1
   MAXDATAFILES 100
   MAXINSTANCES 1
   CHARACTER SET US7ASCII
   NATIONAL CHARACTER SET AL16UTF16
   DATAFILE '/u01/oracle/oradata/edwp01/system01.dbf' SIZE 325M REUSE
   EXTENT MANAGEMENT LOCAL
   SYSAUX DATAFILE '/u01/oracle/oradata/edwp01/sysaux01.dbf' SIZE 325M REUSE 
   DEFAULT TABLESPACE tbs_1
   DEFAULT TEMPORARY TABLESPACE tempts1
      TEMPFILE '/u01/oracle/oradata/edwp01/temp01.dbf' 
      SIZE 20M REUSE
   UNDO TABLESPACE undotbs 
      DATAFILE '/u01/oracle/oradata/edwp01/undotbs01.dbf'
      SIZE 200M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

--  8. Create Additional Tablespaces

CREATE TABLESPACE users LOGGING 
     DATAFILE '/u01/oracle/oradata/edwp01/users01.dbf' 
     SIZE 25M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED 
     EXTENT MANAGEMENT LOCAL;

CREATE TABLESPACE indx LOGGING 
     DATAFILE '/u01/oracle/oradata/edwp01/indx01.dbf' 
     SIZE 25M REUSE AUTOEXTEND ON NEXT  1280K MAXSIZE UNLIMITED 
     EXTENT MANAGEMENT LOCAL;

--  9. Execute Scripts to Build Data Dictionary Views (PL/SQL packages)

CONNECT SYS/password AS SYSDBA
@/u01/oracle/rdbms/admin/catalog.sql
@/u01/oracle/rdbms/admin/catproc.sql
EXIT

-- 10. Execute Scripts to Install Additional Options

-- 11. Back Up the Database

-- 12. SQLPLUS Help
       SELECT * FROM system.help;