Discoverer Startup and Shutdown

START DISCO

cd $ORACLE_HOME/j2ee/OC4J_BI_Forms/applications/discoverer/discoverer/
opmnctl status
df -h
opmnctl startall
 cd $ORACLE_HOME/dcm/bin
emctl status iasconsole
cd $ORACLE_HOME/dcm/bin
./dcmctl start
ps -fu $USER

###########################################################
STOP DISCO

cd $ORACLE_HOME/dcm/bin
ps -fu $USER
./dcmctl stop
emctl stop iasconsole
opmnctl status
opmnctl stopall

###########################################################

Windows Modern UI troubleshooter to fix the issue for Windows 8

This troubleshooter automatically fixes some issues that might prevent your apps from running, including suboptimal screen resolution and incorrect security or account settings.

Refer to the link:

http://download.microsoft.com/download/F/2/4/F24D0C03-4181-4E5B-A23B-5C3A6B5974E3/apps.diagcab

Signon Password

set lines 200
set pages 200
col USER_PROFILE_OPTION_NAME for a35
col PROFILE_OPTION_VALUE for a25

select t.USER_PROFILE_OPTION_NAME ,v.PROFILE_OPTION_VALUE
from applsys.FND_PROFILE_OPTIONS o ,applsys.FND_PROFILE_OPTIONS_TL t
,applsys.FND_PROFILE_OPTION_VALUES v
where o.PROFILE_OPTION_NAME = t.PROFILE_OPTION_NAME
and o.APPLICATION_ID=v.APPLICATION_ID(+)
and o.PROFILE_OPTION_ID=v.PROFILE_OPTION_ID(+)
and t.LANGUAGE='US' and t.USER_PROFILE_OPTION_NAME like 'Signon%'
order by 1 desc;

Who is locking your accounts (ORA-01017 and ORA-28000 errors) ?

I have decided to write this blog post after second time I received question on how to know from where are coming connections that are locking an account in an Oracle database…

Do not smile, I have seen at least two situations where, after a password change, a batch job was initiating plenty of connection (with previous wrong password) and no one was able to know from where this batch job was running (!!).

As a reminder, with default profile in Oracle 11g, accounts are automatically locked 1 day (PASSWORD_LOCK_TIME) after 10 failed login attempt (FAILED_LOGIN_ATTEMPTS):

SQL> SET lines 200 SQL> SET pages 200 SQL> SELECT * FROM dba_profiles WHERE PROFILE='DEFAULT' ORDER BY resource_name;   PROFILE RESOURCE_NAME RESOURCE LIMIT ------------------------------ -------------------------------- -------- ---------------------------------------- DEFAULT COMPOSITE_LIMIT KERNEL UNLIMITED DEFAULT CONNECT_TIME KERNEL UNLIMITED DEFAULT CPU_PER_CALL KERNEL UNLIMITED DEFAULT CPU_PER_SESSION KERNEL UNLIMITED DEFAULT FAILED_LOGIN_ATTEMPTS PASSWORD 10 DEFAULT IDLE_TIME KERNEL UNLIMITED DEFAULT LOGICAL_READS_PER_CALL KERNEL UNLIMITED DEFAULT LOGICAL_READS_PER_SESSION KERNEL UNLIMITED DEFAULT PASSWORD_GRACE_TIME PASSWORD 7 DEFAULT PASSWORD_LIFE_TIME PASSWORD 180 DEFAULT PASSWORD_LOCK_TIME PASSWORD 1 DEFAULT PASSWORD_REUSE_MAX PASSWORD UNLIMITED DEFAULT PASSWORD_REUSE_TIME PASSWORD UNLIMITED DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL DEFAULT PRIVATE_SGA KERNEL UNLIMITED DEFAULT SESSIONS_PER_USER KERNEL UNLIMITED   16 ROWS selected.

Oracle client session will received 10 times ORA-01017: invalid username/password; logon denied error message and then ORA-28000: the account is locked error message (for one day and then back to ORA-01017 error message).

The final question is how to identify from where (client IP address/name) are coming those tentative connections… I have done my testing using Oracle 11.2.0.3 running on Oracle Linux Server release 6.3.

ORA-01017/ORA-28000 with AUDIT_TRAIL

The first and preferred solution is with Oracle standard auditing feature. Start by setting initialization parameter AUDIT_TRAIL to db and restart your Oracle database as it is static parameter.

Then activate network auditing with (as SYS):

SQL> AUDIT network BY ACCESS;   AUDIT succeeded.

With below query you get everything needed:

SELECT * FROM dba_audit_session ORDER BY sessionid DESC;

