ASMLIB

This document describes some advantages the Linux specific ASM library provided by Oracle (herein "ASMLib") brings to the administration of a Linux system running Oracle. Linux often presents the challenge of disk name persistence. Change the storage configuration and a disk that appeared as /dev/sdg yesterday can appear as /dev/sdh after a reboot today. How can these changes be isolated so that they do not affect ASM?

Why Not Let ASM Scan All Disks?

ASM scans all disks it is allowed to discover (via the asm_diskstring). Why not scan all the disks and let ASM determine which it cares about, rather than even worrying about disk name persistence?

The question is notionally correct. If you pass /dev/sd* to ASM, and ASM can read the devices, ASM can indeed pick out its disks regardless of whether /dev/sdg has changed to/dev/sdh on this particular boot.

However, to read these devices, ASM has to have permission to read these devices. That means ASM has to have user or group ownership on all devices /dev/sd*, including any system disks. Most system administrators do not want to have the oracle user own system disks just so ASM can ignore them. The potential for mistakes (DBA writing over the /homevolume, etc) is way too high.


ASMLib vs UDev or DevLabel

There are various methods to provide names that do not change, including devlabel and udev. What does ASMLib provide that these solutions do not?

The bigger problem is not specifically a persistent name - it is matching that name to a set of permissions. It doesn't matter if /dev/sdg is now /dev/sdh, as long as the new /dev/sdh hasoracle:dba ownership and the new /dev/sdg - which used to be /dev/sdf - has the ownership the old /dev/sdf used to have. The easiest way to ensure that permissions are correct is persistent naming. If a disk always appears as the same name, you can always apply the same permissions to it without worrying. In addition, you can then exclude names that match system disks. Even if the permissions are right, a system administrator isn't going to want ASM scanning system disks every time.

Now, udev or devlabel can handle keeping sdg as sdg (or /dev/mydisk, whatever). What does ASMLib add? A few things, actually. With ASMLib, there is a simple command to label a disk for ASM. With udev, you'll have to modify the udev configuration file for each disk you add. You'll have to determine a unique id to match the disk and learn the udev configuration syntax.

The name is also human-readable. With an Apple XServe RAID, why have a disk named/dev/sdg when it can be DRAWER1DISK2? ASMLib can also list all disks, where with udev you have to either know in your head that sdg, sdf, and sdj are for ASM, or you have to provide names. With ASMLib, there is no chance of ASM itself scanning system disks. In fact, ASMLib never modifies the system's names for disks. ASMLib never uses the name " /dev/sdg". After boot-time querying the disks, it provides its own access to the devices with permissions for Oracle. /dev/sdg is still owned by root:root, and the oracle user still cannot access the device by that name.


The configuration is persistent. Reinstall a system and your udev configuration is gone. ASMLib's labels are not. With udev, you have to copy the configuration over to the other nodes in a RAC. If you have sixteen nodes, you have to copy each configuration change to all sixteen nodes. Whether you use udev or devlabel, you have to set the permissions properly on all sixteen nodes. ASMLib just requires one invocation of " /etc/init.d/oracleasm scandisks" to pick up all changes made on the other node.

ADOP (Ad online patching utility) in R12.2

Adop(Ad online patching utility) is used in R12.2 to apply the patches online with out any downtimeit is the new utility which to apply patches in online patching mode .It is perl script and call adpatch internally to apply the patch. It is not recommended to apply the patches directly using adpatch in R12.2

For online patching to work Oracle Apps make use of 11g database feature called as “Edition Based Redefinition”, under this feature. On the Applications side, there will be two file systems, one called as patch and other called as run file system. Both are identical to each other and services will be running out of run file system. During patching process, patches are applied to patch file system, which does not require any downtime. For the patch changes to come to effect, patch file system is switched as run file system and vice versa, and services are restarted. In essence, Downtime will be only for the time duration during which services are restarted.

A complete patching cycle is following while applying the patches with adop. It consists of many phases

1.Prepare: This syncs the current patch file system with run file system, to make sure patch file system is up to date compared to run file system and ready for the patches to get applied. This also created a new patch edition in the database. This syncing process is incremental process to be efficient.

$ adop phase=prepare

2.Apply : Patches are applied to patch file system during this phase, we can apply as many patches we want during this phase. Changed code objects are from patch edition of database. Changes to tables are stored in new columns which are only visible from patch edition of the database.

$ adop phase=apply patches=

3.Finalize: This phase involves compiling of invalid objects etc.

$ adop phase=finalize

4.Cutover: This phase configures the patch file system as new run file system and patch edition of the database as new run edition. Restart application services from new run file system of Apps. This is where downtime is required.

$ adop phase=cutover

5.Cleanup: Deletes obsolete code objects and columns from earlier patch edition are removed at database.

$ adop phase=cleanup

Some more things about adop

1)Online Help
To obtain help about the basics of adop operation, enter the command:

adop -help

2) Enabling maintenance mode is not required in R12.2; it’s not applicable for online patching.

3) There are also three special phases, for use when needed.
Special phases:
abort – Abort the current patching cycle.
actualize_all – Create new copies of all code objects in the patch edition.
fs_clone – Copy the run file system to the patch file system.

4) The adop logfiles are located on the non-editioned file system (fs_ne), under:
s_ne_base/EBSapps/log/adop//__/ext_name>/log
For example, if s_ne_base was /u01/apps/R122_EBS/fs_ne, the session ID was 20, and the
was sunxx_test, the path to the adop log files from 1th July 2013 would resemble this:
/u01/apps/R122_EBS/fs_ne/EBSapps/log/adop/20/apply_20130701_112226/sunxx_te
stsys/log

5) You can obtain a brief report for the current patching session by running the command:
$adop -status
This will display information that includes phases completed and the time taken

Preserving changes in resolv.conf across reboots in OEL/REDHAT6

DNS servers in /etc/resolv.conf change after a reboot or network service restart.We need to shutdown NetworkManager and disable it.

root@rac1 network-scripts]# service NetworkManager stop
Stopping NetworkManager daemon:                            [  OK  ]


[root@rac1 network-scripts]# chkconfig NetworkManager off

Meaning of the Codes in the STATUS_CODE and PHASE_CODE Columns of FND_CONCURRENT_REQUESTS Table?

STATUS_CODE Column:

A Waiting
B Resuming
C Normal
D Cancelled
E Error
F Scheduled
G Warning
H On Hold
I Normal
M No Manager
Q Standby
R Normal
S Suspended
T Terminating
U Disabled
W Paused
X Terminated
Z Waiting

.
PHASE_CODE column
.
C Completed
I Inactive
P Pending
R Running

Oracle Wallet (Orapki commands)

Wallets:
      A wallet is a password-protected container used to store authentication and signing credentials, including private keys, certificates, and trusted certificates needed by SSL. The wallets it creates can be read by Oracle Database, Oracle Application Servers (OHS) , and the Oracle Identity Management infrastructure.

There are three types of wallets.

1.Password protected - ewallet.p12
Required password for all operations like display,add,delete

2.Password protected with autologin- ewallet.p12& cwallet.sso
Required password for only add,delete not for display. It can be read by oracle products without password.

3.Auto_login_only - cwallet.sso
Doesn’t required password for any operations.

Creating and Viewing Oracle Wallets with orapki:

1.password-protected wallet creation:

orapki wallet create -wallet wallet_location

This command will prompt you to enter and re-enter a wallet password. It creates a wallet in the location specified for -wallet.

2.Password-Protected with auto-login enabled: (Used in our environments, Typically used by all)

orapki wallet create -wallet wallet_location -auto_login

This command creates a wallet with auto-login enabled, or it can also be used to enable auto-login on an existing wallet. If the wallet_location already contains a wallet, then auto-login will be enabled for it. To disable the auto-login feature, delete cwallet.sso.For wallets with the auto-login feature enabled, you are prompted for a password only for operations that modify the wallet, such as add

3. Autologin wallets:

To create an auto login wallet (cwallet.sso) that does not need a password, use the following command:

orapki wallet create -wallet wallet_location -auto_login_only

This command creates an auto login wallet (cwallet.sso) that does not need a password to open. You can also modify or delete the wallet without using a password. File system permissions provide the necessary security for such auto login wallets.

4.To view an Oracle wallet:

orapki wallet display -wallet wallet_location

5.Modifying the Password for a Wallet:

To change the wallet password, use the following command:

orapki wallet change_pwd -wallet wallet_location [-oldpwd password ] [-newpwd password]

6.How to add private key and certificates generated using openssl?

a.Create Wallet using openssl:

openssl pkcs12 -export -in jaydba_blogspot_com_cert.cer -inkey jaydba_blogspot_com.key -cerfile jaydba_blogspot_com_interm.cer -out ewallet.p12

b.Enable auto login:

orapki wallet create -wallet . -auto_login

                                             [or]


If you face Issues while enabling Autologin:

In Oracle HTTP server 12.1.3 an exception thrown when attempting to set the "auto-login" flag on a a wallet using the "orapki" command,I got this error.

Exception in thread "main" java.lang.NullPointerException
at oracle.security.pki.OracleKeyStoreSpi.a(Unknown Source)
at oracle.security.pki.OracleSSOKeyStoreSpi.a(Unknown Source)
at oracle.security.pki.OracleFileWalletImpl.b(Unknown Source)
at oracle.security.pki.OracleWallet.saveSSO(Unknown Source)
at oracle.security.pki.textui.OracleWalletTextUI.create(Unknown Source)
at oracle.security.pki.textui.OracleWalletTextUI.command(Unknown Source)
at oracle.security.pki.textui.OraclePKITextUI.main(Unknown Source)"


Please follow below steps as an alternate.

a.Create Wallet using openssl:

openssl pkcs12 -export -in jaydba_blogspot_com_cert.cer -inkey jaydba_blogspot_com.key -cerfile jaydba_blogspot_com_interm.cer -out ewallet.p12 

cp ewallet.p12  temp.p12

b. Create Auto login Wallet:

orapki wallet create -wallet -auto_login -with_trust_flags -compat_v12

c. Finally import the pkcs12 file from step 1.

orapki wallet import_pkcs12 -wallet . -pkcs12file  temp.p12 

7.How to Convert JKS to wallet:

a.create a password protected Oracle wallet with autologin:

orapki wallet create -wallet ./ -pwd password  -auto_login

b.Migrate the JKS keystore entries to the wallet:

orapki wallet jks_to_pkcs12 -wallet ./ -pwd password -keystore ./ewallet.jks -jkspwd password

Oracle Apps DBA - Useful Queries

