跨平台传输数据库
RMAN的convert database命令被用来将整个数据库从一个平台移动到另一个平台。当源平台与目标平台字节序相同时，RMAN会在目标平台自动完成创建新数据库的大部分操作。当跨平台传输数据库时convertdatabase命令的处理过程将会很长。与传输表空间一样，RMAN的convert database命令可以在源平台或目标平台执行。
当跨平台传输数据库时文件会自动被传输到目标平台，这些文件包括：
1.属于永久表空间的数据文件,注意，不管源平台与目标平吧的字节序是否相同，传输数据库的数据文件必须要进行转换不能简单的从一个平台复制到另一个平台。与跨平台传输表空间不一样，传输整个数据库要求特定类型的数据块，比如undo段，要被重新格式化确保与目标平台兼容
2.如果使用PFILE，PFILE文件会被传输。如果使用SPFILE，会基于SPFILE生成PFILE并将其传输，并在目标平基于PFILE来生成新的SPFILE。注意，在大多数情况下，PFILE文件中的有些参数对于新数据库需要手动修改。例如db_name,control_files
跨平台传输数据库的限制
跨平台传输数据库的主要限制是源平台与目标平台必须有相同的字节序。例如，当将数据库从Windows平台传输到Linux平台（都是小字节序）或者从HP-UX到AIX(都是大字节序),不能将整个数据库从HP-UX传输到Linux平台上。然而，可以在目标平台上创建一个新数据库，并使用表空间传输功能将需要的表空间从源数据库中使用跨平台与跨字节序传输到目标数据库中。
在源平台执行转换
将32位windows平台上的数据库jingyong(10.2.0.1)迁移到32位的Linux平台上(10.2.0.5)。在源平台上执行RMAN的convert database操作的步骤如下:
1.将源数据库以只读模式打开
C:\Users\Administrator>sqlplus / as sysdba SQL*Plus: Release 10.2.0.1.0 - Production on 星期二 3月 31 15:08:36 2015 Copyright (c) 1982, 2005, Oracle. All rights reserved. 连接到: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production With the Partitioning, OLAP and Data Mining options SQL> shutdown immediate 数据库已经关闭。 已经卸载数据库。 ORACLE 例程已经关闭。 SQL> startup mount ORACLE 例程已经启动。 Total System Global Area 419430400 bytes Fixed Size 1249320 bytes Variable Size 125833176 bytes Database Buffers 285212672 bytes Redo Buffers 7135232 bytes 数据库装载完毕。 SQL> alter database open read only; 数据库已更改。
2.对源数据库执行dbms_tdb.check_db检查
SQL> set serveroutput on
SQL> declare
2 db_ready boolean;
3 begin
4 db_ready :=dbms_tdb.check_db('Linux IA (32-bit)',dbms_tdb.skip_readonly);
5 end;
6 /
PL/SQL procedure successfully completed
3.对源数据库执行dbms_tdb.check_external来识别外部对象
SQL> set serveroutput on; SQL> declare 2 external boolean; 3 begin 4 external:=dbms_tdb.check_external; 5 end; 6 / The following external tables exist in the database: SH.SALES_TRANSACTIONS_EXT The following directories exist in the database: SYS.TEST_DUMP, SYS.SUBDIR, SYS.XMLDIR, SYS.MEDIA_DIR, SYS.LOG_FILE_DIR, SYS.DATA_FILE_DIR, SYS.WORK_DIR, SYS.ADMIN_DIR, SYS.DATA_PUMP_DIR The following BFILEs exist in the database: PM.PRINT_MEDIA PL/SQL procedure successfully completed
4.执行convert database命令，指定目标平台和输出文件名称。RMAN会生成需要移动到目标平台上的文件，包含以下文件:
--数据库完整的数据文件副本
--在目标平台使用的新的PFILE参数文件，包含来自源数据库PFILE或SPFILE中的设置。在目标平台使用之前需要进行相应修改
--传输脚本，包含在目标平台创建新数据库的SQL语句
RMAN> convert database new database 'jingyong'
2> transport script 'E:\oradata\transport\transport_db_script.sql'
3> to platform 'Linux IA (32-bit)'
4> db_file_name_convert('E:\oradata\JINGYONG\DATAFILE', 'E:\oradata\transport','
E:\oradata\JINGYONG', 'E:\oradata\transport');
启动 convert 于 31-3月 -15
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=152 devtype=DISK
在数据库中找到外部表 SH.SALES_TRANSACTIONS_EXT
在数据库中找到目录 SYS.TEST_DUMP
在数据库中找到目录 SYS.SUBDIR
在数据库中找到目录 SYS.XMLDIR
在数据库中找到目录 SYS.MEDIA_DIR
在数据库中找到目录 SYS.LOG_FILE_DIR
在数据库中找到目录 SYS.DATA_FILE_DIR
在数据库中找到目录 SYS.WORK_DIR
在数据库中找到目录 SYS.ADMIN_DIR
在数据库中找到目录 SYS.DATA_PUMP_DIR
在数据库中找到 BFILE PM.PRINT_MEDIA
在口令文件中找到用户 SYS (具有 SYSDBA and SYSOPER 权限)
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00001 name=E:\ORADATA\JINGYONG\DATAFILE\O1_MF_SYSTEM_BKLLOT8Z_.
DBF
已转换的数据文件 = E:\ORADATA\TRANSPORT\O1_MF_SYSTEM_BKLLOT8Z_.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:25
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00003 name=E:\ORADATA\JINGYONG\DATAFILE\O1_MF_SYSAUX_BKLLOTFL_.
DBF
已转换的数据文件 = E:\ORADATA\TRANSPORT\O1_MF_SYSAUX_BKLLOTFL_.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:16
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00005 name=E:\ORADATA\JINGYONG\DATAFILE\O1_MF_EXAMPLE_BKLLRKG2_
.DBF
已转换的数据文件 = E:\ORADATA\TRANSPORT\O1_MF_EXAMPLE_BKLLRKG2_.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:07
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00006 name=E:\ORADATA\JINGYONG\TSPITR01.DBF
已转换的数据文件 = E:\ORADATA\TRANSPORT\TSPITR01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:03
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00007 name=E:\ORADATA\JINGYONG\TEST01.DBF
已转换的数据文件 = E:\ORADATA\TRANSPORT\TEST01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:03
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00002 name=E:\ORADATA\JINGYONG\DATAFILE\O1_MF_UNDOTBS1_BKLLOTM0
_.DBF
已转换的数据文件 = E:\ORADATA\TRANSPORT\O1_MF_UNDOTBS1_BKLLOTM0_.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:03
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00004 name=E:\ORADATA\JINGYONG\DATAFILE\O1_MF_USERS_BKLLOTM6_.D
BF
已转换的数据文件 = E:\ORADATA\TRANSPORT\O1_MF_USERS_BKLLOTM6_.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
在目标平台上运行 SQL 脚本 E:\ORADATA\TRANSPORT\TRANSPORT_DB_SCRIPT.SQL 以创建数
据库
编辑 init.ora 文件 D:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00Q37GUS_1_0.ORA
。此 PFILE 将用于在目标平台上创建数据库据
要重新编译所有 PL/SQL 模块, 请在目标平台上运行 utlirp.sql 和 utlrp.sql
要更改内部数据库标识符, 请使用 DBNEWID 实用程序
完成 backup 于 31-3月 -15
5.将转换操作生成的所有文件传输到目标主机上存储数据库文件的目录中当convert database执行完成后，源数据库可以以读写模式打开，生成的所有文件都要被传输到目标主机上。
ftp> put INIT_00Q37GUS_1_0.ORA 200 PORT command successful. Consider using PASV. 150 Ok to send data. 226 File receive OK. ftp: 发送 1415 字节，用时 0.00秒 1415.00千字节/秒。 ftp> put O1_MF_EXAMPLE_BKLLRKG2_.DBF 200 PORT command successful. Consider using PASV. 150 Ok to send data. 226 File receive OK. ftp: 发送 104865792 字节，用时 8.57秒 12233.53千字节/秒。 ftp> put O1_MF_SYSTEM_BKLLOT8Z_.DBF 200 PORT command successful. Consider using PASV. 150 Ok to send data. 226 File receive OK. ftp: 发送 503324672 字节，用时 49.54秒 10160.38千字节/秒。 ftp> put TSPITR01.DBF 200 PORT command successful. Consider using PASV. 150 Ok to send data. 226 File receive OK. ftp: 发送 52436992 字节，用时 4.46秒 11762.45千字节/秒。 ftp> put TEST01.DBF 200 PORT command successful. Consider using PASV. 150 Ok to send data. 226 File receive OK. ftp: 发送 52436992 字节，用时 3.48秒 15050.80千字节/秒。 ftp> put O1_MF_UNDOTBS1_BKLLOTM0_.DBF 200 PORT command successful. Consider using PASV. 150 Ok to send data. 226 File receive OK. ftp: 发送 31465472 字节，用时 2.05秒 15341.53千字节/秒。 ftp> put O1_MF_USERS_BKLLOTM6_.DBF 200 PORT command successful. Consider using PASV. 150 Ok to send data. 226 File receive OK. ftp: 发送 5251072 字节，用时 0.20秒 26520.57千字节/秒。 ftp> put TRANSPORT_DB_SCRIPT.SQL 200 PORT command successful. Consider using PASV. 150 Ok to send data. 226 File receive OK. ftp: 发送 2582 字节，用时 0.00秒 2582000.00千字节/秒。 ftp> put O1_MF_SYSAUX_BKLLOTFL_.DBF 200 PORT command successful. Consider using PASV. 150 Ok to send data. 226 File receive OK. ftp: 发送 251666432 字节，用时 19.63秒 12817.89千字节/秒。 [oracle@oracle11g jingyong]$ ls -lrt total 978976 -rwxrwxrwx 1 oracle oinstall 1415 Mar 31 16:18 INIT_00Q37GUS_1_0.ORA -rwxrwxrwx 1 oracle oinstall 104865792 Mar 31 16:18 O1_MF_EXAMPLE_BKLLRKG2_.DBF -rwxrwxrwx 1 oracle oinstall 503324672 Mar 31 16:20 O1_MF_SYSTEM_BKLLOT8Z_.DBF -rwxrwxrwx 1 oracle oinstall 52436992 Mar 31 16:21 TSPITR01.DBF -rwxrwxrwx 1 oracle oinstall 52436992 Mar 31 16:21 TEST01.DBF -rwxrwxrwx 1 oracle oinstall 31465472 Mar 31 16:22 O1_MF_UNDOTBS1_BKLLOTM0_.DBF -rwxrwxrwx 1 oracle oinstall 5251072 Mar 31 16:22 O1_MF_USERS_BKLLOTM6_.DBF -rwxrwxrwx 1 oracle oinstall 2582 Mar 31 16:23 TRANSPORT_DB_SCRIPT.SQL -rwxrwxrwx 1 oracle oinstall 251666432 Mar 31 16:24 O1_MF_SYSAUX_BKLLOTFL_.DBF
6.在目标主机上创建数据库需要的目录，并修改PFILE参数文件中的相关参数，并修改传输脚本中指示数据文件的位置。
创建相关目录
[oracle@oracle11g dbs]$ mkdir -p /u01/app/oracle/admin/jingyong/adump [oracle@oracle11g dbs]$ mkdir -p /u01/app/oracle/admin/jingyong/bdump [oracle@oracle11g dbs]$ mkdir -p /u01/app/oracle/admin/jingyong/cdump [oracle@oracle11g dbs]$ mkdir -p /u01/app/oracle/admin/jingyong/udump [oracle@oracle11g dbs]$ chmod -R 777 /u01/app/oracle/admin/jingyong*
修改PFILE参数文件
[oracle@oracle11g jingyong]$ vi initjingyong.ora # Please change the values of the following parameters: audit_file_dest = '/u01/app/oracle/admin/jingyong/adump' background_dump_dest = '/u01/app/oracle/admin/jingyong/bdump' user_dump_dest = '/u01/app/oracle/admin/jingyong/udump' core_dump_dest = '/u01/app/oracle/admin/jingyong/udump' db_name = "JINGYONG" control_files='/u01/app/oracle/oradata/test/control01.ctl' # Please review the values of the following parameters: dispatchers = "(PROTOCOL=TCP) (SERVICE=jingyongXDB)" # The values of the following parameters are from source database: processes = 150 nls_language = "SIMPLIFIED CHINESE" nls_territory = "CHINA" sga_target = 130M db_block_size = 8192 compatible = "10.2.0.1.0" db_file_multiblock_read_count= 16 undo_management = "AUTO" undo_tablespace = "UNDOTBS1" job_queue_processes = 10 open_cursors = 300 pga_aggregate_target = 32M
修改convert database命令生成的传输脚本
[oracle@oracle11g jingyong]$ vi TRANSPORT_DB_SCRIPT.SQL -- The contents of online logs will be lost and all backups will -- be invalidated. Use this only if online logs are damaged. -- After mounting the created controlfile, the following SQL -- statement will place the database in the appropriate -- protection mode: -- ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE PERFORMANCE STARTUP NOMOUNT PFILE='/u01/app/oracle/10.2.0/db/dbs/initjingyong.ora' -- Create SPFILE CREATE SPFILE FROM PFILE = '/u01/app/oracle/10.2.0/db/dbs/initjingyong.ora'; STARTUP FORCE NOMOUNT CREATE CONTROLFILE REUSE SET DATABASE "JINGYONG" RESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/u02/jingyong/redo01.log' SIZE 50M, GROUP 2 '/u02/jingyong/redo02.log' SIZE 50M, GROUP 3 '/u02/jingyong/redo03.log' SIZE 50M DATAFILE '/u02/jingyong/O1_MF_SYSTEM_BKLLOT8Z_.DBF', '/u02/jingyong/O1_MF_UNDOTBS1_BKLLOTM0_.DBF', '/u02/jingyong/O1_MF_SYSAUX_BKLLOTFL_.DBF', '/u02/jingyong/O1_MF_USERS_BKLLOTM6_.DBF', '/u02/jingyong/O1_MF_EXAMPLE_BKLLRKG2_.DBF', '/u02/jingyong/TSPITR01.DBF', '/u02/jingyong/TEST01.DBF' CHARACTER SET ZHS16GBK ; -- Database can now be opened zeroing the online logs. ALTER DATABASE OPEN RESETLOGS; -- Commands to add tempfiles to temporary tablespaces. -- Online tempfiles have complete space information. -- Other tempfiles may require adjustment. ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/jingyong/temp01.dbf' SIZE 50M AUTOEXTEND OFF; -- End of tempfile additions. -- set echo off prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ prompt * Your database has been created successfully! prompt * There are many things to think about for the new database. Here prompt * is a checklist to help you stay on track: prompt * 1. You may want to redefine the location of the directory objects. prompt * 2. You may want to change the internal database identifier (DBID) prompt * or the global database name for this database. Use the prompt * NEWDBID Utility (nid). prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ SHUTDOWN IMMEDIATE STARTUP UPGRADE @@ ?/rdbms/admin/utlirp.sql SHUTDOWN IMMEDIATE STARTUP -- The following step will recompile all PL/SQL modules. -- It may take serveral hours to complete. @@ ?/rdbms/admin/utlrp.sql
7.使用SQL*Plus来执行传输脚本中的内容在目标主机上创建新的数据库
[oracle@oracle11g jingyong]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 31 18:11:36 2015 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to an idle instance. SQL> STARTUP NOMOUNT PFILE='/u01/app/oracle/10.2.0/db/dbs/initjingyong.ora' ORACLE instance started. Total System Global Area 138412032 bytes Fixed Size 1272432 bytes Variable Size 62915984 bytes Database Buffers 71303168 bytes Redo Buffers 2920448 bytes SQL> CREATE CONTROLFILE REUSE SET DATABASE "JINGYONG" RESETLOGS NOARCHIVELOG 2 MAXLOGFILES 16 3 MAXLOGMEMBERS 3 4 MAXDATAFILES 100 5 MAXINSTANCES 8 6 MAXLOGHISTORY 292 7 LOGFILE 8 GROUP 1 '/u02/jingyong/redo01.log' SIZE 50M, 9 GROUP 2 '/u02/jingyong/redo02.log' SIZE 50M, 10 GROUP 3 '/u02/jingyong/redo03.log' SIZE 50M 11 DATAFILE 12 '/u02/jingyong/O1_MF_SYSTEM_BKLLOT8Z_.DBF', 13 '/u02/jingyong/O1_MF_UNDOTBS1_BKLLOTM0_.DBF', 14 '/u02/jingyong/O1_MF_SYSAUX_BKLLOTFL_.DBF', 15 '/u02/jingyong/O1_MF_USERS_BKLLOTM6_.DBF', 16 '/u02/jingyong/O1_MF_EXAMPLE_BKLLRKG2_.DBF', 17 '/u02/jingyong/TSPITR01.DBF', 18 '/u02/jingyong/TEST01.DBF' 19 CHARACTER SET ZHS16GBK 20 ; Control file created. SQL> ALTER DATABASE OPEN RESETLOGS; ALTER DATABASE OPEN RESETLOGS * ERROR at line 1: ORA-01092: ORACLE instance terminated. Disconnection forced
在以open resetlogs方式打开数据库是出错了，alert.log中的错误信息如下所示:
Errors in file /u01/app/oracle/admin/jingyong/udump/jingyong_ora_5406.trc: ORA-00704: bootstrap process failure ORA-39700: database must be opened with UPGRADE option Error 704 happened during db open, shutting down database USER: terminating instance due to error 704 Instance terminated by USER, pid = 5406 ORA-1092 signalled during: alter database open...
错误信息指示需要以升级模式来启动数据库，因为迁移过来的源数据库的版本是10.2.0.1，而目标主机上的数据库版本是10.2.0.5。
以升级模式启动数据库
[oracle@oracle11g jingyong]$ sqlplus / as sysdba SQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 31 18:11:36 2015 Copyright (c) 1982, 2010, Oracle. All Rights Reserved. Connected to an idle instance. SQL> startup upgrade; ORACLE instance started. Total System Global Area 138412032 bytes Fixed Size 1272432 bytes Variable Size 62915984 bytes Database Buffers 71303168 bytes Redo Buffers 2920448 bytes Database mounted. ORA-01113: file 1 needs media recovery if it was restored from backup, or END BACKUP if it was not ORA-01110: data file 1: '/u02/jingyong/O1_MF_SYSTEM_BKLLOT8Z_.DBF'
执行恢复并关闭数据库
SQL> recover database; Media recovery complete. SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down.
再次以升级模式启动数据库
SQL> startup upgrade; ORACLE instance started. Total System Global Area 138412032 bytes Fixed Size 1272432 bytes Variable Size 62915984 bytes Database Buffers 71303168 bytes Redo Buffers 2920448 bytes Database mounted. Database opened.
创建临时表空间
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u02/jingyong/temp01.dbf' 2 SIZE 50M AUTOEXTEND OFF;
SQL>@$ORACLE_HOME//rdbms/admin/utlirp.sql 省略... SQL> SQL> Rem Continue even if there are SQL errors SQL> WHENEVER SQLERROR CONTINUE; SQL> SQL> Rem =========================================================================== SQL> Rem END utlip.sql SQL> Rem =========================================================================== SQL> SQL> DOC DOC>####################################################################### DOC>####################################################################### DOC> utlirp.sql completed successfully. All PL/SQL objects in the DOC> database have been invalidated. DOC> DOC> Shut down and restart the database in normal mode and run utlrp.sql to DOC> recompile invalid objects. DOC>####################################################################### DOC>####################################################################### DOC>#
上面的输出信息说在以正常模式重启数据库后执行utlrp.sql脚本来编译无效对象。由于这里源数据库是10.2.0.1，而目标主机数据库的版本是10.2.0.5,在重启数据库仍然需要以升级模式来启动并执行utlrp.sql脚本
SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup upgrade ORACLE instance started. Total System Global Area 138412032 bytes Fixed Size 1272432 bytes Variable Size 62915984 bytes Database Buffers 71303168 bytes Redo Buffers 2920448 bytes Database mounted. Database opened. SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql SQL> Rem =========================================================================== SQL> Rem BEGIN utlrp.sql SQL> Rem =========================================================================== 省略... PL/SQL procedure successfully completed. SQL> SET serveroutput off SQL> SQL> SQL> Rem =========================================================================== SQL> Rem END utlrp.sql SQL> Rem ===========================================================================
执行和数据库升级相关的脚本catupgrade.sql
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql 省略... COMP_TIMESTAMP UPGRD_END 2015-03-31 20:42:53 . Oracle Database 10.2 Upgrade Status Utility 03-31-2015 20:42:54 . Component Status Version HH:MM:SS Oracle Database Server VALID 10.2.0.5.0 00:18:20 JServer JAVA Virtual Machine VALID 10.2.0.5.0 00:05:21 Oracle XDK VALID 10.2.0.5.0 00:00:49 Oracle Database Java Packages VALID 10.2.0.5.0 00:00:37 Oracle Text VALID 10.2.0.5.0 00:01:02 Oracle XML Database VALID 10.2.0.5.0 00:02:47 Oracle Workspace Manager VALID 10.2.0.5.0 00:01:39 Oracle Data Mining VALID 10.2.0.5.0 00:00:39 OLAP Analytic Workspace VALID 10.2.0.5.0 00:00:52 OLAP Catalog VALID 10.2.0.5.0 00:01:24 Oracle OLAP API VALID 10.2.0.5.0 00:01:08 Oracle interMedia VALID 10.2.0.5.0 00:05:22 Spatial VALID 10.2.0.5.0 00:07:20 Oracle Expression Filter VALID 10.2.0.5.0 00:00:26 Oracle Enterprise Manager VALID 10.2.0.5.0 00:02:56 Oracle Rule Manager VALID 10.2.0.5.0 00:00:27 . Total Upgrade Time: 00:53:45 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>#######################################################################
再次运行utlrp.sql脚本来进行编译无效对象
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql TIMESTAMP -------------------------------------------------------------------------------- COMP_TIMESTAMP UTLRP_BGN 2015-03-31 20:50:46 DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalid DOC> objects in the database. Recompilation time is proportional to the DOC> number of invalid objects in the database, so this command may take DOC> a long time to execute on a database with a large number of invalid DOC> objects. DOC> DOC> Use the following queries to track recompilation progress: DOC> DOC> 1. Query returning the number of invalid objects remaining. This DOC> number should decrease with time. DOC> SELECT COUNT(*) FROM obj$ WHERE status IN (4, 5, 6); DOC> DOC> 2. Query returning the number of objects compiled so far. This number DOC> should increase with time. DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED; DOC> DOC> This script automatically chooses serial or parallel recompilation DOC> based on the number of CPUs available (parameter cpu_count) multiplied DOC> by the number of threads per CPU (parameter parallel_threads_per_cpu). DOC> On RAC, this number is added across all RAC nodes. DOC> DOC> UTL_RECOMP uses DBMS_SCHEDULER to create jobs for parallel DOC> recompilation. Jobs are created without instance affinity so that they DOC> can migrate across RAC nodes. Use the following queries to verify DOC> whether UTL_RECOMP jobs are being created and run correctly: DOC> 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 2015-03-31 20:53:25 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 ------------------- 53 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