warning: overriding commands for target `nmosudo' during Oracle Patch

When applying Oracle PSU 11.2.0.4.1 (p17478514_112040_Linux-x86-64.zip), you will see below Warning message. Don’t be panic as this is expected warning and it is safe to ignore as per readme.txt

OPatch found the word "warning" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
ins_emagent.mk:113: warning: overriding commands for target `nmosudo'
ins_emagent.mk:52: warning: ignoring old commands for target `nmosudo'
/home/oracle/product/11.2.0.4/dbhome_1/sysman/lib/ins_emagent.mk:113: warning: overriding commands for target `nmosudo'
/home/oracle/product/11.2.0.4/dbhome_1/sysman/lib/ins_emagent.mk:52: warning: ignoring old commands for target `nmosudo' 

OPatch output for your reference
oracle$opatch apply
Oracle Interim Patch Installer version 11.2.0.3.6
Copyright (c) 2013, Oracle Corporation. All rights reserved.
Oracle Home : /home/oracle/product/11.2.0.4/dbhome_1
Central Inventory : /u01/home/oracle/oraInventory
from : /home/oracle/product/11.2.0.4/dbhome_1/oraInst.loc
OPatch version : 11.2.0.3.6
OUI version : 11.2.0.4.0
Log file location : /home/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/opatch/17478514_Feb_12_2014_15_28_16/apply2014-02-12_15-28-16PM_1.log

Applying interim patch '17478514' to OH '/home/oracle/product/11.2.0.4/dbhome_1'
Verifying environment and performing prerequisite checks...
All checks passed.
Provide your email address to be informed of security issues, install and
initiate Oracle Configuration Manager. Easier for you if you use your My
Oracle Support Email address/User Name.
Visit http://www.oracle.com/support/policies.html for details.
Email address/User Name:
You have not provided an email address for notification of security issues.
Do you wish to remain uninformed of security issues ([Y]es, [N]o) [N]: Y
Please shutdown Oracle instances running out of this ORACLE_HOME on the local system.
(Oracle Home = '/home/oracle/product/11.2.0.4/dbhome_1')

Is the local system ready for patching? [y|n]
y
User Responded with: Y
Backing up files...

Patching component oracle.rdbms, 11.2.0.4.0...
Patching component oracle.rdbms.rsf, 11.2.0.4.0...
Patching component oracle.sdo, 11.2.0.4.0...
Patching component oracle.sysman.agent, 10.2.0.4.5...
Patching component oracle.xdk, 11.2.0.4.0...
Patching component oracle.rdbms.dbscripts, 11.2.0.4.0...
Patching component oracle.sdo.locator, 11.2.0.4.0...
Patching component oracle.nlsrtl.rsf, 11.2.0.4.0...
Patching component oracle.xdk.rsf, 11.2.0.4.0...
Patching component oracle.rdbms.rman, 11.2.0.4.0...

OPatch found the word "warning" in the stderr of the make command.
Please look at this stderr. You can re-run this make command.
Stderr output:
ins_emagent.mk:113: warning: overriding commands for target `nmosudo'
ins_emagent.mk:52: warning: ignoring old commands for target `nmosudo'
/home/oracle/product/11.2.0.4/dbhome_1/sysman/lib/ins_emagent.mk:113: warning: overriding commands for target `nmosudo'
/home/oracle/product/11.2.0.4/dbhome_1/sysman/lib/ins_emagent.mk:52: warning: ignoring old commands for target `nmosudo'
Verifying the update...
Patch 17478514 successfully applied
OPatch Session completed with warnings.
Log file location: /home/oracle/product/11.2.0.4/dbhome_1/cfgtoollogs/opatch/17478514_Feb_12_2014_15_28_16/apply2014-02-12_15-28-16PM_1.log

Other known issues in PSU 11.2.0.4.1
1. while running the catbundle.sql script or its rollback script you may see below errors and these are ignorable errors as per readme.txt

RA-29809: cannot drop an operator with dependent objects
ORA-29931: specified association does not exist
ORA-29830: operator does not exist
ORA-00942: table or view does not exist
ORA-00955: name is already used by an existing object
ORA-01430: column being added already exists in table
ORA-01432: public synonym to be dropped does not exist
ORA-01434: private synonym to be dropped does not exist
ORA-01435: user does not exist
ORA-01917: user or role 'XDB' does not exist
ORA-01920: user name '<user-name>' conflicts with another user or role name
ORA-01921: role name '<role name>' conflicts with another user or role name
ORA-01952: system privileges not granted to 'WKSYS'
ORA-02303: cannot drop or replace a type with type or table dependents
ORA-02443: Cannot drop constraint - nonexistent constraint
ORA-04043: object <object-name> does not exist
ORA-29832: cannot drop or replace an indextype with dependent indexes
ORA-29844: duplicate operator name specified
ORA-14452: attempt to create, alter or drop an index on temporary table already in use
ORA-06512: at line <line number>. If this error follow any of above errors, then can be safely ignored.
ORA-01927: cannot REVOKE privileges you did not grant

2. On HP-UX Systems you might see below warnings while performing the relinking of oracle binary and these are ignorable warnings as per readme.txt

WARNING:OUI-67215:
OPatch found the word "failed" in the stderr of the make command.Please look at this stderr. You can re-run this make command.
cc: warning 487: Possibly incorrect message catalog.
total_lntt_bytes is 0, assuming size of .debug_lntt
Failed to mmap lntt temp file. 

After applying Oracle Patch set update 11.2.0.4.1 if you see below warning messages then you might have affected with another bug and refer below Metalink note for more info.

"Warning: odm_mkdir not found in ODM library"

 Bug 18180390 : AFTER APPLYING DBPSU 11.2.0.4.1 DB PROCESSES GET WARNING: ODM_MKDIR NOT FOUND

Regards,
Satishbabu Gunukula,Oracle ACE
http://www.oracleracexpert.com

Optimal redo logfile size in Oracle

If you have a small redo log file then you will see frequent log switches, in case of large redo logfile you might be at risk of losing data during instance crash. The optimal redo logfile size should not have more than 5 switches per hour.

You can use below query to find the number of log switches per hour

col day format a15;
col hour format a4;
col total format 999;
select to_char(first_time,'yyyy-mm-dd') day, to_char(first_time,'hh24') hour, count(*) total
from v$log_history
group by to_char(first_time,'yyyy-mm-dd'),to_char(first_time,'hh24')
order by to_char(first_time,'yyyy-mm-dd'),to_char(first_time,'hh24') asc;

To resize the redo logfile size follow the steps in below link

http://www.oracleracexpert.com/2013/07/thread-1-cannot-allocate-new-log.html

In Oracle 10g, the Redo Logfile Size Advisor introduced and using this you can determine the optimal redo log size based upon FAST_START_MTTR_TARGET parameter. You must set a non-zero value to enable redo log file size advisor.

FAST_START_MTTR_TARGET – this parameter enables you to specific number of seconds the database takes to perform crash recovery. Based up on this value Oracle determines the checkpoint writes to meet the target.

If you DONT set FAST_START_MTTR_TARGET then OPTIMAL_LOGFILE_SIZE in V$INSTANCE_RECOVERY will not populated with recommend redo log file size.

SQL> show parameter FAST_START_MTTR_TARGET
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
fast_start_mttr_target integer 0

SQL> select optimal_logfile_size from v$instance_recovery;
OPTIMAL_LOGFILE_SIZE
--------------------------------

User must set FAST_START_MTTR_TARGET to a non-zero value then OPTIMAL_LOGFILE_SIZE in V$INSTANCE_RECOVERY will be populated with recommend redo log file size.

Here I am setting FAST_START_MTTR_TARGET=60 (sec), you will see that OPTIMAL_LOGFILE_SIZE will be populated with recommended value.

SQL> alter system set FAST_START_MTTR_TARGET=60 scope=both;
System altered.

SQL> select OPTIMAL_LOGFILE_SIZE from v$instance_recovery;
OPTIMAL_LOGFILE_SIZE
--------------------
151

Now the checkpoints are driven by FAST_START_MTTR_TARGET parameter.

In some cases user will see many log switches during batch job window and there is no log switches out of batch job window. In this case you need to optimal value for redo log size and may need to set archive_lag_target to force redo log switches to increase the frequency during non-batch job window.

For more information on archive_lag_target refer below Oracle document.
http://docs.oracle.com/cd/B19306_01/server.102/b14237/initparams009.htm

Regards
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

SAP Transaction Codes (T-CODE) for Basis Admins

If you are a Basis Admin/ABAP developer you must have some knowledge of SAP Transaction codes(T-CODE).

Each function in SAP has a Transaction code (or t-code) and it consists of letters, numbers or both. By suing the Transaction code you can go to task and start the faction in a single step.

Before you learn about SAP Transaction codes, you need to understand transaction Control in SAP.

Transaction Control in SAP - Below commands are commonly used

n + Transaction code – To end current system task and go to new task
/o + Transaction – To create new session and go to new task without closing prior session
/o  – To list all existing or open sessions
/n  – To terminate the transaction ( F 15 or Shift +F3 )
/i  – To terminate the current session
/nend  – To terminate all separate sessions and log off.
/h – To turn on Debugging mode

Below are the few transaction codes I have used and come across during the Basis Administration.

SAP User/Group/Role/Profile Administration SM01 – Lock Transactions
SM04 – Overview of Users
SU1 – Maintain Own user Addresses
SU3 – Maintain users Own Data
SU01 – User Maintenance
SU02 – Maintain Authorization Profiles
SU03 Maintain Authorizations
SU05- Maintain Internet Users
SU10 – User Mass Maintenance
SU12 – Mass Changes to user Master Records
SU20 – Maintain Authorization Fields
SU21 – Maintain Authorization Objects
SU22 – Authorization object usage in Transaction
SU24 – Authorization object check under Transactions
SU25/SU26 – Upgrade tool for profile generator
SU53 – Evaluate user Authorization check
SUIM – User information System
SUGR – Maintain user Groups
SUMM– Global User Manager
SUPC – Role Profiles
PFCG – Role Maintenance

SAP Jobs, Events and Batches
SM36 – To Define Background job
SM37 – To view Background job Overview
SM50 – Work Process Overview (Alternatively you can use SM66)
SM66 –System Wide work process over view
SM51 – To Display list of SAP Systems/Active application servers
SM34 – View cluster maintenance
SM49 – Execute External Operating system Commands
SM69 – Maintain external Operating system commands
SM62 – Define and Display Events
SM64 – Release of an event or administration of events

SAP System/Client Administration, Analysis , Run Time, Logs
SCC4 – Client Administration
SCC5 – Delete Client
SCC8 – Client Export
SCCL – Local Client copy
SCC9 – Remote Client Copy
ST01 – System Level trace
ST06 – Operating System Monitor
ST22 – ABAP dump analysis
SM21 – Online System Log Analysis
AM13 – Administrative Update requests
SE30 – Runtime analysis

SAP Database Administration, Backup/Recovery, Performance, Logs
DB02 – Tables and Indexes Monitor
DB13 - DBA Planning Calendar (Schedule Backups, Maintenance jobs)
DB12 – Overview of Database Backup Logs and Status
DB14 – Display DBA Operation Logs
DB15 – Data archiving from Database tables.
DB16/DB17 – Database system Check
DB24 – Administration/Monitoring of Database Operations
DB 26 –Display and history of Database parameters
DBCO – Maintain Database connection information
DBACOCKPIT - Start DBA Cockpit
SM12 – Display and delete locks
ST04 – Database performance Monitor
ST06 – Database Tune Summary
ST06 – Monitor Memory Resources
ST05 – Performance Trace

SAP Object Level, Paths, Connections
SUC3 – SAP table History/change log
SCMP – Table comparison.
SM58 – Transactional RFC
SM59 – RFC Destinations/Connections
AL11 – SAP file Directories
AL22 – Dependent objects display
FILE – Cross-Client File Names/Paths
SCU0/OY19 – Customizing Cross-System Viewer
SM56 – Number range buffers
SNRO – Number range Objects

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

OPATCH prereq failed with checkConflictAgainstOHWithDetail not executed

Recently I was working on applying a PSU One-Off Patch, before applying patch it is necessary to get conflict resolution with existing patch information.

I was running below command to get one-off patches conflict with the PSU and received error

$ opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./9352164

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

PREREQ session

Oracle Home : /home/oracle/product/10.2.0/db_1
Central Inventory : /home/oracle/oraInventory
from : /etc/oraInst.loc
OPatch version : 10.2.0.4.2
OUI version : 10.2.0.4.0
OUI location : /home/oracle/product/10.2.0/db_1/oui
Log file location : /home/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2013-10-29_15-58-39PM.log
Invoking prereq "checkconflictagainstohwithdetail"
The location "./9352164/README.html" is not a directory or a valid patch zip file.
Prereq "checkConflictAgainstOHWithDetail" not executed
PrereqSession failed: Invalid patch location

OPatch failed with error code 73

You may see below error when you check the log file

INFO:Invoking prereq "checkconflictagainstohwithdetail"
INFO:The location "./9352164/psu_root.sh" is not a directory or a valid patch zip file.
INFO:Prereq "checkConflictAgainstOHWithDetail" not executed
SEVERE:OUI-67073:PrereqSession failed: Invalid patch location.
INFO:Finishing PrereqSession at Tue Oct 29 16:22:07 PDT 2013
INFO:Stack Description: java.lang.RuntimeException: Invalid patch location.
INFO:StackTrace: oracle.opatch.opatchprereq.PQSession.parseBaseDirListFile(PQSession.java:2118)
INFO:StackTrace: oracle.opatch.opatchprereq.PQSession.checkconflictagainstohwithdetail(PQSession.java:1424)
INFO:StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
INFO:StackTrace: sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
INFO:StackTrace: sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
INFO:StackTrace: java.lang.reflect.Method.invoke(Method.java:324)
INFO:StackTrace: oracle.opatch.PrereqSession.process(PrereqSession.java:251)
INFO:StackTrace: oracle.opatch.OPatchSession.main(OPatchSession.java:1937)
INFO:StackTrace: oracle.opatch.OPatch.main(OPatch.java:619)

Solution: You will see this error when you don’t have a latest Opatch version. Go to My Oracle support and download the latest patch.

Irrespective of the Oracle version (9i,10g,11g) you will see this issue when you don’t have latest Opatch version.

Steps to install the newer version of OPATCH
1. Copy the patch into ORACLE_HOME
2. Rename the old “Opatch” directory
$ mv OPatch OPatch_bak
3. Unzip the file patch
4. Check new OPatch version
$ opatch version
You should be able to see new Opatch version.

Refer Metalink Notes:-
Master Note For OPatch (Doc ID 293369.1)
How To Download And Install The Latest OPatch Version [Article ID 274526.1]

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com

INVISIABLE Columns and MULTIPLE Indexes on Same Set of Columns in Oracle 12C

As you know Oracle 11g introduced Virtual Column and Invisible indexes. Now Oracle 12c introduced new enhancements in this area.

1. Invisible Column
2. Multiple Indexes on the same set of Columns

Invisible Column:
In Oracle 12c you can able to add a column to table in the invisible fashion. The invisible column will not show up in DESCRIBE statement and also column won’t appear in queries for ex: - SELECT * query
You can add an INVISIABLE COLUMN or you can modify an existing COLUMN as INVISIBLE.

SQL> CREATE TABLE test1 (a number, b varchar2 (10), c date);
Table created.

SQL> ALTER TABLE test ADD( d number INVISIBLE );
Table altered.

SQL> DESC test
Name Null? Type
--------------------------
A NUMBER
B VARCHAR2(10)
C DATE

As you see the column “D” is not visible. Let’s insert the data into table.

SQL> INSERT INTO test VALUES (1,'Test1',sysdate,100);
1 row created.

SQL> commit;
Commit complete.

SQL> SELECT * FROM test;
A   B       C
--- ------ ---------- 
1 Test1 10-OCT-13

We are able to insert the data into INVISIBLE column, but not able to see the data using “SELECT *” query. Let’s try to query the data referring the columns explicitly in SELECT statement.

SQL> SELECT a,b,c,d from test;
A  B        C                 D
-- ------- ------------ --------
1  Test1  10-OCT-13 100

Now we are able to see the data from INVISIBLE column

NOTE: - In order to INSERT or SELECT data in INVISIBLE column, you must explicitly use the INVISIBLE column name

Let’s change the column to be visible and query the data.

SQL> ALTER TABLE test modify d visible;
Table altered.

SQL> SELECT * from test;
A B         C                 D
-- ------- -----------  ----------
1  Test1  10-OCT-13 100

Multiple Indexes on the same set of Columns
Prior Oracle12c, you cannot able to create another index on same column name or set of columns which already have index. In Oracle 12c, you can able to create multiple indexes the same set of columns as long as some characteristic is different and qualifying characteristics are:

· B-tree versus bitmap
· Different partitioning strategies
· Unique versus nonunique

Creating multiple indexes on the same set of columns enables transparent and seamless application migrations without the need to drop an existing index and re-create it with different attributes.

SQL> CREATE INDEX test_index1 ON TEST(a,b);
Index created.

SQL> CREATE BITMAP INDEX test_index2 ON TEST(a,b);
CREATE BITMAP INDEX test_index2 ON TEST(a,b)
*
ERROR at line 1:
ORA-01408: such column list already indexed

Here I am trying to create a BITMAP index, but it is failed. Note that one type of index is usable/visible at any given point of time.

SQL> CREATE BITMAP INDEX test_index2 ON TEST(a,b) INVISIBLE;
Index created.

Now index created successfully.

SQL> ALTER INDEX test_index2 VISIABLE;
ALTER INDEX test_index2 VISIABLE;
*
ERROR at line 1:
ORA-14147: There is an existing VISIBLE index defined on the same set of
columns.

First we need to make sure that the existing index is INVISIBLE then only you can able to make the other index visiable

SQL> ALTER INDEX test_index1 INVISIBLE;
Index altered.

SQL> ALTER INDEX test_index2 VISIBLE;
Index altered.

Regards,
Satishbabu Gunukula, Oracle ACE
http://www.oracleracexpert.com