Blog
Oracle DBA Interview Questions For Freshers
Oracle DBA Interview Questions For Freshers
Q1. List four possible ways (direct or indirect) to execute an SQL query against an Oracle Database?
Ans. (Oracle DBA Interview Questions For Freshers)
- Using the SQL*Plus command-line tool. With this tool, you can directly execute SQL commands.
- Using a GUI (Graphical User Interface) tool like SQL Developer. You can directly execute SQL commands with such tools.
- Using Oracle Enterprise Manager. This is an indirect way of executing an SQL query. When you perform certain operations with Oracle Enterprise Manager, they are converted to SQL queries implicitly and these SQL queries are executed against the database.
- Writing your own program. This is not a conventional way of executing your queries but actually, it is widely used. Any web or windows program that uses an Oracle database at the backend, executes SQL queries. These programs are written using a programming language like .NET or JAVA and they use a driver to connect to the database.
Q2. What Is SQL*Plus? How can one acquire it and what kind of operations can be performed with it?
Ans.
- SQL*Plus is a command-line tool developed by Oracle Corporation.
- It is freely distributed. It is shipped with Oracle client installations or Oracle database installations as a default. So, if Oracle client or Oracle database software is installed on a computer, you can find it under the “$ORACLE_HOME/bin/” directory. The name of the executable is “surplus” on Linux systems and “sqlplus.exe” on Microsoft Window Systems.
- You can connect to an Oracle database with it. Once connected, you can execute Oracle commands or SQL queries against the connected database. SQL*Plus has also its own commands for formatting the output so that you can display the results in a meat way.
Q3. Who is responsible to update the indexes?
Ans. Oracle automatically maintains and uses indexes and when any change is made in the table data Oracle automatically distributes it into relevant indexes.
Q4. In our organization, we’re using an Oracle database whose version is 11.2.0.4. Explain what each digit shows?
Ans. “11”: This first digit shows the major database version. Oracle usually publishes a major release once a 4 year. This digit is usually followed by a character describing the nature of the release. For example: 9i (internet), 10g (grid), 11g (grid), 12c (cloud).
“2”: This second digit shows the maintenance release number of the software. Oracle publishes the major release as maintenance release 1 and then usually publishes a second maintenance release during the lifetime of the software. New features are added to database Software with maintenance releases.
“o”: This third digit is Fusion Middleware Number. This will be o for database software.
“4”: This fourth digit is called Component-Specific Release Number and it shows the path set update that was applied to the software. Patch set updates are published 4 times a year by Oracle and as you apply them to your database software, this fourth digit advances.
Q5. In Oracle terminology, what is a Synonym?
Ans. A synonym is an identifier that can be used to reference another database object in a SQL statement. The types of database objects for which a synonym may be created are a table, view, sequence, or another synonym.
Q6. Your client said that he forgot the password for the “SYSTEM” user of his database and he no longer could connect. How would you recover this admin password?
Ans.
- If there are other users who have “DBA” privileges, you can connect with those users and change the password for the “SYSTEM” user. The users who have DBA privileges have the privilege to change any user’s password. This option is the easiest method but this may not be the case in all scenarios.
- If there are no other users with “DBA” privileges then the only way to connect to the database is to connect using operating system privileges. The Oracle software runs under a specific user at the operating system. This user is usually named “oracle”. Also, there needs to be a user group that the “oracle” user belongs to. This user group is usually named “dba”. The operating system users who belong to the “dba” group can connect to the database with “SYSDBA” privileges. So, you need to ask the system administrator to logon to a server as an “oracle” user or any user who belongs to this “dba” group. Once logged on to the operating system, you can connect to the database locally using operating system authentication with SYSDBA privileges. After connecting to the database, you can change the reset the password for this system user.
Q7. What is a password file and why is it needed?
Ans. Passwords for database users are stored in the data dictionary of the database. When a user wants to log into the database, the username and password provided by the user are checked against the values stored in the database.
If the username and password match, the user is granted access to the database. The data dictionary is part of the database and it will be accessible as long as the database is open. The passwords for administrators are stored in the dictionary as well.
When the database is closed, the data dictionary will be inaccessible. There needs to be a mechanism for administrators to login into the database even when it is closed because it is one of the administrator’s tasks to start up a down database. A password file is a separate operating system file that is stored on a disk outside of the database.
The username and password for the users who have SYSDBA or SYSOPER privileges are stored in it. Administrators who have those privileges are authenticated using this password file even when the database is down.
Q8. You want to find out how many users are defined in the password file and what privileges those users have. How would you accomplish this?
Ans. You need to query the “v$pwfile_users” view to get information about the existing users in the password file. Execute the SQL query below:
Sql>SELECT * FROM v$pwfile_users;
The query above will return four columns for each user in the password file. The column names are USERNAME, SYSDBA, SYSOPER, and SYSASM.
- The USERNAME column shows the username of the user in the password file.
- The SYSDBA column shows whether the user has SYSDBA privileges or not.
- The SYSOPER column shows whether the user has SYSOPER privileges or not.
- The SYSASM column shows whether the user has SYSASM privileges or not.
Q9. What would be the main responsibilities of an Oracle DBA in an organization?
Ans. The main duty of an Oracle DBA is to keep the Oracle Databases of the organization up and running. This may involve installing and configuring a database from scratch.
On a running system, the DBA will be the only privileged person who can shut down and startup the database.
The DBA will create new users and manage the privileges of each user.
He will take regular backups to ensure that data is safe. In case of a disaster, he will be responsible for restoring the database from backups. He will have to do monitor the space usage and do capacity planning for the database.
He will be responsible for enforcing security policies. He will have to monitor database activities. He will have to tune the database so that it works at an acceptable speed.
He is expected to follow the latest patches and apply them when applicable.
Q10. How does an Oracle DBA role differ from an Oracle Developer role in an organization? Are there any similarities between these two?
Ans. An Oracle developer is mainly responsible for developing backend applications. They do data modeling according to business rules. The design tables, create indexes and other types of constraints. They are expected to know SQL and PL/SQL. They develop procedures using this language.
However, the Oracle developers are not expected to administer the database software itself.
On the other side, an Oracle DBA’s main duty is to administer the database which involves tasks like doing maintenance to keep the databases up and running, taking backups, enforcing security policies, etc. DBAs are not primarily assigned to develop code.
DBAs are supposed to have a good knowledge of SQL and PL/SQL like a developer as these are also required for administering the database.
According to the structure of the organization, DBAs might also be assigned development tasks or at least assist the developers where necessary.
Q11. There are 10 identical servers and you want to install Oracle Database on each of them. What would you use to automate the installation process?
Ans. If you are going to do batch installations, it is best to do it with Oracle Universal Installer in silent mode. For single installations.it is best to start the installer in “interactive mode” and set installation options at each window.
However, in batch installations, this will take longer. You need to do the installations in “silent” mode with a “response file”. In a silent installation, you start the Oracle Universal Installer from a command prompt and specify the location of the “response file”.
The installation files and the response file can be shared among the servers via NFS so that you won’t have to copy the setup files to each server.
Q12. You want to create a response file to speed up the installation of databases. How would you prepare a response file?
Ans. A response file is a plain text file, where options to create a database are stored. It is possible to create it manually from scratch but that would take longer and would be erroneous.
Installation media comes with a template response file. It is rather easier to customize it manually. This file also contains notes about the parameters.
However, the easiest and most reliable way to create a response file is by using Oracle Universal Installer. If you start the installer in “record” mode, every option you choose at each step is automatically recorded in a response file in the correct format. After the installer completes in “record” mode, you’ll have a complete response file with all the options set in it.
Q14. When creating a database with SQL script, what would you specify in the script?
Ans. It is also possible to create a database via an SQL script. In this script I would specify:
- Name of the database
- The password of the SYS user
- The password of the SYSTEM user
- At least three online redo log groups. I would also specify at least two members for each redo log group.
- Character set and the national character set of the database.
- Location and size of the SYSTEM and SYSAUXtablespace. These tablespaces will be used for holding system data.
- I would specify a normal tablespace to use as the default tablespace of the database.
- I would specify a temporary tablespace to use as the default temporary tablespace of the database.
- I would specify an undo tablespace.
Q15. What makes up an Oracle Instance?
Ans. An instance is made up of a shared memory region on RAM called System Global Area (SGA) and background processes.
The system’s global area is a shared memory, which means it can be accessed by multiple processes.
This holds data that is required by the instance to operate.
The background processes are operating system processes and each process has a specific responsibility in the instance.
The System Global Area and background processes are created when the instance is “started”. When the instance is “shut down”, the processes are killed and the shared memory region is “released” back to the operating system.
Q16. What constitutes an Oracle Database?
Ans. An Oracle database resides on disk and this is permanent. It is composed of files that are stored on a disk. These files can be categorized into three types:
- DataFiles: These files hold “user” data or “system” data. Any data that belongs to an application is an example of “user” data. The “data dictionary” of the database is an example of “system” data.
- OnlineRedo Log Files: These files hold the “change” records. Any change, which will be made to a data file, is first written to online redo log files
- ControlFiles: These files are relatively small but they are essential for a database. They hold information about the physical structure of the database like the location of data files, online redo log files, etc.
Q17. Which tools can you use to start up an Oracle database?
Ans. You can start up a database with three tools.
- SQL*Plus: This is the most widely used option. You first connect to an idle instance with SQL*Plus and then startup the instance with the “startup” command.
- Oracle Enterprise Manager: This is another way of starting up a database. You can logon to Oracle Enterprise Manager even if the database is stopped. OEM will detect the status of the down database and will present to you the “Startup” button. You can startup the database by clicking this button.
- RMAN: This is rather a less used tool for starting up a database but it is possible to startup a database from the Recovery Manager command line.
Q18. During the startup of a database, at which order does Oracle software search a parameter file?
Ans. A parameter file holds instance parameters that govern how an instance operates. In order to start up an instance, Oracle needs to locate this file.
The search order is as below: /DBS/spfile.ora – This is a server parameter file and this is the first place that oracle will look for. SID- is the service identifier of the instance.
<$ORACLE_HOME-/dbs/spfile.ora -If Oracle cannot find the file in the first location, it will search this file. This is again a server parameter file.
/dbs/init.ora – This is a parameter file and it is plain text. If Oracle cannot find the two files listed above, it will search for this file. This is the last location to search.
Q19. At what stages does an instance pass while starting up?
Ans. You can start up a database with the modes below:
- NOMOUNT: This is the first stage. In this mode, the instance is started.
- MOUNT: This is the second stage. In this mode, the instance is started and the database is mounted. However, the database is not open so you cannot still access data. However, you can perform several maintenance tasks at this stage.
- OPEN: This is the final stage. The database is open and all the data is accessible. The default open mode is “read/write” which means you can read data or write to it. However, it is also possible to open it in a “read-only” mode where you can only read data but cannot change it.
Q20. You want to do maintenance on your database but during the maintenance period, you don’t want any user to be able to connect to the database. How would you accomplish this?
Ans. When a database is open, any user with the “CREATE SESSION” privilege can make a connection. However, it is possible to open the database in “restricted” mode.
When a database is open in restricted mode, only users with the “RESTRICTED SESSION” privilege can make a connection to the database. By default, only DBAs have the “RESTRICTED SESSION” privilege and it should not be granted to regular users.
Opening a database in “restricted” mode is a good way to prevent regular users from accessing the database during maintenance.
Q21. Your database is open. You don’t want to interrupt currently connected users but you want to temporarily disable further logons. What would you do to achieve this and how would you revert the database back to its normal state after that?
Ans. I would put the database in “restricted mode”. While in restricted mode, only users with the “RESTRICTED SESSION” privilege can make a connection. I would run the below command to put the database in restricted mode:
Sql> alter system enable restricted session;
After executing this command regular users won’t be able to loggon into the database. Once I want to revert the database to normal, I execute this command:
Sql>alter system disable restricted session;
Q22. What are the types of shutdown modes of an Oracle database?
- Normal: In this mode, no new connections are allowed and the database is closed after all the sessions disconnect themselves.
- Immediate: No new connections are allowed and the existing active transactions are rolled back. Changes made by an active transaction are lost in this option.
- Transactional: No new connections are allowed and Oracle waits until all active transactions are completed.
- Abort: This happens immediately however the database is not shut down cleanly. The database will have to perform instance recovery next time it is started. This option should not be used in regular activities.
Q23. The data files of your database reside on a storage system. You want to take a snapshot of the storage so that you can use it for backup purposes. You also want to ensure that no data is written to data files while the snapshot is being taken. Is it possible to accomplish this while the database is open?
Ans. Yes, it is possible to stop all I/O activity while the database is open. Normally, when a database is open, there will be constant I/O to online redo log files or data files. Even if the database is idle, there is no guarantee that the database will not write anything to files during the snapshot.
However, if you “suspend” the database, Oracle will halt I/O operations to these datafiles until it is reverted back to normal mode. So, you should “suspend” the database, take the snapshot of the disk and then put the database back in normal mode immediately after that.
Q24. What kind of information can be given while creating a sequence?
Ans.
- Sequence Name: This is the name of the sequence. It should be unique inside the schema.
- Start With: This is the number that the sequence will start.
- Increment By: This number shows how much the sequence will increment at each move.
- No cycle: This determines whether the sequence will start from the beginning once it reaches the end.
- Nocache: This determines how much the next sequence number will be cached in SGA. Nocache means no next sequence will be cached.
Q25. You want your database to start automatically, after a reboot of the server. How would you do that?
Ans. In the default configuration, the Oracle database will not automatically start after the server reboots. You’ll have to start it manually after each reboot. You’ll usually want it to start automatically. There are two methods to accomplish this:
- Using Oracle Restart: “Oracle Restart” is a feature of Oracle High Availability Service (OHAS). You need to install “Grid Infrastructure” to enable the “Oracle Restart” feature. Using “Oracle Restart” is the recommended way.
- Using Your Own Script: It is also possible for you to write your own “bash” script to start the database and place that script in the startup of the operating system.
Q26. Which components of your database environment can be protected by an “Oracle Restart” configuration?
Ans.
- Database Instances and Automatic Storage Management (ASM): Database instances and ASM instances will be restarted if they crash somehow.
- Oracle NET Listener: Oracle NET Listener will be started if it crashes and stops listening for an incoming connection.
- ASM Disk Groups: Oracle Restart will mount ASM Disk groups if they are dismounted.
- Database Services: Non-default database services will be started by the Oracle Restart feature.
- Oracle Notification Services (ONS): This is another Oracle component that can be protected by Oracle Restart.
Q27. Explain the difference between “shared server” architecture and “dedicated server” architecture?
Ans. When a user connects to a database, he sends SQL queries to the database to execute. These SQL queries are executed by a “server process” and the result is returned back to the user. In the “dedicated server” architecture, the instance will create one server process for each connected user.
That process will be “dedicated” to that user and will only serve that client.
However, in “shared server” architecture, a single server process will serve multiple clients. In shared server architecture, the total memory consumption will be less.
However, certain operations like DBA activities can only be performed on a dedicated server.
Hope you liked Laraonlinetraining Oracle DBA Interview Questions For Freshers