General ORACLE DBA interview Question

1. What is an Oracle Instance?
    An instance is the (executed) Oracle software (Oracle processes) and the memory they use (SGA).             It  is  the instance that manipulates the data stored in the database.

2. What information is stored in Control File?
The database name
The timestamp of database creation
  The names and locations of associated datafiles and redo log files
  Tablespace information
  Datafile offline ranges
  The log history
  Archived log information
  Backup set and backup piece information
  Backup datafile and redo log information
  Datafile copy information
  The current log sequence number

3. When you start an Oracle DB which file is accessed first?
Oracle first open and reads the initialization parameter file (init.ora).
   


4. What is the Job of SMON, PMON processes?
PMON—It cleaned up the failed processes BY
>> Rolling back the transaction
>> Releasing all currently held table  or row  locks
>> freeing other resource currently held by the user
>>Restart the dead dispatcher

SMON—if the oracle instance fails , any information that in SGA that has not been written  to the disk is cost .
>> the background process smon automatic perform instance recovery when the database is reopened.
>>Instance recovery
•    Roll forward changes in the redolog
•    Open the database for user access
•    Roll back the uncommitted transaction also perform
•    It combines or coalesces, adjacent area of free space in the datafile .
•    Dislocate the temporary segment  

5. What is Instance Recovery?


 Instance recovery
•    Roll forward changes in the redolog
•    Open the database for user access
•    Roll back the uncommitted transaction also perform
•    It combines or coalesces, adjacent area of free space in the datafile .
•    Dislocate the temporary segment 
 
6. What is written in Redo Log Files?

# Log writer (LGWR) writes redo log buffer contents Into Redo Log FIles. Log writer does this every three seconds, when the redo log buffer is 1/3 full and immediately before the Database Writer (DBWn) writes its changed buffers into the datafile. 






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?

In a database there can have maximum 65533 data files.
 
9. What is a Tablespace?

A tablespace is a logical group of data files in a database. A database typically contains at least one tablespace, and usually two or more. Within the database, the tablespace plays a role similar to that of a folder on the hard drive of a computer.

Some database programs, such as Oracle, automatically create a tablespace called SYSTEM that contains general information about the structure and contents of the database. A small database can be entirely contained in the SYSTEM tablespace, but in most cases, user datais placed in other tablespaces.

There is no relationship between a tablespace and a schema. Objects in the same schema can be in different tablespaces, and a tablespace can hold objects from different schemas.
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?

21. Large Pool is used for what?

22. What is PCT Increase setting?

23. What is PCTFREE and PCTUSED Setting?

24. What is Row Migration and Row Chaining?

25. What is 01555 - Snapshot Too Old error and how do you avoid it?

26. What is a Locally Managed Tablespace?

27. Can you audit SELECT statements?

28. What does DBMS_FGA package do?

29. What is Cost Based Optimization?

30. How often you should collect statistics for a table?

31. How do you collect statistics for a table, schema and Database?

32. Can you make collection of Statistics for tables automatic?

33. On which columns you should create Indexes?

34. What type of Indexes are available in Oracle?

35. What is B-Tree Index?

36. A table is having few rows, should you create indexes on this table?

37. A Column is having many repeated values which type of index you should create on this
column, if you have to?

38. When should you rebuilt indexes?

39. Can you built indexes online?

40. Can you see Execution Plan of a statement.

41. A table is created with the following setting 
storage (initial 200k next 200k minextents 2 maxextents 100 pctincrease

40) What will be size of 4th extent?

42. What is DB Buffer Cache Advisor?

43. What is STATSPACK tool?

44. Can you change SHARED_POOL_SIZE online?

45. Can you Redefine a table Online?

46. Can you assign Priority to users?

47. You want users to change their passwords every 2 months. How do you enforce this?

48. How do you delete duplicate rows in a table?

49. What is Automatic Management of Segment Space setting?

50. What is the difference between DELETE and TRUNCATE statements?

51. What is COMPRESS and CONSISTENT setting in EXPORT utility?

52. What is the difference between Direct Path and Convention Path loading?

53. Can you disable and enable Primary key?

54. What is an Index Organized Table?

55. What is a Global Index and Local Index?

56. What is the difference between Range Partitioning and Hash Partitioning?

57. What is difference between Multithreaded/Shared Server and Dedicated Server?

58. Can you import objects from Oracle ver. 9i to 10g?

59. How do you move tables from one tablespace to another tablespace?

60. How do see how much space is used and free in a tablespace?




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

how to change sys password in Oracle 10g

 To Change the ""SYS"  Password

SQL> conn sys as sysdba
Enter password: (null) no need to give anything
Connected.
SQL> passw system
Changing password for system
New password: (new password)
Retype new password: (new password)
Password changed
SQL> conn system/(new password) 
Connected.
SQL> passw sys
Changing password for sys
New password: (new sys password )
Retype new password: (new sys password )
Password changed
SQL>




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

MANAGE USER

HOW To create USER
SQL>create user chetan
  2  identified by c
  3  default tablsespace SYSTEM
  4  temporary tablespace TEMP
  5  quota 20 m on SYSTEM
  6  password expire;
-------------

change system tablespace 

SQL> create user chetan
  2  identified by c
  3  default tablespace EXAMPLE
  4  temporary tablespace TEMP
  5  quota 15m on EXAMPLE
  6  password expire;

User created.

now its created ...


TO see the session privilege

SQL>select * from session_privs;

PRIVILEGE
----------------------------------------
CREATE SESSION

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


How to create Profile

______________________________
CREATION OF PROFILE
______________________________
create profile  profile_name limit
  sessions_per_user                 2   --
  cpu_per_session               10000   -- hunderth of seconds
  cpu_per_call                      1   -- hunderth of seconds
  connect_time              unlimited   -- minutes
  idle_time                        30   -- minutes
  logical_reads_per_session   default   -- db blocks
  logical_reads_per_call      default   -- db blocks
  -- composite_limit          default   --
  private_sga                     20M   --
  failed_login_attempts             3   --
  password_life_time               30   -- days
  password_reuse_time              12   --
  password_reuse_max        unlimited   --
  password_lock_time          default   -- days
  password_grace_time               2   -- days
  password_verify_function       null;



EXAMPLE Is blow


SQL >create profile ram limit
    sessions_per_user 2
    connect_time 30
    idle_time 30
    failed_login_attempts 4
    password_life_time 2
    password_reuse_time 5
    password_grace_time 1;
    
 Profile created.

-----------


how to see a profile

SQL> show user
USER is "SYS"

SQL> desc dba_profiles
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PROFILE                                   NOT NULL VARCHAR2(30)
 RESOURCE_NAME                             NOT NULL VARCHAR2(32)
 RESOURCE_TYPE                                      VARCHAR2(8)
 LIMIT                                              VARCHAR2(40)

SQL> select profile from dba_profiles;

PROFILE
------------------------------
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT

PROFILE
------------------------------
DEFAULT
DEFAULT
DEFAULT
DEFAULT
DEFAULT
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE

PROFILE
------------------------------
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
MONITORING_PROFILE
RAM

PROFILE
------------------------------
RAM
RAM
RAM
RAM
RAM
RAM
RAM
RAM
RAM
RAM
RAM

PROFILE
------------------------------
RAM
RAM
RAM
RAM

48 rows selected.
---------------------------------------------------------------------------
SQL> select * from dba_profiles where profile='RAM';

PROFILE                        RESOURCE_NAME                    RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
RAM                            COMPOSITE_LIMIT                  KERNEL
DEFAULT

RAM                            SESSIONS_PER_USER                KERNEL
2

RAM                            CPU_PER_SESSION                  KERNEL
DEFAULT


PROFILE                        RESOURCE_NAME                    RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
RAM                            CPU_PER_CALL                     KERNEL
DEFAULT

RAM                            LOGICAL_READS_PER_SESSION        KERNEL
DEFAULT

RAM                            LOGICAL_READS_PER_CALL           KERNEL
DEFAULT


PROFILE                        RESOURCE_NAME                    RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
RAM                            IDLE_TIME                        KERNEL
30

RAM                            CONNECT_TIME                     KERNEL
30

RAM                            PRIVATE_SGA                      KERNEL
DEFAULT


PROFILE                        RESOURCE_NAME                    RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
RAM                            FAILED_LOGIN_ATTEMPTS            PASSWORD
4

