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 RepliesI 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>
================================================================
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).
how to find the corruption block and judge whether it is physical corruption or logical corruption?
View 3 Replies View RelatedThe 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 RelatedI 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.
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.
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.
I am facing problem of logical block curruption.
SQL> select * from v$database_block_corruption;
FILE# BLOCK# BLOCKS CORRUPTION_CHANGE# CORRUPTIO
---------- ---------- ---------- ------------------ ---------
3 61454 1 1417157166 LOGICAL
3 31256 1 1417011117 LOGICAL
OWNER CONSTRAINT_NAME C TABLE_NAME
------------------------------ ------------------------------ - ------------------------------
SYSMAN MGMT_METRICS_RAW_PK P MGMT_METRICS_RAW
Can i recreate the SYSMAN schema index by dropping this one?
Oracle database 11.2
What is the difference between
recover database until cancel using backup controlfile; and alter database recover automatic until cancel;
1. in user managed and server managed backups. What would be the location of the controlfile which is used in (recover database until cancel using backup controlfile;) .
2. why user first option and why use second option.
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.
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.
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
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.
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.
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.
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.
I'm using Oracle8i, with VB6 as front end When i try to connect Oracle using RDO in VB i get an error message from Oracle It is S1000:Oracle ODBC.ora Ora:1043 User Side Memory Corruption.
View 11 Replies View Relatedi have multi data block filed. and checkbox field which based on control block...My task is when i check checkbox only one field should enabled and my mouse goes to that field
e.g
item11 item21 item31 chkbox1
item12 item22 item32 chkbox2
Scenario like this :
My item field based on data block and checkbox based on control block,while i checked chkbox1 , only item31 on that current record should be enabled and i changed value only on that field
when i checked chkbox1 , my cursor goes to item31...not item32
i have a master detail form, In Master block we have one field cheque amount and in Detail block we have field receiveable amount invoice wise. if company paid us a cheque amount we will enter this amount in Master block field Cheque amount and in detail block there will be invoice wise receivable amounts. i want to distribute the cheque amount in detail block invoice wise for example
Cheque amount in master block = 291
Invoice wise receiveable amount is as follows
Invoice No , receivable amount , Received amount
10, 196 , 0
20 , 95 , 0
30 , 54 , 0
Result should be as follows:
Invoice No , receivable amount , Received amount
10, 196, 196
20 , 95 , 95
30 , 54, 0
Received amount field should be distributed according to the receivable amount when recevied amount = cheque amount then remaining will be 0.
i have an error with block corruption
Error: Corrupt block relative dba: 0x01c12a58 (file 7, block 76376)
What all the ,methods I can go for if we are working on a production environment with out any down time.
I can go for dbms repair package and restore and recover
"If no level 0 backup is available, then the behavior depends upon the compatibility mode setting. If compatibility is >=10.0.0, RMAN copies all blocks changed since the file was created, and stores the results as a level 1 backup. In other words, the SCN at the time the incremental backup is taken is the file creation SCN. If compatibility <10.0.0, RMAN generates a level 0 backup of the file contents at the time of the backup, to be consistent with the behavior in previous releases."
So yes, that's it. Even in the Oracle 11g OCP course and exam, the information given is for Oracle 9 (pre 10g). Seems like a major functional regression.
------------------------------------------------------------------------------------------------------------------------------------------------
Original problem.
Am I not understanding something about rman????
Using oracle 10g standard edition and rman.
No existing backups.
When I run repeated level 1 cumulative incrementals, the appear to back up everything (like a level 0 would).
My understanding is that if a level 1 is run w/o an existing level 0 backup, it will generate a level 0 backup.
All subsequent level 1 backups should be level 1's as expected.
If I explicitly generate an level 0, followed by level 1's it all works as expected.
I am determining what got backed up by the size of the resulting save sets.
Do I have to do an explicit level 0 and then explicit level 1's? I thought not.
I would like to know when I should to use the MD_BACKUP and MD_RESTORE commands. Whats the advantage over the RMAN backups?
View 2 Replies View RelatedI am having an issue when trying to run a backup from node 207...Initially When trying to run the backup I got this error.
scrbdefrmr207:orarer 54> brbackup -u / -c force -t online -m all -p initRER_online.sap
BR0051I BRBACKUP 7.00 (40)
BR0055I Start of database backup: begnlswy.anf 2011-08-11 21.29.08
BR0484I BRBACKUP log file: /oracle/RER/sapbackup/begnlswy.anf
BR0477I Oracle pfile /oracle/RER/102_64/dbs/initRER001.ora created from spfile /oracle/RER/102_64/dbs/spfileRER001.ora
BR0280I BRBACKUP time stamp: 2011-08-11 21.29.13
BR1351I Configuration of database services:
RER001 PREF: RER001 AVAIL:
RER002 PREF: RER002 AVAIL:
DVEBMGS50_scrbdefrmr207 PREF: RER001 AVAIL: RER002
DVEBMGS50_scrbdefrmr208 PREF: RER002 AVAIL: RER001
[code]....
I have many other rac servers and sids setup this way and they all work,,I can login to sqlplus and do any changes .. no issues going into sqlplus in node 207 and node 208..I have checked the directory i.e mesg and teh files are there ..
scrbdefrmr207:orarer 56> pwd
/oracle/RER/102_64/sqlplus/mesg
scrbdefrmr207:orarer 57> ls
cpyus.msb cpyus.msg sp1us.msb sp1us.msg sp2us.msb sp2us.msg
I think the message is decieving as it complains about oracle home and sp1<lang>.msb not found ,, when i tis there.I have also checked my env variables .. they all there i.e
scrbdefrmr207:orarer 58> env |grep ORA
dbms_type=ORA
ORACLE_SID=RER001
ORACLE_HOME=/oracle/RER/102_64
ORACLE_BASE=/oracle
scrbdefrmr207:orarer 59> env | grep LD_
LD_LIBRARY_PATH=/usr/sap/RER/SYS/exe/run:/oracle/RER/102_64/lib
I an run archives log backups perfectly.. So its only when its trying to connect into the db.
if I run below rman command lines in 10g and 11g RMAN, it works well, but NOT in RMAN (Release 8.1.7.4.0), so which commandlines I should use to get to the same pursposes in RMAN 8.1.7.4?
run {
ALLOCATE CHANNEL c1 TYPE 'sbt_tape';
ALLOCATE CHANNEL c2 TYPE 'sbt_tape';
ALLOCATE CHANNEL c3 TYPE 'sbt_tape';
[Code]...
I have a multi record control block (basically a text item displaying 6 records) where user enters values and I want to process the values using pre-insert trigger.
I want to read value in each record and then do some tasks using a pre-insert trigger before I commit the values. To navigate between the records I was using first_record, next_record, clear_record built-ins but it gives errors like "40737-illegalrestricted procedure next_record in pre-insert trigger".
I have some client RMAN backups from an AIX machine, a set of .bkp files including the control file, that I have been asked to restore on a Windows 2003 server. My first question is if such a restore is possible?Also, the backups are from Oracle 10.2.0.3, and I need to restore them to 11g, will this cause any complications?
how to carry out such a restore procedure from scratch.
We just purchased NAS (Raid5) unit to manage our data storage. I am planning to create a virtual partition on this nas device and use one partition for oracle data storage and another virtual partition will be used by other data (files and may be sqlserver data files....etc..)
We will have oracle installed on seperate oracle server. Can we use RMAN to manage incremental backups in this environment? May main worry point is that our data storage device will have many different type of datas and will we be able to tell RMAN to make backups only from certain virtual drives?
I have two questions with this script
RUN
{
RECOVER COPY OF DATABASE
WITH TAG 'incr_update';
BACKUP
INCREMENTAL LEVEL 1
FOR RECOVER OF COPY WITH TAG 'incr_update'
DATABASE;
}
taken from here [URL]....
1) Does it backup archivers? If not, how to add them?
2) Can this script be run daily for ever?
We have EM (Cloud 12c) initiated scheduled backup jobs that perform (all below are online backups, obviously archivelog mode)
- level 0 Incremental 3AM on Sunday - with options backup all archive logs, delete input, delete obsolete
- level 1 Incremental (non cumulative) 3AM every other day - same options
Recovery window is set to 3 days, other options below:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 3 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 2;
[code]....
Everything appears to be working normally (archive logs are being backed up and once they are the archive logs themselves are being deleted from the FRA), but I'm having some trouble understanding the behavior of the delete obsolete.
With a recovery window of 3 days, on Monday at 11AM my expectation would be that I would find in backups the following:
- level 0s from day before (Sunday) and the previous Sunday (8 days prior)
- all level 1s from previous Sunday on (so Mon-Sat and this morning)
- archivelog backups from Friday until this morning
What I'm not understanding is that I'm seeing archivelog backups from last Monday, Tuesday, etc. - i would have thought these would have gone obsolete based on my recovery window, yet when I run report obsolete in RMAN nothing is showing up.