OPatch Utility Guide - 10.2

I have just learned about OPatch Utility Guide - 10.2 (Note:554417.1).

From the note:

NApply
It is used to apply a set of patches under a directory

opatch napply patch_location -id 1,2,3 -skip_subset -skip_duplicate

This will apply patches 1, 2, and 3 which are under the patch_location directory. OPatch will skip duplicate patches and subset patches (patches under patch_location that are subsets of patches installed in the ORACLE_HOME)
 
You must be using
OPatch version : 10.2.0.4.2

ADDM report from SQL*Plus

I have generated ADDM report from SQL*Plus using: @?/rdbms/admin/addmrpt.sql

Here is a recommendation from the report.

RECOMMENDATION 2: SQL Tuning, 4.8% benefit (54 seconds)
  ACTION: Run SQL Tuning Advisor on the SQL statement with SQL_ID "bunssq950snhf".
  RELEVANT OBJECT: SQL statement with SQL_ID bunssq950snhf and
  PLAN_HASH 2694099131
         insert into wrh$_sga_target_advice   (snap_id, dbid, instance_number,
         SGA_SIZE, SGA_SIZE_FACTOR, ESTD_DB_TIME, ESTD_PHYSICAL_READS)  select
         :snap_id, :dbid, :instance_number,    SGA_SIZE, SGA_SIZE_FACTOR,
         ESTD_DB_TIME, ESTD_PHYSICAL_READS  from    v$sga_target_advice
  RATIONALE: SQL statement with SQL_ID "bunssq950snhf" was executed 35
         times and had an average elapsed time of 1.5 seconds.

How do I get the plan for the SQL as seen in Enterprise Manager?
This can be done by passing the SQL_ID to dbms_xplan.display_cursor

 

Awareness Test

http://www.youtube.com/watch?v=Ahg6qcgoay4

 Did you get the right answer?

Block Corruption and blockrecover

Please do not try this unless you can afford to lose the database.

Create table in USERS tablespace.

Connected to:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production      

With the Partitioning, OLAP and Data Mining options  

SQL> create table t
  2  tablespace users as
  3  select * from dba_objects;      

Table created.

Determine block to corrupt.

SQL> select header_block
  2  from dba_segments
  3  where segment_name='T';  

HEADER_BLOCK
------------
         139

Backup database.

JAZZ:/home/oracle/scripts$ rman target /      

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Mar 2 13:07:30 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.  

connected to target database: JAZZ (DBID=1044608836)      

RMAN> backup database;      

Starting backup at 02-MAR-2008 13:07:35
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=33 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00001 name=/ora_data01/JAZZ/system01.dbf
input datafile fno=00002 name=/ora_data02/JAZZ/undotbs01.dbf
input datafile fno=00003 name=/ora_data01/JAZZ/sysaux01.dbf
input datafile fno=00004 name=/ora_data04/JAZZ/users01.dbf
channel ORA_DISK_1: starting piece 1 at 02-MAR-2008 13:07:37
channel ORA_DISK_1: finished piece 1 at 02-MAR-2008 13:08:02
piece handle=/oracle/10gr2/db_1/dbs/01ja8nop_1_1 tag=TAG20080302T130736 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
including current control file in backupset
including current SPFILE in backupset   channel ORA_DISK_1: starting piece 1 at 02-MAR-2008 13:08:04
channel ORA_DISK_1: finished piece 1 at 02-MAR-2008 13:08:05
piece handle=/oracle/10gr2/db_1/dbs/02ja8npi_1_1 tag=TAG20080302T130736 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
Finished backup at 02-MAR-2008 13:08:05  

Corrupt the block.

JAZZ:/home/oracle/scripts$ dd if=/dev/zero of=/ora_data04/JAZZ/users01.dbf bs=8192 conv=notrunc seek=140 count=1
1+0 records in
1+0 records out
8192 bytes (8.2 kB) copied, 9.7241e-05 seconds, 84.2 MB/s

RMAN tablespace backup reveals block corruption.

JAZZ:/home/oracle/scripts$ rman target /      

Recovery Manager: Release 10.2.0.1.0 - Production on Sun Mar 2 13:08:54 2008
Copyright (c) 1982, 2005, Oracle.  All rights reserved.  

connected to target database: JAZZ (DBID=1044608836)      

RMAN> backup tablespace users;      

Starting backup at 02-MAR-2008 13:09:03
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=34 devtype=DISK
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/ora_data04/JAZZ/users01.dbf
channel ORA_DISK_1: starting piece 1 at 02-MAR-2008 13:09:05
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 03/02/2008 13:09:06
ORA-19566: exceeded limit of 0 corrupt blocks for file /ora_data04/JAZZ/users01.dbf

Corruption found in alert log but not v$backup_corruption.

JAZZ:/home/oracle/scripts$ tail -10 /ora_admin/JAZZ/bdump/alert_JAZZ.log  

Reread of blocknum=140, file=/ora_data04/JAZZ/users01.dbf. found same corrupt data
Reread of blocknum=140, file=/ora_data04/JAZZ/users01.dbf. found same corrupt data
Reread of blocknum=140, file=/ora_data04/JAZZ/users01.dbf. found same corrupt data
Reread of blocknum=140, file=/ora_data04/JAZZ/users01.dbf. found same corrupt data
Reread of blocknum=140, file=/ora_data04/JAZZ/users01.dbf. found same corrupt data
Sun Mar  2 13:09:46 2008
Shutting down archive processes
Sun Mar  2 13:09:51 2008
ARCH shutting down
ARC2: Archival stopped      

SQL> set serverout on
SQL> exec print_table('select * from v$backup_corruption');
PL/SQL procedure successfully completed.

But what if RMAN is not used for backup?

It is still possible to check for block corruption by using

backup validate check logical tablespace users;

SQL> set serverout on
SQL> exec print_table('select * from v$backup_corruption');
.RECID                        : 1
.STAMP                        : 648306720
.SET_STAMP                    : 648306720
.SET_COUNT                    : 4
.PIECE#                       : 1
.FILE#                        : 4
.BLOCK#                       : 140
.BLOCKS                       : 1
.CORRUPTION_CHANGE#           : 0
.MARKED_CORRUPT               : YES
.CORRUPTION_TYPE              : ALL ZERO
-----------------     

PL/SQL procedure successfully completed.

Perform blockrecover and backup tablespace.

RMAN> blockrecover datafile 4 block 140;    

Starting blockrecover at 02-MAR-2008 14:26:03
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=35 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 00004
channel ORA_DISK_1: reading from backup piece /oracle/10gr2/db_1/dbs/05ja8o45_1_1
channel ORA_DISK_1: restored block(s) from backup piece 1
piece handle=/oracle/10gr2/db_1/dbs/05ja8o45_1_1 tag=TAG20080302T131341
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:01    

starting media recovery
media recovery complete, elapsed time: 00:00:03    

Finished blockrecover at 02-MAR-2008 14:26:08    

RMAN> backup tablespace users;    

Starting backup at 02-MAR-2008 14:26:15
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backupset
channel ORA_DISK_1: specifying datafile(s) in backupset
input datafile fno=00004 name=/ora_data04/JAZZ/users01.dbf
channel ORA_DISK_1: starting piece 1 at 02-MAR-2008 14:26:15
channel ORA_DISK_1: finished piece 1 at 02-MAR-2008 14:26:16
piece handle=/oracle/10gr2/db_1/dbs/0aja8sc7_1_1 tag=TAG20080302T142615 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 02-MAR-2008 14:26:16

DEFAULT_PERMANENT_TABLESPACE

Prior to 10g, a created users would have SYSTEM tablespace as default. 

In 10g, the database can have a specified default tablespace for created users.

The information is stored in DATABASE_PROPERTIES view.

SQL> select PROPERTY_VALUE
  2  from DATABASE_PROPERTIES
  3  where PROPERTY_NAME=’DEFAULT_PERMANENT_TABLESPACE’;  

PROPERTY_VALUE
——————————————————————————–
USERS

10gR2 New Features in Backup and Recovery

Something to remember - Temporary Datafiles Are Re-Created on RMAN Recovery

10gR2 New Features in Backup and Recovery

drop database

Note: I had to modify the screen shot to remove domain name of the company.

The drop database command can be useful in a testing environment for cleaning up data files, controlfiles, and log files.

Note: database must be mounted EXCLUSIVE and not open for this operation.

One disappointment is that it did not delete the directory beneath DIAGNOSTIC_DEST.

Here are all the directories still left behind.

This is what I executed to remove the remaining directories in my environment.

find /ora* -user oracle -type d -name PRIME -exec rm -rfv {} \;

ALWAYS be careful when using rm -rf command.

Oracle 11gR1 Listener Log Location

I am just getting my feet wet with 11gR1.

When I executed lsnrctl status, I get the following:

<click image to enlarge>

The Listener Log File is in XML format.

Where’s the regular listener.log file?

It is located one directory above under trace.

<click image to enlarge>

This all relates to the new parameter DIAGNOSTIC_DEST in 11g.

REFERENCE:

Oracle® Database Reference 11g Release 1 (11.1)

Oracle 11gR1 install on CentOS 5 Notes

Note:438765.1

Requirements for Installing Oracle 11gR1 32bit RDBMS on RHEL 5 & OEL 5

Surprisingly straight forward using the above notes.

useradd -u 503 oracle

passwd oracle

groupadd -g 521 dba

groupadd -g 522 oinstall

usermod -g oinstall -G dba oracle

rpm -qa compat-libstdc++-33 \
elfutils-libelf-devel \
glibc-devel \
gcc \
gcc-c++ \
libaio-devel \
libstdc++-devel \
sysstat \
unixODBC \
unixODBC-devel |sort

rpm -qa binutils \
elfutils-libelf \
glibc \
glibc-common \
libaio \
libgcc \
libstdc++ \
make |sort

rpm -q –whatprovides

I have taken on a small role in Linux system administration.

I normally use xclock to verify X configuration. Wait, there is no xclock on the server; however it is installed on another server.

Here is what I did.

 [root@hawk ~]# which xclock
 /usr/X11R6/bin/xclock
 [root@hawk ~]# rpm -q –whatprovides /usr/X11R6/bin/xclock
 xorg-x11-tools-6.8.2-1.EL.18
 [root@hawk ~]#