IMPORTANT DBA QUESTIONS<....NASHIM.....>

IMPORTANT DBA INTERVIEW QUESTIONS....

What are the things you check before you install an oracle database on unix or linux or windows platforms ?
How do you identify the pre-req os patches required for an oracle installation on HP or solaris or Linux platforms ?
How do you identify if the oracle software version you are installing is certified on the platform you are installing ?
What is emulator on unix and why do you need it ? What is xterm ?
What is XAUTHORITY env settings ?
What is root.sh ?
What is the importance of /etc/oraInst.loc or /var/opt/oracle/oraInst.loc ?
Why do you need environment file ? Does this get created automatically ? What the environment settings which are needed in environment file ?
What is oraenv,dbshut,dbstart,dbhome files ? Where can you find these ? Explain me about these files ?
What is the importance of /etc/oratab or /var/opt/oracle/oratab ?
What are the various kernel settings which you setup in oracle install on linux/unix ?
Why environment file is not needed in windows ?
What is oradim ? Why do you need this tool in windows ?
When do you need tnsnames.ora listener.ora and sqlnet.ora ? How do you configure these files ?
What is Net8 or SQL*Net means ? How do you get this software ?
What are the different sql net naming methods ?
What is onames or OID ?
What is semaphore ? How do you handle the instance crash ?
How do you run the o/s commands from oracle ?
What is mutating trigger ?
Can we use commit inside a trigger ?

How to enable the auditing in oracle ?

How do you send an email from oracle db ?
How can you schedule the monitoring from the database ?
Give me the detail steps for creating the database manually ?
Give me the detail steps for creating the database using DBCA ?

How to identify the version of the rdbms ?

How to identify the database components and their statuses ?
How to identify if I am using 64 bit oracle or 32 bit oracle ?
How do you approach a new database implementation for a dw or oltp project ? Tell me about key things which you take into consideration ?
Tell me about various RAID levels ?
Explain me about oracle memory structures and background processes ?
What is oracle instance ? What is Oracle SGA ? What are the various components exist in Oracle SGA ?
What is PGA ? Tell about the responsibilities of background processes such as pmon,smon,arc,rec,lck,qmn,aq,lgwr,dbwr ?
Which process retrieves the data from disk into memory ?
What is advanced queuing ? How do you enable this ?
Tell me about checkpoint process in oracle and SCN numbers in oracle.
What is the difference between dedicated and shared servers processes ?
In what kind of configuration, you see oracle dispatcher process ?
Explain me about the instance recovery processes such as rollforward and rollback ?
Explain me about library cache and data dictionary cache ?
Tell me the difference between startup restrict and startup ?
Explain me about the various stages of oracle startup process ?
Explain me about the oracle instance recovery ?
Explain me about the difference between shutdown transactional and shutdown normal ?
What are the various shutdown options which can be used and explain me about those ?
What are the various startup options which can be used and explain me about those ?
How do you identify the oracle alert log file ? controlfile ? trace file ? tns file ? listener file ?
How do you identify the software location(oracle_home) for a running database ?
List at least 25 famous unix commands which are needed for daily day-to-day dba work ?
How do you drop user tablespace and users ?
Explain me the database views which you look at to identify the physical structures of the database ?
Explain me the database views which you look at to identify the logical structures of the database ?
How do you calculate/identify the total database current size ?
How do you calculate/identify the table current size ?
How do you calculate/identify the tablespace current size ?
How do you calculate/identify the freespace in the database ?
How do you move the table from one tablespace to another tablespace ?
How do you resize the logfiles of the database ?
How do you add the log groups to the database ?
How do you drop the redog log groups ?
How do you rename/move the redo log members from one disk to another disk ?
How do you identify the user default tablespace ?
How do you change the user default tablespace ?
How do you specify the tablespace name when you create the table ? What is the syntax ?
How do you add the datafiles to the existing tablespace ?
Give me the syntax for creating the dictionary managed and locally managed tablespaces ?
Give me the syntax for resizing the datafile ?
How do you rename the database tablespaces ?
How do you rename the database datafiles ?
How do you relocate the datafiles for system tablespace ?
How do you relocate the datafiles for normal tablespace ?
How do you make the tablespace offline. What is the advantage. ?
How do you make the tablespace readonly. What is the advantage. ?
What is difference between readonly/offline ?
How do you identify the datafile associated with a table ? How do you know how big the table size is. ?
How do you add,rename the temp datafiles ?
How do you identify the archive logs location ?
How do you change the archive log location ?
How do you change the archive log format ?
How do you enable the archive log, disable the archive log ? Explain in detail about database instance ?
How do you identify if the database is running in archive log mode or no-archive log mode ?
What is export import ? How do you change the db char set or db block size ?
Give me the syntax to export the entire database. ?
Give me the syntax to import the entire database. ?
Give me the syntax to export one schema in the database ?
Give me the syntax to export one table in the database ?
What is data pump ?
Tell me about transportable tablespaces ?
Tell me about your experience with automation of startup shutdown in unix and windows ?
Did you ever heard of rc.d ? Tell me about what do you know about this ?
Tell me about your experience with using data pump ?
Tell me about flash back recovery feature of 10g ?
Tell me about your experience with DBMS_STATS ?
How do you collect the dictionary stats in 9i and 10g ?
How do you speed up the export/import process of entire database ? What approach/settings u use ?
What is external tables ? Give me the steps to set this up ?
Give me the steps to recover a drop table in 10g ?
What is table purge vs table truncate in 10g ?
How do you rewind the database in 10g ? Give me the steps ?
Give me the steps to recover the deleted rows using flashback query in 10g ?

