Oracle-Database: December 2007

Monday, December 24, 2007

Creating a physical standby Database in Oracle10g

STEPS for creating 10g dataguard

prerequisite : 9i dataguard setup knowledge

step1 :

Prepare initSID.ora file for primary and standby databases as follow.

** STANDBY setup parameters are given in BOLD

part A)

**** Production database primary file ****

prod.__db_cache_size=125829120
prod.__java_pool_size=4194304
prod.__large_pool_size=4194304
prod.__shared_pool_size=79691776
prod.__streams_pool_size=0
*.audit_file_dest='C:\oracle\product\10.2.0\admin\prod\adump'
*.background_dump_dest='C:\oracle\product\10.2.0\admin\prod\bdump'
*.compatible='10.2.0.3.0'
*.control_files='Q:\oradata\prod\control01.ctl','Q:\oradata\prod\control02.ctl','Q:\oradata\prod\control03.ctl'
*.core_dump_dest='C:\oracle\product\10.2.0\admin\prod\cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_name='prod'
*.db_recovery_file_dest='C:\oracle\product\10.2.0\flash_recovery_area'
*.db_recovery_file_dest_size=2147483648

*.instance_name='prod'
*.job_queue_processes=10

*.db_unique_name='prod'
*.fal_client='prod'
*.fal_server='prod_stdby'
*.log_archive_config='DG_CONFIG=(prod,stdby)'
*.log_archive_dest_1='LOCATION=Q:\oradata\prod\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=prod'
*.log_archive_dest_2='SERVICE=prod_stdby VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=stdby LGWR ASYNC REOPEN=10'
*.remote_login_passwordfile='EXCLUSIVE'

*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t%s%r.arc'
*.open_cursors=300
*.pga_aggregate_target=72351744
*.processes=150
*.service_names='prod'
*.sga_target=218103808
*.standby_archive_dest='q:\arch'
*.standby_file_management='AUTO'
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='C:\oracle\product\10.2.0\admin\prod\udump'

part B)

**** standby database primary file ****

*.compatible='10.2.0.3.0'
*.control_files='C:\oradata\prod\stdby.ctl'
*.instance_name='stdby'
*.db_name='prod'
*.db_unique_name='stdby'
*.fal_client='prod_stdby'
*.fal_server='prod'
*.log_archive_config='DG_CONFIG=(prod,stdby)'
*.log_archive_dest_1='LOCATION=C:\oradata\prod\stdby\arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=stdby'
*.log_archive_dest_2='SERVICE=prod VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=prod LGWR ASYNC REOPEN=10'
*.db_file_name_convert='Q:\','C:\'
*.log_file_name_convert='Q:\','C:\'
*.remote_login_passwordfile='EXCLUSIVE'

*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t_%s_%r'
*.service_names='stdby'
*.shared_pool_size=104857600
*.standby_file_management='AUTO'

STEP 2:

part A)

shutdown primary database cleanly and copy datafiles to standby location.

part B) create standby controlfile by giving following command at production database at mount stage
alter databse create standby controlfiel as 'location\filename' ;

move this generated file to standby controlfile location as pointed by standby initSID.ora file

step 3)
create oracle service and password file with the same password at standby database location

step 4)
prepare the TNSNAMES.ORA and LISTENER.ora at both production and standy locations.

To check archive log gap

