Snapshot Too Old Error

Snap-Shot too Old Error

Reasons

Snapshot too old error is occur whenever any query is fired and it does not get consistent data or consistent view from and undo tablespace then snapshot too old error comes.

Scenarios. For example

i)         when hr user updates all rows to 50gb table. then it generates undo data. and no commit given yet.

ii)           At the mean time scott user want to access all rows of the table then it gives result from undo tablespace.

iii)   Then again hr user commits all updates transactions so here all commited transactions are deleted from undo tablespace.

iv)          So here scott user that fires long-running query receives snapshot too old error because now there is no remaining data into the undo tablespace.

Solutions

-> configure an appropriate undo retention interval

UNDO_RETENTION:- parameter specifies the how long already committed transaction are to be retained into the undo tablespace.

-> size of the undo tablespace properly

-> Consider guaranteeing undo retention

Guranteeing undo retention

The default behavior is overwrite the committed transactions that have not yet expired rather than to allow an active transaction to fail because of the lack of undo tablespace.

This behavior can be changed by guaranteeing undo retention, undo retention settings are enforced even if they cause transactions to fail.

Retention Gurantee can only applied to undo tablespace, cannot given to any other tablespace.

what is the work of Undo Advisor ?

it provides an estimate of the undo tablespace size required to satisfy a given undo retention.

Sending Notification With Attachment.

Topic        Sending Notification to Application User With Attachment

Steps

Create Workflow Message Type in your workflow (No need to create Custom Workflow, only create message type)

1.

2. Create Message Type

And in that message type Create Attributes whatever you required.

Here, I create all these attributes and I created Document1 to 6 means I can send multiple attachment of the person upto 6. Document attribute you have to create like this.

Save your workflow.

Calling Procedure:

Below Statement calling one Custom Procedure which will send Notification With Attachment to a Particular Application User Name. (YOU HAVE TO USE THIS TO CALL CUSTOM PACKAGE THAT I ATTACHED IN THIS DOCUMENT)

         APPS.KFSH_SEND_ATTACHMENT.KFSH_SEND_NOTI_WITH_ATT (

               'APPOINT_REGISTRATION',

               L_SUBJECT,

               L_MESSAGE,

               'SYSADMIN',

               P_CONTACT_PERSON_ID);

Parameter Explanation

APPOINT_REGISTRATION 

is the Application User Name to whom I want to Send Notification.

L_SUBJECT          

is the Subject Variable which will stores Subject Which you want to put in Notification.

L_MESSAGE      

is the Message Variable which Stores Message that you want to send in Notification.

SYSADMIN       

is the FROM_ROLE From which you want to Send Notification.

P_CONTACT_PERSON_ID 

Here I take PERSON_ID of the DEPEDENT FOR WHICH I WILL SEND NOTIFICATION WITH ALL ATTACHMENT OF THAT DEPEDENT.

Here I am attaching the required Package that is used to send notification with attachment to application username.

Partition Table - Cluster Table - IOT Table(Index Organized Table)

Contents:- Partition Table, Cluster Table, IOT Table(Index Organized Table)

We can use following concepts to do performance tuning at database level. To enhance the performance in datawarehouse environment following technology are best to use to enhance the performance of the system.

Partition Table

Partition table divides table into several partition at more granulity level to enhance the performance of the table.

Concept

Partition Table is used to do Performance Tuning in an Oracle Database. Suppose There are Crores of records in table in data warehouse system . We can implement partition table there. We can divide whole table into different partition to do performance tuning while fetching the records from table.

Partition Table Types

Range Partition

CREATE TABLE EMP_RECORD(EID NUMBER,ENAME VARCHAR2(100),ESALARY NUMBER)

PARTITION BY RANGE(ESALARY)

(PARTITION E1 VALUES LESS THAN (50000) tablespace users,

PARTITION E2 VALUES LESS THAN (100000) tablespace example,

PARTITION E3 VALUES LESS THAN (150000),

PARTITION E4 VALUES LESS THAN (MAXVALUE));

Hash Partition

CREATE TABLE DEP_RECORD

(DEPARTMENT_ID NUMBER,DEPARTMENT_NAME VARCHAR2(100))

