Ans: The Oracle Certified Professional (OCA / OCP) Database Administrator (DBA) certification is designed to reflect proficiency in Oracle database architecture, installation, administration, performance tuning and troubleshooting.
Q2. What is Oracle and what is it used for?
Ans: An Oracle database is a collection of data treated as a unit. The purpose of a database is to store and retrieve related information. ... Oracle Database is the first database designed for enterprise grid computing, the most flexible and cost effective way to manage information and applications.
Ans: You can start up a database with three tools.
Ans: Oracle use Tablespace for logical data Storage. Physically, data will get stored in Data files. Data files will be connected to tablespace. A table space can have multiple data files. A tablespace can have objects from different schema’s and a schema can have multiple tablespace’s. Database creates “SYSTEM tablespace” by default during database creation. It contains read only data dictionary tables which contains the information about the database.
Ans: A checkpoint performs the following three operations: Every dirty block in the buffer cache is written to the data files. That is, it synchronizes the datablocks in the buffer cache with the datafiles on disk. It's the DBWR that writes all modified databaseblocks back to the datafiles.
Ans:
Ans: Oracle home inventory or local inventory is present inside each Oracle home. It only contains information relevant to a particular Oracle home. This file is located in the following location:
$ORACLE_HOME/inventory
It contains the following files and folders:
Ans: A database object is any defined object in a database that is used to store or reference data. Some examples of database objects include tables, views, clusters, sequences, indexes, and synonyms. The table is this hour's focus because it is the primary and simplest form of data storage in a relational database.
Ans: The tasks of Oracle DBA as follows:
Ans: The objects as such can be stored in the database. 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.
Ans: These are the physical components which gets stored in the disk.
Ans: A data block is the smallest unit of logical storage for a database object. As objects grow they take chunks of additional storage that are composed of contiguous data blocks. These groupings of contiguous data blocks are called extents. All the extents that an object takes when grouped together are considered the segment of the database object.
Ans: When you are running dedicated server then process information stored inside the process global area (PGA) and when you are using shared server then the process information stored inside user global area (UGA).
Ans: Two types of storage systems are available :Relational Database Management System (RDBMS) and Hierarchical Storage Management System (HSM)
Ans:
Ans:
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 shutdown cleanly. Database will have to perform instance recovery next time it is started. This option should not be used in regular activities.
Ans: First it will check the syntax and semantics in library cache, after that it will create execution plan.
If already data is in buffer cache it will directly return to the client.
If not it will fetch the data from datafiles and write to the database buffer cache after that it will send server and finally server send to the client.
Ans: EXISTS can be better than IN under various conditions.
UNION ALL is faster than UNION (not sorting).
Ans:
Ans: Be sure to set NLS_LANG for example to "AMERCIAN_AMERICA.WE8ISO8859P1". If your source database is US7ASCII, beware of 8-bit characters. Also be wary of multi-byte characters sets as those may require extra attention. Also watch export/import for messages about any "character set conversions" which may occur.
Ans: Set the WORKAREA_SIZE_POLICY parameter to AUTO and set PGA_AGGREGATE_TARGET.
Ans: The Oracle grid architecture pools large numbers of servers, storage, and networks into a flexible, on-demand computing resource for enterprise computing needs. The grid computing infrastructure continually analyzes demand for resources and adjusts supply accordingly.
For example, you could run different applications on a grid of several linked database servers. When reports are due at the end of the month, the database administrator could automatically provision more servers to that application to handle the increased demand.
Grid computing uses sophisticated workload management that makes it possible for applications to share resources across many servers. Data processing capacity can be added or removed on demand, and resources within a location can be dynamically provisioned. Web services can quickly integrate applications to create new business processes.
Ans:
Ans: SQL*Loader with direct path option can load data ABOVE the high water mark of a table, and DIRECTLY into the datafiles, without going through the SQL engine at all. This avoids all the locking, latching, and so on, and doesn't impact the db (except possibly the I/O subsystem) at all.
Ans: Join methods are of mainly 3 types.
Merge Join :
Nested loop join -:
Hash join :
Ans: Components of logical data model are
Entity - Entity refers to an object that we use to store information. It has its own table.
Attribute - It represents the information of the entity that we are interested in. It is stored as a column of the table and has specific datatype associated with it.
Record - It refers to a collection of all the properties associated with an entity for one specific condition, represented as row in a table.
Domain - It is the set of all the possible values for a particular attribute.
Relation - Represents a relation between two entities.
Ans:
There are six different normal forms
Ans:
Ans: RMAN is faster, can do incremental (changes only) backups, and does not place tablespaces into hotbackup mode.
Ans: With bind variables in SQL, Oracle can cache related queries a single time in the SQL cache (area). This avoids a hard parse each time, which saves on various locking and latching resources we use to check objects existence and so on. BONUS: For rarely run queries, especially BATCH queries, we explicitely DO NOT want to use bind variables, as they hide information from the Cost Based Opitmizer.
Ans: Oracle's SQL and PL/SQL engines are separate parts of the kernel which require context switching, like between unix processes. This is slow, and uses up resources. If we loop on an SQL statement, we are implicitely flipping between these two engines. We can minimize this by loading our data into an array, and using PL/SQL bulk binding operation to do it all in one go.
Ans: Fewer indexes on a table mean faster inserts/updates. More indexes mean faster, more specific WHERE clauses possibly without index merges.
Ans: RAID 5 is striping with an extra disk for parity. If we lose a disk we can reconstruct from that parity disk.
RAID 10 is mirroring pairs of disks, and then striping across those sets.
RAID 5 was created when disks were expensive. Its purpose was to provide RAID on the cheap. If a disk fails, the IO subsystem will perform VERY slowly during the rebuild process. What's more your liklihood of failure increases dramatically during this period, with all the added weight of the rebuild.
Even when it is operating normally RAID 5 is slow for everything but reading. Given that and knowing databases (especially Oracle's redo logs) continue to experience write activity all the time, we should avoid RAID5 in all but the rare database that is MOSTLY read activity. Don't put redologs on RAID5.
RAID10 is just all around goodness. If you lose one disk in a set of 10 for example, you could lose any one of eight other disks and have no troubles. What's more rebuilding does not impact performance at all since you're simply making a mirror copy. Lastly RAID10 perform exceedingly well in all types of databases.
Ans: An Oracle developer is mainly responsible for developing backend applications. They do data modelling according to business rules. They design tables, create indexes and other type of constraints. They are expected to know SQL and PL/SQL. The develop procedures using this languages. 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.
Ans: Passwords for database users are stored in the data dictionary of the database. When a user wants to loginto the database, the username and password provided by the user is checked against the values stored in the database. If the username and password match, the user is granted access to 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 logon to 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 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.
Ans: Truncate is nearly instantaenous, cannot be rolled back, and is fast because Oracle simply resets the HWM. When a full table scan is performed on a table, such as for a sort operation, Oracle reads to the HWM. So if you delete every single solitary row in 10 million row table so it is now empty, sorting on that table of 0 rows would still be extremely slow
Ans: Clustering is one technology used to create a grid infrastructure. Simple clusters have static resources for specific applications by specific owners. Grids, which can consist of multiple clusters, are dynamic resource pools shareable among many different applications and users. A grid does not assume that all servers in the grid are running the same set of applications. Applications can be scheduled and migrated across servers in the grid. Grids share resources from and among independent system owners.
At the highest level, the idea of grid computing is computing as a utility. In other words, you should not care where your data resides, or what computer processes your request. You should be able to request information or computation and have it delivered – as much as you want, and whenever you want. This is analogous to the way electric utilities work, in that you don’t know where the generator is, or how the electric grid is wired, you just ask for electricity, and you get it. The goal is to make computing a utility, a commodity, and ubiquitous. Hence the name ‘The Grid’. This view of utility computing is, of course, a “client side” view.
From the “server side”, or behind the scenes, the grid is about resource allocation, information sharing, and high availability. Resource allocation ensures that all those that need or request resources are getting what they need, that resources are not standing idle while requests are going unserviced. Information sharing makes sure that the information users and applications need is available where and when it is needed. High availability features guarantee all the data and computation is always there, just like a utility company always provides electric power.
Ans: The following are some of the products, tools, and utilities you can use in achieving your goals as a database administrator.
The primary tool for managing your database is Oracle Enterprise Manager, a web-based interface. After you have installed the Oracle software, created or upgraded a database, and configured the network, you can use Oracle Enterprise Manager as the single interface for managing your database. In addition, Oracle Enterprise Manager also provides an interface for performance advisors and an interface for Oracle utilities such as SQL*Loader and Recovery Manager.
Related Interview Questions...