Creating an oracle database 10g manually

Creating oracle database 10g manually

We supposed Oracle already installed(i.e. ORACLE_HOME=/fmstest/fmsclone/fmsclonedb) and
we are creating another database on the same server:
1- Create the required directories:
mkdir -p /fmstest/AEMEDB
mkdir -p /fmstest/AEMEDB/datafiles
mkdir -p /fmstest/AEMEDB/admin/bdump
mkdir -p /fmstest/AEMEDB/admin/cdump
mkdir -p /fmstest/AEMEDB/admin/udump
export ORACLE_HOME=/fmstest/fmsclone/fmsclonedb
export ORACLE_BASE=/fmstest
export ORACLE_SID=AEMEDB

2- Create initial initialization file
Oracle states that it has greatly reduced the number of necessary initialization parameters
in Oracle 10g. This seems to be true. To create the database, I used that minimalistic file as:

vi $ORACLE_HOME/dbs/initAEMEDB.ora

*.user_dump_dest='/fmstest/AEMEDB/admin/udump'
*.core_dump_dest='/fmstest/AEMEDB/admin/cdump'
*.background_dump_dest='/fmstest/AEMEDB/admin/bdump'
*.compatible='10.0.0'
*.control_files=('/fmstest/AEMEDB/datafiles/control01.ctl','/fmstest/AEMEDB/datafiles/control02.ctl')
*.db_block_size=8192
*.sga_target=512M
*.db_domain='LOCAL'
*.db_file_multiblock_read_count=16
*.db_name='AEMEDB'
*.fast_start_mttr_target=600
*.instance_name='AEMEDB'
*.job_queue_processes=2
*.workarea_size_policy='AUTO'
*.pga_aggregate_target=25M
*.remote_login_passwordfile='EXCLUSIVE'
*.timed_statistics=TRUE
*.undo_management='AUTO'
*.undo_retention=3600


3- Create the database:

sqlplus / as sysdba

startup nomount;

create spfile from pfile;

CREATE DATABASE AEMEDB
MAXINSTANCES 1
MAXLOGHISTORY 292
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
DATAFILE '/fmstest/AEMEDB/datafiles/system01.dbf'
SIZE 300M AUTOEXTEND ON NEXT  10240K MAXSIZE 2048M
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/fmstest/AEMEDB/datafiles/sysaux01.dbf'
SIZE 120M AUTOEXTEND ON NEXT  10240K MAXSIZE 1024M
DEFAULT TEMPORARY TABLESPACE TEMP
TEMPFILE '/fmstest/AEMEDB/datafiles/temp01.dbf'
SIZE 20M AUTOEXTEND ON NEXT  640K MAXSIZE UNLIMITED
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/fmstest/AEMEDB/datafiles/undo01.dbf'
SIZE 200M AUTOEXTEND ON NEXT  5120K MAXSIZE UNLIMITED
CHARACTER SET UTF8
NATIONAL CHARACTER SET AL16UTF16
LOGFILE
  GROUP 1 '/fmstest/AEMEDB/datafiles/redo01.log'  SIZE 50M,
  GROUP 2 '/fmstest/AEMEDB/datafiles/redo02.log'  SIZE 50M,
  GROUP 3 '/fmstest/AEMEDB/datafiles/redo03.log'  SIZE 50M
USER SYS IDENTIFIED BY sys
USER system IDENTIFIED BY sys
/

@?/rdbms/admin/catalog.sql;
@?/rdbms/admin/catblock.sql;
@?/rdbms/admin/catproc.sql;
@?/rdbms/admin/catoctk.sql;
@?/rdbms/admin/owminst.plb;
connect SYSTEM/sys
@/oracle/product/10.1.0.2/sqlplus/admin/pupbld.sql;
connect SYSTEM/sys
@?/sqlplus/admin/help/hlpbld.sql helpus.sql;

4- Create a custom tablespaces:
CREATE TABLESPACE ICMLFQ32 DATAFILE
  '/fmstest/AEMEDB/datafiles/ICMLFQ32.dbf' SIZE 2633M AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON
/


CREATE TABLESPACE ICMLNF32 DATAFILE
  '/fmstest/AEMEDB/datafiles/ICMLNF32.dbf' SIZE 25M AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON
/


CREATE TABLESPACE ICMLSNDX DATAFILE
  '/fmstest/AEMEDB/datafiles/ICMLSNDX.dbf' SIZE 286464K AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON
/


CREATE TABLESPACE ICMSFQ04 DATAFILE
  '/fmstest/AEMEDB/datafiles/ICMSFQ04.dbf' SIZE 150M AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON
/


CREATE TABLESPACE ICMVFQ04 DATAFILE
  '/fmstest/AEMEDB/datafiles/ICMVFQ04.dbf' SIZE 25M AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON
/


CREATE TABLESPACE SYSAUX DATAFILE
  '/fmstest/AEMEDB/datafiles/sysaux.dbf' SIZE 400M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON
/


CREATE TABLESPACE SYSTEM DATAFILE
  '/fmstest/AEMEDB/datafiles/system01.dbf' SIZE 325M AUTOEXTEND OFF
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
FLASHBACK ON
/


CREATE TABLESPACE TBS_1 DATAFILE
  '/fmstest/AEMEDB/datafiles/users.dbf' SIZE 443072K AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON
/


CREATE TEMPORARY TABLESPACE TEMPTS1 TEMPFILE
  '/fmstest/AEMEDB/datafiles/temp_tbs.dbf' SIZE 120M AUTOEXTEND ON NEXT 100M MAXSIZE 8192M
TABLESPACE GROUP ''
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M
/


CREATE UNDO TABLESPACE UNDOTBS DATAFILE
  '/fmstest/AEMEDB/datafiles/undo01.dbf' SIZE 748M AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED
ONLINE
RETENTION NOGUARANTEE
BLOCKSIZE 8K
FLASHBACK ON
/


CREATE TABLESPACE USERS DATAFILE
  '/fmstest/AEMEDB/datafiles/users01.dbf' SIZE 1324M AUTOEXTEND ON NEXT 100M MAXSIZE 2048M
LOGGING
ONLINE
PERMANENT
EXTENT MANAGEMENT LOCAL AUTOALLOCATE
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT MANUAL
FLASHBACK ON
/

5- Create the listner:
vi $TNS_ADMIN/listener.ora

AEMEDB =
  (ADDRESS_LIST =
        (ADDRESS= (PROTOCOL= IPC)(KEY= EXTPROCAEMEDB))
        (ADDRESS= (PROTOCOL= TCP)(Host= bahix08.aemeweb.com )(Port= 1544))
  )
SID_LIST_AEMEDB =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /fmstest/fmsclone/fmsclonedb)
      (SID_NAME = AEMEDB)
    )
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /fmstest/fmsclone/fmsclonedb)
      (PROGRAM = extproc)
    )
  )