User-level administration i.e. creates users, remove existing users or modifies user permissions.
Maintains database security.
Manages database storage & objects.
Tunes performance of a database.
Performs backups & recovery tasks.

What is Oracle database?

Oracle database is used to store and retrieve information. It contains physical and logical structures. Physical structures are controlled file, datafile and redo log file. The logical structure is a database, tablespace, segment, extent, and blocks.

What are datafiles?

All the tables are stored in data files. The data file has all the data stored.

How will you recover the database from a loss of a data file?

The database can be recovered using the backup which is taken already. Using rman we can recover from the failure.

How to check the current database name?

Select name from v$database;

What is ora- error?

Snapshot too old error happens when it does not have enough space in the undo. So increasing the space will solve the problem.

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

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:

First Normal Form (NF)
Second Normal Form (NF)
Third Normal Form (NF)
Boyce-Codd Normal Form (BCNF)
Fourth Normal Form (NF)
Fifth Normal Form (NF)

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

Materialized view is a database object that holds query results. If materialized views are used instead of tables or views in complex query executions, performance gets enhanced as re-execution is not required for repeated queries.

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

An index is a schema object, which can search the data efficiently within the table.

Indexes can be clustered or non-clustered. Differences include:

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.
Leaf node of a clustered index holds the data pages while the non-clustered index holds the index rows.

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

Given below is the list of different components.
The physical structure includes:

Data files, which hold all the DB objects like tables, views, indexes, etc.
Redo Log files, which maintains the records of database changes as a result of user transactions.
Control files, which maintain the database status and physical structure.

The logical structure includes:

Tablespace, which is a logical storage unit where the database object resides.
Segments are logical storage units only but within a tablespace.
Extent is the logical unit where various contiguous data blocks and extents together form a segment.
A data block is the smallest logical storage unit in the database.

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

On a higher level, there are 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?

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 hours operational and hence, cannot support bringing the database down. Hence, DBAs need to take the backup in online mode only.

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

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.

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

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?

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 is the difference between restoring a database and recovering a database?

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.

Database full backup is taken on Friday PM
Database crash happened on Saturday AM

We can restore the lost files using the PM full backup which is Restoration. However, the data will be restored up till Friday at PM and not till Saturday at 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?

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?

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 AM on Friday and the next backup is done at AM Saturday. The second incremental backup will only have the transactions occurred after Friday at 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 AM and then differential backup on Saturday at AM, it will take the backup of the files changed since Friday, AM. Further, if the differential backup is taken on Sunday at AM, it will take the backup of the files changed since Friday, AM.

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

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 hours and if a user accidentally deletes the data at AM with commit performed. Then, using FLASHBACK QUERY, he can retrieve these rows until PM only.

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

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?

Recovery catalog is a database schema that holds the metadata used by RMAN for restoration and recovery processes.

It basically stores information on

Datafiles & their backup files.
Archived Redo Logs & their backup sets.
Stored scripts
Incarnation
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?

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

Manually create a control file.
Restore it from the backup control file using the below command.

ALTER DATABASE BACKUP CONTROL FILE TO TRACE;

Restore using RMAN backup by using the below commands.

setdbid XX;

restorecontrolfile;

What is the difference between media recovery & crash recovery?

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?

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

Maintaining high availability of the system.
Managing workload with the least expenses.
Scalability & agility.

How would you differentiate between cluster and grid?

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?

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 a single instance environment be converted into the RAC environment and how will they be different?

Single instance can be converted into RAC using one of the below methods:

Enterprise Manager
DBCA i.e. Database Configuration Assistant
RCONFIG Utility

Single Instance environment Vs RAC Environment

Parameters	Single Instance Environment	RAC Environment
Instance	Instance	Multiple
Memory	Instance will have dedicated SGA	Every instance will have separate SGA
Access to physical files	Only one instance will access data files and control files.	Data files and Control Files are shared across all instances.
Flash Recovery Log	Accessed by single instance.	Shared by multiple instances.
Redo Logs	Dedicated to single instance.	Only one instance can write at a time but others can read data during recovery or archiving process.

Why do we need to rebuild indexes?

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.

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 monitor the space allocations in a database?

We can use the below data dictionary tables to monitor the space allocations:

DBA_FREE_SPACE
DBA_SEGMENTS
DBA_DATA_FILES

What do you understand by “Performance Tuning of DB” & what are the different areas where we can perform tuning?

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:

Database design.
Memory allocation.
Disk I/Os.
Database contention.
OS level (CPU).

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

Various tools include:

AWR(Automatic Workload Repository)
ADDM(Automated Database Diagnostics Monitor)
TKPROF
STATSPACK
OEM(Oracle Enterprise Manager)

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

There are two types of optimizers:

Rule-Based Optimizer (RBO): If the referenced objects don’t maintain any internal statistics, RBO is used.
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?

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?

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.

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

Enlisted are a few of the best practices for writing SQL queries.

Column names should be provided instead of * in SELECT statements.
Joins should be used in the place of sub-queries.
EXISTS should be used instead of IN to verify the existence of data.
UNION ALL should be used in the place of UNION.
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?

Below is the indications for the same:

Getting an ORA- error.
Degrading the performance even when all the other parameters are already optimized.
Poor library cache/data dictionary hits.

What is table partitioning and why is it required?

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?

We can find it out using v$session_waits and v$ system _waits.

What is a pluggable database (PDB) in Multitenant Architecture?

Pluggable Databases (PDBs) is new in Oracle Database c Release (.). You can have many pluggable databases inside a single Oracle Database occurrence. Pluggable Databases are fully backwards compatible with an ordinary pre-. database.

Why would I consider using the Multitenant option?

You should consider following Database Consolidation Goals to achieve:

Reduce Total Cost of Operation
- Administration costs
- Operations costs
- Data center costs
- Storage costs
- Contingency costs
Improve Functionalities
- Resource utilization
- Manageability
- Integration
- Service management
Must not need to change applications
Must not harm performance
Must provide resource management and isolation between applications
Must simplify patching and upgrade of Oracle Database

What other benefits will I get from Multitenant option?

The benefits of mulitenant Pluggable Databases are:

Fast provisioning of a new database or of a copy of an existing database.
Fast redeployment, by unplug and plug, of an existing database to a new platform.
Quickly patch or upgrade the Oracle Database version for many databases and for the cost of doing it once.
Patch or upgrade by unplugging a PDB and plugging it into a different container database (CDB) in a later version.
A machine can run more database instances in the form of PDBs than as individual, monolithic databases.
Separate the duties of the application administrator from the duties of the administrator of the Oracle-supplied system.

How easy to migrate an existing pre . database to c Multitenant database?

Migrate to c Pluggable database is very simple and easy. You can evaluate and adopt which one best suit for you.

Plan A.

