ANBOB™

oracle支持一些异构平台的dataguard，如Windows 到Linux, 在做了平台迁移后可能会出现一些PLSQL 对象执行报错的现象，如ora-7445 type: SIGsEGv, Address not mapped to object或ORA-600 [PL/SQL Native Code: Wrong Platform] Errors, 是因为plsql对象在Windows是可能已编译为NATIVE本地机器码，换平台后不认证，简单记录解决方法

Win2Linux步骤如

检查源库

SQL> select PLATFORM_NAME,name, db_unique_name, database_role  from v$database;

SQL>  show parameter PLSQL_CODE_TYPE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_code_type                      string      NATIVE

检查目标库

SQL> select PLATFORM_NAME,name, db_unique_name, database_role  from v$database;
PLATFORM_NAME                                         NAME      DB_UNIQUE_NAME                 DATABASE_ROLE
----------------------------------------------------- --------- ------------------------------ ----------------
Linux x86 64-bit                                      DB11G     db11g                          PRIMARY

SQL>  show parameter PLSQL_CODE_TYPE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_code_type                      string      INTERPRETED

当 PLSQL_CODE_TYPE 参数设置为 NATIVE 时，PL/SQL 库单元将被编译为本机（机器）代码。因此，在视窗机器代码上编译的 PL/SQL 库单元在 Linux 平台上会出错。

解决方法
把linux库上的所有plsql标记为无效invalid, 再编译为有效。

SQL> show parameter PLSQL_CODE_TYPE

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
plsql_code_type                      string      INTERPRETED

-- From SQL*Plus connected to the database as AS SYSDBA, enter:
SQL> shutdown immediate
SQL> startup upgrade
SQL> @?\rdbms\admin\utlirp.sql
SQL> shutdown immediate
SQL> startup
SQL> @?\rdbms\admin\utlrp.sql

Note:
utlirp.sql标记为无效，utlrp.sql编译失效对象。

Rem NAME
Rem utlirp.sql – UTiLity script to Invalidate Pl/sql modules
Rem
Rem DESCRIPTION
Rem This script can be used to invalidate and all pl/sql modules
Rem (procedures, functions, packages, types, triggers, views)
Rem in a database.
Rem
Rem This script must be run when it is necessary to regenerate the
Rem compiled code because the PL/SQL code format is inconsistent with
Rem the Oracle executable (e.g., when migrating a 32 bit database to
Rem a 64 bit database or vice-versa).
Rem
Rem Please note that this script does not recompile invalid objects
Rem automatically. You must restart the database and explicitly invoke
Rem utlrp.sql to recompile invalid objects.
Rem
Rem USAGE
Rem To use this script, execute the following sequence of actions:
Rem 1. Shut down the database and restart in UPGRADE mode
Rem (using STARTUP UPGRADE or ALTER DATABASE OPEN UPGRADE)
Rem 2. Run this script
Rem 3. Shut down the database and restart in normal mode
Rem 4. Run utlrp.sql to recompile invalid objects. This script does
Rem not automatically recompile invalid objects.
Rem
Rem NOTES
Rem * This script must be run using SQL*PLUS.
Rem * You must be connected AS SYSDBA to run this script.
Rem * This script expects the following files to be available in the
Rem current directory:
Rem standard.sql
Rem dbmsstdx.sql
Rem * There should be no other DDL on the database while running the
Rem script. Not following this recommendation may lead to deadlocks.

打赏

微信扫一扫，打赏作者吧～