FAQ Oracle DBA

 

FAQs Oracle DBA

 

1. How do you see how many instances are running?

$ ps –ef | grep smon

 

2. How do you see how much hard disk space is free in Linux?

$ df –k or df –kh or df –g (for AIX)

 

3. How do you see which segment belongs to which database instances?

4. How do you see how many processes are running in UNIX?

$ ps

 

5. How do you kill a process in UNIX?

$ kill -9 <process id> (process id, we can get from ps command)

 

6. Can you change priority of a Process in UNIX?

$ renice {priority} pid

 

7. Swap partition must be how much the size of RAM?

Double the size of RAM

 

8. How do you automate starting and shutting down of databases in UNIX?

By using dbstart.sh and dbstop.sh scripts and mentioning “Y” in oratab file

 

9. How do you set Kernel Parameters in Red Hat Linux, AIX and Solaris?

By entering parameters with values in /etc/sysctl.conf file

 

10. What are VMSTAT and SHHMMAX?

VMSTAT is used to know memory occupation details

SHMMAX is maximum memory SGA can use from the total RAM size

 

11. How can a DBA see only the files which are modified today?

 

At OS level, using ls –ltr, which will show latest date if it was modified

In windows, we can see in details tab

 

12. How can a DBA see only the files which were created 2 days ago?

$ find . -mtime +2 -print|xargs ls -l

 

13. How can a DBA delete only the files which were created 5 days ago? (Say 15-02-2010)?

$ find . -mtime +5 -print|xargs rm

 

14. How can DBA see the size of RAM?

We can use VMSTAT or TOP commands for unix

We can see through my computer -> properties in windows

 

15. How can a DBA see only the size of directory?

$ du –skh <directory name> (this will give size in GB)

 

16. Difference between “du” and “df” commands?

du – disk used,  df – disk free. So one will give used space and other will use free space

 

17. How can a DBA see the number of network connections to a database?

$ ps –ef | grep oracle

If we see connections with local=NO, then they are network connections

 

18. How can DBA know whether the database is open or not? In other words how can DBA see the status of database?

Select * from v$instance;

Or

Select name,open_mode from v$database;

 

If from OS level, we can use ps –ef | grep smon

 

19. How can DBA See the number of “listeners” available for a database?

$ ps –ef | grep tns

 

20. What is stored in oratab file?

The SID name, its home path and whether DB is auto startable in case of reboot

 

21. What is the difference between Soft Link and Hard Link?

Hard Links :

1. All Links have same inode number.

2.ls -l command shows all the links with the link column(Second) shows No. of links.

3. Links have actual file contents

4.Removing any link just reduces the link count but doesn't affect other links.

 

Soft Links(Symbolic Links) :

1.Links have different inode numbers.

2. ls -l command shows all links with second column value 1 and the link points to original file.

3. Link has the path for original file and not the contents.

4.Removing soft link doesn't affect anything but removing original file the link becomes dangling link which points to nonexistant file.

 

 

22. If oracle is installed or not how do you see from o/s level?

By checking corresponding oracle folder/directories

 

 

23. If there are 5 databases on the server, How to find the versions of the databases I mean whether it is 9i or 10g?

By looking at oratab file

 

 

25. How do you see how many databases are there?

By looking at oratab file (but databases created manually will not have entry)

 

26. Which subdirectory contains the message files the Oracle Networking services?

$ORACLE_HOME/network/admin          

 

27. Which is the Oracle Subdirectory that contains the binary files for all the Oracle products and databases on the system?

$ORACLE_HOME/bin

 

28. Locate the password file for your database. What is the location?

$ORACLE_HOME/dbs

 

29. Which Initialization parameter holds the location of the ALERT file?

Background_dump_dest

 

30. What does the Oradata subdirectory contain?

It contains all the datafiles, controlfiles and redolog files if DB created using DBCA

 

31. Identify the registry entry or environment variable that stores a unique instance name?

ORACLE_SID

 

32. Which initialization parameter limits the size of the ALERT and Background Trace files?

There is no such parameter

 

 Oracle Real time questions

 

1)      How can you see the Current SCN number of the database?

Select current_scn from v$database;

 

2)      How can you see the Current log sequence number the logwriter is writing in to?

Select * from v$log;

 

3)      If you are given a database, how will you know how many datafiles each tablespace contain?

Select distinct tablespace_name,file_name from dba_data_files;

 

4). How will you know which temporaray tablepsace is allocated to which user?

Select temporary_tablespace from dba_users where username=’SCOTT’;

 

5) If you are given a database,how will you know whether it is locally managed or      dictionary managed?

Select extent_management from dba_tablespaces where tablespace_name=’USERS’;

 

 

6) How will you list all the tablespaces and their status in a database?

Select tablespace_name,status from dba_tablespaces;

 

7) How will you find the system wide 1) default permanent tablespace, 2) default temporary tablespace 3) Database time zone?

Select property_name,property_value from database_properties where property_name like ‘%DEFAULT%’;

 

8) How will you find the current users who are using temporary tablespace segments?

V$TEMPSEG_USAGE

 

9) How will you convert an existing dictionary managed permanent tablespace to temporary tablespace?

Not possible

 

10) Is media recovery requird if a tablespace is taken offline immediate?

Not required

 

11) How will you convert dictionary managed tablespace to locally managed tablespace?

Exec dbms_space_admin.tablespace_migrate_to_local(‘TABLESPACE_NAME’);

 

12) If you have given command to make a tablespace offline normal, but its not  happening.it is in transactional read-only mode. How will you find which are the transactions which are preventing theconversion?

By looking at queries using by those SID (u can get script from net). I suspect question is not clear.

 

13) If you drop a tablespace containing 4 datafiles, how many datafiles will be droped at a time by giving a single drop tablespace command?

All datafiles

 

14) If database is not in OMF,How will you drop all the datafiles of a tablespace without dropping the tablespace itself?

Alter database datafile ‘PATH’ offline drop;

 

15) How will you convert the locally managed tablespace to dictionay managed?What are the limitations?

Exec dbms_space_admin.tablespace_migrate_from_local(‘TABLESPACE_NAME’);

 

SYSTEM tablespace should be dictionary

 

16) Which parameter defines the max number of datafile in database?

Db_files and MAXDATAFILES in control file

 

17) Can a single datafile be allocated to two tablespaces?Why?

No. because segments cannot space multiple datafiles

 

18) How will you check if a datafile is Autoextinsible?

Select autoextensible from dba_data_files where file_name=’’;

 

19) Write command to make all datafiles of a tablespace offline without making the tablspace offline itself?

Alter database datafile ‘PATH’ offline normal;

 

20) In 10g, How to allocate more than one temporary tablespace as default temporary tablespace to a single user?

By using temporary tablespace group

 

21) What is the relation between db_files and maxdatafiles parameters?

Both will restrict no of datafiles in the database

 

22) Is it possible to make tempfiles as read only?

yes

 

23) What is the common column between dba_tablespaces and dba_datafiles?

Tablespace_name

 

24) Write a query to display the names of all dynamic performance views?

Select table_name from dictionary where table_name like ‘v$%’;

 

25) Name the script that needs to be executed to create the data dictionary views after database creation?

Catalog.sql

 

26) Grant to the user SCOTT the RESTRICTED SESSION privilege?

SQL> grant restricted session to scott;

Grant succeeded.

 

27) How are privileged users being authenticated on the database you are currently working on? Which initialization parameter would give me this information?

Question not clear

 

28) Which dynamic performance view gives you information about all privileged users who have been granted sysdba or sysoper roles? Query the view?

SQL> desc v$pwfile_users

 

29) What is the purpose of the DICTIONARY table?

To know data dictionary and dynamic performance view names

 

30) Write a query to display the file# and the status of all datafiles that are offline?

Select file#,status from v$datafile where status=’OFFLINE’;

 

31) Write the statement to display the size of the System Global Area (SGA)?

Show parameter sga

Or

Show sga

 

32) Obtain the information about the current database? What is its name and creation date?

Select name,created from v$database;

 

33) What is the size of the database buffer cache? Which two initialization Parameters are used to determine this value?

 