Upgrade an existing pre . database to .
Plug-in the database post upgrade into a CDB

Plan B.

Provision empty PDBs per database that need to consolidated
Use datapump or golden gate replication to migrate a Database into a PDB

Which Oracle Database features are currently not supported in a Multitenant architecture?

The following Oracle Database features are currently not supported in a CDB:

Continuous Query Notification
Flashback Data Archive
Heat Maps
Automatic Data Optimization

If you must use one or more of these features, then create a non-CDB.

Does each non-CDB need its own CDB if I do not have a license for Multitenant?

Yes

Can multiple CDBs run on the same server?

Yes

Can multiple CDBs run out of the same ORACLE_HOME installation?

Yes

How do I know if my database is Multitenant or not?

Establish a SQL Session and run this Query:

SQL> select NAME, DECODE(CDB, 'YES', 'Multitenant Option enabled', 'Regular c
Database: ') "Multitenant Option ?" , OPEN_MODE, CON_ID from V$DATABASE;


NAME Multitenant Option? OPEN_MODE CON_ID

--------- ------------------------------ -------------------- ----------

CDB Multitenant Option enabled MOUNTED

What Pluggable databases do we have in this container database ?

To view pluggable database in the container databases execute the below query.

SQL> Show pdb's;

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ --------------- ---------------

PDB$SEED READ ONLY NO

PDB READ WRITE NO

PDB MOUNTED

PDB READ WRITE YES

How do I connect to a Pluggable Database, say PDB?

You can switch to PDB from other PDB or Root Container using following command.

SQL> alter session set container = pdb;

Connecting to a PDB directly Using the SQL*Plus CONNECT Command:
You can use the following techniques to connect to a PDB with the SQL*Plus CONNECT command:
A) Database connection using easy connect:

SQL> CONNECT username/password@host[:port][/service_name][:server][/instance_name]

Examples of SQLPLUS from OS prompt:

$ sqlplus user/password@//node:/pdb

$ sqlplus user/password@//localhost:/pdb

$ sqlplus user/password@//localhost/pdb

SQL> show con_name

CON_NAME

------------------------------

PDB

B) Database connection using a net service name:
Example TNSNAMES.ora:

LISTENER_CDB =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = ))

CDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = ))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = cdb)

)

)

PDB =

(DESCRIPTION =

(ADDRESS = (PROTOCOL = TCP)(HOST = hpal-node.us.oracle.com)(PORT = ))

(CONNECT_DATA =

(SERVER = DEDICATED)

(SERVICE_NAME = pdb)

)

)

Example of SQLPLUS from Os prompt:

$ sqlplus user/password@pdb

How do I switch to main container Database?

SQL> ALTER SESSION SET CONTAINER = CDB$ROOT;

How do I determine which PDB or CDB I am currently connected to?

SQL> show con_name

CON_NAME

------------------------------

PDB

SQL> select sys_context ( 'Userenv', 'Con_Name') "Container DB" from dual;

Container DB

--------------------------------------------------------------------------------

PDB

How do I start up a Pluggable database?

When connected to current PDB:

SQL> alter pluggable database open;

When connected to root:

SQL> alter pluggable database pdb open;

How do I shutdown / close a Pluggable database?

When connected to current PDB:

SQL> alter pluggable database close;

When connected to root:

SQL> alter pluggable database pdb close;

How do I shut down / Startup the Container Database?

Use startup/Shutdown command similar to startup/shutdown of Non-CDB. When the container database is shutdown, no PDB is accessible.

In a CDB, the root and all of the PDBs share a single instance, or, when using Oracle RAC, multiple concurrent database instances. You start up and shut down an entire CDB, not individual PDBs. However, when the CDB is open, you can change the open mode of an individual PDB by using the ALTER PLUGGABLE DATABASE statement.

Which parameters are modifiable at PDB level?

SQL> select NAME, ISPDB_MODIFIABLE from V$PARAMETER;

What common users do I have in my cdb?

SQL> select distinct USERNAME from CDB_USERS where common = 'YES';

How do I create a common user?

SQL> create user c##db_dba identified by manager container=all;

How do I create a local user?

SQL> create user pdb_dba identified by manager container=current;

What is the difference between Container ID Zero and One?

CON_ID “” means data does not pertain to any particular Container but to the CDB as a whole. For example, a row returned by fetching from V$DATABASE pertains to the CDB and not to any particular Container, so CON_ID is set to “”. A CONTAINER_DATA object can conceivably return data pertaining to various Containers (including the Root which has CON_ID==) as well as to the CDB as a whole, and CON_ID in the row for the CDB will be set to .

Following table describes various values of CON_ID Column in Container Data Objects.
= The data pertains to the entire CDB
= The data pertains to the root
= The data pertains to the seed

– = The data pertains to a PDB, Each PDB has its own container ID.

Are there any background processes ex, PMON, SMON etc associated with PDBs?

No. There is one set of background processes shared by the root and all PDBs.

Is there separate control file required for each PDB?

No. There is a single redo log and a single control file for an entire CDB.

Is there separate Redo log file required for each PDB?

No. There is a single redo log and a single control file for an entire CDB.

Can I monitor SGA usage on a PDB-by-PDB basis?

There are single SGA shared by all pluggable databases. However, you can determine SGA consumptions by all containers i.e, root and PDB.

SQL> alter session set container=CDB$ROOT;

SQL> select POOL, NAME, BYTES from V$SGASTAT where CON_ID = '&con_id';

SQL> select CON_ID, POOL, sum(bytes) from v$sgastat group by CON_ID, POOL order by CON_ID, POOL;

Can I monitor PGA usage on a PDB-by-PDB basis?

Use the below queries to monitor the SGA usage on per PDB basis.

SQL> select CON_ID, sum(PGA_USED_MEM), sum(PGA_ALLOC_MEM), sum(PGA_MAX_MEM)

from v$process

group by CON_ID order by CON_ID;

SQL> alter session set container =CDB$ROOT;

SQL> select NAME , value from v$sysstat where NAME like 'workarea%';

SQL> alter session set container = [targetPDB];

select NAME , value from v$sysstat where NAME like 'workarea%';

Do I need separate UNDO tablespaces for for each of my PDB?

There is one active undo tablespace for a single-instance CDB. For an Oracle RAC CDB, there is one active undo tablespace for each instance. Only a common user who has the appropriate privileges and whose current container is the root can create an undo tablespace.

Do I need separate SYSTEM tablespaces for each of my PDB?

There is a separate SYSTEM tablespace for the root and for each PDB.

Do I need separate SYSAUX tablespaces for for each of my PDB?

There is a separate SYSAUX tablespace for the root and for each PDB.

Do I need Temporary tablespaces for for each of my PDB?

