ORACLE Q & A

ORACLE Q & A

1. Explain shared memory & semaphore?

Shared memory and semaphore:

            Shared memory and semaphores are two important resources for an Oracle instance on linux. An instance cannot start if it is unable to allocate what it needs.

Shared memory:

            Shared memory is exactly that - a memory region that can shared between different processes. Oracle uses shared memory for implementing the SGA, which needs to be visible to all database sessions.

Semaphore:

            Semaphores can be thought of as flags. They are either on or off. A process can turn on the flag or turn it off. If the flag is already on, processes who try to turn on the flag will sleep until the flag is off. Upon awakening, the process will reattempt to turn the flag on, possibly suceeding or possibly sleeping again.

Allocation:

            Shared memory required by the Oracle Instance : On instance startup, the first things that the instance does is: -Read the "init.ora" -Start the background processes -Allocate the shared memory and semaphores required The size of the SGA will be calculated from various "init.ora" parameters. This will be the amount of shared memory required. The SGA is broken into 4 sections - the fixed portion, which is constant in size, the variable portion, which varies in size depending on "init.ora" parameters, the redo block buffer, which has its size controlled by log_buffers, and the db block buffer, which has its size controlled by db_block_buffers. The size of the SGA is the sum of the sizes of the 4 portions. There is unfortunately no simple ormula for determining the size of the variable portion.

Generally, the shared pool dominates all other parts of the variable portion, so as a rule of thumb, one can estimate the size as the value of shared_pool_size.

The number of semaphores required is much simpler to determine.
Oracle will need exactly as many semaphores as the value of the processes "init.ora" parameter.

SHARED MEMORY ALLOCATION

1. One-segment

2. Contigous multi-segment

3. Non-contigous multi-segment

1. One-segment:-

 The one-segment model is the simplest and first model tried. In this model, the SGA resides in only one shared memory segment. Oracle attempts to allocate and attach one shared memory segement of size equal to total size of the SGA.

With multiple segments there are two possibilities. The segments can be attached contiguously, so that it appears to be one large shared memory segment, or non-contiguously, with gaps between the segments.

2. Contigous multi-segment:-

In the contiguous segment model, Oracle simply divides the SGA into SGASIZE/SHMMAX (rounded down) segments of size SHMMAX plus another segment of size SGASIZE modulo SHMMAX

3. Non- contigous multi-segment:- Once the number of segments and their sizes is determined, Oracle then allocates and attaches the segments one at a time; first the fixed and variable portion segment(s), then the redo block buffer segment(s), then the db block buffer segment(s). They will be attached non-contiguously,
At this point, we have either attached the entire SGA or returned an ORA error. The total size of segments attached is exactly SGASIZE; no space is wasted. Once Oracle has the shared memory attached, Oracle proceeds to allocating the semaphores it requires.

kernel parameters for Shared memory:

SHMMIN= min size of shared memory segment=1

SHMMNI= max number of shared memory identifiers on system = 100

SHMSEG= max number of shared memory segments per process = 10

max Sga that can be created by the one segment model is SHMMAX*SHMSEG

You can display the current kernel parameters by doing a "sysdef -i"

SEMAPHORE ALLOCATION

Oracle just needs to allocate a number of semaphores equal to the processes parameter in "init.ora".

SEMMSL= # of semaphores in a semaphore set

SEMMNI= the maximum # of semaphores sets in the system

SEMMNS= the number of semaphores in the system.

SEMOPM= max number of operations per semop call = 100

SEMVMX = semaphore max value = 32767

When an Oracle instance is started, all required semaphores will be allocated. Semaphores are allocated in sets.

Since each oracle process* requires a semaphore, the number that is allocated is equal to the value of the init.ora parameter PROCESSES. The total # of semaphores required is the sum of all your instance's PROCESSES.

Example commands to check shared memory and semaphore

oracle@sample ~]$ su

Password:

[root@sample oracle]# cd

[root@sample ~]# ipcs -b

------ Message Queues --------

key        msqid      owner      perms      used-bytes   messages   

------ Shared Memory Segments --------

key        shmid      owner      perms      bytes      nattch     status     

0x00000000 294912     oracle     777        16384      1          dest        

0x00000000 327681     oracle     777        3932160    2          dest        

0x00000000 589826     oracle     600        524288     2          dest        

------ Semaphore Arrays --------

key        semid      owner      perms      nsems    

0x5c09307c 2064384    oracle     600        250      

0x5c09307d 2097153    oracle     600        250      

 [root@sample ~]# free -m

total        used        free      shared  buff/cache   available

Mem:           3157         718         256        1388        2183         946

Swap:          8999         546        8453

 

[root@sample ~]# ipcs -mu

------ Shared Memory Status --------

segments allocated 154

pages allocated 22699

pages resident  18411

pages swapped   454

Swap performance: 0 attempts           0 successes

***************************************************************************

2. How to install RPM ?

RPM stands for Red Hat Package Manager. It was developed by Red Hat and is primarily used on Red Hat-based Linux operating systems (Fedora, CentOS, RHEL, etc.).

An RPM package uses the .rpm extension and is a bundle (a collection) of different files. It can contain the following:

• Binary files, also known as executables (nmap, stat, xattr, ssh, sshd, etc.).
• Configuration files (sshd.conf, updatedb.conf, logrotate.conf, etc.).
• Documentation files (README, TODO, AUTHOR, etc.).

Check rpm is installed or not:

[root@sample ~]# rpm –qa

