Thinking Out Loud

February 22, 2016

Create DB with minimum parameters

Filed under: 11g,oracle — mdinh @ 2:10 am

Just a quick post to create database with minimum parameters.

Note: for the control_files parameter to be updated, you must create spfile from pfile.

oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ ls *test*
inittest.ora  orapwtest
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ cat inittest.ora
db_name=test
db_create_file_dest='/oradata'
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 21 18:02:12 2016

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

Connected to an idle instance.

SYS@test> create spfile from pfile;

File created.

SYS@test> startup nomount;
ORACLE instance started.

Total System Global Area  229683200 bytes
Fixed Size                  2251936 bytes
Variable Size             171967328 bytes
Database Buffers           50331648 bytes
Redo Buffers                5132288 bytes
SYS@test> CREATE DATABASE
CHARACTER SET AL32UTF8
  2    3  EXTENT MANAGEMENT LOCAL
DATAFILE SIZE 256M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
  4    5  sysaux DATAFILE SIZE 64M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
  6  LOGFILE GROUP 1 SIZE 100M,GROUP 2 SIZE 100M
DEFAULT TEMPORARY TABLESPACE temp TEMPFILE SIZE 64M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
  7    8  DEFAULT TABLESPACE users DATAFILE SIZE 64M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
  9  UNDO TABLESPACE undotbs DATAFILE SIZE 64M AUTOEXTEND ON NEXT 512M MAXSIZE 8388672K
; 10

Database created.

SYS@test> select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/oradata/TEST/controlfile/o1_mf_cdnv3bjv_.ctl

SYS@test> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ strings spfiletest.ora
test.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
*.control_files='/oradata/TEST/controlfile/o1_mf_cdnv3bjv_.ctl'#Oracle managed file
*.db_create_file_dest='/oradata'
*.db_name='test'


oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 21 18:09:48 2016

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

ARROW:(SYS@test):PRIMARY> show parameter pfile

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfiletest.ora
ARROW:(SYS@test):PRIMARY> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$

Many years ago, I had learned about drop database.
https://mdinh.wordpress.com/?s=drop+database

It does not do a complete job for cleanup.

What I did not know then which I now know, it does drop spfile as spfile is considered as part of the database, may be because it can get backup from RMAN just like the database?

oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ ll *test*
-rw-rw----. 1 oracle oinstall 1544 Feb 21 18:14 hc_test.dat
-rw-r--r--. 1 oracle oinstall   44 Feb 21 18:01 inittest.ora
-rw-r-----. 1 oracle oinstall 2048 Feb 21 17:54 orapwtest
-rw-r-----. 1 oracle oinstall 1536 Feb 21 18:03 spfiletest.ora
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Sun Feb 21 18:15:21 2016

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

Connected to an idle instance.

SYS@test> startup restrict force mount;
ORACLE instance started.

Total System Global Area  229683200 bytes
Fixed Size                  2251936 bytes
Variable Size             171967328 bytes
Database Buffers           50331648 bytes
Redo Buffers                5132288 bytes
Database mounted.
SYS@test> drop database;

Database dropped.

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SYS@test> exit
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ ll *test*
-rw-rw----. 1 oracle oinstall 1544 Feb 21 18:17 hc_test.dat
-rw-r--r--. 1 oracle oinstall   44 Feb 21 18:01 inittest.ora
-rw-r-----. 1 oracle oinstall 2048 Feb 21 17:54 orapwtest
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ sysresv

IPC Resources for ORACLE_SID "test" :
Shared Memory
ID              KEY
No shared memory segments used
Semaphores:
ID              KEY
No semaphore resources used
Oracle Instance not alive for sid "test"
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$ ll /oradata/
total 56
drwxr-xr-x. 2 oracle oinstall 16384 Feb  1 11:59 backup
-rw-r--r--. 1 oracle oinstall    60 Nov  5  2014 bkup.rman
-rwxr-xr-x. 1 oracle oinstall   783 Nov  5  2014 bkup.sh
-rwxr-xr-x. 1 oracle oinstall   443 Nov  5  2014 bkup.sh.bak
drwxr-xr-x. 4 oracle oinstall  4096 Jan  9 15:08 fra
-rw-r--r--. 1 oracle oinstall   287 Nov  6  2014 fra.rman
drwxr-xr-x. 5 oracle oinstall  4096 Jan 14 21:28 HAWKLAS
drwxrwxr-x. 5 oracle oinstall  4096 Jan 14 21:28 HAWKSAN
drwxr-xr-x. 2 oracle oinstall  4096 Jan 28 11:07 keep
-rw-r--r--. 1 oracle oinstall   230 Jul 24  2014 restore_validate.rman
-rw-r--r--. 1 oracle oinstall   164 Jul 25  2014 simple.rman
oracle@arrow:test:/u01/app/oracle/product/11.2.0/dbhome_1/dbs
$


oracle@arrow:hawklas:/u01/app/oracle/diag
$ ll
total 8
drwxr-xr-x. 6 oracle oinstall 4096 Feb 21 17:55 rdbms
drwxr-xr-x. 3 oracle oinstall 4096 Sep 25 06:37 tnslsnr
oracle@arrow:hawklas:/u01/app/oracle/diag
$ cd rdbms/
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms
$ ll
total 16
drwxr-xr-x. 3 oracle oinstall 4096 Jan  9 16:34 hawk
drwxr-xr-x. 3 oracle oinstall 4096 Jan  9 16:31 hawklas
drwxr-xr-x. 3 oracle oinstall 4096 Jan  9 16:39 hawksan
drwxr-xr-x. 3 oracle oinstall 4096 Feb 21 18:17 test
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms
$ cd test/
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms/test
$ ll
total 4
-rw-r-----.  1 oracle oinstall    0 Feb 21 18:02 i_1.mif
drwxr-xr-x. 15 oracle oinstall 4096 Feb 21 18:17 test
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms/test
$ cd test/
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms/test/test
$ ll
total 52
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 alert
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 cdump
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 hm
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 incident
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 incpkg
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 ir
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:19 lck
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:19 metadata
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 metadata_dgif
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 metadata_pv
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 stage
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 sweep
drwxr-xr-x. 2 oracle oinstall 4096 Feb 21 18:17 trace
oracle@arrow:hawklas:/u01/app/oracle/diag/rdbms/test/test
$

Advertisements

Leave a Comment »

No comments yet.

RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: