{"id":7604,"date":"2018-06-20T21:27:21","date_gmt":"2018-06-20T13:27:21","guid":{"rendered":"http:\/\/rmohan.com\/?p=7604"},"modified":"2018-06-20T21:27:21","modified_gmt":"2018-06-20T13:27:21","slug":"create-additional-database-in-oracle-express-edition","status":"publish","type":"post","link":"https:\/\/mohan.sg\/?p=7604","title":{"rendered":"\u00a0Create additional database in Oracle Express edition\u00a0"},"content":{"rendered":"

\u00a0Create additional database in Oracle Express edition\u00a0<\/strong><\/p>\n

*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.<\/p>\n

S-1:<\/strong><\/p>\n

create directory<\/p>\n

 <\/p>\n

C:\\Windows\\system32><\/span>mkdir C:\\oraclexe\\app\\oracle\\admin\\TEST\r\n\r\nC:\\Windows\\system32><\/span>cd<\/span> C:\\oraclexe\\app\\oracle\\admin\\TEST\r\n\r\n\r\n\r\nC:\\oraclexe\\app\\oracle\\admin\\TEST><\/span>mkdir adump\r\n\r\nC:\\oraclexe\\app\\oracle\\admin\\TEST><\/span>mkdir bdump\r\n\r\nC:\\oraclexe\\app\\oracle\\admin\\TEST><\/span>mkdir dpdump\r\n\r\nC:\\oraclexe\\app\\oracle\\admin\\TEST><\/span>mkdir pfile\r\n\r\n<\/code><\/pre>\n
 <\/p>\n
S-2:<\/strong><\/p>\n
Create new instance<\/p>\n
\r\n  \r\nC:\\Windows\\System32><\/span>oradim -<\/span>new -<\/span>sid test\r\n\r\nInstance created.<\/span>\r\n<\/code><\/pre>\n
 <\/p>\n
S-3:<\/strong><\/p>\n
create pfile and Password file like below<\/p>\n
 <\/p>\n
C:\\Windows\\System32><\/span>orapwd file<\/span>=<\/span>C:\\oraclexe\\app\\oracle\\product\\11.2<\/span>.<\/span>0<\/span>\\server\\dat\r\nabase\\PWDTEST.<\/span>ora password=<\/span>oracle\r\n\r\n<\/code><\/pre>\n
 <\/p>\n
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<\/strong><\/p>\n
 <\/p>\n
S-4:<\/strong><\/p>\n
Open the pfile “InitTEST.ora”<\/p>\n
\r\n\r\nxe.<\/span>__db_cache_size=<\/span>411041792<\/span>\r\nxe.<\/span>__java_pool_size=<\/span>4194304<\/span>\r\nxe.<\/span>__large_pool_size=<\/span>4194304<\/span>\r\nxe.<\/span>__oracle_base=<\/span>'C:\\oraclexe\\app\\oracle'<\/span>#ORACLE_BASE set from environment<\/span>\r\nxe.<\/span>__pga_aggregate_target=<\/span>432013312<\/span>\r\nxe.<\/span>__sga_target=<\/span>641728512<\/span>\r\nxe.<\/span>__shared_io_pool_size=<\/span>0<\/span>\r\nxe.<\/span>__shared_pool_size=<\/span>205520896<\/span>\r\nxe.<\/span>__streams_pool_size=<\/span>8388608<\/span>\r\n*<\/span>.<\/span>audit_file_dest=<\/span>'C:\\oraclexe\\app\\oracle\\admin\\XE\\adump'<\/span>\r\n*<\/span>.<\/span>compatible=<\/span>'11.2.0.0.0'<\/span>\r\n*<\/span>.<\/span>control_files=<\/span>'C:\\oraclexe\\app\\oracle\\oradata\\XE\\control.dbf'<\/span>\r\n*<\/span>.<\/span>db_name=<\/span>'XE'<\/span>\r\n*<\/span>.<\/span>DB_RECOVERY_FILE_DEST_SIZE=<\/span>10G\r\n*<\/span>.<\/span>DB_RECOVERY_FILE_DEST=<\/span>'C:\\oraclexe\\app\\oracle\\fast_recovery_area'<\/span>\r\n*<\/span>.<\/span>diagnostic_dest=<\/span>'C:\\oraclexe\\app\\oracle\\.'<\/span>\r\n*<\/span>.<\/span>dispatchers=<\/span>'(PROTOCOL=TCP) (SERVICE=XEXDB)'<\/span>\r\n*<\/span>.<\/span>job_queue_processes=<\/span>4<\/span>\r\n*<\/span>.<\/span>memory_target=<\/span>1024M\r\n*<\/span>.<\/span>open_cursors=<\/span>300<\/span>\r\n*<\/span>.<\/span>remote_login_passwordfile=<\/span>'EXCLUSIVE'<\/span>\r\n*<\/span>.<\/span>sessions=<\/span>20<\/span>\r\n*<\/span>.<\/span>shared_servers=<\/span>4<\/span>\r\n*<\/span>.<\/span>undo_management=<\/span>'AUTO'<\/span>\r\n*<\/span>.<\/span>undo_tablespace=<\/span>'UNDOTBS1'<\/span>\r\n<\/code><\/pre>\n
 <\/p>\n
S-5:<\/strong><\/p>\n
Change The parameter like below<\/p>\n
\r\n\r\n*<\/span>.<\/span>audit_file_dest=<\/span>'C:\\oraclexe\\app\\oracle\\admin\\TEST\\adump'<\/span>\r\n*<\/span>.<\/span>compatible=<\/span>'11.2.0.0.0'<\/span>\r\n*<\/span>.<\/span>control_files=<\/span>'C:\\oraclexe\\app\\oracle\\oradata\\TEST\\control.dbf'<\/span>\r\n*<\/span>.<\/span>db_name=<\/span>'TEST'<\/span>\r\n*<\/span>.<\/span>DB_RECOVERY_FILE_DEST_SIZE=<\/span>10G\r\n*<\/span>.<\/span>DB_RECOVERY_FILE_DEST=<\/span>'C:\\oraclexe\\app\\oracle\\fast_recovery_area'<\/span>\r\n*<\/span>.<\/span>diagnostic_dest=<\/span>'C:\\oraclexe\\app\\oracle\\.'<\/span>\r\n*<\/span>.<\/span>dispatchers=<\/span>'(PROTOCOL=TCP) (SERVICE=TESTXDB)'<\/span>\r\n*<\/span>.<\/span>job_queue_processes=<\/span>4<\/span>\r\n*<\/span>.<\/span>memory_target=<\/span>1024M\r\n*<\/span>.<\/span>open_cursors=<\/span>300<\/span>\r\n*<\/span>.<\/span>remote_login_passwordfile=<\/span>'EXCLUSIVE'<\/span>\r\n*<\/span>.<\/span>sessions=<\/span>20<\/span>\r\n*<\/span>.<\/span>shared_servers=<\/span>4<\/span>\r\n*<\/span>.<\/span>undo_management=<\/span>'AUTO'<\/span>\r\n*<\/span>.<\/span>undo_tablespace=<\/span>'UNDOTBS1'<\/span>\r\n<\/code><\/pre>\n
 <\/p>\n
S-6:<\/strong><\/p>\n
After modifying the pfile and i started the new instance like below<\/p>\n
\r\n\r\n  C:\\Windows\\System32><\/span>set<\/span> ORACLE_SID=<\/span>TEST\r\n\r\nC:\\Windows\\System32><\/span>sqlplus\r\n\r\nSQL*<\/span>Plus: Release<\/span> 11.2<\/span>.<\/span>0.2<\/span>.<\/span>0<\/span> Production on<\/span> Wed Sep 20<\/span> 12<\/span>:41<\/span>:38<\/span> 2017<\/span>\r\n\r\nCopyright (<\/span>c<\/span>)<\/span> 1982<\/span>,<\/span> 2010<\/span>,<\/span> Oracle.<\/span>  All<\/span> rights reserved.<\/span>\r\n\r\nEnter user<\/span>-<\/span>name: \/<\/span> as<\/span> sysdba\r\nConnected to<\/span> an idle instance.<\/span>\r\n\r\n<\/code><\/pre>\n
 <\/p>\n
S-7:<\/strong><\/p>\n
Start the database in nomount stage using pfile<\/p>\n
\r\n\r\nSQL><\/span> startup nomount pfile=<\/span>'C:\\oraclexe\\app\\oracle\\admin\\TEST\\pfile\\initTEST.ora\r\n'<\/span>\r\nORACLE instance started.<\/span>\r\n\r\nTotal System Global<\/span> Area  644468736<\/span> bytes\r\nFixed<\/span> Size                  1385488<\/span> bytes\r\nVariable Size             192941040<\/span> bytes\r\nDatabase<\/span> Buffers          444596224<\/span> bytes\r\nRedo Buffers                5545984<\/span> bytes\r\n<\/code><\/pre>\n
 <\/p>\n
S-8:<\/strong><\/p>\n
Create the database script<\/p>\n
\r\ncreate<\/span> database<\/span> TEST\r\nMAXLOGFILES 16<\/span>\r\nMAXLOGMEMBERS 3<\/span>\r\nMAXDATAFILES 100<\/span>\r\nMAXINSTANCES 8<\/span>\r\nMAXLOGHISTORY 292<\/span>\r\nLOGFILE\r\n  GROUP<\/span> 1<\/span> 'C:\\oraclexe\\app\\oracle\\oradata\\TEST\\REDO01.LOG'<\/span>  SIZE 50M BLOCKSIZE 512<\/span>,<\/span>\r\n  GROUP<\/span> 2<\/span> 'C:\\oraclexe\\app\\oracle\\oradata\\TEST\\REDO02.LOG'<\/span>  SIZE 50M BLOCKSIZE 512<\/span>\r\nDATAFILE'C:\\oraclexe\\app\\oracle\\oradata\\TEST\\SYSTEM.DBF'<\/span> size 100m autoextend on<\/span>\r\nsysaux datafile 'C:\\oraclexe\\app\\oracle\\oradata\\TEST\\SYSAUX.DBF'<\/span> size 100m autoextend on<\/span>\r\nundo tablespace<\/span> undotbs1 datafile  'C:\\oraclexe\\app\\oracle\\oradata\\TEST\\UNDOTBS1.DBF'<\/span> size 100m autoextend on<\/span>\r\nCHARACTER SET<\/span> AL32UTF8\r\n;<\/span>\r\n\r\n<\/code><\/pre>\n
 <\/p>\n
S-9:<\/strong><\/p>\n
Execute the @createdatabase.sql file<\/p>\n
\r\nSQL><\/span> @C<\/span>:\\oraclexe\\app\\oracle\\CREATEDATABASE.<\/span>SQL\r\n\r\nDatabase<\/span> created.<\/span>\r\n\r\n<\/code><\/pre>\n
 <\/p>\n
S-10:<\/strong><\/p>\n
Test our database name and instance status<\/p>\n
\r\n\r\nSQL><\/span> select<\/span> status<\/span> from<\/span> v$instance;<\/span>\r\n\r\nSTATUS<\/span>\r\n------------<\/span>\r\nOPEN<\/span>\r\n\r\nSQL><\/span> select<\/span> *<\/span> from<\/span> V$version;<\/span>\r\n\r\nBANNER\r\n--------------------------------------------------------------------------------<\/span>\r\n\r\nOracle Database<\/span> 11g Express Edition Release<\/span> 11.2<\/span>.<\/span>0.2<\/span>.<\/span>0<\/span> -<\/span> Production\r\nPL\/<\/span>SQL Release<\/span> 11.2<\/span>.<\/span>0.2<\/span>.<\/span>0<\/span> -<\/span> Production\r\nCORE    11.2<\/span>.<\/span>0.2<\/span>.<\/span>0<\/span>      Production\r\nTNS for<\/span> 32<\/span>-<\/span>bit<\/span> Windows: Version 11.2<\/span>.<\/span>0.2<\/span>.<\/span>0<\/span> -<\/span> Production\r\nNLSRTL Version 11.2<\/span>.<\/span>0.2<\/span>.<\/span>0<\/span> -<\/span> Production\r\n\r\nSQL><\/span> select<\/span> name from<\/span> V$database<\/span>;<\/span>\r\n\r\nNAME\r\n---------<\/span>\r\nTEST\r\n\r\n<\/code><\/pre>\n
 <\/p>\n
S-11:<\/strong><\/p>\n
Execute this below two scripts<\/p>\n
\r\n  SQL><\/span> @C<\/span>:\\oraclexe\\app\\oracle\\product\\11.2<\/span>.<\/span>0<\/span>\\server\\rdbms\\admin\\catalog.<\/span>sql\r\n\r\n  SQL><\/span> @C<\/span>:\\oraclexe\\app\\oracle\\product\\11.2<\/span>.<\/span>0<\/span>\\server\\rdbms\\admin\\catproc.<\/span>sql\r\n<\/code><\/pre>\n","protected":false},"excerpt":{"rendered":"
 Create additional database in Oracle Express edition <\/p>\n
*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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[34],"tags":[],"_links":{"self":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7604"}],"collection":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=7604"}],"version-history":[{"count":1,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7604\/revisions"}],"predecessor-version":[{"id":7605,"href":"https:\/\/mohan.sg\/index.php?rest_route=\/wp\/v2\/posts\/7604\/revisions\/7605"}],"wp:attachment":[{"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=7604"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=7604"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/mohan.sg\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=7604"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}