Posted on

Oracle dba real time scenarios interview question and answer

1) What is an active session pool?
It is a type of resource allocation method. We can set a maximum allowable number of concurrent sessions within any consumer resource group.All sessions that are beyond the maximum limit are queued for execution after the freeing up of current active sessions.
2) What is an Automatic consumer group switching?
It is a type of resource allocation method.Using this method,we can specify that a user session be automatically switched to a different group after it runs more than a specified number of seconds.The group that the session should switch to is called as switch group,and the time limit is the switch time.The session can revert to its original consumer group after e end of the top call,which is defined as an entire PL/SQL block or a separate SQL statement.
3) What is cancelling SQL and terminating sessions?
It is a type of resource allocation method.By using CANCEL_SQL or KILL_SESSION as the switch group,we can direct a long-running SQL segment or even an entire session to be cancelled or terminated.
4) What is a Parallel Degree Limit?
It is a type of resource allocation method.We can use this method to specify the limit of the degree of parallelism for an operation.
5) What is a resource plan directive?
It is an element of database resource manager.It links a resource plan to a specific resource consumer group
6) How will you get details on SGA and PGA from v$ views?
We can get to know the size of SGA (System Global Area) components and PGA (Program Global Area) components using sql query. We can query the v$ dynamic performance views to get more information about the memory structure of the oracle instance.
SQL> column component format a30;
SQL> column current_size format 999999999;
SQL> column min_size format 999999999;
SQL> column max_size format 999999999;
SQL> select component,current_size,min_size,max_size from v$sga_dynamic_components;
—————————— ———— ———- ———-
shared pool 327155712 327155712 327155712
large pool 8388608 8388608 8388608
java pool 8388608 8388608 8388608
streams pool 0 0 0
DEFAULT buffer cache 452984832 452984832 452984832
KEEP buffer cache 0 0 0
RECYCLE buffer cache 0 0 0
DEFAULT 2K buffer cache 0 0 0
DEFAULT 4K buffer cache 0 0 0
DEFAULT 8K buffer cache 0 0 0
DEFAULT 16K buffer cache 0 0 0
—————————— ———— ———- ———-
DEFAULT 32K buffer cache 0 0 0
Shared IO Pool 0 0 0
ASM Buffer Cache 0 0 0
14 rows selected.
SQL> select name,value from v$pgastat where name in(‘maximum PGA allocated’,’total PGA allocated’);
—————————————————————- ———-
maximum PGA allocated 115508224
7) Why does desc index command does not work?
It is possible to obtain information on the object structure using the SQL command DESC.We can get to know the structure of a table using the command desc tablename
SQL> desc learnersreference_t;
Name Null? Type
—————————————– ——– —————————-
I tried describing an index using the command DESC indexname. I got the following error.
SQL> desc learnersreference_i;
ORA-04043: object learnersreference_i does not exist
I wondered why this happened as I’ve already created an index with this name. So,I tried creating an index with this name.
SQL> create index learnersreference_i on learnersreference_t(user_id);
create index learnersreference_i on learnersreference_t(user_id)
ERROR at line 1:
ORA-00955: name is already used by an existing object
Again this created a problem with an error saying that the object exists which is contradictory. So, I tried creating index on another column
SQL> create index learnersreference_i_new on learnersreference_t(user_id);
Index created.
Again tried DESC indexname command and got the same error
SQL> desc learnersreference_i_new;
ORA-04043: object learnersreference_i_new does not exist
Thus I concluded that DESC indexname command always returns the error ORA-04043: object object_name does not exist
8) What is a cursor? Why do I need it?
If you are a oracle newbie developer, experienced oracle developer the first important concept in the code is going to be cursor.
9) What is real time use of a cursor?
To put it simple, when we have a select statement output (set of ros usually more than one row) to be processed in a PL/SQL code, cursor usually comes into picture.
10) How do I start using a cursor?
A cursor is declared in the declaration section as a variable in PL/SQL. Here is the simple cursor syntax :
Cursor cursor_name is select colname from tables where condition;
for rec in cursor_name
perform operations….
end loop;
This makes the processing of set of records very simple, easy and efficient.
11) What jobs can be scheduled using scheduler in an oracle database?
Oracle Scheduler lets us schedule jobs in database. In OS cron can be used to schedule jobs. Following are some jobs that can be scheduled :
1) Gathering table and index statistics- Keeping statistics of tables, indexes up-to-date is essentially important for best database performance. As a best practice weekly full backup should happen after statistics collection is completed to have a best performing backup. This is a job that can be scheduled using dbms_Scheduler
2) Executing database monitoring scripts – With current OEM need for oracle monitoring has become GUI based. However, adhoc monitoring scripts can be created and scheduled using dbms_Scheduler
Batch jobs
Replication jobs
3) Running daily backups – RMAN backups can be created as scripts and scheduled to run on daily, weekly, hourly basis as needed
4) Index rebuild – If the schema data changes too often, indexes become stale owing to b-tree issues. As such index rebuild becomes mandate. This can be scheduled as monthly job
5) Month end reports – Reporting is creation of csv, text files using utilities like utl_file that creates and writes reports onto location specified using utl_file_dir These jobs can be automated using scheduler
12) You connect to the database and query v$pwfile_users. What does this mean?
In oracle database we can create users and grant sysdba and sysoper privileges. It is interesting to know that when an user connects to the database with sysoper privilege, and when we query the user, PUBLIC is displayed.
SQL> select * from v$pwfile_users;
—————————— —– —– —–
SQL> create user test1 identified by test1;
User created.
SQL> grant sysoper to test1;
Grant succeeded.
SQL> create user test2 identified by test2;
User created.
SQL> grant sysdba to test2;
Grant succeeded.
SQL> connect test1/test1 as sysoper
SQL> show user
SQL> connect test2/test2 as sysdba
SQL> show user
SQL> select * from v$pwfile_users;
—————————— —– —– —–
13) What is Oracle Database Vault?
Oracle Database Vault is a security solution from oracle corporation. It restricts access to certain areas of the database.Even users with administrative privileges have restricted access.This fine-grained access control protects the database from super-privileged users.
14) Where can we use Oracle Database Vault?
Oracle Database Vault can be used to protect standalone database instance,Oracle Real Application Cluster(RAC)
What are the components of Oracle Database Vault?
Oracle Database Vault has the following components:
1) Oracle Database Vault Access Control Components
2) Oracle Database Vault Administrator (DVA)
3) Oracle Database Vault Configuration Assistant (DVCA)
4) Oracle Database Vault DVSYS and DVF Schemas
5) Oracle Database Vault PL/SQL Interfaces and Packages
6) Oracle Database Vault and Oracle Label Security PL/SQL APIs
7) Oracle Database Vault Reporting and Monitoring Tools
15) What is Oracle Database Vault Access Control Components made of ?
It is formed of the following components that helps us manage security for the database instance.They are:
2)Command Rules
3) Factors
4) Rule sets
5) Security Application roles
16) What is a realm?
A realm is a functional grouping of database schemas,objects and roles that must be secured.After grouping we can use realms to control the use of system privileges to specific accounts or roles. This provides fine-grained access control.
17) What is a command rule?
A command rule is a special rule created to control how users can execute almost any SQL statement.This includes SELECT,ALTER SYSTEM,DDL and DML statements.
18) Do command rules work alone?
No.They work with rule sets to determine whether or not a statement is allowed.
19) What is a factor?
A factor is a named variable or a attribute such as user location,database IP address,or session user,which oracle database vault can recognize and secure.
20) What is the use of a factor?
We can use factors for activities such as authorizing database accounts to connect to the database or creating filter logic to restrict the visibility and manageability of data.
21) What is an identity?
Each factor can have one or more identities.An identitiy is the actual value of a factor. A factor can have several identities depending on the factor retrieval method or its identity mapping logic
22) What is a rule set?
A rule set is a collection of one or more rules that we can associate with a realm authorization, command rule, factor assignment, or secure application role.
23) What are the possible evaluations of a rule set?
The rule set evaluates to true or false based on the evaluation of each rule it contains and the evaluation type (All True or Any True).
24) What is a rule within a rule set?
The rule within a rule set is a PL/SQL expression that evaluates to true or false.
25) Can rules be reused?
We can have the same rule in multiple rule sets.
26) What are secure application roles?
A secure application role is a special Oracle Database role that can be enabled based on the evaluation of an Oracle Database Vault rule set.
27) What are the steps taken to secure database using Oracle database vault access control component?
1) Create a realm composed of the database schemas or database objects that we want to secure. 2) We can further secure the realm by creating rules, command rules, factors, identities, rule sets, and secure application roles.
3) We can run reports on the activities these components monitor and protect.
28) What is a Oracle database vault administrator(DVA)?
Oracle Database Vault Administrator(DVA) is a Java application that is built on top of the Oracle Database Vault PL/SQL application programming interfaces (API).
29) What is the use of DVA?
1) DVA allows security managers who may not be proficient in PL/SQL to configure the access control policy through a user-friendly interface.
2) Oracle Database Vault Administrator provides an extensive collection of security-related reports that assist in understanding the baseline security configuration.
3) These reports help to point out deviations from this baseline.
30) What is Oracle Database Vault Configuration Assistant (DVCA)?
It is a command-line utility that is used to perform maintenance tasks on Oracle Database Vault Installation.
31) What are the Oracle Database Vault schemas?
Two major schemas of oracle database vault are DVSYS and DVF
32) Give details on DVSYS schema:-
DVSYS schema stores objects needed to process Oracle data for Oracle database vault.This schema contains the roles,views,accounts,functions, and other database objects that the Oracle Database Vault uses
33) Give details on DVF schema:-
The DVF schema contains public functions to retrieve (at run time) the factor values set in the Oracle Database Vault access control configuration.
34) Give details on Oracle Database Vault PL/SQL Interfaces and Packages :-
Oracle Database Vault PL/SQL Interfaces and Packages are collections that allow security managers,application developers to configure security policy.The PL/SQL procedures and functions allow general database accounts to operate within the boundaries of the access control policy in the context of a given database session.
35) How LogMiner Utility works?
LogMiner reads the redo log files and puts the extracted contents into the v$LOGMNR_CONTENTS view.We can query the v$LOGMNR_CONTENTS view to obtain information about the transaction we’d be interested in.The information in redo logs is in the form of internal object identifier and data is in hexadecimal form.
Oracle recommends that we provide logMiner with access to the data dictionary so it can translate the contents of the reolog file into a form we can readily understand.
We can provide LogMiner with access to the data dictionary in three different ways :
1) We can extract data dictionary to a flat file
2) We can have a dictionary snapshot placed in the redo logs
3) We can do away with the extraction of the data dictionary and direct logMiner to use the online data dictionary
36) What are the database objects supported in latest version of Oracle database?
Oracle database the relational database management system supports RDBMS as well as object RDBMS objects. To start with it supports tables, indexes, views, index organized tables, clustered tables, java, stored procedures, functions, triggers, synonyms, database link, sequences, materialized views to name a few
Tables – Basic object that stores information . Table is composed of rows and columns
Views – A query stored in database admin tables called data dictionary. This doesnt consume any space and is executed on the fly
Indexes – Increase performance by storing reference to table rows
Index organized tables – Tables and indexes created together
Cluster – Some common columns are shared among tables. This forms cluster of tables
Synonym – An alias to database object
Materializd views – stores database object and has view basics. This forms basics of datawarehousing. Lets look at it in detail
java – The programming code stored in database
procedure – An business logic written in pl/sql language that a database understands.PL/SQL is oracle specific. Procedure implements a business logic and doesnt return any value
function – Pl/SQL program unit that returns a value
trigger – a database object that gets executed when a particular database event like logon,logoff etc happen. Triggers can trigger before or after an event
Database Link – This database object establishes connection and helps in sharing information among two different databases
Index-Organized (IOT) Table – Index-Organized (IOT) Table is a table whose physical storage is organized like an index.IOT stores data according to the primary key defined on tha table (sorted order).
37) What parameters are needed for dynamic service registration/automatic service registration?
The init.ora file or SPFILE should contain the following parameters for automatic service registration:
2) INSTANCE_NAME (eg: hr)
38) What if we don’t specify the value of SERVICE_NAMES?
If we don’t specify the value of SERVICE_NAMES parameter it defaults to the global database name
100) What is a global database name?
A global database name is a combination of DB_NAME and DB_DOMAIN parameters
39) How to retain case-sensitivity in Oracle database?
While we create objects in an Oracle database, we specify object names. Oracle database object names are case-sensitive. SQL*PLUS , SQL Developer tools will automatically convert lower case letters to upper case letters. If the name is specified within double quotes, case is retained.
As a summary object names in Oracle database are case sensitive
SQL> create table case (“today’s date” date);
Table created.
SQL> create table “case” (today date);
Table created.
SQL> select table_name from dba_tables where table_name=’case’;
SQL> select table_name from dba_tables where lower(table_name)=’case’;
40) How is data coded in oracle database?
A character set is a defined encoding scheme for representing characters as a sequence of bits.Data stored in the database must be coded into a character set.Oracle is independent of the operating system character set. It is shipped with its own character set.A character set consists of defined number of distinct characters. The number of characters that a character set can represent is limited by the number of bits the character set uses for each character
A single-byte character set will use only one-byte (8bits) per character.A multi-byte character set uses one,two,three bits per character.Oracle s default character set is seven bit ASCII or seven-bit EBCDIC
Oracle 10g ships with more than 250 character sets
41) Give details on tables and their relationship to segments in an oracle database :-
Table is the database object used to store information in the form of rows and columns. We can create tables in a schema and dba_extents can be used to obtain information about the tables.
SQL> create table date);
Table created.
SQL> select tablespace_name,file_id,extent_id,block_id,blocks,bytes from dba_extents where owner=’SYS’ and segment_name=’NEW’;
——- ———- ———- ———- ———-
SYSTEM                                  1          0      87576          8       65536
In Oracle database tables can be created in a schema. They are stored in form of segments. By default these tables are heap-organized tables.
42) What is the use of archiver process in an oracle database?
Archives the online redo log files to the archiving destination
There can be n archiving destinations – LOG_ARCHIVE_DEST parameter
Archives are used to restore and recover the database upon crash/failure
Database can be run in ARCHIVE/NOARCHIVE mode .It is a Safety measure
ARCHn processes depending on the number of archival destinations
I tried configuring the flash recovery area. First step in creating the flash recovery area is to set the value of log_archive_dest parameter. It is essential to set the value of db_recovery_file_dest before setting the value of log_archive_dest
SQL> alter system set log_archive_dest=’ ‘;
alter system set log_archive_dest=’ ‘
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-16018: cannot use LOG_ARCHIVE_DEST with LOG_ARCHIVE_DEST_n or
SQL> alter system set db_recovery_file_dest=”;
System altered.
SQL> alter system set log_archive_dest=”;
System altered.
To create a package in a user’s schema the privilege needed is CREATE PROCEDURE. I had a misconception that it must be CREATE PACKAGE. I wanted to share this information with you all
SQL> connect sys/password as sysdba
SQL> grant create package to user;
grant create package to user
ERROR at line 1:
ORA-00990: missing or invalid privilege
SQL> grant create procedure to practice;
Grant succeeded.
SQL> connect user/password
SQL> create or replace package test_package as
2 procedure set_test;
3 end;
4 /
Package created.
43) How to create Table With LONG Datatype Column?
Tables are the basic objects used to store information in the form of rows/records in a database. We can create a table with one or more column of many different data types. LONG is one such datatype. It is an extended version of the most popular varchar2 data type with a maximum size of 2GB. IT is deprecated and is available in Oracle database 11g version for backward compatability. Here is an example of creating table with LONG datatype column
SQL> create table test_LONG(
2 id number,
3 testlong LONG);
Table created.
SQL> insert into test_LONG values(10,’LONG Value example’);
1 row created.
SQL> desc test_LONG;
Name Null? Type
—————————————– ——– —————————-
Flashback Drop feature is automatically enabled in Oracle Database 10g
44) What is a statement level read consistency in an oracle database?
Statement-level read consistency is the default.All the data that a query sees come from a single point in time. Query will see consistent data
In simple terms, SELECT,INSERT,UPDATE,DELETE statements will see the commited data. Any commits in due course will not affect the transaction which has already started
45) How to fix ORA-02421: missing or invalid schema authorization identifier?

I tried creating a schema with authorization and got the following error. Sequence of steps and fix have been discussed here
SQL> create schema authorization play;
create schema authorization play
ERROR at line 1:
ORA-02421: missing or invalid schema authorization identifier
SQL> create user play identified by play;
User created.
SQL> grant create session to play;
Grant succeeded.
SQL> connect play/play
SQL> create schema authorization play;
Schema created.

46) What is use of bitmap index?

Bitmap index is widely used in case of true or false scenarios like gender column. In addition it provides best performance in case of read only operations in systems like datawarehousing. However, benefits of bitmap index is application based

47) What does ORA-00928: missing SELECT keyword mean?
I tried joining two tables using UNION set operator and got this error. This is because I used a wrong syntax. Result set of two select statements can be combined together using an Union set operator
SQL> select * from t1 union t2;
select * from t1 union t2
ERROR at line 1:
ORA-00928: missing SELECT keyword

This change in syntax fixed the problem.
SQL> select name from t1 union select location from t2;

48) When does ORA-01950: no privileges on tablespace ‘SYSTEM’ occur?
I created an user, granted him create session, create table privilege. Now I connected to the user schema and tried creating a table. By default if no tablespace is assigned to an user he is assigned space in default tablespace. In such case it is necessary to specify the user quota in this default tablespace. Altering the user and allocating him quota in the default tablespace fixed the issue.
SQL> create user temp identified by temp;
User created.
SQL> grant create session to temp;
Grant succeeded.
SQL> grant create table to temp;
Grant succeeded.
SQL> connect temp/temp;
SQL> create table test(id int primary key,name varchar2(10));
create table test(id int primary key,name varchar2(10))
ERROR at line 1:
ORA-01950: no privileges on tablespace ‘SYSTEM’

SQL> alter user temp quota unlimited on system;
User altered.
SQL> connect temp/temp
SQL> create table test(id int primary key,name varchar2(10));
Table created.

49) How to collect html output form Oracle database tables?
Oracle database offers an easy sql*plus option to generate html output from sql prompt
set markup html on
spool output.html
select * from dual;
spool off
set markup html off
The above command does produce output in html format
Utilities like UTL_FILE does create output in csv, text format
50) What is the use of oratab file in an oracle database environment?
The file located in /etc/ on Linux and most of the UNIX systems, /var/opt/oracle/ in a solaris environment is the control file that is made use of by tools like database configuration assistant DBCA, database upgrade assistant DBUA. This file lists the databases that are installed on a particular server environment. In case of conflicts during a new database install or upgrade, check this file and edit this manually to comment or remove entries to fix conflicts
51) You are using DBUA to upgrade a database from to latest 12c. Will this automatically update the COMPATIBLE parameter value?
Yes. As far as the upgrades are concerned the database in version will have COMPATIBLE value set to 11.0.0 for upgrade purpose and this is taken care of automatically
52) You are using DBUA to upgrade a database from to latest 12c. Will this automatically update the COMPATIBLE parameter value?
The DBUA will not change the COMPATIBLE parameter and we need to manually make this change by setting COMPATIBLE to 12.1.0 for the database to utilize all 12c features
53) When is max_string_size set to extended?
By default oracle database 12c supports 32KB for datatypes like VARCHAR2,NVARCHAR2,RAW. To store over 4000 bytes this parameter value needs to be set to extended
54) Your 12c database is in open mode. You want to set max_String_size to EXTENDED. How will you do that?
For this changes to happen the database should be in upgrade mode. If database is in open mode, get a maintenance window and perform the following steps
Shutdown the database using immediate option. This is default in most cases
shutdown immediate;
export ORALCE_SID=value – Linux and unix systems
set ORACLE_SID=value – In windows systems
Log into database using an user with sysdba privilege
sqlplus ‘/ as sysdba’
Bring back database in upgrade mode
startup upgrade pfile=’specify location of this file’;
Change the value of max_String_size
Run the utl32k.sql script. This script needs to be run as an user with SYSDBA privilege. As we already logged in like that proceed further
Shutdown the database and startup the database in normal mode
shutdown immediate;
Make sure there are no invalid objects
select * from dba_objects where status<>‘VALID’;
select * from dba_objects where status=’INVALID’;
Make sure the above command returns No Rows
55) What are the possible values of max_string_size?
This parameter can take one of the two values – STANDARD, EXTENDED
56) You have set the value of max_String_Size to EXTENDED. Can you change it back to standard?
Once set max_string_size can’t be changed back to STANDARD. This parameter sets storage value of datatypes and this will have an impact
57) You are in process of performing an upgrade and see that there is low space in fast recovery area. DBUA tool is being used for upgrade purpose. How can you fix this issue?
During the process of upgrade it becomes necessary to monitor the space usage in fast recovery area. Also, check the alert.log file for errors related to space issues. As a first step to avoid space issues the database needs to be set to NOARCHIVELOG mode before upgrade. In case you still get space issues in fast recovery area during database upgrade using DBUA follow these steps:
export ORALCE_SID=value – Linux and unix systems
set ORACLE_SID=value – In windows systems
Log into database as sysdba user
sqlplus ‘/ as sysdba’
Increase the size of fast recovery area as follows :
alter system set db_recovery_file_dest_size=50g; (this is a new sample size. set values accordingly)
58) What are the uses of v$sysaux_occupants.move_procedure procedure?
As the name suggests this component is used for moving purpose like :
a) The procedure may be used for some components to relocate component data to the SYSAUX
tablespace from its current tablespace
b) The procedure may be used for some components to relocate component data from the SYSAUX
tablespace to another tablespace
59) How is the service registration performed with listener?
The service registration with oracle net listener is taken care of by the process monitor PMON process of each and every database instance as they get installed in an oracle server
60) Which commands are used to perform backup and restore of ASM metadata?
Using commands md_backup and md_restore ASM metadata is backedup and used for restore and recovery regularly. This information comes handy during restore and recovery of a failed diskgroup. RMAN backup can be used in combination with the md_restore command to perform the restore and recovery of a failed disk group
61) One of the default tablespaces in a pluggable database is corrupt. Does this demand complete database restart?
No. The corrupt tablespace of the pluggable database can be made offline
62) In your oracle database unified auditing has been enabled. Which oracle component is audited by default?
By default oracle RMAN the recovery manager is audited once the unified auditing option is enabled
63) Your container database runs in ARCHIVELOG mode with some pluggable databases. The SYSAUX tablespace of the root container goes corrupt. How to perform restore and recovery of the SYSAUX tablespace?
Perform the following steps to bring back SYSAUX tablespace online:
a) Take the corrupt SYSAUX tablespace offline
b) Restore the datafile from last VALID backup
c) Apply the archived reso logs to make it consistent
d) Bring back the SYSAUX tablespace online
64) In your oracle database you have enabled multiprocess multithreaded architecture. What advantage do you get from this feature?
Once the multiprocess multithreaded architecture is enabled it brings the following benefits:
a) Improved parallel execution performance
b) Reduced CPU utilization
c) Reduced virtual memory utilization
65) You have defined global partitioned B-tree indexes in a large heap table that you would like to move to a new tablespace with minimal impact. What happens to the indexes after this move?
Once the partitions are moved, global indexes must be manually rebuilt

Posted on

Oracle interview questions and answers for freshers

1) You want to upgrade your database to 12c version. Which upgrade script will you make use of?
Starting 12c upgrade can be used for upgrade purpose. This is a new script instead of catupgrd.sql from 12c onwards
2) In 12c upgrade can we still make use of catupgrd.sql?
Yes. It is possible to make use of catupgrd.sql in 12c upgrade with two rules to be taken into consideration :
a) Specify PARALLEL=NO as follows :
@$ORACLE_HOME/rdbms/admin/catupgrd.sql PARALLEL=NO
b) Once this script run completes catuppst.sql script must be run manually
3) Is is necessary to manually run capuppst.sql script after running for upgrade purpose?
No need. Only if catupgrd.sql is used this script needs to be run manually
4) What is the default parallelism while running script?
This script runs with default parallelism of 4. This value can be changed using option -n. If set to 0 this script runs in noparallel aka serial mode. The maximum value of -n can be upto 8
5) You want to collect spool and log files while running script. Which option can be used for this purpose?
Make use of option -l to specify spool and log files
6) You are starting to perform a database upgrade and startup using normal open mode instead of startup upgrade mode. Can you proceed with upgrade?
Nope. For performing database upgrade the startup upgrade option must be made use of. In addition to bringing up database in maintenance mode this mode automatically handles certain system parameter values needed for upgrade. Some other things that gets done in startup upgrade includes :
a) Restricts logon to as sysdba sessions. So, a normal user can’t connect to database by mistake
b) Disables system triggers
c) Performs additional operations that prepares environment for the upgrade
d) Suppresses ORA-00942 error for drop table statements that get generated during upgrade
e) Makes sure only genuine errors are recorded in the logfile
7) When do you make sure upgrade is successful?
Check the upgrade log files and make sure there are no ORA- and PLS- errors to ensure that this is successful upgrade. However, this is first level checking that is done after upgrade
8) After an upgrade completes which scripts will you run to recompile invalid objects?
Once the upgrade completes check the dba_objects view and make sure there are no errors :
select owner, object_name, status from dba_objects where status<>’VALID’;
select owner, object_name, status from dba_objects where status=’INVALID’;
Once you find some invalids run the scripts :
9) What does the oracle release numbers indicate?
When you start handling an environment be it test or production the major question that comes into picture happens to be question about which release the current database is running upon now. Typically this is a five digit number separated by dots
First Number – Major version of the database
Second is the maintenance release number
Third position is applicable only for application server release number
Fourth is the major patch release number
Fifth is the platform-specific release number
10) What happens when we apply a patch set update the PSU to an oracle database?
Application of a PSU changes the last digit (i.e) the fifth digit in the oracle release number
11) You plan to apply a major patch release that changes the fourth digit in an oracle release number. Can you install it on the fly?
Nope. For applying the major patch release say from to we need to install oracle software in a separate oracle home directory
12) What is an indirect upgrade in an oracle database environment?
It is also called as migration wherein the oracle software versions are not updated as opposed to data being extracted and migrated to latest database version. This is not an easy upgrade method as a direct upgrade using DBUA the graphical tool used for upgrade purpose
13) What is the advantage of indirect upgrade?
One major advantage is that it provides the ability to easily rollback to an older database. The source database is kept intact in this method which in most cases minimizes outage window. In case of failure the rollback process involves just starting up the old database service and listener in case DB was brought down during upgrade making it simple
14) What are the scenarios that demand using migration the indirect upgrade method?
There are some situations that demand the usage of migration like some situations below
a) Used for application testing. Mainly when the database changes are done and application needs to be tested with database this is used
b) Only chosen objects have been changed in database that does not demand full upgrade
c) The database is in very old version that the upgrade path is not supported. This demands data migration
d) The upgrade does not come with outage downtime window
e) The hardware server needs to be changed during upgrade
f) The Operating system needs to be changed during the upgrade
g) We want to minimize the downtime needed for upgrade
15) What are the indirect upgrade methods (or) migration methods supported by oracle database 12c?
Following are some of the migration methods supported by oracle database 12c:
a) Data pump tool can be used for migration. This tool has been introduced from Oracle 10g version. As far as traditional version goes exp/imp tools can be made use of. This is a preferred tool is the server migration, hardware migration is performed (or) versions where direct migration is not supported
b) CREATE TABLE AS SELECT method using database link can be used that creates tables and copies data from source to destination database
c) A major method that helps in reducing downtime happens to be transportable tablespaces a feature of datapump. This demands source database version to be or higher
d) Advanced products like dataguard, oracle golden gate if available can help with online database upgrade
16) What is the issue with using CREATE TABLE AS SELECT method for migration?
One practical issue happens to be no support for datatype like LONG. There are several other restrictions that make this not a preferred method of migration
17) You are performing upgrade using transportable tablespaces. The target platform endianness is different than source platform. What needs to be done to fix this?
Convert datafile using RMAN to perform migration
18) Can you perform database upgrade in archivelog mode with flashback database enabled?
Though it is possible it is not recommended. The first step in upgrade is to bring down the database in shutdown immediate mode, mount the database and disable archive logging using alter database noarchivelog, open it and use it for upgrade purpose. This is a major step to avoid performance issues that can occur during upgrade in archivelog mode
19) Is there an advantage of performing upgrade in archivelog mode with flashback enabled?
Yes. In case of error during upgrade it is possible to rollback database to an earlier state using flashback logs and guaranteed restore point
20) To which location are the DBUA log files and scripts written by default?
By default DBUA log files and scripts are written to $ORACLE_BASE/cfgtoollogs/dbua/db_unique_name/upgradeN directory
21) What is use of upgrade logs?
They can be used to fully understand the upgrade process, look for errors and fix issues in case the DBUA is used for upgrade and screen is stalled during process of upgrade

Posted on

Oracle RAC DBA Interview Questions

1) What is Oracle RAC?
Oracle RAC stands for Real Application cluster. It is a clustering solution from Oracle Corporation that ensures high availability of databases by providing instance failover, media failover features.
2) Mention the Oracle RAC software components:-
Oracle RAC is composed of two or more database instances. They are composed of Memory structures and background processes same as the single instance database. Oracle RAC instances use two processes GES(Global Enqueue Service), GCS(Global Cache Service) that enable cache fusion.Oracle RAC instances are composed of following background processes:
ACMS—Atomic Controlfile to Memory Service (ACMS)
GTX0-j—Global Transaction Process
LMON—Global Enqueue Service Monitor
LMD—Global Enqueue Service Daemon
LMS—Global Cache Service Process
LCK0—Instance Enqueue Process
RMSn—Oracle RAC Management Processes (RMSn)
RSMN—Remote Slave Monitor
There is a single storage that is accessed by two are more of these instances
3) What is single point of failure?
SPOF stands for Single Point Of Failure.Every component in the system must have redundancy incorporated in it.Failure of a single component may lead to failure of entire system.
Components include – power supplies, hardware, storage disks, OS, database,applications.
Oracle Clustering provides solution to avoid SPOF (single Point Of Failure).
4) What is need of clustering solution?
In todays market data is considered to be the most precious asset of an organization. Database is the data repository. Customers need data on a 24×7 basis which demands the servers hosting database to be up and running all the time. As a protection against server failure due to media failure(Failure in server hardware) or instance failure(database failure) clustering solutions have been developed.
5) What is GRD? How does this help with cache fusion?
GRD stands for Global Resource Directory. The GES and GCS maintains records of the statuses of each datafile and each cached block using global resource directory.This process is referred to as cache fusion and helps in data integrity.
6) Give Details on Cache Fusion:-
Oracle RAC is composed of two or more instances. When a block of data is read from datafile by an instance within the cluster and another instance is in need of the same block,it is easy to get the block image from the instance which has the block in its SGA rather than reading from the disk. To enable inter instance communication Oracle RAC makes use of interconnects. The Global Enqueue Service(GES) monitors and Instance enqueue process manages the cahce fusion.
7) Give Details on ACMS:-
ACMS stands for Atomic Controlfile Memory Service.In an Oracle RAC environment ACMS is an agent that ensures a distributed SGA memory update(ie)SGA updates are globally committed on success or globally aborted in event of a failure.
8) What is clustering?
Clustering is a High availability solution.Clustering makes physically separate servers appear as a single server to the end user.Clustering provides scalability at all level – OS, Storage, database, Applications, hardware.Clustering makes the application available 24x7x365.
9) Give details on GTX0-j :-
The process provides transparent support for XA global transactions in a RAC environment.The database autotunes the number of these processes based on the workload of XA global transactions.GLOBAL_TXN_PROCESSES setting specifies the initial number of GTXn background processes per instance. This process is seen only in RAC environments. The range of value for GLOBAL_TXN_PROCESSES can be from 1 to 20 and there is no definite need to set this parameter. The number of processes needed is decided by oracle database automatically and is tuned on demand
10) Give details on LMON:-
This process monitors instance membership in a RAC encironment, detects isntance transitions, reconfigures GES and GCS resources as needed. This is called Global Enqueue Service Monitor Process primarily used for managing global resources
11) Give details on LMD:-
As LMON is for monitoring global enqueue services, this is global enqueue services daemon process. This process manages incoming remote resource requests within each instance. LMD0 particularly processes incoming enqueue request messages. IT controls access to global enqueues
12) Give details on LMS:-
This process is called as Global Cache service process.This process maintains statuses of datafiles and each cahed block by recording information in a Global Resource Dectory(GRD).This process also controls the flow of messages to remote instances and manages global data block access and transmits block images between the buffer caches of different instances.This processing is a part of cache fusion feature.
13) Give details on LCK0:-
This process is called as Instance enqueue process.This process manages non-cache fusion resource requests such as library and row cache requests.
14) Give details on RMSn:-
This process is called as Oracle RAC management process.These processes perform manageability tasks for Oracle RAC. Tasks include creation of resources related Oracle RAC when new instances are added to the cluster.
15) Give details on RSMN:-
This process is called as Remote Slave Monitor.This process manages background slave process creation and communication on remote instances. This is a background slave process.This process performs tasks on behalf of a co-ordinating process running in another instance.
16) What components in RAC must reside in shared storage?
All datafiles, controlfiles, SPFIles, redo log files must reside on cluster-aware shred storage.
17) What is the significance of using cluster-aware shared storage in an Oracle RAC environment?
All instances of an Oracle RAC can access all the datafiles,control files, SPFILE’s, redolog files when these files are hosted out of cluster-aware shared storage which are group of shared disks.
18) Give few examples for solutions that support cluster storage:-
ASM(automatic storage management),raw disk devices,network file system(NFS), OCFS2 and OCFS(Oracle Cluster Fie systems).
19) Give details on oracle rac lkdebug utility:-
LKDEBUG is an oracle supplied utility.LKDEBUG is integrated with ORADEBUG utility.To use LKDEBUG we must login with SYSDBA system privilege.
LKDEBUG is used to obtain information about the current state GCS and GES structures in the instance.
To obtain information on LKDEBUG options issue the following command
20) What is an interconnect network?
an interconnect network is a private network that connects all of the servers in a cluster. The interconnect network uses a switch/multiple switches that only the nodes in the cluster can access.
21) How can we configure the cluster interconnect?
Configure User Datagram Protocol(UDP) on Gigabit ethernet for cluster interconnect.On unix and linux systems we use UDP and RDS(Reliable data socket) protocols to be used by Oracle Clusterware. Windows clusters use the TCP protocol.
22) Can we use crossover cables with Oracle Clusterware interconnects?
No, crossover cables are not supported with Oracle Clusterware interconnects.
23) What is the use of cluster interconnect?
Cluster interconnect is used by the Cache fusion for inter instance communication.
24) How do users connect to database in an Oracle RAC environment?
Users can access a RAC database using a client/server configuration or through one or more middle tiers ,with or without connection pooling.Users can use oracle services feature to connect to database.
25) What is the use of a service in Oracle RAC environment?
Applications should use the services feature to connect to the Oracle database.Services enable us to define rules and characteristics to control how users and applications connect to database instances.
26) What are the characteristics controlled by Oracle services feature?
The characteristics include a unique name, workload balancing and failover options,and high availability characteristics.
27) Which enable the load balancing of applications in RAC?
Oracle Net Services enable the load balancing of application connections across all of the instances in an Oracle RAC database
28) What is a virtual IP address or VIP?
A virtual IP address or VIP is an alternate IP address that the client connections use instead of the standard public IP address. To configure VIP address, we need to reserve a spare IP address for each node, and the IP addresses must use the same subnet as the public network.
29) What is the use of VIP?
If a node fails, then the node’s VIP address fails over to another node on which the VIP address can accept TCP connections but it cannot accept Oracle connections.
30) Give situations under which VIP address failover happens:-
VIP addresses failover happens when the node on which the VIP address runs fails, all interfaces for the VIP address fails, all interfaces for the VIP address are disconnected from the network.
31) What is the significance of VIP address failover?
When a VIP address failover happens, Clients that attempt to connect to the VIP address receive a rapid connection refused error .They don’t have to wait for TCP connection timeout messages.
32) What are the administrative tools used for Oracle RAC environments?
Oracle RAC cluster can be administered as a single image using OEM(Enterprise Manager),SQL*PLUS,Servercontrol(SRVCTL),clusterverificationutility(cvu),DBCA,NETC
33) How do we verify that RAC instances are running?
Issue the following query from any one node connecting through SQL*PLUS.
$connect sys/sys as sysdba
SQL&gt;select * from V$ACTIVE_INSTANCES;
The query gives the instance number under INST_NUMBER column, host_:instancename under INST_NAME column.
34) What is FAN?
Fast application Notification as it abbreviates to FAN relates to the events related to instances,services and nodes.This is a notification mechanism that Oracle RAC uses to notify other processes about the configuration and service level information that includes service status changes such as,UP or DOWN events.Applications can respond to FAN events and take immediate action.
35) Where can we apply FAN UP and DOWN events?
FAN UP and FAN DOWN events can be applied to instances,services and nodes.
State the use of FAN events in case of a cluster configuration change?
During times of cluster configuration changes,Oracle RAC high availability framework publishes a FAN event immediately when a state change occurs in the cluster.So applications can receive FAN events and react immediately.This prevents applications from polling database and detecting a problem after such a state change.
36) Why should we have separate homes for ASM instance?
It is a good practice to have ASM home separate from the database home (ORACLE_HOME).This helps in upgrading and patching ASM and the Oracle database software independent of each other.Also,we can deinstall the Oracle database software independent of the ASM instance.

37) What is the advantage of using ASM?
Having ASM is the Oracle recommended storage option for RAC databases as the ASM maximizes performance by managing the/storage configuration across the disks. ASM does this by distributing the database file across all of the available storage within our cluster database environment.
38) What is rolling upgrade?
It is a new ASM feature from Database 11g. ASM instances in Oracle database 11g release(from 11.1) can be upgraded or patched using rolling upgrade feature. This enables us to patch or upgrade ASM nodes in a clustered environment without affecting database availability.During a rolling upgrade we can maintain a functional cluster while one or more of the nodes in the cluster are running in different software versions
39) Can rolling upgrade be used to upgrade from 10g to 11g database?
No,it can be used only for Oracle database 11g releases(from 11.1) and upwards
40) State the initialization parameters that must have same value for every instance in an Oracle RAC database:-
Some initialization parameters are critical at the database creation time and must have same values.Their value must be specified in SPFILE or PFILE for every instance.The list of parameters that must be identical on every instance are given below:
41) Can the DML_LOCKS and RESULT_CACHE_MAX_SIZE be identical on all instances?
These parameters can be identical on all instances only if these parameter values are set to zero.
42) What two parameters must be set at the time of starting up an ASM instance in a RAC environment?
The parameters CLUSTER_DATABASE and INSTANCE_TYPE must be set.
43) Mention the components of Oracle clusterware:-
Oracle clusterware is made up of components like voting disk and Oracle Cluster Registry(OCR).
Shared storage is needed for voting disk (quorum disk). Voting disk can be stored on a raw device or a clustered file system.Voting disk can be stored in Oracle ASM and redundancy level can be set to high
Gives details on node membership – Nodes currently available within the cluster
Ocssd uses voting disk to determine which nodes join and leave the cluster. So voting disk is known as CSS(cluster synchronization services) voting disk
In oracle 10.2 and above, 3 copies of voting disks are created by default. Voting disks can be mirrored
Odd number of voting disk needs to be maintained – recommendation from oracle
44) What is a CRS resource?
Oracle clusterware is used to manage high-availability operations in a cluster.Anything that Oracle Clusterware manages is known as a CRS resource aka cluster ready services. Some of the CRS resources are oracle RAC database, ASM instances, database services, listeners, VIP addresses, ASM diskgroups, application processes. Configuration and status of these resources that are managed by oracle clusterware is tracked by clusterware. It is interesting to note that clusterware infrastructure management is automated in the sense that the status of these resources are checked at periodic intervals, resources are restarted for fixed number of time if they fail
45) What is the use of OCR?
Oracle clusterware manages CRS resources based on the configuration information of CRS resources stored in OCR(Oracle Cluster Registry).
46) How does a Oracle Clusterware manage CRS resources?
Oracle clusterware is a mandate software that needs to be installed in each and every server that form part of RAC cluster. They are installed in their separate oracle homes that are not shared. Oracle clusterware manages CRS resources based on the configuration information of CRS resources stored in OCR(Oracle Cluster Registry).
47) Name some Oracle clusterware tools and their uses?
OIFCFG – allocating and deallocating network interfaces
OCRCONFIG – Command-line tool for managing Oracle Cluster Registry
OCRDUMP – Identify the interconnect being used
CVU – Cluster verification utility to get status of CRS resources
48) What are the modes of deleting instances from ORacle Real Application cluster Databases?
We can delete instances using silent mode or interactive mode using DBCA(Database Configuration Assistant).
49) How do we remove ASM from a Oracle RAC environment?
We need to stop and delete the instance in the node first in interactive or silent mode.After that asm can be removed using srvctl tool as follows:
srvctl stop asm -n node_name
srvctl remove asm -n node_name
We can verify if ASM has been removed by issuing the following command:
srvctl config asm -n node_name
50) How do we verify that an instance has been removed from OCR after deleting an instance?
Issue the following srvctl command:
srvctl config database -d database_name
cd CRS_HOME/bin
51) How do we verify an existing current backup of OCR?
We can verify the current backup of OCR using the following command : ocrconfig -showbackup
52) What are the performance views in an Oracle RAC environment?
We have v$ views that are instance specific. In addition we have GV$ views called as global views that has an INST_ID column of numeric data type. GV$ views obtain information from individual V$ views.
53) What are the types of connection load-balancing?
There are two types of connection load-balancing:server-side load balancing and client-side load balancing.
54) What is the difference between server-side and client-side connection load balancing?
Client-side balancing happens at client side where load balancing is done using listener.In case of server-side load balancing listener uses a load-balancing advisory to redirect connections to the instance providing best service.
55) Give details on srvm_trace:-
Oracle RAC (Real Application Cluster) SRVM_TRACE environment variable is an oracle RAC (Real Application Cluster) environment variable from Oracle
It is used in the debugging on Oracle RAC (Real Application Cluster) utility srvctl.
56) Give the usage of srvctl:-
srvctl start instance -d db_name -i “inst_name_list” [-o start_options]srvctl stop instance -d name -i “inst_name_list” [-o stop_options]srvctl stop instance -d orcl -i “orcl3,orcl4” -o immediatesrvctl start database -d name [-o start_options]srvctl stop database -d name [-o stop_options]srvctl start database -d orcl -o mount
57) What is an OCRCHECK utility?
An ocrcheck utility is a diagnostic tool used for diagnosing OC(Oracle Cluster Registry) Problems.This is used to verify the Oracle Cluster Registry(OCR) integrity.
58) What does an ocrcheck display?
The OCRCHECK utility displays the version of the OCR’s block format, total space available and used space, OCRID, and the OCR locations that we have configured.
59) How does ocrcheck perform integrity check?
OCRCHECK performs a block-by-block checksum operation for all of the blocks in all of the OCRs that we have configured. It also returns an individual status for each file as well as a result for the overall OCR integrity check.
60) Give a sample output of ocrcheck utility:-
Sample of the OCRCHECK utility output:
Status of Oracle Cluster Registry is as follows :
Version : 2
Total space (kbytes) : 262144
Used space (kbytes) : 16256
Available space (kbytes) : 245888
ID : 1918913332
Device/File Name : /dev/raw/raw1
Device/File integrity check succeeded
Device/File Name : /dev/raw/raw2
Device/File integrity check succeeded
Cluster registry integrity check succeeded
61) Where does an ocrcheck utility create a log file?
OCRCHECK creates a log file in the directory CRS_home/log/hostname/client.
How can we change the amount of logging?
To change amount of logging, edit the file CRS_home/srvm/admin/ocrlog.ini.
62) What is scalability?
Scalability ensures that performance should remain good irrespective of increase in workload.Infrastructure scalability starts from hardware (physical server), OS, storage, database and ends with application
Clustering solution provides scalability as it makes physically seperate servers appear as a single machine to the end user.
Scalability can be of two types:
Vertical scalability – Based on the workload the server size needs to be increased like adding mroe memory, more CPU, hard disk etc
Horizontal scalability – Instead of going for single expensive hardware, some projects can be supported with horizontal scalability wherein lots of less expensive server hardware is purchased, they are given private interconnect and to end user they appear as single server. Resources are shared and this forms basics of RAC architecture
63) What is oracle RAC hardware requirement?
A typical Oracle RAC implementation needs the following hardware:
i) Two or more physical servers – This is basics of cost cutting. Henceforth, usually low cost x-86 servers are used for the implementation
ii) Networking across these servers
iii) storage shared by these servers
iv) Linux flavors like RHEL, Oracle Linux is commonly used for RAC implementation. Still windows is supported
64) What is the difference between user accounts grid and oracle?
Starting oracle 11gR2 oracle clusterware is integrated with oracle ASM and is available as grid infrastructure. OS user grid owns the grid infrastructure that includes clusterware as well as oracle ASM instance. Oracle RAC database owner is oracle user. This is same as oracle user that is used in stand-alone installations
65) Can Cluster verification utility be Downloaded for free?
Cluster verification utility popularly called the cvu is the utility that comes as an integral part of oracle clusterware. CVU is very helpful using installation and configuration of oracle clusterware and oracle RAC (Real application cluster).It is useful during various phases of installation starting from initial hardware setup to final stages of installation and configuration.We can download and use the CVU along with the Oracle RAC 10g and 11g versions. It is available for free from the oracle website
66) Give details on oracle RAC database datafiles :0
All data belonging to the database are stored in objects like tables, indexes, data dictionary, compiled PL/SQL code
Only one copy of datafile stored on shared storage the central location can be accessed by all instances in the RAC environment same as a single-instance database
Datafiles are not mirrored. Redundancy provided at storage level to prevent loss of datafiles. In 10.1 versions and above redundancy can also be achieve using ASM
67) You have a 32-bit laptop and you want to install oracle database RAC there. Can you do so?
I invoked the Oracle Universal Installer and tried installing and creating RAC databases in my Windows 7 laptop. I’ve installed vmplayer and created virtual machines with Ubuntu OS in them. I just invoked the RAC installation option in my Windows 7 OS. I got an error. I expected the Detail to be – OCR not installed. It requires that Oracle clusterware software must be installed and configured properly for the Oracle RAC to be installed successfully. To my surprise I had a different issue which said Oracle Database RAC can be installed only in 64-bit operating system
68) What is a voting disk?
A voting disk is a file that manages information about node membership.
What are the administrative tasks involved with voting disk?
Following administrative tasks are performed with the voting disk :
1) Backing up voting disks
2) Recovering Voting disks
3) Adding voting disks
4) Deleting voting disks
5) Moving voting disks
How do we backup voting disks?
1) Oracle recommends that you back up your voting disk after the initial cluster creation and after we complete any node addition or deletion procedures.
2) First, as root user, stop Oracle Clusterware (with the crsctl stop crs command) on all nodes. Then, determine the current voting disk by issuing the following command:
crsctl query votedisk css
3) Then, issue the dd or ocopy command to back up a voting disk, as appropriate.
Give the syntax of backing up voting disks:-
On Linux or UNIX systems:
dd if=voting_disk_name of=backup_file_name
voting_disk_name is the name of the active voting disk
backup_file_name is the name of the file to which we want to back up the voting disk contents
On Windows systems, use the ocopy command:
ocopy voting_disk_name backup_file_name
What is the Oracle Recommendation for backing up voting disk?
Oracle recommends us to use the dd command to backup the voting disk with aminimum block size of 4KB.
How do you restore a voting disk?
To restore the backup of your voting disk, issue the dd or ocopy command for Linux and UNIX systems or ocopy for Windows systems respectively.
On Linux or UNIX systems:
dd if=backup_file_name of=voting_disk_name
On Windows systems, use the ocopy command:
ocopy backup_file_name voting_disk_name
backup_file_name is the name of the voting disk backup file
voting_disk_name is the name of the active voting disk
How can we add and remove multiple voting disks?
If we have multiple voting disks, then we can remove the voting disks and add them back into our environment using the following commands, where path is the complete path of the location where the voting disk resides:
crsctl delete css votedisk path
crsctl add css votedisk path
How do we stop Oracle Clusterware?When do we stop it?
Before making any modification to the voting disk, as root user, stop Oracle Clusterware using the crsctl stop crs command on all nodes.
How do we add voting disk?
To add a voting disk, issue the following command as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to add:
crsctl add css votedisk path -force
How do we move voting disks?
To move a voting disk, issue the following commands as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to move:
crsctl delete css votedisk path -force
crsctl add css votedisk path -force
How do we remove voting disks?
To remove a voting disk, issue the following command as the root user, replacing the path variable with the fully qualified path name for the voting disk we want to remove:
crsctl delete css votedisk path -force
What should we do after modifying voting disks?
After modifying the voting disk, restart Oracle Clusterware using the crsctl start crs command on all nodes, and verify the voting disk location using the following command:
crsctl query css votedisk
When can we use -force option?
If our cluster is down, then we can include the -force option to modify the voting disk configuration, without interacting with active Oracle Clusterware daemons. However, using the -force option while any cluster node is active may corrupt our configuration
What does file management in oracle RAC involve?
File Management In Oracle RAC (Real Application Cluster) involves managing datafile,redo log file to name a few
Give details on datafile management in RAC :-
Datafiles store the database data and hence are the critical resources.
Datafiles are stored in a disk that is accessible by all the instances of the RAC i.e they are stored in shared storage.
Every instance in the cluster has to verify its access to datafile.Also it is equally important that they are they access the same version of the datafile. We can use the following command:
It is important to perform this integrity check after tasks like :
1) Tablespace addition
2) Tablespace deletion
3) Tablespace status change from offline/online to viceversa
4) Addition and deletion of datafiles
Whenever a datafile recovery is performed the first instance to start the cluster in RAC performs the datafile recovery.All instances start and verify their access after that.
Give details on Redo Log File Management in RAC :-
Each instance has its own online redo log groups.
To add a redo log group to a specific instance, specify the INSTANCE clause on the ALTER DATABASE ADD LOGFILE statement.
If we do not specify the instance when adding the redo log group,the redo log group is added to the instance to which you are currently connected.
Each instance must have at least two groups of redo log files. We must allocate the redo log groups before enabling a new instance with the
When the current group fills, an instance begins writing to the next log file group.If our database is in ARCHIVELOG mode,then each instance must save filled online log groups as archived redo log files that are tracked in the control file.During database recovery, all enabled instances are checked to see if recovery is
needed. If we remove an instance from your Oracle RAC database, we should disable the instance so it is does not have to be checked during database recovery
In a RAC environment how will you update the OCR with new spfile location?
Make use of srvctl utility command
srvctl modify database -db database_name -p ‘location_of_spfile’
How will you start a database in a RAC environment?
Using the srvctl command we can start the database in an Oracle RAC environment as follows :
srvctl start database -db database_name
You are making use of an Oracle restart configuration. You try to start the single instance using srvctl -i instancename but get an error. Why is that so?
It is because srvctl -i instancename option is not supported here

