Check tablespace size with Sysdate in Oracle EBS R12

select substr(A.tablespace_name,1,16) "Tablespace",
MAX(A.contents) "Type",
MAX(A.status) "Status",
(SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024)-
(ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID))) "USED SIZE(Mb)",
TO_CHAR(100-(SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT
(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "USED USAGE",
ROUND(SUM(C.BYTES)/1024/1024/COUNT(DISTINCT B.FILE_ID)) "FREE SIZE(MB)",
TO_CHAR((SUM(C.BLOCKS)*100*COUNT(B.FILE_ID)/(SUM(B.BLOCKS)*COUNT
(DISTINCT B.FILE_ID)))/COUNT(DISTINCT B.FILE_ID),'999.99')||'%' "FREE USAGE",
SUM(B.BYTES)*COUNT(DISTINCT B.FILE_ID)/COUNT(B.FILE_ID)/1024/1024 "TOTAL SIZE(Mb)",
sysdate
from dba_tablespaces A,
DBA_DATA_FILES B,
DBA_FREE_SPACE C
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
AND A.TABLESPACE_NAME=C.TABLESPACE_NAME
GROUP BY A.TABLESPACE_NAME
order by 1;

How to Check/Validate That RMAN Backups Are Good

How to Check/Validate That RMAN Backups Are Good

I Want to restore and recover the database till time ‘9:00, 22-October-2012

Step 1: The below command just gives the report of backups that are used to do the  restore and recover :

RMAN> run
{
set until time "to_date('2015-20-07:9:00:00','yyyy-dd-mm:hh24:mi:ss')";
restore database preview;
}


Step 2: Then run the below command to check the backup pieces are good :

The below command will read the backup pieces/Copies which has datafiles and if finds any error it will report at the RMAN prompt.

RMAN> run
{
allocate channel c1 type disk;
set until time "to_date('2012-22-10:9:00:00','yyyy-dd-mm:hh24:mi:ss')";
restore database validate;
}


Step 3: Check the archivelogs needed for recovery

Replace the xxx, yyy with the start and end archivelog sequence reported by restore database preview command ran in the step 1.

RMAN> run
2> {
allocate channel c1 type disk;
restore archivelog from sequence 48301 until sequence 48317 validate;
}

How to retrieve the deleted rows in a table

You can use FLASHBACK TABLE to retrieve your rows. But before that you must enable row movement by following query

ALTER TABLE hr.employees ENABLE ROW MOVEMENT;
And there after firing the below query for the particular timestamp when your rows were existing at that time.

FLASHBACK TABLE hr.employees TO TIMESTAMP TO_TIMESTAMP('17-10-2011 12:19:00','DD-MM-YYYY HH24:MI:SS');

Example: select * from apps.PER_CONTACT_RELATIONSHIPS as of timestamp to_timestamp('31-05-2015 04:00:00','DD-MM-YYYY HH24:MI:SS');

FLASHBACK is used to retrive the dropped tables.

Use the following to retrieve the deleted and committed rows

select * from table_name as of timestamp to_timestamp(sysdate-(360/1440))
The above sample SQL will fetch the status of data in the table 6 hours earlier.

Recently changed profile options in Oracle EBS R12

select p.profile_option_name SHORT_NAME, n.user_profile_option_name "PROFILE NAME",

decode(v.level_id, 10001, 'Site', 10002, 'Application',

10003, 'Responsibility', 10004, 'User', 10005, 'Server',

10007, 'SERVRESP', 'UnDef') LEVEL_SET,

decode(to_char(v.level_id), '10001', '',

'10002', app.application_short_name, '10003', rsp.responsibility_key,

'10005', svr.node_name, '10006', org.name, '10004', usr.user_name,

'10007', 'Serv/resp', 'UnDef') "CONTEXT", v.profile_option_value VALUE, v.LAST_UPDATE_DATE  

from fnd_profile_options p,

fnd_profile_option_values v,

fnd_profile_options_tl n,

fnd_user usr,

fnd_application app,

fnd_responsibility rsp,

fnd_nodes svr,

hr_operating_units org

where p.profile_option_id = v.profile_option_id (+)

and p.profile_option_name = n.profile_option_name

--and upper(n.user_profile_option_name) like upper('BNE%')

--and trunc(v.LAST_UPDATE_DATE) > trunc(sysdate-170)

and usr.user_id (+) = v.level_value

and rsp.application_id (+) = v.level_value_application_id

and rsp.responsibility_id (+) = v.level_value

and app.application_id (+) = v.level_value

and svr.node_id (+) = v.level_value

and org.organization_id (+) = v.level_value

and v.LAST_UPDATE_DATE is not null 

order by last_update_date desc, short_name, level_set;

12.1.3 E-Business Suite Unable To Login Due To Error 'Guest user/pwd does not exist or match: GUEST/ORACLE' After 2015 PSU Patch

SYMPTOMS

E-Business Suite 12.1.3, IAS for Applications Technology

Users unable to login to the application, and when attempting to login the following errors are reported in the log files:

ERROR
-----------------------
15/07/20 14:13:32.570 html: chain failed
javax.servlet.ServletException: java.lang.RuntimeException: Guest user/pwd does not exist or match: GUEST/ORACLE
       at com.evermind[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.EvermindPageContext.handlePageThrowable(EvermindPageContext.java:899)
       at com.evermind[Oracle Containers for J2EE 10g (10.1.3.5.0) ].server.http.EvermindPageContext.handlePageException(EvermindPageContext.java:816)
       at _AppsLocalLogin._jspService(_AppsLocalLogin.java:307)
       at com.orionserver[Oracle Containers for J2EE 10g (10.1.3.5.0) ].http.OrionHttpJspPage.service(OrionHttpJspPage.java:59)
       at oracle.jsp.runtimev2.JspPageTable.service(JspPageTable.java:473)
       at oracle.jsp.runtimev2.JspServlet.internalService(JspServlet.java:594)

STEPS
-----------------------
The issue can be reproduced at will with the following steps:

1. Restart the system after 2015 PSU patching

2. Login to E-Business Suite

CAUSE

The 2015 PSU patch contains ojvm patches for the database JVM, which is also used by E-Business suite for authentication.  To confirm the issue test GUEST password validation via the following SQL:

SQL> SELECT fnd_web_sec.validate_login('GUEST','0RACLE') FROM dual;

FND_WEB_SEC.VALIDATE_LOGIN('GUEST','0RACLE')
--------------------------------------------------------------------------------
N

SQL> select fnd_message.get from dual;

GET
--------------------------------------------------------------------------------
PASSWORD_INVALID

SOLUTION

To implement the solution, please test the following steps in a development instance where the problem can be reproduced:

cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> STARTUP
SQL> @catbundle.sql psu apply
SQL> QUIT
========================================================================

EBS R12 Login Page Is Not Coming Up - java.lang.RuntimeException: Guest user/pwd does not exist or match: GUEST/ORACLE

SYMPTOMS

On Oracle Applications 12.1.3 version, IAS for Applications Technology:

E-Business Suite (EBS) Production Instance Login page is not coming up, Instead a blank page is being displayed.

The issue can be reproduced at will with the following steps:
1. Type the EBS URL on browser.
2. It redirects to EBS login page.
3. But a blank page is displayed.

SOLUTION

Please perform the below steps

1. Shutdown the EBS services.

2. The only supported way to change the Guest user password is to update the context variable s_guest_pass and run AutoConfig, which runs the AdminAppServer utility internally.

Note : Restriction on the GUEST User Password : The GUEST User password cannot include the special character "#".

 3. Run autoconfig on DB Node and then application node.

4. Execute the below sql again:

select fnd_web_sec.validate_password('GUEST','ORACLE') from dual;

Check whether query output is showing Y.

If no,  Please check whether the below error is seen on autoconfig log:

Unable to update GUEST_USER_PWD in database to GUEST/ORACLE - Password was not changed, this point to the DB parameter JAVA_JIT_ENABLED which is set as TRUE.

5. On 11g DB you need to have the below settings for EBS specifically, run the below sql

alter system set JAVA_JIT_ENABLED= FALSE scope = both;

6. Follow the steps 2 and 3 again.

7. Run the below command:

perl $FND_TOP/patch/115/bin/ojspCompile.pl --compile --flush -p 2

7. Check whether the compile completed successfully.

8. Restart the application services.

9. Retest the issue.

R12 - Logon is not working, no error message is shown

SYMPTOMS:

After the clone of an E-Business Suite Instance Release 12.0.6, the logon was not working as expected on the target Instance.

Launching the logon URL

http://<hostname>.<domain>:<Port>
 is redirected to

http://host.domain:port/OA_HTML/AppsLocalLogin.jsp

Result :

Nothing happens - no Homepage screen is presented, neither an error message is brought up.

SOLUTION:

To implement the solution, please execute the following steps :

1. Open a new Shell and source the APPS User Environment

2. Change into the $FND_TOP Patch directory

cd $FND_TOP/patch/115/bin

3. Compile the JSP files using following command :

perl ojspCompile.pl --compile --flush -p 2

4. Initiate the execution of Autoconfig on the DB- and the APPS_Tier(s)

5. Start the APPS-Tier(s) Services again and re-test the logon