Returncode column contains Oracle error code and so different of 0 if logon/logoff issue. The invalid password is the error we are chasing:

[oracle@server1 ~]$ oerr ora 1017 01017, 00000, "invalid username/password; logon denied" // *Cause: // *Action:

So if you find 1017 values in this column then we have found what we were looking for. For example with my test case where I intentionally specify a wrong password for my account:

SQL> SELECT username,userhost,returncode FROM dba_audit_session WHERE username='YJAQUIER' ORDER BY sessionid DESC;   USERNAME USERHOST RETURNCODE ------------------------------ -------------------- ---------- YJAQUIER server1 1017 YJAQUIER GVADT30596 0 YJAQUIER server1 0 YJAQUIER server1 0 . . .

And if you insist, as explained, you get:

SQL> SELECT username, account_status,lock_date, PROFILE FROM dba_users WHERE username='YJAQUIER';   USERNAME ACCOUNT_STATUS LOCK_DATE PROFILE ------------------------------ -------------------------------- -------------------- ------------------------------ YJAQUIER LOCKED(TIMED) 23-nov-2012 10:30:37 DEFAULT

If you set AUDIT_TRAIL to db behave the size of SYS.AUD$ table as a small list of audits are already implemented by default:

SQL> SET lines 200 SQL> SET pages 200 SQL> SELECT * FROM DBA_STMT_AUDIT_OPTS;   USER_NAME PROXY_NAME AUDIT_OPTION SUCCESS FAILURE ------------------------------ ------------------------------ ---------------------------------------- ---------- ---------- ALTER SYSTEM BY ACCESS BY ACCESS SYSTEM AUDIT BY ACCESS BY ACCESS CREATE SESSION BY ACCESS BY ACCESS CREATE USER BY ACCESS BY ACCESS ALTER USER BY ACCESS BY ACCESS DROP USER BY ACCESS BY ACCESS PUBLIC SYNONYM BY ACCESS BY ACCESS DATABASE LINK BY ACCESS BY ACCESS ROLE BY ACCESS BY ACCESS PROFILE BY ACCESS BY ACCESS CREATE ANY TABLE BY ACCESS BY ACCESS ALTER ANY TABLE BY ACCESS BY ACCESS DROP ANY TABLE BY ACCESS BY ACCESS CREATE PUBLIC DATABASE LINK BY ACCESS BY ACCESS GRANT ANY ROLE BY ACCESS BY ACCESS SYSTEM GRANT BY ACCESS BY ACCESS ALTER DATABASE BY ACCESS BY ACCESS CREATE ANY PROCEDURE BY ACCESS BY ACCESS ALTER ANY PROCEDURE BY ACCESS BY ACCESS DROP ANY PROCEDURE BY ACCESS BY ACCESS ALTER PROFILE BY ACCESS BY ACCESS DROP PROFILE BY ACCESS BY ACCESS GRANT ANY PRIVILEGE BY ACCESS BY ACCESS CREATE ANY LIBRARY BY ACCESS BY ACCESS EXEMPT ACCESS POLICY BY ACCESS BY ACCESS GRANT ANY OBJECT PRIVILEGE BY ACCESS BY ACCESS CREATE ANY JOB BY ACCESS BY ACCESS CREATE EXTERNAL JOB BY ACCESS BY ACCESS

So you must put in place a purging policy for this table.

ORA-01017/ORA-28000 without AUDIT_TRAIL

The only drawback of the previous solution is that you have to restart the database. And maybe two times because after problem solved you would like to deactivate auditing. This is most probably not reliable solution on a production database so I have been looking for a better solution with no database reboot.

I initially thought of the AFTER LOGON trigger but you need to be logged-in and the BEFORE LOGON does not exits. Then at same documentation place I found the AFTER SERVERERROR trigger and decided to give it a try.

First I created a dummy table to log server error (columns inherited from dba_audit_session dictionary table):

CREATE TABLE sys.logon_trigger ( USERNAME VARCHAR2(30), USERHOST VARCHAR2(128), TIMESTAMP DATE );

Second I created below trigger:

CREATE OR REPLACE TRIGGER sys.logon_trigger AFTER SERVERERROR ON DATABASE BEGIN IF (IS_SERVERERROR(1017)) THEN INSERT INTO logon_trigger VALUES(SYS_CONTEXT('USERENV', 'AUTHENTICATED_IDENTITY'), SYS_CONTEXT('USERENV', 'HOST'), SYSDATE); COMMIT; END IF; END; /

Then third simulated a wrong password access with my account and issued:

SQL> ALTER SESSION SET nls_date_format='dd-mon-yyyy hh24:mi:ss';   SESSION altered.   SQL> SET lines 200 SQL> col USERHOST FOR a30 SQL> SELECT * FROM sys.logon_trigger ORDER BY TIMESTAMP DESC;   USERNAME USERHOST TIMESTAMP ------------------------------ ------------------------------ -------------------- yjaquier ST\GVADT30596 23-nov-2012 11:05:56

And that’s it !!

How to get a list of locked accounts / check that account is locked?

All locked accounts

SELECT username, account_status, created, lock_date, expiry_date
  FROM dba_users
 WHERE account_status != 'OPEN';

Specific User account status

SELECT username, account_status, created, lock_date, expiry_date
  FROM dba_users
 WHERE account_status != 'OPEN' and username='&username';




Use following statement to unlock an account:

ALTER USER username ACCOUNT UNLOCK;

Setting Up Oracle Applications Desktop Integrator (ADI) 7.2

This documentation covers locating the latest version of ADI on Metalink, downloading it, installing it on the client machine and configuring it to access an Oracle Applications instance.

Step 1:        Log on to Oracle Metalink using your logon information (email address and password).

Step 2:        Search for and read “Application Desktop Integrator (ADI) – FAQ” (Doc ID: Note:106667.1) to obtain instructions to download the latest version of ADI.

(Adapted from the FAQ):

QUESTION: Where can I download ADI from?

Download ADI from Metalink

Log on to http://metalink.oracle.com
Click the Patches & Updates tab.
On the Patches & Updates page, click Advanced Search.
Enter:
Product or Product Family: Click the Torchlight icon to search for Applications Desktop Integrator (adi).
Release: ADI 7.2
Platform or Language: Microsoft Windows (32-bit) Client
Patch Type: Any
Press Go and the 7.2 base release will be available for download.

Step 3:        The results of the search are returned at the bottom of the page.

Click the link ‘3966101’ to locate the patchset.

Step 4:        You may be required to enter a username and password to access the file. Enter the email address and password that you used to log onto Metalink.

Step 5:        Details about the patchset are displayed on screen.

Click Download to begin downloading the file. Note that the download size is 71MB.

Step 6:        Click Save in the File Download dialog box, and then choose a location to save the file.

Step 7:        After downloading, execute the patchset by double-clicking on it.

Step 8:        Enter a location to unzip the files to, and then click Unzip.

Step 9:        Ensure that all files were unzipped correctly (you should see ‘5179 file(s) unzipped successfully’), and then click Ok.

Then click Close to close the WinZip Self-Extractor program.

Step 10:   In the folder that ADI was unzipped to, read the README.TXT file. It points the user to the ADI Installation Guide (ADI.PDF), which should be read before proceeding.

Step 11:    After reading the installation guide, double-click Setupadi.exe to begin installation.

Caution: Remove headphones while installing this product since it uses a lot of extremely annoying sound effects.

Step 12:   The Oracle ADI Installer loads.

Click Oracle Applications Desktop Integrator for Excel 2000/XP/2003.

Step 13:   Click Yes to begin installation.

Step 14:   The Oracle Installer Settings form is displayed. In the Company Name field, enter the name of the company. Then click Ok.

Step 15:   The Software Asset Manager appears.

Install all of the products listed under Available Products. The products can be installed individually (one at a time), or all products can be selected and installed.

Note: The ADI Online Help will require you to choose a language to install.

Step 16:   After installing the products, click Exit to exit the installer. You will be required to reboot the machine after exiting the installer.

Step 17:   After the computer restarts, load Applications Desktop Integrator by clicking on the Start button,All Programs, Oracle ADI, and finally Applications Desktop Integrator.

Note: Let the client log on to the machine so that you can configure ADI under the user’s id. This is necessary since signon details are not shared between users.

Step 18:   When the application is first loaded, a message appears informing the user to select an Applications database.

Step 19:   After reading the message by the Office Assistant, click on the Signon button.

Step 20:   The Signon form appears. Since there are no predefined databases, click Define Databasesto define a new Applications database.

Step 21:   The Select Database form appears. Click Add to add a database.

Step 22:   Fill out the fields in the Add Database Details form, and then click OK.

Copy sqlnet.ora and tnsnames.ora from the “806 NET80” directory in the Project Folder to c:orantnet80admin and c:orantnetworkadmin. Note that these files contain configuration information for the production instance only.

In addition, each instance has a unique server ID. To obtain the server ID for the production instance, navigate to the Project Folder, and then open the file “Server ID.txt”. Copy the hexadecimal string under MFGP and paste it into the Server ID field in the Add Database Details form.