Posted on

DGMGRL Usage Oracle Dataguard DGMGRL Usage

Here are the steps to configure and start using DGMGRL the command-line interface at oracle dataguard broker
1) Set the value of dg_broker_start to true.
SQL> alter system set dg_broker_start=true;
System altered.
2) Open a parallel window and type dgmgrl in the command-line. Make sure the following window does open
3) Connect as sys user to the primary database. Don’t specify “as sysdba” as default connection is as sysdba. In recent versions the default connection privilege is SYSDG
DGMGRL> connect sys/learnersreference;
Connected as SYSDG
4) Get the primary database unique name from within sql prompt in parallel window
SQL> show parameter db_unique_name
———————————— ———– ————————-
db_unique_name string test
5) Create the configuration.
DGMGRL> create configuration ‘testdg’ as
> primary database is ‘test’
> connect identifier is ‘test’;
Configuration “testdg” created with primary database “test”
6) List the configuration using show command.
DGMGRL> show configuration;
Configuration Name: testdg
Enabled: NO
Protection Mode: MaxPerformance
Fast-Start Failover: DISABLED
Databases: test – Primary database
Current status for “testdg”:DISABLED
To get more detailed information we can make use of command show configuration verbose as well
7) Try creating standby database:
DGMGRL> add database ‘test_stdby’ as
> connect identifier is ‘new’
> maintained as physical;
Error: ORA-16796: one or more properties could not be imported from the database Failed.
The broker was unable to import property values for the database being added to the broker configuration. This error indicates:
– the net-service-name specified in DGMGRL”s CREATE CONFIGURATION or ADD DATABASE command is not one that provides access to the database being added, or
– there are no instances running for the database being added.
Remove the database from the configuration using the REMOVE CONFIGURATION or REMOVE DATABASE command. Make sure that the database to be added has at least one instance running and that the net-service-name provides access to the running instance. Then reissue the CREATE CONFIGURATION or ADD DATABASE command.

Posted on

Oracle interview questions and answers for experienced

1) What are general considerations while upgrading from 11g to 12c?
While upgrading from Oracle database 11g to 12c the latest multi-tenant system it makes real good sense to determine the upgrade method to be made use of. When we choose to upgrade we can use any one of the following methods:
1) Upgrade using Database Upgrade Assistant
2) Upgrade using manual method
3) Upgrade using Datapump- Install oracle software and database, use transportable tablespace feature to upgrade database. If the database version is the latest datapump feature can be used
2) How to deal with ORA-28000: the account is locked?
An user account should be unlocked and be in a valid state to log onto database. An user account can be locked due to one of the following reasons :
1) FAILED_LOGIN_ATTEMPTS – This profile parameter limit has been reached. In this case we need to wait until PASSWORD_LOCK_TIME. If it is immediate contact the database administrator (DBA)
2) Account has been locked due to security reasons – Contact DBA in this case
SQL> connect username/password
ORA-28000: the account is locked
Warning: You are no longer connected to ORACLE.
SQL> connect username/password
ORA-28000: the account is locked
SQL> connect sys/password as sysdba
SQL> alter user username identified by password account unlock;
User altered.
SQL> connect username/password
SQL> show user
USER is “username”
3) What variables are essential and need to be properly set before creation of Oracle database?
Oracle datbaase stores data and can be created using Oracle supplied dbca utility (or) SQL*PLUS create database command. In either case it becomes essential to set the following variables
ORACLE_HOME – The location on which Oracle binaries are installed
ORACLE_SID – the system identifier
PATH – the location of oracle home,binary etc
4) What are oracle supplied scripts related to setting environment variables?
oraenv, oratab for ksh,bash,bourne shell. For csh,tcsh coraenv,coratab are used
5) Where is oraenv/coraenv stored?
These files are created as part of Oracle binary installation. They are stored onto /etc in Linux. In case of solaris they are created in /var/opt/oracle folder. Note that if they don’t exist we can create and update these folders and files
6) Which Oracle utility is used to detect corruption?
Oracle supplied utility dbv reports corruption when run against Oracle data files
7) what are commonly used Oracle supplied utilities?
Oracle SQL*PLUS, RMAN, Dataump, SQL*Loader,dbshut,dbstart to name a few
8) What data is stored in oratab/coratab file?
oracle isd:oracle home:autoamted startup Y|N
This information is being made use of by oraenv while setting environment variables
9) What best practices can be adopted during import approach?
Import using impdp in general is faster than traditional import. Adopt following steps to expedite process
1) Disable archivelog
2) Set the datfile size in accordance with source
3) Enable archivelog back
10) What is default location of initialization parameter file in Oracle?
Oracle stores the initialization parameter file inside oracle_home/dbs (or) oracle_homedatabase. In either case the startup command looks for this and if found missing will not be able to start oracle instance
11) What is the order in which initialization parameter file is searched for?
Oracle looks for initialization parameter file in following order
Oracle looks for files by names mentioned in above order. If it finds nothing, it throws an error
12) What if the initialization parameter file is stored in non-default location?
Start the instance using the following command
startup pfile=”;
13) What is reason behind Linux Error: 28: No space left on device
This issue occurs when the mount point containing Oracle software, fills up. To look for space availability issue
df -h command
a quick fix would be to remove the old trace files from this location to save space
14) Oracle database connectivity issues and steps to take
Oracle database server hosts different clients and might be unavailable for various reasons
1) Oracle database server is down. Follow the steps in this scenario
14.1) Try to ping and see if remote server is available
ping servername
14.2) If ping returns okie, use telnet command
telnet serverip portnumber
14.3) If both the above works fine, see if listener is up and running
tnsping servername
14.4) If tnsping returns okie, look for Oracle database service availability
Log into server and look for processes – ps -ef in unix
In case of windows launch service manager, see OracleSIDService is up and running
14.5) Navigate to alert_sid.log and determine current status of database
14.6) Determine if the issue has something to do with space filling up in database folder
14.7) There are cases wherein look for logs in ORACLE_HOMEnetworkadminlog folder
if listener_logging is set off , then we dont see any logs here
15) Where do we store details on threshold metrics in oracle database?
SQL> desc dba_thresholds;
Name Null? Type
—————————————– ——– ———————-

16) What is the usage of dba_outstanding_alerts?
When a metric exceeds threshold an alert is generated
In Oracle database control an alert gets created when such trigger happens
17) What is Lost Updates Problem?
This problem is caused by a transaction trying to read a data while it is being updated by other transactions.Say transaction A is reading data from a table while it is being updated by transaction B, transaction B completes successfully and it is committed. If A has read the data before B is fully complete, it might end up with intermediate data.This results in lost update anomaly.Allowing transactions to read and update a table before the completion of another transaction causes the problem
18) Give details on Oracle Database Non-repeatable Reads Problem:-
Also known as fuzzy-read problem.When a transaction finds that it has read previously has been modified by some other transaction
Say we access a table’s data at a certain point in time and then we try to access the same data a little later, only to find that the values are different the second time
This inconsistent data during the same transaction causes a non repeatable-read problem
19) What is Phantom Read Problem Oracle Transaction Management?
Phantom problems are caused by the appearance of new data in between two database operations in a transaction.Say we’re reading from a table using SELECT statement
We re-execute the query after some time elapses and in meantime some user has inserted new data into the table.These extra rows are termed as “phanthoms” and this problem is referred to as phantom-read
20) What pl/sql is used for converting dictionary-managed to locally-managed tablespace in oracle database?
Oracle database has PL/SQL program that is used to convert dictionary-managed tablespaces to locally-managed tablespaces. Migration is simple and easy for all tablespaces except SYSTEM tablespace. In case of SYSTEM tablespace some extra steps are needed.
SQL> exec dbms_space_admin.tablespace_migrate_to_local(‘tablespacename’);
This is used to perform conversion and is most helpful when we migrate from earlier version of Oracle database to most recent version oracle database. It is to be noted that in recent versions of Oracle database all the tablespaces are locally managed.
21) How to fix ORA-00001: unique constraint (SYS.SYS_C003996) violated?
I created a table with PRIMARY KEY constraint on one of its column. A PRIMARY KEY is UNIQUE, NOT NULL. I tried inserting duplicate values into the PRIMARY KEY column and got the following error
SQL> create table dept(manager_id int primary key, dep_name varchar2(10));
Table created.
SQL> insert into dept values(3,’marketing’);
1 row created.
SQL> insert into dept values(3,’IT’);
insert into dept values(3,’IT’)
ERROR at line 1:
ORA-00001: unique constraint (SYS.SYS_C003996) violated
22) What are Oracle Database undocumented initialization parameters?
Oracle Database undocumented initialization parameters are a set of parameters not documented by oracle corporation. These parameters should not be altered.If the system crash is due to making changes to undocumented parameters, we may not get support from oracle corporation.In other way undocumented initialization parameters are unsupported initialization parameters. We can find the list of undocumented parameters by using the following query :
SQL> select a.ksppinm parameter, a.ksppdesc description, b.ksppstvl sessionvalue, c.ksppstvl instancevalue from x$ksppi a, x$ksppcv b, x$ksppsv c where a.indx = b.indx and a.indx = c.indx and substr(a.ksppinm,1,1) = ‘_’ order by a.ksppinm;
23) What is Oracle Database Flashback Recovery Area in Single-instance and RAC databases?
Flashback recovery area is a disk based recovery area introduced in Oracle 10.1. This is called fast recovery area in recent versions. It can store files for backup and recovery operations. This can be configured in regular file system as well as ASM in both single instance and RAC environment. In a RAC environment, flashback recovery area must be shared between all instances. It can be located on an ASM, a raw volume, a CFS (clustered-file system) like ACFS, shared directory mounted through NFS for each instance etc
A single flashback recovery area can be shared by multiple databases. To use a shared area for multiple databases, each database must have a database name specified using DB_NAME or DB_UNIQUE_NAME initialization parameter
Within the flashback recovery area, disk space is automatically managed by Oracle.Older backup files can be automatically deleted when they have been superseded or archived to offline storage devices
Flashback recovery area can be used to store all Oracle recovery files – transient and permanent recovery files

Posted on

Oracle DBA interview questions and answers for experienced

