Saturday, January 15, 2011

Archive files are deleted mistakenly form my Database and RMAN show ORA-19625 error identifying file string

Archive files are deleted mistakenly form my Database and RMAN show ORA-19625 error identifying file string
====================================================================================================


Suddenly one of my DBA member deleted some Archive log files mistakenly. and he don't told me.but when he run RMAN backup script. he found ORA-19625 error. then he told me that.


Solution of this problem:-

==========================


Run in RMAN command line,

RMAN> CROSSCHECK ARCHIVELOG ALL;

RMAN> DELETE EXPIRED ARCHIVELOG ALL;



The first command verifies the list of archivelogs in the
controlfile with those actually present on disk. Files that
are not found on disk (those that were deleted
by mistake) will be marked as "EXPIRED".


The second command then identifies all the "EXPIRED" files,
presents the list to you and asks you to confirm if they
should be deleted -- meaning that they will be deleted from
the controlfile. RMAN, at the next backup run (or a backup that
you initiate immediately thereafter) will not look for
these files and will not error.

How to remove Oracle 11gR2 from windows 7

How to remove Oracle 11gR2 from windows 7 or from others os
========================================


1. First we need to download deinstall tool from oracle site.

The deinstall command removes standalone Oracle Database installations,
Oracle Clusterware and Oracle ASM from your server, and also Oracle Real Application
Clusters (Oracle RAC) and Oracle Database client installations.

The Deinstallation Tool (deinstall) is available in Oracle home directories after installation. It is located in the $ORACLE_HOME/deinstall directory.

The deinstall command uses the information you provide, plus information gathered from the software home to create a parameter file. You can alternatively supply a parameter file

The deinstall command stops Oracle software, and removes Oracle software and configuration files on the operating system.

To download the Deinstallation tool:

a. Go to the following URL:

http://www.oracle.com/technology/software/products/database/index.html

b. Under Oracle Database 11g Release 2, click See All for the respective platform for which you want to download the Deinstallation Tool.
The Deinstallation Tool is available for download at the end of this page.

or

c. you can search in google as key word
"Oracle De-install Utility (11.2.0.1.0) for windows"



2. log in as a Administrator

3. Unzip Deinstallation tool on your machine than follow below command.

4. install it.

Deinstallation Tool (deinstall) is available in Oracle home directories after installation.
It is located in the $ORACLE_HOME/deinstall directory.

5. start>run>cmd

6. set your Oracle 11gR2 home and than use below syntax:

C:\Documents and Settings\era-1>D:

D:\>cd download\database\windows\deinstall

D:\>set ORACLE_HOME= D:\oracle\product\11.2.0\db_1

D:\>deinstall -home D:\oracle\product\11.2.0\db_1


in unix like below
-------------------

$ cd /u01/app/oracle/product/11.2.0/dbhome_1/deinstall
$ ./deinstall



for more info about deinstall tools use:-

http://download.oracle.com/docs/cd/E11882_01/install.112/e17880/remove_oracle_sw.htm#BABEBHAA

Wednesday, January 12, 2011

How to rebuild a LOB Index in oracle

How to move a lob segment and a lob index to a differrent tablespace.
=====================================================================

How to rebuild a LOB Index in oracle
===================================

Normally you cannot move or rebuild a lob index to another tablespace like normal table. so you have to use little difference syntax. this is bellows ....


Example:-
=================

drop table image ;


CREATE TABLE image (
dname VARCHAR2(30),
sname VARCHAR2(30),
fname VARCHAR2(30),
hblob BLOB);



ALTER TABLE TEST MOVE TABLESPACE SYSTEM; ----ITS OK


Above command will move the table to new tablespace but will not move the
CLOB segment and it will still be in original tablespace. This is because LOB
data is stored outside of the table.

Check the tablespace of the CLOB column by issuing following sql.


SELECT index_name, tablespace_name
FROM user_indexes WHERE table_name = 'IMAGE';



Alter index SYS_IL0000098512C00004$$ rebuild tablespace SYSTEM;
---ITS FAIL WITH BELLOWS ERROR

ORA-02327: cannot create index on expression with datatype LOB


---------ITS OK
ALTER TABLE image MOVE LOB (hblob)
STORE AS (TABLESPACE system);


----see again
SELECT index_name, tablespace_name
FROM user_indexes WHERE table_name = 'IMAGE';



SELECT * FROM user_lobs;



[Note : "small" LOBs stored inline (ie in the row itself) are not in a
seperate LOB SEGMENT at all. That is called STORAGE IN ROW and is the
default for LOBs of 4000bytes or less.]



---for generating a script
select 'alter table '||table_name||' move tablespace YOUR_TS'||chr(10)||
'LOB ('||column_name||') store as '||segment_name||chr(10)||
'(tablespace YOUR_TS);'
from user_lobs

ORA-01555: snapshot too old: rollback segment number with name "" too small

ORA-01555: snapshot too old: rollback segment number with name "" too small
============================================================================


Quick check & solution:-



1) Increase size of rollback segment which will reduce the likelihood of overwriting
rollback information that is needed.

2) UNDO_RETENTION parameter value is enough.

3) if possible try to tune the longest running queries

4) Reduce the number of commits .
(ORA-01555 frequently caused by COMMIT within PL/SQL LOOP)

5) Run the processing against a range of data rather than the whole table.

6) Add additional rollback segments. This will allow the updates etc. to be spread
across more rollback segments thereby reducing the chances of overwriting required
rollback information.

7) If fetching across commits, the code can be changed so that this is not done.

8) Ensure that the outer select does not revisit the same block at different times
during the processing.





Analysis:
=========

---find out longest running query.
--------------------------------------------

SQL> select max(MAXQUERYLEN) from v$undostat;

MAX(MAXQUERYLEN)
----------------
81431

SQL>


if we increase undo_retention more than longest running query can
avoid ORA-01555 SNAPSHOT too old errors.




****Execute following query to get the optimal undo_retention size to be increased.
----------------------------------------------------------------------

SQL>
SQL> SELECT d.undo_size/(1024*1024) "ACTUAL UNDO SIZE [MByte]",
2 SUBSTR(e.value,1,25) "UNDO RETENTION [Sec]",
3 ROUND((d.undo_size / (to_number(f.value) *
4 g.undo_block_per_sec))) "OPTIMAL UNDO RETENTION [Sec]"
5 FROM (
6 SELECT SUM(a.bytes) undo_size
7 FROM v$datafile a,
8 v$tablespace b,
9 dba_tablespaces c
10 WHERE c.contents = 'UNDO'
11 AND c.status = 'ONLINE'
12 AND b.name = c.tablespace_name
13 AND a.ts# = b.ts#
14 ) d,
15 v$parameter e,
16 v$parameter f,
17 (
18 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
19 undo_block_per_sec
20 FROM v$undostat
21 ) g
22 WHERE e.name = 'undo_retention'
23 AND f.name = 'db_block_size'
24 /

ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] OPTIMAL UNDO RETENTION [Sec]
------------------------ ------------------------- ----------------------------
23552 900 12993





SQL>
SQL>
SQL> SELECT *
2 FROM (SELECT begin_time, txncount, maxquerylen, ssolderrcnt,
3 nospaceerrcnt, unxpstealcnt, expstealcnt
4 FROM v$undostat
5 ORDER BY begin_time DESC)
6 WHERE ROWNUM <= 30 ;

BEGIN_TIM TXNCOUNT MAXQUERYLEN SSOLDERRCNT NOSPACEERRCNT UNXPSTEALCNT EXPSTEALCNT
--------- ---------- ----------- ----------- ------------- ------------ -----------
12-JAN-11 6596 47 0 0 0 9
12-JAN-11 2382 242 0 0 0 0
12-JAN-11 2441 4464 0 0 0 0
12-JAN-11 2183 3855 0 0 0 0
12-JAN-11 2100 3250 0 0 0 0
12-JAN-11 2139 2641 0 0 0 0
12-JAN-11 7795 4453 0 0 0 0
12-JAN-11 2489 3849 0 0 0 0
12-JAN-11 2426 3248 0 0 0 0
12-JAN-11 2261 2647 0 0 0 0
12-JAN-11 2544 2039 0 0 0 0
12-JAN-11 2393 1143 0 0 0 0
12-JAN-11 7963 844 0 0 0 4
12-JAN-11 2030 238 0 0 0 2
12-JAN-11 1272 541 0 0 0 0
12-JAN-11 1223 30 0 0 0 1
12-JAN-11 720 233 0 0 0 0
12-JAN-11 600 1739 0 0 0 0
12-JAN-11 6023 1131 0 0 0 2
12-JAN-11 325 522 0 0 0 0
12-JAN-11 244 750 0 0 0 0
12-JAN-11 219 270 0 0 0 0
12-JAN-11 160 0 0 0 0 0
12-JAN-11 277 0 0 0 0 0
12-JAN-11 5850 0 0 0 0 31
12-JAN-11 175 0 0 0 0 0
12-JAN-11 164 0 0 0 0 0
12-JAN-11 140 0 0 0 0 1
12-JAN-11 185 0 0 0 0 0
12-JAN-11 134 0 0 0 0 0

