• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 757
  • Last Modified:

Oracle log files which are not getting flush ( size is increasing continuously)

Hi,

Currently i am facing below issue, please give me solution ASAP.

We have SFMS application for message based fund transfer given by IDRBT.
There is automated procedure for archiving of history data in this application, which scheduled daily basis.

The application is on Win2000 advance server and uses Oracle 9i database.

The error is while archiving the data from live tables to archive tables in archiving file.
Due to this archiving the data (moving) from live tables and file to Archive tables and file is not happening and the data size in live tables is increasing.
 
I am attaching both Oracle trc file and application log file for your reference. It shows the file name and no. and the block no. which is corrupt. Also it is showing the Oracle error code.
ApplicationLogFile.txt
sfms-ora-9064.txt
0
srinivas_ganamur
Asked:
srinivas_ganamur
  • 15
  • 10
1 Solution
 
Devinder Singh VirdiCommented:
It looks like that there are corrupted blocks in file 10. Use DBVerify(dbv) and Analyze/DBMS_REPAIR utility/package to validate this. or use RMAN command
blockrecover datafile 10 block 90 (Will only work for oracle 10g and above)
0
 
srinivas_ganamurAuthor Commented:
Dear Master,

already i have mentioned my current database is 9i, please give me solution.........it is gr8 help from you...i am waiting for your positive response.
0
 
srinivas_ganamurAuthor Commented:
it is an serious issue for me,i have to fix it immediately...please give me reply ASAP with solution.
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
Devinder Singh VirdiCommented:
Do you have RMAN configured?
0
 
Devinder Singh VirdiCommented:
Please follows the steps in attached file.
or click the following link
http://www.oracle-base.com/articles/8i/DetectAndCorrectCorruption.php
DBMS-REPAIR.sql
0
 
srinivas_ganamurAuthor Commented:
Hi Master,

Thanks for you quick response...today i am executing attached script in production server & i will let you know the result. I f anything you missed in these script, please message me.

regards,
Srinivas
0
 
srinivas_ganamurAuthor Commented:
is there any data loss at the time of execution of dbms-repair.sql???
0
 
srinivas_ganamurAuthor Commented:
Dear Virdi_ds,

today i have executed your attached script......

In first step it was asking table name (repair_table)..but i dnt know the exact table name..please let me know what i have to do..


when i executed first step script i got the following error;

REPAIR_TABLE must be declared....please once again see my attached file and give me solution.

the error is coming after ADS table(please see attaced script)...today i have checked, there is no table called ADS (user_objects)...i am becoming mad for above problem....

please reply me with solution.....in REPAIR_TABLE what tablename i have to specify.....
0
 
srinivas_ganamurAuthor Commented:
till now i did't get solution for my problem....
0
 
Devinder Singh VirdiCommented:
I am sorry I was not able to contact you. I am very busy in production. I will let you know soon.
0
 
srinivas_ganamurAuthor Commented:
ok fine....but try to give me solution ASAP.
0
 
srinivas_ganamurAuthor Commented:
Hi Virdi_ds,

Still my issue is pending....today have executed below query to list objects in dbf file

SELECT SEGMENT_TYPE,SEGMENT_NAME FROM V$DATAFILE V ,DBA_EXTENTS D WHERE
V.FILE#=D.FILE_ID AND NAME='E:\ORACLE\ORADATA\SFMS\SFMS_BRACH\SFMS_ARCH_NDX1.DBF';

Output:
no records found

What could be the reason?
0
 
Devinder Singh VirdiCommented:
REPAIR_TABLE is the name of table that you want to create using
DBMS_REPAIR.admin_tables (table_name => 'REPAIR_TABLE', ...);

Please check if you forgot to give single quote ' '
0
 
srinivas_ganamurAuthor Commented:
Please see the below error:

SQL> declare
  2  begin
  3    dbms_repair.admin_tables(
  4      table_name => 'REPAIR_TABLE',
  5      table_type => dbms_repair.repair_table,
  6      action => dbms_repair.create_action,
  7      tablespace => 'USERS');
  8  END;
  9  /
    table_type => dbms_repair.repair_table,
                  *
ERROR at line 5:
ORA-06550: line 5, column 19:
PLS-00201: identifier 'DBMS_REPAIR.REPAIR_TABLE' must be declared
ORA-06550: line 3, column 3:
PL/SQL: Statement ignored
0
 
Devinder Singh VirdiCommented:
Can you retry using sysdba account?
0
 
Devinder Singh VirdiCommented:
Or grant execute permission to current user using sysdba account. Then use sys.dbms_repair.repair_table.
0
 
srinivas_ganamurAuthor Commented:
using dba account (command prompt)..i am sucessfully executed step1. in second step 2, it was asking enter value for schema_name and enter the value for object:

what schema_name /object name i have to specify that is output of below select query:

SELECT OWNER, OBJECT_NAME, OBJECT_TYPE FROM DBA_OBJECTS WHERE OBJECT_NAME LIKE '%REPAIR_TABLE';
0
 
srinivas_ganamurAuthor Commented:
please dnt mind...coz i am not a Oracle DBA person....i am SQL server DBA.
0
 
Devinder Singh VirdiCommented:
Schema name should be the one where all your application tables are present.
Sys and system are dba account. Do you know where all your tables are stored?
Actually we need to find which table is causing this problem. I believe UAI_USER_ACC_INFO is causing the problem and type its schema name(username)
0
 
Devinder Singh VirdiCommented:
Also start working with the link I provided in my first comment.

Do you have RMAN configured? If configured already, its very simple through RMAN
0
 
srinivas_ganamurAuthor Commented:
no RMAN configuration....


using below query i find the schema name

select owner schema, table_name as "Table" ,Column_name as "Column" from dba_tab_columns
order by owner,table_name,column_id

but what object name i have to specify... (in step 2)

As per your guess, shall i rebuild PK index of UAI_USER_ACC_INFO table?
0
 
srinivas_ganamurAuthor Commented:
if we rebuild index of that particular table, please let me know how to find PK index UAI_USER_ACC_INFO table? is there any query......
0
 
srinivas_ganamurAuthor Commented:
I have checked index of UAI_USER_ACC_INFO .

select index_name from all_indexes
where table_name = 'UAI_USER_ACC_INFO';

Output:
SYS_C002518.

rebilding this index, my problem will fix or any other issues?


0
 
Devinder Singh VirdiCommented:
Use analyze command to find if there is any problem in UAI_USER_ACC_INFO Table. This is the first step mentioned in the document. Try this first.
If it successful, then recreate/rebuild all indexes.
0
 
srinivas_ganamurAuthor Commented:
i have not received proper solution
0

Featured Post

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

  • 15
  • 10
Tackle projects and never again get stuck behind a technical roadblock.
Join Now