Can't get database up

I have an oracle 11g database that I can't start.  When I try logging in as / sysdba I get iora-01031: insufficient privileges.   If I try to log in regularly I get ora-01033: Oriacle initialization or shutdown in progress.  Process ID : 0 Session ID: 0 Serial number: 0
Randy RichPresidentAsked:
Who is Participating?
slightwv (䄆 Netminder)Connect With a Mentor Commented:
Look at the last few lines:
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: 'C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF'

Do you have backups?

I've never tried to recover the UNDO tablespace.  Can you just drop and recreate it?

You can try a straight recover datafile.

Log into the server as a member of the ORA_DBA group. open up a sqlplus window
at the SQL prompt:
conn / as sysdba
shutdown immediate;
startup nomount;
recover datafile 3;
slightwv (䄆 Netminder) Commented:
Check the alert log.

It is in the folder pointed to by the diagnostic_dest spfile parameter.

Under that folder look under:

Where <ORACLE_SID> is your actual instance name.
the error is accurate

you need to log in as the oracle owner or other user that has sysdba privileges.

On unix/linux/vms that user is often simply "oracle"

On windows, you may need to use admininistrator privileges and start the service from the control panel
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Randy RichPresidentAuthor Commented:
Here is the contents of the alert log.

Shutting down instance (immediate)
Shutting down instance: further logons disabled
Stopping background process MMNL
Stopping background process MMON
License high water mark = 1
All dispatchers and shared servers shutdown
alter database close normal
ORA-1109 signalled during: alter database close normal...
Thu Jan 23 14:47:13 2014
Starting ORACLE instance (normal)
Picked latch-free SCN scheme 2
Using LOG_ARCHIVE_DEST_1 parameter default value as USE_DB_RECOVERY_FILE_DEST
Autotune of undo retention is turned on.
ILAT =27
SYS auditing is disabled
Starting up:
Personal Oracle Database 11g Release - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options.
Using parameter settings in server-side spfile C:\ORACLE\PRODUCT\11.2.0\DBHOME_1\DATABASE\SPFILEORCL.ORA
System parameters with non-default values:
  processes                = 150
  memory_target            = 1224M
  control_files            = "C:\ORACLE\ORADATA\ORCL\CONTROL01.CTL"
  control_files            = "C:\ORACLE\FLASH_RECOVERY_AREA\ORCL\CONTROL02.CTL"
  db_block_size            = 8192
  compatible               = ""
  db_recovery_file_dest    = "C:\oracle\flash_recovery_area"
  db_recovery_file_dest_size= 3852M
  undo_tablespace          = "UNDOTBS1"
  sec_case_sensitive_logon = FALSE
  remote_login_passwordfile= "EXCLUSIVE"
  db_domain                = ""
  dispatchers              = "(PROTOCOL=TCP) (SERVICE=orclXDB)"
  local_listener           = "LISTENER_ORCL"
  audit_file_dest          = "C:\ORACLE\ADMIN\ORCL\ADUMP"
  audit_trail              = "DB"
  db_name                  = "orcl"
  open_cursors             = 300
  diagnostic_dest          = "C:\ORACLE"
Thu Jan 23 14:47:13 2014
PMON started with pid=2, OS id=4132
Thu Jan 23 14:47:13 2014
VKTM started with pid=3, OS id=2760 at elevated priority
VKTM running at (10)millisec precision with DBRM quantum (100)ms
Thu Jan 23 14:47:13 2014
GEN0 started with pid=4, OS id=4732
Thu Jan 23 14:47:13 2014
DIAG started with pid=5, OS id=3408
Thu Jan 23 14:47:13 2014
DBRM started with pid=6, OS id=5916
Thu Jan 23 14:47:13 2014
PSP0 started with pid=7, OS id=4920
Thu Jan 23 14:47:13 2014
DIA0 started with pid=8, OS id=5964
Thu Jan 23 14:47:13 2014
MMAN started with pid=9, OS id=2632
Thu Jan 23 14:47:13 2014
DBW0 started with pid=10, OS id=6108
Thu Jan 23 14:47:13 2014
LGWR started with pid=11, OS id=4448
Thu Jan 23 14:47:13 2014
CKPT started with pid=12, OS id=4516
Thu Jan 23 14:47:13 2014
SMON started with pid=13, OS id=4436
Thu Jan 23 14:47:13 2014
RECO started with pid=14, OS id=6000
Thu Jan 23 14:47:13 2014
MMON started with pid=15, OS id=4504
Thu Jan 23 14:47:13 2014
MMNL started with pid=16, OS id=5768
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
starting up 1 shared server(s) ...
ORACLE_BASE from environment = C:\oracle
Thu Jan 23 14:47:14 2014
alter database mount exclusive
Successful mount of redo thread 1, with mount id 1365349234
Database mounted in Exclusive Mode
Lost write protection disabled
Completed: alter database mount exclusive
alter database open
Errors in file c:\oracle\diag\rdbms\orcl\orcl\trace\orcl_ora_5044.trc:
ORA-01113: file 3 needs media recovery
ORA-01110: data file 3: 'C:\ORACLE\ORADATA\ORCL\UNDOTBS01.DBF'
ORA-1113 signalled during: alter database open..
Randy RichPresidentAuthor Commented:
Yeah, that's the problem.  I can't connect like that.  I get ORA-01031: insufficient privileges.  I'm bringing the datafiles over from their system to mine which I know is a working system to see if I can start it from here.  

A technician went out on site and put in a new computer.  He said all that he did was shut down the oracle services on this computer, but I'm wondering if he didn't try to copy and paste it with it running.  Something definitely is wrong.
Steve WalesSenior Database AdministratorCommented:
Would seem that you're on Windows ?

Make sure your account is a part of the ora_dba group as mentioned.
I've also discovered that in order to connect "/ as sysdba" on windows, my sqlnet.ora needs to have this line in it:


(NTS as opposed to NONE).

Between ORA_DBA group and that, "/ as sysdba" works.

Then you can try slightwv's advice.

Doco on that parameter:

NTS = Microsoft Windows native operating system authentication
slightwv (䄆 Netminder) Commented:
>>Yeah, that's the problem.  I can't connect like that

When physically logged into the database server OS?

I'm not talking about a remote sqlplus connection.
Randy RichPresidentAuthor Commented:
The system had several files that needed to be recovered.  I never could figure out why I couldn't log on as sys on the client's machine but suspect it had something to do with the user rights being improper on it.  What I did was bring the database files over to a machine that was configured the same way that I knew worked.  I then took your advice on recovering the datafiles and then at the end I had to recover the database and open resetlogs.  After that it ran like a champ.  Thanks, you just saved my customer and me a lot of headache. :)
slightwv (䄆 Netminder) Commented:
>>and open resetlogs

Remember that this invalidates ALL previous backups.  You should immediately do a full backup.
Randy RichPresidentAuthor Commented:
Yeah I did.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.