Create/Drop DB in Oracle

Create DB

1) Create file init[dbname].ora to the directory /u01/app/oracle/xxx/xxx/dbs folder (change db_name=[dbname])

2) Create directory /u01/app/oracle/admin/[dbname]

3) Create the directories bdump, cdump and udump in the /u01/app/oracle/admin/[dbname] directory

4) Do a chmod 777 on the /u01/app/oracle/admin/[dbname] directory and sub directories.

5) Prepared the createdb.sql script. Sample script as follow:

CREATE DATABASE dbx
USER SYS IDENTIFIED BY sys
USER SYSTEM IDENTIFIED BY system
LOGFILE GROUP 1 ('/u01/app/oracle/oradata/dbx/redo01.log') SIZE 100M,
GROUP 2 ('/u01/app/oracle/oradata/dbx/redo02.log') SIZE 100M,
GROUP 3 ('/u01/app/oracle/oradata/dbx/redo03.log') SIZE 100M
MAXLOGFILES 5
MAXLOGMEMBERS 5
MAXLOGHISTORY 1
MAXDATAFILES 100
MAXINSTANCES 1
CHARACTER SET WE8ISO8859P15
DATAFILE '/u01/app/oracle/oradata/dbx/system01.dbf' SIZE 325M REUSE
EXTENT MANAGEMENT LOCAL
SYSAUX DATAFILE '/u01/app/oracle/oradata/dbx/sysaux01.dbf' SIZE 325M REUSE
DEFAULT TEMPORARY TABLESPACE tempts1 TEMPFILE '/u01/app/oracle/oradata/dbx/temp01.dbf' SIZE 20M REUSE
UNDO TABLESPACE undotbs01 DATAFILE '/u01/app/oracle/oradata/dbx/undotbs01.dbf' SIZE 200M;

6) Make these files executable (chmod 755 createdb.sql)

7) Switch to user oracle su – oracle

8) Type export ORACLE_SID=[dbname]

9) Type sqlplus /nolog

10) Connect as sysdba

11) get SQL>Connected to an idle instance.

12) Type startup pfile=init[dbname].ora nomount

Should get ORACLE instance started with output as following:

Total System Global Area 707335948 bytes
Fixed Size 452364 bytes
Variable Size 134217728 bytes
Database Buffers 570425344 bytes
Redo Buffers 2240512 bytes

13) Execute the script @/oracle/createdb.sql

14) Run @/u01/app/oracle/product/xxx/rdbms/admin/catalog.sql

Before run the following script, make sure to check the SYSTEM tablespace size whether it is sufficient or not. If not, add datafiles to it, alter the datafile to be auto extend, to resize the tablespace.

15) Run @/u01/app/oracle/product/xxx/rdbms/admin/catproc.sql

After 15) step, check if there's any invalid objects with the following sql (should returns no rows):
SELECT owner, object_name, object_type FROM dba_objects WHERE status='INVALID' ORDER BY owner, object_type, object_name;

16) Connect as user System (sqlplus system/system_password)

17) Run @/u01/app/oracle/product/xxx/sqlplus/admin/pupbld.sql

18) Create Tablespace needed for the database

19) Create User + grantprovide necessary privilege


Drop DB

$ sqlplus /nolog

SQL*Plus: Release 10.1.0.3.0 - Production on Fri Feb 18 20:15:26 2005

Copyright (c) 1982, 2004, Oracle. All rights reserved.

SQL> connect / as sysdba
Connected to an idle instance.

SQL> shutdown abort;
ORACLE instance shut down.

SQL> startup nomount;
ORACLE instance started.

Total System Global Area 612368384 bytes
Fixed Size 790352 bytes
Variable Size 174321840 bytes
Database Buffers 436207616 bytes
Redo Buffers 1048576 bytes

SQL> alter database mount exclusive;

Database altered.

SQL> alter system enable restricted session;

System altered.

SQL> drop database;

Database dropped.