Customise Consent Preferences

We use cookies to help you navigate efficiently and perform certain functions. You will find detailed information about all cookies under each consent category below.

The cookies that are categorised as "Necessary" are stored on your browser as they are essential for enabling the basic functionalities of the site. ... 

Always Active

Necessary cookies are required to enable the basic features of this site, such as providing secure log-in or adjusting your consent preferences. These cookies do not store any personally identifiable data.

No cookies to display.

Functional cookies help perform certain functionalities like sharing the content of the website on social media platforms, collecting feedback, and other third-party features.

No cookies to display.

Analytical cookies are used to understand how visitors interact with the website. These cookies help provide information on metrics such as the number of visitors, bounce rate, traffic source, etc.

No cookies to display.

Performance cookies are used to understand and analyse the key performance indexes of the website which helps in delivering a better user experience for the visitors.

No cookies to display.

Advertisement cookies are used to provide visitors with customised advertisements based on the pages you visited previously and to analyse the effectiveness of the ad campaigns.

No cookies to display.

Oracle DBA Interview Questions

Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
Breadcrumb Abstract Shape
  • User AvatarAshiwini
  • 30 Jan, 2025
  • 0 Comments
  • 45 Mins Read

Oracle DBA Interview Questions

What are the different responsibilities of a Oracle DBA?

