INSTANCE vs DATABASE

Add W3Buddy as a preferred source on Google

हि En

🔊 Listen

DATABASE = Physical files on disk (datafiles, control files, redo logs)
INSTANCE = Memory structures (SGA, PGA) + background processes that access the database

Key Point: One database can have multiple instances (RAC), but typically it’s 1:1.

Analogy:

• Database = Your house (physical structure)
• Instance = People living in the house (active processes)

INTERVIEW QUESTIONS:

Q1: What’s the difference between instance and database?
A: Instance is the memory and processes (temporary, exists in RAM). Database is physical files on disk (permanent). Instance must be started to access the database. You can have multiple instances accessing one database (RAC).

Q2: Can you have a database without an instance?
A: No. You need an instance running to access the database. The database files alone are useless without instance processes and memory.

Q3: Can you have an instance without a database?
A: Yes, temporarily. When you do STARTUP NOMOUNT, instance is started but database is not mounted yet. Used for database creation or control file recreation.

Q4: What happens if instance crashes?
A: Database files remain intact. When you restart the instance, SMON performs automatic instance recovery using redo logs to bring database to consistent state.