100+ Oracle Database Administrator Interview Questions and Answers

Recovering deleted datafiles without backup

• Check if the datafiles are still in the operating system's recycle bin or trash folder

• Use data recovery software to scan the hard drive for deleted files

• If the above methods fail, try to recreate the datafiles using the CREATE DATAFILE command

• If the tablespace is still intact, use the RMAN RECOVER TABLESPACE command to recover the data

Upgrading to Oracle Database 19c and related challenges

• Ensure compatibility of existing applications with 19c

• Perform pre-upgrade tasks such as backup and patching

• Upgrade the database using DBUA or manual methods

• Address challenges such as deprecated features and changes in behavior

• Configure Data Guard for high availability and disaster recovery

• Rollback an OPatch using the -rollback option

• Use RMAN duplication to create a copy of the database for testing or reporting

Monitoring db performance using OEM AWR report

• Access OEM console and navigate to Performance tab

• Select AWR report option to generate a report

• Analyze the report for key performance metrics like CPU usage, memory utilization, I/O stats

• Identify any performance bottlenecks and take necessary actions to optimize db performance

To select a particular row from a table, use the SELECT statement with a WHERE clause specifying the desired criteria.

• Use the SELECT statement to query the table

• Add a WHERE clause to specify the criteria for selecting the row

• The WHERE clause can include conditions based on column values

• Example: SELECT * FROM table_name WHERE column_name = 'desired_value'

Resolve gap on a Physical Standby in Dataguard by identifying the missing archived redo log files and manually copying them over.

• Check the alert log for errors related to missing archived redo log files

• Identify the sequence number of the missing redo log files

• Manually copy the missing archived redo log files from the primary database to the standby database location

• Register the missing redo log files using the ALTER DATABASE REGISTER LOGFILE command

Cold backup is taken when the database is offline while hot backup is taken when the database is online.

• Cold backup is taken when the database is shut down and all files are copied to a backup location.

• Hot backup is taken while the database is still running and all files are copied to a backup location.

• Cold backup is slower and requires downtime while hot backup is faster and does not require downtime.

• Cold backup is more reliable as there is no chance of data being changed du...read more

To recover a deleted control file in Oracle Database, you can use a backup control file or create a new control file.

• If a backup control file is available, you can restore it using the RMAN utility.

• If a backup control file is not available, you can create a new control file using the CREATE CONTROLFILE statement.

• To create a new control file, you need to provide the necessary information about the database, such as datafiles, redo log files, and tablespaces.

• Once the control fi...read more

Investigate the issue, analyze performance metrics, identify bottlenecks, optimize queries, and implement performance tuning.

• Investigate the issue by analyzing logs, monitoring tools, and gathering information from the client.

• Analyze performance metrics such as CPU usage, memory usage, disk I/O, and query execution times.

• Identify bottlenecks in the database system such as slow queries, inefficient indexing, or hardware limitations.

• Optimize queries by rewriting them, adding in...read more

Checkpoint is a process in Oracle that writes dirty buffers from the buffer cache to data files.

• Checkpoint is triggered by various events like log switch, manual checkpoint, etc.

• It ensures that all committed data is written to disk and the database can be recovered in case of a crash.

• Checkpoint writes all dirty buffers from the buffer cache to data files.

• It also updates the control file and data file headers to indicate the most recent checkpoint.

• Checkpoint frequency can be c...read more

A DBA is a database administrator who is responsible for managing and maintaining a company's database system.

• Roles include database design, security, backup and recovery, performance tuning, and troubleshooting

• Responsibilities include ensuring data integrity, availability, and accessibility

• Shift models vary depending on the company's needs, but may include on-call availability and rotating shifts

• Examples of database systems include Oracle, MySQL, and SQL Server

Oracle fetches data using the SQL engine and the buffer cache.

• When a select statement is executed, the SQL engine parses the statement and creates an execution plan.

• The execution plan is used to fetch data from the database.

• The buffer cache is used to store frequently accessed data in memory for faster access.

• Oracle uses a multiversion read consistency model to ensure that data is consistent.

• Data is fetched in blocks called database blocks, which are stored on disk.