1) What are all the job duties of a Oracle DBA?
As opposed to regular office hours oracle database administration is a 5 to 9 job. If you are involved in supporting highly critical production env you are expected 24×7. Things apart here are the typical job duties of an Oracle DBA
1) Be part of capacity planning meeting that does involve business analyst, project manager, infrastructure manager, senior DBA member, sales professional on an as needed basis. DBA is expected to provide information on database capacity planning. This will help the project manager determine cost of storage, servers, software license and all the factors needed for a project implementation. The capacity planning meeting can also be a project meeting in some organizations.
2) Once the project does kick off , DBA should start with preparing the hardware, install the oracle software, create databases. With latest 12c multi-tenancy architecture consolidation projects are underway. Hence, DBA may have to create both pluggable database form seed template as well as migrate stand-alone instances onto container database
3) Once the database is up and running, the development will proceed with product creation. At the end the entire bundle is released as an installer in case of product development firms. IF this is a service provider, this may not be needed
4) Upgrading current version of database to latest version is a typical job of DBA. This starts with analyzing new features to determine the advantages of upgrade onto top management , testing upgrades, performing upgrades etc
5) Maintain consistent copy of database by properly planning full and incremental database backups. This is the first need in case of disaster recovery
6) Assist with developers to tune the queries utilizing oracle supplied tools
7) Monitor the database for alerts, space growth and add additional disk space on an as needed basis
8) Automate the routine job of DBA. This demands shell scripting knowledge these days
9) Latest technologies like RAC, ASM, Dataguard implementation to provide high availability, disaster recovery are some projects that has become a mandate
10) Work with cloud support engineer to migrate the existing environment onto cloud like Oracle cloud,rackspace, AWS etc
11) Conduct business continuity testing in the form of disaster recovery meetings on a quarterly basis
12)Support mission critical systems 24×7 via pager, cell phone
Oracle DBA – Oracle database administrator is vested with the following responsibility as a quick summary :
1) Install Oracle software
2) Create Oracle database
3) Configure Oracle database parameters
4) create spfile from pfile – spfile is used for dynamic parameter changes
5) Start the oracle instance
6) Administer Oracle instance
7) Stop oracle instance
8) Move data across databases
9) Use datapump utilities – expdp/impdp
10) Use SQL*Loader utility
11) Transportable tablespaces feature
12) RMAN backup, restore, recovery
13) User managed backup technique
14) User managed recovery technique
15) Tuning instances
16) Tuning SQL
17) RAC – Real Application Cluster
18) Dataguard – Primary standby database, logical standby database configuration
19) V$views
20) Manage oracle objects
2) How will you display oracle package specification and package body in an oracle database?
Display Package specification and Package Body can be accomplished as follows. For package specification details issue the command:
select line,text from user_source where name = ‘package_name’ and type = ‘PACKAGE’ order by line;
desc ;
For Package Body issue the command:
select line,text from user_source where name = ‘package_name’and type = ‘PACKAGE BODY’order by line;
3) How will you determine the tablespace management type in an oracle database?
In an oracle database environment dba views can be made use of to determine the management type
select tablespace_name, extent_management from dba_tablespaces;
We can also make use of ts$ to determine if this is locally managed or dictionary managed
4) What causes ORA-10616: Operation not allowed on this tablespace? How to fix this error?
Locally managed tablespaces can be migrated to dictionary managed tablespaces only when segment space management is manual. Otherwise we are not able to perform this migration. In such cases we are going to get the following error
ORA-10616: Operation not allowed on this tablespace
SQL> select extent_management, allocation_type,segment_space_management from dba_Tablespaces where tablespace_name=’TSNAME’;
———- ——— ——
LOCAL UNIFORM AUTO — This value should be manual instead of AUTO for successful migration
5) How do we determine if all the tablespaces are locally managed in an oracle database?
Oracle database 12c has tablespaces and all are locally managed. If there has been a database upgrade from previous versions like 11g , 10g etc it is interesting to know if tablespaces are dictionary-managed (or) locally managed after migration. We can get to know this information from dba_tablespaces data dictionary view
SQL> select tablespace_name,extent_management from dba_tablespaces;
—————————— ———-
6) How to convert a dictionary managed tablespace to locally managed tablespace and vice-versa?
It is possible to convert a dictionary managed tablespace to a locally managed tablespace and viceversa by following simple procedure
dbms_space_admin.tablespace_migrate_to_local(‘tablespacename’); – Migrates dictionary managed tablespace to a locally managed tablespace
dbms_space_admin.tablespace_migrate_from_local(‘tablespacename’); – Migrates a locally managed tablespace to a dictionary managed tablespace
Above mentioned commands need to be executed while database is in restricted mode
7) Can we perform dictionary to locally managed tablespace migration and viceversa of SYSTEM tablespace in a live environment?
While performing migration of system tablespace we need downtime as DB is to be brought to restrict mode. This migration needs a temporary tablespace, all other tablespaces should be read only
shutdown immediate;
startup restrict;
alter tablespace read only;
dbms_space_admin.tablespace_migrate_to_local(‘SYSTEM’); (or) dbms_space_admin.tablespace_migrate_from_local(‘SYSTEM’);
shutdown immediate;
8) Is there a possibility of an archivelog becoming corrupt? Archivelog a database file structure can be corrupted as a result of physical corruption due to disk storing it getting corrupt, an corruption in undo tablespace getting archived
9) What is possible impact of archivelog corruption? This leaves archivelog in unusable state. Main issue is that RMAN backups fail when an archived redolog is corrupt
What are some Oracle supplied utilities that come first hand during performance trouble shooting? Oracle AWR, ADDM ,ASH are some scripts that help us determine root cause of the issue
10) ORA-00257 error upon archiving file. Can you explain first hand cause of this issue? This is a common issue caused by archives getting filled up. Move archives from archival destination to a different location to fix this
What is the first place to look for database issues is Oracle? Oracle records all of its issues in a file called alert.log . It is stored in diagnostic_dest from oracle database 11g version onwards. It is stored in trace folder location
What is the use of listener.ora file? The listener.ora file contains the configuration information for the listener
Is there a listener.ora file on the client machine? The listener service is run only on the server. So,there is no listener.ora file on the client machines
How does dynamic service registration happens?
The PMON process(process monitor) is in charge of dynamic service registration of new oracle database service names with the listener,when we create new oracle databases,they’ll register themselves with the listener service.The PMON process will update the listener.ora file after each new database is created on a server.
What is the default value of INSTANCE_NAME parameter ?
The INSTANCE_NAME parameter defaults to the SID value entered during Oracle installation or database creation.
How will you display oracle database version details in a windows environment?
1) In search bar type cmd and click search. Launch Shell prompt (cmd tool)
2) Type sqlplus. It prompts for username and then password. To avoid this we can also make use of : sqlplus username/password@oracle_sid as sysdba. If you just typed sqlplus, enter the username; Password : enter value followed by as sysdba . Say your password is orcl then type orcl as sysdba. Click Enter. You’ll be logged onto SQL> prompt
3) To get the database version issue the following command :
SQL> select * from v$version;
Data dictionary that gets installed as part of installation maintains state of database, information about database in a set of tables in system tablespace. These tables can be static in the sense they update database related information slowly as well as dynamic that records up-to-date activity details. These views are called v$ views. One such view is v$version view that records database version details.
What is the way to determine DBID value while database is up and running?
While database is up and running the database unique identifier commonly called as DBID from v$database view.
What is the easiest way to determine if flash recovery area is enabled or disabled?
Check if db_recovery_file_dest parameter is set from within sqlprompt
How will you determine if backup of controlfile is enabled?
Log into RMAN prompt and issue command show all. This provides information on whether controlfile autobackup is enabled (or) disabled.
When I try to perform RMAN backup, I’m not allowed to do so. What could be first simple reason? Database in noarchivelog mode doesn’t allow us to perform backup. Make sure DB runs in archivelog mode
How to perform database refresh? What utility do you use? expdp/impdp is the simple tool that lets us perform database export /import and perform database refresh
What is OS level requirement for performing DB export datapump? A directory need to be created at OS and must be granted access at DB level for expdp to function properly.
Can we delete directly from oracle internal view?
Yes it is possible. As a simple example:
delete from kottd$ where sys_nc_oid$ not in (select oid$ from obj$ where type#=13);
What is the way to determine tablespace usage in an oracle database? SQL> desc dba_tablespace_usage_metrics Name Null Type TABLESPACE_NAME VARCHAR2(30) USED_SPACE NUMBER TABLESPACE_SIZE NUMBER USED_PERCENT NUMBER SQL> select * from dba_tablespace_usage_metrics;
How to determine which program and from which machine is it connected to database?
Make use of the following command that talks about the application which is referred to as program in oracle terms, the hostname of machine which is typically client machine (or) external application machines connecting to the database server : select program, machine from v$session where program like ‘%nameofexecutable%’;
How to permanently remove dropped objects in an oracle database? SQL> drop table table_name PURGE; SQL> PURGE table table_name; SQL>purge table ‘system_generated_tablename’; SQL> PURGE tablespace tabespace_name; – purge all objects that belong to this tablespace SQL> purge tablespace tablespace_name user user-name; – All objects of this user will be removed
What happens to dropped objects if there is a space pressure in an oracle database environment? Whenever there is a space pressure, Oracle automatically removes the dropped objects without purge being made
Your database is in archived redo log mode. Under what circumstances do you prefer to retain these archived redo logs in more than one location? For a normal project archived redo logs that get created in archival destination can be backed up periodically. This can be restored and recovered on as needed basis. There are some high sensitive projects that demand high availability and redundancy that literally demand zero downtime (or) closer to that. In such instances writing archived redo logs to more than one location is needed
Give details on add_months() function :-
In case of a database supporting administrative tasks such as employee management, order management system etc date and related functions will find a prominent role. One such interesting function in oracle SQL is the add_months() function.Say we have a customer order billing database as in case of an online service provider system. We’d like to know what his bill is going to be next month. This is very helpful in case of offers valid through a particular period.
Say we see many interesting offers from telephone providers these days.In such situations this oracle sql function comes in handy
select name,amount from billing where next_month=add_months(this_month,1);

Posted on

Oracle DBA Interview Questions

1) Is oracle user password encrypted by default?
Oracle user passwords aren’t encrypted by default.This is a security threat.Setting one parameter in the client side and one parameter in the server side we can ensure that Oracle always encrypts a password when it send it across a network.
Client Side : ora_encrypt_login=true
Server Side : dblink_encrypt_login=true
DES – data Encryption Standard will be used for encryption
2) Which oracle database user can make use of datapump utility?
All Oracle users can use the Data Pump utility by default. They can perform basic tasks using datapump utility
3) What is needed for a user to perform advanced datapump tasks in an oracle database?
To perform advanced tasks user needs the special privileges EXP_FULL_DATABASE and IMP_FULL_DATABASE. Granting of the roles mentioned above makes user a privileged user and helps user perform the following tasks:
1) Export and Import database objects owned by any user
2) Attach to and modify jobs started by another user
3) Use the all new remapping capabilities during a Datapump Import job
4) Is oracle database recovery manual or automated while making use of RMAN?
RMAN automates the entire recovery process in an oracle database. It basically involves the usage of the following two commands :
1) RESTORE command– restores entire database, tablespace, single data file, control files, archived redo logs, SPFILE from RMAN backup set or from image copy on disk
2) RECOVER Command – This will perform the actual media recovery by applying necessary archived redo logs or incremental backups
5) When are traditional point-in-time recovery techniques used? What was the disadvantage of this procedure?
Traditional point-in-time recovery techniques are used in case of logical corruption. The process involves restore datafile backup copies and then use archived logs to advance the database forward
This used to be time-consuming and cubersome. As a fix flashback database feature got introduced in latest version of oracle databases
6) What are the steps involved in configuring flashback database?
a) Check if database is in archivelog mode using sql command archive log list
Turn on archivelog mode if it isn’t as follows :
shutdown immediate;
startup mount;
alter database archivelog;
startup open;
Check and make sure archive logging is enabled as follows:
archive log list;
b) As a next step set up a flash recovery area
Set DB_FLASHBACK_RETENTION_TARGET to specify how far back we can flashback our database. The value specified is in minutes
alter system set db_flashback_retention_target = 1440; (1day=1440 minutes)
Shutdown and restart the database in mount mode
shutdown immediate;
startup mount;
Enable flashback database feature while database is in mount mode
alter database flashback on;
Startup open;
c) Check and make sure flashback feature is configured by issuing the following commands
SQL>select flashback_on from v$database;
The output should return YES
7) Give details on dirty-read problem in an Oracle database :-
Dirty-Read Problem occurs when a transaction reads data that has been updated by an ongoing transaction but has not been committed permanently
For example, transaction A has just updated the value of a column and it is now read by transaction B
What if transaction A rollsback the changes, intentionally or because it aborts for some reason? The value of the updated column will also be rolled back as a result
But, transaction B has already read the new value of the column, which is now incorrect because of rolling back of transaction A
8) What privilege is needed to access DB console web application in an oracle database?
DB Console is the free web based application shipped for free since Oracle database 10g. From Oracle 11g onwards database db console web application is shipped for free.
SELECT ANY DICTIONARY Privilege is needed to access DB Console in Oracle Database
9) What is automatic shared memory management? How is this achieved?
Automatic shared memory management is the new feature from oracle database 10g onwards. It is set by setting the parameter MEMORY_TARGET in initialization parameter file. It is possible to disable automatic shared segment management by setting the value of MEMORY_TARGET parameter to zero
SQL> alter system set memory_target=0 scope=memory;
System altered.
Scope has been specified as memory. It can be set to SPFILE, BOTH, MEMORY
10) What does a Flashback Versions Query do in an Oracle database?
Allows us to view all the versions of the same row over a period of time so that we can undo logical errors
It can also provide an audit history of changes, effectively allowing us to compare present data against historical data without performing any DML activity
Restores a table to a point in time or to a specified SCN without restoring data files
This feature uses DML changes to undo the changes in a table
The flashback table feature relies on undo data
11) What are many different types of recovery processes in an oracle database environment?
Oracle Recovery Process comprises of :
1) Crash and Instance Recovery
2) Media Recovery
Oracle automatically performs recovery when a single instance suddenly fails, or when all the instances of a RAC database fails
When an instance is shutdown with SHUTDOWN ABORT command, we need to perform a crash recovery
Instance recovery is same as crash recovery. It is the process in which surviving instances bring back the crashed instances in a RAC environment
Media recovery isn’t automatic. DBA has to initiate the recovery process
RMAN – used to perform block-media recovery
13) When does the error ORA-00903: invalid table name occur? How will you fix it?
Oracle object names are case sensitive. If we try creating a table with name specified in single quotes it throws this error.
SQL> create table ‘singlequote’ (test date);
create table ‘singlequote’ (test date)
ERROR at line 1:
ORA-00903: invalid table name
This can be resolved by specifying table name inside double quote
SQL> create table “doublequote” (test date);
Table created.
14) What are the procedures that you need to execute after applying patch in an Oracle environment? Oracle patches are binary files that make changes to Oracle home, including dicitonary views. Hence it is a good practice to always run statistics collection against system objects after patch application. Follow these simple steps as stand alone or anonymous block to gather statistics after patch application
Standalone Steps For executing the statistics collection procedures that can be run one by one
Anonymous Block that can include all the three statistics collection procedure and can be run as script as well:
15) What is the use of oradim utility? Is there a Linux equivalent of oradim utility?
oradim utility can be used in windows OS to perform a variety of tasks. There is no oradim equivalent utility for Linux/UNIX enironment
To startup an Oracle Service(instance) issue:
oradim -startup -sid sidname
To shutdown a service issue:
oradim -shutdown -sid sidname
If the following error occurs follow the steps below:
ORA-01017: invalid username/password; logon denied
oradim -shutdown -sid sisdname -syspwd sysuserpassword -shuttype srvc,instance -shutmode abort normal immediate
16) How does an undo tablespace size correspond to flashback database feature if one has been implemented?
Undo tablespace should be able to accommodate all the long-running transactions
It has to be big enough to accommodate flashback features we may implement in the database as oracle’s flashback lets us undo changes to the data at various levels
Some flashback features that utilize undo data are – Flashback query, flashback versions query, flashback table
17) How is sizing of individual undo segments done?
We should decide the size of the undo tablespace and the setting for the UNDO_RETENTION initialization parameter. Oracle will do the sizing of the individual undo segments
18) What is the use of Undo advisor present in OEM?
Undo advisor through OEM can be used to figure out the ideal size of undo tablespace and ideal duration to be specified for UNDO_RETENTION parameter. Using the current undo space consumption statistics, we can estimate future undo generation rates for the instance
19) Can you get details on database version as a normal user without SYSDBA privilege?
Database version is the first thing to determine any issue in case of issue investigation. If the DBA is not allowed to have details on SYS user password they can still get details with their normal user privilege
It is possible to identify the version of the oracle database instance when we are connected as normal user and SYS user with SYSDBA privilege. Here is a simple demonstration. Initially I’ve connected as SYS user with SYSDBA privilege.
SQL> select * from v$version;
20) How do I determine if a tablespace is unavailable (or) Dropped?
There are some core database dictionary objects that show detials on status of a tablespace. One such object is the ts$
create tablespace test datafile ‘d:oracledatabase’ 200M autoextend on;
SQL> select ts#,name,online$ from ts$ where name=’TEST’;
———- —————————— ———-
14 TEST 1
SQL> drop tablespace test including contents;
Tablespace dropped.
SQL> select ts#,name,online$ from ts$ where name=’TEST’;
———- —————————— ———-
14 TEST 3
21) What is advantage Of Using Direct-Path Loading Method Over Conventional Data Load?
SQL*Loader performs data loading using two methods:
1) Conventional loading
2) Direct-path loading
Conventional loading method used SQL INSERT statement to insert data into the tables one bind array size at a time.The direct-path loading option doesn’t use the SQL INSERT statement to put data into tables. It formats the Oracle datablocks and writes them directly to the database files.
This direct-write process eliminates much of the overhead involved in executing SQL statements to load tables.So it loads data much faster than the conventional loading method.For large data-loads direct-path loading method is the default loading method because of the speed constraints associated with the conventional loading method
22) What is the user-managed recovery procedure to recover control file in an oracle database?
User-Managed procedures can be followed to recover a control file.If we’ve lost all the control file, we can create a brand new control file using CREATE CONTROLFILE command
We can make use of control file created in trace
SQL>ALTER DATABASE BACKUP CONTROLFILE TO TRACE; – creates a file in udump directory
REUSE option asks Oracle to overwrite any of the old control files if they exist in their default location
23) Give details on many different datapump files :-
Data Pump uses the following files for its operation :
Dump Files :
These hold the table data as well as the metadata that’s being loaded or unloaded
Log Files :
These are the standard files for logging the messages and results of Data Pump Operation
SQL Files :
Data Pump Import uses a special parameter called SQLFILE, which will write all the DDL statement it will execute during the job to a file
Data Pump doesn’t actually execute the statements but write the DDL statements to the file specified by the SQLFILE parameter.
All log files, SQLFILE will overwrite any existing file with the same file name
If an older dump file of the same name already exists we’ll get an error
24) How to perform log sequence based incomplete recovery?
In some cases we see gap in archived redo logs (i.e) archived redo logs are available only until a particular log sequence number. In such cases log based recovery comes handy
In SQL Prompt ,
SQL> startup mount pfile=’pfile location’;
In RMAN Prompt
rman target sys/syspassword@oracle_sid
Log onto RMAN prompt and make sure database is in mount mode
restore database until sequence sequencenmber+1 thread thread-number;
This is an incomplete recovery and hence open database in SQL prompt:
alter database open resetlogs;
Note that if we restore and recover upto log sequence number 99 we specify value as 100
25) How to move controlfiles across disks?
Moving Control Files across disks happens when the company’s high availability policy demands the control files to be placed in different disks.Follow the steps to move the control files.
SQL>show parameter control_files; – list showing control file location.
SQL>shutdown immediate; – instance down
SQL> host mv oldfile new-file; move the files
SQL> startup nomount; – Nomount state to modify the initSID.ora/spfileSID.ora file
SQL>alter system set control_files=new-file; rename the parameter in parameter files.
SQL> startup force; – start the instance
SQL> show parameter control_files; – check the new location values
26) Is table automatically analyzed upon index creation?
Database statistics play a crucial role in determining the performance of the database. Oracle database has many inbuilt tool that will let us analyze the database objects. The most important database object used to store information is the table. An index is based on one or more column of a table.
Whenever a table is created it is not ANALYZED. When an index is created it is automatically ANALYZED.
SQL> create table test_anal (c1 date);
Table created.
SQL> select num_rows,last_analyzed from dba_tables where table_name=’TEST_ANAL’;
———- ———
Query the dba_tables data dictionary view to make sure that no rows have been analyzed upon table creation.
We’ll insert few rows into the table and see if it is analyzed.
SQL> insert into test_anal select created from dba_users;
36 rows created.
SQL> select num_rows,last_analyzed from dba_tables where table_name=’TEST_ANAL’;
———- ———
We see that no rows have been analyzed upon insertion into the table.Now lets look at how an index is created and its rows are automatically analyzed upon creation.
SQL> create index test_anal_index on test_anal(c1);
Index created.
SQL> select distinct_keys,last_analyzed from dba_indexes where index_name=’TEST_ANAL_INDEX’;
————- ———
24 26-NOV-18
We see that an index has been created and automatically analyzed upon creation Num_rows
27) How will you resize a tablespace in an Oracle environment?
Tablespace is a logical data structure in Oracle database management system. Tablespace is composed of one or more data files. A datafile can belong to only one tablespace.We can increase or decrease the size of tablespace by increasing or decreasing the size of datafile using RESIZE option
28) What Happens When An Oracle Transaction Is Committed?
The transaction tables in the redo records are tagged with the unique system change number (SCN) of the committed transaction
The log writer writes the redo log information for the transaction from the redo log buffer to the redo log files on the disk, along with the transaction’s SCN.
This is the point at which commit is considered complete in Oracle
Any lock that Oracle holds are released, and Oracle marks the transaction as complete
29) What to do while Access is denied error happens during creating multiple copies of controlfile for multiplexing control files?
Follow the correct set of steps to fix this issue
1) Show parameter control_files
2) shutdown immediate;
3) host copy source destination
4) startup nomount;
So, be careful on steps followed and get it done. The error typically happens when we try to perform the copy of controlfile in nomount mode
30) What is the use of v$flashback_database_log view and how this is associated with DB_RECOVERY_FILE_DEST_SIZE initialization parameter?
The important initialization parameter to be initialized while configuring flashback database in oracle database is DB_RECOVERY_FILE_DEST_SIZE. After setting the value it is possible to verify it using show parameter command.
Also v$views such as v$flashback_database_log, v$parameter can be used to obtain details about this initialization parameter.
SQL>  select flashback_size from v$flashback_database_log;
SQL> show parameter db_recovery_file_dest_size
NAME                                 TYPE        VALUE
———————————— ———– ——————————
db_recovery_file_dest_size           big integer 3G
31) What is database troubleshooting?
The primary job role of DBA administering many different flavors of databases, different types of databases say from relational databases like DB2, oracle, sql server, mysql to most recent mongodb, couchdb, cassandra , HBase,nosql databases would be to troubleshoot the issues that arise as part of database systems in place
Database troubleshooting can involve any level of issue fixes that arise in the database environment. This can involve taking a quick look at database connection issues, network issues, fixing corruption to complex tasks involving performance tuning of databases. Here are some common database troubleshooting tasks that a DBA does. You can share this in detail to crack your next DBA interview
1) Connectivity Issues – Now-a-days databases have been migrated from data center to cloud infrastructure. Expect this common interview question. When you find network connectivity issue perform some steps to investigate and fix the issues
Look for database availability – Database must be up and running for proper connectivity. If database is down bring it back. If issue still persists, look at alert.log in trace folder to determine root cause of down situation and take it from there on
Look for listener availability – Listener service should be up and running to allow clients to connect to database. TNS listener error is recorded in alert.log file. If this service is down start listener service
Look for network issue between database and web/application server – Typically applications that are web based work on HTTP request response architectural implementation. When a webpage is requested web server contacts database to pull needed data for rendering back to end user. If the web page is blank, look at network connectivity issue between database and web/application server. This is very common in cloud implementations
Tools Used : In case of oracle tnsping can be used to determine connectivity
lsnrctl status – Used to determine listener status
ping command from database to webserver and vice versa will help us determine network issues
Fiddler Capture – This is a tool that can be run to monitor step by step taken by an application to access, render information
2) System performance issues – Make use of OS utilities like sar, top, cpustat, memstat, top to look for resource consumption at OS level. In case of oracle database awrrpt.sql, addmrpt.sql, asjrpt.sql are the tools that come real handy to diagnose and fix the issues
3) Data Dictionary Views – Oracle comes with pre-defined set of tables and views that are stored in SYS schema. They store metadata information about database, current database operations etc. They come handy to fix database issues
4) Lock issues – Oracle is a DBMS based on ACID principle. Locks are established for data consistency and concurrency. At times they can be problematic causing slowness, inappropriate waits etc. Look and fix lock issues if needed
5) Cursour issues – Cursors form main focal point while investigating performance issues
6) Sizing of temporary and undo tablespaces – TEMP tablespace should be sized appropriately as they are used for sorting operations. UNDO tablespace store before image and sizing of undo tablespace, proper setting of undo_retention parameter helps in fixing many database issues
32) How can you get details of undo tablespaces?
Undo tablespaces created in the oracle database and in use can be queried from the data dictionary view dba_tablespaces. Similar to table segments, undo tablespaces are created and they are formed on undo segments. Here is the query to get list of undo tablespaces created in the database, undo tablespace in use
SQL> select tablespace_name from dba_tablespaces where contents=’UNDO’;
SQL> select value from v$parameter where name=’undo_tablespace’;
33) What is the use of undo_tablespace initialization parameter?
UNDO_TABLESPACE is the initialization parameter the defines what is the undo tablespace in use. Hence it is possible to query the v$parameter dynamic performance view to get details on current undo_Tablespcae. Show parameter undo_tablespace is going to fetch the same result
SQL> show parameter undo_tablespace;
NAME                                 TYPE        VALUE
———————————— ———– ———–
undo_tablespace                      string      UNDOTBS1
34) What is an oracle instance formed of?
An oracle instance is formed of two major components :
1) Background processes – the set of operating system processes that support the start, running, management of instances
2) Memory structures – the RAM structures that are allocated for many different database operations
35) From where can you get details about an oracle instance?
We can get information about the oracle instance by determining the current size of various memory structures that make up the instance.Clients such as SQL*PLUS, SQL Developer, TOAD etc can be used to obtain this information
1) Connect to database as privileged user SYSTEM
2) System global area, SGA as it is popularly called is a major component of memory structure
36) How to get details about SGA and PGA in an oracle instance?
Determine the current size, minimum size, maximum size of SGA. Components of SGA can be dynamically resized.
SQL> select COMPONENT,CURRENT_SIZE,MIN_SIZE,MAX_SIZE from v$sga_dynamic_components;
3) Next major component is Program Global Area(PGA).
SQL> select name,value from v$pgastat where name in(‘maximum PGA allocated’,’total PGA allocated’);
37) What is the significance of COMMIT_WRITE Initialization Parameter?
Default behavior is to have a disk I/O after each COMMIT. This causes a slight delay in finishing the transaction
We may avoid the delay resulting from frequent writing of redo log records and waiting for the confirmation of those writes by setting the COMMIT_WRITE to BATCH,NOWAIT
This parameter can be set at SYSTEM or SESSION level
38) How will you format the sqlplus output?
When we query certain tables, views and such database objects, we get the output in an unformatted fashion. In Oracle it is possible to format the sql output. We can format the column to be displayed. We can set size of columns.
Syntax – column column-name format a(xx); – where xx – can take values like 1,2,3,4…
Here are the set of commands to format the output.
SQL> select component,current_size from v$sga_dynamic_components;
—————————————————————- ————
shared pool 327155712
large pool 8388608
java pool 8388608
streams pool 0
DEFAULT buffer cache 452984832
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
—————————————————————- ————
DEFAULT 32K buffer cache 0
Shared IO Pool 0
ASM Buffer Cache 0
14 rows selected.
We can format the column values and set it accordingly.
SQL> column component format a30;
SQL> column current_size format 9999999999;
SQL> select component,current_size from v$sga_dynamic_components;
—————————— ————
shared pool 327155712
large pool 8388608
java pool 8388608
streams pool 0
DEFAULT buffer cache 452984832
KEEP buffer cache 0
RECYCLE buffer cache 0
DEFAULT 2K buffer cache 0
DEFAULT 4K buffer cache 0
DEFAULT 8K buffer cache 0
DEFAULT 16K buffer cache 0
—————————— ————
DEFAULT 32K buffer cache 0
Shared IO Pool 0
ASM Buffer Cache 0
14 rows selected.
39) What is the advantage of compressing a backup?
When backup sets are compressed :
1) Number of backup pieces produced will be less
2) Space needed by backupset  gets reuced (by atleast 50% to 75%)
What is the advantage of ZLIB over bzip2?
BZIP2 – Traditional algorithm which uses most of the CPU
ZLIB – CPU usage not a problem while using this algorithm as it uses less CPU time
42) What is the command used for enabling compression in versions prior to Oracle database?
43) How do you set the compression algorithm to be used in Oracle database?
44) What is need to compress data in oracle database and how is this being done?
Storage is always a costly entity. Saving space is very important as it saves cost for an organization. Oracle Corporation has come out with its properitory backup solution RMAN (Recovery Manager). RMAN has a feature to enable backup compression. The way backup is compressed is different in Oracle database 11g than in previous versions
45) You are getting error ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired. How will you fix this issue using oracle initialization parameter?
Oracle database has an interesting initialization parameter DDL_LOCK_TIMEOUT set at session level and system level. Whenever a DDL statement like ALTER is issued against a table a DDL lock is acquired in that object. If we try to perform any operation against that object, it results in an error.
ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired
To avoid such errors, we can set initialization parameters DDL_LOCK_TIMEOUT which performs automatic retries within the time. Value specified is in seconds
We can set DDL_LOCK_TIMEOUT at session level
SQL> alter session set ddl_lock_timeout=60;
Session altered.
It is possible to set the values at the database level.
SQL> alter system set ddl_lock_timeout=50;
System altered
46) ITIL SLA(Service Level Agreement) demands recovery of most systems to be done in minutes. What is the best way to achieve this in an Oracle database environment?
Oracle Database RECOVER a tablespace using RMAN is a easy way of recovering a single tablespace or a list of tablespaces. We can perform recovery in a OPEN database. We don’t have to shutdown the database.If needed we can bring down database to mount mode
1) Bring the tablespace offline. Rest of the database function will not be affected
RMAN> Alter tablespace tablespace_name OFFLINE;
2) Restore the tablespace as follows :
3) Recover the tablespace
RMAN> RECOVER tablespace tablespace_name;
4) Recovered tablespace can be brought online :
RMAN> Alter tablespace tablespace_name ONLINE;
47) What is an initialization parameter file? What is its significance in an oracle database environment?
Initialization parameter file is the file holding initialization parameters. Initialization parameters are also called as instance configuration parameters. Initialization parameter files are system location specific. Depends on OS:
There are two types of initialization parameter file – SPFILE and text initialization parameter file(init.ora)
Oracle Database locates the initialization parameter file by examining the filenames in the following order:
2) spfile.ora
3) init$ORACLE_SID.ora (text initialization parameter file
48) Give details on oracle client in an oracle environment :-
Install oracle client software at client machines.Download client software from oracle website.
Versions of oracle server and oracle client need not be compatible.Oracle recommends the usage of compatible server and client versions
49) Can you create a database tablename with spaces in them? Is it possible to perform DML operations against this table?
It is possible to create a table the essentially important basic database object name with spaces in them. Specify them inside double quotes
SQL>  create table “with space sample”(“-Hyphen” date);
Table created.
SQL> insert into “with space sample” values(sysdate);
1 row created.
SQL> select * from with space sample;
select * from with space sample
ERROR at line 1:
ORA-00903: invalid table name
SQL> select * from “with space sample”;
This produced the correct result if specified within double quotes. It is also interesting to know that DML (Data Manipulation language) operations such as INSERT,UPDATE,DELETE can be performed against these objects. Names must be specified within double quotes.
50) What is a database resource manager?
Database resource manager allows us to create resource plans,which specify how much of our resources should go to various consumer groups.We can group users based on their resource requirement and we can have the database resource manager allocate a preset amount of resources to these groups.We can easily prioritize our users and jobs.
51) What are the uses of a database resource manager?
1) The database resource manager enables us to limit the length of time a user session can stay idle and to automatically terminate long-running SQL statements and user sessions.
2) Using the database resource manager we can set initial login priorities for various consumer groups
3) By using the concept of active session pool,we can specify the maximum number of concurrent active sessions for a consumer group-the Database resource manager will automatically queue all the subsequent requests until the currently running sessions complete.
Can we switch users between resource consumer groups/consumer groups?
DBA’s can automatically switch users from one resource group to another ,based on preset resource usage criteria,and can limit the amount of undo space a resource group can use.
52) What are the four elements of a Database resource Manager?
Database resource manager is composed of the following four elements : Resource consumer group, resource plan,resource allocation method,resource plan directive.
53) What is a resource consumer group?
A resource consumer group is used to group together similar users based on their resource needs.
54) What is a resource plan?
The resource plan lays o how resource consumer groups are allocated resources. Each resource plan contains a set of resource consumer groups that belong to this plan, together with instructions as to how resources are to be allocated among these groups.
For instance,a resource plan may dictate CPU resources be allocated among three resource consumer groups so that the first group gets 60 percent and the remaining two groups get 20 percent each of the total CPU time.
55) What is a subplan?
A subplan is a part of the resource plan that enables the allocation of resources in greater detailamong the resource consumer groups.
56) What is a resource allocation method?
The resource allocation method dictates the specific method we choose to use to allocate resources like the CPU.The available methods of allocating the database resources are CPU method, Idle time, Execution time limit,Undo pool,Active session pool,automatic consumer group switching,cancelling SQL and terminating sessions,parallel degree limit.
57) What is a CPU method?
Oracle uses multiple levels of CPU allocation to prioritize and allocate CPU usage among the competing user sessions.It is a type of resource allocation method.
58) What is an idle time?
It is a type of resource allocation method.We can direct that a user’s session be terminated after it has been idle for a specified period of time.We can also specify that only idle sessions blocking other sessions be terminated.
59) What is an execution time limit?
It is a type of resource allocation method.We can control resource usage by setting a limit on the maximum execution time of an operation.
60) What is an undo pool?
It is a type of resource allocation method.By setting an undo pool directive,we can limit the total amount of undos that can be generated by a consumer resource group.
61) From which v$ view can you get details on SCN in an oracle database?
Oracle database has many different dynamic performance views.One interesting view is the v$database which provides information on SCN (System Change Number).Whenever a change is made to the database, the change in recorded in redo log buffer in memory. Upon a commit the information is written to online redo log files. This process usually called the database checkpoint.
When the information is written a unique number called SCN (system change number) is associated with this in online redo log file. Information can be obtained from v$database view as follows
SQL> select current_scn from v$database;

