Controlfile and Logfile in Oracle

Back to: Oracle DBA Tutorials

V$Controlfile/ V$Logfile in Oracle with Examples

In this article, I am going to discuss V$Controlfile/ V$Logfile in Oracle Database with Examples. Please read our previous article where we discussed cdb_tablespaces and v$tablespace in Oracle.

V$Controlfile/ V$Logfile in Oracle

In this article, we will learn about how to query control files and log files. Control files and redo log files are the most essential part of the database. It is required during the backup and recovery of the database.

Note: Open the Linux machine and start the database. Once the database is up and running open the pluggable database as well. If all the previous steps are done then open the SQL developer and login as sysdba.

Let us check whether we are in a container database or a pluggable database.

We are the ROOT database. Let us just open all the pluggable databases present in the container.

Command: alter pluggable database all open;

Let us run the list and status of pluggable database queries to check whether all the pluggable databases are opened or not.

Query: select con_id,name,open_mode from V$containers;

So, all the pluggable databases in the container are up and running fine.

Redo Log File in Oracle:

Redo log file stores all the changes made to the database. This is the purpose of redo log files. The database maintains online redo log files to protect against any data loss, especially after an instance failure. The online redo log files enable the oracle database to recover the committed data that is not yet written into the data files. We require a minimum of 2 redo log files. So, the purpose of the redo log files is backup and recovery. The redo log files store the changes that were made to the database but not yet committed. We need a minimum of 2 redo log files. One is always available for writing and the other redo log file is available for being archived.

Note: Redo log file exists in the instance. The redo log file is not for a single container. Let us run a query to check the existence of redo log files.

Query: select * from v$logfile;

We have three redo log files in our instance. These three redo log files have the con_id as 0 which indicates the redo log files are not allocated specially for any container. The location of the three redo log files is “/uo1/app/oracle/oradata/JAYADB/redo_files”. We have also groups 1,2,3 which will be explained later. Here we need to understand how we can use the data dictionary v$logfile to query the redo log files.

Control File in Oracle:

The control file stores metadata about the data files and the online redo log files like names, locations, and status, and this info is required by the instance to open the database. In order to start the database, we need to know where the location of data files, and redo log files and all this information is stored in control files. We have a view called V$controlfile for the control file.

Note: Control file exists for the whole container and not for the particular instance or not for the particular database.

Query: select * from V$controlfile;

We have two control files control01.ctl, and control02.ctl and the con_id for both these control files is 0 which indicates that these control files do not belong to any database or any instance. These control files belong to the instance.

This article is just an indication of how we can use the data dictionary v$logfile and V$controlfile to check the location of the redo log files and the control files.

In the next article, I am going to discuss Pluggable Database Save State in Oracle with Examples. Here, in this article, I try to explain V$Controlfile/ V$Logfile in Oracle Database and I hope you enjoy this V$Controlfile/ V$Logfile in the Oracle article.