Database up gradation from 10g ( 10.2.0.4 ) to 11g ( 11.1.0.7) in Oracle Application 11i

Metalink Note : 452783.1

Step 1 : Down Load the Database Software from (edelivery.oracle.com/ or. http://www.oracle.com/tehnology/software/products/database/index.html
1. 11g Database ( Base versions 11.1.0.6) --- V14215-01_1of2.zip
2. Oracle Database 11g Examples ( formerly Companion ) ---- aix.ppc64_11gR1_examples.zip
3. 11g Database (11.1.0.7) Patch set ---- p6890831_111070_AIX5L_1of2.zip

Step 2: Stop the Oracle Application services , and Shutdown the Database and Db Listener.
Take Full Cold Backup.
Step 3: Create a New 11g Database Home :
/B01/oracle $ mkdir test11gdb
Step 4 : Install the 11g Database only Software only.
$ export Oracle_Base = /B01/oracle/test11gdb …. Export Oracle_SID = TESTNW
cd /t01/clone/others/patches/upgrd_db11g/database_11g/database$
$ ./runInstaller -invPtrLoc /B01/oracle/test11gdb /oraInst.loc

Note: For oracle Inventory Location
Create OraInst.loc file in the ( at New oracle Base /B01/oracle/test11gdb location)
Create directory .. mkdir oraInventory
$ Vi oraInst.loc
Inventory_loc = /B01/oracle/test11gdb/oraInventory
Inst_group = dba
**** next… next… next. ------------------------------------------------------------------------------------------------
Oracle Base Location : /B01/oracle/test11gdb
Oracle Home : /B01/oracle/test11gd/product/11.1.0/db_1 ----------- system will create.
Install ** Software Only **** …… Finish … The Oracle 11g base verion 11.1.0.6 will installed


Step 5: Install the Oracle Database 11g Examples ( formerly Companion )
In the Default New Oracle_home ( /B01/oracle/test11gdb/product/11.1.0/db_1 ).
Source Oracle_home / Oracle Sid
$ cd /t01/clone/others/patches/upgrd_db11g/11g_Example_cd_companion/examples
$ ./runInstaller
**** next … next … some of the products like /ctx/sample directory will create. ---- finsh


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


Step 6: Install the 11g Database (11.1.0.7) Patch set
$ cd /t01/clone/others/patches/upgrd_db11g/11gPatchset/Disk1
$ ./runInstaller -invPtrLoc /B01/oracle/test11gdb /oraInst.loc

--------- next … next …. Next… the patchset will installed i.e 11.1.0.7

Step 7 : Create nls/data/9idata diretory.
$ORACLE_HOME/nl/data/old/cr9idata.pl -----created
$Oracle_home/nls/data/9idata directory.

Step 8 . Apply additional 11.1.0.7 RDBMS Patches. As per the 452783.1 Metalink ID
6530141 , 6815733 , 6972189 , 7111245 , 7253531 , 7295298 , 7486407 , 8940108
9743057.
Opatch utility for apply Database patches.
Create one environment file :
bash-3.00$ cat s.env
export ORACLE_HOME=/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1
export ORACLE_SID=TESTNW
export TNS_ADMIN=/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/network/admin/
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/bin/OPatch:$PATH
export ORACLE_BASE=/B01/oracle/test11gdb/11.1.0

bash-3.00$

-bash-3.00$./opatch version --- find the opatch version
bash-3.00$./opatch apply <>
$ opatch apply /B01/patches/DB_patches/6530141 ------ for apply the patch
opatch rollback -id -- roll back the applied patch
$ORACLE_HOME/OPatch/opatch lsinventory -invPtrLoc /slot/ems2029/oracle/db/tech_st/11.1.0/oraInst.loc
$./opatch lsinventory ------ for find out Patch applied or not.

****** any inventory corruption error
opatch lsinventory –detail
Note:
For Oracle Universal Installer version 10.2.0.2.0 and above, you will have the following scripts in Oracle home to recover from Oracle home inventory corruption:
detachHome.bat / detachHome.sh: Use this script if the Oracle home is corrupted or needs to be updated.
· attachHome.bat / attachHome.sh: Use this script if the Oracle home needs to be added to the inventory.



If Problem Not solved
** Move the /etc/Orainventory Directory …for backup.
Create New /etc/oraInventory directory …

bash-3.00$ pwd at 11g New home. We can find *.sh files.
$find . –name detachHome.sh -print
/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/oui/bin
-rwxrwxr-x 1 oradev dba 213 Jan 29 12:08 detachHome.sh
-rwxrwxr-x 1 oradev dba 306 Jan 29 12:08 attachHome.sh

And run $sh attachHome.sh -------- for new Oracle_home add in New inventory
===================================================================================
Step 9 : For Database Upgradation – Run Pre –Upgrade tool Process. Run utlu111i.sql from Old _ Oracle Home (10g)
a). Copy the utlu111i.sql (/B01/oracle/test11gdb/product/11.1.0/db_1/rdbms/admin) to
/tmp Directory.
b). Stop all the services/ Tns listener / any other in the Instance.
c). Source the enviro nment at Old_Oracle _Home ( /B01/oracle/test10gdb/10.2.0).
$ sqlplus ‘/as sysdba’
Sql> startup ---------- startup the OLD DB from Old_oracle HOME
Sql> spool upgrade_info.log
Sql> @/tmp/utilu11i.sql
Sql> spool off
Sql> shut immediate;