Using this root command on linux rpm –qa to ckeck weather rpm has been installed or not

Sample installed packages

dyninst-9.3.1-2.el7.x86_64

python2-futures-3.1.1-5.el7.noarch

qemu-kvm-common-1.5.3-160.el7.x86_64

python-pillow-2.0.0-21.gitd1c6db8.el7.x86_64

shared-mime-info-1.8-4.el7.x86_64

realmd-0.16.1-11.el7.x86_64

usbutils-007-5.el7.x86_64

List all files installed by the RPM package:

[root@sample ~]# rpm -ql iptables

/etc/sysconfig/ip6tables-config

/etc/sysconfig/iptables-config

/usr/bin/iptables-xml                                

/usr/lib64/libip4tc.so.0

/usr/lib64/libip4tc.so.0.1.0

/usr/lib64/libip6tc.so.0

/usr/lib64/libip6tc.so.0.1.0

/usr/lib64/libiptc.so.0

Following command is used to install or upgrade rpm packages;

The general form of an rpm install command is

 

    rpm {-i|--install} [install-options] PACKAGE_FILE ...

 

    This installs a new package.

 

    The general form of an rpm upgrade command is

 

    rpm {-U|--upgrade} [install-options] PACKAGE_FILE ...

 

rpm install steps:

 

1.      Log in as root, or use the su command to change to the root user at the workstation on which you want to install the software.

2.      Download the package you wish to install. The package will be named something like DeathStar0_42b.rpm.

3.      To install the package, enter the following command at the prompt

rpm -i DeathStar0_42b.rpm

Download rpm latest packages on https://rpm.org/ website

**************************************************************************

 

3. How to install DB using response file?

Step 1.

To create a database in silent mode, we will use a response file, however its not mandatory as we can pass all the arguments in command line to create database. A response file consists of different values which are basically required for database installation. The location of response file is $ORACLE_HOME/assistants/dbca

Add the following arguments response file . The arguments I have passed here as based on my requirement

[oracle@sample bin]$ vi  $ORACLE_HOME/assistants/dbca/dbca.rsp

 

 

responseFileVersion=/oracle/assistants/rspfmt_dbca_response_schema_v19.0.0

gdbName=silentdb ---> This defines global db name

sid=dbsilent    --->  This defines the oracle SID

databaseConfigType=SI ---- > This defines the instance type i.e single instance or Rac instance

createAsContainerDatabase=true ---> Choose true if you want to create a container database

templateName=/u01/app/oracle/product/19.0.0/dbhome_1/assistants/dbca/templates/General_Purpose.dbc --- > Defines the template name would be used for database creation

sysPassword=oracle --- > password for sys user

systemPassword=oracle --- > password for system user

datafileDestination=/u01/app/oracle/oradata ---- > Defines datafile location

storageType=FS --- > Defines the storage type i.e. file system or ASM

characterSet=AL32UTF8 --- > Defines character set

nationalCharacterSet=AL16UTF16 --- > Defines national character set

sampleSchema=TRUE --- > Defines if sample schemas would be created

databaseType=MULTIPURPOSE --- >Defines database type whether  

MULTIPURPOSE|DATA_WAREHOUSING|OLTP

 totalMemory=1024 ---- > Defines total memory allocated for database

Step 2.

Begin installation

[oracle@sample bin]$ dbca -silent -createDatabase -responseFile /u01/app/oracle/product/19.0.0/dbhome_1/assistants/dbca/dbca.rsp

Prepare for db operation

8% complete

Copying database files

31% complete

Creating and starting Oracle instance

32% complete

36% complete

40% complete

43% complete

46% complete

Completing Database Creation

51% complete

53% complete

54% complete

Creating Pluggable Databases

58% complete

63% complete

77% complete

Executing Post Configuration Actions

100% complete

Database creation complete. For details check the logfiles at:

 /u01/app/oracle/cfgtoollogs/dbca/testdb.

Database Information:

Global Database Name:testdb

System Identifier(SID):testdb

Look at the log file "/u01/app/oracle/cfgtoollogs/dbca/testdb/testdb0.log" for further details.

Step 3-check installation

[oracle@sample bin]$ export ORACLE_SID=testdb

[oracle@sample bin]$ sqlplus / as sysdba

 SQL> select name from v$database;

***************************************************************************

 

4. Diff between SGA Target & Memory Target?

SGA_Target is the size of the SGA not including the PGA. Memory_Target includes both SGA and PGA

SGA_TARGET provides the following:

§  Single parameter for total SGA size

§  Automatically sizes SGA components

§  Memory is transferred to where most needed

§  Uses workload information

§  Uses internal advisory predictions

§  STATISTICS_LEVEL must be set to TYPICAL

By using one parameter we don't need to use all other SGA parameters like.

§  DB_CACHE_SIZE (DEFAULT buffer pool)

§  SHARED_POOL_SIZE (Shared Pool)

§  LARGE_POOL_SIZE (Large Pool)

§  JAVA_POOL_SIZE (Java Pool)

 

Some parameter to find sga_target

SQL> show parameter sga_target

NAME                                                     TYPE         VALUE

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

sga_target                                     big integer 0

SQL> show parameter sga_max_size

 

NAME                                                     TYPE         VALUE

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

sga_max_size                               big integer 1264M

SQL> alter system set sga_target=500m;

System altered.

SQL> show parameter sga_target

NAME                                                     TYPE         VALUE

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

sga_target                                     big integer 512M

MEMORY_TARGET we can have help in the following:

