Interview question for oracle DBA

INTERVIEW QUESTION FOR ORACLE DBA


for UNIX

1. How do you see how many instances are running?-
2. How do you automate starting and shutting down of databases in Unix?
3. You have written a script to take backups. How do you make it run automatically every week?
4. What is OERR utility?
5. How do you see Virtual Memory Statistics in Linux?
6. How do you see how much hard disk space is free in Linux? -
7. What is SAR? http://www.ibm.com/developerworks/aix/library/au-
8. What is SHMMAX?
9. Swap partition must be how much the size of RAM?
10. What is DISM in Solaris?
11. How do you see how many memory segments are acquired by Oracle Instances?
12. How do you see which segment belongs to which database instances?
13. What is VMSTAT? See Question no 5
14. How do you set Kernel Parameters in Red Hat Linux, AIX and Solaris?
15. How do you remove Memory segments?
16. What is the difference between Soft Link and Hard Link?
17. What is stored in oratab file? -->
18. How do you see how many processes are running in Unix?
19. How do you kill a process in Unix?
20. Can you change priority of a Process in Unix?




oracle question


what is sga?
diff bw hot backup & coldbackup
diff bw 9i & 10g features
what is rac and how it is faster then other database
what is temp tablepspace? how will you add size in temp tbsp?
RELM
which backup you have in your organisation?
export & import
tell about ur project
what Performance Tuning you have handled for ur project?
what is shared pool?
tablespace concepts
have u handled any db crash? if so how u rectified?
you lost control file? so how do u recover the file without backup?


what is tbsp?
what is partitions?
how do u find which sessions are connected to the db?
which 2 network file have been used while creating a database?
what is triggers? have u used in ur project?
tell abt ur project
diff bw temp tbsp and permanent tbsp
what is the tool / utility u have used in your orgn?
tell abt logical backup and what backup u did for ur orgn?
what is sql * loader?
performance tuning
constraints , diff bw primary key and unique
how do u resize and add the datafile?
what is oracle precompiler?


what is collections
select * from emp
where empno=<******>
group by <*****>
orderby <******>;
what this above command does internally in the oracle?
psedocolumns
diff bw function and procedure
merge cmd
have u used functions, procedures and triggers in ur project?
how do u analyse diff types of functions in a table?
what is sysnonyms and types



tell abt urself
have u done cloning
tell abt hot backup and steps
Hve u done performance tuning work?
one question was based on AWR report.. but i am not sure what it was
how do u rename a database
while importing a table, can u send datas from one user to another use?
list 10 parameters in pfile

EXPORT AND IMPORT USING DATAPUMP

C:\Documents and Settings\Chetan>sqlplus

SQL*Plus: Release 10.2.0.3.0 - Production on Fri Jul 30 11:55:29 2010

Copyright (c) 1982, 2006, Oracle. All Rights Reserved.

Enter user-name: sys as sysdba

Enter password:

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options

SQL> select name from v$database;

NAME

---------

ORA9

SQL> create directory data_p_test as 'c;\data_p_test';

Directory created.

Create manually a directory in the same location with same name ..

SQL> grant read ,write on directory data_p_test to scott;

Grant succeeded.

SQL> select * from dba_directories;

to check you directory is created or not.........................

SQL> grant exp_full_database to scott;

Grant succeeded.

SQL> exit

Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Production

With the Partitioning, OLAP and Data Mining options.

C:\Documents and Settings\Chetan>expdp scott/tiger@ora9 full=y directory=data_p_test dumpfile=full.dmp logfile=full.log

Now connect with your new database where u wanna import .

my database name is "newtest"

SQL> create directory data_p_test as 'c:\data_p_test';

Directory created.

SQL> grant read ,write on directory data_p_test to scott;

Grant succeeded.

SQL> grant imp_full_database to scott;

Grant succeeded.

SQL> alter tablespace temp add tempfile 'tempfilelocation' size 200M autoextend on next 30M;

