Monday 12 November 2018

Query to get cloned date of an oracle instance

https://dbissues.blogspot.com/2018/11/last-cloned-date-of-oracle-instance.html

select CREATED
from V$DATABASE;

Wednesday 8 August 2018

Output Post Processor is Down with Actual Process is 0 And Target Process is 1

https://dbissues.blogspot.com/2018/08/output-post-processor-is-down-with.html

If you see OPP is Down with Actual Process is 0 And Target Process is 1 then do the following

SOLUTION:


1. Shutdown concurrent server via command adcmctl.sh under $COMMON_TOP/admin/scripts/<context_name>

2. To ensure concurrent manager down; check there is no FNDLIBR process running.

ps -ef | grep applmgr | grep FNDLIBR

3. Run adadmin to relink FNDSVC executable.
a. Invoke adadmin from command prompt
b. Choose option 2 (2. Maintain Applications Files menu)
c. Choose option 1 (1. Relink Applications programs )
d. Then type “FND” When prompted; ( Enter list of products to link (‘all’ for all products) [all] : FND )
e. Ensure adrelink is exiting with status 0

4. Start Concurrent Managers using adcmctl.sh

Sunday 24 June 2018

ORA-12899: value too large for column “APPLSYS”.”FND_PROFILE_OPTION_VALUES”.”PROFILE_OPTION_VALUE”

https://dbissues.blogspot.com/2018/06/ora-12899-value-too-large-for-column.html

SOLUTION:


Please execute the following steps:

Step 1 – Change the value dbutilfiledir in the database context file

($ORACLE_HOME/appsutil/$CONTEXT_NAME.xml)

Step 2 – Run autoconfig again.

ORA-12157: TNS:internal network communication error

https://dbissues.blogspot.com/2018/06/ora-12157-tnsinternal-network.html


ERROR


ORA-12157: TNS:internal network communication error

SOLUTION


This error ussualy occurs when the network files are not generated properly. Relink them using the below commands to fix the issue.

Step 1. Go to Oracle Home

cd $ORACLE_HOME/network/lib

Step 2. Regenerate file by giving the following Command

$make -f ins_net_client.mk ntcontab.o

Step 3. Got to bin folder of Oracle Home

$cd $ORACLE_HOME/bin

Step 4. Run the following given command

$genclntsh

Step 5. Go to Lib folder

$cd $ORACLE_HOME/rdbms/lib

Generate files through the given command

$make -f ins_rdbms.mk install

Step 6. Go to lib folder

$cd $ORACLE_HOME/sqlplus/lib

Step 7. Generate files through the given command.

$make -f ins_sqlplus.mk install

adstrtal.sh exits with status 150

https://dbissues.blogspot.com/2018/06/adstrtalsh-exits-with-status-150.html

We may get this error when we start all apps tier services.

[oracle@erp scripts]$ ./adstrtal.sh
You are running adstrtal.sh version 120.13
Enter the APPS username: apps
Enter the APPS password:****
Executing service control script: /d01/oracle/PROD/inst/apps/PROD_erp/admin/scripts/adopmnctl.sh
start script returned:
****************************************************
You are running adopmnctl.sh version 120.4
Starting Oracle Process Manager (OPMN) …
adopmnctl.sh: exiting with status 0
adopmnctl.sh: check the logfile /d01/oracle/PROD/inst/apps/PROD_erp/logs/appl/admin/log/adopmnctl.txt
for more information …
.end std out.
.end err out.
****************************************************
Executing service control script:
/d01/oracle/PROD/inst/apps/PROD_erp/admin/scripts/adapcctl.sh start script returned: ****************************************************
You are running adapcctl.sh version 120.6
Starting OPMN managed Oracle HTTP Server (OHS) instance …
adapcctl.sh: exiting with status 150
adapcctl.sh: check the logfile /d01/oracle/PROD/inst/apps/PROD_erp/logs/appl/admin/log/adapcctl.txt
for more information …
.end std out.
.end err out.
****************************************************
Executing service control script: /d01/oracle/PROD/inst/apps/PROD_erp/admin/scripts/adoacorectl.sh start script returned:
****************************************************
You are running adoacorectl.sh version 120.11
Starting OPMN managed OACORE OC4J instance …
adoacorectl.sh: exiting with status 150
adoacorectl.sh: check the logfile
/d01/oracle/PROD/inst/apps/PROD_erp/logs/appl/admin/log/adoacorectl.tx t for more information …
.end std out.
.end err out.
****************************************************
/d01/oracle/PROD/inst/apps/PROD_erp/admin/scripts/adformsctl.sh start script returned: ****************************************************
You are running adformsctl.sh version 120.12
Starting OPMN managed FORMS OC4J instance …
adformsctl.sh: exiting with status 150
adformsctl.sh: check the logfile
/d01/oracle/PROD/inst/apps/PROD_erp/logs/appl/admin/log/adformsctl.txt for more information…

