Query is running slow .How to go as DBA............

What to do when your query is too slow?


First of all, you have to know why it is slow. What is the real cause of your problem. If the reason why is not known, suggesting to rewrite the query, or hinting the query, suggesting parallellization et cetera is not very productive. Once in a while you may get lucky. But even then you have to realize that if your problem seems "solved", but you don't know why, nothing guarantees you that the problem won't come back tomorrow. So the first step should always be to investigate the root cause.

The tools at your disposal are, among more:

- dbms_profiler

- explain plan

- SQL*Trace / tkprof

- statspack



Use dbms_profiler if you want to know where time is being spent in PL/SQL code. Statspack is a must if you are a dba and want to know what is going on in your entire database. For a single query or a small process, explain plan and SQL*Trace and tkprof are your tools.



explain plan



in SQL*Plus you have to type:

explain plan for Your Query;

select * from table(dbms_xplan.display);

When you get error messages or a message complaining about an old version of plan_table, make sure you run the script utlxplan.sql.

The output you get here basically shows you what the cost based optimizer expects. It gives you an idea on why the cost based optimizer chooses an access path.


SQL*Trace/tkprof=>

For this you have to type in SQL*Plus:

- alter session set sql_trace true;

-

- disconnect (this step is important, because it ensures all cursors get closed, and "row source operation" is generated)

- identify your trace file in the server directory as specified in the parameter user_dump_dest

- on your operating system: tkprof a.txt sys=no sort=prsela exeela fchela

The file a.txt will now give you valuable information on what has actually happened. No predictions but the truth.

By comparing the output from explain plan with the output from tkprof, you are able to identify the possible problem areas.

I am fully aware that this text is only a tiny fraction of what can be done, and that other people may choose different tools and actions, but the above gives you a very reasonable start at solving your performance problem.


Below is the sequence of statments we should follow while writing a sql query,

example:

1. filter column shold come first,
2. Thn join conditions,
3 etc

How to use explain plan:
SQL> explain plan for select * from scott.emp;

Explained.

SQL> select plan_table_output from table(dbms_xplan.display);



PLAN_TABLE_OUTPUT

--------------------------------------------------------------------------------Plan hash value: 3956160932



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


Id
Operation
Name
Rows
Bytes
Cost (%CPU)
Time


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


0
SELECT STATEMENT

14
518
3 (0)
00:00:01



1
TABLE ACCESS FULL
EMP
14
518
3 (0)
00:00:01


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



8 rows selected.



SQL> select * from V$version;

BANNER

--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

PL/SQL Release 11.1.0.6.0 - Production

CORE 11.1.0.6.0 Production

TNS for Linux: Version 11.1.0.6.0 - Production

NLSRTL Version 11.1.0.6.0 - Production


Most import is to read the explain plan in the tkprof.
and finally LIKE operater should be used as 'Word%' to give a match first and to improve Query performance.


Hope this blog or post would be useful.



Thanks,,,,
Rafi.

Undo and Redo concept ===>Aman's at its Best

Why not use Redo log for consistent read  by Aman:

Its not a stupid question as anything related to the functioning of Oralce database is not stupid. But the understanding of yours about redo is wrong. I do know that its termed that redo contains both the images of old and new but its actually not the correct thing. What redo contains is only change vectors which simply put, are just the changed entries of those places in the block which have got modified. This is true for both Data blocks which contain your data and Undo blocks as well which contain your old image. Think about it, what is an Undo Segment, its just like any other segment with the only difference that its maintained and modified by Oracle itself. When you modify a value, for example 100 to 200, the redo entery or the changed entry 200 is maintained in the Redo Buffer and from there, to the redo log file. The old entry must also modify the Undo Segment's some block and has to write itself there so there is a modification involved for that also which is again nothing but an update issued to the Undo segment. So the image changed for the Undo is also maintained in the Redo. Now, if let's suppose you lose both of your data block and the undo block, which means you lose the files of these two, using the redo you can recover both! Taht's what actually means by when it is said that redo contains both old and new image.

Now, if you have understood the above said, you must know that actual old/consistent image is maintained in the Undo segment only using which the consistent copy is created. Redo is used for the recovery whereas, Undo is used for the Read Consistacy.

HTH

Aman....

Well, you haven't understood still I guess. Okay, let's take a little deep dive.

