29 - [Oracle 19C] v$logfile vs V$CONTROLFILE

In this lecture we will learn how to query the log files and also the control file.

 

 

 

SQL> show con_name

CON_NAME
------------------------------
CDB$ROOT


SQL> select con_id,name,open_mode from v$containers;

    CON_ID NAME 	   OPEN_MODE
---------- --------------- ----------
	 1 CDB$ROOT	   READ WRITE
	 2 PDB$SEED	   READ ONLY
	 3 ORCLPDB	   READ WRITE

 

 

 

• redo log files store all changes made to the database as they occur.

The database maintains online redo log files to protect against data loss. 
Specifically,after an instance failure.
the online redo log files enable Oracle Database to recover
committed data that it has not yet written to the data files.
--we need minimum 2 redo log files
--one is always available for wrtiting , while the other is being archived  
very imprtant note: redo log files exists in the whole instance, not for particular container 

 

SQL> select * from v$logfile;

    GROUP# STATUS  TYPE    MEMBER				    IS_     CON_ID
---------- ------- ------- ---------------------------------------- --- ----------
	 3	   ONLINE  /u01/app/oracle/oradata/ORCL/redo03.log  NO		 0
	 2	   ONLINE  /u01/app/oracle/oradata/ORCL/redo02.log  NO		 0
	 1	   ONLINE  /u01/app/oracle/oradata/ORCL/redo01.log  NO		 0

 

 

 

• Control files

Stores metadata about the datafiles and online redo log files ( like names ,locations  and statuses ) 
and this info is required by the database instance to open the database.
very imprtant note: Control files exists in the whole instance, not for particular container 

 

/*
*/

select * from V$CONTROLFILE;  
/*
SQL> select * from V$CONTROLFILE;

STATUS	NAME						   IS_RECOVER BLOCK_SIZE FILE_SIZE_BLKS     CON_ID
------- -------------------------------------------------- ---------- ---------- -------------- ----------
	/u01/app/oracle/oradata/ORCL/control01.ctl	   NO		   16384	   1142 	 0
	/u01/app/oracle/oradata/ORCL/control02.ctl	   NO		   16384	   1142 	 0

*/