SELECT * FROM (
SELECT sequence#, archived, applied,
TO_CHAR(completion_time, 'RRRR/MM/DD HH24:MI') AS completed
FROM sys.v$archived_log
ORDER BY sequence# DESC)
WHERE ROWNUM <= 10

Migrating Dictionar managed tablespace to locally managed tablespace

SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TEMPD')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('TEMPD'); END;
*
ERROR at line 1:
ORA-03245: Tablespace has to be dictionary managed, online and permanent to be able to migrate
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('USERSD')
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10643: Database should be mounted in restricted mode and Exclusive mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP RESTRICT
ORACLE instance started.
Total System Global Area 160504432 bytes
Fixed Size 453232 bytes
Variable Size 125829120 bytes
Database Buffers 33554432 bytes
Redo Buffers 667648 bytes
Database mounted.
Database opened.
SQL>
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPD not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> SELECT * FROM V$TABLESPACE;
TS# NAME INC
---------- ------------------------------ ---
0 SYSTEM YES
1 UNDOTBS1 YES
2 TEMP YES
3 USERS YES
5 USERSD YES
6 TEMPD YES
7 SMALL YES
8 TEMPL YES
8 rows selected.
SQL> ALTER TABLESPACE USERS READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE SMALL READ ONLY;
Tablespace altered.
SQL> ALTER TABLESPACE USERSD READ ONLY;
Tablespace altered.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPD not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> DROP TABLESPACE TEMPD;
DROP TABLESPACE TEMPD
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPL ;
Database altered.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPL not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> DROP TABLESPACE TEMPD;
Tablespace dropped.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPL not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> SELECT NAME FROM V$TABLESPACE;
NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
USERS
USERSD
SMALL
TEMPL
7 rows selected.
SQL> DROP TABLESPACE &1;
Enter value for 1: USERS
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE USERS
Tablespace dropped.
SQL> /
Enter value for 1: USERSD
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE USERSD
DROP TABLESPACE USERSD
*
ERROR at line 1:
ORA-01549: tablespace not empty, use INCLUDING CONTENTS option

SQL> DROP TABLESPACE USERSD INCLUDING CONTENTS ;
Tablespace dropped.
SQL> DROP TABLESPACE &1;
Enter value for 1: SAMLL
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE SAMLL
DROP TABLESPACE SAMLL
*
ERROR at line 1:
ORA-00959: tablespace 'SAMLL' does not exist

SQL> /
Enter value for 1: SMALL
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE SMALL
Tablespace dropped.
SQL> /
Enter value for 1: TEMPL
old 1: DROP TABLESPACE &1
new 1: DROP TABLESPACE TEMPL
DROP TABLESPACE TEMPL
*
ERROR at line 1:
ORA-12906: cannot drop default temporary tablespace

SQL> SELECT NAME FROM V$TABLESPACE;
NAME
------------------------------
SYSTEM
UNDOTBS1
TEMP
TEMPL
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
BEGIN DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM'); END;
*
ERROR at line 1:
ORA-10647: Tablespace other than SYSTEM,UNDOTBS1, TEMPL not found in read only mode
ORA-06512: at "SYS.DBMS_SPACE_ADMIN", line 0
ORA-06512: at line 1

SQL> DROP TABLESPACE TEMP;
Tablespace dropped.
SQL> EXEC DBMS_SPACE_ADMIN.TABLESPACE_MIGRATE_TO_LOCAL('SYSTEM')
PL/SQL procedure successfully completed.
SQL> CREATE TABLESPACE TESTD
2 DATAFILE 'TESTD'
3 EXTENT MANAGEMENT DICTIONARY ;
CREATE TABLESPACE TESTD
*
ERROR at line 1:
ORA-12913: Cannot create dictionary managed tablespace

SQL> SHOW PARAMETER READ
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
db_file_multiblock_read_count integer 16
parallel_threads_per_cpu integer 2
read_only_open_delayed boolean FALSE
thread integer 0
SQL> CREATE TABLESPACE TESTD
2 DATAFILE 'TESTD' ;
CREATE TABLESPACE TESTD
*
ERROR at line 1:
ORA-01119: error in creating database file 'TESTD'
ORA-17610: file 'TESTD' does not exist and no size specified
ORA-27041: unable to open file
OSD-04002: unable to open file
O/S-Error: (OS 2) The system cannot find the file specified.

SQL> ED
Wrote file afiedt.buf
1 CREATE TABLESPACE TESTD
2* DATAFILE 'TESTD' SIZE 1M
3
SQL>
SQL> /
Tablespace created.
SQL> ALTER TABLESPACE TESTD READ ONLY ;
Tablespace altered.

Installation of oracle9i/10g on Linux & Solaris

9/10g install on Linux/Solaris

Install Oracle 9i Database on Linux RHEL AS 3

the following lines can be added to the /etc/sysctl.conf file:
kernel.shmmax = 2147483648
kernel.shmmni = 128
kernel.shmall = 2097152
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
In addition the following lines can be added to the /etc/security/limits.conf file:
oracle soft nofile 65536
oracle hard nofile 65536
oracle soft nproc 16384
oracle hard nproc 16384
Create the new groups and users:
groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
passwd oracle
Create the directories in which the Oracle software will be installed:
mkdir /u01
chown -R oracle.dba /u01
Login as the oracle user and add the following lines at the end of the .bash_profile file:
# Oracle 9i
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0.1.0
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib
export LD_ASSUME_KERNEL=2.4.1
Install the 3006854 patch:
unzip p3006854_9204_LINUX.zip
cd 3006854
sh rhel3_pre_install.sh
/mnt/cdrom/runInstaller
When the ins_oemagent.mk error appears install the 3119415 patch:
unzip p3119415_9203_LINUX.zip
cd 3119415
sh patch.sh
Once this is complete click the retry button. Ignore the ins_ctx.mk error and all other .mk errors that come further in installation as this is fixed in the 9.2.0.4.0 patch. The installation is now complete.

Install Oracle 10g Database on Linux 3 AS

Alternatively the following lines can be added to the /etc/sysctl.conf file:
kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000
In addition the following lines can be added to the /etc/security/limits.conf file:
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384
vi /etc/pam.d/login
session required /lib/security/pam_limits.so
Create the new groups and users:
groupadd oinstall
groupadd dba
useradd -g oinstall -G dba oracle
passwd oracle
Create the directories in which the Oracle software will be installed:
mkdir /u01
chown oracle.dba /u01
chmod 777 /u01
Login as the oracle user and add the following lines at the end of the .bash_profile file:
# Oracle 9i
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0.1.0
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib;
export LD_ASSUME_KERNEL=2.4.1
Install the 3006854 patch:
unzip p3006854_9204_LINUX.zip
cd 3006854
sh rhel3_pre_install.sh
/mnt/cdrom/runInstaller
The installation is now complete.

On solaris +mix

Checking memory/swap etc.
1./usr/sbin/swap –l
2 . check df –h /tmp and if required create tmp and export it along
with tmpdir
3. /usr/sbin/prtconf grep “Memory Size”
4. df –g grep “block size”
5. ulimit –Sa
6. ulimit –Ha
7. id oracle
8. id nobody
9. tar xvf ora******.tar
9.check for availability of required executables
/usr/bin/which make
/usr/bin/which ar
/usr/bin/which ld
/usr/bin/which nm
10.$ export DISPLAY=hostname:0.0
$xclock
11.to determine system architecture /bin/isainfo –kv
12. find version of solaris uname –r
11. pkginfo –i SUNWarc SUNWbtool SUNWhea SUNWlibm SUNWlibms SUNWsprot
\ SUNWbtoo SUNWi1cs SUNWi10f SUNWsprox SUNWxefnt
SUNWi15cs
13. finding showrev –p
Adding patch /usr/sbin/patchadd –p grep patch_number (without version)
Sunsolve.sun.com
Verify hostname set by /etc/hosts only
$Cat etc/nsswitch.conf grep hosts
$hostname
$domainname
$cat /etc/hosts grep ‘eval hostname’
$/usr/sbin/patchadd –p grep 111713
14. Finding release $cat /etc/release
15. edit /etc/system file
1. set shmsys:shminfo_shmmax=4294967295
2. set shmsys:shminfo_shmmin=1
3. set shmsys:shminfo_shmmni=100
4. set shmsys:shminfo_shmseg=10
5. set semsys:seminfo_semmns=2000
6. set semsys:seminfo_semmsl=1000
7. set semsys:seminfo_semmni=100
16. id –a oracle
groupadd -g 115 dba
/usr/sbin/useradd -u 175 –c “oracle software owner.” –g
oinstall –G dba ,oper
-d /u01/app/oracle -m -s /bin/ksh oracle
id nobody
.profile
unzip xyz.zip
cpio –idmvwhen prompt for 2nd cd give $eject cdrom
to execute profile $ .~/.profile
.profile File
export ORACLE_BASE=/oracle
export ORACLE_HOME=$ORACLE_BASE/ora92
export ORACLE_SID=orcl
PATH=$PATH/ORCLE_HOME/bin:/usr/ccs/bin:/usr/bin:/etc:/usr/openwin/bin:/usr/local/bin:/opt/SUNWspro/bin
Unset TWO_TASK

Sql Scripts for knowing the transaction status

select * from v$transaction;

--All tranasactions + sid and username
select s.sid,s.username, t.start_time, t.used_ublk, t.used_urec
from v$transaction t, v$session s
where t.ses_addr=s.saddr;

--All tranasactions + sid and username + first 64 bytes of SQL
select s.sid,s.username, t.start_time, t.used_ublk, t.used_urec,sql.sql_text
from v$transaction t, v$session s, v$sql sql
where t.ses_addr=s.saddr
and s.sql_address=sql.address and s.sql_hash_value=sql.hash_value;

helping-optimizer-to-take-decision-10g.

Give more knowledge to cost based optimizer about object statistics
and make it intelligent..

U remember Knowledge is Power - Aristotle

-- Gather stats for a table. Auto sampling and auto number of histogram
-- buckets, might not work in previous Oracle version.begin
dbms_stats.gather_table_stats('&OWNER','&TABLE_NAME',null,
DBMS_STATS.AUTO_SAMPLE_SIZE -- put null or percentage for older oracle
,false,'FOR ALL COLUMNS SIZE AUTO' -- put 1 for older oracle
,1,'GLOBAL',false,null,null,null,false);
end;
/
--Gather stats for a schema
begin
dbms_stats.gather_schema_stats('&OWNER',DBMS_STATS.AUTO_SAMPLE_SIZE,false,
'FOR ALL COLUMNS SIZE AUTO',1,'GLOBAL',true,null,null,'GATHER AUTO');
end;
/
--See some column statistics
select column_name,num_distinct,rawtohex(low_value),rawtohex(high_value),density
from dba_tab_col_statistics
where owner='&OWNER' and table_name='&TABLE_NAME';

--See some histogram info
select * from dba_tab_histograms
where owner='&OWNER' and table_name='&TABLE_NAME'
order by column_name, endpoint_number;

--Old fashion analyze
analyze table &OWNER.&TABLE_NAME compute statistics;
analyze table &OWNER.&TABLE_NAME estimate statistics;
analyze table &OWNER.&TABLE_NAME delete statistics;

--Turning table monitoring on:
select 'alter table "'||owner||'"."'||table_name||'" monitoring;' stmt
from all_tables
where monitoring ='NO' and tablespace_name <>'SYSTEM';

Query in DR....

What is Right What is Wrong
Found a DBA giving following commands at standby site to switchover while standby database was mounted.

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY
*
ERROR at line 1:
ORA-16139: media recovery required

SQL> recover managed standby database disconnect;
Media recovery complete.
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY

ERROR at line 1:
ORA-16139: media recovery required
**********
from another SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE FINISH;

Database altered.

SQL> ALTER DATABASE ACTIVATE STANDBY DATABASE;

Database altered.

How to create Databas in 2 node RAC ..?

Database creation in RAC environment( 2 instances). can not rely on dbca always
CREATE PARAMETER FILE AND PLACE IT IN SHARED LOCATION
(O:\TEST\INITTEST.ORA shared location here ) AS FOLLOWING
SO THAT BOTH RAC INSTANCES CAN SHARE IT. point to this pfile/spfile from individual instance home/dbs.

# START OF INITTEST.ORA

*.aq_tm_processes=1
*.test1.background_dump_dest='D:\Oracle\admin\test1\bdump'
*.test2.background_dump_dest='D:\Oracle\admin\test2\bdump'

*.cluster_database_instances=2
*.cluster_database=TRUE

*.compatible='9.2.0.0.0'
*.control_files='O:\test\control01.ctl','O:\test\control02.ctl','O:\test\control03.ctl'
*.test1.core_dump_dest='D:\Oracle\admin\test1\cdump'
*.test2.core_dump_dest='D:\Oracle\admin\test2\cdump'
*.db_block_size=8192
*.db_cache_size=25165824
*.db_file_multiblock_read_count=16
*.db_name='test'

test1.instance_name='test1'
test2.instance_name='test2'
test1.instance_number=1
test2.instance_number=2

*.job_queue_processes=1

*.pga_aggregate_target=25165824
*.processes=150
*.remote_login_passwordfile='exclusive'
*.shared_pool_size=150994944
test1.thread=1
test2.thread=2
*.undo_management='AUTO'
*.undo_retention=10800
#test2.undo_tablespace='UNDOTBS2'
test1.undo_tablespace='UNDOTBS1'
test2.user_dump_dest='D:\Oracle\admin\test2\udump'

# END INITTEST.ORA

AT RAC NODE 1

SQL>STARTUP NOMOUNT PFILE='O:\TEST\INITTEST.ORA'
THEN GIVE CREATE DATABASE STMT

1 CREATE DATABASE test
2 MAXINSTANCES 2
3 --MAXLOGHISTORY
4 -- MAXLOGFILES 192
5 --MAXLOGMEMBERS 3
6 --MAXDATAFILES 1024
7 controlfile reuse
8 DATAFILE 'o:\test\system01.dbf' SIZE 200M REUSE AUTOEXTEND ON NEXT 10240K MAXSIZE UNLIMITED
9 DEFAULT TEMPORARY TABLESPACE TEMP TEMPFILE 'o:\test\temp01.dbf' SIZE 5M REUSE AUTOEXTEND ON NEXT 640K MAXSIZE UNLIMIT
10 UNDO TABLESPACE "UNDOTBS1" DATAFILE 'o:\test\undotbs01.dbf' SIZE 20M REUSE AUTOEXTEND ON NEXT 5120K MAXSIZE UNLIMITED
11 CHARACTER SET WE8MSWIN1252
12 NATIONAL CHARACTER SET AL16UTF16
13 LOGFILE 'o:\test\redo01.log' SIZE 10240K REUSE,
14 'o:\test\redo02.log' SIZE 10240K REUSE,
Database created.

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 1 10485760 1 NO INACTIVE 1 06-OCT-06
2 1 2 10485760 1 NO CURRENT 7129 06-OCT-06

SQL> SELECT MEMBER FROM V$LOGFILE ;

MEMBER
---------------------------------------------------------------------------------------------------------------------------
O:\TEST\REDO01.LOG
O:\TEST\REDO02.LOG

Database altered.

SQL> ALTER DATABASE ADD LOGFILE THREAD 2 'O:\TEST\REDO03.LOG' ;

Database altered.

SQL> ALTER DATABASE ADD LOGFILE THREAD 2 'O:\TEST\REDO04.LOG' ;

Database altered.

SQL> SELECT * fROM V$LOG;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 1 10485760 1 NO INACTIVE 1 06-OCT-06
2 1 2 10485760 1 NO CURRENT 7129 06-OCT-06
3 2 0 10485760 1 YES UNUSED 0
4 2 0 10485760 1 YES UNUSED 0

SQL> ALTER DATABASE ENABLE THREAD 2 ;
Database altered.

******************now OPEN THE INSTANCE 2 ***************
**********THEN QUERY AGAIN HERE **********

SQL> SELECT * fROM V$LOG;

GROUP# THREAD# SEQUENCE# BYTES MEMBERS ARC STATUS FIRST_CHANGE# FIRST_TIM
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- ---------
1 1 1 10485760 1 NO INACTIVE 1 06-OCT-06
2 1 2 10485760 1 NO CURRENT 7129 06-OCT-06
3 2 1 10485760 1 NO CURRENT 8271 06-OCT-06
4 2 0 10485760 1 YES UNUSED 0

Now dont forget to create and assign undo tablespace for second instance

Way to get Query execution plan in Oracle10g

traditional method before 10g

select * from v$sql where address='&ADDRESS' and hash_value=&HASH_VALUE;

10g

select * from v$sql where sql_id='&SQL_ID';
select * from v$sqlstats where sql_id='&SQL_ID';
select * from dba_hist_sqlstat where sql_id='&SQL_ID';

select * from table(DBMS_XPLAN.DISPLAY_CURSOR('&SQL_ID', NULL, 'ALL'));
select * from table(DBMS_XPLAN.DISPLAY_AWR('&SQL_ID',&PLAN_HASH,NULL,'ALL'));

Sunday, December 23, 2007

any-longer-getting-ddl

--1 Get DDL of the object:
declare
clb CLOB; pos INTEGER:=1; amt INTEGER; len INTEGER; txt VARCHAR2(4000);
begin
clb := dbms_metadata.get_ddl ('$OBJ_TYPE','$OBJ_NAME','$OWNER');
len := LENGTH(clb);
LOOP
amt := nvl(INSTR (clb, chr(10), pos),len) - pos;
IF amt>0 THEN txt := NVL(SUBSTR (clb, pos, amt),' '); ELSE txt:=''; END IF;
pos := pos + amt + 1;
DBMS_OUTPUT.put_line (SUBSTR(txt,1,250));
EXIT WHEN pos>=len;
END LOOP;
end;
/

--2 Get DDL of all $OBJ_TYPE objects in schema:
declare
clb CLOB; pos INTEGER; amt INTEGER; len INTEGER; txt VARCHAR2(4000);
handle NUMBER; transhandle NUMBER; cnt NUMBER;
begin
handle := dbms_metadata.open ('$OBJ_TYPE');
--dbms_metadata.set_filter (handle, 'NAME_EXPR','like ''%''');
dbms_metadata.set_filter (handle, 'SCHEMA', '$OWNER');
dbms_metadata.set_count (handle, 10);
transhandle := dbms_metadata.add_transform (handle, 'DDL');
dbms_metadata.set_transform_param (transhandle, 'SQLTERMINATOR', TRUE);
LOOP
clb := dbms_metadata.fetch_clob (handle);
EXIT WHEN clb is null;
pos := 1;
len := LENGTH(clb);
txt := '';
LOOP
amt := nvl(INSTR (clb, chr(10), pos),len) - pos;
IF amt>0 THEN txt := NVL(SUBSTR (clb, pos, amt),' '); ELSE txt:=''; END IF;
pos := pos + amt + 1;
DBMS_OUTPUT.put_line (SUBSTR(txt,1,250));
EXIT WHEN pos>=len;
END LOOP;
END LOOP;
end;
/

Typical-index-maintenance-tasks-9i10g

--1 Move index from one tablespace to another
alter index &OWNER.&INDEX_NAME rebuild tablespace &NEW_TS_NAME
;

--2 Moving index partition from one tablespace to another
alter index &OWNER.&INDEX_NAME
rebuild partition &IND_PART_NAME tablespace &NEW_TS_NAME
;

--3 Moving all index subpartitions from one tablespace to anotheralter index &OWNER.&INDEX_NAME
rebuild subpartition &IND_SUBPART_NAME tablespace &NEW_TS_NAME
;

--4 Unusable indexesselect 'alter index '||owner||'.'||index_name||' rebuild;'
from dba_indexes where status='UNUSABLE';

--5 Unusable index partitionsselect 'alter index '||index_owner||'.'||index_name||
' rebuild partition '||partition_name||';' sql
from dba_ind_partitions where status='UNUSABLE';

--6 Unusable index subpartitionsselect 'alter index '||index_owner||'.'||index_name||
' rebuild subpartition '||subpartition_name||';' sql
from dba_ind_subpartitions where status='UNUSABLE';

--7 All things togetherselect 'alter index '||owner||'.'||index_name||' rebuild;' sql
from dba_indexes where status='UNUSABLE'
union all
select 'alter index '||index_owner||'.'||index_name||
' rebuild partition '||partition_name||';'
from dba_ind_partitions where status='UNUSABLE'
union all
select 'alter index '||index_owner||'.'||index_name||
' rebuild subpartition '||subpartition_name||';'
from dba_ind_subpartitions where status='UNUSABLE';

keeping-eye-on-sessions

--Long operations

select sid,username usern,serial#,opname,target,sofar,totalwork tot_work,units,
time_remaining remaning,elapsed_seconds elapsed,last_update_time last_time
from v&session_longops --where sid=73
order by last_update_time desc;

--All active sessions
select * from v&session where status='ACTIVE'
--and sid in (37,43)
order by sid;

--Find session's sid or process id by it's sid or process id

select sid, a.serial#, spid, a.username,
status, taddr, a.program
from v&session a, v&process b
where a.paddr=b.addr and a.username is not null
--and (sid=163 or spid=28179)
order by status, sid;

--Kill session

alter system kill session '&sid,&serial';

moving-tablespartitions-9i-onwards

-1 Move table from one tablespace to another-- (check for unusable indexes after that).
alter table $OWNER.$TABLE_NAME move tablespace $NEW_TS_NAME
;

--2 Move table partition from one tablespace to another
-- (check for unusable indexes and partitoned indexes after that).
alter table $OWNER.$TABLE_NAME
move partition $TAB_PART_NAME tablespace $NEW_TS_NAME
;

--3 Move table subpartition from one tablespace to another
-- (check for unusable indexes, partitioned indexes, and subpartitioned indexes).
alter table $OWNER.$TABLE_NAME
move subpartition $TAB_SUBPART_NAME tablespace $NEW_TS_NAME
;

moving-schema-tablesindexes

-1 Move group of TABLE segments (check for unusable indexes after that)
select 'alter table '||owner||'.'||segment_name||' move '||
decode(segment_type,
'TABLE PARTITION','partition '||partition_name,
'TABLE SUBPARTITION','subpartition '||partition_name,null)||' tablespace &NEW_TS_NAME;' sql
from dba_segments
where segment_type like 'TABLE%'
and tablespace_name='&TS_NAME'
and owner='&OWNER'
and segment_name='&SEG_NAME'
;

--2 Move group of INDEX segments
select 'alter index '||owner||'.'||segment_name||' rebuild '||
decode(segment_type,
'INDEX PARTITION','partition '||partition_name,
'INDEX SUBPARTITION','subpartition '||partition_name,null)||' tablespace &NEW_TS_NAME;' sql
from dba_segments
where segment_type like 'INDEX%'
and tablespace_name='&TS_NAME'
and owner='&OWNER'
and segment_name='&SEG_NAME'
;

--3 List segments that will fail to expand
select /*+ all_rows */ segs.*
from
dba_segments segs,
sys.seg& s,
(select ts#,max(length) m from sys.fet& group by ts#) f
where s.ts#=f.ts# and extsize>m
and segs.header_file=s.file# and segs.header_block=s.block#
;

--4 List of fragmented segments
select segs.*
from
dba_segments segs,
(select file#, segblock# from sys.uet&
group by file#, segblock#
having count(*) > 1024
) f
where segs.header_file=f.file# and segs.header_block=f.segblock#
;

simple-log-management

select * from v&log; --status of logfile groups
select * from v&logfile order by group#; --status of logfiles
select * from v&instance; --status of the archiver
alter system archive log start; --restart the archiver
alter system switch logfile; --switch online log
alter system set log_archive_max_processes=4;

--Add logfile group
alter database add logfile group 4
('&logfilename1',
'&logfilename2') size 64M;

--Drop logfile group and all members in it
alter database drop logfile group &N;

--Add logfile member
alter database add logfile member '&logfilename' reuse to group 4;

--Drop logfile member
alter database drop logfile member '&logfilename';

--Checking archivelog mode
select dbid, name, resetlogs_time, log_mode from v&database;

alter system archive log start; -- restarts the archiver
select * from v&archive_dest; -- archiver destinations

--Altering destination
alter system set log_archive_dest_1='location=&path';
alter system set log_archive_dest_state_1='enable';

--Archived log info from the control file
select * from v&archived_log;

--The sequence# of last backed up log
select thread#, max(sequence#) from v&archived_log
where BACKUP_COUNT>0 group by thread#;

--Redo size (MB) per day, last 30 days
select trunc(first_time) arc_date, sum(blocks * block_size)/1048576 arc_size
from v&archived_log
where first_time >= (trunc(sysdate)-30)
group by trunc(first_time);

I frequently talk about optimizing top resource consumption.And now most obvious question from audience is always how to find such sessions/sqls. here

I frequently talk about optimizing top resource consumption.And now most obvious question from audience is always how to find such sessions/sqls. here are ways to get both while you dont have statspack snapshot you can use them as alternate.

QUERY(cusomizable,according to your workload and) and Sample Output from SPOOL

1 SELECT ses.sid
2 , DECODE(ses.action,NULL,'online','batch') "User"
3 , MAX(DECODE(sta.statistic#,9,sta.value,0))
4 /greatest(3600*24*(sysdate-ses.logon_time),1) "Log IO/s"
5 , MAX(DECODE(sta.statistic#,40,sta.value,0))
6 /greatest(3600*24*(sysdate-ses.logon_time),1) "Phy IO/s"
7 , 60*24*(sysdate-ses.logon_time) "Minutes"
8 FROM V$SESSION ses
9 , V$SESSTAT sta
10 WHERE ses.status = 'ACTIVE'
11 AND sta.sid = ses.sid
12 AND sta.statistic# IN (9,40)
13 GROUP BY ses.sid, ses.action, ses.logon_time
14 ORDER BY
15 SUM( DECODE(sta.statistic#,40,100*sta.value,sta.value) )
16* / greatest(3600*24*(sysdate-ses.logon_time),1) DESC
17
SQL> /

SID User Log IO/s Phy IO/s Minutes
---------- ------ ---------- ---------- ----------
9 online 12913.0075 12912.9963 26.7166667
5 online 98.9491051 98.9362416 29.8
14 online 4578.26388 .012476606 26.7166667
19 online 3170.5866 .00270636 24.6333333
20 online 1328.76316 .035087719 1.9
18 online .111731844 .026536313 11.9333333
7 online .749860101 0 29.7833333
21 online .2 0 .5
6 online .016219239 0 29.8
1 online 0 0 29.8166667
2 online 0 0 29.8166667

SID User Log IO/s Phy IO/s Minutes
---------- ------ ---------- ---------- ----------
3 online 0 0 29.8166667
4 online 0 0 29.8

13 rows selected.

1 SELECT hash_value, executions, buffer_gets, disk_reads, parse_calls
2 FROM V$SQLAREA
3 WHERE buffer_gets > 1000
4 OR disk_reads > 100
5* ORDER BY buffer_gets + 100*disk_reads DESC
SQL>
SQL> /

HASH_VALUE EXECUTIONS BUFFER_GETS DISK_READS PARSE_CALLS
---------- ---------- ----------- ---------- -----------
2626326413 1 572 384 1
690085868 1 2575 134 1
2963598673 349 1095 55 6
657604649 1 1172 18 1

great scenario for 2gbps insertion speed......

great scenario for 2gbps insertion speed......

please look at
http://forums.oracle.com/forums/thread.jspa?threadID=533820&tstart=-2

BT (British Telecom) ran a 82 node (or more) OPS cluster of Pyramid Nil

Recovery Writer process (RVWR) on 10g onwards

Recovery Writer process (RVWR)
The Recovery Writer process is responsible for writing to the flashback logs in the flash recovery area.

These logs are required to restore the database to a previous point in time by using the "flashback" option for Oracle databases (Oracle 10g and later).

U enable the flashback feature in your database and c its power

Log_buffer has no effect from 10g

I installed ORACLE SPOTLIGHT for RAC and found LOG_BUFFER was shown to occupy unusually large value, then I investigated and found fixed sga and log buffer are given a one granule size(4m) irrespective of log_buffer value.

SQL> select * from v$sgainfo;

NAME BYTES RES
-------------------------------- ---------- ---
Fixed SGA Size 1290952 No
Redo Buffers 2899968 No
Buffer Cache Size 243269632 Yes
Shared Pool Size 163577856 Yes
Large Pool Size 4194304 Yes
Java Pool Size 4194304 Yes
Streams Pool Size 0 Yes
Granule Size 4194304 No
Maximum SGA Size 419430400 No
Startup overhead in Shared Pool 37748736 No
Free SGA Memory Available 0

11 rows selected.

SQL> select (1290952+ 2899968) /1024 from dual;

(1290952+2899968)/1024
----------------------
4092.69531

Deciding Index Type..?

guy 1: a table has 10,00000 db blocks
guy 1: it has 9999999999 rows
guy 2: vempires r on the floor
guy 1: on column productid there are 23 distinct values
guy 1: what index would u prefer
guy 2: b+tree
guy 1: nope
guy 1: this column has very low cardinality
guy 2: k
guy 2: sir
guy 1: this column has very low cardinality
guy 1: so very low selectivity . if it was highly selctive only then b+tree
guy 2: yup
guy 1: column seems good candidate for for bmp index
guy 2: im recalling
guy 1: but if heavy dml then even bmp index is also avoided
guy 2: its good .commender
guy 1: because it locks segments (actallyu exents) of objects
guy 2: k
guy 1: so no index if heavy dml on above
guy 2: k

Query ...

Asked by a old DBA friend about waits caused by wait event "OS THREAD STARTUP" found in his 10g RAC(4 nodes on solaris with ASM)

SELECT DECODE (session_state, 'WAITING', event, NULL) event,
session_state, COUNT(*), SUM (time_waited) time_waited
FROM gv$active_session_history
WHERE sample_time > SYSDATE - 1
GROUP BY DECODE (session_state, 'WAITING', event, NULL),
session_state order by time_waited desc;

Node 1

enq: TX - row lock contention
WAITING
3517
1734721441

os thread startup
WAITING
743
681425655

log file sync
WAITING
3557
530889167

DFS lock handle
WAITING
1131
528346071

db file sequential read
WAITING
34162
502368757

PX Deq: Signal ACK
WAITING
3917
406436176

reliable message
WAITING
283
251896304

gc buffer busy
WAITING
580
175092687

db file parallel read
WAITING
2606
169615544

Streams AQ: qmn coordinator waiting for slave to start
WAITING
123
121679315

I'm concluding on base of my experiecne of parallel queries(on test
machine with single CPU!!!! so no benefit in my case no matter I had RAC)

I see there are PX events indicating your RAC database
instances has parallel query/dml executions but I
guess the os thread startup wait event has nothing to
do with rac environment. This event indicates the
waitupon starting os process(thread) to start query
slave process for execution of parallel query. if u
set init parameter parallel_min_server sufficently
high,you may slightly cut this overhead but its not
recomendded. But instead of following rules of thumb
you check trade-off if you have significant waits.

Making 10g RAC ready for worst

focussing on vulenrable part of RAC
RAC gives you high availablity and performance,but what if you dont have clustered disks and your voting disk or cluster registry fails. from 10g R2 these can be mirrored but you should regularly backup and voting/ocr disk.Also you should be heedfull to run integrity test.

manual mirriring of voting disk be

query for current voting disk :
C:\>crsctl query css votedisk
0. 0 P:\cdata\crs\votedsk

located 1 votedisk(s).

adding redundant voting disk:
C:\>crsctl add css votedisk Q:\cdata\crs\votedsk
Cluster is not in a ready state for online disk addition

C:\>crsctl add css votedisk Q:\cdata\crs\votedsk -force
Now formatting voting disk: Q:\cdata\crs\votedsk
successful addition of votedisk Q:\cdata\crs\votedsk.

C:\>crsctl query css votedisk
0. 0 P:\cdata\crs\votedsk
1. 0 Q:\cdata\crs\votedsk

located 2 votedisk(s).

C:\>crsctl add css votedisk O:\cdata\crs\votedsk
Cluster is not in a ready state for online disk addition

investigate ( is CRS running/stopped)

C:\>crsctl add css votedisk O:\cdata\crs\votedsk -force
Now formatting voting disk: O:\cdata\crs\votedsk
successful addition of votedisk O:\cdata\crs\votedsk.

running integrity test
C:\>cluvfy comp ocr -n all

Verifying OCR integrity

Checking OCR integrity...

Checking the absence of a non-clustered configuration...
All nodes free of non-clustered, local-only configurations.

Uniqueness check for OCR device passed.

Checking the version of OCR...
OCR of correct Version "2" exists.

Checking data integrity of OCR...
Data integrity check for OCR passed.

OCR integrity check passed.

Verification of OCR integrity was successful.

installing-9i10g-on-rhel-3

Prerequisite: RHEL 3 has been installed with all required packages.
Also check, if you need OS Patch. For install of 9i u need 3006854 patch.Not needed for oracle 10g

Install the 3006854 patch:
unzip p3006854_9204_LINUX.zip
cd 3006854
sh rhel3_pre_install.sh

1. system configuration change
Login from root user and add following entries to /etc/sysctl.conf file:

kernel.shmmax = 2147483648
kernel.shmmni = 4096
kernel.shmall = 2097152
kernel.sem = 250 32000 100 128
fs.file-max = 65536
net.ipv4.ip_local_port_range = 1024 65000

In addition the following lines can be added to the /etc/security/limits.conf file:
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384

2. install groups/user creation
Create the new groups and users:

groupadd oinstall
groupadd dba

useradd -g oinstall -G dba oracle

change password for oracle user
passwd oracle

3. install directory creation /setup
Create the directories in which the Oracle software will be installed and give ownership to install user

mkdir /u01
chown oracle.dba /u01
chmod 777 /u01

4. install user configuration
Login as the install user ,here oracle user and add the following lines at the end of the .bash_profile file:

# Oracle 9i /10g
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=$ORACLE_BASE/product/9.2.0.1.0
export PATH=$PATH:$ORACLE_HOME/bin
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export CLASSPATH=$ORACLE_HOME/JRE:$ORACLE_HOME/jlib:$ORACLE_HOME/rdbms/jlib:
export CLASSPATH=$CLASSPATH:$ORACLE_HOME/network/jlib;
export LD_ASSUME_KERNEL=2.4.1

5. run OUI
For 9i /mnt/cdrom/runInstallerv

10g does not support installation by cd so
For 10g ,copy the 10g software install cd to disk and begin installation .

10g-r2-rac-on-vmware

10g RAC installed on windows 2003 enterprize edition using vmware.

below links were major help to me.

http://www.oracle-base.com/articles/10g/OracleDB10gR2RACInstallationOnWindows2003UsingVMware.php

http://www.dbasupport.com/oracle/ora10g/RACingAhead0101.shtml

http://forums.oracle.com/forums/thread.jspa?messageID=1123638

http://www.idevelopment.info/data/Oracle/DBA_tips/Oracle10gRAC/CLUSTER_14.shtml#Overview

http://www.oracle.com/technology/obe/10gr2_db_vmware/manage/clusterintro/clusterintro.htm

some more useful links:

-- Listed in order as I found them and kept in store for U in random fashion --
http://www.vmware.com/community/thread.jspa?messageID=737640

Saturday, December 22, 2007

Handling Block Corruptions in Oracle7 / 8 / 8i / 9i / 10g

Contents
Introduction
Overview of Steps to handle a Corruption
Corruption due to NOLOGGING or UNRECOVERABLE
(1) Determine the Extent of the Corruption Problem
(2) Replace or Move Away from Suspect Hardware
(3) Which Objects are Affected ?
Options for various Segment Types:
CACHE
CLUSTER
INDEX PARTITION INDEX
LOBINDEX LOBSEGMENT
ROLLBACK
TABLE PARTITION TABLE
TEMPORARY IOT
TYPE2 UNDO
Other Segment Types
No Segment
(4) Choosing a Recovery Option
(4A) Complete Recovery
Block Level Recovery ,
Datafile Recovery ,
Database Recovery ,
After Complete Recovery
(4B) Recreating Indexes
(4C) Salvaging Data from Tables
Methods of extracting data from a corrupt table AROUND a corrupt block
Methods of extracting data from a table with a corrupt LOBSEGMENT block
Extracting data from the corrupt block itself
(4D) Leaving the Corruption in Place
Warnings when Leaving a Corruption in Place
(4E) Last Options
Document History

All SQL statements here are for use in SQL*Plus (in 8.1 or higher)
or Server Manager (Oracle7 / 8.0) when connected as a SYSDBA user.
(Eg: "connect / as sysdba" or "connect internal")

Introduction
This article discusses how to handle one or more block corruptions
on an Oracle datafile and describes the main actions to take to deal
with them. Please read the complete article before taking any action.

This note does not cover memory corruption issues (typically
ORA-600 [17xxx] type errors).

Note: If the problem is an ORA-1578 on STARTUP then please
contact your local support center for advice referencing
Note:106638.1 - this note is not visible to customers
but the relevant steps from it can be supplied by an experienced
support analyst.

You may be referred to this article from many places for many forms of
error - it is important that you have the following information for each
corrupt block:
An absolute FILE NUMBER of the file containing the corrupt block.
Referred to as "&AFN" in this article.

The file name of the file containing the corrupt block.
Referred to as "&FILENAME" in this article.
( If you know the FILE NUMBER but not its name then V$DATAFILE
can be used to get the file name:
SELECT name FROM v$datafile WHERE file#=&AFN;
If the file number does not appear in V$DATAFILE in Oracle8i
AND &AFN is greater than the DB_FILES parameter
value then it is probably a TEMPFILE. In this case the filename
can be found using:
SELECT name FROM v$tempfile
WHERE file#=(&AFN - &DB_FILES_value);
)
The BLOCK NUMBER of the corrupt block in that file.
Referred to as "&BL" in this article.
The tablespace number and name containing the affected block.
Referred to as "&TSN" (tablespace number) and
"&TABLESPACE_NAME" in this article.
If you do not know these then you can find them using:

SELECT ts# "TSN" FROM v$datafile WHERE file#=&AFN;

SELECT tablespace_name FROM dba_data_files WHERE file_id=&AFN;
The block size of the tablespace where the corruption lies.
Referred to as "&TS_BLOCK_SIZE" in this article.

For Oracle 9i+, run the following query to determine the appropriate
block size:

SELECT block_size FROM dba_tablespaces
WHERE tablespace_name =
(SELECT tablespace_name FROM dba_data_files WHERE file_id=&AFN);

For Oracle 7, 8.0 and 8.1:
Every tablespace in the database has the same block size.
For these versions, issue "SHOW PARAMETER DB_BLOCK_SIZE" and
use this value as your &TS_BLOCK_SIZE.

Eg: For the ORA-1578 error:
ORA-01578: ORACLE data block corrupted (file # 7, block # 12698)
ORA-01110: data file 22: '/oracle1/oradata/V816/oradata/V816/users01.dbf'
then:
&AFN is "22" (from the ORA-1110 portion of the error)
&RFN is "7" (from the "file #" in the ORA-1578)
&BL is "12698" (from the "block #" in the ORA-1578)
&FILENAME is '/oracle1/oradata/V816/oradata/V816/users01.dbf'
&TSN etc.. should be determined from the above SQL

For other errors (ORA-600 , ORA-1498 etc...) the above values should
either be given to you by Oracle Support, or be given to you from the
article which covers the relevant error.

Overview of Steps to handle a Corruption
There are many possible causes of a block corruption including:

- Bad IO hardware / firmware
- OS problems
- Oracle problems
- Recovering through "UNRECOVERABLE" or "NOLOGGING" database actions
(in which case ORA-1578 is expected behaviour - see below)

The point in time when an Oracle error is raised may be much later than
when any corruption initially occurred.

As the root cause is not usually known at the time the corruption is
encountered, and as in most cases the key requirement is to get up
and running again, then the steps used tackle corruption problems in
this article are:

1) Determine the extent of the corruption problems
and also determine if the problems are permanent or transient.

If the problem is widespread or the errors move about
then focus on identifying the cause first (check hardware
etc..). This is important as there is no point recovering
a system if the underlying hardware is faulty.

2) Replace or move away from any faulty or suspect hardware.

3) Determine which database objects are affected.

4) Choose the most appropriate database recovery / data salvage
option.

For all steps above it is sensible to collect evidence and
document exactly what actions are being taken. The 'Evidence>>'
tags in this article list the information which should be collected
to assist with identifying the root cause of the problem.

Corruption due to NOLOGGING or UNRECOVERABLE

If a NOLOGGING (or UNRECOVERABLE) operation is performed on an
object and the datafile containing that object is subsequently
recovered then the data blocks affected by the NOLOGGING operation
are marked as corrupt and will signal an ORA-1578 error when
accessed. In Oracle8i an ORA-26040 is also signalled
("ORA-26040: Data block was loaded using the NOLOGGING option" )
which makes the cause fairly obvious, but earlier releases have no
additional error message. If a block is corrupt due to recovery
through a NOLOGGING operation then you can use this article from
Section 3 "Which Objects are Affected ?" onwards but note that:

(a) Recovery cannot retrieve the NOLOGGING data

(b) No data is salvagable from inside the block

(1) Determine the Extent of the Corruption Problem
Whenever a corruption error occurs note down the FULL error message/s
and look in the instance's alert log and trace files for any associated
errors. It is important to do this first to assess whether this is
a single block corruption, an error due to an UNRECOVERABLE operation
or a more severe issue.

It is a good idea to scan affected files (and any important files)
with DBVERIFY to check for other corruptions in order to determine
the extent of the problem.
For details of using DBVERIFY see Note 35512.1

Once you have determined a list of corrupt file/block combinations
then the steps below can be used to help determine what action
can be taken.

Evidence>>
- Record the original error in full, along with details of
the application which encountered the error.
- Save an extract from the alert log from a few hours before
the FIRST recorded problem up to the current point in time.
- Save any tracefiles mentioned in the alert log.
- Record any recent OS problems you have encountered.
- Note if you are using any special features - Eg: ASYNC IO,
fast write disk options etc..
- Record your current BACKUP position (Dates, Type etc...)
- Note if your database is in ARCHIVELOG mode or not
Eg: Issue "ARCHIVE LOG LIST" in SQL*Plus (or Server Manager)

(2) Replace or Move Away from Suspect Hardware
The vast majority of corruption problems are caused by faulty hardware.
If there is a hardware fault or a suspect component then it is sensible
to either repair the problem, or make disk space available on a
separate disk sub-system prior to proceeding with a recovery option.

You can move datafiles about using the following steps:

1. Make sure the file to be relocated is either OFFLINE or
the instance is in the MOUNT state (not open)

2. Physically restore (or copy) the datafile to its new location
eg: /newlocation/myfile.dbf

3. Tell Oracle the new location of the file.
eg: ALTER DATABASE RENAME FILE '/oldlocation/myfile.dbf'
TO '/newlocation/myfile.dbf';
(Note that you cannot RENAME a TEMPFILE - TEMPFILEs should
be dropped and recreated at the new location)

4. Online the relevant file / tablespace (if database is open)

IMPORTANT: If there are multiple errors (which are NOT due to NOLOGGING)
OR You have OS level errors against the affected file
OR The errors are transient and keep moving about
then there is little point proceeding until the underlying problem
has been addressed or space is available on alternative disks.
Get your hardware vendor to check the system over and contact
Oracle Support with details of all errors.
Please note: Whilst a failed hardware check is a good indication
that there is a hardware issue, a successful hardware check should not
be taken as proof that there is no hardware related issue - it is very
common for hardware tests to report success when there really is some
underlying fault.

If using any special IO options such as direct IO , async IO or
similar it may be worth disabling them in order to eliminate such options
as a potential source of problems.

(3) Which Objects are Affected ?
It is best to determine which objects are affected BEFORE making any
decisions about how to recover - this is because the corruption/s may be
on object/s which can easily be re-created.
Eg: For a corruption on a 5 row lookup table it may be far quicker to
drop and recreate the table than to perform a recovery.

For each corruption collect the information in the following table.
The steps to do this are explained below.
Information to Record for each Corruption Original
Error Absolute
File#
&AFN Relative
File#
&RFN Block#

&BL Tablespace Segment
Type Segment
Owner.Name Related
Objects Recovery
Options

The notes below will help you fill in this table for each corruption.

"Original Error"
This is the error as initially reported.
Eg: ORA-1578 / ORA-1110 , ORA-600 with all arguments etc..

"Absolute File#", "Relative File#" and "Block#"
The File# and Block# should have been given to you either by the
error, by Oracle Support, or by the steps in an error article which
directed you to this article.

In Oracle8/8i/9i/10g:
The absolute and relative file numbers are often the
same but can differ (especially if the database has
been migrated from Oracle7). It is important to get
the correct numbers for &AFN and &RFN
or you may end up salvaging the wrong object !!

An ORA-1578 reports the RELATIVE file number, with the
ABSOLUTE file number given in the accompanying ORA-1110
error. For ORA-600 errors you should be told an absolute
file number.

The following query will show the absolute and relative
file numbers for datafiles in the database:

SELECT tablespace_name, file_id "AFN", relative_fno "RFN"
FROM dba_data_files;

In Oracle8i/9i/10g:
In addition to the notes above about Oracle8, Oracle8i onwards
can have TEMPFILES. The following query will show the
absolute and relative file numbers for tempfiles in the
database:

SELECT tablespace_name, file_id+value "AFN", relative_fno "RFN"
FROM dba_temp_files, v$parameter
WHERE name='db_files';

In Oracle7: Use the same file number for both the "Absolute File#"
and the "Relative File#"

"Segment Type", "Owner", "Name" and "Tablespace"
The following query will tell you the object TYPE , OWNER and NAME of
a segment given the absolute file number "&AFN" and block number "&BL" of the
corrupt block - the database must be open in order to use this query:

SELECT tablespace_name, segment_type, owner, segment_name
FROM dba_extents
WHERE file_id = &AFN
and &BL between block_id AND block_id + blocks - 1
;

If the block is in a TEMPFILE the above query will return no data.
For TEMPFILES the "Segment Type" will be "TEMPORARY".

"Related Objects" and Possible "Recovery Options" by SEGMENT_TYPE:
The related objects and recovery options which can be used depend on the
SEGMENT_TYPE. The additional queries and possible recovery options are
listed below for each of the most common segment types.

CACHE
CLUSTER
INDEX PARTITION INDEX
LOBINDEX LOBSEGMENT
ROLLBACK
TABLE PARTITION TABLE
TEMPORARY
TYPE2 UNDO
Some other Segment Type
"no rows" from the query

CACHE
- If the segment type is CACHE recheck you have entered the SQL
and parameters correctly.
If you get the same result contact Oracle support with all
information you have.

Options:
The database is likely to require recovery.

{Continue} {Back to Segment List}

CLUSTER
- If the segment is a CLUSTER determine which tables it contains.
Eg:
SELECT owner, table_name
FROM dba_tables
WHERE owner='&OWNER'
AND cluster_name='&SEGMENT_NAME'
;

Options:
If the OWNER is "SYS" then contact Oracle support with all details.
The database is likely to require recovery.

For non dictionary clusters possible options include:
Recovery
OR Salvage data from all tables in the cluster
THEN Recreate the cluster and all its tables

As the cluster may contain a number of tables, it is best to
collect information for each table in the cluster before making a
decision.

{Collect TABLE information} {Back to Segment List}

INDEX PARTITION
- If the segment is an INDEX PARTITION note the NAME and OWNER
and then determine which partition is affected thus:

SELECT partition_name
FROM dba_extents
WHERE file_id = &AFN
AND &BL BETWEEN block_id AND block_id + blocks - 1
;

then continue below as if the segment was an INDEX segment.

Options:
Index partitions can be rebuilt using:
ALTER INDEX xxx REBUILD PARTITION ppp;
(take care with the REBUILD option as described in
"Recreating Indexes" below)

INDEX
- If the segment is an INDEX then if the OWNER is "SYS" contact
Oracle support with all details.

For a non-dictionary INDEX or INDEX PARTITIONs find out which table
the INDEX is on:
Eg:
SELECT table_owner, table_name
FROM dba_indexes
WHERE owner='&OWNER'
AND index_name='&SEGMENT_NAME'
;

and determine if the index supports a CONSTRAINT:

Eg: SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints
WHERE owner='&TABLE_OWNER'
AND constraint_name='&INDEX_NAME'
;

Possible values for CONSTRAINT_TYPE are:

P The index supports a primary key constraint.
U The index supports a unique constraint.

If the INDEX supports a PRIMARY KEY constraint (type "P") then
check if the primary key is referenced by any foreign key constraints:
Eg:
SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints
WHERE r_owner='&TABLE_OWNER'
AND r_constraint_name='&INDEX_NAME'
;

Options:
If the OWNER is "SYS" then contact Oracle support with all details.
The database is likely to require recovery.

For non dictionary indexes possible options include:
Recovery
OR Recreate the index (with any associated constraint
disables/enables)
(take care with the REBUILD option as described in
"Recreating Indexes" below)

{Continue} {Back to Segment List}

ROLLBACK
- If the segment is a ROLLBACK segment contact Oracle support as
rollback segment corruptions require special handling.

Options:
The database is likely to require recovery.
{Continue} {Back to Segment List}

TYPE2 UNDO
- TYPE2 UNDO is a system managed undo segment which is a special
form of rollback segment. Corruptions in these segments require
special handling.

Options:
The database is likely to require recovery.
{Continue} {Back to Segment List}

TABLE PARTITION
- If the segment is a TABLE PARTITION note the NAME and OWNER
and then determine which partition is affected thus:

SELECT partition_name
FROM dba_extents
WHERE file_id = &AFN
AND &BL BETWEEN block_id AND block_id + blocks - 1
;

then continue below as if the segment was a TABLE segment.

Options:
If all corruptions are in the same partition then one option
at this point is to EXCHANGE the corrupt partition with an
empty TABLE - this can allow the application to continue (without
access to the data in the corrupt partition) whilst any good
data can then be extracted from the table.

For other options see the TABLE options below.

TABLE
- If the OWNER is "SYS" then contact Oracle support with all details.
The database is likely to require recovery.

For a non-dictionary TABLE or TABLE PARTITIONs find out which
INDEXES exist on the TABLE:
Eg:
SELECT owner, index_name, index_type
FROM dba_indexes
WHERE table_owner='&OWNER'
AND table_name='&SEGMENT_NAME'
;

and determine if there is any PRIMARY key on the table:

Eg: SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints
WHERE owner='&OWNER'
AND table_name='&SEGMENT_NAME'
AND constraint_type='P'
;

If there is a primary key then check if this is referenced by any
foreign key constraints:
Eg:
SELECT owner, constraint_name, constraint_type, table_name
FROM dba_constraints
WHERE r_owner='&OWNER'
AND r_constraint_name='&CONSTRAINT_NAME'
;

Options:
If the OWNER is "SYS" then contact Oracle support with all details.
The database is likely to require recovery.

For non dictionary tables possible options include:
Recovery
OR Salvage data from the table (or partition)
THEN Recreate the table (or partition)
OR Leave the corruption in place
(eg: Use DBMS_REPAIR to mark the problem blocks to be skipped)

{Continue} {Back to Segment List}

IOT (Index Organized Table)
The corruption in IOT table should be handled in the same way as in a heap or partitioned table.
The only exception is if the PK is corrupted.
PK of an IOT table is the table itself and can't be dropped and recreated.

Options:
If the OWNER is "SYS" then contact Oracle support with all details.
The database is likely to require recovery.

For non dictionary tables possible options include:
Recovery
OR Salvage data from the table (or partition)
THEN Recreate the table (or partition)
OR Leave the corruption in place
(eg: Use DBMS_REPAIR to mark the problem blocks to be skipped)

{Continue} {Back to Segment List}

LOBINDEX
- Find out which table the LOB belongs to:

SELECT table_name, column_name
FROM dba_lobs
WHERE owner='&OWNER'
AND index_name='&SEGMENT_NAME';

- If the table is owned by "SYS" then contact Oracle support with all
details. The database is likely to require recovery.

- It is not possible to rebuild LOB indexes and so you have to
treat the problem as a corruption on the LOB column of the
affected table.

Get index and constraint information for the table which has
the corrupt LOB index using the SQL in the TABLE
section, then return here.

Options:
If the OWNER is "SYS" then contact Oracle support with all details.
The database is likely to require recovery.

For non dictionary tables possible options include:
Recovery
OR Salvage data from the table (and its LOB column/s)
THEN Recreate the table
It is not generally sensible just to leave the corruption in
place unless the table is unlikely to have any further DML on
the problem column.

{Continue} {Back to Segment List}

LOBSEGMENT

- Find out which table the LOB belongs to:
Eg:
SELECT table_name, column_name
FROM dba_lobs
WHERE owner='&OWNER'
AND segment_name='&SEGMENT_NAME';

- If the table is owned by "SYS" then contact Oracle support with all
details. The database is likely to require recovery.

- For non-dictionary tables ...

Get index and constraint information for the table which has
the corrupt LOB data using the SQL in the TABLE
section, then return here to find details of the exact rows
affected.

Finding the exact row which references the corrupt LOB block
can be a challenge as the errors reported do not show any
detail about which table row owns the lob entry which is corrupt.

Typically one can refer to application logs or any SQL_TRACE
or 10046 trace of a session hitting the error (if available) or
see if having event "1578 trace name errorstack level 3"
set in the session helps identify the current SQL/binds/row.
eg:
ALTER SYSTEM SET EVENTS '1578 trace name errorstack level 3';

Then wait for the error to be hit by the application
and find the trace file.

If there are no clues then you can construct a PLSQL block
to scan the problem table row by row extracting the LOB
column data which loops until it hits an error. Such a technique
may take a while but it should be possible to get a primary key
or rowid of any row which references a corrupt LOB block.

eg:
set serverout on
exec dbms_output.enable(100000);
declare
error_1578 exception;
pragma exception_init(error_1578,-1578);
n number;
cnt number:=0;
badcnt number:=0;
begin
for cursor_lob in
(select rowid r, &LOB_COLUMN_NAME L from &OWNER..&TABLE_NAME)
loop
begin
n:=dbms_lob.instr(cursor_lob.L,hextoraw('AA25889911'),1,999999) ;
exception
when error_1578 then
dbms_output.put_line('Got ORA-1578 reading LOB at '||cursor_lob.R);
badcnt:=badcnt+1;
end;
cnt:=cnt+1;
end loop;
dbms_output.put_line('Scanned '||cnt||' rows - saw '||badcnt||' errors');
end;
/

It is possible to have a corrupt LOB block which is only
present as an old version (for consistent read) and which has
not yet been re-used in which case all table rows will be
accessible but it may not be possible to insert / update
the LOB columns once that block is reclaimed for reuse.

Options:
If the OWNER is "SYS" then contact Oracle support with all details.
The database is likely to require recovery.

For non dictionary tables possible options include:
Recovery
OR Salvage data from the table (and its LOB column/s)
THEN Recreate the table
OR Leave the corruption in place
(It is not possible to use DBMS_REPAIR on LOB segments)

{Continue} {Back to Segment List}

TEMPORARY
- If the segment type is TEMPORARY then the corruption does not
affect a permanent object. Check if the tablespace where the
problem occurred is being used as a TEMPORARY tablespace thus:

SELECT count(*) FROM dba_users
WHERE temporary_tablespace='&TABLESPACE_NAME'
;

Options:
If this is a TEMPORARY_TABLESPACE then it may be possible
to create a NEW temporary tablespace and switch all users
to that tablespace then DROP the problem tablespace.

If this is not a temporary tablespace then the block should
not be read again and should get re-formatted next time the
block is used - the error should not repeat PROVIDED any
underlying cause has been cured.

No restore is normally required, although if the disk is
suspect and the tablespace contains useful data then a
database recovery of the affected file/s may be wise.

{Continue} {Back to Segment List}

Some other SEGMENT_TYPE
- If the segment type returned is not covered above then contact
Oracle support for advice with all information collected so far.

{Continue} {Back to Segment List}

"no rows returned"
- If there appears to be no extent containing the corrupt block
then first double check the figures used in the query. If you
are sure the file and block are correct and do not appear as
belonging to an object in DBA_EXTENTS then:

- Double check if the file involved is a TEMPFILE.
Note that TEMPFILE file numbers depend on the init.ora
parameter DB_FILES so any changes to this parameter
change the absolute file number reported in errors.

- DBA_EXTENTS does not include blocks which are used
for local space management in locally managed tablespaces.

- If the database you are now querying is from a different
point in time to the datafile with the error then the
problem object may have been dropped and so queries against
DBA_EXTENTS may show no rows.

- If the error you are investigating was reported by DBVERIFY
then DBV checks all blocks regardless of whether they
belong to an object or not. This it is possible for a
corrupt block to exist in the datafile but in a block
not in use by any object.

Options:
An error on an UNUSED Oracle block can be ignored as Oracle will
create a new block image should the block need to be used so any
existing problem on the block will never get read.

If you suspect that the block may be a space management
block then you can use DBMS_SPACE_ADMIN to help check
this by running:

exec DBMS_SPACE_ADMIN.TABLESPACE_VERIFY('&TABLESPACE_NAME');

This should write inconsistencies to the trace file but
if it encounters a fatally corrupt block it will report an
error like:
ORA-03216: Tablespace/Segment Verification cannot proceed

An error on a bitmap space management block can often be corrected
by running:

exec DBMS_SPACE_ADMIN.TABLESPACE_REBUILD_BITMAPS('&TABLESPACE_NAME');

{Continue} {Back to Segment List}

Evidence>>
- For each corrupt block it is also a good idea to collect
the following physical evidence if there is a need to try
and identify the actual cause of the corruption:

i) An operating system HEX dump of the bad block and the block
either side of it.
On UNIX:
dd if=&FILENAME bs=&TS_BLOCK_SIZE skip=&BL-1 count=3 of=BL.dd
^^^^^^^^ ^^^^^^^^^^^^^^ ^^^
Eg: For BL=1224:
dd if=ts11.dbf bs=4k skip=1223 count=3 of=1223_1225.dd

On VMS:
DUMP/BLOCKS=(start:XXXX,end:YYYY)/out=dump.out &FILENAME

Where XXXX=Operating system block number (in 512 byte chunks)
To calculate this multiply the block number reported by
'&TS_BLOCK_SIZE/512'.

ii) If you are in ARCHIVELOG mode make a safe copy of the archived
log files around the time of the error, and preferably for a few
hours before the error was reported. Also secure any backup/s
of the problem datafile from before the errors as the before
image PLUS redo can help point towards a cause.
(DBV can often be used to check if the problem exists in a
backup copy of a file). The ideal scenario is to have a
datafile backup image which does not have any corruption
and all the redo from that point in time up to and just past
the time when the corruption is first reported.

iii) Obtain an Oracle dump of the problem block/s:

ALTER SYSTEM DUMP DATAFILE '&FILENAME'
BLOCK &BL
;

(The output will go to a tracefile in the USER_DUMP_DEST).

{Continue} {Back to Segment List}

(4) Choosing a Recovery Option
The best recovery option now depends on the objects affected. The notes
in Section (3) above should have highlighted the main options available
for each affected object. The actual recovery method chosen may include
a mix or one or more methods thus:

Is any Recovery Required ?
If the error is in a TEMPORARY tablespace, or is in a block
which is no longer part of any database object then no action
is required, although it may be wise to relocate the problem
tablespace to a different storage device.
See Warnings.

Is Complete Recovery an option ?

In order for complete recovery to be an option the following
must be true:
- The database is in ARCHIVELOG mode
(The "ARCHIVE LOG LIST" command shows Archivelog Mode)

- You have a good backup of affected files. Note that in some cases,
the corruption may have been present, but undetected, for a long
period of time. If the most recent datafile backup still contains
the corruption, you can try an earlier backup as long as you have all
the necessary ARCHIVELOGS.

(You can often use the DBV START= / END= options to check
if specific block/s in a restored copy of a backup
file are corrupt)

- All ARCHIVELOGS are available from the time of the backup
to the current point in time

- The current online log/s are available and intact

- The errors are NOT due to recovery through a NOLOGGING operation

When the above criteria are satisfied then complete recovery
is usually the preferred option
*BUT NOTE*
(a) If the rollback of a transaction has seen a corrupt block on an
object other than the rollback segment itself then UNDO may
have been discarded. In this case you may need to rebuild
indexes / check data integrity AFTER the recovery completes.
(b) If the files to be recovered contain data from NOLOGGING
operations performed since the last backup then those blocks
will be marked corrupt if datafile or database recovery is
used. In some cases this can put you in a worse scenario than
the current position.

If database recovery has already been performed and the corruption
is still there then either all of your backups contain the corruption,
the underlying fault is still present or the problem is replaying
through redo. In these cases you will need to choose some other
recovery option.

See "(4A) Complete Recovery" for complete recovery steps.

Can the object be Dropped or Re-created without needing
to extract any data from the object itself ?
It may be possible to lose the object, or to recreate it from
a script / recent export. Once an object is dropped then blocks
in that object are marked as "free" and will be re-formatted when
the block gets allocated to a new object. It is advisable to RENAME
rather than DROP a table unless you are absolutely sure that you
do not need any data in it.

In the case of a table partition then only the affected partition
needs to be dropped. eg: ALTER TABLE ... DROP PARTITION ...
If the corruption affects the partition segment header, or the
file containing the partition header is offline, then DROP
PARTITION may fail. In this case it may still be possible to
drop the partition by first exchanging it with a table of the
same definition.
eg: ALTER TABLE .. EXCHANGE PARTITION .. WITH TABLE ..;

The most common object which can be re-created is an index.
Always address TABLE corruptions before INDEX problems on a table.
See "(4B) Recreating Indexes" for more details.

Is it required to salvage data before recreating the object ?
If the problem is on a critical application table which is
regularly updated then it may be required to salvage as much
data from the table as possible, then recreate the table.

See "(4C) Salvaging Data from Tables" for more details.

Is it acceptable to leave the corruption in place for the moment?
In some cases the best immediate option may be to leave the
corruption in place and isolate it from application access.

See "(4D) Leaving the Corruption In Place" for more details.

Last Options
Are any of the following possible ?
Recovery to an old point-in-time (via point in time recovery)
of either the database or tablespace point in time recovery
OR Restore of a COLD backup from before the corruption
OR Use of an existing export file
See "(4E) Last Options" for more details.

(4A) Complete Recovery
If the database is in ARCHIVELOG mode and you have a good backup
of the affected files then recovery is usually the preferred option.
This is not GUARANTEED to clear a problem, but is effective for the
majority of corruption issues. If recovery re-introduces the problem
then return to the list of options above and choose another method.

If you are using Oracle9i (or higher) then it may be possible
to perform block level recovery using the RMAN BLOCKRECOVER command.
If using an earlier Oracle release then you can either perform datafile
recovery (which can be done while the rest of the database is still up
and running), or database recovery (which requires the database to be
taken down) .

Block Level Recovery
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
As of Oracle9i RMAN allows individual blocks to be recovered whilst the
rest of the database (including other blocks in the datafile) are available
for normal access. Note that block level recovery can only be used
to recover a block fully to the current point in time.

It is not necessary to be using RMAN for backups to be able to use this
option for recovery of individual blocks.
eg:
Consider that you have an ORA-1578 on file #6 block #30 which is likely
due to a media corruption problem and there is a good cold backup
image of that file which has been restored to '.../RESTORE/filename.dbf'.
Provided all archivelogs exist (in the default location) then you can use
RMAN to perform a block level recovery using a command sequence like:

rman nocatalog
connect target
catalog datafilecopy '.../RESTORE/filename.dbf';
run {blockrecover datafile 6 block 30;}

This will use the registered datafile backup image and any required
archivelogs to perform block recovery of just the one problem block
to current point in time.

Please see the documentation for full details of the RMAN BLOCKRECOVER
command and limitations.

Datafile Recovery
~~~~~~~~~~~~~~~~~~
Datafile recovery of a file involves the following steps. If there are
several files repeat the steps for each file or see "Database Recovery"
below. These steps can be used if the database is either OPEN or MOUNTED.

OFFLINE the affected data file
eg: ALTER DATABASE DATAFILE 'name_of_file' OFFLINE;

Copy it to a safe location (in case the backup is bad)

Restore the latest backup of the file onto a GOOD disk

Check the restored file for obvious corruptions with DBVERIFY
For details of using DBVERIFY see Note 35512.1

Assuming the restored file is OK, then RENAME the datafile to the
NEW location (if different from the old location)
eg: ALTER DATABASE RENAME FILE 'old_name' TO 'new_name';

Recover the datafile
eg: RECOVER DATAFILE 'name_of_file';

Online the file/s
eg: ALTER DATABASE DATAFILE 'name_of_file' ONLINE;

{Continue}

Database Recovery
~~~~~~~~~~~~~~~~~
Database recovery generally involves the following steps:

Shutdown (Immediate or Abort)

Copy the current copy of all files to be recovered to a safe location

Restore the backup files to a GOOD disk location
DO NOT RESTORE THE CONTROL FILES or ONLINE REDO LOG FILES

Check restored files with DBVERIFY
For details of using DBVERIFY see Note 35512.1

Startup MOUNT

Rename any relocated files
eg: ALTER DATABASE RENAME FILE 'old_name' TO 'new_name';

Ensure all required files are online
eg: ALTER DATABASE DATAFILE 'name_of_file' ONLINE;

Recover the database
eg: RECOVER DATABASE

Open the database
eg: ALTER DATABASE OPEN;

After a Complete Recovery
~~~~~~~~~~~~~~~~~~~~~~~~~~
Once a complete recovery has been performed it is advisable to check the
database before allowing it to be used:

- Run "ANALYZE VALIDATE STRUCTURE CASCADE"
against each problem object to check for table/index mis-matches.
If there has been any UNDO discarded this may show a mismatch
requiring indexes to be re-created.

- Check the logical integrity of data in the table at the application level.

(4B) Recreating Indexes
If the corrupt object is a user INDEX you can simply drop and
re-create it PROVIDED the underlying table is not also corrupt.
If the underlying table is also corrupt it is advisable to sort out
the TABLE before recreating any indexes.

If the information collected shows that the index has dependent FOREIGN
KEY constraints then you will need to do something like this:

- ALTER TABLE DISABLE CONSTRAINT ;
for each foreign key

- Rebuild the primary key using
ALTER TABLE DISABLE CONSTRAINT ;
DROP INDEX ;
CREATE INDEX .. with appropriate storage clause
ALTER TABLE

ENABLE CONSTRAINT ;

- Enable the foreign key constraints
ALTER TABLE ENABLE CONSTRAINT ;

For an index partition you can:
ALTER INDEX ... REBUILD PARTITION ...;

Notes:
(1) It is important not to REBUILD a non-partitioned corrupt index
using an "ALTER INDEX .. REBUILD" command as this will usually
try to build the new index from the existing index segment,
which contains a corrupt block.
"ALTER INDEX ... REBUILD ONLINE" and "ALTER INDEX ... REBUILD
PARTITION ..." do not build the new index from the old index
segment and so can be used.

(2) Create INDEX can use the data from an existing index
if the new index is a sub-set of the columns in the
existing index. Hence if you have 2 corrupt indexes drop
them BOTH before re-creating them.

(3) Be sure to use the correct storage details when recreating indexes.

(4C) Salvaging Data from Tables
If the corrupt object is a TABLE or CLUSTER or LOBSEGMENT then it
must be understood that the data within the corrupt block is lost.
Some of the data may be salvageable from a HEX dump of the block, or
from columns covered by indexes.

Important:
As it may be required to salvage data in the corrupt
block from the indexes it is a good idea NOT to drop
any existing index until any required data has been
extracted.

There are many ways to get data out of a table which contains a
corrupt block. Choose the most appropriate method as detailed
below. The aim of these methods is to extract as much data as
possible from the table blocks which can be accessed. It is
usually a good idea to RENAME the corrupt table so that the new
object can be created with the correct name.
Eg: RENAME TO ;

Methods of extracting data from a corrupt table AROUND a corrupt block

(1) From Oracle 7.2 onwards, including Oracle 8.0, 8.1, and 9i, it is
possible to SKIP over corrupt blocks in a table.
This is by far the simplest option to extract table data and is
discussed in:

Extracting data using DBMS_REPAIR.SKIP_CORRUPT_BLOCKS or
Event 10231 Note 33405.1

Note that this method can only be used if the block "wrapper" is
marked corrupt. Eg: If the block reports ORA-1578.

If the problem is an ORA-600 or other error which does not report
and ORA-1578 error then it is often possible to use DBMS_REPAIR
to mark the problem blocks in a table as "soft corrupt" such
that they will then signal ORA-1578 when accessed which then
allows you to use DBMS_REPAIR.SKIP_CORRUPT_BLOCKS.

Note: Any blocks which are marked corrupt by the "FIX_CORRUPT_BLOCKS"
procedure will also be marked corrupt following any restore /
recover operation through the time of the FIX_CORRUPT_BLOCKS.

Full details of using DBMS_REPAIR for this can be found in the
documentation but in summary the steps are:

- Use DBMS_REPAIR.ADMIN_TABLES to create the admin tables
- Use DBMS_REPAIR.CHECK_OBJECT to find problem blocks
- Get any good data out of problem blocks before corrupting them.
- Use DBMS_REPAIR.FIX_CORRUPT_BLOCKS to mark the found problem
blocks as corrupt so that they will then signal ORA-1578
- If required use DBMS_REPAIR.SKIP_CORRUPT_BLOCKS to skip corrupt
blocks on the table.

(2) From Oracle 7.1 onwards you can use a ROWID range scan.
The syntax for this is a little tricky but it is possible
to select around a corrupt block using a ROWID hint.
As the format of ROWIDs changed between Oracle7 and Oracle8
there are 2 articles which discuss this:

Using ROWID Range Scans to extract data in Oracle8 and higher Note 61685.1
Using ROWID Range Scans to extract data in Oracle7 Note 34371.1

(3) If there is a primary key you can select table data via this index.
It may also be possible to select some of data via any other index.
This can be slow and time consuming and is only normally needed
for Oracle 7.0 releases. This method is described in Note 34371.1
(which also describes the ROWID range scans)

(4) There are various salvage programs / PLSQL scripts which can be
used to salvage data from a table. These can take longer to set
up and use than the above methods but can often cope with various
kinds of corruption besides an ORA-1578.
As these methods typically require much hand-holding from
support then some of these articles may not be visible to customers.

These require Pro*C to be available and an understanding of how
to build Pro*C executables:
SALVAGE.PC for Oracle7 Note 2077307.6

These requires manual interaction:
SALVAGE.SQL for Oracle7/8 Note 2064553.4

Methods of extracting data from a table with a corrupt LOBSEGMENT block

It is not possible to used DBMS_REPAIR on LOB segments.

If the corrupt LOB block is NOT referenced by any row in the table
then it should be possible to CREATE TABLE as SELECT (CTAS) or
export / drop / import the table as is.

If the corrupt LOB block is referenced by a row then it should be
possible to select or export with a WHERE predicate that excludes
the problem row/s.

WARNING:
It is possible to update the LOB column value
of a problem row to NULL which will then clear
ORA-1578 on SELECT operations *BUT* the corrupt
block will then be waiting to be reclaimed and will
eventually signal an ORA-1578 on attempts to get a
new LOB for INSERT or UPDATE operations on any row
which can be a worse situation than having a corruption
on a known row.
Hence you should only really set the LOB column to NULL
if you intend to immediately recreate the table.

Extracting data from the corrupt block itself

As the corrupt block itself is "corrupt" then any data extracted
from the block should be treated as suspect. The main methods
of getting the rows from the corrupt block itself are:

- For TABLE blocks Oracle Support can use a tool which attempts to
interpret the block contents.
- Use any existing indexes on the table to extract data for
columns covered by the index where the ROWID falls inside the
corrupt block. This is described towards the end of the ROWID
range scan articles mentioned above:
For Oracle8/8i see Note 61685.1
For Oracle7 see Note 34371.1

- It may be possible to use LogMiner on the redo stream
to find the original inserts/updates which loaded the
data to the problem block. The main factor here is
WHEN the data was actually put in the block.
eg; row 2 may have been inserted yesterday but row 1 may
have been inserted 5 years ago.

(4D) Leaving A Corruption In Place
It is possible to leave a corruption in place and just accept the
errors reported, or prevent access to the problem rows at an application
level.
eg: If the problem block / row is in a child table then it may be possible
at application level to prevent access via the parent row/s such that
the child rows are never accessed. (Be wary of cascade type constraints
though)

This may not help with reports and other jobs which access data in
bulk so it may also be desirable to use the DBMS_REPAIR options
shown in 4C above to prevent the block/s erroring when
accessed. Marking a corruption like this and leaving it around may
give a short term solution allowing full data salvage and/or recovery
to be attempted at scheduled outage, or allowing time to check other
recovery options on a second (clone) database. Note though that
marking a block corrupt with DBMS_REPAIR.FIX_CORRUPT_BLOCKS will cause
the marked block/s to also be corrupt after recovery through the
time that FIX_CORRUPT_BLOCKS was executed.

Leaving a corruption may be sensible for data which rapidly ages and is
subsequently purged (eg: In a date partitioned table where older
partitions are dropped at some point).

Leaving Corruptions in LOB segments

At application level it can be possible to leave a corrupt
LOB column in place until such time as the table can be rebuilt.

One way to ensure you do not hit the "WARNING" scenario above
is to ensure that the table is only ever accessed via a view
which includes a WHERE predicate to prevent the problem row/s
from being seen.
eg: Consider table MYTAB( a number primary key, b clob ) has
one or more rows pointing at corrupt LOB data.

ALTER TABLE MYTAB ADD ( BAD VARCHAR2(1) );

CREATE VIEW MYVIEW AS SELECT a,b FROM MYTAB WHERE BAD is null;

Set BAD='Y' for any problem row/s

If you only access MYTAB via MYVIEW and the row will never be visible
and so cannot be updated keeping the corrupt entry isolated until it
can be dealt with.

Clearly this example is more of a design time solution but some
applications may already have similar mechanisms and may only access data
via a view (or via an RLS policy) giving some option/s to hide the problem
row/s.

Warnings when Leaving a Corruption in Place
Whilst it is possible to leave a corruption in place it should be
noted that the corrupt blocks will still show up in runs of DBVERIFY,
in RMAN backup warnings / errors etc..

It is important to make a careful record of any corruption you
expect to see from these tools, particularly any blocks you expect
to skip with RMAN (eg: having MAX_CORRUPT set) and be sure to remove
any "acceptance" of the errors once the corruptions have been cleared.

eg: Consider that a corrupt block has been handled by leaving the corruption
in place and avoiding the problem row/s at application level.
RMAN may be configured to allow the corruptions during backup.
The table is then recreated at a later date during some table
reorganisation.
If RMAN is not updated to reflect that no errors should now be
expected then RMAN may ignore some other corruption which occurs
at a later time.

It is also important to note that leaving corrupt blocks around in
table segments can lead to mismatched results from queries
eg: different results can occur for tables with SKIP_CORRUPT set
depending on whether an index scan or table access occurs.
Other reports may just error .

Note that leaving a corruption in place but marking the block with
DBMS_REPAIR.FIX_CORRUPT_BLOCKS writes redo to corrupt the block
which may limit subsequent recovery options.

(4E) Last Options
If you have a standby setup (physical or logical) then check that first.

Whatever sort of block the problem occurred on, one possible option
is to recover the database, or problem tablespace, to a point in time
BEFORE the corruption appeared. The difficulty with this option is that
it is not always possible to know when the problem first appeared.

DBVERIFY can be often be used to check a restored file for corruptions.
For details of using DBVERIFY see Note 35512.1 . In particular the
START= / END= DBV options can be used to give a quick first test of whether
the problem block itself is bad on a restored backup image.

This section outlines some final options available for recovering.
If you have come here then one or more of the following have happened:

- You have lost a "vital" datafile (or have a corruption on it)
and have no good backup of the problem file/s (without the corruption)
- Are either not in ARCHIVELOG mode OR do not have all archivelogs
since the file was first created
- Complete recovery keeps reintroducing the problem

Last chance:
Please note if you have lost all copies of a datafile but DO still have
the ARCHIVE logs from when the file was first created it is still possible
to recover the file.
Eg:
ALTER DATABASE CREATE DATAFILE '....' [as '...'] ;
RECOVER DATAFILE '....'
ALTER DATABASE DATAFILE '....' ONLINE;

If you are in this scenario try to recover the datafile using these
steps before proceeding below.

If you have reached this line there are no options left to recover to
the current point in time. It is advisable to shutdown the instance and
take a BACKUP of the current database NOW in order to provide a fall-back
position if the chosen course of action fails. (Eg: if you find your backup
is bad).

Some outline options available are:

Revert to an old COLD backup
- eg: If in NOARCHIVELOG mode

Set up a clone database from a COLD backup
- and extract (export) the problem table/s
or transport the problem tablespace

Point in time recovery to an older point in time that is consistent
- requires a good backup and any necessary archive logs
- ALL files have to be restored and the whole DB rolled forward
to a suitable point in time.
- It may be possible to do the point in time recovery in a
clone database and then transport the problem tablespace
to the problem database, or export / import the problem table
from the clone to the problem database .

Tablespace point in time recovery
- It may be possible to perform a point in time recovery
of the affected tablespace only. There are many notes
describing tablespace point in time recovery such as
Note 223543.1.

Rebuild of DB from some logical export / copy
- Requires there to already be a good logical backup of the database
- NB: You have to RE-CREATE the database for this option.
- As with other options the rebuild could be in a clone database
just to get a good image of the problem table/s.

If you have a good backup then rolling forwards with DB_BLOCK_CHECKING=TRUE
can help find the first point in time where something started to go wrong.
It is not generally necessary to take the problem database down while
investigating the recovery options.
eg: You can restore the system tablespace and problem tablespace
datafiles only to a totally different location and/or machine
as a different instance to investigate how far you can roll forwards etc..
As of Oracle9i you can also use "Trial Recovery" options to save
having to keep restoring a backup while looking into your options.