Ashiwini
30 Jan, 2025
0 Comments
112 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.

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.
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)
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.
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.
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
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).
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)
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.
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.
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.
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.
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.
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.
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.
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
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
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
How to create password file?

Answer: $ orapwd file=orapwSID password=sys_password force=y nosysdba=y
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.
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.
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
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.
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.
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.
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.
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.
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
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.
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.
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.
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.
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.
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.
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.
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.
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.

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;

How do you check the Oracle Database version?

Answer:
```
SELECT * FROM v$version;
```
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.
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;
```

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';

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.
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.

How do you create a new Oracle user?

Answer:

CREATE USER username IDENTIFIED BY password;
GRANT CONNECT, RESOURCE TO username;

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.

What is the command to add a datafile to a tablespace?

Answer:

ALTER TABLESPACE tablespace_name ADD DATAFILE 'datafile_name' SIZE 5M;

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.
What is Disaster Recovery (DR)?

Answer: Disaster Recovery refers to the processes and technologies used to restore and maintain database operations after unexpected failures.
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.
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.
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;
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;
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
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.
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;
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);
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;
```
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.
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.
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.
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).
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.
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.
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.
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;

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`

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
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
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.
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
```
How to change the default tablespace for a user?
```
ALTER USER AISHU DEFAULT TABLESPACE 
```
How to give a tablespace quota to a user?
```
ALTER USER AISHU QUOTA UNLIMITED ON T;
```
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)
);
```
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.
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);
```
How to check if the instance is using SPFILE or PFILE?

Answer:
```
ALTER TABLE EMPLOYEE STORAGE (PCT_FREE 20)
```

How to create a password file?

Answer:

orapwd file=$ORACLE_HOME/dbs/pwaishu.ora entries=5 ignorecase=Y;

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
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
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.
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;
```

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;

How to resize a datafile?

Answer:
Ensure the datafile is auto-extensible before resizing.
ALTER DATABASE DATAFILE '/u02/oracle/rbdb1/stuff01.dbf' RESIZE 100M;

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';

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;
```
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;
```

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;

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
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.
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>;
```
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.
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;
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.
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.
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.
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.
Where do you check the free space of objects?

Answer:
Query the DBA_FREE_SPACE view:
```
sql
SELECT * FROM DBA_FREE_SPACE;
```

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;

Can you kill PMON, SMON, or CKPT processes?

Answer:
No, killing these mandatory background processes will crash the database.
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.
Startup and shutdown modes of Oracle database:

Answer:

Startup modes: NOMOUNT, MOUNT, OPEN.
Shutdown modes: CLOSE, DISMOUNT, SHUTDOWN.
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.
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.
Changing the database to archive log mode:

Answer: Bring the database to mount mode and use:
```
sql
ALTER DATABASE ARCHIVELOG;
```
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).
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.
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;
```
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.
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.

How to check ASM disk groups?

Answer:

SELECT name, state, total_mb, free_mb FROM v$asm_diskgroup;

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.
How to check the listener status?

Answer:
```
lsnrctl status
```
How to restart the listener?

Answer:
```
lsnrctl stop
lsnrctl start
```

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;

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;

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.
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.
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;
```
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.
How do you check the last SCN number?

Answer:
```
SELECT current_scn FROM v$database;
```
How to check database uptime?

Answer:
```
SELECT * FROM v$instance;
```
Look for the STARTUP_TIME column.

How do you check RMAN backup details?

Answer:

SELECT * FROM v$backup;

RMAN> LIST BACKUP SUMMARY;

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;
```

How do you find the database size?

Answer:

SELECT SUM(bytes)/1024/1024/1024 AS size_in_GB FROM dba_data_files;

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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
How can we take RMAN backup in parallel?

Answer: Yes, we can. Either by mentioning the number of channels or using the parallel parameter.
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;
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.
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.
What can be done to improve RMAN backup performance?

Answer:
Use more channels
Enable parallelism
Use multi-section backups
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;
```
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.
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.
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)
What is the difference between opatch apply and opatchauto?

Answer:
opatch apply: Used for manually applying patches to Oracle software. You need to specify the patch location and control the process manually.
opatchauto: Automates the patching process, simplifying patch application across multiple components. It’s especially useful for Oracle Grid Infrastructure (GI) and database homes.
What is the difference between opatch apply and opatch napply?

Answer:
opatch apply: Applies patches and tracks them in the Oracle Inventory. A patch applied once won’t be reapplied.
opatch napply: Used to apply multiple patches in a batch, avoiding reapplying already installed patches. It is useful when applying multiple patches at once.
Should we patch the standby database first or the primary? Can we patch both together?

Answer:
Best practice is to patch the standby database first to validate the patch before applying it to the production system.
Patching both together is possible but increases risk and complexity.
Should we patch the Grid Infrastructure (GI) home first or the Database (DB) home first?

Answer:

Best practice: Patch GI home first, as it provides services to the database. Patching the database home first can lead to compatibility issues.
What happens if we patch the primary database but not the standby?

Answer:
It may lead to compatibility and synchronization issues between the two databases, potentially causing replication failures.
What are Rolling, Non-Rolling, and Hybrid Patching in Oracle RAC?

Answer:
Rolling Patching: Nodes are patched one at a time while the cluster remains online, minimizing downtime.
Non-Rolling Patching: The entire cluster is taken offline, and all nodes are patched simultaneously.
Hybrid Patching: A mix of both, where some nodes are patched in a rolling manner, while others are patched together.
What is a conflict check in patching?

Answer:
A conflict check ensures that the patch being applied does not interfere with existing patches, dependencies, or software versions.
What is the difference between Central Inventory and Local Inventory?

Answer:
Central Inventory: A global repository that tracks all Oracle installations on a server.
Local Inventory: Specific to a single Oracle home and contains patching details for that installation.
How to find the locations of Central and Local Inventory?

Answer:
Central Inventory: Located in the oraInst.loc file (/etc/oraInst.loc on Linux or C:\Program Files\Oracle\Inventory on Windows).
Local Inventory: Found inside $ORACLE_HOME/inventory.
What happens if Central or Local Inventory is deleted?

Answer:
Central Inventory Loss: Oracle installations on the server become difficult to manage, patch, or upgrade.
Local Inventory Loss: Patching operations for that Oracle home may fail, but the database may still function.
What is .patch_storage under $ORACLE_HOME, and why is it needed?

Answer:
It stores patch history, rollback information, and metadata related to applied patches.
What happens if .patch_storage is deleted?

Answer:
Patch history will be lost, making future patch management difficult. However, the database itself remains unaffected.
How to restore .patch_storage if deleted?

Answer:
Restore from backup if available. Otherwise, reapply the patches to regenerate the necessary files.
What are RMAN optimization parameters?

Answer:

RMAN offers parameters like BACKUP OPTIMIZATION, COMPRESSION, and PARALLELISM to improve backup efficiency.
How can RMAN backup performance be improved?

Answer:

Using multiple channels.

Enabling Block Change Tracking (BCT).

Applying compression techniques.

Using optimized storage solutions.

Maintaining RMAN metadata properly.
What are RMAN backup pieces, backup sets, and image copies?

Answer:
Backup Piece: A backup piece is a physical file created by RMAN that stores the actual data from the database. It is an essential component of a backup set.

Backup Set: A backup set is a collection of backup pieces that RMAN groups together to efficiently store and manage backups. It is the preferred method for backup operations.

Image Copy: An image copy is an exact duplicate of a database file created at the operating system level. It provides an alternative backup method, often used for fast recovery.
Advantages and Disadvantages of Backup Sets and Image Copies

Answer:

Backup Sets:

Advantages: Efficient in terms of storage, supports compression, and improves backup and restore performance.

Disadvantages: Takes more time to create and restore compared to image copies.

Image Copies:

Advantages: Faster restoration since files can be directly copied back to their original location.

Disadvantages: Requires more storage space and is generally slower for backup operations compared to backup sets.
Can RMAN backups be taken in NOARCHIVELOG mode?

Answer:

Yes, RMAN backups can be performed in NOARCHIVELOG mode, but you will not be able to recover the database to a specific point in time since redo logs are not archived.
Can RMAN backups be taken when the database is in MOUNT mode?

Answer:

Yes, RMAN backups can be taken in the MOUNT state. This mode is commonly used for consistent backups, especially for whole database backups.
Can RMAN backups be taken from a standby database?

Answer:

Yes, RMAN backups can be taken from a standby database, which helps in offloading backup operations from the primary database.
What is Block Change Tracking (BCT) in RMAN, and why is it needed?

Answer:

BCT is a feature that tracks modified database blocks, making incremental backups faster by identifying only changed blocks since the last backup, thus reducing backup time and resource consumption.
Can BCT be enabled on a standby database?

Answer:

Yes, enabling BCT on a standby database improves incremental backup performance, reducing the impact on the primary database.
What is the difference between an RMAN target database and an RMAN auxiliary database?

Answer:

Target Database: The primary database for which backup, restore, and recovery operations are performed.

Auxiliary Database: A separate database used in operations like duplication or cloning.
What is the difference between an RMAN channel and an RMAN auxiliary channel?

Answer:

RMAN Channel: A session between RMAN and a target/auxiliary database, used for parallel backup and restore operations.

Auxiliary Channel: Specifically used in auxiliary database operations like duplication.
Can RMAN backups be taken from a standby database?

Answer:

Yes, this method helps distribute the backup workload and enhances data availability.
Can a backup taken from a standby database be used to restore the primary database?

Answer:

Yes, RMAN allows the restoration and recovery of a primary database using backups from a standby database.
Can BCT be enabled on a standby database?

Answer:

Yes, enabling BCT improves incremental backup efficiency for the standby database.
What is the difference between ‘rman target /’ and ‘rman auxiliary /’?

Answer:

rman target /: Connects RMAN to the target database for backup and recovery.

rman auxiliary /: Connects RMAN to an auxiliary database for duplication and related tasks.
What is RMAN active database duplication?

Answer:

It is a method of creating a duplicate database by copying data directly from the primary database while it remains online.
Comparison: RMAN Active Database Duplicate vs. RMAN Duplicate from Backup

Answer:

Active Database Duplicate:

Pros: Faster duplication, minimal downtime, no need for prior backups.

