Skip to content

Can we create additional database for Oracle Express Edition?

by Mohamed Azar on May 22, 2011

Can we create new additional database for Oracle Expression Edition? Because No DBCA available in express edition, However we can create database using manual script.

We can follow way for normal database creation manual steps only…

Consider new Database Name is “XETEST”

Step 1:  Create suitable directory for new database.

like below

C:\oraclexe\app\oracle\admin\XETEST

    
	
  • adump
    • 
	
  • dpdump
    • 
	
  • pfile
    • 


C:\oraclexe\app\oracle\oradata\XETEST

Step 2: Create new instance for new database 

       C:\Windows\system32>oradim -new -sid xetest
         Instance created.

Step 3 : Create password file for new database

C:\Windows\system32>orapwd file=C:\oraclexe\app\oracle\product\11.2.0\server\database\PWDXETEST.ora password=sysxe

Step 4 : Create New pfile like below

xe.__db_cache_size=117440512
xe.__java_pool_size=4194304
xe.__large_pool_size=8388608
xe.__oracle_base='C:\oraclexe\app\oracle'#ORACLE_BASE set from environment
xe.__pga_aggregate_target=188743680
xe.__sga_target=566231040
xe.__shared_io_pool_size=92274688
xe.__shared_pool_size=335544320
xe.__streams_pool_size=0
*.audit_file_dest='C:\oraclexe\app\oracle\admin\XETEST\adump'
*.compatible='11.2.0.0.0'
*.control_files='C:\oraclexe\app\oracle\oradata\XETEST\control.dbf'
*.db_name='XETEST'
*.DB_RECOVERY_FILE_DEST_SIZE=10G
*.DB_RECOVERY_FILE_DEST='C:\oraclexe\app\oracle\flash_recovery_area'
*.diagnostic_dest='C:\oraclexe\app\oracle'###########################################
*.dispatchers='(PROTOCOL=TCP) (SERVICE=XETESTXDB)'
*.job_queue_processes=4
*.local_listener='(ADDRESS=(PROTOCOL=TCP)(HOST=AZAR-PC.ace-ins.com)(PORT=1522))'
*.open_cursors=300
*.os_authent_prefix=''
*.pga_aggregate_target=180M
*.remote_login_passwordfile='EXCLUSIVE'
*.sessions=20
*.sga_target=540M
*.shared_servers=4
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'

Step 5: Stratup nomount stage db using new pfile

.
C:\Windows\system32>SET ORACLE_SID=XETEST

C:\Windows\system32>sqlplus

SQL*Plus: Release 11.2.0.2.0 Beta on Sun May 22 15:23:42 2011

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

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

SQL> startup nomount pfile='d:\temp\initXETEST.ora';
ORACLE instance started.

Total System Global Area  564957184 bytes
Fixed Size                  1384956 bytes
Variable Size             171970052 bytes
Database Buffers          385875968 bytes
Redo Buffers                5726208 bytes

Step 6: Create Database Script  like below

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

Step 7: After created it, you can run at sql prompt


SQL> @D:\temp\create.sql

Database created.

Step 8: Now database was created, Check Instance status & Version

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 - Beta
PL/SQL Release 11.2.0.2.0 - Beta
CORE    11.2.0.2.0      Production
TNS for 32-bit Windows: Version 11.2.0.2.0 - Beta
NLSRTL Version 11.2.0.2.0 - Production

