-
Formerly-working Oracle installation no longer functions
Greetings Oracle experts,
I'm running Oracle 9iR2 on a Solaris 8 SPARC workstation. After completing nearly all of the installation, I had some problems and was consulted by a knowledgeable Oracle admin who was able to get the database up and running.
Unfortunately, after a power outage and subsequent reboot of the machine, things don't quite seem to work anymore. I can manually start the TNS listener server and use 'tnsping' to verify that it's working -- so that's fine I think. I can use the 'oemapp console' tool to access the database entry and login -- so all that's still fine. But when I try to expand the Schema section under the database that I'd like to access/manipulate, I get an "Oracle not available" error. I've also tried using 'sqlplus', and that can connect to an idle instance, but beyond that I simply get shared memory/realm errors.
I've searched various forums/newsgroups, and all solutions that seem to be related to the problem I'm having don't seem to work. I verified that my ORACLE_SID environment variable (and all other important environment variables) are set-up properly. My 'oracle' user account and 'dba' group seem to be functioning properly.
Has the power outage somehow ruined my table data, or is the problem stemming from something else?
Thank you in advance for any insight you can lend!
-
Is the database up? What does the alert.log say?
Jeff Hunter
-
Run the DBVERIFY utility on each of the datafiles that make up your database. It will report any corruption.
See the doc:
http://download-east.oracle.com/docs...13.htm#1006626
Tom Best
-
Before you resolute to run the DBVERIFY, check the alert log to see what caused the problem. Also if you don't mind can you please post your complete error message that you get when you try connecting through sqlplus.
Looking forward for your reply.
Sam
Thanx
Sam
Life is a journey, not a destination!
-
Well, I can't find where any 'alert.log' files are at, but I did run 'dbv' on a few of the files in the 'oradata/my_database' folder and they all seem to be ~ 75% corrupt.
For example:
bash-2.03$ dbv FILE=cwmlite01.dbf FEEDBACK=100
...
DBVERIFY - Verification complete
Total Pages Examined : 10243
Total Pages Processed (Data) : 0
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 0
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 0
Total Pages Marked Corrupt : 7598
Total Pages Influx : 0
Does this mean that the database data is truly messed up, or could it simply mean that the spatial data that I have in there is not able to be read/processed by the dbverify utility?
Thank you for your help.
-
Originally posted by Anonymous
Well, I can't find where any 'alert.log' files are at,
How long have you been managing/working on oracle database administration?
You can find it under $ORACLE_BASE/admin/SID/bdump/alert_SID.log
Sam
Thanx
Sam
Life is a journey, not a destination!
-
I've included the alert log file below for your perusal. Let me know what you think.
Also, thanks for being so helpful.
Tue Jul 22 14:19:23 2003
alter database rename global_name to mustang.stc.us.ray.com
Completed: alter database rename global_name to mustang.stc.u
Tue Jul 22 14:19:24 2003
ALTER TABLESPACE TEMP ADD TEMPFILE '/app/oracle/oradata/mustang/temp01.dbf' REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMITED
Completed: ALTER TABLESPACE TEMP ADD TEMPFILE '/app/oracle/or
Tue Jul 22 14:19:24 2003
alter database character set INTERNAL_CONVERT WE8ISO8859P1
Tue Jul 22 14:19:35 2003
Updating character set in controlfile to WE8ISO8859P1
Completed: alter database character set INTERNAL_CONVERT WE8I
Tue Jul 22 14:19:43 2003
alter database national character set INTERNAL_CONVERT AL16UTF16
Completed: alter database national character set INTERNAL_CON
Tue Jul 22 14:20:43 2003
Shutting down instance: further logons disabled
Shutting down instance (normal)
License high water mark = 1
Waiting for dispatcher 'D000' to shutdown
All dispatchers and shared servers shutdown
Tue Jul 22 14:20:46 2003
ALTER DATABASE CLOSE NORMAL
Tue Jul 22 14:20:46 2003
SMON: disabling tx recovery
SMON: disabling cache recovery
Tue Jul 22 14:20:46 2003
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 1
Successful close of redo thread 1.
Tue Jul 22 14:20:46 2003
Completed: ALTER DATABASE CLOSE NORMAL
Tue Jul 22 14:20:46 2003
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Tue Jul 22 14:20:52 2003
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 3
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.1.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 117440512
large_pool_size = 16777216
java_pool_size = 117440512
control_files = /app/oracle/oradata/mustang/control01.ctl, /app/oracle/oradata/mustang/control02.ctl, /app/oracle/oradata/mustang/control03.ctl
db_block_size = 8192
db_cache_size = 16777216
compatible = 9.2.0.0.0
db_file_multiblock_read_count= 32
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain = stc.us.ray.com
instance_name = mustdb
dispatchers = (PROTOCOL=TCP) (SERVICE=mustdbXDB)
job_queue_processes = 10
hash_join_enabled = TRUE
hash_area_size = 1048576
background_dump_dest = /app/oracle/admin/mustang/bdump
user_dump_dest = /app/oracle/admin/mustang/udump
core_dump_dest = /app/oracle/admin/mustang/cdump
sort_area_size = 1048576
db_name = mustang
open_cursors = 300
star_transformation_enabled= TRUE
query_rewrite_enabled = TRUE
pga_aggregate_target = 33554432
aq_tm_processes = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Tue Jul 22 14:20:55 2003
starting up 1 shared server(s) ...
Tue Jul 22 14:20:55 2003
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Tue Jul 22 14:20:55 2003
ALTER DATABASE MOUNT
Tue Jul 22 14:20:59 2003
Successful mount of redo thread 1, with mount id 1480879751.
Tue Jul 22 14:20:59 2003
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Tue Jul 22 14:20:59 2003
ALTER DATABASE OPEN
Tue Jul 22 14:20:59 2003
Thread 1 opened at log sequence 1
Current log# 3 seq# 1 mem# 0: /app/oracle/oradata/mustang/redo03.log
Successful open of redo thread 1.
Tue Jul 22 14:20:59 2003
SMON: enabling cache recovery
Tue Jul 22 14:21:00 2003
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Tue Jul 22 14:21:00 2003
SMON: enabling tx recovery
Tue Jul 22 14:21:00 2003
Database Characterset is WE8ISO8859P1
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN
Shutting down instance: further logons disabled
Shutting down instance (normal)
License high water mark = 3
Waiting for shared server 'S000' to die
All dispatchers and shared servers shutdown
Tue Jul 22 14:37:01 2003
ALTER DATABASE CLOSE NORMAL
Tue Jul 22 14:37:01 2003
SMON: disabling tx recovery
SMON: disabling cache recovery
Tue Jul 22 14:37:01 2003
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Thread 1 closed at log sequence 1
Successful close of redo thread 1.
Tue Jul 22 14:37:01 2003
Completed: ALTER DATABASE CLOSE NORMAL
Tue Jul 22 14:37:01 2003
ALTER DATABASE DISMOUNT
Completed: ALTER DATABASE DISMOUNT
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
ARCH: Archiving is disabled
Shutting down archive processes
Archiving is disabled
Archive process shutdown avoided: 0 active
Tue Jul 22 14:37:08 2003
Starting ORACLE instance (normal)
LICENSE_MAX_SESSION = 0
LICENSE_SESSIONS_WARNING = 0
SCN scheme 3
Using log_archive_dest parameter default value
LICENSE_MAX_USERS = 0
SYS auditing is disabled
Starting up ORACLE RDBMS Version: 9.2.0.1.0.
System parameters with non-default values:
processes = 150
timed_statistics = TRUE
shared_pool_size = 117440512
large_pool_size = 16777216
java_pool_size = 117440512
control_files = /app/oracle/oradata/mustang/control01.ctl, /app/oracle/oradata/mustang/control02.ctl, /app/oracle/oradata/mustang/control03.ctl
db_block_size = 8192
db_cache_size = 16777216
compatible = 9.2.0.0.0
db_file_multiblock_read_count= 32
fast_start_mttr_target = 300
undo_management = AUTO
undo_tablespace = UNDOTBS1
undo_retention = 10800
remote_login_passwordfile= EXCLUSIVE
db_domain = stc.us.ray.com
instance_name = mustdb
dispatchers = (PROTOCOL=TCP) (SERVICE=mustdbXDB)
job_queue_processes = 10
hash_join_enabled = TRUE
hash_area_size = 1048576
background_dump_dest = /app/oracle/admin/mustang/bdump
user_dump_dest = /app/oracle/admin/mustang/udump
core_dump_dest = /app/oracle/admin/mustang/cdump
sort_area_size = 1048576
db_name = mustang
open_cursors = 300
star_transformation_enabled= TRUE
query_rewrite_enabled = TRUE
pga_aggregate_target = 33554432
aq_tm_processes = 1
PMON started with pid=2
DBW0 started with pid=3
LGWR started with pid=4
CKPT started with pid=5
SMON started with pid=6
RECO started with pid=7
CJQ0 started with pid=8
QMN0 started with pid=9
Tue Jul 22 14:37:11 2003
starting up 1 shared server(s) ...
Tue Jul 22 14:37:11 2003
starting up 1 dispatcher(s) for network address '(ADDRESS=(PARTIAL=YES)(PROTOCOL=TCP))'...
Tue Jul 22 14:37:11 2003
ALTER DATABASE MOUNT
Tue Jul 22 14:37:15 2003
Successful mount of redo thread 1, with mount id 1480896855.
Tue Jul 22 14:37:15 2003
Database mounted in Exclusive Mode.
Completed: ALTER DATABASE MOUNT
Tue Jul 22 14:37:15 2003
ALTER DATABASE OPEN
Tue Jul 22 14:37:15 2003
Thread 1 opened at log sequence 1
Current log# 3 seq# 1 mem# 0: /app/oracle/oradata/mustang/redo03.log
Successful open of redo thread 1.
Tue Jul 22 14:37:15 2003
SMON: enabling cache recovery
Tue Jul 22 14:37:16 2003
Undo Segment 1 Onlined
Undo Segment 2 Onlined
Undo Segment 3 Onlined
Undo Segment 4 Onlined
Undo Segment 5 Onlined
Undo Segment 6 Onlined
Undo Segment 7 Onlined
Undo Segment 8 Onlined
Undo Segment 9 Onlined
Undo Segment 10 Onlined
Successfully onlined Undo Tablespace 1.
Tue Jul 22 14:37:16 2003
SMON: enabling tx recovery
Tue Jul 22 14:37:16 2003
Database Characterset is WE8ISO8859P1
replication_dependency_tracking turned off (no async multimaster replication found)
Completed: ALTER DATABASE OPEN
Tue Jul 22 15:31:42 2003
Thread 1 advanced to log sequence 2
Current log# 1 seq# 2 mem# 0: /app/oracle/oradata/mustang/redo01.log
Tue Jul 22 15:34:42 2003
Thread 1 advanced to log sequence 3
Current log# 2 seq# 3 mem# 0: /app/oracle/oradata/mustang/redo02.log
Tue Jul 22 15:36:49 2003
Thread 1 advanced to log sequence 4
Current log# 3 seq# 4 mem# 0: /app/oracle/oradata/mustang/redo03.log
Wed Jul 23 16:18:13 2003
Thread 1 advanced to log sequence 5
Current log# 1 seq# 5 mem# 0: /app/oracle/oradata/mustang/redo01.log
-
What about today's messages? When did you have this problem?
Jeff Hunter
-
But is the database up? The statments "idle instance" and "No shared memory realm" tell me that while you datatabase service has started and no SGA has been initialized....the instance is not up. So, you need to connect as "sys as sysdba" and startup the database, using the approprite SPFILE or init.ora file.
Or am I just missing something else here in all the other responses?
-
Gee, Bob, you are missing something here. On UNIX, where or what would a service be?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|