Num of records in v$instance
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Num of records in v$instance

Hybrid View

  1. #1
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    Hi ,

    Could u anybody please confirm if , there will ever be more than 1 record in v$instance table . i have seen different scripts for date , time of last instance startup in the following path : http://www.dbasupport.com/oracle/scr...tration/Audit/ why do we need a where clause here .

    Thanks .

  2. #2
    Join Date
    May 2002
    Posts
    2,645
    Just one record.

    Column Datatype Description
    INSTANCE_NUMBER
    NUMBER
    Instance number used for instance registration. Corresponds to INSTANCE_NUMBER initialization parameter.


    INSTANCE_NAME
    VARCHAR2(16)
    Instance name

    HOST_NAME
    VARCHAR2(64)
    Name of the host machine

    VERSION
    VARCHAR2(17)
    RDBMS version

    STARTUP_TIME
    DATE
    Time when instance was started up

    STATUS
    VARCHAR2(7)
    STARTED/MOUNTED/OPEN

    STARTED after startup nomount

    MOUNTED after startup mount or alter database close

    OPEN after startup or after database open

    PARALLEL
    VARCHAR2(3)
    (YES | NO) in Oracle Application Cluster mode

    THREAD#
    NUMBER
    Redo thread opened by the instance

    ARCHIVER
    VARCHAR2(7)
    (STOPPED | STARTED | FAILED) FAILED means that the archiver failed to archive a log last time, but will try again within 5 minutes

    LOG_SWITCH_WAIT
    VARCHAR2(11)
    The ARCHIVE LOG/CLEAR LOG/CHECKPOINT event log switching is waiting for. Note that if ALTER SYSTEM SWITCH LOGFILE is hung, but there is room in the current online redo log, then value is NULL

    LOGINS
    VARCHAR2(10)
    ALLOWED | RESTRICTED

    SHUTDOWN_PENDING
    VARCHAR2(3)
    YES | NO

    DATABASE_STATUS
    VARCHAR2(17)
    The status of the database

    INSTANCE_ROLE
    VARCHAR2(16)
    Describes whether the instance is an active instance (PRIMARY_INSTANCE) or a inactive secondary instance (SECONDARY_INSTANCE), or UNKNOWN if the instance has been started but not mounted

    ACTIVE_STATE
    VARCHAR2(9)
    (NORMAL|QUIESCING|QUIESCED).

    NORMAL indicates the database is in a normal state.

    QUIESCING indicates that the ALTER SYSTEM QUIESCE RESTRICTED statement has been issued: no new user transactions, queries, or PL/SQL statements are processed in this instance. User transactions, queries, or PL/SQL statements issued before the ALTER SYSTEM QUIESCE RESTRICTED statement are unaffected. DBA transactions, queries, or PL/SQL statements are also unaffected.

    QUIESCED indicates that the ALTER SYSTEM QUIESCE RESTRICTED statement has been issued: no user transactions, queries, or PL/SQL statements are processed. DBA transactions, queries, or PL/SQL statements are unaffected. User transactions, queries, or PL/SQL statements issued after the ALTER SYSTEM QUIESCE RESTRICTED statement are not processed.

    Note that a single ALTER SYSTEM QUIESCE RESTRICTED statement quiesces all instances in an Oracle9i Real Application Clusters environment. After this statement has been issued, some instances may enter into a quiesced state before other instances; the system is quiesced when all instances enter the quiesced state.


  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    You can definitely have more than one record returned from V$INSTANCE, but only if you are having paralel server (OPS/RAC) configuration on that database. One record per instance that has database mounted.

    The script you are refering to (http://www.dbasupport.com/oracle/scripts/Administration/Audit/ ) has a totaly different isue, though! That script was obviously written for Oracle7 or earlier and will not work in Oracle 8.0 or any later release, since starting with Oracle8 the view V$INSTANCE has totaly different structure. With Oracle7 you allways got more than one row from it and hence the WHERE clause in that script.

    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    Thanks a lot .

  5. #5
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by jmodic
    You can definitely have more than one record returned from V$INSTANCE, but only if you are having paralel server (OPS/RAC) configuration on that database.
    Jurij, are you telling him that in OPS/RAC, after you issue
    Code:
    select * from v$instance;
    then you will have more than 1 record returned? If so, then this is not true.
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  6. #6
    Join Date
    Jul 2002
    Posts
    132
    I am working on OPS -database having 2 instances.
    v$instance shows only one record -the details of the current instance in which u r in.

  7. #7
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Yes, my bad.

    V$INSTANCE returns only one row even with OPS/RAC (in 8.0 and above). That's normal since it has "... where inst_id = USERENV('Instance')" condition built in. What I was reealy talking about was view GV$INSTANCE - this lists all the instances that have database mounted.

    Sorry for the confusion.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  8. #8
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by jmodic
    Yes, my bad.

    V$INSTANCE returns only one row even with OPS/RAC (in 8.0 and above). That's normal since it has "... where inst_id = USERENV('Instance')" condition built in. What I was reealy talking about was view GV$INSTANCE - this lists all the instances that have database mounted.

    Sorry for the confusion.
    I suspected you meant GV$INSTANCE:

    Code:
    SQL>  select * from gv$instance;
    
       INST_ID INSTANCE_NUMBER INSTANCE_NAME    HOST_NAME           
    ---------- --------------- ---------------- -------------------
             1               1 ADS01            fim420-01           
             2               2 ADS02            fim420-02           
    
    SQL>
    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g
    email: ocp_9i@yahoo.com

  9. #9
    Join Date
    May 2002
    Location
    USA
    Posts
    462
    Hi ,

    select * from v$instance ; retireves 1 record
    select * from gv$instance also returns 1 record in RAC .

    may be it retrieves more records in OPS .

    prakash

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