DBA FAQ

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

2. When will index rebuild possible?

When index height is more than three levels, since three levels will support millions of index entries.

The height of the index refers to the number of levels that are spawned by the index as a result in row inserts. When a large amount of rows are added to a table, Oracle may spawn additional levels of an index to accommodate the new rows.
Oracle indexes can support many millions of entries in three levels.  Any Oracle index that has spawned to a 4th level followed by a large delete job might benefit from rebuilding to restore the index to it's pristine state.

Find out indexes having height(blevel+1) > 4
i.e. Indexes having BLEVEL > 3
How:

SQL>select owner,index_name,table_name,blevel from dba_indexes where BLEVEL>3;

Analyze indexes and find out ratio of
(DEL_LF_ROWS/LF_ROWS*100) is >20
How:
First “Analyze the index with validate structure option” and then,

SQL>SELECT name,height,lf_rows,del_lf_rows,(del_lf_rows/lf_rows)*100 as ratio
    FROM INDEX_STATS;

http://www.dba-oracle.com/art_index1.htm
https://blogs.oracle.com/sysdba/entry/when_to_rebuild_index
http://oracle-online-help.blogspot.in/2007/01/how-to-find-indexes-which-are-candidate.html

3.How to size undo tablespace?
db_block_size*undo_retention*undo block per second

To predict undo block per second=

select max(undoblks/((end_time-begin_time)*24*60*60)) "UNDO BLOCK PER SECOND"
from v$undostat;

4. How you will find the size of a DB?
Size of DB is nothing but sum of size of all datafiles.

select sum(bytes) from dba_data_files;

Size of each datafile will be available in DBA_DATA_FILES.

select file_name,bytes from dba_data_Files;

5. Removing an Oracle Instance in silent mode?
dbca -silent -deleteDatabase -sourceDB YOUR_DATABASE

6.What is an orainstRoot.sh and Root.sh?
OrainstRoot.sh needs to be run to change the permissions and groupname to 770 and to dba.
Root.sh (Oracle_Home) location needs to be run to create a ORATAB in /etc/oratab.

7. What is a ORA-01555 ?
snapshot too old within Rollback.
Cause:
Rollback records needed by a reader for consistent read are overwritten By other writers.
Action:
If in Automatic Undo Management mode, increase the setting of UNDO_RETENTION. Otherwise,
use larger rollback segments.

 

8.Oracle Flashback Database

The return of the whole database to a prior consistent SCN by means of the FLASHBACK DATABASE command in RMAN or SQL.
A database flashback is different from traditional media recovery because it does not involve the restore of physical files,
instead restoring your current datafiles to past states using saved images of changed data blocks.
This feature uses flashback logs and archived redo logs.

1. What is your day to day activity?
2. How to monitor RAC?
3. How to take backup of ASM?
4. How to backup the ocr and voting disk?
5. What is the use of ocrconfig?
6. What is virtual private catalog?what are the steps for create it.
7. How to restore the ASM Backup?
8. What is the sql profile?
9. How to troubleshoot the issue that yesterday my query running fine and today it’s not working?
10. How to know/Check the Oracle Interconnect?oifcfg
11. What is the cr block lost? and what should be reason for that.?
11. What is the top 5 event in AWR?(RAC)
12. what is standby database?
13. How to sync the standby database ?Standby database is out of sync than primary database and we had lost 5 archive logs so how can sync the database?
14. What is the purpose of snapshot standby database?Steps?
15. How to check the instance name in oracle rac?
16. What do you mean of process parameter in asm instance?
17. How can we know the asm disk mapping ?
18. How to restore the rman backup on third(new) machine?
19. How to check the location of file with new backup?
20. What is SAN?
21. What is the best approach /whether we should design single the asm disk group or multiple if we have single database of 15Tb?
22. How can we know that should rebuild the index?when should we rebuild the index?
23. What is the incremental backup?
24. What is Histogram?