Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, santhoshreddy.

Asked: February 09, 2018 - 2:21 pm UTC

Last updated: February 11, 2019 - 4:34 am UTC

Version: 11g

Viewed 1000+ times

You Asked

Hi,

I know the difference between Instance and Database.
but i have few doubts about version of Instance and Data Base.
1). Can the versions be different for Instance and Database or they must have same version.
2). i did not understand what are Database components. are they different from Data base. why they have separate versions.

i got above doubts when i query below Tables.

Select * from V$instance; --Provides Instance Version

select * from V$version; --Provides DB Version

select * from database_properties; --Provides DB Properties

DBMS_DB_VERSION; -- Package to know Version and Release of DB


select * from V$Database; --Not sure about this did not find Version column.

select * from PRODUCT_COMPONENT_VERSION; --Provides Component Version


Thanks in Advance.

and Connor said...

We've got a whole book on that

https://docs.oracle.com/en/database/oracle/oracle-database/12.2/cncpt/introduction-to-oracle-database.html#GUID-8F2EEEC8-0372-4419-88FF-7D77A9C0FCAD

But in a nutshell

"Database"
- your *data*, ie the files that make up the physical representation of your data

"Instance"
- the processes/memory that interact with the database in order to provide you with access/update etc.

Hence, the most common scenario is 1 database = 1 instance. But we also have a thing called Real Application Clusters, which lets you have multiple instances to talk to a single database.

But please... read the Concepts guide (the link I gave).

Rating

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, February 06, 2019 - 1:13 am UTC

Can i use either of the tables V$instance,V$version to know database version? if not so,is instance version different from database version?
Connor McDonald
February 06, 2019 - 1:49 am UTC

They could conceivably be different during an upgrade operation (eg, you've started the instance in the new version and not yet run the upgrade scripts), but for normal operation they should align.

Perry, February 09, 2019 - 9:04 am UTC

Which view is help me to find out when the database is shutdown and when started at all time also contain past history.
Connor McDonald
February 11, 2019 - 4:34 am UTC

The alert log can give you that

SQL> select originating_timestamp, message_text
  2  from V$DIAG_ALERT_EXT
  3  where message_text like 'Starting ORACLE instance%'
  4  or message_text like 'Shutting down instance%'
  5  order by 1;

ORIGINATING_TIMESTAMP                    MESSAGE_TEXT
---------------------------------------- ---------------------------------------------------
23-AUG-18 10.25.40.283000000 AM +08:00   Starting ORACLE instance (normal) (OS id: 15552)
23-AUG-18 10.27.04.385000000 AM +08:00   Shutting down instance (abort) (OS id: 12692)
23-AUG-18 10.27.34.552000000 AM +08:00   Starting ORACLE instance (normal) (OS id: 17084)
23-AUG-18 10.28.10.369000000 AM +08:00   Shutting down instance (immediate) (OS id: 1244)
23-AUG-18 10.28.11.388000000 AM +08:00   Shutting down instance: further logons disabled
23-AUG-18 10.28.42.738000000 AM +08:00   Starting ORACLE instance (normal) (OS id: 9700)
23-AUG-18 10.33.23.868000000 AM +08:00   Shutting down instance (immediate) (OS id: 6628)
23-AUG-18 10.33.24.888000000 AM +08:00   Shutting down instance: further logons disabled
23-AUG-18 10.34.04.662000000 AM +08:00   Starting ORACLE instance (restrict) (OS id: 2124)
23-AUG-18 10.37.21.561000000 AM +08:00   Shutting down instance (immediate) (OS id: 15896)


more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database