Status of Concurrent Manager & Requests: How to Find Session Details of conurrent requet id: select a.sid,a.event,a.sql_id,a.blocking_session,a.action from v$session a ,V$process b,apps.fnd_concurrent_requests c where a.paddr=b.addr and b.spid=c.oracle_process_id and c.request_id='&request_id'; How to Find  Running Concurrent Programs: select user_concurrent_program_name from apps.fnd_conc_req_summary_v where status_code='R' and phase_code='R'; Concurrent Manager Status: set verify off set lines 256 set trims ON set pages 60 col concurrent_queue_id format 99999 heading "QUEUE Id" col concurrent_queue_name format a20 trunc heading "QUEUE Code" col user_concurrent_queue_name format a30 trunc heading "Concurrent Queue Name" col max_processes format 999 heading "Max" col running_processes format 999 heading "Act" col running format 999 heading "Run" col target_node format a15 heading "Node" col status format a12 trunc heading "Status" col run format 9999 heading 'Run' col pend format 9999 heading 'Pending' col cmgr_program FOR a65; SELECT 'Instance : ' ||NAME instance_name FROM v$database; Prompt =========================== Prompt concurrent manager status Prompt =========================== SELECT q.concurrent_queue_id, q.concurrent_queue_name, q.user_concurrent_queue_name, q.target_node, q.max_processes, q.running_processes, running.run running, pending.pend, Decode(q.control_code, 'D', 'Deactivating', 'E', 'Deactivated', 'N', 'Node unavai', 'A', 'Activating', 'X', 'Terminated', 'T', 'Terminating', 'V', 'Verifying', 'O', 'Suspending', 'P', 'Suspended', 'Q', 'Resuming', 'R', 'Restarting') status FROM (SELECT concurrent_queue_name, COUNT(phase_code) run FROM fnd_concurrent_worker_requests WHERE phase_code = 'R' AND hold_flag != 'Y' AND requested_start_date <= SYSDATE GROUP BY concurrent_queue_name) running, (SELECT concurrent_queue_name, COUNT(phase_code) pend FROM fnd_concurrent_worker_requests WHERE phase_code = 'P' AND hold_flag != 'Y' AND requested_start_date <= SYSDATE GROUP BY concurrent_queue_name) pending, apps.fnd_concurrent_queues_vl q WHERE q.concurrent_queue_name = running.concurrent_queue_name(+) AND q.concurrent_queue_name = pending.concurrent_queue_name(+) AND q.enabled_flag = 'Y' ORDER BY Decode(q.application_id, 0, Decode(q.concurrent_queue_id, 1, 1,4, 2)), Sign(q.max_processes) DESC,q.concurrent_queue_name, q.application_id; List of Concurrent Requests Completed with Error in last two hours: set linesize 200 pagesize 200 feedback on; col USER_CONCURRENT_PROGRAM_NAME for a40 col COMPLETION_TEXT for a60 select a.user_concurrent_program_name , REQUEST_ID,DECODE(phase_code,'C','Completed',phase_code) phase_code, DECODE(status_code,'D', 'Cancelled' ,'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X','Terminated', 'C', 'Normal', status_code) status_code , to_char(ACTUAL_START_DATE,'DD-MON-YYYY HH24:MI:SS') START_DATE ,to_char(actual_completion_date,'DD-MON-YYYY HH24:MI:SS') END_DATE,COMPLETION_TEXT from fnd_concurrent_programs_tl a, fnd_concurrent_requests b where a.concurrent_program_id=b.concurrent_program_id and actual_completion_date > sysdate - (2/24) and phase_code = 'C' and status_code = 'E'; List of Concurrent Requests Completed with Warninng in last two hours: set linesize 200 pagesize 200 feedback on; col USER_CONCURRENT_PROGRAM_NAME for a40 col COMPLETION_TEXT for a50 select a.user_concurrent_program_name , REQUEST_ID ,PHASE_CODE , STATUS_CODE , to_char(ACTUAL_START_DATE,'DD-MON-YYYY HH24:MI:SS') START_DATE ,to_char(actual_completion_date,'DD-MON-YYYY HH24:MI:SS') END_DATE,COMPLETION_TEXT from fnd_concurrent_programs_tl a, fnd_concurrent_requests b where a.concurrent_program_id=b.concurrent_program_id and actual_completion_date > sysdate - (2/24) and phase_code = 'C' and status_code = 'G' order by actual_completion_date; List of Long Running Requests: set linesize 200 pagesize 200 feedback on; col USER_CONCURRENT_PROGRAM_NAME for a40; select a.user_concurrent_program_name , REQUEST_ID ,PHASE_CODE , STATUS_CODE , to_char(ACTUAL_START_DATE,'DD-MON-YYYY HH24:MI:SS') START_DATE ,to_char(actual_completion_date,'DD-MON-YYYY HH24:MI:SS') END_DATE,COMPLETION_TEXT from fnd_concurrent_programs_tl a, fnd_concurrent_requests b where a.concurrent_program_id=b.concurrent_program_id and actual_start_date > sysdate - 1 and phase_code = 'R' and status_code = 'R'; Determine which concurrent Manager ran the particular request: col USER_CONCURRENT_QUEUE_NAME for a100 select b.USER_CONCURRENT_QUEUE_NAME from fnd_concurrent_processes a, fnd_concurrent_queues_vl b, fnd_concurrent_requests c where a.CONCURRENT_QUEUE_ID = b.CONCURRENT_QUEUE_ID and a.CONCURRENT_PROCESS_ID = c.controlling_manager and c.request_id = '&conc_reqid'; Determine the details of particular CM request: set linesize 200 pagesize 200; col USER_CONCURRENT_PROGRAM_NAME for a40 col completion_text for a50 SELECT request_id, user_concurrent_program_name, DECODE(phase_code,'C','Completed',phase_code) phase_code, DECODE(status_code,'D', 'Cancelled' , 'E', 'Error' , 'G', 'Warning', 'H','On Hold' , 'T', 'Terminating', 'M', 'No Manager' , 'X','Terminated', 'C', 'Normal', status_code) status_code, to_char(actual_start_date,'dd-mon-yy:hh24:mi:ss') Start_Date, to_char(actual_completion_date,'dd-mon-yy:hh24:mi:ss') completion_date,completion_text FROM apps.fnd_conc_req_summary_v WHERE request_id = '&req_id' ORDER BY 6 DESC; TRACE Enabled Concurrent Porgrams: select a.CONCURRENT_PROGRAM_ID, b.USER_CONCURRENT_PROGRAM_NAME "Program_Name", a.CONCURRENT_PROGRAM_NAME "Short_Name", a.APPLICATION_IDfrom apps.fnd_concurrent_programs a, apps.fnd_concurrent_programs_tl b where a.CONCURRENT_PROGRAM_ID=b.CONCURRENT_PROGRAM_ID and a.ENABLE_TRACE='Y'; Workflow Mailer: Check the status of the componenet: set pagesize 400 set linesize 120 set pagesize 50 column COMPONENT_NAME format a45 column STARTUP_MODE format a15 column COMPONENT_STATUS format a15 select fsc.COMPONENT_NAME,fsc.STARTUP_MODE,fsc.COMPONENT_STATUS from APPS.FND_CONCURRENT_QUEUES_VL fcq, fnd_svc_components fsc where fsc.concurrent_queue_id = fcq.concurrent_queue_id(+) order by COMPONENT_STATUS , STARTUP_MODE , COMPONENT_NAME; Find Workflow Mailer logfile: select fl.meaning,fcp.process_status_code, decode(fcq.concurrent_queue_name,'WFMLRSVC','maile r container','WFALSNRSVC','listener container',fcq.concurrent_queue_name), fcp.concurrent_process_id,os_process_id, fcp.logfile_name from fnd_concurrent_queues fcq, fnd_concurrent_processes fcp , fnd_lookups fl where fcq.concurrent_queue_id=fcp.concurrent_queue_id and fcp.process_status_code='A' and fl.lookup_type='CP_PROCESS_STATUS_CODE' and fl.lookup_code=fcp.process_status_code and concurrent_queue_name in('WFMLRSVC','WFALSNRSVC') order by fcp.logfile_name; Ready Count : select msg_state,count(*) from applsys.aq$wf_notification_out group by msg_state; Check The even't status in wf_deferred queue: Ready = Activity is ready to be processed Delayed = Activity will be processed later Retained = Activity was already processed Exception = Activity had an error col event_name for a40 col state for  a13 select wfd.user_data.event_name EVENT_NAME,       decode(wfd.state,                    0, '0 = Ready',                    1, '1 = Delayed',                    2, '2 = Retained',                    3, '3 = Exception',       to_char(substr(wfd.state,1,12))) State,       count(*) COUNT       from applsys.wf_deferred wfd       group by wfd.user_data.event_name, wfd.state       order by 3 desc, 1 asc;             select decode(wfd.state,                    0, '0 = Ready',                    1, '1 = Delayed',                    2, '2 = Retained',                    3, '3 = Exception',                    to_char(substr(wfd.state,1,12))) State,       count(*) COUNT       from applsys.wf_deferred wfd       group by wfd.state       order by 2 desc, 1 asc; R122 adop queries:   adop session status: set pagesize 200; set linesize 160; col PREPARE_STATUS format a15 col node_name format a30 col APPLY_STATUS format a15 col CUTOVER_STATUS format a15 col ABORT_STATUS format a15 col STATUS format a15 select NODE_NAME,ADOP_SESSION_ID, PREPARE_STATUS , APPLY_STATUS  ,CUTOVER_STATUS , CLEANUP_STATUS , ABORT_STATUS , STATUS from AD_ADOP_SESSIONS where ADOP_SESSION_ID=66; Note: Y denotes that the phase is done N denotes that the phase has not been completed X denotes that the phase is not applicable R denotes that the phase is running (in progress) F denotes that the phase has failed P (is applicable only to APPLY phase) denotes at least one patch is already applied for the session id C denotes that the status of this ADOP session has completed cutover statuses: cutover_status='Y' 'COMPLETED' cutover_status not in ('N','Y','X') and status='F' 'FAILED' cutover_status='0' 'CUTOVER STARTED' cutover_status='1' 'SERVICES SHUTDOWN COMPLETED' cutover_status='3' 'DB CUTOVER COMPLETED' cutover_status='D' 'FLIP SNAPSHOTS COMPLETED' cutover_status='4' 'FS CUTOVER COMPLETED' cutover_status='5' 'ADMIN STARTUP COMPLETED' cutover_status='6' 'SERVICES STARTUP COMPLETED' cutover_status='N' 'NOT STARTED' cutover_status='X' 'NOT APPLICABLE' fs_clone status: set linesize 300 col node_name format a15 col clone_status format a20 col driver_file_name format a20 col clone_status format a20 select node_name,clone_status,driver_file_name,status,to_char(start_date,'DD-MON-YYYY HH:MI:SS PM'),to_char(end_date,'DD-MON-YYYY HH:MI:SS PM') from ad_adop_session_patches where adop_session_id=89; To find the patches applied in the particular adop session: select distinct BUG_NUMBER from AD_ADOP_SESSION_PATCHES  where adop_session_id='&session_id'; set linesize 300; select distinct bug_number,node_name,to_char(start_date ,'DD-MON-YYYY HH24:MI:SS') START_DATE,to_char(end_date,'DD-MON-YYYY HH24:MI:SS')END_DATE from ad_adop_session_patches where adop_session_id=67 group by bug_number,node_name,start_date,end_date order by bug_number; select distinct bug_number,node_name,to_char(start_date ,'DD-MON-YYYY HH24:MI:SS') START_DATE,to_char(end_date,'DD-MON-YYYY HH24:MI:SS')END_DATE from ad_adop_session_patches where adop_session_id=&session_id  and bug_number='&bug_number' group by bug_number,node_name,start_date,end_date order by bug_number; AD,TXK VERSIONS: SELECT abbreviation, codelevel FROM AD_TRACKABLE_ENTITIES WHERE abbreviation in ('txk','ad'); ETCC QUERIES: set linesize 300; col node_name format a10; col database_name format a10; col check_message format a50; select database_name,node_name ,check_date ,component_name,component_version,check_message from apps.TXK_TCC_RESULTS where database_name='&DB_NAME';