Come on your command prompt

C:\Documents and Settings\Chetan>impdp scott/tiger@newtest  full=y directory=data_p_test dumpfile=full.dmp logfile=full2.log

for export and import table  use all above command and just use the blow command for imp and exp

 export
SQL>expdp scott/tiger@new test tables=CHETAN directory=table_test dumpfile=table.dmp logfile=tableimp.log;

import

SQL>impdp scott/tiger@ora9 tables=CHETAN directory=table_test dumpfile=table.dmp logfile=tableimp2.log;

any mistake is there or u wanna give any suggestion so please write to me ............

how to create a controlfile

CREATE CONTROLFILE SET DATABASE "fsdemo" RESETLOGS FORCE LOGGING NOARCHIVELOG
MAXLOGFILES 50
MAXLOGMEMBERS 5
MAXDATAFILES 100
MAXINSTANCES 1
MAXLOGHISTORY 453
LOGFILE
GROUP 1 'E:\oracle\product\10.2.0\oradata\fsdemo\redo01.log' SIZE 200M,
GROUP 2 'E:\oracle\product\10.2.0\oradata\fsdemo\redo02.log' SIZE 200M,
GROUP 3 'E:\oracle\product\10.2.0\oradata\fsdemo\redo03.log' SIZE 200M
DATAFILE
'E:\oracle\product\10.2.0\oradata\fsdemo\system01.dbf',
'E:\oracle\product\10.2.0\oradata\fsdemo\undotbs01.dbf',
'E:\oracle\product\10.2.0\oradata\fsdemo\users01.dbf'
CHARACTER SET WE8ISO8859P1;

"fsdemo" is the  database name for which u wanna create controlfile

"E:\oracle\product\10.2.0\oradata\fsdemo\" this is the path where your logfile and datafile are store .
just past this file and edit path and name . and save it as "control.sql' in notepad
run this file from sql promt
SQL>@control.sql

remember your database should be in no mount stage  for creating controlfile

any mistake is there or u wanna give any suggestion so please write to me ............

RENAME DATABASE AND INSTACE

RENAME DATABASE AND INSTACE

Let suppose  your db name is TEST and wanna change your db to NEWTEST

C:\> set ORACLE_SID=TEST

C:\> sqlplus  sys/sys as sysdba

SQL>select name from v$database;

Name

----------

TEST

SQL>alter database backup controlfile to trace as 'c:\c.txt' reuse;

SQL> shutdown immediate;

copy and past your old controlfile to another loction and edit your INITTEST.ora in this change your  "db_name=NEWTEST"

SQL>startup nomount pfile='D:\oracle\product\10.2.0\db_1\database\inittest.ora'

(the path where you save your pfile )

now create the new control file ..........

SQL>alter database open resetlogs;

SQL>select name from v$database;

Name

----------

NEWTEST

****this will change only your db name not instance name 

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

TO CHANGE  INSTANCE  INSTANCE NAME 

SQL> show parameter db_name;

NAME                                 TYPE        VALUE

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

db_name                              string           newtest

SQL>show parameter instance_name;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------------
instance_name                        string      test 

if we are using spfile  we need to do ........

SQL> alter system reset instance_name scope= spfile sid ='*";

SQL> startup force ; 

BUT If we are using PFILE then....

In this case:
 remove instance_name parameter from PFILE

 shutdown/restart instance 

c:\> set ORACLE_SID=NEWTEST

if instance is  running in LINUX then every thing is done .

But in windows we need to crate through ORADIM

for ORADIM

create new instance -

c:\>E:\oracle\product\10.2.0\db_1\bin\oradim -new -sid NEWTEST -SRVC OracleServiceNEWTEST 

    -STARTMODE auto -SRVCSTART system -PFILE

                 e:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INITTEST.ORA

         

 "    e:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INITTEST.ORA"  this is the path where your pfile is saved.

    This will create a new instance .

Now delete your old instance 

