Oracle Database Block corruption

My Signature Article

Tips:
Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips




Oracle Database Block corruption

“Block corruption is rare but it does happen. As databases get larger and larger – the probability of it happening at some point nears 100%.”--Mr. Tom Kyte Oracle Expert

Block corruption is while the data is being written to the data blocks, if the write to the block fails abruptly, I mean that there is a partial write in the block, may be because of power disruption or I/O problem, leaving no time for header to be updated, or row data to be populated, oracle leaves the block corrupt.In case of block corruption you can normally use the database unless you try to read that particular block, against which it shoots up the block corruption error.Generally block corruption occurs if write fails on the block, when the transaction is being committed


ORA-01578:
ORACLE data block corrupted (file # string, block # string)
Whenever we encounter above error message mean we have BLOCK CORRUPTION.

NOTE: We can find detail information about block corruption in alert.log file

Two types of block corruption can happens

- Physical corruption (media corrupt)
- Logical corruption (soft corrupt)

Physical corruption can be caused by defected memory boards, controllers or broken sectors on a hard disk;

Logical corruption can among other reasons be caused by an attempt to recover through a NOLOGGING action.

Difference between logical and physical corruption

Logical corruption is header - footer - that is one of the checks, yes (it is looking for fractured blocks and when it hits one, it'll re-read it, that is why there is no need for "alter tablespace begin backup" with rman)

Physical corruption is "we cannot read the block from disk, something is physically preventing us from doing so”.

How to detect block corruption?
1. DBVERIFY utility

DBVERIFY is an external command-line utility that performs a physical data structure integrity check. It can be used on offline or online databases, as well on backup files. You use DBVERIFY primarily when you need to ensure that a backup database (or datafile) is valid before it is restored.

http://dbataj.blogspot.com/2007/04/offline-database-verification-utility.html

2. Block checking parameters

There are two initialization parameters for dealing with block corruption:- DB_BOCK_CHECKSUM (calculates a checksum for each block before it is written to disk, every time)causes 1-2% performance overhead- DB_BLOCK_CHECKING (serverprocess checks block for internal consistency after every DML)causes 1-10% performance overhead

Note: In10g db_block_checksum value TYPICAL is implying TRUE and db_block_checking value FULL implying TRUE.

DB_BLOCK_CHECKING Initialization Parameter
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/repair.htm#sthref3176

3. ANALYZE TABLE tablename VALIDATE STRUCTURE CASCADE SQL statement

Validate the structure of an index or index partition, table or table partition, index-organized table, cluster, or object reference (REF).

ANALYZE: Reporting Corruption
http://download.oracle.com/docs/cd/B19306_01/server.102/b14231/repair.htm#sthref3173

4. RMAN BACKUP command with THE VALIDATE option

You can use the VALIDATE option of the BACKUP command to verify that database files exist and are in the correct locations, and have no physical or logical corruptions that would prevent RMAN from creating backups of them. When performing a BACKUP... VALIDATE, RMAN reads the files to be backed up in their entirety, as it would during a real backup. It does not, however, actually produce any backup sets or image copies.

Detection of Logical Block Corruption

Besides testing for media corruption, the database can also test data and index blocks for logical corruption, such as corruption of a row piece or index entry. If RMAN finds logical corruption, then it logs the block in the alert.log. If CHECK LOGICAL was used, the block is also logged in the server session trace file. By default, error checking for logical corruption is disabled.
For BACKUP commands the MAXCORRUPT parameter sets the total number of physical and logical corruptions permitted in a file. If the sum of physical and logical corruptions for a file is less than its MAXCORRUPT setting, the RMAN command completes successfully. If MAXCORRUPT is exceeded, the command terminates and RMAN does not read the rest of the file. V$DATABASE_BLOCK_CORRUPTION is populated with corrupt block ranges if the command succeeds. Otherwise, you must set MAXCORRUPT higher and re-run the backup to find out the corrupt block ranges.
RMAN found any block corruption in database then following Data Dictionary view populated.

V$COPY_CORRUPTION
V$BACKUP_CORRUPTION
V$DATABASE_BLOCK_CORRUPTION

Using RMAN to Validate Database Files
http://download.oracle.com/docs/cd/B19306_01/backup.102/b14192/bkup005.htm#i1006673

5. EXPORT/IMPORT command line utility

Full database EXPORT/IMPORT show=y is another method.

. about to export SCOTT's tables via Conventional Path ...
. . exporting table BONUS
EXP-00056: ORACLE error 1578 encountered
ORA-01578: ORACLE data block corrupted (file # 4, block # 43)
ORA-01110: data file 4: 'C:\ORA10GHOME\ORADATA\ORCL10G\USERS01.DBF'

6. DBMS_REPAIR package

dbms_repair is a utility that can detect and repair block corruption within Oracle. It is provided by Oracle as part of the standard database installation.

http://www.oracleutilities.com/Packages/dbms_repair.html

How to Repair & Fix block corruption?

We can recover everything but we have valid database backup.
Whenever we found block corruption then first need to find out which type of block corruption occurred because block corruption recovery depends on block corruption type.

Like Corrupted block related to TABLE segment, INDEX segment, TABLE
PARTITION segment, INDEX PARTITION segment, ROLLBACK segment, LOB segment.

Through below query we can find out corrupted block type

select segment_type,owner'.'segment_name
from dba_extents
where file_id = [&file_id] and [&block] between block_id and block_id+blocks -1;

Below is example with RMAN BLOCK MEDIA RECOVERY.

SQL> conn scott/tiger
Connected.
SQL> select * from test;
select * from test
*
ERROR at line 1:
ORA-01578: ORACLE data block corrupted (file # 5, block # 11)
ORA-01110: data file 5: 'C:\INDEXDATA01.DBF'

First check which type of block corruption happened through above mentioned query.

RMAN> blockrecover datafile 5 block 11;
Starting recover at 29-APR-08using channel
ORA_DISK_1 starting media recoverymedia recovery complete,
elapsed time: 00:00:00
Finished recover at 29-APR-08

If you are not using rman then applying below procedure
- if it is index then drop and recreate index
- if it is table and you have backup of that table then restore backup on another database and exp/imp the table.

Same Reference:
http://sysdba.wordpress.com/2006/04/05/how-to-check-for-and-repair-block-corruption-with-rman-in-oracle-9i-and-oracle-10g/

NOTE: Find more information “Handling Oracle Block Corruptions in Oracle7/8/8i/9i/10g” Metalink Note: 28814.1

How to corrupt database block for practice purpose?
On Unix:
Use dd command
$man dd

On Windows:
Use Editor and open datafile write some junk character at middle of file and save it.

Preparation for DBA Interview

Nobody can give us complete or most of questions about interview because it is like our school, college exam which we don't know what exact questions comes in.

For school, college exam we need to go through every books which related to exams topic same like any Interview not for DBA only we need to prepare our self and go through each and every topic related to Oracle Database.

So now question is change from Oracle DBA interview question TO How to prepare myself for DBA interview?

And now I explained how we can prepare our self for DBA interview.

1. First understand we are going for DBA interview means Database Administrator.

“A person who’s responsible to handle and maintain database called DBA”
Means DBA position is very Hard, though, sensitive and confidential.

We can divide Oracle DBA interview in Four Major part.
1. Oracle Database Administration
2. Oracle Database Backup & Recovery Administration
3. Oracle Database Performance Tuning
4. Oracle Database Advanced administration

1. Oracle Database Administration
1. How to install Oracle Database Software on Different platforms?
Above point include Oracle Database Server or Client installation on different flavor like LINUX/SORALIS/UNIX/WINDOWS etc. so whenever you are going for interview must go through each installation guide.

2. How to Create Database?
There is two option for create DATABASE.
1. DBCA or 2. MANUALLY

3. How to create tablespace?
In above point we can include TABLESPACE type, DATAFILE size or AUTOEXTEND option.

4. How to configure Oracle Client to connect to Oracle Server?
In Above point we need to create TNS_ENTRY for database client through NETCA tools.

5. Database User Management
In above point we need to do User Creation, Specific permission on database according user needs. User profile creation.

6. Database Security
In above point we need to do Database Auditing, User Logon, Logoff information.

2. Oracle Database Backup & Recovery Administration
1. What is your backup strategy for database backup?
It is depends on many factor.
1. Database size It is in MB, GB or TB
2. Database type It is OLTP or DSS
3. Database availability It is 24x7
So whenever we design backup strategy for database keeps in mind above points. Because Oracle Expert Mr. Tom kyte said “We can FIX everything except BAD DATABASE BACKUP”.

2. What is your backup method for database backup?
It is also depend on condition.
1. Backup Type COLD backup or HOT backup
2. Backup Method RMAN or USER MANAGED
3. Backup Interval Every Day, Every Week, Every Month.

Oracle Corp. recommended to use RMAN (server managed) backup method for database backup but we can also use USER MANAGED backup if applicable.
NOTE: Always RUN production database in ARCHIVELOG mode.

After set proper backup strategy now practice on database recovery because interviewer must ask question about recovery side.

“Database Recovery is depend on Database backup”
There are so many or different types of recovery so we can divide recovery area in following.

How many types of recovery we can perform?
1. When datafile is lost
2. when controlfile is lost
3. when online redolog file is lost

Above three files is most important file for database if we lost them then we needed to perform recovery.
1. We can perform recovery at DATABASE level when complete database is lost.
2. We can perform recovery at TABLESPACE level when tablespace is lost.
3. We can perform recovery at DATAFILE level when datafile is lost.
4. We can perform recovery at DATA BLOCK level when any block is corrupted.
5. We can Multiplex CONTROLFILE or REDOLOG file for protect against LOST.

And suppose still we lost all controlfile or redolog files then recovery depends on many conditions.

NOTE: Before going for interview must go through backup and recovery user guide.

3. Oracle Database Performance tuning administration
This is third big area where most of DBA questions come.

For database tuning we need years of experience. But we can start with bottom.
1. What is database background process like DBRW, LGWR, CKPT SMON, PMON etc? 2. What is database memory area means SGA or PGA size?
3. What are database activities?

For above question oracle providing same diagnostic tools
1. STATPACK or AWR report
2. Tkprof
3. SQL trace
4. AUTO TRACE

Above are same tool to use find out bottleneck of slow database performance so we must know below things about this tools
1. How to use?
2. How to understand?
3. How to use these tools to solve database performance issue.

Above question is simple but answer is difficult because answer want practical or years of experience in database.
Note: Search on Google or visit OTN site for starting.

4. Oracle Database advanced administration
Oracle Advanced Features
1. Data Guard
2. RAC system
3. Stream
4. Partitioning
5. ASM
6. Others

DBA interviewer should ask question above points but it is also depend on DBA position because above points will include in senior level position.

End of the story…
For Interview we need good knowledge about Oracle DB and Experience because if we read thousands of interview related questions without good knowledge or experience we can clear interview but can’t be become a good DBA.

Migrating Databases From NON-ASM to ASM

1. First need to set below parameter for controlfile,datafile or FRA.

NOTE: I have two disk group here i am using "DGRP2" disk group.

SQL> alter system set control_files='+DGRP2' scope=spfile;
System altered.
SQL> alter system set db_create_file_dest='+DGRP2' scope=spfile;
System altered.
SQL> alter system set db_recovery_file_dest='+DGRP2' scope=spfile;
System altered.

2. Create New controlfile , datafile or spfile in ASM diskgroup.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup nomount

ORACLE instance started.
Total System Global Area 263639040 bytes

Fixed Size 1332552 bytes

Variable Size 218106552 bytes

Database Buffers 37748736 bytes

Redo Buffers 6451200 bytes

Connect with RMAN session.

C:\>RMAN target=sys
Recovery Manager: Release 11.1.0.6.0 - Production on Thu Apr 17 10:12:53 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
target database Password:connected to target database: TEST (not mounted)

Restore Controlfile.

RMAN> restore controlfile from 'C:\app\m.taj\oradata\test\CONTROL01.ctl';
Starting restore at 17-APR-08using target database control file instead of recovery catalogallocated channel: ORA_DISK_1channel ORA_DISK_1: SID=151 device type=DISK
channel ORA_DISK_1: copied control file copyoutput file name=+DGRP2/test/controlfile/current.256.652270419Finished restore at 17-APR-08

RMAN> alter database mount;
database mountedreleased channel: ORA_DISK_1

Take backup of database which we use for ASM diskgroup.

RMAN> backup as copy database format '+DGRP2';

Starting backup at 17-APR-08allocated channel: ORA_DISK_1channel ORA_DISK_1: SID=151 device type=DISKchannel ORA_DISK_1: starting datafile copyinput datafile file number=00001 name=C:\APP\M.TAJ\ORADATA\TEST\SYSTEM01.DBFoutput file name=+DGRP2/test/datafile/system.257.652270565 tag=TAG20080417T101550 RECID=1 STAMP=652270748channel ORA_DISK_1: datafile copy complete, elapsed time: 00:03:06channel ORA_DISK_1: starting datafile copyinput datafile file number=00002 name=C:\APP\M.TAJ\ORADATA\TEST\SYSAUX01.DBFoutput file name=+DGRP2/test/datafile/sysaux.258.652270761 tag=TAG20080417T101550 RECID=2 STAMP=652270908channel ORA_DISK_1: datafile copy complete, elapsed time: 00:02:35channel ORA_DISK_1: starting datafile copyinput datafile file number=00003 name=C:\APP\M.TAJ\ORADATA\TEST\UNDOTBS01.DBFoutput file name=+DGRP2/test/datafile/undotbs1.259.652270927 tag=TAG20080417T101550 RECID=3 STAMP=652270952channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:35channel ORA_DISK_1: starting datafile copycopying current control fileoutput file name=+DGRP2/test/controlfile/backup.260.652270971 tag=TAG20080417T101550 RECID=4 STAMP=652270975channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:07channel ORA_DISK_1: starting datafile copyinput datafile file number=00004 name=C:\APP\M.TAJ\ORADATA\TEST\USERS01.DBFoutput file name=+DGRP2/test/datafile/users.261.652270989 tag=TAG20080417T101550 RECID=5 STAMP=652270991channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03channel ORA_DISK_1: starting full datafile backup setchannel ORA_DISK_1: specifying datafile(s) in backup setincluding current SPFILE in backup setchannel ORA_DISK_1: starting piece 1 at 17-APR-08channel ORA_DISK_1: finished piece 1 at 17-APR-08piece handle=+DGRP2/test/backupset/2008_04_17/nnsnf0_tag20080417t101550_0.262.652271003 tag=TAG20080417T101550 comment=NONEchannel ORA_DISK_1: backup set complete, elapsed time: 00:00:02Finished backup at 17-APR-08

RMAN> switch database to copy;

datafile 1 switched to datafile copy "+DGRP2/test/datafile/system.257.652270565"
datafile 2 switched to datafile copy "+DGRP2/test/datafile/sysaux.258.652270761"
datafile 3 switched to datafile copy "+DGRP2/test/datafile/undotbs1.259.652270927"
datafile 4 switched to datafile copy "+DGRP2/test/datafile/users.261.652270989"

Again connect to sqlplus session and perform incomplete recovery

C:\>sqlplus sys/oracle as sysdba
SQL*Plus: Release 11.1.0.6.0 - Production on Thu Apr 17 10:26:24 2008
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to:Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 1071679 generated at 04/17/2008 10:11:10 needed for thread 1ORA-00289: suggestion : +DGRP2ORA-15173: entry 'ARCHIVELOG' does not exist in directory 'TEST'ORA-00280: change 1071679 for thread 1 is in sequence #14
Specify log: {=suggested filename AUTO CANCEL}

CANCEL

Media recovery cancelled.

OPEN database with RESETLOGS option.

SQL> alter database open resetlogs;
Database altered.

4. Drop old tempfile and create new tempfile in existing temp tablespace

SQL> alter database tempfile 'c:\app\m.taj\oradata\test\temp01.dbf' 2 drop including datafiles;
Database altered.

SQL> alter tablespace temp add tempfile size 512m 2 autoextend on next 250m maxsize unlimited;
Tablespace altered.

SQL> select tablespace_name, file_name, bytes from dba_temp_files;
TABLESPACE_NAME FILE_NAME BYTES------------------------------ ------------------------------ ----------TEMP +DGRP2/test/tempfile/temp.266. 536870912 652271571

5. Recreate All redolog group on ASM diskgroup

SQL> select a.group#, a.member, b.bytes

2 from v$logfile a, v$log b where a.group# = b.group#;

GROUP# MEMBER BYTES

---------- ------------------------------------ ---------

3 C:\APP\M.TAJ\ORADATA\TEST\REDO03.LOG 52428800
2 C:\APP\M.TAJ\ORADATA\TEST\REDO02.LOG 52428800
1 C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG 52428800

SQL> select group#,status from v$log;
GROUP# STATUS

---------- ----------------

1 CURRENT

2 UNUSED

3 UNUSED

SQL> alter system switch logfile;
System altered.
SQL> alter system switch logfile;
System altered.

SQL> select group#,status from v$log;
GROUP# STATUS

---------- ----------------

1 ACTIVE

2 ACTIVE

3 CURRENT

SQL> alter database drop logfile group 1;

alter database drop logfile group 1*ERROR at line 1:ORA-01624: log 1 needed for crash recovery of instance test (thread 1)ORA-00312: online log 1 thread 1: 'C:\APP\M.TAJ\ORADATA\TEST\REDO01.LOG'

When you get above error message then set checkpoint with below command.

SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 1;
Database altered.
SQL> alter database add logfile group 1 size 10m;
Database altered.

SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 2;
Database altered.
SQL> alter database add logfile group 2 size 10m;
Database altered.

SQL> alter system checkpoint global;
System altered.
SQL> alter database drop logfile group 3;
Database altered.
SQL> alter database add logfile group 3 size 10m;
Database altered.

SQL> column member format a30

SQL> select a.group#, a.member, b.bytes

2 from v$logfile a, v$log b where a.group# = b.group#;
GROUP# MEMBER BYTES

---------- ------------------------------ ----------

3 +DGRP2/test/onlinelog/group_3. 10485760 277.652273117
2 +DGRP2/test/onlinelog/group_2. 10485760 274.652273019
1 +DGRP2/test/onlinelog/group_1. 10485760 271.652272977
1 +DGRP2/test/onlinelog/group_1. 10485760 272.652272979
2 +DGRP2/test/onlinelog/group_2. 10485760 275.652273021
3 +DGRP2/test/onlinelog/group_3. 10485760 278.652273119
6 rows selected.

6. Recreate SPFILE on ASM diskgroup

SQL> create pfile='c:\initTEST.ora' from spfile;
File created.
SQL> create spfile='+DGRP2/spfileTEST.ora' from pfile='c:\initTEST.ora';
File created.

7. Detele all backup copy from RMAN

RMAN> delete noprompt force copy;

Find more details

http://www.idevelopment.info/data/Oracle/DBA_tips/Automatic_Storage_Management/ASM_33.shtml#Overview

Managing Diskgroups

Creating Diskgroup
1. Assign Unqiue name for each diskgroup
2. Specify redundancy level of disk group
There is three type of redundancy level
1. NORMAL redundancy with 2 way mirroring
2. HIGH redundancy with 3 way mirroring
3. EXTERNAL redundancy with NO mirroring.

3. Specify disks as belonging to specific failgroup.
If we manually not define any failgroup for disk then Oracle automatically create failgroup for each diskgroup.

4. Specify asm disks (v$asm_disk) for diskgroup.

5. Optionally specify disk group attributes such as compatibility or allocation unit size.

Find more http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asmdiskgrps.htm#i1009309

SQL> conn / as sysasm
Connected to an idle instance.
SQL> startup nomount
ASM instance started
Total System Global Area 267825152 bytes
Fixed Size 1332584 bytes
Variable Size 241326744 bytes
ASM Cache 25165824 bytes

Create DISKGROUP with NORMAL REDUNDANCY
SQL> CREATE DISKGROUP dgrp1 NORMAL REDUNDANCY
2 FAILGROUP controller1 DISK
3 'e:\asmdisks\disk1' NAME diska1,
4 'e:\asmdisks\disk2' NAME diska2
5 FAILGROUP controller2 DISK
6 'e:\asmdisks\disk3' NAME diska3;
Diskgroup created.

SQL> select group_number,
2 name,
3 type,
4 total_mb,
5 free_mb
6 from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE TOTAL_MB FREE_MB
------------ ------------------------------ ------ ---------- ------
1 DGRP1 NORMAL 1536 1432

ADD NEW DISK in exiting DISKGROUP dgrp1
SQL> alter diskgroup dgrp1 add disk 'e:\asmdisks\disk4';
Diskgroup altered.

SQL> select group_number,
2 name,
3 type,
4 total_mb,
5 free_mb
6 from v$asm_diskgroup;
GROUP_NUMBER NAME TYPE TOTAL_MB FREE_MB
------------ ------------------------------ ------ ---------- ------
1 DGRP1 NORMAL 2048 1940

Managing Capacity in Disk Groups
When ASM provides redundancy, such as when you create a disk group with NORMAL or HIGH redundancy, you must have sufficient capacity in each disk group to manage a re-creation of data that is lost after a failure of one or two failure groups. After one or more disks fail, the process of restoring redundancy for all data requires space from the surviving disks in the disk group. If not enough space remains, then some files might end up with reduced redundancy.

Find more http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asmdiskgrps.htm#CHDGGGAD

Negative Values of USABLE_FILE_MB

Due to the relationship between FREE_MB, REQUIRED_MIRROR_FREE_MB, and USABLE_FILE_MB, USABLE_FILE_MB can become negative. Although this is not necessarily a critical situation, it does mean that:

• Depending on the value of FREE_MB, you may not be able to create new files.

• The next failure might result in files with reduced redundancy.

If USABLE_FILE_MB becomes negative, it is strongly recommended that you add more space to the disk group as soon as possible.

When Should External, Normal, or High Redundancy Be Used?

ASM mirroring runs on the database server and Oracle recommends to off load this processing to the storage hardware RAID controller by using external redundancy. You can use normal redundancy in the following scenarios:

• Storage system does not have RAID controller

• Mirroring across storage arrays

• Extended cluster configurations

In general, ASM mirroring is the Oracle alternative to third party logical volume managers. ASM mirroring eliminates the need to deploy additional layers of software complexity in your Oracle database environment.

Find more http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asmdiskgrps.htm#CHDHDGDI

Next Part >>> http://dbataj.blogspot.com/2008/04/migrating-databases-from-non-asm-to-asm.html

ASM Instance Creation

ASM Instance Creation on Windows

1. Create INIT.ORA file for asm instance

DIAGNOSTIC_DEST=C:\app\m.taj\diag
_asm_allow_only_raw_disks= FALSE
instance_type=asm
remote_login_passwordfile=exclusive
memory_target=52m

Note: Minimum required parameters.

C:\>oradim -new -asmsid +ASM -startmode manual
Instance created.

C:\>oradim -edit -asmsid +ASM -startmode a

C:\>oradim -edit -asmsid +ASM -syspwd change_on_install

C:\>set ORACLE_SID=+ASM

C:\>sqlplus "/ as sysdba"

SQL*Plus: Release 11.1.0.6.0 - Production on Tue Apr 8 11:22:47 2008

Copyright (c) 1982, 2007, Oracle. All rights reserved.

Connected to an idle instance.

SQL> startup pfile ='c:\init.ora';
ASM instance started

Total System Global Area 267825152 bytes
Fixed Size 1332584 bytes
Variable Size 241326744 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted


SQL> create spfile from pfile='c:\init.ora';

File created.

SQL> shutdown
ORA-15100: invalid or missing diskgroup name


ASM instance shutdown
SQL> startup
ASM instance started

Total System Global Area 267825152 bytes
Fixed Size 1332584 bytes
Variable Size 241326744 bytes
ASM Cache 25165824 bytes
ORA-15110: no diskgroups mounted

SQL> SELECT group_number, disk_number, mount_status, header_status, state, path FROM v$asm_disk;
GROUP_NUMBER DISK_NUMBER MOUNT_S HEADER_STATU STATE PATH

----------- ----------- ------- ------------ -------- -------------------- 0 1 CLOSED CANDIDATE NORMAL E:\ASMDISKS\DISK2 0 0 CLOSED CANDIDATE NORMAL E:\ASMDISKS\DISK1

Administratering ASM instance

http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asminst.htm#CHDGDJDF

http://www.idevelopment.info/data/Oracle/DBA_tips/Automatic_Storage_Management/ASM_10.shtml

Next Part >>> http://dbataj.blogspot.com/2008/04/managing-diskgroups.html

Preparing disk for ASM instance

Preparing disk for ASM instance on Windows

We can use DISKPART.EXE to create primary,logical partitions and using ASMTOOL (commandline) & ASMTOOLG (GUI)stamp(map) for ASM instance.

Preparing Disks for ASM

http://download.oracle.com/docs/cd/B28359_01/server.111/b31107/asmprepare.htm#BHCDCFIH

Diskpart.exe

http://download.oracle.com/docs/cd/B28359_01/install.111/b28250/racstorage.htm#BABFIDGI

ASMTOOL & ASMTOOLG

http://download.oracle.com/docs/cd/B28359_01/install.111/b28250/racstorage.htm#sthref212

Demostration

E:\>asmtool -list
NTFS \Device\Harddisk0\Partition1 37997M
NTFS \Device\Harddisk0\Partition2 38319M

E:\>mkdir asmdisks
E:\>cd e:\asmdisks
E:\asmdisks>asmtool -create e:\asmdisks\DISK1 512
E:\asmdisks>asmtool -create e:\asmdisks\DISK2 512
E:\asmdisks>cd c:\app\m.taj\product\11.1.0

More Reference on disks preparing for ASM

http://www.databasejournal.com/features/oracle/article.php/3571371

http://www.databasejournal.com/img/jsc_ASM_Listing2.html

Next Part >>> ASM Instance Creation on Windows

http://dbataj.blogspot.com/2008/04/asm-instance-creation.html

ASM - Introduction

ASM - Automatic storage management

Introduce with Oracle 10gr1.

About ASM

http://www.oracle.com/database/demos/asm.html

Benefits of Automatic Storage Management

http://download.oracle.com/docs/cd/B14117_01/server.101/b10743/mgmt_db.htm#sthref2270

ASM provides many of the same benefits as storage technologies such as RAID or logical volume managers (LVMs). Like these technologies, ASM enables you to create a single disk group from a collection of individual disk devices. It balances I/O to the disk group across all of the devices in the disk group. It also implements striping and mirroring to improve I/O performance and data reliability.
However, unlike RAID or LVMs, ASM implements striping and mirroring at the file level. This implementation enables you to specify different storage attributes for individual files in the same disk group.

Next Part >>>Preparing Disks for ASM instance

http://dbataj.blogspot.com/2008/04/preparing-disk-for-asm-instance.html