*** open upgrade_info.log file and take correct action plan.

Step 10. Create new Database Listener
$ source the 11g New Database Home
$./netca
Note : ** Give As per the OLD Listener … Same Lisener name : TESTNW … Same Port : 1571

Step 11: Check the Time zone version .. compatibilities

Step 12: Start the Upgradation.
Source the New Database Home . and SID ,Path …
New Listener should be up.
$ cd ORACLE_HOME/bin
$./dbua
· chose … Database Don’t Move.
· Next… next… password : DBSNMP..SYSMAN -> pitti ……………….. FINISH.
------------- OR ----------------
For manual upgrade without use ./dbua
Shut down the database:

SQL> SHUTDOWN

Enter the following SQL*Plus commands:

SQL> STARTUP UPGRADE

SQL> SPOOL patch.log

SQL> @?/rdbms/admin/catupgrd.sql
SQL> SPOOL OFF
SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP
************* Upgraded from source 10.2.0.4 to Target 11.1.0.7 **************
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
------------------------------------------------------------------------------------------------------------------
Modify initialization Parameters Metalink Note : 216205.1 for Oracle 11i ( 11.1.0.7 )
See the Page No: 10
------------------------------------------------------------------------------------------------------------------------------------
After the Database Upgrade
i). Fix Korean lexers
$sqlplus ‘/as sysdba’
@ORACLE_HOME/ctx/sample/script drkorean.sql

ii). Run adgrants.sql
copy $APPL_TOP/admin/agrants.sql to New_Oracle_home/rdbms/admin
$sqlplus ‘/as sysdba’ @ adgrants.sql APPLSYS
III). Grant create procedure privilege on CTXSYS
Copy $AD_TOP/patch115/adctxprv.sql to New_Oracle_home/rdbms/admin
$ sqlplus apps/apps @adctxprv.sql MANAGER CTXSYS
================================================================================
IV). Implement and run AUTOCONFIG.
a).
a. Log in to server with applmgr userb. source /oracle/tst10appl/[context_name].envc. perl $AD_TOP/bin/admkappsutil.pld. cp $APPL_TOP/admin/out/appsutil.zip /B01/oracle/test11gdb/product/11.1.0/db_1/e. Login to server with oracle userf. cd /B01/oracle/test11gdb/product/11.1.0/db_1 -- NEW 11g HOMEg. unzip –o appsutil.zip
b). source the environment
bash-3.00$ cat s.env
export ORACLE_HOME=/B01/oracle/test11gdb/product/11.1.0/db_1
export ORACLE_SID=TESTNW
export TNS_ADMIN=/B01/oracle/test11gdb/product/11.1.0/db_1/network/admin/
export LD_LIBRARY_PATH=$ORACLE_HOME/lib
export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/bin/OPatch:$PATH
export ORACLE_BASE=/B01/oracle/test11gdb/11.1.0

