# Oracle Database # spfile v.s. pfile 1. pfile 純文字檔案, client端參數檔案;不能動態修改,可以用普通的編輯器修改,修改之後需要重啟。pfile可能會導致伺服器啟動不一致,因為可以在客戶端啟動。 2. spfile 二進制檔案,伺服器端參數檔案,有了spfile,oracle可以實現動態參數在線修改,部分參數修改之後無需重啟。但是,因為是二進制檔案,所以不能用普通的編輯器修改,要用alter命令從sql裡面來修改。spfile保證伺服器每次的啟動都是一致的。 spfile 與 pfile 設定檔可以互相轉換,只有 spfile 而沒有 pfile 文件時 `create pfile='位置+名字' from spfile;` 例如: ```SQL create pfile='E:\ORACLE\PRODUCT\10.2.0\DB_1\DBS\spfileorcl_bak.ora' from spfile; ``` 同理,只有pfile而沒有spfile時,可以透過: `create spfile='位置+名字' from pfile;` ##### 檢查系統是以pfile還是spfile啟動 > 如果 isspecified 裡有 true,表明用 spfile 啟動,如果全為 false,則表明用pfile啟動 ```SQL select isspecified,count(*) from v$spparameter group by isspecified; ``` ##### 檢查 spfile 的檔案路徑 ``` show parameters spfile ``` ##### 從 spfile 取得 pfile ``` Create pfile='d:pfileSID.ora' from spfile='spfile_location'; ``` ##### 從 pfile 取得 spfile ``` Create spfile='spfile_location' from pfile='Your_pfile_location'; ``` ##### Backup current spfile exp: spfile = spfilewin.ora ```SQL CREATE pfile='/opt/oracle/product/10.2.0/db_1/dbs/pfile.tmp' FROM spfile='/opt/oracle/product/10.2.0/db_1/dbs/spfilewin.ora' CREATE spfile='/opt/oracle/product/10.2.0/db_1/dbs/spfilewin.ora.20140313' FROM pfile='/opt/oracle/product/10.2.0/db_1/dbs/pfile.tmp' ``` ##### 使用其他 spfile/pfile 啟動資料庫 ``` startup nomount startup pfile='my_pfile' startup spfile='my_spfile' ``` # Patch Set Update (PSU) 安裝 PSU 10.2.0.5.4 (p12419392\_10205\_Linux-x86\_64.zip) 教學參考:Zip 檔解壓後 Readme.html #### 事前準備 - Oracle DB 必須已經安裝完成 patch 10.2.0.5。 - 下載需要的檔案 - p6880880\_102000\_LINUX.zip (OPatch\_upgrade) - p12419392\_10205\_Linux-x86-64.zip (PSU 10.2.0.5.4) 如何檢查目前 DB 的版本訊息 方法一: ``` > su - oracle > cd $ORACLE_HOME/OPatch > ./opatch lsinventory Invoking OPatch 10.2.0.4.9 Oracle Interim Patch Installer version 10.2.0.4.9 Copyright (c) 2009, Oracle Corporation. All rights reserved. Oracle Home : /opt/oracle/product/10.2.0/db_1 Central Inventory : /opt/oracle/oraInventory from : /etc/oraInst.loc OPatch version : 10.2.0.4.9 OUI version : 10.2.0.5.0 OUI location : /opt/oracle/product/10.2.0/db_1/oui Log file location : /opt/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2014-06-11_10-55-50AM.log Patch history file: /opt/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt Lsinventory Output file location : /opt/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2014-06-11_10-55-50AM.txt -------------------------------------------------------------------------------- Installed Top-level Products (3): Oracle Database 10g 10.2.0.1.0 Oracle Database 10g Release 2 Patch Set 4 10.2.0.5.0 There are 3 products installed in this Oracle Home. There are no Interim patches installed in this Oracle Home. -------------------------------------------------------------------------------- OPatch succeeded. ``` 方法二: ``` $ ./opatch lsinventory -bugs_fixed | grep -i -E 'DATABASE PSU|DATABASE PATCH SET UPDATE' 9952230 12419392 Wed Jun 11 11:45:57 CST 2014 DATABASE PSU 10.2.0.5.1 (INCLUDES CPUOCT2010) 10248542 12419392 Wed Jun 11 11:45:57 CST 2014 DATABASE PSU 10.2.0.5.2 (INCLUDES CPUJAN2011) 11724962 12419392 Wed Jun 11 11:45:57 CST 2014 DATABASE PSU 10.2.0.5.3 (INCLUDES CPUAPR2011) 12419392 12419392 Wed Jun 11 11:45:57 CST 2014 DATABASE PSU 10.2.0.5.4 (INCLUDES CPUJUL2011) ``` 方法三: ``` > sqlplus / as sysdba SQL> col comments for a20 SQL> col version for a20 SQL> col bundle_series for a20 SQL> select comments, version, bundle_series from sys.registry$history where bundle_series = 'PSU' order by action_time; COMMENTS VERSION BUNDLE_SERIES -------------------- -------------------- -------------------- PSU 10.2.0.5.4 10.2.0.5 PSU ``` #### 升級 OPatch 至 12.0.5.1 要安裝 PSU 之前,Oracle 的 OPatch 版本必須是 10.2.0.5 以上。 檢查 OPatch 版本的指令有二: ``` > su - oracle > cd $ORACLE_HOME/OPatch > ./opatch lsinventory 或 > ./opatch version ``` 升級 OPatch ``` > su - oracle > mkdir p6880880_102000_LINUX/ > unzip "p6880880_102000_LINUX(OPatch_upgrade).zip" -d p6880880_102000_LINUX > mv $ORACLE_HOME/OPatch/ $ORACLE_HOME/OPatch_old/ > cp -a p6880880_102000_LINUX/OPatch/ $ORACLE_HOME > cd $ORACLE_HOME/OPatch/ > ./opatch version ``` > NOTE: > > 上述步驟請改用 root 執行,避免有些檔案因為權限問題會有遺漏。 > > > cp -a /mnt/tpeitpfs02-share/ora10g\_install/patch\_10205/p6880880\_102000\_LINUX/OPatch /opt/oracle/product/10.2.0/db\_1 > > chown -R oracle.dba /opt/oracle/product/10.2.0/db\_1/OPatch #### 安裝 PSU > NOTE: 不需要桌面環境 安裝前:檢查 PSU patch 是否會與目前的 DB 有所衝突 ``` > mkdir p12419392_10205_Linux-x86-64 > unzip "p12419392_10205_Linux-x86-64(PSU_patch).zip" -d p12419392_10205_Linux-x86-64 > cd p12419392_10205_Linux-x86-64/ > $ORACLE_HOME/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -phBaseDir ./12419392 Invoking OPatch 10.2.0.5.1 Oracle Interim Patch Installer version 10.2.0.5.1 Copyright (c) 2010, Oracle Corporation. All rights reserved. PREREQ session Oracle Home : /opt/oracle/product/10.2.0/db_1 Central Inventory : /opt/oracle/oraInventory from : /etc/oraInst.loc OPatch version : 10.2.0.5.1 OUI version : 10.2.0.5.0 OUI location : /opt/oracle/product/10.2.0/db_1/oui Log file location : /opt/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2014-06-11_11-20-16AM.log Patch history file: /opt/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt Invoking prereq "checkconflictagainstohwithdetail" Prereq "checkConflictAgainstOHWithDetail" passed. OPatch succeeded. ``` 安裝開始 > NOTE:執行前必須將所有的 DB Instances 與 Listeners 停止。 ``` Shutdown All Instances and Listeners > lsnrctl stop > sqlplus /nolog SQL> conn / as sysdba SQL> shutdown immediate > cd p12419392_10205_Linux-x86-64/12419392 > $ORACLE_HOME/OPatch/opatch apply ``` ##### 狀況一: 現有資料庫的升級 > Enter email > Enter password:<Enter> > > \*\* 耐心等候 \*\* > > Unable to establish a network connection to Oracle. If your systems require a > proxy server for outbound Internet connections, enter the proxy server details > in this format: > \[<proxy-user>@\]<proxy-host>\[:<proxy-port>\] > If you want to remain uninformed of critical security issues in your > configuration, enter NONE > Proxy specification: > An invalid proxy specification was given. Please re-enter the proxy information. > > Unable to establish a network connection to Oracle. If your systems require a > proxy server for outbound Internet connections, enter the proxy server details > in this format: > \[<proxy-user>@\]<proxy-host>\[:<proxy-port>\] > If you want to remain uninformed of critical security issues in your > configuration, enter NONE > Proxy specification: NONE <輸入NONE> > > > OPatch detected non-cluster Oracle Home from the inventory and will patch the local system only. > > > Please shutdown Oracle instances running out of this ORACLE\_HOME on the local system. > (Oracle Home = '/opt/oracle/product/10.2.0/db\_1') > > > Is the local system ready for patching? \[y|n\] > y <輸入 y> > > ... > ... > Patching component oracle.sysman.bsln, 10.2.0.5.0... > Patching component oracle.sysman.repository.core, 10.2.0.5.0a... > Patching component oracle.sysman.console.db, 10.2.0.5.0... > Patching component oracle.xdk.rsf, 10.2.0.5.0... > Patching component oracle.precomp.common, 10.2.0.5.0... > > Patching component oracle.rdbms.rman, 10.2.0.5.0... > Running make for target client\_sharedlib > Running make for target ioracle > Running make for target iwrap > Running make for target client\_sharedlib > Running make for target proc > Running make for target irman > ApplySession adding interim patch '12419392' to inventory > > Verifying the update... > Inventory check OK: Patch ID 12419392 is registered in Oracle Home inventory with proper meta-data. > Files check OK: Files from Patch ID 12419392 are present in Oracle Home. > > The local system has been patched and can be restarted. > > > OPatch succeeded. 安裝後:套用新 Patch ``` > cd $ORACLE_HOME/rdbms/admin > sqlplus /nolog SQL> connect / as sysdba SQL> startup SQL> @catbundle.sql psu apply SQL> @utlrp.sql SQL> quit ``` > TIP: > > 執行 @catbundle.sql .... 時,最後顯示訊息如下: > > Updating registry... > > 1 row created. > > > Commit complete. > > Check the following log file for errors: > /opt/oracle/product/10.2.0/db\_1/cfgtoollogs/catbundlez/catbundle\_PSU\_WIN\_APPLY\_2014Jun11\_11\_55\_31.log 最後確認 SQL : ```sql select owner,count(1) from dba_objects where status='INVALID' group by owner; ``` SQL: ```sql select * from registry$history; ``` ##### 狀況二: 全新安裝,無任何資料庫 > Email address/User Name: 按 Enter > Do you wish to remain uninformed of security issues (\[Y\]es, \[N\]o) \[N\] 輸入 Y > Is the local system ready for patching? 輸入 y > > Verifying the update... > Inventory check OK: Patch ID 12419392 is registered in Oracle Home inventory with proper meta-data. > Files check OK: Files from Patch ID 12419392 are present in Oracle Home. > > The local system has been patched and can be restarted. > > > OPatch succeeded. ##### 完成後檢視 ``` > cd $ORACLE_HOME/OPatch > ./opatch lsinventory Invoking OPatch 10.2.0.5.1 Oracle Interim Patch Installer version 10.2.0.5.1 Copyright (c) 2010, Oracle Corporation. All rights reserved. Oracle Home : /opt/oracle/product/10.2.0/db_1 Central Inventory : /opt/oracle/oraInventory from : /etc/oraInst.loc OPatch version : 10.2.0.5.1 OUI version : 10.2.0.5.0 OUI location : /opt/oracle/product/10.2.0/db_1/oui Log file location : /opt/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch2014-06-11_13-05-53PM.log Patch history file: /opt/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/opatch_history.txt Lsinventory Output file location : /opt/oracle/product/10.2.0/db_1/cfgtoollogs/opatch/lsinv/lsinventory2014-06-11_13-05-53PM.txt -------------------------------------------------------------------------------- Installed Top-level Products (3): Oracle Client 10.2.0.1.0 Oracle Database 10g 10.2.0.1.0 Oracle Database 10g Release 2 Patch Set 4 10.2.0.5.0 There are 3 products installed in this Oracle Home. Interim patches (1) : Patch 12419392 : applied on Wed Jun 11 11:45:57 CST 2014 Unique Patch ID: 13794201 Created on 29 May 2011, 22:29:59 hrs PST8PDT Bugs fixed: 6402302, 10269717, 10327190, 8865718, 10017048, 9024850, 8394351, 8546356 9360157, 9770451, 9020537, 9772888, 8664189, 10091698, 12551710, 7519406 10132870, 8771916, 9109487, 10173237, 10068982, 8350262, 11792865 11724962, 11725006, 9184754, 8544696, 9320130, 7026523, 8277300, 9726739 8412426, 12419392, 6651220, 9150282, 9659614, 9949948, 10327179, 8882576 7612454, 9711859, 9714832, 10248542, 9952230, 9469117, 9952270, 8660422 10324526, 12419258, 9713537, 10010310, 9390484, 9963497, 12551700 12551701, 10249537, 12551702, 12551703, 8211733, 12551704, 9548269 12551705, 12551706, 9337325, 12551707, 7602341, 12551708, 9308296 10157402, 11737047 -------------------------------------------------------------------------------- OPatch succeeded. ``` # Web sites ##### Oracle Official - Forum: [https://forums.oracle.com/ords/apexds/domain/dev-community](https://forums.oracle.com/ords/apexds/domain/dev-community)