• A single parameter for total SGA and PGA sizes
• Automatically sizes SGA components and PGA
• Memory is transferred to where most needed
• Uses workload information
• Uses internal advisory predictions
• Can be enabled by DBCA at the time of Database creation.

Some parameters in memory target

SQL> show parameter memory_target

NAME                                                     TYPE         VALUE

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

memory_target                                         big integer 1264M

SQL> ALTER SYSTEM SET MEMORY_TARGET='2G' SCOPE=spfile;

System altered.

SQL> shut immediate

SQL>startup

SQL> show parameter memory_target;

NAME                                                     TYPE         VALUE

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

memory_target                                         big integer 2G

***************************************************************************

5. Diff between SPfile and Pfile ?

Use of PFILE or SPFILE

 

When an Oracle instance is started, its characteristics are set by reading some initialization parameters. These parameters are specified either through PFILE or SPFILE.  (0r)

 

When an oracle instance is being started, it reads either PFILE or SPFILE to set its characteristics.

 

What is PFILE ?

 

A PFILE is a static, client side text file. This file normally resides on the server. However in case you would like to start oracle from a remote machine, you will need a local copy of the PFILE in that machine.

 

This file can be edited with a standard text editor like notepad or vi editor. This file will be commonly referred as INIT.ORA file.

 

What is SPFILE ?

 

SPFILE (Server parameter file) is a persistent server side binary file. SPFILE should only be modified through “ALTER SYSTEM SET” command . Editing an SPFILE directly will corrupt the file and the start up of the database or instance will not be possible.

 

As SPFILe is a server side binary file,  local copy of the PFILE is not required to start oracle from a remote machine.

 

 

Advantages of SPFILE compared to PFILE

 

A SPFILE doesnot need a local copy of  the pfile to start  oracle from a remote machine.  Thus eliminates configuration problems.

 

SPFILE  is a binary file and modications to that can only be done through ALTER SYSTEM SET command.

 

As SPFILE is maintained by the server, human errors can be eliminated as the parameters are checked before modification in SPFILE

 

It is easy  to locate SPFILE as it is stored in a central location

 

Changes to the parameters in SPFILE will take immediate effect without restart of the instance i.e Dynamic change of parameters is possible

 

SPFILE can be backed up by RMAN

 

Difference between PFILE and SPFILE in Oracle :

 

 

SNO	PFILE	SPFILE
1	Static, client side text file	Persistent server side binary file
2	Local copy of pfile required to start database from a remote machine	local copy is not required
3	Can be edited through notepad or vi editor	Editing directly will corrupt the file. It should be modified only through ALTER SYSTEM SET command
4	Is available in earlier versions of Oracle 9i	Available from Oracle 9i and above
5	Prone to human errors while modification	Eliminates human errors as parameters are checked before modification
6	Cannot be backed up by RMAN	Can be backed up by RMAN
7	Parameters in pfile cannot be changed dynamically and system needs to be bounced for the new changes to take effect	Dynamic change of some parameters is possible. Need not restart server for the changes to take effect

 

***************************************************************************

6. Explain PGA & Tell 15 parameters?

What is PGA

The Program Global Area (PGA) is a private memory region that contains the data and control information for a server process. Only a server process can access the PGA. Oracle Database reads and writes information in the PGA on behalf of the server process. An example of such information is the run-time area of a cursor. Each time a cursor is executed, a new run-time area is created for that cursor in the PGA memory region of the server process executing that cursor.

·         Sort-based operators, such as ORDER BY, GROUP BY, ROLLUP, and window functions

·         Hash-join

·         Bitmap merge

·         Bitmap create

·         Write buffers used by bulk load operations

A sort operator uses a work area (the sort area) to perform the in-memory sorting of a set of rows. Similarly, a hash-join operator uses a work area (the hash area) to build a hash table from its left input.

PGA_AGGREGATE_LIMIT

PGA_AGGREGATE_LIMIT specifies a limit on the aggregate PGA memory consumed by the instance.

There is no difference in behavior between PGA_AGGREGATE_LIMIT being explicitly set or being set to the default.

SQL> show parameter pga_aggregate_limit;

NAME                                                     TYPE         VALUE

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

pga_aggregate_limit                     big integer 2G

PGA_AGGREGATE_TARGET 

PGA_AGGREGATE_TARGET specifies the target aggregate PGA memory available to all server processes attached to the instance. To set a hard limit for aggregate PGA memory, use the PGA_AGGREGATE_LIMIT parameter.

Setting PGA_AGGREGATE_TARGET to a nonzero value has the effect of automatically setting the WORKAREA_SIZE_POLICY parameter to AUTO. With this setting, SQL working areas used by memory-intensive SQL operators (such as sort, group-by, hash-join, bitmap merge, and bitmap create) will be automatically sized. A nonzero value for this parameter is the default since, unless you specify otherwise, Oracle sets it to 20% of the SGA or 10 MB, whichever is greater.

SQL> show parameter pga_aggregate_target;

NAME                                                     TYPE         VALUE

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

pga_aggregate_target                   big integer 0

 

 

 

 

7. what is Oracle_home and Oracle_Base?

Difference between ORACLE BASE and ORACLE HOME

 

Oracle BASE and Oracle HOME are the directories defined by the Oracle Flexible Architecture.  Oracle Base is the top level directory or you can say root directory. Under Oracle Base you can have multiple Oracle versions with different Oracle HOME.

 

ORACLE_BASE

 

ORACLE_BASE specifies the directory at the top of the Oracle software and administrative file structure. The value recommended for an OFA configuration is software_mount_point/app/PROD

For example: /u01/app/PROD

If you are not using an OFA-compliant system, you do not have to set ORACLE_BASE, but it is highly recommended that you do set it.

 

ORACLE_HOME

 

ORACLE_HOME specifies the directory containing the Oracle software for a given release. The Optimal Flexible Architecture recommended value is:

$ORACLE_BASE/product/release

 

In my system following values are set.

[oracle@sample ~]$ echo $ORACLE_BASE

/u01/app/oracle

[oracle@sample ~]$ echo $ORACLE_HOME

/u01/app/oracle/product/19.0.0/dbhome_1

[oracle@sample ~]$

8. Define Oracle_SID ?

The Oracle System ID (SID) is used to uniquely identify a particular database on a system. For this reason, one cannot have more than one database with the same SID on a computer system. When using RAC, all instances belonging to the same database must have unique SID's.

SQL> select instance_name,host_name from v$instance;

INSTANCE_NAME       HOST_NAME

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

orcl                       sample.localdomain

 

9. How to get current SCN number ?

Fetch the current SCN number in v$database view.

SQL> select name,CURRENT_SCN from v$database;

NAME              CURRENT_SCN

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

ORCL       2245463

10.What is SCN & LSN?

Log Sequence Numbers

LSNs are used internally during a RESTORE sequence to track the point in time to which data has been restored. When a backup is restored, the data is restored to the LSN corresponding to the point in time at which the backup was taken. Differential and log backups advance the restored database to a later time, which corresponds to a higher LSN.

System Change Numbers (SCNs)

A system change number (SCN) is a logical, internal time stamp used by Oracle Database. SCNs order events that occur within the database, which is necessary to satisfy the ACID properties of a transaction. Oracle Database uses SCNs to mark the SCN before which all changes are known to be on disk so that recovery avoids applying unnecessary redo. The database also uses SCNs to mark the point at which no redo exists for a set of data so that recovery can stop.

SCNs occur in a monotonically increasing sequence. Oracle Database can use an SCN like a clock because an observed SCN indicates a logical point in time and repeated observations return equal or greater values. If one event has a lower SCN than another event, then it occurred at an earlier time with respect to the database. Several events may share the same SCN, which means that they occurred at the same time with respect to the database.

 

11.How to check locking sessions and tables?

Blocking session

Blocking sessions are a problem for the DBA and we need a way to find them so we can deal with them. Blocking sessions occur when a session issues an insert, update or delete command that changes a row.

When the change occurs, the row is locked until the session either commits the change, rolls the change back or the user logs off the system. You can see where problems might occur, for example a user might make a change and then forget to commit it and leaves for the weekend without logging off the system.

We can use this query to find these nasty blocking sessions. We use our old friend, v$session to find the blocking session, and also a list of sessions locked by that session.

First session

SQL> select * from rman;

            ID NAME

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

             7 viki

             1 vignesh

             2 vijay

7 rows selected.

SQL> update rman set id=10 where id='2';

The command take run more time

Second session

SQL> update rman set id=10 where id='1';

1 row updated.

SQL>  select * from rman;

            ID NAME

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

             7 viki

            10 vignesh

             2 vijay

             3 ajay

             4 pradeep

8 rows selected.

 

SQL> update rman set id=10 where id='2';

1 row updated.

SQL> select * from rman;

            ID NAME

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

             7 viki

            10 vignesh

            10 vijay

             3 ajay

8 rows selected.

Blocking session can be occurred:

SQL> SELECT s.blocking_session,s.sid,s.serial#,s.seconds_in_wait FROM gv$session s WHERE blocking_session IS NOT NULL;

 

BLOCKING_SESSION        SID    SERIAL# SECONDS_IN_WAIT

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

                  79  82       57089                204

Solution:

Commit one session

Kill the process

Terminate one session

12.How to pin a table DBBC?

While the access times of heavily accessed objects can be improved by placing them in their own filegroups, assuming separate I/O controllers exist for distinct hard drives, the best place for objects to reside will always be memory. With the exception of the large 64 bit servers, current data volumes prohibit placing all objects into RAM. However, the DBA should try to keep all or most of the often-accessed data in memory as the response times will be better than if their data is constantly re-read from disk.

Pinning

DECLARE @DatabaseID INT, @TableID INT

USE DatabaseName 

SET @DatabaseID = DB_ID('DatabaseName') 

SET @TableID = OBJECT_ID('TableName') 

DBCC PINTABLE (@DatabaseID, @TableID) 

 

13.How to pin a query in shared pool?

Pinning database objects in Oracle's shared pool can improve database performance. It is recommended that all ESRI stored procedures and sequences be pinned. Once objects reside in Oracle's shared pool, they do not need to be parsed, which saves considerable resources.

Pinning objects in the shared pool uses available memory that might otherwise be used for other processes that share the pool. Allocate the additional memory to the shared pool as needed. In general, do not pin objects that are rarely used; this could have the adverse affect on database performance.

Procedure

1.      Before pinning database objects, create the SYS package that pins the objects in the shared pool. As the SYS user in SQL*Plus, execute the dbmspool.sql script located under the $ORACLE_HOME/rdbms/admin directory.

Optionally, it is possible to grant execute privileges to the new DBMS_SHARED_POOL package to users who will need to pin objects. For example, to grant these privileges to the SDE user:

Code:

GRANT execute ON dbms_shared_pool TO sde; 

2.      Begin pinning objects in the shared pool.

