|
Q1. What is Data? Answer: Data is any information in the form of text, numbers, pictures, sound, or video. Q2. What is Database ? Answer: A database is a collection of information organized systematically to act on data. Q3. What is RDBMS(Relational Database Management System) Database ? Answer: RDBMS Database is a collection of information organized systematically in the form of rows and columns so that it can be easily accessed, managed and updated. Q4. Why do we need any database? Answer: Permanent Storage: You need any database for permanent storage where you can store n- number of data depends on your storage capacity. Better Security: You can secure your data in the database using advanced features. Database gives you row and column level security. High Availability: You can perform hardware/maintenance activity while DB is online without any business downtime. Performance: Single user or multiple users can access/modify database without degrading database performance at the same time. Data Backup: You can restore and recover database at any point in time. Also you can rewind the changes in database. Q5. What is Oracle Database? Answer: The database developed by Oracle Corporation is called as Oracle Database. The software which provides Oracle Database Setup from Oracle Corporation is called as Oracle Database Software.
Q6. What is history Oracle Database? Answer: Larry Ellison, Bob Miner, and Ed Oates started a consultancy called SDL (Software Development Laboratories) in 1977 and SDL developed the original version of the Oracle software. Q7. What is SQL? Answer: SQL(Structure Query Language) is the only language or medium to communicate with Oracle Database and it is based on relational database management system in row and column format. SQL statements are used to perform tasks such as updating data in a database, or retrieving data from a database. Q8. What is an Instance? Answer: Instance is a platform to access an Oracle database where you can run/execute SQL commands to access the data from an Oracle database. Q9. What is ACID Model? Answer: ACID stands for Atomicity Consistency Isolation Durability. - Atomicity: Database transaction has to be atomic means either all steps of transaction completes or none of them. There are no partial transactions. If some part of a transaction gets failed, then the entire transaction gets failed. - Consistency: A transaction maintains the consistent copy of the data. It maintains both committed as well as uncommitted image of data. - Isolation: The effect of a transaction is not visible to other transactions until the transaction is committed. For example, one user updating the test.employee table does not see the uncommitted changes to employee made concurrently by another user. - Durability: Once the transaction is successfully completed, then the changes made to the database will be permanent even if there is a system failure, or any abnormal changes. i.e. A committed transaction will never be lost. Q10. What is Buffer Cache? Answer: Database Buffer Cache is the largest memory area component of SGA. It stores copies of data blocks that are read from data files which are physically located on Storage. Q11. What are different types of Buffers in Oracle ? Answer: - Dirty buffers in LRU List: These are modified buffers which have not yet been written to dirty list. - Dirty buffers in Dirty List: These are modified buffers which have not yet been written to disk. - Free or unused buffers: These buffers are free and ready to use. - Pinned Buffers: These buffers are currently being accessed. These are data buffers that are currently in active use by user sessions. - Clean Buffers: These buffers contain some data but it is sync with block content stored in datafiles, so there is no need to write these buffer to disk. - Undo buffers: It contains previous image of the changed block. Q12. What are different types of Pools in Oracle SGA? Answer: The different type of pools are Keep Pool, Recycle Pool, and Default Pool. - Keep Pool: Suppose, you have small tables in your DB that are frequently accessed by an Application with FULL TABLE SCAN. These frequently accessed buffers can be flushed from the Buffer Cache to disk after the certain threshold limit and again these are copied from disk to SGA as per the request. These buffers are again and again are flushed/copied from SGA to disk and disk to SGA frequently which requires more I/O operations. To avoid this issue, you can cache these tables in Keep Pool to minimize disk I/O and maximize performance. So the answer is "Keep Pool is used to keep the data blocks always in memory(SGA). Frequently accessed small tables with FULL TABLE SCAN can be cached in Keep Pool to prevent them from being aged out of the database buffer cache". - Recycle Pool: Like the Keep Pool is used for small tables, the Recycle Pool is used for large tables that are not frequently accessed. It operates differently than Keep Pool. It quickly flushes the buffers that are no longer needed so that it makes room for others. - Default Pool: If an object is not assigned a specific buffer pool, then its blocks are loaded into default pool. The Keep and the Recycle buffer pools are optional, while the Default pool is mandatory. Q13. What is Redo Log Buffer in Oracle? Answer: The Redo Log Buffer is a circular buffer in the SGA that stores the information about changes made to the database. It contains both committed as well uncommitted information. This information is stored in the form of redo entries. Redo entries contain the information necessary to reconstruct/redo the changes made to the database by SQL statements like INSERT, UPDATE, DELETE, CREATE, ALTER, or DROP operations. Redo entries are used for database recovery when needed. A description of a change made to a single block in the database which is nothing but the Change Vectors. A redo record or a redo entry is made up of a group of change vectors. Q14. What is Shared Pool in Oracle? Answer: Shared Pool is the 2nd largest component of SGA. A main purpose of the Shared Pool is to cache the executable versions of SQL and PL/SQL statements. It allows multiple executions of the same SQL or PL/SQL code to perform without hard parsing that results in CPU and Memory reductions since the SQL statements have already been executed by someone and same statement is executed again so no need to perform extra parsing. Q15. What is Dictionary Cache in Oracle? Answer: The data dictionary is a collection of database tables and views containing reference information about the database, its structures, and its users. Oracle Database accesses the data dictionary frequently during SQL statement parsing. The dictionary cache is also known as the row cache because it holds data as rows instead of buffers. Q16. What is Library Cache in Oracle? Answer: The Library Cache is a Shared Pool memory structure that stores executable SQL and PL/SQL code. This contains the shared SQL and PL/SQL areas and control structures such as locks and library cache handles. Q17. What is Soft Parse and Hard Parse? Answer: When a SQL statement is executed, the database tries to reuse executed code that has executed previously by someone. If a parsed representation of a SQL statement(Previously executed statements) exists in the library cache, then the database reuses the code which is known as a Soft Parse or a Library Cache Hit (Touches the Library Cache). On the other hand, if a parsed representation of a SQL statement is not found, then the database builds a new executable version of the application code which is known as a Hard Parse or a Library Cache Miss (Missing the Library Cache). Q18. What is Large Pool? Answer: Large Pool is an optional memory area of SGA. It is configured for large memory allocations like RMAN backup and recovery, parallel query operations, and in case of shared server environment. Large pool does not have an LRU list unlike Shared Pool. 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. Q19. What is Java Pool? Answer: The Java Pool is an optional memory area required if the database has Oracle Java installed. The size is set with the JAVA_POOL_SIZE parameter that defaults to 24MB.The Java Pool is used for memory allocation to parse java commands and to store data related to Java commands. Q19. What is Streams Pool? Answer: The Streams Pool provides memory for Oracle Streams capture and apply processes. It is specified by the parameter STREAMS_POOL_SIZE. Oracle Streams enables information sharing in the form of message. These messages are shared in a stream. The stream can propagate this information within a database or from one database to another database. Q20. What is User Process in Oracle? Answer: The program or platform to communicate with Oracle database so that Oracle users can execute SQL statements. i.e. SQL *Plus, SQL Developr, Toad, etc. Q21. What is Server Process in Oracle? Answer: To handle the request raised by User Process, Oracle database create a Server Process. Server process can be a Dedicated or Shared. Q21. What is Dedicated and Shared Server Processes in Oracle? Answer: Dedicated Server Process services only one User Process. This is used in OLTP systems (Batch-Jobs and Long Running Operations) with fewer user environment for faster and efficient access. Shared Server Process can service multiple User Processes. In a Shared Server configuration, client or user processes connect to a dispatcher. A dispatcher process receives client requests and places them in a request queue. Shared server processes pick requests from this request queue and execute them. Shared servers place all the completed requests into a dispatcher's response queue. The dispatcher returns the request's output to the appropriate client process. Q22. What are advantages in Dedicated and Shared Server Processes? Answer: Advantages in Dedicated Server Process : - This is easy to configure, monitor, and troubleshoot since each user session is isolated. - Each user gets a dedicated process with quick faster response without waiting for resource availability. - It is specifically used for OLTP systems (Batch Jobs and Long Running Operations) wit fewer users. - In Dedicated Server Process, resources are fully allocated to the session till its duration. Advantages in Shared Server Process : - Here, multiple users share a small pool of server processes which reduces CPU and memory resource usage. - It is used for OLTP systems where sessions perform short transactions. - It reduces the total number of server processes. Q23. What are disadvantages in Dedicated and Shared Server Processes? Answer: Disadvantages in Dedicated Server Process : - In Dedicated Server Process Environment, each client connection gets it's own server process, which results higher memory usage as connection increases. - The systems with more number of concurrent users, the number of Operating System processes can increase rapidly which can consume more server resources like CPU, RAM, OS process limits, etc. that degrades performance and can cause system crashes and reboots. - Though Dedicated Server Processes are idle or inactive, still consumes resources. Disadvantages in Shared Server Process : - Complex Structure, since each user request must go through Dispatcher,Request,Shared Server,Response queue. - This is not sutaible for Long Running Operations and Batch Jobs. - Shared Server uses Virtual Circuits. Each virtual circuit consumes UGA memory in the SGA, which can increase SGA resizing or memory fragmentation issues. Q24. Which are mandatory background processes in Oracle? Answer: Below are the mandatory background processes in Oracle. PMON SMON DBWn LGWR CKPT Q25. What is role of PMON process in Oracle? Answer: PMON or Process Monitor performs the below tasks: - There can be a maximum of 1 PMON process per instance. - It monitors the other background processes and performs process recovery when a server or dispatcher process terminates abnormally. - When a user process dies holding table locks, the PMON process releases these locks from the dead process. - It rollbacks the uncommitted transaction. - It wakes up every 3 seconds to perform housekeeping activities. - It is responsible for cleaning up the database buffer cache and freeing up resources that were allocated to a process. - It is responsible for performing recovery if a user process fails. - It performs dynamic registration of services with listener. When you create a new database instance, the PMON process registers the instance information with the listener. Q26. What is role of SMON process in Oracle? Answer: SMON or System Monitor performs the below tasks: - There can be a maximum of 1 SMON process per instance. - SMON does Instance recovery in case of shut abort or abnormal instance termination or power failure. This is called as Instance Crash Recovery. This is automatically done by SMON during next startup of the instance. You don't have to do anything. - In Oracle RAC database, SMON process of one instance performs instance recovery for other instances which are failed. - SMON coalesces free extents in dictionary managed tablespaces. - SMON cleans up temporary segments that are no longer needed. - SMON maintains SCN number to time mapping table which is used for Flashback features. - SMON maintains undo tablespace space usage statistics. Q27. How does SMON perform Instance Recovery ? Answer: If Oracle Instance was terminted due to Power failure or termintaed by shut abort command or any abnormal termination, SMON performs Instance Recovery in below two phases. 1) Roll-Forward OR Cache Recovery : Roll-Forward is also known as Cache Recovery. When the database suddenly crashes, not all the committed transactions will have been written to disk. During Roll-forward stage, the database applies both committed and uncommitted data in the current online redo log files to the current online datafiles. This data is needed to see what changes need to be applied to disk. Now here is the question when the last checkpoint was done before the crash. This point in the redo logs is known as Thread Checkpoint Redo Byte Address. All the data in the buffers is written to disk during a checkpoint stage, only the changes after this last checkpoint need to be recovered. Hence, the process of applying the contents of both the archived and redo log files to bring the datafiles up to date is called Cache Recovery or Roll-Forward phase. Now all committed changes have been taken care, but what about uncommitted changes that are part of the redo log which will be taken care in next phase. 2) Rollback OR Transaction Recovery : During the application of the redo log data to the datafiles,both committed and uncommitted changes get applied. The uncommitted changes must now be removed from the datafiles. Oracle uses undo segments to identify before image of data stored to remove these uncommitted changes. This second step is called Transaction Recovery or Rollback. Q28. What is DBWn process in Oracle and what is role of it? Answer: DBWn or Database Writer Process is responsible for writing the modified or changed or dirty data from memory (Database Buffer Cache) to data files (Physically Located Disks). When a user performs an operation, Oracle first looks whether a free block is available in Database Buffer Cache. If it does not find any free buffer in Database Buffer Cache then it's resposibility of DBWn process to flush the previousely modified buffers from Buffer Cache to Data files so that the buffers or blocks will be free and available for new processes to work. Now, here the question comes how DBWn process decides which buffers have to be flushed ? This is achieved by LRU (Least Recently Used) Algorithm so that recently accessed blocks will retmain into Buffer Cache and less frequently used blocks will be candidates for flushing from Buffer Cache to Data Files. There can be maximum 1 to 100 DBWn processes in Oracle. It differs in different database releases. In older release (10g), the process maximum range was 20, then it was changed to 36 (11g), and now it is 100. e.g. DBW0-DBW9 =10 DBWa-DBWz = 26 10+20 = 36 The next DBWn would be from DBW37 to DBWN99. Q29. Under what conditions, DBWn process flushes the buffers or blocks from Buffer Cache to Data Files ? - For every 3 seconds. - When a server process cannot find a clean reusable buffer in Buffer Cache. - When the dirty blocks in Buffer Cache reaches to a threshold value. - When the database is shutting down with some dirty blocks in the Buffer Cache. - When a checkpoint is issued. - When a large table is accessed and Oracle cannot find enough free space in the Buffer Cache. - When Table is DROPPED or TRUNCATED. - When a tablespace is putting OFFLINE or READ ONLY or BEGIN BACKUP mode. Q30. What is LGWR process in Oracle and what is role of it? Answer: LGWR or Log Writer Process is responsible for writing redo entries from Log Buffer (SGA Memory) to physically located Redo Log files. Each Redo Log file or member is filled with redo records. A redo record is also called as redo entry which is nothing but the group of change vectors. A change vector is a description of any single change made to the database. A change can be DML, DDL, DCL, etc. There can be maximum of 1 LGWR process per Oracle instance. Redo log files or Redo log members can be multiplexed to avoid data loss in case of redo disk failure due to any reason. In case of multiple members in a group, LGWR process concurrently writes the same redo log information to multiple identical redo log files in a same group to avoid single point of failure. LGWR process never writes concurrently to members of different groups. Let's say, there are two redo log groups with two members each. The 1st member of Group1 is located on DISK1 and 2nd member on DISK2. Similarly, the 1st member of Group2 is located on DISK2 and 2nd member on DISK2. Here, if you place both memebrs on same DISK then there is no use of multiplexing in case of disk failure, because if disk gets failed then all he members will become inaccessible and hence always ensure to place both the members of same group on a different disk to avoid single point of failure. Here, if one of the member of Group1 gets failed then 2nd member is available for recovery.   If any redo disk gets failed, then only that failed member of a group out of two becomes unavailable and other member will be accessible, so the instance can continue to function. Q31. What is Right Ahead Protocol Rule? Answer: It's role of te DBWn process to ensure that whether LGWR process has completed writing of all redo records from the Log Buffer to redo log files on disk. DBWn process does this before flushing dirty buffers from Database Buffer cache to disk. If DBWn process finds that some redo records have not yet been written by LGWR then, it signals LGWR process to write the redo records from Log Buffer to disk and waits for LGWR process to complete the writing. DBWn process never perform this writing work before LGWR process and hence this rule is called as Right Ahead Protocol rule. Q32. What is Redo Log Switch or Log Switch in Oracle DB ? Answer: Log Switch is the point at which LGWR process stops writing to current redo log group and starts writing with next available redo log group. A Log Switch occurs when the current redo log group is completely filled. However, we can configure log switches to occur at regular intervals or force log switches manually as well. Q33. Under what conditions, LGWR process writes the redo data from Log Buffer to Redo Log Files ? - Every 3 seconds. - When a Redo Log Buffer is one-third full. - When Redo Log Buffer 1 MB full. - Every commit. - When you execute "alter system switch logfile" command. - When DBWn process signals the LGWR to write redo records from Log Buffer to Redo Log Files. *********** Work is in still progress..... |
Very useful
ReplyDelete