Cons: Puts additional load on the source database.

Duplicate from Backup:

Pros: Uses existing backups, reducing the impact on the source database.

Cons: Requires pre-existing backups, making it slower.
What happens to an ongoing RMAN backup if a new datafile or tablespace is added?

Answer:

The newly added datafile or tablespace will not be included in the current backup. It will be backed up in subsequent backup runs.
What happens if a datafile or tablespace is dropped during an RMAN backup?

Answer:

The backup may face inconsistencies or errors if RMAN tries to access a missing file.
What happens if a datafile is moved or renamed while an RMAN backup is running?

Answer:

The backup process may fail or produce errors if it attempts to access the original file location.
To create a Guaranteed Restore Point (GRP), must Flashback Database be enabled?

Answer: Yes, Flashback Database must be enabled before you can create a Guaranteed Restore Point.
To enable Flashback, does the database need to be stopped?

Answer: No, the database does not need to be stopped. Flashback Database can be enabled while the database is running.
How many Guaranteed Restore Points (GRPs) can I create on a database?

Answer: The number of GRPs you can create depends on the available space in the UNDO tablespace and the retention policy you have configured.
How many times can I flashback my database?

Answer: There is no fixed limit on the number of times you can perform Flashback Database. However, it depends on the availability of the required undo data and flashback logs.
After flashing back the database, are the latest table statistics reverted, or do they remain persistent?

Answer: Flashback Database does not revert table statistics. The statistics remain as they were at the time of the Flashback operation.
After changing the compatibility parameter, can I still flashback the database?

Answer: Yes, you can still perform a Flashback Database after modifying the compatibility parameter. However, changing compatibility might impact certain database features.
After opening the database with RESETLOGS, can I still flashback the database?

Answer: No, once the database is opened with the RESETLOGS option, you cannot flashback to a point before the resetlogs operation.
Can I perform Flashback Database using SQL*Plus or RMAN?

Answer: Yes, Flashback Database can be performed using both the SQL*Plus command prompt and the RMAN command prompt.
What are the differences between Flashback Logs and Archive Logs?

Answer:
Flashback Logs: Store historical data required for Flashback Database operations.
Archive Logs: Used for point-in-time recovery, media recovery, and data protection.
Where are Flashback Logs stored?

Answer: Flashback logs are stored in the Fast Recovery Area (FRA) under the directory:
flash_recovery_area/DB_UNIQUE_NAME/flashback.
Can I change the location of Flashback Logs to a custom directory?

Answer: No, Flashback logs are automatically managed in the FRA, and their location cannot be customized.
When are Flashback Logs generated?

Answer: Flashback Logs are generated automatically as the database changes, capturing historical data needed for Flashback Database operations.
What is the purpose of Flashback Logs in the database?

Answer: Flashback Logs store historical information that allows the database to be reverted to a previous state, helping in quick recovery without requiring traditional backups.
Can we create a Restore Point inside a Pluggable Database (PDB)?

Answer: Yes, Restore Points can be created inside a Pluggable Database (PDB).
Can we flashback a Pluggable Database (PDB) to a restore point in Oracle 12cR1 and 12cR2?

Answer:
In Oracle 12c Release 1 (12cR1): Flashback of an individual PDB to a restore point is not supported.
In Oracle 12c Release 2 (12cR2): You can flashback a PDB to a restore point independently.
How would you perform database restore/refresh?

Answer:
Performing a database restore/refresh in Oracle typically involves restoring the database to a previous backup or refreshing it with data from another source. The process depends on whether you’re restoring from a backup or performing a database refresh using export/import methods or RMAN duplication. Here’s an outline of both methods:

1. Restore Using RMAN (Recovery Manager):

This method is used when you want to restore the database from RMAN backups due to failure or data corruption.

Steps to perform a restore:

Prepare for Restore:
Ensure the Oracle instance is in MOUNT state if it’s not already.
```
shutdown immediate; startup mount;
```
Check Backup Availability: Use the following RMAN command to list the available backups:
```
RMAN> list backup;
```
Restore the Control File (If Needed): If the control file is missing or corrupted, restore it using:
```
RMAN> restore controlfile from autobackup;
```
Restore the Database: If you need to restore the entire database, use the following command:
```
RMAN> restore database;
```
Recover the Database: After restoring, apply any necessary archived redo logs to bring the database to a consistent state:
```
RMAN> recover database;
```
Open the Database: After recovery, open the database:
```
alter database open;
```
2. Database Refresh Using RMAN DUPLICATE:

This method is used to refresh a database from a source database (e.g., a production database) to a target (e.g., a test or development database).

Steps to perform a refresh:

Connect to RMAN: Connect to both the target (the database to be refreshed) and the auxiliary (the new database) using RMAN.

Ensure the auxiliary database is in NOMOUNT state.

Prepare the Auxiliary Instance:
```
startup nomount;
```
Duplicate the Database: Use the following RMAN command to duplicate the database:
```
RMAN> duplicate target database to newdb from active database;
```
3. Refresh Using Data Pump (EXPDP/IMPDP):

Data Pump is another common method to refresh a database by exporting and importing schemas or entire databases.

Steps to perform a refresh using Data Pump:

Export the Data (EXPDP): On the source database (e.g., production), export the data using the expdp utility:
```
expdp system/password@prod full=y directory=exp_dir dumpfile=full_exp.dmp logfile=expdp.log
```
Transfer the Dump File: Copy the dump file (full_exp.dmp) to the destination (target) database server.

Import the Data (IMPDP): On the target database (e.g., development or test), import the dump file:
```
impdp system/password@dev full=y directory=exp_dir dumpfile=full_exp.dmp logfile=impdp.log
```
This will refresh the target database with the data from the source database.
What MRP process do?

Answer:
In Oracle Data Guard, MRP (Managed Recovery Process) is responsible for applying archived and redo logs to a physical standby database to keep it synchronized with the primary database.
Steps to Create Oracle Standby Database

Answer:
Prerequisites
1. Primary Database: Ensure the primary database is in ARCHIVELOG mode and has FORCE LOGGING enabled.
2. Network Configuration: Ensure proper network connectivity between the primary and standby sites.
3. Oracle Software: Install the same version of Oracle software on the standby server.
4. Storage: Ensure sufficient storage is available on the standby server.
Steps to Create a Standby Database

1. Enable Archiving and Force Logging on the Primary Database
```
-- Connect to the primary database as SYSDBA
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> ALTER DATABASE ARCHIVELOG;
SQL> ALTER DATABASE OPEN;
SQL> ALTER DATABASE FORCE LOGGING;
```
2. Create a Password File on the Primary Database

Ensure the primary database has a password file. If not, create one:
```
orapwd file=$ORACLE_HOME/dbs/orapw<SID> password=<password> entries=10
```
3. Configure the Primary Database for Data Guard

Add the following parameters to the primary database’s spfile or pfile:
```
ALTER SYSTEM SET LOG_ARCHIVE_CONFIG='DG_CONFIG=(primary_db,standby_db)';
ALTER SYSTEM SET LOG_ARCHIVE_DEST_2='SERVICE=standby_db VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=standby_db';
ALTER SYSTEM SET FAL_SERVER=standby_db;
ALTER SYSTEM SET FAL_CLIENT=primary_db;
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO;
```
4. Create a Backup of the Primary Database

Use RMAN to create a backup of the primary database:
```
rman target /
RMAN> BACKUP DATABASE PLUS ARCHIVELOG;
```
5. Copy the Backup to the Standby Server

Transfer the backup files to the standby server using scp, rsync, or any other file transfer method.

6. Create an Initialization Parameter File for the Standby Database

Create a pfile for the standby database:
```
CREATE PFILE='/path/to/standby_init.ora' FROM SPFILE;
```
Edit the pfile to reflect the standby database’s settings
Why you became DBA and didn’t go into coding?

Answer:

I have always been interested in database management, data security, and ensuring high availability of critical systems. While I did explore coding, I found that my strengths and passion lie in managing databases, optimizing performance, and ensuring data integrity. The role of a DBA is crucial in maintaining the backbone of any application, and I enjoy the challenge of troubleshooting issues, managing backups and recovery, and working on high-availability solutions like Oracle RAC and Data Guard.

Additionally, I like the combination of administration, problem-solving, and automation that a DBA role offers. While coding is an essential skill, I prefer scripting for automation rather than full-time application development. This balance makes the DBA role a perfect fit for me.
As a DBA what are your day to day activities?

Answer:
Maintaing databases for the oracle based applications.
Monitoring and healthcheck of all the DB servers.
Backup and Recovery Management
User and Security Management
Applying critical patch updates (CPUs) and PSU patches.
Performance Tuning of the DB servers and instances.
Creating scripts for automating the DB procedure.
Perform backup and DB restoration activity.
Running SQL scripts given by the developers for the incident and change management.
Troubleshooting the DB for production issues.
Preparing audit reports for compliance requirements.
You get the alert and tickest from OEM so your OEM is integrated to Service Now or not?How it is?

Answer:
Yes, Oracle Enterprise Manager (OEM) can be integrated with ServiceNow to automate incident management. The integration ensures that alerts generated in OEM automatically create tickets in ServiceNow, reducing manual effort and improving response time.
OEM Alerts and Tickets
OEM provides alerts and tickets for various database-related events, such as:

– Performance issues
– Space utilization
– Security vulnerabilities
– Configuration changes
Integration with ServiceNow
To integrate OEM with ServiceNow, we can use the OEM’s built-in integration capabilities or third-party plugins

Example of OEM-ServiceNow Integration

1. OEM detects a performance issue with a database instance and generates an alert.
2. The OEM-ServiceNow plugin sends the alert details to ServiceNow, which creates a new incident.
3. The incident is assigned to the database team, which investigates and resolves the issue.
4. Once the issue is resolved, the incident is closed in ServiceNow, and the resolution details are synced back to OEM.

By integrating OEM with ServiceNow, we can streamline incident management, improve collaboration, and enhance reporting and analytics capabilities, ultimately leading to better database operations and improved service quality.
That OEM server do you manage is there any other team that manages OEM?

Answer:
If you do not manage the OEM server
While I don’t directly manage the OEM server’s infrastructure, I do configure event rules, set up alert notifications, and integrate it with tools like ServiceNow. If there are any issues with OEM itself—such as agent communication failures, repository database issues, or performance slowdowns—I coordinate with the respective team for resolution.

