Thursday, January 20, 2011

Change Default APPS password

Change Default APPS password

  • Shutdown concurrent manager
  • logon as sysadmin
  • goto sysadmin-->security-->Oracle-->Register
  • Query for APP%
  • Change the APPLSY & APPS Password (Both the Password should be same)
  • Logon to TOAD as system/manager
  • Run the command
Code :
Alter user APPS identified by <
(this password should be identical as in step 5) 
Alter user APPLSYS identified by <
(this password should be identical as in step 5)

  • Edit startup and shutdown scripts (e.g. & in the following lines:
Code :
sh apps/<> -- Startup script
sh apps/<>-- Shutdown script
  • Edit the following files (change the path according to your instance) – See step 10 to find the editing instructions.
Code :
-- Optional
-- Optional

Find the word password and type the new password against the password field where the original value is 

Note: There might be many password fields in these files. Do not change other password fields where the default password is not APPS.
  • Use vi command to open the aforementioned files
Code :
:set ic
/password – This will take the cursor 
to the first password field 

If this password field has default value as APPS, change it.
If this is not the field then press “n” to go to the next password field. Do this till you get the correct field.
Save the file once the change is made.

Important: There will be only one password field with default value as APPS in each of the files. So you need to make only one change per file.
Restart all the services (bounce the DB & restart all services)[/quote]
  • Login to application and test whether you can enter into the forms.

Sukhwinder Singh


Script to generate a hot backup copy script from any database

Script to generate a hot backup copy script from any database

set serveroutput on
set heading off
set feedback off
spool c:\scripts\backup.sql
fname varchar2(55);
tname varchar2(55);
tname1 varchar2(55);
cursor tspaces is
select tablespace_name,file_name from v$datafile,sys.dba_data_files
where enabled like '%WRITE%' and file# = file_id order by 1
dbms_output.put_line('spool hotback');
open tspaces;
fetch tspaces into tname,fname;
tname1 := tname;
dbms_output.put_line('alter tablespace '||tname||' begin backup;');
while tspaces%FOUND loop
if tname1 != tname then
dbms_output.put_line('alter tablespace '||tname1||' end backup;');
dbms_output.put_line('alter tablespace '||tname||' begin backup;');
tname1 := tname;
end if;
dbms_output.put_line('!copy '||fname||' c:\VALEN\DBFILES\');
--Change the line above line with o.s specific copy command.
fetch tspaces into tname,fname;
end loop;
dbms_output.put_line('alter tablespace '||tname1||' end backup;');
close tspaces;
dbms_output.put_line('spool off');
spool off
set heading on
set feedback on
set serveroutput off

Sukhwinder Singh.


Steps to clone a database from PROD to TEST

Steps to clone a database from PROD to TEST

The following is a brief Guideline on steps to follow to clone you Oracle Applications from PROD to TEST

  • log In to PROD Server as applprod user
  • go to /applprod/prodappl
  • run APPSORA.env
  • sqlplus system/manager
  • SQL> alter database backup controlfile to trace;
  • This will generate a trace file in $ORACLE_HOME//admin/_/udump/*.trc
  • Go to above directory
  • Run ls - alt
  • Find out latest trace file and edit the trace file
    • SID to desired SID (PROD to TEST)
    • Change dbf path accordingly.
  • Shutdown database (Shutdown normal both production & test server by using , your PROD stop script, and your TEST stop script, respectively )
  • Now ftp to your server as applprod user and download the file from PROD server to Test server in /appltest directory .
  • Rename /appltest/testdata directory to /appltest/testdatabk
  • Create a new testdata directory in /appltest
  • Copy all datafiles, control file etc.of to new /appltest/testdata from PROD
    • (E.g rcp applprod @ ebsnode2:/applprod/proddata/*.dbf) and rename cntrl*.dbf to cntrl*.bak on test server
  • Run TEST instance .env file from /appltest/testdb/9.2.0 .
  • Now run the edited trace file (this will recreate the TEST database with PROD configuration) (sqlplus /nolog @edited script.sql)
  • This will start up TEST oracle database
  • Change profile_option_values from FND_profile_option_values table
Select * from fnd_concurrent_queues
Where control_code=’N’

Update fnd_concurrent_queues
set target_node=NULL

Table ICX_Parameters set session_cookie= null
  • Clear concurrent Manager table from toad by using the following commands:
UPDATE fnd_concurrent_processes
SET process_status_code = 'K'
WHERE process_status_code not in ('K', 'S');

UPDATE fnd_concurrent_queues
SET running_processes = 0, max_processes = 0;

UPDATE fnd_concurrent_queues
SET control_code = NULL
WHERE control_code not in ('E', 'R', 'X')
AND control_code IS NOT NULL;

UPDATE fnd_concurrent_queues
SET target_node = null;

UPDATE fnd_concurrent_requests
SET phase_code = 'C', status_code = 'E'
WHERE status_code ='T' OR phase_code = 'R'

  • Copy the Server_ID from $FND_TOP/secure/ebsnode1_test.dbc and update the server_id column of FND_NODES table with that. Also change the NODE_NAME = EBSNODE1
  • Run the . APPSORA.env from /appltest/testappl directory
  • Now run following Java commands from command prompt:
java apps/apps AUTHENTICATION OFF DBC=/appltest/testappl/fnd/11.5.0/secure/TEST_ebsnode1/test.dbc

java apps/apps AUTHENTICATION OFF DBC=/appltest/testappl/fnd/11.5.0/secure/ebsnode1.<>_test.dbc

java apps/apps AUTHENTICATION OFF DBC=/appltest/testappl/fnd/11.5.0/secure/ebsnode1_test.dbc
  • Shutdown the Oracle database.
  • Start application by using from /appltest dir
  • If anything goes wrong check at http://:8000/OA_HTML/jsp/fnd/aoljtest.jsp
  • Login to Oracle Applications
  • Change Profile options from Application –
  • System Administrator(Responsibility) Profile-> System-> concurrent: GSM enabled = ‘N’

Warm Regards,
Sukhwinder singh.

There was an error in this gadget