{"id":5324,"date":"2026-01-31T11:45:09","date_gmt":"2026-01-31T06:15:09","guid":{"rendered":"https:\/\/w3buddy.com\/?p=5324"},"modified":"2026-01-31T12:29:30","modified_gmt":"2026-01-31T06:59:30","slug":"oracle-database-architecture-interview-questions-complete-dba-guide","status":"publish","type":"post","link":"https:\/\/w3buddy.com\/blog\/oracle-database-architecture-interview-questions-complete-dba-guide\/","title":{"rendered":"Oracle Database Architecture Interview Questions &#8211; Complete DBA Guide"},"content":{"rendered":"\n<h2 class=\"wp-block-heading\">Introduction<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Preparing for an Oracle DBA interview?<\/strong><br><em>Interviewers often focus on database architecture fundamentals and real production scenarios<\/em> rather than just textbook definitions.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">This <strong>comprehensive guide<\/strong> covers <strong>Oracle Database Architecture interview questions that truly matter<\/strong> \u2014 <em>how Oracle actually works under the hood<\/em>. You\u2019ll learn about <strong>instance and memory structures (SGA, PGA)<\/strong>, <strong>background processes<\/strong>, <strong>startup and shutdown sequences<\/strong>, <strong>automatic recovery<\/strong>, <strong>storage architecture<\/strong>, <strong>performance monitoring views<\/strong>, and <strong>practical troubleshooting<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Everything is explained in <strong>simple, interview-oriented language<\/strong> with <em>real-world examples<\/em>. Understand <strong>not just what happens inside Oracle, but <em>why<\/em> it happens and <em>how<\/em> to handle production situations<\/strong> \u2014 exactly what <strong>hiring managers expect from experienced DBAs<\/strong>.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\">0. Fundamentals First<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">What is Data?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Data<\/strong> = Raw facts without context.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Examples:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>25<\/code><\/li>\n\n\n\n<li><code>John<\/code><\/li>\n\n\n\n<li><code>2024-12-21<\/code><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">What is Information?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Information<\/strong> = Data with context or meaning.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Example:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><em>John is 25 years old and joined on 2024-12-21.<\/em><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">What is a Database?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Database<\/strong> = An organized collection of structured data stored electronically.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Key benefits:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Eliminates data redundancy<\/li>\n\n\n\n<li>Ensures data integrity<\/li>\n\n\n\n<li>Enables concurrent access<\/li>\n\n\n\n<li>Provides security and backup<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Important:<\/strong><br>A database stores both <strong>data<\/strong> and <strong>metadata<\/strong> (data about data).<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">What is DBMS (Database Management System)?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>DBMS<\/strong> = Software used to create, manage, and access databases.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Key points:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Acts as an interface between users\/applications and the database<\/li>\n\n\n\n<li>Controls data storage, retrieval, and security<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Examples:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Oracle<\/li>\n\n\n\n<li>MySQL<\/li>\n\n\n\n<li>PostgreSQL<\/li>\n\n\n\n<li>SQL Server<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">What is RDBMS (Relational DBMS)?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>RDBMS<\/strong> = A DBMS based on the relational model where data is stored in tables (rows and columns).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Key features:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Uses SQL for data manipulation<\/li>\n\n\n\n<li>Enforces ACID properties (Atomicity, Consistency, Isolation, Durability)<\/li>\n\n\n\n<li>Oracle is an RDBMS<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Interview Questions<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Q1: What is the difference between data and database?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>Data is raw facts. A database is an organized collection of related data stored systematically with relationships, constraints, and metadata for efficient retrieval and management.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q2: Why use a database instead of flat files?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>Databases provide:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Data integrity through constraints<\/li>\n\n\n\n<li>Concurrent access control<\/li>\n\n\n\n<li>ACID transactions<\/li>\n\n\n\n<li>Efficient querying using indexes<\/li>\n\n\n\n<li>Security and access control<\/li>\n\n\n\n<li>Backup and recovery mechanisms<\/li>\n\n\n\n<li>Reduced data redundancy<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q3: What are ACID properties?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Atomicity:<\/strong> Transaction is all-or-nothing<\/li>\n\n\n\n<li><strong>Consistency:<\/strong> Database moves from one valid state to another<\/li>\n\n\n\n<li><strong>Isolation:<\/strong> Concurrent transactions do not interfere<\/li>\n\n\n\n<li><strong>Durability:<\/strong> Committed data survives system failures<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">1. Instance vs Database<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">What is a Database?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Database<\/strong> = Physical files stored on disk.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Includes:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Datafiles<\/li>\n\n\n\n<li>Control files<\/li>\n\n\n\n<li>Redo log files<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Nature:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Permanent<\/li>\n\n\n\n<li>Exists even when the instance is down<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">What is an Instance?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Instance<\/strong> = Memory structures + background processes that access the database.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Includes:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Memory: SGA, PGA<\/li>\n\n\n\n<li>Background processes (DBWn, LGWR, SMON, PMON, etc.)<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Nature:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Temporary<\/li>\n\n\n\n<li>Exists in memory (RAM)<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Key Point<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>One <strong>database<\/strong> can be accessed by <strong>multiple instances<\/strong> (RAC).<\/li>\n\n\n\n<li>In a non-RAC environment, the relationship is typically <strong>1 instance : 1 database<\/strong>.<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Analogy<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Database<\/strong> = Your house (physical structure)<\/li>\n\n\n\n<li><strong>Instance<\/strong> = People living in the house (active users\/processes)<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Interview Questions<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Q1: What is the difference between an instance and a database?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>An instance consists of memory and background processes and exists in RAM.<br>A database consists of physical files stored on disk and is permanent.<br>The instance must be started to access the database. In RAC, multiple instances can access a single database.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q2: Can you have a database without an instance?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>No. Without an instance, the database files cannot be accessed.<br>The database exists on disk, but it is unusable without instance memory and processes.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q3: Can you have an instance without a database?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>Yes, temporarily.<br>When you issue <code>STARTUP NOMOUNT<\/code>, the instance is started but the database is not mounted.<br>This state is used for database creation or control file recreation.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q4: What happens if the instance crashes?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>The database files remain intact on disk.<br>When the instance is restarted, <strong>SMON<\/strong> performs automatic instance recovery using redo logs to bring the database to a consistent state.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">2. Memory Architecture<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">A. SGA (System Global Area) \u2013 Shared Memory<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Purpose:<\/strong><br>Shared memory allocated at instance startup. Used by all users and background processes.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">1. Database Buffer Cache<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Purpose:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Stores copies of data blocks read from datafiles<\/li>\n\n\n\n<li>Oracle always checks buffer cache first before disk I\/O<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Key points:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Size parameter: <code>DB_CACHE_SIZE<\/code> or auto-managed via <code>SGA_TARGET<\/code><\/li>\n\n\n\n<li>Uses <strong>LRU (Least Recently Used)<\/strong> algorithm for eviction<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Types of buffers:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Dirty buffers:<\/strong> Modified but not yet written to disk<\/li>\n\n\n\n<li><strong>Clean buffers:<\/strong> Not modified or already written to disk<\/li>\n\n\n\n<li><strong>Pinned buffers:<\/strong> Currently being accessed<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>DBA concern:<\/strong><br>Too small buffer cache \u21d2 increased physical reads \u21d2 slow queries<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">2. Shared Pool<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Stores parsed SQL, PL\/SQL code, and metadata.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">a) Library Cache<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Stores parsed SQL, PL\/SQL code, execution plans<\/li>\n\n\n\n<li>Avoids re-parsing identical SQL<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Parsing types:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Hard parse:<\/strong> New SQL, creates execution plan (CPU intensive)<\/li>\n\n\n\n<li><strong>Soft parse:<\/strong> Reuses existing plan (fast)<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Components:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SQL Area<\/li>\n\n\n\n<li>PL\/SQL Area<\/li>\n\n\n\n<li>Java Pool Cache<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">b) Data Dictionary Cache (Row Cache)<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Stores metadata about:\n<ul class=\"wp-block-list\">\n<li>Tables<\/li>\n\n\n\n<li>Users<\/li>\n\n\n\n<li>Privileges<\/li>\n\n\n\n<li>Tablespaces<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Cached from SYS schema objects<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">c) Result Cache (19c)<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Stores query results for reuse<\/li>\n\n\n\n<li>Improves performance for repetitive queries<\/li>\n\n\n\n<li>Size controlled by <code>SHARED_POOL_SIZE<\/code><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>DBA concern:<\/strong><br>Insufficient size \u21d2 hard parses spike \u21d2 CPU saturation<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">3. Redo Log Buffer<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Small circular buffer for redo entries (change vectors)<\/li>\n\n\n\n<li>Written to redo log files by <strong>LGWR<\/strong><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Key points:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Size parameter: <code>LOG_BUFFER<\/code> (10\u201316 MB usually sufficient)<\/li>\n\n\n\n<li>Rarely needs tuning in modern systems<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>DBA concern:<\/strong><br>Too small \u21d2 LGWR cannot keep up \u21d2 <code>\"log file sync\"<\/code> waits<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">4. Large Pool (Optional but Recommended)<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Used for:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>RMAN backup and restore<\/li>\n\n\n\n<li>Parallel execution message buffers<\/li>\n\n\n\n<li>Shared server (MTS) session memory<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Parameter:<\/strong> <code>LARGE_POOL_SIZE<\/code><br><strong>Best practice:<\/strong> At least <strong>128MB<\/strong> if RMAN is used<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">5. Java Pool<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Used for Java stored procedures and SQLJ<\/li>\n\n\n\n<li>Parameter: <code>JAVA_POOL_SIZE<\/code><\/li>\n\n\n\n<li>Not required if Java is not used in the database<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">6. Streams Pool<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Used by Oracle Streams and GoldenGate<\/li>\n\n\n\n<li>Parameter: <code>STREAMS_POOL_SIZE<\/code><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">7. Fixed SGA<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Internal housekeeping structures<\/li>\n\n\n\n<li>Stores instance state information<\/li>\n\n\n\n<li>Size cannot be controlled by DBA<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Interview Questions (SGA)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Q1: What is SGA and its main components?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>SGA is shared memory allocated at instance startup.<br>Main components include:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Database Buffer Cache<\/li>\n\n\n\n<li>Shared Pool<\/li>\n\n\n\n<li>Redo Log Buffer<\/li>\n\n\n\n<li>Optional pools like Large Pool and Java Pool<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q2: What is the difference between hard parse and soft parse?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Hard parse:<\/strong> New SQL, execution plan created, CPU intensive<\/li>\n\n\n\n<li><strong>Soft parse:<\/strong> Existing plan reused, very fast<\/li>\n\n\n\n<li>Use bind variables to reduce hard parses<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q3: How to reduce hard parses?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Use bind variables<\/li>\n\n\n\n<li>Increase <code>SHARED_POOL_SIZE<\/code><\/li>\n\n\n\n<li>Set <code>CURSOR_SHARING=FORCE<\/code> (last resort)<\/li>\n\n\n\n<li>Check <code>V$SQL<\/code> for similar SQL with different literals<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q4: What is stored in Database Buffer Cache?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>Copies of data blocks (table and index blocks).<br>Modified blocks become dirty until written to disk by DBWn.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q5: What happens when buffer cache is full?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>LRU evicts clean buffers.<br>If no clean buffers are available, DBWn writes dirty buffers to disk, causing <code>\"free buffer waits\"<\/code>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">B. PGA (Program Global Area) \u2013 Private Memory<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Purpose:<\/strong><br>Private memory for each server process. Not shared.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">What is stored in PGA?<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Session memory (logon info, session state)<\/li>\n\n\n\n<li>Private SQL area (bind info, runtime data)<\/li>\n\n\n\n<li>Sort area (ORDER BY, GROUP BY)<\/li>\n\n\n\n<li>Hash area (hash joins)<\/li>\n\n\n\n<li>Bitmap merge area<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">PGA Parameters<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>PGA_AGGREGATE_TARGET<\/code> \u2013 Total PGA for all sessions<\/li>\n\n\n\n<li><code>PGA_AGGREGATE_LIMIT<\/code> \u2013 Hard limit (19c)<\/li>\n\n\n\n<li><code>WORKAREA_SIZE_POLICY=AUTO<\/code> (recommended)<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">How Oracle allocates PGA<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Estimates memory per operation<\/li>\n\n\n\n<li>Distributes PGA among active sessions<\/li>\n\n\n\n<li>If insufficient, spills to TEMP tablespace<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>DBA concern:<\/strong><br>Small PGA \u21d2 disk-based sorts \u21d2 very slow performance<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Monitoring views:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>V$PGASTAT<\/code><\/li>\n\n\n\n<li><code>V$PROCESS<\/code><\/li>\n\n\n\n<li><code>V$SQL_WORKAREA<\/code><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Interview Questions (PGA)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Q1: Difference between SGA and PGA?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>SGA:<\/strong> Shared, allocated at startup<\/li>\n\n\n\n<li><strong>PGA:<\/strong> Private, per process, dynamic<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q2: What happens if PGA is insufficient?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>Sort and hash operations spill to TEMP tablespace, causing <code>\"direct path read\/write temp\"<\/code> waits.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q3: How to identify PGA undersizing?<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT name, value\nFROM v$pgastat\nWHERE name IN ('over allocation count','global memory bound');\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">If <code>over allocation count<\/code> &gt; 0, increase PGA.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q4: What is PGA_AGGREGATE_LIMIT (19c)?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>Hard limit on total PGA usage. Oracle terminates sessions using excessive PGA to protect the instance.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">C. Memory Management Modes (19c)<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">1. Automatic Shared Memory Management (ASMM) \u2013 <strong>Recommended<\/strong><\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SGA_TARGET = 10G\nSGA_MAX_SIZE = 12G\nPGA_AGGREGATE_TARGET = 3G\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>How it works:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Oracle auto-tunes SGA components<\/li>\n\n\n\n<li>Minimums can be set (<code>DB_CACHE_SIZE<\/code>, <code>SHARED_POOL_SIZE<\/code>)<\/li>\n\n\n\n<li>Restart required only to increase <code>SGA_MAX_SIZE<\/code><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Best for:<\/strong> Production systems<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">2. Automatic Memory Management (AMM)<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>MEMORY_TARGET = 13G\nMEMORY_MAX_TARGET = 16G\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Limitations:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Not supported with HugePages on Linux<\/li>\n\n\n\n<li>Overhead on large SGA systems<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Best for:<\/strong> Small DBs, test\/dev<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">3. Manual Memory Management<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SGA_TARGET = 0\nDB_CACHE_SIZE = 6G\nSHARED_POOL_SIZE = 2G\nLARGE_POOL_SIZE = 256M\nLOG_BUFFER = 16M\nPGA_AGGREGATE_TARGET = 3G\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>When to use:<\/strong> Rare, legacy, very specific tuning<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Critical Rules (Very Important)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">\u274c <strong>Never mix AMM and ASMM<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>MEMORY_TARGET = 10G\nSGA_TARGET = 8G   -- WRONG\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u2705 <strong>Valid configurations<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>ASMM (Recommended):<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SGA_TARGET = 10G\nSGA_MAX_SIZE = 12G\nPGA_AGGREGATE_TARGET = 3G\nMEMORY_TARGET = 0\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>AMM:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>MEMORY_TARGET = 13G\nMEMORY_MAX_TARGET = 16G\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Parameter Change \u2013 Downtime Summary<\/h2>\n\n\n\n<figure class=\"wp-block-table has-small-font-size\"><table><thead><tr><th>Parameter<\/th><th>Dynamic<\/th><th>Restart<\/th><\/tr><\/thead><tbody><tr><td>SGA_TARGET<\/td><td>Yes<\/td><td>No<\/td><\/tr><tr><td>SGA_MAX_SIZE<\/td><td>No<\/td><td>Yes<\/td><\/tr><tr><td>PGA_AGGREGATE_TARGET<\/td><td>Yes<\/td><td>No<\/td><\/tr><tr><td>MEMORY_TARGET<\/td><td>Yes<\/td><td>No<\/td><\/tr><tr><td>MEMORY_MAX_TARGET<\/td><td>No<\/td><td>Yes<\/td><\/tr><tr><td>LOG_BUFFER<\/td><td>No<\/td><td>Yes<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Interview Questions (Memory)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Q1: Can I set both MEMORY_TARGET and SGA_TARGET?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>No. They are mutually exclusive. If MEMORY_TARGET is set, SGA_TARGET is ignored.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q2: Which is better \u2013 AMM or ASMM?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>ASMM is recommended for 19c production systems.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q3: Can I increase SGA_TARGET without downtime?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>Yes, up to <code>SGA_MAX_SIZE<\/code>. Beyond that, restart is required.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q4: How to check memory usage?<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$sga;\nSELECT * FROM v$pgastat;\nSHOW PARAMETER sga\nSHOW PARAMETER pga\nSHOW PARAMETER memory\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q5: How to switch from AMM to ASMM?<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SET memory_target=0 SCOPE=SPFILE;\nALTER SYSTEM SET sga_target=10G SCOPE=SPFILE;\nALTER SYSTEM SET sga_max_size=12G SCOPE=SPFILE;\nALTER SYSTEM SET pga_aggregate_target=3G SCOPE=SPFILE;\n\nSHUTDOWN IMMEDIATE;\nSTARTUP;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">3. Process Architecture<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">A. User Process<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Definition:<\/strong><br>Started when a user connects to the database.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Key points:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Started when user connects (SQL*Plus, application, JDBC)<\/li>\n\n\n\n<li>Runs on <strong>client machine<\/strong>, not on database server<\/li>\n\n\n\n<li><strong>NOT<\/strong> part of Oracle instance<\/li>\n\n\n\n<li>Sends SQL requests to server process<\/li>\n\n\n\n<li>Receives results back from server process<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">B. Server Process<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Definition:<\/strong><br>Runs on the database server and handles requests from the user process.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Responsibilities:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Parses and executes SQL<\/li>\n\n\n\n<li>Reads data blocks into buffer cache<\/li>\n\n\n\n<li>Returns results to user process<\/li>\n\n\n\n<li>Manages private memory (PGA)<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Connection Modes<\/h3>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">1. Dedicated Server (Default)<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Characteristics:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>One server process per user connection<\/li>\n\n\n\n<li>Server process dedicated to a single user<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Pros:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Simple and predictable<\/li>\n\n\n\n<li>Better for long-running operations<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Cons:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>More memory consumption (each process has its own PGA)<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Best for:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>OLTP systems<\/li>\n\n\n\n<li>Batch jobs<\/li>\n\n\n\n<li>Less than 500 concurrent users<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">2. Shared Server (MTS \u2013 Multi-Threaded Server)<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Characteristics:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Multiple users share a pool of server processes<\/li>\n\n\n\n<li>Uses dispatcher processes to route requests<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Pros:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Supports more users with less memory usage<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Cons:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Not suitable for long-running queries<\/li>\n\n\n\n<li>Not suitable for batch jobs<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Best for:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Many concurrent short transactions (&gt;1000 users)<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Configuration:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SET shared_servers=10 SCOPE=BOTH;\nALTER SYSTEM SET dispatchers='(PROTOCOL=TCP)(DISPATCHERS=5)' SCOPE=BOTH;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Interview Questions<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Q1: What&#8217;s the difference between user process and server process?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>User process runs on the client machine and sends SQL requests.<br>Server process runs on the database server, executes SQL, reads data into buffer cache, and returns results.<br>In dedicated server mode, it is 1:1. In shared server mode, multiple users share server processes.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q2: When should you use shared server?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>When you have many (&gt;1000) concurrent users with short transactions.<br>Each dedicated server process uses ~4\u20135MB memory.<br>With 5000 users, that is ~25GB memory just for server processes.<br>Shared server significantly reduces this memory usage.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q3: Can I mix dedicated and shared server?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>Yes. You can configure shared servers, and users can still force a dedicated connection.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Force dedicated server in tnsnames.ora\n(SERVER=DEDICATED)\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">C. Background Processes<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">Background processes are automatically started when the instance starts.<br>They perform housekeeping and internal maintenance tasks.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Mandatory Background Processes<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><em>(Instance will not start without these)<\/em><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">1. SMON (System Monitor)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Responsibilities<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Instance recovery at startup after a crash\n<ul class=\"wp-block-list\">\n<li>Rolls forward redo logs (cache recovery)<\/li>\n\n\n\n<li>Opens database for use<\/li>\n\n\n\n<li>Rollback is done later by SMON in background<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Cleans up temporary segments in <strong>TEMP<\/strong> tablespace<\/li>\n\n\n\n<li>Coalesces free space in dictionary-managed tablespaces (old method)<\/li>\n\n\n\n<li>Recovers dead transactions skipped during instance recovery<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Recovery Process<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>Crash \u2192 Startup \u2192 SMON reads redo logs\n\u2192 Rolls forward (apply changes)\n\u2192 Database opens\n\u2192 SMON rolls back uncommitted transactions in background\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>If SMON dies:<\/strong><br>Instance crashes immediately.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Monitoring<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$bgprocess WHERE name = 'SMON';\nSELECT * FROM v$recovery_progress;  -- During recovery\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">2. PMON (Process Monitor)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Responsibilities<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Monitors user server processes<\/li>\n\n\n\n<li>Cleans up after failed user processes:\n<ul class=\"wp-block-list\">\n<li>Releases locks held by dead sessions<\/li>\n\n\n\n<li>Releases database resources<\/li>\n\n\n\n<li>Rolls back uncommitted transactions<\/li>\n\n\n\n<li>Frees PGA memory<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Registers instance with listener (dynamic registration)<\/li>\n\n\n\n<li>Restarts dead dispatcher processes (shared server mode)<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Example Scenario<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>User application crashes \u2192 Connection lost\n\u2192 PMON detects failure\n\u2192 Rolls back user's transaction\n\u2192 Releases locks\n\u2192 Other users can proceed\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>If PMON dies:<\/strong><br>Instance crashes.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Monitoring<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$bgprocess WHERE name = 'PMON';\n\n-- Check dead processes cleaned by PMON\nSELECT COUNT(*) FROM v$process WHERE paddr = '00';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">3. DBWn (Database Writer) \u2013 Multiple Processes<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Naming<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">DBW0, DBW1, \u2026, DBW9, DBWa, DBWb, \u2026, DBWj<br><em>(Up to 100 DBWn processes)<\/em><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Responsibilities<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Writes dirty buffers from the database buffer cache to datafiles<\/li>\n\n\n\n<li>Writes in <strong>batch<\/strong> for efficiency (not one block at a time)<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">When DBWn Writes (Triggers)<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Checkpoint occurs (CKPT signals DBWn)<\/li>\n\n\n\n<li>No free buffers available (server process needs space)<\/li>\n\n\n\n<li>Timeout (every 3 seconds)<\/li>\n\n\n\n<li>Tablespace goes offline or becomes read-only<\/li>\n\n\n\n<li>Table is dropped or truncated<\/li>\n\n\n\n<li>Before redo log switch (to maintain write-ahead logging)<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Write-Ahead Logging (WAL) Protocol<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Redo must be written to disk <strong>before<\/strong> data blocks<\/li>\n\n\n\n<li>LGWR always writes before DBWn<\/li>\n\n\n\n<li>Ensures database recoverability<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Performance Tuning<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Increase number of DBWn processes on busy systems:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SET db_writer_processes=4 SCOPE=SPFILE;\n-- Requires restart\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Increasing DBWn can help if you see <strong>&#8220;free buffer waits&#8221;<\/strong><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Monitoring<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$bgprocess WHERE name LIKE 'DBW%';\n\n-- Check write activity\nSELECT name, value\nFROM v$sysstat\nWHERE name LIKE '%dbwr%' OR name LIKE '%buffer%';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Performance Issue<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>If DBWn is slow, users experience <strong>&#8220;free buffer waits&#8221;<\/strong><\/li>\n\n\n\n<li>This means clean buffers are not available in the buffer cache<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">4. LGWR (Log Writer)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Critical Process:<\/strong><br>If LGWR cannot write, the <strong>database hangs<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Responsibilities<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Writes redo log buffer to <strong>online redo log files<\/strong><\/li>\n\n\n\n<li>Writes <strong>sequentially<\/strong> (very fast)<\/li>\n\n\n\n<li>Ensures <strong>data durability<\/strong><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">When LGWR Writes (Triggers)<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>User commits<br><em>(Synchronous \u2014 COMMIT does not return until LGWR writes)<\/em><\/li>\n\n\n\n<li>Redo log buffer is <strong>1\/3 full<\/strong> (proactive)<\/li>\n\n\n\n<li>Before DBWn writes (Write-Ahead Logging protocol)<\/li>\n\n\n\n<li>Every <strong>3 seconds<\/strong> (timeout)<\/li>\n\n\n\n<li>More than <strong>1MB of redo<\/strong> is generated<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Critical for Commits<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>COMMIT;\n-- Waits here until LGWR writes redo to disk\n-- This wait is called \"log file sync\"\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Performance Issues<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Slow disk I\/O for redo logs \u21d2 users experience <strong>&#8220;log file sync&#8221;<\/strong> waits<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Solutions:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Put redo logs on the <strong>fastest storage<\/strong> (SSD, RAID 10)<\/li>\n\n\n\n<li><strong>Never<\/strong> place redo logs on NFS or slow SAN<\/li>\n\n\n\n<li>Separate redo logs from datafiles (different disks)<\/li>\n\n\n\n<li>Use <strong>ASM<\/strong> or raw devices for redo logs<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Monitoring<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$bgprocess WHERE name = 'LGWR';\n\n-- Check LGWR-related wait events\nSELECT event, total_waits, time_waited\nFROM v$system_event\nWHERE event LIKE 'log file%';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">If LGWR Cannot Write<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Archive destination full \u2192 <strong>Database hangs<\/strong><\/li>\n\n\n\n<li>Redo log disk full \u2192 <strong>Database hangs<\/strong><\/li>\n\n\n\n<li>Redo log corruption \u2192 <strong>Instance crashes<\/strong><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">5. CKPT (Checkpoint Process)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Responsibilities<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Signals DBWn to write dirty buffers to disk<\/li>\n\n\n\n<li>Updates control file with checkpoint information<\/li>\n\n\n\n<li>Updates datafile headers with checkpoint SCN<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">What is a Checkpoint?<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Point in time when all dirty buffers are written to disk<\/li>\n\n\n\n<li>Database is in a <strong>consistent state<\/strong><\/li>\n\n\n\n<li>Checkpoint <strong>SCN is recorded<\/strong><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Types of Checkpoints<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Complete checkpoint<\/strong>\n<ul class=\"wp-block-list\">\n<li>All dirty buffers written<\/li>\n\n\n\n<li>Occurs during <code>SHUTDOWN NORMAL<\/code> \/ <code>SHUTDOWN IMMEDIATE<\/code><\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Incremental checkpoint<\/strong>\n<ul class=\"wp-block-list\">\n<li>Continuous process<\/li>\n\n\n\n<li>Controlled by DBWn during normal operation<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Partial checkpoint<\/strong>\n<ul class=\"wp-block-list\">\n<li>Only specific tablespace or datafile<\/li>\n\n\n\n<li>Example: <code>ALTER TABLESPACE OFFLINE<\/code><\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">When Checkpoints Occur<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Redo log switch<\/li>\n\n\n\n<li>Forced checkpoint:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM CHECKPOINT;\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Database shutdown (except <code>ABORT<\/code>)<\/li>\n\n\n\n<li>Tablespace offline or read-only<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Checkpoint Tuning<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Control checkpoint frequency (in seconds)\nFAST_START_MTTR_TARGET = 300  -- Target 5-minute recovery time\n-- Oracle auto-tunes checkpoint frequency to meet this target\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Monitoring<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$bgprocess WHERE name = 'CKPT';\nSELECT checkpoint_change# FROM v$database;\nSELECT checkpoint_change#, checkpoint_time FROM v$datafile_header;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Interview Questions (Checkpoint, DBWn, LGWR, SMON, PMON)<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q1: What happens during a checkpoint?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>CKPT signals DBWn to write all dirty buffers to datafiles.<br>Once complete, CKPT updates the control file and datafile headers with the checkpoint SCN (System Change Number).<br>This ensures database consistency and reduces recovery time.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q2: What is the sequence: LGWR or DBWn writes first?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>LGWR always writes first (Write-Ahead Logging).<br>Redo must be written to disk before the data block.<br>This ensures that if a crash happens after DBWn writes, the database can still recover using redo logs.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q3: User says \u201ccommit is slow\u201d. What do you check?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>Check the <strong>&#8220;log file sync&#8221;<\/strong> wait event in <code>V$SESSION_EVENT<\/code> or AWR.<br>This usually indicates LGWR is slow writing redo logs.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Investigate:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Redo log disk I\/O performance<\/li>\n\n\n\n<li>Network latency (if redo is on remote storage)<\/li>\n\n\n\n<li>Whether archive destination is full<\/li>\n\n\n\n<li><code>V$SYSTEM_EVENT<\/code> for log file\u2013related waits<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q4: Difference between SMON and PMON?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>SMON:<\/strong> System-level recovery\n<ul class=\"wp-block-list\">\n<li>Cleans up instance after crash<\/li>\n\n\n\n<li>Coalesces free space<\/li>\n\n\n\n<li>Cleans temporary segments<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>PMON:<\/strong> Process-level cleanup\n<ul class=\"wp-block-list\">\n<li>Cleans up after failed user sessions<\/li>\n\n\n\n<li>Releases locks<\/li>\n\n\n\n<li>Registers instance with listener<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q5: Can I increase the number of DBWn processes without downtime?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>No. <code>DB_WRITER_PROCESSES<\/code> is a <strong>static parameter<\/strong>.<br>It must be set in the SPFILE and requires an instance restart.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q6: What if all DBWn processes are busy and buffer cache has no free space?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>Server processes wait on the <strong>&#8220;free buffer waits&#8221;<\/strong> event.<br>They cannot read new blocks into the buffer cache, causing severe performance impact.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Solution:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Increase DBWn processes<\/li>\n\n\n\n<li>Improve disk write performance<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q7: Why does LGWR write every 3 seconds even with no commits?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>To prevent the redo log buffer from filling up and to ensure redo is persisted quickly.<br>Even without commits, DML operations generate redo that must be protected.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Optional but Important Processes<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">6. ARCn (Archiver)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><em>(Multiple processes: ARC0\u2013ARC9, up to ARCa\u2013ARCz)<\/em><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Copies filled online redo logs to the archive location<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Active only if:<\/strong><br>Database is in <strong>ARCHIVELOG<\/strong> mode<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Why Is ARCn Needed?<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Point-in-time recovery (restore backup + apply archived logs)<\/li>\n\n\n\n<li>Data Guard standby synchronization<\/li>\n\n\n\n<li>Compliance and audit requirements<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">How ARCn Works<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>Online redo log fills\n\u2192 LGWR switches to next redo log\n\u2192 ARCn copies filled log to archive destination\n\u2192 Old redo log becomes available for reuse\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Configuration<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Enable ARCHIVELOG Mode<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SHUTDOWN IMMEDIATE;\nSTARTUP MOUNT;\nALTER DATABASE ARCHIVELOG;\nALTER DATABASE OPEN;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Set Archive Destinations<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SET log_archive_dest_1='LOCATION=\/archive1\/';\nALTER SYSTEM SET log_archive_dest_2='LOCATION=\/archive2\/';  -- Multiplex\nALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Configure Multiple ARCn Processes<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SET log_archive_max_processes=4;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Critical Issue<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>Archive destination full\n\u2192 ARCn cannot write\n\u2192 LGWR cannot switch logs\n\u2192 Database HANGS\n\u2192 No transactions possible\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">DBA Daily Checks<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Check archive destination status\nSELECT dest_id, status, destination, error\nFROM v$archive_dest;\n\n-- Check archive gap (Data Guard)\nSELECT * FROM v$archive_gap;\n\n-- Check failed archives\nSELECT * FROM v$archive_dest_failure;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Monitoring<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$bgprocess WHERE name LIKE 'ARC%';\nSELECT COUNT(*) FROM v$archived_log WHERE completion_time &gt; SYSDATE - 1;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Parameter \u2013 Downtime Requirement<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>LOG_ARCHIVE_MAX_PROCESSES<\/code>\n<ul class=\"wp-block-list\">\n<li><strong>Dynamic parameter<\/strong><\/li>\n\n\n\n<li><strong>No restart required<\/strong><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">7. RECO (Recoverer)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Resolves failures in <strong>distributed transactions<\/strong><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Active only if:<\/strong><br>Database links are used for distributed transactions<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Scenario<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Transaction spans two databases via database link\nBEGIN\n  UPDATE local_table SET ...;\n  UPDATE remote_table@dblink SET ...;\n  COMMIT;  -- Two-phase commit\nEND;\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>-- If the remote database crashes after PREPARE but before COMMIT,\n-- RECO automatically resolves the transaction when the remote database comes back up\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Monitoring<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$bgprocess WHERE name = 'RECO';\n\n-- In-doubt distributed transactions\nSELECT * FROM dba_2pc_pending;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">8. MMON (Manageability Monitor)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Performance monitoring and statistics collection<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Responsibilities<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Collects <strong>AWR (Automatic Workload Repository)<\/strong> snapshots<br><em>(Default interval: every 60 minutes)<\/em><\/li>\n\n\n\n<li>Writes statistics to <strong>SYSAUX<\/strong> tablespace<\/li>\n\n\n\n<li>Issues alerts for threshold violations<\/li>\n\n\n\n<li>Maintains optimizer statistics<\/li>\n\n\n\n<li>Generates automatic space management alerts<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Critical for DBAs<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Without MMON, <strong>no AWR reports<\/strong> (no performance analysis)<\/li>\n\n\n\n<li><code>dbms_workload_repository<\/code> packages rely on MMON<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Configuration<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Set AWR Snapshot Interval (Minutes)<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>EXEC dbms_workload_repository.modify_snapshot_settings(interval =&gt; 30);\n<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Set AWR Retention (Days)<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>EXEC dbms_workload_repository.modify_snapshot_settings(retention =&gt; 14);\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Monitoring<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$bgprocess WHERE name = 'MMON';\n\nSELECT snap_id, begin_interval_time, end_interval_time\nFROM dba_hist_snapshot\nORDER BY snap_id DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">9. MMNL (Manageability Monitor Lite)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Writes <strong>Active Session History (ASH)<\/strong> data to memory<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">How It Differs from MMON<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>MMNL:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Writes ASH samples <strong>every second<\/strong> (high frequency)<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>MMON:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Writes AWR snapshots <strong>every hour<\/strong> (low frequency)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">ASH Data Storage<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Last <strong>1 hour<\/strong> of ASH data is kept <strong>in memory<\/strong>\n<ul class=\"wp-block-list\">\n<li>View: <code>V$ACTIVE_SESSION_HISTORY<\/code><\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Older ASH data is written to disk by MMON\n<ul class=\"wp-block-list\">\n<li>View: <code>DBA_HIST_ACTIVE_SESS_HISTORY<\/code><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Why MMNL Is Important<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Enables <strong>real-time performance troubleshooting<\/strong><\/li>\n\n\n\n<li>Captures what sessions are doing <strong>every second<\/strong><\/li>\n\n\n\n<li>Essential for diagnosing <strong>short-lived \/ transient performance issues<\/strong><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Monitoring<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$bgprocess WHERE name = 'MMNL';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Query ASH Data<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Current ASH data (last 1 hour, in memory):<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT *\nFROM v$active_session_history\nWHERE sample_time &gt; SYSDATE - 1\/24;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Historical ASH data (stored on disk):<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT *\nFROM dba_hist_active_sess_history\nWHERE sample_time &gt; SYSDATE - 1;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">10. MMAN (Memory Manager)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Handles <strong>automatic memory management<\/strong><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Responsibilities<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Dynamically resizes SGA components<br><em>(when using ASMM or AMM)<\/em><\/li>\n\n\n\n<li>Coordinates memory transfers between SGA components<\/li>\n\n\n\n<li>Ensures memory allocation stays within:\n<ul class=\"wp-block-list\">\n<li><code>SGA_TARGET<\/code> (ASMM)<\/li>\n\n\n\n<li><code>MEMORY_TARGET<\/code> (AMM)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Example<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>11:00 AM \u2013 Heavy queries\n\u2192 MMAN increases buffer cache\n\u2192 Shrinks shared pool\n\n02:00 PM \u2013 Heavy parsing\n\u2192 MMAN increases shared pool\n\u2192 Shrinks buffer cache\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Active Only If<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>ASMM is used (<code>SGA_TARGET<\/code> set), <strong>or<\/strong><\/li>\n\n\n\n<li>AMM is used (<code>MEMORY_TARGET<\/code> set)<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Monitoring<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$bgprocess WHERE name = 'MMAN';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">View Memory Resize Operations<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT component,\n       current_size\/1024\/1024 MB,\n       oper_type,\n       oper_mode,\n       start_time,\n       end_time\nFROM v$sga_resize_ops\nORDER BY start_time DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">11. LREG (Listener Registration) \u2013 19c<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Registers the database instance with the listener <strong>dynamically<\/strong><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">In Older Versions<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>This responsibility was handled by <strong>PMON<\/strong><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Why a Separate Process in 19c?<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Offloads work from PMON<\/li>\n\n\n\n<li>Improves performance for connection handling<\/li>\n\n\n\n<li>Handles service registration more efficiently<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">How LREG Works<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>Instance starts\n\u2192 LREG contacts listener\n\u2192 Registers services and load information\n\u2192 Listener knows which instances are available\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Configuration<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Local Listener<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SET local_listener=\n'(ADDRESS=(PROTOCOL=TCP)(HOST=server1)(PORT=1521))';\n<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Remote Listener (SCAN in RAC)<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SET remote_listener='scan-name:1521';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Monitoring<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$bgprocess WHERE name = 'LREG';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Check Registered Services<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT name, pdb, network_name\nFROM v$services;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">12. DBRM (Database Resource Manager)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Enforces <strong>resource plans<\/strong><br><em>(CPU, I\/O limits per user or application)<\/em><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Active only if:<\/strong><br>Resource Manager is enabled<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Use Case<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Limit reporting users to <strong>30% CPU<\/strong>.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>BEGIN\n  dbms_resource_manager.create_plan(\n    plan    =&gt; 'DAYTIME_PLAN',\n    comment =&gt; 'Daytime resource allocation'\n  );\n\n  dbms_resource_manager.create_plan_directive(\n    plan              =&gt; 'DAYTIME_PLAN',\n    group_or_subplan  =&gt; 'REPORTING_GROUP',\n    cpu_p1            =&gt; 30  -- Max 30% CPU\n  );\nEND;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Activate Resource Plan<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SET resource_manager_plan='DAYTIME_PLAN';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Monitoring<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$bgprocess WHERE name = 'DBRM';\nSELECT * FROM v$rsrc_plan;\nSELECT * FROM v$rsrc_consumer_group;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">13. Jnnn (Job Queue Processes)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><em>(J000, J001, \u2026)<\/em><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Executes scheduled jobs created using:\n<ul class=\"wp-block-list\">\n<li><code>DBMS_SCHEDULER<\/code><\/li>\n\n\n\n<li><code>DBMS_JOB<\/code><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Configuration<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Set Maximum Job Processes<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SET job_queue_processes=10;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Monitoring<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$bgprocess WHERE name LIKE 'J0%';\n\nSELECT job_name, state, running_instance\nFROM dba_scheduler_running_jobs;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">14. Pnnn (Parallel Execution Servers)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><em>(P000, P001, \u2026)<\/em><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Executes <strong>parallel queries<\/strong> and <strong>parallel DML<\/strong> operations<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Active when:<\/strong><br>Queries use parallel execution<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Example<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Parallel query\nSELECT \/*+ PARALLEL(8) *\/ *\nFROM large_table;\n-- Uses 8 parallel processes\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Configuration<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SET parallel_min_servers=5;   -- Keep 5 idle\nALTER SYSTEM SET parallel_max_servers=20;  -- Maximum allowed\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Monitoring<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$bgprocess WHERE name LIKE 'P0%';\nSELECT * FROM v$px_process;\nSELECT degree FROM v$sql WHERE sql_id = 'xxxxx';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">15. Snnn (Shared Server Processes)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><em>(S000, S001, \u2026)<\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Active only if:<\/strong><br>Shared server mode is enabled<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">16. Dnnn (Dispatcher Processes)<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><em>(D000, D001, \u2026)<\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Active only if:<\/strong><br>Shared server mode is enabled<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Routes user requests to available shared server processes<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">17. Wnnn (Space Management Coordinator Workers)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Performs automatic space management tasks<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Tasks<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Segment shrink operations<\/li>\n\n\n\n<li>Space reclamation<\/li>\n\n\n\n<li>Statistics gathering for segments<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">18. VKTM (Virtual Keeper of Time)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Provides <strong>high-resolution time service<\/strong> for the instance<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Why a Separate Process?<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>System calls for time are expensive<\/li>\n\n\n\n<li>VKTM makes one call and shares time with all processes<\/li>\n\n\n\n<li>Improves overall performance<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Monitoring<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$bgprocess WHERE name = 'VKTM';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">19. DIAG (Diagnosability Daemon)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Monitors instance health and captures diagnostic data<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Responsibilities<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Detects hangs and deadlocks<\/li>\n\n\n\n<li>Dumps diagnostic information<\/li>\n\n\n\n<li>Coordinates ADR (Automatic Diagnostic Repository)<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">20. GEN0 (General Task Execution Process)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Performs general maintenance tasks<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Introduced in:<\/strong><br>Oracle 12c and later<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Process Architecture \u2013 Summary Table<\/h2>\n\n\n\n<figure class=\"wp-block-table has-small-font-size\"><table><thead><tr><th>Process<\/th><th>Critical?<\/th><th>Purpose<\/th><th>If it dies<\/th><th>Can have multiple?<\/th><\/tr><\/thead><tbody><tr><td>SMON<\/td><td>\u2705 Yes<\/td><td>Instance recovery, space cleanup<\/td><td>Instance crashes<\/td><td>No (only 1)<\/td><\/tr><tr><td>PMON<\/td><td>\u2705 Yes<\/td><td>Process cleanup, lock release<\/td><td>Instance crashes<\/td><td>No (only 1)<\/td><\/tr><tr><td>DBWn<\/td><td>\u2705 Yes<\/td><td>Write dirty buffers to disk<\/td><td>Instance crashes<\/td><td>Yes (DBW0\u2013DBWj)<\/td><\/tr><tr><td>LGWR<\/td><td>\u2705 Yes<\/td><td>Write redo to logs<\/td><td>Instance crashes \/ hangs<\/td><td>No (only 1)<\/td><\/tr><tr><td>CKPT<\/td><td>\u2705 Yes<\/td><td>Coordinate checkpoints<\/td><td>Instance crashes<\/td><td>No (only 1)<\/td><\/tr><tr><td>ARCn<\/td><td>\u26a0\ufe0f ARCHIVELOG only<\/td><td>Archive redo logs<\/td><td>DB hangs if destination full<\/td><td>Yes (ARC0\u2013ARCz)<\/td><\/tr><tr><td>RECO<\/td><td>\ud83d\udd27 If using DB links<\/td><td>Distributed transaction recovery<\/td><td>No immediate impact<\/td><td>No (only 1)<\/td><\/tr><tr><td>MMON<\/td><td>\ud83d\udcca Performance<\/td><td>AWR snapshots, alerts<\/td><td>No AWR data<\/td><td>No (only 1)<\/td><\/tr><tr><td>MMNL<\/td><td>\ud83d\udcca Performance<\/td><td>ASH sampling<\/td><td>No ASH data<\/td><td>No (only 1)<\/td><\/tr><tr><td>MMAN<\/td><td>\ud83e\udde0 AMM\/ASMM only<\/td><td>Automatic memory management<\/td><td>Manual memory only<\/td><td>No (only 1)<\/td><\/tr><tr><td>LREG<\/td><td>\ud83d\udd0c 19c<\/td><td>Listener registration<\/td><td>Services not registered<\/td><td>No (only 1)<\/td><\/tr><tr><td>DBRM<\/td><td>\ud83c\udfaf Resource Manager<\/td><td>Enforce resource plans<\/td><td>Resource limits ignored<\/td><td>No (only 1)<\/td><\/tr><tr><td>Jnnn<\/td><td>\u23f0 Scheduled jobs<\/td><td>Execute DBMS_SCHEDULER jobs<\/td><td>Jobs do not run<\/td><td>Yes (J000\u2013J999)<\/td><\/tr><tr><td>Pnnn<\/td><td>\u26a1 Parallel execution<\/td><td>Parallel query execution<\/td><td>Parallel operations fail<\/td><td>Yes (P000\u2013P999)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Interview Questions (Process Architecture)<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q1: Which background processes are mandatory?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>SMON, PMON, DBWn, LGWR, CKPT.<br>Without any of these processes, the instance cannot start or will crash immediately.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q2: My database is hanging. No users can connect or run queries. What should I check?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Check if archive destination is full (ARCHIVELOG mode):<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>df -h \/archive\n<\/code><\/pre>\n\n\n\n<ol start=\"2\" class=\"wp-block-list\">\n<li>Check the alert log for errors:<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>tail -100 alert_&lt;SID&gt;.log\n<\/code><\/pre>\n\n\n\n<ol start=\"3\" class=\"wp-block-list\">\n<li>Check if LGWR is waiting:<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT event\nFROM v$session\nWHERE program LIKE '%LGWR%';\n<\/code><\/pre>\n\n\n\n<ol start=\"4\" class=\"wp-block-list\">\n<li>Check for <strong>&#8220;log file sync&#8221;<\/strong> waits:<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT event, total_waits\nFROM v$system_event\nWHERE event LIKE 'log%';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q3: What is the relationship between CKPT and DBWn?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>CKPT is the <strong>coordinator<\/strong> and DBWn is the <strong>worker<\/strong>.<br>CKPT signals DBWn to write dirty buffers.<br>After DBWn completes, CKPT updates the control file and datafile headers with the checkpoint SCN.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q4: Can I manually trigger a checkpoint?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>Yes.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM CHECKPOINT;  -- Forces a complete checkpoint\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Use this before taking a tablespace offline or before a backup.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q5: I see 8 DBWn processes but set <code>db_writer_processes=4<\/code>. Why?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>Oracle may start more DBWn processes than configured if it detects heavy write activity.<br>This is part of <strong>automatic tuning<\/strong>.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$bgprocess WHERE name LIKE 'DBW%';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q6: What is the sequence of events during COMMIT?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>User issues COMMIT\n\u2192 Server process writes redo to redo log buffer\n\u2192 Server process signals LGWR\n\u2192 LGWR writes redo to disk\n\u2192 LGWR signals server process\n\u2192 COMMIT returns to user\n\u2192 SCN incremented\n\u2192 Locks released\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">DBWn writes dirty buffers later (asynchronously).<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q7: How do you check if MMON is running and collecting AWR?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Check MMON process:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$bgprocess WHERE name = 'MMON';\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Check recent AWR snapshots:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT snap_id, begin_interval_time, end_interval_time\nFROM dba_hist_snapshot\nWHERE begin_interval_time &gt; SYSDATE - 1\nORDER BY snap_id DESC;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>If no recent snapshots exist:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM dba_hist_wr_control;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q8: Difference between MMON and MMNL?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>MMON:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Collects AWR snapshots every <strong>60 minutes<\/strong> (configurable)<\/li>\n\n\n\n<li>Writes data to <strong>SYSAUX<\/strong> tablespace<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>MMNL:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Samples active sessions <strong>every second<\/strong><\/li>\n\n\n\n<li>Keeps ASH data in memory (<code>V$ACTIVE_SESSION_HISTORY<\/code>)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q9: Can I disable ARCn processes?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>You cannot disable the ARCn processes directly, but you can disable <strong>ARCHIVELOG mode<\/strong>.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SHUTDOWN IMMEDIATE;\nSTARTUP MOUNT;\nALTER DATABASE NOARCHIVELOG;\nALTER DATABASE OPEN;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">ARCn processes remain present but stay <strong>idle<\/strong>.<br>This is <strong>not recommended for production<\/strong> databases.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q10: I increased <code>job_queue_processes<\/code> but still see only 5 job processes. Why?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>Oracle starts job processes <strong>on demand<\/strong>, not all at once.<br>As scheduled jobs execute, Oracle spawns additional <strong>Jnnn<\/strong> processes up to the configured limit.<br>Idle job processes are not created unnecessarily.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q11: What is PMON\u2019s role in RAC?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>In RAC, when an instance crashes, PMON on surviving instances:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Recovers locks held by the failed instance<\/li>\n\n\n\n<li>Performs instance recovery<\/li>\n\n\n\n<li>Re-masters resources<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q12: How to identify which process is consuming the most CPU?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>On the database side:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT s.sid,\n       s.serial#,\n       s.username,\n       s.program,\n       p.spid,\n       s.status\nFROM v$session s,\n     v$process p\nWHERE s.paddr = p.addr\nORDER BY p.spid;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>On the OS side (Linux):<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>top -p &lt;SPID&gt;   -- Use SPID obtained from the query above\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">4. Storage Architecture<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">A. Physical Storage Structure<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">1. Datafiles (<code>.dbf<\/code>)<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Store actual database objects:\n<ul class=\"wp-block-list\">\n<li>Tables<\/li>\n\n\n\n<li>Indexes<\/li>\n\n\n\n<li>LOBs<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Characteristics<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Binary format<\/li>\n\n\n\n<li>Belong to <strong>exactly ONE tablespace<\/strong><\/li>\n\n\n\n<li>Can be resized (manual or auto-extend)<\/li>\n\n\n\n<li>Can be moved or renamed while the database is running <em>(with restrictions)<\/em><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Best Practices<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Use <strong>ASM<\/strong> or <strong>LVM<\/strong> for easier management<\/li>\n\n\n\n<li>Separate datafiles across different disks for better I\/O distribution<\/li>\n\n\n\n<li>Size datafiles appropriately to avoid frequent auto-extends<\/li>\n\n\n\n<li>Monitor space usage using:\n<ul class=\"wp-block-list\">\n<li><code>DBA_DATA_FILES<\/code><\/li>\n\n\n\n<li><code>V$DATAFILE<\/code><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Operations<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Add Datafile to Tablespace<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLESPACE users\nADD DATAFILE '\/u01\/oradata\/ORCL\/users02.dbf' SIZE 1G\nAUTOEXTEND ON NEXT 100M MAXSIZE 10G;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Resize Datafile<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE DATAFILE '\/u01\/oradata\/ORCL\/users01.dbf' RESIZE 5G;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Enable Autoextend<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE DATAFILE '\/u01\/oradata\/ORCL\/users01.dbf'\nAUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Take Datafile Offline<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\"><em>(Database must be in ARCHIVELOG mode)<\/em><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE DATAFILE '\/u01\/oradata\/ORCL\/users01.dbf' OFFLINE;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Bring Datafile Online<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE DATAFILE '\/u01\/oradata\/ORCL\/users01.dbf' ONLINE;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Move \/ Rename Datafile<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLESPACE users OFFLINE;\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>-- At OS level\nmv users01.dbf \/new_location\/users01.dbf\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLESPACE users RENAME DATAFILE\n'\/old_location\/users01.dbf' TO '\/new_location\/users01.dbf';\n\nALTER TABLESPACE users ONLINE;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Monitoring<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Check Datafile Size and Usage<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT file_name,\n       tablespace_name,\n       bytes\/1024\/1024 MB,\n       autoextensible,\n       maxbytes\/1024\/1024 MAX_MB\nFROM dba_data_files\nORDER BY tablespace_name;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Check Datafile I\/O<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT name,\n       phyrds,\n       phywrts,\n       readtim,\n       writetim\nFROM v$datafile df, v$filestat fs\nWHERE df.file# = fs.file#;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">2. Control Files<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Database <strong>metadata repository<\/strong><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Contains<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Database name and creation timestamp<\/li>\n\n\n\n<li>Datafile names and locations<\/li>\n\n\n\n<li>Redo log file names and locations<\/li>\n\n\n\n<li>Tablespace information<\/li>\n\n\n\n<li>Current log sequence number<\/li>\n\n\n\n<li>Checkpoint information (SCN)<\/li>\n\n\n\n<li>RMAN backup metadata<\/li>\n\n\n\n<li>Archive log history<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Critical Importance<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Database <strong>cannot open<\/strong> without a valid control file<\/li>\n\n\n\n<li>Loss of <strong>all control files<\/strong> = major disaster<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Best Practices<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Always <strong>multiplex<\/strong> control files\n<ul class=\"wp-block-list\">\n<li>Minimum <strong>3 copies<\/strong> on different disks\/controllers<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Never place all control files on the same disk<\/li>\n\n\n\n<li>Always include control files in backup strategy<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Multiplexing Control Files<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Check Current Control Files<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT name FROM v$controlfile;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Add Control File Locations<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\"><em>(Requires instance restart)<\/em><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SET control_files=\n'\/u01\/oradata\/ORCL\/control01.ctl',\n'\/u02\/oradata\/ORCL\/control02.ctl',\n'\/u03\/oradata\/ORCL\/control03.ctl'\nSCOPE=SPFILE;\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>SHUTDOWN IMMEDIATE;\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>-- At OS level\ncp control01.ctl control03.ctl\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>STARTUP;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Recovery from Control File Loss<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">If One Copy Is Still Available<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SHUTDOWN ABORT;\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>-- At OS level\ncp good_control.ctl missing_control.ctl\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>STARTUP;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">If All Control Files Are Lost<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>RMAN&gt; RESTORE CONTROLFILE FROM AUTOBACKUP;\nRMAN&gt; ALTER DATABASE MOUNT;\nRMAN&gt; RECOVER DATABASE;\nRMAN&gt; ALTER DATABASE OPEN RESETLOGS;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Monitoring<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$controlfile;\n\nSELECT name, value\nFROM v$parameter\nWHERE name = 'control_files';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Control File Record Sections<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT type,\n       record_size,\n       records_total,\n       records_used\nFROM v$controlfile_record_section;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">3. Online Redo Log Files<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Records <strong>all changes<\/strong> made to the database:\n<ul class=\"wp-block-list\">\n<li>DML<\/li>\n\n\n\n<li>DDL<\/li>\n\n\n\n<li>COMMIT operations<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Critical For<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Database recovery after crash<\/li>\n\n\n\n<li>Data Guard replication<\/li>\n\n\n\n<li>GoldenGate replication<\/li>\n\n\n\n<li>Point-in-time recovery<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Structure<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Redo Log Groups<\/strong>\n<ul class=\"wp-block-list\">\n<li>Logical unit<\/li>\n\n\n\n<li>Minimum <strong>2 groups required<\/strong><\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Redo Log Members<\/strong>\n<ul class=\"wp-block-list\">\n<li>Physical files within a group<\/li>\n\n\n\n<li>Should always be <strong>multiplexed<\/strong><\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Circular Usage<\/strong>\n<ul class=\"wp-block-list\">\n<li>LGWR continuously switches between groups<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Example Structure<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Group 1<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>\/u01\/redo\/redo01a.log<\/code> (Member 1)<\/li>\n\n\n\n<li><code>\/u02\/redo\/redo01b.log<\/code> (Member 2 \u2013 mirror)<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Group 2<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>\/u01\/redo\/redo02a.log<\/code> (Member 1)<\/li>\n\n\n\n<li><code>\/u02\/redo\/redo02b.log<\/code> (Member 2 \u2013 mirror)<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Group 3<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>\/u01\/redo\/redo03a.log<\/code> (Member 1)<\/li>\n\n\n\n<li><code>\/u02\/redo\/redo03b.log<\/code> (Member 2 \u2013 mirror)<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">How It Works<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>LGWR writes to Group 1\n\u2192 Group 1 fills\n\u2192 Log switch\n\u2192 LGWR switches to Group 2\n\u2192 ARCn archives Group 1\n\u2192 Group 2 fills\n\u2192 Log switch\n\u2192 LGWR switches to Group 3\n\u2192 Group 3 fills\n\u2192 LGWR switches back to Group 1 (overwritten)\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Sizing Guidelines<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Ideal log switch every <strong>15\u201330 minutes<\/strong><\/li>\n\n\n\n<li>Too frequent:\n<ul class=\"wp-block-list\">\n<li>Performance impact<\/li>\n\n\n\n<li>Checkpoint storms<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Too infrequent:\n<ul class=\"wp-block-list\">\n<li>Longer crash recovery time<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Best Practices<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Minimum <strong>3 redo log groups<\/strong><\/li>\n\n\n\n<li>Minimum <strong>2 members per group<\/strong> (multiplexing)<\/li>\n\n\n\n<li>Place members on <strong>different disks\/controllers<\/strong><\/li>\n\n\n\n<li>Use <strong>fast storage<\/strong> (SSD recommended)<\/li>\n\n\n\n<li>Size redo logs based on <strong>redo generation rate<\/strong><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Operations<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Check Redo Log Configuration<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT group#, thread#, sequence#, bytes\/1024\/1024 MB,\n       members, status, archived\nFROM v$log;\n\nSELECT group#, member, status\nFROM v$logfile;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Add Redo Log Group<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE ADD LOGFILE GROUP 4\n('\/u01\/redo\/redo04a.log', '\/u02\/redo\/redo04b.log')\nSIZE 512M;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Add Member to Existing Group<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE ADD LOGFILE MEMBER\n'\/u03\/redo\/redo01c.log' TO GROUP 1;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Drop Redo Log Group<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\"><em>(Must be inactive and archived)<\/em><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE DROP LOGFILE GROUP 4;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Drop Redo Log Member<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\"><em>(At least one member per group must remain)<\/em><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE DROP LOGFILE MEMBER '\/u03\/redo\/redo01c.log';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Resize Redo Logs<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><em>(Redo logs cannot be resized directly \u2014 must be recreated)<\/em><\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>1. Add new groups with larger size<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE ADD LOGFILE GROUP 4\n('\/u01\/redo\/redo04a.log', '\/u02\/redo\/redo04b.log') SIZE 1G;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>2. Force log switches<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SWITCH LOGFILE;\nALTER SYSTEM CHECKPOINT;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>3. Drop old smaller groups (after archive)<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE DROP LOGFILE GROUP 1;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>4. Recreate dropped group with new size<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE ADD LOGFILE GROUP 1\n('\/u01\/redo\/redo01a.log', '\/u02\/redo\/redo01b.log') SIZE 1G;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Force Log Switch<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SWITCH LOGFILE;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Clear Corrupted Redo Log<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\">\u26a0\ufe0f <strong>Use with extreme caution<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE CLEAR LOGFILE GROUP 2;\n\n-- If unarchived\nALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Monitoring<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Log Switches per Hour<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') AS hour,\n       COUNT(*) AS switches\nFROM v$log_history\nWHERE first_time &gt; SYSDATE - 1\nGROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')\nORDER BY hour;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Redo Generation Rate (MB)<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT inst_id,\n       ROUND(SUM(value)\/1024\/1024, 2) AS redo_mb\nFROM gv$sysstat\nWHERE name = 'redo size'\nGROUP BY inst_id;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Current Redo Log Status<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT group#, status, archived\nFROM v$log;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Redo-Related Wait Events<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT event, total_waits, time_waited\nFROM v$system_event\nWHERE event LIKE '%log%'\nORDER BY time_waited DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Common Issues<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>\u201cCannot allocate new log\u201d<\/strong>\n<ul class=\"wp-block-list\">\n<li>All groups active<\/li>\n\n\n\n<li>Solution: Increase redo log size or add more groups<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>\u201cCheckpoint not complete\u201d<\/strong>\n<ul class=\"wp-block-list\">\n<li>Log switches too fast<\/li>\n\n\n\n<li>DBWn cannot keep up<\/li>\n\n\n\n<li>Solution: Increase redo log size<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>\u201cLog file sync\u201d waits<\/strong>\n<ul class=\"wp-block-list\">\n<li>LGWR is slow<\/li>\n\n\n\n<li>Check redo log disk I\/O<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">4. Archive Redo Log Files<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Copies of online redo logs used for recovery<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Active only when:<\/strong><br>Database is in <strong>ARCHIVELOG<\/strong> mode<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Why Use ARCHIVELOG Mode<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Point-in-time recovery<br><em>(restore backup + apply archived redo logs)<\/em><\/li>\n\n\n\n<li>Online backups (hot backups while database is running)<\/li>\n\n\n\n<li>Data Guard standby synchronization<\/li>\n\n\n\n<li>Zero data loss (with proper backup strategy)<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Disadvantages<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Requires additional disk space<\/li>\n\n\n\n<li>Database can hang if archive destination becomes full<\/li>\n\n\n\n<li>Slightly more overhead compared to NOARCHIVELOG mode<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Configuration<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Check Current Log Mode<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT log_mode FROM v$database;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Enable ARCHIVELOG Mode<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SHUTDOWN IMMEDIATE;\nSTARTUP MOUNT;\nALTER DATABASE ARCHIVELOG;\nALTER DATABASE OPEN;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Configure Archive Destinations<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SET log_archive_dest_1='LOCATION=\/archive1\/ MANDATORY' SCOPE=BOTH;\nALTER SYSTEM SET log_archive_dest_2='LOCATION=\/archive2\/ OPTIONAL'  SCOPE=BOTH;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Archive Log Format<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\"><em>(Must include <code>%t<\/code> thread, <code>%s<\/code> sequence, <code>%r<\/code> resetlogs)<\/em><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SET log_archive_format='arch_%t_%s_%r.arc' SCOPE=SPFILE;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Configure Multiple Archiver Processes<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SET log_archive_max_processes=4 SCOPE=BOTH;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Disable ARCHIVELOG Mode<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SHUTDOWN IMMEDIATE;\nSTARTUP MOUNT;\nALTER DATABASE NOARCHIVELOG;\nALTER DATABASE OPEN;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Backup and Cleanup<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Backup Archive Logs Using RMAN<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>RMAN&gt; BACKUP ARCHIVELOG ALL DELETE INPUT;\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Backs up archived redo logs<\/li>\n\n\n\n<li>Deletes them after successful backup<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Delete Archives Older Than 7 Days<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>RMAN&gt; DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Manual Deletion (Warning)<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Never delete archived redo logs manually<\/li>\n\n\n\n<li>Always confirm they are backed up using RMAN<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Monitoring<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Check Archive Destinations<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT dest_id, status, destination, error\nFROM v$archive_dest;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Check Archive Log Generation<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT TO_CHAR(first_time, 'YYYY-MM-DD') AS log_date,\n       COUNT(*)\nFROM v$archived_log\nWHERE first_time &gt; SYSDATE - 7\nGROUP BY TO_CHAR(first_time, 'YYYY-MM-DD')\nORDER BY 1;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Check Archive Destination Space<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$recovery_file_dest;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Check Archive Gaps (Data Guard)<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$archive_gap;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">5. Parameter File<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\">There are <strong>two types<\/strong> of parameter files used by Oracle:<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">a) PFILE (Parameter File) \u2013 Text<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Characteristics<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Location:<\/strong> <code>$ORACLE_HOME\/dbs\/init&lt;SID&gt;.ora<\/code> (Linux\/Unix)<\/li>\n\n\n\n<li><strong>Format:<\/strong> Plain text, human-readable<\/li>\n\n\n\n<li><strong>Modifications:<\/strong> Edited manually using a text editor<\/li>\n\n\n\n<li><strong>Changes:<\/strong> Not persistent unless saved in the file<\/li>\n\n\n\n<li><strong>Use case:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Initial database creation<\/li>\n\n\n\n<li>Startup troubleshooting<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Example PFILE<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>db_name=ORCL\nsga_target=10G\npga_aggregate_target=3G\ncontrol_files=\/u01\/control01.ctl,\/u02\/control02.ctl\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">b) SPFILE (Server Parameter File) \u2013 Binary \u2705 <em>Recommended<\/em><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Characteristics<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Location:<\/strong> <code>$ORACLE_HOME\/dbs\/spfile&lt;SID&gt;.ora<\/code> (Linux\/Unix)<\/li>\n\n\n\n<li><strong>Format:<\/strong> Binary (not directly editable)<\/li>\n\n\n\n<li><strong>Modifications:<\/strong> Performed using <code>ALTER SYSTEM<\/code><\/li>\n\n\n\n<li><strong>Changes:<\/strong> Can be persistent or temporary<\/li>\n\n\n\n<li><strong>Use case:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Production databases (standard practice)<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Scope Options<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Change Only in Memory<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\"><em>(Lost after restart)<\/em><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SET sga_target=12G SCOPE=MEMORY;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Change Only in SPFILE<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\"><em>(Takes effect after restart)<\/em><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SET sga_target=12G SCOPE=SPFILE;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Change in Both Memory and SPFILE<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\"><em>(Immediate + persistent)<\/em><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SET sga_target=12G SCOPE=BOTH;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Operations<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Check Which Parameter File Is Being Used<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT value FROM v$parameter WHERE name = 'spfile';\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>If the value is <strong>NULL<\/strong>, the database is using a <strong>PFILE<\/strong><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Create SPFILE from PFILE<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE SPFILE FROM PFILE;\nCREATE SPFILE='\/u01\/spfileORCL.ora' FROM PFILE='\/tmp\/initORCL.ora';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Create PFILE from SPFILE<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\"><em>(For editing or backup)<\/em><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE PFILE FROM SPFILE;\nCREATE PFILE='\/tmp\/initORCL.ora' FROM SPFILE;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Start Database with a Specific Parameter File<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>STARTUP PFILE='\/tmp\/initORCL.ora';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Best Practices<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Always use <strong>SPFILE<\/strong> in production<\/li>\n\n\n\n<li>Keep a backup copy of PFILE:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE PFILE='\/backup\/init.ora' FROM SPFILE;\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Document all parameter changes<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">6. Password File<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Stores passwords for users with <strong>SYSDBA<\/strong> and <strong>SYSOPER<\/strong> privileges<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Why Password File Is Needed<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Enables <strong>remote SYSDBA<\/strong> connections<\/li>\n\n\n\n<li>Allows database <strong>startup and shutdown remotely<\/strong><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Location<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>$ORACLE_HOME\/dbs\/orapw&lt;SID&gt;<\/code> (Linux\/Unix)<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Operations<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Create Password File<\/h4>\n\n\n\n<p class=\"wp-block-paragraph\"><em>(OS-level command)<\/em><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>orapwd file=$ORACLE_HOME\/dbs\/orapwORCL password=SysPassword123 entries=10\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Grant SYSDBA Privilege<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>GRANT SYSDBA TO scott;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Check Users with SYSDBA Privilege<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$pwfile_users;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Remote SYSDBA Connection Using Password File<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>sqlplus sys\/password@orcl as sysdba\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">7. Alert Log<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Chronological log of database messages and errors<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Contains<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Startup and shutdown events<\/li>\n\n\n\n<li>All errors (ORA- errors)<\/li>\n\n\n\n<li>Log switches<\/li>\n\n\n\n<li>Checkpoints<\/li>\n\n\n\n<li>Archive operations<\/li>\n\n\n\n<li>Internal errors<\/li>\n\n\n\n<li>Administrative operations<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Location (Oracle 19c)<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>$ORACLE_BASE\/diag\/rdbms\/&lt;db_name&gt;\/&lt;instance_name&gt;\/trace\/alert_&lt;SID&gt;.log\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Also Available in XML Format<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>$ORACLE_BASE\/diag\/rdbms\/&lt;db_name&gt;\/&lt;instance_name&gt;\/alert\/log.xml\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">DBA Daily Tasks<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Monitor Alert Log Continuously<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>tail -f $ORACLE_BASE\/diag\/rdbms\/orcl\/orcl\/trace\/alert_orcl.log\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Check for Errors Today<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>grep -i \"ora-\" alert_orcl.log | grep \"$(date +%Y-%m-%d)\"\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Search for a Specific Error<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>grep -i \"ora-00600\" alert_orcl.log\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Check Last 100 Lines<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>tail -100 alert_orcl.log\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Query Alert Log from SQL<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Query Recent Alert Log Entries (11g+)<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT originating_timestamp,\n       message_text\nFROM v$diag_alert_ext\nWHERE originating_timestamp &gt; SYSDATE - 1\nORDER BY originating_timestamp;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Check for ORA Errors<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT message_text\nFROM v$diag_alert_ext\nWHERE message_text LIKE '%ORA-%'\nAND originating_timestamp &gt; SYSDATE - 1;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">8. Trace Files<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Provides detailed diagnostic information for troubleshooting<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Types of Trace Files<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>User trace files<\/strong>\n<ul class=\"wp-block-list\">\n<li>Generated by user sessions<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Background process trace files<\/strong>\n<ul class=\"wp-block-list\">\n<li>Generated by processes such as SMON, PMON, etc.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Location<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Same directory as the alert log<\/li>\n\n\n\n<li>Typically:<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>$ORACLE_BASE\/diag\/rdbms\/&lt;db_name&gt;\/&lt;instance_name&gt;\/trace\/\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Generate Trace File<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Enable Trace for Current Session<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SESSION SET sql_trace=TRUE;\nALTER SESSION SET tracefile_identifier='MY_TRACE';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Enable Trace Using Events (10046)<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SESSION SET EVENTS '10046 trace name context forever, level 12';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Find Your Trace File<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">Using Diagnostic Views<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT value\nFROM v$diag_info\nWHERE name = 'Default Trace File';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Manually Construct Trace File Name<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT value || '\/' || instance_name || '_ora_' || spid || '_MY_TRACE.trc'\nFROM v$parameter, v$instance, v$process\nWHERE name = 'user_dump_dest'\nAND addr = (\n  SELECT paddr\n  FROM v$session\n  WHERE sid = (\n    SELECT sid FROM v$mystat WHERE rownum = 1\n  )\n);\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Disable Trace<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SESSION SET sql_trace=FALSE;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Format Trace File (Make It Readable)<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>tkprof tracefile.trc output.txt\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Interview Questions (Storage Architecture)<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q1: What\u2019s the difference between a control file and a parameter file?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Control file:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Contains database structure metadata<\/li>\n\n\n\n<li>Includes datafile locations, redo log information, and checkpoint SCN<\/li>\n\n\n\n<li>Binary file<\/li>\n\n\n\n<li>Updated automatically by Oracle<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Parameter file:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Contains instance configuration settings (memory, processes, etc.)<\/li>\n\n\n\n<li>SPFILE is binary, PFILE is text<\/li>\n\n\n\n<li>Updated and managed by the DBA<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q2: Database won\u2019t open \u2013 <em>ORA-00205: error in identifying control file<\/em>. What should you do?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>This error indicates the control file is missing or corrupted.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Steps to resolve:<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Check if the control file exists at the location specified in the <code>control_files<\/code> parameter<\/li>\n\n\n\n<li>If one copy exists, copy it to the missing location and restart the database<\/li>\n\n\n\n<li>If all copies are lost, restore from backup:<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>RMAN&gt; RESTORE CONTROLFILE FROM AUTOBACKUP;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q3: What is the minimum number of redo log groups?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Minimum <strong>2 redo log groups<\/strong> are required<\/li>\n\n\n\n<li>Best practice is <strong>3 or more groups<\/strong> to avoid <em>\u201ccannot allocate new log\u201d<\/em> errors<\/li>\n\n\n\n<li>Each group should have <strong>2 or more members<\/strong> (multiplexed)<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q4: What\u2019s the difference between online redo logs and archive logs?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Online redo logs:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Fixed number of groups<\/li>\n\n\n\n<li>Reused in a circular manner by LGWR<\/li>\n\n\n\n<li>Always exist<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Archive logs:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Copies of filled online redo logs<\/li>\n\n\n\n<li>Generated only in ARCHIVELOG mode<\/li>\n\n\n\n<li>Continuously grow and require cleanup<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q5: Log switches are happening every 5 minutes. Is this a problem?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>Yes. The ideal log switch interval is <strong>15\u201330 minutes<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Too frequent log switches cause:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Checkpoint storms (performance impact)<\/li>\n\n\n\n<li>Excessive archive log generation<\/li>\n\n\n\n<li>ARCn bottlenecks<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Solution:<\/strong><br>Increase redo log size.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q6: How do you check if the database is in ARCHIVELOG mode?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT log_mode FROM v$database;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Or:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ARCHIVE LOG LIST;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q7: Can I delete archive logs manually?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br><strong>NO. Never delete archive logs manually.<\/strong><br>Always use <strong>RMAN<\/strong>, which ensures the logs are backed up first.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>RMAN&gt; DELETE ARCHIVELOG ALL COMPLETED BEFORE 'SYSDATE-7';\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">RMAN guarantees that archived logs are safely backed up before deletion.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q8: What\u2019s the difference between PFILE and SPFILE?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>PFILE:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Text file<\/li>\n\n\n\n<li>Edited manually<\/li>\n\n\n\n<li>Changes are not persistent after restart<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>SPFILE:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Binary file<\/li>\n\n\n\n<li>Edited using <code>ALTER SYSTEM<\/code><\/li>\n\n\n\n<li>Changes can be persistent (<code>SCOPE=SPFILE<\/code> or <code>SCOPE=BOTH<\/code>)<\/li>\n\n\n\n<li>Always recommended for production environments<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q9: Database is hung and alert log shows <em>\u201ccheckpoint not complete\u201d<\/em>. What does it mean?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>Redo logs are switching too fast.<br>DBWn has not finished writing dirty buffers from the previous checkpoint before LGWR needs to reuse the redo log.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Solutions:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Increase redo log size<\/li>\n\n\n\n<li>Add more redo log groups<\/li>\n\n\n\n<li>Tune DBWn (increase DBWn processes)<\/li>\n\n\n\n<li>Check for slow datafile I\/O<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q10: How do you increase redo log size without downtime?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>You cannot resize existing redo logs.<br>However, you can add new larger groups and drop the old ones while the database remains online.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- 1. Add new groups with larger size\nALTER DATABASE ADD LOGFILE GROUP 4 ('\/u01\/redo\/redo04.log') SIZE 1G;\nALTER DATABASE ADD LOGFILE GROUP 5 ('\/u01\/redo\/redo05.log') SIZE 1G;\n\n-- 2. Force log switches to make old groups inactive\nALTER SYSTEM SWITCH LOGFILE;\nALTER SYSTEM CHECKPOINT;\n\n-- 3. Check status (wait until INACTIVE and ARCHIVED)\nSELECT group#, status, archived FROM v$log;\n\n-- 4. Drop old small groups\nALTER DATABASE DROP LOGFILE GROUP 1;\nALTER DATABASE DROP LOGFILE GROUP 2;\n\n-- 5. Recreate with new size\nALTER DATABASE ADD LOGFILE GROUP 1 ('\/u01\/redo\/redo01.log') SIZE 1G;\nALTER DATABASE ADD LOGFILE GROUP 2 ('\/u01\/redo\/redo02.log') SIZE 1G;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">The database stays <strong>online<\/strong> throughout this process.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q11: What happens if I lose one member of a redo log group?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>Nothing happens immediately.<br>LGWR continues writing to the remaining member(s).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Actions to take:<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Check the alert log for the error<\/li>\n\n\n\n<li>Recreate the missing redo log member<\/li>\n\n\n\n<li>Or drop the damaged member if other members exist<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q12: What happens if I lose <strong>all<\/strong> members of the current redo log group?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>The database crashes immediately.<br>If redo cannot be written, the instance fails.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Recovery steps:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>STARTUP MOUNT;\nALTER DATABASE CLEAR UNARCHIVED LOGFILE GROUP 2;\nALTER DATABASE OPEN;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">\u26a0\ufe0f Data loss is possible for transactions in that redo log.<br>A <strong>full backup is required immediately<\/strong> after recovery.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q13: Why should control files be multiplexed, but I see only one path sometimes?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>You are likely checking different views.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT name FROM v$controlfile;\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Shows <strong>all<\/strong> multiplexed control file locations<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT value FROM v$parameter WHERE name = 'control_files';\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Shows the parameter value containing all configured paths<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q14: Can I start the database without SPFILE or PFILE?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><br>No. Oracle searches for parameter files in the following order:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><code>spfile&lt;SID&gt;.ora<\/code> (default location)<\/li>\n\n\n\n<li><code>spfile.ora<\/code> (default location)<\/li>\n\n\n\n<li><code>init&lt;SID&gt;.ora<\/code> (default location)<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">If none are found, Oracle returns:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ORA-01078: failure in processing system parameters\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q15: What\u2019s the difference between alert log and trace files?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Answer:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Alert log:<\/strong>\n<ul class=\"wp-block-list\">\n<li>High-level events<\/li>\n\n\n\n<li>Errors<\/li>\n\n\n\n<li>Startup and shutdown messages<\/li>\n\n\n\n<li>Administrative operations<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Trace files:<\/strong>\n<ul class=\"wp-block-list\">\n<li>Detailed diagnostic information<\/li>\n\n\n\n<li>Specific session or background process details<\/li>\n\n\n\n<li>SQL execution and wait events<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">B. Logical Storage Structure<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Hierarchy<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>Database \u2192 Tablespace \u2192 Segment \u2192 Extent \u2192 Data Block\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>DATABASE (ORCL)\n    \u251c\u2500\u2500 SYSTEM Tablespace\n    \u2502       \u251c\u2500\u2500 Segment: SYS.TAB$\n    \u2502       \u2502       \u251c\u2500\u2500 Extent 1 (128 blocks)\n    \u2502       \u2502       \u2514\u2500\u2500 Extent 2 (128 blocks)\n    \u2502       \u2514\u2500\u2500 Segment: SYS.OBJ$\n    \u251c\u2500\u2500 SYSAUX Tablespace\n    \u251c\u2500\u2500 USERS Tablespace\n    \u2502       \u2514\u2500\u2500 Segment: SCOTT.EMP (table)\n    \u2502               \u251c\u2500\u2500 Extent 1 (8 blocks)\n    \u2502               \u2514\u2500\u2500 Extent 2 (8 blocks)\n    \u2514\u2500\u2500 TEMP Tablespace\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">1. Data Block<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Definition<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Smallest unit of I\/O in Oracle.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Characteristics<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Set at database creation (<code>DB_BLOCK_SIZE<\/code>)<\/li>\n\n\n\n<li>Typical sizes: 2KB, 4KB, 8KB (most common), 16KB, 32KB<\/li>\n\n\n\n<li>Cannot be changed after database creation (must recreate database)<\/li>\n\n\n\n<li>OS block size should match or be smaller than DB block size<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Block Structure<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502 Block Header            \u2502 \u2190 Metadata (address, type, SCN)\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502 Table Directory         \u2502 \u2190 Tables in this block\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502 Row Directory           \u2502 \u2190 Row addresses\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502 Free Space              \u2502 \u2190 Available space\n\u251c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2524\n\u2502 Row Data                \u2502 \u2190 Actual data\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Block overhead: ~100\u2013200 bytes per block<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Choosing Block Size<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>8KB (default):<\/strong> Good for most OLTP applications<\/li>\n\n\n\n<li><strong>16KB \/ 32KB:<\/strong> Better for data warehouse workloads (large rows, full table scans)<\/li>\n\n\n\n<li><strong>4KB:<\/strong> Suitable for very small rows and high concurrency environments<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Check Block Size<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT value \nFROM v$parameter \nWHERE name = 'db_block_size';\n\nSELECT tablespace_name, block_size \nFROM dba_tablespaces;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">2. Extent<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Definition<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Logical unit of contiguous data blocks.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Characteristics<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Set of consecutive data blocks<\/li>\n\n\n\n<li>Initial extent allocated when object is created<\/li>\n\n\n\n<li>Additional extents allocated as the object grows<\/li>\n\n\n\n<li>Managed automatically by Oracle in modern versions<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Extent Allocation<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Example: Table creation with extent parameters\nCREATE TABLE large_table (...)\nTABLESPACE users\nSTORAGE (\n  INITIAL 1M        -- Initial extent size\n  NEXT 1M           -- Next extent size\n  MINEXTENTS 1      -- Minimum extents at creation\n  MAXEXTENTS 100    -- Maximum extents (UNLIMITED in modern Oracle)\n  PCTINCREASE 0     -- Percentage increase for each extent\n);\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">In Modern Oracle (ASSM \u2013 Automatic Segment Space Management)<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Oracle manages extents automatically<\/li>\n\n\n\n<li>No need to specify the <code>STORAGE<\/code> clause<\/li>\n\n\n\n<li>Uses bitmaps to track block usage within segments<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Check Extents<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Extents for a specific object\nSELECT segment_name, extent_id, bytes\/1024\/1024 MB, blocks\nFROM dba_extents\nWHERE owner = 'SCOTT' \n  AND segment_name = 'EMP'\nORDER BY extent_id;\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Count extents per object\nSELECT segment_name, COUNT(*) extents\nFROM dba_extents\nWHERE owner = 'SCOTT'\nGROUP BY segment_name;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">3. Segment<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Definition<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">A <strong>segment<\/strong> is a set of extents that stores data for a specific database object.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Types of Segments<\/h3>\n\n\n\n<h4 class=\"wp-block-heading\">a) Data Segment<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Stores table data<\/li>\n\n\n\n<li>One segment per table (non-partitioned tables)<\/li>\n\n\n\n<li>One segment per partition (partitioned tables)<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">b) Index Segment<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Stores index data<\/li>\n\n\n\n<li>One segment per index<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">c) Temporary Segment<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Created in the <strong>TEMP<\/strong> tablespace<\/li>\n\n\n\n<li>Used for:\n<ul class=\"wp-block-list\">\n<li>Sorting<\/li>\n\n\n\n<li>Hash joins<\/li>\n\n\n\n<li>Global temporary tables<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Automatically dropped after the operation completes<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">d) Undo Segment<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Stores <strong>before-image<\/strong> of data (old values)<\/li>\n\n\n\n<li>Used for:\n<ul class=\"wp-block-list\">\n<li>Transaction rollback<\/li>\n\n\n\n<li>Read consistency<\/li>\n\n\n\n<li>Flashback operations<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>System-managed in the <strong>UNDO<\/strong> tablespace (modern Oracle)<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">e) LOB Segment<\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Stores large objects such as <strong>CLOB<\/strong> and <strong>BLOB<\/strong><\/li>\n\n\n\n<li>Stored separately from the table segment<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Segment Space Management<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">1. Manual Segment Space Management (MSSM) \u2014 Old Method<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLESPACE users\nDATAFILE '\/u01\/users01.dbf' SIZE 100M\nSEGMENT SPACE MANAGEMENT MANUAL;  -- Old way\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Characteristics:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Uses <strong>freelists<\/strong> to track free blocks<\/li>\n\n\n\n<li>Requires more DBA intervention<\/li>\n\n\n\n<li>Not recommended for modern databases<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">2. Automatic Segment Space Management (ASSM) \u2014 Modern (Default)<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLESPACE users\nDATAFILE '\/u01\/users01.dbf' SIZE 100M\nSEGMENT SPACE MANAGEMENT AUTO;  -- Default in 10g+\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Characteristics:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Uses <strong>bitmaps<\/strong> to track block status<\/li>\n\n\n\n<li>Better concurrency<\/li>\n\n\n\n<li>Self-tuning<\/li>\n\n\n\n<li><strong>Always use this in Oracle 19c<\/strong><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Check Segments<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>-- All segments for a user\nSELECT segment_name, segment_type, tablespace_name, \n       bytes\/1024\/1024 MB, blocks, extents\nFROM dba_segments\nWHERE owner = 'SCOTT'\nORDER BY bytes DESC;\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Largest segments in the database\nSELECT owner, segment_name, segment_type, bytes\/1024\/1024\/1024 GB\nFROM dba_segments\nORDER BY bytes DESC\nFETCH FIRST 20 ROWS ONLY;\n<\/code><\/pre>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Segment growth over time (AWR)\nSELECT * \nFROM dba_hist_seg_stat \nWHERE obj# = ...;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">4. Tablespace<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Definition<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">A <strong>tablespace<\/strong> is a logical container for segments and maps to one or more physical datafiles.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Organize database objects logically<\/li>\n\n\n\n<li>Separate user data from system data<\/li>\n\n\n\n<li>Allocate different storage for different applications<\/li>\n\n\n\n<li>Simplify backup and recovery (backup by tablespace)<\/li>\n\n\n\n<li>Control I\/O by placing tablespaces on different disks<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Types of Tablespaces<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">a) SYSTEM Tablespace \u26a0\ufe0f Critical<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Created automatically during database creation<\/li>\n\n\n\n<li>Contains data dictionary (SYS schema objects)<\/li>\n\n\n\n<li><strong>Never store user data here<\/strong><\/li>\n\n\n\n<li>Cannot be dropped or taken offline (except during recovery)<\/li>\n\n\n\n<li>Must always remain online<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">b) SYSAUX Tablespace \u26a0\ufe0f Critical<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Auxiliary tablespace for SYSTEM<\/li>\n\n\n\n<li>Contains:\n<ul class=\"wp-block-list\">\n<li>AWR data (performance statistics)<\/li>\n\n\n\n<li>MMON \/ MMNL data<\/li>\n\n\n\n<li>Automatic Maintenance tasks<\/li>\n\n\n\n<li>Oracle Text, Spatial, Multimedia metadata<\/li>\n\n\n\n<li>STATSPACK, DBMS_SCHEDULER metadata<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Can be taken offline temporarily (not recommended)<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">c) UNDO Tablespace \u26a0\ufe0f Critical<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Stores undo segments (before-image data)<\/li>\n\n\n\n<li>Required for:\n<ul class=\"wp-block-list\">\n<li>Transaction rollback<\/li>\n\n\n\n<li>Read consistency (multi-version concurrency)<\/li>\n\n\n\n<li>Flashback queries<\/li>\n\n\n\n<li>Flashback database<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>System-managed (do not manually create undo segments)<\/li>\n\n\n\n<li>Only <strong>one active UNDO tablespace<\/strong> at a time<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">d) TEMP Tablespace<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Stores temporary segments used for:\n<ul class=\"wp-block-list\">\n<li>Sorting (<code>ORDER BY<\/code>, <code>GROUP BY<\/code>, <code>DISTINCT<\/code>)<\/li>\n\n\n\n<li>Hash joins<\/li>\n\n\n\n<li>Index creation<\/li>\n\n\n\n<li>Temporary tables<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Contents are automatically dropped after operations<\/li>\n\n\n\n<li>Never backed up (recreated if lost)<\/li>\n\n\n\n<li>Supports <strong>Temporary Tablespace Groups<\/strong><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">e) User Tablespaces<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Store application data (tables, indexes)<\/li>\n\n\n\n<li>Examples:\n<ul class=\"wp-block-list\">\n<li>USERS<\/li>\n\n\n\n<li>DATA<\/li>\n\n\n\n<li>INDEX<\/li>\n\n\n\n<li>LOB tablespaces<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Multiple tablespaces can be created for better organization<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Tablespace States<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">1. ONLINE<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Available for use<\/li>\n\n\n\n<li>Default state<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLESPACE users ONLINE;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">2. OFFLINE<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Not available for use, but metadata remains intact<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Types:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>NORMAL<\/strong> \u2013 Clean offline (checkpoint performed)<\/li>\n\n\n\n<li><strong>TEMPORARY<\/strong> \u2013 Quick offline (no checkpoint, requires recovery)<\/li>\n\n\n\n<li><strong>IMMEDIATE<\/strong> \u2013 Forced offline (no checkpoint, requires recovery)<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLESPACE users OFFLINE NORMAL;\nALTER TABLESPACE users OFFLINE TEMPORARY;\nALTER TABLESPACE users OFFLINE IMMEDIATE;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">3. READ ONLY<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Data can be queried but not modified<\/li>\n\n\n\n<li>Useful for static or archive data<\/li>\n\n\n\n<li>Can be skipped during backups if unchanged for long periods<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLESPACE archive_data READ ONLY;\nALTER TABLESPACE archive_data READ WRITE;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Creating Tablespaces<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Basic Tablespace<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLESPACE app_data\nDATAFILE '\/u01\/oradata\/ORCL\/app_data01.dbf' SIZE 1G\nAUTOEXTEND ON NEXT 100M MAXSIZE 10G;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Tablespace with Specific Features<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TABLESPACE app_data\nDATAFILE '\/u01\/oradata\/ORCL\/app_data01.dbf' SIZE 1G\nAUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED\nEXTENT MANAGEMENT LOCAL          -- Default\nSEGMENT SPACE MANAGEMENT AUTO    -- ASSM (Default)\nLOGGING;                         -- Default\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">TEMP Tablespace<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TEMPORARY TABLESPACE temp2\nTEMPFILE '\/u01\/oradata\/ORCL\/temp02.dbf' SIZE 2G\nAUTOEXTEND ON NEXT 100M MAXSIZE 10G;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">UNDO Tablespace<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE UNDO TABLESPACE undotbs2\nDATAFILE '\/u01\/oradata\/ORCL\/undotbs02.dbf' SIZE 1G\nAUTOEXTEND ON NEXT 100M MAXSIZE 5G;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Bigfile Tablespace<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE BIGFILE TABLESPACE big_data\nDATAFILE '\/u01\/oradata\/ORCL\/big_data01.dbf' SIZE 10G\nAUTOEXTEND ON NEXT 1G MAXSIZE 32T; -- Can go up to 32TB\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Bigfile vs Smallfile Tablespaces<\/h2>\n\n\n\n<figure class=\"wp-block-table has-small-font-size\"><table><thead><tr><th>Feature<\/th><th>Smallfile (Default)<\/th><th>Bigfile<\/th><\/tr><\/thead><tbody><tr><td>Max datafiles per tablespace<\/td><td>1022<\/td><td>1<\/td><\/tr><tr><td>Max datafile size (8KB block)<\/td><td>32GB<\/td><td>32TB<\/td><\/tr><tr><td>Use case<\/td><td>Multiple files for I\/O<\/td><td>Single large file, ASM<\/td><\/tr><tr><td>Advantages<\/td><td>Better I\/O distribution<\/td><td>Simpler management<\/td><\/tr><tr><td>Disadvantages<\/td><td>Many files to manage<\/td><td>Single point of failure<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Tablespace Operations<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Add Datafile to Tablespace<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLESPACE users \nADD DATAFILE '\/u01\/oradata\/ORCL\/users02.dbf' SIZE 1G;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Resize Datafile<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE DATAFILE '\/u01\/oradata\/ORCL\/users01.dbf' RESIZE 5G;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Drop Tablespace (Including Datafiles)<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>DROP TABLESPACE old_data INCLUDING CONTENTS AND DATAFILES;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Rename Tablespace (12c+)<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLESPACE old_name RENAME TO new_name;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Set Default Tablespace for User<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER USER scott DEFAULT TABLESPACE app_data;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Set Default Temporary Tablespace<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Coalesce Free Space<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><em>(For dictionary-managed tablespaces only; not needed for locally-managed)<\/em><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLESPACE users COALESCE;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Monitoring Tablespaces<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Tablespace Size and Max Capacity<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT tablespace_name, \n       ROUND(SUM(bytes)\/1024\/1024\/1024, 2) total_gb,\n       ROUND(SUM(CASE WHEN autoextensible = 'YES' \n                     THEN maxbytes \n                     ELSE bytes END)\/1024\/1024\/1024, 2) max_gb\nFROM dba_data_files\nGROUP BY tablespace_name\nUNION ALL\nSELECT tablespace_name,\n       ROUND(SUM(bytes)\/1024\/1024\/1024, 2) total_gb,\n       ROUND(SUM(CASE WHEN autoextensible = 'YES' \n                     THEN maxbytes \n                     ELSE bytes END)\/1024\/1024\/1024, 2) max_gb\nFROM dba_temp_files\nGROUP BY tablespace_name;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Tablespace Free Space<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT tablespace_name, \n       ROUND(SUM(bytes)\/1024\/1024, 2) free_mb\nFROM dba_free_space\nGROUP BY tablespace_name;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Tablespace Usage Percentage<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT a.tablespace_name,\n       ROUND(((a.total - NVL(b.free, 0)) \/ a.total) * 100, 2) used_pct\nFROM (SELECT tablespace_name, SUM(bytes) total\n      FROM dba_data_files \n      GROUP BY tablespace_name) a,\n     (SELECT tablespace_name, SUM(bytes) free\n      FROM dba_free_space \n      GROUP BY tablespace_name) b\nWHERE a.tablespace_name = b.tablespace_name(+);\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Check Tablespace Attributes<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT tablespace_name, block_size, status, contents,\n       logging, extent_management, allocation_type,\n       segment_space_management, bigfile\nFROM dba_tablespaces;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">TEMP Tablespace Usage<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT tablespace_name, \n       tablespace_size\/1024\/1024 size_mb,\n       allocated_space\/1024\/1024 allocated_mb,\n       free_space\/1024\/1024 free_mb\nFROM dba_temp_free_space;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Identify Sessions Using TEMP<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT s.username, s.sid, s.serial#, s.sql_id,\n       t.tablespace, t.blocks, \n       t.blocks * 8192 \/ 1024 \/ 1024 mb_used\nFROM v$tempseg_usage t, v$session s\nWHERE t.session_addr = s.saddr\nORDER BY t.blocks DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">INTERVIEW QUESTIONS<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Q1: What&#8217;s the difference between extent and segment?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Extent<\/strong>: Contiguous set of data blocks (storage allocation unit)<\/li>\n\n\n\n<li><strong>Segment<\/strong>: Set of extents that stores one database object (table, index)\n<ul class=\"wp-block-list\">\n<li>One segment can consist of multiple extents<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q2: Can you change <code>DB_BLOCK_SIZE<\/code> after database creation?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>No. <code>DB_BLOCK_SIZE<\/code> is fixed at database creation and <strong>cannot be changed<\/strong>.<br>You must recreate the database to change it.<\/p>\n\n\n\n<blockquote class=\"wp-block-quote is-layout-flow wp-block-quote-is-layout-flow\">\n<p class=\"wp-block-paragraph\">Note: You <em>can<\/em> create tablespaces with <strong>non-standard block sizes<\/strong>.<\/p>\n<\/blockquote>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q3: What&#8217;s ASSM vs MSSM?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>MSSM (Manual Segment Space Management)<\/strong>\n<ul class=\"wp-block-list\">\n<li>Uses freelists to track free blocks<\/li>\n\n\n\n<li>Requires tuning (<code>FREELISTS<\/code>, <code>FREELIST GROUPS<\/code>)<\/li>\n\n\n\n<li>Old method<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>ASSM (Automatic Segment Space Management)<\/strong>\n<ul class=\"wp-block-list\">\n<li>Uses bitmaps to track block usage<\/li>\n\n\n\n<li>Self-tuning and better concurrency<\/li>\n\n\n\n<li>Default from 10g onward<\/li>\n\n\n\n<li><strong>Always use ASSM in modern databases<\/strong><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q4: Can SYSTEM tablespace be taken offline?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>No (except during incomplete recovery).<br>SYSTEM contains the data dictionary and must always remain online for the database to function.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q5: What&#8217;s stored in SYSAUX tablespace?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>AWR data<\/li>\n\n\n\n<li>MMON statistics<\/li>\n\n\n\n<li>Automatic Maintenance tasks<\/li>\n\n\n\n<li>Oracle Text \/ Spatial \/ Multimedia metadata<\/li>\n\n\n\n<li>STATSPACK data<\/li>\n\n\n\n<li><code>DBMS_SCHEDULER<\/code> metadata<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">SYSAUX is auxiliary to SYSTEM.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q6: Difference between UNDO and TEMP tablespace?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>UNDO<\/strong>\n<ul class=\"wp-block-list\">\n<li>Stores before-image data<\/li>\n\n\n\n<li>Used for rollback, read consistency, flashback<\/li>\n\n\n\n<li>Data persists until transaction commits or rolls back<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>TEMP<\/strong>\n<ul class=\"wp-block-list\">\n<li>Stores temporary segments for sorting and joins<\/li>\n\n\n\n<li>Automatically dropped after the operation<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q7: Can you have multiple UNDO tablespaces?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>Yes. Multiple UNDO tablespaces can exist, but <strong>only one is active per instance<\/strong>.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SET undo_tablespace=undotbs2;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Useful during maintenance or in RAC (different instances use different UNDO tablespaces).<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q8: What happens if TEMP tablespace fills up?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>Queries fail with:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ORA-01652: unable to extend temp segment\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Solutions:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Add more tempfiles<\/li>\n\n\n\n<li>Resize existing tempfiles<\/li>\n\n\n\n<li>Identify and tune SQL using excessive TEMP<\/li>\n\n\n\n<li>Clean up temporary segments (usually automatic)<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q9: When to use READ ONLY tablespace?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>For static or archive data, such as:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Historical data (closed fiscal years)<\/li>\n\n\n\n<li>Reference data<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\">Benefits:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Can skip in incremental backups<\/li>\n\n\n\n<li>Prevents accidental modifications<\/li>\n\n\n\n<li>Can be placed on read-only storage<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q10: What&#8217;s a Bigfile tablespace?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>A tablespace with <strong>one datafile<\/strong> that can grow up to <strong>32TB<\/strong> (with 8KB blocks).<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Comparison:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Smallfile tablespace<\/strong>:\n<ul class=\"wp-block-list\">\n<li>Up to 1022 datafiles<\/li>\n\n\n\n<li>Each datafile up to 32GB<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Bigfile tablespace<\/strong>:\n<ul class=\"wp-block-list\">\n<li>Single datafile<\/li>\n\n\n\n<li>Simplifies management<\/li>\n\n\n\n<li>Creates a single point of failure<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q11: Can you drop SYSTEM or SYSAUX tablespace?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>No. Both <strong>SYSTEM<\/strong> and <strong>SYSAUX<\/strong> tablespaces are mandatory.<br>They are created during database creation and <strong>must always exist<\/strong>.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q12: How to find which tablespace is filling up?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT tablespace_name,\n       ROUND(used_percent, 2) used_pct\nFROM dba_tablespace_usage_metrics\nWHERE used_percent &gt; 80\nORDER BY used_percent DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q13: Difference between <code>DROP TABLESPACE<\/code> and <code>DROP TABLESPACE INCLUDING CONTENTS<\/code>?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>DROP TABLESPACE<\/strong>\n<ul class=\"wp-block-list\">\n<li>Fails if the tablespace contains any objects<\/li>\n\n\n\n<li>Only drops an empty tablespace<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>DROP TABLESPACE INCLUDING CONTENTS<\/strong>\n<ul class=\"wp-block-list\">\n<li>Drops the tablespace and all objects inside it<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>DROP TABLESPACE INCLUDING CONTENTS AND DATAFILES<\/strong>\n<ul class=\"wp-block-list\">\n<li>Drops the tablespace<\/li>\n\n\n\n<li>Deletes all objects<\/li>\n\n\n\n<li>Deletes physical datafiles from the OS<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q14: What&#8217;s a Temporary Tablespace Group?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>A <strong>Temporary Tablespace Group<\/strong> is a group of TEMP tablespaces assigned to users.<br>Oracle automatically distributes TEMP usage across the group for better performance.<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>CREATE TEMPORARY TABLESPACE temp2 \nTEMPFILE '...' SIZE 1G \nTABLESPACE GROUP temp_group;\n\nALTER USER scott TEMPORARY TABLESPACE temp_group;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q15: How to move a table from one tablespace to another?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Method 1: <code>ALTER TABLE MOVE<\/code><\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLE scott.emp MOVE TABLESPACE new_tbs;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Notes:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Locks the table<\/li>\n\n\n\n<li>Invalidates indexes<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Rebuild indexes after move\nALTER INDEX scott.emp_pk REBUILD ONLINE;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h4 class=\"wp-block-heading\">Method 2: <code>DBMS_REDEFINITION<\/code><\/h4>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Online method<\/li>\n\n\n\n<li>No table lock<\/li>\n\n\n\n<li>Allows concurrent access<\/li>\n\n\n\n<li>More complex than <code>ALTER TABLE MOVE<\/code><\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">5. STARTUP \/ SHUTDOWN SEQUENCE<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Database Startup Stages<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SHUTDOWN \u2192 NOMOUNT \u2192 MOUNT \u2192 OPEN\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Stage 1: NOMOUNT<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Command<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>STARTUP NOMOUNT;\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">What happens<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Reads parameter file (<strong>SPFILE<\/strong> or <strong>PFILE<\/strong>)<\/li>\n\n\n\n<li>Allocates <strong>SGA<\/strong> memory<\/li>\n\n\n\n<li>Starts background processes<br>(SMON, PMON, DBWn, LGWR, CKPT, etc.)<\/li>\n\n\n\n<li><strong>Does NOT<\/strong> open control files or datafiles<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\">Instance State<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Instance is started<\/li>\n\n\n\n<li>No database access<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">When to use<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Database creation <code>CREATE DATABASE;<\/code><\/li>\n\n\n\n<li>Control file recreation <code>CREATE CONTROLFILE;<\/code><\/li>\n\n\n\n<li>Certain recovery scenarios<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Check status<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT status FROM v$instance;\n-- Returns: STARTED\n\nSELECT name FROM v$database;\n-- Error: database not mounted\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Stage 2: MOUNT<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Command<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>STARTUP MOUNT;\n-- OR (if already NOMOUNT)\nALTER DATABASE MOUNT;\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">What happens<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Opens and reads control file(s)<\/li>\n\n\n\n<li>Identifies datafile and redo log locations<\/li>\n\n\n\n<li><strong>Does NOT<\/strong> open datafiles or redo logs<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\">Database State<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Database is mounted<\/li>\n\n\n\n<li>Not accessible to users<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">When to use<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Database recovery<\/li>\n\n\n\n<li>Enable\/disable ARCHIVELOG mode<\/li>\n\n\n\n<li>Renaming datafiles<\/li>\n\n\n\n<li>Certain backup operations<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Operations allowed in MOUNT stage<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Enable ARCHIVELOG<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE ARCHIVELOG;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Rename datafile<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE RENAME FILE\n'\/old\/path\/users01.dbf'\nTO\n'\/new\/path\/users01.dbf';\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Recover database<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>RECOVER DATABASE;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Create standby control file<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE CREATE STANDBY CONTROLFILE\nAS '\/backup\/standby.ctl';\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Check status<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT status FROM v$instance;\n-- Returns: MOUNTED\n\nSELECT name, open_mode FROM v$database;\n-- name: ORCL\n-- open_mode: MOUNTED\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Stage 3: OPEN<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Command<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>STARTUP;           -- Goes through all stages\nSTARTUP OPEN;\n\n-- OR (if already MOUNTED)\nALTER DATABASE OPEN;\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">What happens<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Opens all online datafiles<\/li>\n\n\n\n<li>Opens redo log files<\/li>\n\n\n\n<li><strong>SMON<\/strong> performs instance recovery if required:\n<ul class=\"wp-block-list\">\n<li>Roll forward (apply redo)<\/li>\n\n\n\n<li>Roll back uncommitted transactions<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Database becomes available for normal operations<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\">Database State<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Database is open<\/li>\n\n\n\n<li>Users can connect and access data<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Open Modes<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">a) READ WRITE (Default)<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE OPEN;\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Full access<\/li>\n\n\n\n<li>DML and DDL allowed<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">b) READ ONLY<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE OPEN READ ONLY;\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Only <code>SELECT<\/code> allowed<\/li>\n\n\n\n<li>No DML \/ DDL<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Used for:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Reporting databases<\/li>\n\n\n\n<li>Testing<\/li>\n\n\n\n<li>Safety checks before changes<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">c) UPGRADE \/ MIGRATE<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>STARTUP UPGRADE;\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Used during database upgrades<\/li>\n\n\n\n<li>Limited operations allowed<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Check status<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT status FROM v$instance;\n-- Returns: OPEN\n\nSELECT name, open_mode FROM v$database;\n-- name: ORCL\n-- open_mode: READ WRITE\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Database Shutdown Options<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>NORMAL \u2192 TRANSACTIONAL \u2192 IMMEDIATE \u2192 ABORT\n(Cleanest)                         (Fastest, requires recovery)\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">1. SHUTDOWN NORMAL<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>(Cleanest, Slowest)<\/strong><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Command<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SHUTDOWN NORMAL;\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">What happens<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Waits for all connected users to disconnect<br>(No new connections allowed)<\/li>\n\n\n\n<li>Waits for all transactions to complete<\/li>\n\n\n\n<li>Performs checkpoint<br>(Writes all dirty buffers to disk)<\/li>\n\n\n\n<li>Closes datafiles and redo logs<\/li>\n\n\n\n<li>Dismounts database<\/li>\n\n\n\n<li>Shuts down instance<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\">Characteristics<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>No data loss<\/li>\n\n\n\n<li>No instance recovery required on next startup<\/li>\n\n\n\n<li>Can take very long if users don\u2019t disconnect<\/li>\n\n\n\n<li>Rarely used in production<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Use when<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Planned shutdown with advance notice to all users<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">2. SHUTDOWN TRANSACTIONAL<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Command<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SHUTDOWN TRANSACTIONAL;\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">What happens<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Prevents new transactions and connections<\/li>\n\n\n\n<li>Waits for active transactions to complete<br>(Commit or rollback)<\/li>\n\n\n\n<li>Disconnects users after transactions finish<\/li>\n\n\n\n<li>Performs checkpoint<\/li>\n\n\n\n<li>Closes datafiles and redo logs<\/li>\n\n\n\n<li>Shuts down instance<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\">Characteristics<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>No data loss<\/li>\n\n\n\n<li>No instance recovery required<\/li>\n\n\n\n<li>Faster than NORMAL<br>(Does not wait for idle sessions)<\/li>\n\n\n\n<li>Allows users to finish current work<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Use when<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Planned shutdown where users need time to complete transactions<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">3. SHUTDOWN IMMEDIATE<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>\u2705 Recommended for most cases<\/strong><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Command<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SHUTDOWN IMMEDIATE;\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">What happens<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Rolls back uncommitted transactions<\/li>\n\n\n\n<li>Disconnects all users immediately<\/li>\n\n\n\n<li>Performs checkpoint<\/li>\n\n\n\n<li>Closes datafiles and redo logs<\/li>\n\n\n\n<li>Shuts down instance<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\">Characteristics<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>No data loss<br>(Uncommitted work is rolled back)<\/li>\n\n\n\n<li>No instance recovery required on next startup<\/li>\n\n\n\n<li>Fast (seconds to minutes)<\/li>\n\n\n\n<li>Most commonly used in production<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Use when<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Standard planned shutdown<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">4. SHUTDOWN ABORT<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>\u26a0\ufe0f Emergency only<\/strong><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Command<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SHUTDOWN ABORT;\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">What happens<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Immediately terminates the instance<\/li>\n\n\n\n<li>No checkpoint performed<\/li>\n\n\n\n<li>No rollback of transactions<\/li>\n\n\n\n<li>Disconnects users instantly<\/li>\n\n\n\n<li>Equivalent to pulling the power plug<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\">Characteristics<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Fastest (instant)<\/li>\n\n\n\n<li>Instance recovery required on next startup<\/li>\n\n\n\n<li>Dirty buffers are not written to disk<\/li>\n\n\n\n<li>Use only when:\n<ul class=\"wp-block-list\">\n<li>Database is hung<\/li>\n\n\n\n<li>Emergency situations<\/li>\n\n\n\n<li>Other shutdown methods fail<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Next startup<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>STARTUP;\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SMON automatically performs instance recovery<\/li>\n\n\n\n<li>Rolls forward using redo logs<\/li>\n\n\n\n<li>Rolls back uncommitted transactions<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Use when<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Database is hung<\/li>\n\n\n\n<li>Emergency situations<\/li>\n\n\n\n<li>All other shutdown methods are not working<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Shutdown Comparison Table<\/h2>\n\n\n\n<figure class=\"wp-block-table has-small-font-size\"><table><thead><tr><th>Feature<\/th><th>NORMAL<\/th><th>TRANSACTIONAL<\/th><th>IMMEDIATE \u2705<\/th><th>ABORT \u26a0\ufe0f<\/th><\/tr><\/thead><tbody><tr><td>Waits for users<\/td><td>Yes<\/td><td>No<\/td><td>No<\/td><td>No<\/td><\/tr><tr><td>Waits for transactions<\/td><td>Yes<\/td><td>Yes<\/td><td>No<\/td><td>No<\/td><\/tr><tr><td>Rolls back active transactions<\/td><td>N\/A<\/td><td>N\/A<\/td><td>Yes<\/td><td>No<\/td><\/tr><tr><td>Performs checkpoint<\/td><td>Yes<\/td><td>Yes<\/td><td>Yes<\/td><td>No<\/td><\/tr><tr><td>Instance recovery needed<\/td><td>No<\/td><td>No<\/td><td>No<\/td><td>Yes<\/td><\/tr><tr><td>Speed<\/td><td>Slowest<\/td><td>Slow<\/td><td>Fast<\/td><td>Instant<\/td><\/tr><tr><td>Data loss<\/td><td>No<\/td><td>No<\/td><td>No<\/td><td>No<\/td><\/tr><tr><td>Use in production<\/td><td>Rare<\/td><td>Sometimes<\/td><td><strong>Standard<\/strong><\/td><td>Emergency only<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">INTERVIEW QUESTIONS<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q1: What are the stages of database startup?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>Three stages:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>NOMOUNT<\/strong>\n<ul class=\"wp-block-list\">\n<li>Instance started<\/li>\n\n\n\n<li>Memory allocated<\/li>\n\n\n\n<li>Background processes started<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>MOUNT<\/strong>\n<ul class=\"wp-block-list\">\n<li>Control file opened<\/li>\n\n\n\n<li>Datafile and redo log locations identified<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>OPEN<\/strong>\n<ul class=\"wp-block-list\">\n<li>Datafiles and redo logs opened<\/li>\n\n\n\n<li>Database accessible to users<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<pre class=\"wp-block-code\"><code>STARTUP;  -- Goes through all stages automatically\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q2: When would you use <code>STARTUP NOMOUNT<\/code>?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Creating a new database <code>CREATE DATABASE;<\/code><\/li>\n\n\n\n<li>Recreating control file<\/li>\n\n\n\n<li>Certain RMAN restore operations<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q3: What&#8217;s the difference between MOUNT and OPEN?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>MOUNT<\/strong>\n<ul class=\"wp-block-list\">\n<li>Control file is read<\/li>\n\n\n\n<li>Oracle knows file locations<\/li>\n\n\n\n<li>Files are not opened<\/li>\n\n\n\n<li>Database not accessible to users<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>OPEN<\/strong>\n<ul class=\"wp-block-list\">\n<li>Datafiles and redo logs opened<\/li>\n\n\n\n<li>SMON performs recovery if needed<\/li>\n\n\n\n<li>Users can connect<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q4: Can you rename a datafile when database is OPEN?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>No. Tablespace must be offline <strong>or<\/strong> database must be in <strong>MOUNT<\/strong> stage.<\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Method 1: Offline tablespace<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLESPACE users OFFLINE;\n-- Move file at OS level\nALTER TABLESPACE users\nRENAME DATAFILE '\/old\/path' TO '\/new\/path';\nALTER TABLESPACE users ONLINE;\n<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Method 2: MOUNT stage<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>SHUTDOWN IMMEDIATE;\nSTARTUP MOUNT;\nALTER DATABASE RENAME FILE '\/old\/path' TO '\/new\/path';\nALTER DATABASE OPEN;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q5: What&#8217;s the difference between <code>SHUTDOWN IMMEDIATE<\/code> and <code>SHUTDOWN ABORT<\/code>?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>IMMEDIATE<\/strong>\n<ul class=\"wp-block-list\">\n<li>Rolls back uncommitted transactions<\/li>\n\n\n\n<li>Performs checkpoint<\/li>\n\n\n\n<li>Clean shutdown<\/li>\n\n\n\n<li>No recovery needed<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>ABORT<\/strong>\n<ul class=\"wp-block-list\">\n<li>Instant termination<\/li>\n\n\n\n<li>No checkpoint<\/li>\n\n\n\n<li>No rollback<\/li>\n\n\n\n<li>Instance recovery required on next startup<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q6: Which SHUTDOWN option is recommended for planned maintenance?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br><code>SHUTDOWN IMMEDIATE<\/code><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Fast<\/li>\n\n\n\n<li>Clean (no recovery needed)<\/li>\n\n\n\n<li>Properly rolls back uncommitted work<\/li>\n\n\n\n<li>Used in ~99% of planned shutdowns<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q7: Database is hung and won&#8217;t respond. What do you do?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Try IMMEDIATE first\nSHUTDOWN IMMEDIATE;\n\n-- If no response after 5 minutes\nSHUTDOWN ABORT;\n\n-- Then startup\nSTARTUP;\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SMON performs automatic instance recovery<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q8: After <code>SHUTDOWN ABORT<\/code>, do I lose data?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>No data loss for <strong>committed transactions<\/strong>.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Redo logs contain committed changes<\/li>\n\n\n\n<li>SMON applies redo during recovery<\/li>\n\n\n\n<li>Only uncommitted transactions are lost (expected behavior)<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q9: What happens during instance recovery?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>SMON automatically performs:<\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Roll Forward (Cache Recovery)<\/strong>\n<ul class=\"wp-block-list\">\n<li>Applies redo logs<\/li>\n\n\n\n<li>Brings datafiles to a consistent state<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Roll Back (Transaction Recovery)<\/strong>\n<ul class=\"wp-block-list\">\n<li>Uses UNDO<\/li>\n\n\n\n<li>Rolls back uncommitted transactions<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<p class=\"wp-block-paragraph\">No DBA intervention required.<\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q10: Can you open database in READ ONLY mode for upgrades?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>No. For upgrades use:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>STARTUP UPGRADE;\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Special mode for running upgrade scripts<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q11: How to check why database took long to startup?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>Check <strong>alert log<\/strong> for instance recovery details:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>grep -i \"recovery\" alert_orcl.log\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\">Look for:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Recovery start\/end timestamps<\/li>\n\n\n\n<li>Number of redo blocks applied<\/li>\n\n\n\n<li>Media recovery details<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q12: What&#8217;s the command to change from NOMOUNT to MOUNT to OPEN?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><\/p>\n\n\n\n<h4 class=\"wp-block-heading\">Method 1: Step by step<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>STARTUP NOMOUNT;\nALTER DATABASE MOUNT;\nALTER DATABASE OPEN;\n<\/code><\/pre>\n\n\n\n<h4 class=\"wp-block-heading\">Method 2: Direct<\/h4>\n\n\n\n<pre class=\"wp-block-code\"><code>STARTUP;  -- Goes through all stages automatically\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">6. INSTANCE RECOVERY<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>(Automatic Crash Recovery)<\/strong><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">What is Instance Recovery?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Scenario:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Database crashes due to:\n<ul class=\"wp-block-list\">\n<li>Power failure<\/li>\n\n\n\n<li><code>SHUTDOWN ABORT<\/code><\/li>\n\n\n\n<li>Background \/ server process killed<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>At the moment of crash:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Dirty buffers exist in buffer cache (not written to disk)<\/li>\n\n\n\n<li>Some committed transactions may still be in redo log buffer<\/li>\n\n\n\n<li>Online redo logs contain:\n<ul class=\"wp-block-list\">\n<li>Committed changes<\/li>\n\n\n\n<li>Uncommitted changes<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li>Datafiles are <strong>inconsistent<\/strong><\/li>\n<\/ul>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Recovery is needed to:<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li>Bring database to a consistent state<\/li>\n\n\n\n<li>Ensure all committed transactions are persisted<\/li>\n\n\n\n<li>Roll back uncommitted transactions<\/li>\n<\/ol>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Two Phases of Instance Recovery<\/h2>\n\n\n\n<pre class=\"wp-block-code\"><code>STARTUP after crash\n        \u2193\n   SMON starts recovery\n        \u2193\nPhase 1: ROLL FORWARD (Cache Recovery)\n        \u2193\nPhase 2: ROLL BACK (Transaction Recovery)\n        \u2193\n     Database OPEN\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Phase 1: ROLL FORWARD<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>(Cache Recovery)<\/strong><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Apply all changes from redo logs to datafiles<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">What happens<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>SMON reads online redo logs starting from the <strong>last checkpoint SCN<\/strong><\/li>\n\n\n\n<li>Applies <strong>all changes<\/strong> (committed and uncommitted)<\/li>\n\n\n\n<li>Brings datafiles to the state they were in at crash time<\/li>\n\n\n\n<li>Recreates the buffer cache state<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\">Why apply uncommitted transactions?<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Oracle cannot determine committed vs uncommitted transactions without reading redo<\/li>\n\n\n\n<li>Applying everything first is faster<\/li>\n\n\n\n<li>Uncommitted transactions are handled in rollback phase<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Timeline<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>Last Checkpoint \u2192 Crash\n       \u2193            \u2193\n     SCN 100      SCN 150\n       |------------|\n        Redo logs applied\n<\/code><\/pre>\n\n\n\n<h3 class=\"wp-block-heading\">Example<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Before crash:\nTransaction A:\nUPDATE emp SET sal=5000 WHERE id=1;\nCOMMIT;                -- SCN 110\n\nTransaction B:\nUPDATE emp SET sal=6000 WHERE id=2;\n-- NOT committed       -- SCN 120\n\n-- Database crashes at SCN 150\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Roll forward phase result:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SMON applies <strong>both<\/strong> changes from redo logs\n<ul class=\"wp-block-list\">\n<li>emp(id=1): sal=5000<\/li>\n\n\n\n<li>emp(id=2): sal=6000<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Phase 2: ROLL BACK<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>(Transaction Recovery)<\/strong><\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Purpose<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Undo all uncommitted transactions<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">What happens<\/h3>\n\n\n\n<ol class=\"wp-block-list\">\n<li>SMON identifies uncommitted transactions using <strong>UNDO segments<\/strong><\/li>\n\n\n\n<li>Rolls back these transactions using UNDO data<\/li>\n\n\n\n<li>Database reaches a consistent state<\/li>\n\n\n\n<li>Database becomes available for users<\/li>\n<\/ol>\n\n\n\n<h3 class=\"wp-block-heading\">Rollback timing<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\">Rollback can occur:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Before database OPEN<\/strong> (faster startup)<\/li>\n\n\n\n<li><strong>After database OPEN<\/strong> (background rollback by SMON)<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">19c Improvement<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Fast-Start Recovery<\/strong> limits recovery time<\/li>\n\n\n\n<li>Improves startup performance after crash<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Example (continued)<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>After roll forward:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>emp(id=1): sal = 5000 \u2713\nemp(id=2): sal = 6000 \u2717 (uncommitted)\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Roll back phase:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>SMON uses UNDO to reverse Transaction B<\/li>\n<\/ul>\n\n\n\n<pre class=\"wp-block-code\"><code>emp(id=2): sal = original_value\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Final state:<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>emp(id=1): sal = 5000 \u2713  (Committed)\nemp(id=2): sal = original_value \u2713  (Rolled back)\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Monitoring Instance Recovery<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">During recovery<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Check recovery progress<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$recovery_progress;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Monitor alert log<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>tail -f alert_orcl.log\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Sample Alert Log Entries<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>Beginning crash recovery of 1 threads\nStarted redo scan\nCompleted redo scan\n read 2500 KB redo, 145 data blocks need recovery\nStarted recovery at\n Thread 1: logseq 52, block 2, scn 987654\nRecovery of Online Redo Log: Thread 1 Group 2 Seq 52 Reading mem 0\nCompleted redo application of 1.23MB\nCompleted crash recovery at\n Thread 1: logseq 52, block 2150, scn 987799\n145 data blocks read, 145 data blocks written, 2500 KB redo read\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Tuning Recovery Time<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">Problem<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Long recovery time = long downtime after crash<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Solution<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>FAST_START_MTTR_TARGET<\/code> parameter<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Example<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>-- Target recovery time: 5 minutes (300 seconds)\nALTER SYSTEM SET fast_start_mttr_target=300 SCOPE=BOTH;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">How it works<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Oracle automatically tunes checkpoint frequency<\/li>\n\n\n\n<li>More frequent checkpoints \u21d2 less redo to apply \u21d2 faster recovery<\/li>\n\n\n\n<li>Trade-off:\n<ul class=\"wp-block-list\">\n<li>More checkpoints<\/li>\n\n\n\n<li>Slightly higher overhead during normal operation<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Default<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>0<\/code> (disabled)<\/li>\n\n\n\n<li>Oracle uses internal calculation<\/li>\n<\/ul>\n\n\n\n<h3 class=\"wp-block-heading\">Recommended<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li><code>60\u2013300<\/code> seconds for production databases<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Check estimated recovery time<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT estimated_mttr FROM v$instance_recovery;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Parameters Affecting Recovery<\/h2>\n\n\n\n<figure class=\"wp-block-table has-small-font-size\"><table><thead><tr><th>Parameter<\/th><th>Purpose<\/th><th>Recommendation<\/th><\/tr><\/thead><tbody><tr><td>FAST_START_MTTR_TARGET<\/td><td>Target recovery time (seconds)<\/td><td>300 (5 min) for OLTP<\/td><\/tr><tr><td>LOG_CHECKPOINT_INTERVAL<\/td><td>Checkpoint every N OS blocks<\/td><td>Let Oracle manage<\/td><\/tr><tr><td>LOG_CHECKPOINT_TIMEOUT<\/td><td>Checkpoint every N seconds<\/td><td>Let Oracle manage<\/td><\/tr><tr><td>DB_BLOCK_CHECKSUM<\/td><td>Verify block corruption<\/td><td>TYPICAL (default)<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Modern Oracle (19c) Best Practice<\/h3>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Set <strong>FAST_START_MTTR_TARGET<\/strong><\/li>\n\n\n\n<li>Let Oracle automatically manage all other recovery-related parameters<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">INTERVIEW QUESTIONS<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q1: What is instance recovery?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>Automatic recovery performed by <strong>SMON<\/strong> when the database starts after a crash.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Two phases:<\/strong><\/p>\n\n\n\n<ol class=\"wp-block-list\">\n<li><strong>Roll Forward<\/strong>\n<ul class=\"wp-block-list\">\n<li>Applies redo logs<\/li>\n\n\n\n<li>Brings datafiles to crash-time state<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Roll Back<\/strong>\n<ul class=\"wp-block-list\">\n<li>Undoes uncommitted transactions using UNDO<\/li>\n<\/ul>\n<\/li>\n<\/ol>\n\n\n\n<ul class=\"wp-block-list\">\n<li>No DBA intervention required<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q2: What&#8217;s the difference between roll forward and roll back?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Roll Forward<\/strong>\n<ul class=\"wp-block-list\">\n<li>Applies changes from redo logs<\/li>\n\n\n\n<li>Includes committed and uncommitted transactions<\/li>\n\n\n\n<li>Brings datafiles to a consistent crash state<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Roll Back<\/strong>\n<ul class=\"wp-block-list\">\n<li>Undoes uncommitted transactions<\/li>\n\n\n\n<li>Uses UNDO segments<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q3: Why does Oracle apply uncommitted transactions during roll forward?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>Redo logs do not distinguish between committed and uncommitted changes at the physical level.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Faster to apply all redo<\/li>\n\n\n\n<li>Uncommitted transactions are later rolled back using UNDO<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q4: Can users access database during roll back phase?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>Yes, with <strong>fast-start recovery<\/strong>.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Oracle opens the database after critical roll forward<\/li>\n\n\n\n<li>Roll back continues in the background<\/li>\n\n\n\n<li>Users can access data not involved in uncommitted transactions<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q5: How to reduce instance recovery time?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>Set <code>FAST_START_MTTR_TARGET<\/code> to the desired recovery time (seconds).<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM SET fast_start_mttr_target=300;  -- 5 minutes\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Oracle increases checkpoint frequency to meet this target<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q6: What if both roll forward and roll back fail?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>The database will not open. Check the alert log.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Possible causes:<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Corrupted redo logs \u2192 Media recovery required<\/li>\n\n\n\n<li>Corrupted UNDO segments \u2192 May need incomplete recovery<\/li>\n\n\n\n<li>Missing datafiles \u2192 Restore from backup<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q7: Does instance recovery cause data loss?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>No.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>All committed transactions are preserved (stored in redo logs)<\/li>\n\n\n\n<li>Only uncommitted transactions are lost (expected behavior)<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q8: What&#8217;s the difference between instance recovery and media recovery?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li><strong>Instance Recovery<\/strong>\n<ul class=\"wp-block-list\">\n<li>Automatic<\/li>\n\n\n\n<li>Handles crashes \/ <code>SHUTDOWN ABORT<\/code><\/li>\n\n\n\n<li>Uses online redo logs<\/li>\n<\/ul>\n<\/li>\n\n\n\n<li><strong>Media Recovery<\/strong>\n<ul class=\"wp-block-list\">\n<li>DBA-initiated<\/li>\n\n\n\n<li>Handles disk failures or file corruption<\/li>\n\n\n\n<li>Uses backups and archived redo logs<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q9: Where can I see how long recovery took?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>Check the <strong>alert log<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Example:<\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>Completed crash recovery at\n Thread 1: logseq 52, block 2150, scn 987799\n145 data blocks read, 145 data blocks written, 2500 KB redo read\n<\/code><\/pre>\n\n\n\n<ul class=\"wp-block-list\">\n<li>Calculate time between:\n<ul class=\"wp-block-list\">\n<li><strong>Beginning crash recovery<\/strong><\/li>\n\n\n\n<li><strong>Completed crash recovery<\/strong><\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Q10: If I set <code>FAST_START_MTTR_TARGET = 60<\/code>, is recovery guaranteed in 60 seconds?<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>A:<\/strong><br>No.<\/p>\n\n\n\n<ul class=\"wp-block-list\">\n<li>It is a <strong>target<\/strong>, not a guarantee<\/li>\n\n\n\n<li>Oracle tunes checkpoints to try to meet it<\/li>\n\n\n\n<li>Actual recovery time depends on:\n<ul class=\"wp-block-list\">\n<li>Redo volume<\/li>\n\n\n\n<li>I\/O performance<\/li>\n\n\n\n<li>System load<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">7. KEY VIEWS FOR DBAs \u2014 Quick Reference<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Instance and Memory<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Instance Information<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT instance_name,\n       host_name,\n       version,\n       status,\n       startup_time,\n       database_status\nFROM v$instance;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">SGA Components<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Total SGA<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT name,\n       value\/1024\/1024 mb\nFROM v$sga;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Detailed SGA breakdown<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT pool,\n       name,\n       bytes\/1024\/1024 mb\nFROM v$sgastat\nWHERE bytes &gt; 10485760      -- Show components &gt; 10 MB\nORDER BY bytes DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">PGA Statistics<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT name,\n       value\/1024\/1024 mb\nFROM v$pgastat\nWHERE name IN (\n    'total PGA allocated',\n    'total PGA inuse',\n    'over allocation count'\n);\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Memory Parameters<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT name,\n       value,\n       isdefault,\n       ismodified\nFROM v$parameter\nWHERE name LIKE '%target%'\n   OR name LIKE '%sga%'\n   OR name LIKE '%pga%';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Memory Resize Operations (Last 7 Days)<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT component,\n       oper_type,\n       oper_mode,\n       initial_size\/1024\/1024 initial_mb,\n       target_size\/1024\/1024  target_mb,\n       final_size\/1024\/1024   final_mb,\n       start_time,\n       end_time\nFROM v$sga_resize_ops\nWHERE start_time &gt; SYSDATE - 7\nORDER BY start_time DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Sessions and Processes<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Active Sessions<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT sid,\n       serial#,\n       username,\n       osuser,\n       machine,\n       program,\n       status,\n       sql_id,\n       event,\n       wait_time,\n       seconds_in_wait\nFROM v$session\nWHERE username IS NOT NULL\n  AND status = 'ACTIVE'\nORDER BY seconds_in_wait DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Long-Running Sessions<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT s.sid,\n       s.serial#,\n       s.username,\n       s.sql_id,\n       s.last_call_et\/3600 hours_running,\n       sq.sql_text\nFROM v$session s,\n     v$sql sq\nWHERE s.sql_id = sq.sql_id\n  AND s.status = 'ACTIVE'\n  AND s.last_call_et &gt; 3600    -- Running &gt; 1 hour\nORDER BY s.last_call_et DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Blocking Sessions<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT blocking_session,\n       sid,\n       serial#,\n       wait_class,\n       seconds_in_wait\nFROM v$session\nWHERE blocking_session IS NOT NULL;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Kill Session<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Process Information<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT pid,\n       spid,\n       program,\n       pga_used_mem\/1024\/1024   pga_mb,\n       pga_alloc_mem\/1024\/1024  pga_alloc_mb\nFROM v$process\nORDER BY pga_used_mem DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Session Count by Program<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT program,\n       COUNT(*) session_count\nFROM v$session\nGROUP BY program\nORDER BY COUNT(*) DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">SQL Performance<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Top SQL by Elapsed Time (Last 1 Hour)<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT sql_id,\n       elapsed_time\/1000000 elapsed_sec,\n       executions,\n       elapsed_time\/executions\/1000000 avg_elapsed_sec,\n       buffer_gets,\n       disk_reads,\n       SUBSTR(sql_text, 1, 100) sql_text\nFROM v$sqlarea\nWHERE last_active_time &gt; SYSDATE - 1\/24\nORDER BY elapsed_time DESC\nFETCH FIRST 20 ROWS ONLY;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Top SQL by CPU Time<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT sql_id,\n       cpu_time\/1000000 cpu_sec,\n       executions,\n       buffer_gets,\n       SUBSTR(sql_text, 1, 100) sql_text\nFROM v$sqlarea\nORDER BY cpu_time DESC\nFETCH FIRST 20 ROWS ONLY;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Top SQL by Physical Reads<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT sql_id,\n       disk_reads,\n       executions,\n       disk_reads\/executions avg_reads,\n       SUBSTR(sql_text, 1, 100) sql_text\nFROM v$sqlarea\nWHERE executions &gt; 0\nORDER BY disk_reads DESC\nFETCH FIRST 20 ROWS ONLY;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Full SQL Text<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT sql_fulltext\nFROM v$sqlarea\nWHERE sql_id = 'xxxxxxxxx';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Current Execution Plan<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT *\nFROM TABLE(\n    dbms_xplan.display_cursor(\n        'sql_id',\n        NULL,\n        'ALLSTATS LAST'\n    )\n);\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Wait Events<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Top Wait Events (System-Wide)<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT event,\n       total_waits,\n       time_waited\/100 time_waited_sec,\n       average_wait*10 avg_wait_ms,\n       wait_class\nFROM v$system_event\nWHERE wait_class != 'Idle'\nORDER BY time_waited DESC\nFETCH FIRST 20 ROWS ONLY;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Current Session Waits<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT sid,\n       serial#,\n       username,\n       event,\n       wait_time,\n       seconds_in_wait,\n       state\nFROM v$session\nWHERE wait_class != 'Idle'\n  AND username IS NOT NULL\nORDER BY seconds_in_wait DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Wait Event Histogram<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT event,\n       wait_time_milli,\n       wait_count\nFROM v$event_histogram\nWHERE event = 'log file sync'\nORDER BY wait_time_milli;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Storage<\/h2>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>(Datafiles, Tablespaces)<\/strong><\/p>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Datafile Information<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT file_name,\n       tablespace_name,\n       bytes\/1024\/1024\/1024 size_gb,\n       autoextensible,\n       maxbytes\/1024\/1024\/1024 max_gb,\n       status\nFROM dba_data_files\nORDER BY bytes DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Tablespace Usage<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT tablespace_name,\n       ROUND(used_percent, 2) used_pct,\n       ROUND(used_space * 8192 \/ 1024 \/ 1024 \/ 1024, 2) used_gb,\n       ROUND(tablespace_size * 8192 \/ 1024 \/ 1024 \/ 1024, 2) total_gb\nFROM dba_tablespace_usage_metrics\nORDER BY used_percent DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Free Space by Tablespace<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT tablespace_name,\n       ROUND(SUM(bytes)\/1024\/1024\/1024, 2) free_gb\nFROM dba_free_space\nGROUP BY tablespace_name\nORDER BY SUM(bytes);\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">TEMP Space Usage<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT tablespace_name,\n       ROUND(tablespace_size\/1024\/1024, 2) size_mb,\n       ROUND(allocated_space\/1024\/1024, 2) allocated_mb,\n       ROUND(free_space\/1024\/1024, 2) free_mb\nFROM dba_temp_free_space;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Who Is Using TEMP<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT s.username,\n       s.sid,\n       s.serial#,\n       s.sql_id,\n       t.tablespace,\n       t.blocks * 8192 \/ 1024 \/ 1024 mb_used\nFROM v$tempseg_usage t,\n     v$session s\nWHERE t.session_addr = s.saddr\nORDER BY mb_used DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Top 20 Largest Segments<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT owner,\n       segment_name,\n       segment_type,\n       ROUND(bytes\/1024\/1024\/1024, 2) size_gb,\n       tablespace_name\nFROM dba_segments\nORDER BY bytes DESC\nFETCH FIRST 20 ROWS ONLY;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Redo and Archive Logs<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Online Redo Log Configuration<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT group#,\n       thread#,\n       sequence#,\n       bytes\/1024\/1024 size_mb,\n       members,\n       status,\n       archived\nFROM v$log;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Redo Log Members<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT group#,\n       member,\n       status,\n       type\nFROM v$logfile;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Log Switches per Hour (Last 24 Hours)<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') hour,\n       COUNT(*) switches\nFROM v$log_history\nWHERE first_time &gt; SYSDATE - 1\nGROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')\nORDER BY hour;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Redo Generation Rate (MB\/Hour)<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT TO_CHAR(first_time, 'YYYY-MM-DD HH24') hour,\n       COUNT(*) *\n       (SELECT bytes\/1024\/1024 FROM v$log WHERE ROWNUM = 1) redo_mb\nFROM v$log_history\nWHERE first_time &gt; SYSDATE - 1\nGROUP BY TO_CHAR(first_time, 'YYYY-MM-DD HH24')\nORDER BY hour;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Archive Log Destinations<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT dest_id,\n       status,\n       destination,\n       error\nFROM v$archive_dest\nWHERE status != 'INACTIVE';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Archive Log Generation (Last 7 Days)<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT TO_CHAR(first_time, 'YYYY-MM-DD') day,\n       COUNT(*) archives,\n       ROUND(SUM(blocks * block_size) \/ 1024 \/ 1024 \/ 1024, 2) size_gb\nFROM v$archived_log\nWHERE first_time &gt; SYSDATE - 7\n  AND dest_id = 1\nGROUP BY TO_CHAR(first_time, 'YYYY-MM-DD')\nORDER BY day;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Check for Archive Gaps (Data Guard)<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$archive_gap;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Background Processes<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">All Background Processes<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT paddr,\n       name,\n       description,\n       error_count\nFROM v$bgprocess\nWHERE paddr != '00'\nORDER BY name;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Check Specific Process Status<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT *\nFROM v$bgprocess\nWHERE name IN ('PMON', 'SMON', 'LGWR', 'DBW0', 'CKPT');\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Process Resource Usage<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT p.program,\n       p.spid,\n       p.pga_used_mem\/1024\/1024 pga_mb,\n       s.sid,\n       s.status,\n       s.event\nFROM v$process p,\n     v$session s\nWHERE p.addr = s.paddr(+)\nORDER BY p.pga_used_mem DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Database Configuration<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Database Information<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT dbid,\n       name,\n       db_unique_name,\n       platform_name,\n       log_mode,\n       open_mode,\n       database_role,\n       flashback_on,\n       force_logging\nFROM v$database;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">All Parameters<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT name,\n       value,\n       isdefault,\n       ismodified,\n       description\nFROM v$parameter\nORDER BY name;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Modified Parameters (Non-Default)<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT name,\n       value,\n       ismodified\nFROM v$parameter\nWHERE ismodified != 'FALSE'\nORDER BY name;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Control Files<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT name,\n       status\nFROM v$controlfile;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Features in Use<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT name,\n       currently_used,\n       first_usage_date,\n       last_usage_date\nFROM dba_feature_usage_statistics\nWHERE currently_used = 'TRUE'\nORDER BY last_usage_date DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Performance Monitoring<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">AWR Snapshot Information<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT snap_id,\n       begin_interval_time,\n       end_interval_time\nFROM dba_hist_snapshot\nORDER BY snap_id DESC\nFETCH FIRST 10 ROWS ONLY;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Generate AWR Report<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>@?\/rdbms\/admin\/awrrpt.sql\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">ASH Report<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>@?\/rdbms\/admin\/ashrpt.sql\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Database Time Model<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT stat_name,\n       ROUND(value\/1000000, 2) time_sec\nFROM v$sys_time_model\nORDER BY value DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">System Statistics<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT name,\n       value\nFROM v$sysstat\nWHERE name IN (\n    'user commits',\n    'user rollbacks',\n    'physical reads',\n    'physical writes',\n    'db block gets',\n    'consistent gets',\n    'redo size',\n    'parse count (total)',\n    'parse count (hard)',\n    'execute count'\n)\nORDER BY name;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Hit Ratios<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT\n  'Buffer Cache Hit Ratio' metric,\n  ROUND(\n    (1 - (phy.value \/ (db.value + con.value))) * 100,\n    2\n  ) pct\nFROM v$sysstat phy,\n     v$sysstat db,\n     v$sysstat con\nWHERE phy.name = 'physical reads'\n  AND db.name  = 'db block gets'\n  AND con.name = 'consistent gets'\n\nUNION ALL\n\nSELECT\n  'Library Cache Hit Ratio',\n  ROUND((SUM(pinhits) \/ SUM(pins)) * 100, 2)\nFROM v$librarycache;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Alert Log Queries<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Recent Alerts (Last 24 Hours)<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT originating_timestamp,\n       message_text\nFROM v$diag_alert_ext\nWHERE originating_timestamp &gt; SYSDATE - 1\nORDER BY originating_timestamp DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Errors in Alert Log<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT originating_timestamp,\n       message_text\nFROM v$diag_alert_ext\nWHERE message_text LIKE '%ORA-%'\n  AND originating_timestamp &gt; SYSDATE - 7\nORDER BY originating_timestamp DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Specific Error Search<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT originating_timestamp,\n       message_text\nFROM v$diag_alert_ext\nWHERE message_text LIKE '%ORA-00600%'\nORDER BY originating_timestamp DESC;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">8. QUICK TROUBLESHOOTING GUIDE<\/h2>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Problem: Database Performance Slow<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">1. Check Wait Events<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT event,\n       total_waits,\n       time_waited\/100 sec\nFROM v$system_event\nWHERE wait_class != 'Idle'\nORDER BY time_waited DESC\nFETCH FIRST 10 ROWS ONLY;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">2. Check Top SQL<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT sql_id,\n       elapsed_time\/1000000 elapsed_sec,\n       executions\nFROM v$sqlarea\nORDER BY elapsed_time DESC\nFETCH FIRST 10 ROWS ONLY;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">3. Check Blocking Sessions<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT blocking_session,\n       sid,\n       seconds_in_wait\nFROM v$session\nWHERE blocking_session IS NOT NULL;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">4. Check Resource Usage<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT *\nFROM v$resource_limit\nWHERE current_utilization &gt; 0;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Problem: Database Hanging<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">1. Check Alert Log<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>tail -100 alert_orcl.log\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">2. Check Archive Destination Status<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT dest_id,\n       status,\n       error\nFROM v$archive_dest;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">3. Check for Locks<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM dba_blockers;\nSELECT * FROM dba_waiters;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">4. Check LGWR Status<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT event\nFROM v$session\nWHERE program LIKE '%LGWR%';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">Last Resort<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SHUTDOWN ABORT;\nSTARTUP;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Problem: Tablespace Full<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">1. Check Usage<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT tablespace_name,\n       used_percent\nFROM dba_tablespace_usage_metrics\nWHERE used_percent &gt; 85;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">2. Check Autoextend Status<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT file_name,\n       autoextensible,\n       maxbytes\nFROM dba_data_files\nWHERE tablespace_name = 'USERS';\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">3. Add Datafile or Resize<\/h3>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Add datafile<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER TABLESPACE users\nADD DATAFILE '\/u01\/users02.dbf'\nSIZE 1G AUTOEXTEND ON;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Resize existing datafile<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE DATAFILE '\/u01\/users01.dbf' RESIZE 10G;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Enable autoextend<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>ALTER DATABASE DATAFILE '\/u01\/users01.dbf'\nAUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Problem: High CPU Usage<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">1. Find CPU-Intensive SQL<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT sql_id,\n       cpu_time\/1000000 cpu_sec,\n       executions\nFROM v$sqlarea\nORDER BY cpu_time DESC\nFETCH FIRST 10 ROWS ONLY;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">2. Check Parsing Issues<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT name,\n       value\nFROM v$sysstat\nWHERE name IN (\n    'parse count (total)',\n    'parse count (hard)',\n    'execute count'\n);\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Check for literal SQL (no bind variables)<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT sql_id,\n       sql_text,\n       executions\nFROM v$sqlarea\nWHERE executions = 1\nFETCH FIRST 20 ROWS ONLY;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h2 class=\"wp-block-heading\">Problem: High Memory Usage<\/h2>\n\n\n\n<h3 class=\"wp-block-heading\">1. Check PGA Usage<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT * FROM v$pgastat;\n<\/code><\/pre>\n\n\n\n<p class=\"wp-block-paragraph\"><strong>Sessions using most PGA<\/strong><\/p>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT s.sid,\n       s.username,\n       p.pga_used_mem\/1024\/1024 pga_mb\nFROM v$session s,\n     v$process p\nWHERE s.paddr = p.addr\nORDER BY p.pga_used_mem DESC\nFETCH FIRST 20 ROWS ONLY;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">2. Check SGA Usage<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT *\nFROM v$sgastat\nORDER BY bytes DESC\nFETCH FIRST 20 ROWS ONLY;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<h3 class=\"wp-block-heading\">3. Check for Memory Leaks<\/h3>\n\n\n\n<pre class=\"wp-block-code\"><code>SELECT pool,\n       name,\n       bytes\/1024\/1024 mb\nFROM v$sgastat\nWHERE name LIKE '%free%'\nORDER BY bytes;\n<\/code><\/pre>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity\"\/>\n\n\n\n<p class=\"wp-block-paragraph\">Best of luck with your Oracle DBA interview!<br>I hope this guide has helped strengthen your preparation and confidence.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">Feel free to revisit any section whenever needed, and remember\u2014<strong>understanding the <em>why<\/em> behind Oracle\u2019s architecture is what truly sets great DBAs apart<\/strong>.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">You\u2019ve got this! \ud83d\ude80<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Introduction Preparing for an Oracle DBA interview?Interviewers often focus on database architecture fundamentals and real production scenarios rather than just textbook definitions. This comprehensive guide covers Oracle Database Architecture interview questions that truly matter \u2014 how Oracle actually works under the hood. You\u2019ll learn about instance and memory structures (SGA, PGA), background processes, startup and [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":5346,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"googlesitekit_rrm_CAowu461DA:productID":"","footnotes":""},"categories":[1225],"tags":[],"class_list":["post-5324","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-database"],"_links":{"self":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5324","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/comments?post=5324"}],"version-history":[{"count":23,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5324\/revisions"}],"predecessor-version":[{"id":5348,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/posts\/5324\/revisions\/5348"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media\/5346"}],"wp:attachment":[{"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/media?parent=5324"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/categories?post=5324"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/w3buddy.com\/blog\/wp-json\/wp\/v2\/tags?post=5324"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}