The blog has moved
Dear Readers,
my blog has moved to a different server.
Backing up your system configuration
Dear Readers,
my blog has moved to a different server.
http://blog.ronnyegner-consulting.de
When backing up servers with any kind of backup software you will most certainly backup your data.
But how about the system configuration?
When restoring a system from scratch do you know how big the disks were, what partition sizes, what disks used (in case you have more than one disk), if you used an LVM and so on.
Think about it!
In this article i wanted to summarize what system information should be collected to enable a fast and error free restore.
Feel free to add your own comments and suggestions.
Oracle on linux – yes of course – but what linux?
Dear Readers,
my blog has moved to a different server.
http://blog.ronnyegner-consulting.de
There is a discussion from December 2008 what Linux (SLES vs. Red vHat vs. Oracle Enterprise Linux) to use for running oracle on Linux by Yann Neuhaus. You can read his post here.
After nearly one year i wanted to catch up the article and check if the pros and cons are still valid or if there changed anything.
Read more…
ORA-01555 (snapshot too old) error although undo_retention set to a high value
Dear Readers,
my blog has moved to a different server.
http://blog.ronnyegner-consulting.de
The past week i had a argumentation with a colleague of mine on the behavior of the parameter undo_retention in 10g.
He stated in 10g undo_retention determines the lower number of seconds oracle keeps the undo records. If there is enough space available an ORA-01555 should not be raised.
I knew there are cases in which undo_retention is set to a ridiculous high value (for instance a week), the undo tablespace has a lot of free space but an ORA-01555 is raised nonetheless. So we took a look at the documentation:
For Oracle 10g Release 1 the documentation states:
UNDO_RETENTIONspecifies (in seconds) the low threshold value of undo retention. The system retains undo for at least the time specified in this parameter and automatically tunes the undo retention period to satisfy the undo requirements of the queries.
But for Oracle 10g Release 2 the documentation says different:
UNDO_RETENTIONspecifies (in seconds) the low threshold value of undo retention. For AUTOEXTEND undo tablespaces, the system retains undo for at least the time specified in this parameter, and automatically tunes the undo retention period to satisfy the undo requirements of the queries. For fixed- size undo tablespaces, the system automatically tunes for the maximum possible undo retention period, based on undo tablespace size and usage history, and ignoresUNDO_RETENTIONunless retention guarantee is enabled.
In Oracle 11g Release 1 and Oracle 11g Release 2 the behavior is the same as for Oracle 10g Release 2.
11g Release 2 release rumors
Dear Readers,
my blog has moved to a different server.
http://blog.ronnyegner-consulting.de
There are rumors on the release dates of 11.2.0.1.0 for different plattforms. According to my information the release dates are:
- Windows: second quarter 2010
- Linux: already released (32-bit and 64-bit intel)
- HP-UX: 4th quarter 2009
- Solaris: 4th quarter 2009
- AIX 5L based: 4th quarter 2009
I personally expect the release of 11g Release 2 for solaris operating system pretty soon.
Data Pump falsely reports the number of imported rows as 1 (one)
Dear Readers,
my blog has moved to a different server.
http://blog.ronnyegner-consulting.de
Yesterday i did a large import using data pump and the network mode feature. I noticed a table containing a large number of rows were reported by data pump as imported but with only one (1) row.
First i checked my import options, checked for errors in source and target database and even cleaned up everything and restarted the import. Surprise surprise: Same error again.
The environment was:
- Oracle Enterprise Edition 10.2.0.4.0
- Solaris (SPARC) 10
- Data Pump Import with NETWORK_MODE
- excluding STATISTICS, GRANTS and ROLE_GRANTS
Extract from the data pump log file:
Processing object type SCHEMA_EXPORT/CLUSTER/CLUSTER Processing object type SCHEMA_EXPORT/CLUSTER/INDEX Processing object type SCHEMA_EXPORT/TABLE/TABLE . . imported "USER"."TABLE_A" 55082451 rows . . imported "USER"."SOME_LARGE_TABLE" 307126916 rows . . imported "USER"."ANOTHER_TABLE" 176936257 rows . . imported "USER"."JUST_ANOTHER_TABLE" 215682029 rows . . imported "USER"."HUGE_TABLE" 1 rows <==== . . imported "USER"."AND_SO_ON" 133356302 rows
So i started to dig a little bit deeper and noticed the source table has approx 62 million rows with a size of 28 GB. To my surprise the target database also reported the table with 28 gb in DBA_SEGMENTS.
So i did a simple:
SQL> select count(1) from user.huge_table;
COUNT(1) ---------- 62485290
Guess what? The table was imported correctly with all rows while data pump reported only one imported row.
I check metalink for related errors but found nothing.
I can only guess if this is a bug or there is some time limit when querying the rows of the just imported tables.
Update: I did some reseach and created a really large table with 1 billion rows the data pump showed:
. . imported "USER"."TABLE_WITH_1_BILLION_ROWS" -1416726547 rows
There might be some kind of counter overflow…. i will investiage this further.
New public oracle yum server
Dear Readers,
my blog has moved to a different server.
http://blog.ronnyegner-consulting.de
I just found a new public yum server hosted by oracle. This server can be used to install missing packages over the internet. It does not contain any security updates or bug fixes.
The public yum server can be found here.
For larger installation you can run your own yum server.
Configuring a small DNS server for SCAN
Dear Readers,
my blog has moved to a different server.
http://blog.ronnyegner-consulting.de
A few users asked what to do if there is no DNS server available for configuring the SCAN names when installing oracle grid infrastructure.
Most asked if they can use hosts file entries. The short answer is: Nope. The grid infrastructure will install fine but the cluster verification utility will fail.
So my suggestion is as follows: Create your own small DNS server on your rac nodes. This is quite fast and easy and described in this article.
ASM resilvering – or – how to recover your crashed cluster
Dear Readers,
my blog has moved to a different server.
http://blog.ronnyegner-consulting.de
In this and the following posts i will perform some crash and recover scenarios and show how to recover the cluster successfully.
At the moment the following tests are planned and will be published during the next days:
- suddenly turning off the power and restarting the node
- terminating private network connect between the cluster nodes
- recovering an ACFS file system which will not mount automatically
(was not able to reproduce more than once; sorry guys) - overwriting the ASM disk header with the disk group being offline
- corrupting an online and active ASM disk by writing chunks of random data to the disk randomly
- simulating disk errors by removing the device from the operating system
- corrupting the OCR
- corrupting the Voting Disk
The environment used for the posts are explained in detail here.
Useful scripts can be found here.
ASM resilvering – or – how to recover your crashed cluster – Test no 4
Dear Readers,
my blog has moved to a different server.
http://blog.ronnyegner-consulting.de
Test #4: Corrupting the ASM disk with ASM disk group being online and active
After overwriting the ASM disk header while the disk group was offline we will now put some load on the full running cluster and corrupt the asm disk slightly.
Testcase
Put load on the database
Prior starting this test i created a larger table with one column and approx 1.2 GB size and an empty table with the same structure. So for our simple test we will just copy one table into another table. This forces oracle to read the database blocks and do some writes as well.
create table test2_empty as select * from test where rownum<1; insert into test2_empty select * from test;
Corrupting the disk
According to fdisk the asm lun has a size of 21474836480 bytes:
Disk /dev/sde: 21.4 GB, 21474836480 bytes 64 heads, 32 sectors/track, 20480 cylinders Units = cylinders of 2048 * 512 = 1048576 bytes Device Boot Start End Blocks Id System /dev/sde1 1 20480 20971504 83 Linux
And the disk is indeed the mirror partner of the disk we destroyed and re-added in the earlier part.
[root@rac1 ~]# oracleasm querydisk /dev/sde1 Device "/dev/sde1" is marked an ASM disk with the label "DISK003B"
We will corrupt the disk by writing 512 byte chunks of random data from /dev/urandom to 1000 different locations all over the lun. Given the lun size this makes 41943040 blocks of 512 bytes each.
Our command to corrupt the lun will be:
dd if=/dev/random bs=512 count=1 of=/dev/sde1 seek=nnnn
Where “seek=nnn” is a number from 0 to 41943040-1.
Note that this time we manipulate the second mirror disk of disk group DATA2 – DISK003B. In test no 3 we overwrote the asm disk header of DISK003A and re-added the disk to the data group. In this example we manipulate DISK003B. If there were undetected errors rebalancing the disks we will discover it now.
For this we first of all needed a small little random number generator within the limits of 0 to 41943040; here it is:
#!/bin/bash
function random()
{
od -d /dev/urandom | sed -e 's/^[0-9]* //' -e 's/ //g' |\
while read L ; do echo -n $L ; done |\
dd bs=1 count=${1:-10} 2>/dev/null
}
A=$(random 10) #return random number with 10 numbers
B=$(expr $C % 41943040) # keep number within range
echo $B
With this random number generator we created a small shell script for corrupting the asm lun which looks like this:
dd if=/dev/urandom of=/dev/sde1 bs=512 count=1 seek=986024 dd if=/dev/urandom of=/dev/sde1 bs=512 count=1 seek=1594423 dd if=/dev/urandom of=/dev/sde1 bs=512 count=1 seek=2236024 [..]
Expected test results
So what are we expecting?
According to oracle documents (note 416046.1) there are two different scenarios:
- If the primary extent is detected to be corrupt (asm only reads from primary extent unless a read preference set) ASM retries the read. If re-read data is also corrupt ASM fails over to the secondary extent. If data in secondary extent is valid data in primary extent will be overwritten with the good data from the secondary extent.
- If data in secondary extent is corrupt it will remain undetected. If new data is written the new data will be written both to the primary and secondary disk. This will most likely overwrite the corrupted data. But if the primary extent fails oracle will read the secondary extent and will discover the corruption as well - but this time the corruption is not fixable. You will end up with restoring/recovering the corrupt database blocks and probably much more.
In addition to that i expect ASM and/or the database to automatically fix detected corruptions.
Testing
Message no. 1: Database detected and fixed corruption
During our Insert statement running the database alert.log showed:
Fri Oct 02 10:47:21 2009 Hex dump of (file 6, block 156029) in trace file /u01/app/oracle/diag/rdbms /ora11p/ora11p1/trace/ora11p1_ora_30023.trc Corrupt block relative dba: 0x0182617d (file 6, block 156029) Bad header found during multiblock buffer read Data in bad block: type: 180 format: 3 rdba: 0xb19a95b2 last change scn: 0x6f92.6c357e5d seq: 0x1 flg: 0x5a spare1: 0xfd spare2: 0x80 spare3: 0x4f3c consistency value in tail: 0x1591148c check value in block header: 0x2abf block checksum disabled Reading datafile '+DATA2/ora11p/users02.dbf' for corruption at rdba: 0x0182617d (file 6, block 156029) Read datafile mirror 'DISK003B' (file 6, block 156029) found same corrupt data Read datafile mirror 'DISK003A' (file 6, block 156029) found valid data Repaired corruption at (file 6, block 156029)
Message no. 2: Database detected corruption
Corrupt block relative dba: 0x01c12d35 (file 7, block 77109) Bad check value found during validation Data in bad block: type: 2 format: 2 rdba: 0x01c12d35 last change scn: 0x0000.00253bd5 seq: 0x73 flg: 0x04 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0x3bd50273 check value in block header: 0x94cb computed block checksum: 0xf0eb Trying mirror side DISK003B. Reread of blocknum=77109, file=+DATA2/ora11p/datafile/undotbs01_02.dbf. found same corrupt data Reread of blocknum=77109, file=+DATA2/ora11p/datafile/undotbs01_02.dbf. found valid data
Note: This time the database does not say anything about the corruption being fixed.
Message no. 3: ASM detected and fixed corruption
Mon Oct 05 09:32:15 2009 WARNNING: cache read a corrupted block group=DATA2 fn=1 blk=893 from disk 0 NOTE: a corrupted block from group DATA2 was dumped to /u01/app/oracle/diag/asm /+asm/+ASM1/trace/+ASM1_ora_4739.trc WARNNING: cache read(retry) a corrupted block group=DATA2 fn=1 blk=893 from disk 0 NOTE: cache repaired a corrupted block group=DATA2 fn=1 blk=893 from disk 0
Conclusion
First of all: regardless of the caveats described below the insert statement most of the time completed successfully:
SQL> insert into test2 select * from sys.test; 134217728 rows created.
Second: During our tests ASM performed well and stable. We were consistently able to work on the disk groups even if the disk group missed their mirrors.
However i discovered some problems as well:
- ASM has no ability to check data on disk groups completely (i.e. compare data on all mirror partners and check for corruptions); so corruptions on secondary extents will remain undetected until primary extent fails and secondary extent is read
- ASM nor the database does always fix corruptions found in primary extents (see message 2 above… corruption was detected but NOT corrected and remained on disk) as indicated by note 416046
- Checking database with RMAN and “backup validate database” finds and reports errors but does not fix ANY error found. The error message no. 2 originated from a “select * from test….” but rman produces this type of error messages in alert.log as well. From the Metalink document which describes handling of block corruptions in ASM i would have expected ASM or the database to fix these errors automatically.
- There are scenarios where corrupted primary blocks prevent the database from starting at all. During our tests we have seen this two times with different error messages. One sample error message stack is reproduced in error message no. 3 coming from the asm instance and in the following lines below coming from the database instance:
Exception [type: SIGILL, Illegal operand] [ADDR:0x105DC01] [PC:0x105DC01, kkestRCSBase()+221] [flags: 0x0, count: 1] Errors in file /u01/app/oracle/diag/rdbms/ora11p/ora11p1/ trace/ora11p1_ora_5206.trc (incident=48254): ORA-07445: exception encountered: core dump [kkestRCSBase()+221] [SIGILL] [ADDR:0x105DC01] [PC:0x105DC01] [Illegal operand] [] Incident details in: /u01/app/oracle/diag/rdbms/ora11p/ora11p1/ incident/incdir_48254/ora11p1_ora_5206_i48254.trc Mon Oct 05 09:33:47 2009 Trace dumping is performing id=[cdmp_20091005093347] Mon Oct 05 09:33:49 2009 Exception [type: SIGILL, Illegal operand] [ADDR:0x105DC01] [PC:0x105DC01, kkestRCSBase()+221] [flags: 0x0, count: 1] Errors in file /u01/app/oracle/diag/rdbms/ora11p/ora11p1/ trace/ora11p1_ora_5296.trc (incident=48310): ORA-07445: exception encountered: core dump [kkestRCSBase()+221] [SIGILL] [ADDR:0x105DC01] [PC:0x105DC01] [Illegal operand] [] Incident details in: /u01/app/oracle/diag/rdbms/ora11p/ora11p1/ incident/incdir_48310/ora11p1_ora_5296_i48310.trc
Although we met this errors we were able to fix them: In one case dropping the disk containing the corrupted blocks was enough to start the instance again. The second incident reproduced above required the corrupted disk to be dropped, re-added and ASM instance to be restarted before we were able to successfully start the database instance.
- Dropping a corrupted disk and re-adding the disk requires the disk group the disk belonged to be taken offline. Otherwise labeling the former member disk failed with “device or resource busy”. I will investigate this further.
Summarizing my experiences until now i would go with ASM and normal or even high redundancy. This enables far more options for fixing errors without having to rebuild the disk group and restoring everything from tape/disk.
