I have installed the Oracle Express edition on Ubuntu as mentioned here.
I am facing issues when I try to create a sample table.
Started oracle
$ sudo service oracle-xe start
Starting Oracle Database 11g Express Edition instance.
Started sqlplus
$ 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:
Shutdown command
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.
Note:
Output of
$ lsnrctl services
LSNRCTL for Linux: Version 11.2.0.2.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
recover database until cancel