jgzzvatupgutidt.sql fails on 12.1.1 upgrade patch 6678700 application

Error:  sqlplus -s APPS/***** @/R12PI/apps/appl/jg/12.0.0/patch/115/sql/jgzzvatupgutidt.sql &un_jg &batchsize 6 30
           WHERE stg.ledger_id NOT IN (SELECT ledger_id FROM jg_zz_vat_upg_stg_tmp)
                                                             *
ERROR at line 90:
ORA-06550: line 90, column 62:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 73, column 4:
PL/SQL: SQL Statement ignored
ORA-06550: line 108, column 10:
PL/SQL: ORA-00942: table or view does not exist
ORA-06550: line 104, column 13:
PL/SQL: SQL Statement ignored

Solution  The table jg_zz_vat_upg_stg_tmp is a simple temp table so, please try to create that table via DDL script ( Executing adodfcmp against jgjgzx.odf) and continue with the paused patch 6678700 adpatch session.


adodfcmp utility: 

The ODF Comparison utility is used to compare the datamodel of a customer’s data to a standard set of data model files from the current Oracle Apps release. It can optionally modify the database to match the standard data model.

Whenever we apply the oracle application patches,patches supply odf's file and adpatch run odf comparison utlity to make the changes in the database.

There is an object descriptor file (ODF) describing the tables, views, indexes, sequences and privilege sets for the particular building block.ODF Comparison compares the building block to the object descriptor file (ODF). A log file is created showing any missing, extra, or incorrectly defined objects as shown in below example


Example/Syntax :
 /R12PI/apps/appl/jg/12.0.0/patch/115/odf
adodfcmp odffile=jgjgzx.odf userid=jg/jg mode=tables touser=apps/****** logfile=jgjgzx.log priv_schema=system/*******

Log file will be as below :

*******************************************************
The database is missing the table JG_ZZ_VAT_UPG_STG_TMP.
Create it with the statement(s):

CREATE TABLE JG.JG_ZZ_VAT_UPG_STG_TMP (LEDGER_ID NUMBER(15) NOT NULL,
 COUNTRY_CODE VARCHAR2(2) NOT NULL, CREATION_DATE DATE NOT NULL, CREATED_BY
 NUMBER(15) NOT NULL, LAST_UPDATED_BY NUMBER(15) NOT NULL, LAST_UPDATE_DATE
 DATE NOT NULL, LAST_UPDATE_LOGIN NUMBER(15)) STORAGE(INITIAL 4K NEXT 32K
 MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4
 FREELISTS 4 ) PCTFREE 10 PCTUSED 60 INITRANS 10 MAXTRANS 255

CREATE UNIQUE INDEX JG.JG_ZZ_VAT_UPG_STG_TMP_U1 ON JG.JG_ZZ_VAT_UPG_STG_TMP
 (LEDGER_ID) LOGGING STORAGE (INITIAL 4K NEXT 128K MINEXTENTS 1 MAXEXTENTS
 UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE 10
 INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS

Schema is "JG", SQL statement is:
GRANT ALL ON JG.JG_ZZ_VAT_UPG_STG_TMP TO APPS WITH GRANT OPTION
[did not execute above statement -- Mode changedb = NO]

CREATE OR REPLACE SYNONYM APPS.JG_ZZ_VAT_UPG_STG_TMP FOR
 JG.JG_ZZ_VAT_UPG_STG_TMP

ODF Comparison Utility is complete.
***********************************************************

Connect to sqlplus with apps user and execute above scripts , restart the failed adworker

SQL> CREATE TABLE JG.JG_ZZ_VAT_UPG_STG_TMP (LEDGER_ID NUMBER(15) NOT NULL,
  2   COUNTRY_CODE VARCHAR2(2) NOT NULL, CREATION_DATE DATE NOT NULL, CREATED_BY
  3   NUMBER(15) NOT NULL, LAST_UPDATED_BY NUMBER(15) NOT NULL, LAST_UPDATE_DATE
  4   DATE NOT NULL, LAST_UPDATE_LOGIN NUMBER(15)) STORAGE(INITIAL 4K NEXT 32K
  5   MINEXTENTS 1 MAXEXTENTS UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4
  6   FREELISTS 4 ) PCTFREE 10 PCTUSED 60 INITRANS 10 MAXTRANS 255
  7  /

Table created.

SQL> CREATE UNIQUE INDEX JG.JG_ZZ_VAT_UPG_STG_TMP_U1 ON JG.JG_ZZ_VAT_UPG_STG_TMP
  2   (LEDGER_ID) LOGGING STORAGE (INITIAL 4K NEXT 128K MINEXTENTS 1 MAXEXTENTS
 UNLIMITED PCTINCREASE 0 FREELIST GROUPS 4 FREELISTS 4 ) PCTFREE 10
  3    4   INITRANS 11 MAXTRANS 255 COMPUTE STATISTICS
  5  /

Index created.

SQL> CREATE OR REPLACE SYNONYM APPS.JG_ZZ_VAT_UPG_STG_TMP FOR
 JG.JG_ZZ_VAT_UPG_STG_TMP
  2    3  /

Synonym created.


Ref : Patch 6678700 fails running Jgzzvatupgutidt.sql script With Ora-00942 On Jg_zz_vat_upg_stg_tmp [ID 1421163.1]

DB Console EMAGENT process is still running

Issue:

I see that the EMAGENT process is still running , even after i shutdown the DB Console. It is writing XML logs in the "upload" directory. I tried to shutdown the agent using command "emctl stop agent", but the command is not working.

 Solution:

Due to various reasons, such as crashing, incorrect configuration, corrupted memory or process termination, the DB Console processes may be left over in an undetermined state making start/stop operation failing.

 Below are 3 main processes for DB Console:
1. "emwd.pl dbconsole"
    Called EM WatchDog process, this one is controlling all the other ones.
2. "emagent"
    This is the EM agent process, parent PID is the WatchDog one above.
3. "java"
    This is the DB Console process it self (the UI console, OMS, etc...). Parent PID is also the emwd one (WatchDog)

Here I manually cleared the agent process

Ref: How To Cleanup All DBConsole left-over Processes? [ID 1171814.1]

DB Console re-installation steps - Issues

-bash-4.2$ ./emca -deconfig dbcontrol db

STARTED EMCA at Apr 8, 2012 9:25:17 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: TEST

Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 8, 2012 9:25:36 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /db1/oracle/TEST/cfgtoollogs/emca/TEST/emca_2012_04_08_09_25_16.log.
Apr 8, 2012 9:25:36 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 8, 2012 9:25:46 AM

-bash-4.2$ emca -deconfig dbcontrol db -repos drop

STARTED EMCA at Apr 8, 2012 9:37:27 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: TEST
Listener port number: 1571
Password for SYS user:
Password for SYSMAN user:

----------------------------------------------------------------------
WARNING : While repository is dropped the database will be put in quiesce mode.
----------------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 8, 2012 9:39:03 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /db1/oracle/TEST/cfgtoollogs/emca/TEST/emca_2012_04_08_09_37_27.log.
Apr 8, 2012 9:39:04 AM oracle.sysman.emcp.util.GeneralUtil initSQLEngineLoacly
WARNING: ORA-01017: invalid username/password; logon denied

Apr 8, 2012 9:39:04 AM oracle.sysman.emcp.ParamsManager checkListenerStatusForDBControl
WARNING: Error initializing SQL connection. SQL operations cannot be performed
Apr 8, 2012 9:39:04 AM oracle.sysman.emcp.EMDBPreConfig performDeconfiguration
WARNING: EM is not configured for this database. No EM-specific actions can be performed. Some of the possible reasons may be:
 1) EM is configured with different hostname then physical host. Set environment variable ORACLE_HOSTNAME=<hostname> and re-run EMCA script
 2) ORACLE_HOSTNAME is set. Unset it and re-run EMCA script
Apr 8, 2012 9:39:04 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Apr 8, 2012 9:39:05 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 8, 2012 9:39:05 AM




SQL> shut immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP RESTRICT;
ORACLE instance started.

Total System Global Area 6415597568 bytes
Fixed Size                  2170304 bytes
Variable Size            1409286720 bytes
Database Buffers         4865392640 bytes
Redo Buffers              138747904 bytes
Database mounted.
Database opened.
SQL> EXEC sysman.emd_maintenance.remove_em_dbms_jobs;

PL/SQL procedure successfully completed.

SQL> EXEC sysman.setEMUserContext('',5);

PL/SQL procedure successfully completed.

SQL> REVOKE dba FROM sysman;
REVOKE dba FROM sysman
*
ERROR at line 1:
ORA-01951: ROLE 'DBA' not granted to 'SYSMAN'


SQL> DECLARE
CURSOR c1 IS
  2    3  SELECT owner, synonym_name name
  4  FROM dba_synonyms
  5  WHERE table_owner = 'SYSMAN';
  6  BEGIN
  7  FOR r1 IN c1 LOOP
  8  IF r1.owner = 'PUBLIC' THEN
  9  EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
 10  ELSE
 11  EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
 12  END IF;
 13  END LOOP;
 14  END;
 15  /

PL/SQL procedure successfully completed.

SQL> DROP USER mgmt_view CASCADE;

User dropped.

SQL> DROP ROLE mgmt_user;

Role dropped.

SQL> DROP USER sysman CASCADE;

User dropped.

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

System altered.



-bash-4.2$ emca -config dbcontrol db -repos create

STARTED EMCA at Apr 8, 2012 9:42:12 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle.  All rights reserved.

Enter the following information:
Database SID: TEST
Listener port number: 1571
Listener ORACLE_HOME [ /db1/oracle/TEST/product/11.2.0 ]:
Password for SYS user:
Password for DBSNMP user:
Password for SYSMAN user:
Email address for notifications (optional):
Outgoing Mail (SMTP) server for notifications (optional):
-----------------------------------------------------------------

You have specified the following settings

Database ORACLE_HOME ................ /db1/oracle/TEST/product/11.2.0

Local hostname ................ localhost.my
Listener ORACLE_HOME ................ /db1/oracle/STEST/product/11.2.0
Listener port number ................ 1571
Database SID ................ TEST
Email address for notifications ...............
Outgoing Mail (SMTP) server for notifications ...............

-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 8, 2012 9:43:08 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /db1/oracle/TEST/cfgtoollogs/emca/TEST/emca_2012_04_08_09_42_12.log.
Apr 8, 2012 9:43:10 AM oracle.sysman.emcp.EMReposConfig createRepository
INFO: Creating the EM repository (this may take a while) ...
Apr 8, 2012 9:48:49 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully created
Apr 8, 2012 9:48:53 AM oracle.sysman.emcp.EMReposConfig uploadConfigDataToRepository
INFO: Uploading configuration data to EM repository (this may take a while) ...
Apr 8, 2012 9:50:13 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Uploaded configuration data successfully
Apr 8, 2012 9:50:47 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Securing Database Control (this may take a while) ...
Apr 8, 2012 9:50:53 AM oracle.sysman.emcp.util.DBControlUtil secureDBConsole
INFO: Database Control secured successfully.
Apr 8, 2012 9:50:54 AM oracle.sysman.emcp.util.DBControlUtil startOMS
INFO: Starting Database Control (this may take a while) ...
Apr 8, 2012 9:51:25 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: Database Control started successfully
Apr 8, 2012 9:51:25 AM oracle.sysman.emcp.EMDBPostConfig performConfiguration
INFO: >>>>>>>>>>> The Database Control URL is https://localhost.my:5500/em <<<<<<<<<<<
Apr 8, 2012 9:51:28 AM oracle.sysman.emcp.EMDBPostConfig invoke
WARNING:
************************  WARNING  ************************

Management Repository has been placed in secure mode wherein Enterprise Manager data will be encrypted.  The encryption key has been placed in the file: /db1/oracle/TEST/product/11.2.0/localhost.me_TEST/sysman/config/emkey.ora. Ensure this file is backed up as the encrypted data will become unusable if this file is lost.

***********************************************************
Enterprise Manager configuration completed successfully


Ref: When to Use the Create/Drop Dbconsole Commands which are Given in Note:278100.1 [ID 1377211.1]
How To Drop, Create And Recreate the Database Control (DB Control) Release 10g and 11g [ID 278100.1]

RMAN-06091: no channel allocated for maintenance (of an appropriate type)

Got below error when I try to delete obsolete backups using RMAN> DELETE NOPROMPT OBSOLETE;
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of delete command at 04/06/2012 14:23:27
RMAN-06091: no channel allocated for maintenance (of an appropriate type)

Even I tried with below option

RMAN> delete obsolete;

RMAN> delete noprompt obsolete;

RMAN> delete force obsolete;

Solution:

Run the following to specify the device type , in my case device type is disk

RMAN> delete noprompt obsolete device type disk;

If not worked use below:

ALLOCATE CHANNEL FOR MAINTENANCE DEVICE TYPE DISK;

run

{

report obsolete;

CROSSCHECK BACKUP;

CROSSCHECK COPY;

DELETE EXPIRED BACKUP;

DELETE EXPIRED COPY;

delete obsolete;

}

release channel;

Ref: Encountered RMAN-03002 and RMAN-06091 when Deleting Obsolete Backups [ID 567555.1]

Rman Could Not Delete Obsolete Backupsets Due To Change From Sbt_tape To Disk and To New Server [ID 1287755.1]

Advanced Filters

The Linux commands "grep" , "sed","awk"

1) grep:
The grep program searches a file or files for lines that have a certain pattern
syntax : $grep "pattern" files

The command g/re/p, which means "globally search for a regular expression and print all lines containing it

usage:
$grep "Unix" *
$ls -ltr | grep April

grep options:

-v --> print all lines that do not match pattern
-n --> print the matched line with line number
-l --> print only the names of files with matching lines
-c --> print only count of matching lines
-i --> print that match either upper or lower

Examples with above options one by one:

$ grep -v bash /etc/passwd | grep -v nologin
sync:x:5:0:sync:/sbin:/bin/sync
shutdown:x:6:0:shutdown:/sbin:/sbin/shutdown
halt:x:7:0:halt:/sbin:/sbin/halt
news:x:9:13:news:/etc/news:
ldap:x:55:55:LDAP User:/var/lib/ldap:/bin/false
radiusd:x:95:95:radiusd user:/:/bin/false

$ grep -n root /etc/passwd
1:root:x:0:0:root:/root:/bin/bash
12:operator:x:11:0:operator:/root:/sbin/nologin

$ grep -l siri /etc/passwd
/etc/passwd

$ grep -c root /etc/passwd
2

$ grep -i root /etc/passwd
root:x:0:0:root:/root:/bin/bash
operator:x:11:0:operator:/root:/sbin/nologin

$ grep -i  env ~/.bash* | grep -v apps
/home/appluat/.bash_history:ls -lrt *.env
/home/appluat/.bash_history:env | grep CONFIG
/home/appluat/.bash_history:env | grep - i secure
/home/appluat/.bash_history:env | grep -i secure
/home/appluat/.bash_profile:# User specific environment and startup programs

$ grep -i vnc ~/.bash*
/home/appluat/.bash_history:ps -fuappluat|grep vnc
/home/appluat/.bash_history:ps -ef|grep vnc
/home/appluat/.bash_history:vncserver


--grep and regular expressions:
1) line and word anchors
Here is the example of which exclusively display the lines start with "vnc"
$ grep ^vnc ~/.bash*
/home/appluat/.bash_history:vncserver

Example of the word ending with "secure"
$ grep secure$ ~/.bash*
/home/appluat/.bash_history:cd secure
/home/appluat/.bash_history:env | grep - i secure
/home/appluat/.bash_history:env | grep -i secure



 2) Character classes:
Here it lists the entries/containing  either of u,e words
$ grep [ue] ~/.bash*
/home/appluat/.bash_history:cat backup_apps_tier.sh
/home/appluat/.bash_history:sh backup_apps_tier.sh
/home/appluat/.bash_history:cat backup_apps_tier.sh

3) wild cards
Here it lists all words starting with "s" and ending in "e" from the system's dictionary

$ grep '\<s.*e\>' ~/.bash*
/home/appluat/.bash_history:cd ./appl/fnd/12.0.0/secure/
/home/appluat/.bash_history:cd secure
/home/appluat/.bash_history:env | grep - i secure
/home/appluat/.bash_history:env | grep -i secure



 

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