To Oracle 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 状态
2.用RMAN生成迁移文件
转换数据文件
3.修改对应的转换文件
在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;
4.修改对应的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
7升级
@/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
注意细项:
1. oracle的版本在windows下和linux下要一样,不然,会有问题。
2. 用dbca建库方便,也可手动建立。
3. 用rman的convert 功能比较强大,不用担心其它问题,也是oracle的建议事项。
4. 修改init和转换文件时,对应的文件夹时要注意,不要对应错误了。建议用ORACLE建议的哪种。
----------------------------------------------完成---------------------------------------------------
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/203348/viewspace-1247324/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/203348/viewspace-1247324/