Oracle Concepts - 11 Physical Storage Structures

Database Architecture - Core DataFiles

• Datafiles align with the database portion of the RDBMS
• Transient data vs persistent Data
• Some datafiles ma[ to a tablespace - logical name for physical file
• Datafile = c:\oradata\orcl\data01.dbf
• Tablespace = DATA
  • Control File
    • Most important file in oracle database - the ‘brain’
    • Information on the state of the database
    • System Change Number - SCN
    • Multiplexing
  • Redo Log File - ‘OnLine Log’
    • Records database changes
    • Used to ‘roll forward’ a database from backup
    • can be written
    • Duplexing
  • ArchiveLog File - ‘OffLine Log’
    • ARCHIVELOG mode - full recoverability
    • Redo Logs written to archivelogs
  • SYSTEM
    • ‘Heart’ of the database
    • contains the data dictionary
    • Datafile #1
  • SYSAUX
    • Automatic Workload Repository - AWR
    • Second Data Dictionary
    • dedicated to Performance Metrics
  • Temporary datafiles (tempfiles)
    • Stored data segments that overflow from the PGA
    • Also stored temporary tables
  • Undo datafiles
    • Anatomy of a transaction
    • Before-images of data
  • Non-specialized datafiles
    • Hold application data in the form of tables
    • Maximum size 4,194,304 times the size of a database block
    • 8 block x 4 = 32GB

Database Architecture - Redo Architecture

• True RDBMS must be designed for recovery
• Incomplete recovery as well as complete recovery
• LGWR process writes to the log buffer, then the redo logs

 

Database Architecture - Real Application Clusters

• Oracle Clustering solution
• Pay-extra option
• High availability solution
• Scalability solution
• Growing company example

_______________________________________________________________________

Oracle Database and Instance Architecture

- Some SQL Juice

### List of datafiles in a database

SQL> select name from v$datafile;

NAME

--------------------------------------------------------------------------------

/home/oracle/app/oracle/oradata/orcl/system01.dbf

/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf

/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf

/home/oracle/app/oracle/oradata/orcl/users01.dbf

/home/oracle/app/oracle/oradata/orcl/example01.dbf

/home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf

/home/oracle/app/oracle/oradata/orcl/APEX_2041602962184952.dbf

/home/oracle/app/oracle/oradata/orcl/APEX_2610402357158758.dbf

/home/oracle/app/oracle/oradata/orcl/APEX_2611417663389985.dbf

/home/oracle/app/oracle/oradata/orcl/APEX_2614203650434107.dbf

10 rows selected.

### list datafiles, tablespace names, and size in MB:

SQL> connect system/oracle

Connected.

SQL> set pagesize 0

SQL> set lines 2000

SQL> col file_name format a70

SQL> col tablespace_name format a25

SQL> SELECT file_name, tablespace_name, ROUND(bytes/1024000) MB FROM dba_data_files ORDER BY tablespace_name;

/home/oracle/app/oracle/oradata/orcl/APEX_1930613455248703.dbf         APEX_1930613455248703              2

/home/oracle/app/oracle/oradata/orcl/APEX_2041602962184952.dbf         APEX_2041602962184952              2

/home/oracle/app/oracle/oradata/orcl/APEX_2610402357158758.dbf         APEX_2610402357158758              2

/home/oracle/app/oracle/oradata/orcl/APEX_2611417663389985.dbf         APEX_2611417663389985              2

/home/oracle/app/oracle/oradata/orcl/APEX_2614203650434107.dbf         APEX_2614203650434107              7

/home/oracle/app/oracle/oradata/orcl/example01.dbf                     EXAMPLE                           84

/home/oracle/app/oracle/oradata/orcl/sysaux01.dbf                      SYSAUX                          1289

/home/oracle/app/oracle/oradata/orcl/system01.dbf                      SYSTEM                           861

/home/oracle/app/oracle/oradata/orcl/undotbs01.dbf                     UNDOTBS1                         174

/home/oracle/app/oracle/oradata/orcl/users01.dbf                       USERS                            230

10 rows selected.

- Oracle database is a set of files that store Oracle data in persistent disk storage.

- Database instance is a set of memory structures that manage database files.

- Database files generated when you issue a CREATE DATABASE statement:

1. Data files (.dbf files on disk) and temp files (belongs to a temporary tablespace)

2. Control files, root file that tracks the physical components of the database.

3. Online redo log files (records of changes made to data)

- Mechanisms for Storing Database Files (May use combination of the preceding storage mechanisms)

1. Oracle Automatic Storage Management (Oracle ASM)

2. Operating system file system (Like LVM)

3. Raw device (disk partitions or logical volumes not formatted, using direct I/O and larger buffers)

4. Cluster file system (RAC)

- Oracle ASM Storage Components

Oracle ASM Storage Components

- Oracle ASM Instance and Database Instances

- Oracle Managed Files is a file naming strategy that enables you to specify operations in terms of database objects rather than file names, Oracle ASM requires Oracle Managed Files.

- user-managed files, you directly manage the operating system files in the database. You make the decisions regarding file structure and naming.

- Oracle Database physically stores tablespace data in data files. Tablespaces and data files are closely related, but have important differences:

• A segment can span one or more data files, but it cannot span multiple tablespaces.
• A database must have the SYSTEM and SYSAUX tablespaces. 
• Oracle Database automatically allocates the first data files of any database for the SYSTEM tablespace during database creation, 
• SYSTEM Tablespace contains the data dictionary, a set of tables that contains database metadata.

Data Files and Tablespaces

- Permanent tablespace contains persistent schema objects. Objects in permanent tablespaces are stored in data files.

- Temporary tablespace contains schema objects only for the duration of a session. Locally managed temporary tablespaces have temporary files (temp files),

- Temp files are created as sparse files. In this case, disk blocks are allocated not at file creation or resizing, but as the blocks are accessed for the first time.

- Every data file is either online (available) or offline (unavailable).When you take a data file offline in an online tablespace, the tablespace itself remains online,  You can make all data files of a tablespace temporarily unavailable by taking the tablespace itself offline

### Data File Structure

- Oracle Database creates a data file for a tablespace by allocating the specified amount of disk space plus the overhead for the data file header

- Data file header contains metadata about the data file such as its size and checkpoint SCN. 

- Each data file header contains: 

1. absolute file number uniquely identifies the data file within the database. 

2. relative file number uniquely identifies a data file within a tablespace.

- Oracle Database uses the free space in the data files to allocate extents for the segment.

- Space in a Data File

- Control file is a small binary file associated with only one database. 

- Control file contains information such as the following:

• The database name and database unique identifier (DBID)
• The time stamp of database creation
• Information about data files, online redo log files, and archived redo log files
• Tablespace information
• RMAN backups

- Control file serves the following purposes:

• contains information about data files, online redo log files, and so on that are required to open the database.
• contains metadata that must be accessible when the database is not open.

- Multiplexing a control file on different disks, the database can achieve redundancy and thereby avoid a single point of failure.

- control file contains the following types of records:

• Circular reuse records - information that is eligible to be overwritten if needed, Examples include records about archived redo log files and RMAN backups.
• Noncircular reuse records - information that does not change often and cannot be overwritten. Examples of information include tablespaces, data files, online redo log files, and redo threads

- Oracle Database reads and writes directly from the disk to the program global area (PGA).

- database maintains online redo log files to protect against data loss. Specifically, after an instance failure
- administrators can query online redo log files through a SQL interface in the Oracle LogMiner utility

- log switch occurs when the database stops writing to one online redo log file and begins writing to another

- Reuse of Online Redo Log Files

- Filled online redo log files are available for reuse depending on the archiving mode:

• NOARCHIVELOG > available after > written
• ARCHIVELOG > available after > written > archived

- Multiple Copies of Online Redo Log Files

- Archived redo log files are a crucial part of a backup and recovery strategy, used for:

• Recover a database backup
• Update a standby database
• Obtain information about the history of a database using the LogMiner utility

- Archiving is the operation of generating an archived redo log file. Archiving is either automatic or manual and is only possible when the database is in ARCHIVELOG mode.

- Online redo log files contain redo records. A redo record is made up of a group of change vectors, each of which describes a change to a data block.

- redo records have all relevant metadata for the change, including the following:

• SCN and time stamp of the change
• Transaction ID of the transaction that generated the change
• SCN and time stamp when the transaction committed (if it committed)
• Type of operation that made the change
• Name and type of the modified data segment