Dev's Weblog

Suppose you want to create huge index on some table but don’t want optimizer to change its plan based on new index then you need to use 11G’s new feature called invisible index.

11G allows you to create index which would be invisible to optimizer.

• How to create invisible index

SQL> create index idx on tbl1(name) invisible;
Index created.

You can also specify a tablespace while creating the invisible index.

SQL> create index idx2 on tbl2(name) invisible tablespace users;
Index created.

The index idx2 is stored to users tablespace as just a normal index.

• Making index invisible

SQL> alter index idx1 invisible;
Index altered.

• Making index visible

SQL> alter index idx1 visible;
Index altered.

You cann’t create two index on same column even if one being invisible.

If you want optimizer to use invisible index while deciding query plan then you need to set following parameter enabled.

SQL> alter session set optimized_use_invisible_indexes=true;

SQL> alter system set optimized_use_invisible_indexes=true;

In 10G if you would like to add a column to any table and if oracle is not able to acquire the exclusive lock on that table because of frquent updates on that table then DDL command would fail immediately.

But in 11G you can ask Oracle to wait before it fails in case of its inability to acquire the necessary DML lock on the table.

The new command LOCK TABLE lets you specify the maximum time a statement should wait to obtain a DML lock on a table.

The syntax for new command is ,

LOCK TABLE … IN lockmode MODE [NOWAIT | WAIT integer]

nowait and wait means :

• Nowait – Immediate error.
• Wait – Will wait number of seconds before it fails to acquire DML lock.You can set the value of this parameter to any integer value you want—there’s no limit.
• If you don’t specify either wait or nowait, the database will wait until the locked table is available and then lock it before returning control to you.

In previous releases, by default, any DDL locks wouldn’t wait for a DML lock but would fail right away if they couldn’t obtain the DDL lock. In Oracle Database 11g, you can use the new initialization parameter ddl_lock_timeout to specify the duration for which a DDL statement will wait for a DML lock.

The default value of the ddl_lock_timeout parameter is zero, meaning that DDL statements won’t wait for a DML lock. The maximum value of 1,000,000 seconds means you can potentially set the wait duration for as long as 11.5 days. You can use the alter session statement to specify the duration of wait for a DML lock, as shown here:

SQL> alter session set ddl_lock_timeout = 60;
Session altered.

Sometimes you might want to store data in a column based on the evaluation of an expression. Oracle Database 11g provides a new type of column you can include in a table, called a virtual column. Virtual columns are similar to normal table columns, with two major differences:

1. You can’t write to a virtual column.
2. A virtual column is populated by the evaluation of an expression.

You can do the following things with a virtual column:

1. Use them in both DDL and DML statements.
2. Collect statistics on them.
3. Define indexes on them. The indexes you define are similar to function-based indexes

you create on normal columns. Behind the scenes, Oracle creates a function-based index on the virtual column when you create an index on that column.

Creating a Table with a Virtual Column

To create a virtual column, you must use the clause generated always as after the virtual column name when you create a table. Here’s an example showing how to incorporate a virtual column in a table:

SQL> create table emp (
2 no NUMBER(5) PRIMARY KEY,
3 name VARCHAR2(10) NOT NULL,
4 ni NUMBER(5),
5 sal NUMBER(8,3),
6 rate NUMBER(8,3) generated always as (sal/2000));
Table created.
SQL>

Above example creates the virtual column rate. If you want, you can also use the keyword virtual after this line to make it syntactically complete, but the keyword is purely optional.

The following example shows how to use the optional keyword virtual as part of a table creation statement that also creates a check constraint on the virtual column.

SQL> create table emp
2 (sal number (8,3),
3 rate number (8,3) generated always as (sal/2000)
4 virtual
5 constraint HourlyRate CHECK (rate > 8.00));
Table created.
SQL>

Virtual Columns Limitations

The following are restrictions on virtual columns:

• You can create virtual columns only on ordinary (heap-organized) tables.
• You can’t create virtual columns on an index-organized table, an external table, a temporary table, an object, or a cluster.
• You can’t create a virtual column as a user-defined type, LOB, or RAW.
• All columns in the column expression must belong to the same table.
• The column expression must result in a scalar value.
• The column expression in the generated always as clause can’t refer to another virtual column.
• You can’t update a virtual column by using it in the set clause of an update statement.
• You can’t perform a delete or an insert operation on a virtual column.

• First you need to have OPatch 11.1.0.6.2 or higher to apply the CPU JUL 2008.You can download OPatch 11.1.0.6.3 from Patch 6880880.

[oracle@quartz Desktop]$ unzip p6880880_111000_Linux-x86-64.zip
Archive: p6880880_111000_Linux-x86-64.zip
creating: OPatch/
creating: OPatch/docs/
inflating: OPatch/docs/FAQ
inflating: OPatch/docs/Users_Guide.txt
inflating: OPatch/docs/Prereq_Users_Guide.txt
extracting: OPatch/docs/tmp
creating: OPatch/jlib/
inflating: OPatch/jlib/opatch.jar
inflating: OPatch/jlib/opatchutil.jar
inflating: OPatch/jlib/opatchprereq.jar
inflating: OPatch/jlib/opatchactions.jar
creating: OPatch/opatchprereqs/
creating: OPatch/opatchprereqs/opatch/
inflating: OPatch/opatchprereqs/opatch/opatch_prereq.xml
inflating: OPatch/opatchprereqs/opatch/rulemap.xml
inflating: OPatch/opatchprereqs/opatch/runtime_prereq.xml
creating: OPatch/opatchprereqs/oui/
inflating: OPatch/opatchprereqs/oui/knowledgesrc.xml
inflating: OPatch/opatchprereqs/prerequisite.properties
inflating: OPatch/opatch
inflating: OPatch/opatch.bat
inflating: OPatch/opatch.pl
inflating: OPatch/opatch.ini
inflating: OPatch/emdpatch.pl
inflating: OPatch/README.txt
creating: OPatch/ocm/
extracting: OPatch/ocm/ocm.zip
creating: OPatch/ocm/doc/
inflating: OPatch/ocm/doc/license.txt
creating: OPatch/ocm/lib/
inflating: OPatch/ocm/lib/emocmutl.jar
creating: OPatch/ocm/bin/
inflating: OPatch/ocm/bin/emocmrsp