When you do an update to an Oracle data block, whch means you change a salary value from 100 to 200, a change is done over the actual value(here 100). For this change, a Change Vector is generated which is actually an information that tells to oracle that this thing has happened and a code representation of the Update command for this value is generated. This Change Vector gets logged in the redo log buffer. For a transaction, multiple changes are logged into a redo stream which isnothing but the collection of the change vectors of that transaction. So the first thing you need to remember from now is that with the DML, the entire block doesn't get logged into the log buffer. Think about it, how it is possible in anyway since the size of the log buffer, in general is much smaller compared to other memory areas. If we would log a full block inthe log buffer, with few blocks only, it would touch its maximum size. So this is something you need to understand and remember.



Now if you have understood what I just said, you should understand this as well that the Undo blocks are no different from the data blocks. Both contain segments only but for the Undo blocks, the control is not with you. Only Oracle knows how to go and make changes to them! That's all what is different for them other than data blocks. So if there would be an update happening to the Undo segment to maintain the old image, from the above example 100, it would also be doing the same behavior that the normal table Update statment would do to the EMP table. So again, the same thing would be going to the redo stream. That's what I explained that in the redo, both the entries for the change of your data and the change for the Undo image are maintained and that's what the meaning of both Old and New values in the redo since using it , both can be recovered.



If all what above said is clear to you, you should have got an answer for your question that the recovery is done using the Redo's change vectors which are applied to the data files( Undo is also stored in a datafile which is a part of a tablespace) . If the Undo required is not available for the recovery of the uncommitted data, that would also be recreated using the Redo only which would recover the Undo as well , regenerating all of its contents! So you do need to care about the Redo/Archive only as the rest is going to be taken care by the Change Vectors stored in them!



I hope this all makes some sense for you and would help.



HTH

Aman....

 
  Thanks Aman  for the excellent explanation.
 
Best regards,
Rafi

LOCKING BY TOM'S MUST READ ONCE

Hi All,
Please read the below details to know about locking better:
Hi Tom,
Here are the few questions on Locking.
1)How do I use v$lock to figure out lock details ? Details like
whos has locked,on which table,who else is waiting, and what type
of locking (shared or exlusive ?), since when it is locked.
2)Why and when exclusive/shared locks are used ? Could you give me
example please ?
Thanks for the answer, I understand better.
3)Could you tell me significance of v$mystat and v$locked_object ?
Thank you .
4) Do we have page level locking in Oracle ?
Thank you
Thanks in advance,
Gururaj
and we said...1) Here is a small example showing how this happens. We will use three V$ tables in order
to see how this works; V$TRANSACTION, V$SESSION, and V$LOCK. V$TRANSACTION contains an
entry for every active transaction. V$LOCK contains an entry for all locks held as well
as locks being waited on. V$SESSION shows us the sessions logged in. We'll start by
starting a transaction in one session and looking at the state of the system at that
point:
tkyte@TKYTE816> update dept set deptno = deptno+10;
4 rows updated.
tkyte@TKYTE816> select username,
2 v$lock.sid,
3 trunc(id1/power(2,16)) rbs,
4 bitand(id1,to_number('ffff','xxxx'))+0 slot,
5 id2 seq,
6 lmode,
7 request
8 from v$lock, v$session
9 where v$lock.type = 'TX'
10 and v$lock.sid = v$session.sid
11 and v$session.username = USER
12 /
USERNAME SID RBS SLOT SEQ LMODE REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ----------
TKYTE 8 2 46 160 6 0
tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction
3 /
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
2 46 160
The interesting things to note here are:
ć The LMODE is 6 in the V$LOCK table and the request is 0. If you refer to the
definition of the V$LOCK table in the Oracle Server Reference, you will find that LMODE=6
is an exclusive lock. A value of 0 in the request means we are not making a request ¡V we
have the lock.
ć There is only one row in this table. This V$LOCK table is more of a queuing table than
a lock table. Many people expect there would be four rows in V$LOCK since we have four
rows locked. What you must remember however is that Oracle does not store a master list
of every row locked anywhere. To find out if a row is locked, we must go to that row.
ć I took the ID1 and ID2 columns, and performed a bit of bit manipulation on them.
Oracle needed to save three 16bit numbers, but only had two columns in order to do it.
So, the first column ID1 holds two of these numbers. By dividing by 2^16 with
trunc(id1/power(2,16)) rbs and by masking out the high bits with
bitand(id1,to_number('ffff','xxxx'))+0 slot, I am able to get the two numbers that are
hiding in that one number back out.
ć The RBS, SLOT, and SEQ values match the V$TRANSACTION information. This is my
transaction ID.
Now I'll start another session using the same user name, update some rows in EMP, and
then try to update DEPT:
tkyte@TKYTE816> update emp set ename = upper(ename);
14 rows updated.
tkyte@TKYTE816> update dept set deptno = deptno-10;
I am now blocked in this session. If we run the V$ queries again, we see:
tkyte@TKYTE816> select username,
2 v$lock.sid,
3 trunc(id1/power(2,16)) rbs,
4 bitand(id1,to_number('ffff','xxxx'))+0 slot,
5 id2 seq,
6 lmode,
7 request
8 from v$lock, v$session
9 where v$lock.type = 'TX'
10 and v$lock.sid = v$session.sid
11 and v$session.username = USER
12 /
USERNAME SID RBS SLOT SEQ LMODE REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ----------
TKYTE 8 2 46 160 6 0
TKYTE 9 2 46 160 0 6
TKYTE 9 3 82 163 6 0
tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction
3 /
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
3 82 163
2 46 160
What we see here is that a new transaction has begun ¡V (3,82,163) is the transaction ID.
It has two rows in V$LOCK this time. One row represents the locks that it owns (where
LMODE=6). It also has a row in there that shows a REQUEST with a value of 6. This is a
request for an exclusive lock. The interesting thing to note here is that the
RBS/SLOT/SEQ values of this request row are the transaction ID of the holder of the lock.
We can easily see that the transaction with SID=8 is blocking the transaction with SID=9.
Now, if we commit in SID = 8 the above changes:
tkyte@TKYTE816> select username,
2 v$lock.sid,
3 trunc(id1/power(2,16)) rbs,
4 bitand(id1,to_number('ffff','xxxx'))+0 slot,
5 id2 seq,
6 lmode,
7 request, block
8 from v$lock, v$session
9 where v$lock.type = 'TX'
10 and v$lock.sid = v$session.sid
11 and v$session.username = USER
12 /
USERNAME SID RBS SLOT SEQ LMODE REQUEST
-------- ---------- ---------- ---------- ---------- ---------- ----------
TKYTE 9 3 82 163 6 0
tkyte@TKYTE816> select XIDUSN, XIDSLOT, XIDSQN
2 from v$transaction
3 /
XIDUSN XIDSLOT XIDSQN
---------- ---------- ----------
3 82 163
that request row has gone ¡V it disappeared the instant the other session gave up its
lock. That request row was the queuing mechanism. The database is able to wake up the
blocked sessions the instant the transaction is completed. Note that the above gives us a
very easy way to see blockers and waiters:
tkyte@TKYTE816> select
(select username from v$session where sid=a.sid) blocker,
2 a.sid,
3 ' is blocking ',
4 (select username from v$session where sid=b.sid) blockee,
5 b.sid
6 from v$lock a, v$lock b
7 where a.block = 1
8 and b.request > 0
9 and a.id1 = b.id1
10 and a.id2 = b.id2
11 /
BLOCKER SID 'ISBLOCKING' BLOCKEE SID
-------- ---------- ------------- -------- ----------
TKYTE 8 is blocking TKYTE 9
simply by doing a self-join of V$LOCK with itself (I ran this query before committing the
session with SID=8).
2) exclusive lock -- I updated a row. no one else can update it until I commit. I have
an X lock on that row and only one person at a time can have an X lock. an X lock
provides serialization to a resource.
A shared lock -- when I update a table, I take a shared lock on the DEFINITION of the
table. Everyone else can do that as well (more then one session can get a shared lock on
the table definition). So, more than one person at a time can update the table. If you
wanted to ALTER the table, you would need an X lock on the defintion. You cannot get an
X lock when there are shared locks so you wait until there are no shared locks.
3) mystat has the statistics (cpu use, blocks read, cursors opened, etc) for your session
only. v$locked_object shows you all of the locks in the system.
4) no, never.

Oracle Database Cross Platform Transportable Tablespace