• Oracle use...read more

Background processes are essential components of Oracle database that run continuously in the background. Memory architecture is the way Oracle manages memory.

• Background processes include DBWn, LGWR, PMON, SMON, etc.

• Memory architecture includes SGA, PGA, buffer cache, shared pool, etc.

• SGA is a shared memory region that stores data and control information.

• PGA is a private memory region that stores session-specific information.

• Buffer cache is a portion of SGA that stores data b...read more

To check long running in database, use SQL queries to identify and troubleshoot slow queries.

• Use Oracle Enterprise Manager to monitor database performance

• Check the V$SESSION_LONGOPS view to identify long running operations

• Use SQL trace to identify slow queries

• Check the alert log for any errors or warnings

• Use AWR reports to analyze database performance over time

Up to 60 nodes can be implemented in RAC.

• RAC stands for Real Application Clusters

• The number of nodes depends on the version of Oracle Database being used

• Oracle Database 12c supports up to 60 nodes in RAC

To create a schema, use CREATE SCHEMA statement. Data types include VARCHAR2, NUMBER, DATE, etc.

• Create a schema using CREATE SCHEMA statement

• Specify data types for columns when creating tables

• Common data types include VARCHAR2, NUMBER, DATE, etc.

• Use appropriate data types based on the type of data being stored

DataGuard installation involves configuring primary and standby databases for data protection and disaster recovery.

• Configure primary database for archiving redo logs

• Configure standby database for receiving redo logs from primary

• Set up DataGuard broker for managing the configuration

• Monitor DataGuard performance and synchronization

• Test failover and switchover scenarios for disaster recovery

Maintaining the database involves regular monitoring, performance tuning, applying patches, and ensuring backups are taken regularly.

• Regularly monitor database performance and usage

• Perform routine maintenance tasks such as applying patches and updates

• Take regular backups to ensure data integrity and disaster recovery

• Implement security measures to protect the database from unauthorized access

• Optimize database performance by tuning queries and indexes

To patch specific db components to reduce downtime, follow these steps:

• Identify the specific components that need to be patched

• Plan the patching process during a scheduled maintenance window

• Backup the database before applying patches

• Apply patches to the specific components one by one

• Test the patched components to ensure they are functioning correctly

• Monitor the database for any issues post-patching

New version has improved security, performance and automation features.

• Improved security with new encryption algorithms and secure password management

• Enhanced performance with in-memory column store and advanced indexing

• Automation features like automatic data optimization and automatic storage management

• New features for database management like JSON support and sharding

• Cloud integration with Oracle Cloud Infrastructure and Autonomous Database

Oracle database is a relational database management system developed by Oracle Corporation.

• It is a software system used to manage and store data.

• It uses Structured Query Language (SQL) to manage data.

• It is widely used in enterprise applications.

• It provides features like data security, backup and recovery, and scalability.

• Examples of Oracle database applications include online banking systems, airline reservation systems, and e-commerce websites.

11i and R12 are two different versions of Oracle E-Business Suite.

• 11i is the older version, released in 2000, while R12 was released in 2007.

• R12 has a more modern architecture and improved features, such as online patching and subledger accounting.

• Upgrading from 11i to R12 requires careful planning and testing.

• Both versions are still in use, but Oracle has announced that support for 11i will end in 2020.

Oracle is a relational database management system developed by Oracle Corporation.

• Oracle is used to store and manage data in a structured manner.

• It supports SQL and PL/SQL programming languages.

• Indexes are updated by the database itself as part of its maintenance tasks.

• However, DBAs can also manually update indexes if needed.

To take RMAN backup quickly, use parallelism, compression, and backup sets.

• Use multiple channels to take backup in parallel

• Enable compression to reduce backup size

• Use backup sets to split backup into smaller pieces

• Use incremental backup to reduce backup time

• Use fast recovery area to store backups for quick recovery

TNS is a network protocol used to connect to Oracle databases, while LISTENER is a process that listens for incoming connection requests.

• TNS (Transparent Network Substrate) is a protocol used to establish connections between clients and Oracle databases.

