ORACLE TRINITY

ORACLE TRINITY

How Oracle manages the security to database not in terms of user loggings?

I call this the Oracle Trinity.

The great potential energy is stored in the parameter file that enabled the trinity become active.

Oracle depends on three physical structures after the instance is created using the initialization parameter file. As long as those three physical structures are secure Oracle Database is OK.

You need out of those three two structures available to recover the third in case some thing unwanted happens to one of those structures.

When Oracle creates the instance control files are created/identified as part of the instance. The control files contain the structures of for a database. The controlfiles also have the begin SCN and end SCN numbers for all the datafiles used by Oracle for a given database. The basic structures for identification of the database lay in the controlfiles for the database. The data files shall have SCN numbers in headers and redo logs files contain all the SCN numbers for each transaction. The consistency is achieved by comparing the SCN between the control files and data files and then if necessary applying those transactions from the redo log files to fill the gaps by rolling forward those transactions and filling up of the gap between the SCNs in Controlfiles and data files. This is the technique for the recovery of databases.

They are vital and hence these physical structures are multiplexed by oracle and Oracle recommends that you need at the least 3 controlfiles located on three different disks for security. If all the control files are lost and you do not have any backup control file or you do not have any create control file command saved you have lots of problems to open the database. If you are using RMAN, even those structures required for identifying RMAN are stored in the control file. You can control the entries that are to be retained in the control file by setting an initialization parameter. The parameter CONTROL_FILE_RECORD_KEEP_TIME specifies the minimum number of days before a reusable record in the control file can be reused. In the event a new record needs to be added to a reusable section and the oldest record has not aged enough, the record section expands. If this parameter is set to 0, then reusable sections never expand, and records are reused as needed.

When the instance is started it reads the control files and find the datafiles and redo log files spawns SGA and starts the database for user transactions.

So after the controlfiles the other physical structures you need for an Oracle database are datafiles and redolog files.

Datafiles are the files that contain data and redo log files contains all the committed and uncommitted transactions.

The datafiles are or permanent and temporary in nature and are associated with the tabelspaces which are the top most logical structures for any database. These tablespaces are to provide logical space for storing the data structures, index structures and undo images for the uncommitted DML activity and space for doing sorting activities with in the database.

The redolog files collects all the transactions performed by user and oracle against the database. They are online redo log file and archived redo log files. The committed transactions are flushed from the redolog buffers to online redo log files and from there to archived redo log files. The online redo log files are needed for crash recovery and transaction recover by Oracle. Then the database is opened; to open the database in a consistent mode these online redolog files are required. If these files are lost, the database cannot be opened. As their nature is such a vital Oracle recommends that you create a database with at the least two members for each group with two groups minimum. The location of these redo log groups should be spread across the disks for security purposes. Multiplexing of the redo log files is provided to ensure the security to database.

~This was proposed by me in 1997