Physical structures of Database

The physical structures are the files that actually live on disk in the system. They are present even if you completely power down the system.

Many different types of files are required (and optional) to run an Oracle database:
✓ Data files
✓ Control files
✓ Redo log files
✓ Archive log files
✓ Server and initialization parameter files
Knowing what each of these files does greatly increases your database management success.

Datafiles

Data files are the largest file types in an Oracle database. They store all the actual data you put into your database, as well as the data Oracle requires to manage the database. Data files are a physical structure: They exist whether the database is open or closed.
Data files are also binary in nature. You can’t read them yourself without starting an instance and executing queries. The data is stored in an organized format broken up into Oracle blocks.
Whenever a server process reads from a data file, it does so by reading at the very least one complete block. It puts that block into the buffer cache so that data can be accessed, modified, and so on.

In every data file, the very first block stores the block header. To be specific,
depending on your Oracle block size, the data file header block may be several blocks. By default, the header block is 64k. Therefore, if your Oracle block size is 4k, then 16 header blocks are at the beginning of the file. These header blocks are for managing the data file’s internal workings. They contain
✓ Backup and recovery information
✓ Free space information
✓ File status details
Lastly, a tempfile is a special type of database file. Physically, it’s just like a regular data file, but it only holds temporary information. For example, a tempfile is used if you perform sorts on disk or if you’re using temporary tables. The space is then freed to the file either immediately after your operation is done or once you log out of the system.

Control files

The control file is a very important file in the database — so important that you have several copies of it. These copies are placed so that losing a disk on your system doesn’t result in losing all of your control files.

Control files contain the following things:
✓ Names and locations of your data files and redo log files
✓ Recovery information
✓ Backup information
✓ Checkpoint information
✓ Archiving information
✓ Database name
✓ Log history
✓ Current logging information

Redo log files

Redo log files store the information from the log buffer. They’re written to by the Log Writer (LGWR). Again, you can’t read these binary files without the help of the database software.

Redo log files contain all the information necessary to recover lost data in your database. Every SQL statement that you issue changing data can be reconstructed by the information saved in these files.

Redo log files don’t record select statements. If you forget what you selected, you’re just going to have to remember that on your own!

The optimal size for your redo log files depends on how many changes you make to your database. The size is chosen by you when you set up the database and can be adjusted later. When the LGWR is writing to a redo log file, it does so sequentially. It starts at the beginning of the file and once it is filled up, it moves on to the next one. This is where the concept of groups comes in.
Oracle fills each group and moves to the next. Once it has filled all the groups, it goes back to the first. You could say they are written to in a circular fashion.
If you have three groups, it would go something like 1,2,3,1,2,3, . . . and so on.
Each time a group fills and the writing switches, it’s called a log switch operation.
These things happen during a log switch operation:
✓ The LGWR finishes writing to the current group.
✓ The LGWR starts writing to the next group.
✓ A database check point occurs.
✓ The DBWR writes dirty blocks out of the buffer cascade.

Because these redo log files may be involved in recovery operations, don’t lose them. Similar to control files, redo log files should be configured with mirrored copies of one another. And, as with control files, each member should be on a separate disk device. That way, if a disk fails and the database goes down, you still have recovery information available. You should not lose any data.
Each copy within a group is called a member. A common configuration might be three groups with two members apiece, for a total of six redo log files. The group members are written to simultaneously by the log writer.
✓ How many groups are appropriate? The most common configuration we come across is three. You want enough that the first group in the list can be copied off and saved before the LGWR comes back around to use it. If it hasn’t been copied off, the LGWR has to wait until that operation is complete. This can severely impact your system. Thankfully, we rarely see this happen.
✓ How many members are appropriate? It depends on how paranoid you are. Two members on two disks seems to be pretty common. However, it isn’t uncommon to see three members on three disks. More than that and you’re just plain crazy. Well, not really. It’s just that the more members ou have, the more work the LGWR has to do. It can impact system performance at the same time offering very little return.
A very common question: If my disks are mirrored at the hardware level, do I need more than one member on each group? After all, if a disk fails, I have another one right there to pick up the slack.
Unfortunately, you get different answers depending on who you ask. Ask me, and i’ll recommend at least two members for each group:
✓ Oracle still recommends two members for each group as a best practice.
✓ Depending on how your hardware is set up, you may have the same disk controller writing to your disk mirrors. What if that controller writes corrupt gibberish? Now both your copies are corrupted. Separating your members across two different disks with different controllers is the safest bet.

Archive Logs

Archive log files are simply copies of redo log files. They’re no different from redo log files except that they get a new name when they’re created.

Not all databases have archive log files. It depends on whether you turn on archiving. By turning on archiving, you can recover from nearly any type of failure providing two things:
✓ You have a full backup.
✓ You haven’t lost all copies of the redo or archive logs.
There is a small amount of overhead with database archiving:
✓ I/O cost: The ARCn process has to copy each redo log group as it fills up.
✓ CPU cost: It takes extra processing to copy the redo logs via the ARCn process.
✓ Storage cost: You have to keep all the archive logs created between each backup.

Server and initialization parameter files

Server and initialization parameter files are the smallest files on your system:
✓ PFILE, or parameter file, is a text version that you can read and edit with a normal text editor
✓ SPFILE, or server parameter file, is a binary copy that you create for the database to use after you make changes.

PFILEs and SPFILEs have information about how your running database is configured. This is where you configure the following settings:
✓ Memory size
✓ Database and instance name
✓ Archiving parameters
✓ Processes
✓ Over 1,900 other parameters (From this only at max 30 are used,1600 are hidden and rest are taken care by oracle internally)

Whenever you start your database, the very first file read is the parameter file. It sets up all your memory and process settings and tells the instance where the control files are located. It also has information about your archiving status.The PFILEs and SPFILEs are under the directory where you installed the database software. This directory is called the ORACLE_HOME.