• LISTENER is a process that runs on the database server and listens for incoming connection requests.

• TNS resolves the service name to the database's network address, allowing clients to connect.

• LISTENER receives connection requ...read more

A database is a collection of related data, while an instance is the set of memory structures and background processes used to access the database.

• Database is the physical or logical container for data, while instance is the set of processes and memory structures used to access the database.

• Multiple instances can access the same database, but each instance has its own memory and processes.

• Database is persistent, while instance is transient and can be started or stopped.

• Exampl...read more

RAC (Real Application Clusters) is a feature in Oracle DBA that allows multiple instances to access a single database simultaneously.

• RAC enables high availability and scalability by distributing the workload across multiple nodes.

• Each node in a RAC environment has its own instance of Oracle Database and shared access to the same database.

• RAC requires a shared storage solution like Oracle ASM (Automatic Storage Management) or a cluster file system.

• RAC environments can be confi...read more

Physical stand-by is a standby database that is an exact replica of the primary database, while snapshot standby is a read-only standby database that allows for testing and reporting.

• Physical stand-by is an exact replica of the primary database, maintained through redo apply.

• Snapshot standby is a read-only standby database that allows for testing and reporting.

• Snapshot standby can be converted back to a physical stand-by database if needed.

Configuring RAC involves setting up shared storage, network, and database components on multiple nodes.

• Configure shared storage using ASM or NFS

• Configure network components such as VIP, SCAN, and listener

• Install Oracle Grid Infrastructure and Oracle Database software on all nodes

• Create a RAC database using DBCA or manually

• Configure load balancing and failover using services

• Use shell scripting and SQL scripting to automate tasks

• Perform regular maintenance tasks such as patchin...read more

Oracle database architecture consists of multiple components like instance, memory structures, and physical storage.

• Oracle database architecture includes instance, memory structures, and physical storage.

• Instance consists of background processes and memory structures like SGA and PGA.

• Physical storage includes data files, control files, and redo log files.

• Oracle database architecture allows for scalability, high availability, and performance tuning.

SGA is a shared memory area that stores data and control information for Oracle instance. Shared pool is a part of SGA that stores SQL statements and data dictionary cache.

• SGA is a fixed size memory area allocated at instance startup

• SGA contains database buffer cache, redo log buffer, shared pool, etc.

• Shared pool stores frequently used SQL statements and data dictionary cache

• Shared pool can be resized dynamically using ALTER SYSTEM command

Different types of objects in a database include tables, views, indexes, sequences, and stored procedures.

• Tables: Used to store data in rows and columns.

• Views: Virtual tables created by a query.

• Indexes: Improve the performance of queries by allowing quick access to specific rows.

• Sequences: Generate unique numbers.

• Stored Procedures: Precompiled SQL code that can be executed with parameters.

Yes, I have knowledge on cloud computing.

• I have experience working with cloud databases like Oracle Cloud Database

• I am familiar with cloud storage solutions like Amazon S3 and Google Cloud Storage

• I have implemented cloud backup and disaster recovery strategies in previous roles

Yes, I have worked on Oracle Dataguard.

• I have experience in configuring and managing Oracle Dataguard for disaster recovery.

• I have performed switchover and failover operations to test the failover mechanism.

• I have monitored and resolved issues related to Dataguard synchronization and network connectivity.

• I have also worked on configuring Active Dataguard for read-only reporting purposes.

RMAN backups can be performed using commands like BACKUP DATABASE, BACKUP ARCHIVELOG, BACKUP CONTROLFILE, etc.

• BACKUP DATABASE: used to backup the entire database

• BACKUP ARCHIVELOG: used to backup archived redo logs

• BACKUP CONTROLFILE: used to backup the control file

• BACKUP TABLESPACE: used to backup specific tablespaces

• BACKUP AS COPY: used to create a physical copy of data files during backup

A materialized view (mview) is a database object that contains the results of a query. Datapump is a tool for moving data between Oracle databases. Oracle GoldenGate is a real-time data integration and replication tool.

• Materialized views (mviews) store the results of a query for faster access.

• Datapump is a tool used for exporting and importing data between Oracle databases.

