How to know if a patch requires downtime?

1. Download the particular patch file
2. Check the file ..\\etc\config\inventory
3. If the following entry is present
(instance_shutdown) true (/instance_shutdown)

then it requires downtime.

Note: OPatch means rdbms patch.
For rdbms patch go to OPatch dorectory and select /etc/config/inventory

Copy files using ssh

you can't copy using the 'ssh' program specifically, but you can with it's associated programs: sftp or scp

sftp is (and works) similar to ftp

scp is a neat little program:

copy from a remote machine to my machine:
scp asami@192.168.1.100:/home/remote_user/Desktop/file.txt /home/me/Desktop/file.txt

copy from my machine to a remote machine:
scp /home/me/Desktop/file.txt asami@192.168.1.100:/home/remote_user/Desktop/file.txt

copy all file*.txt from a remote machine to my machine (file01.txt, file02.txt, etc.; note the quotation marks:
scp "asami@192.168.1.100:/home/remote_user/Desktop/file*.txt" /home/me/Desktop/file.txt

copy a directory from a remote machien to my machine:
scp -r asami@192.168.1.100:/home/remote_user/Desktop/files /home/me/Desktop/.

see 'man scp' or 'man sftp' for more ...

emctl upload - disk full error

Today when I tried to run the command 'emctl upload' I got the following error,

-bash-$ emctl upload agent
Oracle Enterprise Manager 10g Release 3 Grid Control 10.2.0.3.0.
Copyright (c) 1996, 2007 Oracle Corporation. All rights reserved.
---------------------------------------------------------------
EMD upload error: Upload was successful but collections currently disabled - disk full

#From Metalink note 317242.1

#Cause
The EMD disk system shows used percent at 99%.
The agent default for space required for upload files is 98%.
The collections will stop when the space on the disk is beyond that.

#Solution
To implement the solution, please execute the following steps:
1. Update the AGENT OH/sysman/config/emd.properties with the parameter:uploadMaxDiscUsedPct=99uploadMaxDiskUsedPctFloor=99
2. Stop the agent: emctl stop agent
3. Start the agent: emctl start agent
4. Upload to the OMS: emctl upload

This resolved the issue.

Samba server configuration

I am writing this as I was doing Samba share configuration between linux and windows machines.

Samba Overview:
---------------
1. Uses Session Message Block (SMB) protocol.
2. The most typical reason to use Samba is to allow windows machine and linux machine to interact.
3. Simple to Complex configurations.
4. Easy to manage as there is one main Samba Configuration file /etc/samba/smb.conf

We are going to setup a Linux box so that certain directories and files are shared and we can control how they are shared weather it should be read only or read write. Then from the windows machine we can access that linux share and vice versa. You can also interact between two linux machines using Samba protocol.

Smb.conf layout:
----------------
There are 4 special sections in Samba configuration files as given below,

[global] Global Configuration Settings
[homes] Shares users home directories -> Any setting in home section will override the global settings.
[printers] Define shared printer access
[userdefined] This will be a user defined section

Example of a user defined section is as given below,

[myshare]
path = /u01/app/oracle/oradata/orcl <--directory to share
writable = true <--people can write to this directory
valid users = samiora <-- only samiora can access this directory
browsable = yes <-- other users can read this directory but cannot write to it
create mode = 0700 <-- this is chmod for this directory for the owner ie. here it is rwx for the owner.

There are zillion (exaggerating) more parameters used for different settings like require passwords, limit by host, limit by time of day etc.

Login in as root and query if the following rpm's are installed or not if the following packages are not available then firstly install them using rpm command,

#rpm -ivh

[root@egdodb samba]# rpm -qa | grep xinetd
xinetd-2.3.14-10.el5

[root@egdodb samba]# rpm -qa | grep samba
samba-common-3.0.33-3.7.el5
samba-3.0.33-3.7.el5
samba-common-3.0.33-3.7.el5
samba-client-3.0.33-3.7.el5
system-config-samba-1.2.41-3.el5

Configuring Samba:
------------------
You can configure samba by various ways, like manually updating the smb.conf file, using yast utility on suse linux, using SWAT Samba Web Admin Tool and using 'webmin' web based tool. The following is the manual way of updating the smb.conf file,

Update the smb.conf file manually and then run the 'testparm' command to validate the smb.conf file entries,

[root@egdodb samba]# testparm smb.conf

Load smb config files from smb.conf
Processing section "[homes]"
Processing section "[printers]"
Loaded services file OK.
Server role: ROLE_STANDALONE
Press enter to see a dump of your service definitions

[global]
workgroup = MYGROUP
server string = Samba Server Version %v
passdb backend = tdbsam
cups options = raw

[homes]
comment = Home Directories
read only = No
browseable = No

[printers]
comment = All Printers
path = /var/spool/samba
printable = Yes
browseable = No

[myshare]
workgroup =


To check the status of samba server service running or not issue the following command,



[root@egdodb samba]# service smb status
smbd is stopped
nmbd is stopped

Useful SQL Scripts for Oracle Applications

Users Connected through application
Purpose	To get the list of users connected.
Description	Query gives the list of users connected to the application
Parameters	None
Query	SELECT user_name username, description name, to_char(b.first_connect,'MM/DD/RR HH24:MI') firstconnect, to_char(b.last_connect,'MM/DD/RR HH24:MI') lastconnect FROM apps.fnd_user a, (SELECT MIN (first_connect) first_connect, MAX (last_connect) last_connect, last_updated_by user_id FROM apps.icx_sessions GROUP BY last_updated_by) b WHERE a.user_id = b.user_id AND last_connect>SYSDATE-3/12 ORDER BY 4 DESC /

Responsibilities Listing
Purpose	To get list of responsibilities.
Description	Query useful when user wants to get application wise responsibility list
Parameters	None
Query	SELECT (SELECT application_short_name FROM fnd_application fa WHERE fa.application_id = frt.application_id) application, frt.responsibility_id, frt.responsibility_name FROM apps.fnd_responsibility_tl frt;

Menus Listing
Purpose	To get Menus Associated with responsibility
Description	User to check menu attached with a reponsilblity
Parameters	responsibility_id Which user can get from query of section Responsibilities Listing
Query	SELECT DISTINCT a.responsibility_name, c.user_menu_name FROM apps.fnd_responsibility_tl a, apps.fnd_responsibility b, apps.fnd_menus_tl c, apps.fnd_menus d, apps.fnd_application_tl e, apps.fnd_application f WHERE a.responsibility_id(+) = b.responsibility_id AND a.responsibility_id = ‘20538’ AND b.menu_id = c.menu_id AND b.menu_id = d.menu_id AND e.application_id = f.application_id AND f.application_id = b.application_id AND a.LANGUAGE = 'US';

Submenu And Function Listing
Purpose	To get submenus and Function attached to this Main menu.
Description	By using this query user can check function and submenus attached to that specific menu
Parameters	User_menu_name Which user can get from query of section Menu Listing
Query	SELECT c.prompt, c.description FROM apps.fnd_menus_tl a, fnd_menu_entries_tl c WHERE a.menu_id = c.menu_id AND a.user_menu_name = 'F4 UK PAY Navigator';

User And Assigned Responsibility Listing
Purpose	To get assigned responsibility to a user.
Description	User wants to check responsibility attached to a specific user
Parameters	None
Query	SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name, SUBSTR (r.responsibility_name, 1, 60) responsiblity, SUBSTR (a.application_name, 1, 50) application FROM fnd_user u, fnd_user_resp_groups g, fnd_application_tl a, fnd_responsibility_tl r WHERE g.user_id(+) = u.user_id AND g.responsibility_application_id = a.application_id AND a.application_id = r.application_id AND g.responsibility_id = r.responsibility_id ORDER BY SUBSTR (user_name, 1, 30), SUBSTR (a.application_name, 1, 50), SUBSTR (r.responsibility_name, 1, 60);

Responsibility And Assigned Request Group Listing
Purpose	To get responsibility and attached request groups.
Description	Every responsibility contains a request group(request group is basis of submitting requests)
Parameters	None
Query	SELECT responsibility_name responsibility, request_group_name, frg.description FROM fnd_request_groups frg, fnd_responsibility_vl frv WHERE frv.request_group_id = frg.request_group_id ORDER BY responsibility_name

Profile Option With Modification Date and User
Purpose	To get modified profile options.
Description	Query used for audit point of view i.e. when a profile is changed and by whom user
Parameters	None
Query	SELECT t.user_profile_option_name, profile_option_value, v.creation_date, v.last_update_date, v.creation_date - v.last_update_date "Change Date", (SELECT UNIQUE user_name FROM fnd_user WHERE user_id = v.created_by) "Created By", (SELECT user_name FROM fnd_user WHERE user_id = v.last_updated_by) "Last Update By" FROM fnd_profile_options o, fnd_profile_option_values v, fnd_profile_options_tl t WHERE o.profile_option_id = v.profile_option_id AND o.application_id = v.application_id AND start_date_active <= SYSDATE AND NVL (end_date_active, SYSDATE) >= SYSDATE AND o.profile_option_name = t.profile_option_name AND level_id = 10001 AND t.LANGUAGE IN (SELECT language_code FROM fnd_languages WHERE installed_flag = 'B' UNION SELECT nls_language FROM fnd_languages WHERE installed_flag = 'B') ORDER BY user_profile_option_name;

Forms Personalization Listing
Purpose	To get modified profile options.
Description	Personalization is feature available in 11.5.10.X. For More detail on form Personalization Use Following Tables (Rule_id) is reference key for these tables applsys.fnd_form_custom_actions, applsys.fnd_form_custom_scopes
Parameters	None
Query	SELECT ffft.user_function_name "User Form Name", ffcr.SEQUENCE, ffcr.description, ffcr.rule_type, ffcr.enabled, ffcr.trigger_event, ffcr.trigger_object, ffcr.condition, ffcr.fire_in_enter_query, (SELECT user_name FROM fnd_user fu WHERE fu.user_id = ffcr.created_by) "Created By " FROM fnd_form_custom_rules ffcr, fnd_form_functions_vl ffft WHERE ffcr.ID = ffft.function_id ORDER BY 1;

Patch Level Listing
Purpose	To get Patch Level.
Description	Query used to view the patch level status of all modules
Parameters	None
Query	SELECT a.application_name, DECODE (b.status, 'I', 'Installed', 'S', 'Shared', 'N/A') status, patch_level FROM apps.fnd_application_vl a, apps.fnd_product_installations b WHERE a.application_id = b.application_id;

Function Listing
Purpose	To get all Functions
Description	Complete forms and functions
Parameters	None
Query	SELECT function_id, user_function_name, creation_date, description FROM applsys.fnd_form_functions_tl order by order by user_function_name;

Request Attached To Responsibility Listing
Purpose	To get all Request attached to a responsibility
Description	View all request who have attached to a reponsiblity
Parameters	None
Query	SELECT responsibility_name , frg.request_group_name, fcpv.user_concurrent_program_name, fcpv.description FROM fnd_request_groups frg, fnd_request_group_units frgu, fnd_concurrent_programs_vl fcpv, fnd_responsibility_vl frv WHERE frgu.request_unit_type = 'P' AND frgu.request_group_id = frg.request_group_id AND frgu.request_unit_id = fcpv.concurrent_program_id AND frv.request_group_id = frg.request_group_id ORDER BY responsibility_name;

Request Listing Application Wise
Purpose	To get all request with application
Description	View all types of request Application wise
Parameters	None
Query	SELECT fa.application_short_name, fcpv.user_concurrent_program_name, description, DECODE (fcpv.execution_method_code, 'B', 'Request Set Stage Function', 'Q', 'SQL*Plus', 'H', 'Host', 'L', 'SQL*Loader', 'A', 'Spawned', 'I', 'PL/SQL Stored Procedure', 'P', 'Oracle Reports', 'S', 'Immediate', fcpv.execution_method_code ) exe_method, output_file_type, program_type, printer_name, minimum_width, minimum_length, concurrent_program_name, concurrent_program_id FROM fnd_concurrent_programs_vl fcpv, fnd_application fa WHERE fcpv.application_id = fa.application_id ORDER BY description

Count Module Wise Reports
Purpose	To Count Module Wise Report
Description	Application wise request counting
Parameters	None
Query	SELECT fa.application_short_name, DECODE (fcpv.execution_method_code, 'B', 'Request Set Stage Function', 'Q', 'SQL*Plus', 'H', 'Host', 'L', 'SQL*Loader', 'A', 'Spawned', 'I', 'PL/SQL Stored Procedure', 'P', 'Oracle Reports', 'S', 'Immediate', fcpv.execution_method_code ) exe_method, COUNT (concurrent_program_id) COUNT FROM fnd_concurrent_programs_vl fcpv, fnd_application fa WHERE fcpv.application_id = fa.application_id GROUP BY fa.application_short_name, fcpv.execution_method_code ORDER BY 1;

Request Status Listing
Purpose	To calculate request time
Description	This query will shows report processing time
Parameters	None
Query	SELECT f.request_id , pt.user_concurrent_program_name user_concurrent_program_name , f.actual_start_date actual_start_date , f.actual_completion_date actual_completion_date, floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600) || ' HOURS ' || floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) - floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60) || ' MINUTES ' || round((((f.actual_completion_date-f.actual_start_date)*24*60*60) - floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600 - (floor((((f.actual_completion_date-f.actual_start_date)*24*60*60) - floor(((f.actual_completion_date-f.actual_start_date)*24*60*60)/3600)*3600)/60)*60) )) || ' SECS ' time_difference , DECODE(p.concurrent_program_name,'ALECDC',p.concurrent_program_name||'['||f.description||']',p.concurrent_program_name) concurrent_program_name , decode(f.phase_code,'R','Running','C','Complete',f.phase_code) Phase , f.status_code FROM apps.fnd_concurrent_programs p , apps.fnd_concurrent_programs_tl pt , apps.fnd_concurrent_requests f WHERE f.concurrent_program_id = p.concurrent_program_id and f.program_application_id = p.application_id and f.concurrent_program_id = pt.concurrent_program_id and f.program_application_id = pt.application_id AND pt.language = USERENV('Lang') and f.actual_start_date is not null ORDER by f.actual_completion_date-f.actual_start_date desc;