Db_cache_size or db_block_buffers

 

34) What value should the REMOTE_LOGIN_PASSWORDFILE take if you need to set up Operating System authentication?

exclusive

 

35)  Which initialization parameter holds this value? What does the shared pool comprise of?

Library cache and data dictionary cache.

Parameter : shared_pool_size

 

36) Which initialization parameter holds the name of the database?

Db_name

 

37) Which dynamic performance view displays information about the active transactions in the database? Which view returns session related information?

V$transaction, v$session

 

38) Which dynamic performance view is useful for killing user sessions? Which columns of the view will you require to kill a user session? Write the statement to kill any of the currently active sessions in your database?

V$session (SID, SERAIL#)

Alter system kill session ‘SID,SERIAL#’;

 

39) What is the difference between the ALTER SYSTEM and ALTER SESSION commands?

Changes performed using ALTER SYSTEM are either permanent for the memory or database. But for ALTER SESSION, its only for that session

 

40) Write down the mandatory steps that a DBA would need to perform before the CREATE DATABASE command may be used to create a database?

Create a pfile or spfile

Create password file

If windows, create instance using ORADIM utility

 

 

42) In which Oracle subdirectory are all the SQL scripts such as catalog.sql/ catproc.sql /utlexcpt.sql etc...? Located?

$ORACLE_HOME/rdbms/admin/

 

43) Which dynamic performance view would you use to display the OPTIMAL size of the rollback segment RBS2. Write a query to retrieve the OPTIMAL size and Rollback segment name?

V$undostat (but many scripts are available in google or even in my blog)

 

 

44) During a long-running transaction, you receive an error message indicating you have insufficient space in rollback segment RO4. Which storage parameter would you modify to solve this problem?

Extent size

 

45) How would I start the database if only users with the RESTRICTED SESSION privilege need to access it?

Startup restrict

 

46) Which data dictionary view would you query to find out information about free extents in your database? Write a query to display a count of the number of free extents in your database?

We can use scripts. Exactly its difficult to know

 

47) Write a query to display the tablespace name, datafile name and type of extent management (local or dictionary) from the data dictionary?

You need to combine dba_data_files and dba_tablespaces

 

48) Which two types of tablespace cannot be taken offline or dropped?

SYSTEM and UNDO

 

49) When a tablespace is offline can it be made read only? Perform the

Required steps to confirm your answer?

Didn’t got the answer

 

50) Which parameter specifies the percentage of space in each data block that is reserved for future updates?

PCTFREE

51) write down two reasons why automatic extent allocation for an extent may fail?

If the disk space reached max limit

If autoextend reached maxsize limit

 

52) Query the DBA_CONSTRAINTS view and display the names of all the constraints that are created on the CUSTOMER table?

Select constraint_name from dba_constraints where table_name=’CUSTOMER’;

 

53) Write a command to display the names of all BITMAP indexes created in the database?

Select index_name from dba_indexes where index_type=’BITMAP’;

 

54) Write a command to coalesce the extents of any index of your choice?

Alter tablespace <tablespace_name> coalesce;

Don’t know for extents

 

55) . What happens to a row that is bigger than a single block? What is this called? Which data dictionary view can be queried to obtain information about such blocks?

Row will be chained into multiple blocks. CHAINED_ROWS is the view

 

56) Write a query to retrieve the employee number and ROWIDs of all rows that belong to the EMP table belonging to user SCOTT?

Select rowid,empno from scott.emp;

 

57) During a long-running transaction, you receive an error message indicating you have insufficient space in rollback segment RO4. Which storage parameter would you modify to solve this problem?

Repeated question

 

58) How to compile a view?  How to compile a table?

Alter view <view_name> compile;

Tables cannot be compiled

 

59) What is the block size of your database and how do you see it?

Db_block_size

 

60) At one time you lost parameter file accidentally and you don't have any backup. How you will recreate a new parameter file with the parameters set to previous values.?

We can recover it from alert log file which contains non-default values

 

61) You want to retain only last 3 backups of datafiles. How do you go for it in RMAN?

By configuring backup retention policy to redundancy 3