• Oracle GoldenGate is a real-time data integration and replication tool used for moving and synchronizing...read more

Yes, I have knowledge on other RDBMS such as MySQL, SQL Server, and PostgreSQL.

• I have experience working with MySQL, including database design and optimization.

• I am familiar with SQL Server and have performed tasks such as backup and recovery.

• I have worked with PostgreSQL and have knowledge of advanced features like partitioning and replication.

DBA stands for Database Administrator. The architecture of DBA involves managing and maintaining databases to ensure data integrity and security.

• DBA is responsible for installing, configuring, and upgrading database software.

• They monitor database performance and troubleshoot issues.

• DBA designs and implements backup and recovery strategies to prevent data loss.

• They also manage user access and security permissions within the database.

• DBA works closely with developers and system...read more

Data pump utility is used for fast data and metadata movement between databases.

• Used for high-speed data and metadata movement between databases

• Can be used to export and import data and metadata

• Provides parallelism and network-based export and import

• Can be used to move data between different Oracle database versions

• Can be used to move data between different operating systems

• Examples: expdp, impdp

Physical files in Oracle DBA refer to the actual files on the operating system that store database data and metadata.

• Physical files include datafiles, control files, redo log files, and archive log files.

• Datafiles store the actual data of the database.

• Control files store metadata about the database structure.

• Redo log files store a record of changes made to the database.

• Archive log files store a copy of redo log files for backup and recovery purposes.

To check DR DC sync, use Data Guard Broker or query V$DATAGUARD_STATS view.

• Use Data Guard Broker to check the status of the standby database and its synchronization with the primary database.

• Query V$DATAGUARD_STATS view to check the redo apply lag and transport lag between the primary and standby databases.

• Check the alert log files of both primary and standby databases for any errors or warnings related to data synchronization.

Causes of query slowness can include inefficient queries, lack of indexes, outdated statistics, hardware limitations, and network latency.

• Inefficient queries - poorly written queries that do not utilize indexes or join tables unnecessarily

• Lack of indexes - missing indexes on columns frequently used in queries

• Outdated statistics - outdated or inaccurate statistics can lead to poor query execution plans

• Hardware limitations - insufficient memory, CPU, or disk I/O can impact quer...read more

Restoring a database using RMAN involves connecting to the target database, restoring the control file, and recovering the database.

• Connect to the target database using RMAN

• Restore the control file from a backup

• Restore the datafiles and tablespaces

• Recover the database to bring it to a consistent state

• Perform necessary checks and validations after restoration

Fundamentals of Oracle DB and Architecture

• Oracle Database is a relational database management system (RDBMS)

• It uses SQL (Structured Query Language) for querying and managing data

• Consists of physical and logical structures like data files, tablespaces, and schemas

• Architecture includes components like instance, memory structures, and background processes

Primary and standby databases can be synced using Data Guard in Oracle.

• Configure Data Guard on primary and standby databases

• Enable redo transport services

• Monitor and resolve any synchronization issues

• Perform switchover or failover when necessary

LNS stands for Log Network Server, responsible for transmitting redo data from the primary database to standby databases. MRP is Managed Recovery Process, used to apply redo data to standby databases.

• LNS is responsible for transmitting redo data from primary to standby databases

• MRP is used to apply redo data to standby databases for data recovery

• LNS and MRP are essential components of Oracle Data Guard for ensuring data protection and availability

To start RFS and MRP Service in Oracle Database, you can use the ALTER SYSTEM command.

• Connect to the Oracle Database as a user with SYSDBA or SYSOPER privilege.

• To start RFS (Remote File Server) Service, execute the following command: ALTER SYSTEM SET REMOTE_LISTENER='listener_name' SCOPE=SPFILE;

• To start MRP (Managed Recovery Process) Service, execute the following command: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

Real Application Cluster (RAC) is a cluster database with shared disk architecture.

• RAC allows multiple instances to access a single database simultaneously.

• It provides high availability and scalability.

• RAC uses Oracle Clusterware to manage the cluster.

• Load balancing and failover are automatic in RAC.

• RAC requires additional hardware and software licenses.