跨平台传输数据库
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 sysdbaSQL*Plus: Release 10.2.0.1.0 - Production on 星期二 3月 31 15:08:36 Copyright (c) 1982, , Oracle. All rights reserved.连接到:Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - ProductionWith the Partitioning, OLAP and Data Mining optionsSQL> shutdown immediate数据库已经关闭。已经卸载数据库。ORACLE 例程已经关闭。SQL> startup mountORACLE 例程已经启动。Total System Global Area 419430400 bytesFixed Size 1249320 bytesVariable Size 125833176 bytesDatabase Buffers285212672 bytesRedo Buffers7135232 bytes数据库装载完毕。SQL> alter database open read only;数据库已更改。
2.对源数据库执行dbms_tdb.check_db检查
SQL> set serveroutput onSQL> declare2 db_ready boolean;3 begin4 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> declare2 external boolean;3 begin4 external:=dbms_tdb.check_external;5 end;6 /The following external tables exist in the database:SH.SALES_TRANSACTIONS_EXTThe 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_DIRThe following BFILEs exist in the database:PM.PRINT_MEDIAPL/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_.DBF已转换的数据文件 = 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.ORA200 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_.DBF200 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_.DBF200 PORT command successful. Consider using PASV.150 Ok to send data.226 File receive OK.ftp: 发送 503324672 字节，用时 49.54秒 10160.38千字节/秒。ftp> put TSPITR01.DBF200 PORT command successful. Consider using PASV.150 Ok to send data.226 File receive OK.ftp: 发送 52436992 字节，用时 4.46秒 11762.45千字节/秒。ftp> put TEST01.DBF200 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_.DBF200 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_.DBF200 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.SQL200 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_.DBF200 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 -lrttotal 978976-rwxrwxrwx 1 oracle oinstall1415 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 oinstall2582 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= 150nls_language = "SIMPLIFIED CHINESE"nls_territory = "CHINA"sga_target= 130Mdb_block_size = 8192compatible= "10.2.0.1.0"db_file_multiblock_read_count= 16undo_management= "AUTO"undo_tablespace= "UNDOTBS1"job_queue_processes= 10open_cursors = 300pga_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 PERFORMANCESTARTUP NOMOUNT PFILE='/u01/app/oracle/10.2.0/db/dbs/initjingyong.ora'-- Create SPFILECREATE SPFILE FROM PFILE = '/u01/app/oracle/10.2.0/db/dbs/initjingyong.ora';STARTUP FORCE NOMOUNTCREATE CONTROLFILE REUSE SET DATABASE "JINGYONG" RESETLOGS NOARCHIVELOGMAXLOGFILES 16MAXLOGMEMBERS 3MAXDATAFILES 100MAXINSTANCES 8MAXLOGHISTORY 292LOGFILEGROUP 1 '/u02/jingyong/redo01.log' SIZE 50M,GROUP 2 '/u02/jingyong/redo02.log' SIZE 50M,GROUP 3 '/u02/jingyong/redo03.log' SIZE 50MDATAFILE'/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 offprompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~prompt * Your database has been created successfully!prompt * There are many things to think about for the new database. Hereprompt * 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 theprompt * NEWDBID Utility (nid).prompt ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~SHUTDOWN IMMEDIATESTARTUP UPGRADE@@ ?/rdbms/admin/utlirp.sqlSHUTDOWN IMMEDIATESTARTUP-- 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 sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 31 18:11:36 Copyright (c) 1982, , 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 bytesFixed Size 1272432 bytesVariable Size 62915984 bytesDatabase Buffers 71303168 bytesRedo Buffers2920448 bytesSQL> CREATE CONTROLFILE REUSE SET DATABASE "JINGYONG" RESETLOGS NOARCHIVELOG2MAXLOGFILES 163MAXLOGMEMBERS 34MAXDATAFILES 1005MAXINSTANCES 86MAXLOGHISTORY 2927 LOGFILE8 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 50M11 DATAFILE12 '/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 ZHS16GBK20 ;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 failureORA-39700: database must be opened with UPGRADE optionError 704 happened during db open, shutting down databaseUSER: terminating instance due to error 704Instance terminated by USER, pid = 5406ORA-1092 signalled during: alter database open...
错误信息指示需要以升级模式来启动数据库，因为迁移过来的源数据库的版本是10.2.0.1，而目标主机上的数据库版本是10.2.0.5。
以升级模式启动数据库
[oracle@oracle11g jingyong]$ sqlplus / as sysdbaSQL*Plus: Release 10.2.0.5.0 - Production on Tue Mar 31 18:11:36 Copyright (c) 1982, , Oracle. All Rights Reserved.Connected to an idle instance.SQL> startup upgrade;ORACLE instance started.Total System Global Area 138412032 bytesFixed Size 1272432 bytesVariable Size 62915984 bytesDatabase Buffers 71303168 bytesRedo Buffers2920448 bytesDatabase mounted.ORA-01113: file 1 needs media recovery if it was restored from backup, or ENDBACKUP if it was notORA-01110: data file 1: '/u02/jingyong/O1_MF_SYSTEM_BKLLOT8Z_.DBF'
执行恢复并关闭数据库
SQL> recover database;Media recovery complete.SQL> shutdown immediate;ORA-01109: database not openDatabase dismounted.ORACLE instance shut down.
再次以升级模式启动数据库
SQL> startup upgrade;ORACLE instance started.Total System Global Area 138412032 bytesFixed Size 1272432 bytesVariable Size 62915984 bytesDatabase Buffers 71303168 bytesRedo Buffers2920448 bytesDatabase 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 errorsSQL> WHENEVER SQLERROR CONTINUE;SQL>SQL> Rem ===========================================================================SQL> Rem END utlip.sqlSQL> Rem ===========================================================================SQL>SQL> DOCDOC>#######################################################################DOC>#######################################################################DOC> utlirp.sql completed successfully. All PL/SQL objects in theDOC> database have been invalidated.DOC>DOC> Shut down and restart the database in normal mode and run utlrp.sql toDOC> recompile invalid objects.DOC>#######################################################################DOC>#######################################################################DOC>#
上面的输出信息说在以正常模式重启数据库后执行utlrp.sql脚本来编译无效对象。由于这里源数据库是10.2.0.1，而目标主机数据库的版本是10.2.0.5,在重启数据库仍然需要以升级模式来启动并执行utlrp.sql脚本
SQL> shutdown immediateDatabase closed.Database dismounted.ORACLE instance shut down.SQL> startup upgradeORACLE instance started.Total System Global Area 138412032 bytesFixed Size 1272432 bytesVariable Size 62915984 bytesDatabase Buffers 71303168 bytesRedo Buffers2920448 bytesDatabase mounted.Database opened.SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sqlSQL> Rem ===========================================================================SQL> Rem BEGIN utlrp.sqlSQL> Rem ===========================================================================省略...PL/SQL procedure successfully completed.SQL> SET serveroutput offSQL>SQL>SQL> Rem ===========================================================================SQL> Rem END utlrp.sqlSQL> Rem ===========================================================================
执行和数据库升级相关的脚本catupgrade.sql
SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql省略...COMP_TIMESTAMP UPGRD_END -03-31 20:42:53.Oracle Database 10.2 Upgrade Status Utility 03-31- 20:42:ponent Status Version HH:MM:SSOracle Database ServerVALID10.2.0.5.0 00:18:20JServer JAVA Virtual Machine VALID10.2.0.5.0 00:05:21Oracle XDK VALID10.2.0.5.0 00:00:49Oracle Database Java Packages VALID10.2.0.5.0 00:00:37Oracle Text VALID10.2.0.5.0 00:01:02Oracle XML Database VALID10.2.0.5.0 00:02:47Oracle Workspace Manager VALID10.2.0.5.0 00:01:39Oracle Data Mining VALID10.2.0.5.0 00:00:39OLAP Analytic Workspace VALID10.2.0.5.0 00:00:52OLAP CatalogVALID10.2.0.5.0 00:01:24Oracle OLAP API VALID10.2.0.5.0 00:01:08Oracle interMediaVALID10.2.0.5.0 00:05:22SpatialVALID10.2.0.5.0 00:07:20Oracle Expression Filter VALID10.2.0.5.0 00:00:26Oracle Enterprise Manager VALID10.2.0.5.0 00:02:56Oracle Rule Manager VALID10.2.0.5.0 00:00:27.Total Upgrade Time: 00:53:45DOC>#######################################################################DOC>#######################################################################DOC>DOC> The above PL/SQL lists the SERVER components in the upgradedDOC> database, along with their current version and status.DOC>DOC> Please review the status and version columns and look forDOC> any errors in the spool log file. If there are errors in the spoolDOC> file, or any components are not VALID or not the current version,DOC> consult the Oracle Database Upgrade Guide for troubleshootingDOC> recommendations.DOC>DOC> Next shutdown immediate, restart for normal operation, and thenDOC> run utlrp.sql to recompile any invalid application objects.DOC>DOC>#######################################################################DOC>#######################################################################
再次运行utlrp.sql脚本来进行编译无效对象
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sqlTIMESTAMP--------------------------------------------------------------------------------COMP_TIMESTAMP UTLRP_BGN -03-31 20:50:46DOC> The following PL/SQL block invokes UTL_RECOMP to recompile invalidDOC> objects in the database. Recompilation time is proportional to theDOC> number of invalid objects in the database, so this command may takeDOC> a long time to execute on a database with a large number of invalidDOC> objects.DOC>DOC> Use the following queries to track recompilation progress:DOC>DOC> 1. Query returning the number of invalid objects remaining. ThisDOC>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 numberDOC>should increase with time.DOC> SELECT COUNT(*) FROM UTL_RECOMP_COMPILED;DOC>DOC> This script automatically chooses serial or parallel recompilationDOC> based on the number of CPUs available (parameter cpu_count) multipliedDOC> 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 parallelDOC> recompilation. Jobs are created without instance affinity so that theyDOC> can migrate across RAC nodes. Use the following queries to verifyDOC> whether UTL_RECOMP jobs are being created and run correctly:DOC>DOC> 1. Query showing jobs created by UTL_RECOMPDOC> SELECT job_name FROM dba_scheduler_jobsDOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';DOC>DOC> 2. Query showing UTL_RECOMP jobs that are runningDOC> SELECT job_name FROM dba_scheduler_running_jobsDOC> WHERE job_name like 'UTL_RECOMP_SLAVE_%';DOC>#TIMESTAMP--------------------------------------------------------------------------------COMP_TIMESTAMP UTLRP_END -03-31 20:53:25DOC> The following query reports the number of objects that have compiledDOC> with errors (objects that compile with errors have status set to 3 inDOC> obj$). If the number is higher than expected, please examine the errorDOC> messages reported with each object (using SHOW ERRORS) to see if theyDOC> point to system misconfiguration or resource constraints that must beDOC> fixed before attempting to recompile these objects.DOC>#OBJECTS WITH ERRORS-------------------53DOC> The following query reports the number of errors caught duringDOC> recompilation. If this number is non-zero, please query the errorDOC> messages in the table UTL_RECOMP_ERRORS to see if any of these errorsDOC> are due to misconfiguration or resource constraints that must beDOC> fixed before objects can compile successfully.DOC>#ERRORS DURING RECOMPILATION---------------------------0
来自 “ ITPUB博客 ” ，链接：/26736162/viewspace-1985359/，如需转载，请注明出处，否则将追究法律责任。