Now issue a DDL statement. DDL’s have implicit commit
SQL> alter table new modify id varchar(1000);

Table altered.

SQL> select current_scn from v$database;

62) Do we need to set oracle_home before using oracle vault?
YEs. This is mandate. Oracle Database Vault is the security solution from Oracle Corporation. It is necessary to set the value of ORACLE_HOME before enabling oracle database vault
C:Usersusername>dvca -action enable
ORACLE_HOME not set. Set ORACLE_HOME and retry
C:Usersusername>set ORACLE_HOME=location-name
C:Usersusername>dvca -action enable
63) What is ASM?
ASM is the Automated storage manager. Originally file system management was taken care of at OS level. Oracle has come up with Oracle Automatic Storage Manager that makes storage management possible using Oracle ASM instance
64) What are the type of partitions?
Partitions can be broadly classified into three major categories
1) List partition
2) Range patition
3) Hash partition
65) What is Grid Computing?
Management of the organization’s infrastructure from a single console is called as grid computing. Oracle uses Oracle Enterprise Manager for Grid computing.
66) Purpose of OEM Grid Control:
The purpose of the OEM Grid Control is to facilitate the management of entire systems, including hosts, databases, web servers, listeners and other services.
It provides us with a powerful and convenient centralized means of managing our entire infrastructure, not just our oracle databases.We can manage our systems from just about anywhere including from mobile devices
67) How to address ORA-02270: no matching unique or primary key for this column-list?
I created two tables and tried creating a foreign key constraint. I didn’t specify any key as primary key in parent table. I modified a column as primary key in parent table and created a foreign key with this primary key.This fixed the issue.
SQL> create table dept(manager_id int, manager varchar(20));
Table created.
SQL> create table employee(emp_id int, employee_name varchar2(20), salary int);
Table created.
SQL> alter table dept add constraint mgr_fk foreign key(manager_id) references employee(emp_id);
alter table dept add constraint mgr_fk foreign key(manager_id) references employee(emp_id)
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list
SQL> alter table employee modify emp_id primary key;
Table altered.
SQL> alter table dept add constraint mgr_fk foreign key(manager_id) references employee(emp_id);
Table altered.
68) How to Monitor Flashback Database?
Flashback database in oracle database 11g can be configured using steps mentioned in previous post. It is noted that flashback database can be in YES|NO status.

We can make use of v$database view to get more details on flashback database
SQL>  select flashback_on from v$database;

69) How do you maintain more than one copy of archived redo logs?
This can be done in many different ways:
1) Setup jobs to copy the archived redo logs at scheduled intervals
2) Manually copy the logs
3) Setup dataguard the oracle product that does log transport
These logs can be copied onto different disk drive within same server or different server
70) How will you change Flash Recovery Area Size Dynamically?
Flashrecovery area needs to be configured if we want to backup database using RMAN(Oracle Recovery Manager) backup/restore utility of oracle database. It is possible to change the flash recovery area size dynamically.
SQL> show parameter db_recovery_file_dest_size;
———————————— ———– ——————————
db_recovery_file_dest_size big integer 2G
SQL> alter system set db_recovery_file_dest_size = 3G;
System altered.
SQL> show parameter db_recovery_file_dest_size;
———————————— ———– ——————————
db_recovery_file_dest_size big integer 3G
71) What is the use of SQL.BSQ Script?
SQL.BSQ Script is run to create base tables,roles at the database creation time.sql.bsq creates the following roles – connect,resource,dba,select_catalog_role,delete_catalog_role,execute_catalog_role
Role is the grouping of privileges that can be granted to users.As privileges are grouped they can be granted and revoked simultaneously.
72) What is the procedure to recover Lost SYSDBA Password in Oracle Database?
There are instances when the dba loses their sysdba passwords.
1) Identify the location of the password file.
2) Make a copy of the password file
3) Remove the password file
4) Use the orapwd utility from the OS prompt and create a new password file as follows :
orapwd file=(newpassword file) password=newsysdba password entries=no of entries
5) Try logging in with new password.
73) How to address DBMS_LOB Usage Oracle Error?
When we use dbms_lob package to manipulate objects like images,video,pictures we follow the steps:
1) create a directory
2) create a anonymous pl/sql procedure or prcedure to manupulate images
We encounter a common oracle error when we execute dbms_lob
File can’t be opened/file is not existing in locationpecified.
grant read on directory directory_name to user;
grant write on directory directory_name to user;
This resolves the issue.
74) When a tablespace is in readonly mode can you create table in that tablespace?
I made the tablespace read only and tried creating table. It did throw error.I changed it back to read write and it worked fine without any issues.
SQL> alter tablespace user read only;
Tablespace altered.
SQL> alter tablespace users read write;
Tablespace altered.
SQL> create table news(name varchar2(100) ) tablespace users;
Table created.
75) Give details on Oracle Datapump Export Modes :-
Datapump in oracle is an interesting technology that has replaced traditional exp/imp starting 10.1. In latest versions Oracle Datapump features have been enhanced. Using oracle datapump we can unload the database information traditionally called database export in one of the following modes
1) Full mode
2) Tables mode
3) Schemas mode
4) Tablespaces mode
5) Transportable tablespaces mode
76) Your manager wants to make sure if the performance of the database is same before and after upgrade. What is the proactive measure you will need to take towards this?
The best and easy way is to make use of AWR compare-period reports. As a first step capture periodic baselines before the upgrade, that can be used after upgrade for comparison purpose. This baseline can be year-end, month-end, quarter-end periods depending on requirement
77) What is the minimum SYSTEM, SYSAUX,UNDOTBS1, TEMP tablespace size required for upgrading to Oracle 12c?
SYSTEM tablespace should be 1262MB (1.3GB) in size, SYSAUX needs to be 1476MB (1.4GB) minimum in size , UNDOTBS1 needs to be 400 MB in size, TEMP tablespace needs to be 60MB minimum in size for successful 12c upgrade
78) What is the disadvantage of using database upgrade assistant DBUA for upgrading your database?
The DBUA tool is the graphic tool used for upgrade purpose. Inspite of GUI convenience there are some disadvantages of using this tool as follows :
a) As this is an automated tool the control over upgrade process is limited
b) This tool can’t be used to perform OS (or) hardware migration
c) This tool needs to meet a specific release level
79) What are the disadvantages of manual upgrade?
This is error prone owing to manual intervention of DBA and involves more work. Errors needs to be checked manually. Again this upgrade needs proper release level check before upgrade. Upgrade does not automatically takes care of hardware (or) OD migration

Posted on

RMAN interview question answer preparation

We are offering rman interview questions for beginners, rman interview questions for middle level DBA, rman interview questions for experienced. Clear your interview, grab next job
1) What are all the new rman features supported in oracle 12c?
Oracle database 12c supports RMAN the Recovery manager tool to perform backup,restore and recovery. In Oracle database 12c RMAN is enhanced to support many new features that are really interesting
1) Fine-grained recovery is supported – This involves recovery of individual tables from backup. This feature is available in addition to flashback database features
recover table – Command to recover tables/partitions from RMAN backup
This is a great improvement as this provides enhanced point-in-time recovery of tables as opposed to tablespaces in earlier versions. This is primarily useful over FLASHBACK TABLE in cases wherein there is insufficient UNDO space
2) Migration of databases across different platforms is simplified
3) Cloning has been improved which enhances faster duplication of databases using fast ACTIVE duplicate mechanism
4) Multitenant database feature starting 12c can be backed up and restored using RMAN – This is a brand new implementation in 12c. Backup , restore and recovery of both pluggbale databases and container databases is fully supported
5) Faster synchronization of standby databases is supported
2) What is RMAN?
RMAN the oracle recovery manager is a tool from Oracle Corporation
RMAN helps us backup the database files including datafiles, archived redo log files, control files,spfile from within the database with the help of database server itself. This is a utility that comes for FREE as part of oracle database software
3) What files can be backed up using RMAN?
RMAN can make backup of the following files – backups of datafiles, data file image copies, control files and control file image copies, archived redo logs, the SPFILE, RMAN backup pieces
4) What is Proxy copy in RMAN?
RMAN can perform a special kind of backup called as proxy copy
Media manager is given control of copying process
Proxy copies can’t be used with disks and can be used only with tape backups
It is to be noted that media manager will have to determine if a PROXY COPY can be performed as data transfer control is given to media manager
5) What happens if media manager can not perform PROXY COPY?
If a PROXY is specified as part of BACKUP command and media manager determines that data transfer can’t be performed, RMAN will query the media manager. If there is no provision, RMAN will perform backup same as the case without PROXY option being specified
6) What happens when PROXY ONLY option is used with RMAN?
RMAN will query the media manager to determine is proxy copy can be performed. IF media manager fails to do so, RMAN backup fails
7) Why is rman preferred tool for performing oracle recovery process?
RMAN is the preferred tool for performing Oracle recovery process. RMAN selects and applies the necessary data and log files during recovery
RMAN can perform recovery at the data block level with the block media recovery feature, which dramatically reduces recovery time
RMAN provides restore optimization – a great timesaving feature that enables us to bypass data files that are ok during the recovery process
RMAN provides DUPLICATE command which lets us create copies of production database easily. RMAN DUPLICATE command is the preferred method of database duplication in a dataguard standby creation process. The duplicate command makes use of control file, data file backups and the archived logs from primary database to instantiate a standby database
8) What is the use of recovery_catalog_owner role?
Oracle RMAN(Recovery Manager) RECOVERY_CATALOG_OWNER is a predefined system role which includes the following system privileges:
RECOVERY_CATALOG_OWNER is the role granted to rman user. rman user is the default user used for creating and accessing the recovery catalog of an oracle database.
9) Give details on backup tag format in RMAN?
RMAN lets us specify tag for every backup so we can easily identify the backup.We can specify the tags when we perform are store or recovery.
Using backup tag when we perform a restore or recovery operation , we can speify the tag to identify the backup to use.Tags are very useful in identifying various backups,especially those created using incremental backup strategies.A simple example showing how we can tag a full database backup is given below:
RMAN> BACKUP TAG ‘weekly_full_database_backup’ DATABASE;
We can use the format option with backup commands to specify a location and name for backup pieces and copies. We use substitution variables to generate unique filenames. A example showing how to specify a file format , as well as location,using the FORMAT option is given below:
To make the RMAN backup job a non-interfering one we can issue DURATION option with RMAN BACKUP command. This specifies the time oracle should take to perform a backup.
We can use the FORMAT option with backup commands to specify a location and name for backup pieces and copies.We can use substitution variables – RMAN> BACKUP FORMAT ‘NAME_substitution_variables’ ARCHIVELOG LIKE ‘name’;
10) Give details on restore validate command in RMAN:-
RESTORE VALIDATE – check whether a certain object of interest is among the RMAN’s backup set.
RMAN> RESTORE TABLESPACE tablespace-name VALIDATE; – this command asks RMAN to confirm whether it can restore the tablespace from its backup sets
Success Message – “Finished Restore”
Give details on restore preview command in RMAN:-
RESTORE PREVIEW – lets us identify all the backup files necessary for a specific restore operation
The command provides detailed report of all the backups needed for the RESTORE command to succeed
11) How to PRINT script in RMAN?
RMAN scripts can be printed using PRINT SCRIPT Command
RMAN>PRINT SCRIPT script_name;
To print a global RMAN Script issue:
12) What is oracle backup aka Oracle Secure Backup?
Oracle Backup is a media manager from Oracle Corporation used for tape backups.It simplifies and automates backup and recovery operations. In Oracle 10.2 this tool has been named as Oracle Secure Backup
13) How to create recovery catalog?
To create a recovery catalog we must connect to the database in which we want to create the recovery catalog. Create a new recovery catalog owner schema usually named rman (create user rman).
Grant necessary privileges to it, then create recovery catalog-
SQL> grant connect, resource to rman;
SQL> grant recovery_catalog_owner to rman;
Connect to catalog as shown previously
RMAN>CREATE CATALOG ;- creates RMAN recovery catalog in the tablespace assigned as default tablespace for user rman
DROP catalog; – drop catalog
14) How does user-managed recovery work?
User-managed techniques can be used to restore and recover a database
Decide if we can allow users to access the database during the time of recovery
If database is not open it will be in mount state. Now open with ALTER DATABASE OPEN command
RMAN has provision to automate all of the above mentioned user-managed restore and recovery procedures
15) How to Backup Flash Recovery Area in oracle?
RMAN>show all;
RMAN>configure backup optimization on;
RMAN>backup recovery area;
RMAN-06747: at least 1 channel of tertiary storage must be allocated to execute this command
Ensure that tape drive is properly connected.
The steps mentioned above are used to backup flash recovery area.
16) How to backup flashback recovery area using RMAN?
We can backup flash recovery area with RMAN backup commands. We have to run CONFIGURE BACKUP OPTIMIZATION ON. We can backup the flash recovery area to the tape device using the backup commands
RMAN>BACKUP RECOVERY AREA – Allows us to backup every file in the flash recovery area. This will only backup the files that haven’t been backed up to the tape before
RMAN>BACKUP RECOVERY FILES – Backup every file, including files on the file system
17) What are all the tools needed for oracle database backup?
RMAN – Recovery Manager. It is a tool from Oracle corporation. Can be used in command-line mode and as GUI from OEM
Operating system utilities – cp and dd. Used to perform user-managed backups. We use a combination of OS backup commands and SQL*PLUS commands to backup the database files
18) How to connect to rman?
Simply type $rman at OS prompt. This will lead us to RMAN> prompt
RMAN commands can also be used in batch mode
We need SYSDBA privilege to connect to target databases
For connecting to recovery catalog we need special RMAN username/password. SYSDBA privilege is not needed
$rman target / – OS authentication
Recovery catalog – rman target target-database-name catalog username/password@recoverycatalog_SID
19) How to check the syntax in RMAN?
RMAN commands can be issued as individual commands in RMAN> prompt. It is also possbile to script the sequence of commands in a RMAN script.We can check the syntax of a RMAN script and a RMAN command using the parameter CHECKSYNTAX
#rman CHECKSYNTAX @/abc/rmancheck
The above command :
1) Shows message on Recovery Manager version
2) Lists the RMAN commands in the rmancheck file
3) Displays the message – ” The cmdfile has no syntax errors Recovery Manager complete” – If there are no errors
4) Displays an error message if there is an error
20) Is there a way it skip previously backed up files in RMAN?
By default backup optimization is off
We can configure backup optimization to on and skip the previously backed up files
Configure backup optimization on
21) Is there a way to override backup optimization?
Yes. We can make use of FORCE option as part of backup commands – backup database force, backup archivelog all force overrides backup optimization setting , creates backup of database files even though it happens to be identical to previous version of the file
22) What is the main advantage of fast recovery area?
IT is to be noted that flash recovery area introduced in 10g has been renamed to fast recovery area starting 11g. To keep it simple this is a location configured using certain initialization parameters like db_recovery_file_Dest, db_recovery_file_Dest_size instead of log_Archive_DEst, log_Archive_DEst_n, log_Archive_duplex_Dest etc that the RMAN is aware of. This is a disk based location created as part of disk group, ASM etc. Disk based backup and recovery is considered fastest form of recovery and this mechanism comes handy
23) Who can configure fast recovery area?
USer who is owner of oracle software, users logged in as sysdba have the privilege to configure fast recovery area
24) What are all the steps needed to configure fast recovery area?
Here are the simple steps that must be executed in sequence
1) Disable archived redo log destinations. All the destinations must be disabled
alter system set log_Archive_Dest=”;
alter system set log_Archive_Dest_!=”;
2) Log into system as sysdba
sqlplus / as sysdba
sqlplus sys/password as sysdba
3) Configure fast recovery area as follows:
alter system set db_recovery_file_Dest=’location’;
alter system set db_recovery_file_DEst_size=’value’;
The location used for fast recovery area is typically a directory in file system, ASM disk group location etc
25) How is space managed in fast recovery area?
RMAN does utilize the redundancy and retention policies, deletes unwanted obsolete backup files, image copies, archived redo logs etc from fast recovery area. Again delete obsolete command is used for this purpose
26) What is common oracle error while making use of fast recovery area?
It is to be noted that fast recovery area is totally disk based. Henceforth, there is a emergency situation associated with running out of disk space. Here is a common oracle error ORA-38760: This database instance failed to turn on flashback database
To fix this issue do one of the following:
Increase the size of fast recovery area
alter system set db_Recovery_file_dest_Size=’newvalue’;
Manually run rman obsolete, remove unnecessary files
delete obsolete;
Above command does remove the unnecessary files. Files needed for recovery are retained and not touched. In that case we need to increase the fast recovery area size
Get details on restore point, remove some permanent restore points to conserve space
select * from v$restore_point;
drop restore point restore_point_name;
Above steps will help us startup database without error
27) How to delete archived logs based on log sequence number?
delete archivelog from logseq=starting_log until logseq=ending_log
28) How does rman treat user-made backups?
RMAN generally treats user made backups as image copies
29) What are all the simple steps to recover from loss of controlfile usign rman?
We can use RMAN to recover from the loss of control file. Details on recovering from loss of control file using RMAN is given below:
2) At this step we delete the control files.If we try to start the database using STARTUP option we get the following error :
RMAN – 03002: failure of startup command at 06/02/2016 12:30
ORA – 00205 : error in identifying controlfile, check alert log for more info
We have to follow the steps below to recover from the loss of control file.
30) What are all the various options for creating multiple copies of RMAN backups of database,tablespace,datafile?
After making the image copies of the datafile, tablespace, database we can backup the image copies of the backups;
Copy the previous image copy of the database – RMAN>BACKUP AS COPY COPY OF DATABASE;
Image copy of a single tablespace – RMAN> BACKUP AS COPY TABLESPACE tablespace-name;
Create a backup set from tablespace image copy – RMAN>BACKUP AS BACKUPSET COPY OF TABLESPACE tablespace-name;

