11.2.0.3 database异机升级至11.2.0.4

最新推荐文章于 2022-05-09 15:35:17 发布

cuiqiong6888

最新推荐文章于 2022-05-09 15:35:17 发布

阅读量282

点赞数

文章标签：数据库 java

A、B两台主机，A上安装的是11.2.0.3单机版，B上安装的是11.2.0.4单机版，为有效利用主机资源，欲将A上的数据库迁移到B机上运行，假设A上的数据文件所在存储已经能在B机上认到。要完成迁移其实就是将A上的数据库从11.2.0.3升级至11.2.0.4，实施步骤如下：

/////////////////
// 运行@$ORACLE_HOME/rdbms/admin/utlu112i.sql
/////////////////

startup upgrade

SQL> @$ORACLE_HOME/rdbms/admin/utlu112i.sql
DECLARE
*
ERROR at line 1:
ORA-00600: internal error code, arguments: [qcisSetPlsqlCtx:tzi init], [], [], [], [], [], [], [],
[], [], [], []

同时发现alert.log里有如下的报错，大致的意思是时区有关的文件无法找到：
Failed to find timezone data file # 18 (DST_4)

SQL> select NAME, VALUE$ from SYS.PROPS$ where NAME like ('DST_%_TT_VERSION')
2 ;

NAME VALUE$
------------------------------ ----------------------------------------
DST_PRIMARY_TT_VERSION 18
DST_SECONDARY_TT_VERSION 0

按照MOS 362036.1 必须确保$ORACLE_HOME/oracore/zoneinfo/目录下有timezone_18.dat、timezlrg_18.dat两个文件，查看本机上果然没有，从其它主机上复制过来后解决
SQL> @$ORACLE_HOME/rdbms/admin/utlu112i.sql

Oracle Database 11.2 Pre-Upgrade Information Tool 09-01-2015 13:18:42
Script Version: 11.2.0.4.0 Build: 001
.
**********************************************************************
Database:
**********************************************************************
--> name: RWDDB
--> version: 11.2.0.3.0
--> compatible: 11.2.0
--> blocksize: 8192
--> platform: AIX-Based Systems (64-bit)
--> timezone file: V18
.
**********************************************************************
Tablespaces: [make adjustments in the current environment]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
.... minimum required size: 1174 MB
--> SYSAUX tablespace is adequate for the upgrade.
.... minimum required size: 1562 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
.... minimum required size: 400 MB
--> TEMP tablespace is adequate for the upgrade.
.... minimum required size: 60 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:
WARNING: --> "sga_target" needs to be increased to at least 620 MB
.
**********************************************************************
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]
**********************************************************************
-- No obsolete parameters found. No changes are required
.