User And Responsibility Listing
Purpose	Check responsibility assigned to a specific USER
Description
Parameters	None
Query	SELECT UNIQUE u.user_id, SUBSTR (u.user_name, 1, 30) user_name, SUBSTR (r.responsibility_name, 1, 60) responsiblity, SUBSTR (a.application_name, 1, 50) application FROM fnd_user u, fnd_user_resp_groups g, fnd_application_tl a, fnd_responsibility_tl r WHERE g.user_id(+) = u.user_id AND g.responsibility_application_id = a.application_id AND a.application_id = r.application_id AND g.responsibility_id = r.responsibility_id and a.application_name = 'Purchasing' ORDER BY SUBSTR (user_name, 1, 30), SUBSTR (a.application_name, 1, 50), SUBSTR (r.responsibility_name, 1, 60)

Applied Patch Listing
Purpose	Check Current Applied Patch
Description	This shows the applied patch list.
Parameters	None
Query	SELECT patch_name, patch_type, maint_pack_level, creation_date FROM applsys.ad_applied_patches ORDER BY creation_date DESC

How to Change/Reset ias_admin Password

— ias_admin is user required to use Enterprise Manager Web (Application Server Console - iASConsole) Site.

— ias_admin password is set during Installation of Oracle Application Server (902, 904, 10.1.2.X) or Oracle Identity Management (Infra Tier 10.1.4.X)—ias_admin account is NOT stored in OID (Oracle Internet Directory), It is stored in XML file (JAZN-XML - Java AuthoriZatioN)

How to Reset / Change ias_admin Password

You can reset/change ias_admin password in following ways

1. Using Enterprise Manager (Application Server Control) Web Site
–Login to Instance Home Page
–Click on Preferences on top right
–In new screen, click on “Change Password” on left menu
–Enter current password and New Password

2. Using Command line tool

emctl set password oldpassword new password

like

emctl set password welcome1 welcome2 (Here welcome1 is current ias_admin password and welcome2 is new password which you wish to reset)

If you don’t know current ias_admin password then change it in configuration file

3. Change ias_admin password directly in configuration file
–Backup $ORACLE_HOME/sysman/j2ee/config/jazn-data.xml
–Search for xml entry like below

< user >
< name > ias_admin < / >
< credentials >{903}8QkQ/crno3lX0f3+67dj6WxW9KJMXaCu < / credentials >
< /user >

and Update new password (welcome2 like )

< user >< name > ias_admin < / >
< credentials > !welcome2 / credentials >
< /user >

Note ! (Exclamation Mark in front of password. This signifies that password is stored in clear text)

Renaming/Dropping and recreating online redo logs and Standby logfiles

If you are in process of testing Dataguard with Physical standby and After created Primary and Standby DB's you realize that u haven't put file names as per your standard. So I would recommend you to do the following,
rename both, online redo logs and standby logs.
Follow these steps to do it,

1. Following is the script to identify all log files we have
SELECT GROUP#, BYTES, 'ONLINE' AS TYPE FROM V$LOG
UNION
SELECT GROUP#, BYTES, 'STANDBY' AS TYPE FROM V$STANDBY_LOG
ORDER BY 1;
2. Make sure protection mode is MAXPERFORMANCE:
SELECT PROTECTION_MODE, PROTECTION_LEVEL, DATABASE_ROLE FROM V$DATABASE

First we start working on Standby database
3. STOP redo apply
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;
4. Set STANDBY_FILE_MANAGEMENT to MANUAL.
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='MANUAL';
5. Gather current status of all member.
SQL> select member from v$logfile;
6. Prepare command to drop standby redo logfiles.
select 'alter database drop standby logfile group ' group# ';' from v$standby_log;
7. Prepare command to drop online redo logfiles.
select 'alter database drop logfile group ' group# ';' from v$log;
8. Check the status of online redolog group.
SELECT GROUP#, STATUS FROM V$LOG;
GROUP# STATUS
---------- ----------------
1 CLEARING_CURRENT
3 CLEARING
2 CLEARING
We can not drop CLEARING_CURRENT status logs, for that you have sync with Primary (Easy way is, leave them for a while and move ahead with droping Primary Database redo logs, and come back to Standby and you will be able to do it).
9. Clear the log group first before drop.
ALTER DATABASE CLEAR LOGFILE GROUP 1;
10. Now drop online redo log.
alter database drop logfile group 1;
Follow the same process for rest of the groups.
11. Now drop standby redo logs, leave the one which refuses to drop.
alter database drop standby logfile group 5;
12. Now create new online redo logs.
ALTER DATABASE ADD LOGFILE GROUP 1 '/path/redo_01.dbf' SIZE 10M;
13. Now we will crate standby redo logs.
ALTER DATABASE ADD STANDBY LOGFILE '/path/redo_std_by_05.dbf' SIZE 10M REUSE;
14. Again check the current status of member.
select member from v$logfile;

Now you will see latest updated names for all files. The one which left we can do them after We are done with Primary DB.

Now on Primary Database:
15. Follow step 5,6,7 to know current status and prepare commands.
16. Drop Standby redo logs and recreate them
alter database drop standby logfile group 5;
ALTER DATABASE ADD STANDBY LOGFILE '/path/redo_std_by_05.dbf' SIZE 10M REUSE;
17. Drop online Redo logs, recrate, switch and drop next inactive one.
alter database drop logfile group 1;
ALTER DATABASE ADD LOGFILE GROUP 1 '/path/redo_01.dbf' SIZE 10M;
ALTER SYSTEM SWITCH LOGFILE;
SELECT GROUP#, STATUS FROM V$LOG;
18. Check the new status.
select member from v$logfile;

Now come back to Standby Database back and drop recreate left over online and standby redo logs.
19. Set STANDBY_FILE_MANAGEMENT to AUTO.
ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT='AUTO';
20. start redo apply.
ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;
21. Check the status of applied log again.
select sequence#, applied from v$archived_log;