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.