Solved

Can't get database up

Posted on 2014-01-23
10
405 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)
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 73

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

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
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
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 76

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 76

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Oracle TEXT search question 9 48
Oracle Syntax 8 56
Oracle DATE Column Space 11 65
PL/SQL Search for multiple strings 5 42
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…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

863 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

27 Experts available now in Live!

Get 1:1 Help Now