There is one default temporary tablespace for the entire CDB. However, you can create additional temporary tablespaces in individual PDBs. One active temporary tablespace is needed for a single-instance CDB, or one active temporary tablespace is needed for each instance of an Oracle RAC CDB.

Can I specify a separate default tablespace for the root and for each PDB?

Yes. You can specify a separate default tablespace for the root and for each PDB.

Are all physical datafiles separate for root and PDB?

There are separate datafiles for the root, the seed, and each PDB.

Where is user data stored in CDB?

In a CDB, most user data is in the PDBs. The root contains no user data or minimal user data.

Does Pluggable database support separate database characterset?

A CDB uses a single character set. All of the PDBs in the CDB use this character set. Oracle recommends the following:

For all new deployments and if all PDBs are created empty, Oracle strongly recommends ALUTF for the CDB database character set and ALUTF for the CDB national character set.
If you can migrate your existing databases to ALUTF before consolidation, Oracle recommends that you do so and consolidate into one or more ALUTF CDBs, depending on your needs. You can use Oracle Database Migration Assistant for Unicode to migrate a non-CDB to ALUTF. You cannot migrate the CDB using Oracle Database Migration Assistant for Unicode, after creation.
If you cannot migrate your existing databases prior to consolidation, then you have to partition them into sets with plug-in compatible database character sets and plug each set into a separate CDB with the appropriate superset character set.

How do I configure Net Files in a Pluggable database environment?

There is a single listener.ora, tnsnames.ora, and sqlnet.ora file for an entire CDB. All of the PDBs in the CDB use these files.

How can I install and setup Pluggable Database?

Use runInstaller to install the Oracle Database software. Use dbca to create databases. You can create many pluggable databases in a single operation. DBCA enables you to specify the number of PDBs in the CDB when it is created.After a CDB is created, you can use DBCA to plug PDBs into it and unplug PDBs from it.

What Operations act on PDBs as entities?

These operations act on PDBs as entities:

create PDB (brand-new, as a clone of an existing PDB, by plugging in an unplugged PDB)
unplug PDB
drop PDB
set the Open_Mode for a PDB

How can I create a pluggable database?

Use the below queries to create a pluggable database.

sql> create pluggable database x admin user a identified by p;

sql> create pluggable database y admin user a identified by p 
file_name_convert = ('pdbseed', 'y');

How to drop a PDB irrevocably?

Use the below queries to drop a PDB database irrevocably.

sql> drop pluggable database x including datafiles;

How easy it is to manage the provisioning of PDBs using PL/SQL?

Following an Example of PL/SQL Code to show this.

Using Oracle-Managed Files

declare

t integer not null := -;

procedure Show_Time(What in varchar) is

t varchar();

begin

t := Lpad((DBMS_Utility.Get_Time() - t), );

DBMS_Output.Put_Line('create PDB:'||t||' centiseconds');

end Show_Time;

begin

t := DBMS_Utility.Get_Time();

execute immediate '

create pluggable database x

admin user a identified by p

';

Show_Time('create PDB:');

t := DBMS_Utility.Get_Time();

execute immediate '

drop pluggable database x

including datafiles

';

Show_Time('drop PDB: ');

end;

How to clone a PDB from an existing PDB?

The clonee must be open in read only mode. Example using Oracle-Managed Files:

sql> create pluggable database x from x;

How to unplug a PDB?

Use the below query to unplug a plugged database.

sql> alter pluggable database x unplug into '/some_directory/x_description.xml' ;

The into keyword must be followed by the full path for a description of the PDB, in XML, generated by the operation.

How to add or modify a user-managed service?

sql> srvctl add service –pdb [pdb_name]

Starting a user-managed service using srvtcl will open the PDB automatically in all the instances in which the service is started. Specifying the empty string (“”) as the [pdb_name] will cause the pluggable database attribute of a service to be set to null. The service can then be used only to connect to the root.

How can I view which service is attached to my Pluggable database?

SQL> column NAME format a

SQL> select PDB, INST_ID, NAME from gv$services order by ;

PDB INST_ID NAME

-------------------------------- ---------- --------------------------------

CDB$ROOT cdbXDB

CDB$ROOT SYS$BACKGROUND

CDB$ROOT SYS$USERS

CDB$ROOT cdb

PDB pdb

PDB pdb

Where can I find the Alert log for my pluggable Database?

A Single copy of Alert log is generated which contains warnings and alert information for all PDBs. XML version of alert can be found in “Diag Alert” and text formatted Aler log can be found in “Diag Trace” of the container database. You can find details by selecting from v$diag_info dynamic view.

Where can I find trace files related to my pluggable Database?

All traces generated from all PDBs are currently found in “Diag Trace” of the container database. You can find details by selecting from v$diag_info dynamic view.

If a user-defined, common user creates schema objects in a PDB, and if later that PDB is unplugged and plugged into a different CDB in which that common user does not exist, then what happens to the schema objects? By which user will they be owned? Will other users within the PDB, which had been granted privileges on those schema objects, still retain those privileges?

If you plug a PDB that contains a common user into a CDB, then the following actions take place:
– The common user accounts in this PDB lose commonly granted privileges that they may have had, including the SET CONTAINER privilege.
– If the target CDB has a common user with the same name as a common user in a newly plugged-in PDB, then the new common user is merged with the target CDB common user.
– The password of the target CDB common user takes precedence. Otherwise, a common user in a newly plugged in PDB becomes a locked account. In this case, you can do one of the following:

Leave the user account locked and use the objects of its schema.
Use Oracle Data Pump to copy these objects to another schema, and then drop the locked user account.
Close the PDB, connect to the root, and then create a common user with the same name as the locked account. When you re-open the PDB, Oracle Database resolves the differences in the roles and privileges that were commonly granted to the locked user. Afterward, you can unlock this user account in the PDB. Privileges and roles that were locally granted to the user will remain unchanged.

Is the multitenant option available in Standard Edition?

Yes, but you may only create one PDB, per CDB.

Can a transaction span across PDBS?

No, though “alter session set container” is allowed after starting a transaction in a PDB, only select is allowed in the second PDB. The transaction is preserved and you can do commit or rollback after switch back to original PDB.

What data can be seen in CDB_ and V$ views from each container?

CDB_* views are container data objects. When a user connected to the root queries a CDB_* view, the query results will depend on the CONTAINER_DATA attribute for users for the view. The CONTAINER_DATA clause of the SQL ALTER USER statement is used to set and modify users’ CONTAINER_DATA attribute.

In the root of a multitenant container database (CDB), CDB_* views can be used to obtain information about tables, tablespaces, users, privileges, parameters, and so on contained in the root and in pluggable databases (PDBs).

The CDB_* views are owned by SYS, regardless of who owns the underlying DBA_* view. By default, a user connected to the root will only see data pertaining to the root.

May the database time zone be set on a per-PDB basis?