Answer: Oracle DBA is the database administrator who performs all administrative tasks. Administrative tasks include:
1 .User-level administration i.e. creates users, remove existing users or modifies user permissions.
2 .Maintains database security.
3 .Manages database storage & objects.
4 .Tunes performance of a database.
5. Performs backups & recovery tasks.

  1. Why do we use the materialized view instead of a table or views?

    Answer: A Materialized View is a database object that stores the results of a query. Using materialized views instead of tables or regular views in complex queries can significantly improve performance since the query results are precomputed and do not need to be re-executed repeatedly.

  2. What do you mean by Database Normalization and why is it important?

    Answer: Normalization technique is a set of rules that are used to organize the relational database to prevent data redundancy and dependency. Once initial database objects are identified, normalization helps in identifying the relationships between schema objects.
    Different normalization forms are as follows:
    1.First Normal Form (1NF)
    2. econd Normal Form (2NF)
    3. Third Normal Form (3NF)
    4. Boyce-Codd Normal Form (BCNF)
    5. Fourth Normal Form (4NF)
    6. Fifth Normal Form (5NF)

  3. Can you list down the different components of physical and logical database structure?

    Answer: Given below is the list of different components.
    The physical structure includes:
    1.Data files, which hold all the DB objects like tables, views, indexes, etc.
    2. Redo Log files, which maintains the records of database changes as a result of user transactions.
    3. Control files, which maintain the database status and physical structure.
    The logical structure includes:
    1. Tablespace, which is a logical storage unit where the database object resides.
    2. Segments are logical storage units only but within a tablespace.
    3. Extent is the logical unit where various contiguous data blocks and extents together form a segment.
    4. A data block is the smallest logical storage unit in the database.

  4. What is a SYSTEM tablespace and why do we need it?

    Answer: System tablespace is created at the time of database creation. This tablespace holds all the data dictionary tables and hence it is used for the creation of various database objects. System tablespace must stay online for the database to function smoothly.

  5. How is the Clustered Index different from the Non-Clustered Index?

    Answer: An index is a schema object, which can search the data efficiently within the table.
    Indexes can be clustered or non-clustered. Differences include:
    1. In a clustered index, table records are sorted physically and stored in a particular order. Hence, a table can have a single clustered index only. While in a non-clustered index, logical sorting happens which does not match the physical order of the records.
    2. Leaf node of a clustered index holds the data pages while the non-clustered index holds the index rows.

  6. What do you mean by SGA and how is it different from PGA?

    Answer: SGA means System Global Area is the memory area that is defined by Oracle during instance startup. This area can be shared by the system-level processes and hence it is known as the Shared Global Area as well. PGA is Program Global Area is memory specific to a process or session. It is created when the Oracle process gets started and each process will have a dedicated PGA

  7. What is a password file in a database and why is it required when a user can be authenticated using data dictionary tables?

    Answer: Database users can be authenticated using data dictionary tables as they store the username & password. If the password provided by a user matches with the one stored in the database, then the user would be able to log in. However, this can happen only if the database is open. If the database is in shutdown mode, then these tables cannot be accessed and hence password file will be used by the database administrators to log in and open the database.

  8. What are the different types of backups that are available in Oracle?

    Answer: On a higher level, there are 2 types of backup that are available in Oracle which are physical & logical. During physical backup, copies of physical database files (like data files, control files, redo logs & other executables) are created and saved for the future. This can be achieved using either operating system utilities or RMAN.
    In contrast, logical backup allows taking a backup of the database objects like tables, views, indexes, stored procedures, etc. individually through Export/Import utility provided by Oracle.

  9. What do we mean by hot backup & cold backup and how are they different?

    Answer: Hot backup is the process of taking database backup while the database is in running mode. Hence, it is also known as Online Backup. While cold backup can be taken only when the database is in shut down mode and hence it is known as Offline Backup as well. There are few websites like banking & trading ones, which are 24 hours operational and hence, cannot support bringing the database down. Hence, DBAs need to take the backup in online mode only.

  10. What is the difference between restoring a database and recovering a database?

    Answer: During the restoration process, backup files are copied from the hard disk, media or tapes to the restoration location and later make the database operational. Recovery has an additional step of updating these data files by applying redo logs so as to recover the changes which are not backed up. Let us understand this with the help of a scenario.
    1.Database full backup is taken on Friday 11 PM
    2.Database crash happened on Saturday 7 AM
    We can restore the lost files using the 11 PM full backup which is Restoration. However, the data will be restored up till Friday at 11 PM and not till Saturday at 7 AM. In order to do the same, redo logs can be applied which will bring the database to the point of failure.

  11. What do you understand by Redo Log file mirroring?

    Answer: Redo log is the most crucial component of database architecture that records all transactions within the database even before it goes to the data file. Hence, the mirroring of these files is done to protect them. Redo Log file mirroring allows redo logs to be copied to different disks simultaneously. And this can be achieved using Data Guard and other utilities.

  12. How is incremental backup different from differential backup?

    Answer: Incremental backup is known for keeping back up of only the changed data files since the last backup, which might be full or incremental. For Example, An incremental/full backup is done at 10 AM on Friday and the next backup is done at 10 AM Saturday. The second incremental backup will only have the transactions occurred after Friday at 10 AM. While Differential backup backs up the files that changed during the last full backup. If you take a full back up on Friday at 10 AM and then differential backup on Saturday at 10 AM, it will take the backup of the files changed since Friday, 10 AM. Further, if the differential backup is taken on Sunday at 10 AM, it will take the backup of the files changed since Friday, 10 AM.

  13. What is a Flashback Query and when should it be used?

    Answer: Oracle has introduced a flashback technology to recover the past states of database objects. It can recover the accidental changes, which got committed as well. Recovery depends on the specified value of the UNDO_RETENTION parameter. For Example, the UNDO_RETENTION parameter is set to 2 hours and if a user accidentally deletes the data at 11 AM with commit performed. Then, using FLASHBACK QUERY, he can retrieve these rows until 1 PM only.

  14. How is RMAN better than the user-managed backup recovery process?

    Answer: Recovery Manager (RMAN) is an Oracle built-in utility that can automate database backup & recovery processes and administrate backup strategies as well. In user-managed backups, the user needs to perform backup manually. RMAN backup time will be less when compared to user-managed backups as RMAN maintains all the metadata in the Central Repository and can quickly retrieve the same. RMAN does incremental backup rather than taking full file backups which are done by user-managed backups, which again saves time. RMAN creates backup and recovery scripts that can be re-used and scheduled and does not need manual intervention. RMAN can detect corrupted data blocks automatically during the backup process and recover them, whereas it doesn’t happen in user-managed backups.

  15. What is a Recovery Catalog?

    Answer: Recovery catalog is a database schema that holds the metadata used by RMAN for restoration and recovery processes. It basically stores information on
    1.Datafiles & their backup files.
    2.Archived Redo Logs & their backup sets.
    3.Stored scripts
    4.Incarnation
    5.Backup history The catalog gets updated once RMAN takes the backup or switches redo log or changes data file.

  16. How do you recover a lost control file?

    Answer: If one of the control files is lost or gets corrupted, we can use another copy of the control file maintained at the OS level. If all the copies of control files are lost or if a user is maintaining only one copy of the control file which gets lost, then a user can
    1.Manually create a control file.
    2.Restore it from the backup control file using the below command.

    ALTER DATABASE BACKUP CONTROL FILE TO TRACE;

    3.Restore using RMAN backup by using the below commands.

  17. What is the difference between media recovery & crash recovery?

    Answer: Media recovery is the process of recovering the database from the backup whenever a disk failure is there. Physical files like data files, control files or server parameter files get recovered during media recovery. However, crash recovery will be performed whenever a database instance failure occurs. Media recovery needs to be performed by DBA while crash recovery is an automated process that is taken care of SMON background process.

  18. What is RAC and what are the various benefits of using RAC architecture?

    Answer: RAC or Real Application Cluster allows the database to be installed across multiple servers forming a cluster and sharing the storage structure at the same time. This prevents the database from a single point of failure as one or the other instance will always stay up even if the other fails.
    Using RAC helps in
    1.Maintaining high availability of the system.
    2.Managing workload with the least expenses.
    3.Scalability & agility.

  19. How would you differentiate between cluster and grid?

    Answer: Clustering is an integral part of grid infrastructure and focuses on a specific objective. While grid, which may or may not consist of multiple clusters, possesses a wider framework that enables sharing of storage systems, data resources and remaining others across different geographical locations. A cluster will have single ownership but the grid can have multiple ownership based on the number of the cluster it holds.

  20. What do you understand from Cache Fusion?

    Answer: Cache fusion is the process of transferring data from one instance buffer cache to
    another at a very high speed within a cluster. Instead of fetching data from physical disk which is
    a slow process, the data block can be accessed from the cache.
    For Example, Instance A wants to access a data block, owned by instance B. It will send an
    access request to instance B and hence can access the same using the other instance B’s buffer
    cache.

  21. How can we monitor the space allocations in a database?

    Answer: We can use the below data dictionary tables to monitor the space allocations:;
    1.DBA_FREE_SPACE
    2.DBA_SEGMENTS
    3.DBA_DATA_FILES

  22. What do you understand by “Performance Tuning of DB” & what are the different
    areas where we can perform tuning?

    Answer: It is the process of enhancing database performance by making optimal use of the
    available resources.
    Performance can be enhanced by tuning any of the below areas:
    1.Database design.
    2.Memory allocation.
    3.Disk I/Os.
    3.Database contention.
    4.OS level (CPU).

  23. What are the different tools that are provided by Oracle to assist performance
    monitoring?

    Answer: Various tools include:
    1.AWR(Automatic Workload Repository)
    2.ADDM(Automated Database Diagnostics Monitor)
    3.TKPROF
    4.STATSPACK
    5.OEM(Oracle Enterprise Manager)

  24. What are the different optimizers that are used to optimize the database?

    Answer: There are two types of optimizers:

    1. Rule-Based Optimizer (RBO): If the referenced objects don’t maintain any
    internal statistics, RBO is used.
    2. Cost-Based Optimizer (CBO): If the referenced objects maintain internal
    statistics, CBO will check all the possible execution plans and select the one with
    the lowest cost.

  25. What is an explain plan and how does it help in optimizing the SQL query?

    Answer: An explain plan is a statement that displays the execution plan selected by the Oracle
    optimizer for SELECT, INSERT, UPDATE & DELETE statements. By looking at this plan, one
    can figure out Oracle selection of the right indexes, proper joins & sorts operations, etc.

  26. How can we collect the statistics of different database objects?

    Answer: ANALYZE statement can be used to collect the statistics of various database objects
    like tables, indexes, partitions, cluster or object references. Using this statement we can also
    identify migrated as well as chained rows within a table or cluster.

  27. Why do we need to rebuild indexes?

    Answer: Rebuilding indexes is required in order to improve the performance of an
    application. Due to various INSERT & DELETE operations, the index gets fragmented &
    unstructured, thereby making the application slow. To reorganize data within these indexes,
    rebuilding is performed.

  28. What is TKPROF and how can we use it?

    Answer: TKPROF is a tuning utility provided by Oracle which can convert SQL trace files into a
    readable format.
    Once trace file is generated using SQL Trace Utility, the TKPROF tool can be run against trace
    file and output can be read. It can also generate the execution plan for SQL statements. The
    executable for TKPROF is located in the ORACLE HOME/bin directory.

  29. How can we tune a SQL query to optimize the performance of a database?

    Answer: Enlisted are a few of the best practices for writing SQL queries.
    1.Column names should be provided instead of * in SELECT statements.
    2.Joins should be used in the place of sub-queries.
    3.EXISTS should be used instead of IN to verify the existence of data.
    4.UNION ALL should be used in the place of UNION.
    5.HAVING should be used only for filtering the resulted rows from the SQL query.

  30. How would you identify the SHARED_POOL_SIZE parameter that needs to be adjusted?

    Answer: Below is the indications for the same:
    1.Getting an ORA-04031 error.
    2.Degrading the performance even when all the other parameters are already
    optimized.
    3.Poor library cache/data dictionary hits.

  31. What do you understand by Row Chaining?

    Answer: When a row is too large that it cannot fit in a block, then it will end up using consequent
    blocks which lead to the concept of Row Chaining. It can be avoided by updating the storage
    parameters to an appropriate value

  32. What is table partitioning and why is it required?

    Answer: It is a process of dividing a table into smaller chunks so as to make the data retrieval
    easy and quick. Each piece will be known as a partition and can be accessed separately. Apart
    from tables, indexes can also be partitioned

  33. How can we identify the resources for which the sessions are waiting?

    Answer: We can find it out using v$session_waits and v$ system _waits

  34. How to create password file?

    Answer: $ orapwd file=orapwSID password=sys_password force=y nosysdba=y

  35. How many types of indexes are there?

    Answer: Clustered and Non-Clustered
    1.B-Tree index
    2.Bitmap index
    3.Unique index
    4.Function based index
    5. Implicit index and explicit index
    Explicit indexes are again of many types like simple index, unique index, Bitmap index, Functional index,
    Organizational index, cluster index.

  36. What is bitmap index & when it’ll be used?

    Answer:
    – Bitmap indexes are preferred in Data warehousing environment. Refer Q31
    – Preferred when cardinality is low.

  37. What is B-tree index & when it’ll be used?

    Answer:
    – B-tree indexes are preferred in OLTP environment. Refer Q31
    – Preferred when cardinality is high

  38. How you will find out fragmentation of index?

    Answer:
    – AUTO_SPACE_ADVISOR_JOB will run in daily maintenance window and report fragmented
    Indexes/Tables
    SQL>ANALYZE INDEX VALIDATE STRUCTURE;
    This populates the table ‘INDEX_STATS’. It should be noted that this table contains only one row and therefore only
    one index can be analyzed at a time.
    An index should be considered for rebuilding under any of the following conditions:
    * the percentage of deleted rows exceeds 30% of the total, i.e. if del_lf_rows / lf_rows > 0.3.
    * If the ‘HEIGHT’ is greater than 4.
    * If the number of rows in the index (‘LF_ROWS’) is significantly smaller than ‘LF_BLKS’ this can indicate a large
    number of deletes, indicating that the index should be rebuilt.

  39. What is the difference between delete and truncate?

    Answer:
    Truncate will release the space. Delete won’t.
    Delete can be used to delete some records. Truncate can’t.
    Delete can be rolled back.
    Delete will generate undo (Delete command will log the data changes in the log file where as the truncate will simply
    remove the data without it. Hence data removed by Delete command can be rolled back but not the data removed by
    TRUNCATE).
    Truncate is a DDL statement whereas DELETE is a DML statement.
    Truncate is faster than delete.

  40. Describe Oracle Architecture.

    Answer: Oracle’s architecture consists of several components, including memory structures, background processes, and database storage. The key memory components are the System Global Area (SGA), which contains the Shared Pool, Buffer Cache, Redo Log Buffer, and other pools. The Program Global Area (PGA) holds data related to user sessions. Background processes like Log Writer (LGWR), Database Writer (DBWR), System Monitor (SMON), and others ensure the smooth functioning of the database. The database itself consists of data files, control files, redo logs, and archive logs, which together support data storage and recovery.

  41. What is the role of the PMON process?

    Answer: The Process Monitor (PMON) in Oracle is responsible for cleaning up after failed user processes by releasing locked resources, recovering transactions, and ensuring that any session-specific memory is freed up.

  42. What are the different stages of the database startup process?

    Answer: Oracle startup involves three primary stages:

    NOMOUNT: Starts the instance by reading the initialization file but does not open the database.

    MOUNT: Associates the instance with the database and reads control files.

    OPEN: Fully opens the database, making it accessible for users and applications.

  43. Explain the backup process used in your organization.

    Answer: A typical backup strategy includes full backups, incremental backups, and archive log backups. RMAN (Recovery Manager) is commonly used to automate and optimize the backup process

  44. How do you restore a database from a backup?

    Answer:
    Start the database in NOMOUNT mode.

    Restore the SPFILE and Control Files if required.

    Mount the database and restore the datafiles using RMAN.

    Recover the database using archived logs.

    Open the database using RESETLOGS if necessary.

  45. What are LMT and DMT?

    Answer: LMT (Locally Managed Tablespaces) store extent allocation information within the tablespace itself, whereas DMT (Dictionary Managed Tablespaces) rely on the data dictionary for extent tracking. LMT is preferred due to improved performance and reduced contention.

  46. How do you resolve a 1000-archive log gap in a standby database?

    Answer:
    Check the SCN numbers between primary and standby databases.

    Identify missing archive logs and manually apply them.

    Use RMAN to fetch missing logs and apply them to synchronize standby.

  47. What would you check if archives are not arriving at the standby database?

    Answer:
    Verify Data Guard configurations.

    Check network connectivity between primary and standby.

    Query v$dataguard_status for potential errors.

    Ensure proper log_archive_dest configuration.

  48. Difference between a Data Dictionary table and a regular table?

    Answer: Data dictionary tables store metadata about database structures, users, and privileges, whereas regular tables contain user-generated data.

  49. What happens when you issue a STARTUP UPGRADE command?

    Answer: This mode is used when upgrading the Oracle database. It restricts normal operations and allows only necessary upgrade scripts to be executed.

  50. What are the key steps in an Oracle database upgrade?

    Answer:
    Backup the database.
    Gather statistics and clean up unnecessary objects.
    Run Oracle’s pre-upgrade utility.
    Install the new database software.
    Execute the upgrade scripts.
    Perform post-upgrade checks and testing.

  51. How do you install Oracle binaries and configure kernel parameters?

    Answer: Before installation, kernel parameters like SHMMAX, SHMMNI, SEMMNI, and FS_FILE_MAX must be configured to ensure optimal resource allocation. The installation process involves using runInstaller to deploy the Oracle software.

  52. What are the steps to configure Oracle Data Guard?

    Answer:
    Enable ARCHIVELOG mode.

    Set STANDBY_FILE_MANAGEMENT to AUTO.

    Configure TNS and listener settings.

    Set log_archive_config and fal_client parameters.

    Use RMAN to create a standby database.

    Start managed recovery.

  53. How do you check the directory size in ASM?

    Answer:

    SELECT f.group_number, f.file_number, bytes, space, space/(1024*1024) AS "Size_MB", a.name 
    FROM v$asm_file f, v$asm_alias a;
  54. How do you check the Oracle Database version?

    Answer: 

    SELECT * FROM v$version;
  55. What are the different modes of Oracle Data Guard?

    Answer:
    Physical Standby: Exact copy of the primary database.

    Logical Standby: Uses SQL apply to keep standby updated.

    Snapshot Standby: Read-write mode for testing.

    Active Data Guard: Allows queries while logs are applied.

  56. What is RMAN, and how do you check RMAN’s default configuration?

    Answer: RMAN (Recovery Manager) is an Oracle tool for backup and recovery automation. To check the configuration:

    SHOW ALL;
  57. How do you verify that standby is in sync with primary?

    Answer: Run these queries:

    -- On Primary
    SELECT thread#, MAX(sequence#) FROM v$archived_log;
    
    -- On Standby
    SELECT thread#, MAX(sequence#) FROM v$archived_log WHERE applied = 'YES';
  58. What is Flashback Recovery?

    Answer: Flashback Recovery allows rolling back the database to a previous point without using full restore operations. It requires Flashback Logging to be enabled.

  59. What is a snapshot too old error?

    Answer: This occurs when Oracle cannot find undo data needed for a query. Increasing undo tablespace or tuning queries can help mitigate this issue.

  60. How do you create a new Oracle user?

    Answer:

    CREATE USER username IDENTIFIED BY password;
    GRANT CONNECT, RESOURCE TO username;
  61. What is the difference between a control file and a parameter file?

    Answer:
    Control File: Stores database metadata such as data file locations and log sequences.

    Parameter File (PFILE/SPFILE): Contains configuration parameters for the database instance.

  62. What is the command to add a datafile to a tablespace?
    Answer:
    ALTER TABLESPACE tablespace_name ADD DATAFILE 'datafile_name' SIZE 5M;
  63. Can we resize a redo log file?

    Answer: No, redo log files cannot be resized directly. Instead, new logs must be created, and old ones must be dropped after switching them to INACTIVE status.

  64. What is Disaster Recovery (DR)?

    Answer: Disaster Recovery refers to the processes and technologies used to restore and maintain database operations after unexpected failures.

  65. Can we have two MRP processes on a DR server?

    Answer: No, an Oracle Data Guard environment typically allows only one MRP (Managed Recovery Process) running on the standby server at a time. Multiple MRP processes are not supported as each standby database has only one dedicated recovery process responsible for applying archive logs from the primary database. If additional standby databases exist, each will have its own separate MRP process.

  66. How to set pga size, can you change it while the database is running?

    Answer: You can set the PGA size using the PGA_AGGREGATE_TARGET parameter, and it can be modified while the database is running.

    To check the current value:

    sql
    SHOW PARAMETER PGA_AGGREGATE_TARGET;

    To change the PGA size dynamically:

    sql
    ALTER SYSTEM SET PGA_AGGREGATE_TARGET = 200M;

    Yes, PGA_AGGREGATE_TARGET is a dynamic parameter, meaning you can adjust it without restarting the database.

  67. How to know which parameter is dynamic/static?

    Answer: Run the following SQL query:
    1. Query V$PARAMETER View

    sql
    SELECT name, issys_modifiable
    FROM v$parameter
    WHERE name = 'pga_aggregate_target';
    • If issys_modifiable is IMMEDIATE, the parameter can be changed dynamically.
    • If issys_modifiable is DEFERRED, the change takes effect at the next session login.
    • If issys_modifiable is FALSE, the database must be restarted for the change to take effect.

    2. Query V$SYSTEM_PARAMETER

    Another method to check:

    sql
    SELECT name, isdefault, isses_modifiable, issys_modifiable
    FROM v$system_parameter
    WHERE name = 'sga_target';
    • ISSES_MODIFIABLE = TRUE → Can be changed at the session level.
    • ISSYS_MODIFIABLE = IMMEDIATE → Can be changed dynamically at the system level.

    3. Query V$PARAMETER2 for More Details

    sql
    SELECT name, type, value, isdefault, issys_modifiable
    FROM v$parameter2
    WHERE name = 'memory_target';

    4. Check with SHOW PARAMETER

    If you want to see all parameters, use:

    sql
    SHOW PARAMETER;
     
  68. How to know how much free memory available in sga?

    Answer:
    1. Query V$SGA_DYNAMIC_FREE_MEMORY

    This view provides the amount of free memory available in the SGA:

    sql
    SELECT * FROM V$SGA_DYNAMIC_FREE_MEMORY;
    • BYTES: Displays the amount of free memory in bytes.

    2. Query V$SGASTAT

    The V$SGASTAT view provides a more detailed breakdown of SGA memory usage.
    To find the free memory:

    sql
    SELECT pool, name, bytes
    FROM V$SGASTAT
    WHERE name = 'free memory';
    • This will show how much free memory is available in different memory pools.

    3. Query V$SGAINFO

    Another useful view is V$SGAINFO:

    sql
    SELECT name, bytes
    FROM V$SGAINFO
    WHERE name IN ('Free SGA Memory Available');
    • This gives the available free memory within the SGA.

    4. Query V$SGA

    To get a summary of SGA memory components:

    sql
    SELECT * FROM V$SGA;
    • This helps you understand total memory allocation.

    5. Use SHOW SGA

    sql
    SHOW SGA;
  69. What are oracle storage structures?

    Answer:
    1. Logical Storage Structures
    (Organizes Data)

    Tablespaces → Logical storage units containing datafiles.
    Segments → Storage for tables, indexes, undo, and temporary data.
    Extents → Groups of contiguous blocks allocated to segments.
    Data Blocks → Smallest storage unit in Oracle (Default: 8 KB).
    2. Physical Storage Structures (OS-Level Storage)

    Datafiles → Store actual database data.
    Redo Log Files → Store transaction logs for recovery.
    Control Files → Store database metadata (SCN, file locations).
    Archive Logs → Backup of redo logs for recovery.
    Temporary Files → Used for sorting and temp operations.
    Parameter Files (SPFILE/PFILE) → Store database initialization parameters

  70. List types of Oracle objects

    Answer:
    Types of Oracle Objects:

    Table – Stores structured data in rows and columns.
    Index – Improves query performance (B-Tree, Bitmap, etc.).
    Cluster Table – Stores related tables together to improve performance.
    IOT (Index-Organized Table) – Stores table data in a B-Tree index structure.
    Function – A stored PL/SQL program that returns a value.
    Procedure – A stored PL/SQL program that does not return a value.
    Package – A collection of related procedures and functions.
    Trigger – Executes automatically on specific database events (INSERT, UPDATE, DELETE).
    Sequence – Generates unique numbers (often used for primary keys).
    Synonym – An alias for database objects (used to simplify access).
    View – A virtual table based on a query.
    Materialized View – A stored query result for fast retrieval.
    Database Link – Allows access to remote databases.
    Tablespace – A logical storage unit that contains datafiles.
    Directory – Defines file system paths accessible from Oracle.

  71. What is a Synonym in Oracle?

    Answer:
    A synonym in Oracle is an alias for a database object, such as a table, view, sequence, or another synonym. It helps simplify access to objects and provides security by hiding the underlying object’s name and schema.
    View: DBA_SYNONYMS
    Query: Select synonym_name from dba_synonyms;

  72. What is sequence?

    Answer: A sequence in Oracle is used to generate unique and incremental values, typically for columns that require unique identifiers, such as employee numbers or account IDs.

    Example Use Case:

    1. Create a table to store employee information:
      sql
      CREATE TABLE employee (
      emp_id NUMBER,
      emp_name VARCHAR2(50),
      emp_salary NUMBER
      );
    2. Create a sequence to generate unique numbers for the emp_id column:
      sql
      CREATE SEQUENCE emp_id_seq
      START WITH 1
      INCREMENT BY 1;
    3. Insert data into the employee table using the sequence to auto-generate the employee ID:
      sql
      INSERT INTO employee (emp_id, emp_name, emp_salary)
      VALUES (emp_id_seq.NEXTVAL, 'Paddu', 120000);
  73. Where do you see the tablespace information?

    Answer: SELECT * FROM dba_tablespaces;
    To view tablespace information in an Oracle database, you can query the DBA_TABLESPACES or USER_TABLESPACES view, depending on the access level:
    1. DBA_TABLESPACES: This view shows tablespaces for the entire database (requires DBA privileges).

    SELECT tablespace_name, status, contents, extent_management, allocation_type FROM dba_tablespaces;

    2. USER_TABLESPACES: This view shows tablespaces for the current user (no DBA privileges required).

    SELECT tablespace_name FROM user_tablespaces;
  74. How to find the datafiles that associated with particular tablespace? Ex: System

    Answer: To find the data files associated with a particular tablespace, such as SYSTEM, you can query the DBA_DATA_FILES view and filter by the tablespace name. Here’s the query:

    sql
    SELECT file_name, tablespace_name, bytes/1024/1024 AS size_mb
    FROM dba_data_files
    WHERE tablespace_name = 'SYSTEM';

    This will return the data files associated with the SYSTEM tablespace, along with their file paths and sizes in MB.

  75. How to see which undo tablespace is used for database?

    Answer: To find the undo tablespace being used by your Oracle database, you can query the V$PARAMETER view for the undo_tablespace parameter:

    sql
    SELECT value AS undo_tablespace
    FROM v$parameter
    WHERE name = 'undo_tablespace';

    This query will return the name of the undo tablespace currently in use by the database.

    Alternatively, you can also query the DBA_TABLESPACES view to check for any tablespaces that are specifically used for undo purposes:

    sql
    SELECT tablespace_name, contents
    FROM dba_tablespaces
    WHERE contents = 'UNDO';

    This will show all tablespaces that are designated for undo purposes.

  76. How to see the default temporary tablespace for a database?

    Answer: To find the default temporary tablespace for your Oracle database, you can query the DBA_USERS view or the V$PARAMETER view.

    1. Using the DBA_USERS view:This will show the default temporary tablespace for each user, including the database default:
      sql
      SELECT username, default_tablespace, temporary_tablespace
      FROM dba_users;

      The temporary_tablespace column will show the default temporary tablespace for the user. The database default is typically used unless overridden by a user-specific setting.

    2. Using the V$PARAMETER view:You can also query the V$PARAMETER view to find the overall default temporary tablespace for the database:
      sql
      SELECT value AS default_temp_tablespace
      FROM v$parameter
      WHERE name = 'default_temp_tablespace';

    This query will return the name of the default temporary tablespace for the entire database.

  77. How to see what is the default block size for a database ?

    Answer: To check the default block size for an Oracle database, you can use the following SQL query:

    sql
    SELECT value FROM v$parameter WHERE name = 'db_block_size';

    Explanation:

    • v$parameter: This view contains database initialization parameters.
    • db_block_size: This parameter defines the default database block size in bytes.

    Example Output:

    markdown
    VALUE
    -----

    8192

    This means the default block size for the database is 8 KB (8192 bytes).

  78. What is the oracle block, can you explain?

    Answer: An Oracle block is the smallest unit of storage in an Oracle database, containing data like table and index records. It has a fixed size (e.g., 4 KB, 8 KB) set during database creation. The block is divided into several sections:

    Header: Stores metadata and transaction info.
    Row Directory: Contains pointers to rows in the block.
    Free Space: Empty area for new data.
    Data Space: Holds actual data.

  79. Can you change the blocksize once the database is created?

    Answer: Obsolutely no becoz once the datafiles is formatted into 8k
    we cannot change the database block sizes , if you need, you have to
    create fresh database with new block size and restore from backup or
    import.

  80. How to change the instance name once the database is created?

    Answer:
    Steps:

    Shutdown the Database: First, shut down the Oracle instance you want to rename.

    sql
    shutdown immediate;

    Edit Initialization File:

    Locate the spfile or pfile in your Oracle home.
    Change the db_name and instance_name parameters in the initialization file. Example:

    sql
    db_name = new_database_name
    instance_name = new_instance_name

    Set the ORACLE_SID: Set the environment variable ORACLE_SID to the new instance name.

    bash
    export ORACLE_SID=new_instance_name

    Restart the Database: Restart the database instance with the new name.

    sql
    startup;

    Test the Change: Verify that the new instance name works by connecting to the database using the new SID.

  81. Where you can see the datafile information?

    Answer: Run the following query to get datafile details:
    Using SQL Query (DBA Views)
    sql

    SELECT file_name, tablespace_name, status, bytes/1024/1024 AS size_mb, autoextensible, maxbytes/1024/1024 AS max_size_mb
    FROM dba_data_files;
  82. What is the difference between v$ views and dba views?

    Answer: Here’s a refined version highlighting the key differences between V$ Views (Dynamic Views) and DBA Views (Static Views):

    Feature V$ Views (Dynamic Performance Views) DBA Views (Static Data Dictionary Views)
    Nature Dynamic (data comes from memory and changes in real time). Static (data is stored in system tables and persists).
    Availability Accessible in MOUNT and OPEN states. Available only when the database is in OPEN mode.
    Data Source Fetched from SGA (System Global Area) and reflects current status. Stored in data dictionary tables inside the SYSTEM tablespace.
    Persistence Data is lost after a database restart (except logs stored in AWR/Alert logs). Data remains permanently stored in system tables.
    Usage Used for real-time monitoring, performance tuning, and troubleshooting. Used for database administration, object management, and metadata querying.
    Examples V$DATABASE, V$DATAFILE, V$SESSION, V$LOG DBA_TABLES, DBA_USERS, DBA_DATA_FILES, DBA_SEGMENTS

  83. What is the difference between a role and privilege , can you provide an example?

    Answer: Difference Between Role and Privilege:
    A privilege is a specific right or permission granted to a user to perform a particular action in the database. A role is a collection of multiple privileges grouped together for easier management.

    Example:
    Instead of granting individual privileges like CREATE TABLE, SELECT, INSERT, and DELETE to multiple users, you can create a role named DATA_MANAGER and assign these privileges to the role. Then, you can grant the DATA_MANAGER role to users, simplifying access management.

    Types of Privileges:

    1. System Privileges – These allow users to perform administrative tasks on the database.
      Examples: CREATE SESSION, CREATE USER, DROP USER, ALTER SYSTEM
    2. Object Privileges – These control access to specific objects like tables, views, and sequences.
      Examples: SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX, REFERENCES on a table
  84. Where to view the roles and privileges assigned to a user?

    Answer: To view the roles and privileges assigned to a user in Oracle, you can use the following queries:

    1️⃣ Check System Privileges Assigned to a User

    sql
    SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'USERNAME';

    🔹 This shows system-level privileges like CREATE SESSION, CREATE TABLE, etc.


    2️⃣ Check Object Privileges Assigned to a User

    sql
    SELECT * FROM DBA_TAB_PRIVS WHERE GRANTEE = 'USERNAME';

    🔹 This displays object-level privileges (e.g., SELECT, INSERT, UPDATE, DELETE on tables, views, etc.).


    3️⃣ Check Roles Assigned to a User

    sql
    SELECT * FROM DBA_ROLE_PRIVS WHERE GRANTEE = 'USERNAME';

    🔹 This lists the roles granted to the user.


    4️⃣ Check Privileges Included in a Role

    sql
    SELECT * FROM DBA_SYS_PRIVS WHERE GRANTEE = 'ROLE_NAME';

    🔹 This helps identify what privileges are included in a specific role.


    5️⃣ Check All Roles in the Database

    sql
    SELECT * FROM DBA_ROLES;

    🔹 This retrieves all available roles in the database.

    Alternative for Non-DBA Users

    If you don’t have access to DBA views, you can check your privileges using:

    sql
    SELECT * FROM USER_SYS_PRIVS; -- System privileges for the current user
    SELECT * FROM USER_TAB_PRIVS; -- Object privileges for the current user
    SELECT * FROM USER_ROLE_PRIVS; -- Roles assigned to the current user

  85. What is the difference between with grant option and with admin option while assigning privileges?

    Answer:
    1️⃣ WITH GRANT OPTION

    ✅ Used only for object privileges (e.g., SELECT, INSERT, UPDATE, DELETE on tables, views, etc.).
    ✅ Allows the grantee (user who receives the privilege) to grant the same privilege to other users.
    ✅ If the original grantor’s privilege is revoked, all granted privileges are also revoked.

    📌 Example:

    sql
    GRANT SELECT ON employees TO user1 WITH GRANT OPTION;

    🔹 Now, user1 can use the SELECT privilege and also grant it to others.


    2️⃣ WITH ADMIN OPTION

    ✅ Used only for roles (not for system or object privileges).
    ✅ Allows the grantee to grant and revoke the role to/from other users.
    ✅ Even if the original grantor loses the role, the users they granted it to still keep it.

    📌 Example:

    sql
    GRANT dba TO user2 WITH ADMIN OPTION;

    🔹 Now, user2 can use the DBA role and grant/revoke it to other users.

  86. How to revoke privileges or roles?

    Answer: To revoke an object privilege:

    REVOKE SELECT ON T FROM AISHU;

    To revoke a role:

    REVOKE SURESH FROM AISHU; -- SURESH is a role here
  87. How to change the default tablespace for a user?
    ALTER USER AISHU DEFAULT TABLESPACE 
  88. How to give a tablespace quota to a user?
    ALTER USER AISHU QUOTA UNLIMITED ON T;
  89. What are constraints? List them and their use cases.

    Answer:
    Constraints in Oracle ensure data integrity. Common constraints include:

    NOT NULL: Prevents null values in a column.

    UNIQUE: Ensures all values in a column are unique.

    PRIMARY KEY: Uniquely identifies a row (combines NOT NULL and UNIQUE).

    FOREIGN KEY: Ensures a valid reference to a primary key in another table.

    CHECK: Enforces a specific condition on column values.

    Example:

    Master Table (Stores primary key data)

    CREATE TABLE PINCODE (
    AREA VARCHAR2(30),
    PINCODENUM NUMBER PRIMARY KEY
    );
    
    INSERT INTO PINCODE VALUES ('Miyapur', 500049);
    INSERT INTO PINCODE VALUES ('Ameerpet', 500084);

    Child Table (References the primary key)

    CREATE TABLE EMPLOYEE (
    EMPNAME VARCHAR2(30),
    EMPID NUMBER UNIQUE,
    ADDRESS1 VARCHAR2(10) CHECK (ADDRESS1 = 'Hyderabad'),
    ADDRESS2 VARCHAR2(20),
    PINCODE NUMBER CONSTRAINT PIN_FK FOREIGN KEY (PINCODE) REFERENCES PINCODE (PINCODENUM)
    );
  90. What is row chaining? When does it occur? How can you find and fix it?

    Answer:

    Row chaining occurs when a row is too large to fit into a single database block, causing it to span multiple blocks.

    Identify chained rows:

    SELECT TABLE_NAME, CHAIN_CNT FROM DBA_TABLES WHERE TABLE_NAME='TABLE_NAME';

    Solutions:

    Use a tablespace with a larger block size.

    CREATE TABLESPACE TS DATAFILE '/u01/oradata/aishu/paddu.dbf' SIZE 100M BLOCKSIZE 16K;
    ALTER TABLE EMPLOYEE MOVE TABLESPACE TS;

    Ensure a properly sized DB_BUFFER_CACHE is configured.

  91. What is row migration? How can you detect and fix it?

    Answer:

    Row migration happens when a row no longer fits in its original block after an update, forcing Oracle to move it.

    Detect migrated rows:

    SELECT TABLE_NAME, CHAIN_CNT FROM DBA_TABLES WHERE TABLE_NAME='TABLE_NAME';

    Solution:

    Set PCT_FREE storage parameter appropriately to reduce row migration.

    ALTER TABLE EMPLOYEE STORAGE (PCT_FREE 20);
  92. How to check if the instance is using SPFILE or PFILE?

    Answer:

    ALTER TABLE EMPLOYEE STORAGE (PCT_FREE 20)
  93. How to create a password file?

    Answer:

    orapwd file=$ORACLE_HOME/dbs/pwaishu.ora entries=5 ignorecase=Y;
  94. How to create a database manually?

    Answer:

    Create an initialization parameter file (pfile).

    Create necessary directories for datafiles, logs, and control files.

    Execute the CREATE DATABASE command.

    Run catalog.sql and catproc.sql.

    Update listener.ora and tnsnames.ora.

    Add an entry in /etc/oratab

  95. What is Oracle Flexible Architecture (OFA)?

    Answer:

    OFA is a best practice for organizing database files to improve performance and manageability. It distributes files across multiple disks for optimized I/O

  96. What do SYSTEM and SYSAUX tablespaces contain?

    Answer:

    SYSTEM Tablespace: Stores core database objects, dictionary tables, and metadata.

    SYSAUX Tablespace: From Oracle 10g onward, offloads some SYSTEM tablespace content, including AWR, Oracle session statistics, and execution plans.

  97. What are statistics in Oracle? Why are they important?

    Answer:

    Statistics provide information about database objects, helping the optimizer choose the best execution plan.

    System Statistics: Hardware-related metrics (CPU speed, I/O rates).

    Object Statistics: Table and index details (row count, block usage, distinct values).

    SELECT TABLE_NAME, NUM_ROWS, BLOCKS FROM DBA_TABLES;
  98. How to check the size of a table and database?

    Answer:

    Table Size:

    SELECT SEGMENT_NAME, BYTES/1024/1024 AS SIZE_MB FROM DBA_SEGMENTS WHERE SEGMENT_NAME='TABLE_NAME';

    Database Size:

    SELECT SUM(BYTES)/1024/1024 AS SIZE_MB FROM DBA_SEGMENTS;
  99. How to resize a datafile?
    Answer:
    Ensure the datafile is auto-extensible before resizing.
    ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' RESIZE 100M;
  100. How to add or remove a datafile?

    Answer:
    Add Datafile:

    ALTER TABLESPACE T ADD DATAFILE '/u01/oradata/paddu/tbs1.dbf' SIZE 100M;

    Drop Datafile (only if empty):

    ALTER TABLESPACE T DROP DATAFILE '/u01/oradata/paddu/tbs1.dbf';
  101. How to move a datafile to a new location?

    Answer:

    Take the tablespace offline:

    ALTER TABLESPACE T OFFLINE;

    Move the file at the OS level.

    Rename it in Oracle:

    ALTER DATABASE RENAME FILE '/old/path/datafile.dbf' TO '/new/path/datafile.dbf';

    Bring the tablespace online:

    ALTER TABLESPACE T ONLINE;
  102. What is a profile? How do you manage profiles?

    Answer:
    A profile is a set of resource limits for users.

    SELECT USERNAME, PROFILE FROM DBA_USERS;
    ALTER USER AISHU PROFILE DEFAULT;
  103. How to create a user and assign a profile?

    Answer:

    CREATE USER PADDU IDENTIFIED BY PADDU DEFAULT TABLESPACE TESTTBS1 PROFILE TEST;
    GRANT CONNECT, RESOURCE TO PADDU;
  104. Can you delete the alert log while the database is running?

    Answer:
    Yes, Oracle will automatically create a new alert log when needed.

    rm -f $ORACLE_BASE/diag/rdbms/*/trace/alert_*.log

  105. Why do you need to do open resetlogs, and what does it do?

    Answer:
    Resetlogs is required after performing incomplete recovery (or point-in-time recovery) because the database might not have all redo and archive logs up to the point of failure. Opening with resetlogs initializes new online redo log files, starts a new log sequence, and resets the log history.

  106. How to multiplex redo log files?

    Answer: You can add a redo log file member to an existing group using:

    ALTER DATABASE ADD LOGFILE MEMBER 'new_location' TO GROUP <group_number>;
  107. How to multiplex control files?

    Answer: Using pfile: Add multiple control file paths in control_files parameter in the pfile, copy the control file to new locations, and restart the database.
    Using spfile: Modify the control_files parameter using ALTER SYSTEM and follow the same steps to copy files before restarting.

  108. How to add redo log groups to a database?

    Answer: Add a new redo log group using:
    sql
    Copy
    Edit
    ALTER DATABASE ADD LOGFILE GROUP <group_number> ‘location’ SIZE 50M;

  109. Dropping redo log groups while the database is up and running?

    Answer:

    Yes, you can drop a redo log group, but it must be inactive.

  110. Can you drop the system tablespace?

    Answer:
    No, you cannot drop the SYSTEM tablespace. Oracle does not allow this since it contains critical database objects.

  111. Can you drop normal tablespaces?

    Answer:
    Yes, but you must ensure the tablespace is empty or use INCLUDING CONTENTS or INCLUDING CONTENTS AND DATAFILES clauses to drop it and the associated objects.

  112. Difference between Oracle home and Oracle base?

    Answer:
    ORACLE_BASE: The root directory for Oracle software installations.
    ORACLE_HOME: The directory under ORACLE_BASE where Oracle products are installed.

  113. Where do you check the free space of objects?

    Answer:
    Query the DBA_FREE_SPACE view:

    sql
    SELECT * FROM DBA_FREE_SPACE;
  114. How to kill a blocking session?

    Answer:
    Identify the blocking session:

    sql
    
    SELECT sid, username, serial#, status, event, blocking_session FROM v$session WHERE blocking_session IS NOT NULL;

    Kill the blocking session using:

    sql
    
    ALTER SYSTEM KILL SESSION 'sid,serial#' IMMEDIATE;
  115. Can you kill PMON, SMON, or CKPT processes?

    Answer:
    No, killing these mandatory background processes will crash the database.

  116. Parameters for different pools of Oracle instance:

    Answer:
    Example parameters: shared_pool_size, db_cache_size, java_pool_size, large_pool_size, log_buffer.
    To automatically manage memory pools, set SGA_TARGET and SGA_MAX_SIZE.

  117. Startup and shutdown modes of Oracle database:

    Answer:

    Startup modes: NOMOUNT, MOUNT, OPEN.
    Shutdown modes: CLOSE, DISMOUNT, SHUTDOWN.

  118. Finding Oracle Homes and instances on a host:

    Answer:

    Check /etc/oratab for Oracle homes.
    Use ps -ef | grep pmon to list running Oracle instances.

  119. Difference between PuTTY and SQL*Plus:

    Answer:

    PuTTY: An SSH client used to connect to a remote server.
    SQL*Plus: A tool used to connect to and manage Oracle databases.

  120. Changing the database to archive log mode:

    Answer: Bring the database to mount mode and use:

    sql
    ALTER DATABASE ARCHIVELOG;
  121. What is the use of the tnsnames.ora file?

    Answer: The tnsnames.ora file is a configuration file that contains network service names mapped to connect descriptors. It helps Oracle clients resolve database service names to their corresponding network locations (host, port, and service name/SID).

  122. How to check if the database is in ARCHIVELOG mode?

    Answer:
    Run the following SQL command:

    SELECT log_mode FROM v$database;

    If the output is ARCHIVELOG, the database is in ARCHIVELOG mode; otherwise, it is in NOARCHIVELOG mode.

  123. How do you enable ARCHIVELOG mode in Oracle?

    Answer:

    Shut down the database:

    SHUTDOWN IMMEDIATE;

    Start the database in mount mode:

    STARTUP MOUNT;

    Enable ARCHIVELOG mode:

    ALTER DATABASE ARCHIVELOG;

    Open the database:

    ALTER DATABASE OPEN;
  124. How do you take an RMAN full backup?

    Answer:

    RMAN> BACKUP DATABASE PLUS ARCHIVELOG;

    This command takes a full database backup along with archived redo logs.

  125. What is ASM, and why is it used?

    Answer:
    ASM (Automatic Storage Management) is a volume manager and file system for Oracle databases that simplifies storage management. It provides striping and mirroring, improving performance and availability.

  126. How to check ASM disk groups?

    Answer:

    SELECT name, state, total_mb, free_mb FROM v$asm_diskgroup;
  127. What are the steps to apply an Oracle patch?

    Answer:

    Check the patch details:

    opatch lsinventory

    Stop database services (if required).

    Apply the patch:

    opatch apply

    Verify the patch application:

    opatch lsinventory

    Restart the database and services.

  128. How to check the listener status?

    Answer:

    lsnrctl status
  129. How to restart the listener?

    Answer:

    lsnrctl stop
    lsnrctl start
  130. How do you check database performance issues?

    Answer:

    Use AWR Reports:

    @$ORACLE_HOME/rdbms/admin/awrrpt.sql

    Use ASH Reports:

    @$ORACLE_HOME/rdbms/admin/ashrpt.sql

    Query performance views:

    SELECT * FROM v$session_wait;
    SELECT * FROM v$sqlarea ORDER BY elapsed_time DESC;
  131. How to check database blocking sessions?

    Answer:

    SELECT blocking_session, sid, serial#, wait_class, seconds_in_wait
    FROM v$session WHERE blocking_session IS NOT NULL;
  132. What is Data Guard, and how does it work?

    Answer:
    Oracle Data Guard is a disaster recovery and high availability solution. It maintains a standby database that remains synchronized with the primary database using redo apply or SQL apply.

  133. How do you switch roles between the primary and standby databases?

    Answer:

    On Primary:

    ALTER DATABASE COMMIT TO SWITCHOVER TO STANDBY;

    On Standby:

    ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;

    Restart both databases.

  134. What is Oracle Flashback Technology?

    Answer:
    Flashback technology allows recovery of data without restoring backups. Common commands:

    Flashback a table:

    FLASHBACK TABLE table_name TO TIMESTAMP (SYSDATE - 1);

    Flashback a dropped table:

    FLASHBACK TABLE table_name TO BEFORE DROP;
  135. What is an Oracle SCN?

    Answer:
    System Change Number (SCN) is a unique identifier that Oracle assigns to every transaction to maintain consistency and recovery operations.

  136. How do you check the last SCN number?

    Answer:

    SELECT current_scn FROM v$database;
  137. How to check database uptime?

    Answer:

    SELECT * FROM v$instance;

    Look for the STARTUP_TIME column.

  138. How do you check RMAN backup details?

    Answer:

    SELECT * FROM v$backup;
    
    RMAN> LIST BACKUP SUMMARY;
  139. How do you recover from an RMAN backup?

    Answer:

    Start RMAN:

    rman target /

    Mount the database (if needed):

    STARTUP MOUNT;

    Restore and recover:

    RESTORE DATABASE;
    RECOVER DATABASE;

    Open the database:

    ALTER DATABASE OPEN;
  140. How do you find the database size?

    Answer:

    SELECT SUM(bytes)/1024/1024/1024 AS size_in_GB FROM dba_data_files;
  141. What is the difference between expired and obsolete backups?

    Answer:
    Expired Backup: A backup is considered expired when it is no longer available at its physical location. RMAN marks such backups as expired when they cannot be found during a crosscheck.
    Obsolete Backup: A backup is marked as obsolete when it is no longer needed based on the retention policy. These backups are still available but are considered outdated.

  142. What is Block Change Tracking (BCT)?

    Answer:
    When taking an incremental backup, RMAN typically scans the entire database to identify changed blocks, which can be time-consuming for large databases.
    By enabling Block Change Tracking (BCT), RMAN maintains a record of changed blocks and backs up only those blocks, avoiding a full database scan, which significantly improves backup performance.

  143. How does Block Change Tracking work internally?

    Answer:
    The Block Change Tracking file maintains a bitmap structure to track changes.
    Each chunk consists of four contiguous 8K blocks (totaling 32K).
    If any block in this chunk changes, the corresponding bitmap entry is updated.
    During an incremental backup, RMAN checks this bitmap and backs up only changed chunks instead of scanning the entire database.

  144. What is the difference between Differential and Cumulative Incremental Backups?

    Answer:
    Differential Incremental Backup:
    Backs up only the blocks that have changed since the last incremental backup (level 1 or level 0).
    Backup size is smaller, but restore requires multiple incremental backups.
    Cumulative Incremental Backup:
    Backs up all blocks changed since the last level 0 (full) backup.
    Backup size is larger, but restoration is faster because only one incremental backup is required.

  145. Can we take an RMAN backup when the database is down?

    Answer:
    RMAN backups require the database to be in MOUNT or OPEN state.
    If the database is completely down, an RMAN backup is not possible.
    However, a cold backup (OS-level backup) can be taken when the database is shut down.

  146. What happens when we put the database in HOT BACKUP mode (ALTER DATABASE BEGIN BACKUP)? Why does it generate more redo?

    Answer:
    The following actions occur:
    DBWn (Database Writer): Writes all dirty blocks to disk as of a specific SCN (System Change Number).
    CKPT (Checkpoint Process): Stops updating the regular checkpoint SCN in datafile headers and instead updates the hot backup checkpoint SCN field.
    LGWR (Log Writer Process): Instead of logging only changes, it begins logging entire changed blocks when they are modified for the first time after the backup begins.
    This generates a large amount of redo, as complete block images are logged instead of just changes, ensuring data consistency during recovery.

  147. What is a Snapshot Control File?

    Answer:
    A Snapshot Control File is a temporary copy of the control file created at the beginning of an RMAN backup.
    It ensures a consistent point-in-time view of the control file while the backup is running.
    If a tablespace or file is added after the backup starts, it will not be included in that backup.

  148. What is the difference between the VALIDATE and CROSSCHECK commands?

    Answer:
    VALIDATE BACKUPSET: Verifies whether backup files are intact and can be restored.
    CROSSCHECK BACKUP: Checks if backup files are still available on disk or tape and updates RMAN metadata accordingly.

  149. What is the purpose of CONTROL_FILE_RECORD_KEEP_TIME?

    Answer:
    This parameter determines how long RMAN retains backup records in the control file before reusing them.
    If the value is too low, older backup records may be overwritten, affecting recovery operations.

  150. Should we enable CONFIGURE CONTROLFILE AUTOBACKUP? What is its significance? What is the default value?

    Answer:
    It should always be ON.
    When enabled, RMAN automatically backs up the control file and SPFILE after structural changes (e.g., adding a tablespace, datafile, or backup).
    The default value is OFF, but it is highly recommended to turn it ON for better recoverability.

  151. Can we restore a database from an obsolete backup?

    Answer: Yes, we can restore a database from an obsolete backup. For that, we need to catalog those files explicitly.

  152. How can we take RMAN backup in parallel?

    Answer: Yes, we can. Either by mentioning the number of channels or using the parallel parameter.

  153. What are the different types of retention policies? Explain about them.

    Answer:

    Recovery Window-Based Retention Policy:
    In this policy, Oracle checks the current backup and looks for its relevance backward in time.
    Example: CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
    This ensures that the database can be recovered to any point in the last 7 days.

    Redundancy-Based Retention Policy:
    Specifies how many backups of each datafile must be retained. Older backups beyond the specified count become obsolete.
    Example: CONFIGURE RETENTION POLICY TO REDUNDANCY 2;

    No Retention Policy:
    Means backups will never be obsolete.
    Example: CONFIGURE RETENTION POLICY TO NONE;

  154. What is an incarnation number?

    Answer: When we open the database in RESETLOGS mode, a new incarnation number is created. This resets the log sequence number to 1, and online redo logs get a new timestamp and SCN.

  155. Difference between Backup Piece and Backup Set?

    Answer:
    Backup Set: A logical structure where the backup is stored, containing one or multiple database files, SPFILEs, control files, etc.
    Backup Piece: A physical file within a backup set, stored in binary format.

  156. What can be done to improve RMAN backup performance?

    Answer:
    Use more channels
    Enable parallelism
    Use multi-section backups

  157. If an RMAN backup needs to complete within 40 minutes, can we enforce this?

    Answer: Yes, using the BACKUP DURATION command:

    BACKUP DURATION 00:40 DATABASE;
  158. What is an image copy in RMAN?

    Answer: Image copies are exact copies of datafiles, including free space. They are not stored in RMAN backup pieces but as actual datafiles, making them useful for moving a database from non-ASM to ASM.

  159. How can we recover from the loss of an online redo log?

    Answer: The recovery method depends on the status of the lost or corrupted redo log file. The appropriate steps should be taken based on its availability and whether it was archived.

  160. What is incomplete recovery? How does it work? What are the different scenarios?

    Answer: Incomplete recovery (or point-in-time recovery) is performed when we do not have all the required redo logs for a complete recovery. Scenarios where it is needed:

    1.Missing or damaged archived redo logs or online redo logs
    2.Intentional rollback to a specific point in time (e.g., recovering from an accidental table truncation)