blockrecover and dbms_repair

During backup and recovery practice sessions, we often struggle to perform block recovery scenario.
This is because we find it difficult to corrupt an Oracle block.
I performed this test on Oracle 10g Release 2 (10.2.0.4) on Linux (Unix).

In this article, I will discuss how to corrupt an Oracle data block, but before beginning this discussion, I would like to answer: Why to corrupt an Oracle Block?
We will be corrupting an Oracle block in order to practice recovery procedures involved when one encounters a Block Corruption in a production environment.
If a block gets corrupted in any of our production databases we will be in a position to rectify and correct the error instead of wandering for help.

This is purely for educational purpose and please do not practice this on any of your production/development/testing databases, rather create a new database for this purpose
and practice it there. For the purpose of this test, I have created a separate tablespace and a new schema.

SQL> create tablespace corrupt_it_tbs
datafile '/home/oracle/oradata/MYDB1/corrupt_it_.dbf' size 20m;

Tablespace created.

SQL> create user corr identified by corr default tablespace corrupt_it_tbs;

User created.

SQL> grant connect, resource to corr;

Grant succeeded.

Create and populate test table with dummy data as shown:

SQL> connect corr/corr
Connected.
SQL>
SQL> create table tab_all_objects
as
select rownum rowno, object_name
from all_objects;
Table created.
SQL> select count(*) from tab_all_objects;
COUNT(*)
----------
2893

Insert a record into this table which we will be corrupting:

SQL> insert into tab_all_objects values (8888, 'CORRUPT THIS');

1 row created.

SQL> commit;

Commit complete.

Let us take RMAN full database backup before we corrupt the block.

RMAN> backup format '/home/oracle/admin/MYDB1/backup/fulldb_%U' database plus archivelog;
Starting backup at 01-FEB-08 current log archived : :
piece handle=C:\MYDB\RMAN\FULLDB_0LJ7LIML_1_1 tag=TAG20080201T234641 comment=NONE channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:28
Finished backup at 02-FEB-08 Starting backup at 02-FEB-08 current log archived using channel ORA_DISK_1 channel
ORA_DISK_1: starting archive log backupset channel
ORA_DISK_1: specifying archive log(s) in backup set input archive log thread=1 sequence=105 recid=105 stamp=645581560 channel
ORA_DISK_1: starting piece 1 at 02-FEB-08 channel
ORA_DISK_1: finished piece 1 at 02-FEB-08 piece handle=C:\MYDB\RMAN\FULLDB_0MJ7LINS_1_1 tag=TAG20080202T001242 comment=NONE channel
ORA_DISK_1: backup set complete, elapsed time: 00:00:04 Finished backup at 02-FEB-08
RMAN>

Take the tablespace offline so that we can make changes to the datafile. There are many freeware and shareware Hex Editors available in the market.
I am using UltraEdit editor to make changes in our datafile.

SQL> alter tablespace corrupt_ts offline;

Tablespace altered.

Open datafile “'c:\mydb\data\corrupt01.dbf” using UltraEdit (press “Ctrl+h” to toggle between Hex Mode).

Search for our record entry “LET ME CORRUPT” in the file and changed “CORRUPT” to “NORRUPT” and save the file and close UltraEdit. I just changed “C” to “N”. Bring back the tablespace to online mode.

SQL> alter tablespace corrupt_ts online;

Tablespace altered.

You may notice that Oracle doesn’t complain when it brings the datafile online because the file header wasn’t modified. Oracle will complain only when it tries to access the corrupt blocks.
Let’s see what happens when we try to query table “T1”.

SQL> conn test/test Connected.

