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建议的哪种。

 

----------------------------------------------完成---------------------------------------------------