Check if DB is using pfile or spfile

Check if DB is using pfile or spfile:

SELECT DECODE(value, NULL, 'PFILE', 'SPFILE') "Init File Type"
FROM sys.v_$parameter WHERE name = 'spfile';

Session lock history

Around 2hrs of recent data 

WITH ash_query AS (
SELECT substr(event,6,2) lock_type,program,
h.module, h.action, object_name, h.session_id, h.session_serial#, h.sample_time,
SUM(time_waited)/1000 time_ms, COUNT( * ) waits,
username, sql_text,
RANK() OVER (ORDER BY SUM(time_waited) DESC) AS time_rank,
ROUND(SUM(time_waited) * 100 / SUM(SUM(time_waited))
OVER (), 2) pct_of_time
FROM v$active_session_history h
JOIN dba_users u USING (user_id)
LEFT OUTER JOIN dba_objects o
ON (o.object_id = h.current_obj#)
LEFT OUTER JOIN v$sql s USING (sql_id)
WHERE event LIKE 'enq: %'
GROUP BY substr(event,6,2) ,program, h.module, h.action,
object_name, h.session_id, h.session_serial#, h.sample_time, sql_text, username)
SELECT lock_type,module, username, object_name, session_id, session_serial#, sample_time, sql_text, time_ms,pct_of_time
FROM ash_query
WHERE time_rank < 11
ORDER BY time_rank;

Weeks...

WITH ash_query AS (
SELECT substr(event,6,2) lock_type,program,
h.module, h.action, object_name, h.session_id, h.session_serial#, h.sample_time,
SUM(time_waited)/1000 time_ms, COUNT( * ) waits,
username, sql_text,
RANK() OVER (ORDER BY SUM(time_waited) DESC) AS time_rank,
ROUND(SUM(time_waited) * 100 / SUM(SUM(time_waited))
OVER (), 2) pct_of_time
FROM dba_hist_active_sess_history h
JOIN dba_users u USING (user_id)
LEFT OUTER JOIN dba_objects o
ON (o.object_id = h.current_obj#)
LEFT OUTER JOIN v$sql s USING (sql_id)
WHERE event LIKE 'enq: %'
GROUP BY substr(event,6,2) ,program, h.module, h.action,
object_name, h.session_id, h.session_serial#, h.sample_time, sql_text, username)
SELECT lock_type,module, username, object_name, session_id, session_serial#, sample_time, sql_text, time_ms,pct_of_time
FROM ash_query
WHERE time_rank < 11
ORDER BY time_rank;

Install Apex+Glassfish+ApexListener

Installation will be performed on Oracle Linux 6u3 x64 with Oracle database 11.2.0.3 installed and configured.

1. Download latest versions of Apex, Glassfish server and ApexListener
2. Create tablespace in database(this is optional as you can install apex in any tablespace, for example SYSAUX)

CREATE TABLESPACE APEX_TS DATAFILE
  '/u01/app/oracle/oradata/fdapex/apex01.dbf' SIZE 2048M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED
LOGGING
ONLINE
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 128K
BLOCKSIZE 8K
SEGMENT SPACE MANAGEMENT AUTO
FLASHBACK ON;

2. Unzip Apex installation, go to unpacked Apex folder and as SYS user connectand run the installation:

SQL>@apexins APEX_TS APEX_TS TEMP /i/

 3. When installation is finished change the admin user password. Be aware that admin password should contain one upper letter and one of the punctation marks.
SQL>@apxchpwd

4. As SYS user define password for APEX_PUBLIC_USER

SQL>alter user APEX_PUBLIC_USER identified by <new_password>

5. Install JDK latest version
6. Install Glassfish server

sh Glassfish-4.0-unix.sh

Follow the screens:

7. Enable secure admin

asadmin enable-secure-admin

If there is a problem with this then define admin user password:

asadmin change-admin-password

here current password is blank, just press enter and then define new password.

8. Create service for automatic start with computer boot:

asadmin create-service

9. Install apex-listener

-Copy images folder from Apex unzipped folder to specific location, for example /home/oracle/apex/images

-Unzip apex listener archive

Define configuration directory

java -jar apex.war configdir /home/oracle/apex

then configure database communication 

java -jar apex.war setup

then configure images folder

java -jar apex.war static /home/oracle/apex/images

10. Deploy apex.war and i.war

 Login to Glasswish admin console

localhost:4848

To install the deployment:

On the navigation tree, click the Application node.

The Applications page displays.

Click the Deploy button.

The Deploy Applications or Modules page displays.

Select Packaged File to be Uploaded to the Server and click Browse.

Navigate to the location of the apex.war file, select the file, and click Open.

The Deploy Applications or Modules page displays.

On the Deploy Applications or Modules page, specify the following:

Type: Web Application

Context Root: apex

Application Name: apex

Status: Enabled

Description: Application Express Listener

Accept all other default settings and click OK.

Repeat the previous steps to deploy the i.war file. Clear the Context Root field so that the context root set in the sun-web.xml is used.

Start Apex:

http://localhost:8080/apex

If for some reason you get blank screen when trying to access this page, please check and apply patch number 16760897 to Apex installation. Check README.txt

Thats all!

Vncserver on Oracle Linux 6u3

How to install vncserver on Oracle linux 6u3 default installation(without x server)

yum install tigervnc-server
yum install xorg-x11-twm
yum install xterm
yum install xsetroot

Thats it!