Hi All,
One more important interview question from ORACLE CORPORATION.
How you transport tablespace from one platform to other platform?
Ans: Using ORACLE'S TRANSPORTABLE TABLESPACE FEATURE FROM ORACLE 1og
Transporting Tablespaces Across Platforms
Starting with Oracle Database 10g, you can transport tablespaces across platforms. This functionality can be used to:
· Allow a database to be migrated from one platform to another
· Provide an easier and more efficient means for content providers to publish structured data and distribute it to customers running Oracle Database on different platforms
· Simplify the distribution of data from a data warehouse environment to data marts, which are often running on smaller platforms
· Enable the sharing of read-only tablespaces between Oracle Database installations on different operating systems or platforms, assuming that your storage system is accessible from those platforms and the platforms all have the same endianness, as described in the sections that follow
Many, but not all, platforms are supported for cross-platform tablespace transport. You can query the V$TRANSPORTABLE_PLATFORM view to see the platforms that are supported, and to determine each platform's endian format (byte ordering). The following query displays the platforms that support cross-platform tablespace transport:SQL> COLUMN PLATFORM_NAME FORMAT A32SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM; PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT----------- -------------------------------- -------------- 1 Solaris[tm] OE (32-bit) Big 2 Solaris[tm] OE (64-bit) Big 7 Microsoft Windows IA (32-bit) Little 10 Linux IA (32-bit) Little 6 AIX-Based Systems (64-bit) Big 3 HP-UX (64-bit) Big 5 HP Tru64 UNIX Little 4 HP-UX IA (64-bit) Big 11 Linux IA (64-bit) Little 15 HP Open VMS Little 8 Microsoft Windows IA (64-bit) Little 9 IBM zSeries Based Linux Big 13 Linux 64-bit for AMD Little 16 Apple Mac OS Big 12 Microsoft Windows 64-bit for AMD Little 17 Solaris Operating System (x86) Little 16 rows selected.
If the source platform and the target platform are of different endianness, then an additional step must be done on either the source or target platform to convert the tablespace being transported to the target format. If they are of the same endianness, then no conversion is necessary and tablespaces can be transported as if they were on the same platform.
Before a tablespace can be transported to a different platform, the datafile header must identify the platform to which it belongs. In an Oracle Database with compatibility set to 10.0.0 or later, you can accomplish this by making the datafile read/write at least once.
Limitations on Transportable Tablespace Use
Be aware of the following limitations as you plan to transport tablespaces:
· The source and target database must use the same character set and national character set.
· You cannot transport a tablespace to a target database in which a tablespace with the same name already exists. However, you can rename either the tablespace to be transported or the destination tablespace before the transport operation.
· Objects with underlying objects (such as materialized views) or contained objects (such as partitioned tables) are not transportable unless all of the underlying or contained objects are in the tablespace set.
· Beginning with Oracle Database 10g Release 2, you can transport tablespaces that contain XMLTypes, but you must use the IMP and EXP utilities, not Data Pump. When using EXP, ensure that the CONSTRAINTS and TRIGGERS parameters are set to Y (the default).
The following query returns a list of tablespaces that contain XMLTypes:select distinct p.tablespace_name from dba_tablespaces p, dba_xml_tables x, dba_users u, all_all_tables t where t.table_name=x.table_name and t.tablespace_name=p.tablespace_name and x.owner=u.username
See Oracle XML DB Developer's Guide for information on XMLTypes.
Transporting tablespaces with XMLTypes has the following limitations:
o The target database must have XML DB installed.
o Schemas referenced by XMLType tables cannot be the XML DB standard schemas.
o Schemas referenced by XMLType tables cannot have cyclic dependencies.
o Any row level security on XMLType tables is lost upon import. This is because the access control lists (ACLs) that implement the row level security cannot be imported, as the target database may not have the same set of users as the source database.
o If the schema for a transported XMLType table is not present in the target database, it is imported and registered. If the schema already exists in the target database, an error is returned unless the ignore=y option is set.
Additional limitations include the following:
Advanced Queues Transportable tablespaces do not support 8.0-compatible advanced queues with multiple recipients.
SYSTEM Tablespace Objects You cannot transport the SYSTEM tablespace or objects owned by the user SYS. Some examples of such objects are PL/SQL, Java classes, callouts, views, synonyms, users, privileges, dimensions, directories, and sequences.
Opaque Types Types whose interpretation is application-specific and opaque to the database (such as RAW, BFILE, and the AnyTypes) can be transported, but they are not converted as part of the cross-platform transport operation. Their actual structure is known only to the application, so the application must address any endianness issues after these types are moved to the new platform. Types and objects that use these opaque types, either directly or indirectly, are also subject to this limitation.
Floating-Point Numbers BINARY_FLOAT and BINARY_DOUBLE types are transportable using Data Pump but not the original export utility, EXP.
Compatibility Considerations for Transportable Tablespaces
When you create a transportable tablespace set, Oracle Database computes the lowest compatibility level at which the target database must run. This is referred to as the compatibility level of the transportable set. Beginning with Oracle Database 10g, a tablespace can always be transported to a database with the same or higher compatibility setting, whether the target database is on the same or a different platform. The database signals an error if the compatibility level of the transportable set is higher than the compatibility level of the target database.
The following table shows the minimum compatibility requirements of the source and target tablespace in various scenarios. The source and target database need not have the same compatibility setting.
Table 8-1 Minimum Compatibility Requirements
Transport Scenario
Minimum Compatibility Setting
Source Database
Target Database
Databases on the same platform
8.0
8.0
Tablespace with different database block size than the target database
9.0
9.0
Databases on different platforms
10.0
10.0

