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
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)
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
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
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
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.
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
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: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.
ORA-27123: unable to attach to shared memory segmentYou can determine the value of SHMMAX by performing the following:
# cat /proc/sys/kernel/shmmax 33554432
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/
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.