Pages

Friday, February 8, 2019

What happens while creating the database?

What happens while creating the database?


1.The oracle engine will creates the controlfile. The location and name of the file will be taken from the parameter file.

After the creation of the controlfile,

2.The Oracle will mount the database and then create the system tablespace. And in the system tablespace, a rollback segment with the name system is created. This rollback will exist till the life time of the database and will take care of all transactions(which are performed against the system tablespace).

3.The Controlfile records all the physical files as entries.

4.Now, Oracle has to create base tables in the system tablespace.
Base tableslike TAB$, SYN$, USER$, VIEWS$, .,.,
For creation of base tables, oracle needs the definitions of these tables. The definitions of these base tables have been pre-defined by oracle in a file called sql.bsq.
When we execute the create database command, this file is called.
After execution, the database is opened.

5.Execution of catalog.sql – $ORACLE_HOME/rdbms/admin/catalog.sql

This view creates most of the views, synonyms and public synonyms that are used by the DBA and programmer.

catproc.sql :- Creates most of the packages used for Oracle Programming.
The location of the script is $ORACLE_HOME/rdbms/admin/catproc.sql

pupbld.sql :- Product user profile, This script will create certain tables and roles in the system user that will be used by the DBA to restrict particular user from executing particular command.
The location of the script is $ORACLE_HOME/sqlplus/admin/pupbld.sql.

background_dump_dest = /ora1/oradata/sandy/bdump
If any error is encountered in any of the background processes. Then, those errors would be recorded in this directory. It also contains the alert log file

core_dump_dest=/ora1/oradata/sandy/cdump
It will contain the trace files associated with any memory leaks that may occur within the database. Memory leaks usually occur when a programmer  does not close a pl/sql cursor that had been opened previously. Any errors related to SGA will be recorded to CDUMP directory.

user_dump_dest=/ora1/oradata/sandy/udump
It contains the files that have been traced by the users or DBA.

Process Monitor (PMON):-

If a user updates or performs some dml operation on a set rows but does not commit then that set of rows will be locked. Thus other users cannot perform dml on those rows.
In the mean while, if the client system from where the dml operation was performed gets rebooted or disconnected from the oracle server.
Then, PMON will come into picture and release the locks that had been placed by the dead user process.
After the locks are released PMON will rollback the transaction.

ALERT_LOG_FILE :-

This is the most important file for the DBA. The location is in /ora1/oradata/sandy/bdump. This file will contain the entire physical changes that are made to the database. It will also contain log of every action that is done by the database including..

Startup.
Shutdown.
Startup of background processes.
Opening the files.
Any database related errors.

No comments:

Post a Comment