Step 23:   After setting up the database details, save the information and log on to ADI using an Oracle Applications username:

After logging on, perform the following:

• Choose a Responsibility (for example, GL Super User)
• From the ADI Toolbar, click Submit Report
• Click Financial Statement
• Click in the Report Name, enter % as criteria, click OK and then select a report (for example, Unaudited Balance Sheet)
• Choose Period (for example, APR-07)
• Click the Publishing button
• Select Spreadsheet
• Click Submit

The job is submitted and completes within a few minutes. It will automatically open in Excel on completion.

Errors Encountered

Error Message: “No Listener”

Solution: Either instance on server does not exist or entry in TNSNAMES.ORA is incorrect. If TNS entry in the local TNSNAMES.ORA file is correct, then listener or database is not started on the server.

Error Message: “ORA-12571: TNS Packet Writer Failure”

Solution: Cannot send packets to the server. A firewall is blocking packets on the client machine.

Error Message: “ORA-12154: TNS: could not resolve service name”

Solution: Ensure that the service name is in c:orantnet80admintnsnames.ora

Check that c:orantnet80adminsqlnet.ora has names.default_domain and name.default_zone commented off.

Check %WINDIR%system32driversetchosts file has hostname and ip address.

Error Message: “An error occurred while attempting to establish an Applications File Server connection. There may be a network configuration problem, or the TNS listener may not be running. Nodename : ORACLESRV”

Solution: Incorrect FNDFS entry is in the TNSNAMES.ORA file. Recopy sqlnet.ora and tnsnames.ora from server onto client machine.

Metalink References

Subject: Tns Packet Writer Failure When Trying To Connect To ADI Ora-12571 (Doc ID: Note:264952.1)

Subject: Unable To Start ADI ORA-12571:TNS:Packet Writer Failure (Doc ID: Note:351088.1)

Subject: Troubleshooting the “Error Occurred While Attempting to Establish an Applications File Server Connection” (Doc ID:  Note:117012.1)

How to Backup Your BlogSpot Blog?

BlogSpot is one of the top free blogging platform but it comes with many limitations.  One of the biggest limitation is, Google owns the control of your blog. Google owns the right to delete your blog at any point of time. This usually comes when you are using your blog for spamming or for any other activities which violates BlogSpot TOS. Probably that’s one of the reason, I keep suggesting people to go for self hosted WordPress blog.

One thing which you can do apart from following BlogSpot guideline is taking a periodic backup of your BlogSpot blog. This will ensure that if something goes wrong with your blog in future, you can always restore your data and posts.

Taking backup of BlogSpot blog:

Any BlogSpot blogger can take a complete backup of blogspot posts, comments by using export feature of BlogSpot blog. Backup will be done in the format of .xml format, and you can store it on your local desktop. Unfortunately, this is more of a manual process, and depending upon how often you update your blog, you should take backup weekly or monthly.

A good idea is to take a backup of your customized blogSpot template at regular interval. This will solve two major problem:

• If your blog get deleted, you will have a complete back up of your theme.
• If anytime, while playing with template code, you made some blunder, you can quickly restore the template from your backup files.

I also recommend you to keep the backup files either on Google drive or on Dropbox, so you can access it from anywhere. Do let me know what method you are using to take backup of your Blog?

Here is a video which explains the whole process:

Welcome to My Oracle World

A good idea is to take a backup of your customized blogSpot template at regular interval. This will solve two major problem:

• If your blog get deleted, you will have a complete back up of your theme.
• If anytime, while playing with template code, you made some blunder, you can quickly restore the template from your backup files.

I also recommend you to keep the backup files either on Google drive or on Dropbox, so you can access it from anywhere. Do let me know what method you are using to take backup of your Blog?A good idea is to take a backup of your customized blogSpot template at regular interval. This will solve two major problem:

• If your blog get deleted, you will have a complete back up of your theme.
• If anytime, while playing with template code, you made some blunder, you can quickly restore the template from your backup files.

I also recommend you to keep the backup files either on Google drive or on Dropbox, so you can access it from anywhere. Do let me know what method you are using to take backup of your Blog?A good idea is to take a backup of your customized blogSpot template at regular interval. This will solve two major problem:

If your blog get deleted, you will have a complete back up of your theme.

If anytime, while playing with template code, you made some blunder, you can quickly restore the template from your backup files.

I also recommend you to keep the backup files either on Google drive or on Dropbox, so you can access it from anywhere. Do let me know what method you are using to take backup of your Blog?