**********************************************************************
Components: [The following database components will be upgraded or installed]
**********************************************************************
--> Oracle Catalog Views [upgrade] INVALID
--> Oracle Packages and Types [upgrade] INVALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Real Application Clusters [upgrade] INVALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] INVALID
--> OLAP Catalog [upgrade] VALID
--> Oracle Label Security [upgrade] VALID
... To successfully upgrade Oracle Label Security, choose
... 'Select Options' in Oracle installer and then select
... Oracle Label Security.
--> EM Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle interMedia [upgrade] VALID
--> Spatial [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle Application Express [upgrade] VALID
... APEX will only be upgraded if the version of APEX in
... the target Oracle home is higher than the current one.
--> Oracle OLAP API [upgrade] VALID
.
**********************************************************************
Miscellaneous Warnings
**********************************************************************
WARNING: --> Database is using a timezone file greater than version 14.
.... BEFORE upgrading the database, patch the 11gR2
.... $ORACLE_HOME/oracore/zoneinfo/ with a timezone data file of the
.... same version as the one used in the 11.2.0.3.0 release database.
WARNING: --> Database contains INVALID objects prior to upgrade.
.... The list of invalid SYS/SYSTEM objects was written to
.... registry$sys_inv_objs.
.... The list of non-SYS/SYSTEM objects was written to
.... registry$nonsys_inv_objs.
.... Use utluiobj.sql after the upgrade to identify any new invalid
.... objects due to the upgrade.
.... USER ETL has 1 INVALID objects.
.... USER AINEWAWARD has 42 INVALID objects.
WARNING: --> Your recycle bin contains 789 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 APEX_030200 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.

/////////////////
// 清空回收站
/////////////////
purge dba_recyclebin;

/////////////////
// 重启到upgradate
/////////////////
shutdown abort
startup upgrade

/////////////////
// 运行$ORACLE_HOME/rdbms/admin/catupgrd.sql
/////////////////
SQL>@$ORACLE_HOME/rdbms/admin/catupgrd.sql
Oracle Database 11.2 Post-Upgrade Status Tool 09-01-2015 14:00:13
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:08:31
JServer JAVA Virtual Machine
. VALID 11.2.0.4.0 00:04:37
Oracle Real Application Clusters
. INVALID 11.2.0.4.0 00:00:00
Oracle Workspace Manager
. VALID 11.2.0.4.0 00:00:33
OLAP Analytic Workspace
. VALID 11.2.0.4.0 00:00:42
OLAP Catalog
. VALID 11.2.0.4.0 00:00:34
Oracle OLAP API
. VALID 11.2.0.4.0 00:00:23
Oracle Label Security
. OPTION OFF 11.2.0.3.0 00:00:00
Oracle Enterprise Manager
. VALID 11.2.0.4.0 00:02:09
Oracle XDK
. VALID 11.2.0.4.0 00:00:41
Oracle Text
. VALID 11.2.0.4.0 00:00:21
Oracle XML Database
. VALID 11.2.0.4.0 00:02:10
Oracle Database Java Packages
. VALID 11.2.0.4.0 00:00:18
Oracle Multimedia
. VALID 11.2.0.4.0 00:02:27
Spatial
. VALID 11.2.0.4.0 00:02:16
Oracle Expression Filter
. VALID 11.2.0.4.0 00:00:07
Oracle Rules Manager
. VALID 11.2.0.4.0 00:00:06
Oracle Application Express
. VALID 3.2.1.00.12
Final Actions
. 00:00:00
Total Upgrade Time: 00:26:05

PL/SQL procedure successfully completed.

SQL>
SQL> SET SERVEROUTPUT OFF
SQL> SET VERIFY ON
SQL> commit;

Commit complete.

SQL>
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
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

/////////////////
// 重启数据库到正常状态后运行utlrp.sql
/////////////////
startup

SQL> select count(*) from dba_invalid_objects;

COUNT(*)
----------
388

@$ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> select count(*) from dba_invalid_objects;

COUNT(*)
----------
30

***列出invalid object
select owner,object_name,object_type,status from dba_invalid_objects;

***查看哪些对象原来是valid在upgrade后变成了invalid，如果有就要解决一下了
SQL> @$ORACLE_HOME/rdbms/admin/utluiobj.sql
.
Oracle Database 11.2 Post-Upgrade Invalid Objects Tool 09-01-2015 14:22:15
.
This tool lists post-upgrade invalid objects that were not invalid
prior to upgrade (it ignores pre-existing pre-upgrade invalid objects).
.
Owner Object Name Object Type
.

PL/SQL procedure successfully completed.

/////////////////
// 运行utlu112s.sql检查各组件的升级用时及结果
/////////////////
SQL> @$ORACLE_HOME/rdbms/admin/utlu112s.sql
.
Oracle Database 11.2 Post-Upgrade Status Tool 09-01-2015 14:25:10
.
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
.
Oracle Server
. VALID 11.2.0.4.0 00:08:31
JServer JAVA Virtual Machine
. VALID 11.2.0.4.0 00:04:37
Oracle Real Application Clusters
. INVALID 11.2.0.4.0 00:00:00
Oracle Workspace Manager
. VALID 11.2.0.4.0 00:00:33
OLAP Analytic Workspace
. VALID 11.2.0.4.0 00:00:42
OLAP Catalog
. VALID 11.2.0.4.0 00:00:34
Oracle OLAP API
. VALID 11.2.0.4.0 00:00:23
Oracle Label Security
. OPTION OFF 11.2.0.3.0 00:00:00
Oracle Enterprise Manager
. VALID 11.2.0.4.0 00:02:09
Oracle XDK
. VALID 11.2.0.4.0 00:00:41
Oracle Text
. VALID 11.2.0.4.0 00:00:21
Oracle XML Database
. VALID 11.2.0.4.0 00:02:10
Oracle Database Java Packages
. VALID 11.2.0.4.0 00:00:18
Oracle Multimedia
. VALID 11.2.0.4.0 00:02:27
Spatial
. VALID 11.2.0.4.0 00:02:16
Oracle Expression Filter
. VALID 11.2.0.4.0 00:00:07
Oracle Rules Manager
. VALID 11.2.0.4.0 00:00:06
Oracle Application Express
. VALID 3.2.1.00.12
Final Actions
. 00:00:00
Total Upgrade Time: 00:26:05

PL/SQL procedure successfully completed.

使用语句
select substr(comp_id,1,15) comp_id,substr(comp_name,1,30),comp_name,substr(version,1,10) version,status from dba_registry order by modified
也可以达到相同的效果

/////////////////////////////////////////
//执行$ORACLE_HOME/rdbms/admin/catuppst.sql
/////////////////////////////////////////
$ORACLE_HOME/rdbms/admin/catuppst.sql的用途是：migrate the Baseline data

@$ORACLE_HOME/rdbms/admin/catuppst.sql
This script will migrate the Baseline data on a pre-11g database
to the 11g database.

... ...
... Completed Moving the Baseline Data ...
... ...
... If there are no Move BL Data messages ...
... above, then there are no renamed ...
... baseline tables in the system. ...
... ...
... ...
... Completed the Dropping of the ...
... Renamed Baseline Tables ...
... ...
... If there are no Drop Table messages ...
... above, then there are no renamed ...
... baseline tables in the system. ...
... ...
。。。。省略部分输出

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/53956/viewspace-1788835/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/53956/viewspace-1788835/