HEMESH ORAWORLD: 2010

Monday 20 December 2010

Thursday 16 December 2010

Script to move SYS.AUD$ table out of SYSTEM tablespace

In 10g Database

Script to move SYS.AUD$ table out of SYSTEM tablespace

In 11g Database

http://aprakash.wordpress.com/2012/01/19/moving-aud-table-to-another-tablespace-in-11gr2/

Thursday 2 December 2010

ORA-01762: vopdrv: view query block not in FROM

01762. 00000 - "vopdrv: view query block not in FROM"

*Cause:

*Action:

Error at Line: 29 Column: 18

Answer:: Relace join with simple muti-join table query , see below e.g.

WITH

NewsT AS ( SELECT P.related_id ID

FROM Profiling P
JOIN Account A

ON A.ID = P.account_id

WHERE P.profiling_type = 1

AND P.related_type = 3

AND A.ID = 6 )

Select * FROM NewsT;

Replace Join:

WITH

NewsT AS ( SELECT P.related_id ID

FROM Profiling P , Account A

Where A.ID = P.account_id

AND P.profiling_type = 1

AND P.related_type = 3

AND A.ID = 6 )

Select * FROM NewsT;

Great, it works !

DataPump

Typical Export & Import of Few Tables and Sequences

Export(without sequence)11.2.par

DIRECTORY=DATA_PUMP_DIR

DUMPFILE=IIS_ORACLE_VODAFONE_UAT4_02122010.DMP

LOGFILE=exp_4_BSQUAT_VODAFONE_UAT4_02122010.log

TABLES=IIS_ORACLE_VODAFONE_UAT4.RESOURCECATEGORY,IIS_ORACLE_VODAFONE_UAT4.RESOURCEITEM,IIS_ORACLE_VODAFONE_UAT4.RESOURCENEWSJOIN,IIS_ORACLE_VODAFONE_UAT4.RESOURCETAGJOIN,IIS_ORACLE_VODAFONE_UAT4.NEWS

QUERY=IIS_ORACLE_VODAFONE_UAT4.NEWS:"WHERE ID > 207"

VERSION=11.1

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

Export: Release 11.2.0.1.0 - Production on Thu Dec 2 11:38:38 2010

;;;

Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

Starting "SYSTEM"."SYS_EXPORT_TABLE_01": system/******** parfile=exp_oracle_VODAFONE_UAT2_4_BSQUAT.par

Estimate in progress using BLOCKS method...

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 1.437 MB

Processing object type TABLE_EXPORT/TABLE/TABLE

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/TRIGGER

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "IIS_ORACLE_VODAFONE_UAT4"."NEWS" 149.3 KB 21 rows

. . exported "IIS_ORACLE_VODAFONE_UAT4"."RESOURCEITEM" 101.8 KB 66 rows

. . exported "IIS_ORACLE_VODAFONE_UAT4"."RESOURCECATEGORY" 8.273 KB 15 rows

. . exported "IIS_ORACLE_VODAFONE_UAT4"."RESOURCENEWSJOIN" 7.218 KB 91 rows

. . exported "IIS_ORACLE_VODAFONE_UAT4"."RESOURCETAGJOIN" 7.640 KB 123 rows

Master table "SYSTEM"."SYS_EXPORT_TABLE_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_TABLE_01 is:

/u02/backup/dpdump/bsquat/IIS_ORACLE_VODAFONE_UAT4_02122010.DMP

Job "SYSTEM"."SYS_EXPORT_TABLE_01" successfully completed at 11:38:44

Export(with sequence)11.2.par

DIRECTORY=DATA_PUMP_DIR

DUMPFILE=IIS_ORACLE_VODAFONE_UAT4_02122010_with_SEQNC.DMP

LOGFILE=exp_4_BSQUAT_VODAFONE_UAT4_02122010_with_sequnse.log

SCHEMAS=IIS_ORACLE_VODAFONE_UAT4

INCLUDE=TABLE:"IN('RESOURCECATEGORY','RESOURCEITEM','RESOURCENEWSJOIN','RESOURCETAGJOIN','NEWS')"

QUERY=IIS_ORACLE_VODAFONE_UAT4.NEWS:"WHERE ID > 207"

INCLUDE=SEQUENCE:\"like'RESOU%'\"

VERSION=11.1

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

Export: Release 11.2.0.1.0 - Production on Thu Dec 2 12:25:04 2010

Connected to: Oracle Database 11g Release 11.2.0.1.0 - 64bit Production

With the Real Application Clusters and Automatic Storage Management options

Starting "SYSTEM"."SYS_EXPORT_SCHEMA_01": system/******** parfile=exp_test.par

Estimate in progress using BLOCKS method...

Processing object type SCHEMA_EXPORT/TABLE/TABLE_DATA

Total estimation using BLOCKS method: 1.437 MB

Processing object type SCHEMA_EXPORT/SEQUENCE/SEQUENCE

Processing object type SCHEMA_EXPORT/TABLE/TABLE

Processing object type SCHEMA_EXPORT/TABLE/INDEX/INDEX

Processing object type SCHEMA_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type SCHEMA_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type SCHEMA_EXPORT/TABLE/TRIGGER

Processing object type SCHEMA_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

. . exported "IIS_ORACLE_VODAFONE_UAT4"."NEWS" 149.3 KB 21 rows

. . exported "IIS_ORACLE_VODAFONE_UAT4"."RESOURCEITEM" 101.8 KB 66 rows

. . exported "IIS_ORACLE_VODAFONE_UAT4"."RESOURCECATEGORY" 8.273 KB 15 rows

. . exported "IIS_ORACLE_VODAFONE_UAT4"."RESOURCENEWSJOIN" 7.218 KB 91 rows

. . exported "IIS_ORACLE_VODAFONE_UAT4"."RESOURCETAGJOIN" 7.640 KB 123 rows

Master table "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully loaded/unloaded

******************************************************************************

Dump file set for SYSTEM.SYS_EXPORT_SCHEMA_01 is:

/u02/backup/dpdump/bsquat/IIS_ORACLE_VODAFONE_UAT4_02122010_with_SEQNC.DMP

Job "SYSTEM"."SYS_EXPORT_SCHEMA_01" successfully completed at 12:25:16

Import 11.1.par

DUMPFILE = IIS_ORACLE_VODAFONE_UAT4_02122010.DMP

LOGFILE = IMP_2_LIVE_IIS_ORACLE_VODAFONE_UAT4_02122010.log

DIRECTORY = DATA_PUMP_DIR

REMAP_SCHEMA = IIS_ORACLE_VODAFONE_UAT4:IIS_ORACLE_VODAFONE_LIVE2

TABLE_EXISTS_ACTION=APPEND

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

Import: Release 11.1.0.6.0 - Production on Thursday, 02 December, 2010 11:48:08

Connected to: Oracle Database 11g Release 11.1.0.6.0 - Production

Master table "SYSTEM"."SYS_IMPORT_FULL_07" successfully loaded/unloaded

Starting "SYSTEM"."SYS_IMPORT_FULL_07": system/********@xxxxxx parfile=imp_oracle_VODAFONE_UAT4_2_Live.par

Processing object type TABLE_EXPORT/TABLE/TABLE

ORA-39152: Table "IIS_ORACLE_VODAFONE_LIVE2"."NEWS" exists. Data will be appended to existing table but all dependent meta

data will be skipped due to table_exists_action of append

Processing object type TABLE_EXPORT/TABLE/TABLE_DATA

. . imported "IIS_ORACLE_VODAFONE_LIVE2"."NEWS" 149.3 KB 21 rows

. . imported "IIS_ORACLE_VODAFONE_LIVE2"."RESOURCEITEM" 101.8 KB 66 rows

. . imported "IIS_ORACLE_VODAFONE_LIVE2"."RESOURCECATEGORY" 8.273 KB 15 rows

. . imported "IIS_ORACLE_VODAFONE_LIVE2"."RESOURCENEWSJOIN" 7.218 KB 91 rows

. . imported "IIS_ORACLE_VODAFONE_LIVE2"."RESOURCETAGJOIN" 7.640 KB 123 rows

Processing object type TABLE_EXPORT/TABLE/INDEX/INDEX

Processing object type TABLE_EXPORT/TABLE/CONSTRAINT/CONSTRAINT

Processing object type TABLE_EXPORT/TABLE/INDEX/STATISTICS/INDEX_STATISTICS

Processing object type TABLE_EXPORT/TABLE/TRIGGER

ORA-39082: Object type TRIGGER:"IIS_ORACLE_VODAFONE_LIVE2"."RESOURCETAGJOIN_ID_TRG" created with compilation warnings

ORA-39082: Object type TRIGGER:"IIS_ORACLE_VODAFONE_LIVE2"."RESOURCENEWSJOIN_ID_TRG" created with compilation warnings

ORA-39082: Object type TRIGGER:"IIS_ORACLE_VODAFONE_LIVE2"."RESOURCE_ID_TRG" created with compilation warnings

ORA-39082: Object type TRIGGER:"IIS_ORACLE_VODAFONE_LIVE2"."RESOURCECATEGORY_ID_TRG" created with compilation warnings

Processing object type TABLE_EXPORT/TABLE/STATISTICS/TABLE_STATISTICS

Job "SYSTEM"."SYS_IMPORT_FULL_07" completed with 9 error(s) at 11:48:20

Datapump Export & Import some sequence :

we want to export and import tables, functions and sequences, that they have "REWARD%" name.

we created parfile for export and import(when table exist)

parfile for export =>
DIRECTORY=INVENTORY2
dumpfile=REWARD.dmp
logfile=REWARD2.log
include=TABLE:\"like'REWARD%'\"
include=FUNCTION:\"like'REWARD%'\"
include=SEQUENCE:\"like'REWARD%'\"

parfile for import =>
DIRECTORY=INVENTORY2
dumpfile=REWARD.dmp
logfile=REWARD2.log
include=TABLE:\"like'REWARD%'\"
include=FUNCTION:\"like'REWARD%'\"
include=SEQUENCE:\"like'REWARD%'\"
TABLE_EXISTS_ACTION=replace

#expdp parfile=parexport

#impdp parfile=parimport

Friday 26 November 2010

ORA-29855 error in oracle and solution & Oracle Text

We sometime see the error when insufficient privileges are assigned and CTXSYS is not presented in oracle db 10g.

ORA-29855: error occurred in the execution of ODCIINDEXCREATE routine
ORA-20000: Oracle Text error:
DRG-10700: preference does not exist: CTXSYS.DEFAULT_LEXER
ORA-06512: at "CTXSYS.DRUE", line 160
ORA-06512: at "CTXSYS.TEXTINDEXMETHODS", line 364

This errors comes when lack of privileges/roles when we work with text components (formerly known as Context, or Intermedia Text), this concept provides a powerful search, retrieval, and viewing capabilities for text stored in oracle database.

The solution is as follows:
AS sys DBA:
GRANT EXECUTE ON CTX_DDL TO username ; -- for oracle text package execution i.e. index rebuild and synchronize etc. Ignore if CTX_DDL not going to be used.
grant CTXAPP to username ;

Solution:
-----------
Ensure that CTXSYS schema is installed on database instance.

Install:
@? points oracle home directory i.e. ORACLE_HOME variable, ensure ORACLE_HOME and ORACLE_SID is set (window) or with export in UNIX.
The steps to be executed as SYS user
create tablespace drsys as sysdba
run @?/ctx/admin/catctx.sql ctxsys drsys temp01 nolock
run @?/ctx/admin/defaults/drdefus.sql (as CTXSYS user )-- see below for why and how
grant execute on ctxsys.ctx_ddl to user;

Uninstall:
@?/ctx/admin/catnoctx.sql as sys

The next step is to install appropriate language-specific default preferences. There is script which creates language-specific default preferences for every language Oracle text supports in ORACLE_HOME/ctx/admin/defaults directory and script should be executed as CTXSYS user.
@?/ctx/admin/defaults/drdefXX.sql -where XX is the language code

- wish this helps you too..

Wednesday 24 November 2010

ORA-23421: job number <nnn> is not a job in the job queue

You can only change your own jobs by using DBMS_JOB package. Not even SYS is exception to this, meaning that if you connect to the database as SYSDBA you'll be only able to change jobs belonging to SYS, but not those belonging to SCOTT, for example.

Howevere there is undocumented package called DBMS_IJOB that enables you to manipulate other users jobs (provided that you have EXECUTE ANY PROCEDURE privilege). For example, you can remove any job (not only those owned by you) from the job queue by executing procedure SYS.DBMS_IJOB.REMOVE(:job_id)

I'm not sure if DBMS_IJOB has all the procedures that DBMS_JOB has (WHAT, NEXT_DATE, INTERVAL, BROKEN, ...) and if all the parameters are the same, but you can find this out by yourself.

logon as sysdba and use:

SQL>select * from dba_jobs;

To drop perticular job:

SQL>exec SYS.DBMS_IJOB.remove(210);

To drop all user’s job:

SQL>exec SYS.DBMS_IJOB.DROP_USER_JOBS('myuser');

Cheers!

Job Removed!

Dell Service Tag

Open CMD

Type "wmic bios get serialnumber" (without quotes). Press "Enter." The computer responds with information from the Dell remote computer. The information below "Serial Number" is the Dell system service tag
To obtain the make and model number, type "wmic csproduct get vendor,name,identifyingnumber" (without quotes). Press "Enter.

Labels:

Thursday 18 November 2010

Word Count Function

Word Count Function :
create or replace
FUNCTION wordcount (str IN VARCHAR2)
RETURN PLS_INTEGER
AS
words PLS_INTEGER := 0;
len PLS_INTEGER := NVL(LENGTH(str),0);
inside_a_word BOOLEAN;
BEGIN
FOR i IN 1..len + 1
LOOP
IF ASCII(SUBSTR(str, i, 1)) < 33 OR i > len
THEN
IF inside_a_word
THEN
words := words + 1;
inside_a_word := FALSE;
END IF;
ELSE
inside_a_word := TRUE;
END IF;
END LOOP;
RETURN words;
END;

Tuesday 26 October 2010

Order by Month

Sometime we stuck to get our desire output.So its good practice we kept such information where its easily available.
Good Luck.
e.g.
SELECT TO_CHAR (TRUNC (dateordered, 'mm'), 'Month'),
MEDIAN (dateshipped - dateordered)
FROM orders
GROUP BY TRUNC (dateordered, 'mm')

ORDER BY TRUNC (dateordered, 'mm');

e.g.
select a.id,a.name,TO_CHAR(trunc(ah.date_viewed,'mm'),'Mon-yyyy') "Month Downloaded",count(*) "No of times Viewed"
from articleviewhistory ah,article a
where ah.article_id = a.id
and ah.article_id in (1085,984,961,900,638,630,628,624,613,611)
group by a.id, a.name, trunc(ah.date_viewed,'mm')
order by 2,trunc(ah.date_viewed,'mm');

e.g.
select to_char(day,'MON-YYYY'), sum(daily_growth_mb)
from db_growth
group by to_char(day,'MON-YYYY')
order by to_date(to_char(day,'MON-YYYY'),'MM-YYYY') desc;
or best one

select to_char(trunc(date_created,'MM'),'MON-YYYY'),count(*) from twitter
group by trunc(date_created,'MM')
order by trunc(date_created,'MM')

Wednesday 13 October 2010

Access Control List (ACL) Problems when using the UTL_TCP Package for FTP in Oracle 11g

I am sure you have read this. But pay attention that it is address to:
- particular IP address or host name
- particular Oracle user

First, create an ACL:

begin
dbms_network_acl_admin.create_acl (
acl => 'utlpkg.xml',
description => 'Normal Access',
principal => 'CONNECT',
is_grant => TRUE,
privilege => 'connect',
start_date => null,
end_date => null
);
end;

Here the parameter principal => 'CONNECT' indicates that the ACL applies to the CONNECT role. You can define a user or a role here. The ACL is created as a file called utlpkg.xml.
After the creation, you can check to make sure the ACL was added:

SELECT any_path
FROM resource_view
WHERE any_path like '/sys/acls/%.xml';

The output is:
ANY_PATH
----------------------------------------------------------------------------
/sys/acls/ANONYMOUS/ANONYMOUS3553d2be53ca40e040a8c0680777c_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN3551b25f93feb8dde040a8c068075b7_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN3551b25f944b8dde040a8c068075b7_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN3551b25f948b8dde040a8c068075b7_acl.xml
/sys/acls/OLAP_XS_ADMIN/OLAP_XS_ADMIN3551b25f94cb8dde040a8c068075b7_acl.xml
/sys/acls/all_all_acl.xml
/sys/acls/all_owner_acl.xml
/sys/acls/bootstrap_acl.xml
/sys/acls/ro_all_acl.xml
/sys/acls/ro_anonymous_acl.xml
/sys/acls/utlpkg.xml

Note the last line in the output, which shows the ACL you just created. Next, add a privilege to this ACL. In this example, you are trying to limit this ACL to the user SCOTT. You can also define start and end dates.
begin
dbms_network_acl_admin.add_privilege (
acl             => 'utlpkg.xml',
principal       => 'SCOTT',
is_grant       => TRUE,
privilege       => 'connect',
start_date       => null,
end_date       => null);
end;

Assign hosts and other details that will be subject to this ACL:
begin
dbms_network_acl_admin.assign_acl (
acl => 'utlpkg.xml',
host => 'www.proligence.com',
lower_port => 22,
upper_port => 55);
end;

In this example, you are specifying that "the user SCOTT can call only the host www.proligence.com and only for the ports 22 through 55, and not outside it." Now, let's try it:
SQL> grant execute on utl_http to scott
2 /

Grant succeeded.

SQL> conn scott/tiger
Connected.
SQL> select utl_http.request('http://www.proligence.com') from dual;
select utl_http.request('http://www.proligence.com') from dual
*
ERROR at line 1:
ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1577
ORA-24247: network access denied by access control list (ACL)
ORA-06512: at line 1

Note the error "ORA-24247: network access denied by access control list (ACL)." The user called the http server on port 80, which is outside the allowed range 22-55. Therefore the action was prevented.
Now, add another rule to allow the communication:

1 begin
2 dbms_network_acl_admin.assign_acl (
3 acl => 'utlpkg.xml',
4 host => 'www.proligence.com',
5 lower_port => 1,
6 upper_port => 10000);
7* end;
8 /

PL/SQL procedure successfully completed.

SQL> conn scott/tiger
Connected.
SQL> select utl_http.request('http://www.proligence.com') from dual;

UTL_HTTP.REQUEST('HTTP://WWW.PROLIGENCE.COM')
--------------------------------------------------------------------------------
</iframe><!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">

<HTML><HEAD><TITLE>Proligence Home</TITLE>
<META http-equiv=Content-Language content=en-us>

Tuesday 12 October 2010

Each OMS Startup Adds the Error "Integration Class not found" in the emoms Logfiles

Applies to:

Enterprise Manager Grid Control - Version: 10.2.0.5 and later [Release: 10.2 and later ]
Information in this document applies to any platform.

Symptoms

Each time 10.2.0.5 OMS is restarted, the following errors are logged in $OMS_HOME/sysman/log/emoms.trc file:

<Timestamp> [Orion Launcher] ERROR app.ContextInitializer contextInitialized.453 - Integration Class not found: oracle.sysman.test.pp.common.intg.ProvTestIntg
<Timestamp> [Orion Launcher] ERROR app.ContextInitializer contextInitialized.453 - Integration Class not found: oracle.sysman.pp.paf.sample.ui.intg.PAFDemoIntegration

Cause

This is due to an internal bug: "error in emoms.trc:oracle.sysman.pp.paf.sample.ui.intg.pafdemointegration". The bug is fixed in Grid Control 11.1.

Solution

These error messages can be safely ignored. They are due to some misconfigured integration class XML files, but do not prevent the Provisioning Framework to work as expected.

Workaround

1. Backup and edit $OMS_HOME/j2ee/OC4J_EM/applications/em/em/WEB-INF/config/empp_intg.xml.
Remove the line:

2. Backup and edit $OMS_HOME/j2ee/OC4J_EM/applications/em/em/WEB-INF/config/demo_intg.xml.
Remove the line:

3. Bounce the OMS.

Saturday 9 October 2010

ORA-12162: TNS:net service name is incorrectly specified

[oracle@Grid-EM ~]$ echo $ORACLE_SID

oraworld

[oracle@Grid-EM ~]$ echo $ORACLE_HOME

/oracle/product/11.2.0/dbhome_1

[oracle@Grid-EM ~]$ echo $TNS_ADMIN

[oracle@Grid-EM ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 9 13:59:01 2010

SQL> conn / as sysdba

ERROR:

ORA-12162: TNS:net service name is incorrectly specified

]$ cat initoraworld.ora

###########################################

# Database Identification

###########################################

db_domain=Grid-Em.co.uk

db_name=oraworld

*************** parameter file is case sensitive it follow ORACLE_SID variable value.

it means if ORACLE_SID=ORAWORLD then parameter file should be initORAWORLD.ora , spfileORAWORLD.ora

and if ORACLE_SID=oraworld then parameter file should be initoraworld.ora ,spfileoraworld.ora ******************************************************

[oracle@Grid-EM ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.1.0 - Production on 09-OCT-2010 14:06:39

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=Grid-EM.co.uk)(PORT=1521)))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production

Start Date 09-OCT-2010 13:13:37

Uptime 0 days 0 hr. 53 min. 2 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /oracle/product/11.2.0/dbhome_1/network/admin/listener.ora

Listener Log File /oracle/diag/tnslsnr/Grid-EM/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=Grid-EM.co.uk)(PORT=1521)))

Services Summary...

Service "Oracle8" has 1 instance(s).

Instance "ORCL", status UNKNOWN, has 1 handler(s) for this service...

Service "oraworld" has 1 instance(s).

Instance "oraworld", status UNKNOWN, has 1 handler(s) for this service...

Service "oraworld.Grid-Em.co.uk" has 1 instance(s).

Instance "oraworld", status READY, has 1 handler(s) for this service...

Service "oraworldXDB.Grid-Em.co.uk" has 1 instance(s).

Instance "oraworld", status READY, has 1 handler(s) for this service...

The command completed successfully

[oracle@Grid-EM ~]$ export ORACLE_SID=oraworld

[oracle@Grid-EM ~]$ export ORACLE_HOME=/oracle/product/11.2.0/db_home1

--- sometimes even though "echo" display value of "ORACLE_SID & ORACLE_HOME" but they are not defined in environment. So it better we "export" it again manually and try it which I did in above steps.

[oracle@Grid-EM ~]$ sqlplus /nolog

SQL*Plus: Release 11.2.0.1.0 Production on Sat Oct 9 14:08:20 2010

SQL> conn / as sysdba

Connected to an idle instance.

everything was fine here Listener.ora,TnsNames.ora,Init.ora but Only Environment variable ORACLE_SID was not "export" properly.

******************** **********Some times small mistake create big chaos***************

if you lost your init.ora you may get some where here "/oracle/admin/<ORACLE_SID>/pfile"

Wednesday 29 September 2010

Install Apex on Oracle XE database

1. Ensure you are logged into Windows XP/7 with Administrator rights

I assume you already installed OracleXE database.

2. Create home directory for APEX and extract software to that directory

I have created like this C:\ORACLE\product\OracleXE\app\oracle\product\10.2.0\server\apex_4.0.1\apex\all_apex_files

3. Using SQLPLUS, install the APEX software against the XE database you already installed

@apexins.sql SYSAUX SYSAUX TEMP /i/

4. Change the APEX ADMIN account password (4:35)

@apxchpwd.sql

5. Run the image loader script (5:25)

@apxldimg.sql C:\ORACLE\product\OracleXE\app\oracle\product\10.2.0\server\apex_4.0.1

6. Login to http://localhost:8080/apex and check it.

That's All !

Enjoy it.

Monday 27 September 2010

Out-of-place upgrade

Hello Hemss,

I am trying to do an in-place-upgrade (replacing the old Oracle software completely because our test servers have only very little space). When you want to perform an out-of-place upgrade, things are more simple, but you need the disk space for a new Oracle home (about 6 GB on Linux-x64 in my case). Essentially, the steps are:

1. You can keep the production database online during the next steps
2. Install the new 11.2.0.2.0 into a new Oracle Home (an Oracle software directory which does not exist yet). Only install the software.
3. Apply any post-11.2.0.2.0 patches if available and needed
4. Schedule a maintenance window with your users
5. Put the original database(s) in MOUNT mode (SHUTDOWN IMMEDIATE followed by STARTUP MOUNT)
6. Perform a FULL backup of both database and old Oracle home, just to be on the safe side.
7. Set your enviromnent: ORACLE_HOME and ORACLE_BASE should point to your NEW installation, ORACLE_SID to your production database.
8. Start netca to create a new LISTENER for your new software installation (you might want to use a different port than your previous installation in case you need to run both at the same time)
9. Start dbua from the new Oracle Home and follow the on-screen instructions. You can either create an updated copy of the database (needs more disk space but you can easily switch back to your orginal DB), or you change the database to fit your new Oracle version (needs less space, but you need to run the scripts to downgrade to the old release if you need to go back)

So, as long as you have plenty of disk space, things are not that difficult at all. Of course, you should always read the release notes and the full installation guides for the new version because there might be new features or behaviours that affect your database. It is also highly advisable to test this on a completely different machine first.

You can find the complete upgrade guide here: http://download.oracle.com/docs/cd/E11882_01/server.112/e17222/toc.htm

Friday 24 September 2010

Upgrade/Install Oracle Apex 3.2.1 to 4.0.1 on Oracle Database 10g,11g

1. Download APEX from OTN

2. Unzip it making sure you don't change the name of the apex folder inside the zip.

unzip apex_4.0.1.zip /tmp/
cd /tmp/apex

3. Connect to your XE installation

sqlplus / as sysdba

4. Run the installation script

SQL> @apexins.sql SYSAUX SYSAUX TEMP /i/

5. Wait 10-15 minutes then connect again as sysdba and import images

SQL>@apxldimg.sql /tmp

!do not put the "apex" folder in the path there

That's it, no more unlock account, set password, set port, you already did this on the previous installation.
Enjoy!
*****************************************************************
To do fresh installation follow above steps plus these ::
sql > @apxconf.sql
Enter a password for the ADMIN user and you can change default Port[8080] if you want.
sql > ALTER USER ANONYMOUS ACCOUNT UNLOCK;
Now,login in browser http://127.0.0.1:8080/apex with following detail :
Workspace – internal
Username – Admin
Password – password entered in @apxconf.sqll
That's all!
Enjoy !

Other userful link

http://www.davidghedini.com/pg/entry/install_oracle_11g_xe_on

http://www.truexense.com/2013/03/how-to-upgrade-oracle-apex-41-to-421-on.html

Thursday 23 September 2010

How to Configure APEX & the Embedded PL/SQL Gateway (EPG) in an 11G DB

Applies to:

Oracle Application Express (formerly HTML DB) - Version: 3.0.1 to 3.2
Information in this document applies to any platform.
APEX 3.0.1

Goal

How to Configure APEX & the Embedded PL/SQL Gateway (EPG) in an 11G DB

Solution

The 11g DB (11.1.X) comes with APEX 3.0.1 installed and requires just a few post-install
configuration steps:

Accomplish the following steps to configure the embedded PL/SQL gateway, specify APEX password, and unlock the ANONYMOUS account.

1. Change your working directory to $ORACLE_HOME/apex.
2. Start SQL*Plus and connect to the database where Oracle Application Express is installed as SYS.
For example:

ORACLE_HOME/bin/sqlplus /nolog
CONNECT SYS as SYSDBA
Enter the SYS password

3. Run apxconf.sql ->

4. When prompted, enter a password for the Oracle Application Express Admin account.
Make note of the password you enter, since you will use this password to log in to Oracle
Application Express Administration Services.

5. When prompted, enter the port for the Oracle XML DB HTTP server. The default port number is 8080.

Note:
Port numbers less than 1024 are reserved for use by privileged processes on many operating systems.
To enable the XML DB HTTP listener on a port less than 1024, such as 80, review the following
documentation:
· Refer to chapter "Using Protocols to Access the Repository" in Oracle XML DB Developer's
Guide for more information on using HTTP(S) and Oracle XML DB Protocol Server.
· Refer to chapter "Protocol Address Configuration" in Oracle Database Net Services Reference
for more information on protocol addressing.

6. Enter the following statement to unlock the ANONYMOUS account->

ALTER USER ANONYMOUS ACCOUNT UNLOCK;

At this time you should be able to log into apex as the admin user from a browser using -> http://machine.domain:port/apex

The machine is the DB host and the port is the one chosen in step 5.

If for some reason you can't log in, verify the EPG is up by running the following in your browser ->

http://machine.domain:port

If it's up, you should be prompted for a username and password for XDB.

Workspace – internal
Username – Admin
Password – passwd

If the EPG is not up, accomplish the following to start it:

1. Log in as SYS as SYSDBA
2.. Run the following statement:

EXEC DBMS_XDB.SETHTTPPORT(port);

Where port is the PLSQL Gatway port.

3. COMMIT;

For example:

EXEC DBMS_XDB.SETHTTPPORT(8080);
COMMIT;

Monday 20 September 2010

How to Clear asm disk headers

dd if=/dev/zero bs=8k count=1000 of=/dev/sde1

[root@rac2 ~]# /etc/init.d/oracleasm querydisk -d /dev/sde1

Device "/dev/sde1" is marked an ASM disk with the label "DISK4"

[root@rac2 ~]# /etc/init.d/oracleasm querydisk -d /dev/sdb1

Device "/dev/sdb1" is marked an ASM disk with the label "DISK1"

[root@rac2 ~]# /etc/init.d/oracleasm querydisk -d /dev/sdc1

Device "/dev/sdc1" is marked an ASM disk with the label "DISK2"

[root@rac2 ~]# /etc/init.d/oracleasm querydisk -d /dev/sdd1

Device "/dev/sdd1" is marked an ASM disk with the label "DISK3"

[root@rac2 ~]# /etc/init.d/oracleasm querydisk -d /dev/sde1

Device "/dev/sde1" is marked an ASM disk with the label "DISK4"

[root@rac2 ~]# dd if=/dev/zero bs=8k count=1000 of=/dev/sde1

1000+0 records in

1000+0 records out

8192000 bytes (8.2 MB) copied, 0.460529 seconds, 17.8 MB/s

[root@rac2 ~]# oracleasm listdisks

DISK1

DISK2

DISK3

DISK4

[root@rac2 ~]# oracleasm scandisks

Reloading disk partitions: done

Cleaning any stale ASM disks...

Cleaning disk "DISK4"

Scanning system for ASM disks...

[root@rac2 ~]# oracleasm listdisks

DISK1

DISK2

DISK3

Use the below command if you want to clear an ASM disk header. We may want to do that if we want to reuse the disk to add it to some other diskgroup.

$ dd if=/dev/zero of=/dev/raw/raw1 bs=8192 count=12800
12800+0 records in
12800+0 records out
104857600 bytes (105 MB) copied, 3.78717 seconds, 27.7 MB/s

Let us now verify the disk header..

$ kfed read /dev/raw/raw1
kfbh.endian: 0 ; 0x000: 0x00
kfbh.hard: 0 ; 0x001: 0x00
kfbh.type: 0 ; 0x002: KFBTYP_INVALID
kfbh.datfmt: 0 ; 0x003: 0x00
kfbh.block.blk: 0 ; 0x004: T=0 NUMB=0x0
kfbh.block.obj: 0 ; 0x008: TYPE=0x0 NUMB=0x0
kfbh.check: 0 ; 0x00c: 0x00000000
kfbh.fcn.base: 0 ; 0x010: 0x00000000
kfbh.fcn.wrap: 0 ; 0x014: 0x00000000
kfbh.spare1: 0 ; 0x018: 0x00000000
kfbh.spare2: 0 ; 0x01c: 0x00000000
$

KFBTYP_INVALID, indicates the disk headers are cleared. This disk should now appear as a CANDIDATE disk in the v$asm_disk.

http://blog.ronnyegner-consulting.de/2009/10/08/asm-resilvering-%E2%80%93-or-%E2%80%93-how-to-recover-your-crashed-cluster-%E2%80%93-test-no-3/

Thursday 16 September 2010

Secure Other Executables and DBSNMP

1.4 Secure Other Executables

Background
Take a look at the other executables in the $ORACLE_HOME/bin directory; some may look familiar, such as sqlplus or lsnrctl (the utility to start the listener); others may not.

Some of these files—such as tnslsnr, the utility that the listener process runs, or dbsnmp, which was used in Oracle Intelligent Agent—are not directly touched by the end user. To properly secure them, you must understand what they do and take appropriate action.

Recall that if the SUID bit is set for a file, then regardless of who runs the file it runs under the privileges of the owner, not the executor. You also learned that setting the SUID can be dangerous and should be discouraged.

There are several other files that have the SUID set to on. Let's find them.

$ cd $ORACLE_HOME

$ find . -type f $ -perm -2000 -o -perm -4000 $ -exec ls -l {} \;

In Oracle Database 10g Release 1 and later, the above should return only the following executables (Oracle Database 11g should return only a subset of this):

-rwsr-s--x 1 orasoft dba 93300507 Jul 22 11:20 ./bin/oracleO

-r-sr-s--- 1 root dba 0 Jul 1 23:15 ./bin/oradism

-rwsr-s--x 1 orasoft dba 94492 Jul 22 11:22 ./bin/emtgtctl2

-rwsr-s--- 1 root dba 18944 Jul 22 11:22 ./bin/nmb

-rwsr-s--- 1 root dba 20110 Jul 22 11:22 ./bin/nmo

-r-sr-sr-x 1 nobody nobody 58302 Jul 22 11:23 ./bin/extjob

Let's see what these files are:

Program	Description
./bin/oracle	This file is a copy of the executable oracle. When you recompile the oracle executable using the relink command, the old copy is saved as oracleO. This is a potential security hazard; most DBAs ignore it, and it can be an avenue for hackers. Therefore you should take action to remove the permissions. The best option is to have no permissions for it to anyone other than orasoft: $ chmod 600 oracleO Now, if you check the permissions: $ ls -l oracleO -rw------- 1 orasoft oinstall 248823320 Sep 15 13:27 oracleO
./bin/oradism	Used for Dynamic Intimate Shared Memory. May be in use on your platform. May not be present in all cases. If present, leave as is.
./bin/emtgtctl2	Used for Oracle Enterprise Manager agent. There is no need for it to be set with SUID. The justification is the same as the oracle executable. Remove the permissions: $ chmod 0700 emtgtctl2
./bin/nmb	Used for Oracle 10g Grid Control agent to collect statistics on the target server. Leave as is.
./bin/nmo	Used for Oracle 10g Grid Control agent to collect statistics on the target server. Leave as is.
./bin/extjob	This is the executable for the EXTJOB (External Jobs, which allow you to execute OS-based programs from within Oracle Enterprise Manager). This is something you should be careful about. Do you use external jobs a lot? If not, then you should not even have this executable. In such a case, you can leave it in the directory but change the permissions and the ownership. The owner can be the Oracle software owner (orasoft, in our case), and the permissions should be rwx------: $ chown orasoft:oinstall extjob $ chmod 0700 extjob There may be another program present, extjobO, which was a previous compilation of the same program. Change the permissions of that too: $ chown orasoft:oinstall extjobO $ chmod 0600 extjobO

In Oracle9i Database Release 2, you will find a different file, ./bin/dbsnmp, which is the Oracle Intelligent Agent executable file. The permissions are set as such:

-rwsr-s--- 1 root dba 2986836 Jan 26 2005 dbsnmp

The problem with this file is that it needs root privileges to work properly, hence the SUID bit must be set to on. However, because this file is owned by root, hackers typically exploit it to gain access as root. The best advice is to eliminate it, or make it owned by the Oracle software owner and set the permissions to 700. You will lose some functionality, but it's worth it to eliminate the risk.

The other executable to consider is tnslsnr, which is the Oracle Net Listener. There are two executables:

• tnslsnr—the actual listener executable
• lsnrctl—the utility that is used to manage the listener, such as starting, stopping, and so on

If you look at the permissions:

$ ls -l *lsnr*

-rwxr-x--x 1 orasoft oinstall 214720 Oct 25 01:23 lsnrctl

-rwxr-xr-x 1 orasoft oinstall 214720 Oct 1 18:50 lsnrctl0

-rwxr-x--x 1 orasoft oinstall 1118816 Oct 25 01:23 tnslsnr

-rwxr-xr-x 1 orasoft oinstall 1118816 Oct 1 18:50 tnslsnr0

the files have execute privileges for all. Like the executable oracleO, when a new file tnslsnr is created by relinking the Oracle software, the existing file tnslsnr is renamed to tnslsnr0. This is done because if the process needs to be rolled back, the old executable can be copied over the new one. Because it's the copy of the old executable, the file tnslsnr0 may contain the same functionality as the original tnslsnr. The same goes for lsnrctl0.

Strategy
Now that you understand the purpose of each executable, let's see how you can secure your database infrastructure. Most of the strategy has been discussed in the above section on background information. So, in essence, your strategic moves are all these actions

Remove all permissions to others from the files that are not needed—for example, lsnrctl0.
Restrict permissions for executables to Oracle software only.
Remove the SUID bit if the Oracle software owner starts the processes.
So, you want to change the permissions of the listener-related files as follows:
$ chmod 700 lsnrctl tnslsnr lsnrctl0 tnslsnr0
$ chmod 600 lsnrctl0 tnslsnr0

Verify the result:
$ ls -l *lsnr*
-rwx------ 1 orasoft oinstall 214720 Oct 25 01:23 lsnrctl
-rw------- 1 orasoft oinstall 214720 Oct 1 18:50 lsnrctl0
-rwx------ 1 orasoft oinstall 1118816 Oct 25 01:23 tnslsnr
-rw------- 1 orasoft oinstall 1118816 Oct 1 18:50 tnslsnr0

Implications
There are a few implications in this case:

Changing the oracleO executable has no impact on the operation of the database. If you ever face an issue that points to a corrupt oracle executable, your best bet is to rename the oracleO file to "oracle." If you do so, make sure you reset to permissions to 700. The same goes for lsnrctl0 and tnslsnr0executables.
Changing the emtgtctl2 permissions will have no impact if you use the Oracle software owner userid as the Oracle Enterprise Manager OS credentials. If you use a different userid (not orasoft, for example), the SUID must be reset to the old value and the permissions must be set as they were.
The executable dbnsmp is used by Oracle Enterprise Manager Intelligent Agent, but only up until Oracle9i Database Release 2. Again, if you use the Oracle software owner as the OS credentials, there is no impact from changing the permissions. If you use a different userid, you must reset the permissions to the previous value.
Action Plan

Change permissions of oracleO, tnslsnr0, and lsnrctl0 to 0600.
Change permissions for tnslsnr and lsnrctl to 0700.

Do you use external jobs in Oracle Enterprise Manager?

IF no THEN change the permissions of extjob to 0000 ELSE
	Change the permissions of extjob to 0700 and change the owner and group to orasoft and oinstall (or whatever the user and group of the Oracle software owner are).
END IF

IF you are on Oracle9i Database THEN

Are you using Oracle Intelligent Agent?
IF no THEN

Change ownership of dbsnmp to orasoft
Change permissions to 0700
ELSE

No change needed
END IF

Note: If you apply a patch or upgrade the database, the permissions will be reset, so you need to re-examine them after the upgrade.
1.5 Change DBSNMP Password
Background
As you may know, Oracle Intelligent Agent communicates with Oracle Enterprise Manager to pass on information about components such as the database, the listener, and the server itself. To get data about the database, it needs to connect to the database using some userid. By default, the userid used is DBSNMP.
When the database is created, the password of DBSNMP] is also set to dbsnmp. This user has some powerful privileges, such as UNLIMITED TABLESPACE, SELECT ANY DICTIONARY (which allows the user to select from dynamic performance views and data dictionary views), and ANALYZE ANY DICTIONARY (which allows analyze of the system objects). Many intruders use this userid and password for back-door entry into the database. Needless to say, this is a huge security hole.
Strategy
You have to change the password of this user to something other than dbsnmp. However, you can't just change the password at the database level, because that password is also stored in the agent configuration files. You need to update the configuration files to use the new password as well. Here's the procedure for Oracle Database 10g:
First change the password of the user DBSNMP to something else—for example, TopSecret:
SQL> alter user dbsnmp identified by topsecret;
Go to the directory where the Oracle Agent Home is installed (not ORACLE_HOME)—for example, /u01/app/oracle/10.1/gridc.
Go to directory <hostname>/sysman/emd , where <hostname> is the name of the host or server. For instance, if the name of the server is prolin1, then the directory should be prolin1/sysman/emd.
Here you will find a file named targets.xml. Copy it under a new name (for example, targets.xml.old).
Open the file targets.xml and search for the word "dbsnmp"; the contents should be similar to:
<Target TYPE="oracle_database" NAME="PROPRD1_prolin1">
<Property NAME="MachineName" VALUE="192.168.101"/>
<Property NAME="OracleHome" VALUE="/u01/app/oracle/10.1/db1"/>
<Property NAME="Port" VALUE="1521"/>
<Property NAME="Role" VALUE="NORMAL"/>
<Property NAME="SID" VALUE="PROPRD1"/>
<Property NAME="ServiceName" VALUE="PROPRD"/>
<Property NAME="UserName" VALUE="dbsnmp"/>
<Property NAME="password" VALUE="3797cf30e7c4a9c6" ENCRYPTED="TRUE"/>

<CompositeMembership>
<MemberOf TYPE="rac_database" NAME="PROPRD" ASSOCIATION="cluster_member"/>
</CompositeMembership>
</Target>
Note this line (in bold type, above):
<Property NAME="password" VALUE="3797cf30e7c4a9c6" ENCRYPTED="TRUE"/>

This is where you will set the value of the password. Replace the above with
<Property NAME="password" VALUE="topsecret" ENCRYPTED="FALSE"/>

Note that you changed the value of ENCRYPTED to FALSE.
If this is a RAC database, this line will occur twice in the file. Make sure you change both occurrences. Search the file for the word "password" to locate these two instances
Now stop the agent by issuing this command
/u01/app/oracle/10.1/gridc/bin/emctl stop agent

Restart the agent:
/u01/app/oracle/10.1/gridc/bin/emctl stop agent
When you restart the agent, the cleartext password in the configuration file is encrypted. If you check the above line in the targets.xml file again, you will see something similar to the following:
<Property NAME="password" VALUE="3797cf30e7c4a9c6" ENCRYPTED="TRUE"/>

Note how the cleartext value has been converted to an encrypted value.
Now the agent is configured with the new password.
If you use the standalone Database Console instead of Oracle 10g Grid Control, then the procedure is similar—except that in Step 2, you would go to ORACLE_HOME, not where the Agent Home is located.
Implications
There are no user implications here.
Action Plan

1. Change the password of the user DBSNMP.
2. Update the agent files to reflect the new password.

Tuesday 14 September 2010

Oracle Configuration Manager (OCM) Release 10.3 FAQ [ID 369111.5]

Oracle Configuration Manager (OCM) Release 10.3 FAQ [ID 369111.5]

Monday 13 September 2010

RMAN 11G : Data Recovery Advisor - RMAN command line example

Applies to:

Oracle Server Enterprise Edition - Version: 11.1.0.6 to 11.1.0.7
Information in this document applies to any platform.
Oracle Server - Enterprise Edition - Version: 11.1.0.6 to 11.1.0.7

Restrctions:

In the current release, Data Recovery Advisor supports single-instance databases. Oracle Real Application Clusters databases are not supported in 11.1.0.6 -> 11.1.0.8

Data Recovery Advisor cannot use blocks or files transferred from a standby database to repair failures on a primary database. Also, you cannot use Data Recovery Advisor to diagnose and repair failures on a standby database. However, the Data Recovery Advisor does support failover to a standby database as a repair option (as mentioned above).

Goal

This note will provide a DBA with an understanding of how the new feature Data Recovery Advisor in 11g operates.

Solution

Overview

What Is the Data Recovery Advisor?

The Data Recovery Advisor is a tool that helps you to diagnose and repair data failures and corruptions. The Data Recovery Advisor analyzes failures based on symptoms and intelligently determines optimal repair strategies. The tool can also automatically repair diagnosed failures.

The Data Recovery Advisor is available from Enterprise Manager (EM) Database Control and Grid Control. You can also use it via the RMAN command-line.

In this example I will you will see examples of via the RMAN command line utilising the DRA commands:

Three DRA commands are available within RMAN:

List Failure - lists the results of previously executed failure assessments. Revalidates existing failures and closes them, if possible.

Advise Failure - presents manual and automatic repair options

Repair Failure - automatically fix failures by running optimal repair option, suggested by ADVISE FAILURE. Revalidates existing failures when completed.

Below is a demonstration of DRA.

1. Take Full RMAN backup;

2. RMAN> validate check logical datafile 2;

Show that the datafile is free from corruption.

RMAN> validate check logical datafile 2;

Starting validate at 2008/11/10 09:52:36
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=132 device type=DISK
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/oradata/V11/sysaux01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:25
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2        OK              0        28279          104896    2885460
File Name: /oradata/V11/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data                    0            21440
Index                   0            24664
Other                   0            30513

Finished validate at 2008/11/10 09:53:02

3. Corrupt several Blocks within the sysaux tablespace

SQL> @corrupt_blocks.sql

4. RMAN> validate check logical datafile 2;

This should show the corruption and indicate the # of blocks corrupted.

RMAN> validate check logical datafile 2;

Starting validate at 2008/11/10 09:57:05
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00002 name=/oradata/V11/sysaux01.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:07
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
2 FAILED                 0        28273          104896    2894632
File Name: /oradata/V11/sysaux01.dbf
Block Type Blocks Failing Blocks Processed
---------- -------------- ----------------
Data                    0            21440
Index                   0            24663
Other                  10            30520

validate found one or more corrupt blocks
See trace file /u01/app/oracle/product/11.1.0/db_1/diag/rdbms/v11/V11/trace/V11_ora_22522.trc for details
Finished validate at 2008/11/10 09:57:12

Review: /u01/app/oracle/product/11.1.0/db_1/diag/rdbms/v11/V11/trace/V11_ora_22522.trc
Open the trace file to show the errors that have been picked up.

Corrupt block relative dba: 0x00800024 (file 2, block 36)
Completely zero block found during validation
Reread of blocknum=36, file=/oradata/V11/sysaux01.dbf. found same corrupt data
Reread of blocknum=36, file=/oradata/V11/sysaux01.dbf. found same corrupt data
Reread of blocknum=36, file=/oradata/V11/sysaux01.dbf. found same corrupt data
Reread of blocknum=36, file=/oradata/V11/sysaux01.dbf. found same corrupt data
Reread of blocknum=36, file=/oradata/V11/sysaux01.dbf. found same corrupt data
Hex dump of (file 2, block 37)

We can now issue list failure & list failure <number> detail to get more about the issue.

5. RMAN> list failure;
RMAN> list failure ### detail; ( where ### equlas the failure number)

RMAN> list failure;

List of Database Failures
=========================

Failure ID Priority Status          Time Detected            Summary
---------- -------- ---------       -------------------       -------
351261         HIGH    OPEN 2008/11/10 09:56:33 Datafile 2: '/oradata/V11/sysa
ux01.dbf' contains one or more corrupt blocks

RMAN> list failure 351261 detail;

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
351261 HIGH OPEN 2008/11/10 09:56:33 Datafile 2: '/oradata/V11/sysa
ux01.dbf' contains one or more corrupt blocks
Impact: Some objects in tablespace might be unavailable
List of child failures for parent failure ID 351261
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
351261 HIGH OPEN 2008/11/10 09:56:33 Datafile 2: '/oradata/V11/sysa
ux01.dbf' contains one or more corrupt blocks
Impact: Some objects in tablespace might be unavailable
List of child failures for parent failure ID 351261
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
352028 HIGH OPEN 2008/11/10 09:56:35 Block 37 in datafile 2: '/or
adata/V11/sysaux01.dbf' is media corrupt

At this point you are able to issue the advise keyword which will prompt DBA will be advice on how to solve the current scenario.

6. RMAN> Advise Failure;

List of Database Failures
=========================

Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
351261 HIGH OPEN 2008/11/10 09:56:33 Datafile 2: '/oradata/V11/sysa
ux01.dbf' contains one or more corrupt blocks
Impact: Some objects in tablespace might be unavailable
List of child failures for parent failure ID 351261
Failure ID Priority Status Time Detected Summary
---------- -------- --------- ------------------- -------
352028 HIGH OPEN 2008/11/10 09:56:35 Block 37 in datafile 2: '/or
adata/V11/sysaux01.dbf' is media corrupt
..
..
..
Impact: Object I_SMB$CONFIG_PKEY owned by SYS might be unavailable
351974 HIGH OPEN 2008/11/10 09:56:33 Block 28 in datafile 2: '/or
adata/V11/sysaux01.dbf' is media corrupt
Impact: Object I_SMB$CONFIG_PKEY owned by SYS might be unavailable

analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
analyzing automatic repair options complete

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If a standby database is available, then consider a Data Guard switchover or failover

Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Recover multiple corrupt blocks in datafile 2

Strategy: The repair includes complete media recovery with no data loss

Repair script: /u01/app/oracle/product/11.1.0/db_1/diag/rdbms/v11/V11/hm/reco_189110358.hm

RMAN>

Review File: : /u01/app/oracle/product/11.1.0/db_1/diag/rdbms/v11/V11/hm/reco_189110358.hm

[oracle@aulnx7 hm]$ vi reco_189110358.hm

# block media recovery for multiple blocks
recover datafile 2 block 28 to 37;

As can be seen above one option is to perform rman block recovery. The next step for the DBA is to proceed with the repair preview.

7. RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/product/11.1.0/db_1/diag/rdbms/v11/V11/hm/reco_42
19629556.hm

contents of repair script:
# block media recovery for multiple blocks
recover datafile 2 block 28 to 37;

Then you are ready to perform the fix.

8. RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/product/11.1.0/db_1/diag/rdbms/v11/V11/hm/reco_42
19629556.hm

contents of repair script:
# block media recovery for multiple blocks
recover datafile 2 block 28 to 37;

Do you really want to execute the above repair (enter YES or NO)? YES
executing repair script

Starting recover at 2008/11/10 10:18:48
using channel ORA_DISK_1

channel ORA_DISK_1: restoring block(s)
channel ORA_DISK_1: specifying block(s) to restore from backup set
restoring blocks of datafile 00002
channel ORA_DISK_1: reading from backup piece /recovery_area/V11/backupset/2008_
11_10/o1_mf_nnndf_TAG20081110T093808_4kgslm1w_.bkp
channel ORA_DISK_1: piece handle=/recovery_area/V11/backupset/2008_11_10/o1_mf_n
nndf_TAG20081110T093808_4kgslm1w_.bkp tag=TAG20081110T093808
channel ORA_DISK_1: restored block(s) from backup piece 1
channel ORA_DISK_1: block restore complete, elapsed time: 00:00:35

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

Finished recover at 2008/11/10 10:19:27
repair failure complete

RMAN>

At this point the blocks have been fully recovered.

______________________________________________________________________
This email has been scanned by the MessageLabs Email Security System.
For more information please visit http://www.messagelabs.com/email
______________________________________________________________________

IF you are on Oracle9i Database THEN
	Are you using Oracle Intelligent Agent?
IF no THEN
	Change ownership of dbsnmp to orasoft Change permissions to 0700
ELSE
	No change needed
END IF