Solved

Can't get database up

Posted on 2014-01-23
10
417 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 77

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:
diag\rdbms\<ORACLE_SID>\<ORACLE_SID>\diag

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

Expert Comment

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

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)
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 77

Accepted Solution

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

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.
0
 
LVL 22

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:

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 77

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.
0
 

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. :)
0
 
LVL 77

Expert Comment

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

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

Author Comment

by:Randy Rich
ID: 39806521
Yeah I did.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

728 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