Saturday 27 December 2014

restore sql server failed exclusive access could not be obtained

alter database Axe_Credit  set offline with rollback immediate
alter database Axe_Credit  set online




Sunday 21 December 2014

RMAN-03002: failure of switch command at ORA-19563: header validation failed for file

Solution:
I used below query to fix the path for the restored files, as path was wrong for some datafiles
select 'set newname for datafile '||file#||' to  ''/u01/oradata/jblprov/'|| SUBSTR (NAME, INSTR (NAME, '/', -1) + 1)||''';' from v$datafile order by 1



Monday 15 December 2014

SSL ORA-28788: user provided invalid information, or an unknown error ORA-28759: failure to open file

Error1:
UTL_HTTP.REQUEST_FAILED; WEB SERVER IP ADDRESS = server110 ERR : POWERCARD/PCARD001@pcard===ORA-28788: user provided invalid information, or an unknown error ==> 
ORA-06512: at "POWERCARD.PCRD_REPORT_TOOLS_1", line 509
ORA-06512: at "SYS.UTL_HTTP", line 1674
ORA-28788: user provided invalid information, or an unknown error>
Reason: Invalid Wallet / Wallet Path is given
Solution: Give the correct application server wallet path in PCRD_REPORT_TOOLS_1.

Error2:
UTL_HTTP.REQUEST_FAILED; WEB SERVER IP ADDRESS = server110 ERR : POWERCARD/PCARD001@pcard===ORA-28759: failure to open file ==> 
ORA-06512: at "POWERCARD.PCRD_REPORT_TOOLS_1", line 509
ORA-06512: at "SYS.UTL_HTTP", line 1674
ORA-28759: failure to open file>
Reason: Invalid permissions to Wallet directory

Solution: Give 777 permissions on wallet.

ORA-01450: maximum key length (3215) exceeded

Error Description
Whenever I try to rebuild index online then it fails with message ORA-00604: error occurred at recursive SQL level 1 along with ORA-01450: maximum key length (3215) exceeded. Below is the scenario.
SQL> create table tab1(a varchar2(3000),b varchar2(2000));
Table created.

SQL> create index tab1_I on tab1(a,b);
Index created.

SQL> alter index tab1_I rebuild online;
alter index tab1_I rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

Let's now create one table with column length 3000+199=3199 bytes and see what happens.
SQL> create table tab3(a varchar2(3000),b varchar2(199));
Table created.

SQL> create index tab3_I on tab3(a,b);
Index created.

Try to rebuild it online and it works.
SQL> alter index tab3_I rebuild online;
Index altered.

Now just add extra 1 bytes on column b. And whenever we try to rebuild it online it will fail.

SQL> alter table tab3 modify b varchar2(200);
Table altered.

SQL> alter index tab3_I rebuild online;
alter index tab3_I rebuild online
*
ERROR at line 1:
ORA-00604: error occurred at recursive SQL level 1
ORA-01450: maximum key length (3215) exceeded

Cause of the Problem
When creating a index the total length of the index cannot exceed a certain value. Primarily this value depends on DB_BLOCK_SIZE.
If 2K block size then maximum index key length=758
If 4K block size then maximum index key length=1578
If 8K block size then maximum index key length=3218
If 16K block size then maximum index key length=6498
How the maximum index key length is measured by?
Maximum index key length=Total index length (Sum of width of all indexed column+the number of indexed columns)+Length of the key(2 bytes)+ROWID(6 bytes)+the length of the rowid(1 byte)

The index key size is limited by the value of db_block_size, because a key value may not span multiple blocks. So, based on the size of the block size of index depends. In fact, it is required that any index block must contain at least TWO index entries per block.

So we can say that the maximum key length for an index will be less than half of
the DB_BLOCK_SIZE. But we know that in a block there also needed space for PCTFREE, INITRANS and space for block overhead(Block Header,ROW Directory, Table Directory, etc). After considering these bytes the actual space that can be used for the Index key is actually just over 1/3 of the DB_BLOCK_SIZE.

Solution of the Problem
The causes already indicates what might be the solutions. Solution may be,
1)Increase your database block size. Create a tablespace with bigger block size and create index on that tablespace.

2)If you have index on multiple columns then you can split index to single or 2 columns so that size does not extended over it can handle.

3)Rebuild the index without online clause. That is 
ALTER INDEX index_name REBUILD;
Because The online rebuild of the index creates a journal table and index. This internal journal IOT table contains more columns in its index. This is a feature of online rebuild. This time the error arises because that current value of the initialization parameter db_block_size is not large enough to create internal journal IOT

Tuesday 2 December 2014

Could not locate file for database in sys.database_files. The file either does not exist, or was dropped.

While trying to shrink log file we got below error:

Use ICCSPro
GO
Alter Database XXX Set Recovery Simple
GO
DBCC SHRINKFILE ('YYYYYY', 500)
GO
Alter Database XXX Set Recovery Full
GO

Could not locate file  for database in sys.database_files. The file either does not exist, or was dropped.

Solution:
Use the file_id in sys.database_files instead of file name
Change  DBCC SHRINKFILE ('YYYYYY', 500)
to             DBCC SHRINKFILE (2, 500) -- Two is the file id in our database.

Number of Visitors