oracle DBA interview questions

1. What is SMON and its purpose?

System monitor process (SMON) is a mandatory background process which perform instance crash recovery.  sapose instance crash and restart again at the time SMON perform uncommitted data roll back and committed data writ in to data files and remove the table locks

2. What is PMON and its purpose?

The process monitor (PMON) monitors the other background processes and performs process recovery when a server or dispatcher process terminates abnormally. PMON is responsible for cleaning up the database buffer cache and It will rollback uncommitted transactions. If the old session locked any resources that will be unlocked by PMON.

3. What is DBWR and purpose

Whenever check point is accord at the time database writer process (DBWn) writes the database buffers contents to data files. DBWn processes write modified buffers in the database buffer cache to disk

4. How to change the data file location

In 11g first we have to put data file into offline then move the physical file in OS level then alter database rename file old name and new name in database Level then put data file in to online

In 12c move physical file in OS level then alter database rename file old name and new name recovery

5. How to restore particular data file from the backup

Put data file in offline

Then restore data file from the backup.

 Then recovery data file

put data file into online

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/TEST/datafile/data1.dbf' ONLINE IMMEDIATE;

RESTORE DATAFILE /u01/app/oracle/oradata/TEST/datafile/data1.dbf

RECOVER DATAFILE /u01/app/oracle/oradata/TEST/datafile/data1.dbf

ALTER DATABASE DATAFILE '/u01/app/oracle/oradata/TEST/datafile/data1.dbf' ONLINE;

6. What are the objects in a database?

A database object is any defined object in a database that is used to store or reference data.

Some examples of database objects include tables, views, clusters, sequences, indexes, and 

synonyms.

7. What is a Tablespace?

Oracle use Tablespace for logical data Storage. Physically, data will get stored in Data files. Data files will be connected to tablespace.

A table space can have multiple data files. A tablespace can have objects from different schemas and a schema can have multiple tablespace’s.

Database creates “SYSTEM tablespace” by default during database creation. It contains read only data dictionary tables which contains the information about the database.

8. What is the use of checkpoint in Oracle?

A checkpoint performs the following three operations: Every dirty block in the buffer cache is written to the data files.

That is, it synchronizes the data blocks in the buffer cache with the data files on disk. It’s the DBWR that writes all modified database blocks back to the data files.

9.What is Oracle Home Inventory?

Oracle home inventory or local inventory is present inside each Oracle home. It only contains information relevant to a particular Oracle home.

This file is located in the following location:

$ORACLE_HOME/inventory

It contains the following files and folders:

Components File

Home Properties File

Other Folders

10. what are the types of shutdown modes of an Oracle database?

Normal: In this mode, no new connections are allowed and the database is closed after all the sessions disconnect themselves.

Immediate: No new connections are allowed and the existing active transactions are rolled back. Changes made by an active transaction are lost in this option.

Transactional: No new connections are allowed and Oracle waits until all active transactions are completed.

Abort: This happens immediately however the database is not shutdown cleanly. Database will have to perform instance recovery next time it is started.This option should not be used in regular activities.

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

 System tablespace is created at the time of database creation.This tablespace holds all the data dictionary tables and hence it is used for the creation of various database objects. System tablespace must stay online for the database to function smoothly.

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

 On a higher level, there are 2 types of backup that are available in Oracle which are physical & logical.

During physical backup, copies of physical database files (like data files, control files, redo logs & other executable) are created and saved for the future. This can be achieved using RMAN.

In contrast, logical backup allows taking backup of the database objects like tables, views, indexes, stored procedures etc. individually through Export/Import utility provided by Oracle.

13. What do we mean by hot backup & cold backup and how are they different?

Hot backup is the process of taking database backup while the database is in running mode. Hence, it is also known as Online Backup.

While cold backup can be taken only when the database is in shut down mode and hence it is known as Offline Backup as well.

14. How to find the size of your sga

 show sga

show parameter sga_target

select * from v$sgastat

15. . How to find out the total number of users connected to your database

select username from v$session;

16. What is 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. Sometimes you can force the log switch.

ALTER SYSTEM SWITCH LOGFILE;

17.Control file location

show parameter control_file and v$controlfile

SQL> show parameter control

NAME                                 TYPE        VALUE

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

control_file_record_keep_time        integer     7

control_files                        string      /u01/app/oracle/oradata/TEST/c

                                                 ontrolfile/o1_mf_gpw1vdbg_.ctl

                                                 , /u01/app/oracle/fast_recover

                                                 y_area/test/TEST/controlfile/o

                                                 1_mf_gpw1vdf7_.ctl

control_management_pack_access       string      DIAGNOSTIC+TUNING

18.datafile name, location  and size

dba_data_files and v$datafile views

SQL> select FILE_NAME,BYTES from dba_data_files;

FILE_NAME                                                                   BYTES

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

/u01/app/oracle/oradata/TEST/datafile/o1_mf_users_gpw1sxp5_.dbf           5242880

/u01/app/oracle/oradata/TEST/datafile/o1_mf_undotbs1_gpw1sthg_.dbf      419430400

/u01/app/oracle/oradata/TEST/datafile/o1_mf_system_gpw1ooxq_.dbf        954204160

/u01/app/oracle/oradata/TEST/datafile/o1_mf_sysaux_gpw1rdgm_.dbf        765460480

19.archivelog location

show parameter dest_1 and show parameter recovery and archive log list

SQL> show parameter recovery

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_

                                                 area/test

db_recovery_file_dest_size           big integer 8016M

recovery_parallelism                 integer     0

remote_recovery_file_dest            string

