ps:綠字為之前打錯或漏掉修正的部分~

--登入app user

--adpatch 依序打以下patch每個patch ,閱讀README.txt 進行,pre-install,post

[applmgr@oel5u7-x64 sql]$adstpall.sh apps/apps

[applmgr@oel5u7-x64 sql]$adadmin

-- enable maintain mode

5.    Change Maintenance Mode

   1.    Enable Maintenance Mode

adpatch9062910 =>INTEROPERABILITY PATCH FOR R12.1.1 ON 11.2 RDBMS

adpatch9868229 =>CST_LAYER_ACTUAL_COST_DTLS_V BECOMES INVALID AFTER 11.2.0.2 UPGRADE

adpatch10163753 =>BIV_B_AGE_H_SUM_MV FAILS DURING INDEX CREATION

adpatch11071569 =>ADBLDXML FAILS ON WINDOWS SERVER 2008 R2 64 BIT

adpatch9403153  =>Warehouse patch ,沒有的話不需安裝

adpatch9738085  =>WIN:SOURCING CMD FILE ON WINDOWS EXITS WITH 1

adpatch9852070  =>ADBLDXML AND AUTOCONFIG COMPLETES WITH JAVA.LANG.UNSATISFIEDLINKERROR

==========================================

--opatch p6400501_10105_LINUX.zip ,注意:即便是64 bit也要打32 bit 10105 版本

--如果db11.0.6以下還需要用opatch p6400501_111060_LINUX.zip(32 bit) . p6400501_111060_Linux-x86-64.zip(64 bit)RDBMS 環境

[applmgr@oel5u7-x64 sql]$cp p6400501_10105_LINUX.zip  $IAS_ORACLE_HOME

[applmgr@oel5u7-x64 sql]$cd $IAS_ORACLE_HOME

[applmgr@oel5u7-x64 sql]$unzip p6400501_10105_LINUX.zip

[applmgr@oel5u7-x64 sql]$cd 6400501

[applmgr@oel5u7-x64 sql]$opatch apply

[applmgr@oel5u7-x64 sql]$cd $ORACLE_HOME/forms/lib32 $ORACLE_HOME/forms/lib

[applmgr@oel5u7-x64 sql]$make -f ins_forms.mk install

[applmgr@oel5u7-x64 sql]$cd $ORACLE_HOME/reports/lib32    $ORACLE_HOME/reports/lib

[applmgr@oel5u7-x64 sql]$make -f ins_reports.mk install

--如果 make發生 /usr/lib/libXtst.so.6: undefined reference to  錯誤,且作業系統為rhel 5 oel5

--則操作如下後再重作make 指令

[root@oel5u7-x64 lib] unlink /usr/lib/libXtst.so.6

[root@oel5u7-x64 lib] ln -s /usr/X11R6/lib/libXtst.so.6.1 /usr/lib/libXtst.so.6

==========================================

--登入11.0.7 db user opatch 打以下patch  ,請閱讀README.txt 進行,pre-install,post

opatch4247037=>RFID-EPC from Spatial TEAM

 

--清空11.0.7 oracle 環境變數後,開始安裝 ,不能用11.0.7ORACLE_HOME ,在這我指定為/ebs/prod/db/tech_st/11.2.0

==========================================

安裝11.2.0.3 db軟體,

--db src ,要兩個都解壓完成再執行安裝 ,不然裝到一半會有問題

[root@oel5u7-x64 11.2.0.3]$xhost +

[oramgr@oel5u7-x64 11.2.0.3]$unzip p10404530_112030_Linux-x86-64_1of7

[oramgr@oel5u7-x64 11.2.0.3]$unzip p10404530_112030_Linux-x86-64_2of7

[oramgr@oel5u7-x64 11.2.0.3]$cd database

[oramgr@oel5u7-x64 11.2.0.3]$./runInstaller

--選擇只安裝軟體,不要裝db , 記得加選 ebs 有安裝的語言,ZHT,ZHS

--ORACLE_BASE我設為/ebs/prod/db/tech_st

--ORACLE_HOME 設為/ebs/prod/db/tech_st/11.2.0

--orainventory 設為/ebs/prod/db/orainventory

 

==========================================

安裝11.2.0.3 db example,

[oramgr@oel5u7-x64 11.2.0.3]$unzip p10404530_112030_Linux-x86-64_6of7