SQL>
The above newly created database is Express edition version.
And also RUN
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
From: Administration
←
→
36 Comments Post a comment
  1. prem #

    how create pfile file and whick location in step 4

    December 4, 2011
    Reply
    • Gordon #

      Create it in a text file and call it “initXETEST.ora”

      you can call it whatever you want though, as long as you reference it correctly in the next step.

      February 10, 2012
      Reply
  2. Pietro #

    Hey Mohamed. My name is Pietro and I m trying ot follow your instructions to create a db with Oracle 11g XE.
    At step 4 I get an error:
    ORA-01081: cannot start already running ORACLE – shut it down first
    if I shut it down it says it is not connected.
    Can you help me?

    Thanks a lot

    Pietro

    January 4, 2012
    Reply
    • Gordon #

      Pietro, best thing to do is come out of sqlplus by typing “exit”, then go back to step 5 and it should work.

      February 10, 2012
      Reply
  3. Gordon #

    Good tutorial, although in my installation in step 4:

    *.DB_RECOVERY_FILE_DEST=’C:\oraclexe\app\oracle\flash_recovery_area’

    is

    *.DB_RECOVERY_FILE_DEST=’C:\oraclexe\app\oracle\fast_recovery_area’

    Also the scripts failed when some directories didnt exist already but by exiting sqlplus and repeating the steps, I got round the issue.

    February 10, 2012
    Reply
  4. ayoige #

    my interest is n using it for website development i mean as a database for my web page

    February 15, 2012
    Reply
  5. Ambarish #

    Hi

    After database creation need to use datapump but it was failing because of internal metadata error can you check and confirm what all database components got installed with this above manual database creation and is datapump is runnig ?

    Thanks for you help

    February 29, 2012
    Reply
  6. Carpio3 #

    Great tutorial! Thank you.

    March 11, 2012
    Reply
  7. Ronei Godinho #

    Very good thank you……..

    June 15, 2012
    Reply
  8. rod #

    SQL> startup nomount pfile=’c:\temp\initXETEST.ora’;
    ORA-01261: Parameter db_recovery_file_dest destination string cannot be translat
    ed
    ORA-01263: Name given for file destination directory is invalid
    OSD-04018: Unable to access the specified directory or device.
    O/S-Error: (OS 2) The system cannot find the file specified.
    SQL>
    I dont know if I have to change something on the script on the file

    October 3, 2012
    Reply
  9. Mark #

    GREAT instructions. For others … make sure you edit the stuff to whatever DB name you are using … and create all the folders referenced.

    the last two scripts are important too … they create all the oracle stuff

    October 29, 2012
    Reply
  10. Ashok Kumar #

    At Step 5 I got the following error.
    Can you please help me.

    C:\Windows\System32>set SET ORACLE_SID=XETEST

    C:\Windows\System32>sqlplus

    SQL*Plus: Release 10.2.0.1.0 – Production on Tue Nov 27 14:52:36 2012

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

    Enter user-name: / as sysdba
    ERROR:
    ORA-12560: TNS:protocol adapter error

    Enter user-name:

    November 27, 2012
    Reply
  11. Ashok Kumar #

    When I go and see in services, OracleServicexetest is not started.
    If I try to start it throwing an error message

    November 27, 2012
    Reply
  12. Guest #

    Hi all!
    thanks for the tutorial.
    Could you tell me please how to connect to the database that is created? (after step8)

    December 26, 2012
    Reply
  13. best cragislist posting service #

    Thanks for finally talking about >Can we create additional database for Oracle Express Edition?
    | DBA <Liked it!

    February 8, 2013
    Reply
  14. David Cohen #

    Hello!

    I enjoyed your Blog and specially this article. I just want to have more information because your procedure worked perfectly in my system except for the end. At the end of Database creation I wanted to import a file using ‘impdp’ but it was not available for the newly created instance of the database. Do you have any ideas in how can I reinstall the pump?

    Thanks

    David Cohen

    February 18, 2013
    Reply
  15. http://newrepublicoftexas.com/nrt/groups/make-your-life-much-easier-with-the-truth-about-six-pack-abs-review-understanding #

    It results to us wasting time and efforts.

    February 25, 2013
    Reply
  16. vancouver Extended stay - learn more #

    Hey there are using WordPress for your blog platform? I’m new to the blog world but I’m trying to get started and set up my own.

    Do you need any html coding expertise to make your own blog?
    Any help would be really appreciated!

    March 21, 2013
    Reply
  17. jeans sale #

    It is located in the performance of the last census, but that only
    addresses one problem. The Lyme Bay coastal region of south west England.
    Put the mussels in a remote corner of Buckingham Palace.
    With apologies to William Ernest Henley: What have I done to you, England, my England?

    March 31, 2013
    Reply
  18. Aicha #

    Thank you for this tutorial.
    baraka ellaho fika

    April 14, 2013
    Reply
  19. battlefront heroes hack #

    Pretty great post. I just stumbled upon your blog and
    wanted to say that I’ve really enjoyed browsing
    your blog posts. In any case I’ll be subscribing to your rss feed
    and I’m hoping you write once more very soon!

    May 28, 2014
    Reply
  20. 魚菜共生 #

    Pretty! This was an extremely wonderful post. Thanks for providing these details.

    May 29, 2014
    Reply
  21. Hikoe #

    Thanks for tutorial. However, step6 – create database script is in sql prompt or Command prompt? Can you plz a little bit specify about that step 6? thanks

    August 28, 2014
    Reply
  22. Subrahmanyam #

    Hi Azar,
    I am getting error at Step 6: as
    SQL> @D:\temp\createDBLISA_DEV.sql
    create database lisa
    *
    ERROR at line 1:
    ORA-01034: ORACLE not available
    Process ID: 0
    Session ID: 0 Serial number: 0

    Can you please reply with your solution.

    November 28, 2014
    Reply
    • Subrahmanyam #

      this problem solved, but when i created a new SID (database), not able to connect to it, error: ORA 12505- TNS:listener does not currently know of SID given in connect descriptor – Error Message of the Month (January)

      November 29, 2014
      Reply
      • Wrox #

        I struck in the same problem can any one reply please

        May 7, 2015
  23. Hi #

    I have created the database according to your steps and everything went fine on the day of creation. But the next day when im trying to connect to that database as per step 5, Im getting an error as below. Can you please help me with this?

    error:

    C:\WINDOWS\system32>SET ORACLE_SID=TESTDB

    C:\WINDOWS\system32>sqlplus

    SQL*Plus: Release 11.2.0.2.0 Production on Wed May 24 09:07:07 2017

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

    Enter user-name: / as sysdba
    ERROR:
    ORA-12560: TNS:protocol adapter error

    May 23, 2017
    Reply
  24. Shabuddin Shaikh #

    Thank you Mohamed Azar. It was very clear and detailed.

    August 4, 2017
    Reply
  25. Etor Madiv #

    Many thanks to you for that great tutorial, I followed your tutorial and made it work for 10.2.0 with minor changes, for 10.2.0, BLOCKSIZE is not supported so I removed it from script create.sql, also commented line 16 #*.diagnostic_dest=’C:\oraclexe\app\oracle of initXETEST.ora and everything worked like a charm.

    March 7, 2018
    Reply
  26. StefanH #

    I am very sorry, in step 5

    Enter user-name: / as sysdba

    I do not get around the error-Message

    ORA-12560: TNS: protocol adapter error

    My service OracleserviceGRXE is running.
    The default database service “OracleServiceXE” is not running.

    Does anybody have a solution here? – THX

    Stefan

    September 28, 2018
    Reply
  27. Henrik Christensen #

    Thanks for great tutorial.
    Is it possible to create an additional database also for Oracle 18c XE ?

    February 14, 2020
    Reply

Trackbacks & Pingbacks

  1. Version Xyz Blog

Leave a reply to Hi Cancel reply