Formerly-working Oracle installation no longer functions
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: Formerly-working Oracle installation no longer functions

  1. #1
    Join Date
    Jul 2003
    Posts
    6

    Unhappy 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!

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    Is the database up? What does the alert.log say?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  3. #3
    Join Date
    Jul 2003
    Posts
    59
    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

  4. #4
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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!


  5. #5
    Join Date
    Jul 2003
    Posts
    6
    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.

  6. #6
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    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!


  7. #7
    Join Date
    Jul 2003
    Posts
    6
    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

  8. #8
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    What about today's messages? When did you have this problem?
    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

  9. #9
    Join Date
    Feb 2001
    Posts
    99
    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?

  10. #10
    Join Date
    May 2002
    Posts
    2,645
    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
  •  



Click Here to Expand Forum to Full Width