Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

database cannot be started, urgent!!!

Posted on 2015-01-26
17
Medium Priority
?
214 Views
Last Modified: 2015-01-29
Total System Global Area 9637228544 bytes
Fixed Size                  2248088 bytes
Variable Size            5687476840 bytes
Database Buffers         3925868544 bytes
Redo Buffers               21635072 bytes
Database mounted.
ORA-16038: log 8 sequence# 12796 cannot be archived
ORA-19504: failed to create file ""
ORA-00312: online log 8 thread 2:
'+ASM_REDO1/essp/onlinelog/group_8.272.819108539'
ORA-00312: online log 8 thread 2:
'+ASM_REDO2/essp/onlinelog/group_8.272.819108543'
0
Comment
Question by:Jason Yu
  • 10
  • 7
17 Comments
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 40570949
What is the parameter log_archive_dest_1 set to?

Is that filesystem mounted?
Is the ownership correct?
0
 

Author Comment

by:Jason Yu
ID: 40570966
log_archive_dest_1                   string      LOCATION=+ASM_ARCH_ESSP
0
 

Author Comment

by:Jason Yu
ID: 40570969
the filesystem is mounted.


how should I check the ownership of the ASM disks?
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 40570975
Sorry, I'm not an ASM Expert.  I was thinking the archived redo would be on a regular filesystem.

What is db_recovery_file_dest_size set to?   If you have space available, you might just need to increase this parameter.

I assume you have been removing the archived redo logs that you no longer need for recovery?
0
 

Author Comment

by:Jason Yu
ID: 40570985
How to check this parameter?  db_recovery_file_dest_size ??
0
 

Author Comment

by:Jason Yu
ID: 40570988
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576    101982      486                0             486              0             N  ASM_ARCH_ESSP/
MOUNTED  EXTERN  N         512   4096  1048576    305964   305826                0          305826              0             N  ASM_ARCH_MEMBP/
MOUNTED  EXTERN  N         512   4096  1048576     10134     9736                0            9736              0             Y  ASM_CLUSTER/
MOUNTED  EXTERN  N         512   4096  1048576     20387    20284                0           20284              0             N  ASM_CONFIG_ESSP/
MOUNTED  EXTERN  N         512   4096  1048576     20387    20284                0           20284              0             N  ASM_CONFIG_MEMBP/
MOUNTED  EXTERN  N         512   4096  1048576     10134    10006                0           10006              0             N  ASM_CTL_ESSP/
MOUNTED  EXTERN  N         512   4096  1048576     10134    10006                0           10006              0             N  ASM_CTL_MEMBP/
MOUNTED  EXTERN  N         512   4096  1048576    150513    17143                0           17143              0             N  ASM_DATA_ESSP/
MOUNTED  EXTERN  N         512   4096  1048576    301026   215527                0          215527              0             N  ASM_DATA_MEMBP/
MOUNTED  EXTERN  N         512   4096  1048576    255996   146173                0          146173              0             N  ASM_DATA_MPDP/
MOUNTED  EXTERN  N         512   4096  1048576    409618   373610                0          373610              0             N  ASM_FRA_MPDP/
MOUNTED  EXTERN  N         512   4096  1048576     61168    24128                0           24128              0             N  ASM_REDO1/
MOUNTED  EXTERN  N         512   4096  1048576     61168    24128                0           24128              0             N  ASM_REDO2/
ASMCMD> exit
0
 

Author Comment