30 rows selected.

SQL>







===================================================
introduction:
===================================================

An ORA-01555 is never about running out of rollback. It is about rollback that was
generated being overwritten. A select statement will not cause rollback to be "held".
As soon as the transaction that generated the rollback commits - that rollback may be
reused and if it is and it is needed by some query, you will get an ORA-01555.

if you size your rollback adequately, neither will you.

The ORA-01555 happens when people try to save space typically. They'll have small
rollback segments that could grow if they needed (and will shrink using OPTIMAL).

So, they'll start with say 10 or so 1 Mb rollback segments. These rollback segments COULD
grow to 100 MB each if we let them (in this example) however, they will NEVER grow unless
you get a big transaction.

If your database does lots of little transactions, the rollback segment will never grow on their own.
They will stay small.

Now, someone needs to run a query that will take 5 minutes. On your system however the
rollback wraps every 2 minutes due to lots of little transactions going on. In this
system, ORA-01555 will happen frequently.

What you need to do here is size rollback so that it wraps less frequently (less frequently
then your long running queries). Here if you sized the rollback so that you had 10, 10 MB segments
(not so they could GROW to 10MB but that they are starting at 10MB)
we would wrap maybe every 20 minutes now. that'll
give that 5 minute query plenty of time to complete without reusing rollback it needs.






===============================================
ORA-01555 Explanation
===============================================

There are two fundamental causes of the error ORA-01555 that are a result of Oracle
trying to attain a 'read consistent' image. These are :

*** The rollback information itself is overwritten so that Oracle is unable to rollback
the (committed) transaction entries to attain a sufficiently old enough version of the
block.

*** The transaction slot in the rollback segment's transaction table (stored in the
rollback segment's header) is overwritten, and Oracle cannot rollback the transaction
header sufficiently to derive the original rollback segment transaction slot.

Both of these situations are discussed below with the series of steps that cause the
ORA-01555. In the steps, reference is made to 'QENV'. 'QENV' is short for 'Query
Environment', which can be thought of as the environment that existed when a query is
first started and to which Oracle is trying to attain a read consistent image. Associated
with this environment is the SCN
(System Change Number) at that time and hence, QENV 50 is the query environment with SCN
50.

CASE 1 - ROLLBACK OVERWRITTEN

This breaks down into two cases: another session overwriting the rollback that the
current session requires or the case where the current session overwrites the rollback
information that it requires. The latter is discussed in this article because this is
usually the harder one to understand.

Steps:

1. Session 1 starts query at time T1 and QENV 50

2. Session 1 selects block B1 during this query

3. Session 1 updates the block at SCN 51

4. Session 1 does some other work that generates rollback information.

5. Session 1 commits the changes made in steps '3' and '4'.
(Now other transactions are free to overwrite this rollback information)

6. Session 1 revisits the same block B1 (perhaps for a different row).

Now, Oracle can see from the block's header that it has been changed and it is
later than the required QENV (which was 50). Therefore we need to get an image of the
block as of this QENV.

If an old enough version of the block can be found in the buffer cache then we
will use this, otherwise we need to rollback the current block to generate another
version of the block as at the required QENV.

It is under this condition that Oracle may not be able to get the required
rollback information because Session 1's changes have generated rollback information that
has overwritten it and returns the ORA-1555 error.

CASE 2 - ROLLBACK TRANSACTION SLOT OVERWRITTEN

1. Session 1 starts query at time T1 and QENV 50

2. Session 1 selects block B1 during this query

3. Session 1 updates the block at SCN 51

4. Session 1 commits the changes
(Now other transactions are free to overwrite this rollback information)

5. A session (Session 1, another session or a number of other sessions) then use the
same rollback segment for a series of committed transactions.

These transactions each consume a slot in the rollback segment transaction table
such that it eventually wraps around (the slots are written to in a circular fashion) and
overwrites all the slots. Note that Oracle is free to reuse these slots since all
transactions are committed.

6. Session 1's query then visits a block that has been changed since the initial QENV
was established. Oracle therefore needs to derive an image of the block as at that point
in time.

Next Oracle attempts to lookup the rollback segment header's transaction slot
pointed to by the top of the data block. It then realises that this has been overwritten
and attempts to rollback the changes made to the rollback segment header to get the
original transaction slot entry.

If it cannot rollback the rollback segment transaction table sufficiently it will
return ORA-1555 since Oracle can no longer derive the required version of the data block.


It is also possible to encounter a variant of the transaction slot being overwritten
when using block cleanout. This is briefly described below :

Session 1 starts a query at QENV 50. After this another process updates the blocks that
Session 1 will require. When Session 1 encounters these blocks it determines that the
blocks have changed and have not yet been cleaned out (via delayed block cleanout).
Session 1 must determine whether the rows in the block existed at QENV 50, were
subsequently changed,

In order to do this, Oracle must look at the relevant rollback segment transaction table
slot to determine the committed SCN. If this SCN is after the QENV then Oracle must try
to construct an older version of the block and if it is before then the block just needs
clean out to be good enough for the QENV.

If the transaction slot has been overwritten and the transaction table cannot be rolled
back to a sufficiently old enough version then Oracle cannot derive the block image and
will return ORA-1555.

(Note: Normally Oracle can use an algorithm for determining a block's SCN during block
cleanout even when the rollback segment slot has been overwritten. But in this case
Oracle cannot guarantee that the version of the block has not changed since the start of
the query).


=============================================
Solutions
=============================================

This section lists some of the solutions that can be used to avoid the ORA-01555 problems
discussed in this article. It addresses the cases where rollback segment information is
overwritten by the same session and when the rollback segment transaction table entry is
overwritten.

It is worth highlighting that if a single session experiences the ORA-01555 and it is not
one of the special cases listed at the end of this article, then the session must be
using an Oracle extension whereby fetches across commits are tolerated. This does not
follow the ANSI model and in the rare cases where
ORA-01555 is returned one of the solutions below must be used.

CASE 1 - ROLLBACK OVERWRITTEN

1. Increase size of rollback segment which will reduce the likelihood of overwriting
rollback information that is needed.

2. Reduce the number of commits (same reason as 1).

3. Run the processing against a range of data rather than the whole table. (Same
reason as 1).

4. Add additional rollback segments. This will allow the updates etc. to be spread
across more rollback segments thereby reducing the chances of overwriting required
rollback information.

5. If fetching across commits, the code can be changed so that this is not done.

6. Ensure that the outer select does not revisit the same block at different times
during the processing. This can be achieved by :

- Using a full table scan rather than an index lookup
- Introducing a dummy sort so that we retrieve all the data, sort it and then
sequentially visit these data blocks.

CASE 2 - ROLLBACK TRANSACTION SLOT OVERWRITTEN

1. Use any of the methods outlined above except for '6'. This will allow transactions
to spread their work across multiple rollback segments therefore reducing the likelihood
or rollback segment transaction table slots being consumed.

2. If it is suspected that the block cleanout variant is the cause, then force block
cleanout to occur prior to the transaction that returns the ORA-1555. This can be
achieved by issuing the following in SQL*Plus, SQL*DBA or Server Manager :

alter session set optimizer_goal = rule;
select count(*) from table_name;

If indexes are being accessed then the problem may be an index block and clean out
can be forced by ensuring that all the index is traversed. Eg, if the index is on a
numeric column with a minimum value of 25 then the following query will force cleanout of
the index :

select index_column from table_name where index_column > 24;

ORA-01991:invalid password file

ORA-01991:invalid password file
===================================

Solution:-

Shutdown the database and recreate the password file using orapwd utility.

$cd ORACLE_HOME/bin/

$orapwd file=$ORACLE_HOME/dbs/pwd.ora password= entries=5

Then start the Database.


for info

sql> show parameter remote_login

sql> select * from v$pwfile_users;

Tuesday, January 11, 2011

How to Insert multimedia data (image, video) in oracle table

How to Insert multimedia data(image, video) in oracle table.
-----------------------------------------------------------

Follow the bellows steps...

How to save blob data to disk from oracle table?

How to insert image/pdf/multimedia or blob data to oracle database table from client machine without using database directory ?



1)Create Directory Where multimedia resides.

create or replace directory temp as 'C:/dir_blob';

2)Grant read permission to the user who work with this directory.

grant read on directory temp to test;

3)Create the Table which holds Lov object.