[oramgr@oel5u7-x64 11.2.0.3]$cd example

[oramgr@oel5u7-x64 11.2.0.3]$./runInstaller

--安裝時記得選新的11.2.0.3 ORACLE_HOME

 

 

=====================================================

--抓新版1120x  opatch p6880880_112000_Linux-x86-64.zip

[oramgr@oel5u7-x64 11.2.0]#mv OPatch OPatch.old

[oramgr@oel5u7-x64 11.2.0]#unzip p6880880_112000_Linux-x86-64.zip

[oramgr@oel5u7-x64 11.2.0]#rm p6880880_112000_Linux-x86-64.zip

=====================================================

 

 

==========================================

--建立11.2.0 script

=====================================

--設定 11.2.0.3 為新的oracle 環境變數 ,記住PATH 包含opatch,perl路徑等都要改成11.2.0.3 版本的路徑

[oramgr@oel5u7-x64 11.2.0]$ export ORACLE_BASE=/ebs/prod/db/tech_st

[oramgr@oel5u7-x64 11.2.0]$ export ORACLE_HOME=/ebs/prod/db/tech_st/11.2.0

[oramgr@oel5u7-x64 11.2.0]$ export PATH=/ebs/prod/db/tech_st/11.2.0/bin:/ebs/prod/db/tech_st/11.2.0/perl/bin:/usr/bin:/usr/sbin:/ebs/prod/db/tech_st/11.2.0/appsutil/jre/bin:/bin:/usr/bin/X11:/usr/local/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oramgr/bin:/ebs/prod/db/tech_st/11.2.0/OPatch

[oramgr@oel5u7-x64 11.2.0]$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib

[oramgr@oel5u7-x64 11.2.0]$ export PERL5LIB=$ORACLE_HOME/perl/lib/5.10.0:$ORACLE_HOME/perl/lib/site_perl/5.10.0

=====================================

--建立 nls/data/9idata 資料

[oramgr@oel5u7-x64 11.2.0]$ perl $ORACLE_HOME/nls/data/old/cr9idata.pl

 

Creating directory /ebs/prod/db/tech_st/11.2.0/nls/data/9idata ...

 

Copying files to /ebs/prod/db/tech_st/11.2.0/nls/data/9idata...

Copy finished.

 

Please reset environment variable ORA_NLS10 to /ebs/prod/db/tech_st/11.2.0/nls/data/9idata!

 

--設定nls環境變數到新路徑

[oramgr@oel5u7-x64 11.2.0]$ export ORA_NLS10=/ebs/prod/db/tech_st/11.2.0/nls/data/9idata

 

[oramgr@oel5u7-x64 11.2.0]$ export TNS_ADMIN=/ebs/prod/db/tech_st/11.2.0/network/admin/PROD_oel5u7-x64

 

[oramgr@oel5u7-x64 11.2.0]$ cd $ORACLE_HOME

 

[oramgr@oel5u7-x64 11.2.0]$ export ORACLE_SID=PROD

 

=====================================

 

--原視窗不動,另開新的console 視窗 吃舊的 11.0.7 env 後連接sqlplus

[oramgr@oel5u7-x64 11.1.0]$sqlplus / as sysdba

 

--執行11.2.0.3 路徑下的utlu112i.sql,進行前置建議

SQL> SPOOL upgrade_11203.log

SQL> @/ebs/prod/db/tech_st/11.2.0/utlu112i.sql

SQL> SPOOL OFF

SQL> exit

=====================================

---產出檢查結果  upgrade_11203.log

Oracle Database 11.2 Pre-Upgrade Information Tool 10-29-2011 05:39:17                              

Script Version: 11.2.0.3.0 Build: 001                                                              

.                                                                                                   

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

Database:                                                                                           

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

--> name:          PROD                                                                            

--> version:       11.1.0.7.0                                                                       

--> compatible:    11.1.0                                                                          

--> blocksize:     8192                                                                             

--> platform:      Linux x86 64-bit                                                                

--> timezone file: V10                                                                             

.                                                                                                   

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

Tablespaces: [make adjustments in the current environment]                                          

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

--> SYSTEM tablespace is adequate for the upgrade.                                                 

.... minimum required size: 11323 MB                                                                

