DBA- Architecture- Questions-- More than 100 Questions

ARCHITECTURE -QUESTIONS

1. What is oracle Instance?

·        Instance is nothing but combination of SGA+ Background process

·        SGA means system global area. It allocates memory for all subcomponents.

 2. Difference between PGA,UGA?

When you are running dedicated server then process information stored inside the process global area (PGA) and when you are using shared server then the process information stored inside user global area (UGA).

Dedicated connection: one client to one server

Shared connection: Many clients can connect with single server.

3.What is use of user process and server process?

Whenever user process will start here two types of checking will be there.

     syntactical checking means whether the SQL statement is valid or not

     symantical checking  means whether the user is valid or not,it checks the privileges

once completed user process and then server process will start

here server will establish the connection between the user process and and instance through listener.

4.Explain SGA,PGA components?

·        Two types of components are there SGA.

 Mandatory components :

·        Data buffer cache

·        Redo log buffer cache

·        Shared pool: Library cache, Data dictionary cache

Optional components:

 

·        Java pool

·        Stream pool

·        large pool

PGA ( Programme global area):

·        Session memory

·        Pvt SQL Area

·        Shared SQL Area

·        Sorting

5.  How many types of background process we have?

·        Mandatory Background process

·        Optional background Process

6.  Explain mandatory background process. How many mandatory process we have?

·        DBWR- DB writer

·        LGWR - Log writer

·        CKPT - Check point

·        PMON - Process monitor

·        SMON -System monitor

If the mandatory background process goes down, Instance terminates suddenly

7.How many optional background process we have?

ARCH	Archiver
MMON	Memory monitor
MMAN	Memory manager
CTWR	Change track writer
CJQU	Co- ordinated job queue process
MMNL	Magnetricity monitor light weight process
ASMB	ASM back ground process
RBAL	Red balance master

 

8.What is the difference between private SQL Area and Shared SQL area?

·        Private SQL area will maintain the  Execution plan of the statement

·        Shared SQL area will  maintain the cursor information.

9. How many types of database connections we have ,explain?

Dedicated connection:

·        When the dedicated connection happened, the connection information store in PGA Pvt SQL Area.

·        here one to one connection will be happened. It  means one client to one server.

 

Shared connection:

·        When the shared connection happened ,the information store SGA Pvt SQL area.

·        Here many to one connections will be happened. It means many clients can connect with single server.

·        Advantage: is to reduce the memory connections

 

10.Explain Select statement  and update statement?

SELECT STATEMENT:

1)      Client request server to fetch the information from DB

2)      At the instance sid, it will assign a statement id, hash value

3)      Parsing will be done parsing

i)                 Syntactical check

ii)                Semantical check

Either it should be soft parsing or hard parsing.

Soft parsing – 2^nd  time of execution

Hard parsing--- 1^st time execurion.

4)      Optimizer

i)                 Rule based optimizer—based on the pre requested rules as index.

ii)                Cost based optimizer--  based on the statistics

5)      Save the execution plan.

6)      Fetch the information from database into dbbc

7)      Show the results to end users;    

PGA will maintain the session information and sorting operation.

Session memory means login information, log out information.

And then Instance process will start

Instance is nothing but combination of  SGA+ Background process.

SGA means system global area. It allocates memory  for all components and then go to the library cache.

A. Library cache: Library cache will maintain the SQL/PLSQL code and maintaining the parsing operation and library cache will generate the #code for that statement and optimizer will generate the execution plan.

two types of parsing will be happened here

1.soft parsing: If the SQL statement is running second time .this is called soft parsing .

2.Hard parsing: if the SQL statement is running first time. This is called Hard parsing.1

B. Data Dictionary Cache: For the SQL statement it will maintain the Table and view structure. it can store a blocks has a Rows. this is called metadata. It will store all the metadata for SQL statement .

under the data dictionary will have

private SQL area: It will maintain the execution plan for the statement.

Shared SQL area: It will maintain the cursor information.

Note: parsing means address of the statement.

Database buffer cache:

Data buffer cache will  maintain the blocks information. It can retrieve the block information from disk. That is called data file. It follows the FIFO (First in Fist out)

LRU; Which is going to use the blocks

MRU; which is going to move out from the blocks

Dirty blocks Or modified blocks :Modified transactions is nothing but dirty blocks

 cache miss: will fetch the data from data files

cache hit: will fetch the data from memory itself.

Here all the business and metadata will store

After that statement will display the values.

11. What is Oratab file?

"Oratab" is a file created by Oracle in the  /etc  directory when installing database software. Originally ORATAB is to find out the how many databases, software versions are configure on  a server. "oratab" is a file created by Oracle in the /etc or /var/opt/oracle directory when installing database software. Originally ORATAB was used for SQL*Net V1, but lately is's being used to list the databases and software versions installed on a server.

This file may contain comments staring with a pound signs (#) in column one, and data lines consisting of entries in the following format:

database_sid:oracle_home_dir:Y|N

- database_sid is the system id (SID) of an Oracle instances on the server.

- Oracle_home_dir is the ORACLE_HOME directory associated with this instance.

- The Y|N flags indicate if the instance should automatically start at boot time (Y=yes, N=no).

Besides acting as a registry for what databases and software versions are installed on the server, ORATAB is also used for the following purposes:

• Oracle's "dbstart" and "dbshut" scripts use this file to figure out which instances are to be start up or shut down (using the third field, Y or N).
• The "oraenv" utility uses ORATAB to set the correct environment variables.
• One can also write Unix shell scripts that cycle through multiple instances using the information in the oratab file.

12.What is difference between oracle SID and Oracle service name?

Oracle SID is the unique name that uniquely identifies your instance/database where as the service name is the TNS alias can be same or different as SID.

13. What is oraInventory ?

oraInventory is repository (directory) which store/records oracle software products & their oracle_homes location on a machine. This Inventory now a days in XML format and called as XML Inventory where as in past it used to be in binary format & called as binary Inventory.

There are basically two kind of inventories,

One is Local Inventory (also called as Oracle Home Inventory) and other is

Global Inventory (also called as Central Inventory).

 

14 .What is Local Inventory ?

Inventory inside each Oracle Home is called as local Inventory or oracle_home Inventory. This Inventory holds information to that oracle_home only.

 

15 .What is Global Inventory ?

Global Inventory holds information about Oracle Products on a Machine. These products can be vari9ous oracle components like database, oracle application server, collaboration suite, soa suite, forms & reports or discoverer server . This global Inventory location will be determined by file oraInst.loc in /etc (on Linux) or /var/opt/oracle (solaris). If you want to see list of oracle products on machine check for file inventory.xml under ContentsXML in oraInventory Please note if you have multiple global Inventory on machine check all oraInventory directories)

You will see entry like

HOME NAME=”ORA10g_HOME” LOC=”/u01/oracle/10.2.0/db” TYPE=”O” IDX=”1?/

 

16.Can I have multiple Global Inventory on a machine ?

YES

you can have multiple global Inventory but if your upgrading or applying patch then change Inventory Pointer oraInst.loc to respective location. If you are following single global Inventory and if you wish to uninstall any software then remove it from Global Inventory as well.

 

17.What to do if my Global Inventory is corrupted ?

No need to worry if your global Inventory is corrupted, you can recreate global Inventory on machine using Universal Installer and attach already Installed oracle home by option

-attachHome

./runInstaller -silent -attachHome -invPtrLoc $location_to_oraInst.loc

ORACLE_HOME=”Oracle_Home_Location” ORACLE_HOME_NAME=”Oracle_Home_Name”

CLUSTER_NODES=”{}”

18. Oracle Database 11g New Feature for DBAs?

1) Automatic Diagnostic Repository [ADR]

2) Database Replay

3) Automatic Memory Tuning

4) Case sensitive password

5) Virtual columns and indexes

6) Interval Partition and System Partition

7) The Result Cache

8) ADDM RAC Enhancements

9) SQL Plan Management and SQL Plan Baselines

10) SQL Access Advisor & Partition Advisor

11) SQL Query Repair Advisor

12) SQL Performance Analyzer (SPA) New

13) DBMS_STATS Enhancements

14) Total Recall (Flashback Data Archive)

Note: The above are only top new features, there are other features as well introduced in 11g which will be included subsequently

 

19.what is bind variables?

With bind variable in SQL, oracle can cache queries in a single time in the SQL cache area. This avoids a hard parse each time, which saves on various locking and latching resource we use to check object existence and so on.

20.what is redo log buffer? what kind of information maintained?

Redo log buffer cache will maintain the DML information. Modified transactions will maintain the DML.

21.what is the parsing?

Parsing means address of the statement.

22.Difference between library cache and data dictionary cache and execution plan?

·        Library cache will maintain the SQL,PLSQL code and parsing operation also.

·        Data dictionary cache will maintain the  table and view structure. it can store blocks has a rows.

           Under data dictionary will have.......

·        Private SQL area: It will maintain the  Execution plan of the statement

·        Shared SQL area: It  will  maintain the cursor information.

23.Difference between soft parsing and hard parsing?

·        Hard parsing: If the SQL statement is running first time that is called hard parsing.

·        Soft parsing: If the SQL statement is running Second time that is called soft parsing.

24. What are the Database mandatory files?

·        Control files

·        Redo log files

·        Data files

25. Explain about CRD files?

CONTROL FILES:

Control file is going to  content couple of things

·        DB Name

·        Names & location of DB files & Redo files

·        Current log sequence number

·        DB creation time

·        Check point Information

v  Control file is a binary file, we cannot  read, but oracle can read. Another important thing is ,this control file is very important file.

v  if we lose control files then we do not know where is data files .In that case database will not going to start.

v  Whenever we start instance, we need to know what are the data files & redo log files. control file will give that information to data files & redo log files

v  And whatever control file is not there, then  we going to trouble. you cannot open the data file hence data file is not going to start up.

 

To Avoid that we are always have Multiplex control file.......

Multiplex control file on different Disks:

 

v  Every oracle database should have at least two control files, each stored on a different physical disk.

v  If a control file is damaged due to disk failure, the associated instance must be shutdown.

v  once the disk drive is repaired, the damaged control file can be restored using the intact copy of the control file from the other disk. and then instance can be  restarted.

 

This is called Multiplex file...............

REDO LOG FILES:

Redo log file essentially a transaction log of the data base, that  means by following the redo log files can create or take my data base to particular state .

The only purpose of Redo log files is to recover  the database in case of media failure.

The characteristics of online redo logs are

·        It require 2 Redo log files or groups

·        Minimum size of each online redo log file is 2MB

·        Each group will maintain minimum one member, if it contains 2  members  within a group called mirroring .

·        same size will be created to each group

·        when log writer starts the writing to fill the log file, that  means it is now current state.

·        when online redo log file is full, that means it is now active state.

·        when online redo log file is empty, that means it is  now Inactive state

·        when online redo log member becomes active state then log switch occur ,when log switch occur these transactions will store into the Archive log location.

·        when online redo log file is full, oracle starts writing the data in another online redo log  file belongs to different room, this we call it as "logs witch'.

·        we can't remove online redo logs ,when it is active and current state.

 

Data files:

·        A data file is a file that is part of an Oracle database. Data files are used to store data - including user data and undo data. Data files are grouped together into table spaces

 26. What is SCN number? when SCN number will be generated?

·        SCN means service change number. when log switch occur, checkpoint will generate the SCN number for committed data.

27.What is instance recovery? when instance recovery will be happened and explain bout roll backward and role forward?

·        Instance  recovery is nothing but roll backward and roll forward.

·        If it all the transaction is committed, oracle is going roll forward, that means updates the committed data to the respective data file.

·        In case of roll backward ,oracle is going to delete the data (or)updates the past image in case of instance recovery

28.what is log switch. when log switch occurred?

·        Once one online redo log file is filled log switch will occur, then check point will generate SCN number for committed transactions  and then it went to data file.

29. What process will get data from data files to DB cache?

        Server process

30. What background process will write undo data?

       DBWR

31.      What are physical components of Oracle database?

Oracle database is comprised of three types of files. One or more datafiles, two or more redo log files, and one or more control files. Password file and parameter file also come under physical components.

32. What are logical components of Oracle database?

Blocks, Extents, Segments, Table spaces

33. What are the differences between LMTS and DMTS?

·        Tablespaces that record extent allocation in the dictionary are called dictionary managed tablespaces.

·        tablespaces that record extent allocation in the tablespace header are called locally managed tablespaces.

34.  what diff b/w uniform and uniform autoallocation?

Uniform—next extend will be allocated based on the uniformly as 1MB.

Uniform allocation;- initial extent will be 64k upto reaching 8MB. After 8MB, next extent allocation will be allocated with 1MB upto 64MB. after 64MB, next extent allocation will be allocated as 8MB up to 1GB,after 1GB,  next allocation will be 64MB up to inifinate.

35. what are the shutdown types?

            I.           Shutdown normal

           II.          Shutdown transactional

         III.          Shutdown immediate

         IV.          Shutdown abort

36) startup modes?

        I.          Startup nomount – check for PFILE

Alter database mount;

      II.          Startup mount – check for controlfile

Alter database open;

     III.          Startup open – sanitary check(scn number verification)

Alter database close (TO become mount)

Alter database dismount; (to become nomount—ideal instance )

37) what is the scope parameter in spfile?

Scope= when to assign the memory allocation to parameter . its based on 3 valaues

If it is SCOPE=memory – parameter allocation is starts from nw. It will be applicable for upto this session

If it is SCOPE=spfile –parameter allocation from the next startup

If ist is SCOPE=BOTH – combination from memory+scope

38) what are the parameter to mention in the OMF db creation?

Db_create_file_dest, db_create_online_log_dest

 34. What is a datafile?

 Every Oracle database has one or more physical datafiles. Datafiles contain all the database data. The data of logical database structures such as tables and indexes is physically stored in the datafiles allocated for a database.

35. What are the contents of control file?

Database name, SCN, LSN, datafile locations, redolog locations, archive mode, DB Creation Time, RMAN Backup & Recovery Details, Flashback mode. 

36. What are the uses of undo tablespace or redo segments?

 Every Oracle database must have a method of maintaining information that is used to roll back, or undo, changes to the database. Such information consists of records of the actions of transactions, primarily before they are committed. Oracle refers to these records collectively as undo.

Undo records are used to:

Roll back transactions when a ROLLBACK statement is issued

Recover the database

Provide read consistency

When a rollback statement is issued, undo records are used to undo changes that were made to the database by the uncommitted transaction.

During database recovery, undo records are used to undo any uncommitted changes applied from the redo log to the data files.

Undo records provide read consistency by maintaining the before image of the data for users who are accessing the data at the same time that another user is changing it.

37.. How undo tablespace can guarantee retain of required undo data?

Alter tablespace undo_ts retention guarantee;

38. What is the use/size of temporary tablespace?

Temporary table spaces are used for special operations, particularly for sorting data results on disk. For SQL with millions of rows returned, the sort operation is too large for the RAM area and must occur on disk.  The temporary tablespace is where this takes place.  

Each database should have one temporary tablespace that is created when the database is created. You create, drop and manage tablespaces with create temporary tablespace, drop temporary tablespace and alter temporary tablespace commands, each of which is like it’s create tablespace counterpart. 

The only other difference is that a temporary tablespace uses temporary files (also called tempfiles) rather than regular datafiles.

39. How to create password file?

$ orapwd file=orapwSID password=sys_ password force=y  nosysdba=y

40. What is the difference between delete and truncate?

·        Truncate will release the space.

·        Delete won’t. Delete can be used to delete some records.

·        Truncate can’t. Delete can be rolled back.

·        Delete will generate undo (Delete command will log the data changes in the log file where as the truncate will simply remove the data without it. Hence data removed by Delete command can be rolled back but not the data removed by TRUNCATE).

·        Truncate is a DDL statement whereas DELETE is a DML statement. Truncate is faster than delete.

41. What is the difference between schema and user?

Schema is collection of user’s objects.

42. What is the difference between SYSDBA, SYSOPER and SYSASM?

SYSOPER can’t create and drop database.

SYSOPER can’t do incomplete recovery.

SYSOPER can’t change character set.

SYSOPER can’t CREATE DISKGROUP; ADD/DROP/RESIZE DISK

SYSASM can do anything SYSDBA can do.

43. How to find out whether database/tablespace/datafile is in backup mode or not?

Query V$BACKUP.

44. How you will recover if you lost one/all control file(s)?

Lost one controlfile:

a. Shutdown the database =shutdown

b. Copy and rename the controlfile from the existing or mirror controlfile at os level ‘OR’ Remove the controlfile location from the pfile

c. start the database=startup

 Lost of all controlfile: using the backup:

a. shut the database (abort)=shutdown abort

b. startup the database in nomount state =startup nomount

c. restore the controlfile from the autobackup

d. open the database with resetlogs =alter database open resetlogs

Lost of all controlfile: without using the backup:

a. create the controlfile manually with all the datafile locations

b. mount the controlfile

c. open the database with resetlogs

45. How can you transport tablespaces across platforms with different endian formats?

RMAN

46. In one server can we have different oracle versions?

               Yes

47. How do sessions communicate with database?

               Server processes execute SQL received from user processes.

48. Which SGA memory structure cannot be resized dynamically after instance startup?

               Log buffer

49. When a session changes data, where does the change get written?

               To the data block in the cache, and the redo log buffer

50. How many maximum no of control files we can have within a database?

               8

51.Explain active or inactive or current state of the redo logs ?

·        when log writer starts the writing to fill the log file, that  means it is now current state.

·        when online redo log file is full, that means it is now active state.

·        when online redo log file is empty, that means it is  now Inactive state

52. Difference between RESETLOGS and NORESETLOGS ?

NORESETLOGS:

The NORESETLOGS option does not clear the redo log files during start-up and the online redo logs to be used for recovery. Only used in scenario where MANUAL RECOVERY is started, CANCEL is used, and then RECOVER DATABASE is started.

 

RESETLOGS:

CAUTION: Never use RESETLOGS unless necessary.

Once RESETLOGS is used then the redo log files cannot be used and any completed transactions in those redo logs are lost!!

Before using the RESETLOGS option take an offline backup of the database.

 

53.How to perform manual log switch?

alter system switch logfile;

32..How to take  backup of the control file?

      Alter database backup control file to trace as <'path'>

33.How to create multiplex in control file?

·        connect to database

·        list all the control files in database

·        shutdown the database

·        copy the control file from existing control file

·        Edit pfile and new control file path

·        Startup pfile=$ORACLE_HOME/dbs/<pfilename>

·        start the database

·        check control file using show parameter command

34.How will identify location of control file? and explain about of views?

      select name from v$controlfile;

      desc dba_controlfiles

35.What is archive log. why we need archives?

ARCHIVELOG mode is a mode that you can put the database in for creating a backup of all transactions that have occurred in the database so that you can recover to any point in time.

NOARCHIVELOG mode is basically the absence of ARCHIVELOG mode and has the disadvantage of not being able to recover to any point in time. NOARCHIVELOG mode does have the advantage of not having to write transactions to an archive log and thus increases the performance of the database slightly.

ARCHIVELOG  MODE

Advantages:

·        You can perform hot backups (backups when the database is online).

·        The archive logs and the last full backup (offline or online) or an older backup can completely recover the database without losing any data because all changes made in the database are stored in the log file.

Disadvantages:

·        It requires additional disk space to store archived log files. However, the agent offers the option to purge the logs after they have been backed up, giving you the opportunity to free disk space if you need it.

NO-ARCHIVELOG MODE

Advantages:

·        It requires no additional disk space to store archived log files.

Disadvantages :

·        If you must recover a database, you can only restore the last full offline backup. As a result, any changes made to the database after the last full offline backup are lost.

·        Database downtime is significant because you cannot back up the database online. This limitation becomes a very serious consideration for large databases.

 

ARCHIVES:ARCHIVES IS USED TO RECOVER THE DATABASE.

 

37. How to find archive destination and sequence number and status of archive?

               archive loglist;

38. How to see the archive log location?

show parameter db_recovery_file_dest;

38. what is the views ? we are using to find the archive information?

v$archive_log

39.How check the enable or disable mode  in archive log?

select name,open_mode,log_mode from v$database;

40.What is the Difference  between pfile and spfile?

 PFILE:

·        The PFILE is a text-based file usually called “initSID.ora”, meaning the file will use the ORACLE_SID you defined when you created the database.  If your SID is called TESTDB, the resulting PFILE should be called initTESTDB.ora

·        Inside the PFILE there are database settings called parameters. These parameters help the Oracle programs know how to start.

·        The parameters tell the Oracle programs how much memory to allocate, where to put files related to the database and where certain database files already exist.

·        As the PFILE is text based, one can edit it in an editor like notepad or vi.

·        Depending on which operating system you are running on, your PFILE is located by default in the ORACLE_HOME\dbs

         SPFILE:

·        The SPFILE is different from the PFILE in that it can not be directly edited. This is because it has a header and footer that contains binary values.

·        Since you can not change a SPFILE directly, Oracle allows you to manage the SPFILE via the alter system command.

·        For using an SPFILE, you can reap great benefits. It can be backed up by RMAN (Oracle’s backup and recovery software) every time a change is made or when the database is backed up, which means it’s easier to recover.

·        SPFILES allow you to make dynamic changes to parameters that are persistent. For example

 Alter system set db_recovery_file_dest_size=10g;

·        If we were using SPFILES the parameter would keep the same value, even after a database restart. This means you only have to change the parameter value in one place, and that you can forget having to change it in the PFILE of the database.

·        One of the most important benefits of the SPFILE is that Oracle has introduced many automatic tuning features into the core of the database.  Without an SPFILE, Oracle can not auto tune your database.

·        An SPFILE uses the same formatting for its file name as the PFILE, except the word spfile replaces init.  For instance, if your ORACLE_SID is TESTDB, the resulting spfile would be called spfileTESTDB.ora.

 

PFILE and SPFILE Backup

·        As a DBA the main thing you need to worry about with the SPFILE and PFILES are backing them up. You can use RMAN to backup an SPFILE, or back them up yourself.

·        PFILE is simply a text based file, which means you can copy it to another directory without affecting the Oracle instance.  This is the easiest way to backup a PFILE.

·        To back up an SPFILE, you will first want to convert it to a PFILE. This will create a PFILE named initSID.ora in your $ORACLE_HOME/database (Windows) or $ORACLE_HOME/dbs (Linux/Unix) directory. You can do this with the following syntax

create pfile from spfile;

·        In addition, you can back up the file directly to the preferred location with the command

create pfile=/path/to/backup.ora from spfile;

·        If the time comes that you must put the SPFILE back into place, you can do so with this command

create spfile from pfile=/path/to/backup.ora

·        You can use the V$PARAMETER dynamic view to see the current setting of the different database parameters

select name, value from v$parameter where name = 'control_files';

41.what is location of sp file and p file?

cd$ORACLE_HOME/dbs

42.What is the oracle database default location?

cd$ORACLE_HOME

43.what is oracle home?

·        /home/oracle

44.How to start database with p file?

startup nomount pfile=$ORACLE_HOME/dbs/<pfile>

47.Difference between dynamic views  and data dictionary views? How will you identify ,whether its data dictionary and dynamic views?

Data Dictionary Views	Dynamic Performance Views
The DBA_ views usually have plural names (for example, DBA_DATA_FILES).	The names of the V$ views are generally singular (for example, V$DATAFILE).
The DBA_ views are available only when the database is open and running.	Some V$ views are available even when the database is not fully open and running.
The data contained in the DBA_ views is generally uppercase.	The data contained in the V$ views is usually lowercase.
The data contained in the DBA_ views is static and is not cleared when the database is shut down.	The V$ views contain dynamic statistical data that is lost each time the database is shut down.

 

51.What is uses to store redo logs?

Redo log file essentially a transaction log of the data base, that  means by following the redo log files can create or take my data base to particular state .

The only purpose of Redo log files is to recover  the database in case of media failure.

The characteristics of online redo logs are

·        It require 2 Redo log files or groups

·        Minimum size of each online redo log file is 2MB

·        Each group will maintain minimum one member, if it contains 2  members  within a group called mirroring .

·        same size will be created to each group

·        when log writer starts the writing to fill the log file, that  means it is now current state.

·        when online redo log file is full, that means it is now active state.

·        when online redo log file is empty, that means it is  now Inactive state

·        when online redo log member becomes active state then log switch occur ,when log switch occur these transactions will store into the Archive log location.

·        when online redo log file is full, oracle starts writing the data in another online redo log  file belongs to different room, this we call it as "logs witch'.

·        we can't remove online redo logs ,when it is active and current state.

 

52.How to add members to group?

alter database add logfile member '/u03/app/oracle/oradata/orcl/redo/redo01_b.log' to  group1;

53.create multiplex of online redo log group?

alter database add logfile member '/u03/app/oracle/oradata/orcl/redo/redo01_b.log' to  group1;

alter database add logfile member '/u03/app/oracle/oradata/orcl/redo/redo02_b.log'  to group2;

54.What is the dirty blocks?

Modified transactions  is nothing but dirty blocks.

55.How to drop the online redo logs, when we can able to drop the online redo logs?

alter database drop group;

we can drop the log files when its inactive. we should not drop the log files when it is current

56..How to create the online redo log groups?

alter database add log file group<'group path'> size ;

57.How to resize the online redo log groups?

alter database add logfile '<group path'> size ;

59.Shall I  Maintain different size of online redo log files ?If it is NO ,explain about reason?

online redolog file should be same.

60.how to check whether database is up ,running or not?

               ps -ef|grep pmon

61.How to set environment for database?

               .oraenv

    62. What exactly will happen when nomount to mount, and mount to open?

·        In no mount state. oracle is going to read the parameter file(init.ora) or SP file.

·        Based on the parameter specified ,oracle is going to allocate memory for SGA  and starts the mandatory background process

·        Creates and opens "alert < SID>.log file" in the' bdump directory'

·        from no mount to mount oracle is going to scan the contents of the control file for the physical locations of data files & redo log file.

·        At mount state, oracle will not go to the physical location to check for the existence of online redo log files and data files .

·        From mount to open ,oracle performs" Sanitary check" which is nothing but checking for the existence of redo logs &data files, along with these oracle also checks for the synchronisation of SCN number

65.What is the default location of database?

               cd $ORACLE_HOME/dbs

64. When will get  ' SNAP SHOT ERROR' ?How you will rectify this ?

·        When the user gives select query, it will fetch the data from undo table space. if  not get the read consistency data we will get SNAP shot error. that means updating  is happening while giving query

·        We have to increase the undo retention period and undo table space size. to solve this error. otherwise frequently gives  commit.

65. What is ORA600?

 If we get ora600 error ,we will raise the case(Service Request) with oracle support.

66.What are you needed to raise the SR?

·        we will check OS version ,Bit size, name, database version

·        we will upload trace files in the meta link.

67. How to see the alert  log file?

·        Show parameter dump:

·        In that parameter dump trace file generated.

68.. What is the difference between in 10g and 11g when it comes to alert log file?

·        In 11g all the trace files depending on the one directory location. that is Diag location

·        In 10g all the trace files depending on the dump files that is A,B,C,D dump.

69.What are the parameters of undo?

·        undo_management

·        undo_retention

·        undo_tablespace

70. How to find out the expired ,unexpired used space in undo?

select tablespace name,status,sum(bytes)/1024/1024 'used_mb' from dba_undo_extents group by tablespace_name,status;

71.How will you identify whether undo tablespace is guranty or noguranty?

select tablespace_name, retention from dba_tablespaces where tablespace_name like '%undo%';

72. How to find out the undo table size?

select sum(bytes)/1024/1024, tablespace_name from dba_data_files where tablespace_name='<tablename>' group by tablespace_name;

73.How to identify instance start with p or sp file?

     show parameter spfile

74. Why do we need to backup oracle archive logs along with datafiles?

Archived redo logs are the key to successful media recovery. Back them up regularly

75.Describe 3 -tier architecture?

76.Discuss on logical and physical structure of database?

Ø  An Oracle database is made up of physical and logical structures. Physical structures can be seen and operated on from the operating system, such as the physical files that store data on a disk.

Ø  Logical structures are created and recognized by Oracle Database and are not known to the operating system. The primary logical structure in a database, a tablespace, contains physical files.

Ø  The applications developer or user may be aware of the logical structure, but is not usually aware of this physical structure. The database administrator (DBA) must understand the relationship between the physical and logical structures of a database

Logical structure of Database:

v  Whenever we create a Table T, oracle is going to create  storage unit. and that storage unit called  SEGMENT. That means for any object that is going to consume some storage, one segment must be created for that.

v  This segment will have a initial size (2MB), that is also giving at the time of create table statement.

v  In this case we have not given those parameters, oracle is going to take default parameters.

v  This segment content multiple EXTENTS. That means Each extent is 1MB

v  EXTENT  is basically a contiguous space allocation. Inside the extent the memory allocation contiguous.

v  These Extents consists of number of BLOCKS. Space allocated inside the block that is called Header. oracle block size is 8k.

v  Inside the block we can store table data, index data, PLSQL procedure.

Physical structure: CRD files.

77.Which command is used to view SGA memory allocation?

·        SHOW SGA

·        Select * from v$sga;

·        select * from V$sgainfo;

78.What do you mean by dynamic SGA?

SGA_TARGET: The amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the MEMORY_MAX_TARGET limit. The default value is "0".

 

SGA_MAX_TARGET:This defines the maximum size the MEMORY_TARGET can be increased to without an instance restart. If the MEMORY_MAX_TARGET is not specified, it defaults to MEMORY_TARGET setting.

 

SGA_TARGET is a database initialization parameter (introduced in Oracle 10g) that can be used for automatic SGA memory sizing.

Parameter description:

SGA_TARGET

Property          Description

Parameter type    Big integer

Syntax            SGA_TARGET = integer [K | M | G]

Default value     0 (SGA autotuning is disabled)

Modifiable        ALTER SYSTEM

Range of values   64 to operating system-dependent

Basic             Yes

SGA_TARGET provides the following:

§  Single parameter for total SGA size 

§  Automatically sizes SGA components

§  Memory is transferred to where most needed

§  Uses workload information 

§  Uses internal advisory predictions 

§  STATISTICS_LEVEL must be set to TYPICAL 

By using one parameter we don't need to use all other SGA parameters like.

§  DB_CACHE_SIZE (DEFAULT buffer pool)

§  SHARED_POOL_SIZE (Shared Pool)

§  LARGE_POOL_SIZE (Large Pool)

§  JAVA_POOL_SIZE (Java Pool)

 

 

Enable SGA_TARGET[edit]

SQL> show parameter sga_target

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_target                           big integer 0

SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

sga_max_size                         big integer 600M

 

As we can see our automatic SGA tuning is not enabled so we can enable it by setting the SGA_TARGET parameter value.

 

SQL> alter system set sga_target=500m;

System altered.

SQL> show parameter sga_target

NAME                                 TYPE        VALUE

------------------------------------ ----------- -------

sga_target                           big integer  500M

 

 

Resize SGA_TARGET[edit]

§  SGA_TARGET is dynamic 

§  Can be increased till SGA_MAX_SIZE

§  Can be reduced till some component reaches minimum size

§  Change in value of SGA_TARGET affects only automatically sized components

 

 

SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- -----------

sga_max_size                         big integer 600M

SQL> show parameter sga_target

NAME                                 TYPE        VALUE

------------------------------------ ----------- -----------

sga_target                           big integer 500M

We can resize it to only 600m if we will try to increase it from 600m we will get error.

SQL> alter system set sga_target=605m;

alter system set sga_target=605m

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00823: Specified value of sga_target greater than sga_max_size

For that we must first increase our SGA_MAX_SIZE parameter value. But we must restart out instance because its STATIC parameter.

SQL> alter system set sga_max_size=956m scope=spfile;

System altered.

SQL> startup force

ORACLE instance started.

Total System Global Area 1000189952 bytes

Fixed Size                  1337492 bytes

Variable Size             624953196 bytes

Database Buffers          369098752 bytes

Redo Buffers                4800512 bytes

Database mounted.

Database opened.

SQL> show parameter sga_max_size

NAME                                 TYPE        VALUE

------------------------------------ ----------- --------

sga_max_size                         big integer 956M

SQL> alter system set sga_target=900m;

System altered.

79.What is granules?

A granule is unit of contiguous virtual memory allocated to process.

If an oracle instance starts up ,it allocates the  SGA in granule size memory units. The size of these granules depends on your database version and sometimes  even on the operating system port.

The minimum SGA size is three granules, based on:

1.     One Granule for Fixed SGA (includes redo buffers)

2.     One Granule for Buffer Cache

3.     One Granule for the Shared Pool

 

80.Discuss on algorithm used to manage shared pool and buffer pool?

·        Under shared pool we have Library cache, Data dictionary cache

Library cache: Library cache will maintain the SQL/PLSQL code and maintaining the parsing operation and library cache will generate the #code for that statement and optimizer will generate the execution plan.

two types of parsing will be happened here

1.soft parsing: If the SQL statement is running second time .this is called soft parsing .

2.Hard parsing: if the SQL statement is running first time. This is called Hard parsing.

B. Data Dictionary Cache: For the SQL statement it will maintain the Table and view structure. it can store a blocks has a Columns. this is called metadata. It will store all the metadata for SQL statement .

under the data dictionary will have

private SQL area: It will maintain the execution plan for the statement.

Shared SQL area: It will maintain the cursor information.

Note: parsing means address of the statement.

Database buffer cache:

Data buffer cache will  maintain the blocks information. It can retrieve the block information from disk. That is called data file. It follows the FIFO (First in Fist out)

LRU; Which is going to use the blocks

MRU; which is going to move out from the blocks

Dirty blocks Or modified blocks :Modified transactions is nothing but dirty blocks

 cache miss: will fetch the data from data files

cache hit: will fetch the data from memory itself.

Here all the business and metadata will store

After that statement will display the values.

81.How will you switch on and off data buffer cache advisory?

V$db_cache_advice;

SELECT name, block_size, size_for_estimate, size_factor,estd_physical_reads

  FROM v$db_cache_advice;

 

To disable the buffer cache advisory in Oracle version 9.0:

ALTER SYSTEM SET DB_CACHE_ADVICE=off [SCOPE=spfile];

To Enable the buffer cache advisory in Oracle version 9.0:

ALTER SYSTEM SET DB_CACHE_ADVICE=on [SCOPE=spfile];

To disable all advisories in Oracle version 9.2 and above:

ALTER SYSTEM SET STATISTICS_LEVEL=basic [SCOPE=spfile];

 

82. Explain at least two benefits of  configuring large pool?

1.Configuring the Large Pool for Shared Server Architecture

As Oracle Database allocates shared pool memory to shared server sessions, the amount of shared pool memory available for the library cache and data dictionary cache decreases. If you allocate the shared server session memory from a different pool, then the shared pool can be reserved for caching shared SQL.

Oracle recommends using the large pool to allocate the User Global Area (UGA) for the shared server architecture. Using the large pool instead of the shared pool decreases fragmentation of the shared pool and eliminates the performance overhead from shrinking the shared SQL cache.

By default, the large pool is not configured. If you do not configure the large pool, then Oracle Database uses the shared pool for shared server user session memory. If you do configure the large pool, Oracle Database still allocates a fixed amount of memory (about 10K) for each configured session from the shared pool when a shared server architecture is used. In either case, consider increasing the size of the shared pool accordingly.

2.Configuring the Large Pool for Parallel Query

Parallel query uses shared pool memory to cache parallel execution message buffers when Automatic Memory Management or Automatic Shared Memory Management is not enabled. Caching parallel execution message buffers in the shared pool increases its workload and may cause fragmentation.

To avoid possible negative impact to performance, Oracle recommends that you do not manage SGA memory manually when parallel query is used. Instead, you should enable Automatic Memory Management or Automatic Shared Memory Management to ensure that the large pool will be used to cache parallel execution memory buffers.

83.What are the disk components in oracle?

84.What is system change number (SCN)?

The system change number (SCN) is an ever-increasing value that uniquely identifies

a committed version of the database. Every time a user commits a transaction,Oracle records a new SCN.  You can obtain SCNs in a number of ways,

 

for example,from the alert log. You can then use the SCN as an identifier for purposes of recovery. For example, you can perform an incomplete recovery of a database up to SCN 1030.   Oracle uses SCNs in control files, datafile headers, and redo records.

 

Every redo log file has both a log sequence number and low and high SCN. The low SCN records the lowest SCN recorded in the log file, while the high SCN records the highest SCN

in the log file.

 

85.Where are parameter files stored and how can you start a database using a specific  parameter file?

Cd$ORACLE_HOME/dbs

startup nomount pfile=$ORACLE_HOME/dbs/init.ora

86.What is block,extents,segments and tablespaces?

v  Whenever we create a Table T, oracle is going to create  storage unit. and that storage unit called  SEGMENT. That means for any object that is going to consume some storage one segment must be created for that

v  This segment will have a initial size (2MB), that is also giving at the time of create table statement.

v  In this case we have not given those parameters, oracle is going to take default parameters.

v  This segment content multiple EXTENTS. That means Each extent is 1MB

v  EXTENT  is basically a contiguous space allocation. Inside the extent the memory allocation contiguous.

v  These Extents consists of number of BLOCKS. Space allocated inside the block that is called Header.oracle block size is 8k.

v  Inside the block we can store table data, index data, PLSQL procedure.

 

87.At what stage of Instance, control file information is read and can we recover control file and how to know information in control file?

oracle read control file information in mount state.

alter database backup control file to trace as '/u02/app/oracle/home/bkp_control.sql';

cat  /u02/app/oracle/home/bkp_control.sql;

88.How do you resize the data file and tablespace?

desc v$datafile;

select name,sum(bytes)/1024/1024 from v$datafile group by name;

alter database datafile  '/u02/app/oracle/payroll/pay02.dbf' resize 20m;

We cannot resize the  tables pace. because data file is attach to table space

alter tablesace <tablespcename> add dataflle '/u02/app/oracle/payroll/pay02.dbf' size 20m;

desc dba_data_files;

select file_name ,tablespace_name from dba_data_files;

90.Name the views used to look at the size of a data file, control files  block size, determine free space in a table space? and Table spaces related ?

To check datafile size:

desc dba_data_files;

select file_id,file_name,tablespace_name,bytes/1024/1024,autoextensible, maxbytes/1024/1024 from dba_data_files;

To check tablespace size:

desc dba_data_files;

select tablespace_name,sum(bytes)/1024/1024 from dba_data_files group by tablespace_name;

To check datafile  free space AND tablespace free space:

desc dba_free_space;

select tablespace_name,file_id,bytes/1024/1024 from dba_free_space;

create Tablespace:

select name from v$datafile;

create tablespace bhaskar datafile '/u02/app/oracle/vijay/bhaskar01.dbf'size 10m;

select tablespace_name from dba_tablespaces;

Add data files to tablespace:

alter tablespace suresh add datafile '/u02/app/oracle/vijay/suresh_02.dbf' size 10m;

resize the datafile:

select name from v$datafile;

alter database datafile '/u02/app/oracle/vijay/suresh_03dbf' resize 30m;

To change the status of tablespace:

select tablespace_name,status from dba_tablespaces;

alter tablespace latha online;

alter tablespace latha offline;

change the logging mode and no logging mode:

desc dba_tablespaces;

select tablespace_name,logging from dba_tablespaces;

alter tablespace <tablespace name> nologging;

Rename the datafile:

alter database rename file '/u02/app/oracle/vijay/latha02.dbf' to '/u02/app/oracle/vijay/latha03.dbf';

91.What do you mean by deadlock?

In a database, a deadlock is a situation in which two or more transactions are waiting for one another to give up locks.

For example,

Transaction A might hold a lock on some rows in the Accounts table and needs to update some rows in the Orders table to finish.

Transaction B holds locks on those very rows in the Orders table but needs to update the rows in the Accounts table held by Transaction A.

Transaction A cannot complete its transaction because of the lock on Orders. Transaction B cannot complete its transaction because of the lock on Accounts.

All activity comes to a halt and remains at a standstill forever unless the DBMS detects the deadlock and aborts one of the transactions. The following figure shows this situation.

92.what are the various oracle database objects?

Tables,views,Indexes,synonyms,sequences,partitions,clusters,Stored procedures and packages, user defined data types ,table spaces, constraint.

93. what do you mean by redolog file mirroring?

Each group will maintain the one member.if it contains two members in each group that is called mirrioring.

94. What is D-Commision database?

In the Ora tab file  if it is hash symbol before database name which is called D- commission databases.

We should not start D- commission databases. If we want to start the D- commission databases, need to check with application owner to start database.

95. What is different types of check points are?

·        Incremental check point

·        complete check pint

·        Thread check point

·        Tablespace check point

·        object checkpoint

98. What is Database files?

CRD files, parameter file, trace files, log files, archive logs, flashback files.

But database files actually contains CRD files.

99. what is shutdown modes. explain it?

SHUT DOWN MODES:

Shut down Normal:

Ø  This is a grace full shutdown

Ø  oracle writes until all users disconnect their sessions

Ø  No new connections will be allowed

Ø  Dismounts  the database and releases the memory to the O.S and stops all the BP

Shut down Transactional:

Ø  Once DBA issues shut down transactional no more new connections and oracle wait until the ongoing  transactions are either committed or roll back.

Ø  This is a graceful shut down

Shut down immediate:

Ø  Oracle disconnects all the session by roll backing the ongoing transactions

Ø  dismount the database & release the memory

Ø  This is the grace full shut down

Shut down abort:

Ø  This is not a grace full shut down

Ø  oracle releases the memory suddenly

Ø  Data base will not be closed

Ø  Next start up requires Instance recovery

 

100. What is insert statement?

Ø  In case of insert ,oracle is going to keep the rows in the undo.dbf

Ø  The purpose of undo.dbf is "transaction recovery, transaction rollback and instance recovery"

Ø  Oracle insert all the data into Redo buffer cache.

Ø  From the redo buffer cache log writer writes all the inserting records into online redo log files under the following  conditions .

·        If 1/3rd  of redo buffer cache

·        If the data is 1MB reached

·        If user gives commit.

Ø  If the online redo log file is filled, oracle starts writing the data into other online redo log file.

Ø  Once one online redo log file is filled log switch will occur, then check point will generate SCN number and then it went to data file.

Ø  The different types of check points are

·        Incremental check point

·        complete check point

·        Thread check point

·        Table space check point

·        object check point

Ø  SCN number will generate the every 3 seconds

Ø  two types of mandatory data files is there

system.dbf; It contains data dictionary which is nothing but metadata

sysax.dbf

Note: Every data file has got a 'header'

101.What is update statement?

Ø  Whenever Instance terminates without a proper shutdown, next start-up requires instance recover.

Ø  Instance recovery will be performed by the 'SMON' Back ground process

Ø  Instance recovery is nothing but roll forward and roll backward.

Ø  If it all the transaction is committed, oracle is going roll forward, that means updates the committed data to the respective data file.

Ø  In case of roll backward ,oracle is going to delete the data (or)updates the past image in case of instance recovery

Ø  PMON background process continuously monitors the user sessions, if any of the user session gets terminated without issuing commit/rollback ,PMON releases the locks held the objects.

Ø  Generally, in the industry DBA creates online redo log files by specifying the size in MB's

Ø  Every change, that is done against the database has to go through the online redo log file[Exception when you create a table space in no logging mode]

                              UPDATE  EMP SET emp  no=20

                              Where emp-no=10

DB writer writes the modified data under the following conditions

·        whenever log switch occurs

·        when timeout occurs

·        when threshold value is reached

·        when you make a table space read only.

102. What is DELETE Statement?

Ø  In case of delete operation, oracle is going to delete all records, but it is not going to release the all space. that are allocated for segment

·        The default size of extent is 64k

·        The default size of block is 8k

Ø  In case of delete ,oracle is going to keep the 'entire row data' in the undo segment, which requires more space in the undo.dbf

Ø  In case of update, oracle is going to retain the 'Past image' in the undo.dbf

Ø  in case of insert, oracle is going to retain the row-id's of the respective modified segments

Ø  out of all three DML statement ,delete requires more space in the undo.dbf

 

103.Explain the Mandatory background process?

 

DB WRITER: It writes the block information from memory into disk. It follow the few conditions before write the block into data files

Ø  when the check point occurs

Ø  when the table space read only mode

Ø  when the table space is begin backup mode

Ø  when the memory reach max value or threshold value

LOG WRITER: It will writes the transactions information from redo log buffer to online redo log file. It follow few conditions before writing...

Ø  When the memory is filled 1/3rd

Ø  when the user gives the commit

Ø  If it reach max value Or 1MB

CHECK POINT: When the log switch occurs, It will give the signal between log writer and DB writer

Signal means ,when the log switch occurred.

It follow few conditions before checking..

 

Ø  checkpoint will update SCN  into data files header along with control file.

Ø  SCN will generate for the committed transactions.

PMON:PMON background process continuously monitors the user sessions, if any of the user session gets terminated without issuing commit/rollback ,PMON releases the locks held the objects.

SMON:

Ø  It will perform the instance recovery

Ø  It clear the temporary  segments

Instance  recovery: it means instance is down abnormally

 it follow the roll backward and roll forwarded

·        Role forward means committed information will be stored or updated into the data file

·        Role backward means un committed  transaction will be roll back into UNDO.

 

Architecture - 1

1. Why do we need to maintain our data in database?

• Database has a availability of a storage system
• the most obvious of these being persistence.
• We need to share data of all kinds between users (except for very small systems), between tools working on

them, and usually between different computers. All should be able to work with the same set of data.

• We need persistent storage and storage must be reliable.
• Several users and/or several tools must be able to safely access the same data concurrently.
• efficient access to large amounts of data, through indexing and other optimizations.
• A DBMS and its data model provides an abstraction; we do not need to care about the physical storage format.  A DBMS may also be used for communication between different users or applications.

2. What are the responsibilities of a Database Administrator?

A.            Installing and upgrading the Oracle Server and application tools.

               Allocating system storage and planning future storage requirements for the database system.

               Managing primary database structures (tablespaces) Managing primary objects (table, views, indexes)

               Enrolling users and maintaining system security.

               Ensuring compliance with Oralce license agreement Controlling and monitoring user access to the database.

               Monitoring and optimizing the performance of the database.

               Planning for backup and recovery of database information.

               Maintain archived data on tape Backing up and restoring the database.

               Contacting Oracle Corporation for technical support.

 

3. What are the Daily Activities of a Oracle DBA

A.            1. Check the Database availability

               2. Check the Listener availability

               3. Check the alert log file for errors

               4. Monitoring space availability in table spaces

               5. Monitoring mount point (see capacity planning document)

               6. Validate Database backup or Archive backup

               7. Find objects which is going to reach max extents

               8. Database Health check

               9. CPU, Processor, Memory usage

 

4. What is the most challenging aspect of your job?

A.            This question will give you clues about the mindset of the DBA. Please go with your actvites.

5. How do you perceive the relationship between the DBA and the development staff?

A.            DBAs by virtue of their high pay and product-specific knowledge, tend to think of developers as underlings, in   some cases, DBAs view developers with outright contempt, believing their queries to be naive. On the other hand,       DBAs with the proper attitude will respond to this question by talking about the developers as clients to whom         they provide data services essential to the application. In some shops, the DBAs may be responsible for code,        reviewing SQL queries, or DML statements written by developers; so, a good relationship is vital.

6. What is a Database instance?

A.            Explain A database instance (Server) is a set of memory structure and background processes that access a set of             database files. The process can be shared by all users. The memory structure that are used to store most queried             data from database. This helps up to improve database performance by decreasing the amount of I/O performed       against data file.

 

7. What are mandatory background processes in Oracle Database?

A.            Smon, pmon, ckpt, dbwr, lgwr

 

8. What is Oltp database?

A.            OLTP means Online Transaction Processing. OLAP means Online Analytical Processing. OLTP deals with processing         of data from transactional systems. For example, an application that loads the reservation data of a hotel is an               OLTP system. An OLTP system is designed mainly keeping in the mind the performance of the end application. It              comprises of the application, database & the reporting system that directly works on this database. The database          in an OLTP system would be designed in a manner as to facilitate the improvement in the application efficiency                thereby reducing the processing time of the application.

 

