Query to print SID, log, out etc details for a given req

select /*+ ORDERED */
       req.request_id
,      req.oracle_process_id
,      ses.sid
,      ses.serial#
,      ses.program
,      req.logfile_name log
,      req.outfile_name out
from  v$process pid
,     v$session ses
--,     apps.fnd_concurrent_processes prc
,     apps.FND_CONCURRENT_REQUESTS req
where req.request_id = &request_id
--and   req.controlling_manager = prc.concurrent_process_id
--and   prc.CONCURRENT_QUEUE_ID = q_id
--and   req.oracle_process_id IS NOT NULL
and   req.oracle_process_id = pid.spid(+)
and   pid.addr = ses.paddr(+)
/

APEX Installation/Configuration

                                                              

SQL> select name from v$database;

NAME

---------

TEST

SQL> select owa_util.get_version from dual;

GET_VERSION

--------------------------------------------------------------------------------

10.1.2.0.8

SQL> create tablespace APEX datafile '/oradata/data04/APEX01.dbf' size 1000M autoextend on next 50M maxsize 5000M;

Tablespace created.

Change your working directory to /apex

Full development environment - Run apexins.sql passing the following

4 arguments in the order shown:

[orat@db01 apex]$ sqlplus "/as sysdba"

SQL*Plus: Release 11.2.0.1.0 Production on Wed Aug 18 21:01:48 2010

Copyright (c) 1982, 2009, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

  

Execution of script will take 30-45 minutes.

SQL> @apexins APEX APEX TEMP /i/

...Compiled 622 out of 2720 objects considered, 0 failed compilation 22:49:32

...234 packages

...227 package bodies

...426 tables

...12 functions

...19 procedures

...3 sequences

...439 triggers

...1177 indexes

...175 views

...0 libraries

...4 types

...0 type bodies

...0 operators

...0 index types

...Begin key object existence check 22:49:32

...Completed key object existence check 22:49:32

...Setting DBMS Registry 22:49:32

...Setting DBMS Registry Complete 22:49:32

...Exiting validate 22:49:32

timing for: Validate Installation

Elapsed: 00:27:49.70

timing for: Development Installation

Elapsed: 00:35:32.03

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> @apxchpwd.sql

Enter a value below for the password for the Application Express ADMIN user.

Enter a password for the ADMIN user              []    Admin – Apex

Session altered.

...changing password for ADMIN

PL/SQL procedure successfully completed.

Commit complete.

SQL> alter user apex_public_user account unlock;

User altered.

SQL> alter user apex_public_user identified by apex;

User altered.

SQL> select version_no from apex_release;

VERSION_NO

--------------------------------------------------------------------------------

4.0.2.00.06

[apexd@disco ~]$ cd /d01/apex

[apexd@disco apexd]$ ls

Disk1  Disk2  lost+found  ofm_webtier_linux_11.1.1.2.0_32_disk1_1of1.zip

[apexd@disco apexd]$ ls -ltr

total 1222688

drwxr-xr-x 6 apexd dba        4096 Nov  4  2009 Disk1

drwxrwxr-x 3 apexd dba        4096 Nov  4  2009 Disk2

drwx------ 2 root    root      16384 Nov 29 21:58 lost+found

-rw-r--r-- 1 apexd dba  1250778396 Nov 29 22:59 ofm_webtier_linux_11.1.1.2.0_32_disk1_1of1.zip

[apexd@disco apexd]$ cd Disk1

[apexd@disco Disk1]$ ls -ltr

total 28

-rwxrwxr-x  1 apexd dba 11460 Oct 28  2009 runInstaller

drwxrwxr-x  3 apexd dba  4096 Nov  4  2009 plugins

drwxrwxr-x  9 apexd dba  4096 Nov  4  2009 doc

drwxrwxr-x  3 apexd dba  4096 Nov  4  2009 install

drwxr-xr-x 12 apexd dba  4096 Nov  4  2009 stage

[apexd@disco Disk1]$ pwd

/d01/apex/Disk1

[apexd@disco Disk1]$

 

---Installation screens were removed due to low size image.

/d01/apex/product/Oracle_apex/ohs/images

Configure dads.conf to point to APEX schema /home/apexd/Oracle_HTTP/Oracle_DEV/instances/instance1/config/OHS/ohs1/mod_plsql – dads.conf

