Oracle Application Server Maintenance (Purge log files)

We can purge below log files after stopping all the Oracle Application server services,

%ORACLE_HOME%\Apache\Apache\logs
%ORACLE_HOME%\opmn\logs
%ORACLE_HOME%\dcm\logs
%ORACLE_HOME%\sysman\logs
%ORACLE_HOME%\j2ee\ [ OC4J instance name ]\log
%ORACLE_HOME%\j2ee\ [ oc4j instance name ]\application-deployments\ [ application name ] \[ oc4j instance name ]_default_island_1\*

Oracle Magazine July/August 2010

Oracle Magazine for July/August 2010 available at the below link

http://www.oraclemagazine-digital.com/oraclemagazine/20100708?sub_id=oW7rAkMPiAA4#pg2

Change Negative (-) Value to Positive (+) Value in Oracle

Description:
We can use the below method to change the records into Negative to Positive.
Solution:
SQL>create table tmp (col1 number)
SQL>SET DEFINE OFF;
SQL>Insert into TMP(COL1) Values(-1);
SQL>Insert into TMP(COL1)Values(-2);
SQL>Insert into TMP(COL1) Values(-20);
SQL>Insert into TMP(COL1)Values(300);
SQL>COMMIT;
SQL>select col1,case when sign(col1)=-1 then '('||(col1)*(-1)||')' else to_char(col1) end col1 from tmp;

Connection to sys /as sysdba hangs

Description:
When I try to connect database using sys /as sysdba database hangs. No response from the database. I tried the below method to solve this issue.
Solution:
Step 1: Tried sqlplus connect " / as sysdba", found that Database hangs.
Step 2: While this connection hangs, opened a different session, issued the following command to find the Oracle related process.
$ps -ef | grep ORACLE_SID
Step 3: Killed the below the Oracle related process using th below command.
kill -9 pid of processess that are connected that are not related to the sysdba connection (pid from Step 2)
Step 4: Now successfully logged into sys/as sysdba session returns SQL prompt, and issued the following
SQL> shutdown abort
SQL> startup restrict
SQL> shutdown immediate
SQL> startup
Step 5: Checkd the database connections as sysdba are successful
$sqlplus "/as sysdba"
SQL> connect / as sysdba

Application Server Log File Details

Application Server Control Console:
ORACLE_HOME/j2ee/home/log/ascontrol.log
ORACLE_HOME/j2ee/home/log/home_default_group-1/
Content DB (Node Log):
ORACLE_HOME/content/log/domain_namenode_name.log
Content DB (Application Logs):
ORACLE_HOME/j2ee/OC4J_Content/application-deployments/Content/OC4J_Content_default_group_1/application.log
HTTP Server:
ORACLE_HOME/Apache/Apache/logs/error_log.log
OC4J instance_name:
ORACLE_HOME/j2ee/instance_name/log/instance_group_process/
ORACLE_HOME/j2ee/instance_name/application-deployments/application_name/application.log
OC4J instance_name:
ORACLE_HOME/j2ee/instance_name/log/instance_group_process/oc4j/log.xml
OPMN:
ORACLE_HOME/opmn/logs
ORACLE_HOME/opmn/logs/component_type~
Port Tunneling:
ORACLE_HOME/iaspt/logs
Universal Installer:
ORACLE_HOME/cfgtoollogs
WebCenter (Application Logs):
ORACLE_HOME/j2ee/OC4J_WebCenter/application-deployments/type/OC4J_WebCenter_default_group_1/application.log

How do I check Oracle Application Server Version details?

We can use the below files to find Oracle Application Server versions details

ORACLE_HOME\config\ias.properties
ORACLE_HOME\install\readme.txt
ORACLE_HOME\install\setupinfo.txt
C:\Program Files\Oracle\Inventory\logs\installActions_installed_date

Auditing Login/Logout Session information

Description:

Below method can be used to store Session Login and Logout Information. It stores in to a table.

Step 1: Create UserLogAudit Table using the below script

CREATE TABLE UserLogAudit
(
UserId VARCHAR2(30)
, SessionId NUMBER(8)
, HostName VARCHAR2(30)
, LogonDay DATE
, LogonTime VARCHAR2(10)
, LogoffDay DATE
, LogoffTime VARCHAR2(10)
, ElapsedMinutes Number(8)
, Updated DATE
, UpdatedBy VARCHAR2(50)
);

Step 2: Create Logon Trigger to get Logon Information

CREATE OR REPLACE TRIGGER LogonAuditTrigger AFTER LOGON ON DATABASE
BEGIN

INSERT INTO USERLOGAUDIT VALUES (User
, Sys_Context('USERENV','SESSIONID')
, Sys_Context('USERENV','HOST')
, Sysdate
, to_char(Sysdate, 'hh24:mi:ss')
, null
, null
, null
);

END;

Step 3: Create Logoff Trigger to get Logoff Information

CREATE OR REPLACE TRIGGER LogoffAuditTrigger BEFORE LOGOFF ON DATABASE
BEGIN
-- UPDATE the logoff day
UPDATE USERLOGAUDIT
SET LogoffDay = sysdate
WHERE Sys_Context('USERENV','SESSIONID') = SessionId;

-- UPDATE the logoff time
UPDATE USERLOGAUDIT
SET LogoffTime = to_char(sysdate, 'hh24:mi:ss')
WHERE Sys_Context('USERENV','SESSIONID') = SessionId;

-- Compute the elapsed minutes
UPDATE USERLOGAUDIT
SET ElapsedMinutes = ROUND((LogoffDay - LogonDay)*1440)
WHERE Sys_Context('USERENV','SESSIONID') = SessionId;

END;