9. What is Olap database?

A.            OLAP systems were mainly developed using data in a warehouse. Having said that a need was felt to isolate older     data, it was necessary to store them in a format that would be useful in easing out the reporting bottlenecks. A         need was felt to isolate the data & redesign the application data to such a format & structure that this data     repository would be the prime source of business decisions. Coming back to OLAP systems, these systems were           mainly developed on the isolated data.

10.  What is the basic element of Base configuration of an oracle Database?
A.            It consists of one or more data files. One or more control files. Two or more redo log  groups. The Database       contains multiple users/schemas one or more rollback segments
               one or more tablespaces Data dictionary tables User objects (table,indexes,views etc.,) The server that access the        database consists of SGA (Database buffer cache,Shared pool ,Redo log buffer)

                              SMON (System monitor)
                              PMON (Process MONitor)
                              LGWR (LoG Write)
                              DBWR (Data Base Write)
                              ARCH (ARCHiver)
                              CKPT (Check Point)
                              RECO
                              Dispatcher
                              User Process with associated PGA.

11. What is the function of Optimizer?
A.            The goal of the optimizer is to choose the most efficient way to execute a SQL statement.

12. What is Execution Plan?
A.            The combination of the steps the optimizer chooses to execute a statement is called an execution plan.

13. What are the different approaches used by Optimizer in choosing an execution plan?
A.            Rule-based and Cost-based.

14. What are the different Components of SGA?
A.            1. The fixed SGA (Fixed SGA)
               2. Default block buffer (Db cache)
               3. Different Standard Block buffers
               4. Redo log buffer (Redo log buffer)
               5. Java pool (Java pool)
               6. Large pool (Large pool)
               7. Shared pool (Shared pool)
               8. Stream pool (Stream pool)
               9. Result cache

15. What is dictionary cache?

A.            The dictionary cache stores “metadata” (data about your tables and indexes) and it’s also known as the row               cache. It is used to cache data dictionary related information in RAM for quick access. The dictionary cache is like       the buffer cache, except it’s for Oracle data dictionary information instead of user information.

16. What is Database Buffers?
A.            Database buffers are cache in the SGA used to hold the data blocks that are read from the data    segments in the       database such as tables, indexes and clusters DB_BLOCK_BUFFERS parameter in INIT.ORA decides the size

17. What is the functionality of SYSTEM table space?
A.            System tablespace is a main part of oracle database. All the database information is in it.it is created when       database is created.It is managed by oracle server . dba cannot change its contents. it contains the data dictionary         objects .

18. What is the function of checkpoint (CKPT)?
A.            Checkpoint is a background process which ensures dbwn process has written data to datafiles and upadates       control file and datafile header to establish data consistency.The CKPT is also useful to get the point in time from        where to begin the recovery in case of failure.

19. When does LGWR write to the database?
A.            Log Writer (LGWR) writes redo log entries. it is generated in the redo log buffer of the SGA to on-line Redo Log File. LGWR   writes  redo  log  entries  into  an  on-line  redo  log  file  when transactions commit and the log buffer         files are full.

20. What is Shared SQL Area ?
A.            A shared SQL area contains the parse tree and execution plan for a given SQL statement. Oracle saves memory by   using one shared SQL area for SQL statements run multiple times, which often happens when many users run the       same application.Oracle allocates memory from the shared pool when a new SQL statement is parsed, to store in the shared SQL area. The size of this memory depends on the complexity of the statement. If the entire shared          pool has already been allocated, Oracle can deallocate items from the pool using a modified LRU (least recently                used) algorithm until there is enough free space for the new statement's shared SQL area.

21. What Does DBWR do?
 A.           Database writer writes modified blocks from the database buffer cache to the data files.

22. What is server processes?
A.            A server process is one that handles user requests. When you type in a SQL statement, the server process      handles the parsing and running of that SQL statement,

23. Name the process which carries the request to the memory components,And also fetches from disk to buffer?
  A.          Server Process

24. Which background process write dirty blocks from database buffer cache to   data files?

A.            Database writer (DBWR)

 

25. Which background process writes data from log buffer to redo log files? 

A.            Log writer (LGWR)

 

26. Which background process performs Crash recovery?
A.   SMON (system monitor)

 

27. What is Log Switch?
A.   The point at which ORACLE ends writing to one online redo log file and begins writing to another is called     

                a log switch.

 

28. What is On-line Redo Log?
A.   The On-line Redo Log is a set of tow or more on-line redo files that record all committed changes made to                

                the database. Whenever a transaction is committed, the   corresponding redo entries temporarily stores in

               redo log buffers of the SGA are   written to an on-line redo log file by the background process LGWR. The

                on-line redo log files are used in cyclical fashion.

29. What are the steps involved in Instance Recovery?
A.            Rolling forward to recover data that has not been recorded in data files yet has been recorded in the on-line redo            log, including the contents of rollback segments. Rolling back transactions that have been explicitly rolled back or   have  not been committed as indicated by the rollback segments regenerated in step a. Releasing any resources          (locks) held by transactions in process at the time of the failure. Resolving any Pending distributed transactions             undergoing a two-phase commit at the time of the instance failure.

30. What does COMMIT do?
A.            COMMIT makes permanent the changes resulting from all SQL statements in the transaction. The changes made              by the SQL statements of a transaction become visible to other user sessions transactions that start only after     transaction is committed.

 

31. How do you know whether the process is Server Side Process?
A.            By seeing the process in ps-ef as ORACLE_SID.

 

32. When ckpt occurs?
A.            1. For every 3 seconds
               2. When 1/3rd of DB buffer fills
               3. When log swtich occurs
               4. When database shuts down

 

33. What are the two steps involved in instance recovery?

A.            1. Roll forward (redofiles data to datafiles), 2.Roll backward (undo files to data files).

 

34. What is the use of SMON?

 A.           SMON is an Oracle mandatory background process. It is used for Instance recovery.

 

35. List the Optional Flexible Architecture (OFA) of Oracle database? How can we organize the                table spaces in Oracle database to have maximum performance?

A.            SYSTEM - Data dictionary tables.

               DATA - Standard operational tables.

               DATA2- Static tables used for standard operations

               INDEXES - Indexes for Standard operational tables.

               INDEXES1 - Indexes of static tables used for standard operations.

               TOOLS - Tools table.

               TOOLS1 - Indexes for tools table.

               RBS - Standard Operations Rollback Segments,

               RBS1, RBS2 - Additional/Special Rollback segments.

               TEMP - Temporary purpose tablespace

               TEMP_USER - Temporary table space for users.

               USERS - User tablespace.

 

36. How do you know when the process is started?

  A.          Using ps -ef grep process name

 

37. What is meant by redo log buffer?
A.            Changes made to entries are written to the on-line redo log files. So that they can be used in roll forward          operations during database recoveries. Before writing them into the redo log files, they will first brought to redo          log buffers in SGA and LGWR will write into files frequently. LOG_BUFFER parameter will decide the size.