當前位置:成語大全網 - 新華字典 - oracle 11.2.0.2.3補丁怎麽打

oracle 11.2.0.2.3補丁怎麽打

oracle手動升級有兩種方式,壹種在安裝oracle軟件之後,安裝升級包,然後創建數據庫;另壹種在oracle數據庫創建以後,進行升級。

本文主要介紹oracle數據庫創建後進行的數據庫升級(10.2.0.1-10.2.0.4),代碼如下:

[sql] view plaincopy

SQL> --查看當前數據庫版本

SQL> select * from v$version;

SQL> --給用戶scott解鎖

SQL> alter user scott account unlock;

SQL> --查看表空間

SQL> select * from dba_tablespaces;

SQL> --查看數據文件存放位置

SQL> select * from dba_data_files;

SQL> --創建測試表空間

SQL> create tablespace ts_upgrade datafile '/u02/oradata/data/orcl/ts_upgrade01.dbf' size 5m autoextend on maxsize 30g;

SQL> --創建用戶

SQL> create user u_upgrade identified by "123456" default tablespace ts_upgrade temporary tablespace temp;

SQL> --為用戶賦權

SQL> grant connect,resource to u_upgrade;

SQL> --創建測試表

SQL> create table u_upgrade.dept as select * from scott.dept;

SQL> select * from u_upgrade.dept;

[oracle@feegle ~]$ #停止em

[oracle@feegle ~]$ emctl stop dbconsole

[oracle@feegle ~]$ #停止sql*plus

[oracle@feegle ~]$ isqlplusctl stop

[oracle@feegle ~]$ #停止監聽

[oracle@feegle ~]$ lsnrctl stop

[oracle@feegle ~]$ #安裝升級包(p6810189_10204_Linux-x86-64.zip)

[oracle@feegle ~]$ #手動升級數據庫

[oracle@feegle ~]$ sqlplus /nolog

SQL> conn /as sysdba

SQL> --以升級模式啟動數據庫

SQL> startup upgrade;

SQL> --重建數據字典(建議關閉數據庫歸檔)

SQL> spool upgrade.log

SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql

#####重建數據字典部分截圖#####

Oracle Database 10.2 Upgrade Status Utility 06-15-2012 18:27:56

.

Component Status Version HH:MM:SS

Oracle Database Server VALID 10.2.0.4.0 00:12:04

JServer JAVA Virtual Machine VALID 10.2.0.4.0 00:04:58

Oracle XDK VALID 10.2.0.4.0 00:00:32

Oracle Database Java Packages VALID 10.2.0.4.0 00:00:28

Oracle Text VALID 10.2.0.4.0 00:00:25

Oracle XML Database VALID 10.2.0.4.0 00:02:25

Oracle Workspace Manager VALID 10.2.0.4.3 00:00:43

Oracle Data Mining VALID 10.2.0.4.0 00:00:21

OLAP Analytic Workspace VALID 10.2.0.4.0 00:00:23

OLAP Catalog VALID 10.2.0.4.0 00:01:02

Oracle OLAP API VALID 10.2.0.4.0 00:00:53

Oracle interMedia VALID 10.2.0.4.0 00:03:52

Spatial VALID 10.2.0.4.0 00:01:37

Oracle Expression Filter VALID 10.2.0.4.0 00:00:12

Oracle Enterprise Manager VALID 10.2.0.4.0 00:01:18

Oracle Rule Manager VALID 10.2.0.4.0 00:00:10

.

Total Upgrade Time: 00:31:30

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> spool off

SQL> --關閉數據庫

SQL> shutdown immediate;

SQL> --啟動數據庫

SQL> startup;

SQL> --編譯無效對象

SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql

#####編譯無效對象部分截圖#####

DOC> 1. Query showing jobs created by UTL_RECOMP

DOC> SELECT job_name FROM dba_scheduler_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>

DOC> 2. Query showing UTL_RECOMP jobs that are running

DOC> SELECT job_name FROM dba_scheduler_running_jobs

DOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';

DOC>#

TIMESTAMP

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

COMP_TIMESTAMP UTLRP_END 2012-06-15 18:45:01

DOC> The following query reports the number of objects that have compiled

DOC> with errors (objects that compile with errors have status set to 3 in

DOC> obj$). If the number is higher than expected, please examine the error

DOC> messages reported with each object (using SHOW ERRORS) to see if they

DOC> point to system misconfiguration or resource constraints that must be

DOC> fixed before attempting to recompile these objects.

DOC>#

OBJECTS WITH ERRORS

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

0

DOC> The following query reports the number of errors caught during

DOC> recompilation. If this number is non-zero, please query the error

DOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errors

DOC> are due to misconfiguration or resource constraints that must be

DOC> fixed before objects can compile successfully.

DOC>#

ERRORS DURING RECOMPILATION

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

0

SQL>

#####編譯無效對象部分截圖#####

SQL> 驗證升級

SQL> --查看組件狀態

SQL> --status=VALID

SQL> select comp_name, version, status from sys.dba_registry;

SQL> --查看表空間狀態

SQL> select tablespace_name,status from dba_tablespaces;

SQL> --查看用戶

SQL> select username from dba_users;

SQL> --查看用戶對象

SQL> select object_name from dba_objects where owner='SCOTT';

SQL> select object_name from dba_objects where owner='U_UPGRADE';

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

[oracle@feegle ~]$ #啟動監聽

[oracle@feegle ~]$ lsnrctl start

[oracle@feegle ~]$ #啟動EM

[oracle@feegle ~]$ emctl start dbconsole

[oracle@feegle ~]$ #啟動SQL*PLUS

[oracle@feegle ~]$ isqlplusctl start