SQL & PL/SQL :: DB Locks And Data Corruption?

Feb 25, 2012

I have a sql script where triggers, procedures and functions are written. The triggers are causing db outages and causing problems in the application as well.

I am trying my best but with my low experience and expertise, am not able to make good progress.

Scenario: Course enrollments are inserted, deleted and updated in course_main and course_users table. This is done in gui as well as in background snapshot scheduler in a cron process. Course_main table contains all course enrollments and course_users table has crsmain_pk1 as foreign key.

Its quite a big file and am not sure what should i paste here so am uploading the file in txt.

View -1 Replies


ADVERTISEMENT

Server Administration :: Data Block Corruption

May 12, 2011

1) What is PHYSICAL/LOGICAL Corruption.
2) How it occurs.
3) Will RMAN works on both the types of corruption or only Physical (My senior told it works on both).

View 3 Replies View Related

RAC & Failsafe :: Inserting Large Data Locks The Destination Table In RAC

Oct 18, 2010

Scenario:

Our application is using a two instance, one for the live active data and the other for the reports data. We have a process which moves the data from the live instance to reports instance every night. In a single db environment the process is working without any issues. However when we move to the RAC environment the reports db's (insert) in large table get locked and we are unable to insert data to the reports db.

What we are performing is:

Insert into my_table_rpt select * from may_table_live@db_link_to_livedb;

Issues:

my_table_rpt get locked

We have found the workaround by disable locking in destination and subsequent to the insert enable locking

ALTER TABLE my_table_rpt DISABLE TABLE LOCK;

Insert the data to the reports database table

Then

ALTER TABLE my_table_rpt ENABLE TABLE LOCK

Question:

Why does the large destination table (my_table_rpt) get locked in the RAC environment?

View 2 Replies View Related

Backup & Recovery :: Ora-01578 - Oracle Data Block Corruption

Jun 24, 2013

We are facing block corruption error and it's refer to system datafile (SYSTEM01.DBF).Below is the script through it, we can come to know about the extent.

select segment_name, segment_type from dba_extents
where file_id=1 and 134144 between block_id and block_id+blocks-1;

select owner,index_name,index_type, table_name from dba_indexes
where index_name='I_CDEF3';

How to resolve the problem as it is related to system datafile? We tried to drop the index but system is not allowing to do.

ORA-00701 - object necessary for warmstarting database cannot be altered.

View 7 Replies View Related

Locks On Partition Table?

May 13, 2011

I have the below questions on the locking mechanism in a partition table. Example I execute the below query,

delete from table_name subpartition (subpartition_name);

In that case,

1. If we perform above query, then which level of lock is happened to the table/partition & sub-partitions?

2. If we perform above query on same sub-partition of the same table with different where clause, what will happen?

3. If we perform above query for same table but different sub partition at same time, what will happen?

View 2 Replies View Related

How To Check Locks In Database

Apr 13, 2013

oracleDatabase version:11gR2

Os:solaris

how to check locks on database objects .it might be any lock? any select sql query to check database object levlel locks?what basis locks will occur?

View 3 Replies View Related

Dead Locks - Not Releasing?

Oct 8, 2012

We have encountered dead locks in EBS R12 Database 10.2.0.3. Even after bouncing the appstier and dbtier completely does not release the locks. How to clear the locks?

View 4 Replies View Related

Index Corruption In Database?

Nov 8, 2012

I am getting the below error when I run utlrp in my database.

ERROR at line 1:
ORA-08102: index key not found, obj# 423571, file 6, block 113416 (2)
ORA-06512: at "SYS.UTL_RECOMP", line 760
ORA-06512: at line 4

I have run dbverify on File_id=6 but it did not return any corrupt pages or blocks.

View 7 Replies View Related

Table Locks In Exclusive Mode

Jan 6, 2011

Under what scenarios do we lock a table in the exclusive mode.

View 1 Replies View Related

Unexplainable Exclusive Transaction Locks?

Aug 23, 2010