• Remove the Old Opatch from $ORACLE_HOME

[oracle@quartz Desktop]$ rm -rf /u01/app/oracle/product/11.1.0/db_2/OPatch/

• Move new OPatch to $ORACLE_HOME

[oracle@quartz Desktop]$ mv OPatch/ $ORACLE_HOME

• Check the OPatch version

[oracle@quartz Desktop]$ /u01/app/oracle/product/11.1.0/db_2/OPatch/opatch version
Invoking OPatch 11.1.0.6.3

OPatch Version: 11.1.0.6.3

OPatch succeeded.

• Check the State of Resources

[oracle@quartz Desktop]$ crs_stat -t -v
Name Type R/RA F/FT Target State Host
———————————————————————-
ora….TZ.lsnr application 0/5 0/0 ONLINE ONLINE quartz
ora.quartz.gsd application 0/5 0/0 ONLINE ONLINE quartz
ora.quartz.ons application 0/3 0/0 ONLINE ONLINE quartz
ora.quartz.vip application 0/0 0/0 ONLINE ONLINE quartz

• Stop all application on the node where you are going to apply Patch.

[oracle@quartz Desktop]$ srvctl stop nodeapps -n quartz

• Check once again to make sure state if off line for all resources.

[oracle@quartz Desktop]$ crs_stat -t -v
Name Type R/RA F/FT Target State Host
———————————————————————-
ora….TZ.lsnr application 0/5 0/0 OFFLINE OFFLINE
ora.quartz.gsd application 0/5 0/0 OFFLINE OFFLINE
ora.quartz.ons application 0/3 0/0 OFFLINE OFFLINE
ora.quartz.vip application 0/0 0/0 OFFLINE OFFLINE

• Move to directory where you have unzipped CPU JUL patch and apply it.

[oracle@quartz Desktop]$ cd 7150417/

[oracle@quartz 7150417]$ /u01/app/oracle/product/11.1.0/db_2/OPatch/opatch napply -skip_subset -skip_duplicate -local
Invoking OPatch 11.1.0.6.3

Oracle Interim Patch Installer version 11.1.0.6.3
Copyright (c) 2007, Oracle Corporation. All rights reserved.

UTIL session

Oracle Home : /u01/app/oracle/product/11.1.0/db_2
Central Inventory : /u01/app/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.1.0.6.3
OUI version : 11.1.0.6.0
OUI location : /u01/app/oracle/product/11.1.0/db_2/oui
Log file location : /u01/app/oracle/product/11.1.0/db_2/cfgtoollogs/opatch/opatch2008-09-11_14-07-43PM.log

Invoking utility “napply”
Checking conflict among patches…
Checking if Oracle Home has components required by patches…
Skip patch 7155439 from list of patches to apply: This patch is not needed.
Checking skip_duplicate
Checking skip_subset
Checking conflicts against Oracle Home…
OPatch continues with these patches: 6650132 6650135 6731395 6871274 6871276 7150417 7155429 7155447 7155449 7155452 7155459 7155461 7155463 7155464 7158307

Do you want to proceed? [y|n]
y
User Responded with: Y

Running prerequisite checks…

You selected -local option, hence OPatch will patch the local system only.

Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = ‘/u01/app/oracle/product/11.1.0/db_2′)

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files affected by the patch ‘NApply’ for restore. This might take a while…

***************************************************************************
Oracle Configuration Manager (OCM) is included with this release of OPatch.
***************************************************************************

The Oracle Configuration Manager is installed previously in the Oracle Home.

Removing previously installed Oracle Configuration Manager from the Oracle Home…
Uninstalling Oracle Configuration Manager…

OPatch will now install the Oracle Configuration Manager in the Oracle Home.

OPatch has successfully installed Oracle Configuration Manager in the Oracle Home.
Please refer log file for details.

OPatch will now configure the Oracle Configuration Manager in the Oracle Home.

Configuring Oracle Configuration Manager. This may take a while…

Oracle Configuration Manager enables Oracle to provide superior, proactive
support for our customers. Oracle strongly recommends customers configure OCM.

ORACLE CONNECTION TOOLS AGREEMENT
Introduction
————
Customer (hereafter “Customer” or “you”) agrees to the following conditions
regarding the use of Oracle’s live connection tools, including Oracle
Configuration Manager and Remote Diagnostic Agent, that you have either
downloaded from Oracle’s technical support web site or which you received from
Oracle, and the related services that Oracle provides in connection with those
tools (collectively, the “Tools”). The individual entering into this
agreement certifies that he/she has authority to enter into this agreement on
Customer’s behalf.
These terms supplement the terms of your licensing or distribution agreement
with Oracle and the terms of Oracle’s Technical Support Policies. In the
event of a conflict between your licensing or distribution agreement or the
Technical Support Policies and the following terms, the relevant terms below
will control your use of the Tools.
Description of Tools: What Data is Collected and How it is Stored and Used
————

[Press enter to continue, 'q' to stop displaying the license agreement.] q

I accept the License Agreement (Y/N) [Y]: n

OPatch failed to configure Oracle Configuration Manager in the Oracle Home. Please refer log file
for details.

*******************************************************************************************
Oracle Configuration Manager is installed but not configured. OCM enables Oracle to
provide superior, proactive support for our customers. Oracle strongly recommends customers
configure OCM. To complete the configuration of OCM, refer to the OCM Installation and
Administration Guide (http://www.oracle.com/technology/documentation/ocm.html).
*******************************************************************************************

Execution of ‘sh /home/oracle/Desktop/7150417/6731395/custom/scripts/pre -apply 6731395 ‘:

Return Code = 0
Execution of ‘sh /home/oracle/Desktop/7150417/7150417/custom/scripts/pre -apply 7150417 ‘:

Return Code = 0

Applying patch 6650132…

ApplySession applying interim patch ’6650132′ to OH ‘/u01/app/oracle/product/11.1.0/db_2′
Backing up files affected by the patch ’6650132′ for rollback. This might take a while…

Patching component oracle.rdbms, 11.1.0.6.0…
Updating archive file “/u01/app/oracle/product/11.1.0/db_2/lib/libserver11.a” with “lib/libserver11.a/kzia.o”
ApplySession adding interim patch ’6650132′ to inventory

Verifying the update…
Inventory check OK: Patch ID 6650132 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 6650132 are present in Oracle Home.

Applying patch 6650135…

ApplySession applying interim patch ’6650135′ to OH ‘/u01/app/oracle/product/11.1.0/db_2′
Backing up files affected by the patch ’6650135′ for rollback. This might take a while…

Patching component oracle.rdbms, 11.1.0.6.0…
Updating archive file “/u01/app/oracle/product/11.1.0/db_2/lib/libserver11.a” with “lib/libserver11.a/kzsr.o”
Updating archive file “/u01/app/oracle/product/11.1.0/db_2/lib/libserver11.a” with “lib/libserver11.a/kzu.o”
ApplySession adding interim patch ’6650135′ to inventory

Verifying the update…
Inventory check OK: Patch ID 6650135 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 6650135 are present in Oracle Home.

Applying patch 6731395…

ApplySession applying interim patch ’6731395′ to OH ‘/u01/app/oracle/product/11.1.0/db_2′
Backing up files affected by the patch ’6731395′ for rollback. This might take a while…

Patching component oracle.rdbms, 11.1.0.6.0…
Copying file to “/u01/app/oracle/product/11.1.0/db_2/cpu/view_recompile/recompile_precheck_jan2008cpu.sql”
Copying file to “/u01/app/oracle/product/11.1.0/db_2/cpu/view_recompile/view_recompile_jan2008cpu.sql”
ApplySession adding interim patch ’6731395′ to inventory

Verifying the update…
Inventory check OK: Patch ID 6731395 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 6731395 are present in Oracle Home.

Applying patch 6871274…

ApplySession applying interim patch ’6871274′ to OH ‘/u01/app/oracle/product/11.1.0/db_2′
Backing up files affected by the patch ’6871274′ for rollback. This might take a while…

Patching component oracle.sdo.locator, 11.1.0.6.0…
Copying file to “/u01/app/oracle/product/11.1.0/db_2/md/admin/prvtpidx.plb”
ApplySession adding interim patch ’6871274′ to inventory

Verifying the update…
Inventory check OK: Patch ID 6871274 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 6871274 are present in Oracle Home.

Applying patch 6871276…

ApplySession applying interim patch ’6871276′ to OH ‘/u01/app/oracle/product/11.1.0/db_2′
Backing up files affected by the patch ’6871276′ for rollback. This might take a while…

Patching component oracle.rdbms, 11.1.0.6.0…
Updating jar file “/u01/app/oracle/product/11.1.0/db_2/rdbms/jlib/CDC.jar” with “/rdbms/jlib/CDC.jar/oracle/CDC/AdvanceChangeSet.class”
ApplySession adding interim patch ’6871276′ to inventory

Verifying the update…
Inventory check OK: Patch ID 6871276 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 6871276 are present in Oracle Home.

Applying patch 7150417…

ApplySession applying interim patch ’7150417′ to OH ‘/u01/app/oracle/product/11.1.0/db_2′
Backing up files affected by the patch ’7150417′ for rollback. This might take a while…

Patching component oracle.rdbms, 11.1.0.6.0…
Copying file to “/u01/app/oracle/product/11.1.0/db_2/cpu/CPUJul2008/catcpu.sql”
Copying file to “/u01/app/oracle/product/11.1.0/db_2/cpu/CPUJul2008/catcpu_rollback.sql”
Copying file to “/u01/app/oracle/product/11.1.0/db_2/cpu/CPUJul2008/rollback_all.lst”
Copying file to “/u01/app/oracle/product/11.1.0/db_2/cpu/CPUJul2008/rollback_new.lst”
Copying file to “/u01/app/oracle/product/11.1.0/db_2/rdbms/admin/bundledata_CPU.xml”
Copying file to “/u01/app/oracle/product/11.1.0/db_2/rdbms/admin/catbundle.sql”
Copying file to “/u01/app/oracle/product/11.1.0/db_2/cpu/scripts/sdo_jul2008.sql”
ApplySession adding interim patch ’7150417′ to inventory

Verifying the update…
Inventory check OK: Patch ID 7150417 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 7150417 are present in Oracle Home.

Applying patch 7155429…

ApplySession applying interim patch ’7155429′ to OH ‘/u01/app/oracle/product/11.1.0/db_2′
Backing up files affected by the patch ’7155429′ for rollback. This might take a while…

Patching component oracle.rdbms, 11.1.0.6.0…
Copying file to “/u01/app/oracle/product/11.1.0/db_2/rdbms/lib/env_rdbms.mk”
ApplySession adding interim patch ’7155429′ to inventory

Verifying the update…
Inventory check OK: Patch ID 7155429 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 7155429 are present in Oracle Home.

Applying patch 7155447…

ApplySession applying interim patch ’7155447′ to OH ‘/u01/app/oracle/product/11.1.0/db_2′
Backing up files affected by the patch ’7155447′ for rollback. This might take a while…

Patching component oracle.rdbms, 11.1.0.6.0…
Copying file to “/u01/app/oracle/product/11.1.0/db_2/rdbms/admin/prvtdefr.plb”
ApplySession adding interim patch ’7155447′ to inventory

Verifying the update…
Inventory check OK: Patch ID 7155447 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 7155447 are present in Oracle Home.

Applying patch 7155449…

ApplySession applying interim patch ’7155449′ to OH ‘/u01/app/oracle/product/11.1.0/db_2′
Backing up files affected by the patch ’7155449′ for rollback. This might take a while…

Patching component oracle.rdbms, 11.1.0.6.0…
Updating archive file “/u01/app/oracle/product/11.1.0/db_2/lib/libserver11.a” with “lib/libserver11.a/kupp.o”
Copying file to “/u01/app/oracle/product/11.1.0/db_2/rdbms/admin/prvtbpp.plb”
ApplySession adding interim patch ’7155449′ to inventory

Verifying the update…
Inventory check OK: Patch ID 7155449 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 7155449 are present in Oracle Home.

Applying patch 7155452…

ApplySession applying interim patch ’7155452′ to OH ‘/u01/app/oracle/product/11.1.0/db_2′
Backing up files affected by the patch ’7155452′ for rollback. This might take a while…

Patching component oracle.rdbms, 11.1.0.6.0…
Copying file to “/u01/app/oracle/product/11.1.0/db_2/rdbms/admin/catdef.sql”
ApplySession adding interim patch ’7155452′ to inventory

Verifying the update…
Inventory check OK: Patch ID 7155452 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 7155452 are present in Oracle Home.

Applying patch 7155459…

ApplySession applying interim patch ’7155459′ to OH ‘/u01/app/oracle/product/11.1.0/db_2′
Backing up files affected by the patch ’7155459′ for rollback. This might take a while…

Patching component oracle.rdbms, 11.1.0.6.0…
Updating archive file “/u01/app/oracle/product/11.1.0/db_2/lib/libserver11.a” with “lib/libserver11.a/ctc.o”
ApplySession adding interim patch ’7155459′ to inventory

Verifying the update…
Inventory check OK: Patch ID 7155459 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 7155459 are present in Oracle Home.

Applying patch 7155461…

ApplySession applying interim patch ’7155461′ to OH ‘/u01/app/oracle/product/11.1.0/db_2′
Backing up files affected by the patch ’7155461′ for rollback. This might take a while…

Patching component oracle.rdbms, 11.1.0.6.0…
Updating archive file “/u01/app/oracle/product/11.1.0/db_2/lib/libserver11.a” with “lib/libserver11.a/kwqn.o”
ApplySession adding interim patch ’7155461′ to inventory

Verifying the update…
Inventory check OK: Patch ID 7155461 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 7155461 are present in Oracle Home.

Applying patch 7155463…

ApplySession applying interim patch ’7155463′ to OH ‘/u01/app/oracle/product/11.1.0/db_2′
Backing up files affected by the patch ’7155463′ for rollback. This might take a while…

Patching component oracle.sdo.locator, 11.1.0.6.0…
Updating jar file “/u01/app/oracle/product/11.1.0/db_2/md/jlib/sdotopo.jar” with “/md/jlib/sdotopo.jar/oracle/spatial/topo/TopoMap.class”
Updating jar file “/u01/app/oracle/product/11.1.0/db_2/md/jlib/sdotopo.jar” with “/md/jlib/sdotopo.jar/oracle/spatial/topo/TopoMap$AlongEdgeComparator.class”
Updating jar file “/u01/app/oracle/product/11.1.0/db_2/md/jlib/sdotopo.jar” with “/md/jlib/sdotopo.jar/oracle/spatial/topo/TopoMap$AlongSegmentComparator.class”
Updating jar file “/u01/app/oracle/product/11.1.0/db_2/md/jlib/sdotopo.jar” with “/md/jlib/sdotopo.jar/oracle/spatial/topo/TopoMap$EdgeSplit.class”
Updating jar file “/u01/app/oracle/product/11.1.0/db_2/md/jlib/sdotopo.jar” with “/md/jlib/sdotopo.jar/oracle/spatial/topo/TopoMap$Int.class”
Updating jar file “/u01/app/oracle/product/11.1.0/db_2/md/jlib/sdotopo.jar” with “/md/jlib/sdotopo.jar/oracle/spatial/topo/TopoMap$PointStackEntry.class”
Updating jar file “/u01/app/oracle/product/11.1.0/db_2/md/jlib/sdotopo.jar” with “/md/jlib/sdotopo.jar/oracle/spatial/topo/TopoMap$SmartPoint.class”
ApplySession adding interim patch ’7155463′ to inventory

Verifying the update…
Inventory check OK: Patch ID 7155463 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 7155463 are present in Oracle Home.

Applying patch 7155464…

ApplySession applying interim patch ’7155464′ to OH ‘/u01/app/oracle/product/11.1.0/db_2′
Backing up files affected by the patch ’7155464′ for rollback. This might take a while…

Patching component oracle.sysman.plugin.db.main.oms, 11.1.0.5.0…
Updating jar file “/u01/app/oracle/product/11.1.0/db_2/sysman/jlib/emDB.jar” with “/sysman/jlib/emDB.jar/oracle/sysman/db/rsc/inst/RsrcMonitorMsg.class”
Updating jar file “/u01/app/oracle/product/11.1.0/db_2/sysman/jlib/emDB.jar” with “/sysman/jlib/emDB.jar/oracle/sysman/db/rsc/inst/RsrcMonitorMsgID.class”
Updating jar file “/u01/app/oracle/product/11.1.0/db_2/sysman/jlib/emDB.jar” with “/sysman/jlib/emDB.jar/oracle/sysman/db/adm/inst/rsrcmgr/RsrcmgrMonController.class”
Updating jar file “/u01/app/oracle/product/11.1.0/db_2/sysman/jlib/emdb_emjsp.jar” with “/sysman/jlib/emdb_emjsp.jar/_database/_instance/_sitemap/_healthgif.class”
ApplySession adding interim patch ’7155464′ to inventory

Verifying the update…
Inventory check OK: Patch ID 7155464 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 7155464 are present in Oracle Home.

Applying patch 7158307…

ApplySession applying interim patch ’7158307′ to OH ‘/u01/app/oracle/product/11.1.0/db_2′
Backing up files affected by the patch ’7158307′ for rollback. This might take a while…

Patching component oracle.sysman.plugin.db.main.oms, 11.1.0.5.0…
Updating jar file “/u01/app/oracle/product/11.1.0/db_2/sysman/jlib/emDB.jar” with “/sysman/jlib/emDB.jar/oracle/sysman/db/adm/inst/MemoryController.class”
Updating jar file “/u01/app/oracle/product/11.1.0/db_2/sysman/jlib/emDB.jar” with “/sysman/jlib/emDB.jar/oracle/sysman/db/adm/inst/SessionDetailsController.class”
Updating jar file “/u01/app/oracle/product/11.1.0/db_2/sysman/jlib/emDB.jar” with “/sysman/jlib/emDB.jar/oracle/sysman/db/adm/inst/SitemapController.class”
Updating jar file “/u01/app/oracle/product/11.1.0/db_2/sysman/jlib/emDB.jar” with “/sysman/jlib/emDB.jar/oracle/sysman/emo/perf/bean/sesn/SessionDetails.class”
Updating jar file “/u01/app/oracle/product/11.1.0/db_2/sysman/jlib/emdb_emjsp.jar” with “/sysman/jlib/emdb_emjsp.jar/_database/_instance/_sitemap/_health.class”
ApplySession adding interim patch ’7158307′ to inventory

Verifying the update…
Inventory check OK: Patch ID 7158307 is registered in Oracle Home inventory with proper meta-data.
Files check OK: Files from Patch ID 7158307 are present in Oracle Home.
Running make for target ioracle
Running make for target iextjob
Running make for target iextjobo

——————————————————————————–
********************************************************************************
********************************************************************************
** ATTENTION **
** **
** Please note that the Security Patch Installation (Patch Deinstallation) is **
** not complete until all the Post Installation (Post Deinstallation) **
** instructions noted in the Readme accompanying this patch, have been **
** successfully completed. **
** **
********************************************************************************
********************************************************************************

——————————————————————————–

Execution of ‘sh /home/oracle/Desktop/7150417/7150417/custom/scripts/post -apply 7150417 ‘:

Return Code = 0

The local system has been patched and can be restarted.

UtilSession: N-Apply done.
——————————————————————————–
The following warnings have occurred during OPatch execution:
1) OUI-67293:
OPatch failed to configure Oracle Configuration Manager in the Oracle Home. Please refer log file
for details.

2) OUI-67294:
*******************************************************************************************
Oracle Configuration Manager is installed but not configured. OCM enables Oracle to
provide superior, proactive support for our customers. Oracle strongly recommends customers
configure OCM. To complete the configuration of OCM, refer to the OCM Installation and
Administration Guide (http://www.oracle.com/technology/documentation/ocm.html).
*******************************************************************************************

——————————————————————————–
OPatch Session completed with warnings.

OPatch completed with warnings.

• Check whether patch has been applied and registered in inventory or not.

[oracle@quartz 7150417]$ /u01/app/oracle/product/11.1.0/db_2/OPatch/opatch lsinv
Invoking OPatch 11.1.0.6.3

Oracle Interim Patch Installer version 11.1.0.6.3
Copyright (c) 2007, Oracle Corporation. All rights reserved.

Oracle Home : /u01/app/oracle/product/11.1.0/db_2
Central Inventory : /u01/app/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 11.1.0.6.3
OUI version : 11.1.0.6.0
OUI location : /u01/app/oracle/product/11.1.0/db_2/oui
Log file location : /u01/app/oracle/product/11.1.0/db_2/cfgtoollogs/opatch/opatch2008-09-11_14-26-45PM.log

Lsinventory Output file location : /u01/app/oracle/product/11.1.0/db_2/cfgtoollogs/opatch/lsinv/lsinventory2008-09-11_14-26-45PM.txt

——————————————————————————–
Installed Top-level Products (1):

Oracle Database 11g 11.1.0.6.0
There are 1 products installed in this Oracle Home.

Interim patches (15) :

Patch 7158307 : applied on Thu Sep 11 14:15:18 BST 2008
Created on 22 Jun 2008, 01:14:25 hrs PST8PDT
Bugs fixed:
7158307

Patch 7155464 : applied on Thu Sep 11 14:14:03 BST 2008
Created on 22 Jun 2008, 01:14:05 hrs PST8PDT
Bugs fixed:
7155464

Patch 7155463 : applied on Thu Sep 11 14:13:02 BST 2008
Created on 22 Jun 2008, 01:13:43 hrs PST8PDT
Bugs fixed:
7155463

Patch 7155461 : applied on Thu Sep 11 14:12:54 BST 2008
Created on 22 Jun 2008, 01:13:22 hrs PST8PDT
Bugs fixed:
7155461

Patch 7155459 : applied on Thu Sep 11 14:12:28 BST 2008
Created on 22 Jun 2008, 01:12:56 hrs PST8PDT
Bugs fixed:
7155459

Patch 7155452 : applied on Thu Sep 11 14:12:11 BST 2008
Created on 22 Jun 2008, 01:09:57 hrs PST8PDT
Bugs fixed:
7155452

Patch 7155449 : applied on Thu Sep 11 14:12:06 BST 2008
Created on 22 Jun 2008, 01:09:32 hrs PST8PDT
Bugs fixed:
7155449

Patch 7155447 : applied on Thu Sep 11 14:11:47 BST 2008
Created on 22 Jun 2008, 01:02:51 hrs PST8PDT
Bugs fixed:
7155447

Patch 7155429 : applied on Thu Sep 11 14:11:45 BST 2008
Created on 22 Jun 2008, 00:59:22 hrs PST8PDT
Bugs fixed:
7155429

Patch 7150417 : applied on Thu Sep 11 14:11:42 BST 2008
Created on 22 Jun 2008, 00:47:12 hrs PST8PDT
Bugs fixed:
7150417

Patch 6871276 : applied on Thu Sep 11 14:11:38 BST 2008
Created on 13 Mar 2008, 23:27:15 hrs PST8PDT
Bugs fixed:
6871276

Patch 6871274 : applied on Thu Sep 11 14:11:34 BST 2008
Created on 13 Mar 2008, 23:26:15 hrs PST8PDT
Bugs fixed:
6871274

Patch 6731395 : applied on Thu Sep 11 14:11:31 BST 2008
Created on 14 Mar 2008, 00:12:44 hrs PST8PDT
Bugs fixed:
6731395

Patch 6650135 : applied on Thu Sep 11 14:11:25 BST 2008
Created on 13 Mar 2008, 23:58:36 hrs PST8PDT
Bugs fixed:
6650135

Patch 6650132 : applied on Thu Sep 11 14:10:37 BST 2008
Created on 14 Mar 2008, 00:00:24 hrs PST8PDT
Bugs fixed:
6650132

——————————————————————————–

OPatch succeeded.

This article only explains about the how to patch Oracle Home and if you have DB also created then you need to follow post installation steps as per readme.

• Create a Failure

Before we can start identifying and repairing failures, we need to create one. Probably the easiest way to do this is to corrupt a datafile using the “echo” command. The following script navigates to the directory holding the datafiles, checks the current size of the users01.dbf file, echos nothing to it, then checks the file size once more.

$ cd /u01/app/oracle/oradata/DB11G
$ ls -l users01.dbf
-rw-r—– 1 oracle oinstall 57745408 Jan  3 11:42 users01.dbf
$ echo > users01.dbf
$ ls -l users01.dbf
-rw-r—– 1 oracle oinstall 1 Jan  3 13:26 users01.dbf

You can see the file size has been reduced to a single byte.

Next, we connect to the database and try to create a table in the USERS tablespace.

SQL> CREATE TABLE test_tab (id NUMBER) TABLESPACE USERS;
CREATE TABLE test_tab (id NUMBER) TABLESPACE USERS
*
ERROR at line 1:
ORA-01115: IO error reading block from file 4 (block # 3)
ORA-01110: data file 4: ‘/u01/app/oracle/oradata/DB11G/users01.dbf’
ORA-27072: File I/O error
Additional information: 4
Additional information: 2

• Health Monitor (DBMS_HM)

In the previous example the failure was detected and logged automatically in reaction to the table creation failure. If this were a low use tablespace, we might not have noticed the problem for some time. The Health Monitor also allows us to perform the same integrity checks manually, rather than waiting for the reactive tests to take place. This may help you identify and fix problems before they are ever noticed by users.

Access to Health Monitor is available using the DBMS_HM package or Enterprise Manager (see below). The available checks are displayed using the V$HM_CHECK view.

SQL> SELECT name FROM v$hm_check WHERE internal_check=’N';

NAME
———————————-
DB Structure Integrity Check
Data Block Integrity Check
Redo Integrity Check
Transaction Integrity Check
Undo Segment Integrity Check
Dictionary Integrity Check

6 rows selected.

• The DBMS_HM.RUN_CHECK procedure is used to run a specific check with the appropriate parameters.

BEGIN
DBMS_HM.run_check (
check_name   => ‘DB Structure Integrity Check’,
run_name     => ‘my_test_run’);
END;
/

PL/SQL procedure successfully completed.

he example below shows the test output of the DBMS_HM.GET_RUN_REPORT function.
SET LONG 100000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 1000
SET LINESIZE 512
SELECT DBMS_HM.get_run_report(‘MY_TEST_RUN’) FROM dual;

DBMS_HM.GET_RUN_REPORT(‘MY_TEST_RUN’)
——————————————————————————
Basic Run Information
Run Name                     : my_test_run
Run Id                       : 330
Check Name                   : DB Structure Integrity Check
Mode                         : MANUAL
Status                       : COMPLETED
Start Time                   : 2008-01-04 11:30:27.293105 +00:00
End Time                     : 2008-01-04 11:30:27.345898 +00:00
Error Encountered            : 0
Source Incident Id           : 0
Number of Incidents Created  : 0

Input Parameters for the Run
Run Findings And Recommendations
Finding
Finding Name  : Corrupt Datafile
Finding ID    : 334
Type          : FAILURE
Status        : OPEN
Priority      : HIGH
Message       : Datafile 4: ‘/u01/app/oracle/oradata/DB11G/users01.dbf’ is corrupt
Message       : Some objects in tablespace USERS might be unavailable

• The ADR Command Interpreter (ADRCI) utility is initiated by issuing the “adrci” command on the command line.

$ adrci

ADRCI: Release 11.1.0.6.0 – Beta on Fri Jan 4 12:01:20 2008

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

ADR base = “/u01/app/oracle”
adrci>
Health Monitor runs are displayed using the following command.
adrci> show hm_run
The relevant run name is then used to produce a report using the following commands.
adrci> set homepath diag/rdbms/db11g/DB11G
adrci> create report hm_run my_test_run
adrci> show report hm_run my_test_run
<?xml version=”1.0″ encoding=”US-ASCII”?>
<HM-REPORT REPORT_ID=”my_test_run”>
<TITLE>HM Report: my_test_run</TITLE>
<RUN_INFO>
<CHECK_NAME>DB Structure Integrity Check</CHECK_NAME>
<RUN_ID>330</RUN_ID>
<RUN_NAME>my_test_run</RUN_NAME>
<RUN_MODE>MANUAL</RUN_MODE>
<RUN_STATUS>COMPLETED</RUN_STATUS>
<RUN_ERROR_NUM>0</RUN_ERROR_NUM>
<SOURCE_INCIDENT_ID>0</SOURCE_INCIDENT_ID>
<NUM_INCIDENTS_CREATED>0</NUM_INCIDENTS_CREATED>
<RUN_START_TIME>2008-01-04 11:30:27.293105 +00:00</RUN_START_TIME>
<RUN_END_TIME>2008-01-04 11:30:27.345898 +00:00</RUN_END_TIME>
</RUN_INFO>
<RUN_PARAMETERS/>
<RUN-FINDINGS>
<FINDING>
<FINDING_NAME>Corrupt Datafile</FINDING_NAME>
<FINDING_ID>334</FINDING_ID>
<FINDING_TYPE>FAILURE</FINDING_TYPE>
<FINDING_STATUS>OPEN</FINDING_STATUS>
<FINDING_PRIORITY>HIGH</FINDING_PRIORITY>
<FINDING_CHILD_COUNT>0</FINDING_CHILD_COUNT>
<FINDING_CREATION_TIME>2008-01-04 11:30:27.341374 +00:00</FINDING_CREATION_TIME>
<FINDING_MESSAGE>Datafile 4: ‘/u01/app/oracle/oradata/DB11G/users01.dbf’ is corrupt</FINDING_MESSAGE>
<FINDING_MESSAGE>Some objects in tablespace USERS might be unavailable</FINDING_MESSAGE>
</FINDING>
</RUN-FINDINGS>
</HM-REPORT>
adrci>

You already know what undo data is used for. When a transaction changes a block, the past image of the block is kept it the undo segments. The data is kept there even if the transaction is committed because some long running query that started before the block is changed can ask for the block that was changed and committed. This query should get the past image of the block—the pre-commit image, not the current one. Therefore undo data is kept undo segments even after the commit. The data is flushed out of the undo segment in course of time, to make room for the newly inserted undo data.

When the RMAN backup runs, it backs up all the data from the undo tablespace. But during recovery, the undo data related to committed transactions are no longer needed, since they are already in the redo log streams, or even in the datafiles (provided the dirty blocks have been cleaned out from buffer and written to the disk) and can be recovered from there. So, why bother backing up the committed undo data?

In Oracle Database 11g, RMAN does the smart thing: it bypasses backing up the committed undo data that is not required in recovery. The uncommitted undo data that is important for recovery is backed up as usual. This reduces the size and time of the backup (and the recovery as well).

In many databases, especially OLTP ones where the transaction are committed more frequently and the undo data stays longer in the undo segments, most of the undo data is actually committed. Thus RMAN has to backup only a few blocks from the undo tablespaces.

The best part is that you needn’t do anything to achieve this optimization; Oracle does it by itself.

dev@lala:/u01/database/dev$ sqlplus / as sysdba

SQL*Plus: Release 11.1.0.6.0 – Production on Wed Sep 3 16:02:22 2008

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

Connected to:
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create table test (col1 number) tablespace users;
create table test (col1 number) tablespace users
*
ERROR at line 1:
ORA-01116: error in opening database file 4
ORA-01110: data file 4: ‘/u01/database/dev/users01.dbf’
ORA-27041: unable to open file
Linux Error: 2: No such file or directory
Additional information: 3

SQL> quit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 – Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
dev@lala:/u01/database/dev$ rman target /

Recovery Manager: Release 11.1.0.6.0 – Production on Wed Sep 3 16:02:55 2008

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

connected to target database: DEV (DBID=3742639916)

RMAN> list failure;

using target database control file instead of recovery catalog
List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
182        HIGH     OPEN      03-SEP-08     One or more non-system datafiles are missing

RMAN> list failure 182 detail;

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

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
182        HIGH     OPEN      03-SEP-08     One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 182
Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
185        HIGH     OPEN      03-SEP-08     Datafile 4: ‘/u01/database/dev/users01.dbf’ is missing
Impact: Some objects in tablespace USERS might be unavailable

RMAN> advise failure;

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

Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
182        HIGH     OPEN      03-SEP-08     One or more non-system datafiles are missing
Impact: See impact for individual child failures
List of child failures for parent failure ID 182
Failure ID Priority Status    Time Detected Summary
———- ——– ——— ————- ——-
185        HIGH     OPEN      03-SEP-08     Datafile 4: ‘/u01/database/dev/users01.dbf’ is missing
Impact: Some objects in tablespace USERS might be unavailable

analyzing automatic repair options; this may take some time
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=133 device type=DISK
analyzing automatic repair options complete

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

Optional Manual Actions
=======================
1. If file /u01/database/dev/users01.dbf was unintentionally renamed or moved, restore it

Automated Repair Options
========================
Option Repair Description
—— ——————
1      Restore and recover datafile 4
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/dev/dev/hm/reco_3070598829.hm

RMAN> repair failure preview;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/dev/dev/hm/reco_3070598829.hm

contents of repair script:
# restore and recover datafile
sql ‘alter database datafile 4 offline’;
restore datafile 4;
recover datafile 4;
sql ‘alter database datafile 4 online’;

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/dev/dev/hm/reco_3070598829.hm

contents of repair script:
# restore and recover datafile
sql ‘alter database datafile 4 offline’;
restore datafile 4;
recover datafile 4;
sql ‘alter database datafile 4 online’;

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

sql statement: alter database datafile 4 offline

Starting restore at 03-SEP-08
using channel ORA_DISK_1

channel ORA_DISK_1: restoring datafile 00004
input datafile copy RECID=6 STAMP=664368067 file name=/u01/app/oracle/flash_recovery_area/DEV/datafile/o1_mf_users_4ct2klrs_.dbf
destination for restore of datafile 00004: /u01/database/dev/users01.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00004
output file name=/u01/database/dev/users01.dbf RECID=0 STAMP=0
Finished restore at 03-SEP-08

Starting recover at 03-SEP-08
using channel ORA_DISK_1

starting media recovery

archived log for thread 1 with sequence 43 is already on disk as file /u01/app/oracle/flash_recovery_area/DEV/archivelog/2008_09_02/o1_mf_1_43_4cvbgbo7_.arc
archived log for thread 1 with sequence 44 is already on disk as file /u01/app/oracle/flash_recovery_area/DEV/archivelog/2008_09_03/o1_mf_1_44_4cw37lw7_.arc
archived log for thread 1 with sequence 45 is already on disk as file /u01/app/oracle/flash_recovery_area/DEV/archivelog/2008_09_03/o1_mf_1_45_4cx9ophk_.arc
archived log file name=/u01/app/oracle/flash_recovery_area/DEV/archivelog/2008_09_02/o1_mf_1_43_4cvbgbo7_.arc thread=1 sequence=43
media recovery complete, elapsed time: 00:00:02
Finished recover at 03-SEP-08

sql statement: alter database datafile 4 online
repair failure complete

RMAN>

Active database Duplication using RMAN

===========================

Starting from 11g database can be duplicated with having prior copy of the database backup on the destination. Prior to 11g it requires source database, a copy of a backup on destination and destination db.

Beginning with 11g you can use the RMAN or Enterprise manager to create a duplicate database online. This feature instructs the source database to perform online image copies and archived log copies directly to the target instance. Preexisting backups are not required.

RMAN > DUPLICATE TARGET DATABASE
TO db_duplicate
FROM ACTIVE DATABASE
SPFILE PARAMETER_VALUE_CONVERT ‘/u02′, ‘u03′
SET SGA_MAX_SIXE = ’500m’
SET SGA_TARGET = ’250M’
SET LOG FILE_NAME_CONVERT = ‘/u02′, ‘u03′
DB_FILE_NAME_CONVERT = ‘/u02′, ‘u03′;

Using above command the target database is duplicated to database “db_duplicate” and the database file locations are changes from /u02 to /u03. Make sure that the /u03 partition is already existing on the OS side.

ASM Fast Mirror Resync

================

- In 10g ASm assumes that an offline disk only contains stale data and reads no data from such disks as a result disk is put offline. After this ASM drops the disk from group and recreates all the extents using the mirror copy which is fairly time consuming process and may take hours.
- ASM fast mirror resync significantly reduces the time required to resync a transient failure of any disk. With this feature when disk goes offline ASM track all the changes to the extents during the offline time and when the disk comes online ASM quickly resync ONLY the extents that were affected during the offline period.

- DISK_REPAIR_TIME is the attribute that needs to be set corresponding to the disk group> This determines the duration of disk outage that ASM instance will tolerate being able to resync.

How to setup ASM fast mirror resync:

1. Use ALTER DISKGROUP to set the DISK_REPAIR_TIME attribute.

ALTER DISKGROUP diskgrp1 SET ATTRIBUTE ‘disk_repair_time’ = ’3.5h’
or
ALTER DISKGROUP dg01 SET ATTRIBUTE ‘disk_repair_time’ = ’210m’

2. Use ALTER DISKGROUP DISK ONLINE statement to bring the disk online.

For Example.

ALTER DISKGROUP diskgrp1 OFFLINE DISK diskgrp_1 DROP AFTER 10m;

This will take disk diskgrp_1 offline and drops it after 10 minutes.

3. Can refer to V$ASM_OPERATION view while running any of ALTER DISKGROUP commands as it displays name and current state of operation you are performing.

ASM Scalability

- 63 Disk groups
- 10,000 ASM Disks
- 4 Petabytes per ASM disk
- 40 Exabytes of storage
- 1 Million files per disk group
- Maximum filesize
External reduntancy : 140 PB
Nornal reduntancy : 42 PB
High reduntancy : 15 PB