20.alert log location

show parameter diagnostic_dest and v$diag_info

SQL> show parameter recovery

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string      /u01/app/oracle/fast_recovery_

                                                 area/test

db_recovery_file_dest_size           big integer 8016M

recovery_parallelism                 integer     0

remote_recovery_file_dest            string

21.how to check database is in archive log mode or not

archive log list

SQL> archive log list

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     45

Next log sequence to archive   47

Current log sequence           47

22 .how to check how  many database  are running

ps -ef|grep pmon/smon

[oracle@localhost ~]$ ps -ef|grep pmon

oracle     3785      1  0 07:41 ?        00:00:00 asm_pmon_+ASM

oracle    10672      1  0 08:19 ?        00:00:00 ora_pmon_test

oracle    27218  24026  0 09:54 pts/1    00:00:00 grep --color=auto pmon

23 .how to check database size

dba_segments

SQL> select sum(BYTES/1024/1024) from dba_segments;

SUM(BYTES/1024/1024)

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

           1910.1875

24 .how to check database which parameter files are using

show parameter spfile or pfile

SQL> show parameter spfile

NAME                                 TYPE        VALUE

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

spfile                               string      /u01/app/oracle/oradata/spfile

                                                 test.ora

25. How to change the pfile parameter

       By using alter system system parameter name = parameter value

26. How to add the datafile to the existing tablespace

Alter tablespace tablespace name add datafile datafile name size

ALTER TABLESPACE users ADD DATAFILE '/u01/oracle/oradata/users_02.dbf' size 100m

27. How to drop the datafile from the tablespace

Alter tablespace name drop datafie file name

ALTER TABLESPACE users ADD DATAFILE '/u01/oracle/oradata/users_02.dbf'

28. How to put datafile into off line

Alter database datafile name offline

ALTER DATABASE DATAFILE '/u01/oracle/oradata/users_02.dbf' OFFLINE;

29. How to put datafile into online

Alter database datafile name online

ALTER DATABASE DATAFILE '/u01/oracle/oradata/users_02.dbf' Online;

30. How to put database in archive log mode and no archive log mode

SQL> archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     47

Current log sequence           49

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup mount

ORACLE instance started.

Total System Global Area 1157627168 bytes

Fixed Size                  8895776 bytes

Variable Size             318767104 bytes

Database Buffers          822083584 bytes

Redo Buffers                7880704 bytes

Database mounted.

SQL> alter database archivelog;

Database alterede

SQL> alter database open;

Database altered.

SQL> archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     47

Next log sequence to archive   49

Current log sequence           49

31. How to find out tnsnames.ora file location

Default location $ORACLE_HOME/network/admin

If it is not there in default location    with the help of echo $TNS_ADMIN  we can find out the location 

32. What is the difference between SPFILE and PFILE?

Spfile: Server parameter file of binary file, It contains all the information for setting the database initialization parameters. SPFILE permits dynamic changes without requiring you to restart that instance.

Default location= $ORACLE_HOME/dbs

Pfile: It is text parameter file, information for setting the database initialization parameters.

PFILE parameters  are static parameter database restart required

Default location= $ORACLE_HOME/dbs

33.what is use of index
Indexes are used in Oracle to provide quick access to rows in a table. Indexes provide faster access to data for operations that return
 a small portion of a table's rows.Although Oracle allows an unlimited number of indexes on a table, the indexes only help if they are used to speed up queries.
33.what is database statistics how to gather statistics 
 EXEC DBMS_STATS.gather_database_stats;
 EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15);
 EXEC DBMS_STATS.gather_database_stats(estimate_percent => 15, cascade => TRUE);

 EXEC DBMS_STATS.gather_schema_stats('SCOTT');
 EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15);
 EXEC DBMS_STATS.gather_schema_stats('SCOTT', estimate_percent => 15, cascade => TRUE);

 EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES');
 EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15);
 EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMPLOYEES', estimate_percent => 15, cascade => TRUE);

 EXEC DBMS_STATS.gather_dictionary_stats;
34.what is the use of statics 
 with the help of satatics orcle opitymysa create best exigution plane it will increase the performance
35.what is execution plan what is the use of it
 way of data fetching data from the database.every sql query have execution plane oracle optimysar are going to create the execution plane depends on statistics
36.why stats are going to stale 
  if more dml operations are happens at the time stats are going the stale (row migration are happen or empty blocks are accord )
37.what is split brain syndrome in oracle RAC ?

Split brain syndrome occurs when the instances in a RAC fails to connect or ping to each other via the private interconnect. So, in a two node situation both the instances will think that the other instance is down because of lack of connection. The problem which could arise out of this situation is that the sane block might get read, updated in these individual instances which cause data integrity issues, because the block changed in one instance will not be locked and could be overwritten by another instance.

Interview Questions 2023.

==================

1.what is split brain syndrome.

2.use is open and have all access still user not able to connect to the database.

3.database is up and running fine parameter file got deleted, how will you create.

4.high level patching steps.

5. new features of upgrade process in 11G, 12C,19C.

6.Database upgrade steps.

7.types of standbys and explain all.

8.database is in hot backup mode and instance got rebooted, database will start or not ?

9.disk added to asm disk group load balancer is running Is there any chance to increase the load

balancer.

10.RAC startup sequence.

11.is it possible to create local user in CDB,

12.how to stop the pdb.

13.how to do the pdb clone. And pdb clone types

14. CDB, PDB have same tablespace or we can able to create different tablespaces.

15.explain about OEM.

16.agent deployment

17.node eviction.

18. how to take the database backup in 2 node RAC.