Create database manually

1. first create directories for oradata and core_dump_dest etc, set ORACLE_HOME, ORACLE_SID in .profle and create init.ora file in $ORACLE_HOME/dbs

db_name='mydatabase'
memory_target=1G
processes = 150
control_files='/ora/app/oracle/product/11.2.0/dbs/CONTROL01.CTL','/ora/data/mydatabase/CONTROL02.CTL'
audit_file_dest=/ora/app/oracle/admin/mydatabase/adump
audit_trail ='db'
core_dump_dest=/ora/app/oracle/admin/mydatabase/cdump
[@more@]

#don't specify below 2 parameters, otherwise oracle says ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
#user_dump_dest=/ora/app/oracle/admin/mydatabase/udump
#background_dump_dest=/ora/app/oracle/admin/mydatabase/bdump


db_block_size=8192
db_domain=''
#db_recovery_file_dest='/flash_recovery_area'
#db_recovery_file_dest_size=2G
#diagnostic_dest=''
dispatchers='(PROTOCOL=TCP) (SERVICE=mydatabaseXDB)'
open_cursors=900
remote_login_passwordfile='EXCLUSIVE'
undo_tablespace='UNDOTBS1'

compatible ='11.2.0'

2. startup nomount, ready to create database manually.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size 2228344 bytes
Variable Size 616566664 bytes
Database Buffers 444596224 bytes
Redo Buffers 5545984 bytes


#Notes: UNDO TABLESPACE in below create database has to be the same name as undo_tablespace in init file.

CREATE DATABASE mydatabase
USER SYS IDENTIFIED BY mydb123
USER SYSTEM IDENTIFIED BY mydb123
LOGFILE GROUP 1 ('/ora/data/mydatabase/redo01.log') size 200M,
GROUP 2 ('/ora/data/mydatabase/redo02.log') size 200M,
GROUP 3 ('/ora/data/mydatabase/redo03.log') size 200M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16
EXTENT MANAGEMENT LOCAL
DATAFILE '/ora/data/mydatabase/system01.dbf' SIZE 1024M REUSE
SYSAUX DATAFILE '/ora/data/mydatabase/sysaux01.dbf' SIZE 1024M REUSE
DEFAULT TABLESPACE users
DATAFILE '/ora/data/mydatabase/users01.dbf'
SIZE 512M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE tempts1
TEMPFILE '/ora/data/mydatabase/temp01.dbf'
SIZE 512M REUSE
UNDO TABLESPACE UNDOTBS1
DATAFILE '/ora/data/mydatabase/undotbs01.dbf'
SIZE 512M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

建立資料字典檢視，分別執行下述指令碼來完成：
1 SQL> @?/rdbms/admin/catalog.sql
2 ......
3 ......
4 SQL> @?/rdbms/admin/catproc.sql
5 ......
6 ......
7 SQL> @?/sqlplus/admin/pupbld.sql --Note: use account system to execute this script, otherwise there's issue. Connect as SYSTEM before running this script


3. for other tablespace, use below statement to create.

SQL>create tablespace index
datafile '/ora/data/mydatabase/index01.dbf' size 100M
reuse autoextend on next 1280K maxsize unlimited
extend management local;

4. after the db is created, create spfile from pfile. reboot the database.