$ s.env ------------- source the environment.

c). ** create .xml file create.

$cd /B01/oracle/test11gdb/product/11.1.0/db_1/bin
$perl adbldxml.pl tier=db appsuser=apps appspass=apps
Host name : erpprod
SID : TESTNW
Xdisplay : erpprod:0.0

d). *** Run Auto Config from db_tier.
$cd /B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/bin
$ ./adconfig.sh
Enter the context file path =/B01/oracle/test11gdb/product/11.1.0/db_1/appsutil/TESTNW_erpprod.xml




*** After complete the autoconfig the Environment file will automatically created in 11g oracle_home
Cd /B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1
ash-3.00$ ls *.env
TESTNW_erpprod.env






e). * Note : autoconfing core dump error ,

Solution : take ,xml backup , and modified with perl verision 5.8.3 , and re-run autoconfig.
$ cp TESTNW_erpprod.xml TESTNW_erpprod.xml_bak07_feb_2011
$ vi TESTNW_erpprod.xml
/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/perl/bin/perl
/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/perl/lib/5.8.3/aix-thread-multi:/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/perl/lib/5.8.3:/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/perl/lib/site_perl/5.8.3/aix-thread-multi:/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/perl/lib/site_perl/5.8.3:/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/perl/lib/site_perl:.:/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/appsutil/perl:/B01/oracle/test11gdb/11.1.0/product/11.1.0/db_1/appsutil/perl

· - re – run autoconfig at db_tier.
==============================================================================
f). Note : any temp datafile error while run autoconfing.

HELP : RE- CREATE / Add the temp data file for NEW TEMPORARY TABLESPACE
--------------------------------------------------------------------------------------------------

select property_name, property_value from database_properties where property_name='DEFAULT_TEMP_TABLESPACE'

CREATE TEMPORARY TABLESPACE temp1
TEMPFILE '/B01/oracle/testdata/temp01.dbf' SIZE 5000M REUSE
AUTOEXTEND ON NEXT 1M MAXSIZE unlimited

DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES; --- drop old one.

ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp1

ALTER TABLESPACE temp1 ADD TEMPFILE '/B01/oracle/testdata/temp02.dbf' SIZE 5000M REUSE
AUTOEXTEND ON NEXT 1m MAXSIZE UNLIMITED





v). Gather statistics for Sys schema.
Copy $APPL_TOP/admin/adstats.sql to New_oracle_home/rdbms/admin
Sql> shutdown normal
Sql> startup restrict;
Sql> @adstats.sql
Sql> shutdown normal ;
Sql> startup.

VI. Re-Create custom database links
Sql> select db_link from dba_db_links;
Sql> drop database link ( custom database link )
Sql> create database link < testnw=" (DESCRIPTION="" address="(PROTOCOL="tcp)(HOST="erpprod.pittilam.com)(PORT="1571))" connect_data=" (SID="TESTNW)">user / pwd /port number / sid . net lisener ) )


VII . Re- create grants and synonyms.
At Application side : source the environment
$ adadmin
Select “Application file system “ menu à Recreate grants and synonyms for APPS Schema.

VII. Restart the Application Server Processes.
Modify initialization Parameters Metalink Note : 216205.1 for Oracle 11i ( 11.1.0.7 )
Through dbua create new initTESTNW.ora file.
***** bash-3.00$ cat initTESTNW.ora

