srinivas_ganamur
asked on
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
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
ASKER
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.
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.
ASKER
it is an serious issue for me,i have to fix it immediately...please give me reply ASAP with solution.
Do you have RMAN configured?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
is there any data loss at the time of execution of dbms-repair.sql???
ASKER
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.....
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.....
ASKER
till now i did't get solution for my problem....
I am sorry I was not able to contact you. I am very busy in production. I will let you know soon.
ASKER
ok fine....but try to give me solution ASAP.
ASKER
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\SF
Output:
no records found
What could be the reason?
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\SF
Output:
no records found
What could be the reason?
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 ' '
DBMS_REPAIR.admin_tables (table_name => 'REPAIR_TABLE', ...);
Please check if you forgot to give single quote ' '
ASKER
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'
ORA-06550: line 3, column 3:
PL/SQL: Statement ignored
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'
ORA-06550: line 3, column 3:
PL/SQL: Statement ignored
Can you retry using sysdba account?
Or grant execute permission to current user using sysdba account. Then use sys.dbms_repair.repair_tab
ASKER
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';
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';
ASKER
please dnt mind...coz i am not a Oracle DBA person....i am SQL server DBA.
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)
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)
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
Do you have RMAN configured? If configured already, its very simple through RMAN
ASKER
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?
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?
ASKER
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......
ASKER
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?
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?
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.
If it successful, then recreate/rebuild all indexes.
ASKER
i have not received proper solution
blockrecover datafile 10 block 90 (Will only work for oracle 10g and above)