I have a job which runs the procedure below concurrently in 4 different sessions; It updates two tables.

PROCEDURE UPDATE_TAB_1_AND_TAB_2( traceIdTab IN pl_sql table,
individualIdTab IN pl_sql table,
fileSeqIdTab IN pl_sql table,

[Code].....

Each session is passed a sequence generated file ID; so the records processed in each session are completely different.

However, everytime this job runs, without failure, ONLY TWO sessions process concurrently, while the other two sessions are blocked. Once the first two have finished, then the remaining two sessions start work.

I have being monitoring the sessions on each run daily, and realized that the first two sessions processing are each holding a transaction exclusive lock on objects; hence blocking the other two sessions.

When I tried to find the objects that the two running sessions have a lock on, I cant seem to find anything by searching DBA_OBJECTS.object_id. However, when I look for the objects that are being waited on, its either a partitioned table or a partitioned index on either table_1 or table_2.

At first, I thought it was an ITL lock and after much analysis, I did not find anything which definitely pointed to an ITL lock. However, just to make sure, I increased the INITTRANS value from 2 to 5, but the problem did not go away.

At this point, I have applied all I know to understand the origin of the TX lock and how I can work around this situation, and decided to ask higher powers in Oracle for advise.

View 4 Replies View Related

How To Analyze Locks And Performance Of Database

Sep 6, 2010

I used v$locked_object and v$lock query to get the output.. But still I'm an one year exp in ORACLE. How to analyze the output of lock queries. what are the parameters to be analyzed on AWR report.

How to do proper performance checkup in ORACLE database as well analyze it.

View 4 Replies View Related

Library Cache Locks By Dynamic DDL

Jun 30, 2011

At a customers site I see lot of long lasting library cache locks during a complex ETL run. Several Sessions run in parallel and create Database tables with dynamic sql ( CREATE TABLE AS SELECT ....) .

Sometimes these procedures wait for each other with wait event 'Library Cache' . I presume that this is a side effect of the dynamic DDL in the stored procedures. Is that possible even when the Procs create different Tables but reference the same tables in their SELECT clauses ?

I presume that this is plausible but I need some arguments to convince my colleagues.

View 1 Replies View Related

SQL & PL/SQL :: Session Is Killed But Locks Not Released

Jun 8, 2011

On DB123, I have 2 KILLED sessions which are not disappearing and are holding locks on some tables in ONE schema.

The sessions belonged to ETL process and I have killed them last Friday after they have been hanging for 3 days. Unfortunately, I've also killed the corresponding Unix session - and now I've read on the Internet that in those cases the KILLED sessions might be hanging forever (=until the DB is shutdown).

how to remove these locks.

View 5 Replies View Related

Performance Tuning :: How To Reduce Dml Locks

May 24, 2012

After ran db health check, my database report gives the following details

dml_locks OK. dml_locks = 3396, transactions = 849.

View 6 Replies View Related

RMAN Active Clone And Corruption

Feb 11, 2011

I am cloning my prod db to test with the rman active clone command. I can successfully clone my DB, but after a few hours or so I see messages in the alert log that I have corrupted blocks in several datafiles. Note, i dont see these messages in my PROD DB therefore I think that DB has no corruption. I have few questions:

1) I was reading tha having tables or indexes set with the NOLOGGING option can cause objects to be unrecoverable. Would this affect my active clone?

2) I know you can either change a DB or tablespace to force logging. Is there a query I can use to determine if the DB is in force logging mode.

ALTER TABLESPACE tablespace_name FORCE LOGGING;
ALTER DATABASE FORCE LOGGING;

3) Lastly how to check as why my clone DB would have corrupted blocks.

Here is the clone command I am using.

rman catalog=rman/rman@proddb target=sys/sys@proddb << EOT
connect auxiliary sys/sys@clonedb
duplicate target database to clonedb from active database nofilenamecheck
pfile=/u01/app/oracle/product/11g/dbs/initclonedb.ora ;
exit
EOT

View 7 Replies View Related

Possible To Recover BLOCK Corruption With Exp Backups

Jul 9, 2013

While taking a full export i came to know there was a block corruption in SYSAUX tablespace. I dont have any COLD/HOTBACKUPS/RMAN BACKUPS. As i have only the Exp backups and the database is in archive log mode. Whether is it possible to recover the BLOCK CORRUPTION with exp backups.

View 12 Replies View Related

How To Check For Datafile Corruption Or Error

Oct 12, 2010

How can i check for datafile corruption or a datafile error,its some tool like Linux (fsck) command in Oracle that can halp to me to chack a datafile?.

Is any way to isolate the disk region that is corrupted?

If a disk error exist an an ASM disk group how can i isolate the error from that disk group? It's an alternative different to the VALIDATE DATAFILE command in RMAN?I'm using Oracle 11GR2 on a Linux Box.

View 1 Replies View Related

Server Administration :: How To Avoid Blocking Locks

Nov 8, 2011

how to avoid blocking locks.

View 27 Replies View Related

Script To Find Blocking Locks More Than 30 Minutes

Jun 29, 2012

writing a script where there is a blocking lock for more than 30 minutes ?

View 2 Replies View Related

PL/SQL :: How To View The Number Of Locks Defined For A Database

Aug 21, 2012

How to view the number of locks defined for a database? I am using Oracle 10g.

How to calculate the number of locks for a given sql select statement?

say for example,

select a.eno,b.ename,c.salary,d.dob,e.address
from a,b,c,d,e
where...
...
..

Assume all of them are row level locking.

I want to calculate the number of locks . How many locks will be held while running the above query?

View 16 Replies View Related

Backup & Recovery :: Block Corruption In Expdp?

Feb 15, 2011

I am facing issue of block corruption in my exp backup which I am taking through expdp command. (Refer Attachment of Screen shot of error)

I want to know few things about the block corruption.

1. Why the block corruption occur.

2. How can I resolve this.

3. Can I rosolve this by deleting the same record on which this error is coming. if yes then how can I track that row in a table.

I already tried for DB verify utility. It shows the below result.

===================================================================
C:Documents and SettingsAdministrator>dbv file='E:ORADATAAFCCV1MONETA01.DBF' blocksize=8192
DBVERIFY: Release 11.1.0.6.0 - Production on Wed Feb 16 10:13:11 2011
Copyright (c) 1982, 2007, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = E:ORADATAAFCCV1MONETA01.DBF
DBV-00600: Fatal Error - [28] [27070] [0] [0]
C:Documents and SettingsAdministrator>
================================================================

View 12 Replies View Related

Backup & Recovery :: Restore Terminated Due To Log Corruption

Jul 18, 2012

I am trying user managed recovery using archived and redo log files. I had restored old cold backup, then copy latest control file, redo log files, and archived log files at actual locations. Now I tried to recover database using following steps.

Quote:
connect sys as sysdba
startup mount;
recover database;
auto

After applied some archived files, one of the archived file corrupted and recovery cancel due to error, then i shut down the database. Now i want to copy corrupted archived file from backup and run the recovery further. so my question that should i have to run recovery from the beginning or can i run the recover from the last status of the database.

View 3 Replies View Related

Backup & Recovery :: How To Find Corruption Block

Jun 9, 2012

how to find the corruption block and judge whether it is physical corruption or logical corruption?

View 3 Replies View Related

Replication :: Block Corruption While Accessing Some Table

Mar 16, 2011

The database is running in archivelog mode and we have a standby with Maximum performance.There is no RMAN backup..We have noticed there is block corruption while accessing some tables.Now i would like to know are the corrupted blocks also replicated to the physical standby? Is there a way to recover the data from these corrupted blocks without shutting down the database ?

View 1 Replies View Related

Networking And Gateways :: Select For Update - How To Kill Old Locks

Aug 3, 2011