--> CTXD tablespace is adequate for the upgrade.                                                   

.... minimum required size: 16 MB                                                                  

--> ODM tablespace is adequate for the upgrade.                                                    

.... minimum required size: 10 MB                                                                  

--> APPS_UNDOTS1 tablespace is adequate for the upgrade.                                           

.... minimum required size: 400 MB                                                                 

--> APPS_TS_TX_DATA tablespace is adequate for the upgrade.                                        

.... minimum required size: 6082 MB                                                                

--> APPS_TS_QUEUES tablespace is adequate for the upgrade.                                         

.... minimum required size: 108 MB                                                                  

--> SYSAUX tablespace is adequate for the upgrade.                                                 

.... minimum required size: 553 MB                                                                 

.                                                                                                   

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

Flashback: OFF                                                                                      

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

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

Update Parameters: [Update Oracle Database 11.2 init.ora or spfile]                                

Note: Pre-upgrade tool was run on a lower version 64-bit database.                                 

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

--> If Target Oracle is 32-Bit, refer here for Update Parameters:                                  

-- No update parameter changes are required.                                                       

.                                                                                                   

 

--> If Target Oracle is 64-Bit, refer here for Update Parameters:                                   

-- No update parameter changes are required.                                                       

.                                                                                                  

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

Renamed Parameters: [Update Oracle Database 11.2 init.ora or spfile]                               

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

-- No renamed parameters found. No changes are required.                                           

.                                                                                                  

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

Obsolete/Deprecated Parameters: [Update Oracle Database 11.2 init.ora or spfile]                   

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

--> plsql_native_library_dir     11.2       OBSOLETE                                               

--> plsql_native_library_subdir_ 11.2       OBSOLETE                                               

.                                                                                                   

 

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

Components: [The following database components will be upgraded or installed]                      

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

--> Oracle Catalog Views         [upgrade]  VALID                                                   

--> Oracle Packages and Types    [upgrade]  VALID                                                  

--> JServer JAVA Virtual Machine [upgrade]  VALID                                                  

--> Oracle XDK for Java          [upgrade]  VALID                                                  

--> Real Application Clusters    [upgrade]  INVALID                                                

--> OLAP Analytic Workspace      [upgrade]  VALID                                                   

--> OLAP Catalog                 [upgrade]  VALID                                                  

--> Oracle Text                  [upgrade]  VALID                                                  

--> Oracle XML Database          [upgrade]  VALID                                                  

--> Oracle Java Packages         [upgrade]  VALID                                                  

--> Oracle interMedia            [upgrade]  VALID                                                   

--> Spatial                      [upgrade]  VALID                                                  

--> Data Mining                  [upgrade]  VALID                                                  

--> Oracle OLAP API              [upgrade]  VALID                                                  

.                                                                                                  

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

Miscellaneous Warnings                                                                             

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

WARNING: --> Database is using a timezone file older than version 14.                              

.... After the release migration, it is recommended that DBMS_DST package                          

.... be used to upgrade the 11.1.0.7.0 database timezone version                                    

.... to the latest version which comes with the new release.                                       

WARNING: --> Your recycle bin contains 185 object(s).                                              

.... It is REQUIRED that the recycle bin is empty prior to upgrading                               

.... your database.  The command:                                                                  

        PURGE DBA_RECYCLEBIN                                                                        

.... must be executed immediately prior to executing your upgrade.                                 

WARNING: --> Database contains schemas with objects dependent on DBMS_LDAP package.                

.... Refer to the 11g Upgrade Guide for instructions to configure Network ACLs.                    

.... USER APPS has dependent objects.                                                              

.                                                                                                   

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

Recommendations                                                                                    

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

Oracle recommends gathering dictionary statistics prior to                                         

upgrading the database.                                                                            

To gather dictionary statistics execute the following command                                      

while connected as SYSDBA:                                                                         

 

    EXECUTE dbms_stats.gather_dictionary_stats;                                                    

 

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

Oracle recommends removing all hidden parameters prior to upgrading.                               

 

To view existing hidden parameters execute the following command                                   

while connected AS SYSDBA:                                                                         

 

    SELECT name,description from SYS.V$PARAMETER WHERE name                                        

        LIKE '\_%' ESCAPE '\'                                                                       

 

Changes will need to be made in the init.ora or spfile.                                            

 

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

Oracle recommends reviewing any defined events prior to upgrading.                                  

 

To view existing non-default events execute the following commands                                 

while connected AS SYSDBA:                                                                          

  Events:                                                                                          

    SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2                            

      WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE'                                           

 

  Trace Events:                                                                                     

    SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2                           

      WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'                                     

 

Changes will need to be made in the init.ora or spfile.                                            

 

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

The DMSYS schema exists in the database.  Prior to performing an                                   

upgrade Oracle recommends that the DMSYS schema, and its associated                                

objects be removed from the database.                                                              

 

Refer to the Oracle Data Mining Administration Guide for the                                       

instructions on how to perform this task.                                                          

 

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

SQL> spool off

 

================================

--依照upgrade_11203.log 檢查結果說明調整,做升級準備  11.0.7 db

[oramgr@oel5u7-x64 11.1.0]$ sqlplus / as sysdba

SQL> PURGE DBA_RECYCLEBIN

DBA Recyclebin purged.

 

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

 

PL/SQL procedure successfully completed.

 

--drop sys.enabled$indexes 如不存在可忽略

SQL> drop table sys.enabled$indexes;

 

SQL> create spfile from pfile;

 

SQL> shutdown immediate;

 

SQL> exit

 

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

 

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

 

 =====================================

--建立11.2.0.3 prod pfile/spfile,複製11.0.7的到11.2.03

 

[oramgr@oel5u7-x64 11.1.0]$ cd /ebs/prod/db/tech_st/11.1.0/dbs

 

[oramgr@oel5u7-x64 11.1.0]$ cp initPROD.ora /ebs/prod/db/tech_st/11.2.0/dbs/

 

[oramgr@oel5u7-x64 11.1.0]$ cp spfilePROD.ora /ebs/prod/db/tech_st/11.2.0/dbs/

 

[oramgr@oel5u7-x64 11.1.0]$ cp PROD_oel5u7-x64_ifile.ora /ebs/prod/db/tech_st/11.2.0/dbs/

--修改 initPROD.ora裡面的所有 路徑有 11.1.0 相關字眼的,改成 11.2.0 

--註解掉 initPROD.ora裡面的 plsql_native_library_dir ,plsql_native_library_subdir_count ,_index_join_enabled,query_rewrite_enabled,nls_language,timed_statistics,plsql_optimize_level

--其他參數請參考doc : 396009.1, Database Initialization Parameter Settings for Oracle Applications Release 12

=====================================

--建立11.2.0 prod net , listener tnsname etc.. ,複製11.0.7的到11.2.03

[oramgr@oel5u7-x64 11.2.0]$  cp /ebs/prod/db/tech_st/11.1.0/network/admin/PROD_oel5u7-x64 /ebs/prod/db/tech_st/11.2.0/network/admin

 

[oramgr@oel5u7-x64 11.2.0]$  cd /ebs/prod/db/tech_st/11.2.0/network/admin

--修改/ebs/prod/db/tech_st/11.2.0/network/admin裡面的sqlnet.ora, listener.ora, tnsnames.ora 內容路徑從 11.1.0 -> 11.2.0

 

=====================================

--建立11.2.0 prod db dump 資料夾

[oramgr@oel5u7-x64 11.2.0]$ cd /ebs/prod/db/tech_st/11.2.0

 

[oramgr@oel5u7-x64 11.2.0]$ mkdir admin

 

[oramgr@oel5u7-x64 11.2.0]$ cd admin

 

[oramgr@oel5u7-x64 11.2.0]$ mkdir PROD_oel5u7-x64

 

[oramgr@oel5u7-x64 11.2.0]$ cd PROD_oel5u7-x64

 

[oramgr@oel5u7-x64 11.2.0]$ mkdir bdump

 

[oramgr@oel5u7-x64 11.2.0]$ mkdir cdump

 

[oramgr@oel5u7-x64 11.2.0]$ mkdir diag

 

[oramgr@oel5u7-x64 11.2.0]$ mkdir udump

==========================================

--轉換成11.2.0.3 console oracle_home 及環境變數

[oramgr@oel5u7-x64 11.2.0]$ export PATH=/ebs/prod/db/tech_st/11.2.0/bin:/ebs/prod/db/tech_st/11.2.0/perl/bin:/usr/bin:/usr/sbin:/ebs/prod/db/tech_st/11.2.0/appsutil/jre/bin:/bin:/usr/bin/X11:/usr/local/bin:/usr/kerberos/bin:/usr/local/bin:/bin:/usr/bin:/home/oramgr/bin:/ebs/prod/db/tech_st/11.2.0/OPatch

 

[oramgr@oel5u7-x64 11.2.0]$ export ORACLE_HOME=/ebs/prod/db/tech_st/11.2.0

 

[oramgr@oel5u7-x64 11.2.0]$ export ORA_NLS10=/ebs/prod/db/tech_st/11.2.0/nls/data/9idata

 

[oramgr@oel5u7-x64 11.2.0]$ export TNS_ADMIN=/ebs/prod/db/tech_st/11.2.0/network/admin/PROD_oel5u7-x64

 

[oramgr@oel5u7-x64 11.2.0]$ cd $ORACLE_HOME

 

[oramgr@oel5u7-x64 11.2.0]$ export ORACLE_SID=PROD

 

[oramgr@oel5u7-x64 11.2.0]$ export ORA_NLS10=/ebs/prod/db/tech_st/11.2.0/nls/data

 

--11.2.0.3 環境啟動db,開始進行升級,確保listener是關閉的,不能是作用中

[oramgr@oel5u7-x64 11.2.0]$ cd  /ebs/prod/db/tech_st/11.2.0/rbms/admin

[oramgr@oel5u7-x64 11.2.0]$sqlplus / as sysdba

--進入升級模式

SQL> startup upgrade

--執行升級

SQL> @catupgrd.sql

SQL>

SQL>

SQL> /*****************************************************************************/

SQL> /* Step 10 - SHUTDOWN THE DATABASE..!!!!!

SQL> */

SQL> /*****************************************************************************/

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL>

SQL>

SQL> DOC

DOC>#######################################################################

DOC>#######################################################################

DOC>

DOC>   The above sql script is the final step of the upgrade. Please

DOC>   review any errors in the spool log file. If there are any errors in

DOC>   the spool file, consult the Oracle Database Upgrade Guide for

DOC>   troubleshooting recommendations.

DOC>

DOC>   Next restart for normal operation, and then run utlrp.sql to

DOC>   recompile any invalid application objects.

DOC>

DOC>   If the source database had an older time zone version prior to

DOC>   upgrade, then please run the DBMS_DST package.  DBMS_DST will upgrade

DOC>   TIMESTAMP WITH TIME ZONE data to use the latest time zone file shipped

DOC>   with Oracle.

DOC>

DOC>#######################################################################

DOC>#######################################################################

DOC>#

SQL>

SQL> Rem Set errorlogging off

SQL> SET ERRORLOGGING OFF;

SQL>

SQL> REM END OF CATUPGRD.SQL

SQL>

SQL> REM bug 12337546 - Exit current sqlplus session at end of catupgrd.sql.

SQL> REM                This forces user to start a new sqlplus session in order

SQL> REM                to connect to the upgraded db.

SQL> exit

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

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

=============================================

--完成升級11.2.0.3 db 用正常模式啟動db以完成後續動作執行catmgdidcode.sql   utlrp.sql

[oramgr@oel5u7-x64 11.2.0]$sqlplus / as sysdba

SQL> startup

SQL> @?/md/admin/catmgdidcode.sql

Session altered.

 

User altered.

 

SQL> @?/rdbms/admin/utlrp.sql

 

PL/SQL procedure successfully completed.

 

SQL> select count(*) from obj$ where status in (4,5,6);

  COUNT(*)

----------

       0

SQL> exit

=============================================

--啟動新的監聽器

[oramgr@oel5u7-x64 11.2.0]$ lsnrctl start prod

 

--執行後續動作,複製apps tier 以下兩個檔案到db tier ,並修改所有權為db user後執行

[root@oel5u7-x64 11.2.0]# cp /ebs/prod/apps/apps_st/appl/ad/12.0.0/patch/115/sql/adctxprv.sql  /ebs/prod/db/tech_st/11.2.0/admin/

[root@oel5u7-x64 11.2.0]# cp /ebs/prod/apps/apps_st/appl/admin/adgrants.sql /ebs/prod/db/tech_st/11.2.0/admin/

[root@oel5u7-x64 11.2.0]# chown -R oramgr:dba  /ebs/prod/db/tech_st/11.2.0/admin

[oramgr@oel5u7-x64 11.2.0]$ sqlplus / as sysdba

--執行 adgrants.sql ,參數為APPS,若有錯誤則重新執行輸入APPLSYS

SQL> @?/admin/adgrants.sql

Enter value for 1:

 

APPS

=============================================

--執行 adctxprv.sql設定CTXSYS 權限 ,參數1SYSTEM密碼,參數2CTXSYS

SQL> @?/admin/adctxprv.sql

Connecting to SYSTEM

 

Enter value for 1: manager

 

Connected.

 

Enter value for 2: CTXSYS

=============================================

--設定CTXSYS 參數

SQL> exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');

 

PL/SQL procedure successfully completed.

=============================================

--修正timezomeV15 , opatch 10272702  p10272702_112030_Linux-x86-64.zip

--opatch apply 10272702 (DST V15) to  11.2.0.3 RDBMS HOME    

[oramgr@oel5u7-x64 11.2.0]$unzip  p10272702_112030_Linux-x86-64

[oramgr@oel5u7-x64 11.2.0]$cd 10272702

[oramgr@oel5u7-x64 11.2.0]$opatch apply

--檢視是否產生V15

[oramgr@oel5u7-x64 oracore]# ls -al /ebs/prod/db/tech_st/11.2.0/oracore/zoneinfo/time*15*

-rwxrwxrwx 1 oramgr dba 791476 Oct 14 14:46 /ebs/prod/db/tech_st/11.2.0/oracore/zoneinfo/timezlrg_15.dat

-rwxrwxrwx 1 oramgr dba 344448 Oct 14 14:46 /ebs/prod/db/tech_st/11.2.0/oracore/zoneinfo/timezone_15.dat

 

=============================================

[oramgr@oel5u7-x64 11.2.0]$sqlplus / as sysdba

SQL> SELECT version FROM v$timezone_file;

 

VERSION

———-

10     

 

--V15準備

SQL> exec DBMS_DST.BEGIN_PREPARE(15);

PL/SQL procedure successfully completed.

 

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME;

 

PROPERTY_NAME

——————————

VALUE

——————————————————————————–

DST_PRIMARY_TT_VERSION

10

 

DST_SECONDARY_TT_VERSION

15

 

DST_UPGRADE_STATE

PREPARE

=============================================

--清除  SYS.DST$TRIGGER_TABLE,  sys.dst$affected_tables , sys.dst$error_table

SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

 

Table truncated.

 

SQL> TRUNCATE TABLE sys.dst$affected_tables;

 

Table truncated.

 

SQL> TRUNCATE TABLE sys.dst$error_table;

 

Table truncated.

=============================================

--執行 AFFECTED

SQL> BEGIN

DBMS_DST.FIND_AFFECTED_TABLES

(affected_tables => 'sys.dst$affected_tables',

log_errors => TRUE,

log_errors_table => 'sys.dst$error_table');

END;  2    3    4    5    6

  7  /

PL/SQL procedure successfully completed.

============================================

--確認 sys.dst$affected_tables沒資料後結束dst 準備 ,進行升級timezone

SQL> SELECT * FROM sys.dst$affected_tables;

 

no rows selected

 

SQL> EXEC DBMS_DST.END_PREPARE;

 

PL/SQL procedure successfully completed.

SQL> shutdown immediate;

 

============================================

--啟動進入 db upgrade ,升級timezone

SQL> startup upgrade;

--執行 DST upgrade.

SQL> EXEC DBMS_DST.BEGIN_UPGRADE(15);

PL/SQL procedure successfully completed.

--檢查 primary DST TZ version 是否為v15

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value

FROM DATABASE_PROPERTIES

WHERE PROPERTY_NAME LIKE 'DST_%'

ORDER BY PROPERTY_NAME; 

 

PROPERTY_NAME

——————————

VALUE

——————————————————————————–

DST_PRIMARY_TT_VERSION

15

 

DST_SECONDARY_TT_VERSION

10

============================================

SQL>shutdown immediate;

SQL>startup

SQL>set serveroutput on

SQL>VAR numfail number

SQL>BEGIN