Solution :

Open a new shell and set your apps environment, and then run the following:

$cd $ADMIN_SCRIPTS_HOME adopmnctl.sh stop
Now Check if  it is really down: ps -ef | grep -i opm
Delete the following:
$ rm -fr $INST_TOP/ora/10.1.3/j2ee/oacore/persistence/*
$ rm -fr $INST_TOP/ora/10.1.3/j2ee/oafm/persistence/*
$ rm -fr $INST_TOP/ora/10.1.3/j2ee/forms/persistence/*
Run the following script
$adopmnctl.sh start
Start all applications services and check if it completes with status 0
$adstrtal.sh.sh apps/[appspassword]

How to Create Group and User in Linux 7.2

https://dbissues.blogspot.com/2018/06/how-to-create-group-and-user-in-linux-72.html


1:  Command to create user.
useradd   -r  shafqat

2:) Command to create  group.
groupadd  dba

3:) Command to assign  group to a user.
usermod  -a  -G  dba  shafqat

4:) Command to delete group.
groupdel  dba

5:) Command to delete user.
userdel   shafqat


Change a User’s Primary Group
Sometimes you might want to switch out the primary group that a user is assigned to, which you can do with this command:
usermod -g <groupname> username
You need to the useradd command to add new users to existing group (or create a new group and then add user). If group does not exist, create it. The syntax is as follows:
useradd -G {group-name} username

RMAN SIGN

https://dbissues.blogspot.com/2018/06/rman-sign.html

%a          Specifies the activation id number of the database.

%A         Specifies the activation id number of the database completed 0

%c          Specifies the copy number of the backup piece within a set of duplexed backup pieces. The maximum value is 256.

%d          Specifies the name of the database--

%D         Specifies the current day of the month in format DD--

%e          Specifies the archived log sequence number--

%f           Specifies the absolute file number           
       
%F          Combines the DBID, day, month, year, and sequence into a unique and repeatable generated name--

%h          Specifies the archived redo log thread number--

%I           Specifies the DBID--

%M        Specifies the month in format MM--

%n          Specifies the name of the database. padded on the right with x characters to a total length of eight characters.

%N         Specifies the tablespace name.

%p          Specifies the piece number within the backup set

%r           Resetlogs ID

%s          Specifies the backup set number

%S          Specifies the backup set number completed 0

%t           Specifies the backup set time stamp.

%T          Specifies the year, month, and day in format YYYYMMDD

%u          Specifies an 8-character name constituted by compressed representations of the backup set or image copy number.

%U         Specifies a system-generated unique file name.

%Y          Specifies the year in this format: YYYY.

How to configure VNC server on Red Hat server and using VNC Viewer connect the server remotely from Windows XP.

https://dbissues.blogspot.com/2018/06/vnc-configuration.html


Step-by-step


In this section, I’ll show how to configure VNC server on Red Hat server and using VNC Viewer connect the server remotely from Windows XP.

1. On Red Hat server, login with your username that you want to enable remote access. In this example, I will use ‘admin’ user.




2. Open Terminal, type ‘vncpasswd’. Type your password and verify password again. This command will use to set you password for remote access for the current user.





3. Before next step, you need to logged on as root by type ‘su root’.





4. Edit the file /etc/sysconfig/vncservers by type ‘ vi /etc/sysconfig/vncservers’.

Note: If you are new to Linux, vi is an editor tool in command line mode on Linux.



5. You’ll see text file as in the figure below. Next, I’ll edit on the highlight line.




6. Uncomment on the highlight line. If you have more than one usernames that want to enable remote acces, you can change VNCSERVERS value in this format, “1:username1 2:username2 3:username3 ….”.

In this example, I have only one user which is root so this line of mine is ‘VNCSERVERS = “1:admin”‘.





7. Now save the file and exit.

To save file, hold ESC + ‘:’ and type ‘wq’ to write and quit file.





8. Next, log off the user if you are not user ‘root’ and log in as root. Enable VNC service by type ‘chkconfig vncserver on’. Then, start the VNC service by type ‘service vncserver start’.





9. If you have firewall enable on Red Hat, be sure that your firewall configuration won’t block connection from remote computer by open port TCP 5901 for remote access. Open Applications -> System Settings -> Security Level. Add ‘5901:tcp’ on Other ports.

Note: VNC uses TCP protocol on port 5901.





10. Now you can connect Red Hat server from remote computer. On my Windows XP computer, open VNC Viewer on Windows XP, type IP Address of Red Hat server with number as a username specify in step 6. In this example, I want to remote to Red Hat server as ‘admin’ user which I assign as number 1 in step 6 (1:admin) and my Red Hat server is 10.110.141.220. So I type ‘10.110.141.220:1′.

Note: You can download VNC Viewer for free at Download VNC® Viewer





11. Type your password for ‘admin’ user which has been assign in step 2.





12. Now you have connect to Red Hat server remotely. But you’ll see that the interface looks different. You have to do a little thing more.





13. On Red Hat server, open terminal and type ‘vi /home/username/.vnc/xstartup’. In this example, I type ‘vi /home/admin/.vnc/xstartup’.

Note: If you going to enable remote access for user ‘root’, the file would be at ‘/root/.vnc/xstartup’.





14. Uncomment these two lines and save the file.

Note: If you want to force to load Gnome or KDE Desktop on remote access, edit the last line from ‘twm &’ to ‘startx &’ for Gnome and ‘startkde &’ for KDE Desktop.




15. Type ‘service vncserver restart’ to apply changes.




16. Reconnect using VNC viewer on remote computer again. Now you will see the desktop as you were log in at the server but now you’re remotely :).



Saturday 23 June 2018

Find out APPS/SYSADMIN password in Oracle Apps R12 if you lost or forgotten

https://dbissues.blogspot.com/2018/06/find-out-appssysadmin-password-in.html


Step to Find APPS PASSWORD :


Step #1


sqlplus> sqlplus system/system_password
sqlplus / as sysdba


Step#2: Create Function to decrypt the encrypted password

SQL> create FUNCTION apps.decrypt_pin_func(in_chr_key IN VARCHAR2,in_chr_encrypted_pin IN VARCHAR2) RETURN VARCHAR2 AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String';
/
Function created.

Step#3: Query for password
set linesize 200 long 300
SQL> select ENCRYPTED_FOUNDATION_PASSWORD from apps.fnd_user where USER_NAME='GUEST';
ENCRYPTED_FOUNDATION_PASSWORD
--------------------------------------------------------------------------------
ZG379C5C883FF56235BE975D075A6FD2E7A749279B7BFCE331530B443E86F41A1966E3E4B1864519DB88BC07D58666AD837B

Step#4: Get the Apps password using encrypted guest password
SQL> SELECT apps.decrypt_pin_func('GUEST/ORACLE','ZG379C5C883FF56235BE975D075A6FD2E7A749279B7BFCE331530B443E86F41A1966E3E4B1864519DB88BC07D58666AD837B') from dual;
APPS.DECRYPT_PIN_FUNC('GUEST/ORACLE','ZG379C5C883FF56235BE975D075A6FD2E7A749279B
--------------------------------------------------------------------------------
APPS123

Step#5: Test apps password
SQL> conn apps/APPS123;
Connected.

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

Step to Find SYSADMIN PASSWORD :


Step# 1:
SQL> conn apps/APPS123;
Connected.
================================================================

--ALL_USER ACCEPT APPS USER ---

Step#2:
SQL> CREATE OR REPLACE PACKAGE get_pwd AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2;
END get_pwd;
/
Package created.

Step# 3:
SQL>
CREATE OR REPLACE PACKAGE BODY get_pwd AS
FUNCTION decrypt (KEY IN VARCHAR2, VALUE IN VARCHAR2)
RETURN VARCHAR2
AS LANGUAGE JAVA NAME 'oracle.apps.fnd.security.WebSessionManagerProc.decrypt(java.lang.String,java.lang.String) return java.lang.String'; END get_pwd;
/
Package body created.

Step# 4:
SELECT usr.user_name,
       get_pwd.decrypt
          ((SELECT (SELECT get_pwd.decrypt
                              (fnd_web_sec.get_guest_username_pwd,
                               usertable.encrypted_foundation_password
                              )
                      FROM DUAL) AS apps_password
              FROM fnd_user usertable
             WHERE usertable.user_name =
                      (SELECT SUBSTR
                                  (fnd_web_sec.get_guest_username_pwd,
                                   1,
                                     INSTR
                                          (fnd_web_sec.get_guest_username_pwd,
                                           '/'
                                          )
                                   - 1
                                  )
                         FROM DUAL)),
           usr.encrypted_user_password
          ) PASSWORD
  FROM fnd_user usr
 WHERE usr.user_name = '&USER_NAME';
------------------------ -----------------
SYSADMIN SYSADMIN123

Step#5 : Bellow sql will help you to validate SYSADMIN Password
SQL> select fnd_web_sec.validate_login('SYSADMIN','SYSADMIN123) from dual;
FND_WEB_SEC.VALIDATE_LOGIN('SYSADMIN','SYSADMIN123')
--------------------------------------------------------------------------------
Y

Oracle Database context file Information

https://dbissues.blogspot.com/2018/06/oracle-database-context-file-information.html


Database context file location :-
Database context file called the <CONTEXT_NAME>.xml contains the configuration information for the database tier & is located in /u01/oracle/PROD/db/tech_st
11.1.0/appsutil/Server_PROD.xml <---------(contextfile).
(context_file.xml).

Application context file location :-
Application context file called the <CONTEXT_NAME>.xml contains the configuration information for the application tier & is located in  /u01/oracle/PROD/inst/appl/admin/PROD_Server.xml. <-------(contextfile).

What Contextfile contains:-
 The context file contains host names,domain name , directory structure, port numbers used The AutoConfig feature of Oracle application manager(OAM)
 is used to update & manage context files.

You can check contextfile location by the following command:-
sqlplus!  select NAME,VISSION,PATH  from FND_OAM_CONTEXT_FILES;
Autoconfig work:-

The autoconfig script uses information from the context file to generate all applications configuration files & updates database profiles.

How to Run autoconf:-
Step 1 : Stop all services
$ $INST_TOP/apps/PROD_Server/app/admin/script/adstpall.sh APPS/<apps password>
Step 2:From same above location Run the autoconfig script, $adautocfg.sh .

How to run adconfig in datbase tier:-
/u01/oracle/db/tech_st/11.1.0/appsutil/bin/adconfig.sh <------location
adconfig.sh contextfile=/u01/oracle/db/tech_st/11.1.0/appsutil/PROD_Server.xml <-------(contextfile)

How to run adconfig in Application tier:-
/u01/oracle/apps/apps_st/appl/ad/12.0.0/bin/adconfig.sh <-------location
adconfig.sh contextfile=/u01/oracle/PROD/apps/apps_st/ad/12.0.0/bin/ PROD_Server.xml <---(contextfile)

Why we use adconfig.sh
The adconfig.sh would update the database with the XML entries and create the Listener and TNSNAMES.ora.

What is difference between ADpatch and Opatch:--
ADPATCH is utility to apply ORACLE application Patches whereas OPATCH is utility to apply database patches.

Checking status of all the Concurrent Managers from backend.

https://dbissues.blogspot.com/2018/06/checking-status-of-all-concurrent.html

In Oracle Applications, when we have to check the status of Concurrent Managers, we usually login to Oracle Applications and select the following path:
System Administrator >> Concurrent : Manager >> Administer
On this screen, we see the Actual and Target processes for a concurrent manager and if they are same and nonzero, we conclude that the CM is working fine.
Now, I am going to post a simple sql script which shows the same output as shown in the screen above. Here it goes:
select decode(CONCURRENT_QUEUE_NAME,'FNDICM','Internal Manager','FNDCRM','Conflict Resolution Manager','AMSDMIN','Marketing Data Mining Manager','C_AQCT_SVC','C AQCART Service','FFTM','FastFormula Transaction Manager','FNDCPOPP','Output Post Processor','FNDSCH','Scheduler/Prereleaser Manager','FNDSM_AQHERP','Service Manager: AQHERP','FTE_TXN_MANAGER','Transportation Manager','IEU_SH_CS','Session History Cleanup','IEU_WL_CS','UWQ Worklist Items Release for Crashed session','INVMGR','Inventory Manager','INVTMRPM','INV Remote Procedure Manager','OAMCOLMGR','OAM Metrics Collection Manager','PASMGR','PA Streamline Manager','PODAMGR','PO Document Approval Manager','RCVOLTM','Receiving Transaction Manager','STANDARD','Standard Manager','WFALSNRSVC','Workflow Agent Listener Service','WFMLRSVC','Workflow Mailer Service','WFWSSVC','Workflow Document Web Services Service','WMSTAMGR','WMS Task Archiving Manager','XDP_APPL_SVC','SFM Application Monitoring Service','XDP_CTRL_SVC','SFM Controller Service','XDP_Q_EVENT_SVC','SFM Event Manager Queue Service','XDP_Q_FA_SVC','SFM Fulfillment Actions Queue Service','XDP_Q_FE_READY_SVC','SFM Fulfillment Element Ready Queue Service','XDP_Q_IN_MSG_SVC','SFM Inbound Messages Queue Service','XDP_Q_ORDER_SVC','SFM Order Queue Service','XDP_Q_TIMER_SVC','SFM Timer Queue Service','XDP_Q_WI_SVC','SFM Work Item Queue Service','XDP_SMIT_SVC','SFM SM Interface Test Service') as "Concurrent Manager's Name", max_processes as "TARGET Processes", running_processes as "ACTUAL Processes" from apps.fnd_concurrent_queues where CONCURRENT_QUEUE_NAME in ('FNDICM','FNDCRM','AMSDMIN','C_AQCT_SVC','FFTM','FNDCPOPP','FNDSCH','FNDSM_AQHERP','FTE_TXN_MANAGER','IEU_SH_CS','IEU_WL_CS','INVMGR','INVTMRPM','OAMCOLMGR','PASMGR','PODAMGR','RCVOLTM','STANDARD','WFALSNRSVC','WFMLRSVC','WFWSSVC','WMSTAMGR','XDP_APPL_SVC','XDP_CTRL_SVC','XDP_Q_EVENT_SVC','XDP_Q_FA_SVC','XDP_Q_FE_READY_SVC','XDP_Q_IN_MSG_SVC','XDP_Q_ORDER_SVC','XDP_Q_TIMER_SVC','XDP_Q_WI_SVC','XDP_SMIT_SVC');
save the above SQL in a script as “cmstatus.sql
Connect as “apps” and run the above script:
sqlplus apps/******

SQL> set pagesize 9999

SQL> @cmstatus.sql

Concurrent Manager's Name                      TARGET Processes ACTUAL Processes
---------------------------------------------- ---------------- ----------------
Service Manager: AQHERP                         1                1
Output Post Processor                           1                1
Workflow Document Web Services Service          1                1
WMS Task Archiving Manager                      2                2
Marketing Data Mining Manager                   5                5
Conflict Resolution Manager                     1                1
Internal Manager                                1                1
Scheduler/Prereleaser Manager                   1                1
Standard Manager                               10               10
PO Document Approval Manager                    3                3
Receiving Transaction Manager                   3                3
FastFormula Transaction Manager                 1                1
PA Streamline Manager                           1                1
Inventory Manager                               5                5
INV Remote Procedure Manager                    5                5
Workflow Agent Listener Service                 1                1
Workflow Mailer Service                         1                1
Transportation Manager                          10               10
C AQCART Service                                1                1
Session History Cleanup                         1                1
UWQ Worklist Items Release for Crashed session  1                1
SFM Controller Service                          1                1
SFM Order Queue Service                         1                1
SFM Work Item Queue Service                     1                1
SFM Fulfillment Actions Queue Service           1                1
SFM Fulfillment Element Ready Queue Service     1                1
SFM Event Manager Queue Service                 1                1
SFM Inbound Messages Queue Service              1                1
SFM Timer Queue Service                         1                1
SFM Application Monitoring Service              1                1
SFM SM Interface Test Service                   1                1
OAM Metrics Collection Manager                  1                1

32 rows selected.
It will show you the similar output as shown by Concurrent Manager Administer screen.