31) How to run RMAN Scripts From Operating System File ?
We can incorporate RMAN script in an Operating system file. This is called as command file
$rman TARGET/CATALOG username/password@catalog_database CMDFILE file.rcv LOG outlog.txt – OS file is used directly to run the RMAN commands and show the results in an output logfile
32) Why is RMAN needed for ASM?
RMAN is critical to ASM and is responsible for tracking ASM filenames and for deleting obsolete ASM files. ASM files can’t be copied through the OS (operating system) commands, RMAN is the preferred means of copying ASM Files
RMAN is the only method of performing backup of the database files containing ASM files
33) Give details on obtool:-
We can invoke oracle secure backup from command line interface using the command obtool
obtool: Error: this host’s identity and membership within a Oracle Secure Backup
obtool: administrative domain have not yet been established
34) What is the preferred medium to store backups infrastructure professional choice?
DBA’s frequently use tape devices for database backup.Tape devices – convenient, easy to archive, safely kept
Media Management Layer is needed if we want to use tape devices with RMAN
Oracle Corporation now offers its own media management tool called as Oracle Secure Backup , free with Oracle Server
35) How does user privilege impact RMAN backup mechanism?
In an oracle database environment privileged user is someone who has access to privileged OS account or who has been granted privileges at database level. At os level the user will be part of administrative groups, ora_dba group in case of windows environment etc. At the database level users are offered privilege with proper grants, being part of password authentication file as privileged user offers an user option to connect to database with higher privilege access. This same privilege is used by RMAN to establish privileged database session via rman utility. It is this utility command-line interface or GUI in oracle enterprise manager that allows us to perform backup, restore, recovery operations
36) Give details on backupdba OS group?
The backupdba operating system group is new in oracle database 12c. User who is part of backupdba OS group is given database privilege sysbackup. This privilege is new in oracle database 12c and allows an user to startup database, shutdown database, perform all backup and recovery operations at the oracle database level
37) How do you specify a location to write archived redo logs in an oracle database environment?
All the production, mission critical oracle databases needs to be run in archived redo log mode. Once the archiving is enabled, archived redologs are created and stored in archival destination. By default in UNIX/Linux environment $ORACLE_HOME/dbs is the default archival destination unless otherwise specified. In case of windows environment the location is going to be %ORACLE_HOME%\database folder. This can be modified by properly specifying flash recovery area (or) logarchive_Dest_n destination. This location needs to be designed and properly specified while designing the database
38) How to make backups without using RMAN?
We can make completely valid backups ourselves without use of RMAN
We can use OS commands like cp and dd
In this approach, we must keep track of all the backups and check their validity
This helps us determine the backups that we need during a recovery session
This method is referred to as user-managed backups
If the database load is simple and not onerous, we may go for user-managed backups
39) What is hot backup?
hot backup is also called as online backup.It is taken while database is open,datafiles are online.Tablespace/database are put into backup mode in case of user-managed backup.In case of RMAN there is no need to switch to backup mode.
How do you put tablespace and database in backup mode during hot/online bakup?
alter tablespace begin backup
alter database begin backup
40) How do you put tablespace/database back into normal mode from backup mode?
alter tablespace end backup
alter database end backup
41) How to create multiple copies of RMAN backup?
RMAN lets us make multiple copies of backup sets with a BACKUP command. We can send produce upto four copies of each backup piece in a backup set within a single BACKUP command
We can’t produce multiple copies of RMAN image copies
Multiple copies of BACKUP sets can be produced as follows : Use the CONFIGURE..BACKUP COPIES option, Use SET BACKUP COPIES in a RUN block, Use COPIES option in the BACKUP Command
42) What is cold backup?
Backup of one or more datafiles made while database is closed is cold backup.It can be consistent or inconsistent depending on if few or all datafiles are taken offline.It is also called as whole database backup.
43) Give details on oracle recovery filetypes:-
Transient recovery files can be automatically deleted
Full datafile backups, incremental backups, datafile copies, control file (autobackup), archived redo logs, block change tracking files, flashback logs
Oracle places mirrored copies of certain database files in the flash recovery area
Mirrored copies of online redo log files, mirrored copies of current control files are permanent files and should never be deleted
44) What is a fuzzy file?
When data files are in backup mode chances are that there can be a datafile whose SCN(system change number) is greater than or equal to the checkpoint SCN.This file needs recovery.
45) How to switch from init.ora file to spfile?
create spfile from pfile;/create spfile=[spfile_name] from pfile=[pfile_name];
46) How do you create pfile from spfile?
create pfile from spfile;/create pfile=[pfile_name] from spfile=[spfile_name];
47) What is a log switch?
The point at which ORACLE ends writing to one online redo log file and begins writing to another is called a log switch.
48) What is Testing Oracle Database Backups – Avoid Recovery Disaster?
Defective backup strategies lead to frustration during recovery of a production database.It is due to inadequate and in some cases non-existent planning and testing of the backup and recovery strategy
To avoid this, DBA must have a well established and tested backup and disaster recovery plan
Validate the backups – make sure that they are all readable
Check for corrupted-blocks in backed-up files, to avoid recovery problems due to bad files
We have features in user-managed backup and RMAN to avoid this problem
RMAN comes as an integral part of Oracle and doesnt need seperate installation,RMAN can be easily invoked using command-line client rman
RMAN in Oracle database 12c can perform backup of following files
1) Datafiles – These are physical files containing database data
2) Controlfiles – The heartbeat file that stores details on database structures, structural changes, backup details in case of nocatalog, transactions details to name a few
3) Archived redo logs – All the archives created in archive folder since last database startup can be backedup
4) SPFILE – The binary file needed for Oracle instance startup and interaction of instance with database. This is extermely crucial as its loss will bring entire system down
49) Basic Database Configuration Needed For Database Backup:
Whenever there is a question from customer/client what is the basic database backup tool from Oracle corporation and what is DB level configuration it is easy to mention that basic requirement
Oracle database must be in archivelog mode to make use of Oracle database backup tool RMAN
To determine if database is in archivelog mode issue the following command
archive log all
50) RMAN-00554 initialization of internal recovery manager package failed. What to do?
When we try connecting to the rman executable using the rman target / we might get this error.To fix this error invoke rman as rman target /@ORACLE_SID
51) Give details on RMAN special terminologies:-
RMAN uses some special terminology. To use RMAN effectively, we need a good understanding of the terms.
Backup Piece :
A backup piece is an operating system file containing the backup of a data file.,control file, or archived redo log file. This backup information is stored in an RMAN-specific format.
Backup Set :
A backup set is a logical set that consists of one or more RMAN backup pieces(the default is one backup piece per backup set). We can create a backup set on a disk or tape.If we backup a database, data file, tablespace,or archive log ,RMAN groups the complete set of relevant backup pieces into one backup set.
When we issue the backup command, RMAN creates the backup set to hold the output.
By default RMAN creates a backup set when we use a backup command, whether we are copying to disk or tape (using a media manager).
Note : Backup set is a file or a set of files in proprietor format that only RMAN can understand. So, only RMAN is able to use the backup sets to recover the database.
52) What is a user-managed recovery and how to perform one?
There are circumstances that demand the restore and recovery of the oracle database as a whole or portion of it say a datafile. Such procedures and practices are called the user-managed recovery procedures. Here are the simple steps needed to perform user-managed recovery
1) Identify the database files that need to be restored
2) Place database in appropriate mode – mount or open
3) Restore database files from media (tape or disk) to appropriate location
4) Recover database using RECOVER command
5) RECOVER command applies archived redo logs to the database files – if needed
53) What to do when we encounter ORA-19550: cannot use backup/restore functions while using dispatcher?
To fix this issue create a new tns alias in tnsnames.ora file. This should be created with SERVER=DEDICATED option. This makes sure that rman connectivity does not make use of dispatchers. The error will be fixed
54) Give details on RMAN validate command :-
VALIDATE BACKUPSET command – validate backup sets before we use them from a recovery
If there is an error – RMAN:06159 – error while looking up backup set while issuing
RMAN>validate backupset 2; – indicates that backup can’t be found by RMAN
CROSSCHECK – Make sure that backup is present and usable
55) How to determine the location of RMAN backups at OS level?
Actual location on the disk where the backup is stored can be determined in one of the following ways
We can use FORMAT parameter, this location will override any location specified for the flash recovery area – RMAN>BACKUP DATABASE FORMAT ‘format’;
If we don’t specify the FORMAT parameter, flash recovery area is the location chosen – RMAN>BACKUP DATABASE;
If we haven’t configured flash recovery area as well as don’t specify FORMAT command, RMAN will store backup in an OS directory
56) How does oracle RMAN perform Recovery?
RMAN restores data files from backups and applies the necessary archived redo logs to bring the database up to date
RMAN looks into its recovery catalog and knows which files it needs and thus simplifies the error that may occur during user-managed recovery.RMAN can check if the backups are valid before performing a recovery
57) What are oracle database recovery scenarios?
Database can be unavailable due to system crash, network failure, media failure, natural disaster
Solidly tested backups and regular recovery drills are keys to successful recovery.
Practicing recovery techniques is essential for successful recovery.
Latest technique – Flashback recovery
58) You are restoring controlfile from backup. You are not restoring datafile as part of this process. How will you open the database?
The database should be opened with alter database open resetlogs command. Inspite of restore of datafiles this process is considered a media recovery because the backup controlfile will have SCN that does not synchronize with datafile SCN and online redo logfile SCN. Henceforth, resetlogs option should be used
59) How will you determine if a controlfile is a normal one or the one from backup?
This can be easily determine by querying v$database controlfile_type column

60) You have enabled flash recovery area. You have enabled autobackup of controlfile. Can you get information on DBID from the controlfile piece in FRA?
Nope. It is not possible to determine DBID value. It is because if controlfile autobackup happens at FRA the file is created based on oracle managed file naming format
61) You forgot to record details on DBID. Your database has crashed now. You dont have FRA. You are trying to restore controlfile from autoback and get the error RMAN-06495 : must explicitly specify DBID with SET DBID command. What will you do?
As FRA is not set DBID value can be determined from controlfile naming convention which defaults to C-xxxxxxxxxx-yyyymmdd-qq format. The 10x’s map to value of DBID
IF the format is explicitly specified the value may not exist. In this case look at alert.log in case you have earlier recorded using dbms_System package. IF you have logfile showing rman output earlier the value will be present there
IF none of the above options work there is a way to dump dbid value from any of the existing datafile, archived redolog file, online redolog file in nomount mode. Only requirement is that these files must be present physically
startup nomount;
alter system dump datafile ‘filepath’ block min 1 block max 10;
From the above command it is evident that DBID is a 10 digit value that appears in first 10 blocks of datafile
If you choose to dump the DBID from online redolog file (or) archived redolog file use command:
alter system dump logfile ‘path’ ;
In both of the above mentioned scenarios DBID is recorded in tracefiles that are generated in diagnostic tracefile destination
65) A backup is taking too long time. How will you decide if the backup is progressing fine (or) has been terminated?
As a DBA the data dictionary views come handy while monitoring long jobs like background batch jobs, RMAN backup jobs, datapump jobs etc. For RMAN backup make use of v$session_longops view
66) You have a RMAN recovery catalog in place. The recovery catalog database is in a separate database. Can you shutdown recovery catalog database using RMAN prompt rather than logging into that database the way we do for TARGET databases?
For performing startup and shutdown of recovery catalog databases either log into them directly perform shutdown , from RMAN connect to recovery catalog databases specified as target database and then issue the shutdown and startup commands
67) You want to know if you your RMAN command is free of syntax errors. How will you accomplish this?
By making use of RMAN checksyntax followed by rman commands we can verify and validate RMAN commands
For set of rman commands specified from within command files this checksyntax can be used
68) You have configured recovery catalog to store the backup metadata information in RMAN environment. IF control file useless in this case?
Nope. Inspite of recovery catalog configuration in place controlfile still records details of RMAN backups
69) The recovery catalog database was down and was not noticed. Suddenly DBA notices this and fixes the recovery catalog database. Is the RMAN backup information lost in this case?
Nope. Inspite of recovery catalog configuration in-place backup information is still stored in controlfile of target database by default. Once the recovery catalog is up and running connect to recovery catalog database and target database. PErform resynchronization by using the command:
resync catalog
After this process completes we see a message that says full resync complete
70) How will you know what files are needed for media recovery?
Look at v$recover_file view to see the list of files that are needed for media recovery
71) You have configured controlfile autobackup to on in your environment. What is the benefit of this configuration?
Once the controlfile autobackup is set to on at the end of every RMAN backup command, the controlfile is backed up automatically. This backup controlfile gets created at trace folder in location where alert_sid.log file in located
72) From which view can you get details on the messages reported by RMAN?
The v$rman_output view an in-memory view that is transient upon database restart isused to get details on messages reported by RMAN
73) How to convert oracle rman stored script to textfile script
RMAN offers two kinds of scripts – stored scripts kept in recovery catalog, text scripts kept in regular text files
Stored scripts have an advantage – any user who logs into RMAN can access them easily
We can change scripts from stored scripts to textfile and viceversa
RMAN> PRINT script script_name to file ‘filename’; – RMAN command can send the contents of a stored script to a text file
74) Give some details on substitution variable usage in RMAN:
RMAN comes with powerful scripting lanuage which can be used in addition to manual commands
Common backup tasks can be encapsulated easily using scripting
We can store scripts in recovery catalog or as text files
When we need large number of configuration parameters for a particular backup, we do it easily using scripting.RMAN scripting has been enhanced and it is now possible to create a RMAN script that is more dynamic. The substitution variable & can be made use of and values are passed through shell scripting
Lets say we create a shell script and assign value to these variables.
Now it is possible to pass these values to this rman script as follows from within the shell script
rman @rmanscript using $variable1 $variable2
75) Give some details on whole and partial database backups:-
We can backup entire database, or a part of it such as a tablespace or a datafile
Whole Database backup – Most commonly performed backup. It consists of all the datafiles, control file. Without the control file Oracle will not open the database, so we need the latest backup of the control file along with all the datafile backups for recovery. We can perform whole database backup in archivelog or noarchivelog mode
Partial Database backup – It can only be done if database is run in archivelog mode. Is the database is in noarchivelog mode, tablespaces and files must be in read-only mode
76) What is the purpose of restore validate command?
RESTORE..VALIDATE Command is used to check whether a certain job of interest is among RMAN backup sets.
This command will ask for confirmation as if it can restore the tablespace specified.On completion of restore we get “Finished Restore” message.
77) How to restart rman backup?
If the RMAN backup fails before it completes we can resume it from the point of failure., without needing to redo the entire backup. Let us say we perform a daily backup and the last backup failed midway. We can issue the following command after the backup failure:
BACKUP DATABASE NOT BACKED UP SINCE TIME command will backup only those files that haven’t been backed up before.
78) How to register a database in oracle RMAN?
For RMAN to perform its job, we need to register the target database we want to backup or recover.Database is enrolled in the recovery catalog
To make sure that datafiles of target database are registered to recovery catalog use
79) What are different types of database failure?
DBA’s important function is to safeguard enterprise data and enable users to access it with few disruptions.System Failures – Hardware-related failures. Disk drive controller may fail, disk head could be defective, system peripherals can fail, CPU, memory, power-failure may have problem, OS errors. RAC is a solution
Data Centre Disasters – Range from tornado to fire to a terrorist attack. Good solution is Dataguard. During disaster recovery session, turn the standby database into primary database with no disruption and no data loss
Human Error – People may make mistakes. DBA and system administrators can make critical mistakes
Media Failures – Damage to disks that prevent them from being read and written. Two factors are critical when disaster occurs – amount of data that becomes unavailable and needs to be replaced from backups, amount of time it might take to replace data
80) How to collect trace from RMAN backup job?
At times it becomes mandatory to understand the reason behind an RMAN issue. This includes RMAN performance slowness, indefinite backup, backups taking too long etc. AS RMAN is a product from Oracle corporation, it is developed using PL/SQL and hence RMAN trace files will be of great help as is case with sql traces
Follow simple steps to collect trace output from RMAN
rman target / log rman.log trace rman.trc debug
sql “alter session ”10046 set name context forever, level 12””;
Say backup validate datafile 1;
The above command will create trace output under diag_dest in case of 11g and can be analysed using tkprof
81) Give details on RMAN duplication types?
It becomes often mandatory to make a copy of the existing production database to create a test system for which we can make use of traditional cold backup wherein we shutdown the Oracle instance, copy the database onto new machine.
As this traditional process involves downtime, this has not been the best method always. As a good alternative Oracle has come up with its unique methods that makes use of popular Oracle backup solution RMAN
RMAN has an inherent DUPLICATE feature that makes this copy and DB recreation process easy and simple without any downtime
There are two ways to perform DUPLICATE
1) Active Database Duplication – Involving duplication of database from active production database
2) Backup-based Duplication – Involves copying backup onto destination host(server also called auxillary host) and recreating duplicate database there – Note that auxillary (or) duplicate database can be created in same host that will make use of auxillary instance
82) What is the meaning of RMAN-06171: not connected to target database?
User connects to a Catalog database and tries backing up database by issuing backup database command from catalog database.This issues the following error:
C:Documents and SettingsDBA>rman catalog rman/rman
Recovery Manager: Release – Production
Copyright (c) 1995, 2004, Oracle. All rights reserved.
connected to recovery catalog database
RMAN> backup database ;
Starting backup at=======RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============RMAN-00571: ===========================================================RMAN-03002: failure of backup command at 09/05/2008 11:29:09RMAN-06171: not connected to target database
To fix it use rman catalog rman/rman@oracle_sid
83) Give details on executing sql commands in RMAN?
We can execute few sql statements from RMAN prompt.We need to specify sql commands withinsingle quotes.
We cannot use select command :
RMAN> sql “create table rman_test_double(no int)”;sql “create table rman_test_double(no int)”;sql statement: create table rman_test_double(no int)
SQL> select distinct table_name from dba_tables where table_name like ‘RMAN%TE%’;
RMAN> sql “select sysdate from dual”;
sql “select sysdate from dual”;
sql statement: select sysdate from dual
Note : We can use single as well as double quotes.
We can create database objects from within RMAN prompts. SELECT commands don’t fetch the results when used from RMAN prompt
84) What are the most salient features of RMAN backup, restore and recovery solution?
Following are some of the salient features:
Commands are easy, flexible, and very simple. Using these set of commands we can easily perform backup, restore and recovery
With RMAN the backup process is well organized and information log is maintained either in controlfile of target database or in recovery catalog database if one is in place
Optimal backup performance utilizing parallelism feature of RMAN
Backups of different types including full database backup, incremental backups performing delta change backups is possible with RMAN
Disaster recovery is possible with RMAN backups that can be restored and recovered
RMAN duplicate option enabled setting up advanced business continuity solutions like dataguard
85) What is a backup pieve file in RMAN?
The binary backup files created by RMAN are backup files
Based on format configured more than one file is created in physical disk drive that has been often configured as backup destination
20) Why is a backup piece datafile smaller in sie than original datafiles?
In RMAN backups only used blocks in datafiles are backed up. Henceforth, even in case of full backup only used data blocks are backed up and the size of file is often smaller than original database datafile size
86) What are the uses of flash recovery area and how is it related to RMAN?
IT is an optional disk area that can be used to store RMAN backups, multiplexed copies of online redo log files, control files
87) Is v$datafile same as v$datafile_header view?
Nope. V$dtafile_header view uses information from physical datafile on disk to get details on problems. Information from error and recover columns report potential problems
v$datafile view uses information from control files
88) You are looking at v$datafile_header view and see value of yes as well as null in recover colum. What does this mean?
Error columns reports description of problems. Recover column shows value yes or null and this is an indication of problem with associated datafile that can be located from datafile number that is obtained from file# column in this v$datafile_header view
89) How to fix RMAN-05020: cannot specify AUXILIARY DESTINATION option for normal recovery?
I tried performing tablespace point in time recovery popularly called as TSPITR today using oracle RMAN (recovery manager) in Oracle database. I tried issuing command without until time ‘timestamp’ and found the following error
RMAN> recover tablespace pointintime auxiliary destination ‘destination_location’;
Starting recover at
using channel ORA_DISK_1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at
RMAN-05020: cannot specify AUXILIARY DESTINATION option for normal recovery
This is an error in syntax. It needs to be UNTIL TIME ‘timestamp’
RMAN> recover tablespace pitr until time ‘mm-dd-yy hh24:mi:ss’ auxiliary destination ‘destination_location’;
This will fix the issue.
90) How will you Change Value of RMAN Compression Algorithm in an oracle database?
Oracle RMAN offers to compress the backups created by them. Compression algorithms like bzip2, zlib are being used by rman to perform compression of backups

91) How to look for compression algorithm being made use of?
It is possible to look at v$rman_compression_algorithm view and determine which algorithm RMAN uses for compressing backup. By default it is bzip2It is possible to configure a different compression algorithm as follows :
configure compression algorithm ‘zlib’;
92) How does degree of parallelism work with channel configuration in RMAN?
Oracle rman utility offers provision to configure channels before initiating database backup. Channel is a mount point onto which files are written to as part of database backup. RMAN offers configure command to configure channels. PARALLELISM is a configuration possible with RMAN utility
Here is the simple command to configure parallelism for device type disks in rman
configure device type disk parallelism 2;
The many different types of devices can be disk, sbt that are tapes used for performing backups etc
Next, we can configure two channels as follows:
configure channel 1 device type disk format ‘location1’;
configure channel 2 device type disk format ‘location2’;
In the above command the location1 and location2 correspond to two different mount points respectively
It should be noted that always degree of parallelism should match the channels configured
93) What happens when they are different?
Say the degree of parallelism is 3 and only two channels are configured. In this case 3 channels are opened by RMAN. The third channel location is going to be flash recovery area and other two are configured mount points as mentioned above. In this case you have sufficient disk space available in flash recovery area
94) How to protect rman from exposing password?
Database backup is a routine task that does happen on hourly, daily, weekly, monthly basis in the form of incremental backups, differential backups, full backups etc. As such this is a potential task that can be hacked to get details on sys user password that is crucial for business. A person with password detail can easily log onto database system and steal the data. This is a heavy security threat in businesses dealing with customer sensitive information like healthcare data, financial data, banking services, credit card services etc. This is an audit item to be kept under consideration while designing, implementing and managing databases
Here is a simple measure one can take to avoid password exposure while performing RMAN commands including backups, restore, recovery etc
1) Log into rman prompt with username only
rman target sys@oracle_sid
The above command will prompt for password. Once we enter this information this is not exposed to the reader. Make use of this approach while designing automation scripts used for RMAN backups
2) When an rman command is being run, ps -ef unix command will pull details on password. In addition to this when we run a rman script password detail is evident. To avoid this we can run the rman script with @ suffix
rman @script.rman
3) Make use of connect target inside rman script. Rather than specifying password command-line, use connect target username/password@ORACLE_SID as part of script. Say, script.rman is the script to backup database. Use the commands within script
connect target username/password@ORACLE_SID
backup database;
The above script is called as run as rman @script.rman. This will replace username/password with simple asterisk *
Make sure RMAN is used safe and password is unexposed
95) What is use of parameter DBMS_BLOCK_CHECKING?
DBMS_BLOCK_CHECKING parameter when turned on at different levels say low, medium,full offers user tablespace information check to make sure data blocks are logically self-consistent
96) You have RMAN cataog database configured. From trace files you see some corrupt blocks in RMAN catalog database. What is the way to recover corrupt blocks in a RMAN catalog database?
The best method is to make use of block media recovery feature of RMAN as there are few blocks recorded in trace file and alert.log file that has details on error ORA-01578. Make note of datafile number, block number to perform block media recovery as follows :
BLOCKRECOVER DATAFILE datafile_number BLOCK block_number_1 DATAFILE datafile_number BLOCK block_number_n;
97) Which tablespace does RMAN make use of?
RMAN makes use of SYSAUX tablespace the tablespace that comes default with latest versions from 10g, 11g, 12c all the way upto latest versions. This offloads the performance issue that does occur while making use of SYS tablespace in earlier versions
98) Your environment has one container database hosting multiple pluggable databases. You want to make backup of one specific pluggable database say PDBTEST. Which RMAN command will you make use of to accomplish this?
BACKUP PLUGGABLE DATABASE PDBTEST while connected to the root container
BACKUP DATABASE if you are directly connected to PDBTEST container
99) You have enabled unified auditing option by default in your environment. Which oracle database component is audited by default?
Oracle recovery manager the RMAN is audited if unified auditing is enabled by default
100) What is the use of FROM clause in RMAN?
The FROM clause supports synchronization of a standby database with the primary database in a dataguard environment
This clause is used for standby database setup as this supports PULL-based active database duplication
This clause supports file recovery over the network in a dataguard environment
101) You have been asked to backup the datafiles that belong to the root container and all pluggable databases in your multi-tenant environment. Which command will you make use of for this purpose?
Connect to the container database (root container) and issue the following command from RMAN prompt :
102) What is the reason behind RMAN-06167: already connected?
This issue occurs when we have RMAN connection to a specific target database and we try to connect again to that same database
We connect to a target database and we try reconnecting to the same database. We will get the following error:
From operating system prompt, type rman to launch and connect to RMAN client
RMAN> connect target username/pass@oracle_sid
connected to target database: oracle_sid (DBID=192324234813)
RMAN> connect target username/password@oracle_sid
RMAN-06167: already connected
103) How can you fix RMAN-06167 error?
To fix the error we need to exit RMAN and connect to this afresh as there is no disconnect command available as part of RMAN
104) You have scheduled an RMAN backup. It fails before completion. Do you need to start this from beginning?
No need. If the RMAN backup fails before it completes we can resume it from the point of failure, without needing to redo the entire backup. Let us say we perform a daily backup and the last backup failed midway. We can issue the following command after the backup failure:
BACKUP DATABASE NOT BACKED UP SINCE TIME command will backup only those files that haven’t been backed up before
105) What is new in oracle compression? What has this got to do with backups?
RMAN in oracle database in latest version uses ZLIB algorithm. In previous versions bzip2 was the algorithm used for compression
106) How do you enable compression?
Compression can be used as an option with the RMAN command CONFIGURE
107) What are the two formats in which control file can be backed up?
Controlfile is the crucial file of oracle database as it stores information on database structures. Controlfile the crucial file that controls all physical structural changes, RMAN backups etc can be backedup in one of the following two formatsControlfile is automatically backed up once there is a change in structure of database, at the end of every backup. Control file is critical for recovery, as it contains crucial information like database checkpoints and the data file header checkpoints for the data files.A recovery is much harder when we lose all copies of our control file
We also need to create a new control file when we want to change the name of a database, clone a database in a different location, increase the maximum number of files we specified when we first created the control file
In RMAN if CONTROLFILE AUTOBACKUP is set to ON, this automatically back up controlfile and spfile at end of every backup.
We can manually backup controlfile while database is operational with the mandate that database runs in archived log mode
1) Binary Format
2) Human readable format that can be backedup by running some commands in sql prompt
Backup controlfile from sql prompt in binary format
alter database backup controlfile to ‘/filepath’;
alter database backup controlfile to ‘/filepath/’ reuse;
Backup controlfile in human readable format from sql prompt
alter database backup controlfile to trace ;
alter database backup controlfile to trace as ‘filepath’;
alter database backup controlfile to trace as ‘filepath’ reuse;
The controlfile backedup in human readable format contains database structure in human readable form. These are the set of commands that got executed at the time of database creation. By removing the comments this file can be directly used if needed.In case of DB failure, this can be copied to needed location and it is easy to bring up database
107) How will you backup controlfile using RMAN?
The control file is critical for database operation. The control file stores details on structure of oracle database, database checkpoints and the data file header checkpoints for the data file, database backup information that is necessary for performing restore and recovery. Considering the criticality it is always recommended to multiplex controlfiles. When we lose all copies of our control file we need to recover controlfiles. Control file backups can be done using oracle supplied utility RMAN:
RMAN> BACKUP CONTROLFILE; # This command produces binary copy of controlfile
Control file backup is needed immediately after we perform the following operations – create or drop a tablespace, add or rename a datafile, add, rename, drop an online redo log group or member
108) When is it needed to backup controlfile?
It’s good practice to backup controlfile on a regular basis. We should immediately backup control file after we perform any of the following operations:
1) Create or drop a tablespace
2) Add or rename a datafile
3) Add,rename,drop an online redolog group or member
109) Give details on Oracle RMAN Controlfile Automatic Backup :-
It is possible to make the Oralce RMAN (Recovery Manager) tool backup the control file automatically. This simple command helps us accomplish this using RMAN tool
configure controlfile autobackup on;
The configure command will configure the parameters at RMAN level. Controlfile – the name/type of file to be backed up. Autobackup on – Will perform automatic backup of this file.
It is interesting to note that the CONFIGURE command creates persistent settings across rman sessions.
To determine the current rman configuration make use of the command
show all;
110) Can we perform backup of tablespace when it is in readonly more?
I made the tablespace read only and tried performing backup in a table in read only mode. Make sure database is in read only mode as follows:
SQL > select file#, fuzzy, name from v$datafile_header;
Should return YES for fuzzy for datafiles belonging to the tablespaces that are in READ WRITE mode. If the column fuzzy is not set for datafiles then the tablespace is in READ ONLY mode
Perform backup of tablespace and found that it worked fine without any issues.
RMAN> backup tablespace users;
Starting backup at 04-JAN-19
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=1081 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
Finished backup at 04-JAN-19
Starting Control File and SPFILE Autobackup at 04-JAN-19
piece handle=C:\oracle\BACKUPCTL_C-1951524609-20190104-01.BAK comment=NONE
Finished Control File and SPFILE Autobackup at 04-JAN-19
111) You try to register database and see error RMAN-06429. What causes this error?
RMAN backup has some core procedure that come as part of SYS schema. They are essentially important for backup and restore operations of RMAN utility
1) DBMS_RCVMAN – This package is used to access tables in the control file and pass the information to RMAN. RMAN uses this information to build backup and restore operations. This package also sets time operators, verify checkpoint information in datafile headers ,locations and size check, information on node affinity in RAC, disk affinity
2) DBMS_BACKUP_RESTORE – This also accesses controlfile and verifies all requisite information
DBMS_RCVCAT and DBMS_RCVMAN are RMAN catalog packages. In case of typical Oracle 12c upgrade, the target database is in Oracle database 12c and catalog database is in oracle database 11g version. When there is an attempt to register the target database onto 11g catalog database following error is thrown
RMAN-06429: RCVCAT database is not compatible with this version of RMAN
To fix thee issue,
1) Upgrade the catalog database
upgrade catalog;
2) Try to register the target database once again
register database;
This properly registers the database and starts proper resync of recovery catalog