DBMS_DST.UPGRADE_DATABASE(:numfail,parallel => TRUE,log_errors => TRUE,log_errors_table => 'SYS.DST$ERROR_TABLE',log_triggers_table => 'SYS.DST$TRIGGER_TABLE',error_on_overlap_time => FALSE,error_on_nonexisting_time => FALSE); DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);

END;

SQL>/

SQL> VAR fail number

SQL>BEGIN

DBMS_DST.END_UPGRADE(:fail); DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);

END;

SQL> /

SQL> select * from v$timezone_file;

FILENAME                VERSION
-------------------- ----------
timezlrg_15.dat              15

SQL>

 

SQL> shutdown immediate

--用新的pfile正常啟動db , pfile='/ebs/prod/db/tech_st/11.2.0/dbs/initPROD.ora'

SQL> startup pfile='/ebs/prod/db/tech_st/11.2.0/dbs/initPROD.ora'

 

--檢查 timezone是否為v15

SQL> SELECT version FROM v$timezone_file;

VERSION

———-

15

--建立新的spfile 後用spfile模式啟動db

SQL> create spfile from pfile;

 

File created.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

 

Total System Global Area 1068937216 bytes

Fixed Size                  2235208 bytes

Variable Size             427820216 bytes

Database Buffers          624951296 bytes

Redo Buffers               13930496 bytes

Database mounted.

Database opened.

SQL>exit

=============================================

 

 

--開新console ,apps user ap tier env 後進入

--驗證Workflow ruleset

[applmgr@oel5u7-x64 sql]$ cd $FND_TOP/patch/115/sql/

[applmgr@oel5u7-x64 sql]$ ls -al wfaqupfix.sql

-rwxr-xr-x 1 applmgr dba 4631 Mar 31  2009 wfaqupfix.sql

[applmgr@oel5u7-x64 sql]$ sqlplus apps/apps

 

SQL*Plus: Release 10.1.0.5.0 - Production on Sat Oct 29 10:09:45 2011

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

Connected to:

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

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

--參數1:APPLSYS ,參數2:APPS

SQL> @wfaqupfix.sql

Enter value for 1: APPLSYS

Enter value for 2: APPS

PL/SQL procedure successfully completed.

Commit complete.

 

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

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

=============================================

如要修改db port, host, SID ,dbname 則要清除設定如下,不修改的畫此步驟可跳過

[applmgr@oel5u7-x64 sql]$ sqlplus apps/apps

SQL> exec fnd_conc_clone.setup_clean;

SQL> exit

=============================================

--建立 appsutil並搬到新的11.2.0.3 home下解壓

[applmgr@oel5u7-x64 sql]$ perl $AD_TOP/bin/admkappsutil.pl

Starting the generation of appsutil.zip

Log file located at /ebs/prod/inst/apps/PROD_oel5u7-x64/admin/log/MakeAppsUtil_10291013.log

output located at /ebs/prod/inst/apps/PROD_oel5u7-x64/admin/out/appsutil.zip

MakeAppsUtil completed successfully.

 

[root@oel5u7-x64 admin]# cp /ebs/prod/inst/apps/PROD_oel5u7-x64/admin/out/appsutil.zip /ebs/prod/db/tech_st/11.2.0/

[root@oel5u7-x64 admin]# chown oramgr:dba /ebs/prod/db/tech_st/11.2.0/appsutil.zip

--轉換成11.2.0.3 console oracle_home 及環境變數

[oramgr@oel5u7-x64 11.2.0]$ cd $ORACLE_HOME

[oramgr@oel5u7-x64 11.2.0]$ unzip -o appsutil.zip

=============================================

--進行11.2.0.3 db node autoconfig

--建立Context file

[oramgr@oel5u7-x64 11.2.0]$ cd appsutil/bin/

[oramgr@oel5u7-x64 bin]$ perl adbldxml.pl

 

Starting context file generation for db tier..

Using JVM from /ebs/prod/db/tech_st/11.2.0/jdk/jre/bin/java to execute java programs..

APPS Password: apps

 

The log file for this adbldxml session is located at:

/ebs/prod/db/tech_st/11.2.0/appsutil/log/adbldxml_10291437.log

UnsatisfiedLinkError exception loading native library: njni11

java.lang.UnsatisfiedLinkError: no njni11 in java.library.path

 