RAM                            PASSWORD_LIFE_TIME               PASSWORD
2

RAM                            PASSWORD_REUSE_TIME              PASSWORD
5


PROFILE                        RESOURCE_NAME                    RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
RAM                            PASSWORD_REUSE_MAX               PASSWORD
DEFAULT

RAM                            PASSWORD_VERIFY_FUNCTION         PASSWORD
DEFAULT

RAM                            PASSWORD_LOCK_TIME               PASSWORD
DEFAULT


PROFILE                        RESOURCE_NAME                    RESOURCE
------------------------------ -------------------------------- --------
LIMIT
----------------------------------------
RAM                            PASSWORD_GRACE_TIME              PASSWORD
1


16 rows selected.
-----------------------------------------







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

Question and answer's

what is sga?

The SGA (System Global Area) is an area of memory (RAM) allocated when an Oracle Instance starts up. The SGA's size and function are controlled by initialization (INIT.ORA or SPFILE) parameters.

SQL> SHOW SGA
Total System Global Area  638670568 bytes
Fixed Size                   456424 bytes
Variable Size             503316480 bytes
Database Buffers          134217728 bytes
Redo Buffers                 679936 bytes
SQL> SELECT * FROM v$sga;
NAME                      VALUE
-------------------- ----------
Fixed Size               456424
Variable Size         503316480
Database Buffers      134217728
Redo Buffers             679936

SQL> select * from v$sgainfo;

NAME                             BYTES                  RESIZEABLE 
-------------------------------- ---------------------- ---------- 
Fixed SGA Size                   2109352                No   
Redo Buffers                     13533184               No    
Buffer Cache Size                3103784960             Yes 
Shared Pool Size                 822083584              Yes  
Large Pool Size                  67108864               Yes 
Java Pool Size                   134217728              Yes  
Streams Pool Size                134217728              Yes 
Shared IO Pool Size              0                      Yes 
Granule Size                     16777216               No 
Maximum SGA Size                 4277059584             No 
Startup overhead in Shared Pool  251658240              No 
Free SGA Memory Available        0      

what is difference between 9i and 10g

New feature

Completely reworked 10g Enterprise Manager (OEM)

AWR and ASH tables incorporated into OEM Performance Pack and Diagnostic Pack options

Automated Session History (ASH) materializes the Oracle Wait Interface over time

Data Pump replaces imp utility with impdp

Automatic Database Diagnostic Monitor (ADDM)

Automatic Storage Management (ASM) introduced Stripe And Mirror Everywhere (SAME) standard

Automatic Workload Repository (AWR) replaces STATSPACK

SQLTuning Advisor

SQLAccess Advisor

Rolling database upgrades (using Oracle10g RAC) 

Ability to UNDROP a table from a recycle bin 

Ability to rename tablespaces 

Ability to transport tablespaces across machine types (E.g Windows to Unix) 

Support for bigfile tablespaces that is up to 8 Exabytes in size 

RMAN introduces compression for backups

Flashback enhancements for flashback database and flashback table syntax 
New commands

Set Database Default Tablespace syntax

Rename Tablespace command

New drop database syntax

New alter database begin backup syntax
Tablespaces 

SYSAUX tablespace
ysaux, system, Transportable tablespaces, Bigfile tablespaces
Smallfile tablespaces
Default tablespaces (temp), Displaying tablespaces (dba_tablespaces)

Multiple Temporary Tablespaces supported to reduce stress on sorting in TEMP

Moreover, 10g has additional 149 features than 9i

what is RAC ?

Oracle RAC allows multiple computers to run Oracle RDBMS software simultaneously while accessing a single database, thus providing a clustered database.

In a non-RAC Oracle database, a single instance accesses a single database. The database consists of a collection of data files, control files, and  located on disk. The instance comprises the collection of Oracle-related memory and operating system processes thredo logsat run on a computer system.

In an Oracle RAC environment, two or more computers (each with an instance) concurrently access a single database. This allows an application or user to connect to either computer and have access to a single coordinated set of data.

what is temp tablespace ? how will you add size in temp tablespace?