Code:

EXECUTE dbms_shared_pool.keep ('STANDARD', 'P'); As a general rule, always pin the following packages owned by SYS:

   STANDARD 

   DBMS_STANDARD 

   DBMS_UTILITY 

   DBMS_DESCRIBE 

   DBMS_OUTPUT 

Pinning other SYS packages that are often used, such as DBMS_LOCK and DBMS_ALERT, may be desired.

3.      As the SDE user, pin the objects that the application frequently uses.
Code:
EXECUTE dbms_shared_pool.keep ('VERSION_UTIL', 'P');

Code:

 EXECUTE dbms_shared_pool.keep ('LOCK_UTIL', 'P'); 

Database objects are not limited to stored procedures. Database sequences, triggers, and cursors can all be pinned in the shared pool. ESRI recommends pinning frequently used sequences as well.

Code:

EXECUTE dbms_shared_pool.keep ('SDE.CONNECTION_ID_GENERATOR', 'Q'); 

SQL> SELECT 'Example TEST' FROM dual;

'EXAMPLETEST

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

Example TEST

SQL> select substr(sql_text,1,15) Text,address,hash_value,KEPT_VERSIONS

from v$sql where sql_text like '%Example%';  2 

SQL> @?/rdbms/admin/dbmspool.sql

Session altered.

Package created.

Grant succeeded.

Session altered.

SQL> exec DBMS_SHARED_POOL.PURGE ('000000006189AC28,2929179549','c');

PL/SQL procedure successfully completed.

SQL> select sid,serial#,prev_sql_id from v$session where audsid=userenv('sessionid');

       SID    SERIAL# PREV_SQL_ID

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

            79        57117 5rsr6qt29hu5s

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_Id='5rsr6qt29hu5s';

ADDRESS      HASH_VALUE

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

0000000061A2DE58 1150838968

SQL>

 

14.What is checkpoint?

The Checkpoint process (not surprisingly) controls database checkpoints. Every 3 seconds the Checkpoint process determines the earliest redo log entry for which changes have not been written to the database. This becomes the checkpoint, and it is recorded in the control file and in all the datafiles. The following Slide Show illustrates this process:

Checkpoint automatically occurs at:

1.Checkpoint automatically occurs at a log switch.
2.When we will specify the parameter fast_start_mttr_target=<No of Seconds>.
3.When Normally forced by the Database Administrator.
4.If the datafile is offline checkpoint will occur.
5.Consistent database shutdown
6.ALTER SYSTEM CHECKPOINT statement
7.ALTER DATABASE BEGIN BACKUP statement

SQL> SELECT TARGET_MTTR, ESTIMATED_MTTR, CKPT_BLOCK_WRITES FROM V$INSTANCE_RECOVERY;

TARGET_MTTR        ESTIMATED_MTTR             CKPT_BLOCK_WRITES

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

              0                    15                    749

15.What is instance recovery?

Oracle performs instance recovery when the database is restarted due to instance failure or shutdown the database with ABORT option(shutdown abort) or startup the database with FORCE option(startup force). Instance recovery is taken care by SMON oracle background process automatically.

Instance recovery consists of two steps. One is Roll forward, next is Roll backward or Cache Recovery and Transaction Recovery

Roll Forward/ Cache recovery:

Changes being made to a database are recorded in the database buffer cache. These changes are also recorded in online redo log files simultaneously. When there are enough data in the database buffer cache, they are written to data files. If an Oracle instance fails before the data in the database buffer cache are written to data files, Oracle uses the data recorded in the online redo log files to recover the lost data when the associated database is re-started. This process is called Roll forward or cache recovery.

Roll backward/ransaction recovery:

When a transaction modifies data in a database, the before image of the modified data is stored in an undo segment.The data stored in the undo segment is used to restore the original values in case a transaction is rolled back. At the time of an instance failure, the database may have uncommitted transactions. It is possible that changes made by these uncommitted transactions have gotten saved in data files.(We don’t have any mechanism to track this) To maintain read consistency, Oracle rolls back all uncommitted transactions when the associated database is re-started. Oracle uses the undo data stored in undo segments to accomplish this. This process is called transaction recovery.

SCN number of transactions are not synchronized with datafile headers. so while starting up SMON compares SCN in redologs and datafile headers since its not same it writes committed as well as

uncommitted data present in redolog files to datafiles now this step is called rollforward now datafiles contain both committed and uncommitted data. During rollforward undo is generated in undo files. Now this undo is applied to datafiles and uncommitted data is rolled back this is called rollback mechanism .

16.How to flush shared pool DBBC and RLBC?

Shared pool

Flush Shared pool means flushing the cached execution plan and SQL Queries from memory. FLush buffer cache means flushing the cached data of objects from memory. Both is like when we restart the oracle database and all memory is cleared

STEP 1: Find Address and  hash_value of particular sql_id.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID='251fjyn5fj84q';

STEP 2: Purge sql plan from the shared pool by passing the above values.