Alias /i/ "/home/apexd/Oracle_HTTP/Oracle_DEV/ohs/images/"

<Location /pls/apex>

Order deny,allow

PlsqlDocumentPath docs

AllowOverride None

PlsqlDocumentProcedure wwv_flow_file_mgr.process_download

PlsqlDatabaseConnectString db01.ven.net:1521:APEX ServiceNameFormat

PlsqlNLSLanguage AMERICAN_AMERICA.AL32UTF8

PlsqlAuthenticationMode Basic

SetHandler pls_handler

PlsqlDocumentTablename wwv_flow_file_objects$

PlsqlDatabaseUsername APEX_PUBLIC_USER

PlsqlDefaultPage apex

PlsqlDatabasePassword apexd

PlsqlRequestValidationFunction wwv_flow_epg_include_modules.authorize

Allow from all

</Location>

Copy Images from DB node APEX folder, Edit dads.conf – bounce opmnctl

URL for APEX : http://disco.ven.net:8080/pls/apex/apex_admin

Change port number :

/d01/apex/product/Oracle_apex/instances/instance1/config/OHS/ohs1/httpd.conf

Start/Stop Scripts

[apexd@disco bin]$ pwd

/d01/apex/product/Oracle_apex/instances/instance1/bin

[apexd@disco bin]$ ./opmnctl status

Processes in Instance:TEST

---------------------------------+--------------------+---------+---------

ias-component                    | process-type       |     pid | status

---------------------------------+--------------------+---------+---------

TEST                             | WebCache-admin     |   32114 | Alive

TEST                             | WebCache           |   32115 | Alive

ohs1                             | OHS                |   32113 | Alive

Quick Cloning steps

Assumptions:
============


1. ORACLE_HOME tech stack would be cloned beforehand
2. Apps TechStack would be cloned before hand
3. The locations of the datafiles,redo log files,temp files ( data tops from adcfgclone) would be identified
and new locations would be mapped.
This mapping would be created in 3 files

data_tops.txt    ===> Datafile mapping
temp_tops.txt    ===> Temp datafiles mapping
log_tops.txt    ===> Online redolog files mapping

The format of each file would be
Old_loc_path:New_loc_path

4. RMAN Backup files (/backup mount point) will be available on target database node.

Onetime Activities
------------------
Cloning DB techstack
--------------------

Restore ORACLE_HOME from PROD and run the adcfgclone with the correct port pools

Cloning Apps Tech Stack
-----------------------
Restore Apps Tech Stack directories

Clone with the correct parameters and teh correct port pools for REP instance

Restore custom.env file

The dbtier script would do the following

a) Shutdown running Instance
b) Clean up all the tops based on the above 3 top files but retaining the tech stacks
c) Pick up the backup from a mentioned directory
d) Initiate restore with a specified no of threads between 2 and 5
e) Restore db to new locations as mentioned in the top files and recover and open the database
f) Shutdown database
g) Run nid to rename the database to VENK (New DB name)
h) Change system and sys passwords in the database
i) Clean up fnd_nodes
j) Startup listener if it is not running
k) Run autoconfig for db tier
l) Run db link creation script
m) Truncate the fnd_concurrent_requests
o) Update fnd_concurrent_queues to update the node_names
p) Update the Sitename -
q) Update scripts in the Appendix

5) Initiate the apps tier cloning script

This would

a) Run autoconfig
b) Change Apps Passwords
c) Rerun Autoconfig if the above passwords are changed.

Appendix
--------
Update Site Name Profile

update fnd_profile_option_values
set PROFILE_OPTION_VALUE='REP (Daily refresh from PROD)'
where PROFILE_OPTION_ID=125 ; 

Discoverer URL -

update fnd_profile_option_values
set PROFILE_OPTION_VALUE='http://dis.ven.net:7777/discoverer/plus?Connect=[APPS_SECURE]'
where profile_option_id=3567; 

update fnd_profile_option_values
set PROFILE_OPTION_VALUE='http://dis.ven.net:7777/discoverer/viewer?Connect=[APPS_SECURE]'
where profile_option_id=4987; 

update fnd_profile_option_values
set PROFILE_OPTION_VALUE='exitURL=http://erp.ven.net/OA_HTML/OA.jsp?OAFunc=OAHOMEPAGE'
where profile_option_id=5882;