Transporting Tablespaces Between Databases: A Procedure and Example
The following steps summarize the process of transporting a tablespace. Details for each step are provided in the subsequent example.
1. For cross-platform transport, check the endian format of both platforms by querying the V$TRANSPORTABLE_PLATFORM view.
Ignore this step if you are transporting your tablespace set to the same platform.
2. Pick a self-contained set of tablespaces.
3. Generate a transportable tablespace set.
A transportable tablespace set (or transportable set) consists of datafiles for the set of tablespaces being transported and an export file containing structural information (metadata) for the set of tablespaces. You use Data Pump or EXP to perform the export.
Note:
If any of the tablespaces contain XMLTypes, you must use EXP.
If you are transporting the tablespace set to a platform with different endianness from the source platform, you must convert the tablespace set to the endianness of the target platform. You can perform a source-side conversion at this step in the procedure, or you can perform a target-side conversion as part of step 4.
Note:
This method of generating a transportable tablespace requires that you temporarily make the tablespace read-only. If this is undesirable, you can use the alternate method known as transportable tablespace from backup. See Oracle Database Backup and Recovery Advanced User's Guide for details.
4. Transport the tablespace set.
Copy the datafiles and the export file to a place that is accessible to the target database.
If you have transported the tablespace set to a platform with different endianness from the source platform, and you have not performed a source-side conversion to the endianness of the target platform, you should perform a target-side conversion now.
5. Import the tablespace set.
Invoke the Data Pump utility or IMP to import the metadata for the set of tablespaces into the target database.
Note:
If any of the tablespaces contain XMLTypes, you must use IMP.
Example
The steps for transporting a tablespace are illustrated more fully in the example that follows, where it is assumed the following datafiles and tablespaces exist:
Tablespace
Datafile
sales_1
/u01/oracle/oradata/salesdb/sales_101.dbf
sales_2
/u01/oracle/oradata/salesdb/sales_201.dbf