Could not Connect to the Database with the above parameters, Please answer the Questions below

 

 

Enter Hostname of Database server: oel5u7-x64

 

Enter Port of Database server: 1521

 

Enter SID of Database server: PROD

 

Enter the value for Display Variable: oel5u7-x64:0.0

 

The context file has been created at:

/ebs/prod/db/tech_st/11.2.0/appsutil/PROD_oel5u7-x64.xml

--指定context file執行autoconfig

[oramgr@oel5u7-x64 bin]$ ./adconfig.sh contextfile=/ebs/prod/db/tech_st/11.2.0/appsutil/PROD_oel5u7-x64.xml

Enter the APPS user password:

The log file for this session is located at: /ebs/prod/db/tech_st/11.2.0/appsutil/log/PROD_oel5u7-x64/10291444/adconfig.log

 

AutoConfig is configuring the Database environment...

 

AutoConfig will consider the custom templates if present.

        Using ORACLE_HOME location : /ebs/prod/db/tech_st/11.2.0

        Classpath                   : :/ebs/prod/db/tech_st/11.2.0/jdbc/lib/ojdbc5.jar:/ebs/prod/db/tech_st/11.2.0/appsutil/java/xmlparserv2.jar:/ebs/prod/db/tech_st/11.2.0/appsutil/java:/ebs/prod/db/tech_st/11.2.0/jlib/netcfg.jar:/ebs/prod/db/tech_st/11.2.0/jlib/ldapjclnt11.jar

 

        Using Context file          : /ebs/prod/db/tech_st/11.2.0/appsutil/PROD_oel5u7-x64.xml

Context Value Management will now update the Context file

 

        Updating Context file...COMPLETED

 

        Attempting upload of Context file and templates to database...COMPLETED

 

Updating rdbms version in Context file to db112

Updating rdbms type in Context file to 64 bits

Configuring templates from ORACLE_HOME ...

 

AutoConfig completed successfully.

 

=============================================

--修改db user source env 對應到11.2.0 新路徑env

[oramgr@oel5u7-x64 11.2.0]vi ~/.bash_profile

--. /ebs/prod/db/tech_st/11.1.0/PROD_oel5u7-x64.env改成 . /ebs/prod/db/tech_st/11.2.0/PROD_oel5u7-x64.env

 

--複製app tier $APPL_TOP/admin/adstats.sql db tier $ORACLE_HOME/admin ,並修改擁有者為db user

[root@oel5u7-x64 oracore]# cp /ebs/prod/apps/apps_st/appl/admin/adstats.sql /ebs/prod/db/tech_st/11.2.0/admin/

[root@oel5u7-x64 oracore]# chown oramgr:dba /ebs/prod/db/tech_st/11.2.0/admin/adstats.sql

--切換到11.2.0.3 user ,

[oramgr@oel5u7-x64 bin]$ cd /ebs/prod/db/tech_st/11.2.0/admin/

[oramgr@oel5u7-x64 admin]$ ls

adctxprv.sql  adgrants.sql  adstats.sql  PROD_oel5u7-x64

[oramgr@oel5u7-x64 admin]$ sqlplus / as sysdba

--限制session

SQL>  alter system enable restricted session;

--執行adstats.sql SYS statistics

SQL> @adstats.sql

Connected.

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

--- adstats.sql started at 2011-10-29 14:53:19 ---

Checking for the DB version and collecting statistics ...

SQL> alter system disable restricted session;

 

System altered.

 

SQL> exit

 

 

=============================================

--連接app console , 執行adadmin 重建 grants and synonyms

[applmgr@oel5u7-x64 sql]$ adadmin

4.    Maintain Applications Database Entities menu

  2.    Re-create grants and synonyms for APPS schema

5.    Change Maintenance Mode

   2.    Disable Maintenance Mode

--啟動ap  

[applmgr@oel5u7-x64 ~]$ adstrtal.sh apps/apps

--登入ebs 選擇"System Administrator" responsibility ,執行request .

請求選擇:Workflow Directory Services User/Role Validation

參數:

BatchSize = 10000

 

fix Dangling user = Yes

 

Add missing user/role assignments = Yes

 

Update WHO columns in WF tables = No

 

---ebs 11.2.0.3 升級完成

 

 

全站熱搜

Nolem 發表在 痞客邦 留言(0) 人氣()