手动升级oracle,ORACLE 10.2.0.1手动升级到10.2.0.4
ORACLE 10.2.0.1手動升級到10.2.0.4
1、解壓zip包以及查看目錄權(quán)限
p6810189_10204_Linux-x86-64.zip
oracle@single-10g:/u01>ll
total 1950552
-rw-r—r—. 1 oracle oinstall 801603584 Jan 9 10:35 10201_database_linux_x86_64.cpio
drwxr-xr-x. 3 oracle oinstall 4096 Apr 2 09:48 backup
drwxr-xr-x. 6 oracle oinstall 4096 Oct 23 2005 database
drwxr-xr-x. 5 oracle oinstall 4096 Mar 12 2008 Disk1
drwxr-xr-x. 3 oracle oinstall 4096 Mar 30 15:03 marvin
drwxrwx—-. 6 oracle oinstall 4096 Jan 9 12:10 oracle
-rw-r—r—. 1 root root 1195551830 Apr 2 09:53 p6810189_10204_Linux-x86-64.zip
-rw-r—r—. 1 oracle oinstall 902 Mar 26 11:21 pfile.ora
-rwxr-xr-x. 1 oracle oinstall 171882 Mar 17 2008 README.html
drwxr-xr-x. 3 oracle oinstall 4096 Mar 30 16:27 scripts
2、查看升級前版本信息
版本信息
SQL> select * from v$version;
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE 10.2.0.1.0
Production
TNS for Linux: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Productionhttps://www.cndba.cn/cndba/Marvinn/article/2704
調(diào)整下內(nèi)存參數(shù)(share pool和java pool 加快升級速度)值大小根據(jù)情況確定,也不用太大
SQL> show parameter sga;
NAME TYPE VALUEhttps://www.cndba.cn/cndba/Marvinn/article/2704
lock_sga boolean FALSE
pre_page_sga boolean FALSE
sga_max_size big integer 492M
sga_target big integer 492M
SQL> select name,bytes/1024/1024 from v$sgainfo;
[java] view plain copy
NAME BYTES/1024/1024
Fixed SGA Size 1.1638031
Redo Buffers 2.8359375
Buffer Cache Size 328
Shared Pool Size 124
Large Pool Size 12
Java Pool Size 24
Streams Pool Size 0
Granule Size 4
Maximum SGA Size 492
Startup overhead in Shared Pool 36
Free SGA Memory Available 0
11 rows selected.
SQL>alter system set shared_pool_size = 150m;
System altered.
SQL> alter system set java_pool_size =100m;
System altered.
3、停止所有數(shù)據(jù)庫服務(wù)以及監(jiān)聽
lsnrctl stop
sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Mon Apr 2 10:15:18 2018
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
SQL> shuttdown immediate;
SP2-0734: unknown command beginning “shuttdown …” - rest of line ignored.
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
4、冷備份所有數(shù)據(jù)庫
備份數(shù)據(jù)文件、控制文件以及日志文件
這里可以直接物理備份，采用系統(tǒng)cp命令
oracle@single-10g:/u01/oracle/oradata/ORCL>cp controlfile ORCLBAKUP/
cp: omitting directory `controlfile’
oracle@single-10g:/u01/oracle/oradata/ORCL>cp -r controlfile ORCLBAKUP/
oracle@single-10g:/u01/oracle/oradata/ORCL>ls
controlfile datafile onlinelog ORCLBAKUP
oracle@single-10g:/u01/oracle/oradata/ORCL>cp -r datafile/ ORCLBAKUP/
oracle@single-10g:/u01/oracle/oradata/ORCL>cp -r onlinelog/ ORCLBAKUP/
oracle@single-10g:/u01/oracle/oradata/ORCL/ORCLBAKUP>ll
total 12
drwxr-x—-. 2 oracle oinstall 4096 Apr 2 10:29 controlfile
drwxr-x—-. 2 oracle oinstall 4096 Apr 2 10:30 datafile
drwxr-x—-. 2 oracle oinstall 4096 Apr 2 10:30 onlinelog
或者exp/imp以及數(shù)據(jù)泵
或者重啟庫到mount狀態(tài)，用RMAN全備
當(dāng)前環(huán)境有兩個庫，測試我只備了一個庫
備份spfile以及口令文件
oracle@single-10g:/u01/oracle/oradata/ORCL/ORCLBAKUP>cd $ORACLE_HOME/dbs
oracle@single-10g:/u01/oracle/12.2.0/dbs>mkdir backup
oracle@single-10g:/u01/oracle/12.2.0/dbs>cp spfileorcl.ora orapworcl backup/
oracle@single-10g:/u01/oracle/12.2.0/dbs>
5、配置并運(yùn)行安裝腳本
采用靜默安裝
oracle@single-10g:/u01/Disk1/response>cp -a patchset.rsp patchset.rsp.bak (-a 表示復(fù)制保留原來文件同樣的所有屬性)
oracle@single-10g:/u01/Disk1/response>ls
patchset.rsp patchset.rsp.bak
編輯文件，修改如下：(未提及的不變動)
安裝用戶組為oinstall
UNIX_GROUP_NAME=oinstall
升級patch包的路徑(以自己機(jī)子里面的路徑為準(zhǔn))
FROM_LOCATION=/u01/Disk1/stage/products.xml
環(huán)境變量中ORACLE_HOME
ORACLE_HOME=/opt/oracle/app/oracle/product/10.2/db_1
ORACLE_HOME的HOME NANME，該值位于oraInventory目錄下的ContentsXML
ORACLE_HOME_NAME=OraDb10g_home1
oracle@single-10g:/u01/oracle/oraInventory/ContentsXML>cat inventory.xml
10.2.0.1.0
2.1.0.6.0
https://www.cndba.cn/cndba/Marvinn/article/2704
安裝升級軟件并指定patchset.rsp使用用這個文件
oracle@single-10g:/u01/Disk1>./runInstaller -silent -responseFile /u01/Disk1/response/patchset.rsp
Starting Oracle Universal Installer…
Checking installer requirements…
Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, redhat-4, redhat-5, UnitedLinux-1.0, asianux-1, asianux-2 or asianux-3
Failed <<<
Exiting Oracle Universal Installer, log for this session can be found at /u01/oracle/oraInventory/logs/installActions2018-04-02_10-51-17AM.log
解決方案:
1、修改Linux的發(fā)行標(biāo)記
如在redhat-5上安裝oracle的時候，需要將文件 ‘/etc/redhat-release’的內(nèi)容由
Red Hat Enterprise Linux Server release 6.6 (Santiago)
修改為
Red Hat Enterprise Linux Server release 4 (Tikanga)
2、runInstaller的時候加上-ignoreSysPreReqs參數(shù)，如：
./runInstaller -ignoreSysPreReqs
不過不推薦這種方式，因?yàn)檫@可能會錯過對操作系統(tǒng)內(nèi)核參數(shù)以及oracle必需的包的檢測
3、修改補(bǔ)丁包中安裝文件oraparam.ini(比較推薦)
oracle@single-10g:/u01/Disk1/l>cd Disk1
oracle@single-10g:/u01/Disk1/install>vi oraparam.ini
找到改行文件，添加自身所在系統(tǒng)發(fā)行標(biāo)記
[Certified Versions]
Linux=redhat-3,SuSE-9,SuSE-10,redhat-4,redhat-5,redhat-6.6,UnitedLinux-1.0,asianux-1,asianux-2,asianux-3
重新安裝即可…
oracle@single-10g:/u01/Disk1>./runInstaller -silent -responseFile /u01/Disk1/response/patchset.rsp
Starting Oracle Universal Installer…
Checking installer requirements…
Checking operating system version: must be redhat-3, SuSE-9, SuSE-10, redhat-4, redhat-5, redhat-6.6, UnitedLinux-1.0, asianux-1, asianux-2 or asianux-3
Passed
Root script to run
/u01/oracle/12.2.0/root.sh
To execute the configuration scripts:
1. Open a terminal window
2. Log in as "root"
3. Run the scripts
The installation of Oracle Database 10g Release 2 Patch Set 3 was successful.
Please check ‘/u01/oracle/oraInventory/logs/silentInstall2018-04-02_10-56-48AM.log’ for more details.
運(yùn)行root.sh腳本(一路按y，覆蓋原來文件)
[root@single-10g ~]# /u01/oracle/12.2.0/root.sh
Running Oracle10 root.sh script…
The following environment variables are set as:
ORACLE_OWNER= oracle
ORACLE_HOME= /u01/oracle/12.2.0
Enter the full pathname of the local bin directory: [/usr/local/bin]:
The file “dbhome” already exists in /usr/local/bin. Overwrite it? (y/n)
Copying dbhome to /usr/local/bin …
The file “oraenv” already exists in /usr/local/bin. Overwrite it? (y/n)
Copying oraenv to /usr/local/bin …
The file “coraenv” already exists in /usr/local/bin. Overwrite it? (y/n)
Copying coraenv to /usr/local/bin …
Entries will be added to the /etc/oratab file as needed by
Database Configuration Assistant when a database is created
Finished running generic part of root.sh script.
Now product-specific root actions will be performed.
6、手工升級數(shù)據(jù)庫(以O(shè)racle身份)
驗(yàn)證環(huán)境變量
? 當(dāng)前數(shù)據(jù)庫軟件中存在兩個數(shù)據(jù)庫，所以都需要啟動到升級模式按以下方式跑utlu102i.sql以及catupgrd.sql升級腳本，升級數(shù)據(jù)字典.以下就跑一個數(shù)據(jù)庫，另一個數(shù)據(jù)庫第6跟第7步驟過程書寫忽略(可以同時運(yùn)行跑)https://www.cndba.cn/cndba/Marvinn/article/2704
由于當(dāng)前存在兩個庫，所以都需要升級下數(shù)據(jù)字典
oracle@single-10g:/home/oracle>echo $ORACLE_HOME
/u01/oracle/12.2.0/
oracle@single-10g:/home/oracle>echo $ORACLE_SID
orcl
oracle@single-10g:/home/oracle>echo $ORACLE_BASE
/u01/oracle
oracle@single-10g:/home/oracle>sqlplus / as sysdba
https://www.cndba.cn/cndba/Marvinn/article/2704
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Apr 2 11:10:21 2018
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to an idle instance.
啟動到升級模式
SQL> startup upgrade;
ORACLE instance started.
Total System Global Area 520093696 bytes
Fixed Size 2085160 bytes
Variable Size 146804440 bytes
Database Buffers 364904448 bytes
Redo Buffers 6299648 bytes
Database mounted.
Database opened.
設(shè)置升級日志
SQL>SPOOL /u01/Disk1/upgrade_info.log
運(yùn)行升級前信息工具
SQL> @?/rdbms/admin/utlu102i.sql
Oracle Database 10.2 Upgrade Information Utility 04-02-2018 11:13:35
.
Database:
—> name: ORCL
—> version: 10.2.0.1.0
—> compatible: 10.2.0.1.0
—> blocksize: 8192
.
Tablespaces: [make adjustments in the current environment]
—> SYSTEM tablespace is adequate for the upgrade.
…. minimum required size: 500 MB
…. AUTOEXTEND additional space required: 10 MB
—> UNDOTBS1 tablespace is adequate for the upgrade.
…. minimum required size: 400 MB
…. AUTOEXTEND additional space required: 130 MB
—> SYSAUX tablespace is adequate for the upgrade.
…. minimum required size: 367 MB
—> TEMP tablespace is adequate for the upgrade.
…. minimum required size: 58 MB
…. AUTOEXTEND additional space required: 38 MB
.
Update Parameters: [Update Oracle Database 10.2 init.ora or spfile]
— No update parameter changes are required.
.
Renamed Parameters: [Update Oracle Database 10.2 init.ora or spfile]
— No renamed parameters found. No changes are required.
.
Obsolete/Deprecated Parameters: [Update Oracle Database 10.2 init.ora or spfile]
— No obsolete parameters found. No changes are required
.
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
—> Oracle Java Packages [upgrade] VALID
—> Oracle Text [upgrade] VALID
—> Oracle XML Database [upgrade] VALID
—> Oracle Workspace Manager [upgrade] VALID
—> Oracle Data Mining [upgrade] VALID
—> OLAP Analytic Workspace [upgrade] VALID
—> OLAP Catalog [upgrade] VALID
—> Oracle OLAP API [upgrade] VALID
—> Oracle interMedia [upgrade] VALID
—> Spatial [upgrade] VALID
—> Expression Filter [upgrade] VALID
—> EM Repository [upgrade] VALID
—> Rule Manager [upgrade] VALID
.
Miscellaneous Warnings
WARNING: —> Database contains INVALID objects prior to upgrade.
…. USER PUBLIC has 19 INVALID objects.
.
https://www.cndba.cn/cndba/Marvinn/article/2704
PL/SQL procedure successfully completed.
關(guān)閉日志輸出
SQL> SPOOL OFF
再運(yùn)行catupgrd.sql腳本
SQL>SPOOL /u01/Disk1/catinfo.log
SQL> @?/rdbms/admin/catupgrd.sql #時間較長
升級到最后輸出內(nèi)容
Total Upgrade Time: 00:38:49
DOC>#######################################################################
DOC>#######################################################################
DOC>
DOC> The above PL/SQL lists the SERVER components in the upgraded
DOC> database, along with their current version and status.
DOC>
DOC> Please review the status and version columns and look for
DOC> any errors in the spool log file. If there are errors in the spool
DOC> file, or any components are not VALID or not the current version,
DOC> consult the Oracle Database Upgrade Guide for troubleshooting
DOC> recommendations.
DOC>
DOC> Next shutdown immediate, restart for normal operation, and then
DOC> run utlrp.sql to recompile any invalid application objects.
DOC>
DOC>#######################################################################
DOC>#######################################################################
DOC>#
SQL> SQL> https://www.cndba.cn/cndba/Marvinn/article/2704
關(guān)閉日志輸出
SQL> SPOOL OFF；
··························································································································································
若運(yùn)行腳本有報如下錯誤跳出執(zhí)行(若無可忽略)，可按以下解決…
報錯1：
No errors.
SELECT version_script AS file_name FROM DUAL
*
ERROR at line 1:
ORA-20000: Upgrade re-run not supported from version
ORA-06512: at “SYS.VERSION_SCRIPT”, line 45
解決方法：
通過以下SQL更新到你要升級到的版本：10.2.0.1升級到10.2.0.4執(zhí)行catupgrd.sql報錯——BUG
update registry$ set prv_version=’10.2.0.4.0’ where cid=’CATPROC’;
quit退出會話，再conn /as sysdba連接到數(shù)據(jù)庫
再次運(yùn)行， 不會報錯
報錯2：
ERROR at line 1:
ORA-03113: end-of-file on communication channel
SQL>
SQL> alter system flush shared_pool;
ERROR:
ORA-03114: not connected to ORACLE
報錯終止執(zhí)行解決，重新執(zhí)行catupgrd腳本
oracle@single-10g:/home/oracle>sqlplus / as sysdba
SQL>update registry$ set prv_version=’10.2.0.4.0’ where cid=’CATPROC’;
SQL> alter system flush shared_pool;
SQL>exit;
oracle@single-10g:/home/oracle> sqlplus / as sysdba
SQL>SPOOL /u01/Disk1/catinfo.log
SQL>@?/rdbms/admin/catupgrd.sql #時間較長，直到出現(xiàn)sql> –升級腳本[刷新數(shù)據(jù)字典] #版本要是最新版本10.2.0.4.0，status要是valid
SQL>SPOOL OFF；
·········································································································································
檢查path.log中的錯誤，如果有錯誤重新運(yùn)行catupgrd.sql,若無錯誤，關(guān)庫，正常起庫 (#升級運(yùn)行的日志文件路徑/u01/Disk1/catinfo.log)
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 520093696 bytes
Fixed Size 2085160 bytes
Variable Size 209719000 bytes
Database Buffers 301989888 bytes
Redo Buffers 6299648 bytes
Database mounted.
Database opened.
https://www.cndba.cn/cndba/Marvinn/article/2704
退出EXIT，重進(jìn)數(shù)據(jù)庫，重新編譯無效對象
oracle@single-10g>sqlplus / as sysdba
SQL*Plus: Release 10.2.0.4.0 - Production on Mon Apr 2 12:01:24 2018
Copyright (c) 1982, 2007, Oracle. All Rights Reserved.
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @?rdbms/admin/utlrp.sql ##耐心等待
7、檢查組件的升級情況
SQL> select * from UTL_RECOMP_ERRORS;
no rows selected
SQL>col comp_name format a30;
col version format a20;
col status format a20;
set line 200;
SQL> select comp_name,version,status from sys.dba_registry;
COMP_NAME VERSION STATUS
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and T 10.2.0.4.0 VALID
ypes
Oracle Workspace Manager 10.2.0.4.3 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Data Mining 10.2.0.4.0 VALID
Oracle Text 10.2.0.4.0 VALID
COMP_NAME VERSION STATUS
Oracle XML Database 10.2.0.4.0 VALID
Oracle Rule Manager 10.2.0.4.0 VALID
Oracle interMedia 10.2.0.4.0 VALID
OLAP Analytic Workspace 10.2.0.4.0 VALID
Oracle OLAP API 10.2.0.4.0 VALID
OLAP Catalog 10.2.0.4.0 VALID
Spatial 10.2.0.4.0 VALID
Oracle Enterprise Manager 10.2.0.4.0 VALID
17 rows selected.
顯示的值為valid, 表示成功完成升級
SQL> select comp_name,version,status from sys.dba_registry; #版本要是最新版本10.2.0.4.0，status要是valid
COMP_NAME VERSION STATUS
Oracle Database Catalog Views 10.2.0.4.0 VALID
Oracle Database Packages and T 10.2.0.4.0 VALID
ypes
Oracle Workspace Manager 10.2.0.4.3 VALID
JServer JAVA Virtual Machine 10.2.0.4.0 VALID
Oracle XDK 10.2.0.4.0 VALID
Oracle Database Java Packages 10.2.0.4.0 VALID
Oracle Expression Filter 10.2.0.4.0 VALID
Oracle Data Mining 10.2.0.4.0 VALID
Oracle Text 10.2.0.4.0 VALID
COMP_NAME VERSION STATUS
Oracle XML Database 10.2.0.4.0 VALID
Oracle Rule Manager 10.2.0.4.0 VALID
Oracle interMedia 10.2.0.4.0 VALID
OLAP Analytic Workspace 10.2.0.4.0 VALID
Oracle OLAP API 10.2.0.4.0 VALID
OLAP Catalog 10.2.0.4.0 VALID
Spatial 10.2.0.4.0 VALID
Oracle Enterprise Manager 10.2.0.4.0 VALID
17 rows selected.
查看數(shù)據(jù),表空間，用戶等等
sql>select tablespace_name,status from dba_tablespaces;
sql>select username from dba_user;
sql>select object_name from dba_objects where owner=’marvin’;
sql>select * from marvin,marvin;
檢查是否有無效對象
SQL> select count(*) from dba_objects where status=’INVALID’;
COUNT(*)
0
檢查是否有無效的對象
SQL> select object_name,status from dba_objects where object_Name in (‘SYS’,’SYSTEM’) and status = ‘INVALID’;
no rows selected
如果值大于0則說明有錯誤，就要重新執(zhí)行sql>@d:/oracle/product/10.2.0/db_1/rdbms/admin/catupgrd.sql，知道沒有錯誤
查看版本
SQL> select * from v$version;
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Linux: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
更新EM資料庫
oracle@single-10g:/u01/oracle/12.2.0/rdbms/admin>emca -upgrade db
https://www.cndba.cn/cndba/Marvinn/article/2704
STARTED EMCA at Apr 2, 2018 12:10:32 PM
EM Configuration Assistant, Version 10.2.0.1.0 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
ORACLE_HOME for the database to be upgraded:
ORACLE_HOME for the database to be upgraded: /u01/oracle/12.2.0/
Database SID: orcl
Listener port number: 1521
Do you wish to continue? [yes(Y)/no(N)]: y
Apr 2, 2018 12:11:19 PM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /u01/oracle/12.2.0/cfgtoollogs/emca/emca_2018-04-02_12-10-32-PM.log.
Apr 2, 2018 12:11:19 PM oracle.sysman.emcp.EMConfig perform
SEVERE: EM is not configured for this database. No EM-specific actions can be performed.
Refer to the log file at /u01/oracle/12.2.0/cfgtoollogs/emca/emca_2018-04-02_12-10-32-PM.log for more details.
Could not complete the configuration. Refer to the log file at /u01/oracle/12.2.0/cfgtoollogs/emca/emca_2018-04-02_12-10-32-PM.log for more details.
收集下數(shù)據(jù)字典統(tǒng)計信息
SQL> EXECUTE dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.
修改數(shù)據(jù)庫compatibel
alter system set compatible=’10.2.0.4.0’ scope=spfile;
等到數(shù)據(jù)庫所有都升級完成后，重啟監(jiān)聽，對外服務(wù)
oracle@single-10g>lsnrctl start
版權(quán)聲明：本文為博主原創(chuàng)文章，未經(jīng)博主允許不得轉(zhuǎn)載。
總結(jié)
以上是生活随笔為你收集整理的手动升级oracle,ORACLE 10.2.0.1手动升级到10.2.0.4的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： ios请求php接口失败,laravel
- 下一篇： linux查看oracle的sga设置,