My code executes a Select For Update before updating a table. In some cases the network is disconnected and it causes the lock to hang. Then, I must kill the session in order to realese this lock.

I want to do it automatically. I would like to create a job that kills session that has a lock due to Select For Update that is not alive.

View 18 Replies View Related

Forms :: Alert Windows Appear To Be Hidden And Application Locks Up

May 6, 2011

I have a forms 11g application that of course has several message and alert windows that pop up when various errors or messages need to be displayed. This all works great on the developer machine, but when I try and run the form on another machine using any browser the message and alert windows appear to be hidden and the application locks up with the rolling bar across the bottom. I would guess it is waiting for a response, but I cannot get the window to appear.

Following is an example of an alert to be displayed:
declare
vAlertButton number;
begin
vAlertButton := show_alert('NO_EMAIL');
end;

View 1 Replies View Related

Database Keeps Running Even After Deletion / Corruption Of Control Files

Jan 21, 2011

I was carrying out an experiment in order to crash the database and recover it.

The database was running, I moved the control file to another location and to my surprise the database was still running. I tried issuing checkpoint and transaction but it didn't affect database operations. I tried doing log switch. It completed successfully. According to my understanding and Oracle Certification books database was supposed to crash. But it didn't.

I tried this not only on RHEL, Windows XP but also in Solaris 5.10. The database version is Oracle 10.2.0.4 Standard Edition.

View 1 Replies View Related

Backup & Recovery :: Block Corruption In System Datafile?

Dec 25, 2011

I have noticed a block corruption issue in my RAC database. Its an index , how to recover a corrupted index in system datafile.

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents WHERE file_id = 5
3 and 147551 between block_id AND block_id + blocks - 1;
TABLESPACE_NAME SEGMENT_TYPE OWNER

[code]....

We are using OCFS2 and O/S is RHEL 4.7.

View 3 Replies View Related

Server Administration :: Block Corruption - Segment Not Found

May 16, 2012

I have a database where view v$database_block_corruption shows a corrupted block

SQL> select * from v$database_block_corruption;

FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
4 756652 1 5.5157E+12 CORRUPT

I am not able to get the relevant segment from the above information

SQL> select segment_name, segment_type, owner
2 from dba_extents
3 where file_id = 4
4 and 756652 between block_id
5 and block_id + blocks -1;

no rows selected

DBVERIFY Summary
DBVERIFY - Verification complete
Total Pages Examined : 3932160
Total Pages Processed (Data) : 3119107
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 755048

[code]....

I have uploaded the complete logfile.

Below is a part of logfile

DBVERIFY - Verification starting : FILE = /prd/dvp/ora/oradata/LHF/disk06/gds_t01_01.dbf
Block Checking: DBA = 21728172, Block Type = KTB-managed data block
**** kdxcoavs = -84 < 0, avail = 3129
---- end index block validation
Page 756652 failed with check code 6401
##not here that 756652 is the same block# mentioned in v$database_block_corruption

here i tried finding the OBJECT ID as below

SELECT dbms_utility.data_block_address_block(21728172) "BLOCK", dbms_utility.data_block_address_file(21728172) "FILE" FROM dual;
BLOCK FILE
---------- ----------
756652 5

Now for the same BLOCK it is giving different File, again the segment_name or segment_id could not be found from the above information.

View 4 Replies View Related

Logical Corruption In Oracle Database - Invalid RowID Error

Jul 30, 2012

I observed logical corruption in one of the database, while running select on some tables observed ORA-01410: invalid ROWID error. These errors or errors related to this logical corruption are not reported in alert log file.

Since our database is in NOARCHIVELOG mode and regular backups are not happening through RMAN (weekly cold mount point backup to tape),
i was not able to use RMAN to investigate block corruption.

So used DBVERIFY on all datafiles of database to check the consistency of datafiles, and found DBV gives error for one of the datafile - 'Completely zero block found during dbv:' As i mentioned earlier, we are not taking regular backups using RMAN and database is in NOARCHIVE mode.

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved