Database Reference
In-Depth Information
SQL> select name from v$datafile;
NAME
-------------------------------------------------------------------------------
/home/ora12cr1/app/ora12cr1/product/12.1.0/dbhome_1/dbs/dbs1ora12c.dbf
/home/ora12cr1/app/ora12cr1/product/12.1.0/dbhome_1/dbs/dbx1ora12c.dbf
/home/ora12cr1/app/ora12cr1/product/12.1.0/dbhome_1/dbs/dbu1ora12c.dbf
SQL> select member from v$logfile;
MEMBER
-------------------------------------------------------------------------------
/home/ora12cr1/app/ora12cr1/product/12.1.0/dbhome_1/dbs/log1ora12c.dbf
/home/ora12cr1/app/ora12cr1/product/12.1.0/dbhome_1/dbs/log2ora12c.dbf
SQL> select name from v$controlfile;
NAME
-------------------------------------------------------------------------------
/home/ora12cr1/app/ora12cr1/product/12.1.0/dbhome_1/dbs/cntrlora12c.dbf
Oracle used defaults to put everything together and created a database as a set of persistent files. If we close this
database and try to open it again, we'll discover that we can't:
SQL> alter database close;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-16196: database has been previously opened and closed
An instance can mount and open, at most, one database—a single-tenant or container database—in its lifetime.
Remember, the instance consists simply of the processes and shared memory. This is still up and running. All we did
was close the database—that is, the physical files. We must discard this instance (shutdown) and create a new one
(startup) to open this or any other database.
To recap:
•
An instance is a set of background processes and shared memory.
•
A (single-tenant or container) database is a self-contained collection of data stored on disk.
•
An instance can mount and open only a single database, ever. As we'll see later, it may provide
access to many pluggable databases and “open” and “close” them multiple times; however, it
will only ever open a single self-contained database.
•
A database may be mounted and opened by one or more instances (using RAC) and the
number of instances mounting a single database can fluctuate over time.
As noted earlier, in most cases there's a one-to-one relationship between an instance and a database. This is
probably why the confusion surrounding the terms arises. In most peoples' experiences, a database is an instance,
and an instance is a database.