-- the storage table for the image file
CREATE TABLE image (
dname VARCHAR2(30), -- directory name
sname VARCHAR2(30), -- subdirectory name
fname VARCHAR2(30), -- file name
iblob BLOB); -- image file

4)Create the procedure that insert multimedia objects.

/* Formatted on 2011/01/11 18:10 (Formatter Plus v4.8.8) */
-- create the procedure to load the file

CREATE OR REPLACE PROCEDURE load_file (
pdname VARCHAR2,
psname VARCHAR2,
pfname VARCHAR2
)
IS
src_file BFILE;
dst_file BLOB;
lgh_file BINARY_INTEGER;
BEGIN
src_file := BFILENAME ('TEMP', pfname);

-- insert a NULL record to lock
INSERT INTO image
(dname, sname, fname, iblob
)
VALUES (pdname, psname, pfname, EMPTY_BLOB ()
)
RETURNING iblob
INTO dst_file;

-- lock record
SELECT iblob
INTO dst_file
FROM image
WHERE dname = pdname AND sname = psname AND fname = pfname
FOR UPDATE;

-- open the file
DBMS_LOB.fileopen (src_file, DBMS_LOB.file_readonly);
-- determine length
lgh_file := DBMS_LOB.getlength (src_file);
-- read the file
DBMS_LOB.loadfromfile (dst_file, src_file, lgh_file);

-- update the blob field
UPDATE image
SET iblob = dst_file
WHERE dname = pdname AND sname = psname AND fname = pfname;

-- close file
DBMS_LOB.fileclose (src_file);
END load_file;
/

5)Execute the Procedure.

SQL> exec load_file('TEMP','This is Image','image.png');
PL/SQL procedure successfully completed.


7)From Oracle Determine multimedia size..

1 declare
2 a blob;
3 begin
4 select iblob into a from image;
5 dbms_output.put_line(dbms_lob.getlength(a));
6* end;
SQL> /

PL/SQL procedure successfully completed.

SQL> set serveroutput on
SQL> /
66666

PL/SQL procedure successfully completed.


How to save blob data to disk from oracle table?





Connect to the oracle instance without creating any database

Connect to the oracle instance without creating any database
-------------------------------------------------------------
suppose requirement like bellows :-

I have not created any database.
I have only install the oracle software.
I just want to connect to the Ideal instance of database through sqlplus.


Solution:-
=========
create a service name with oradim utility .

CMD> set oracle_sid=prod

CMD> oradim -NEW -SID prod -STARTMODE auto -PFILE C:\oracle\product\10.1.0\admin\prod\pfile\init.ora

Then start the service ...

then you can connect to the idle instance ..

How to create an HTML content document easily

How to create an HTML content document easily
==================================

To create your new HTML document, use one of the following two methods.
-----------------------------------------------------------------------------------------------------------
Method 1

1. Start Ms Word 2003.
2. In the New Document task pane, click Web Page under New.
3. Click File, and then click Save.

Note The Save as type box uses Single File Web Page (*.mht; *.mhtml), or you can switch to Web Page (*.htm, *.html).
4. In the File name box, type the file name that you want for your document, and then click Save.

Method 2

1. Start Word 2003.
2. Click File, and then click Save as Web Page.
3. In the File name box, type the file name that you want for your document, and then click Save.


Add Text and Hyperlinks to Your HTML Document
-------------------------------------------------------------------------

1. Open the HTML document that you created earlier in this article. To do this, follow these steps:
1. Click File, and then click Open.
2. Locate your saved article, in the "Create Your HTML Document" section of this article.
3. Click the file, and then click Open.
2. Type the following text in the document:
You can use Microsoft Word to create HTML documents as easily as you can create plain Word documents.
3. To create a hyperlink, in the text that you typed, click the words Microsoft Word.
4. On the Insert menu, click Hyperlink.
5. In the Insert Hyperlink dialog box, type http://www.microsoft.com/word in the Address box, and then click OK.
6. Save your changes to the document.


Add an Image to Your HTML Document
-----------------------------------------------------------
1. Put your insertion point where you want the image to be in your document.
2. Click Insert, point to Picture, and then click ClipArt.
3. In the ClipArt task pane, click Search.

Note If you click Search without typing anything in the Search Text box, the search result will display all the images that are currently available on your system.
4. In the Results section, select the image that you want to insert in the page.
5. Save your changes and then close the document.


Open an HTML Document in Word
--------------------------------------------------
1. Click File, and then click Open.
2. In the Open dialog box, locate the HTML document that you created earlier, and then select it.
3. Click Open.