Yes.

May NLS currency settings (NLS_CURRENCY) be set on a per-PDB basis?

Yes.

How to monitor the undo usage of each container/database in CDB/PDB?

Use the below queries to monitor the undo usage of each container or database in a CDB or PDB architecture.

sql> select NAME, MAX(TUNED_UNDORETENTION), MAX(MAXQUERYLEN), MAX(NOSPACEERRCNT), MAX(EXPSTEALCNT)

from V$CONTAINERS c , V$UNDOSTAT u

where c.CON_ID=u.CON_ID

group by NAME;

sql> select NAME, SNAP_ID,UNDOTSN,UNDOBLKS,TXNCOUNT,MAXQUERYLEN,MAXQUERYSQLID

from V$CONTAINERS c , DBA_HIST_UNDOSTAT u

where c.CON_ID=u.CON_ID

and u.CON_DBID=c.DBID

order by NAME;

What is the difference between schema-based consolidation and the multitenant architecture?

. Name collision might prevent schema-based consolidation.
. Schema-based consolidation brings weak security.
. Per-application, back-end, point-in-time recovery is prohibitively difficult.
. Resource management between application back-ends is difficult.
. Patching the Oracle version for a single application back-end is not possible.
. Cloning a single application back-end is difficult.

How to check opatch applied or not in our Oracle home?

./opatch lsinventory GB of database size while applying opatch it will take mins, so GB of database size how much time it will take to apply opatch. Same Mins, We will apply the opatch for oracle binary only not for data.

What is sqlnet.ora ?

It is used to security of our database, Privileged IP address only able to access the database.

what are the different components of physical and logical database structure?

physical: Datafile,Controlfiles, redologfiles.
Logical: Database buffer cache, Redo log buffer cache,Shared pool,large pool

What is Database Writer (DBWR)?

It is responsible to writing data from database buffer cache into datafiles.

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.

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.

What is the purpose of collecting statistics?

It will help optimizer to generate best execution plan

What is an Oracle index?

It is an database object used to increase the performance for retrieval data.

If the table is fragmented, how would you rebuild it?

Alter table tablename move, We can analyze the table.

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

AWR,ADDM,Trace,TKPROF,OEM

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

We will check if query is using proper indexes are not. If not we will either create new indexes or can use hints to specify which indexes to use.

If performance issue will occur, What are the actions need to take ?

We need to take AWR report and check query elapsed time and how many executions, Query using proper index or not, or elase we have to analyze table and indexes.Check alertlog any issue occurred

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

RMAN
Datapump,Exp/IMP
Cold backup

RMAN full backup was failed due to archivelogs error, What we will do ?

We need to crosscheck the archivelogs.
crosscheck archivelog all;
delete expired archivelog all;

How do you identify which sessions are locking the objects? What is the way to clear them?

By combining V$locked_object and v$session

Till yesterday a query is giving output in min. today it is taking min. how you will troubleshoot this problem?

We will start looking at the network issues
Check if the query is changed from yesterday to today
Check table statistics are upto date
Generate explain plan and see where is the problem

How to import table with different schema and different table and different tablespace?

remap_schema,remap_table,remap_tablespace

how to check production database and standby database sync ?