SQL> exec DBMS_SHARED_POOL.PURGE ('00000005DGEC9DE0, 257655674', 

STEP 3: Check if the plan still exist in the memory. If no rows selected then plan has been flushed out from the memory for that sql_id.

SQL> select ADDRESS, HASH_VALUE from V$SQLAREA where SQL_ID='251fjyn5fj84q';

Flush dbbc

SQL> alter system flush buffer_cache;

SELECT o.owner,
       o.object_type,
       substr(o.object_name,1,10)
       objname,
       b.objd,
       b.status,
       count(b.objd)
  FROM v$bh b, dba_objects o
 WHERE b.objd = o.data_object_id
   AND o.owner not in ('SYS','SYSTEM','SYSMAN')
GROUP BY o.owner,
         o.object_type,
         o.object_name,
         b.objd,
         b.status;

RLBC

The redo log buffer is a RAM area (defined by the initialization parameter log_buffer) that works to save changes to data, in case something fails and Oracle has to put it back into its original state (a ?rollback?).  When Oracle SQL updates a table (a process called Data Manipulation Language, or DML), redo images are created and stored in the redo log buffer. Since RAM is faster than disk, this makes the storage of redo very fast.

17.What is orainventory and oratab?

Orainventory:

The Oracle Inventory directory (oraInventory) stores an inventory of all software installed on the system. It is required and shared by all Oracle software installations on a single system. If you have an existing Oracle Inventory path, then Oracle Universal Installer continues to use that Oracle Inventory.

/u01/app/oracle/orainventory

Oratab:

The oratab file is created by the root.sh script during installation, and it is updated by the Database Configuration Assistant when creating or deleting a database.

The oratab file entry is also created automatically by the Database Agent when a database is first started on a node where it has not run previously.

Cat  /etc/oratab

 

18.Difference between shutdown transaction and shutdown immediate?

shutdown normal - no new logins.  Shutdown happens after all current users disconnect.  This is a very gentle way of performing shutdown, but is likely to take a long time - you are catering to your users' convenience, not your own.

shutdown transactional - no new transactions.  Shutdown happens after currently active transactions complete.  Connected users get automatically logged off, but not at the cost of losing work in progress.  This type of shutdown will probably go faster than shutdown normal because connected users are disconnected automatically.  The only delay would be if there is a run away transaction active that won't complete in a reasonable length of time.

shutdown immediate - active transactions are rolled back.  Connected users get logged off after their transactions are rolled back.  This figures to be the fastest of the non-abort shutdowns.  The main delay would be a long running rollback of a large transaction.

 

19.What is LGWP?

Log Writer process (LGWR) is a background Process that writes redo log entries sequentially into redo log file.

Redo log entries are generated in the redo log buffer of the SGA.

If the database has a multiplexed redo log, then LGWR writes the same redo log entries to all of the members of a redo log file group

If oracle database is running in archiving mode then archive log files are created from redo log files. These archive log files are used to in various database recovery techniques.

Redo log buffer works in circular fashion. It means that it overwrites old entries. But before overwriting, old entries must be copies to redo log files. Usually Log writer process (LGWR) is fast enough to mange these issues. Log writer process (LGWR) writes redo entries after certain amount of time to ensure that free space is available for new redo entries.

 

20.What is scope=spfile, both, memory?

ALTER SYSTEM SET parameter = value SCOPE=[SPFILE/MEMORY/BOTH]

• BOTH - (Default) The parameter takes affect in the current instance and is stored in the SPFILE.
• SPFILE - The parameter is altered in the SPFILE only. It does not affect the current instance.
• MEMORY - The parameter takes affect in the current instance, but is not stored in the SPFILE.

ALTER SYSTEM set parameter = value SCOPE = MEMORY;

ALTER SYSTEM set parameter = value SCOPE = SPfile;

ALTER SYSTEM set parameter = value SCOPE = BOTH;

21. What is SGA and tell the parameters?

A system global area (SGA) is a group of shared memory structures that contain data and control information for one Oracle database instance. If multiple users are concurrently connected to the same instance, then the data in the instance's SGA is shared among the users. Consequently, the SGA is sometimes called the shared global area.

An SGA and Oracle processes constitute an Oracle instance. Oracle automatically allocates memory for an SGA when you start an instance, and the operating system reclaims the memory when you shut down the instance. Each instance has its own SGA.

The SGA is read/write. All users connected to a multiple-process database instance can read information contained within the instance's SGA, and several processes write to the SGA during execution of Oracle.

The SGA contains the following data structures:

• Database buffer cache
• Redo log buffer
• Shared pool
• Java pool
• Large pool (optional)
• Streams pool
• Data dictionary cache

 

22. When user gives commit, what will happen in Database?

            User can login to the session and perform dml operations like insert update something in the database. The user can insert more rows in the table, that time the data can be copied in undo tablespace once the user gives commit in the transaction that rows or files can be updated in the specified datafile. In undo tablespace they can by available sometime then it will be removed in the undo tablespace

23. Expalin shutdown options in database?

SHUTDOWN NORMAL

The SHUTDOWN NORMAL option waits for the current users to disconnect from the database before shutting down the database. The database instance will not accept any further database connection.  The SHUTDOWN NORMAL does not require an instance recovery on the next database startup.

SHUTDOWN TRANSACTIONAL

The SHUTDOWN TRANSACTIONAL waits for all uncommitted transactions to complete before shutting down the database instance. This saves the work for all users without requesting them to log off.

SHUTDOWN ABORT

`The SHUTDOWN ABORT is not recommended and only used on some occasions. The SHUTDOWN ABORT has a similar effect as you unplug the power of the server. The database will be in an inconsistent state. Therefore, you should never use the SHUTDOWN ABORT command before backing up the database. If you try to do so, you may not be able to recover the backup.

SHUTDOWN IMMEDIATE

The SHUTDOWN IMMEDIATE is the most common and practical way to shut down the Oracle database.

The SHUTDOWN IMMEDIATE does not wait for the current users to disconnect from the database or current transactions to complete.

24. Explain startup option in database?

1) NOMOUNT stage

using the SPFILE or PFILE parameters in the STARTUP command.

Next, read the parameter file to get the values of the initialization parameters.

Then, allocate the system global area (SGA) based on the initialization parameter settings.

After that, start the Oracle background processes such as SMON, PMON, and LGWR.

Finally, open the alert log and trace files and record all explicit parameters to the alert log in the valid parameter syntax.

At the NOMOUNT stage, Oracle does not associate the database with the instance.

2) MOUNT stage

In the MOUNT stage, Oracle associates a database with an instance. In other words, the instance mounts the database.

First, get the name of the database control files specified in the CONTROL_FILE initialization parameter.

Second, open the control files.

Third, find the name of data files and the online redo log files.

When a database is mounted, the database is only available to database administrators, not all users.

3) OPEN stage

First, open the online data files in tablespaces other than the undo tablespaces.

Then, select an undo tablespace. The instance uses default undo tablespace if an undo tablespace is specified in the UNDO_TABLESPACE initialization parameter. Otherwise, it will select the first available undo tablespace.

Finally, open the online redo log files.

25. What is deadlock?, How to find it and How to resolve it?

A deadlock occurs when two or more sessions are waiting for data locked by each other, resulting in all the sessions being blocked. Oracle automatically detects and resolves deadlocks by rolling back the statement associated with the transaction that detects the deadlock. Typically, deadlocks are caused by poorly implemented locking in application code. This article shows the steps necessary to identify the offending application code when a deadlock is detected.

Deadlock example

SQL> DECLARE

  l_deadlock_1_id  deadlock_1.id%TYPE;

  l_deadlock_2_id  deadlock_2.id%TYPE;

BEGIN

  SELECT id

  INTO   l_deadlock_1_id

  FROM   deadlock_1

  WHERE  id = 1

  FOR UPDATE;

  DBMS_LOCK.sleep(30);

  SELECT id

  INTO   l_deadlock_2_id

  FROM   deadlock_2

  WHERE  id = 1

  FOR UPDATE;

  ROLLBACK;

END;

/ 

DECLARE

*

ERROR at line 1:

ORA-00060: deadlock detected while waiting for resource

ORA-06512: at line 16

 

26. Where and how to change the parameter value to be work immediatly and after bounced?

change the value of a parameter in a parameter file in one of the following ways:

• By editing an initialization parameter file

In most cases, the new value takes effect the next time you start an instance of the database.

• By issuing an ALTER SYSTEM SET ... SCOPE=SPFILE statement to update a server parameter file

27. Expalin PMON, SMON, DBWR, LGWR, CKPT, MMON, RECO, FBDA?

PMON:

               The process monitor (PMON) monitors the other background processes and performs process recovery when a server or dispatcher process terminates abnormally. PMON is responsible for cleaning up the database buffer cache and freeing resources that the client process was using. For example, PMON resets the status of the active transaction table, releases locks that are no longer required, and removes the process ID from the list of active processes.

               PMON also registers information about the instance and dispatcher processes with the Oracle Net listener (see "The Oracle Net Listener"). When an instance starts, PMON polls the listener to determine whether it is running. If the listener is running, then PMON passes it relevant parameters. If it is not running, then PMON periodically attempts to contact it.

 

[oracle@sample ~]$ ps -ef |grep pmon

oracle    1988     1  0 Aug16 ?        00:00:00 ora_pmon_orcl

oracle   30811  8286  0 03:24 pts/1    00:00:00 grep --color=auto pmon

SMON

SMON (System MONitor) is an Oracle background process created when you start a database instance. The SMON process performs instance recovery, cleans up after dirty shutdowns and coalesces adjacent free extents into larger free extents.

SMON wakes up every 5 minutes to perform housekeeping activities. SMON must always be running for an instance. If not, the instance will terminate.

 

[oracle@sample ~]$ ps -ef |grep smon

oracle    1237  8286  0 03:25 pts/1    00:00:00 grep --color=auto smon

oracle    2028     1  0 Aug16 ?        00:00:00 ora_smon_orcl

DBWR

DBWR (DataBase WRiter) is an Oracle background process created when you start a database instance. The DBWR writes modified data (dirty buffers) from the SGA into the Oracle database files. When the SGA data buffer cache fills the DBWR process selects buffers using an LRU algorithm and writes them to disk. There can be multiple database writer processes named DBWn.

SQL> show parameter db_writer

NAME                                                     TYPE         VALUE

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

db_writer_processes                    integer        1

LGWR

LGWR (LoG WRiter) is an Oracle background process created when you start a database instance. The LGWR writes the redo log buffers to the on-line redo log files. If the on-line redo log files are mirrored, all the members of the group will be written out simultaneously.

[oracle@sample ~]$ ps -ef | grep lgwr

oracle    2024     1  0 Aug16 ?        00:00:00 ora_lgwr_orcl

oracle   12279  8286  0 03:29 pts/1    00:00:00 grep --color=auto lgwr

[oracle@sample ~]$

CKPT

CKPT (Oracle Checkpoint Process) is an Oracle background process that timestamps all datafiles and control files to indicate that a checkpoint has occurred.

The "DBWR checkpoints" statistic (v$sysstat) indicates the number of checkpoint requests completed.

 

[oracle@sample ~]$ ps -ef | grep ckpt

oracle    2026     1  0 Aug16 ?        00:00:02 ora_ckpt_orcl

oracle   16070  8286  0 03:31 pts/1    00:00:00 grep --color=auto ckpt

[oracle@sample ~]$

MMON

MMON (Manageability Monitor) is a background process that gathers memory statistics (snapshots) and stores this information in the AWR (automatic workload repository). MMON is also responsible for issuing alerts for metrics that exceed their thresholds.

[oracle@sample ~]$ ps -ef | grep mmon

oracle    2052     1  0 Aug16 ?        00:00:03 ora_mmon_orcl

oracle   19434  8286  0 03:32 pts/1    00:00:00 grep --color=auto mmon

RECO

RECO (Oracle RECOverer Process) is an Oracle background process created when you start an instance with DISTRIBUTED_TRANSACTIONS= in the initialization parameter file. The RECO process will try to resolve in-doubt transactions across Oracle distributed databases.

[oracle@sample ~]$ ps -ef |grep reco

oracle    2032     1  0 Aug16 ?        00:00:00 ora_reco_orcl

oracle   23135  8286  0 03:33 pts/1    00:00:00 grep --color=auto reco

[oracle@sample ~]$

FBDA

Flashback Data Archiver Process (FBDA) is a background process that provides the ability to track and store transactional changes to a table over its lifetime. This way, you can flashback tables back in time to restore the way they were.Each information written in the undo tablespace is also written in the FDA by the FBDA background process.

FBDA can run as a thread or as an operating system process.FBDA is also responsible for automatically managing the flashback data archive for space, organization (partitioning tablespaces), and retention. FBDA also keeps track of how far the archiving of tracked transactions has progressed.

 

[oracle@sample ~]$ ps -ef |grep fbda

oracle   27952  8286  0 03:35 pts/1    00:00:00 grep --color=auto fbda

 

28. Explain Largepool, Javapool, Streampool, Library cache, Result cache?

Large Pool

Used for allocating session memory for shared server, Oracle XA, or parallel query buffers or for RMAN.

LARGE_POOL_SIZE

SQL> show parameter large_pool_size;

NAME                                                     TYPE         VALUE

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

large_pool_size                            big integer 0

Java Pool

Java pool memory is used in server memory for all session-specific Java code and data within the JVM.

JAVA_POOL_SIZE 

SQL> show parameter java_pool_size

NAME                                                     TYPE         VALUE

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

java_pool_size                              big integer 0

Stream Pool

The streams pool is used exclusively by Oracle Streams. The Streams pool stores buffered queue messages, and it provides memory for Oracle Streams capture processes and apply processes.

STREAMS_POOL_SIZE

SQL> show parameter streams_pool_size

NAME                                                     TYPE         VALUE

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

streams_pool_size                        big integer 0

Library Cache

The library cache includes the shared SQL areas, private SQL areas (in the case of a shared server configuration), PL/SQL procedures and packages, and control structures such as locks and library cache handles.

Shared SQL areas are accessible to all users, so the library cache is contained in the shared pool within the SGA.

 

Result cache

A result cache is an area of memory, either in the Shared Global Area (SGA) or client application memory, that stores the results of a database query or query block for reuse. The cached rows are shared across SQL statements and sessions unless they become stale.

SQL> show parameter result_cache_size

NAME                                                     TYPE         VALUE

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

client_result_cache_size               big integer 0

 

 

 

29. Difference between Runtime area and Persistent area?

• The run-time area

This area contains query execution state information. For example, the run-time area tracks the number of rows retrieved so far in a full table scan.

Oracle Database creates the run-time area as the first step of an execute request. For DML statements, the run-time area is freed when the SQL statement is closed.

• The persistent area

This area contains bind variable values. A bind variable value is supplied to a SQL statement at run time when the statement is executed. The persistent area is freed only when the cursor is closed.

30. What will happen when we run   root.sh?

This step is also required after an Oracle patch installation. See Verify the required operating system packages. To run the root.sh script:

Procedure

Log in as root or become superuser. Set the DISPLAY environment variable.

Change to the directory where Oracle server files were installed. (This is the directory as set in the ORACLE_HOME environment variable.) For example:

# cd  /opt/oracle/product/12.1.0

Run the following command:

# ./root.sh

Messages like the following are displayed:

Running Oracle12c root.sh script...

# ./root.sh

Check /opt/oracle/product/12.1.0/install/root_

<server_hostname>_2014-03-18_13-45-04.log for the output of root script

File contents:

Performing root user operation for Oracle 12c

The following environment variables are set as:

    ORACLE_OWNER= oracle

    ORACLE_HOME=  /opt/oracle/product/12.1.0

Entries will be added to the /etc/oratab file as needed by

Database Configuration Assistant when a database is created

Finished running generic part of root script.

Now product-specific root actions will be performed.

Finished product-specific root actions.

 

31. What is shared server and dedicated server?

• A dedicated server process, which services only one user process
• A shared server process, which can service multiple user processes

A Shared server is a good choice for those website owners who don’t have much technical skills to manage and control everything, but wish to build the online presence. With Shared hosting you have limited administrative access and can't customize software configuration. Still, you have access to a cPanel, with which you can manage files, databases, email accounts, error logs, and some server analytics. If the server goes down, you can always be sure that the technicians will take care of it in the shortest time possible.

A Dedicated server is a good choice for those who have technical background and can manage/configure the server on their own. Yet, the lack of technical skills should not be an obstacle, since we offer different types of Dedicated server management: User-Responsible, Basic, Complete. You may find more information on each type here. Root access, provided with a Dedicated server, is a huge benefit if you need to make some server modifications.