I have installed the Oracle Express edition on Ubuntu as mentioned here.
I am facing issues when I try to create a sample table.
$ sudo service oracle-xe start Starting Oracle Database 11g Express Edition instance.
$ sqlplus / as sysdba
Executed the CREATE command
SQL> CREATE TABLE SAMPLE (ID NUMBER); CREATE TABLE SAMPLE (ID NUMBER) *** ERROR at line 1: ORA-01109: database not open**
After a series of research on web, I tried to shutdown and restart oracle:
SQL> shutdown ORA-01507: database not mounted ORACLE instance shut down.
Started the oracle instance
SQL> startup ORACLE instance started. Total System Global Area 688959488 bytes Fixed Size 2229688 bytes Variable Size 411044424 bytes Database Buffers 272629760 bytes Redo Buffers 3055616 bytes ORA-00205: error in identifying control file, check alert log for more info
I realized that the control file is missing at
/u01/app/oracle/oradata/XE. XE is the DB name.
So I tried to create the control file as follows:
SQL> CREATE CONTROlFILE SET DATABASE XE RESETLOGS; Control file created.
Tried to create the sample table again
SQL> CREATE TABLE SAMPLE(ID NUMBER); CREATE TABLE SAMPLE(ID NUMBER) ERROR at line 1: ORA-01109: database not open
So I tried to issue the following command
SQL> ALTER DATABASE OPEN RESETLOGS; ALTER DATABASE OPEN RESETLOGS ERROR at line 1: ORA-01194: file 1 needs more recovery to be consistent** ORA-01110: data file 1: '/u01/app/oracle/product/11.2.0/xe/dbs/dbs1XE.dbf'
What should be done next? I am clueless as I am not a database guy.
$ lsnrctl services LSNRCTL for Linux: Version 18.104.22.168.0 - Production on 04-JAN-2013 09:15:37 Copyright (c) 1991, 2011, Oracle. All rights reserved. Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE))) Services Summary... Service "PLSExtProc" has 1 instance(s). Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 LOCAL SERVER Service "XE" has 1 instance(s). Instance "XE", status READY, has 1 handler(s) for this service... Handler(s): "DEDICATED" established:0 refused:0 state:ready LOCAL SERVER The command completed successfully