Step 1: Determine if Platforms are Supported and Endianness
This step is only necessary if you are transporting the tablespace set to a platform different from the source platform.
If you are transporting the tablespace set to a platform different from the source platform, then determine if cross-platform tablespace transport is supported for both the source and target platforms, and determine the endianness of each platform. If both platforms have the same endianness, no conversion is necessary. Otherwise you must do a conversion of the tablespace set either at the source or target database.
If you are transporting sales_1 and sales_2 to a different platform, you can execute the following query on each platform. If the query returns a row, the platform supports cross-platform tablespace transport.SELECT d.PLATFORM_NAME, ENDIAN_FORMAT FROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE d WHERE tp.PLATFORM_NAME = d.PLATFORM_NAME;
The following is the query result from the source platform:PLATFORM_NAME ENDIAN_FORMAT------------------------- --------------Solaris[tm] OE (32-bit) Big
The following is the result from the target platform:PLATFORM_NAME ENDIAN_FORMAT------------------------- --------------Microsoft Windows NT Little
You can see that the endian formats are different and thus a conversion is necessary for transporting the tablespace set.
Step 2: Pick a Self-Contained Set of Tablespaces
There may be logical or physical dependencies between objects in the transportable set and those outside of the set. You can only transport a set of tablespaces that is self-contained. In this context "self-contained" means that there are no references from inside the set of tablespaces pointing outside of the tablespaces. Some examples of self contained tablespace violations are:
· An index inside the set of tablespaces is for a table outside of the set of tablespaces.
Note:
It is not a violation if a corresponding index for a table is outside of the set of tablespaces.
· A partitioned table is partially contained in the set of tablespaces.
The tablespace set you want to copy must contain either all partitions of a partitioned table, or none of the partitions of a partitioned table. If you want to transport a subset of a partition table, you must exchange the partitions into tables.
· A referential integrity constraint points to a table across a set boundary.
When transporting a set of tablespaces, you can choose to include referential integrity constraints. However, doing so can affect whether or not a set of tablespaces is self-contained. If you decide not to transport constraints, then the constraints are not considered as pointers.
· A table inside the set of tablespaces contains a LOB column that points to LOBs outside the set of tablespaces.
· An XML DB schema (*.xsd) that was registered by user A imports a global schema that was registered by user B, and the following is true: the default tablespace for user A is tablespace A, the default tablespace for user B is tablespace B, and only tablespace A is included in the set of tablespaces.
To determine whether a set of tablespaces is self-contained, you can invoke the TRANSPORT_SET_CHECK procedure in the Oracle supplied package DBMS_TTS. You must have been granted the EXECUTE_CATALOG_ROLE role (initially signed to SYS) to execute this procedure.
When you invoke the DBMS_TTS package, you specify the list of tablespaces in the transportable set to be checked for self containment. You can optionally specify if constraints must be included. For strict or full containment, you must additionally set the TTS_FULL_CHECK parameter to TRUE.
The strict or full containment check is for cases that require capturing not only references going outside the transportable set, but also those coming into the set. Tablespace Point-in-Time Recovery (TSPITR) is one such case where dependent objects must be fully contained or fully outside the transportable set.
For example, it is a violation to perform TSPITR on a tablespace containing a table t but not its index i because the index and data will be inconsistent after the transport. A full containment check ensures that there are no dependencies going outside or coming into the transportable set. See the example for TSPITR in the Oracle Database Backup and Recovery Advanced User's Guide.
Note:
The default for transportable tablespaces is to check for self containment rather than full containment.
The following statement can be used to determine whether tablespaces sales_1 and sales_2 are self-contained, with referential integrity constraints taken into consideration (indicated by TRUE).EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('sales_1,sales_2', TRUE);
After invoking this PL/SQL package, you can see all violations by selecting from the TRANSPORT_SET_VIOLATIONS view. If the set of tablespaces is self-contained, this view is empty. The following example illustrates a case where there are two violations: a foreign key constraint, dept_fk, across the tablespace set boundary, and a partitioned table, jim.sales, that is partially contained in the tablespace set.SQL> SELECT * FROM TRANSPORT_SET_VIOLATIONS; VIOLATIONS---------------------------------------------------------------------------Constraint DEPT_FK between table JIM.EMP in tablespace SALES_1 and tableJIM.DEPT in tablespace OTHERPartitioned table JIM.SALES is partially contained in the transportable set
These violations must be resolved before sales_1 and sales_2 are transportable. As noted in the next step, one choice for bypassing the integrity constraint violation is to not export the integrity constraints.
See Also:
· Oracle Database PL/SQL Packages and Types Reference for more information about the DBMS_TTS package
· Oracle Database Backup and Recovery Advanced User's Guide for information specific to using the DBMS_TTS package for TSPITR
Step 3: Generate a Transportable Tablespace Set
Any privileged user can perform this step. However, you must have been assigned the EXP_FULL_DATABASE role to perform a transportable tablespace export operation.
Note:
This method of generating a transportable tablespace requires that you temporarily make the tablespace read-only. If this is undesirable, you can use the alternate method known as transportable tablespace from backup. See Oracle Database Backup and Recovery Advanced User's Guide for details.
After ensuring you have a self-contained set of tablespaces that you want to transport, generate a transportable tablespace set by performing the following actions:
1. Make all tablespaces in the set you are copying read-only.2. SQL> ALTER TABLESPACE sales_1 READ ONLY;3. 4. Tablespace altered.5. 6. SQL> ALTER TABLESPACE sales_2 READ ONLY;7. 8. Tablespace altered.9.
10. Invoke the Data Pump export utility on the host system and specify which tablespaces are in the transportable set.
Note:
If any of the tablespaces have XMLTypes, you must use EXP instead of Data Pump. Ensure that the CONSTRAINTS and TRIGGERS parameters are set to Y (the default).SQL> HOST $ EXPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_TABLESPACES = sales_1,sales_2
You must always specify TRANSPORT_TABLESPACES, which determines the mode of the export operation. In this example:
o The DUMPFILE parameter specifies the name of the structural information export file to be created, expdat.dmp.
o The DIRECTORY parameter specifies the default directory object that points to the operating system or Automatic Storage Management location of the dump file. You must create the DIRECTORY object before invoking Data Pump, and you must grant the READ and WRITE object privileges on the directory to PUBLIC. See Oracle Database SQL Reference for information on the CREATE DIRECTORY command.
o Triggers and indexes are included in the export operation by default.
If you want to perform a transport tablespace operation with a strict containment check, use the TRANSPORT_FULL_CHECK parameter, as shown in the following example:EXPDP system/password DUMPFILE=expdat.dmp DIRECTORY = dpump_dir TRANSPORT_TABLESPACES=sales_1,sales_2 TRANSPORT_FULL_CHECK=Y
In this example, the Data Pump export utility verifies that there are no dependencies between the objects inside the transportable set and objects outside the transportable set. If the tablespace set being transported is not self-contained, then the export fails and indicates that the transportable set is not self-contained. You must then return to Step 1 to resolve all violations.
Notes:
The Data Pump utility is used to export only data dictionary structural information (metadata) for the tablespaces. No actual data is unloaded, so this operation goes relatively quickly even for large tablespace sets.
11. When finished, exit back to SQL*Plus:12. $ EXIT
See Also:
Oracle Database Utilities for information about using the Data Pump utility
If sales_1 and sales_2 are being transported to a different platform, and the endianness of the platforms is different, and if you want to convert before transporting the tablespace set, then convert the datafiles composing the sales_1 and sales_2 tablespaces:
4. From SQL*Plus, return to the host system:5. SQL> HOST6.
7. The RMAN CONVERT command is used to do the conversion. Start RMAN and connect to the target database:8. $ RMAN TARGET /9. 10. Recovery Manager: Release 10.1.0.0.0 11. 12. Copyright (c) 1995, 2003, Oracle Corporation. All rights reserved.13. 14. connected to target database: salesdb (DBID=3295731590)15.
16. Convert the datafiles into a temporary location on the source platform. In this example, assume that the temporary location, directory /temp, has already been created. The converted datafiles are assigned names by the system.17. RMAN> CONVERT TABLESPACE sales_1,sales_2 18. 2> TO PLATFORM 'Microsoft Windows NT'19. 3> FORMAT '/temp/%U';20. 21. Starting backup at 08-APR-0322. using target database control file instead of recovery catalog23. allocated channel: ORA_DISK_124. channel ORA_DISK_1: sid=11 devtype=DISK25. channel ORA_DISK_1: starting datafile conversion26. input datafile fno=00005 name=/u01/oracle/oradata/salesdb/sales_101.dbf27. converted datafile=/temp/data_D-10_I-3295731590_TS-ADMIN_TBS_FNO-5_05ek24v528. channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:1529. channel ORA_DISK_1: starting datafile conversion30. input datafile fno=00004 name=/u01/oracle/oradata/salesdb/sales_101.dbf31. converted datafile=/temp/data_D-10_I-3295731590_TS-EXAMPLE_FNO-4_06ek24vl32. channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:4533. Finished backup at 08-APR-03
See Also:
Oracle Database Backup and Recovery Reference for a description of the RMAN CONVERT command
34. Exit Recovery Manager:35. RMAN> exit36. Recovery Manager complete.
Step 4: Transport the Tablespace Set
Transport both the datafiles and the export file of the tablespaces to a place that is accessible to the target database.
If both the source and destination are files systems, you can use:
· Any facility for copying flat files (for example, an operating system copy utility or ftp)
· The DBMS_FILE_TRANSFER package
· RMAN
· Any facility for publishing on CDs
If either the source or destination is an Automatic Storage Management (ASM) disk group, you can use:
· ftp to or from the /sys/asm virtual folder in the XML DB repository
See "Accessing Automatic Storage Management Files with the XML DB Virtual Folder" for more information.
· The DBMS_FILE_TRANSFER package
· RMAN
Caution:
Exercise caution when using the UNIX dd utility to copy raw-device files between databases. The dd utility can be used to copy an entire source raw-device file, or it can be invoked with options that instruct it to copy only a specific range of blocks from the source raw-device file.
It is difficult to ascertain actual datafile size for a raw-device file because of hidden control information that is stored as part of the datafile. Thus, it is advisable when using the dd utility to specify copying the entire source raw-device file contents.
If you are transporting the tablespace set to a platform with endianness that is different from the source platform, and you have not yet converted the tablespace set, you must do so now. This example assumes that you have completed the following steps before the transport:
1. Set the source tablespaces to be transported to be read-only.
2. Use the export utility to create an export file (in our example, expdat.dmp).
Datafiles that are to be converted on the target platform can be moved to a temporary location on the target platform. However, all datafiles, whether already converted or not, must be moved to a designated location on the target database.
Now use RMAN to convert the necessary transported datafiles to the endian format of the destination host format and deposit the results in /orahome/dbs, as shown in this hypothetical example:RMAN> CONVERT DATAFILE 2> '/hq/finance/work/tru/tbs_31.f',3> '/hq/finance/work/tru/tbs_32.f',4> '/hq/finance/work/tru/tbs_41.f'5> TO PLATFORM="Solaris[tm] OE (32-bit)"6> FROM PLATFORM="HP TRu64 UNIX"7> DB_FILE_NAME_CONVERT=8> "/hq/finance/work/tru/", "/hq/finance/dbs/tru"9> PARALLELISM=5;
You identify the datafiles by filename, not by tablespace name. Until the tablespace metadata is imported, the local instance has no way of knowing the desired tablespace names. The source and destination platforms are optional. RMAN determines the source platform by examining the datafile, and the target platform defaults to the platform of the host running the conversion.
See Also:
"Copying Files Using the Database Server" for information about using the DBMS_FILE_TRANSFER package to copy the files that are being transported and their metadata
Step 5: Import the Tablespace Set
Note:
If you are transporting a tablespace of a different block size than the standard block size of the database receiving the tablespace set, then you must first have a DB_nK_CACHE_SIZE initialization parameter entry in the receiving database parameter file.
For example, if you are transporting a tablespace with an 8K block size into a database with a 4K standard block size, then you must include a DB_8K_CACHE_SIZE initialization parameter entry in the parameter file. If it is not already included in the parameter file, this parameter can be set using the ALTER SYSTEM SET statement.
See Oracle Database Reference for information about specifying values for the DB_nK_CACHE_SIZE initialization parameter.
Any privileged user can perform this step. To import a tablespace set, perform the following tasks:
1. Import the tablespace metadata using the Data Pump Import utility, impdp:
Note:
If any of the tablespaces contain XMLTypes, you must use IMP instead of Data Pump.IMPDP system/password DUMPFILE=expdat.dmp DIRECTORY=dpump_dir TRANSPORT_DATAFILES= /salesdb/sales_101.dbf, /salesdb/sales_201.dbf REMAP_SCHEMA=(dcranney:smith) REMAP_SCHEMA=(jfee:williams)
In this example we specify the following:
o The DUMPFILE parameter specifies the exported file containing the metadata for the tablespaces to be imported.
o The DIRECTORY parameter specifies the directory object that identifies the location of the dump file.
o The TRANSPORT_DATAFILES parameter identifies all of the datafiles containing the tablespaces to be imported.
o The REMAP_SCHEMA parameter changes the ownership of database objects. If you do not specify REMAP_SCHEMA, all database objects (such as tables and indexes) are created in the same user schema as in the source database, and those users must already exist in the target database. If they do not exist, then the import utility returns an error. In this example, objects in the tablespace set owned by dcranney in the source database will be owned by smith in the target database after the tablespace set is imported. Similarly, objects owned by jfee in the source database will be owned by williams in the target database. In this case, the target database is not required to have users dcranney and jfee, but must have users smith and williams.
After this statement executes successfully, all tablespaces in the set being copied remain in read-only mode. Check the import logs to ensure that no error has occurred.
When dealing with a large number of datafiles, specifying the list of datafile names in the statement line can be a laborious process. It can even exceed the statement line limit. In this situation, you can use an import parameter file. For example, you can invoke the Data Pump import utility as follows:IMPDP system/password PARFILE='par.f'
where the parameter file, par.f contains the following:DIRECTORY=dpump_dirDUMPFILE=expdat.dmpTRANSPORT_DATAFILES="'/db/sales_jan','/db/sales_feb'"REMAP_SCHEMA=dcranney:smithREMAP_SCHEMA=jfee:williams
See Also:
Oracle Database Utilities for information about using the import utility
2. If required, put the tablespaces into read/write mode as follows:3. ALTER TABLESPACE sales_1 READ WRITE;4. ALTER TABLESPACE sales_2 READ WRITE;

