Interview questions for Oracle DBA
1. How to check opatch applied or not in our Oracle home?
opatch lsinventory 100 GB of database size while applying opatch it will take 30 mins, so 500 GB of database size how much time it will take to apply opatch. Same 30 Mins, We will apply the opatch for oracle binary only not for data.
2. What is the difference between Oracle database and Oracle instance?
Oracle database is the collection of datafiles,redo logs and control files while Oracle instance is the SGA ,processes in the Memory. We can have 1 or more instance serving a oracle database . In Oracle RAC, we have one set of datafiles,control file and redo logs while instance on one ore more boxes accesses the same database
3. What is sqlnet.ora ?
It is used to security of our database, Privileged IP address only able to access the database.
4. What is a Tablespace?
Oracle use Tablespace for logical data Storage. Physically, data will get stored in Datafiles. Datafiles will be connected to tablespace. A tablespace can have multiple datafiles. A tablespace can have objects from different schema and a schema can have multiple tablespace. Database creates “SYSTEM tablespace” by default during database creation. It contains read only data dictionary tables which contains the information about the database.
5. what are the different components of physical and logical database structure?
• Physical: Datafile,Control Files, redo logfiles.
• Logical: Database buffer cache, Redo log buffer cache,Shared pool,large pool
6. What are Datafiles?
The 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.
7. What is Database Writer (DBWR)?
It is responsible to writing data from database buffer cache into datafiles.
8. What is Redo Log Files?
The primary function of the redo log is to record all changes made to data. If a failure prevents modified data from being permanently written to the datafiles, then the changes can be obtained from the redo log, so work is never lost.
9. What is a listener process?
It is network connectivity, Application and sql developer tool to access database. Lietener.ora and Tnsnames.ora files are used.
10. What is schema?
A user account and its associated data including tables, Oracle views, indexes, clusters, sequences, procedures, functions, triggers, packages and database links is known as Oracle schema. System, SCOTT etc is default schemas. We can create a new Schema/User. But we can’t drop default database schemas.
11. What is data blocks ?
Data Blocks are the base unit of logical database space. Each data block represents a specific number of bytes of database space on a disk.The data blocks can be 4 K,8 K size depending on the requirement.
12. What is the difference between SPFILE and PFILE?
• Spfile: Server parameter file of binary file, It contains all the information for setting the database initialization parameters. SPFILE permits dynamic changes without requiring you to restart that instance.
• Pfile: It is text parameter file, information for setting the database initialization parameters.
13. What is an Extent ?
Extent is a collection of Continuous data blocks, which is used for storing a specific type of information.
14. What is a Segment ?
A segment is a collection of extents which is used for storing a specific data structure and resides in the same table space.
15. What is the purpose of collecting statistics?
It will help optimizer to generate best execution plan
16. What is archivelog and Noarchivelog mode?
We all know that redo logs stored the redo information and redo log files are in circular fashion.Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log. The process of turning redo log files into archived redo log files is called archiving. The background process ARCn automates archiving operations when automatic archiving is enabled. The database starts multiple archiver processes as needed to ensure that the archiving of filled redo logs does not fall behind. No archive log means archive log are not generated and redo are overwritten
17. What is an Oracle index?
It is an database object used to increase the performance for retrieval data.
18. What are the roles of DBA?
A DBA has the authority to create new users, remove the existing users, or modify any of the environment variables or privileges assigned to other users.
• Manage database storage
• Administer users and security
• Manage schema objects
• Monitor and manage database performance
• Perform backup and recovery
• Schedule and automate jobs
19. If the table is fragmented, how would you rebuild it?
Alter table table name move, We can analyze the table.
20. What are the different tools that are provided by Oracle to assist performance monitoring?
AWR, ADDM, Trace, TKPROF, OEM
21. What is a database link? How to create it?
If we want to access objects of another database from this database then we need a database link from this database to the other.
1. Login as oracle user
2. sqlplus “/as sysdba”
3. Create database link connect to identified by using:
Create database link MY1_TO_MY2 connect to apps identified by apps using ‘MY2′;
Database link created.
22. What are the benefits of ORDBMS?
In ORDBMS, the objects can be stored as they are. The language of the DBMS can be integrated with an object-oriented programming language. The language may even be exactly the same as that used in the application, which does not force the programmer to have two representations of his objects.
Hiya, I’m really glad I have found this information. Nowadays bloggers publish just about gossip and internet stuff and this is really irritating. A good website with exciting content, this is what I need. Thanks for making this site, and I will be visiting again. Do you do newsletters by email?