Skip to content
Advertisements

Can we create additional database for Oracle Express Edition?

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

Advertisements
29 Comments Post a comment
  1. prem #

    how create pfile file and whick location in step 4

    December 4, 2011
    • 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
  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
    • 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
  3. 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
  4. my interest is n using it for website development i mean as a database for my web page

    February 15, 2012
  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
  6. Carpio3 #

    Great tutorial! Thank you.

    March 11, 2012
  7. Very good thank you……..

    June 15, 2012
  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
  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
  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
  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
  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
  13. Thanks for finally talking about >Can we create additional database for Oracle Express Edition?
    | DBA <Liked it!

    February 8, 2013
  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
  15. 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
  16. 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
  17. Aicha #

    Thank you for this tutorial.
    baraka ellaho fika

    April 14, 2013
  18. 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
  19. Pretty! This was an extremely wonderful post. Thanks for providing these details.

    May 29, 2014
  20. 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
  21. 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
    • 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
      • I struck in the same problem can any one reply please

        May 7, 2015

Trackbacks & Pingbacks

  1. Version Xyz Blog

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

%d bloggers like this: