本文主要介紹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