PARTITION BY HASH(DEPARTMENT_ID)

PARTITIONS 4

STORE IN (USERS,EXAMPLE);

List Partition

CREATE TABLE DEPT_RECORD

(DEPARTMENT_ID NUMBER,

DEPARTMENT_NAME VARCHAR2(100))

PARTITION BY LIST(DEPARTMENT_ID)

(PARTITION UPTO_20 VALUES(10,20) TABLESPACE USERS,

PARTITION UPTO_50 VALUES(30,40,50));

Composite Partition

Composite Partition is a combination of more than one partition in a table.

CREATE TABLE EMP_DEPT(EMPID NUMBER,EMPNAME VARCHAR2(10),DEPT_ID NUMBER)

PARTITION BY RANGE(EMPID)

SUBPARTITION BY HASH(DEPT_ID)

SUBPARTITIONS 8 STORE IN (USERS,EXAMPLE)

(PARTITION PART1 VALUES LESS THAN (110) TABLESPACE EXAMPLE,

PARTITION PART2 VALUES LESS THAN (MAXVALUE));

Partition Administration

Insert Records into Partition Table

SQL> Insert into dept_record values(10,’Administration’);

Select Data from Partition

SQL> select * from dept_record partition(upto_20);

Truncate Particular Partition

Sql> alter table dept_record truncate partition upto_20;

Add Partition into Partition by List

Sql> alter table dept_record add partition other values(default);

Add Partition into Partition by Range

SQL> alter table emp_Record add partition part6 values less than (500);

Add Partition into Hash

Alter table emp_record add partition;

Drop Partition of a table.

Sql> alter table emp_Record drop partition part6;

Dynamic Performance Views Related to Partition

Query to know how many partition tables in your Schema

Sql> select * from user_part_Tables;

Query to know partition level partitioning information

Sql> select * from user_tab_partitions;

Cluster Table

A Cluster is a schema object that contains data from one or more tables, all table of cluster have one or more common columns. Oracle Database stores together all the rows from all the tables that shares the same cluster key. So, that common column stores at the same disk space. So at the time of fetching data server process did not require to go different different block, it will fetch data from the same block.

1.Create Cluster Definitions

Sql> create cluster dept_id_c(department_id number) size 500 tablespace users;

2.Create Index on Cluster

Sql> create index dept_id_x_idx on cluster dept_id_c tablespace example;

3.Create First Table and use cluster

Sql> create table emp_record(empid number,ename varchar2(100),department_id number) cluster dept_id_c(department_id);

Create Second Table and use cluster

Sql> create table dept_record(department_id number,department_name varchar2(100)) cluster dept_id_c(department_id);

Index Organized Table(IOT)

An Index-Organized-Table has a storage organization that is variant of a primary B-Tree. Unlike an Ordinary (heap-organized) table whose data is stored as an unordered collection (heap).

An index organized table keeps its data sorted according to the primary key column values for the table. An Index Organized table stores its data as if the entire table was stored in an index.

An index organized table allows you to store its entire data in an index. A Normal index only stores the indexed column; an index organized table stores all its columns in the index.

Organization index Clause is used to create an index-organized table.

Typical Table                    -         Head Organized

Index Organized Table(IOT)      -         Structured in an Index

IOT Used for

è Small tables

è Exact Match

è Reducing Storage Requirements.

Example

SQL> create table country_state(state_id number,name varchar2(20 byte),constraint country_state_pk primary key(state_id)) organized index tablespace users;

Trasnportable Tablespace

Contents:- Transportable Tablespace

Concepts:-

Transportable tablespace is used to move the tablespace from one database to another database. Before move tablespace from one database to another database we have to check violations of the tablespace as well as need to make tablespace read only.

Source Database   :-        evosys

Target Database   :-        Safal

Source Side:- Evosys

$>. Oraenv

evosys

Sql> create tablespace demot datafile ‘/u01/app/oracle/oradata/evosys/demo01.dbf’ size 10m autoextend on next 5m maxsize 20m extent management local segment space management auto;

Sql> create user demo_user identified by demo_user default tablespace demot;

Sql> grant create session,create table to demo_user;

sql> conn demo_user/demo_user

sql> create table demo_table(id number,description varchar2(100));