If you do manage the OEM server, you can modify the response:

Yes, I manage the OEM server along with the DBA team. We are responsible for:

Installing and configuring Oracle Enterprise Manager
Managing the repository database
Deploying and troubleshooting OEM agents on target servers
Configuring event rules, alert thresholds, and integrations (e.g., ServiceNow)
Upgrading and patching the OEM environment when needed
How you create the alert in OEM?How do you define that alert in OEM?

Answer:
How to Create an Alert in Oracle Enterprise Manager (OEM)
1. Navigate to Event Rules in OEM
Login to OEM Cloud Control using your credentials.
Go to Enterprise → Monitoring → Incident Rules.
Click on Create Rule Set to define a new alerting rule.
2. Define the Alert Criteria
Select Target Type (e.g., Database, Listener, ASM, Host).
Choose Specific Metrics (e.g., Tablespace Usage, CPU Utilization, Database Down).
Set Thresholds for Warning and Critical levels:
Warning: 85% tablespace full
Critical: 95% tablespace full
3. Configure Notifications
Specify Actions when an alert is triggered:
Send an email notification to DBAs.
Trigger an SNMP trap for ServiceNow integration.
Execute a custom script (e.g., auto-purge logs if space issue).
4. Save and Enable the Rule
Review the rule configuration.
Click Save and Enable.
The rule will now trigger alerts when the specified conditions are met.

How Do You Define an Alert?
Alerts are defined based on threshold values for specific metrics in OEM.
You can customize alerts for database, storage, OS, or network conditions.
Incident Rules help automate responses (email, ServiceNow, scripts).

In OEM, I create alerts by defining Event Rules and configuring Metric Thresholds for key parameters like CPU usage, tablespace utilization, and database availability. I ensure that alerts trigger email notifications, SNMP traps, or automation scripts. This helps in proactive database monitoring and issue resolution.
Is there any rule or so that you should define so that alert will trigger for taht particular DB?

Answer:
Yes, in OEM, I define an Incident Rule Set and associate it with a specific database target. Within this rule, I configure metric alerts for key parameters like availability, performance, and storage. When a threshold is crossed, the rule triggers an incident and sends notifications via email or ServiceNow integration.
You are doing Db patching or Grid Patching?

Answer:
If You Handle Both DB and Grid Patching:

Yes, I handle both Database patching and Grid Infrastructure patching in our environment.
- For Database patching, I apply Quarterly Patch Set Updates (PSU) or Release Updates (RU) using OPatch or DBMS_ROLLING (for Data Guard setups).
- For Grid Infrastructure patching, I use Rolling Patching to minimize downtime, applying patches on one node at a time in an Oracle RAC setup.
- I ensure proper pre-patch checks, take backups, and validate post-patch health using opatch lsinventory, srvctl, crsctl, and log verification.
  If You Only Handle Database Patching:
  
  I mainly handle Database patching, where I apply RU/PSU patches using OPatch. Before patching, I perform pre-checks, backup verification, and post-patch validations to ensure smooth deployment. If there are Grid patches required, I coordinate with the team managing Grid Infrastructure.
  
  If You Only Handle Grid Patching:
  
  I focus on Grid Infrastructure patching, ensuring a smooth rolling upgrade process in an Oracle RAC environment. I patch one node at a time to minimize downtime and ensure cluster stability. I use tools like opatchauto, crsctl, srvctl, and run pre-checks before applying patches.
Did you perform grid patching?-I said No

Answer:
I haven’t directly performed grid patching, but I have extensive experience with database patching, including applying Oracle Database Release Updates and SQL Server Cumulative Updates. I understand that grid patching involves updating the clusterware and infrastructure components, and I’ve collaborated with infrastructure teams during these cycles to ensure compatibility and smooth operations. I’m confident in my ability to learn and adapt to grid patching if required, and I’m always eager to expand my skill set.
How do you perform DB patching? step by step

Answer:
Pre-checks: Verify existing patch level, active sessions, and take a backup.
Shutdown Database & Listener: Ensure services are stopped before patching.
Apply the Patch: Use OPatch or OPatchAuto in RAC.
Post-checks: Start the database, run Datapatch, and validate system health.
Monitor for Issues: Check logs and verify performance after patching
Which backup is configured in your system? is it export or RMAN?

Answer:

✅ If RMAN is used:
We use RMAN for database backups, as it provides block-level backups, incremental backups, and automated recovery options. It also integrates well with Oracle features like Data Guard and ASM.

✅ If Export (Data Pump) is used:
We use Data Pump export for logical backups, mainly for schema-level, table-level, or full database exports. However, this is not a complete database backup solution since it does not capture physical structures like RMAN.
Do you have stats gathering scheduled or taking on requirement basis?

Answer:

In our environment, we primarily rely on automatic statistics gathering, which is scheduled by Oracle’s AutoTask framework. The GATHER_STATS_JOB runs during the maintenance window and collects optimizer statistics for objects with stale or missing stats.

However, in some cases, we also perform manual statistics gathering based on business requirements, especially for:

High DML activity tables where stale statistics can impact performance.
Partitioned tables where incremental stats collection is beneficial.
Large data loads (ETL processes) where we gather stats after bulk inserts for better query optimization.
Specific performance tuning cases where histograms or extended statistics are needed.
Suppose there is one query which was running fine till yesterday but suddenly it started to run longer, how you will resolve that issue?

Answer:
Check Execution Plan – Compare the current plan with the previous one (DBA_HIST_SQL_PLAN). If it’s using a bad plan, enforce the old one using SQL Plan Baseline or SQL Profile.Check Statistics – Verify if table/index stats were updated recently (DBA_TAB_STATISTICS). If needed, restore previous stats.
Check Bind Variables – Ensure bind variable peeking isn’t causing plan changes.
Check Wait Events & Resource Contention – Analyze ASH, AWR, and V$SESSION_WAIT for high waits, CPU, or I/O issues.
Check Indexes – Ensure indexes are valid and being used correctly.
Check Database Changes – Look for recent optimizer settings or parameter modifications.
You have to take one table backup and you have to import it to other db but that table already exist in that system how will you export that table?

Answer:
1. Export the table: Use the Data Pump Export (EXPDP) utility to export the table from the source database.
```
expdp username/password@source_db directory=dpump_dir dumpfile=table_export.dmp tables=table_name
```
2. Import the table: Use the Data Pump Import (IMPDP) utility to import the table into the target database, using the REPLACE option to overwrite the existing table.
```
impdp username/password@target_db directory=dpump_dir dumpfile=table_export.dmp tables=table_name replace
```
Suppose if the plan is also same then how you will resolve that issue?

Answer:
If it’s resource contention, optimize system usage.
If it’s index fragmentation, rebuild indexes.
If it’s wait events, resolve blocking or storage bottlenecks.
If it’s bad statistics, regenerate them properly
When a select query runs in db how it fectches the data?

Answer:
When a SELECT query is executed:

1. Parser: Parses the query and generates an abstract syntax tree (AST).
2. Query Optimizer: Analyzes the query and generates an optimal execution plan.
3. Execution Engine: Executes the plan, retrieving data from storage or memory.
4. Data Processing: Performs aggregations, transformations, and generates the result set.
5. Result Set Return: Returns the result set to the client application.

Key architecture components:

– Parser
– Query Optimizer
– Execution Engine
– Data Retrieval
– Data Processing
– Result Set Generator
– Storage
If a user is firing the update query in db how the process happens?

Answer:
Architecture Components
The following architecture components are involved in the update query process:

1. Parser: Parses the query and generates an abstract syntax tree (AST).
2. Query Optimizer: Analyzes the query and generates an optimal execution plan.
3. Execution Engine: Executes the query plan, performing the update operation.
4. Lock Manager: Manages concurrency control, acquiring locks on rows.
5. Transaction Manager: Manages the transaction, ensuring atomicity and consistency.
6. Log Manager: Logs the update operation, ensuring recoverability.
7. Recovery Manager: Ensures that the update is recoverable in case of a failure.
8. Data Storage: Stores the updated data.
Suppoese your are firing select query to fetch a row and at the same time some job is running and deleted that row but they haven’t comitted or rolled back the data , so for your select what result you will find?

Answer:
When a SELECT query is issued while a job is running and deleting a row, but the transaction hasn’t yet committed or rolled back, the result you get can depend on a few factors.

– Undo Segments: Oracle uses undo segments to maintain a consistent view of the data for active transactions. Even though a row is marked for deletion by the transaction that hasn’t committed, the undo segments will still store the previous version of the row (the version before the delete).
– Consistent Read: Oracle ensures a consistent read for a SELECT query. So, even though the delete operation is in progress, the query will fetch the row as it was at the time the query started, not as it is at the time of the commit.

– Your SELECT query would return the row as it existed before the deletion because Oracle would use the undo information to provide a consistent view of the data.
– You would not see the row as deleted until the transaction is committed.
Suppose that data is not there in undo block what will happen?

Answer:
If the undo information for the deleted row has been overwritten or is no longer available, Oracle will not be able to return the row. The transaction’s consistent view will be lost, and either the query will return no data or the system might raise an ORA-01555 error.
What are the steps you follow the software and the db from scratch on linux?

Answer:
General Steps to Set Up an Oracle Database on Linux (from Scratch):

Step 1: Prepare the Linux Server
1. Ensure Linux System is Up to Date:
bash
sudo apt update && sudo apt upgrade -y # For Ubuntu/Debian
sudo yum update -y # For RHEL/CentOS

2. Install Dependencies:
Install the required packages for Oracle installation.
bash
sudo yum install -y oracle-database-preinstall-19c # For Oracle 19c

3. Set Hostname and Configure Network:
Make sure your system’s hostname is set appropriately.
bash
sudo hostnamectl set-hostname your-db-hostname

