27. Interview QS and some Questions - sayanbhattacharya1234/Oracle_DBA GitHub Wiki

1. Cache hit:

If the data block required for the user's query is already available in the database buffer cache then it is called cache hit.

2. Caceh miss:

It Occurs when the requested data block is not found in the Database Buffer Cache. By the following steps a new data block come to the database buffer cache , then it serve to the user

3. Soft Parsing :

if an user again writes a query from user process to server process, which has its hashcode already present in database sql area, then all the earlier steps are avoided and server process follows the previous execution plan and executes and serve it to the user. this is soft parsing.

4. Hard Parsing

If an user first time writes a query from user process to server process , server process takes it to the library cache, its divided into hashcode, then in data dictionary the syntax and simentic check are done. Then the query optimizer set the execution plan. All steps from start to finish is known as Hard Parsing.

##.

5. Function of redo log buffer cache:

When an user writes DDL ,DML queries. First the query is written into the redo log buffer cache. The change is done in the data block in the database buffer cache. Following steps --

Every 3 seconds it write the Redolog buffer cache to redo log file through LGWR.

If 1/3rd of redolog buffer cache is full. then it write the Redolog buffer cache to redo log file through LGWR.

If an user exxecute commit; then it write the Redolog buffer cache to redo log file through LGWR.

if 1 MB is full then it write the Redolog buffer cache to redo log file through LGWR .

6. ORACLE HOME vs ORACLE BASE:

Oracle home :: Oracle Home is the location where the oracle application vertions is installed. [Ex= ORACLE 19c, ORACLE 12c ] We can have multiple oracle home in one server. Technically it is an linux environment variable containing the location of the oracle application.

The Location of the Oracle Home is

/u01/app/oracle/product/19.0.0/dbhome_1/

.

Oracle Base is the location where the binary files of database is located like data files, control files, alert logs etc.

The location os the oracle base is

/u01/app/oracle/

7. Location of Parameter files : SPFILE and PFILE >

They both are located in $ORACLE_HOME/dbs/

8. If both the parameter files are deleted by mistake when the database is running then the work of DBA in that scenario:

If Spfile and Pfile both are deleted, when the database is running.Then we can recover the parameter files from the memory. The memory of the running database has the parameter file inside. The command = create spfile from memory ;

Recovers the physical parameter fils.

9. In password file whom Password is stored and Why:

Only sysdba role users password are stored in the password file.

Because only the sysdba role user can login if the database is in shutdown. other users data is stored in the data dictionary.

10. What is instance of database.

When users connect to the database basically they are connected into Instance of Database

Instance of Database : its nothing but the combination of system global area and background process.

11. What are the Startup processes.

Startup process are 3 types . a.Startup nomount; , b.Startup mount; c. Alter database open;

Startup nomount: In this state database server either search or read the parameter files [ sp file or pfile].By default server reads sp file , if spfile is not present then it reads p file and start. Then it create a blank structure of SGA in memory. then it starts all background processes . if no file is exist then database will not started.

Startup mount:

Alter database mount : this state reads control file. Parameter has their path. From there it will read the control files. If the multiple control files has different contents then the database will also not start. From there the server learns that what data files are present in database as the control files has the metadata of the data files.

Alter database open : in open state server reads the data files. If any data file is missing physically which are written in control file then it will also not start

12. What is Static view and Dynamic View , and their difference.

Static views = This view gets records or datas from datafile.

this view cannot execute even the database is in mount state, it can execute only the database is in open state. Any view start with dba_ is Static View

Dynamic views = This view gets records or datas from control file.

thats why this view can execute even the database is in mount state Any view start with v$ is dynamic view.

Difference

the primary difference is that Static Views (Data Dictionary Views) display the structural metadata of the database, whereas Dynamic Views (Dynamic Performance Views) capture the real-time operational state and performance metrics of the Oracle instance

Static view start with dba_ but dynamic view start with v$.

Static view cannot execute even the database is in mount state but dynamic view can execute even the database is in mount state

13. What is issys modifiable

'Issys_modifiable' means = to which parameter we changed if effect immediately / i need to restart the database / i need to restart the session

Those parameter shows 'IMMEDIATE' = that parameters effect immediately after changes value. those parameter showa 'FALSE' = we need to restart the database after change any value. ( we need downtime)without any verified approval we dont do anything as a dba. the parameter shows 'Deferred' = we have to restart the session after change any value.

14. What is the function of data dictionary.

.

=== Some other Questions ===

Restore validate:

Restore validate is fininng out whether the data files are in a position to restore like if they are obsolete are not according to retention policy.