21 Oracle Database(4): Create Database from script

Setup ORACLE_SID name

SID is PODB.

export ORACLE_SID=PODB

Setup the parameter file: init$ORACLE_SID.ora

• DB_NAME
• DB_BLOCK_SIZE
• SGA_TARGET
• CONTROL_FILES
• UNDO_MANAGEMENT

The file is under $ORACLE_HOME/dbs

DB_NAME=PODB

DB_BLOCK_SIZE= 8192

SGA_TARGET  = 300M

CONTROL_FILES=/home/oracle/app/oracle/oradata/podb/controlfile/PODB_controlfile.ctl

UNDO_MANAGEMENT = AUTO

Create all the directory and grant the permission

Create database

• Startup with nomount
• NO OMF: db_create_file_dest

startup nomount pfile=/home/oracle/app/oracle/product/11.2.0/dbhome_2/dbs/initPODB.ora

@/home/oracle/app/oracle/oradata/podb/createdb.sql

Run the createdb.sql. Here is my createdb.sql

create database PODB

user sys identified by oracle

user system identified by oracle

MAXLOGFILES 5

MAXLOGMEMBERS 5

LOGFILE GROUP 1 ( '/home/oracle/app/oracle/oradata/podb/logfile/redo01a.log','/home/oracle/app/oracle/oradata/podb/logfile/redo01b.log') SIZE 30M, GROUP 2 ('/home/oracle/app/oracle/oradata/podb/logfile/redo02a.log','/home/oracle/app/oracle/oradata/podb/logfile/redo02b.log') size 30M

CHARACTER SET AL32UTF8

NATIONAL CHARACTER SET AL16UTF16

EXTENT MANAGEMENT LOCAL

DATAFILE '/home/oracle/app/oracle/oradata/podb/controlfile/system01.dbf' SIZE 300M

SYSAUX DATAFILE '/home/oracle/app/oracle/oradata/podb/controlfile/sysaux01.dbf' SIZE 300M

DEFAULT TABLESPACE users DATAFILE '/home/oracle/app/oracle/oradata/podb/controlfile/user01.dbf' SIZE 300M AUTOEXTEND ON MAXSIZE UNLIMITED 

DEFAULT TEMPORARY TABLESPACE tempts TEMPFILE '/home/oracle/app/oracle/oradata/podb/controlfile/tempts01.dbf' SIZE 300M

UNDO TABLESPACE undots DATAFILE '/home/oracle/app/oracle/oradata/podb/controlfile/undots01.dbf' SIZE 300M AUTOEXTEND ON MAXSIZE UNLIMITED 

Check database

select STATUS from V$INSTANCE;

select name from V$DATABASE;

select name from V$DATAFILE;

select name from V$TEMPFILE;

select name from V$CONTROLFILE;

select GROUP#,member from V$LOGFILE;

select NAME from V$TABLESPACE;

-- check the users / dba_users

select NAME from SYS.USER$ where TYPE#=1;

-- check the roles

select NAME from sys.user$ where type#=0;

Dictionary related tables are not usable because we are not yet create those tables.

Post DB creation

Please refer here for more detail.

• Create the oracle password file to enable the remote login as sysdba

  

  Please refer here for more detail.

• Create Data Dictionary and Build-in Package

• Create SQL PLUS related tables

orapwd file=$ORACLE_HOME/dbs/orapwPODB password=orcl

spool /tmp/PODB_POST.log

show user

@?/rdbms/admin/catalog.sql

@?/rdbms/admin/catproc.sql

connect system/oracle

@?/sqlplus/admin/pupbld.sql

spool off