4. Ensure proper firewall configuration:
You need to open ports for Oracle’s listener and other services.
“`bash
sudo firewall-cmd –zone=public –add-port=1521/tcp –permanent

Step 2: *Install Oracle Database Software*

Step 3: *Create and Configure the Oracle Database*

Step 4: *Verify the Database Installation*

Step 5: Configure Oracle Database for Production
What kernal parameters you set before running ./runInstaller?

Answer:
– Setting Kernel Parameters: It adjusts various kernel parameters in /etc/sysctl.conf to optimize the system for Oracle Database performance.
– Setting Resource Limits: It modifies /etc/security/limits.conf to configure limits for the Oracle user (e.g., file descriptors, processes).
– Configuring Groups and Users: It creates the oracle user and relevant groups (e.g., oinstall, dba).
– Disk Partition and Directory Creation: It sets up Oracle directories and ensures permissions are correctly set.
– Enabling Required Services: It prepares the system by configuring necessary services like swap, shared memory, and file system.
Pre-Installation Package Actions:

1. Installing the Pre-Installation Package (RHEL/CentOS/Oracle Linux):

bash
sudo yum install -y oracle-database-preinstall-19c
This package will automatically adjust various system settings, and you won’t need to manually adjust kernel parameters unless you want to customize them further.

2. The Pre-Installation Package Automatically Configures Kernel Parameters
Do you know about limits.configuration ? — i didn’t knew

Answer:
I wasn’t familiar with limits.conf specifically, but I understand it’s used to configure user-level resource limits on Linux systems, especially for applications like Oracle. I’ve worked with similar system resource configurations, such as adjusting ulimit settings for performance tuning. I’m always eager to learn more about Linux administration and would certainly dive into the documentation and practical resources to become more familiar with limits.conf if I encountered it in a production environment.
Did you upgrade database? what version to what version you did upgradation?

Answer:
Yes, I have experience upgrading an Oracle database from 12c to 19c in my previous role. The process included several key steps and considerations to ensure a smooth transition:
1. Pre-Upgrade Information Gathering: The first step I would take is to run Oracle’s Pre-Upgrade Information Tool to identify any deprecated features and check for compatibility issues. The tool will highlight any features that are no longer supported in 19c, such as certain initialization parameters or Oracle options.

2. Backups: As with any upgrade, taking a full backup is crucial. Oracle’s RMAN (Recovery Manager) would be used to create a backup of the entire database and ensure recovery in case something goes wrong.

3. Review of Deprecated Features: Oracle 19c comes with some deprecated features that need to be checked, including changes to Oracle RAC and multitenant architecture. It’s important to review and either address or replace these features during the upgrade.

4. Database Upgrade Using DBUA: The Database Upgrade Assistant (DBUA) is Oracle’s recommended tool for performing an upgrade. It automates the process, performs checks, and handles the database schema changes. DBUA can also handle patching and required configurations for new features, ensuring a smoother upgrade.
5.Manual Steps After DBUA: After using DBUA, it’s important to manually address certain tasks, such as gathering statistics and verifying application compatibility. If necessary, manual intervention may be required for adjusting configurations for performance tuning or handling deprecated parameters.

6. Post-Upgrade Validation: Once the upgrade is complete, testing and validation are essential. I would ensure that all applications and queries are functioning as expected and check the performance of the database after the upgrade, focusing on any bottlenecks or regressions.

7. New Features in 19c: After the upgrade, I would explore the new features in 19c, such as improvements in in-memory processing, automatic indexing, pdb enhancements, and optimizations in SQL performance. It would also be a good idea to review Data Guard settings and ensure everything is properly synchronized post-upgrade.

8. Documentation and Cleanup: Finally, I’d ensure that any deprecated initialization parameters are removed, and I would update configuration settings according to the best practices outlined in the Oracle 19c documentation.
Can you explain the steps of upgradation?

Answer:
I. Backup your database.
II. Empty recycle bin & gather stats.
III. Run oracle’s database pre-upgrade utility.
IV. Install the oracle’s 19c database s/w.
V. Run the DBUA Utility.
VI. Run post-upgrade script & restart database.
What kind of isses you face on daily basis performance isses any kind of errors or what kind of complaints you get on daily basis from users?

Answer:
1. Acknowledge the Common Types of Issues:
In my daily work, I often come across a variety of issues. These can include performance-related issues, user errors, system failures, or complaints regarding usability or functionality. Depending on the type of issue, I address it by first understanding its root cause and then implementing a suitable solution.
2. Performance Issues:
One of the most common performance-related issues I deal with is ensuring that applications or systems run efficiently. This could include slow response times, high resource usage, or system overloads. For example, if users complain about a website’s slow loading speed, I typically analyze server performance, database queries, and optimize the code to improve performance.

f the issue requires a deeper technical fix or if it’s something beyond my expertise, I collaborate with the development team or escalate it to higher levels of support to ensure timely resolution. I’m always focused on resolving issues efficiently and keeping communication open with users to maintain a positive experience.
Your data guard is on same location or different

Answer:
In a typical *disaster recovery setup, I would configure Oracle Data Guard to have the Primary and Standby databases located in different cities or geographic regions. This setup helps ensure that even if there’s a localized disaster (like a natural disaster, power outage, or network failure) affecting one location, the other location can take over, minimizing downtime and preventing data loss.
What kind of issues you face in DataGuard?

Answer:
1. Network Issues: Connectivity problems between primary and standby databases.
2. Redo Log Transmission: Delays or failures in transmitting redo logs from primary to standby.
3. Apply Lag: Delay in applying redo logs on the standby database.
4. Switchover/Failover Issues: Problems during switchover or failover operations.
5. Archive Log Management: Issues with archive log management, such as running out of space.
6. Standby Database Corruption: Corruption on the standby database, requiring repair or rebuild.
7. Configuration Issues: Misconfiguration of Data Guard parameters or settings.
8. Performance Issues: Performance problems on the primary or standby database.
What kind of reasons you found for archive logs not shipping from primary to standby db?

Answer:
Here are the reasons:
1. Network fluctuations: Temporary network connectivity issues or packet losses.
2. High archive log generation: Large volume of archive logs being generated on the primary database.
How will you resolve the sync issues?

Answer:
1. Check network connectivity: Verify network stability and configure network settings for optimal performance.
2. Adjust archive log transmission parameters: Modify parameters like LOG_ARCHIVE_MAX_PROCESSES, LOG_ARCHIVE_DEST_1, and LOG_ARCHIVE_DEST_2 to optimize archive log transmission.
3. Clear archive log backlog: Manually delete or move archive logs from the primary database to clear the backlog.
4. Re-sync standby database: Use Oracle’s built-in re-sync feature or manually re-create the standby database to re-establish synchronization.
5. Monitor and adjust: Continuously monitor the sync process and adjust parameters as needed to maintain optimal performance.
If the archive log files are more than 300 or 400 what you will do to sync?

— We can take the incremental backup on primary and apply it to DR to solve the sync issue
Answer:
If archive log files exceed 300-400, to sync the primary and standby databases:

Take Incremental Backup on Primary and Apply to DR

1. Take an incremental backup on the primary database.
2. Transfer the backup to the DR site.
3. Apply the incremental backup to the standby database.

This approach helps to:

– Reduce the archive log gap between primary and standby.
– Re-sync the standby database with the primary.
– Avoid manual deletion of archive logs.

By taking an incremental backup and applying it to the DR site, you can efficiently re-sync the databases and resolve the sync issue.
For Db refresh what kind of process you are following and what refresh you are doing(export or RMAN) full db or schema refresh?

Answer:
Process
1. Pre-refresh checks: Verify database consistency, backup availability, and downtime requirements.
2. Export/RMAN backup: Create a full database export or RMAN backup of the source database.
3. Transfer backup: Transfer the backup to the target database server.
4. Restore and recover: Restore the database from the backup and recover to the desired point in time.
5. Post-refresh checks: Verify database integrity, consistency, and functionality.
Refresh Types
1. Full database refresh: Complete replacement of the target database with the source database.
2. Schema refresh: Refresh of specific schemas or objects within the database.
Tools Used
1. RMAN (Recovery Manager)
2. Data Pump Export/Impor
How you start and stop the databse in RAC?

Answer:
Start Database
1. CRS start: Start the Oracle Clusterware (CRS) service using crsctl start crs
2. Database start: Start the database instance using srvctl start database -d <database_name>
3. ASM start: Start the Automatic Storage Management (ASM) instance using srvctl start asm -n <node_name>

Stop Database
1. Database stop: Stop the database instance using srvctl stop database -d <database_name>
2. ASM stop: Stop the ASM instance using srvctl stop asm -n <node_name>
3. CRS stop: Stop the CRS service using crsctl stop crs
In multinent architecture you have number of PDB’s i want to connect to a particular single PDB how do you do that using sql developer?

Answer:
Step 1: Create a new connection
1. Open SQL Developer and click on “New Connection” (or press Ctrl+N).
2. Select “Oracle” as the connection type.

Step 2: Enter connection details
1. Enter the hostname, port, and service name of the CDB (Container Database).
2. Enter the username and password for the CDB.

Step 3: Specify the PDB
1. In the “Connection” tab, click on the “Advanced” button.
2. In the “Advanced” window, select the “Oracle Multitenant” tab.
3. Enter the name of the PDB you want to connect to in the “Pluggable Database” field.

Step 4: Connect to the PDB
1. Click “Connect” to establish the connection.
2. You should now be connected to the specified PDB.

Alternatively, if you are already connected to CDB, you can switch to a specific PDB using:

sql

ALTER SESSION SET CONTAINER = PDB1;
Where do you define the service name in daatabase?

Answer:
The service name for a PDB is defined in the database and registered with the listener. It is specified in the listener.ora file or dynamically registered with the listener using the LOCAL_LISTENER or REMOTE_LISTENER parameters.
If i want to give user a grant on 2 or 3 data dictionary tables how do you provide grant?

Answer:
GRANT SELECT_CATALOG_ROLE TO username;
The SELECT_CATALOG_ROLE role provides read-only access to most data dictionary tables, including the ones you mentioned.
How you will give full database read only access to user?

Answer:
To give full database read-only access to a user:
```
GRANT SELECT ANY TABLE TO username;
```
Alternatively, you can also grant the SELECT_CATALOG_ROLE and READ_ONLY roles:
```
GRANT SELECT_CATALOG_ROLE TO username;
GRANT READ_ONLY TO username;
```
If database is slow what you will check? what could be the reasons?

Answer:
If a database is slow, here are some key areas to check:
1. Check the Database Sessions and Wait Events
2. Check CPU and Memory Usage
3. Check Disk I/O Performance
4. Check Running SQL Queries
5. Check Index Usage
6. Check Redo Log and Archive Log Activity
7. Check Background Processes and Alerts
8. Check Database Parameters
9. Check Network Latency (For RAC or Distributed Databases)
10. Check Blocking Locks
Some common reasons for slow database performance include:

– Inefficient SQL queries
– Insufficient indexing
– Poor database configuration
– Disk I/O bottlenecks
– Memory constraints
– Network congestion
– Lock contention and deadlocks
– Pending database maintenance tasks
RMAN backup you are taking on daily basis its a cron job or is there any third party tool that you are using?

Answer:
We use a combination of both:

Oracle’s built-in scheduling tool: DBMS_SCHEDULER
We schedule RMAN backups using DBMS_SCHEDULER, which is a built-in Oracle tool for scheduling jobs. This allows us to manage and monitor backups directly within the Oracle database.

Cron job for additional automation
We also use a cron job to automate the backup process, which calls the RMAN script to perform the backup. This provides an additional layer of automation and ensures that backups are executed consistently.

Third-party tools for monitoring and management
While not directly used for scheduling backups, we utilize third-party tools like Oracle Enterprise Manager (OEM) for monitoring and managing our Oracle databases, including backup and recovery operations. OEM provides a centralized platform for monitoring, reporting, and automating database administration tasks.
Have you worked on OCI?

Answer:
Yes, I have worked on Oracle Cloud Infrastructure (OCI). In fact, I have experience migrating on-premises databases to OCI, configuring OCI databases, and managing database security and networking within the OCI environment.

Some specific areas I’ve worked on in OCI include:

– Setting up and configuring Oracle Autonomous Database
– Migrating databases to OCI using Oracle Cloud Infrastructure Database Migration
– Configuring OCI networking and security, including VCNs, subnets, and security lists
– Managing OCI database instances, including patching, upgrading, and scaling

I’m excited to leverage my OCI experience to help drive cloud adoption and optimization in your organization!
What kind of things you check in AWR reports?

Answer:
When analyzing AWR (Automatic Workload Repository) reports in Oracle, I focus on the following key areas to diagnose performance issues:
1. Report Summary (Snapshot Information)
2. Top Timed Events
3. Load Profile
4. Instance Efficiency Percentage
5. Top SQL Queries (SQL Statistics)
6. Wait Events (Wait Classes)
7. System Statistics
8. Segment Statistics
9. I/O Statistics
10. RAC Statistics (For RAC Environments)
How many team members you have in your team?

Answer:

Our Oracle DBA team consists of 10 members, each handling different aspects of database management to ensure smooth operations. The team is divided as follows:
- Production Support (4 members): These team members monitor and maintain production databases, troubleshoot performance issues, and ensure 24/7 availability.
- Backup & Recovery (2 members)
- Patching & Upgrades (2 members)
- Performance Tuning & Optimization (1 member)
- Automation & Scripting (1 member):
Do you work in shifts?

Answer:
Yes, I do work in shifts. As a DBA, I am responsible for ensuring the availability and performance of the database 24/7. To achieve this, our team works in shifts to provide around-the-clock coverage. This includes night shifts, weekend shifts, and holiday shifts
What is the path of alert log file?

Answer:
The path of the alert log file in Oracle is:
```
$ORACLE_BASE/diag/rdbms/instance_name/trace/alert.log
```

What is the difference between CPU and PSU patch?

Answer:

Difference Between CPU and PSU Patch in Oracle

Feature	CPU (Critical Patch Update)	PSU (Patch Set Update)
Full Form	Critical Patch Update	Patch Set Update
Purpose	Security fixes only	Security fixes + Bug fixes
Release Cycle	Quarterly (Jan, Apr, Jul, Oct)	Quarterly (Jan, Apr, Jul, Oct)
Contents	Includes only security vulnerability fixes	Includes CPU patches + additional bug fixes for stability
Impact	Minimal impact on functionality	May have minor functional changes due to bug fixes
Recommended For	Systems needing only security fixes	Systems needing security + stability improvements
Superset Of	Independent security patch	Includes CPU patches and additional fixes

What kind of patching activities done?

Answer:
1. CPU (Critical Patch Update) patching: Apply quarterly CPU patches to fix security vulnerabilities.
2. PSU (Patch Set Update) patching: Apply quarterly PSU patches to fix bug fixes, security patches, and stability improvements.
3. BP (Bundle Patch) patching: Apply bundle patches that include a collection of fixes for specific Oracle Database versions.
4. RU (Release Update) patching: Apply release updates that include a collection of fixes, security patches, and new features for specific Oracle Database versions.
5. OPatch patching: Apply individual patches using OPatch, Oracle’s patching tool.
6. Grid Infrastructure patching: Patch Oracle Grid Infrastructure (GI) components, such as Oracle Clusterware and Oracle Automatic Storage Management (ASM).
7. Database patching: Patch Oracle Database software, including the database kernel and other components.

These patching activities help ensure the security, stability, and performance of Oracle databases.
How do you restore a database from an available backup?

Answer:
The database restoration process involves the following steps:

Connect to RMAN using rman target /

Start the database in NOMOUNT mode: STARTUP NOMOUNT;

Restore the Server Parameter File (SPFILE): RESTORE SPFILE FROM AUTOBACKUP;

Restore the control file: RESTORE CONTROLFILE FROM AUTOBACKUP;

Mount the database: ALTER DATABASE MOUNT;

Restore the datafiles: RESTORE DATABASE;

Apply archived logs for recovery: RECOVER DATABASE;

Open the database with resetlogs: ALTER DATABASE OPEN RESETLOGS;
What is the backup process in your organization?

Answer:
The backup process involves taking periodic snapshots of database data to ensure recovery in case of failure. This typically includes full, incremental, and archive log backups using RMAN (Recovery Manager) or third-party tools. Additionally, automated scripts schedule backups and store them in secure locations for disaster recovery.
Archives are not arriving at the standby. What will you check?

Answer:
To diagnose this issue, check the following:

Query v$dataguard_status on the primary database for errors.

Verify log_archive_dest_state_2 is set to ENABLE.

Confirm that standby listener and tnsnames.ora are correctly configured.

Check network connectivity between primary and standby databases.

Ensure there is sufficient disk space in the archive destination.
What is the difference between a Data Dictionary Table and a Normal Table?

Answer:
A data dictionary table contains metadata about database objects such as tables, indexes, users, privileges, and schemas. It provides essential information for database management but is not modifiable by users. In contrast, normal tables store user data and can be read, modified, and deleted by authorized users.
What process will get data from datafiles to DB cache?

Answer: Server process
What background process will writes data to datafiles?

Answer: DBWR
What background process will write undo data?

Answer: BWR
What are physical components of Oracle database?

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

Answer: Blocks, Extents, Segments, Tablespaces
What are the differences between LMTS and DMTS?

Answer:
Tablespaces that record extent allocation in the dictionary are called dictionary managed tablespaces, and
tablespaces that record extent allocation in the tablespace header are called locally managed tablespaces.
What is a datafile?

Answer:
Every Oracle database has one or more physical datafiles. Datafiles contain all the database data. The data of logical
database structures such as tables and indexes is physically stored in the datafiles allocated for a database.
What are the contents of control file?

Answer:
Database name, SCN, LSN, datafile locations, redolog locations, archive mode, DB Creation Time, RMAN Backup &
Recovery Details, Flashback mode.
What is the use of redo log files?

Answer:

Redo log files in Oracle are used to record all changes made to the database. Their primary purposes are:
1. Crash Recovery – Helps recover the database in case of a system failure.
2. Data Integrity – Ensures committed transactions are not lost.
3. Replication & Standby – Used for redo shipping in Data Guard and replication setups.
4. Rollback & Roll-Forward – Enables rolling back uncommitted transactions and rolling forward committed changes.
What are the uses of undo tablespace or redo segments?

Answer:
Undo Tablespace:
1. Transaction Rollback: Stores undo information to roll back transactions in case of failure or user error.
2. Read Consistency: Provides read-consistent views of data for queries, ensuring that queries see a consistent view of data despite concurrent changes.
3. Error Recovery: Enables recovery from errors, such as deadlocks or corrupted data blocks.

Redo Logs:
1. Transaction Recovery: Stores redo information to recover transactions in case of failure or crash.
2. Database Recovery: Enables recovery of the database in case of a crash or media failure.
3. Replication and Standby: Supports replication and standby databases by providing a record of changes made to the primary database.
4. Auditing and Troubleshooting: Provides a record of changes made to the database, which can be useful for auditing and troubleshooting purposes.
How undo tablespace can guarantee retain of required undo data?

Answer: Alter tablespace undo_ts retention guarantee;
What is ORA-01555 – snapshot too old error and how do you avoid it?

Answer:
ORA-01555: snapshot too old error occurs when a query attempts to access a snapshot of data that is no longer available in the undo tablespace. This happens when:

1. A long-running query requires a read-consistent view of data.
2. The undo retention period is too short, causing the required undo data to be overwritten.

To avoid ORA-01555:

1. Increase undo retention period: Set the UNDO_RETENTION parameter to a higher value to retain undo data for a longer period.
2. Increase undo tablespace size: Expand the undo tablespace to store more undo data.
3. Tune queries: Optimize long-running queries to reduce their execution time and minimize the need for old snapshots.
4. Use GUARANTEE option: Use the GUARANTEE option when creating the undo tablespace to ensure that enough space is allocated for undo data.
5. Monitor undo tablespace usage: Regularly monitor undo tablespace usage and adjust parameters as needed.
How to create password file?

Answer: $ orapwd file=orapwSID password=sys_password force=y nosysdba=y
How many types of indexes are there?

Answer:
In Oracle, there are several types of indexes:

1. B-Tree Index: The most common type, used for columns with high cardinality.
2. Bitmap Index: Used for columns with low cardinality, storing a bitmap of rows.
3. Text Index: Used for full-text search, indexing text data.
4. Function-Based Index: Used to index the result of a function or expression.
5. Unique Index: Used to enforce uniqueness on a column or set of columns.
6. Composite Index: Used to index multiple columns.
7. Descending Index: Used to index data in descending order.
8. Reverse Key Index: Used to improve performance for range scans
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.
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.
What’s the difference between a primary key and a unique key?

Answer:
Both primary and unique key enforce uniqueness of the column on which they are defined. But by default primary
key creates a clustered index on the column, where unique key creates a non-clustered index by default. Primary key
doesn’t allow NULLs, but unique key allows one NULL only.
What is the difference between schema and user?

Answer:
Schema is collection of user’s objects.
What is the difference between SYSDBA, SYSOPER and SYSASM?

Answer:
SYSOPER can’t create and drop database.
SYSOPER can’t do incomplete recovery.
SYSOPER can’t change character set.
SYSOPER can’t CREATE DISKGROUP; ADD/DROP/RESIZE DISK
SYSASM can do anything SYSDBA can do
What is the difference between SYS and SYSTEM?

Answer:
SYS:
1. Owner of the database: SYS is the owner of the Oracle database and its underlying structures.
2. Highest privileges: SYS has all privileges and can perform any action in the database.
3. Core database objects: SYS owns core database objects, such as system tables, views, and packages.

SYSTEM:
1. Default administrative user: SYSTEM is the default administrative user for the database.
2. High-level privileges: SYSTEM has high-level privileges, but not as extensive as SYS.
3. Database administration: SYSTEM is used for database administration tasks, such as creating users, granting privileges, and managing database objects.

Key differences:

– SYS is the owner of the database, while SYSTEM is an administrative user.
– SYS has more extensive privileges than SYSTEM.
– SYS is used for core database operations, while SYSTEM is used for administrative tasks.
What is the difference between view and materialized view?

Answer:
View:
1. Virtual table: A view is a virtual table based on a SQL query.
2. No physical storage: Views do not store data physically; they derive data from underlying tables.
3. Real-time data: Views always show real-time data from the underlying tables.
4. Query-based: Views are defined by a SQL query that is executed every time the view is queried.

Materialized View:
1. Physical storage: A materialized view stores data physically, just like a table.
2. Periodic refresh: Materialized views are refreshed periodically, either on demand or at scheduled intervals.
3. Stale data: Materialized views can show stale data if not refreshed recently.
4. Query optimization: Materialized views can be used to optimize queries by precomputing and storing results.
How to find out when was a materialized view refreshed?

Answer:
Query dba_mviews or dba_mview_analysis or dba_mview_refresh_times
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,’YYYY-MM-DD HH24:MI:SS’) from dba_mviews;
(or)
SQL> select NAME, to_char(LAST_REFRESH,’YYYY-MM-DD HH24:MI:SS’) from dba_mview_refresh_times;
(or)
SQL> select MVIEW_NAME, to_char(LAST_REFRESH_DATE,’YYYY-MM-DD HH24:MI:SS’) from dba_mview_analysis;
What is atomic refresh in mviews?

Answer:
When an MV is refreshed atomically:

1. Locks are acquired: The MV is locked exclusively to prevent concurrent modifications.
2. Refresh is executed: The refresh process is executed, which may involve deleting and re-inserting data.
3. Changes are committed: If the refresh is successful, the changes are committed, and the locks are released.

Atomic refresh ensures:

– Consistency: The MV remains consistent, even in the event of failures during the refresh process.
– Data integrity: The MV data is not partially updated, reducing the risk of data inconsistencies.

To enable atomic refresh, use the ATOMIC_REFRESH clause when creating or altering the MV:
CREATE MATERIALIZED VIEW mv_name
REFRESH COMPLETE ON DEMAND
ATOMIC_REFRESH
AS
SELECT * FROM table_name;
How to find out whether database/tablespace/datafile is in backup mode or not?

Answer:
```
Query V$BACKUP view.
```
What is row chaining?

Answer:
Row chaining, also known as row migration, occurs in Oracle when a row is updated and its new size exceeds the available space in its current block.

When a row is updated:

1. Row grows: The row size increases due to the update.
2. No space available: There is not enough space in the current block to accommodate the larger row.
3. Row chaining: Oracle chains the row to a new block, storing the additional data in the new block.

Row chaining can lead to:

– Performance issues: Increased I/O operations to access chained rows.
– Storage inefficiencies: Wasted space due to fragmented rows.
What is row migration?

Answer:

Row migration, also known as row movement, occurs in Oracle when a row is updated and its new size exceeds the available space in its current block, but unlike row chaining, the entire row is moved to a new block.

When a row is updated:

1. Row grows: The row size increases due to the update.
2. No space available: There is not enough space in the current block to accommodate the larger row.
3. Row migration: Oracle moves the entire row to a new block, freeing up space in the original block.

Row migration can lead to:

– Performance issues: Increased I/O operations to access migrated rows.
– Storage inefficiencies: Wasted space due to fragmented rows.

To minimize row migration:

– Proper table design: Ensure sufficient column sizes and padding.
– Regular table maintenance: Monitor and reorganize tables as needed.
– Use automatic segment space management: Enable automatic space management to reduce fragmentation.
– Use PCTFREE parameter: Set PCTFREE to a suitable value to reserve space for future row growth.
What are different types of partitions?

Answer:
In Oracle, there are several types of partitions:

1. Range Partitioning: Divides data into partitions based on a range of values for a specific column.
2. List Partitioning: Divides data into partitions based on a list of discrete values for a specific column.
3. Hash Partitioning: Divides data into partitions based on a hash function applied to a specific column.
4. Composite Partitioning: Combines range, list, or hash partitioning to create a multi-level partitioning scheme.
5. Interval Partitioning: An extension of range partitioning, where new partitions are created automatically as new data is inserted.
6. Reference Partitioning: Partitions a table based on a referential constraint to another table.
7. System Partitioning: Allows the database administrator to define partitions manually.
8. Hybrid Partitioning: Combines different partitioning methods, such as range and hash partitioning.
What is local partitioned index and global partitioned index?

Answer:
A local index is an index on a partitioned table which is partitioned in the exact same manner as the underlying
partitioned table. Each partition of a local index corresponds to one and only one partition of the underlying table.
A global partitioned index is an index on a partitioned or non partitioned tables which are partitioned using a
different partitioning key from the table and can have different number of partitions. Global partitioned indexes can
only be partitioned using range partitioning.
How you will recover if you lost one/all control file(s)?

Answer:
Lost one controlfile:
a. Shut database
b. Copy and rename the controlfile from the existing or mirror controlfile at os level ‘OR’
Remove the controlfile location from the pfile
c. start the database
Lost of all controlfile: using the backup:
a. shut the database (abort)
b. startup the database in nomount state
c. restore the controlfile from the autobackup
d. open the database with resetlogs
Lost of all controlfile: without using the backup:
a. create the controlfile manually with all the datafile locations
b. mount the controlfile
c. open the database with resetlogs
Why more archivelogs are generated, when database is begin backup mode?

Answer:
When a database is put in BEGIN BACKUP mode:

1. Redo log generation increases: Oracle generates additional redo logs to ensure data consistency and integrity.
2. Archivelogs are generated more frequently: The increased redo log generation leads to more frequent archiving of redo logs, resulting in a higher number of archivelogs.

This is because:

– Oracle ensures data consistency: By generating more redo logs, Oracle ensures that all changes are recorded and can be recovered in case of a failure.
– Prevents data corruption: The increased archiving of redo logs helps prevent data corruption by ensuring that all changes are properly recorded and stored.

When the database is taken out of BEGIN BACKUP mode, the redo log generation and archiving return to normal.
What UNIX parameters you will set while Oracle installation?

Answer:
During an Oracle installation on a UNIX-based system, the following parameters are typically set:

1. kernel.shmall: Sets the maximum number of shared memory segments.
2. kernel.shmmax: Sets the maximum size of a shared memory segment.
3. kernel.shmmni: Sets the maximum number of shared memory identifiers.
4. semaphores: Sets the semaphore parameters (semmsl, semmns, semopm, semmni).
5. file descriptors (nofiles): Sets the maximum number of open file descriptors.
6. processes (nproc): Sets the maximum number of processes.
7. stack size (stack): Sets the maximum stack size for a process.

These parameters are usually set in the /etc/sysctl.conf file or using the ulimit command.

Example:
bash
# sysctl -w kernel.shmall=2097152
# sysctl -w kernel.shmmax=2147483648
# sysctl -w kernel.shmmni=4096
# ulimit -n 65536
# ulimit -u 16384
What are differences between dbms_job and dbms_schedular?

Answer:
DBMS_JOB:
1. Simple job scheduling: DBMS_JOB provides basic job scheduling capabilities.
2. Limited functionality: It only allows for simple, one-time or recurring jobs.
3. No support for complex schedules: DBMS_JOB does not support complex schedules, such as schedules based on events or windows.
4. No support for job chains: DBMS_JOB does not support job chains, which are series of jobs that run in a specific order.

DBMS_SCHEDULER:
1. Advanced job scheduling: DBMS_SCHEDULER provides advanced job scheduling capabilities.
2. Support for complex schedules: It supports complex schedules, such as schedules based on events, windows, or calendars.
3. Support for job chains: DBMS_SCHEDULER supports job chains, which enable you to run multiple jobs in a specific order.
4. Support for windows and resource allocation: It allows you to define windows and allocate resources to jobs.
5. Improved manageability and monitoring: DBMS_SCHEDULER provides better manageability and monitoring capabilities, including support for email notifications and job logging.
What are differences between dbms_schedular and cron jobs?

Answer:
DBMS_SCHEDULER:
1. Database-specific: DBMS_SCHEDULER is a built-in Oracle database feature.
2. Runs database jobs: It runs PL/SQL code, stored procedures, and other database-specific tasks.
3. Integrated with database security: DBMS_SCHEDULER uses database authentication and authorization.
4. Supports complex schedules: It supports calendars, windows, and event-based scheduling.
5. Provides detailed logging and monitoring: DBMS_SCHEDULER offers detailed logging and monitoring capabilities.

Cron Jobs:
1. Operating system-specific: Cron jobs are a feature of Unix-like operating systems.
2. Runs system-level commands: Cron jobs run system-level commands, scripts, and programs.
3. Uses system authentication: Cron jobs use system authentication and authorization.
4. Supports simple schedules: Cron jobs support simple, time-based scheduling (e.g., daily, weekly).
5. Limited logging and monitoring: Cron jobs have limited logging and monitoring capabilities.
What you will do if (local) inventory corrupted [or] opatch lsinventory is giving error?

Answer:
Step 1: Check the error message
Review the error message to understand the cause of the issue.

Step 2: Run opatch lsinventory with verbose option
Run opatch lsinventory -verbose to get detailed output and identify potential issues.

Step 3: Run opatch lsinventory with clean option
Run opatch lsinventory -clean to remove any corrupted or inconsistent entries from the inventory.

Step 4: Run opatch lsinventory with force option
Run opatch lsinventory -force to recreate the inventory from scratch.

Step 5: Manually remove and recreate the inventory
If the above steps fail:

1. Stop all Oracle services.
2. Manually remove the inventory directory (usually $ORACLE_HOME/inventory).
3. Recreate the inventory by running opatch lsinventory.

Step 6: Verify the inventory
After recreating the inventory, run opatch lsinventory to verify that it is correct and up-to-date.
What are the entries/location of oraInst.loc?

Answer:
/etc/oraInst.loc is pointer to central/local Oracle Inventory
What is the difference between central/global inventory and local inventory?

Answer:
Central/Global Inventory:
1. Shared across multiple Oracle homes: The central inventory is shared across multiple Oracle homes on a system.
2. Stores metadata about all Oracle installations: It stores metadata about all Oracle installations, including patches, versions, and components.
3. Located outside of the Oracle home: The central inventory is typically located outside of the Oracle home directory.
4. Managed by the Oracle Universal Installer (OUI): The central inventory is managed by the OUI.

Local Inventory:
1. Specific to a single Oracle home: The local inventory is specific to a single Oracle home and stores metadata about that specific installation.
2. Stores metadata about the Oracle home: It stores metadata about the Oracle home, including patches, versions, and components.
3. Located within the Oracle home: The local inventory is typically located within the Oracle home directory.
4. Managed by OPatch: The local inventory is managed by OPatch, a utility used for patching and maintaining Oracle software.
What is the use of root.sh & oraInstRoot.sh?

Answer:
root.sh:
1. Configures Oracle kernel components: root.sh configures Oracle kernel components, such as shared memory and semaphores.
2. Sets environment variables: It sets environment variables, such as ORACLE_HOME and LD_LIBRARY_PATH.
3. Creates symbolic links: root.sh creates symbolic links for Oracle executables.
4. Runs as root user: This script must be run as the root user.

oraInstRoot.sh:
1. Configures Oracle inventory: oraInstRoot.sh configures the Oracle inventory, including the central inventory location.
2. Creates Oracle inventory directory: It creates the Oracle inventory directory and sets permissions.
3. Runs as root user: This script must also be run as the root user.
What is transportable tablespace (and across platforms)?

Answer:

Transportable tablespaces (TTS) is a feature in Oracle that allows you to move tablespaces between databases, while maintaining the data integrity and consistency.

Benefits:
1. Faster data transfer: TTS is faster than traditional data export/import methods.
2. Reduced downtime: TTS minimizes downtime, as the tablespaces are only offline for a short period.
3. Platform independence: TTS allows you to move tablespaces across different platforms (e.g., from Linux to Windows).

Process:
1. Make the tablespace read-only: Make the tablespace read-only to ensure data consistency.
2. Export the tablespace metadata: Export the tablespace metadata using the expdp utility.
3. Transport the tablespace files: Transport the tablespace files to the target database.
4. Import the tablespace metadata: Import the tablespace metadata using the impdp utility.

Across Platforms:
To transport tablespaces across platforms:

1. *Use the dbms_tts.transport_set_check procedure*: Verify that the tablespaces can be transported between platforms.
2. Convert the tablespaces to a platform-independent format: Use the dbms_tts.transport_set_convert procedure to convert the tablespaces.
3. Transport the tablespaces: Follow the same process as above.
How can you transport tablespaces across platforms with different endian formats?

Answer:
Endian Format:
Endian format refers to the way bytes are stored in memory:

– Big Endian (BE): Most significant byte first (e.g., Oracle on SPARC, AIX).
– Little Endian (LE): Least significant byte first (e.g., Oracle on x86, Windows).

Transporting Tablespaces:
To transport tablespaces across platforms with different endian formats:

1. *Use the dbms_tts.transport_set_check procedure*: Verify that the tablespaces can be transported between platforms.
2. Convert the tablespaces to a platform-independent format: Use the dbms_tts.transport_set_convert procedure with the CONVERT option to convert the tablespaces to a platform-independent format.
3. *Use the RMAN CONVERT command*: Use the RMAN CONVERT command to convert the tablespaces to the target platform’s endian format.
4. Transport the converted tablespaces: Transport the converted tablespaces to the target platform.

RMAN CONVERT Command:
The RMAN CONVERT command is used to convert tablespaces between endian formats:
```
RMAN> CONVERT TABLESPACE 'tablespace_name' 
FORMAT '/path/to/converted/tablespace' 
ENDIAN CONVERSION;
```
What is the difference between restore point & guaranteed restore point?

Answer:
Restore Point:
1. Snapshot of the database: A restore point is a snapshot of the database at a specific point in time.
2. Not guaranteed: A restore point is not guaranteed, meaning that Oracle does not ensure that the database can be restored to that point.
3. Dependent on flashback logs: A restore point relies on flashback logs, which can be deleted if the flashback retention period is exceeded.

Guaranteed Restore Point:
1. Guaranteed snapshot: A guaranteed restore point is a guaranteed snapshot of the database at a specific point in time.
2. Ensures restore: A guaranteed restore point ensures that the database can be restored to that point, even if flashback logs are deleted.
3. Not dependent on flashback logs: A guaranteed restore point does not rely on flashback logs and is stored in the control file.
How to find if your Oracle database is 32 bit or 64 bit?

Answer:
Execute the command “file $ORACLE_HOME/bin/oracle”, you should see output like /u01/db/bin/oracle: ELF 64-bit
MSB executable SPARCV9 Version 1
means you are on 64 bit oracle.
If your oracle is 32 bit you should see output like below
oracle: ELF 32-bit MSB executable SPARC Version 1
How to find opatch Version?

Answer:
Opatch is utility to apply database patch, In order to find opatch version execute
“$ORACLE_HOME/OPatch/opatch version”
Which procedure does not affect the size of the SGA?

Answer: Stored procedure
When Dictionary tables are created?

Answer: Once for the Entire Databasecreation.
The order in which Oracle processes a single SQL statement is?

Answer: Parse, execute and fetch
What are the mandatory datafiles to create a database in Oracle 11g?

Answer:
SYSTEM, SYSAUX, UNDO
In one server can we have different oracle versions?

Answer: Yes
How do sessions communicate with database?

Answer: Server processes execute SQL received from user processes.
Which SGA memory structure cannot be resized dynamically after instance startup?

Answer: Log buffer
When a session changes data, where does the change get written?

Answer: To the data block in the cache, and the redo log buffer
How many maximum no of control files we can have within a database?

Answer: 8
System Data File Consists of? in oracle

Answer:

In Oracle, a System Data File is a part of the SYSTEM tablespace and contains critical metadata required for database operation. It consists of:
1. Data Dictionary – Metadata about database objects such as tables, indexes, users, privileges, and constraints.
2. System Undo Segments – Used to store undo information for system-related transactions.
3. Bootstrap Information – Required to initialize the database instance.
4. Views and Stored Procedures – System-related PL/SQL packages and views.
5. Rollback Segments (if using Manual Undo Management) – For transaction consistency and rollback operations.
Which action occurs during a checkpoint?

Answer:
Checkpoint Actions:
1. All modified database buffers are written to disk: All dirty buffers in the buffer cache are written to their corresponding datafiles on disk.
2. The SCN (System Change Number) is updated: The SCN is incremented and updated in the control file, datafile headers, and redo logs.
3. The checkpoint position is updated: The checkpoint position is updated in the control file to reflect the new SCN.
4. The redo logs are switched: The redo logs are switched, and a new redo log is opened for writing.
5. The database is marked consistent: The database is marked consistent, indicating that all changes have been written to disk
SMON process is used to write into LOG files?

Answer: No
Oracle does not consider a transaction committed until?

Answer: The LGWR successfully writes the changes to redo
How many maximum DBWn (Db writers) we can invoke?

Answer: 20
Which activity would generate less undo data?

Answer: INSERT
What happens when a user issues a COMMIT?

Answer:
COMMIT Actions:
1. Ends the transaction: The COMMIT statement ends the current transaction.
2. Makes changes permanent: All changes made during the transaction are made permanent and visible to other users.
3. Releases locks: Any locks held by the transaction are released.
4. Generates a redo record: A redo record is generated to record the changes made during the transaction.
5. LGWR (Log Writer) writes redo records to redo logs: The LGWR process writes the redo records to the redo logs.
6. SCN (System Change Number) is incremented: The SCN is incremented to reflect the new transaction.
7. Transaction is removed from the undo tablespace: The transaction is removed from the undo tablespace.
What happens when a user process fails?

Answer:
When a user process fails:

Failure Actions:
1. PMON (Process Monitor) is notified: PMON is notified of the failed process.
2. PMON rolls back the transaction: PMON rolls back the transaction to ensure data consistency.
3. PMON releases locks: PMON releases any locks held by the failed process.
4. PMON terminates the failed process: PMON terminates the failed process.
5. The session is terminated: The session associated with the failed process is terminated.

Automatic Recovery:
1. SMON (System Monitor) performs instance recovery: If the instance crashes, SMON performs instance recovery to restore the database to a consistent state.
2. Undo records are applied: Undo records are applied to roll back any uncommitted transactions.
What are the free buffers in the database buffer cache?

Answer:
Free Buffers:
1. Unmodified buffers: Free buffers are unmodified buffers that do not contain any changed or dirty data.
2. Available for new data: These buffers are available to store new data blocks read from disk.
3. Not containing any useful data: Free buffers do not contain any useful data and can be overwritten with new data.
When the SMON Process perform Instance Crash Recovery?

Answer: Only at the time of startup after abort shutdown
Which dynamic view can be queried when a database is started up in no mount state?

Answer: V$INSTANCE
Which two tasks occur as a database transitions from the mount stage to the open stage?

Answer: The online data files & Redo log files are opened.
In which situation is it appropriate to enable the restricted session mode?

Answer: Exporting a consistent image of a large number of tables
What is the component of an Oracle instance?

Answer: The SGA
Which process is involved when a user starts a new session on the database server?

Answer: The Oracle server process
In the event of an Instance failure, which files store command data NOT written to the datafiles?

Answer: Online redo logs
When are the base tables of the data dictionary created?

Answer: When the database is created
Sequence of events takes place while starting a Database is?

Answer: Instance started, Database mounted & Database opened
The alert log will never contain information about which database activity?

Answer: Performing operating system restore of the database files
Where can you find the non-default parameters when an instance is started?

Answer: Alert log
Which tablespace is used as the temporary tablespace if TEMPORARY TABLESPACE is not specified for a user?

Answer: SYSTEM
User SCOTT creates an index with this statement: CREATE INDEX emp_indx on employee (empno). In which tablespace would be the index created?

Answer: SCOTT’S default tablespace
Which data dictionary view shows the available free space in a certain tablespace?

Answer: DBA_FREE_SPACE
Which method increase the size of a tablespace?

Answer: Add a datafile to a tablespace.
What does the command ALTER DATABASE . . . RENAME DATAFILE do?

Answer: It updates the control file.
Can you drop objects from a read-only tablespace?

Answer: Yes
SYSTEM TABLESPACE can be made off-line?

Answer: No
Data dictionary can span across multiple Tablespaces?

Answer: No
Multiple Tablespaces can share a single datafile?

Answer: No
What is a default role?

Answer: A role automatically enabled when the user logs on.
Who is the owner of a role?

Answer: Nobody
When granting the system privilege, which clause enables the grantee to further grant the privilege to other users or roles?

Answer: WITH ADMIN OPTION
Which view will show a list of privileges that are available for the current session to a user?

Answer: SESSION_PRIVS
Which view shows all of the objects accessible to the user in a database?

Answer: ALL_OBJECTS
Which statement about profiles is false?

Answer: Profiles are assigned to users, roles, and other profiles.
Which password management feature is NOT available by using a profile?

Answer: Password change
Which resource can not be controlled using profiles?

Answer: PGA memory allocations
You want to retrieve information about account expiration dates from the data dictionary. Which view do you use?

Answer: DBA_USERS
It is very difficult to grant and manage common privileges needed by different groups of database users using roles?

Answer: No
Which data dictionary view would you query to retrieve a table’s header block number?

Answer: DBA_SEGMENTS
When tables are stored in locally managed tablespaces, where is extent allocation information stored?

Answer: Corresponding tablespace itself
Which of the following three portions of a data block are collectively called as Overhead?

Answer: Table directory, row directory and data block header
Can a tablespace hold objects from different schemes?

Answer: Yes
Which data dictionary view would you query to retrieve a table’s header block number?

Answer: DBA_SEGMENTS
What is default value for storage parameter INITIAL in 10g if extent management is Local?

Answer: 40k
Using which package we can convert Tablespace from DMTS to LMTS?

Answer: DBMS_SPACE_ADMIN
Is it Possible to Change ORACLE Block size after creating database?

Answer: No
Locally Managed table spaces will increase the performance?

Answer: TRUE
Index is a Space demanding Object?

Answer: Yes
What is a potential reason for a Snapshot too old error message?

Answer: An ITL entry in a data block has been reused.
How can we view the status of a rollback segment?

Answer: Using the DBA_ROLLBACK_SEG view.
How can we view the status of a rollback segment?

Answer: Using the DBA_ROLLBACK_SEG view.
Are uncommitted transactions written to flashback redo logs?

Answer: Yes.
Is flashback possible after a table is truncated?

Answer: No.
Which command clears the database recycle bin?

Answer: PURGE RECYCLEBIN.
What does the OPTIMAL parameter control?

Answer: The length of a rollback segment.
What affects flashback query time?

Answer: The UNDO_RETENTION parameter.
Can we create an SPFILE while the database is in shutdown mode?

Answer: Yes.
Can static parameters be changed using SCOPE=BOTH?

Answer: No.
Does the DROP DATABASE command remove the SPFILE?

Answer: Yes.
How do we modify database parameters dynamically?

Answer: Using the ALTER SYSTEM command.
What is the default listener name?

Answer: LISTENER.
Which file stores the configuration for host-based naming?

Answer: sqlnet.ora.
Can multiple listeners share the same network interface card?

Answer: Yes.
Can DML operations be performed on a materialized view?

Answer: No.
Does a materialized view occupy space?

Answer: Yes.
Can a normal user view public database links?

Answer: Yes, using the ALL_DB_LINKS view.
Can we change the refresh interval of a materialized view?

Answer: Yes.
How can we improve SQL*Loader (sqlldr) performance?

Answer: By using the direct path load method.
Which command lists the current archiving status?

Answer: ARCHIVE LOG LIST.
How do we create a text backup of the control file?

Answer: ALTER DATABASE BACKUP CONTROLFILE TO TRACE.
Which dynamic view provides control file section details?

Answer: V$CONTROLFILE_RECORD_SECTION.
Which command adds a new redo log member?

Answer: ALTER DATABASE ADD LOGFILE MEMBER.
Which export option allows table creation without data?

Answer: ROWS=N.
Does Data Pump (expdp) support Flashback?

Answer: Yes.
Can a read-only tablespace be exported?

Answer: Yes.
Can a DBA restart a Data Pump job if it stops?

Answer: Yes.
What must be checked before creating a transportable tablespace?

Answer: The target system must use the same operating system.
When is a media recovery required?

Answer: If a data file is out of sync with other files.
How can a lost control file be restored?

Answer: By restoring from a backup and using RECOVER DATABASE USING BACKUP CONTROLFILE
What is the effect of enabling BLOCK CHANGE TRACKING?

Answer: The CTWR background process is invoked.
Which view lists RMAN restore points?

Answer: RC_RESTORE_POINT.
Which command merges two RMAN recovery catalogs?

Answer: IMPORT CATALOG.
What does interfile backup parallelism do?

Answer: It divides files into multiple sections and takes the backup in parallel.
What is the difference between PFILE and SPFILE? Where are these files located?

Answer:
PFILE (Parameter File): A text file that stores database initialization parameters. Changes require a database restart.

SPFILE (Server Parameter File): A binary file that allows dynamic parameter changes without restarting the instance.

Location: $ORACLE_HOME/dbs.
What will you do if both PFILE and SPFILE are deleted? Can you start the database?

Answer:

If init.ora (PFILE) or spfile is lost, manually create a PFILE using another database’s PFILE.

Edit the PFILE to match db_name, control_files, etc.

Start the database using startup pfile=<path>.

Later, create an SPFILE from the PFILE using:
```
CREATE SPFILE FROM PFILE;
```
What is the difference between static and dynamic init.ora/SPFILE parameters?

Answer:
Static parameters: Require an instance restart to take effect.

Dynamic parameters: Can be changed while the database is running and take effect immediately.
What is the complete syntax to set DB_CACHE_SIZE in memory and SPFILE?
```
ALTER SYSTEM SET DB_CACHE_SIZE=2G SCOPE=BOTH;
```
SCOPE=BOTH applies changes to both memory and SPFILE.
How do we configure multiple buffer caches in Oracle?

Answer:
Use the DB_NK_CACHE_SIZE dynamic parameter in PFILE or SPFILE. (NK can be 2K, 4K, 8K, 16K, or 32K.)

Example:

ALTER SYSTEM SET DB_4K_CACHE_SIZE=512M SCOPE=BOTH;

If DB_BLOCK_SIZE=8K, then DB_8K_CACHE_SIZE is not allowed.
What are the benefits of setting multiple buffer caches?

Answer:
OLTP databases use small block sizes (2K, 4K, 8K) to optimize performance for small transactions.

Data warehouse databases use larger block sizes (8K, 16K, 32K) for efficient large table scans.

Hybrid databases require multiple block sizes and tablespaces with different BLOCKSIZE settings.

Oracle DBA Interview Questions

Oracle DBA Interview Questions

What are the different responsibilities of a Oracle DBA?

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

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

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

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

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

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

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

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

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

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

What do you understand by Redo Log file mirroring?

How is incremental backup different from differential backup?

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

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

What is a Recovery Catalog?

How do you recover a lost control file?

What is the difference between media recovery & crash recovery?

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

How would you differentiate between cluster and grid?

What do you understand from Cache Fusion?

How can we monitor the space allocations in a database?

What do you understand by “Performance Tuning of DB” & what are the different

areas where we can perform tuning?

What are the different tools that are provided by Oracle to assist performance

monitoring?

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

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

How can we collect the statistics of different database objects?

Why do we need to rebuild indexes?

What is TKPROF and how can we use it?

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

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

What do you understand by Row Chaining?

What is table partitioning and why is it required?

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

How to create password file?

How many types of indexes are there?

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

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

How you will find out fragmentation of index?

What is the difference between delete and truncate?

Describe Oracle Architecture.

What is the role of the PMON process?

What are the different stages of the database startup process?

Explain the backup process used in your organization.

How do you restore a database from a backup?

What are LMT and DMT?

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

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

Difference between a Data Dictionary table and a regular table?

What happens when you issue a STARTUP UPGRADE command?

What are the key steps in an Oracle database upgrade?

How do you install Oracle binaries and configure kernel parameters?

What are the steps to configure Oracle Data Guard?

How do you check the directory size in ASM?

How do you check the Oracle Database version?

What are the different modes of Oracle Data Guard?

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

How do you verify that standby is in sync with primary?

What is Flashback Recovery?

What is a snapshot too old error?

How do you create a new Oracle user?

What is the difference between a control file and a parameter file?

What is the command to add a datafile to a tablespace?

Answer:

Can we resize a redo log file?

What is Disaster Recovery (DR)?

Can we have two MRP processes on a DR server?

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

How to know which parameter is dynamic/static?

How to know how much free memory available in sga?

What are oracle storage structures?

List types of Oracle objects

What is a Synonym in Oracle?

What is sequence?

Where do you see the tablespace information?

How to find the datafiles that associated with particular tablespace? Ex: System