update fnd_profile_option_values
set PROFILE_OPTION_VALUE='/d02/oracle/erpappl/bne/11.5.0/log'
where profile_option_id=6472;

update fnd_profile_option_values
set PROFILE_OPTION_VALUE='Trace'
where profile_option_id=6481;

update fnd_profile_option_values
set PROFILE_OPTION_VALUE='/oa_servlets/'
where profile_option_id=6482;

update fnd_profile_option_values
set PROFILE_OPTION_VALUE='/d02/oracle/erpappl/bne/11.5.0/upload'
where profile_option_id=6484;

update fnd_profile_option_values
set PROFILE_OPTION_VALUE='/d02/oracle/erpappl/bne/11.5.0/upload/import'
where profile_option_id=6487;

update fnd_profile_option_values
set PROFILE_OPTION_VALUE='/d02/oracle/erpappl/bne/11.5.0/upload'
where profile_option_id=6489;

6)    Update POR : CA Certificate File Name profile option.

update fnd_profile_option_values
set PROFILE_OPTION_VALUE='/d02/oracle/erpora/iAS/Apache/Apache/conf/ssl.crt/ca-bundle.crt'  where profile_option_id=5223 ;

7)    Disable email preferences for all email account.

update fnd_user_preferences
set PREFERENCE_VALUE = 'DISABLED'
where user_name not in ('XYZ') ;
and preference_name='MAILTYPE' and module_name='WF';

8)    Update WF ‘MAIL’ status to ‘SENT’

update WF_NOTIFICATIONS set mail_status = 'SENT' where mail_status = 'MAIL';

UPDATE wf_notifications SET mail_status = 'SENT' WHERE message_type IN ('XDPWFSTD', 'WFERROR');

9)  RESTORE the USERS, PRINTERS and profile options from backup taken before remove target instance.

Variuos DDL queries to get TBS creation scripts

Scripts 1:
set lines 132
set pages 200
set long 4000
select dbms_metadata.get_ddl('TABLESPACE', tablespace_name) DDL from dba_tablespaces;

Out put will be like below:
select dbms_metadata.get_ddl('TABLESPACE','SYSTEM') from dual;
select dbms_metadata.get_ddl('TABLESPACE','SYSAUX') from dual;
select dbms_metadata.get_ddl('TABLESPACE','UNDOTBS1') from dual;
select dbms_metadata.get_ddl('TABLESPACE','TEMP') from dual;
select dbms_metadata.get_ddl('TABLESPACE','USERS') from dual;
select dbms_metadata.get_ddl('TABLESPACE','DW_ORABAM') from dual;
select dbms_metadata.get_ddl('TABLESPACE','DW_MDS') from dual;
select dbms_metadata.get_ddl('TABLESPACE','DW_SOAINFRA') from dual;
select dbms_metadata.get_ddl('TABLESPACE','DW_IAS_TEMP') from dual;
select dbms_metadata.get_ddl('TABLESPACE','DW_IAS_ORASDPM') from dual;
select dbms_metadata.get_ddl('TABLESPACE','EDI_ORABAM') from dual;
select dbms_metadata.get_ddl('TABLESPACE','EDI_MDS') from dual;
select dbms_metadata.get_ddl('TABLESPACE','EDI_IAS_ORASDPM') from dual;
select dbms_metadata.get_ddl('TABLESPACE','EDI_SOAINFRA') from dual;
select dbms_metadata.get_ddl('TABLESPACE','EDI_IAS_TEMP') from dual;

Script 2:

select 'create tablespace ' || df.tablespace_name || chr(10)
 || ' datafile ''' || df.file_name || ''' size ' || df.bytes/1024/1024
 || decode(autoextensible,'N',null, chr(10) || ' autoextend on maxsize '
 || maxbytes/1024/1024)
 || chr(10)
 || 'default storage ( initial ' || initial_extent
 || decode (next_extent, null, null, ' next ' || next_extent )
 || ' minextents ' || min_extents
 || ' maxextents ' ||  decode(max_extents,'2147483645','unlimited',max_extents)
 || ') ;'
 from dba_data_files df, dba_tablespaces t
 where df.tablespace_name=t.tablespace_name
/


Script 3:

REM
REM                    SCRIPT FOR CREATING TABLESPACES
REM
REM This script must be run by a user with the DBA role.
REM
REM Running this script will in turn create a script to build all the
REM tablespaces in the database.  This created script, create_tablespaces.sql,
REM can be run by any user with the DBA role or with the 'CREATE TABLESPACE'
REM system privilege.
REM

set verify off;
set termout off;
set feedback off;
set echo off;
set pagesize 0;

set termout on;
select 'Creating tablespace build script...' from dual;
set termout off;

create table ts_temp (lineno number, ts_name varchar2(30),
                    text varchar2(800));

DECLARE
   CURSOR ts_cursor IS select   tablespace_name,
                                initial_extent,
                                next_extent,
                                min_extents,
                                max_extents,
                                pct_increase,
                                status
                        from    sys.dba_tablespaces
                        where tablespace_name != 'SYSTEM'
                        and status != 'INVALID'
                        order by tablespace_name;
   CURSOR df_cursor (c_ts VARCHAR2) IS select   file_name,
                                                bytes,
                                                autoextensible,
                                                maxbytes,
                                                increment_by
                                       from     sys.dba_data_files
                                       where    tablespace_name = c_ts
                                         and    tablespace_name != 'SYSTEM'
                                       order by file_name;
   lv_tablespace_name   sys.dba_tablespaces.tablespace_name%TYPE;
   lv_initial_extent    sys.dba_tablespaces.initial_extent%TYPE;
   lv_next_extent       sys.dba_tablespaces.next_extent%TYPE;
   lv_min_extents       sys.dba_tablespaces.min_extents%TYPE;
   lv_max_extents       sys.dba_tablespaces.max_extents%TYPE;
   lv_pct_increase      sys.dba_tablespaces.pct_increase%TYPE;
   lv_status            sys.dba_tablespaces.status%TYPE;
   lv_file_name         sys.dba_data_files.file_name%TYPE;
   lv_bytes             sys.dba_data_files.bytes%TYPE;
   lv_autoextensible    sys.dba_data_files.autoextensible%TYPE;
   lv_maxbytes          sys.dba_data_files.maxbytes%TYPE;
   lv_increment_by      sys.dba_data_files.increment_by%TYPE;
   lv_first_rec         BOOLEAN;
   lv_string            VARCHAR2(800);
   lv_lineno            number := 0;

   procedure write_out(p_line INTEGER, p_name VARCHAR2,
             p_string VARCHAR2) is
   begin
     insert into ts_temp (lineno, ts_name, text) values
            (p_line, p_name, p_string);
   end;

BEGIN
   OPEN ts_cursor;
   LOOP
      FETCH ts_cursor INTO lv_tablespace_name,
                           lv_initial_extent,
                           lv_next_extent,
                           lv_min_extents,
                           lv_max_extents,
                           lv_pct_increase,
                           lv_status;
      EXIT WHEN ts_cursor%NOTFOUND;
      lv_lineno := 1;
      lv_string := ('CREATE TABLESPACE '||lower(lv_tablespace_name));
      lv_first_rec := TRUE;
      write_out(lv_lineno, lv_tablespace_name, lv_string);
      OPEN df_cursor(lv_tablespace_name);
      LOOP
         FETCH df_cursor INTO lv_file_name,
                              lv_bytes,
                              lv_autoextensible,
                              lv_maxbytes,
                              lv_increment_by;
         EXIT WHEN df_cursor%NOTFOUND;
         if (lv_first_rec) then
            lv_first_rec := FALSE;
            lv_string := 'DATAFILE ';
         else
            lv_string := lv_string || ',';
         end if;
         lv_string:=lv_string||''''||lv_file_name||''''||
                    ' SIZE '||to_char(lv_bytes) || ' REUSE';
         if (lv_autoextensible = 'YES') then
            lv_string:=lv_string||' AUTOEXTEND ON'||
                       ' NEXT '||to_char(lv_increment_by)||
                       ' MAXSIZE '||to_char(lv_maxbytes);
         END IF;
      END LOOP;
      CLOSE df_cursor;
         lv_lineno := lv_lineno + 1;
         write_out(lv_lineno, lv_tablespace_name, lv_string);
         lv_lineno := lv_lineno + 1;
         lv_string := (' DEFAULT STORAGE (INITIAL ' ||
                      to_char(lv_initial_extent) ||
                      ' NEXT ' || lv_next_extent);
         write_out(lv_lineno, lv_tablespace_name, lv_string);
         lv_lineno := lv_lineno + 1;
         lv_string := (' MINEXTENTS ' ||
                      lv_min_extents ||
                      ' MAXEXTENTS ' || lv_max_extents);
         write_out(lv_lineno, lv_tablespace_name, lv_string);
         lv_lineno := lv_lineno + 1;
         lv_string := (' PCTINCREASE ' ||
                      lv_pct_increase || ')');
         write_out(lv_lineno, lv_tablespace_name, lv_string);
         lv_string := ('   '||lv_status);
         write_out(lv_lineno, lv_tablespace_name, lv_string);
         lv_lineno := lv_lineno + 1;
         lv_string:='/';
         write_out(lv_lineno, lv_tablespace_name, lv_string);
         lv_lineno := lv_lineno + 1;
         lv_string:='                                                  ';
         write_out(lv_lineno, lv_tablespace_name, lv_string);
   END LOOP;
   CLOSE ts_cursor;
END;
/

spool create_tablespaces.sql
set heading off
set recsep off
col text format a80 word_wrap


select   text
from     ts_temp
order by ts_name, lineno;

spool off;

drop table ts_temp;

Workflow Service Containers Activate/Deactivate Script

REM HEADER
REM   $Header: atg_supp_wf_srv_ctl.sql v2.0 GGGRANT $
REM  
REM MODIFICATION LOG:
REM
REM    14-JUN-2010    GGGRANT Added Abort and an option and numerical prompt for operation.
REM           
REM   atg_supp_wf_srv_ctl.sql
REM    
REM       This is an admin script to start and stop the Workflow Service Containers.
REM      
REM
REM   How to run it?
REM  
REM   sqlplus apps/<password>
REM
REM   @atg_supp_wf_srv_ctl.sql
REM
REM  Type Number to Perform Operation:
REM
REM   1. Activate
REM
REM   2. Deactivate
REM
REM   3. Abort
REM  
REM 
REM
REM
REM   Created: Apr 21th, 2010
REM   Last Updated: Jun 14th, 2010



WHENEVER SQLERROR EXIT FAILURE ROLLBACK;
WHENEVER OSERROR EXIT FAILURE;
SET VERIFY OFF
SET SERVEROUTPUT ON SIZE 200000


select to_char(rownum)|| '. ' || decode(meaning,
              'Activated', 'Activate',
              'Deactivated', 'Deactivate',
              'Terminated', 'Abort'
              ) meaning
from fnd_lookups
where lookup_type='CP_CONTROL_CODE' and meaning in ('Activated', 'Terminated', 'Deactivated') order by meaning;


-- Option value from user
accept l_option default '1'  prompt 'Enter Workflow Service Container Operation[1]: '



Declare
 l_qaid               number;
 l_queue              number;
 spid                 number;
 errbuf               varchar2(300);
 l_cqname             varchar2(300);
 l_operation varchar2(30) :=  &l_option;

cursor wfsrv_ctl is
   select application_id, CONCURRENT_QUEUE_ID, CONCURRENT_QUEUE_NAME
   from fnd_concurrent_queues    where CONCURRENT_QUEUE_NAME in ('WFMLRSVC', 'WFALSNRSVC', 'WFWSSVC');
  
Begin

 fnd_global.apps_initialize(0,20420,1);


 for l_rec in wfsrv_ctl loop


 l_queue := l_rec.CONCURRENT_QUEUE_ID;

 l_cqname := l_rec.CONCURRENT_QUEUE_NAME;

 if l_queue > 0 and l_operation = '1' then
   spid:=fnd_request.submit_svc_ctl_request( command => 'ACTIVATE',
                             service => l_cqname,
                             service_app => 'FND');
elsif l_queue > 0 and l_operation = '2' then
   spid:=fnd_request.submit_svc_ctl_request( command => 'DEACTIVATE',
                             service => l_cqname,
                             service_app => 'FND');
else
   spid:=fnd_request.submit_svc_ctl_request( command => 'ABORT',
                             service => l_cqname,
                             service_app => 'FND');                            
 end if;

if spid = 0 then
    errbuf := fnd_message.get;       DBMS_OUTPUT.put_line(errbuf);
 end if;

end loop;

end;
/

commit;
exit;

Ref: 1124356.1