Saturday, January 29, 2011
How to i apply new patch while Adpatch is running.
How to Apply an 11i Patch When adpatch is Already Running =============================================== 1. Using the adctrl utility, shutdown the workers. a. adctrl b. Select option 3 "Tell worker to shutdown/quit" 2. Backup the FND_INSTALL_PROCESSES, AD_DEFERRED_JOBS table which is owned by the APPLSYS schema a. sqlplus applsys/ b. create table fnd_Install_processes_back as select * from fnd_Install_processes; c. The 2 tables should have the same number of records. select count(*) from fnd_Install_processes_back; select count(*) from fnd_Install_processes; 3. Backup the AD_DEFERRED_JOBS table. a. sqlplus applsys/ b. create table AD_DEFERRED_JOBS_back as select * from AD_DEFERRED_JOBS; c. The 2 tables should have the same number of records. select count(*) from AD_DEFERRED_JOBS_back; select count(*) from AD_DEFERRED_JOBS; 4. Backup the .rf9 files located in $APPL_TOP/admin//restart directory. At this point, the adpatch session should have ended and the cursor should be back at the Unix prompt. a. cd $APPL_TOP/admin/ b. mv restart restart_back c. mkdir restart 5. Drop the FND_INSTALL_PROCESSES table and the AD_DEFERRED_JOBS table. a. sqlplus applsys/ b. drop table FND_INSTALL_PROCESSES; c. drop table AD_DEFERRED_JOBS; 6. Apply the new patch. Restore backup files 7. Restore the .rf9 files located in $APPL_TOP/admin//restart_back directory. a. cd $APPL_TOP/admin/ b. mv restart restart_ c. mv restart_back restart 8. Restore the FND_INSTALL_PROCESSES table which is owned by the APPLSYS schema. a. sqlplus applsys/ b. create table fnd_Install_processes as select * from fnd_Install_processes_back; c. The 2 tables should have the same number of records. select count(*) from fnd_Install_processes; select count(*) from fnd_Install_processes_back; 9. Restore the AD_DEFERRED_JOBS table. a. sqlplus applsys/ b. create table AD_DEFERRED_JOBS as select * from AD_DEFERRED_JOBS_back; c. The 2 tables should have the same number of records. select count(*) from AD_DEFERRED_JOBS_back; select count(*) from AD_DEFERRED_JOBS; 10. Re-create synonyms a. sqlplus apps/apps b. create synonym AD_DEFERRED_JOBS for APPLSYS.AD_DEFERRED_JOBS; c. create synonym FND_INSTALL_PROCESSES FOR APPLSYS.FND_INSTALL_PROCESSES; 11. Start adpatch, it will resume where it stopped previously. Note:175485.1 ====== while running patch Dropping FND_INSTALL_PROCESSES table... Connecting to APPLSYS......Connected successfully. DROP TABLE fnd_install_processes DELETE FROM ad_timestamps WHERE TYPE = 'FND_INSTALL_PROCESSES' FND_INSTALL_PROCESSES table dropped. Dropping AD_DEFERRED_JOBS table... DROP TABLE AD_DEFERRED_JOBS AD_DEFERRED_JOBS table dropped. Done generating forms. Connecting to APPS......Connected successfully.
control file re-create
Step 1. First take controlfile to trace for new , are else you can use old / backup trace file also. Note : Database should be up. sql> alter database backup controlfile to trace; go to udump directory (/t01/oracle/proddb/9.2.0/admin/DEV_erpprod/udump) $ls -ltr ---------> take latest .trc file $ vi < .trc file name > Take the data from STARTUP NOMOUNT CREATE CONTROLFILE ...... ............. .............. CHARACTER SET US7ASCII ; :wq ~/new10g.trc .............> save at /home/new10g.trc Note : In that .trc file before STARTUP NOMOUNT data you can delete , and after ; (semicolon) the data also you can delete after that u can save the file . ================================================================= sample new10g.trc it is should be like this - for create new controlfile. STARTUP NOMOUNT CREATE CONTROLFILE SET DATABASE "NEWTEST" RESETLOGS NOARCHIVELOG MAXLOGFILES 32 MAXLOGMEMBERS 5 MAXDATAFILES 512 MAXINSTANCES 8 MAXLOGHISTORY 7260 LOGFILE GROUP 1 ( '/p01/oracle/testdata/log01a.dbf', '/p01/oracle/testdata/log01b.dbf' ) SIZE 10M, GROUP 2 ( '/p01/oracle/testdata/log02a.dbf', '/p01/oracle/testdata/log02b.dbf' ) SIZE 10M -- STANDBY LOGFILE DATAFILE '/p01/oracle/testdata/system01.dbf', '/p01/oracle/testdata/system02.dbf', '/p01/oracle/testdata/system03.dbf', '/p01/oracle/testdata/system04.dbf', '/p01/oracle/testdata/system05.dbf', '/p01/oracle/testdata/system06.dbf', '/p01/oracle/testdata/system07.dbf', '/p01/oracle/testdata/system08.dbf', '/p01/oracle/testdata/system09.dbf', '/p01/oracle/testdata/system10.dbf', '/p01/oracle/testdata/system11.dbf', '/p01/oracle/testdata/undo01.dbf', '/p01/oracle/testdata/a_archive01.dbf', '/p01/oracle/testdata/a_int01.dbf', '/p01/oracle/testdata/a_media01.dbf', '/p01/oracle/testdata/a_nolog01.dbf', '/p01/oracle/testdata/a_queue01.dbf', '/p01/oracle/testdata/a_queue02.dbf', '/p01/oracle/testdata/a_ref01.dbf', '/p01/oracle/testdata/a_ref02.dbf', '/p01/oracle/testdata/a_summ01.dbf', '/p01/oracle/testdata/a_txn_data01.dbf', '/p01/oracle/testdata/a_txn_data02.dbf', '/p01/oracle/testdata/a_txn_data03.dbf', '/p01/oracle/testdata/a_txn_ind01.dbf', '/p01/oracle/testdata/a_txn_ind02.dbf', '/p01/oracle/testdata/a_txn_ind03.dbf', '/p01/oracle/testdata/a_txn_ind04.dbf', '/p01/oracle/testdata/a_txn_ind05.dbf', '/p01/oracle/testdata/ctxd01.dbf', '/p01/oracle/testdata/odm.dbf', '/p01/oracle/testdata/olap.dbf', '/p01/oracle/testdata/owad01.dbf', '/p01/oracle/testdata/portal01.dbf', '/p01/oracle/testdata/userdata.dbf', '/p01/oracle/testdata/useridx.dbf', '/p01/oracle/testdata/sysaux01.dbf' CHARACTER SET US7ASCII ; =============================================================== Note : see the file owner group permissions , like this #chown -R oramgr:dba new10g.trc *** move the existing old control file to backup sql> shut immediate; $cd /t01/oracle/proddata> mv cntr101.dbf cntr101.dbf.bak $ cd /t01/oracle/proddata> mv cntr102.dbf cntr102.dbf.bak Note: move all your controlfiles to backup , because this is created new control files sql>@/home/new10g.trc sql>alter database open resetlogs; ----- current redolog sequence is start with 1 Now database is open Successfully , you can check it should be open stage sql> select status from v$instance;
While applying the RUP 7FAILED: file icxwtab.odf on worker 1 for product icx
Problem: While applying the RUP 7FAILED: file icxwtab.odf on worker 1 for product icx username ICX.Time is: Sun Sep 19 2010 00:39:53In adworker log file : /p02/oracle/prodappl/admin/PROD/log/adwork001.logCREATE UNIQUE INDEX ICX.ICX_TRANSACTIONS_U1 ON ICX.ICX_TRANSACTIONS(TRANSACTION_ID) LOGGING PCTFREE 10 INITRANS 11 MAXTRANS 255 COMPUTESTATISTICS TABLESPACE APPS_TS_TX_IDXStatement executed.AD Worker error:The index cannot be created as the table has duplicate keys.Use the following SQL statement to identify the duplicate keys:SELECT TRANSACTION_ID, count(*)FROM ICX.ICX_TRANSACTIONSGROUP BY TRANSACTION_IDHAVING count(*)>1Solution:a).Metalink:icxwtab.odf is unable to create index ICX_TRANSACTIONS_U1 [ID 430673.1]Run -- SQL script under $ICX_TOP/sql (named ICXDLTMP.sql).If any error while run the above sql script like “AUDIT_USER_END” … THENYou can re-run from Patch Direcotry IT WILL BE SUCESSbash-3.00$ cd /B01/patches/RUP_7/6241631/backup/PROD/prod/icx/sql/bash-3.00$ lsICXDLTMP.sql ICXDLTMP.sql_bkpbash-3.00$ sqlplus apps/pllerpSQL> @ICXDLTMP.sql43212 rows deleted.13350 rows deleted.89319 rows deleted.0 rows deleted.0 rows deleted.0 rows deleted.0 rows deleted.0 rows deleted.Commit complete.PL/SQL procedure successfully completed.Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsbash-3.00$For restart the faild worker===================b). AD Controller Menu$cd /p02/oracle/prodappl/Source the environment$adctrlEnter a Logfile : /p02/oracle/prodappl/adctrl_190910.logEnter the ORACLE username of Application Object Library [APPLSYS] : APPLSYSEnter the ORACLE password of Application Object Library [APPS] : ***** ( apps pwd)AD Controller Menu---------------------------------------------------1. Show worker status2. Tell worker to restart a failed job3. Tell worker to quit4. Tell manager that a worker failed its job5. Tell manager that a worker acknowledges quit6. Restart a worker on the current machine7. ExitNote : option 8 is the skip the worker ( this option is disable)First select 1st option : show worker statusAd ControlWorker Code Context Filename Status------ -------- ---------------------- -------------------- --------------1 Run AutoPatch R115 icxwtab.odf FAILED2 Run AutoPatch R115 WaitThen You can select 2 nd option : Restart a failed job--- enter the failed worker number . i.e 1
About the Concurrent Manager
As mentioned there are different types of concurrent mangers in Oracle Applications among them the three important manager which are required by any oracle applications installation are stated below
Internal Concurrent Manager (ICM) - The Internal concurrent manager or the ICM is the 'master' concurrent manager. Its primary responsibility is to take care of the starting and stopping of all other concurrent managers that have been defined in the system and activated. However once the other managers are up and running the ICM does not play much of a role. It is for the same reason that despite your ICM being down oracle applications continues to perform as expected most of the times. That is you would be able to continue submitting requests even with the ICM down. However there are cases where you can evolve your ICM to do larger roles like acting as a conflict resolution manager or in case you have generic service management enabled your ICM can take care of starting and stopping other application services also.
Standard Concurrent Manager - This the core manger and the most hard working of the three. It takes care of managing all the concurrent requests in the system. If however specific programs have been defined to use specific concurrent manager they would be taken care by that manager in all other cases the standard manager will be the default concurrent manager and will process the request. That is by default all the concurrent programs are defined to be run by the standard manager and they should not be excluded unless you have included them to run by an other manager. Though we can change a few setting of the standard concurrent manager like the number of process it can handle, most of the other definition should remain unchanged.
Conflict Resolution Manager (CRM) - The conflict resolution manager of the CRM is responsible of handling any conflicts that might occur within the concurrent programmes. This conflict could be as a result of various reasons for example there might be a business requirement that two instances of a particular report should not be executed at the same time or during a particular period. In case such a scenario occurs the CRM is responsible for taking care of such request. As in the case of the standard concurrent manager the definition for the CRM should be rarely changed.Apart from these three concurrent manages there is another type of concurrent manager known as the Transaction Manager also exists. The transaction manager is responsible for taking the load off the concurrent request table for pooling the request submitted by the user.The transaction manager takes care of these requests and sends it to standard manager directly.In a RAC environment the Transaction manager is required to be activated on each node of the RAC environment. From the front end you could view the status of your concurrent manager by logging with the System Administration responsibility and going to the Concurrent Manager administer screen. The concurrent managers are like other process which run on the oracle applications executable FNDLIBR. The FNDLIBR executable is located at $FND_TOP/bin. You could also grep the FNDLIBR executable to check if any concurrent manager process are running $ ps -efgrep FNDLIBR The $FND_TOP/sql/afcmstat.sql script gives you a list of concurrent managers and their respective status.
Internal Concurrent Manager (ICM) - The Internal concurrent manager or the ICM is the 'master' concurrent manager. Its primary responsibility is to take care of the starting and stopping of all other concurrent managers that have been defined in the system and activated. However once the other managers are up and running the ICM does not play much of a role. It is for the same reason that despite your ICM being down oracle applications continues to perform as expected most of the times. That is you would be able to continue submitting requests even with the ICM down. However there are cases where you can evolve your ICM to do larger roles like acting as a conflict resolution manager or in case you have generic service management enabled your ICM can take care of starting and stopping other application services also.
Standard Concurrent Manager - This the core manger and the most hard working of the three. It takes care of managing all the concurrent requests in the system. If however specific programs have been defined to use specific concurrent manager they would be taken care by that manager in all other cases the standard manager will be the default concurrent manager and will process the request. That is by default all the concurrent programs are defined to be run by the standard manager and they should not be excluded unless you have included them to run by an other manager. Though we can change a few setting of the standard concurrent manager like the number of process it can handle, most of the other definition should remain unchanged.
Conflict Resolution Manager (CRM) - The conflict resolution manager of the CRM is responsible of handling any conflicts that might occur within the concurrent programmes. This conflict could be as a result of various reasons for example there might be a business requirement that two instances of a particular report should not be executed at the same time or during a particular period. In case such a scenario occurs the CRM is responsible for taking care of such request. As in the case of the standard concurrent manager the definition for the CRM should be rarely changed.Apart from these three concurrent manages there is another type of concurrent manager known as the Transaction Manager also exists. The transaction manager is responsible for taking the load off the concurrent request table for pooling the request submitted by the user.The transaction manager takes care of these requests and sends it to standard manager directly.In a RAC environment the Transaction manager is required to be activated on each node of the RAC environment. From the front end you could view the status of your concurrent manager by logging with the System Administration responsibility and going to the Concurrent Manager administer screen. The concurrent managers are like other process which run on the oracle applications executable FNDLIBR. The FNDLIBR executable is located at $FND_TOP/bin. You could also grep the FNDLIBR executable to check if any concurrent manager process are running $ ps -efgrep FNDLIBR The $FND_TOP/sql/afcmstat.sql script gives you a list of concurrent managers and their respective status.
Compile Invalid objects in Oracle Database
Recompiling Invalid Schema Objects
Operations such as upgrades, patches and DDL changes can invalidate schema objects. Provided these changes don't cause compilation failures the objects will be revalidated by on-demand automatic recompilation, but this can take an unacceptable time to complete, especially where complex dependencies are present. For this reason it makes sense to recompile invalid objects in advance of user calls. It also allows you to identify if any changes have broken your code base. This article presents several methods for recompiling invalid schema objects.
Identifying Invalid Objects
The Manual Approach
Custom Script
DBMS_UTILITY.compile_schema
UTL_RECOMP
utlrp.sql and utlprp.sql
Identifying Invalid Objects
The DBA_OBJECTS view can be used to identify invalid objects using the following query:
COLUMN object_name FORMAT A30SELECT owner, object_type, object_name, statusFROM dba_objectsWHERE status = 'INVALID'ORDER BY owner, object_type, object_name;
With this information you can decide which of the following recompilation methods is suitable for you.
The Manual Approach
For small numbers of objects you may decide that a manual recompilation is sufficient. The following example shows the compile syntax for several object types:
ALTER PACKAGE my_package COMPILE;ALTER PACKAGE my_package COMPILE BODY;ALTER PROCEDURE my_procedure COMPILE;ALTER FUNCTION my_function COMPILE;ALTER TRIGGER my_trigger COMPILE;ALTER VIEW my_view COMPILE;
Notice that the package body is compiled in the same way as the package specification, with the addition of the word "BODY" at the end of the command.An alternative approach is to use the DBMS_DDL package to perform the recompilations:
EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');
This method is limited to PL/SQL objects, so it is not applicable for views.
Custom Script
In some situations you may have to compile many invalid objects in one go. One approach is to write a custom script to identify and compile the invalid objects. The following example identifies and recompiles invalid packages and package bodies.
SET SERVEROUTPUT ON SIZE 1000000BEGIN FOR cur_rec IN (SELECT owner, object_name, object_type, DECODE(object_type, 'PACKAGE', 1, 'PACKAGE BODY', 2, 2) AS recompile_order FROM dba_objects WHERE object_type IN ('PACKAGE', 'PACKAGE BODY') AND status != 'VALID' ORDER BY 4) LOOP BEGIN IF cur_rec.object_type = 'PACKAGE' THEN EXECUTE IMMEDIATE 'ALTER ' cur_rec.object_type ' "' cur_rec.owner '"."' cur_rec.object_name '" COMPILE'; ElSE EXECUTE IMMEDIATE 'ALTER PACKAGE "' cur_rec.owner '"."' cur_rec.object_name '" COMPILE BODY'; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(cur_rec.object_type ' : ' cur_rec.owner ' : ' cur_rec.object_name); END; END LOOP;END;/
This approach is fine if you have a specific task in mind, but be aware that you may end up compiling some objects multiple times depending on the order they are compiled in. It is probably a better idea to use one of the methods provided by Oracle since they take the code dependencies into account.
DBMS_UTILITY.compile_schema
The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus:
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');
UTL_RECOMP
The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads. Their definitions are listed below:
PROCEDURE RECOMP_SERIAL( schema IN VARCHAR2 DEFAULT NULL, flags IN PLS_INTEGER DEFAULT 0); PROCEDURE RECOMP_PARALLEL( threads IN PLS_INTEGER DEFAULT NULL, schema IN VARCHAR2 DEFAULT NULL, flags IN PLS_INTEGER DEFAULT 0);
The usage notes for the parameters are listed below:
schema - The schema whose invalid objects are to be recompiled. If NULL all invalid objects in the database are recompiled.
threads - The number of threads used in a parallel operation. If NULL the value of the "job_queue_processes" parameter is used. Matching the number of available CPUs is generally a good starting point for this value.
flags - Used for internal diagnostics and testing only.
The following examples show how these procedures care used:
-- Schema level.EXEC UTL_RECOMP.recomp_serial('SCOTT');EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT'); -- Database level.EXEC UTL_RECOMP.recomp_serial();EXEC UTL_RECOMP.recomp_parallel(4); -- Using job_queue_processes value.EXEC UTL_RECOMP.recomp_parallel();EXEC UTL_RECOMP.recomp_parallel(NULL, 'SCOTT');
There are a number of restrictions associated with the use of this package including:
Parallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete.
The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and DBMS_RANDOM to be present and valid.
Runnig DDL operations at the same time as this package may result in deadlocks.
utlrp.sql and utlprp.sql
The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows:
0 - The level of parallelism is derived based on the CPU_COUNT parameter.
1 - The recompilation is run serially, one object at a time.
N - The recompilation is run in parallel with "N" number of threads.
Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.For further information see:
DBMS_UTILITY.compile_schema
UTL_RECOMP
Hope this helps. Regards Tim...Back to the Top.
Operations such as upgrades, patches and DDL changes can invalidate schema objects. Provided these changes don't cause compilation failures the objects will be revalidated by on-demand automatic recompilation, but this can take an unacceptable time to complete, especially where complex dependencies are present. For this reason it makes sense to recompile invalid objects in advance of user calls. It also allows you to identify if any changes have broken your code base. This article presents several methods for recompiling invalid schema objects.
Identifying Invalid Objects
The Manual Approach
Custom Script
DBMS_UTILITY.compile_schema
UTL_RECOMP
utlrp.sql and utlprp.sql
Identifying Invalid Objects
The DBA_OBJECTS view can be used to identify invalid objects using the following query:
COLUMN object_name FORMAT A30SELECT owner, object_type, object_name, statusFROM dba_objectsWHERE status = 'INVALID'ORDER BY owner, object_type, object_name;
With this information you can decide which of the following recompilation methods is suitable for you.
The Manual Approach
For small numbers of objects you may decide that a manual recompilation is sufficient. The following example shows the compile syntax for several object types:
ALTER PACKAGE my_package COMPILE;ALTER PACKAGE my_package COMPILE BODY;ALTER PROCEDURE my_procedure COMPILE;ALTER FUNCTION my_function COMPILE;ALTER TRIGGER my_trigger COMPILE;ALTER VIEW my_view COMPILE;
Notice that the package body is compiled in the same way as the package specification, with the addition of the word "BODY" at the end of the command.An alternative approach is to use the DBMS_DDL package to perform the recompilations:
EXEC DBMS_DDL.alter_compile('PACKAGE', 'MY_SCHEMA', 'MY_PACKAGE');EXEC DBMS_DDL.alter_compile('PACKAGE BODY', 'MY_SCHEMA', 'MY_PACKAGE');EXEC DBMS_DDL.alter_compile('PROCEDURE', 'MY_SCHEMA', 'MY_PROCEDURE');EXEC DBMS_DDL.alter_compile('FUNCTION', 'MY_SCHEMA', 'MY_FUNCTION');EXEC DBMS_DDL.alter_compile('TRIGGER', 'MY_SCHEMA', 'MY_TRIGGER');
This method is limited to PL/SQL objects, so it is not applicable for views.
Custom Script
In some situations you may have to compile many invalid objects in one go. One approach is to write a custom script to identify and compile the invalid objects. The following example identifies and recompiles invalid packages and package bodies.
SET SERVEROUTPUT ON SIZE 1000000BEGIN FOR cur_rec IN (SELECT owner, object_name, object_type, DECODE(object_type, 'PACKAGE', 1, 'PACKAGE BODY', 2, 2) AS recompile_order FROM dba_objects WHERE object_type IN ('PACKAGE', 'PACKAGE BODY') AND status != 'VALID' ORDER BY 4) LOOP BEGIN IF cur_rec.object_type = 'PACKAGE' THEN EXECUTE IMMEDIATE 'ALTER ' cur_rec.object_type ' "' cur_rec.owner '"."' cur_rec.object_name '" COMPILE'; ElSE EXECUTE IMMEDIATE 'ALTER PACKAGE "' cur_rec.owner '"."' cur_rec.object_name '" COMPILE BODY'; END IF; EXCEPTION WHEN OTHERS THEN DBMS_OUTPUT.put_line(cur_rec.object_type ' : ' cur_rec.owner ' : ' cur_rec.object_name); END; END LOOP;END;/
This approach is fine if you have a specific task in mind, but be aware that you may end up compiling some objects multiple times depending on the order they are compiled in. It is probably a better idea to use one of the methods provided by Oracle since they take the code dependencies into account.
DBMS_UTILITY.compile_schema
The COMPILE_SCHEMA procedure in the DBMS_UTILITY package compiles all procedures, functions, packages, and triggers in the specified schema. The example below shows how it is called from SQL*Plus:
EXEC DBMS_UTILITY.compile_schema(schema => 'SCOTT');
UTL_RECOMP
The UTL_RECOMP package contains two procedures used to recompile invalid objects. As the names suggest, the RECOMP_SERIAL procedure recompiles all the invalid objects one at a time, while the RECOMP_PARALLEL procedure performs the same task in parallel using the specified number of threads. Their definitions are listed below:
PROCEDURE RECOMP_SERIAL( schema IN VARCHAR2 DEFAULT NULL, flags IN PLS_INTEGER DEFAULT 0); PROCEDURE RECOMP_PARALLEL( threads IN PLS_INTEGER DEFAULT NULL, schema IN VARCHAR2 DEFAULT NULL, flags IN PLS_INTEGER DEFAULT 0);
The usage notes for the parameters are listed below:
schema - The schema whose invalid objects are to be recompiled. If NULL all invalid objects in the database are recompiled.
threads - The number of threads used in a parallel operation. If NULL the value of the "job_queue_processes" parameter is used. Matching the number of available CPUs is generally a good starting point for this value.
flags - Used for internal diagnostics and testing only.
The following examples show how these procedures care used:
-- Schema level.EXEC UTL_RECOMP.recomp_serial('SCOTT');EXEC UTL_RECOMP.recomp_parallel(4, 'SCOTT'); -- Database level.EXEC UTL_RECOMP.recomp_serial();EXEC UTL_RECOMP.recomp_parallel(4); -- Using job_queue_processes value.EXEC UTL_RECOMP.recomp_parallel();EXEC UTL_RECOMP.recomp_parallel(NULL, 'SCOTT');
There are a number of restrictions associated with the use of this package including:
Parallel execution is perfomed using the job queue. All existing jobs are marked as disabled until the operation is complete.
The package must be run from SQL*Plus as the SYS user, or another user with SYSDBA.
The package expects the STANDARD, DBMS_STANDARD, DBMS_JOB and DBMS_RANDOM to be present and valid.
Runnig DDL operations at the same time as this package may result in deadlocks.
utlrp.sql and utlprp.sql
The utlrp.sql and utlprp.sql scripts are provided by Oracle to recompile all invalid objects in the database. They are typically run after major database changes such as upgrades or patches. They are located in the $ORACLE_HOME/rdbms/admin directory and provide a wrapper on the UTL_RECOMP package. The utlrp.sql script simply calls the utlprp.sql script with a command line parameter of "0". The utlprp.sql accepts a single integer parameter that indicates the level of parallelism as follows:
0 - The level of parallelism is derived based on the CPU_COUNT parameter.
1 - The recompilation is run serially, one object at a time.
N - The recompilation is run in parallel with "N" number of threads.
Both scripts must be run as the SYS user, or another user with SYSDBA, to work correctly.For further information see:
DBMS_UTILITY.compile_schema
UTL_RECOMP
Hope this helps. Regards Tim...Back to the Top.
Create Custom TOP R12
CUSTOM TOP Creation
For customizations you need to create custom top inorder to create all the custom objects and custom menus, responsibilities.
1. Create directory structure in $APPL_TOP
xxxtest/
`– 12.0.0
– admin
– driver
– odf
– sql
`– template
– bin
– forms
`– US
– help
– html
– java
– lib
– log
– mds
– media
– mesg
– out
– patch
– reports
`– US
`– sql
2) Add the custom module into the environment
cd $APPL_TOP
vi customtest_appsvis1.env
XXRLT_TOP=/sandb1/oracle/TEST/apps/apps_st/appl/xxrlt/12.0.0
export XXRLT_TOP
source the environment file ( /sandb1/oracle/TEST/apps/apps_st/appl/APPSTEST_pkgtest.env )
Make entry to context file
/sandb1/oracle/TEST/inst/apps/TEST_pkgtest/appl/admin/TEST_pkgtest.xml
/sandb1/oracle/TEST/apps/apps_st/appl/xxrlt/12.0.0
cd $INST_TOP/admin/install
sh adgentopfile.sh
Open new session, source environment file, and stop middle tier services, run autoconfig
Open new session, source environment file, check for custom top in topfile.txt in $APPL_TOP/admin, start the middle tier services.
3) Create Tablespace
create tablespace XXRLT datafile ‘/sandb1/oracle/TEST/db/apps_st/data/xxrlt01.dbf’ size 500M
4) Create USER
create user xxrlt identified by xxrlt
default tablespace xxrlt
temporary tablespace temp1
quota unlimited on xxrlt
grant connect, resource to xxrlt;
5) Register your Oracle Schema
Login to Applications with System Administrator responsibility
Navigate to Application–>Register
Application = XXRLT Custom Application
Short Name = XXRLT
Basepath = XXRLT_TOP
Description = XXRLT Custom Application
6) Register Oracle User
Naviate to Security–>Oracle–>Register
Database User Name = XXRLT
Password = XXRLT
Privilege = Enabled
Install Group = 0
Description = XXRLT Custom Application User
7) Add Application to a Data Group
Navigate to Security–>Oracle–>DataGroup
Data Group = XXRLTGroup
Description = XXRLT Custom Data Group
Click on “Copy Applications from” and pick Standard data Group, then add the following entry.
Application = XXRLT Custom
Oracle ID = APPS
Description = XXRLT Custom Application
Create custom request group
This will act as a placeholder for any custom reports we wish to make available for the Custom Responsibility (which is defined at a later stage)
Navigate to Security–>responsbility–>Request
Group = XXRLT Request Group
Application = XXRLT Custom
Code = XXRLT
Description = XXRLT Custom Requests
We will not define any requests to add to the group at this stage, but you can add some now if required.
9) Create custom menu
This will act as a placeholder for any menu items we wish to make available for the Custom Responsibility (which is defined at a later stage). We will create
two menus, one for Core Applications and one for Self Service.
Navigate to Application–>Menu
Menu = XXRLT_CUSTOM_MENU
User Menu Name = XXRLT Custom Application
Menu Type =
Description = XXRLT Custom Application Menu
Seq = 100
Prompt = View Requests
Submenu =
Function = View All Concurrent Requests
Description = View Requests
Seq = 110
Prompt = Run Requests
Submenu =
Function = Requests: Submit
Description = Submit Requests
Menu = XXRLT_CUSTOM_MENU_SSWA
User Menu Name = XXRLT Custom Application SSWA
Menu Type =
Description = XXRLT Custom Application Menu for SSWA
10) Create new responsibility. One for Core Applications and One for Self Service (SSWA)
Navigate to Security–>Responsibility–>Define
Responsibility Name = XXRLT Custom
Application = XXRLT Custom
Responsibility Key = XXRLTCUSTOM
Description = XXRLT Custom Responsibility
Available From = Oracle Applications
Data Group Name = XXRLTGroup
Data Group Application = XXRLT Custom
Menu = XXRLT Custom Application
Request Group Name = XXRLT Request Group
Responsibility Name = XXRLT Custom SSWA
Application = XXRLT Custom
Responsibility Key = XXRLTCUSTOMSSWA
Description = XXRLT Custom Responsibility SSWA
Available From = Oracle Self Service Web Applications
Data Group Name = XXRLTGroup
Data Group Application = XXRLT Custom
Menu = XXRLT Custom Application SSWA
Request Group Name = XXRLT Request Group
11) Add responsibility to user
Navigate to Security–>User–>Define
Add XXRLT Custom responsibility to users as required.
12) Other considerations
You are now ready to create your database Objects, custom Reports, Forms, Packages, etc
Create the source code files in the XXRLT_TOP directory appropriate for the type of object. For example forms would be located in $XXRLT_TOP/forms/US or
package source code in $XXRLT_TOP/admin/sql for example.
Database Objects, such as tables, indexes and sequences should be created in the XXRLT schema, and then you need to
a) Grant all privilege from each custom data object to the APPS schema.
For example : logged in as XXRLT user
grant all privileges on myTable to apps;
b) Create a synonym in APPS for each custom data object
For example : logged in as APPS user
create synonym myTable for XXRLT.myTable;
13) Login to sysadmin, Application Developer Responsibility
Application > Form ( Register the form )
Application > Funtion ( Add the form to a function )
Application > Menu ( Attach the function to a menu )
Menu that is added to a particular responsbility is given to specific user
Security > User > Define
For customizations you need to create custom top inorder to create all the custom objects and custom menus, responsibilities.
1. Create directory structure in $APPL_TOP
xxxtest/
`– 12.0.0
– admin
– driver
– odf
– sql
`– template
– bin
– forms
`– US
– help
– html
– java
– lib
– log
– mds
– media
– mesg
– out
– patch
– reports
`– US
`– sql
2) Add the custom module into the environment
cd $APPL_TOP
vi customtest_appsvis1.env
XXRLT_TOP=/sandb1/oracle/TEST/apps/apps_st/appl/xxrlt/12.0.0
export XXRLT_TOP
source the environment file ( /sandb1/oracle/TEST/apps/apps_st/appl/APPSTEST_pkgtest.env )
Make entry to context file
/sandb1/oracle/TEST/inst/apps/TEST_pkgtest/appl/admin/TEST_pkgtest.xml
/sandb1/oracle/TEST/apps/apps_st/appl/xxrlt/12.0.0
cd $INST_TOP/admin/install
sh adgentopfile.sh
Open new session, source environment file, and stop middle tier services, run autoconfig
Open new session, source environment file, check for custom top in topfile.txt in $APPL_TOP/admin, start the middle tier services.
3) Create Tablespace
create tablespace XXRLT datafile ‘/sandb1/oracle/TEST/db/apps_st/data/xxrlt01.dbf’ size 500M
4) Create USER
create user xxrlt identified by xxrlt
default tablespace xxrlt
temporary tablespace temp1
quota unlimited on xxrlt
grant connect, resource to xxrlt;
5) Register your Oracle Schema
Login to Applications with System Administrator responsibility
Navigate to Application–>Register
Application = XXRLT Custom Application
Short Name = XXRLT
Basepath = XXRLT_TOP
Description = XXRLT Custom Application
6) Register Oracle User
Naviate to Security–>Oracle–>Register
Database User Name = XXRLT
Password = XXRLT
Privilege = Enabled
Install Group = 0
Description = XXRLT Custom Application User
7) Add Application to a Data Group
Navigate to Security–>Oracle–>DataGroup
Data Group = XXRLTGroup
Description = XXRLT Custom Data Group
Click on “Copy Applications from” and pick Standard data Group, then add the following entry.
Application = XXRLT Custom
Oracle ID = APPS
Description = XXRLT Custom Application
Create custom request group
This will act as a placeholder for any custom reports we wish to make available for the Custom Responsibility (which is defined at a later stage)
Navigate to Security–>responsbility–>Request
Group = XXRLT Request Group
Application = XXRLT Custom
Code = XXRLT
Description = XXRLT Custom Requests
We will not define any requests to add to the group at this stage, but you can add some now if required.
9) Create custom menu
This will act as a placeholder for any menu items we wish to make available for the Custom Responsibility (which is defined at a later stage). We will create
two menus, one for Core Applications and one for Self Service.
Navigate to Application–>Menu
Menu = XXRLT_CUSTOM_MENU
User Menu Name = XXRLT Custom Application
Menu Type =
Description = XXRLT Custom Application Menu
Seq = 100
Prompt = View Requests
Submenu =
Function = View All Concurrent Requests
Description = View Requests
Seq = 110
Prompt = Run Requests
Submenu =
Function = Requests: Submit
Description = Submit Requests
Menu = XXRLT_CUSTOM_MENU_SSWA
User Menu Name = XXRLT Custom Application SSWA
Menu Type =
Description = XXRLT Custom Application Menu for SSWA
10) Create new responsibility. One for Core Applications and One for Self Service (SSWA)
Navigate to Security–>Responsibility–>Define
Responsibility Name = XXRLT Custom
Application = XXRLT Custom
Responsibility Key = XXRLTCUSTOM
Description = XXRLT Custom Responsibility
Available From = Oracle Applications
Data Group Name = XXRLTGroup
Data Group Application = XXRLT Custom
Menu = XXRLT Custom Application
Request Group Name = XXRLT Request Group
Responsibility Name = XXRLT Custom SSWA
Application = XXRLT Custom
Responsibility Key = XXRLTCUSTOMSSWA
Description = XXRLT Custom Responsibility SSWA
Available From = Oracle Self Service Web Applications
Data Group Name = XXRLTGroup
Data Group Application = XXRLT Custom
Menu = XXRLT Custom Application SSWA
Request Group Name = XXRLT Request Group
11) Add responsibility to user
Navigate to Security–>User–>Define
Add XXRLT Custom responsibility to users as required.
12) Other considerations
You are now ready to create your database Objects, custom Reports, Forms, Packages, etc
Create the source code files in the XXRLT_TOP directory appropriate for the type of object. For example forms would be located in $XXRLT_TOP/forms/US or
package source code in $XXRLT_TOP/admin/sql for example.
Database Objects, such as tables, indexes and sequences should be created in the XXRLT schema, and then you need to
a) Grant all privilege from each custom data object to the APPS schema.
For example : logged in as XXRLT user
grant all privileges on myTable to apps;
b) Create a synonym in APPS for each custom data object
For example : logged in as APPS user
create synonym myTable for XXRLT.myTable;
13) Login to sysadmin, Application Developer Responsibility
Application > Form ( Register the form )
Application > Funtion ( Add the form to a function )
Application > Menu ( Attach the function to a menu )
Menu that is added to a particular responsbility is given to specific user
Security > User > Define
Change Oracle Apps Password
Change the oracle apps password :
As per Metalink docid : 303621.1 /813653.1
Step 1 : Source the .env file at apps tier.
Step 2 : stop the Application services
· : Take these two tables to backup.
select * from FND_USER order by USER_NAME
--- CREATE TABLE SADHA_FND_USER AS SELECT * FROM FND_USER
select * from FND_ORACLE_USERID order by ORACLE_USERNAME
--- CREATE TABLE SADHA_FND_ORACLE_USERID AS SELECT * FROM FND_ORACLE_USERID
Step 3. Remove other than all_user id’s
select * from fnd_oracle_userid where oracle_username not
in(select username from all_users);
DELETE FND_ORACLE_USERID where ORACLE_ID='20003' – records
Commit;
Step 4 : To change apps password
$cd FND_TOP/bin
bash-3.00$ pwd
/B01/test/testappl/fnd/11.5.0/bin
$ FNDCPASS apps/apps 0 Y SYSTEM/MANAGER SYSTEM APPLSYS erppitti
Log filename : L1197061.log
Report filename : O1197061.out
Step 5: change the new password below files.
1. /p01/dev/devora/iAS/Apache/modplsql/cfg
cp wdbsvr.app wdbsvr.app_backup
== change appspassword as new.
1. /p01/dev/devora/8.0.6/reports60/server
cp CGIcmd.dat CGIcmd.dat_bak
Step 6: Cross check connect applsys connect new apps password.
( APPLSYS is the same password to APPS )
Sql> connect applsys
Password : newpassword
Step 6: bounce the services.
* stop listener/database
* Re-start
Listener/database
the application services
As per Metalink docid : 303621.1 /813653.1
Step 1 : Source the .env file at apps tier.
Step 2 : stop the Application services
· : Take these two tables to backup.
select * from FND_USER order by USER_NAME
--- CREATE TABLE SADHA_FND_USER AS SELECT * FROM FND_USER
select * from FND_ORACLE_USERID order by ORACLE_USERNAME
--- CREATE TABLE SADHA_FND_ORACLE_USERID AS SELECT * FROM FND_ORACLE_USERID
Step 3. Remove other than all_user id’s
select * from fnd_oracle_userid where oracle_username not
in(select username from all_users);
DELETE FND_ORACLE_USERID where ORACLE_ID='20003' – records
Commit;
Step 4 : To change apps password
$cd FND_TOP/bin
bash-3.00$ pwd
/B01/test/testappl/fnd/11.5.0/bin
$ FNDCPASS apps/apps 0 Y SYSTEM/MANAGER SYSTEM APPLSYS erppitti
Log filename : L1197061.log
Report filename : O1197061.out
Step 5: change the new password below files.
1. /p01/dev/devora/iAS/Apache/modplsql/cfg
cp wdbsvr.app wdbsvr.app_backup
== change appspassword as new.
1. /p01/dev/devora/8.0.6/reports60/server
cp CGIcmd.dat CGIcmd.dat_bak
Step 6: Cross check connect applsys connect new apps password.
( APPLSYS is the same password to APPS )
Sql> connect applsys
Password : newpassword
Step 6: bounce the services.
* stop listener/database
* Re-start
Listener/database
the application services
RAPID CLONE WITH RMAN HOTBACKUP
Clone / temp / rman
1. At Target side : Production Server
Run à adpreclone Oracle Home and Application Home.
2. cp –rph -- All Application Tops to another Mount point ( Except Database C/R/D ).
3. Restore Database from RMAN
3.1 . Copy archive log auto backup backup set folders from Production flash area to
Test server flash area i.e
/p01/oracle/others/rman_oracle_backup/flash_recovery_area/PROD
Scp –r 2010_10_05/ root@erpprod.pittilam.com:/p01/oracle/others/rman.....
3.2 in TEST server /p01/oracle/10gdb/10.2.0
Connect RMAN
$ rman target=/
RMAN> STARTUP NOMOUNT;
RMAN> restore controlfile from '/p01/oracle/others/rman_oracle_backup/flash_recovery_area/PROD/autobackup/2010_10_04/o1_mf_s_731485220_6bl96dq0_.bkp';
RMAN> alter database mount;
RMAN> RESTORE DATABASE UNTIL TIME "TO_DATE('04-Oct-2010 14:06','DD-MON-YYYY HH24:MI')"; -- 2:45 afternoon to
RMAN> RECOVER DATABASE UNTIL TIME "TO_DATE('04-Oct-2010 14:06','DD-MON-YYYY HH24:MI')";
sql> alter database disable block change tracking;
RMAN> ALTER DATABASE OPEN RESETLOGS;
4.1 Add the datafile to Temp table space.
Sql> select property_name, property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE' Sql> ALTER TABLESPACE temp ADD TEMPFILE '/p01/oracle/proddata/temp01.dbf’ SIZE 512m AUTOEXTEND ON NEXT 200m MAXSIZE UNLIMITED
SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP'; SQL> CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u02/oradata/TESTDB/temp2_01.dbf' SIZE 5000M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE unlimited SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2; SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
SQL> SELECT COUNT(*) FROM v$sort_usage WHERE tablespace = 'TEMP'
4.2Copy Datafiles (C/R/D) to Clone Location. $ sqlplus ‘/as sysdba’ SQL> shut immediate; $ cp –r /p01/oracle/proddata ---- > clone location. 4.3Change owner the as per username clone ( in second server) #chown –R clone:dba prodappl --- like do all the folders 5.1Login as a clone user in test server start the clone part . DB_Tier
$/B01/testnew/test10gdb/10.2.0/appsutil/clone/bin
Ls
adcfgclone.pl adchkutl.sh adclone.pl adclonectx.pl core
bash-3.00$ perl adcfgclone.pl dbTier
Enter the APPS password [APPS]:
pllerp
First Creating a new context file for the cloned system.
The program is going to ask you for information about the new system:
Provide the values required for creation of the new Database Context file.
Do you want to use a virtual hostname for the target node (y/n) [n] ?:
Target instance is a Real Application Cluster (RAC) instance (y/n) [n]:
Target System database name [PROD]:CLINST
Target system RDBMS ORACLE_HOME directory [/p01/oracle/10gdb/10.2.0]:/B01/testnew/test10gdb/10.2.0
Target system utl_file accessible directories list [/usr/tmp, /usr/tmp, /p01/oracle/proddb/9.2.0/appsutil/outbound/PROD_prod]:
Number of DATA_TOP's on the target system [4]:2
Target system DATA_TOP 1:/B01/testnew/testdata
Target system DATA_TOP 2:/B01/testnew/testdb/9.2.0/dbs
Do you want to preserve the Display set to prod:0.0 (y/n) [y] ?:n
Target system Display [erpprod:0.0]:
Perl executable location is set to:
/usr/bin/perl
Do you want to preserve the port values from the source system on the target system (y/n) [y] ?:n
Clone Context uses the same port pool mechanism as the Rapid Install
Once you choose a port pool, Clone Context will validate the port availability.
Enter the port pool number [0-99]:
50
Checking the port pool 50
done: Port Pool 50 is free
Database port is 1571
Creating the new Database Context file from :
/B01/testnew/test10gdb/10.2.0/appsutil/template/adxdbctx.tmp
The new database context file has been created :
/B01/testnew/test10gdb/10.2.0/appsutil/CLINST_erpprod.xml
Log file located at /tmp/CloneContext_10091021.log
Running Rapid Clone with command:
perl /B01/testnew/test10gdb/10.2.0/appsutil/clone/bin/adclone.pl java=/B01/testnew/test10gdb/10.2.0/appsutil/clone/bin/../jre mode=apply stage=/B01/testnew/test10gdb/10.2.0/appsutil/clone/bin/.. component=dbTier method=CUSTOM dbctxtg=/B01/testnew/test10gdb/10.2.0/appsutil/CLINST_erpprod.xml showProgress contextValidated=true
Beginning database tier Apply - Sat Oct 9 10:22:46 2010
Log file located at /B01/testnew/test10gdb/10.2.0/appsutil/log/CLINST_erpprod/ApplyDBTier_10091022.log
Completed Apply...
Sat Oct 9 10:26:22 2010
Beginning APPSDB_CLINST registration to central inventory...
ORACLE_HOME NAME : APPSDB_CLINST
ORACLE_HOME PATH : /B01/testnew/test10gdb/10.2.0
Using Inventory location in /etc/oraInst.loc
Log file located at /etc/oraInventory/logs/OracleHomeCloner_10091026.log
ORACLE_HOME /B01/testnew/test10gdb/10.2.0 was registered successfully.
Starting database listener for CLINST:
Running:
/B01/testnew/test10gdb/10.2.0/appsutil/scripts/CLINST_erpprod/addlnctl.sh start CLINST
You are running addlnctl.sh version 115.7
Logfile: /B01/testnew/test10gdb/10.2.0/appsutil/log/CLINST_erpprod/addlnctl.txt
Starting listener process CLINST ...
Listener CLINST has already been started.
addlnctl.sh: exiting with status 0
Note : controlfile manual creation If any controlfile creation error ,You should create controlfile manually, First take controlfile from production server. sql> alter database backup controlfile to trace;
go to udump directory (/t01/oracle/proddb/9.2.0/admin/DEV_erpprod/udump)
$ls -ltr ---------> take latest .trc fileSql> In Test server side: $ vi contnew.trc STARTUP NOMOUNTCREATE CONTROLFILE SET DATABASE "CLNINST" RESETLOGS NOARCHIVELOG--- -- -- '/B01/testnew/testdata/strabus01.dbf'CHARACTER SET US7ASCII; Sql>@contnew.trcSql> alter database open resetlogs; sql> select status from v$instance; ---------- > It should be in Open Mode $ run Autoconfig on DB Tier Note :If you get any coredump error , you can do below action plan:
$ vi /t01/TEST/10gdb/10.2.0/appsutil/CLONENEW_erpprod.xml
/p01/oracle/10gdb/10.2.0/perl/bin/perl
/t01/clone/test10gdb/10.2.0/perl/lib/5.8.3/aix-thread-multi: /t01/clone/test10gdb/10.2.0perl/lib/5.8.3: /t01/clone/test10gdb/10.2.0/perl/lib/site_perl/5.8.3/aix-thread-multi: /t01/clone/test10gdb/10.2.0/perl/lib/site_perl/5.8.3: /t01/clone/test10gdb/10.2.0/perl/lib/site_perl:.:/t01/clone/test10gdb/10.2.0/appsutil/perl
/B01/testnew/test10gdb/10.2.0/appsutil/scripts/CLINST_erpprod
adautocfg.sh addbctl.sh adexecsql.pl adpreclone.pl adstrtdb.sql
adchknls.pl addlnctl.sh adlsnodes.sh adstopdb.sql core
Apps Tier
/B01/testnew/testcomn/clone
1.cd/appsutil/clone/jre
2.move lib lib.old
3.move bin bin.old
4.ln -s jre/bin bin
5.ln -s jre/lib lib
$/B01/testnew/testcomn/clone/bin
adaddnode.pl adclonectx.pl
adcfgclone.pl core
adchkutl.sh javacore2216028.1286642450.txt
adclone.pl
6.Run perl adcfgclone.pl appsTier
-----------------------------------------------------------------------
Note 1: If Apps Tns listener not started
Stop the Services
Run the autoconfig.
Note 2: If Concurrent managers show wrong Nodes. Not starting FNDLIBR
So clear all the Nodes in TEST Server.
Sql>
select node_name "Node Name", node_mode "Mode", support_cp "C", support_web "W", support_admin "A", support_forms "F"from FND_NODES;
SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN; COMMIT; EXIT;*) Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers, to repopulate the required system tables.
Note 3: If Java Cache error while login the application at first time.
For Oracle Apps 11.5.10.2 run the script.
sql> begin apps.wf_local_synch.BULKSYNCHRONIZATION(P_ORIG_SYSTEM=>'ALL', P_PARALLEL_PROCESSES=>2, P_LOGGING=>'LOGGING', P_RAISEERRORS=>TRUE, P_TEMPTABLESPACE=>'APPS_TS_TX_DATA'); END; /
If you are NOT running the Oracle Applications Table Space Model run ( Mean before Apps 11.5.10 version)
sql> begin apps.wf_local_synch.BULKSYNCHRONIZATION(P_ORIG_SYSTEM=>'ALL', P_PARALLEL_PROCESSES=>2, P_LOGGING=>'LOGGING', P_RAISEERRORS=>TRUE, P_TEMPTABLESPACE=>'APPLSYSX'); END; /
NOTE 4 : After the clone instance started
Add the xml_file_path. Through xml_publisher --- After that run Autoconfig.
Stop the Workflow Manager Services.
1. At Target side : Production Server
Run à adpreclone Oracle Home and Application Home.
2. cp –rph -- All Application Tops to another Mount point ( Except Database C/R/D ).
3. Restore Database from RMAN
3.1 . Copy archive log auto backup backup set folders from Production flash area to
Test server flash area i.e
/p01/oracle/others/rman_oracle_backup/flash_recovery_area/PROD
Scp –r 2010_10_05/ root@erpprod.pittilam.com:/p01/oracle/others/rman.....
3.2 in TEST server /p01/oracle/10gdb/10.2.0
Connect RMAN
$ rman target=/
RMAN> STARTUP NOMOUNT;
RMAN> restore controlfile from '/p01/oracle/others/rman_oracle_backup/flash_recovery_area/PROD/autobackup/2010_10_04/o1_mf_s_731485220_6bl96dq0_.bkp';
RMAN> alter database mount;
RMAN> RESTORE DATABASE UNTIL TIME "TO_DATE('04-Oct-2010 14:06','DD-MON-YYYY HH24:MI')"; -- 2:45 afternoon to
RMAN> RECOVER DATABASE UNTIL TIME "TO_DATE('04-Oct-2010 14:06','DD-MON-YYYY HH24:MI')";
sql> alter database disable block change tracking;
RMAN> ALTER DATABASE OPEN RESETLOGS;
4.1 Add the datafile to Temp table space.
Sql> select property_name, property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE' Sql> ALTER TABLESPACE temp ADD TEMPFILE '/p01/oracle/proddata/temp01.dbf’ SIZE 512m AUTOEXTEND ON NEXT 200m MAXSIZE UNLIMITED
SQL> SELECT tablespace_name, file_name, bytes FROM dba_temp_files WHERE tablespace_name = 'TEMP'; SQL> CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '/u02/oradata/TESTDB/temp2_01.dbf' SIZE 5000M REUSE AUTOEXTEND ON NEXT 1M MAXSIZE unlimited SQL> ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2; SQL> DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;
SQL> SELECT COUNT(*) FROM v$sort_usage WHERE tablespace = 'TEMP'
4.2Copy Datafiles (C/R/D) to Clone Location. $ sqlplus ‘/as sysdba’ SQL> shut immediate; $ cp –r /p01/oracle/proddata ---- > clone location. 4.3Change owner the as per username clone ( in second server) #chown –R clone:dba prodappl --- like do all the folders 5.1Login as a clone user in test server start the clone part . DB_Tier
$/B01/testnew/test10gdb/10.2.0/appsutil/clone/bin
Ls
adcfgclone.pl adchkutl.sh adclone.pl adclonectx.pl core
bash-3.00$ perl adcfgclone.pl dbTier
Enter the APPS password [APPS]:
pllerp
First Creating a new context file for the cloned system.
The program is going to ask you for information about the new system:
Provide the values required for creation of the new Database Context file.
Do you want to use a virtual hostname for the target node (y/n) [n] ?:
Target instance is a Real Application Cluster (RAC) instance (y/n) [n]:
Target System database name [PROD]:CLINST
Target system RDBMS ORACLE_HOME directory [/p01/oracle/10gdb/10.2.0]:/B01/testnew/test10gdb/10.2.0
Target system utl_file accessible directories list [/usr/tmp, /usr/tmp, /p01/oracle/proddb/9.2.0/appsutil/outbound/PROD_prod]:
Number of DATA_TOP's on the target system [4]:2
Target system DATA_TOP 1:/B01/testnew/testdata
Target system DATA_TOP 2:/B01/testnew/testdb/9.2.0/dbs
Do you want to preserve the Display set to prod:0.0 (y/n) [y] ?:n
Target system Display [erpprod:0.0]:
Perl executable location is set to:
/usr/bin/perl
Do you want to preserve the port values from the source system on the target system (y/n) [y] ?:n
Clone Context uses the same port pool mechanism as the Rapid Install
Once you choose a port pool, Clone Context will validate the port availability.
Enter the port pool number [0-99]:
50
Checking the port pool 50
done: Port Pool 50 is free
Database port is 1571
Creating the new Database Context file from :
/B01/testnew/test10gdb/10.2.0/appsutil/template/adxdbctx.tmp
The new database context file has been created :
/B01/testnew/test10gdb/10.2.0/appsutil/CLINST_erpprod.xml
Log file located at /tmp/CloneContext_10091021.log
Running Rapid Clone with command:
perl /B01/testnew/test10gdb/10.2.0/appsutil/clone/bin/adclone.pl java=/B01/testnew/test10gdb/10.2.0/appsutil/clone/bin/../jre mode=apply stage=/B01/testnew/test10gdb/10.2.0/appsutil/clone/bin/.. component=dbTier method=CUSTOM dbctxtg=/B01/testnew/test10gdb/10.2.0/appsutil/CLINST_erpprod.xml showProgress contextValidated=true
Beginning database tier Apply - Sat Oct 9 10:22:46 2010
Log file located at /B01/testnew/test10gdb/10.2.0/appsutil/log/CLINST_erpprod/ApplyDBTier_10091022.log
Completed Apply...
Sat Oct 9 10:26:22 2010
Beginning APPSDB_CLINST registration to central inventory...
ORACLE_HOME NAME : APPSDB_CLINST
ORACLE_HOME PATH : /B01/testnew/test10gdb/10.2.0
Using Inventory location in /etc/oraInst.loc
Log file located at /etc/oraInventory/logs/OracleHomeCloner_10091026.log
ORACLE_HOME /B01/testnew/test10gdb/10.2.0 was registered successfully.
Starting database listener for CLINST:
Running:
/B01/testnew/test10gdb/10.2.0/appsutil/scripts/CLINST_erpprod/addlnctl.sh start CLINST
You are running addlnctl.sh version 115.7
Logfile: /B01/testnew/test10gdb/10.2.0/appsutil/log/CLINST_erpprod/addlnctl.txt
Starting listener process CLINST ...
Listener CLINST has already been started.
addlnctl.sh: exiting with status 0
Note : controlfile manual creation If any controlfile creation error ,You should create controlfile manually, First take controlfile from production server. sql> alter database backup controlfile to trace;
go to udump directory (/t01/oracle/proddb/9.2.0/admin/DEV_erpprod/udump)
$ls -ltr ---------> take latest .trc fileSql> In Test server side: $ vi contnew.trc STARTUP NOMOUNTCREATE CONTROLFILE SET DATABASE "CLNINST" RESETLOGS NOARCHIVELOG--- -- -- '/B01/testnew/testdata/strabus01.dbf'CHARACTER SET US7ASCII; Sql>@contnew.trcSql> alter database open resetlogs; sql> select status from v$instance; ---------- > It should be in Open Mode $ run Autoconfig on DB Tier Note :If you get any coredump error , you can do below action plan:
$ vi /t01/TEST/10gdb/10.2.0/appsutil/CLONENEW_erpprod.xml
/t01/clone/test10gdb/10.2.0/perl/lib/5.8.3/aix-thread-multi: /t01/clone/test10gdb/10.2.0perl/lib/5.8.3: /t01/clone/test10gdb/10.2.0/perl/lib/site_perl/5.8.3/aix-thread-multi: /t01/clone/test10gdb/10.2.0/perl/lib/site_perl/5.8.3: /t01/clone/test10gdb/10.2.0/perl/lib/site_perl:.:/t01/clone/test10gdb/10.2.0/appsutil/perl
/B01/testnew/test10gdb/10.2.0/appsutil/scripts/CLINST_erpprod
adautocfg.sh addbctl.sh adexecsql.pl adpreclone.pl adstrtdb.sql
adchknls.pl addlnctl.sh adlsnodes.sh adstopdb.sql core
Apps Tier
/B01/testnew/testcomn/clone
1.cd
2.move lib lib.old
3.move bin bin.old
4.ln -s jre/bin bin
5.ln -s jre/lib lib
$/B01/testnew/testcomn/clone/bin
adaddnode.pl adclonectx.pl
adcfgclone.pl core
adchkutl.sh javacore2216028.1286642450.txt
adclone.pl
6.Run perl adcfgclone.pl appsTier
-----------------------------------------------------------------------
Note 1: If Apps Tns listener not started
Stop the Services
Run the autoconfig.
Note 2: If Concurrent managers show wrong Nodes. Not starting FNDLIBR
So clear all the Nodes in TEST Server.
Sql>
select node_name "Node Name", node_mode "Mode", support_cp "C", support_web "W", support_admin "A", support_forms "F"from FND_NODES;
SQL> EXEC FND_CONC_CLONE.SETUP_CLEAN; COMMIT; EXIT;*) Run AutoConfig on all tiers, firstly on the DB tier and then the APPS tiers, to repopulate the required system tables.
Note 3: If Java Cache error while login the application at first time.
For Oracle Apps 11.5.10.2 run the script.
sql> begin apps.wf_local_synch.BULKSYNCHRONIZATION(P_ORIG_SYSTEM=>'ALL', P_PARALLEL_PROCESSES=>2, P_LOGGING=>'LOGGING', P_RAISEERRORS=>TRUE, P_TEMPTABLESPACE=>'APPS_TS_TX_DATA'); END; /
If you are NOT running the Oracle Applications Table Space Model run ( Mean before Apps 11.5.10 version)
sql> begin apps.wf_local_synch.BULKSYNCHRONIZATION(P_ORIG_SYSTEM=>'ALL', P_PARALLEL_PROCESSES=>2, P_LOGGING=>'LOGGING', P_RAISEERRORS=>TRUE, P_TEMPTABLESPACE=>'APPLSYSX'); END; /
NOTE 4 : After the clone instance started
Add the xml_file_path. Through xml_publisher --- After that run Autoconfig.
Stop the Workflow Manager Services.
Subscribe to:
Posts (Atom)