TESTNW.__db_cache_size=230686720
TESTNW.__java_pool_size=67108864
TESTNW.__large_pool_size=117440512
TESTNW.__oracle_base='/B01/oracle/test11gdb/11.1.0'#ORACLE_BASE set from environment
TESTNW.__pga_aggregate_target=1073741824
TESTNW.__sga_target=1073741824
TESTNW.__shared_io_pool_size=0
TESTNW.__shared_pool_size=629145600
TESTNW.__streams_pool_size=4194304
*._b_tree_bitmap_plans=FALSE# adding as per metalink docu
*._optimizer_autostats_job=false# adding as per metalink docu
*.aq_tm_processes=1
*.audit_file_dest='/B01/oracle/test11gdb/11.1.0/TESTNW/admin/adump'
*.compatible='11.1.0'
*.control_files='/B01/oracle/testdata/cntrl01.dbf','/B01/oracle/testdb/cntrl02.dbf','/B01/oracle/testdata/cntrl03.dbf'
*.cursor_sharing='EXACT'
*.db_block_checking='FALSE'
*.db_block_checksum='TRUE'
*.db_block_size=8192
*.db_files=512
*.db_name='TESTNW'
*.diagnostic_dest='/B01/oracle/test11gdb/11.1.0'
*.dml_locks=10000
*.java_pool_size=67108864
*.job_queue_processes=2
*.large_pool_size=117440512
*.local_listener='LISTENER_TESTNW'
*.log_buffer=14238720
*.log_checkpoint_interval=100000
*.log_checkpoint_timeout=1200
*.log_checkpoints_to_alert=TRUE
*.max_dump_file_size='20480'
*.nls_comp='binary'
*.nls_date_format='DD-MON-RR'
*.nls_language='american'
*.nls_length_semantics='BYTE'
*.nls_numeric_characters='.,'
*.nls_sort='binary'
*.nls_territory='america'
*.olap_page_pool_size=4194304
*.open_cursors=600
*.optimizer_secure_view_merging=FALSE
*.parallel_max_servers=8
*.parallel_min_servers=0
*.pga_aggregate_target=1073741824
*.plsql_code_type='INTERPRETED'
*.plsql_optimize_level=2
*.processes=400
*.query_rewrite_enabled='true'
*.sec_case_sensitive_logon=FALSE# adding as per metalink docu
*.session_cached_cursors=500
*.sessions=445
*.sga_target=1073741824
*.shared_pool_reserved_size=157286400
*.shared_pool_size=314572800
*.undo_management='AUTO'
*.undo_tablespace='APPS_UNDOTS1'
*.utl_file_dir='/usr/tmp','/usr/tmp','/usr/tmp','/p01/oracle/proddb/9.2.0/appsutil/outbound/PROD_prod','/B01/oracle/test10gdb/10.2.0/appsutil/outbound/TESTNW_erpprod','/usr/tmp'
*.workarea_size_policy='AUTO'
bash-3.00$

CREATE A PHYSCIAL STANDBY DATABASE - ORACLE 10G DATAGUARD CONCEPT.

1. Os versions / Database Versions / Patchset Level should be same.
2. SID should be same , if not use convert parameter in pfile.
· Here SID = CLONE is the same between two servers.

Primary side

Hostname : devr12.pittioffice.com
Sid : CLONE
Ip : 192.168.0.209

a).Install the Linux Operating system , and #setup -à Disable Firewall at OS Level Mandatory
b).Install the Oracle 10g Software
System pwd : sadha ; Sys pwd : pitti ---à these two password should same into two nodes.

Rman configuration -- if require.
Database in the Archive log mode.


c). configure ssh between two servers.

**
Ssh configuration between to nodes ( servers )
1 st Node side side : 192.168.0.209 Primary side.

a). Login – oracle user
b). mkdir ~/.ssh
chmod 755 ~/.ssh
$/usr/bin/ssh-keygen –t rsa
$ /usr/bin/ssh-keygen –t -dsa
$ cd .ssh
$ cat id_rsa.pub > devr12
Cat id_dsa.pub >> devr12

2nd Node side: 192.168.0.99 Standby side.

a). Login – oracle user
b). mkdir ~/.ssh
chmod 755 ~/.ssh
$/usr/bin/ssh-keygen –t rsa
$ /usr/bin/ssh-keygen –t -dsa
$ cd .ssh
$ cat id_rsa.pub > test
Cat id_dsa.pub >> test
$ scp test devr12:/p01/oratest/.ssh






1 st Node side

$ cd .ssh
$ cat devr12 > authorized_keys
$ cat test > > authorized_keys

$ scp authorized_keys test:/p01/oratest/.ssh
**** Establish user equableness
$/usr/bin/ssh-agent $SHELL
$/usr/bin/ssh-add

2 nd Node side
**** Establish user equableness
$/usr/bin/ssh-agent $SHELL
$/usr/bin/ssh-add


**** check the without password we can connect through ssh between 2 servers ( both nodes side )