Posted on

Oracle cloud interview Questions

Oracle cloud interview Questions will help you prepare and clear upcoming interview questions with oracle cloud
1) There are many different cloud services. Why should I choose oracle cloud over others?
Oracle database is the popular relational database company that has been in existence so long. Oracle cloud offers database as a service. With oracle databases supporting enterprise databases, this is a cloud service that can support databases that are used for testing, development, all the way upto live production databases. Oracle cloud offers high availability, scalability options offering business continuity, time save and lot more advantages. A cloud service from a database company to keep it simple
2) You are opting for standard package. Do you need to pay extra for TDE?
No. All packages including standard package, enterprise package, high performance package, extreme performance package include Oracle Database Transparent Data Encryption
3) Can you implement RAC in oracle cloud?
Yes. Enterprise edition extreme performance RAC under bring your own license BYOL model does offer this provision. Pricing can be referred at oracle cloud official website
4) What is included in standard package?
Under standard package both in Universal credit services as well as bring your own license BYOL model oracle database standard edition 2 is included. In case you have opted to make use of BYOL model standard package this can be standard edition one, or oracle database standard edition 2
5) You have chosen to make a purchase under universal credit services standard package. What cost do you need to pay upfront?
As this a pay as you go service in case of universal credit services you dont need to pay any upfront costs. Based on usage monthly invoice will be generated. This is same as in AWS cloud
6) You have chosen to BYOL standard package. What is upfront cost?
This is a pay as you go service and no monthly upfront cost needed. Only service usage is metered and charged accordingly
7) In oracle cloud platform as service offering what database management services are included?
Following database management services are included as part of Oracle cloud platform as a service PaaS :
Database backup
Big Data
Big Data Cloud
Event Hub
Autonomous NoSQL Database Cloud
Data hub
Autonomous Data Warehouse Cloud
8) What are the many different database deployment model available with oracle cloud?
Following are the many different types of database deployment models that come as part of oracle cloud:
Oracle database cloud service
Oracle database cloud service bare metal
Oracle Database Exadata Cloud Service
Oracle Database Exadata Cloud at Customer
Oracle Database Exadata Express Cloud Service – Managed
Oracle Database Schema Cloud Service – Managed
9) Can I try oracle cloud for free?
Yes. Oracle cloud offers $300 in free credits. You can make use of this credit for building your test, development, production databases, compute options, containers, IoT, bigdata, API, chatbots, integrations and lots more that are valid for 30 days
Create a free account and try these features for free
10) Where can you store your oracle database backup in oracle cloud?
Oracle cloud object storage solution that is reliable and scalable. As of information from oracle website this is a 8000TB storage that is used for storing and accessing the data in databases that are ever growing. This is storage used for storing oracle database backup data
11) What are the unique features of oracle cloud database backup management offering?
This is a reliable and scalable object data storage from oracle cloud. Some salient features includes:
Security – This solution comes with enterprise-grade data protection policies. The privacy policies are in enterprise grade as well. Oracle being the most popular enterprise database solution this cloud offerings from oracle are designed with this in mind
Reliability – Redundancy policies ensure high availability of data
Scalability – Oracle cloud is a pay as you go model solution wherein you can choose to purchase universal credits, BYOL etc. Based on capacity and growing demands storage hardware is allocated making this a scalable solution
Simplification by using existing RMAN for backups – Even with cloud oracle is still making use of RMAN backup for performing the database backups. This makes backup, restore and recovery operations transparent using RMAN
12) Is my backup safe and secure while being stored in oracle cloud? Explain?
Yes. Oracle database backup service offering encrypts backups at the source, this backup is securely transferred to the cloud and stored in cloud
13) What is the difference between normal database and Oracle database cloud service?
Oracle database cloud service is same as single-instance oracle database except for that database is deployed in cloud and computing resources including storage, power etc is provided by Oracle
14) In Oracle cloud is database maintenance and management to be done only using cloud tools?
Nope. Normal database tools can be made use of for maintenance and management purposes. However, oracle cloud tools can be optionally used
15) What are the two service levels available with oracle database cloud service?
Oracle Database Cloud Service Virtual image wherein customer is responsible for installing software, maintenance of software. Customer has root privilege and full database administrative privilege
Oracle Database Cloud Service wherein database deployment is easy using custom options provided online. Oracle database cloud service can perform automated backups. Customer is responsible for setting up maintenance operations, recovery operation setup in the event of failure
16) Which component of Oracle cloud provides service console and REST API?
Platform Service Manager the PSM component of Oracle cloud is responsible for this. This component is the one used in Oracle golden gate and Oracle Java cloud service as well
17) How does PSM interact with compute nodes to perform predefined cloud service actions like backup, patching?
PSM uses secure shell the SSH in port 22 of compute nodes. Compute nodes hosts databases which as database deployments in oracle cloud terms. These cations can be initiated over web service console that uses PSM or REST API
18) How is internal communication between PSM and Oracle cloud compute nodes established?
SSH key-value pairs are used for this communication in port 22. This key value pair is specific to each and every database deployment and used for internal communication purposes. This SSH is internal to Oracle and not accessible. If there is an issue here PSM communication with compute node fails
19) Can PSM communication with compute nodes be audited?
Though PSM is an oracle component the actions of PSM during its communication with compute node can be logged and audited
20) Who has access to the SSH keys used for PSM communication with compute nodes?
Only owner of that project has this access. For security purposes even oracle support and operations is not granted this access unless explicitly shared by customers for troubleshooting purposes
21) What are the database related public cloud offerings in oracle cloud?
Oracle public cloud offering comes with the following three database related services in the public cloud. They are as folows :
a) Schema as a service
b) Database as a service – Popularly called DBaaS this public cloud is offered as both Infrastructure as a service IaaS and Plarform as a Service PaaS
c) Oracle database cloud exadata service
22) What does provisioning an oracle database mean?
Provisioning an oracle database means creating an oracle database and making it available to the end users
23) How will you provision an oracle database?
As an oracle dba (or) cloud dba we need to collect requirements from customers (or) project owners and create it as per specific requirements. In case your project needs a clone of production database immediately for testing (or) UAT purpose say for example this can be done as per standard specification requirements often based on a template
24) Give details on some oracle cloud terminologies :-
Service levels
Virtual image
Cloud storage
25) In oracle cloud infrastructure what is the default encryption used in file storage service?
AES-128 encryption algorithm is being used as default encryption in an oracle cloud infrastructure
26) In Oracle cloud infrastructure which among the following are encrypted at rest rather than in transit?
Data as well as Metadata
27) Is UpdateZoneRecord a valid REST API operation?
Yes. It is a valid REST API operation
28) Is AddZone a valid REST API operation?
Yes. It is a valid REST API operation
29) Give some invalid REST API operations for DNS Zone in OCI :-
a) ListZones
b) GetZone
c) CreateZone
d) UpdateZone
e) DeleteZone
30) Where are IAM resources the users and groups created?
They are created globally
31) You want to point a hostname to an IPv4 address. Which DNS resource record type will you make use of to accomplish this?
A record in DNS can be used for this purpose
32) In TCP level healthcheck you attempt to make a TCP connection with the backend servers . How will you validate the response?
Based on the connection status
33) What is extension of Terraform HCL configuration files?
These files come with extension .tf
34) What configuration formats are supported by terraform?
Hashicorp Configuration Language Format HCL,JSON. Oracle cloud infrastructure can be described using HCL in Terraform configuraiton files
35) How will you achieve high availability in Oracle Cloud Infrastructure?
Attach block volume from availability domain 1 to a compute instance in availability domain 2 or viceversa. Distribute application servers across all availability domains within a region
36) What are the different types of compute instances that come as part of Oracle Cloud infrastructure?
Bare Metal, Virtual Machine
37) What are the components of backend set of a load balancer?
a) Load balancing policy
b) list of backend servers
c) health check policy
d) SSL handling
e) session persistence configuration
38) What are the resource record types supported by Oracle Cloud Infrastructure DNS service?
d) MX
e) NS
39) To tell Terraform what data is important outputs are the way to go. When is this data outputted?
When apply is called
40) When a supply, variables, build are called is the data output created in Terraform?
41) What is the default output at the end of a terraform apply operation?
Statistics including what was added, changed, destroyed, values of output becomes available
42) Which two relevant storage tiers are available in the object storage service?
Standard storage,Enhanced Performance storage
43) Does boot volumes allow you to create significantly faster custom images of running VMs without having to reboot?
Yes they do
44) Are the backend set components in load balancer physical (or) logical?
The load balancer components in an oracle cloud infrastructure are logical in nature
45) In Oracle Cloud infrastructure what is default behavior of security list?
It uses stateful rules by default
46) What are the many different formats of terraform configuration files?
Terraform domain-specific language format HCL, Machine-readable JSON format files
47) How will you parameterize Terraform configurations ?
Using input variables
48) You want to launch an instance in Oracle Cloud Infrastructure. What are all the required parameters?
Subnet, Availability domain, Virtual cloud network, instance shape, image OS
49) Which resource is tied to availability zone on Oracle Cloud Infrastructure?
50) What is extension of JSON format machine-readable Terraform configuration files?
These files are of format .tf.json
51) What are valid REST API operations for DNS Zone in OCI?
a) ListZones
b) GetZone
c) CreateZone
d) UpdateZone
e) DeleteZone
52) What actions are controlled by Oracle cloud infrastructure layer?
a) creating file systems
c) listing file systems
d) associating file systems
e) mount targets
53) How many subnets are needed to create public load balancer?
Two subnets are needed, each in different availability domain within a single region
54) In an Autonomous transaction processing what SQL operations are not available?
Alter profile
55) In an Autonomous transaction processing what SQL operations are available?
a) alter pluggable database with datafile autoextend on
b) Create Tablespace
c) Drop Tablespace
d) Create Index
56) In Oracle cloud infrastructure what is the default location in which automatic backups of databases in cloud are created?
Local Storage
57) What is the main use of load balancing policy in an oracle cloud infrastructure?
It tells the load balancer how to distribute incoming traffic to the backend servers
58) What is the way to tell Terraform what data is important?
Using outputs
56) Can you tell terraform the important data using extracts?
No. For this purpose outputs are used
57) In TCP level healthcheck you send requests to the backend servers at a specific URL. How will you validate the response?
a) based on status code
b) based on entity data returned
c) based on entity body returned
57) State the difference between VM standard and VM dense IO shapes?
The type of storage is NVMe drivers in VM Dense IO and block storage in VM standard
58) What load balancer components are optional backend set components?
SSL handling
session persistence configuration
59) What load balancer components that are mandatory backend set components?
a) Load balancing policy
b) list of backend servers
c) health check policy
d) TCP handling
60) In Oracle cloud infrastructure is IP Networking layer of the security control controlling the actions for connecting the client instance to the mount target?
61) You are in process of designing a load balancer to accept incoming traffic. What configurations must be made for this?
a) listener must be configured
b) a certificate must be available
c) a security list that is open on a listener port must be available
62) What is the use of internet gateway IG in Oracle Cloud infrastructure?
It provides a path for network traffic between VCN and public internet
63) What actions are controlled by Oracle cloud infrastructure layer?
a) creating file systems
b) listing file systems
c) associating file systems
64) In an Autonomous transaction processing which among the following SQL operations are available?
a) alter pluggable database with datafile autoetend on
b) Create Tablespace
c) Drop Tablespace
d) Create Index
65) What are the many different types of load balancing policy in Oracle Cloud Infrastructure environment?
a) Round Robin
b) Least Connections
c) IP Hash
66) Is route hash a load balancing policy in an oracle cloud infrastructure environment?
67) In Oracle cloud infrastructure is unix authentication layer of security controlling the actions for connecting the client instance to the mount target?
Nope. IP Networking layer of the security control takes care of connecting clients to mounting targets

Posted on

Oracle DBA Interview Questions Latest

1) You have recently upgraded your database and see degradation in performance of jobs. You have been asked to collect details on level of parallelism,total database time, number of I/O requests consumed by these jobs. How will you accomplish this?
Making use of ream-time database operation monitoring as follows :
dbms_sql_monitor,report_sql_monitor – This provides the information needed
2) Give some advantages of using unified auditing :- The latest feature in oracle database the unified auditing lets us capture audit information from many different sources like SYS audit records, fine-grained auditing using dbms_fga package, oracle datapump, sql*loader etc. Some advantages of using unified auditing includes : a) Decreased use of storage to store the audit trail rows in the database b) It improves overall auditing performance c) It automatically audits recovery manager events the RMAN events
4) You create two indexes on a table. One index is created with INVISIBLE clause, another index is a normal index. What happens when a DML operation is performed in underlying table ?
In case of DML operations like insert, update, delete in underlying table both the visible and invisible indexes are updates
5) What is the prerequisite for performing flashback transaction?
Undo retention guarantee for the database must be configured
Execute privilege on DBMS_FLASHBACK package must be granted to the user flashing back transaction
6) The statistics_level parameter is pdf_modifiable. Which command will you issue to set statistics_level parameter to all?
Alter system set statistics_level=all sid=’*’ scope=spfile;
7) You have been asked to upgrade your database environment. Whom all should you involve as part of this meeting?
DBA the database administration team members, storage administrator who takes case of NAS/SAN hosting database files to get to know impact of upgrade, system administrator the OS managers, network administrators for safety purpose, solution architects who are involved in software development, team members who ave developed and implemented schema changes that come as part of upgrade
8) Give details on redefining a table with virtual private database VPD policies :-
In case of redefinition on a table :
a) Primary key constraint on the table is disabled
b) VPD policies are copied from original table to the new table during redefinition
9) What is the use of the procedures listed in move_procedure column of the v$sysaux_occupant view?
The procedures are used by some database components to relocate component data to the SYSAUX tablesapce from their current tablespace. In case of upgrades from older versions of oracle wherein some data is stored in sys tablespace (or) some design changes from latest versions that moved the components away from sysaux tablespace this comes handy
This information can also be used to relocate component data from sysaux tablespace to another tablespace
10) Which oracle process takes case of service registration with the listener?
PMON the process monitor takes care of this in a database instance
11) Your environment makes use of multitenant architecture with container database hosting two pluggable databases.The datafile for the default permanent tablespace belonging to one of the pluggable databases is corrupted. You have been asked to restore and recover datafile in this pluggable database. How will you accomplish this?
Take the default tablespace offline in the affected pluggable database before performing restore and recovery of the datafile
13) You have a multitenant container database hosting three pluggable databases.This database runs in archivelog mode. The SYSAUX tablespace is corrupted in root container. How will you recover the SYSAUX tablespace?
a) First take the sysaux tablespace offline
b) Restore the sysaux tablespace related datafile
c) Apply the archived redo logs
d) Bring back the SYSAUX tablespace online
14) Your oracle database environment is a multiprocess, multithreaded environment. What is the benefit of this?
Reduced virtual memory utilization
Improved parallel execution performance
Reduced CPU utilization
15) You have configured your oracle database environment to make use of shared server connections. What happens with this configuration?
a) Allocates memory for RMAN backup and restore operations
b) Allocates memory for shared and private SQL areas
c) Contains a cursor area for storing runtime information about cursors
17) You get a call from your customer that instance is hung and they dont want to restart the instance.The client mentions that this is an unexpected sudden degradation in database performance ever since today’s morning. What will you do to determine the cause of this issue in a live hung environment?
Make use of emergency monitoring to fetch data directly from SGA analysis
18) You have enables in-database archiving on the table. Which system generated hidden column is added to this table?
Once in-database archiving is enabled on a table a column by name ORA_ARCHIVE_STATE is added to the table. By default this column is populated with value 0 for each row
19) Your customer reports performance degradation but database is working fine. What will you do to investigate the issue real-time?
Make use of ADDM reports the automatic database diagnostic monitor by running command
The above command collects data from SGA, analyzes and provides report
Get details on timing of degradation from customer that is needed to generate this report
20) You have a service alias in tnsnames.ora that works fine with tnsping servicename command. However, you are not able to connect to the database while making use of this servce directly like connect username/password@service_name. How to fix this?
This issue happens because database service is not registered with the listener
Make use of command alter system register;
21) Your environment is undergoing audit. You have been asked to perform privilege analysis, check and revoke excessive unused privilege granted to users. How will you do this?
Create a policy to capture the privilege used by a user for privilege analysis
Start analyzing the data captured by the policy
Stop analyzing the data
Generate a report with the data captured for a specified privilege capture
Compare the used and unused privileges list
Revoke the unused privileges
22) In your initialization parameter file you have set Log_archive_Dest_1=’LOCATION=location_DEtails’. You have enabled ARCHIVELOG mode in your database. Are the archived redo logs stores in default location?
No. They are created in the location specified by this parameter
24) What are some of the functions performed by SQL Tuning Advisor?
Building and implementing SQL Profiles
Checking query objects for missing and stale statistics
Recommending the restructuring of SQL queries that are using bad plans
25) When you set enable_ddl_logging=FALSE what happens?
The data definition language statements called DDL’s like create, truncate information is not logged in trace file
26) In your database environment you want to log DDL’s. How can you achieve that?
Alter system set enable_ddl_logging=TRUE;
27) You have yoru locally managed tablespace with free list managed segments. There is a performance problem and you run AWR report. From AWR report you observe high number of buffer busy wits. You determine that this is caused by contention on data blocks. You hae been asked to decrease this wait event immediately. What will you do?
Make use of automatic segment space management ASSM
28) You have to collect statistics on schema and a specific table. When a schema statistics is collected the table statistics should not be collected, this should be available for optimizer at parse time. How will you accomplish this?
execute dbms_stats.set_table_prefs(‘SCHEMA’,’TABLENAME’,’PUBLISH’,’false’);
29) You have been asked to unplug a pluggable database. What is the first step?
First the pluggable database to be unplugged must be closed
30) You have unplugged a pluggable database. Can you plug it back onto same container database?
Yes. We can do that
31) During upgrade process what happens if you execute catupgrd.sql script without executing pre-upgrade scripts in the earlier version of database?
We will get an error. This is because pre-upgrade script will update the database information in the history table that will be used by upgrade scripts
32) Give some examples of information that will be updated by running pre-upgrade script:-
One common information is the time zone information in the registry$history table
33) What is the meaning of ORA-01722 Timezone invalid number error?
This error occurs if upgrade scripts like catupgrd.sql is run without executing pre-upgrade scripts
34) You are executing catuppst.sql as part of database upgrade after running catupgrd.sql. what status do you want your database to be in?
Once the catupgrd.sql is run restart the database and then execute catuppst.sql script. This script is used to perform upgrade actions that don’t require the database to be in upgrade mode like execution of psot-PSU scripts
35) Where does catuppst.sql record upgrade activity?
This script records upgrade activity in registry$database table
36) Do we need to execute post-PSU script separately?
Not always. If the PSU is already installed in target database home before the upgrade this step can be skipped
37) You have been asked to patch your database environment with critical patch update that contain latest bug fixes. You downloaded the patch and see a file called inventory.xml and actions.xml. You move both these files along with readme.txt onto temp folder. You start patch installation using opatch. Will this work fine?
Nope. Among the files moved to temp folder bring back actions.xml file. This is a file that contains details on actions to be performed during patch installation. In simple terms this file talks about steps to be taken by opatch during patch installation like moving and replacing of files, re-linking etc. Opatch needs actions.xml file for patching to work fine
38) What is the major difference between catupgrd.sql script in 11gR2 versus 12c version of oracle database?
There are some major differences in how the catupgrd.sql gets executed in earlier and latest versions of oracle database
a) In 11gR2 catupgrd.sql script execution happens in a serial manner that will upgrade each database component one by one in a serial fashion. In 12c version it is parallel and expedites some upgrades like parallel upgrade of XDB database and such components
b) The serial execution takes more time in 11gR2 as compared to 12c
c) In case of issues during upgrade there was a need to terminate and re-execute this from scratch in versions prior to 12c
39) What is the different between inventory.xml in patch folder and inventory.xml inside contentsXML folder inside ORACLE_HOME?
The inventory.xml in patch folder has details on bugs included in that specific patch. The inventory.xml is the central Oracle Inventory location that stores details on all the oracle homes installed in that particular machine
40) You are installing latest patch for 12c in a server that has Oracle 11g and 12c installation in-place. Which inventory is updated upon patch install?
Local inventory file comps.xml inside ContentsXML folder for Oracle 12c home will be updated
41) You are installing latest patch and you have been asked to install opatch utility to start with. How will you do so?
Opatch utility is the tool from Oracle used to install oracle patches. There is no need to install this separately. This is automatically installed as part of Oracle software installation process and is located inside ORACLE_HOME/Opatch folder
42) Can you manually edit your local inventory file comps.xml inside ORACLE_HOME/inventory/ContentsXML/comps.xml?
The local inventory is an essentially very important component of oracle home and contains information about installed patches along with software version, components installed in that particular oracle home. This should not be edited or modified and can lead to corruption If corrupted these files need to be restored from backup if one is made (or) there is a need to install oracle software at a different location along with patches to match the database patch level
43) Your patch installation demands a latest version of opatch tool. You have downloaded the opatch binary and planning to install this in an oracle home and upgrade the opatch. How do you plan your downtime?
Opatch installation and upgrades does not involve any downtime. This is a binary that does not need any re-linking process
44) Does opatch version always need to match database version?
No need. This can be a number different than database version number. All we need to look for is to make sure opatch version does support a specified patch install and is compatible with specified database version which will internally be considered by oracle corporation during patch release
45) What should you do about statistics to minimize upgrade downtime?
It is a best practice to collect statistics before upgrade to reduce downtime as this will bring down execution time of uprade scripts. Connect to database as an user with sysdba privilege and run the following command :
exec dbms_Stats.gather_dictionary_stats;
46) What happens when dictionary statistics is not run before upgrade?
This should be okie as optimizer statistics will be collected for dictionary tables that lack statistics during upgrade automatically
47) You run a lower version of database. However, a database bug demands you to apply higher version of timezone patch. Now, you are planning to upgrade your database after few days. Is this something you need to make a note of while planning your upcoming upgrade?
Yes. For proper functioning of databases post upgrade it is to be noted that the target database version should consist of a time zone version that is same or higher than the source database
48) From where will you determine details on timezone version in an oracle database?
Make use of the query :
select filename,version from v$time_zone_file;
49) You are preparing your database for upgrade and one of the datafile is in backup mode. Can you start upgrade now?
Nope. For proper upgrade all the datafiles in the database should be in available status that ensures that the database is in consistent state. If not this might demand recovery
To double check run the following command before you kick start upgrade process :
select name,status from v$datafile;
The above command should return ACTIVE value in status column
52) What are the steps to change Online Redo Logfile in Oracle Database?
Online files which hold the information relevant to a database transaction is the online redolog file
LGWR writes information from redo log buffer in the memory to these files in the disk
Used to minimize the data loss
Used for recovery purposes
Redo log groups – atleast two member in a group
Oracle database has a set of online redo log files that are used to record the changes made to the oracle database.Changes recorded here will be eventually written to datafiles using oracle background process RECO. At times changes including changing the location of online redo log files, adding more member to the group can be done online
1) Get details on the status of many different files in the oracle database
select group#,member,status from v$log;
2) Drop the existing online redo log file that is archived and in INACTIVE status
alter database drop logfile group groupnumber;
Please note that above command entries of online redo logfile at the database level and not physically removed from the OS location
3) Add new online redologfiles to the database as follows
alter database add logfile(‘member1′,’member2′,’member3’) size value;
4) Perform log switch to activat the newly added member
alter system switch logfile;
5) Find the status and make sure that it works fine
select group#,member,status from v$log;
6) Remove all the physical logfiles from the OS location
Note that this is a good method to change the location of online redo log files
What happens when a patch is applied?
1) Oracle Services must be shutdown
2) DBA’s must be careful about applying patches within the specified downtime
3) System should be up and running once the maintenance window is over
4) Fallback procedures must be in place if there in any problem in bringing back the system
What is hot patching?
Hot patching is the high availability feature introduced in Oracle database
How do you call it an high availability feature?
Using hot patching DBA’s can install, enable and disable a patch online. There is no need for the oracle services to be shutdown
How do you do hot patching?
Hot patching makes use of the traditional opatch tool.
Can we use hot patch mode in Oracle database?
Hot patching is not universal in oracle. It depends on the patches being applied. Certain patches don’t support hot patching
How do you determine if hot patching is allowed?
Use the following commands :
1. opatch  query -is_online_patch 2. opatch query -allWhich environment variable is critical before applying patched?
Environment variable PATH is critical and must be set properly before using opatch
Where do you find opatch logfiles?
$ORACLE_HOME/patch_storage/patchNumber/_Apply_.logWhat does the opatch logfile contain?
1. opatch logfile provides information on the steps sequence applied by opatch
2. If a patch apply fails logfiles can be used as the first point to reference to fix what went wrong
Give details on Database INTERSECT SQL usage :-
SQL INTERSECT set operator is used to fetch common values in two columns. This is the output of SQL INTERSECT
SQL> select * from t1;
———- ———-
1 learnersreference
2 delhi
SQL> select * from t2;
———- ———-
globe        45623456
delhi             234
SQL> select name from t1 intersect select location from t2;
This is an interesting set operation in oracle database

