Physical and Logical Oracle Database Architecture

Physical Database Structure
------------------------------------------------------
If you call about the physical oracle database structure then it includes the file of database that we can see from Operating Systems. It consists of Datafiles, Control Files, Redo Log Files, Archive Log Files, Parameter Files, Alert and Trace Log Files and Backup Files.

1)Datafiles
-----------------
•All the database data are stored in datafiles.
•A datafile can be associated with only one database.
•All logical objects such as tables, indexes, functions are stored in data files.
•You can see your datafiles location in oracle by querying,
SQL> select file_name from dba_data_files;

2)Control Files
----------------------------
•Every Oracle database has a control file.
•A control file contains entries that specify the physical structure of the database.
•If the physical makeup of the database is altered (for example, if a new datafile or redo log file is created), then the control file is automatically modified by Oracle to reflect the change.
•You can see your control files location in oracle by querying,
SQL> select name from v$controlfile;

Or from Sql*Plus,
SQL> show parameter control_files
To know more about database control file please visit Control file in Oracle

3)Redo Log files
--------------------------
•Every Oracle database has a set of two or more redo log files.
•The set of redo log files is collectively known as the redo log for the database.
•A redo log is made up of redo entries (also called redo records).
•The primary function of the redo log is to record all changes made to data.
•The information in a redo log file is used only to recover the database from a system or media failure that prevents database data from being written to the datafiles.
•You can see your online redo log file location by querying,
SQL> select member from v$logfile;
The process of applying the redo log during a recovery operation is called rolling forward.

To know more about redo log files please visit

4)Archive Log Files
-----------------------
•The online redo log files is archived to another place if database archivelog mode is on. It ensures that all changes of database are saved to another place.
•You can see the location of your archived redo log file by querying,
SQL> select name from v$archived_log;
To know about archival destination please visit Archive log Destination

5)Parameter Files
------------------------------
A parameter file is used to start up the instance. It can be either spfile or pfile.
You can see the location of your spfile by querying,
SQL> select value from v$parameter where name='spfile';
Or in Sql*plus,
SQL> show parameter spfile;
To know more about it please visit Pfile and Spfile in Oracle


6)Alert and Trace Log Files
--------------------------------------
Each server and background process can write to an associated trace file. When an internal error is detected by a process, it dumps information about the error to its trace file.
To know about alert log file please visit What is Alert Log

7)Backup Files
------------------------
Backup files are the backup of database or individual tablespace or datafiles. They can be generated using RMAN or user managed backup.

Logical Database Structure
-------------------------------------------
If you call about the logical oracle database structure then it includes the file of database that we can't see from Operating Systems. Within oracle database itself it is maintained. The logical storage structures, includes oracle data blocks, extents, segments and tablespaces.

To know about tablespace visit
To know about oracle data block visit Overview of oracle data block
To know about extent visit Overview of Extents and when extents are allocated and deallocated
To know about segment visit
The details are discussed in another topics in my blog. To know more about those search inside my blog.