$ ssh test
$ ssh devr12
$ ssh test.pittioffice.com
$ ssh devr12.pittiofficec.om
$ ssh 192.168.0.99
$ ssh 192.168.0.209










*** Create environment file.

[test@devr12 db_1]$ cat clone.env

export ORACLE_HOME=/p01/oratest/oracle/product/10.2.0/db_1
export ORACLE_SID=CLONE
export PATH=$ORACLE_HOME/bin:$PATH
export TNS_ADMIN=/p01/oratest/oracle/product/10.2.0/db_1/network/admin

[test@devr12 db_1]$






d). Configure the Listener.ora at primiary side.

[test@devr12 admin]$ pwd
/p01/oratest/oracle/product/10.2.0/db_1/network/admin

[test@devr12 admin]$ cat listener.ora
# listener.ora Network Configuration File: /p01/oratest/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

CLONE =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = devr12.pittioffice.com)(PORT = 1522))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
)
)

SID_LIST_CLONE =
(SID_LIST =
(SID_DESC =
(SID_NAME = CLONE)
(ORACLE_HOME = /p01/oratest/oracle/product/10.2.0/db_1)
)
)






















e).Configure Tnsnames.ora at primary side.


CLONE =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = devr12.pittioffice.com)(PORT = 1522))
)
(CONNECT_DATA =
(SERVICE_NAME = CLONE)
)
)

CLONE_STANDBY =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.pittioffice.com)(PORT = 1522))
)
(CONNECT_DATA =
(SID=CLONE)
)
)

EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = CLONE)
(PRESENTATION = RO)
)
)






******************************************************************************

** startup the listener at primary side.

$ lsnrctl start CLONE.

f).* Create password file at Primary side. And copy to standby side

$ orapwd file=$ORACLE_HOME/dbs/orapw$ORACLE_SID password=oracle entries=5 force=y

Copy to orapwd file to Standby database side.



g). Ad the Standby redolog at Primary side. With same size.


ALTER DATABASE ADD STANDBY LOGFILE GROUP 4 ('/p01/oratest/product/10.2.0/oradata/CLONE/redo004.log') SIZE 50M;ALTER DATABASE ADD STANDBY LOGFILE GROUP 5 ('/p01/oratest/product/10.2.0/oradata/CLONE/redo004.log') SIZE 50M;ALTER DATABASE ADD STANDBY LOGFILE GROUP 6 ('/p01/oratest/product/10.2.0/oradata/CLONE/redo004.log') SIZE 50M;
SQL> select group# from v$standby_log;






















H).Configure Listener.ora at Standby side.
** STANDBY SIDE : listener.ora



CLONE_STANDBY =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
(ADDRESS = (PROTOCOL = TCP)(HOST = test.pittioffice.com)(PORT = 1522 ))
)
)

SID_LIST_CLONE_STANDBY =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /p01/oratest/oracle/product/10.2.0/db_1)
(SID_NAME=CLONE)
)
)
























i).Configure Tnsnames.ora at Stand by side.

** STANDBY SIDE : tnsnames.ora


CLONE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = devr12.pittioffice.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = CLONE)
)
)

CLONE_STANDBY =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = test.pittioffice.com)(PORT = 1522))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SID=clone)
)
)


EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1))
)
(CONNECT_DATA =
(SID = PLSExtProc)
(PRESENTATION = RO)
)
)








J. *** check the listener/ tnsnames ping between servers


1. *** from Primary side to ping standby server.

[test@devr12 dbs]$ tnsping clone_standby

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 03-JAN-2011 17:03:44

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/p01/oratest/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = test.pittioffice.com)(PORT = 1522))) (CONNECT_DATA = (SERVICE_NAME = CLONE_STANDBY)))
OK (0 msec)
[test@devr12 dbs]$

=========================================================================
2. *** From Standby side to ping primary server


[test@test dbs]$ hostname
test.pittioffice.com
[test@test dbs]$ tnsping CLONE

TNS Ping Utility for Linux: Version 10.2.0.1.0 - Production on 03-JAN-2011 05:23:12

Copyright (c) 1997, 2005, Oracle. All rights reserved.