by:Jason Yu
ID: 40570989
ASMCMD> lsdg
State    Type    Rebal  Sector  Block       AU  Total_MB  Free_MB  Req_mir_free_MB  Usable_file_MB  Offline_disks  Voting_files  Name
MOUNTED  EXTERN  N         512   4096  1048576    101982      486                0             486              0             N  ASM_ARCH_ESSP/
MOUNTED  EXTERN  N         512   4096  1048576    305964   305826                0          305826              0             N  ASM_ARCH_MEMBP/
MOUNTED  EXTERN  N         512   4096  1048576     10134     9736                0            9736              0             Y  ASM_CLUSTER/
MOUNTED  EXTERN  N         512   4096  1048576     20387    20284                0           20284              0             N  ASM_CONFIG_ESSP/
MOUNTED  EXTERN  N         512   4096  1048576     20387    20284                0           20284              0             N  ASM_CONFIG_MEMBP/
MOUNTED  EXTERN  N         512   4096  1048576     10134    10006                0           10006              0             N  ASM_CTL_ESSP/
MOUNTED  EXTERN  N         512   4096  1048576     10134    10006                0           10006              0             N  ASM_CTL_MEMBP/
MOUNTED  EXTERN  N         512   4096  1048576    150513    17143                0           17143              0             N  ASM_DATA_ESSP/
MOUNTED  EXTERN  N         512   4096  1048576    301026   215527                0          215527              0             N  ASM_DATA_MEMBP/
MOUNTED  EXTERN  N         512   4096  1048576    255996   146173                0          146173              0             N  ASM_DATA_MPDP/
MOUNTED  EXTERN  N         512   4096  1048576    409618   373610                0          373610              0             N  ASM_FRA_MPDP/
MOUNTED  EXTERN  N         512   4096  1048576     61168    24128                0           24128              0             N  ASM_REDO1/
MOUNTED  EXTERN  N         512   4096  1048576     61168    24128                0           24128              0             N  ASM_REDO2/
ASMCMD> exit

Open in new window

0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 40570997
>>How to check this parameter?  db_recovery_file_dest_size ??

Same way you checked log_archive_dest_1.  It is an spfile parameter.



The lsdg commands don't mean anything to me since I'm not an ASM person.
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 40571002
Sorry.  Missed this.

Even though I'm not an ASM person it looks like ASM_ARCH_ESSP is about out of space with only 486M free.
0
 

Author Comment

by:Jason Yu
ID: 40571024
Yes, you are correct. We changed the parameter to save the achieve log to local file system resolved this issue.
0
 

Author Comment

by:Jason Yu
ID: 40571026
We had successfully retarted the db.

BTW, we increased the lun size, but the asm didn't see it, could you explain why?
0
 
LVL 78

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 2000 total points
ID: 40571036
>>We changed the parameter to save the achieve log to local file system resolved this issue.

You need to be careful now that you will have archived redo logs in two different locations!!!

>>we increased the lun size, but the asm didn't see it, could you explain why?

Nope.  I'm not an ASM person.  Never used it.

I would check with the DBA that set everything up to see how to tweak the space.  You might even open an SR with Oracle Support.

While you wait for them I would dive into the online documentation and start reading up on administering ASM.  There has to be information in there about increasing available space.
0
 

Author Comment

by:Jason Yu
ID: 40571056
>>We changed the parameter to save the achieve log to local file system resolved this issue.

You need to be careful now that you will have archived redo logs in two different locations!!!


How should I resolve this issue?

Thanks for remindering.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 40571064
>> How should I resolve this issue?

I would verify that you are removing old archived redo logs after they have been backed up so the space can be reused.

If they are all necessary this implies adequate space wasn't allocated when ASM was set up.  I would look ad adding more space to ASM_ARCH_ESSP.

I would also make sure that db_recovery_file_dest_size is set correctly.

All this your DBA should be able to help you with.
0
 

Author Comment

by:Jason Yu
ID: 40571146
HI, Slightwv:

Thank you very much, I will work with our DBA on this. I am the system admin here and learned some DBA's skill.

If I want to add more space to ASM_ARCH_ESSP, how could I do it. I already increased the lun size on the SAN side, however, after that, the Oracle ASM still doesn't recognize the increased size. I may need research on how to make ASM expand the size.


Also, for the parameter db_recovery_file_dest_size, it was set "0". Does this mean we don't use flash backup at all?

thanks.
0
 
LVL 78

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 40571168
>>I am the system admin here and learned some DBA's skill.

I would be very careful about making database changes without the DBA approving them.

Many shops have a separation of duties that defines who can do what in the system.

I assume you are authorized to make database changes?

Remember: Ability to do something and permission to do it are two different things.

Making changes to the database on the fly without authorization might get you fired!

>>If I want to add more space to ASM_ARCH_ESSP, how could I do it.

You can keep asking this but my answer will be the same:
I don't know.  I've never used ASM.  Your DBA should know.

>> Does this mean we don't use flash backup at all?

It may be an ASM item.  Since I've never used ASM I don't know if this parameter does anything.  Your DBA should know.
0
 

Author Closing Comment

by:Jason Yu
ID: 40578728
Very good answer and helpful suggestions.

thank you.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering 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

Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Suggested Courses

879 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