DBA interview Question and Answer

DBA interview Question and Answer

What is basic difference between V$ view to GV$ or V$ and V_$ view?

The V_$ (V$ is the public synonym for V_$ views) view are called dynamic performance views. They are continuously updated while a database is open in use and their contents related primary to performance.

Select owner, object_name, object_type  from dba_objects where object_name like '%SESSION'   and  object_name like 'V%';

OWNER OBJECT_NAME    OBJECT_TYPE

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

SYS    V_$HS_SESSION VIEW

SYS    V_$LOGMNR_SESSION    VIEW

SYS    V_$PX_SESSION VIEW

SYS    V_$SESSION    VIEW

Where as GV$ views are called Global dynamic performance view and retrieve information about all started instance accessing one RAC database in contrast with dynamic performance views which retrieves information about local instance only. The GV$ views having the additional column INST_ID which indicates the instance in RAC environment.

GV$ views use a special form of parallel execution. The parallel execution co-ordinator runs on the instance that the client connects to and one slave is allocated in each instance to query the underlying V$ view for that instance.

What is the Purpose of default Tablespace in oracle database?

Each user should have a default tablespace. When a user creates a schema objects and specifies no tablespace to contain it, oracle database stores the object in default user tablespace.

The default setting for default tablespace of all users is the SYSTEM tablespace. If a user likely to create any type of objects then you should specify and assign the user a default tablespace.

Note: Using the tablespace other than SYSTEM reduces contention between data dictionary objects and the user objects for the same data files. Thus it is not advisable for user data to be stored in the SYSTEM tablesapce.

SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='EDSS';

SQL> Alter user EDSS default tablespace XYZ;

SELECT USERNAME, DEFAULT_TABLESPACE FROM DBA_USERS WHERE USERNAME='EDSS';

Once you change the tablespace for a user the previous/existing objects stay the same,

I suppose that you never specified a tablespace when you created the objects and let to use the default tablespace from the user, the objects stay stored in the previous tablespace(tablespace A) and new objects will be created in the new default tablespace (tablespace B). Like in the example above, the objects for EDSS stay in the ORAJDA_DB tablespace and any new object will be stored in the ORAJDA_DB1 tablespace.

What is Identity Columns Feature in oracle 12c?

Before Oracle 12c there was no direct equivalent of the AutoNumber or Identity functionality, when needed it will implemented using a combination of sequences and triggers. The oracle 12c database introduces the ability to define an identity clause for a table column defined using a numeric type.

Using ALWAYS keyword will force the use of the identity.

GENERATED

[ALWAYS | BY DEFAULT [ ON NULL ] ]

AS IDENTITY [ (identity_options ) ]

Using BY DEFAULT allows you to use the identity if the column isn't referenced in the insert statement.

Using BY DEFAULT ON NULL allows the identity to be used even when the identity column is referenced and NULL value is specified.

How to find Truncated Table user information?

If you have already configure the data mining concept with your database then there is nothing to do you can query with v$logmnr_contents view and find the list, otherwise you need to do some more step to configure it first with your database.

Why used Materialized view instead of Table?

Materialized views are basically used to increase query performance since it contains results of a query. They should be used for reporting instead of a table for a faster execution.

How does Session communicate with the server process?

Server processes executes SQL received from user processes.

Which SGA memory structure cannot re-size dynamically after instance startup?

Log Buffer

Which Activity will generate less UNDO data?

Insert

What happens when a user issue a COMMIT?

The LGWR flushes the log buffer to the online redo log.

When the SMON processes perform ICR?

Only at the time of startup after abort shutdown.

What is the purpose of synonym in oracle?

Synonym permits application to function without modification regardless of which user owns table or view or regardless of which database holds the table or view. It masks the real name and owner of an object and provides location transparency for tables, views or program units of a remote database.

CREATE SYNONYM pay_payment_master FOR HRMS.pay_payment_master;

CREATE PUBLIC SYNONYM pay_payment_master FOR HRMS.pay_payment_master@sadhan.world;

How many memory layers are in the shared pool?

