Can't get database up

Posted on 2014-01-23
Medium Priority
Last Modified: 2014-01-24
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
Question by:Randy Rich
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39804738
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.
LVL 74

Expert Comment

ID: 39804740
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

Author Comment

by:Randy Rich
ID: 39804789
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..
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

LVL 78

Accepted Solution

slightwv (䄆 Netminder) earned 2000 total points
ID: 39804837
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;

Author Comment

by:Randy Rich
ID: 39804895
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.
LVL 23

Expert Comment

by:Steve Wales
ID: 39804964
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: http://docs.oracle.com/cd/E11882_01/network.112/e10835/sqlnet.htm#NETRF198

NTS = Microsoft Windows native operating system authentication
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39805071
>>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.

Author Closing Comment

by:Randy Rich
ID: 39805247
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. :)
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 39805292
>>and open resetlogs

Remember that this invalidates ALL previous backups.  You should immediately do a full backup.

Author Comment

by:Randy Rich
ID: 39806521
Yeah I did.

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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.

Join & Write a Comment

Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
Configuring and using Oracle Database Gateway for ODBC Introduction First, a brief summary of what a Database Gateway is.  A Gateway is a set of driver agents and configurations that allow an Oracle database to communicate with other platforms…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question