To Ｏracle Linux 6.3
东莞Xiaohua 2014/07/26 QQ:54645433
目錄
1. 在源数据库（windows 数据库testdb）准备迁移
2. 用RMAN生成迁移文件
3. 修改对应的转换文件
4. 修改对应的INIT文件
5. 在目标LINUX（linux 数据库testdb）在用DBCA安装数据库
6. 删除文件，复制文件
7. 升级
软件版本：
Oracle VirtualBox 4.2.0
Windows2008 R2 64位
Oracle DB 11.2.0.1 64位
当然，数据库的迁移有很多方法，如最简单的导入导出，我这里讲的是用RMAN的convert 来做迁移的，也是最方便的一种方案，供大家参考，oracle的文件参考 http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmxplat.htm#BRADV481
细项步骤：
1. 在源数据库准备迁移
查看所有数据文件
查看所有数据文件
查看所有控制文件
查看所有日志文件
查看所有临时文件
查看可以转换的平台
The database has a list of its own internal names for each platform supporting cross-platform data transport. You may need the exact name of the source or destination platform as a parameter to the CONVERT command. For example, you can obtain the platform name of the connected database as follows:
查看目的地的平台
SQL> select platform_name from v$database;（在目的地linux系统中查看）
PLATFORM_NAME
--------------------------------------------------------------------------------
Linux x86 64-bit
运行检查包
check ensures that no conditions would prevent the transport of the database （在源数据库执行）
If no warnings appear, or if DBMS_TDB.CHECK_DB returns TRUE, then you can transport the database.
Execute the DBMS_TDB.CHECK_EXTERNAL function to identify any external tables, directories, or BFILEs. RMAN cannot automate the transport of these files, so you must copy the files manually and re-create the database directories. （在源数据库执行）
DECLARE
external BOOLEAN;
BEGIN
/* value of external is ignored, but with SERVEROUTPUT set to ON
* dbms_tdb.check_external displays report of external objects
* on console */
external := DBMS_TDB.CHECK_EXTERNAL;
END;
在WINDOWS下建立 迁移文件夹 c:\app\transfer
关闭数据库，启动至OPEN read only 状态
２.用RMAN生成迁移文件
转换数据文件
３.修改对应的转换文件
在LINUX下 ORACLE_BASE= /u01/app/oracle
ORACLE_HOME= /u01/app/oracle/11.2.0/db_1
在tansfer目录下，生成了数据文件和转换文件，trans.sql就是，内容如下。
打开TRANS.SQL文件
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- 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='C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\INIT_00PEK3MH_1_0.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\ARCH_D-TESTDB_ID-2609810266_S-4_T-1_A-826482140_03PEK3MH' SIZE 50M BLOCKSIZE 512,
GROUP 2 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\ARCH_D-TESTDB_ID-2609810266_S-5_T-1_A-826482140_04PEK3MH' SIZE 50M BLOCKSIZE 512,
GROUP 3 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\ARCH_D-TESTDB_ID-2609810266_S-3_T-1_A-826482140_05PEK3MH' SIZE 50M BLOCKSIZE 512
DATAFILE
'C:\APP\TRANSFER\SYSTEM01.DBF',
'C:\APP\TRANSFER\SYSAUX01.DBF',
'C:\APP\TRANSFER\UNDOTBS01.DBF',
'C:\APP\TRANSFER\USERS01.DBF'
CHARACTER SET ZHT16MSWIN950
;
-- 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 'C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\DATA_D-TESTDB_I-2609810266_TS-TEMP_FNO-1_06PEK3MH'
SIZE 30408704 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- 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 PFILE='C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\INIT_00PEK3MH_1_0.ORA'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='C:\APP\ADMINISTRATOR\PRODUCT\11.2.0\DBHOME_1\DATABASE\INIT_00PEK3MH_1_0.ORA'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;
因为LINUX的路径和实际不同，修改对应的路径为实际的路径，如以下为修改后的。
-- The following commands will create a new control file and use it
-- to open the database.
-- Data used by Recovery Manager will be lost.
-- 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/11.2.0/db_1/dbs/inittestdb.ora'
CREATE CONTROLFILE REUSE SET DATABASE "TESTDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/testdb/redo01.log' SIZE 50M BLOCKSIZE 512,
GROUP 2 '/u01/app/oracle/oradata/testdb/redo02.log' SIZE 50M BLOCKSIZE 512,
GROUP 3 '/u01/app/oracle/oradata/testdb/redo03.log' SIZE 50M BLOCKSIZE 512
DATAFILE
'/u01/app/oracle/oradata/testdb/SYSTEM01.DBF',
'/u01/app/oracle/oradata/testdb/SYSAUX01.DBF',
'/u01/app/oracle/oradata/testdb/UNDOTBS01.DBF',
'/u01/app/oracle/oradata/testdb/USERS01.DBF'
CHARACTER SET ZHT16MSWIN950
;
-- 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 '/u01/app/oracle/oradata/testdb/temp01.dbf'
SIZE 30408704 AUTOEXTEND ON NEXT 655360 MAXSIZE 32767M;
-- 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 PFILE='/u01/app/oracle/11.2.0/db_1/dbs/inittestdb.ora'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='/u01/app/oracle/11.2.0/db_1/dbs/inittestdb.ora'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;
４.修改对应的INIT文件
当然RMAN转换后，也生成了一个INIT文件，在database目录下，在TRANS.SQL文件中已经有记录。
找到对应目录下的INIT_00PEK3MH_1_0.ORA
复制后修改这个文件为inittestdb.ora，修改此文件，将
主要奖路径修改为LINUX的对应的路径即可。再复制此文件到刚才转换的文件夹下
# Please change the values of the following parameters:
control_files = "/u01/app/oracle/oradata/testdb/control01.ora"
db_recovery_file_dest = "/u01/app/oracle/flash_recovery_area"
db_recovery_file_dest_size= 4102029312
audit_file_dest = "/u01/app/oracle/admin/testdb/adump"
db_name = "testdb"
# Please review the values of the following parameters:
# __oracle_base = "/u01/app/oracle"
__shared_pool_size = 285212672
__large_pool_size = 16777216
__java_pool_size = 16777216
__streams_pool_size = 0
__sga_target = 1023410176
__db_cache_size = 687865856
__shared_io_pool_size = 0
remote_login_passwordfile= "EXCLUSIVE"
db_domain = ""
dispatchers = "(PROTOCOL=TCP) (SERVICE=testdbXDB)"
__pga_aggregate_target = 704643072
# The values of the following parameters are from source database:
processes = 150
memory_target = 1728053248
db_block_size = 8192
compatible = "11.2.0.0.0"
log_archive_format = "ARC%S_%R.%T"
undo_tablespace = "UNDOTBS1"
audit_trail = "OS"
open_cursors = 300
# diagnostic_dest = "/u01/app/oracle"
5.在目标LINUX（linux 数据库testdb）在用DBCA安装数据库
在linux 下，用DBCA建立testdb数据库。（这里的步骤略，大家可以参照我的文章）
http://www.itpub.net/thread-1845779-1-1.html
当然，我在这里方便用DBCA建立好资料库后，再删除数据文件，大家也可手动建库，不然要对应相关的目录即可。
6. 删除文件，复制文件
建立完成后，关闭资料库，删除数据文件
删除linux下的testdb下的所有数据文件。
SQL> shutdow immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
将windows的c:\app\transfer所有文件
复制数据文件至LINUX的
/u01/app/oracle/oradata/testdb/,建议用FTP工具，
再到c:\app\transfer下inittestdb.ora文件复制至/u01/app/oracle/11.2.0/db_1/dbs/inittestdb.ora
７升级
@/u01/app/oracle/oradata/testdb/TRANS.SQL
系统会跑很久
SQL> grant execute on dbms_standard to public
2 /
Grant succeeded.
SQL>
SQL> -- Step (III)
SQL> --
SQL> -- Invalidate views and synonyms which depend (directly or indirectly) on
SQL> -- invalid objects.
SQL> begin
2 loop
3 update obj$ o_outer set status = 6
4 where type# in (4, 5)
5 and status not in (5, 6)
6 and linkname is null
7 and ((subname is null) or (subname <> 'DBMS_DBUPGRADE_BABY'))
8 and exists (select o.obj# from obj$ o, dependency$ d
9 where d.d_obj# = o_outer.obj#
10 and d.p_obj# = o.obj#
11 and (bitand(d.property, 1) = 1)
12 and o.status > 1);
13 exit when sql%notfound;
14 end loop;
15 end;
16 /
PL/SQL procedure successfully completed.
SQL>
SQL> commit;
Commit complete.
SQL>
SQL> alter system flush shared_pool;
System altered.
SQL>
SQL> -- Step (IV)
SQL> --
SQL> -- Delete Diana for tables, views, and sequences
SQL> --
SQL> -- The DELETEs are coded in chunks using a PL/SQL loop to avoid running
SQL> -- into rollback segment limits.
SQL> --
SQL> begin
2
3 loop
4 delete from idl_ub1$ where
5 obj# in (select o.obj# from obj$ o where o.type# in (2, 4, 6))
6 and rownum < 5000;
7 exit when sql%rowcount = 0;
8 commit;
9 end loop;
10
11 --
12 -- IDL_UB2$ must use dynamic SQL because its PIECE type is not
13 -- understood by PL/SQL.
14 --
15 loop
16 execute immediate
17 'delete from idl_ub2$ where
18 obj# in (select o.obj# from obj$ o where o.type# in (2, 4, 6))
19 and rownum < 5000';
20 exit when sql%rowcount = 0;
21 commit;
22 end loop;
23
24 --
25 -- IDL_SB4$ must use dynamic SQL because its PIECE type is not
26 -- understood by PL/SQL.
27 --
28 loop
29 execute immediate
30 'delete from idl_sb4$ where
31 obj# in (select o.obj# from obj$ o where o.type# in (2, 4, 6))
32 and rownum < 5000';
33 exit when sql%rowcount = 0;
34 commit;
35 end loop;
36
37 loop
38 delete from idl_char$ where
39 obj# in (select o.obj# from obj$ o where o.type# in (2, 4, 6))
40 and rownum < 5000;
41 exit when sql%rowcount = 0;
42 commit;
43 end loop;
44 end;
45 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL> alter system flush shared_pool;
System altered.
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> Rem Recompile all DDL triggers
SQL> @@utlrdt
SQL> Rem Copyright (c) 2006, Oracle. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem utlrdt.sql - Recompile DDL triggers while still in UPGRADE mode
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script recompiles all DDL triggers in UPGRADE mode at the
SQL> Rem end of one of three operations:
SQL> Rem 1. DB upgrade
SQL> Rem 2. utlirp to invalidate and recompile all PL/SQL
SQL> Rem 3. dbmsupgnv/dbmsupgin to convert PL/SQL to native/interpreted
SQL> Rem
SQL> Rem NOTES
SQL> Rem Two DDL triggers referencing the same external object (e.g.,
SQL> Rem ORA_SYSEVENT) cannot be recompiled successfully in regular mode.
SQL> Rem Here is the sequence of events causing a failure:
SQL> Rem 1. DDL is executed
SQL> Rem 2. Trigger 1 needs to be fired, is invalid and gets recompiled
SQL> Rem 3. Trigger 1 references ORA_SYSEVENT
SQL> Rem 4. ORA_SYSEVENT is invalid and gets recompiled using ALTER COMPILE
SQL> Rem 5. Before COMMIT, ALTER COMPILE fires DDL trigger 2
SQL> Rem 6. Trigger 2 references ORA_SYSEVENT. Because ORA_SYSEVENT is
SQL> Rem being recompiled, PLS-201 is raised and trigger 2 compiles
SQL> Rem with errors.
SQL> Rem 7. Trigger 2 compiled with errors causes all subsequent DDLs to fail.
SQL> Rem
SQL> Rem [5476415] I've observed a self-deadlock brought on by the existence (in
SQL> Rem my testing environment) of certain system triggers. While such
SQL> Rem triggers do not exist today, to forestall any problems when we do have
SQL> Rem such triggers, we'll pre-compile the ORA_* synonyms here.
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem jmuller 10/17/06 - Fix bug 5476415: avoid self-deadlock in utlrp
SQL> Rem gviswana 03/09/06 - Created
SQL> Rem
SQL>
SQL> SET ECHO ON
SQL>
SQL> declare
2 cursor ora_dict_synonyms is
3 select o.object_id from dba_objects o
4 where o.owner = 'PUBLIC'
5 and o.object_type = 'SYNONYM'
6 and o.object_name like 'ORA_%';
7
8 cursor ddl_triggers is
9 select o.object_id from dba_triggers t, dba_objects o
10 where t.owner = o.owner and t.trigger_name = o.object_name
11 and o.object_type = 'TRIGGER'
12 and (t.triggering_event like '%ALTER%' or
13 t.triggering_event like '%DDL%');
14 begin
15 for s in ora_dict_synonyms loop
16 dbms_utility.validate(s.object_id);
17 end loop;
18
19 for t in ddl_triggers loop
20 dbms_utility.validate(t.object_id);
21 end loop;
22 end;
23 /
PL/SQL procedure successfully completed.
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>#
SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> STARTUP PFILE='/u01/app/oracle/11.2.0/db_1/dbs/inittestdb.ora'
ORACLE instance started.
Total System Global Area 1720328192 bytes
Fixed Size 2214056 bytes
Variable Size 1023412056 bytes
Database Buffers 687865856 bytes
Redo Buffers 6836224 bytes
Database mounted.
Database opened.
SQL> -- The following step will recompile all PL/SQL modules.
SQL> -- It may take serveral hours to complete.
SQL> @@ ?/rdbms/admin/utlrp.sql
SQL> Rem
SQL> Rem $Header: utlrp.sql 24-jul-2003.10:06:51 gviswana Exp $
SQL> Rem
SQL> Rem utlrp.sql
SQL> Rem
SQL> Rem Copyright (c) 1998, 2003, Oracle Corporation. All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem utlrp.sql - Recompile invalid objects
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script recompiles invalid objects in the database.
SQL> Rem
SQL> Rem When run as one of the last steps during upgrade or downgrade,
SQL> Rem this script will validate all remaining invalid objects. It will
SQL> Rem also run a component validation procedure for each component in
SQL> Rem the database. See the README notes for your current release and
SQL> Rem the Oracle Database Upgrade book for more information about
SQL> Rem using utlrp.sql
SQL> Rem
SQL> Rem Although invalid objects are automatically re-validated when used,
SQL> Rem it is useful to run this script after an upgrade or downgrade and
SQL> Rem after applying a patch. This minimizes latencies caused by
SQL> Rem on-demand recompilation. Oracle strongly recommends running this
SQL> Rem script after upgrades, downgrades and patches.
SQL> Rem
SQL> Rem NOTES
SQL> Rem * This script must be run using SQL*PLUS.
SQL> Rem * You must be connected AS SYSDBA to run this script.
SQL> Rem * There should be no other DDL on the database while running the
SQL> Rem script. Not following this recommendation may lead to deadlocks.
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem gviswana 06/26/03 - Switch default to parallel if appropriate
SQL> Rem gviswana 06/12/03 - Switch default back to serial
SQL> Rem gviswana 05/20/03 - 2814808: Automatic parallelism tuning
SQL> Rem rburns 04/28/03 - timestamps and serveroutput for diagnostics
SQL> Rem gviswana 04/13/03 - utlrcmp.sql load -> catproc
SQL> Rem gviswana 06/25/02 - Add documentation
SQL> Rem gviswana 11/12/01 - Use utl_recomp.recomp_serial
SQL> Rem rdecker 11/09/01 - ADD ALTER library support FOR bug 1952368
SQL> Rem rburns 11/12/01 - validate all components after compiles
SQL> Rem rburns 11/06/01 - fix invalid CATPROC call
SQL> Rem rburns 09/29/01 - use 9.2.0
SQL> Rem rburns 09/20/01 - add check for CATPROC valid
SQL> Rem rburns 07/06/01 - get version from instance view
SQL> Rem rburns 05/09/01 - fix for use with 8.1.x
SQL> Rem arithikr 04/17/01 - 1703753: recompile object type# 29,32,33
SQL> Rem skabraha 09/25/00 - validate is now a keyword
SQL> Rem kosinski 06/14/00 - Persistent parameters
SQL> Rem skabraha 06/05/00 - validate tables also
SQL> Rem jdavison 04/11/00 - Modify usage notes for 8.2 changes.
SQL> Rem rshaikh 09/22/99 - quote name for recompile
SQL> Rem ncramesh 08/04/98 - change for sqlplus
SQL> Rem usundara 06/03/98 - merge from 8.0.5
SQL> Rem usundara 04/29/98 - creation (split from utlirp.sql).
SQL> Rem Mark Ramacher (mramache) was the original
SQL> Rem author of this script.
SQL> Rem
SQL>
SQL> Rem ===========================================================================
SQL> Rem BEGIN utlrp.sql
SQL> Rem ===========================================================================
SQL>
SQL> @@utlprp.sql 0
SQL>
SQL>
SQL> Rem Copyright (c) 2003, 2009, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem utlprp.sql - Recompile invalid objects in the database
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem This script recompiles invalid objects in the database.
SQL> Rem
SQL> Rem This script is typically used to recompile invalid objects
SQL> Rem remaining at the end of a database upgrade or downgrade.
SQL> Rem
SQL> Rem Although invalid objects are automatically recompiled on demand,
SQL> Rem running this script ahead of time will reduce or eliminate
SQL> Rem latencies due to automatic recompilation.
SQL> Rem
SQL> Rem This script is a wrapper based on the UTL_RECOMP package.
SQL> Rem UTL_RECOMP provides a more general recompilation interface,
SQL> Rem including options to recompile objects in a single schema. Please
SQL> Rem see the documentation for package UTL_RECOMP for more details.
SQL> Rem
SQL> Rem INPUTS
SQL> Rem The degree of parallelism for recompilation can be controlled by
SQL> Rem providing a parameter to this script. If this parameter is 0 or
SQL> Rem NULL, UTL_RECOMP will automatically determine the appropriate
SQL> Rem level of parallelism based on Oracle parameters cpu_count and
SQL> Rem parallel_threads_per_cpu. If the parameter is 1, sequential
SQL> Rem recompilation is used. Please see the documentation for package
SQL> Rem UTL_RECOMP for more details.
SQL> Rem
SQL> Rem NOTES
SQL> Rem * You must be connected AS SYSDBA to run this script.
SQL> Rem * There should be no other DDL on the database while running the
SQL> Rem script. Not following this recommendation may lead to deadlocks.
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem anighosh 02/19/09 - #(8264899): re-enabling of function based indexes
SQL> Rem not needed.
SQL> Rem cdilling 07/21/08 - check bitand for functional index - bug 7243270
SQL> Rem cdilling 01/21/08 - add support for ORA-30552
SQL> Rem cdilling 08/27/07 - check disabled indexes only
SQL> Rem cdilling 05/22/07 - add support for ORA-38301
SQL> Rem cdilling 02/19/07 - 5530085 - renable invalid indexes
SQL> Rem rburns 03/17/05 - use dbms_registry_sys
SQL> Rem gviswana 02/07/05 - Post-compilation diagnostics
SQL> Rem gviswana 09/09/04 - Auto tuning and diagnosability
SQL> Rem rburns 09/20/04 - fix validate_components
SQL> Rem gviswana 12/09/03 - Move functional-index re-enable here
SQL> Rem gviswana 06/04/03 - gviswana_bug-2814808
SQL> Rem gviswana 05/28/03 - Created
SQL> Rem
SQL>
SQL> SET VERIFY OFF;
SQL>
SQL> SELECT dbms_registry_sys.time_stamp('utlrp_bgn') as timestamp from dual;
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_BGN 2014-07-11 07:30:37
SQL>
SQL> DOC
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>#
SQL>
SQL> DECLARE
2 threads pls_integer := &&1;
3 BEGIN
4 utl_recomp.recomp_parallel(threads);
5 END;
6 /
PL/SQL procedure successfully completed.
SQL>
SQL> SELECT dbms_registry_sys.time_stamp('utlrp_end') as timestamp from dual;
TIMESTAMP
--------------------------------------------------------------------------------
COMP_TIMESTAMP UTLRP_END 2014-07-11 07:41:27
SQL>
SQL> Rem #(8264899): The code to Re-enable functional indexes, which used to exist
SQL> Rem here, is no longer needed.
SQL>
SQL> DOC
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>#
SQL> select COUNT(*) "OBJECTS WITH ERRORS" from obj$ where status = 3;
OBJECTS WITH ERRORS
-------------------
0
SQL>
SQL>
SQL> DOC
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>#
SQL> select COUNT(*) "ERRORS DURING RECOMPILATION" from utl_recomp_errors;
ERRORS DURING RECOMPILATION
---------------------------
0
SQL>
SQL> Rem
SQL> Rem If sys.enabled$index table exists, then re-enable
SQL> Rem list of functional indexes that were enabled prior to upgrade
SQL> Rem The table sys.enabled$index table is created in catupstr.sql
SQL> Rem
SQL> SET serveroutput on
SQL> DECLARE
2 TYPE tab_char IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
3 commands tab_char;
4 p_null CHAR(1);
5 p_schemaname VARCHAR2(30);
6 p_indexname VARCHAR2(30);
7 rebuild_idx_msg BOOLEAN := FALSE;
8 non_existent_index exception;
9 recycle_bin_objs exception;
10 cannot_change_obj exception;
11 no_such_table exception;
12 pragma exception_init(non_existent_index, -1418);
13 pragma exception_init(recycle_bin_objs, -38301);
14 pragma exception_init(cannot_change_obj, -30552);
15 pragma exception_init(no_such_table, -942);
16 type cursor_t IS REF CURSOR;
17 reg_cursor cursor_t;
18 function enquote_name (str varchar2) return varchar2 is
19 begin
20 return dbms_assert.enquote_name(str, FALSE);
21 end enquote_name;
22
23 BEGIN
24 -- Check for existence of the table marking disabled functional indices
25
26 SELECT NULL INTO p_null FROM DBA_OBJECTS
27 WHERE owner = 'SYS' and object_name = 'ENABLED$INDEXES' and
28 object_type = 'TABLE' and rownum <=1;
29
30 -- Select indices to be re-enabled
31 EXECUTE IMMEDIATE q'+
32 SELECT 'ALTER INDEX ' ||
33 dbms_assert.enquote_name(e.schemaname) || '.' ||
34 dbms_assert.enquote_name(e.indexname) || ' ENABLE'
35 FROM enabled$indexes e, ind$ i
36 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0 AND
37 bitand(i.property, 16) != 0+'
38 BULK COLLECT INTO commands;
39
40 IF (commands.count() > 0) THEN
41 FOR i IN 1 .. commands.count() LOOP
42 BEGIN
43 EXECUTE IMMEDIATE commands(i);
44 EXCEPTION
45 WHEN NON_EXISTENT_INDEX THEN NULL;
46 WHEN RECYCLE_BIN_OBJS THEN NULL;
47 WHEN CANNOT_CHANGE_OBJ THEN rebuild_idx_msg := TRUE;
48 END;
49 END LOOP;
50 END IF;
51
52 -- Output any indexes in the table that could not be re-enabled
53 -- due to ORA-30552 during ALTER INDEX...ENBLE command
54
55 IF rebuild_idx_msg THEN
56 BEGIN
57 DBMS_OUTPUT.PUT_LINE
58 ('The following indexes could not be re-enabled and may need to be rebuilt:');
59
60 OPEN reg_cursor FOR
61 'SELECT e.schemaname, e.indexname
62 FROM enabled$indexes e, ind$ i
63 WHERE e.objnum = i.obj# AND bitand(i.flags, 1024) != 0';
64
65 LOOP
66 FETCH reg_cursor INTO p_schemaname, p_indexname;
67 EXIT WHEN reg_cursor%NOTFOUND;
68 DBMS_OUTPUT.PUT_LINE
69 ('.... INDEX ' || p_schemaname || '.' || p_indexname);
70 END LOOP;
71 CLOSE reg_cursor;
72
73 EXCEPTION
74 WHEN NO_DATA_FOUND THEN CLOSE reg_cursor;
75 WHEN NO_SUCH_TABLE THEN CLOSE reg_cursor;
76 WHEN OTHERS THEN CLOSE reg_cursor; raise;
77 END;
78
79 END IF;
80
81 EXECUTE IMMEDIATE 'DROP TABLE sys.enabled$indexes';
82
83 EXCEPTION
84 WHEN NO_DATA_FOUND THEN NULL;
85
86 END;
87 /
PL/SQL procedure successfully completed.
SQL>
SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> EXECUTE dbms_registry_sys.validate_components;
PL/SQL procedure successfully completed.
SQL> SET serveroutput off
SQL>
SQL>
SQL> Rem ===========================================================================
SQL> Rem END utlrp.sql
SQL> Rem ===========================================================================
SQL> set feedback 6;
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
testdb OPEN
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
testdb OPEN
注意细项：
１. oracle的版本在windows下和linux下要一样，不然，会有问题。
２. 用dbca建库方便，也可手动建立。
３. 用rman的convert 功能比较强大，不用担心其它问题，也是oracle的建议事项。
４. 修改init和转换文件时，对应的文件夹时要注意，不要对应错误了。建议用ORACLE建议的哪种。
----------------------------------------------完成---------------------------------------------------
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/203348/viewspace-1247324/，如需转载，请注明出处，否则将追究法律责任。
转载于:http://blog.itpub.net/203348/viewspace-1247324/
4618
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