SQL> select * from t1;
RNO OBJECT_NAME
---------- ------------------------------
1 AQ$_AGENT
2 AQ$_DEQUEUE_HISTORY
:
:
30 AQ$_JMS_NAMEARRAY
ERROR:
ORA-01578: ORACLE data block corrupted (file # 6, block # 13)
ORA-01110: data file 6: 'C:\MYDB\DATA\CORRUPT01.DBF'
30 rows selected.

Query returns 30 records and then complains of block corruption in file 6. Block numbered 13 is being reported as corrupt. Let us see what all blocks are corrupt in “corruption01.dbf” datafile by running dbv utility.

C:\ora10g\BIN>dbv file=C:\MYDB\data\corrupt01.dbf blocksize=8192
DBVERIFY: Release 10.2.0.1.0 - Production on Mon Feb 4 00:00:11 2008 Copyright (c) 1982, 2005, Oracle. All rights reserved.
DBVERIFY - Verification starting : FILE = C:\MYDB\data\corrupt01.dbf Page 13 is marked corrupt Corrupt block relative dba: 0x0180000d (file 6, block 13) Bad check value found during dbv: Data in bad block: type: 6 format: 2 rdba: 0x0180000d last change scn: 0x0000.0039aa9f seq: 0x3 flg: 0x06 spare1: 0x0 spare2: 0x0 spare3: 0x0 consistency value in tail: 0xaa9f0603 check value in block header: 0x85b0 computed block checksum: 0x1b00
DBVERIFY - Verification complete
Total Pages Examined : 1280 Total Pages Processed (Data) : 4
Total Pages Failing (Data) : 0
Total Pages Processed (Index): 0
Total Pages Failing (Index): 0
Total Pages Processed (Other): 11
Total Pages Processed (Seg) : 0
Total Pages Failing (Seg) : 0
Total Pages Empty : 1264
Total Pages Marked Corrupt : 1
Total Pages Influx : 0
Highest block SCN : 3779231 (0.3779231)

C:\ora10g\BIN> This utility scans all the blocks in a given datafile and outputs the corrupt ones. In my case, I have one block marked as corrupt. Make a note of all the corrupt blocks as we need to recover them to previous state. Start RMAN session and recover all the corrupt blocks. The beauty of RMAN is that it leaves the entire datafile online except the corrupted blocks and we need to recover only those corrupt blocks instead of entire datafile.
RMAN> blockrecover datafile 6 block 13;
Starting blockrecover at 04-FEB-08 using target database control file instead of recovery catalog allocated channel:
ORA_DISK_1 channel ORA_DISK_1: sid=44 devtype=DISK
channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set restoring blocks of datafile 00006
channel ORA_DISK_1: reading from backup piece C:\MYDB\RMAN\FULLDB_0KJ7LH72_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1 piece handle=C:\MYDB\RMAN\FULLDB_0KJ7LH72_1_1 tag=TAG20080201T234641
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:36
starting media recovery archive log thread 1 sequence 105 is already on disk as file C:\MYDB\FRA\MYDB\ARCHIVELOG\2008_02_02\O1_MF_1_10 5_3T72T48S_.ARC
archive log thread 1 sequence 106 is already on disk as file C:\MYDB\FRA\MYDB\ARCHIVELOG\2008_02_03\O1_MF_1_10 6_3TD97K0Z_.ARC
media recovery complete, elapsed time: 00:00:35
Finished blockrecover at 04-FEB-08
RMAN>

RMAN reports success of block recovery command. Let us query the table again by logging in to SQL*Plus:

SQL> select * from t1;
RNO OBJECT_NAME
---------- ------------------------------
1 AQ$_AGENT
2 AQ$_DEQUEUE_HISTORY
:
:
41 AQ$_JMS_ARRAY_ERROR_INFO
42 AQ$_JMS_ARRAY_ERRORS
99 LET ME CORRUPT

43 rows selected.
SQL>
Wow, the query runs successfully and our original record is restored. Similar article on block recovery in UNIX environment can be found here. Happy recovery (block)!


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


declare
corr_count int;
BEGIN
dbms_repair.CHECK_OBJECT(
schema_name => 'CORR',
OBJECT_NAME => 'TAB_ALL_OBJECTS',
REPAIR_TABLE_NAME => 'REPAIR_TABLE',
CORRUPT_COUNT => corr_count);
dbms_output.put_line('Deteched '|| corr_count || ' block(s).');
END;
/

DECLARE
num_fix INT;
BEGIN
DBMS_REPAIR.FIX_CORRUPT_BLOCKS (
SCHEMA_NAME => 'CORR',
OBJECT_NAME => 'TAB_ALL_OBJECTS',
object_type => dbms_repair.table_object,
repair_table_name => 'REPAIR_TABLE',
fix_count => num_fix);
dbms_output.put_line('num fix: '|| to_char(num_fix));
end;
/

BEGIN
DBMS_REPAIR.SKIP_CORRUPT_BLOCKS (
SCHEMA_NAME => 'CORR',
OBJECT_NAME => 'TAB_ALL_OBJECTS',
OBJECT_TYPE => dbms_repair.table_object,
FLAGS => dbms_repair.skip_flag);
END;
/

SESSION_CACHED_CURSORS - how to figure out if it can be increased to see performance gains

select 'session_cached_cursors' parameter,
lpad(value, 5) value,
decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage
from ( select max(s.value) used
from v$statname n,
v$sesstat s
where n.name = 'session cursor cache count'
and s.statistic# = n.statistic#
),
( select value
from v$parameter
where name = 'session_cached_cursors'
)
union all
select 'open_cursors', lpad(value, 5),
to_char(100 * used / value, '990') || '%'
from ( select max(sum(s.value)) used
from v$statname n,
v$sesstat s
where n.name in ( 'opened cursors current',
'session cursor cache count')
and s.statistic# = n.statistic#
group by s.sid
),
( select value
from v$parameter
where name = 'open_cursors');

PARAMETER VALUE USAGE
---------------------- ----- -----
session_cached_cursors 30 100%
open_cursors   65535 0%

ALTER SYSTEM SESSION_CACHED_CURSORS= requires a bounce. 

Alternatively, a database level trigger can be created like below to automatically set this value.

create or replace trigger ssc_trig after logon on database
begin
    execute immediate 'alter session set session_cached_cursors = 100';
end;
/

Generating an AWR report

#!/bin/bash
# Usage: daily_awr.sh
#
#

# Check format of command line
if [ "$#" != 1 ]
then
echo "Usage: daily_awr.sh "
exit 1
fi

# set environment
. ~/.profile > /dev/null
. ~/dba/lib/include > /dev/null

ORACLE_SID=$1
export ORACLE_SID

oracle_running_locally_check

MAIL_LIST=

sqlplus -s '/ as sysdba' <
column begin_snap new_value begin_snap
column end_snap new_value end_snap
column report_name new_value report_name
column full_report_name new_value full_report_name

column instance_number new_value instance_number
column dbid new_value dbid

column output format a80

select dbid from v\$database;
select instance_number from v\$instance;

select min(snap_id) as begin_snap,
max(snap_id) as end_snap
from dba_hist_snapshot
where dbid = &dbid
and instance_number = &instance_number
and snap_level >= 1 -- 5
and begin_interval_time between (trunc(sysdate) + 3.9/24) and (trunc(sysdate) + 16.2/24);

select '$LOG_DIR/sp_' || to_char(sysdate, 'MMDDYY') || '_4am-4pm.html' as report_name
from dual;

select '&&report_name' as full_report_name
from dual;

set feedback off termout off
spool &full_report_name
set verify off pages 0

select output
from table
(
DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML
(
&dbid,
&instance_number,
&begin_snap,
&end_snap
)
)
/

spool off

! uuencode &&full_report_name &&full_report_name | $MAILX -s "${ORACLE_SID} DB Statspack Report 4am to 4pm" $MAIL_LIST

quit
EOF

11g Silent Install (Software only)

This script can be use to generate a response file.

echo "oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=dba
INVENTORY_LOCATION=/apps/oracle/$ORASID/product/oraInventory
SELECTED_LANGUAGES=en
ORACLE_HOME=/apps/oracle/$ORASID/product/db11gR2
ORACLE_BASE=/apps/oracle/$ORASID/product
oracle.install.db.InstallEdition=EE
oracle.install.db.isCustomInstall=false
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=dba
oracle.install.db.config.starterdb.type=GENERAL_PURPOSE
oracle.install.db.config.starterdb.memoryOption=false
oracle.install.db.config.starterdb.installExampleSchemas=false
oracle.install.db.config.starterdb.enableSecuritySettings=true
#oracle.install.db.config.starterdb.control=DB_CONTROL
oracle.install.db.config.starterdb.dbcontrol.enableEmailNotification=false
oracle.install.db.config.starterdb.automatedBackup.enable=false
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true" > /tmp/silent_install_software_only.rsp


Run this command from the unzipped tar of the downloaded Oracle binaries.

./runInstaller -silent -responseFile /tmp/silent_install_software_only.rsp -ignorePrereq

Oracle Auditing for failure login attempts

So let's enable auditing by changing this init.ora parameter and bouncing the database.


SQL> alter system set audit_trail=db scope=spfile
SQL> /

System altered.

SQL> startup force
ORACLE instance started.

Total System Global Area 1436884992 bytes
Fixed Size 2148072 bytes
Variable Size 788535576 bytes
Database Buffers 637534208 bytes
Redo Buffers 8667136 bytes
Database mounted.
show parameter audit
Database opened.
SQL>


SQL> audit session whenever not successful ;

Audit succeeded.

SQL> connect blah/blah
ERROR:
ORA-01017: invalid username/password; logon denied


Warning: You are no longer connected to ORACLE.
SQL> connect /as sysdba

Connected.
SQL> SQL>

SQL> col os_username format a15
SQL> col userhost format a15
SQL> col userhost format a15
SQL> col timestamp format a25
SQL> set pages 120 lines 120
SQL> col logoff_dlock format a15
SQL> select os_username,
2 username,
3 userhost,
4 to_char(timestamp,'mm/dd/yyyy hh24:mi:ss') timestamp,
5 returncode
6 from dba_audit_session
7 where action_name = 'LOGON'
8 and returncode > 0
9 order by timestamp ;

OS_USERNAME USERNAME USERHOST TIMESTAMP RETURNCODE
--------------- ------------------------------ --------------- ------------------------- ----------
oracle BLAH fcqaodbs01 05/03/2010 11:12:31 1017


Simple isn't it!
We can do a number of different fine-grained auditing in 11g (better than 10g). Keep an eye out for more information on this blog!

Two New and (v) Important defaults with 11g

In Oracle 11g password is case sensitive and the default login failure attempts are set to 10 at the database level.

In Oracle 10g and before we all know that passwords are not case sensitive, so PASSWORD, Password, password would let you in and are all the same.

If you upgrade to Oracle 11g (I know lot of you are waiting for 11gR2), you will find that passwords are case sensitive. Here is an example of case sensitive passwords.

$>sqlplus user/user@mydb1
SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 28 11:04:00 2010

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL>


Lets try to connect with a upper case password now...

$>sqlplus user/user@mydb1
SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 28 11:04:00 2010

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

ERROR:
ORA-01017: invalid username/password; logon denied
Enter user-name:

So what does this mean to apps running with 91 or 10g, that get to run against a 11g database have to make sure that the password set in it's configuration files is using the correct case.

You can also revert to 9i/10g behavior by changing the database-level parameter sec_case_sensitive_logon parameter to FALSE (its TRUE by default)

alter system set sec_case_sensitive_logon=FALSE;

Also, if you are using DEFAULT profile, it will inherit max login attempts to 10 which is the DEFAULT for 11g databases.

You can set it to an acceptable number by the following:

alter system set sec_max_failed_login_attempts=20 scope=spfile;

You need a database bounce for the above..

DDL Logging

ENABLE_DDL_LOGGING
With 11g you can now log ddl into your alert.log (which I thought was cool)


SQL> alter system set enable_ddl_logging=true;
SQL>
SQL> create table x (y number, z timestamp);

following is in trace/alert_MYDB1.log
..
..
Sat Apr 11 12:20:48 2010
create table x (y number, z timestamp)
..
..