c:\>E:\oracle\product\10.2.0\db_1\bin\oradim -DELETE -SID TEST

Until you did not delete your old instance  you always got error so first delete your old instance and then check your db name and instance name ...

any mistake is there or u wanna give any suggestion so please write to me ............

HOT BACKUP

set your archive log on

SQL> shutdown abort ;

SQL> startup mount;

SQL>alter database archivelog;

you got an  error cose  we use shutdown abort so never use shutdown abort if u wanna set archive log
SQL>shutdown immediate ;

SQL>startup mount ;

SQL >alter database archivelog;

SQL>alter database open;

SQL>alter system switch logfile;

SQL>exit

SQL>rman target/

RMAN> backup database plus archivelog;


done

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

Restore database 

SQL >  SHUTDOWN

SQL > EXIT

C:/>RMAN TARGET /

RMAN>   ALTER DATABASE  NOMOUNT ;

RMAN>  RESTORE CONTROLFILE FROM  'D:\BACKUP\C-33354'

** 'D:\BACKUP\C-33354' this is the path where u store ur backup .and C-33354 this the controll file use ur contoll file name .

controll file name always start with 'c-'

RMAN>ALTER DATABASE MOUNT ;

RMAN>RESTORE DATABASE;

RMAN> RECOVER DATABASE;

RMAN>ALTER DATABASE OPEN RESETLOGS;

any mistake is there or u wanna give any suggestion so please write to me ............

cold backup database

Cold Backup using RMAN

Database should be in archive mode
check the database is up or not

c:\> RMAN TARGET /

RMAN:\> SHOW ALL ;

to check configuration of RMAN  . Configure three things in this configuration

RMAN:\>  CONFIGURE CONTROLFILE AUTOBACKUP ON;

RMAN:\>  CONFIGURE DEVICE TYPE DISK FORMAT   'D:\BACKUP\%F';

** this is the path where u wanna take backup

RMAN:\>  CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT 'D:\BACKUP\%U';

RMAN:\>  SHUTDOWN IMMEDIATE ;

RMAN:\>  STARTUP MOUNT;

RMAN:\>  BACKUP DATABASE;

RMAN:\>  ALTER DATABASE OPEN;

--------------------------------------------------------------------------------------------------------
Restore database 



SQL >  SHUTDOWN

SQL > EXIT

C:/>RMAN TARGET /

RMAN>   ALTER DATABASE  NOMOUNT ;

RMAN>  RESTORE CONTROLFILE FROM  'D:\BACKUP\C-33354'

** 'D:\BACKUP\C-33354' this is the path where u store ur backup .and C-33354 this the controll file use ur contoll file name .

controll file name always start with 'c-'

RMAN>ALTER DATABASE MOUNT ;

RMAN>RESTORE DATABASE;

RMAN> RECOVER DATABASE;

RMAN>ALTER DATABASE OPEN RESETLOGS;





any mistake is there or u wanna give any suggestion so please write to me ............

CHECK POINTS

SOME POINTS ON CHECK POINTS

Incremental Checkpoint: Incremental checkpointing improves the performance of crash and instance recovery (but not media recovery). An incremental checkpoint records the position in the redo thread (log) from which crash/instance recovery needs to begin. This log position is determined by the oldest dirty buffer in the buffer cache. The incremental checkpoint information is maintained periodically with minimal or no overhead during normal processing.

Partial Checkpoint: When backup of a tablespace in an open database begins, a partial checkpoint is performed by every instance for the datafiles in that tablespace

Full Checkpoint: In a full checkpoint, the database server flushes all modified pages in the shared-memory buffer pool to disk. When a full checkpoint completes, all physical operations are complete, the MLRU queue is empty, and the database server is said to be physically consistent.

Fast_Start_MTTR_Target cause database checkpoint to occur whenever the expected time to recover the instance exceeds 5 seconds. So just check out your database for it.

any mistake is there or u wanna give any suggestion so please write to me ............