How will you select dbid from database view in an oracle database?
DBID id the unique database identifier. It is of great significance while using RMAN (Recovery Manager) backup. We can get details on dbid , database name information from the v$database view as follows :

SQL> select dbid from v$database;
SQL> select dbid,name from v$database;
———- ———
1611538617 ORCL
the above command returned the value of dbid, database name.
What is a restore point?
Restore point is an alias for SCN or time stamp used for database recovery.
What are the types of restore points?
There are two types of restore points:ordinary and guaranteed.
How do you create an ordinary restore point?
create restore point restorepoint-name
How to drop an ordinary restore point?
drop restore point restorepoint-name
How to create a guaranteed restore point?
create restore point resp-name guarantee flashback database
How do you determine restore point?
Use v$restore_point view to determine restore point
What is value of flashback_on of v$database for various restore points?
For ordinary restore point : NO
For guaranteed point : RESTORE POINT ONLY

How will you clone a database using database control wizard in OEM?
The Enterprise Manager Clone Database Wizard steps us through the database cloning operation.
Here are the main features of the cloning feature :
1) We can clone any Oracle database that is release 8.1.7 or higher
2) The source database can be in the archivelog or the noarchivelog mode
3) We can clone a database while it is open.Database control uses RMAN internally for the cloning operation
4) Database Control will back up the datafiles and restore them in the new location. It will then recover them using archived redo logs
5) Database control will create the new instance, a password file, any necessary networking files, and the init.ora file and SPFILE
6) Database control will automatically start the new instance in the open mode
Steps For Cloning a Database Using Database Control :
1) Click the Maintenance tab on the Database Home Page of Database Control
2) Click on the clone database item in the Data Movement section(Under the move database files group)
3) In the source typege, choose the clone a running Database instance option
4) In the Source Working Directory page, enter your operating system(OS) username and password. click Next
5) In the Select Destination Page, enter the new database name and destination host name. Click Next
6) In the Destination Options Page, you can customize database file locations if you wish. Click Next
7) In the schedule page, you can choose whether you want to clone the database immediately or to schedule it for later. Click Next
8) In the review page you can review the source and clone database information
9) Click the Submit job button to start the cloning operation

What causes LRM-00116: syntax error at ‘initora_location’ and how o fix it?
While trying to startup the oracle database instance, I have been getting the error mentioned above. I was curious to know if there had been few issue related to oracle database environment variables. As all was well, I did some research on parameters in init.ora file. Upon stumbling upon a oracle metanote that said, oracle treats “” as escape character and the init.ora parameters db_file_name_convert, log_file_name_convert shouldn’t have these escape characters at the end, I looked for these two values. though I don’t have dataguard configuration in my environment, I found that these two parameters had esacpe character “” at the end. Upon removing and re-issuing startup command all went well and the issue got fixed
SQL> startup pfile=’initora_location’;

ORACLE instance started.

Total System Global Area 1249902592 bytes

Fixed Size                  1292132 bytes

Variable Size             301992092 bytes

Database Buffers          939524096 bytes

Redo Buffers                7094272 bytes

Database mounted.

Database opened.

SQL> select open_mode from v$database;


Give difference between PL/SQL Programs Definer Rights Vs Invoker Rights :-
Whenever a PL/SQL program which is a PL/SQL block, or subprograms like procedure is created in a particular schema it is given the rights of definer by default. This definer rights grants all the privileges of the program compiler to this program (i.e) this code will execute with the privilege of schema user who compiled the code. This is default and has a limitation on code reusability.
To address this it is possible to execute the PL/SQL code with privileges equivalent to the invoker (i.e) the code is executed with privileges of the schema user who is executing the code in present.
This offers code-reusability option. This simple feature can be implemented by addition of simple invoker rights as follows
int number;
This clause AUTHID CURRENT_USER will allow the executable to run with invoker rights.

Give details on some Performance Views In Oracle Database and Their Uses:-
A brief listing of various dynamic performance views in Oracle database and their uses are given below:
V$ACCESS – Displays information about locks that are currently imposed on library cache objects. The locks are imposed to ensure that they are not aged out of the library cache while they are required for SQL execution.
V$ACTIVE_INSTANCES – Displays the mapping between instance names and instance numbers for all instances that have the database currently mounted.
V$ACTIVE_SERVICES – Displays information about the active services in the database.
V$ACTIVE_SESS_POOL_MTH – Displays available active session pool resource allocation methods.
V$ACTIVE_SESSION_HISTORY – Displays sampled session activity in the database. It contains snapshots of active database sessions taken once a second.
V$ADVISOR_PROGRESS – Displays information about the progress of advisor execution.
V$ALERT_TYPES – Displays information about server alert types.
V$AQ – Displays statistics for the queues in the database.
V$ARCHIVE – Displays information about redo log files in need of archiving. Each row displays information for one thread. This information is also available in V$LOG.
V$ARCHIVE_DEST – Displays, for the current instance, all of the destinations in the Data
Guard configuration, including each destination’s current value, mode, and status.
V$ARCHIVE_DEST_STATUS – Displays runtime and configuration information for the archived redo log destinations. The information in this view does not persist across an instance shutdown.
V$ARCHIVE_GAP – Displays information about archive gaps on a standby database. This view can be used to find out the current archive gap that is blocking recovery.
V$ARCHIVE_PROCESSES – Displays the state of the various ARCH processes for the instance.
V$ARCHIVED_LOG – Displays archived log information from the control file, including archive log names.
ASM(Automatic Storage Manager) Views :
V$ASM_ALIAS -Displays one row for every alias present in every disk group mounted by the Automatic Storage Management instance. In a database instance, V$ASM_ALIAS displays no rows.
V$ASM_ATTRIBUTE – Displays one row for each attribute defined. In addition to attributes specified by CREATE DISKGROUP and ALTER DISKGROUP statements, the view may show other attributes that are created automatically.
V$ASM_CLIENT – Displays one row for each open ASM disk group.
V$ASM_DISK – Displays one row for every disk discovered by the Automatic Storage Management instance, including disks which are not part of any disk group. In a database instance, V$ASM_DISK only displays rows for disks in disk groups in use by the database instance.
V$ASM_DISK_IOSTAT – Displays information about disk I/O statistics for each ASM client. If this view is queried from the database instance, only the rows for that instance are shown.
V$ASM_DISK does, but without performing discovery of new disks. This results in a less
expensive operation. However, since discovery is not performed, the output of this
view does not include any data about disks that are new to the system.
V$ASM_DISKGROUP – Displays one row for every ASM disk group discovered by the
ASM instance on the node.
V$ASM_DISKGROUP_STAT – Displays performance statistics in the same way that V$ASM_DISKGROUP does, but without performing discovery of new disk groups. This results in a less expensive operation. However, since discovery is not performed, the output of this view does not include any data about disk groups that are new to the system.
V$ASM_FILE – Displays one row for every Automatic Storage Management file in every disk group mounted by the Automatic Storage Management instance.

Give details on implicit type conversion matrix in SQL :-
Implicit type conversion matrix is the table that specifies the datatype to which we can convert a particular datatype.
It is interesting to note that type conversion of many different datatypes including char,varchar2,nchar,nvarchar2,date,datetime/interval,number,binary float,binary double,long,raw,row id,clob,blob,nclob
has been included in matrix
What is so special about long datatype?
It is to be noted that in oracle sql 11g long datatype can’t be converted directly into datetime/interval. Instead long gets converted to varchar2 and the resulting varchar2 is converted to interval
What is the implicit function that does this conversion?
What is use of I/O calibration the latest performance tuning feature in oracle database?
The I/O calibration feature of Oracle Database enables us to assess the performance of the storage subsystem, and determine whether I/O performance problems are caused by the database or the storage subsystem. Unlike other external I/O calibration tools that issue I/Os sequentially, the I/O calibration feature of Oracle Database issues I/Os randomly using Oracle datafiles to access the storage media, producing results that more closely match the actual performance of the database.
What are prerequisites for I/O Calibration ?
Before running I/O calibration, ensure that the following requirements are met:
1) The user must be granted the SYSDBA privilege
2) timed_statistics must be set to TRUE
3) Asynchronous I/O must be enabled :
When using file systems, asynchronous I/O can be enabled by setting filesystemio_options to SETALL
4) Ensure that asynchronous I/O is enabled for datafiles by running the following query:
col name format a50
select name,asynch_io from v$datafile f,v$iostat_file i where f.file#=i.file_no and filetype_name=’Data File’;
Additionally, only one calibration can be performed on a database instance at a time.
How will you run I/O Calibration ?
The I/O calibration feature of Oracle Database is accessed using the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure. This procedure issues an I/O intensive read-only workload (made up of one megabytes of random of I/Os) to the database files to determine the maximum IOPS (I/O requests per second) and MBPS (megabytes of I/O per second) that can be sustained by the storage subsystem. Due to the overhead from running the I/O workload, I/O calibration should only be performed when the database is idle, or during off-peak hours, to minimize the impact of the I/O workload on the normal database workload. To run I/O calibration and assess the I/O capability of the storage subsystem used by Oracle Database, use the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure:
DBMS_RESOURCE_MANAGER.CALIBRATE_IO (2, 10, iops, mbps, lat);
DBMS_OUTPUT.PUT_LINE (‘max_iops = ‘ iops);
DBMS_OUTPUT.PUT_LINE (‘latency = ‘ lat);
dbms_output.put_line(‘max_mbps = ‘ mbps);
What are considerations while running I/O calibration procedure?
When running the DBMS_RESOURCE_MANAGER.CALIBRATE_IO procedure, consider the following:
1) Do not run the procedure multiple times across separate databases that use the same storage subsystem
2) Quiesce the database to minimize I/O on the instance issuing the following commands:
After the maintenance work is over unquiesce using:
3) For Oracle Real Application Clusters (RAC) configurations, ensure that all instances are opened to calibrate the storage subsystem across nodes
4) The execution time of the procedure is dependent on the number of disks in the storage subsystem and increases with the number of nodes in the database
5) In some cases, asynchronous I/O is permitted for datafiles, but the I/O subsystem for submitting asynchronous I/O may be maximized, and I/O calibration cannot continue. In such cases, refer to the port-specific documentation for information about checking the maximum limit for asynchronous I/O on the system.
From where can you query the I/O calibration process?
At any time during the I/O calibration process, you can query the calibration status in the V$IO_CALIBRATION_STATUS view. After I/O calibration is successfully completed, you can view the results in the DBA_RSRC_IO_CALIBRATE table

How to change the temporary tablespace of a database?
It is a good practice to allocate a separate temporary tablespace at the time of database creation. If not SYSTEM is taken by default. Here is the command to look for tablespace details of the database
select * from database_properties where property_name=’DEFAULT_TEMP_TABLESPACE’;
If it points to default SYSTEM, here is the command to alter the temporary tablespace of oracle database
alter database default temporary tablespace TEMP;
In above command temp is the temporary tablespace. To add a tempfile to the database issue
alter tablespace temp add tempfile ‘filepath’;
Then issue :
alter database default temporary tablespace TEMP;
Are oracle histograms really needed for performance tuning?
Oracle histogram the internal data distribution pattern collection on all the columns in a table or few columns in a table are essentially important in determining the performance of the database system. Say the data distribution doesn’t  need a histogram but has the histogram collected in its column. So what happens?
There will be usage of wrong index.This will have a great hit on system performance.
What to do to disable histograms in a table column?
First enable histogram to be collected across all columns using the method_opt option of dbms_stats.gather_table_stats as follows
execute dbms_stats.gather_table_stats(owner=>’TEST’, table=>’test’,method_opt=>’for all columns auto’);
As a second step disable histogram on a particular column that doesnt need have one. Note that this needs to be disabled one by one
execute dbms_stats.gather_table_stats(owner=>’test’,table=>’test’,’for column size 1 columnnme’);
The above command disables histogram collection in columnA
Now use the for all column size repeat clause to commit the last changed behaviour to be persistent as the permanent behaviour
execute dbms_stats.gather_table_stats(owner=>’test’,table=>’test’,’for all columns size repeat’);
Give details on Histograms and it importance with Performance Tuning :-
An interesting performance tuning strategy is to make use of histograms feature provided by Oracle corporation. Many of us believe that by enabling histograms as a whole in the table by setting method_opt=FOR COLUMNS SIZE 254 (or) by disabling histograms across the table as method_opt=FOR ALL COLUMNS SIZE 1, the performance of the query can be tuned optimally. But this is not true in most of the cases.
So when do I use histograms and how do I use them?
The best starting point would be to analyse the plan of a query. This is a simple tkprof output, ADDM report etc. Upon analysing the plan look out for the indexes, tables that are being made use of. See to that full table scan, index scans happen properly. Based on this, create histograms in particular table column as method_opt=FOR COLUMN SIZE 2-254 COLUMNNAME (where 2-254 is the range of bucket values). Similarly, disable histograms at column level using method_opt=FOR COLUMN SIZE 1 COLUMNNAME
Costbased optimizer makes use of histograms to make a decision on using fulltablescan, index scan, choosing right indexes etc.
What is Oracle Streams?
Oracle streams is a technology from oracle corporation for sharing information among different databases and among different application environments
What is the memory component that supports streams activity?
Streams pool is the memory component that supports streams activity
How do you setup streams pool?
Streams is the component in SGA(System Global Area) which is the shared memory structure. It is possible to set the STREAMS_POOL_SIZE initialization parameter. It is possible to automate it using automatic shared memory management of SGA which happens by setting SGA_TARGET parameter
What happens when SGA_TARGET is set?
When using automatic shared memory management 10% of the space set aside for SGA_TARGET is allocated to streams pool

How to determine objects in oracle schema?
Whenever we log into a database we land in a virtual space called as schema. A Schema is user specific. By default all the objects created in a schema are accessible by the user. The user is popularly called as schema user.
Details on objects in a schema can be found from a set of system tables. They are called as user_ tables. Say to get to know the objects present in a schema log onto the schema and query the user_objects using select * from user_objects. We can find the number of objects using count() function against user_objects table

Are you New Oracle DBA?

Oracle has been pioneer in developing and marketing database management system(DBMS). Oracle started version 6.0 and has released its latest version 11g on July 11th, 2007. Latest version of Oracle that comes with multi-tenancy capacity is the Oracle database 12c.

Each version comes out as an enhanced version of its predecessor with enriched features than its predecessor. Lets consider 9i where features which support Internet computing has been added and later came the 10g which supports grid computing.
1) Where to get best oracle certification?
Its always a better idea to start with oracle certifications if you are an oracle niche
2) Where to take up the certification course?
There are plenty of institutes offering OCP certification training. Oracle has its University training people in various disciplines. Details can be found here:
3) What will be the salary of a DBA fresher?
It depends on the organization. There are people joining organizations as junior DBA’s, Oracle consultants,junior database consultants, system analysts, data analysts, database designers.
In case of mid-sized organizations a fresher can expect a salary around 1.5-2lac. People joining larger organizations can expect between 2-5lac.

What are Daily Routine Tasks Of An Oracle DBA?
Monitoring for problems – running monitoring scripts or use the oracle built-in tools such as Enterprise manager.Check reports generated by monitoring scripts on a daily basis – disk space problems, memory allocation, disk I/O
Monitor alert log file – fix errors
Types of alert log errors – change some parameters, add some space, perform an administrative task
Use Metalink database and open a new tar with Oracle
Upgrade to latest versions – software, software and database( applications and DBA’s configuration parameters)
Test new applications to be deployed in the system
What is Oracle Net Services?
It is a suite of applications that provide enterprise wide distributed heterogenous computing environment connectivity.
What are the uses of Oracle Net Services?
Oracle Net services ease the network configuration and management,maximize performance,improve network diagnostic capability.
What is an Oracle Net?
Oracle Net is a component of Oracle Net services that enables a session between Oracle Database server and client application,maintains connection, and acts as a medium to transfer data between server and client.
How does Oracle Net performs its task?
Oracle Net resides in server and client and performs its task.
What are the various types of connectivity?
1) Client/Server Connections
2) Web Client Application Connections /Java Client ApplicatinConnections
3) Web Client Connections without an Application Webserver
What is Client/Server application connections?
Oracle Net is a piece of software that is layered on the top of a Oracle support network protocol-rules that determine the way to tansfer data between client and server.A good example in TCP/IP protocol.
Give details on Oracle Net Compnents:-
Oracle Net is comprises of Oracle Net Foundation Layer,Oracle Protocol support.
Oracle Net Foundation Layer – Establishes and maintains connections
Oracle Protocol Support – aps the foundation layer’s technology to industry-standard protocol
Give details on Java client application connections/web client application connections:-
Java client applications access the Oracle database server through a JDBC(Java Database Connectivity) driver,a standard java interface for connecting from java to a relational database.
What are the various types of JDBC drivers from Oracle?
JDBC OCI driver,JDBC thin driver are the drivers from Oracle.
What is a JDBC OCI driver?
JDBC OCI driver is a client-side driver that can be used with an Oracle client installation.
What is a JDBC thin driver?
It is a pure client side java driver that can be used without Oracle installations. It can be used with applets.
How do the drivers establish communication?
These drivers use Oracle Net to enable communication between oracle client and database server.
Name the four naming methods and their description:-
1)Local naming – Uses tnsnames.ora file stored on each client to connect to the database server
2) Easy connect naming – enables connections without any service name configuration
3) External naming – uses a third-party naming service to resolve service names
4) Directory naming – uses a centralized LDAP-compliant directory server to resolve service names
What is a Oracle listener?
Oracle listener is a service that runs only on the server and listens for incoming connection requests.
How do we manage listener?
Oracle provided utility lsnrctl can be used to manage listener process.
How does listener fit into Oracle networking?
1) The database registers information about the services,instances and service handlers with the listener.
2) The client makes the initial connection with the listener
3) The listener receives and verifies the client connection request and forwards it to the service handler for the database service.
4) Once the listener hands off the client request,the listener is out of the picture for that connection.
Where does the listener.ora file reside?
The default location of listener.ora file is $ORACLE_HOME/network/admin on UNIX and $ORACLE_HOMEnetworkadmin directory on windows.
Do we need to configure listener.ora file?
In recent versions of oracle database(from 9i),configuration parameters in listener.ora have default values,and we don’t have to configure listener service manually.
When is the listener service started?
After the first database on the server is created,the listener service automatically startsanf the listener configuration file listener.ora is placed in the default directory $ORACLE_HOME/network/admin.Upon creation of a new database,the database’s network and service information are automatically added to the listener’s configuration file.
What happens to the listener when the instance starts up?
Upon instance startup,the database registers itself automatically with the listener,and the listener starts listening for connection requests to this database.