ORACLE DBA INTERVIEW QUESTION..1

1. How many memory layers are in the shared pool?
2. How do you find out from the RMAN catalog if a particular archive log has been backed-up?
3. How can you tell how much space is left on a given file system and how much space each of the file system’s subdirectories take-up?
4. Define the SGA and how you would configure SGA for a mid-sized OLTP environment? What is involved in tuning the SGA?
5. What is the cache hit ratio, what impact does it have on performance of an Oracle database and what is involved in tuning it?
6. Other than making use of the statspack utility, what would you check when you are monitoring or running a health check on an Oracle 8i or 9i database?
7. How do you tell what your machine name is and what is its IP address?
8. How would you go about verifying the network name that the local_listener is currently using?
9. You have 4 instances running on the same UNIX box. How can you determine which shared memory and semaphores are associated with which instance?
10. What view(s) do you use to associate a user’s SQLPLUS session with his o/s process?
11. What is the recommended interval at which to run statspack snapshots, and why?
12. What spfile/init.ora file parameter exists to force the CBO to make the execution path of a given statement use an index, even if the index scan may appear to be calculated as more costly?
13. Assuming today is Monday, how would you use the DBMS_JOB package to schedule the execution of a given procedure owned by SCOTT to start Wednesday at 9AM and to run subsequently every other day at 2AM.
14. How would you edit your CRONTAB to schedule the running of /test/test.sh to run every other day at 2PM?
15. What do the 9i dbms_standard.sql_txt() and dbms_standard.sql_text() procedures do?
16. In which dictionary table or view would you look to determine at which time a snapshot or MVIEW last successfully refreshed?
17. How would you best determine why your MVIEW couldn’t FAST REFRESH?
18. How does propagation differ between Advanced Replication and Snapshot Replication (read-only)?
19. Which dictionary view(s) would you first look at to understand or get a high-level idea of a given Advanced Replication environment?
20. How would you begin to troubleshoot an ORA-3113 error?
21. Which dictionary tables and/or views would you look at to diagnose a locking issue?
22. An automatic job running via DBMS_JOB has failed. Knowing only that “it’s failed’, how do you approach troubleshooting this issue?
23. How would you extract DDL of a table without using a GUI tool?
24. You’re getting high “busy buffer waits’ - how can you find what’s causing it?
25. What query tells you how much space a tablespace named “test’ is taking up, and how much space is remaining?
26. Database is hung. Old and new user connections alike hang on impact. What do you do? Your SYS SQLPLUS session IS able to connect.
27. Database crashes. Corruption is found scattered among the file system neither of your doing nor of Oracle’s. What database recovery options are available? Database is in archive log mode.
28. Illustrate how to determine the amount of physical CPUs a Unix Box possesses (LINUX and/or Solaris).
29. How do you increase the OS limitation for open files (LINUX and/or Solaris)?
30. Provide an example of a shell script which logs into SQLPLUS as SYS, determines the current date, changes the date format to include minutes & seconds, issues a drop table command, displays the date again, and finally exits.
31. Explain how you would restore a database using RMAN to Point in Time?
32. How does Oracle guarantee data integrity of data changes?
33. Which environment variables are absolutely critical in order to run the OUI?
34. What SQL query from v$session can you run to show how many sessions are logged in as a particular user account?
35. Why does Oracle not permit the use of PCTUSED with indexes?
36. What would you use to improve performance on an insert statement that places millions of rows into that table?
37. What would you do with an “in-doubt” distributed transaction?
38. What are the commands you’d issue to show the explain plan for “select * from dual’?
39. In what script is “snap$” created? In what script is the “scott/tiger” schema created?
40. If you’re unsure in which script a sys or system-owned object is created, but you know it’s in a script from a specific directory, what UNIX command from that directory structure can you run to find your answer?
41. How would you configure your networking files to connect to a database by the name of DSS which resides in domain icallinc.com?
42. You create a private database link and upon connection, fails with: ORA-2085: connects to . What is the problem? How would you go about resolving this error?
43. I have my backup RMAN script called “backup_rman.sh”. I am on the target database. My catalog username/password is rman/rman. My catalog db is called rman. How would you run this shell script from the O/S such that it would run as a background process?
44. Explain the concept of the DUAL table.
45. What are the ways tablespaces can be managed and how do they differ?
46. From the database level, how can you tell under which time zone a database is operating?
47. What’s the benefit of “dbms_stats” over “analyze”?
48. Typically, where is the conventional directory structure chosen for Oracle binaries to reside?
49. You have found corruption in a tablespace that contains static tables that are part of a database that is in NOARCHIVE log mode. How would you restore the tablespace without losing new data in the other tablespaces?
50. How do you recover a datafile that has not been physically been backed up since its creation and has been deleted. Provide syntax example.

1. What is an Oracle Instance?
2. What information is stored in Control File?
3. When you start an Oracle DB which file is accessed first?
4. What is the Job of  SMON, PMON processes?
5. What is Instance Recovery?
6. What is written in Redo Log Files?
7. How do you control number of Datafiles one can have in an Oracle database?
8. How many Maximum Datafiles can there be in an Oracle Database?
9. What is a Tablespace?
10. What is the purpose of  Redo Log files?
11. Which default Database roles are created when you create a Database?
12. What is a Checkpoint?
13. Which Process reads data from Datafiles?
14. Which Process writes data in Datafiles?
15. Can you make a Datafile auto extendible. If yes, how?
16. What is a Shared Pool?
17. What is kept in the Database Buffer Cache?
18. How many maximum Redo Logfiles one can have in a Database?
19. What is difference between PFile and SPFile?
20.  What is PGA_AGGREGRATE_TARGET parameter?
1. What is an Oracle Instance?
2. What information is stored in Control File?
3. When you start an Oracle DB which file is accessed first?
4. What is the Job of  SMON, PMON processes?
5. What is Instance Recovery?
6. What is written in Redo Log Files?
7. How do you control number of Datafiles one can have in an Oracle database?
8. How many Maximum Datafiles can there be in an Oracle Database?
9. What is a Tablespace?
10. What is the purpose of  Redo Log files?
11. Which default Database roles are created when you create a Database?
12. What is a Checkpoint?
13. Which Process reads data from Datafiles?
14. Which Process writes data in Datafiles?
15. Can you make a Datafile auto extendible. If yes, how?
16. What is a Shared Pool?
17. What is kept in the Database Buffer Cache?
18. How many maximum Redo Logfiles one can have in a Database?
19. What is difference between PFile and SPFile?
20.  What is PGA_AGGREGRATE_TARGET parameter?
1. Which types of backups you can take in Oracle?

2. A database is running in NOARCHIVELOG mode then which type of backups you can take?

3. Can you take partial backups if the Database is running in NOARCHIVELOG mode?

4. Can you take Online Backups if the the database is running in NOARCHIVELOG mode?

5. How do you bring the database in ARCHIVELOG mode from NOARCHIVELOG mode?

6. You cannot shutdown the database for even some minutes, then in which mode you should run
the database?

7. Where should you place Archive logfiles, in the same disk where DB is or another disk?

8. Can you take online backup of a Control file if yes, how?

9. What is a Logical Backup?

10. Should you take the backup of Logfiles if the database is running in ARCHIVELOG mode?

11. Why do you take tablespaces in Backup mode?

12. What is the advantage of RMAN utility?

13. How RMAN improves backup time?

14. Can you take Offline backups using RMAN?

15. How do you see information about backups in RMAN?

16. What is a Recovery Catalog?

17. Should you place Recovery Catalog in the Same DB?

18. Can you use RMAN without Recovery catalog?

19. Can you take Image Backups using RMAN?

