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)
)
)