sql> insert into demo_table(select level,’description for ‘||level from dual connect by level<=10000);

sql> commit;

sql> conn sys as sysdba

sql> select tablespace_name,plugged_in,status from dba_tablespaces;

$>mkdir –p /u01/app/oracle/tts

Sql> create or replace directory tts_dir as ‘/u01/app/oracle/tts’;

Check is there any violations in a tablespace.

Sql> exec dbms_tts.transport_set_check(ts_list=>’demot’ incl_constratins=>TRUE);

Sql> select * from transport_check_violations;

Sql> alter tablespace demot read only;

$>expdp userid=sys/evosys directory=tts_dir transport_tablespaces=demo dumpfile=demo.dmp logfile=demo.log

Sys as sysdba(username)

Evosys(password)

Target Side:- Safal

Move demo.dmp and datafile(demo01.dbf) to destination location.

$>. Oraenv

Safal

$>sqlplus sys as sysdba

Sql> create user demo_user identified by demo_user;

Sql> grant create table,create session to demo_user;

Sql> create or replace directory tts_dir as ‘/u01/app/oracle/tts’;

$>impdp userid=sys/safal directory=tts_dir transport_datafiles=’ /u01/app/oracle/oradata/safal/demo01.dbf’ dumpfile=demo.dmp logfile=demo.log

Sys as sysdba(username)

Safal(password)

Now check your tablespace is successfully copied to destination database and data also came.

Sql> select tablespace_name,plugged_in,status from dba_tablespaces;

Sql> alter tablespace demo read write;

Sql> conn demo_user/demo_user

Sql> select * from demo_Table;

-------------Successfully Completed--------------------

Could Not Validate ASMSNMP Password During DB Creation

https://dba010.wordpress.com/2012/05/21/could-not-validate-asmsnmp-password-due-toduring-db-creation/

Total Recall - Flashback Data Archive Feature of Oracle 11g

Contents- Total Recall (Introduced from oracle 11g Release 2), Practical

Concepts

In Database world, Oraganization would keep track of every change to every piece of data they manage.

The Oracle Total Recall option makes it to securely track and query historical data for any database table, without the cost or performance impact.    

Ways to maintain history of Data

Database Triggers

Database Trigger is used to maintain the history of data. But using triggers does not provide a centralized management interface and can have  a significant impact on application.

Problem:

Privileged users can modify the historical data, calling into question of integrity of data.

Redo Log Mining

Another approach is to mine redo logs to create and store data history.

But Log mining requires a separate set of tools process to configure, monitor and manage.

Problem:

As with application and database triggers, privileged users can modify the historical data stored by log mining the question of integrity of data.

Total Recall Advantage

Flashback Data Archive

Flashback Data Archive (FDA) feature of the oracle total recall option in 11g removes the cost, performance and complexity to maintain historical data.

A Flashback  data archive is a logical container for managing historical information for specified tables. It is new dictionary object in oracle database 11g that defines archive storage and data purging policies.

A Flashback data archive can span multiple tablespace.

Administrators can define the amount of space a flashback data archive can use in each tablespace using the quota parameter.

Multiple Flashback data archives can be created as needed to implement different archiving policies.

Each flashback data archive contain a RETENTION parameter that specifies the duration for retaining historical changes.

Flashback  data archive makes it possible to automatically and transparently track all the changes to any set of tables in oracle 11g database and to easily query data in history table as of any point in time or over any interval within the specified retention period with minimum performance impact.

Features

Minimum cost to maintain historical data.

Data Forensics:- Find and revert changes made by a disgruntled employee.

Automatically purge history older than 5 years.

Error Recovery :- Restore records that were erroneously removed or updated.

Flashback Data archive can be enabled on both existing and new tables and in a completely application transparent manner.

Application transparency:

Enabling flashback data archive on one or more tables can be done instantaneously with no application changes.

Security      

Access to internal history tables is restricted to read only. No DML Operations on historical data are allowed for any users, including administrators. 

Application need not query the internal history tables directly as seamless access is provided through the flashback query mechanism.

Storage Optimization :- Flashback data archive compresses and partitions the internal history tables to optimize storage and performance.

Architecture