Used parameter files:
/p01/oratest/oracle/product/10.2.0/db_1/network/admin/sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = devr12.pittioffice.com)(PORT = 1522)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = CLONE)))
OK (10 msec)
[test@test dbs]$
--------------------------------------------------------------------------------------------------------------------------------
*** $tnsping 192.168.0.99 , tnsping test.pittioffice.com , tnsping TNSNAME.

* Some time : TNS:12560 Tns Protocol adapter error ------ solution At OS level disable firewall




3. * Database connectivity checking from two servers.

** from Primary side.


$ sqlplus sys/pitti@CLONE_STANDBY as sysdba

$sqlplus system/sadha@CLONE_STANDBY as sysdba
Enter user name : system
Pwd : sadha



*** From Standby side

$ [test@test ~]$ sqlplus sys/pitti@CLONE as sysdba

SQL*Plus: Release 10.2.0.1.0 - Production on Sun Feb 6 22:42:58 2011
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL>

$ $sqlplus system/sadha@CLONE as sysdba



















PRIMARY SIDE
** Modify initCLONE.ora file from Primary Side ( Pfile ).

CLONE.__db_cache_size=905969664
CLONE.__java_pool_size=16777216
CLONE.__large_pool_size=16777216
CLONE.__shared_pool_size=285212672
CLONE.__streams_pool_size=0
*.audit_file_dest='/p01/oratest/oracle/product/10.2.0/db_1/admin/CLONE/adump'
*.background_dump_dest='/p01/oratest/oracle/product/10.2.0/db_1/admin/CLONE/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/p01/oratest/oracle/product/10.2.0/oradata/CLONE/control01.ctl','/p01/oratest/oracle/product/10.2.0/oradata/CLONE/control02.ctl','/p01/oratest/oracle/product/10.2.0/oradata/CLONE/control03.ctl'
*.core_dump_dest='/p01/oratest/oracle/product/10.2.0/db_1/admin/CLONE/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.db_recovery_file_dest_size=39728447488
*.db_recovery_file_dest='/p01/oratest/oracle/product/10.2.0/rman_bkp/flash_recovery_area'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=CLONEXDB)'
*.open_cursors=300
*.pga_aggregate_target=412090368
*.processes=150
*.sga_target=1237319680
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/p01/oratest/oracle/product/10.2.0/db_1/admin/CLONE/udump'
*.db_name='CLONE'

# Datagurad – Physcial standby database – parameters

*.db_unique_name='CLONE'
*.fal_client='CLONE'
*.fal_server='CLONE_STANDBY'
*.job_queue_processes=10
*.log_archive_dest_1='LOCATION=/p01/oratest/oracle/product/10.2.0/arch_bkp VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CLONE'

*.log_archive_dest_2='SERVICE=CLONE_STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CLONE_STANDBY'
*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='CLONE_%t%s%r.arc'
*.log_archive_max_processes=30
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_archive_dest='/p01/oratest/oracle/product/10.2.0/arch_bkp'
*.standby_file_management='AUTO'

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



Note : copy the initClone.ora file to Standby Database Location
Note: Dataguard is using spfile file.

Create spfile from primary side.

$sqlplus ‘/as sysdba’
$startup nomount;
$create spfile from pfile;
$ shut immediate;
$exit

** Next login default database is using spfile.




----------------------------------------------------------------------------------------------------------------------------------
STAND BY SIDE.

** Create the all directory structures as per the primary spfile into to the Standby server

** Modify initCLONE.ora file from Standby Side ( Pfile ).

CLONE.__db_cache_size=905969664
CLONE.__java_pool_size=16777216
CLONE.__large_pool_size=16777216
CLONE.__shared_pool_size=285212672
CLONE.__streams_pool_size=0
*.audit_file_dest='/p01/oratest/oracle/product/10.2.0/db_1/admin/CLONE/adump'
*.background_dump_dest='/p01/oratest/oracle/product/10.2.0/db_1/admin/CLONE/bdump'
*.compatible='10.2.0.1.0'
*.control_files='/p01/oratest/sadhanew/clone.ctl'
*.core_dump_dest='/p01/oratest/oracle/product/10.2.0/db_1/admin/CLONE/cdump'
*.db_block_size=8192
*.db_domain=''
*.db_file_multiblock_read_count=16
*.open_cursors=300
*.pga_aggregate_target=412090368
*.processes=150
*.sga_target=1237319680
*.undo_management='AUTO'
*.undo_tablespace='UNDOTBS1'
*.user_dump_dest='/p01/oratest/oracle/product/10.2.0/db_1/admin/CLONE/udump'
*.db_recovery_file_dest_size=39728447488
*.db_recovery_file_dest='/p01/oratest/oracle/product/10.2.0/rman_bkp/flash_recovery_area'