Temporary tablespaces are used to manage space for database sort operations and for storing global temporary tables. For example, if you join two large tables, and Oracle cannot do the sort in memory (see SORT_AREA_SIZE initialisation parameter), space will be allocated in a temporary tablespace for doing the sort operation. Other SQL operations that might require disk sorting are: CREATE INDEX, ANALYZE, Select DISTINCT, ORDER BY, GROUP BY, UNION, INTERSECT, MINUS, Sort-Merge joins, etc.


When we create a TEMPFILE, Oracle only writes to the header and last block of the file. This is why it is much quicker to create a TEMPFILE than to create a normal database file.

The DBA should assign a temporary tablespace to each user in the database to prevent them from allocating sort space in the SYSTEM tablespace. This can be done with one of the following commands:

SQL> CREATE USER scott DEFAULT TABLESPACE data TEMPORARY TABLESPACE temp;
SQL> ALTER USER scott TEMPORARY TABLESPACE temp;
SQL> select TABLESPACE_NAME, BYTES_USED, BYTES_FREE from V$TEMP_SPACE_HEADER;

Several methods exist to reclaim the used space   for a larger than normal temporary tablespace depending on which release of Oracle you are running. The method that exists for all releases of Oracle is to simply drop and recreate the temporary tablespace back to its original (or another reasonable) size. If you are using Oracle9i or higher, you can apply another method which is to drop the large tempfile (which will drop the tempfile from the data dictionary AND the O/S file system) using the alter database tempfile '' drop including datafiles  


what is shared pool?
The shared pool is used for objects that are shared among all users. For example: table definitions, PL/SQL definitions, cursors and so on.
The shared pool can further be subdivied into:
Control structures
Character sets
Dictionary cache
The dictionary cache stores parts fo the data dictionary because Oracle has to query the data dictionary very often as it is fundamental to the functioning of Oracle.
Library cache
The library cache is further divided into
Shared SQL Area
PL/SQL Procedures
Control Structures 

select    name, bytes/1024/1024 "MB"
from    v$sgastat
where    pool = 'shared pool'
order by bytes desc;

what is tablespace concepts?

Database is divided into one or more logical storage units known as table space. The table space is created by database administrator. After the table space gets created by administrator tables can be created by users in their table space in other words tables can be created in the table space provided for the users .

There are three types of tablespaces in Oracle:

• Permanent tablespaces
• Undo tablespaces
• temporary tablespaces

what is triggers?

Oracle lets you define procedures called triggers that run implicitly when an INSERT, UPDATE, or DELETE statement is issued against the associated table or, in some cases, against a view, or when database system actions occur. These procedures can be written in PL/SQL or Java and stored in the database.

Triggers are similar to stored procedures. A trigger stored in the database can include SQL and PL/SQL or Java statements to run as a unit and can invoke stored procedures. However, procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger. Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.

how to create trigger ...

create trigger trigger-name before event

create trigger trigger-name after event

create trigger trigger-name instead of event

Difference  between temporary  tablespace and permanent tablespace

Temporary Tablespace is in generally used for sorting 
purpose while Permanent tablespace is used to sore 
permanent objects


what is logical backup ?
Logical backup is taking by the use of Exp /expdb utility .

 what is oracle precompiler?
The Oracle Precompilers are used to process C, COBOL, or PL/I programs before passing them to their native compilers.  

Oracle Precompilers translate embedded SQL statements in the programs into the appropriate native language statements and calls necessary to access the Oracle9i database server.
how do u resize and add the datafile?
to resize data file 
alter databse datafile 'location of datafile' resize 2000M
to add the data file
alter tablespace tablspace_name 
  add datafile 'datafile_location' size 4M autoextend off;

how to take table space backup ?
we can take tablespace backup through data Pump .
check below link for details of data pump.
http://chetanyadavds.blogspot.com/2010/08/export-and-import-using-datapump.html

Ques.:  What is SHMMAX & Semaphores ?

The SHMMAX parameter is used to define the maximum size (in bytes) for a shared memory segment and should be set large enough for the largest SGA size. If the SHMMAX is set incorrectly (too low), it is possible that the Oracle SGA (which is held in shared segments) may be limited in size. An inadequate SHMMAX setting would result in the following:

ORA-27123: unable to attach to shared memory segment

You can determine the value of SHMMAX by performing the following:

# cat /proc/sys/kernel/shmmax
33554432

 A semaphore can be thought of as a counter that is used to control access to a shared resource. Semaphores provide low level synchronization between processes (or threads within a process) so that only one process (or thread) has access to the shared segment, thereby ensureing the integrity of that shared resource.


QUESTION:     Which process starts the RMAN in Oracle 

We initiate RMAN. RMAN is basically a utility. We invoke rman by executing the rman binary executable ($ORACLE_HOME/bin) from the operating system prompt. So, when you break it down, users start rman. When you further break it down, it comes down to "User Process".

Phase-1: When we invoke rman, oracle creates a "user process" to run/start it as below:

a) $ <$ORACLE_HOME/bin/rman

b) RMAN>


Phase-2: When rman is started and connected to target database, 2 server processes are spawned, by default. That is, when you attempt to connect to the target database (using "connect target /"), RMAN checks the parameter, $ORACLE_SID, and invokes a server process. On successful authentication, RMAN would connect you to the target database with 'sysdba' privilege. RMAN would then immediately invoke another server process (some people call it "channel process") and this will be used to perform actions. However, more than 1 channel process may also be started if you allocate more channels (by overriding the default settings)

c) RMAN> connect target /

Note: You can merge phase-1 and phase-2 by using:

d) $ rman target /


In the interviews, as you do not get sufficient time to explain all this, you can say that "user process" will invoke RMAN and RMAN, in turn, invokes "server process(es)" on establishing a connection with target database. However, to be politically correct, when we run rman executable, user process is started. When we subsequently connect to target database, server process/(es) is/(are) started.  

QUESTION : Can we use rman on a shared connection to server ?
Is the server process started when we connect to target database or when a backup begins or when a channel is allocated ?

only Dedicated server process can be used for RMAN. If you use a shared server configuration, you MUST modify the tns entries of your database to use the clause, "SERVER=dedicated". Set up a new net service name (for example, set 'xxxx_new' for 'xxxx') and Ensure that you use the SAME service name. When you use this clause, you force the listener to spawn a dedicated server process.

Example:

xxxx_new =
(DESCRIPTION=
(ADDRESS= ................)
(CONNECT_DATA=(SERVICE_NAME=xxxx) (SERVER=dedicated))
)

Then, connect to RMAN as:

$ <$ORACLE_HOME>/bin/rman

RMAN> connect target sys/@xxxx_new 
To answer you second question, when you connect to the target database, RMAN spawns 2 server processes, by default, as explained in my previous day's comment.

The 1st server process checks compatibility, executes queries against controlfile and performs resync operations. This process also executes sql statements issued from rman prompt. There can only be 1 such server process. If you notice any performance issues related to oracle layers, you have to analyze this process.

2nd server process monitors the activities being carried out by the channels. When you issue any new operation via rman prompt, this process will determine if the previous activities were completed and only then the new operations are permitted. Even in this case, there can only be 1 such 2nd server process per instance. This process is very quick and does not cause any performance issue(s). By the way, in very rare cases, if you use different connect strings while MANUALLY allocating channels (using "allocate channel" command) additional 2nd server processes will be spawned by RMAN. I have intentionally called this process as "2nd server process", in order to distinguish it from the 1st server process.

In addition, when you issue a command to perform backup or recovery operations, RMAN spawns another server process or server session. This process is also called 'target connection'. By default only 1 target connection is allocated. If you allocate multiple channels MANUALLY, more than 1 target connection (also known as server process or server session) will be spawned, where each server process or target connection or server session corresponds to each of the allocated channels. If you notice any performance issues related to the media manager, you have to track down and analyze this process, i.e. track the server session corresponding to the channels. You can find this by joining v$session and v$process and querying sid, spid and client_info.   

what is the difference between database refresh and database cloning

 

Answer :_Database refresh is normally done for Test/DEV databases during development and test phases of App Development, where latest production cut of database is taken and applied on these environments.

Cloning is creating replica of already existing databases on the same/different target hosts. Cloning doesn't necessarily involve copying Oracle Home. Database can also be cloned on the same machine.

install oracle on Oracle Enterprise Linux (OEL)

THIS video is created by MR. Kamran Agayev A.