Recovering Oracle Block corruption

Occurence of Problem:
While trying to login to our application running on Oracle 10g R2 (10.2.0.4) we received the following error:

ORA-01115: IO error reading block from file 1 (block # 94697)
ORA-01110: data file 1: '(...)\SYSTEM01.DBF'
ORA-27091: unable to queue I/O
ORA-27070: async read/write failed
OSD-04006: ReadFile() failure, unable to read from file
O/S-Error: (OS 23) Data error (cyclic redundancy check).

Investigation:

• Oracle's Metalink pointed out that the problem was a block corruption on the disk.


• It is a 500GB Seagate SATA disk. The Event Viewer of Windows Server 2008 also gave the 'disk' block corrupt error.


• A DBVERIFY was done on the corrupted datafile and it returned an O/S Error 0RA-01115.


• A complete Check Disk was run on the disk and the corrupted block were fixed.


• DBVERIFY now gave the following error for the datafile
  

  
  Corrupt block relative dba: 0x004171e8 (file 1, block 94696)
  Fractured block found during buffer read
  Data in bad block:
  type: 6 format: 2 rdba: 0x004171e8
  last change scn: 0x0000.01c61503 seq: 0x1 flg: 0x06
  spare1: 0x0 spare2: 0x0 spare3: 0x0
  consistency value in tail: 0x00000000
  check value in block header: 0xa06b
  computed block checksum: 0xdfa7
  Reread of rdba: 0x004171e8 (file 1, block 94696) found same corrupted data
  

  
  Same error also appears in the alert.log file


• Attempting to login into the application gave a block corruption error in Oracle in the datafile.
  

  
  ORA-01578: ORACLE data block corrupted (file # 1, block # 94696)
  ORA-01110: data file 1: '(...)\SYSTEM01.DBF'
  

  
  Do note that it is the previous block ID from last error message


• Alert.log contained the following information
  

  
  Corrupt Block Found
  TSN = 0, TSNAME = SYSTEM
  RFN = 1, BLK = 94696, RDBA = 4289000
  OBJN = 331, OBJD = 331, OBJECT = I_PROCEDUREPLSQL$, SUBOBJECT =
  SEGMENT OWNER = SYS, SEGMENT TYPE = Index Segment
  

  
  

Resolution:
Refer to Oracle's documentation on Detecting and Repairing Data Block Corruption.

1. Logon to SYS user and create a REPAIR Table:
   

   
   BEGIN
   DBMS_REPAIR.ADMIN_TABLES (
   TABLE_NAME => 'REPAIR_TABLE',
   TABLE_TYPE => dbms_repair.repair_table,
   ACTION => dbms_repair.create_action,
   TABLESPACE => 'USERS');
   END;
   



2. Check the Object for corruption
   

   
   DECLARE num_corrupt INT;
   BEGIN
   num_corrupt := 0;
   DBMS_REPAIR.CHECK_OBJECT (
   SCHEMA_NAME => 'SYS',
   OBJECT_NAME => 'I_PROCEDUREPLSQL$',
   OBJECT_TYPE => dbms_repair.index_object,
   REPAIR_TABLE_NAME => 'REPAIR_TABLE',
   CORRUPT_COUNT => num_corrupt);
   DBMS_OUTPUT.PUT_LINE('number corrupt: ' || TO_CHAR (num_corrupt));
   END;
   

   
   Note: If parameter Object Type is ignored, it takes the default as TABLE_OBJECT and the query will return a table or view not found error. The object turned out to be an INDEX OBJECT.


3. Command - "select * from repair_object" will show the corrupt blocks and associated information


4. Fix the errors using the following code:
   

   
   DECLARE num_fix INT;
   BEGIN
   num_fix := 0;
   DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
   SCHEMA_NAME => 'SYS',
   OBJECT_NAME=> 'I_PROCEDUREPLSQL$',
   OBJECT_TYPE => dbms_repair.INDEX_OBJECT,
   REPAIR_TABLE_NAME => 'REPAIR_TABLE',
   FIX_COUNT=> num_fix);
   DBMS_OUTPUT.PUT_LINE('num fix: ' || TO_CHAR(num_fix));
   END;
   

   
   num fix: 0 was returned.
   Corrupt block could not be fixed


5. It was a UNIQUE index on procedures. The index was dropped!


6. Trying to recreate the index failed due to duplicate records


7. From the object ID, the problematic procedure (from our application) was located and dropped.


8. Index creation was successful.


9. Application procedure which was dropped was re-created.


10. Login to the application was successful.


11. DB Verify still gives the same error informing that a block has been marked as corrupt and is not usable.

Using AWE to improve Oracle performance

Problem:
Hardware:

1. 4 x Dual core Intel Xeon processors


2. 12 GB of physical RAM


3. 2 x 74 GB SATA Disks configured as RAID-0 (RAID-1 removed to increase performance. Data security done using archiving and automated backups).

Despite having a high performance configuration, Oracle Database 10g R2 (Version 10.2.0.4) was reporting problems with speed. Typical user complaints were as follows

• Startup time for login screen is high.


• Transition between screens is slow.


• Execution of programs within a screen is fast.


• Time lag (Freeze) experienced (anything between 0.5 second to 5 seconds) while traversing between multiple records in one screen.

Analysis:
The Oracle Database was tuned manually. All the values for Pool size and SGA, PGA targets were provided manually in the SPFILE during installation.
Some of them were as follows:

• _db_cache_size=989855744


• _large_pool_size=25165824


• java_pool_size=512165824


• shared_pool_size=233554432


• sga_max_size=2500016000


• sga_target=2500016000


• pga_aggregate_target=262144000

The /3gb switch was turned on for the Operating system. oracle.exe process was able to acquire upto 2.5 GB physical memory. But the performance problems continued. The complete 12GB of physical RAM was also unutilised most of the time.

Solution:
Windows Address Windowing Extension (AWE) was used to increase the Oracle Data Buffer size. The Windows AWE is actually a set of extensions that allows applications like Oracle Database to manipulate physical memory greater than 4GB on 32-bit Operating systems. AWE allows fast memory management.

How To:
To use the Windows AWE, the following was done.

1. Turn on /PAE (Physical Address Extension) and /3gb switch on the Operating System so that more than 2GB of addressable space is available to applications.


2. Set the parameter AWE_WINDOW_MEMORY to a suitable value in the registry.
   Parameter is set in HKEY_LOCAL_MACHINE\Software\Oracle\(Oracle_Home)
   Value used: 536870912


3. In the init.ora set USE_INDIRECT_DATA_BUFFERS=TRUE


4. In the init.ora set DB_BLOCK_BUFFERS to a suitable value.
   Value used: 850000


5. The above should be sufficient for most databases. But we use poi Java utilities to import/export data to excel that requires a minimum of 500 MB Java Pool size.
   Hence, in init.ora JAVA_POOL_SIZE=512165824


6. An error was encountered on starting up the database that said that there was insufficient Streams Pool size. For which in init.ora, we set
   STREAMS_POOL_SIZE=100M

Note: Oracle's Metalink note - 225349.1 has a detailed description on using AWE and the calculations needed to arrive at appropriate values for AWE_WINDOW_MEMORY and DB_BLOCK_BUFFERS.

On starting up the database, we can see that Oracle acquired upto 9GB of physical memory and all the performance related problems from users were addressed.

Problems encountered:

• If the DB_BLOCK_BUFFERS or AWE_WINDOW_MEMORY value is incorrect, we get an
  ORA-27102: out of memory Error followed by
  O/S-Error: (OS 8) Not enough storage is available to process this command

The init.ora used is attached herewith:

_db_block_lru_latches=32
audit_file_dest='C:\admin\(db_name)\adump'
background_dump_dest='C:\admin\(db_name)\bdump'
compatible='10.2.0.3.0'
control_files='D:\(db_name)\CONTROLFILE\(controlfilename).CTL'
core_dump_dest='C:\admin\(db_name)\cdump'
db_block_buffers=850000
db_block_size=8192
db_create_file_dest='D:\(db_name)'
db_domain=''
db_file_multiblock_read_count=16
db_name=(db_name)
dispatchers='(protocol=TCP)'
java_pool_size=512165824
job_queue_processes=10
log_buffer=8388608
open_cursors=3000
processes=150
remote_login_passwordfile='EXCLUSIVE'
shared_pool_size=1468006400
shared_servers=5
streams_pool_size=100M
undo_management='AUTO'
undo_tablespace='UNDOTBS1'
use_indirect_data_buffers=TRUE
user_dump_dest='C:\admin\(db_name)\udump'
log_archive_format=Log_%s_%t_%r.arc
log_archive_dest='D:\(db_name)\ARCHIVE'

Using the /3gb switch

All of our CES Installations are done on Windows servers (32-bit). Windows Servers, by default do not allow any process to utilise more than 2GB of memory in the RAM. With the maximum addressable space being 4GB for 32-bit OS, 2 GB is utilised by the Windows Kernel processes. Extended memory of upto 16GB can be used on the server and the extended memory is detected only by the Enterprise edition of Windows Server.
Even though, upto 10GB of RAM is available to be utilised, default settings on windows do not allow the Oracle Database to acquire more than 2GB (irrespective of the Physical Address space defined). To allow the Oracle Database instance to acquire more than 3GB, we have to turn on the /3gb switch on the Operating System.

In Windows 2003 Server
In Windows 2003 Server (Enterprise Edition), the 3GB switch can be turned ON using the boot.ini file
Note: The boot.ini file is available in the default drive (C:). You might have to show hidden and system files to view and edit the file.
Open the boot.ini file and locate Operating System parameters.

1. At the end of the parameter list, Add /3gb
   

   
   multi(0)disk(0)rdisk(0)partition(1)\WINDOWS="Microsoft Windows XP Professional" /fastdetect /usepmtimer /NoExecute=OptOut /3gb
   

   
   


2. Save the boot.ini file


3. Restart the Server for the changes to take effect

For more about the /3gb switch on Windows 2003 Server, read this

On Windows 2008 Server
On the Windows 2008 Server (or Vista OS), the 3gb switch can be turned ON/OFF using the Boot Configuration Data Editor (BCDEdit). [Read more about BCD]
To turn ON the 3GB Switch,

1. Run the Command Prompt (cmd) as Administrator.


2. Type
   

   
   bcdedit /set IncreaseUserVA 3072
   

   
   and press Enter

3072 = 3 * 1024 MB = 3GB

3. Restart the Server


4. Type bcdedit on command prompt to check the current value of IncreaseUserVA

To Turn OFF the 3GB switch, type

bcdedit /deletevalue IncreaseUserVA

Archive Log on Oracle 10g R2

What is to be done?

1. Set an existing Database in Oracle 10g R2 into Archive mode.
2. Try to recover a database from a cold backup and subsequent Archives.

Pre-requisites:

- A running Oracle Database (version used 10g R2 10.2.0.4) which is not in Archive mode.
- Stand-by database
- Sufficient disk space to keep multiple versions of Cold Backup.
- OS: Windows 2003 Server SP2 or Windows 2008 Server

Part I: Putting a database into archive mode.

Step 1: Take a cold backup of the database.
Step 2: Generate a PFILE from existing SPFILE and incorporate Archive parameters in PFILE
Step 3: Shut the database down.
Step 4: After securing a copy of the existing SPFILE overwrite it with the altered PFILE
Step 5: Mount the database but do not open it for users to log on.
Step 6: Alter the Database to start archiving.
Step 7: Open the Database.
 

Steps explained:

1. Take a cold backup of the database:

A cold backup can be taken using the RMAN Utility. But for this test, a very simple backup was taken. Database was turned off and all the control files, data files and Log files were copied to a different location.
To check if the database is currently archive mode or not use the following command from SYS User:
SQL> select log_mode from gv$database;
LOG_MODE
-------------------
NOARCHIVELOG

2. Generate a PFILE from existing SPFILE and incorporate Archive parameters in PFILE

From SYS User generate a PFILE (Oracle initialization Parameters file) using the command.
SQL> create pfile from spfile;
File created
The new PFILE will be created in the default location /database. To create it in some other location use create pfile=’’ from spfile;
In the PFILE created add the following two parameters for the Archive Logs.
log_archive_format='LOG_%S_%R_%T.ARC'
log_archive_dest=
Other options available for format are:
%s - log sequence number
%S - log sequence number, zero filled
%t - thread number
%T - thread number, zero filled
%a - activation ID
%d - database ID
%r - resetlogs ID that ensures unique names are constructed for the archived log files across multiple incarnations of the database.

3. Shut the database down

Login to the database through Command Prompt with the SYS user. If you have multiple databases on the same machine, the Windows global variable ORACLE_SID needs to set. It can be done by the command:
C:> set ORACLE_SID=
Connect to an idle instance of SQL Plus using the command.
C:> sqlplus /nolog
SQL> connect SYS as sysdba
Enter password:
Connected.
Shut down the database. Use immediate or abort if users are logged on.
SQL> shutdown immediate

4. After securing a copy of the existing SPFILE overwrite it with the altered PFILE

Make a copy of the SPFILE in the location, /database and create the new SPFILE from the PFILE with the command.
SQL> create spfile from pfile;
File created.
If pfile is in a different location from /database provide the full location as pfile=’’;

5. Mount the database but do not open it for users to log on.

Mount the database with the command.
SQL> startup mount
ORACLE instance started
Total system global area …
….
Database mounted.

6. Alter the Database to start archiving.

Alter the database to start the archiving process with the command,
SQL> alter database archivelog;
Database altered.

7. Open the Database.

Now the database can be opened for use.
SQL> alter database open;
Database altered.
To test if archiving is turned on run the same SQL statement as before.
SQL> select log_mode from gv$database;
LOG_MODE
---------------
ARCHIVELOG
The archiving on the database has successfully been activated.