Repository navigation
27. Interview QS and some Questions
If the data block required for the user's query is already available in the database buffer cache then it is called cache hit.
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
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.
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.
##.
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 --
If 1/3rd of redolog buffer cache is full. then it write the Redolog buffer cache to redo log file through LGWR.
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.
/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.
/u01/app/oracle/
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.
Because only the sysdba role user can login if the database is in shutdown. other users data is stored in the data dictionary.
Instance of Database : its nothing but the combination of system global area and background process.
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.
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
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
thats why this view can execute even the database is in mount state Any view start with v$ is dynamic view.
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 cannot execute even the database is in mount state but dynamic view can execute even the database is in mount state
'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.
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.