The shared pool of SGA having three layers: Library cache which contains parsed sql statement, cursor information, execution plan etc; dictionary cache contains cache user account information, privilege information, datafiles, segments and extent information; buffer for parallel execution messages and control structure.

What is the cache hit ratio, what impact does it have on performance?

It calculates how often a requested block has been found in the buffer cache without requiring disk space. This ratio is computed using view V$SYSSTAT. The buffer cache hit ratio can be used to verify the physical I/O as predicted by V$DB_CACHE_ADVICE.

select name, value From v$sysstat Where name in ('db block gets', 'consistent gets', 'physical reads');

The cache-hit ratio can be calculated as follows: Hit ratio = 1 – (physical reads / (db block gets + consistent gets)) If the cache-hit ratio goes below 90% then:  increase the initialization parameter DB_CACHE_SIZE.

Which environment variables are critical to run OUI?

ORACLE_BASE; ORACLE_HOME; ORACLE_SID

What is Cluster verification utility in RAC env.

The cluster verification utility (CVU) is a validation tools that you can use to check all the important component that need to verified at different stage of deployment in a RAC environment.

How to identify the voting disk in RAC env. and why it is always in odd number?

As we know every node are interconnected with each other and pinging voting disk in cluster to check whether they are alive. If voting disks are in even count then both nodes are survival node and it is created multiple brains in same cluster. If it is odd number in that case only one node ping greater count of voting disk and cluster can be saved from multiple brain syndrome. You can identify voting disk by using the below command line:

#crsctl query css votedisk

What are the components of physical database structure? What is the use of control files?

Oracle database consists of three main categories of files: one or more datafiles, two or more redo log files; one or more control files.

When an instance of an Oracle database is started, its control file is used to identify the database and redo log files that must be opened for database operation to proceed. It is also used in database recovery.

What is difference between database Refreshing and Cloning?

DB refreshing means the data in the target environment has been synchronized with a copy of production. This can be done by restoring with a backup of production database where as cloning means that an identical copy of production has been taken and restore to the target environment.

When we need to Clone or Refresh the database?
There are a couple of scenarios when cloning should be performed:
1. Creating a new environment with the same or different DBNAME.
2. Sometimes we need to apply patches or other major configuration changes thus a copy of environment is needed to test the effect of this change.

3. Normally in software development environment before any major development efforts take place, it is always good to re-clone dev, test environments to keep environment sync.
The refreshment is needed only when you sure that the environment are already sync and you need to apply only change of latest data.

What is OERR utility?

The OERR (Oracle Error) utility is provided only with Oracle databases on  UNIX  platforms.  OERR is not an executable, but instead, a shell script that retrieves messages from installed message files. OERR is an Oracle utility that extracts error messages with suggested actions from the standard Oracle message files. This utility is very useful as it can extract OS-specific errors that are not in the generic Error Messages and Codes Manual.

What do you mean by logfile mirroring?

The Process of having copy of redolog file is called mirroring. It is done by creating group by log file together. This ensures that LGWR automatically writes them to all the member of the current online redo log group. In case a group fails the database automatically switch over the next group. It diminishes the performance.

What is the use of large pool? Which case you need to use the large pool?

You need to set large pool if you are using multi thread server and RMAN backup. It prevents RMAN and MTS server from competing with other subsystem for the same memory. RMAN uses the large pool for backup & restore when you set the DBWR_IO_SLAVES or BACKUP_TAPE_IO_SLAVES parameters to simulate asynchronous I/O. If neither of these parameters is enabled then oracle allocates backup buffers from local process memory rather than shared memory. Then there is no use of large pool.

What will be your first steps if you get the message Application is running slow?

1. Gather the statistics (statspack, AWR) report to find TOP 5 wait event or run a Top command in Linux to see CPU usage. Later run VMSTAT, SAR and PRSTAT command to get more information on CPU and Memory usage and possible blocking.
2. If poor written statements then run EXPLAIN PLAN on these statements and see whether new index or use of HINT brings the cost of SQL down.

How do you add more or subsequent block size specification?

 Re-create the CONTROLFILE to specify the new BLOCK SIZE for specific data files or Take the database OFFLINE and bring back online with a new BLOCK SIZE specification.