Create additional database in Oracle Express edition 

 Create additional database in Oracle Express edition 

*In my windows i have installed oracle11g express edition i want to create new database for my testing purpose but the express edition doesnot support DBCA utiltiy let us we can discuss How to Create additional database in Oracle Express edition or How to create manual database in oracle11g windows enivornment.

S-1:

create directory

 

C:\Windows\system32>mkdir C:\oraclexe\app\oracle\admin\TEST

C:\Windows\system32>cd C:\oraclexe\app\oracle\admin\TEST



C:\oraclexe\app\oracle\admin\TEST>mkdir adump

C:\oraclexe\app\oracle\admin\TEST>mkdir bdump

C:\oraclexe\app\oracle\admin\TEST>mkdir dpdump

C:\oraclexe\app\oracle\admin\TEST>mkdir pfile

 

S-2:

Create new instance

  
C:\Windows\System32>oradim -new -sid test

Instance created.

 

S-3:

create pfile and Password file like below

 

C:\Windows\System32>orapwd file=C:\oraclexe\app\oracle\product\11.2.0\server\dat
abase\PWDTEST.ora password=oracle

 

Note: I just copied the pfile (InitXE.ora )from Xe database into new database pfile(my manual database) location then I changed the file name “initXE.ora” into “initTEST.ora” and opened that file

 

S-4:

Open the pfile “InitTEST.ora”

xe.__db_cache_size=411041792
xe.__java_pool_size=4194304
xe.__large_pool_size=4194304
xe.__oracle_base='C:\oraclexe\app\oracle'#ORACLE_BASE set from environment
xe.__pga_aggregate_target=432013312
xe.__sga_target=641728512
xe.__shared_io_pool_size=0
xe.__shared_pool_size=205520896
xe.__streams_pool_size=8388608
*.audit_file_dest='C:\oraclexe\app\oracle\admin\XE\adump'
*.compatible='11.2.0.0.0'
*.control_files='C:\oraclexe\app\oracle\oradata\XE\control.dbf'
*.db_name='XE'
*.DB_RECOVERY_FILE_DEST_SIZE=10G
*.DB_RECOVERY_FILE_DEST='C:\oraclexe\app\oracle\fast_recovery_area'
*.diagnostic_dest='C:\oraclexe\app\oracle\.'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XEXDB)'
*.job_queue_processes=4
*.memory_target=1024M
*.open_cursors=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=20
*.shared_servers=4
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'

 

S-5:

Change The parameter like below

*.audit_file_dest='C:\oraclexe\app\oracle\admin\TEST\adump'
*.compatible='11.2.0.0.0'
*.control_files='C:\oraclexe\app\oracle\oradata\TEST\control.dbf'
*.db_name='TEST'
*.DB_RECOVERY_FILE_DEST_SIZE=10G
*.DB_RECOVERY_FILE_DEST='C:\oraclexe\app\oracle\fast_recovery_area'
*.diagnostic_dest='C:\oraclexe\app\oracle\.'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=TESTXDB)'
*.job_queue_processes=4
*.memory_target=1024M
*.open_cursors=300
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=20
*.shared_servers=4
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'

 

S-6:

After modifying the pfile and i started the new instance like below

  C:\Windows\System32>set ORACLE_SID=TEST

C:\Windows\System32>sqlplus

SQL*Plus: Release 11.2.0.2.0 Production on Wed Sep 20 12:41:38 2017

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

Enter user-name: / as sysdba
Connected to an idle instance.

 

S-7:

Start the database in nomount stage using pfile

SQL> startup nomount pfile='C:\oraclexe\app\oracle\admin\TEST\pfile\initTEST.ora
'
ORACLE instance started.

Total System Global Area  644468736 bytes
Fixed Size                  1385488 bytes
Variable Size             192941040 bytes
Database Buffers          444596224 bytes
Redo Buffers                5545984 bytes

 

S-8:

Create the database script

create database TEST
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'C:\oraclexe\app\oracle\oradata\TEST\REDO01.LOG'  SIZE 50M BLOCKSIZE 512,
  GROUP 2 'C:\oraclexe\app\oracle\oradata\TEST\REDO02.LOG'  SIZE 50M BLOCKSIZE 512
DATAFILE'C:\oraclexe\app\oracle\oradata\TEST\SYSTEM.DBF' size 100m autoextend on
sysaux datafile 'C:\oraclexe\app\oracle\oradata\TEST\SYSAUX.DBF' size 100m autoextend on
undo tablespace undotbs1 datafile  'C:\oraclexe\app\oracle\oradata\TEST\UNDOTBS1.DBF' size 100m autoextend on
CHARACTER SET AL32UTF8
;

 

S-9:

Execute the @createdatabase.sql file

SQL> @C:\oraclexe\app\oracle\CREATEDATABASE.SQL

Database created.

 

S-10:

Test our database name and instance status

SQL> select status from v$instance;

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

SQL> select * from V$version;

BANNER
--------------------------------------------------------------------------------

Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
PL/SQL Release 11.2.0.2.0 - Production
CORE    11.2.0.2.0      Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
NLSRTL Version 11.2.0.2.0 - Production

SQL> select name from V$database;

NAME
---------
TEST

 

S-11:

Execute this below two scripts

  SQL> @C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\catalog.sql

  SQL> @C:\oraclexe\app\oracle\product\11.2.0\server\rdbms\admin\catproc.sql