Monday , September 25 2017
Home / Oracle DBA Interview Questions / ORACLE ARCHITECTURE INTERVIEW QUESTIONS

ORACLE ARCHITECTURE INTERVIEW QUESTIONS

ARCHITECTURE INTERVIEW QUESTIONS

  1. Explain briefly about Oracle database architecture
    1. Oracle database architecture is a combination of instance and database. Instance is a combination of memory structures and background processes which helps in reading and writing the data to/from the database .
  1. Which background process is used during user connectivity?
    1. PMON
  1. What are base tables? When and how they will get created?
    1. Base tables are dictionary information of the database. They will be created at the time of database creation using SQL.BSQ script.
      4.What are different views DBA uses? Which script will create them?
    1. We use data dictionary views to look into permanent information about the database and dynamic performance views to get ongoing actions in the database. Both the views will be created after database creation using catalog.sql script.
  1. Why to execute catproc.sql script?
    1. It will create necessary packages and procedures which DBA use for certain actions.
  1. Explain the phases of SQL execution
    1. SQL execution contains 2 phases.
      1. Parsing – in which syntax checking, semantic checking and dividing the statement into literals will be done
      2. Execution – in which parsed statement will get converted into ASCII format and will be executed
      3. Fetch – in which data will be fetched either from database buffer cache or database.
  1. What is mean by semantic checking? Which component helps in that?
    1. Semantic checking means checking for the privileges for the user or in other words authorizing the user. Base tables or dictionary will help in doing this.
  1. Explain how a select statement will process in the database
    1. Answer is available in the class notes.
  1. What is server process?
    1. It is a process created to help the user process either in reading/writing the data in the database.
  1. What is the difference between physical and logical read?
    1. If we fetch data from database buffer cache, then its called logical read. If we fetch it from database, its called physical read as it includes an I/O operation.
  1. Why to maintain a copy of data in database buffer cache?
    1. When the same query is ran by same or different user, data can be picked from buffer cache thus avoiding I/O and improving performance.
  1. Why server process will not start searching from MRU end?
    1. If server process starts searching at MRU end,  there is a chance that data may get flush from buffer cache by the ime it reaches LRU end. In that case again to fetch data, we need to do an I/O which is costly. So oracle designed its architecture that server process will search only from LRU end.
  1. What are the logical structures of the database?
    1. Tablespace, segment, extent and oracle data block are logical structures.
  1. What are the 4 mandatory tablespaces req to run the database?
    1. SYSTEM, SYSAUX, TEMP and UNDO.
  1. Can I have a database without SYSAUX tablespace in 10g?
    1. Yes, but load will be more on system tablespace.
  1. If we have physical structures, why do we need logical structures?
    1. Logical strutures are defined to provide the easeness in maintenance.
  1. Explain the difference between a block, extent and segment?
    1. Block is a basic storage unit where as extent is a small memory area allocated to a table and segment is a object which occupies space.
  1. Explain how a DML statement will process in the database
    1. Answer is available in the class notes.
  1. What are redo entries? When and where they will be created?
    1. A single atomic chance happened to the database is called redo entry. They will be created when we run any DML or DDL commands or when any changes are done in the database. They will be created in PGA.
  1. What is different status for a block in database buffer cache?
    1. UNUSED, FREE, PINNED and DIRTY.
  1. What is write-ahead protocol?
    1. LGWR writing before DBWR is called write-ahead protocol.
  1. Will there be any undo generated in case of DDL statement processing? If so why?
    1. Yes. Reason is even tough it is a DDL externally, it will be DML to base tables and to rollback them always oracle requires undo.
  1. What is PGA?
    1. PGA is a memory area which is used to store user’s exclusive information like session information and helps in executing a query.
      24.What is the difference between memory allocation in 8i and 9i for PGA?
    1. In 8i, we need to set different individual parameters where as in 9i setting only one parameter PGA_AGGREGATE_TARGET would be enough
  1. Which is correct? Sorting will take place in PGA or temp tablespace?
    1. Both. If the data which to be sorted is more than sort area size of PGA, then temp tablespace will be used.
  1. How you will define INSTANCE and DATABASE?
    1. INSTANCE is a combination of memory structures and background processes which helps in reading/writing the data. DATABASE is mix of physical and logical structures which helps in storing user data.
  1. What is SGA?
    1. SGA is a combination of different memory structures which helps in several actions in the database.
  1. What are the responsibilities of SMON?
    1. It will do instance recovery, coalesces the tablespace and will release the temp segments occupied by a transaction when it is completed.
  1. How SMON will perform instance recovery?
    1. Answer is available in the class notes.
  1. Why oracle maintains 2 redolog files?
    1. To avoid any space constraints.
  1. What is a logswitch? What happens when it occurs?
    a.Answer is available in the class notes.
  1. What are the activities of PMON?
    1. Answer is available in the class notes.
  1. What are the situations when DBWR and LGWR will write?
    1. Answer is available in the class notes.
  1. What is SCN and which process writes that?
    1. SCN is a unique number assigned to a transaction which modifies data in the database. CKPT process will always update this SCN to both datafiles header and control file.
      35.What are the other new background processes in 10g that you know?
    1. MMAN – memory manager which helps in maintaining ASMM
    2. RVWR – helps in generating flashback logs incase flashback is turned on
    3. CTWR – hepls incremental backup in RMAN
    4. MMNL – memory monitor light, which helps in AWR statistics collection
    5. MMON – managebility monitor, which helps in automatic problem detection and self-tuning.
  1. What information control file contains?
    1. It contains latest SCN, all locations and sizes of datafiles and redolog files, database creation date and timestamp and controlfile parameters.
  1. What is server parameter file and how it is different from parameter file?
    1. Spfile is a binary file and it helps in changing parameters efficiently than a pfile.
  1. What is Ifile and when it is used?
    1. Ifile is a index file which can help database when pfile or spfile are not in default location.
  1. How to recover if I lost parameter pfile or spfile?
    1. We can recover from alert log file which contains non-default parameters.
  1. If we have both pfile and spfile in place, which file oracle will use during startup?
    1. Spfile.
  1. What are hidden parameters and their use?
    1. Hidden parameters are to be used only on recommendation of oracle support and sometimes they help us in providing work around to any serious problem in the database.
  1. What is the purpose of password file?
    1. It is used to authenticate any user is connecting as SYSDBA from a remote machine.
  1. Even though there is a password file, still I observed that any user is being able to connect as sysdba from remote machine without a password. What went wrong?
    1. REMOTE_LOGIN_PASSWORD is not set to exclusive.
  1. While creating password file, what is the use of ENTRIES?
    1. To specify how many users with sysdba role can connect to the database remotely.
  1. What is ASMM? Which background process helps in that?
    1. Setting SGA_TARGET parameter is called ASMM. This helps in managing all SGA components automatically and MMAN is the background process helps in that.
  1. How database will behave when you have both ASMM and individual parameters are configured?
    1. Values of individual parameters will act as minimum and ASMM as maximum.
  1. You increased the SGA_MAX_SIZE parameter to a higher value. But when trying to increase SGA_TARGET, its throwing error that “it cannot increase”. What might be the reason?
    1. Kernel parameter SHMMAX may be reached. We should increase that first.
  1. Which parameter helps ASMM to be affective? What is the value it should be set to?
    1. SGA_TARGET and value is dependent on no of transactions in the database.
  1. What is AMM in 11g?
    1. Managing both SGA and PGA automatically by oracle is called Automatic Memory Management (AMM).
  1. My database size is 1 TB, how much SGA will you configure?
    1. SGA size is not dependant on database size, it depends on how many transactions happening in the database.
  1. What is alert log? How it is helpful for DBA?
    1. It is file which helps in diagnosing all the errors occurred in the database.
  1. What are the contents of alert log file?
    1. Database startup and shutdown times, non-default parameters, any logical or physical structural changes and alloracle errors etc.
  1. What happens if we remove alert log file when database is in use?
    1. No effect on the database functionality. Oracle will create a new alert log file.
  1. If we have alert log file to diagnose the problem, why we need trace files? What are they?
    1. Alert log itself cannot provide complete information about the error, in which case it will generate trace file. Depends on the error, it will generate background, core or user trace files.
  1. Which background process is responsible for writing into alert log file?
    1. All the background processes are responsible.
  1. Which process will start first when instance is started?
    1. PSP0, process spawner. This background process will start other processes like PMON, SMON etc. But in alert log first it will show PMON.
  1. Which file is req to place database in NOMOUNT state and what happens in that phase?
    1. Pfile or spfile is required to bring database to NOMOUNT state and instance will be started (backgroung processes will be started and memory will be allocated to SGA from RAM) in this phase.
  1. Which file is req to bring into MOUNT phase and what is the use of this phase?
    1. Control file is required and it is for maintenance of some database actions.
  1. What files are needed to open the database and How oracle knows the locations of them?
    1. Datafiles and redolog files are required to open the database and oracle will get that information from controlffiles.
  1. What you will do when SHUTDOWN IMMEDIATE command hanging for last 30 min?
    1. We can open another terminal and issue shut abort. Then once again startup and do shut immediate.
  1. What could be reason for SHUTDOWN IMMEDIATE command hanging for long time?
    1. Might be there is a transaction which is large and rollback is happening for the same.
  1. What is server result cache? What benefit we get out of it?
    1. It is a new component introduced in 11g. It will store the rows of a query directly thus avoids the need of executing a statement.
  1. Explain how a select statement will get the benefit by using server result cache?
    1. Read how a select statement will process in 11g from class notes.
  1. What parameters to be used to make 11g database use server result cache?
    1. SERVER_RESULT_CACHE.
  2. How to increase the size of server result cache?
    1. By increasing the size specified by SERVER_RESULT_CACHE or by increasing MEMORY_TARGET parameter if AMM is used.
  1. Where all the trace files located in 11g?
    1. It is in a single location defined by DIAG_DEST parameter.
  2. Which background process clears sessions and releases locks when system fails?
    1. SMON (here system means database).
  1. What is OFA? Do you recommend to implement it?
    1. It is a rule which says database related files should split across multiple disks and yes i will recommend to implement it.
  1. Why oracle recommends OFA? How it is related to OS?
    1. As one disk will have only one I/O header, it will be burden to the database if we place all the files in single hard disk. So oracle recommends OFA.
  1. What is DISPATCHER in shared server architecture?
    1. It is a service which server multiple user requests.
  1. Explain shared server architecture
    1. Answer is in class notes.
  1. As a DBA, when you will take decision to enable shared server architecture?
    1. When we observed ORA-04030 or ORA-04031 errors very frequently in alert log file.
  1. You configured all the parameters to enable shared server architecture, but still users facing memory allocation issues. What you would do?
    1. We need to check if SERVER=SHARED is mentioned in client TNS entry or not.
  1. What are the parameters you use to configure shared server architecture?
    1. DISPATCHERS
    2. MAX_DISPATCHERS
    3. SHARED_SERVER_PROCESSES
    4. MAX_SHARED_SERVER_PROCESSES.
  1. How many slave process we can have for DBWR and SMON?
    1. DBWR – 20, SMON – 16 .
  1. I connected to database as sysdba, but its not allowing me to shutdown the database, what may be the reason?
    1. The connection would have been shared server connection in which case oracle will not allow to shutdown/startup the database.
Read More:
about ORACLE UTILITIES ( SQL * Loader )
Oracle Materialized Views
Oracle Database Links

Comments

comments

Check Also

ORACLE BACKUP and RECOVERY INTERVIEW QUESTIONS

BACKUP & RECOVERY INTERVIEW QUESTIONS 1.What is the difference between cold and hot backup? a.COLD …

Leave a Reply

Your email address will not be published. Required fields are marked *