select max(sequence#) from V$archived_log;
select max(sequence#) from V$log_history;

Our standby database not in sync with production archivelogs gap will be there how to sync with production?

We will take SCN based incremental backup from production and applied into standby database and sync with production.

How to open standby database read write mode and again how-to rollback, what are options we have to use it.

Using flashback and restore point option we can open and rollback the standby database.

Database Components

set line ;
set pagesize ;
col COMP_ID format a;
col COMP_NAME format a;
select COMP_ID,COMP_NAME,STATUS from dba_registry;

What makes you mean by Database Normalization?

Normalization technique is a set of rules that exist used to build this relational database to prevent data redundancy also dependency. Once original database objects remain classified,
normalization benefits in identifying specific relationships between schema objects.

First Normal Form
Second Normal Form
Third Normal Form
Boyce-Codd Normal Form
Fourth Normal Form
Fifth Normal Form

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

We can find out using v$session_waits and v$ system _waits.

Slove : Hope the above set would have provided you a good knowledge of any of the advanced ideas of Oracle. Just go through those questions before attending the interview also success will be yours.

Defined by table partitioning?

It is a process of sharing a table within smaller chunks so as to obtain the data retrieval simple and quick. Each piece order is known as a partition also can occur accessed separately.

How do understanding for row Chaining?

When a row is too long that it cannot and then it will end up accepting consequent blocks which can be led to this concept of Row Chaining. It can signify avoided by refreshing this storage parameter over an appropriate value.

Why do we need to rebuild indexes?

Due to various insert & delete actions, that file gets fragmented & unstructured data, through creating that relationship slowly. To get data inside these indexes, rebuilding is completed.

Different tools that are provided by Oracle to assist performance monitoring?

TKPROF – (Transient kernel profiler)
STATSPACK – (Stands for Statistics Package)
ADDM – (Automated Database Diagnostics Monitor)
AWR – (Automatic Workload Repository)
OEM – (Oracle Enterprise Manager)

Different areas where we can perform tuning?

Database Design.
Memory Allocation.
Disk I/Os.
Database Contention.
OS level (CPU).

How can we monitor these space allocations in a database?

DBA_FREE_SPACE
DBA_SEGMENTS
DBA_DATA_FILES

Defined by Recovery Catalog?

Recovery Catalog is a means by database schema that holds by the metadata managed by RMAN for restoration data and recovery data processes.

What is means by Database Writer?

DBWR is a background process that writes changed data blocks of defense amounts over this data file. This changed information blocks move called dirty blocks.

What are the specific disk components?

Data files
Redo Logs
Control files
Password files and
Parameter files

What is mean by Oracle DBA PGA?

PGA is a memory distance used by Oracle database. It is expected to save session specific information.

When effects DBWR write to the data file?

When checkpoint occurs
When number from infected blocks provides a threshold
Every three moments due to timeout
When the server method demands free space in buffer cache to read new blocks.

What is defined by System Change Number?

SCN is an ID that Oracle makes to every transaction. It is recorded including those corresponding variety into a redo approach.

What is the significance of Oracle DBA?

The Oracle Certified Professional (OCA/OCP) Database Administrator (DBA) accreditation is intended to reflect capability in Oracle database engineering, establishment, organization, execution tuning, and investigating.

What is Oracle and what is it utilized for?

An Oracle database is an accumulation of information treated as a unit. The motivation behind a database is to store and recover related data. … Oracle Database is the primary database intended for big business framework figuring, the most adaptable and practical approach to oversee data and applications.

Clarify the devices which are utilized to fire up an Oracle database?

You can start up a database with three devices.

SQL*Plus: This is the most generally utilized choice. You initially associate with an inactive case with SQL*Plus and after that startup the case with "startup" direction.

Prophet Enterprise Manager: This is another method for beginning up a database. You can sign in to Oracle Enterprise Manager regardless of whether the database is halted. OEM will recognize the status of the down database and will introduce you " Startup" catch. You can start up the database by clicking this catch.

RMAN: This is somewhat a less utilized instrument for beginning up a database yet it is conceivable to fire up a database from the Recovery Manager direction line.

What is the utilization of checkpoint in Oracle?

A checkpoint plays out the accompanying three tasks: Every filthy square in the cushion reserve is kept in touch with the information records. That is, it synchronizes the information hinders in the support reserve with the datafiles on circle. It&#;s the DBWR that composes all changed database hinders back to the datafiles.

What makes up an Oracle Instance?

An occurrence is comprised of a mutual memory locale on RAM called System Global Area (SGA) and foundation forms.
The framework worldwide region is a common memory, which implies it very well may be gotten to by various procedures. This holds information which is required by the case to work.
The foundation forms are working framework forms and each procedure has a particular obligation in the case.
The System Global Area and foundation forms are made when the case is "began"
When the occasion is " shut down" the procedures are murdered and the common memory
the district is "discharged" back to the working framework.

What is the Oracle Home Inventory?

Prophet home stock or neighborhood stock is available inside every Oracle home. It just contains data significant to a specific Oracle home.
This document is situated in the accompanying area: $ORACLE_HOME/stock
It contains the accompanying records and organizers:

Components File

Home Properties File
Other Folders

What are the articles in a database?

A database object is any characterized item in a database that is utilized to store or reference information. A few instances of database objects incorporate tables, sees, bunches, arrangements, files, and equivalent words. The table is this present hour&#;s center since it is the essential and most straightforward type of information stockpiling in a social database.

Rundown the normal assignments of Oracle DBA?

The undertakings of Oracle DBA as pursues:

Installing Oracle programming
Creating Oracle databases
Performing updates of the database and programming to new discharge levels
Starting up and closing down the database
Managing the database&#;s stockpiling structures
Managing clients and security
Managing diagram objects, for example, tables, files, and perspectives
Making database reinforcements and performing recuperation when fundamental
Proactively checking the database&#;s wellbeing and making the preventive or remedial move as required
Monitoring and tuning execution a little to a fair size database condition, you may be the sole individual playing out these undertakings. In enormous venture conditions, the activity is regularly isolated among a few DBAs, each with their territory of claim to fame, for example, the database security director or database tuning master.

What are the advantages of ORDBMS?

The items accordingly can be put away in the database. The language of the DBMS can be coordinated with an item arranged programming language. The language may even be equivalent to that utilized in the application, which does not constrain the software engineer to have two portrayals of his items.

What are the circle parts in Oracle?

These are the physical parts which get put away in the circle.

Data documents

Redo Log documents

Control documents

Password documents
Parameter documents

What is the contrast between information square/degree/fragment?

An information square is the littlest unit of intelligent stockpiling for a database object. As items develop they take pieces of extra stockpiling that are made out of touching information squares. These groupings of coterminous information squares are called degrees. Every one of the degrees that an article takes when gathered are viewed as the portion of the database object.

What is the contrast among PGA and UGA?

When you are running committed server at that point procedure data put away inside the procedure worldwide region (PGA) and when you are utilizing shared server then the procedure data put away inside the client worldwide region (UGA).

What are the various kinds of capacity frameworks accessible and which one is utilized by Oracle?

Two kinds of capacity frameworks are accessible: Relational Database Management System (RDBMS) and Hierarchical Storage Management System (HSM)

Most databases utilize the RDBMS model, Oracle likewise utilizes the RDBMS model.
Hierarchical Storage Management System (HSM)
Information Management System (IMS) from IBM.
Integrated Database Management System (IDMS) from CA.

Rundown out the real establishment ventures of Oracle programming on UNIX in a word?

Set up the circle and ensure you have Installation document (run Installer) in your dump.
Check the swap and TEMP space.
Fare the accompanying condition factors
ORACLE_BASE
ORACLE_HOME Way
LD_LIBRARY_PATH
TNS_ADMIN
Set up the part parameters and document most extreme descriptors.
Source the Environment record to the separate slam profile and now run Oracle Universal Installer.

What are the kinds of shutdown methods of an Oracle database?

Ordinary: In this mode, no new associations are permitted and the database is shut after every one of the sessions detaches themselves.

Quick: No new associations are permitted and the current dynamic exchanges are moved back. Changes made by a functioning exchange are lost in this alternative.

Value-based: No new associations are permitted and Oracle holds up until every single dynamic exchange are finished.

Prematurely end: This happens quickly, notwithstanding, the database isn&#;t closed down neatly. The database should perform case recuperation next time it is begun. This choice ought not be utilized in standard exercises.

What do you understand by Row Chaining?

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 means by LoG-WRiter (LGWR)?

LGWR is the background method that writes redo data from redo log buffers before the log files.

What are the two involved in network connection to a database?

TNSNAMES.ORA and SQLNET.ORA

How will you backup all redo log file from Linux?

$cp *.log/u/app/backup/db_name (This command will copy all redo log file.

How will you backup all datafiles from Linux?

$cp *.dbf/u/app/backup/db_name (This command will copy all datafile.

How will you find the location of pfile?

> Show parameter pfile;

What is the use of a control file?

The control file contains the physical structure of the database, such as the number of log files and their location. Oracle database server uses a control file to find its physical component.

What is oracle database?

Oracle Database is a relational database management system (RDBMS) which is used to store and retrieve the large amounts of data. Oracle Database had physical and logical structures. Logical structures and physical structures are separated from each other

oracle1

oracle2

What is the difference between Oracle database and Oracle instance?

Oracle database is the collection of datafiles,redologs and control files while Oracle instance is the SGA ,processes in the Memory.

We can have 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

orac3

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

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.

orac4

What are Control Files?

Every Oracle database has a control file. A control file contains entries that specify the physical structure of the database such as Database name and the Names and locations of datafiles and redo log files.

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.

What is Archive Log Files?

Oracle automatically archives log files when the database is in ARCHIVELOG mode. This prevents oracle from overwriting the redo log files before they have been safely archived to another location.

What is Parameter Files (initSID.ora)

Parameter files contain a list of configuration parameters for that instance and database.

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 are default schema’s. We can create a new Schema/User. But we can’t drop default database schema’s.

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 K, K size depending on the requirement.

What is an Extent?

Extent is a collection of Continuous data blocks, which is used for storing a specific type of information.

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

orac5

What is Rollback Segment?

Database contain one or more Rollback Segments to roll back transactions and data recovery.

What are the different type of Segments?

Data Segment (for storing User Data), Index Segment (for storing index), Rollback Segment and Temporary Segment.

What is archive-log and No archive log 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

What all things are present in the shared pool?

The shared pool portion of the SGA contains three major areas:
library cache (contains parsed sql statements,cursor information,execution plans)
dictionary cache (contains cache -user account information,privileges information,datafile,segment and extent information)
buffers for parallel execution messages
control structure.

orac6

What is Hotbackup?

If the database must be up and running hours a day, seven days a week, then you have no choice but to perform inconsistent backups of the whole database. A backup of online data files is called an online backup. This requires that you run your database in ARCHIVELOG mode.

Which views is used to finding the locking in the database?

v$lock, v$session, v$process

You have many instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?

There are two ways

SQL> oradebug setmypid

SQL> oradebug ipc

SQL>oradebug tracfile_name

Another way is to use

$ORACLE_HOME/bin/sysresv

What is Database index?

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of slower writes and increased storage space.By default, Oracle creates B-tree indexes.

orac7

How to check Oracle database version?

Select * from v$version;

The below matrix explains the number of the Oracle version

Major Database Release Number	The first digit is the most general identifier. It represents a major new version of the software that contains significant new functionality.
Database Maintenance Release Number	The second digit represents a maintenance release level. Some new features may also be included.
Application Server Release Number	The third digit reflects the release level of the Oracle Application Server (OracleAS).
Component-Specific Release Number	The fourth digit identifies a release level specific to a component. Different components can have different numbers in this position depending upon, for example, component patch sets or interim releases.
Platform-Specific Release Number	The fifth digit identifies a platform-specific release. Usually this is a patch set. When different platforms require the equivalent patch set, this digit will be the same across the affected platforms.

Lets take the example of Oracle version ….
– Major database release number
– Database Maintenance release number
– Application server release number
– Component Specific release number
– Platform specific release number

How to find the database/sqlplus version?

select banner from v$version;

How to find operating system version?

uname –a

What is the location of init.ora ?

$RDBMS_ORACLE_HOME/dbs

What is that trace files contains and the utility used to read them?

Trace file contains the detail diagnostics of a sql statement like explain plan, physical reads, logical reads, buffer gets etc. Tkprof utility is used to convert trace file into readable format.

What is the syntax for tkprof?

tkprof explain=apps/ sys=no

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.

.Login as oracle user

.sqlplus “/as sysdba”

. create database link connect to identified by using ”;

create database link MY_TO_MY connect to apps identified by apps using ‘MY′;

Database link created.

SQL> select name from v$database@ MY_TO_MY;
NAME
——
MY
SQL>select db_link from dba_db_links;

Add destination database tns entry in tnsnames.ora

What is the pre-req for applying a rdbms patch?

Inventory should be set in file oraInst.loc @/var/opt/oracle or /etc and Oracle home must be registered in Central inventory

What is Inventory?

The oraInventory is the location for the OUI (Oracle Universal Installer)’s book keeping. The inventory stores information about: All Oracle software products installed in all ORACLE_HOMES on a machine Other non-Oracle product, such as the Java Runtime Environment (JRE)

In a R Application system, the RDBMS and iAS ORACLE_HOMEs and .. are registered in the oraInventory.

What are different types of inventories?

The Global inventory (or Central inventory)

The Local inventory (or Home inventory)

What is Global inventory or central inventory?

The Global Inventory is the part of the XML inventory that contains the high-level list of all oracle products installed on a machine. There should therefore be only one per machine. Its location is defined by the content of oraInst.loc.The Global Inventory records the physical location of Oracle products installed on the machine, such as ORACLE_HOMES (RDBMS and IAS) or JRE. It does not have any information about the detail of patches applied to each ORACLE_HOMEs. The Global Inventory gets updated every time you install or de-install an ORACLE_HOME on the machine, be it through OUI Installer, Rapid Install, or Rapid Clone.

Note: If you need to delete an ORACLE_HOME, you should always do it through the OUI de-installer in order to keep the Global Inventory synchronized.

What is local inventory?

There is one Local Inventory per ORACLE_HOME. It is physically located inside the ORACLE_HOME at $ORACLE_HOME/inventory and contains the detail of the patch level for that ORACLE_HOME.The Local Inventory gets updated whenever a patch is applied to the ORACLE_HOME, using OUI.

How to apply patch to Oracle database Home software?

Oracle database Home software is patched using OPATCH utility

Applying patch

cd <patch no>

opatch apply

Rollback

opatch rollback -id <patch no>

How to find Database version?

SQL> select * from v$version;

The command returns the release information,

How to find opatch Version ?

opatch is utility to apply database patch , In order to find opatch version execute”$ORACLE_HOME/OPatch/opatch version”
Even if you simple write $ORACLE_HOME/OPatch/opatch, it will show the opatch version in the start

How to find that the database is -bit/-bit?

$RDBMS_ORACLE_HOME/bin/file oracle

What is top command?

top is a operating system command, it will display top processes which are taking high cpu and memory. . What is a patch? A patch can be a solution for a bug/it can be a new feature.

Which table you will query to check the tablespace space issues?

bytes column in dba_free_spaces and dba_data_files

Which table u will query to check the temp tablespace space issues?

dba_temp_files

What is temp tablespace?

Temp tablespace is used by so many application programs for sorting and other stuff.

How to find out invalid objects in the apps schema in the database?

select count(*) from dba_objects where status =’INVALID’and owner ='APPS'

How you will see hidden files in linux/solaris?

ls -la

How to generate the AWR report

$ORACLE_HOME/rdbms/admin/awrrpt.sql

How to apply a rdbms patch?

Using opatch

Go the patch directory

cd <patch num>

opatch apply

How to kill a database session?

alter system kill session ‘&sid,&sno’;

How to find opatch is enabled or not for your database?

If Opatch directory exists under RDBMS_ORACLE_HOME.

What is AWR?

AWR is a database utility to gather database and session level performance information.

How to enable trace at database level?

set init.ora parameter sql_trace

How to enable trace for a session?

Alter system set sql_trace=true;

Execute the sql query

Alter system set sql_trace=false;

This will create a trace file at background dump dest directory

How to enable trace for other session?

exec sys.dbms_system.set_sql_trace_in_session(sid,serial#,true/false)

Example To enable trace for sql session with sid

SQL> exec sys.dbms_system.set_sql_trace_in_session(,,true);

PL/SQL procedure successfully completed.

To disable trace

SQL> exec sys.dbms_system.set_sql_trace_in_session(,,false);

What is flashback database?

It is New feature in Oracle database post . onwards. It Uses past block images to back out changes to a oracle database. As the name suggest, we can use this flashback database in previous time

a.During normal database operation, Oracle occasionally logs past block images in flashback logs
b.Flashback logs are written sequentially not archived
c. Oracle automatically creates, resizes and deletes flashback logs in the flash recovery area
d. DBA should be aware of flashback logs to monitor performance,to decide how much space to allocate to flash recovery area
e. Allows database to be recovered to a previous time to correct problems caused by logical data corruptions,user errors

How can you rebuild an index?

We can rebuild the index using the below command

If it is to be online

alter index index_name rebuild online;

If it is to be rebuild offline

alter index index_name rebuild;

You can use parallel to speed up the rebuild

alter index index_name rebuild parallel ;

What is Branch Block in index?

Branch block rows hold <separator key,dba> pairs used to guide the B-tree search
to a row in a leaf block.

What is Leaf Block in index?

Leaf block rows hold the <KEY, KEYDATA> pairs stored by the B-tree.

What is High Water Mark in Oracle?

High water mark in Oracle is the maximum amount of database blocks used so far by a segment. This mark cannot be reset by delete operations.
Delete Table in Oracle operation won’t reset HWM.
Oracle TRUNCATE will reset HWM.
The high water mark level is just a line separate the used blocks and free blocks.

The blocks above the HWM level is free blocks, they are ready to use.
The blocks below the HWM level is used blocks, they are already used.

What parameters are used to set parallelism in the database?

Following initialization parameters are required for parallelism setup in database.

PARALLEL_SERVER,

PARALLEL_SERVER_INSTANCES,

PARALLEL_MIN_SERVERS,

PARALLEL_MAX_SERVERS

PARALLEL_THREADS_PER_CPU

What is difference between startup mount and startup nomount?

startup mount -mount the control file

startup nomount- does not mount the controlfile

What is SCN (System Change Number) ?

The system change number (SCN) is an ever-increasing value that uniquely identifies a committed version of the database at a point in time. Every time a user commits a transaction Oracle records a new SCN in redo logs.
Oracle uses SCNs in control files datafile headers and redo records. Every redo log file has both a log sequence number and low and high SCN. The low SCN records the lowest SCN recorded in the log file while the high SCN records the highest SCN in the log file

How to find Last password change of a user?

select du.username, du.profile, du.account_status, u.ptime last_pwd_change

from dba_users du, sys.user$ u

where du.username = u.name and du.username='&'

order by ,

/

What is library cache lock?

A library cache lock means that a session is waiting to use or change an object definition or to use a SQL statement that another session is loading, changing or parsing, or is waiting to change or parse. This usually indicates that database object definitions are being changed regularly.

Another example is gathering statistics on an object. When statistics are gathered all references to that object in the shared pool or library cache become invalid, requiring a new hard parse for each SQL statement referencing the object. Statistics should only be gathered when there are no active users or system activity is very low.

How do we find the blocker for Library cache lock?

We can run hanganalyze to find the blocking session.

Many times the below query also works in wonderful manner

select /*+ all_rows */ w.sid waiting_session,

h.sid holding_session,

w.kgllktype lock_or_pin,

w.kgllkhdl address,

decode(h.kgllkmod, , 'None', , 'Null', , 'Share', , 'Exclusive',

'Unknown') mode_held,

decode(w.kgllkreq, , 'None', , 'Null', , 'Share', , 'Exclusive',

'Unknown') mode_requested

from dba_kgllock w, dba_kgllock h, v$session w, v$session h

where

(((h.kgllkmod != ) and (h.kgllkmod != )

and ((h.kgllkreq = ) or (h.kgllkreq = )))

and

(((w.kgllkmod = ) or (w.kgllkmod= ))

and ((w.kgllkreq != ) and (w.kgllkreq != ))))

and w.kgllktype = h.kgllktype

and w.kgllkhdl = h.kgllkhdl

and w.kgllkuse = w.saddr

and h.kgllkuse = h.saddr

/

How to take global hanganalyze dump?

ORADEBUG setmypid

ORADEBUG setinst all

ORADEBUG -g def hanganalyze <level>

How do you recover the database if you lost one of the controlfile in the database?

Shutdown Instance ( abort )

sqlplus / as sysdba
shutdown abort

Change Init.ora file to remove the lost controlfile or copy the existing controlfile to that location
startup the database

How do you recover the database if you lost all of the controlfile in the database?

a.Shutdown Instance (abort)
b. If the control-file backup is available ,then restore it from backup or if you have got the controlfile information in trace using alter database backup controlfile to trace
c. Once the controlfile is created, recover database using backup controlfile. You will need to apply the redo logs to complete the recovery
d, alter database open resetlogs;

We can avoid the resetlogs by using the steps below
. After the database is mounted with restore of controlfile from backup,create a trace of the controlfile using the command below
alter database backup controlfile to trace;
. Now take out the create controlfile statement from the trace. Choose the NORESETLOG portion
. Recreate the controlfile using the above portion.
. Do recover database
. alter database open

If the table is fragmented, how would you rebuild it?

First we need to rebuild the table

alter table <table_name> move;

Secondly we need to rebuild all its indexes

We can find all the indexes

select index_name from dba_indexes where table_name=<table name> and table_owner=<table owner>


alter index <index_name> rebuild

Finally we should gather the stats on the table

What view would you use to determine free space in a tablespace?

dba_free_space

How do you switch from an init.ora file to a spfile?

Create spfile from pfile;
shutdown instance
startup

It will start using spfile

You are experiencing high “busy buffer waits” . how can you find what’s causing it?

Buffer busy wait means that the queries are waiting for the blocks to be read into the db cache.There could be the reason when the block may be busy in the cache and session is waiting for it. It could be undo, data block or segment header wait.

Run the following query to find out the p,p and p of a session causing buffer busy wait

sql> select p "File #",p "Block #",p "Reason Code" from v$session_wait where 
event = 'buffer busy waits';

After that running the following query to find the segment causing buffer busy wait:-

sql> select owner,segment_name,segment_type from dba_extents where 
file_id = &P and &P between block_id and block_id + blocks -

How to kill the database session?

First you need to find the SID,serial# of the session

select SID,SERIAL# from v$session where <>;

alter system kill session 'SID,SERIAL#';

In some situations, like waiting for a reply from a remote database or rolling back transactions, the session will not kill itself immediately and will wait for the current operation to complete.So Upon issuing the alter system kill session command, the session will be ‘marked for kill’. It will then be killed as soon as possible

For Oracle RAC

If you want to kill session from another instance

ALTER SYSTEM KILL SESSION 'sid,serial#,@inst_id';

What are Physical Block Corruptions?

This kind of block corruptions are normally reported by Oracle with error ORA- and the detailed corruption description is printed in the alert log.

Corruption Examples are:

Bad header – the beginning of the block (cache header) is corrupt with invalid values
The block is Fractured/Incomplete – header and footer of the block do not match
The block checksum is invalid
The block is misplaced

What is Row chaining?

Row chaining happens when a row is too large to fit into a single database block.

For example, Suppose you have KB block size for your database,and you need to insert a row of KB into it, Oracle will use blocks and store the row in pieces.

In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment.So, instead of just having a forwarding address on one block and the data on another we have data on two or more blocks.And Row Chaining happens only when the row is being inserted and whenever it has inserted it cannot be chained.

In most cases chaining is unavoidable, especially when this involves tables with large columns such as LONGS, LOBs, etc. When you have a lot of chained rows in different tables and the average row length of these tables is not that large, then you might consider rebuilding the database with a larger block size.
Tables with more than columns can potentially force Chaining

What is Row Migration?

We will migrate a row when an update to that row would cause it to not fit on the block anymore (with all of the the «forwarding address». So, the original block just has the ROWID of the new block and the entire row is moved.

How to find the physical location of the datafiles,redo logs,controlfile?

Prompt Control file Locations

select name

from sys.v_$controlfile

/Prompt Redo logs Locations

col Grp format

col member format a heading "Online REDO Logs"

col File# format

col name format a heading "Online REDO Logs"

break on Grp

select group#,member

from sys.v_$logfile

/

Prompt Data Files Locations

col Tspace format a

col status format a heading Sta

col Id format

col Mbyte format

col name format a heading "Database Data Files"

select F.file_id Id,

F.file_name name,

F.bytes/(*) Mbyte,

decode(F.status,'AVAILABLE','OK',F.status) status,

F.tablespace_name Tspace

from sys.dba_data_files F

order by tablespace_name;

How do we check if a Database upgrade is successful?

select COMP_NAME,VERSION,STATUS from dba_registry;

COMP_NAME VERSION STATUS

------- ------ ------

Oracle XML Database ... VALID

Oracle Text ... VALID

What is the difference between CPU and PSU patching?

The CPU are the Critical Patch Update, we can summarize it as the Security bugs fixes, The new name for the critical patch updates is security patch update (SPU). the PSU contains the CPU + some technical bugs fixed.

For my point of view, it is better to apply PSU as it contains the CPU and the some others Important bugs fixes).

How to verify whether a parameter changed required database bounce or not?

In v$parameter we can find one column ie. ISSYS_MODIFIABLE. This column contains three phases ) Immediate ) DEFERRED ) False

Immediate : We can change the parameter in fly database i.e Dynamic.(only need to change the value no need to bounce)
DEFERRED : We can change the parameter in fly database but this will effect after restart the database only.(here we need to edit using spfile and bounce the database)
False : Compulsory we need to down the database i.e Static.

How to find the locks and what is the resolution?

we can find general locks with the following query:

SELECT inst_id,DECODE(request,,’Holder: ‘,’Waiter: ‘)||sid sess,  id, id, lmode, 
request, type  FROM GV$LOCK  WHERE (id, id, type) IN  (SELECT id, id, type FROM 
gV$LOCK WHERE request>)ORDER BY id, request ;

If it’s a dead lock, we need to kill that session.

While applying a rdbms patch using opatch you are getting the error, unable to read inventory/inventory is corrupted/ORACLE_HOME is not registered, what you will do, and how you will apply the patch?

We will check the inventory directory permission, try to apply the patch after giving permissions to that inventory directory.

We can try to create to recreate the central inventory and If still it won’t work, we will apply patch with the following command:

Opatch apply no_inventor

So, this brings us to the end of the Oracle DBA Interview Questions blog. This Tecklearn ‘Top Oracle DBA Interview Questions and Answers’ helps you with commonly asked questions if you are looking out for a job in Oracle DBA or Database Domain. If you wish to learn Oracle DBA and build a career in Database domain, then check out our interactive, Oracle DBA Training, that comes with 24*7 support to guide you throughout your learning period.

https://www.tecklearn.com/course/oracle-12c-dba-course/

Oracle 12c DBA Course Training

About the Course

Oracle Database Administration training will help you master the Oracle DBA. As part of the training, you will master the Oracle Database architecture, the database processes, memory structures, schema objects, security, data recovery and backup through hands-on projects and case studies. Oracle DBA Training helps students gain the skills and knowledge required to install, configure, and administer Oracle databases. Besides, this training also develops the full skill set required to become a successful Oracle Database Administrator.

Why Should you take Oracle DBA Training?

The Average salary of an Oracle DBA is $108,837 per annum – GlassDoor.com
According to Gartner, Oracle is the market leader in the Relational Database with 48.35% market share globally.
IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle Database

What is the Oracle Database
Pre-requisites for oracle database
Oracle database 12c installation
Multi-tenant architecture

Exploring the Oracle 12c Database Architecture

Explain the Memory Structures
Describe the Process Structures
Overview of Storage Structures

Preparing the Database Environment

Identify the tools for Administering an Oracle Database
Plan an Oracle Database Installation
Install the Oracle Software by using Oracle Universal Installer (OUI)
Create a Database by using the Database Configuration Assistant (DBCA)

Managing the Oracle Instance

Setting database initialization parameters
Describe the stages of database startup and shutdown

Database Storage Structures and Schema Objects

Managing Database Storage Structures
Overview of table space and data files
Overview of Primary Components
Identify the importance of checkpoints, redo log files, and archived log files
Configure ARHIVELOG mode

Database Startup & User Requests and Oracle Network Environment

Describe the stages of database startup and shutdown
Using alert log and trace files
Configure and Manage the Oracle Network
pFile and spFile
Tablespace Management
Storage and Relationship Structure

Oracle Backup & Recovery

Redo log File Management
Performing Database Recovery
Overview of Data Recovery Advisor
Use Data Recovery Advisor to Perform recovery (Control file, Redo log file and Data file)

Deep Dive into Oracle Recovery Manager (RMAN)

Using the RMAN Recovery Catalog
Identify situations that require RMAN recovery catalog
Create and configure a recovery catalog
Synchronize the recovery catalog
Create and Use RMAN stored scripts
Back up the recovery catalog
Create and use a virtual private catalog

Configuring Backup Specifications

Using RMAN to Create Backups
Create image file backups
Create a whole database backup
Enable fast incremental backup
Using RMAN to Perform Recovery
Perform complete recovery from a critical or noncritical data file loss using RMAN
Perform incomplete recovery using RMAN

Data Movement and Oracle Data Pump

Moving Data
Describe and use methods to use move data (Directory objects, SQL* Loader, External tables)
Explain the general architecture of Oracle Data Pump

Data Dictionary & Dynamic Performance Tables

Oracle Data Dictionary contains information related to database privileges
Data dictionary views
Media failure
Flashback CDB

Database Tuning

What is database tuning
How to tune the database
Tuning the data dictionary

Got a question for us? Please mention it in the comments section and we will get back to you.

750