Creating or Dropping a database in Oracle 11g

CREATING A NEW DATABASE

  • Creating a initialization parameter file at location $ORACLE_HOME/dbs which is the default location where oracle looks for a init file .

Lets create a database by name ‘test’ :

[[email protected]]$ cat $ORACLE_HOME/dbs/inittest.ora
db_name=test
db_block_size=8192
control_files=(/home/oracle/app/oracle/oradata/test/control01.ctl,/home/oracle/app/oracle/oradata/test/control02.ctl)
  • Startup instance in nomountĀ modeĀ and create a database
[[email protected] ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.1.0 Production on Mon Feb 18 23:55:15 2013
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 150667264 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 6725632 bytes

SQL> select status from v$instance;

STATUS
------------
NOMOUNT

SQL> CREATE DATABASE test
LOGFILE
GROUP 1 ('/home/oracle/app/oracle/oradata/test/redo01a.log', '/home/oracle/app/oracle/oradata/test/redo01b.log') SIZE 10M,
GROUP 2 ('/home/oracle/app/oracle/oradata/test/redo02a.log', '/home/oracle/app/oracle/oradata/test/redo02b.log') SIZE 10M
DATAFILE
 '/home/oracle/app/oracle/oradata/test/system01.dbf' SIZE 200M
SYSAUX DATAFILE '/home/oracle/app/oracle/oradata/test/sysaux01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 2000M
UNDO TABLESPACE "UNDOTBS1" DATAFILE '/home/oracle/app/oracle/oradata/test/undotbs01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE '/home/oracle/app/oracle/oradata/test/temp01.dbf' SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 500M
DEFAULT TABLESPACE USERS DATAFILE '/home/oracle/app/oracle/oradata/test/users01.dbf' SIZE 50M AUTOEXTEND ON MAXSIZE 200M
CHARACTER SET US7ASCII
NATIONAL CHARACTER SET AL16UTF16;

Database created.


SQL> select status from v$instance;

STATUS
------------
OPEN

  • Verifying physical files on disk.
[[email protected] test]$ pwd
/home/oracle/app/oracle/oradata/test

[[email protected] test]$ ls -lrt
total 518172
-rw-r-----. 1 oracle oinstall 104865792 Feb 19 00:06 temp01.dbf
-rw-r-----. 1 oracle oinstall 10486272 Feb 19 00:06 redo01a.log
-rw-r-----. 1 oracle oinstall 10486272 Feb 19 00:06 redo01b.log
-rw-r-----. 1 oracle oinstall 209723392 Feb 19 00:07 system01.dbf
-rw-r-----. 1 oracle oinstall 104865792 Feb 19 00:07 sysaux01.dbf
-rw-r-----. 1 oracle oinstall 104865792 Feb 19 00:07 undotbs01.dbf
-rw-r-----. 1 oracle oinstall 52436992 Feb 19 00:07 users01.dbf
-rw-r-----. 1 oracle oinstall 10486272 Feb 19 00:10 redo02a.log
-rw-r-----. 1 oracle oinstall 10486272 Feb 19 00:10 redo02b.log
-rw-r-----. 1 oracle oinstall 7847936 Feb 19 00:11 control01.ctl
-rw-r-----. 1 oracle oinstall 7847936 Feb 19 00:11 control02.ctl

DROPPING A DATABASE:

  • Shutdown and Startup instance in mount state.
SQL> SHUT IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP MOUNT
ORACLE instance started.
Total System Global Area 150667264 bytes
Fixed Size 1335080 bytes
Variable Size 92274904 bytes
Database Buffers 50331648 bytes
Redo Buffers 6725632 bytes
Database mounted.
  • Enable restricted session and drop database.
SQL> alter system enable restricted session;
System altered.

SQL> drop database;
Database dropped.
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> exit

[[email protected] test]$ pwd
/home/oracle/app/oracle/oradata/test
[[email protected] test]$ ls -lrt
total 0

Leave a Reply

Your email address will not be published. Required fields are marked *