Solved

Can't get database up

Posted on 2014-01-23
10
400 Views
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
0
Comment
Question by:Randy Rich
10 Comments
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
Check the alert log.

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

Under that folder look under:
diag\rdbms\<ORACLE_SID>\<ORACLE_SID>\diag

Where <ORACLE_SID> is your actual instance name.
0
 
LVL 73

Expert Comment

by:sdstuber
Comment Utility
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
0
 

Author Comment

by:Randy Rich
Comment Utility
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)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
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.
IMODE=BR
ILAT =27
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up:
Personal Oracle Database 11g Release 11.2.0.1.0 - 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               = "11.2.0.0.0"
  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..
1080-012014-1-alert-log.txt
0
 
LVL 76

Accepted Solution

by:
slightwv (䄆 Netminder) earned 500 total points
Comment Utility
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;
0
 

Author Comment

by:Randy Rich
Comment Utility
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.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 22

Expert Comment

by:Steve Wales
Comment Utility
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:

SQLNET.AUTHENTICATION_SERVICES= (NTS)

(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
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>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.
0
 

Author Closing Comment

by:Randy Rich
Comment Utility
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. :)
0
 
LVL 76

Expert Comment

by:slightwv (䄆 Netminder)
Comment Utility
>>and open resetlogs

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

Author Comment

by:Randy Rich
Comment Utility
Yeah I did.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
ORACLE SQL DEVELOPER Query ? 5 57
Oracle SQL Char delimited error 5 30
Format Number Field 10 39
Export BLOB data from Oracle 10g 4 23
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
This post first appeared at Oracleinaction  (http://oracleinaction.com/undo-and-redo-in-oracle/)by Anju Garg (Myself). I  will demonstrate that undo for DML’s is stored both in undo tablespace and online redo logs. Then, we will analyze the reaso…
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
This video shows how to recover a database from a user managed backup

762 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now