*.dispatchers='(PROTOCOL=TCP) (SERVICE=CLONEXDB)'
*.job_queue_processes=10

**** Dataguard / standby database parameters at standby side.

*.db_name='CLONE'
*.db_unique_name='CLONE_STANDBY'

*.fal_client='CLONE_STANDBY'
*.fal_server='CLONE'

*.log_archive_dest_1='LOCATION=/p01/oratest/oracle/product/10.2.0/arch_bkp VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=CLONE_STANDBY'

*.log_archive_dest_2='SERVICE=CLONE_STANDBY LGWR ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=CLONE'

*.log_archive_dest_state_1='ENABLE'
*.log_archive_dest_state_2='ENABLE'
*.log_archive_format='%t%s%r.arc'
*.log_archive_max_processes=30
*.remote_login_passwordfile='EXCLUSIVE'
*.standby_archive_dest='/p01/oratest/oracle/product/10.2.0/arch_bkp'
*.standby_file_management='AUTO'







Create spfile from standby side.

$sqlplus ‘/as sysdba’
$startup nomount;
$create spfile from pfile;
$ shut immediate;
$exit

** Next login default database is using spfile.










* PRIMARY SIDE :

* CREATE STANDBY CONTROL FILE AND TAKE COLD BACKUP / copy data to stand by side

Creae standby controlfile

$sqlplus ‘/as sysdba’
Sql>startup
Sql> alter database create standby controlfile as ‘/p01/oratest/sdhanew/standby.ctl’;


Cold backup

Sql> shut immediate;
$ cd /p01/oratest/oracle/product/10.2.0/oradata/CLONE
$ copy *.* /backup.

And copy all the files to Standby Database.

$ scp *.log *.dbf *.ctl test@192.168.0.99:/p01/oratest/oracle/product/10.2.0/oradata/CLONE/.



Copy Standby ctl to Standby side.

$ cd /p01/oratest/sadhanew/
$scp standby.ctl test@192.168.0.99:/p01/oratest/sadhanew/.



















*** Standby Side


a). change the controlfile location in the initClone.ora ( spfile ).

*.control_files='/p01/oratest/sadhanew/clone.ctl'

Save and exit :wq --- enter

$sqlplus ‘/as sysdba’
Sql>startup nomount;
Sql>create spfile from pfile;
Sql>shut immediate;
Sql>exit

b). At standby side database put into MRM mode.

$sqlplus ‘/as sysdba’
Sql>startup nomount;
Sql> alter databse mount standby database;
Sql> alter database recover managed standby database disconnect from session;




==================================================================
· Startup the database at PRIMARY SIDE.

Sql> startup.
Sql> archive log list; -------------- check the current log sequence.

Sql> alter system switch log file ---à manual log switch.


Sql> check the Log apply .. at Standby side. ( check into Alert log file ).


Other Help commands

**
1. Stop Redo apply to Standby
At Standby side:
Sql> alter database recover managed standby database cancel;

2. check that primary and standby are synchronized.
Primary side:
Sql> archive log list;
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
---------------- ----------
PRIMARY READ WRITE

Standby side
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE OPEN_MODE
PHYSICAL STANDBY MOUNTED


3. ACTIVATE the Physical standby database.
Sql> ALTER DATABASE ACTIVATE STANDBY DATABASE;
FOR reverse.
Sql> Alter database covert to physical standby;


4. Register the missed redo.

At standby side ;

sql> alter database register logfile '/............................/......xx.arc'

Help urls:

http://download.oracle.com/docs/cd/B19306_01/server.102/b14239.pdf

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.

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.