In Flashback data archive history generation is implemented by a new process called FBDA.

After a base table has been enabled for history tracking with flashback data archive, all transactions on the table and the corresponding  undo records are marked for archival.

In order to gurantee that every such transactions is archived, the undo records are not recycled until the history is generated and stored in the database.

 

Process

 1)   Create a tablespace to store flashback data.

 2)   Create a Flashback Data Archive in new tablespace.

 3)   Enable the flashback data archive feature on the tables you want to track.

Practical

Requirement

Flashback data archive tablespace must be managed with automatic segment space management.

Automatic Undo Management Must Be enabled.

 1)   Create  a new tablespace to enable flashback data archive.

Sql> create tablespace demofda datafile '+data(datafile)' size 10m autoextend on next 10m extent management local segment space management auto;

 2)   Grant Flashback archive administrator privilege to create flashback data archive.

 3)   Create a Flashback Data Archive on a Created tablespace.

Sql> create flashback archive fda1 tablespace demofda retention 1 year;

 4)   Enable Flashback data archived on Desired Table.

Sql> alter table employees flashback archive fda1;

Or

Sql> grant flashback archive to hr;

 5)   Sql> conn hr/hr

Sql> create table testdb(id number,name varchar2(100),salary number) flashback archive;

Sql> insert into test values('&id','&nm','&sal');

Sql>update test set salary=100000 where name=’Mitesh’;

Now Query to see your historical data by using flashback version query or flashback query

Note:- Flashback archive object privileges is required to enable flashback data archive.

Data Dictionary Views

USER_FLASHBACK_ARCHIVE-

Displays information about flashback data archive.

USER_FLASHBACK_ARCHIVE_TS       

Displays tablespace and the mapping to flashback data archives.

USER_FLASHBACK_ARCHIVE_TABLES-  

Display information about tables that are enabled for flashback data archive.

Temporary Tablespace Recovery

Concepts:- Temporary File Concepts and Scenarios.

Temporary File Recovery

Automatic Temporary File Recovery

If any temporary file is lost then it can be automatically recovered at time of database startup.

Manually Recover Temp File                                     

We can manually recovery the temporary file.

SQL> alter tablespace temp add tempfile ‘+data(tempfile)’ size 100M;

SQL> Alter tablespace temp drop tempfile ‘+data/orcl/tempfile/temporary.12364.266.tmp’;

Shared Server- Dedicated Server Configuration

Contents:- Shared Server, Dedicated Server Configuration.

Server Process

Oracle database creates server process to handle the request of user processes connected to an instance.

There are 2 Types of server process.

i)   Shared Server Process

Shared Server process can service more then one client connections.

Example:- Online Transaction Processing System.

ii)   Dedicated Server Process

Dedicated Server process can only service to one client. (Default)

Shared Server Configuration

Dispatcher Process

Dispatcher process comes in picture when you have configured shared server architecture. When user request connection to the database Dispatcher process handles user request and pass user connection to one common queue available in SGA. And then after that user request processed by server process.

Dispatcher supports multiple client connections concurrently.

Circuit

Each client is bound to a virtual circuit. Circuit is a piece of shared memory used by dispatcher for client database connection request and replies.

The dispatcher places a virtual circuit in a common queue when a request arrives.

Configure Shared Server

Shared_servers 

Specifies the number of shared server processes created when an instance is startup.

Shared_server_sessions   

Specifies the total number of oracle shared server user session to allow.

Dispatchers

Specifies the number of dispatchers that are initially started for a given protocol.

Circuits  

Specifies the total number of virtual circuits that are available for inbound and outbond network sessions.

Sql> alter system set shared_servers=6;

Sql> alter system set shared_server_session=100;

Sql> alter system set dispatchers =’(protocol=TCP) (dispatchers=2)(service=orcl)’;

Sql> alter system set circuits=2;

Now created service for client side connection with server type shared server.

Ex:- I created sharedorcl service for client connection.

NOW Check your server configuration by connecting to the same service that you created for shared server .

$> sqlplus sys/orcl@sharedorcl as sysdba

Sql> select distinct server from v$session.

You cannot shutdown database when any dispatcher process is running. So You have to make all parameter of shared server configuration to 0 to make it shutdown.