20. Can you use Backupsets created by RMAN with any other utility?

CLONING DATABASE 11G ON SAME SERVER

Clone an Oracle database using RMAN duplicate (same server)
This procedure will clone a database onto the same server using RMAN duplicate.
1. Backup the source database. To use RMAN duplicate an RMAN backup of the source database is required. If there is already one available, skip to step 2. If not, here is a quick example of how to produce an RMAN backup. This example assumes that there is no recovery catalog available:
rman target sys/Nashim#123@orcl nocatalog
backup database plus archivelog;

This will backup the database and archive logs. Default location of Backup is flashrecovery area.
2. Produce a pfile for the new database This step assumes that the source database is using a spfile. If that is not the case, simply make a copy the existing pfile. Connect to the source database as sysdba and run the following:
sql> create pfile from spfile;

This will create a new pfile in the $ORACLE_HOME/dbs directory. The file name is initorcl.ora just rename it initniit.ora. The new pfile will need to be edited immediately. If the cloned database is to have a different name to the source, this will need to be changed, as will any paths. Review the contents of the file and make alterations as necessary. Because in this example the cloned database will reside on the same machine as the source, Oracle must be told how convert the filenames during the RMAN duplicate operation. This is achieved by adding the following lines to the newly created pfile:

Here is an example where the source database orcl is being cloned to niit. Note the trailing slashes and lack of quotes:
db_file_name_convert=(/home/oracle/app/oracle/oradata/orcl/,/home/oracle/app/oracle/oradata/niit/)
log_file_name_convert=(/home/oracle/app/oracle/oradata/orcl/,/home/oracle/app/oracle/oradata/niit/)
3. Create bdump, udump & cdump directories Create bdump, udump & cdump directories as specified in the pfile from the previous step.
4. Add a new entry to oratab, and source the environment Edit the /etc/oratab (or /opt/oracle/oratab) and add an entry for the new database. Source the new environment with '. oraenv' and verify that it has worked by issuing the following command:
echo $ORACLE_SID
If this doesn't output the new database sid go back and investigate why not.  and just add ORACLE_SID=niit; export ORACLE_SID in .bash_profile
5. Create a password file Use the following command to create a password file (add an appropriate password to the end of it):
orapwd file=$ORACLE_HOME/dbs/orapwniit.ora password=Nashim#12345
6. Duplicate the database From sqlplus, start the instance up in nomount mode:
startup nomount

Exit sqlplus, start RMAN and duplicate the database. As in step 1, it is assumed that no recovery catalog is available. If one is available, simply amend the RMAN command to include it. SEE COMMAND WITH COMPLETE OUTPUT.
[oracle@oracle ~]$ rman target sys/Nashim#123@orcl nocatalog auxiliary /

Recovery Manager: Release 11.2.0.1.0 - Production on Wed Feb 20 21:07:39 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

connected to target database: ORCL (DBID=1335615098)
using target database control file instead of recovery catalog
connected to auxiliary database: NIIT (not mounted)

RMAN> duplicate target database to niit;

Starting Duplicate Db at 20-FEB-13
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK

contents of Memory Script:
{
   sql clone "create spfile from memory";
}
executing Memory Script
............................................................
......................................................

contents of Memory Script:
{
   Alter clone database open resetlogs;
}
executing Memory Script

database opened
Finished Duplicate Db at 20-FEB-13

RMAN>

This will restore the database and apply some archive logs. It can appear to hang at the end sometimes. Just give it time - I think it is because RMAN does a 'shutdown normal'. If you see the following error, it is probably due to the file_name_convert settings being wrong. Return to step 2 and double check the settings.
RMAN-05001: auxiliary filename '%s' conflicts with a file used by the target database
Once the duplicate has finished RMAN will display a message similar to this:
database opened
Finished Duplicate Db at 26-FEB-05
RMAN>
Exit RMAN.
7. Create an spfile From sqlplus:
create spfile from pfile;
shutdown immediate
startup
Now that the clone is built, we no longer need the file_name_convert settings:
alter system reset db_file_name_convert scope=spfile sid='*'
/
alter system reset log_file_name_convert scope=spfile sid='*'
/
8. Optionally take the clone database out of archive log mode RMAN will leave the cloned database in archive log mode. If archive log mode isn't required, run the following commands from sqlplus:
shutdown immediate
startup mount
alter database noarchivelog;
alter database open;
9. Configure TNS Add entries for new database in the listener.ora and tnsnames.ora as necessary.