Difference between RAID 0,RAID 1 AND RAID 5

Hi Friends,
This was one of the interview question in ORACLE CORPORATION INDIA P.V.T BANGALORE.
Difference between Linux RAID 0, RAID 1 and RAID 5
Dear members,While installing Linux it display information about RAID 0, 1 and 5. I would like to know what is the different between all these RAID options.Which one is the best and why?regards,JO
Ans:

RAID 0
=> Use if you want good read and write performance
=> Strips data across multiple disks
=> No Fault tolerance
=> Good for Workstation computers or application which needs fast disk I/O
=> Minimum requirement: 2 hard disk

RAID 1
=> Use for mirroring (hard disk to hard disk or partition to partition )
=> Fault tolerance => Use this if you want exact copies of data
=> Minimum requirement: 2 hard disks

RAID 5
=> Use for dedicated FILE/App server
=> Combination of RAID 0 and RAID 1
=> Read rate high
=> Write rate medium
=> Use this for database, Internet servers (such as web, mail etc), File server etc
=> Minimum requirement: 3 hard disks



RAID 0:Block striping, yields higher performance than with individual drives. There is no redundancy. ex. disk1 = 80G disk2 = 100G
total space = 160G

RAID 1:Drives are paired and mirrored. All data is 100% duplicated on anequivalent drive. Fully redundant. total space is equal to the size of the smallest disk.ex. disk1 = 80G disk2 = 100G
total space = 80G

RAID 5:Data is striped across several physical drives. Parity protection is usedfor data redundancy. Parity is stored on each disk. (in RAID 3, parity is stored in single disk). supports single disk failure. ex. disk1 = 80G disk2 = 100G disk3 = 120G
total space = 80*2=160G

Thanks,
Rafi.