Oracle数据库迁移--->从Windows到Linux
I did a practice to migrate the oracle database from windows to linux operation system. The following is the operational process step by step. I recorded it in my blog for the future reference.
从10g开始，RMAN新增了CONVERT的语法，使得数据库文件可以跨版本复制，从这个特性开始，操作系统以及平台不再是数据库物理文件的限制了。
Oracle支持的可转换的平台可以在V$TRANSPORTABLE_PLATFORM视图中查询：
SQL> SELECT * FROM V$TRANSPORTABLE_PLATFORM;
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- ---------------------------
-
1 Solaris[tm] OE (32-bit) Big
2 Solaris[tm] OE (64-bit) Big
7 Microsoft Windows IA (32-bit) Little
10 Linux IA (32-bit) Little
6 AIX-Based Systems (64-bit) Big
3 HP-UX (64-bit) Big
5 HP Tru64 UNIX Little
4 HP-UX IA (64-bit) Big
11 Linux IA (64-bit) Little
15 HP Open VMS Little
8 Microsoft Windows IA (64-bit) Little
PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT
----------- ---------------------------------------- ---------------------------
-
9 IBM zSeries Based Linux Big
13 Linux 64-bit for AMD Little
16 Apple Mac OS Big
12 Microsoft Windows 64-bit for AMD Little
17 Solaris Operating System (x86) Little
18 IBM Power Based Linux Big
17 rows selected.
Oracle支持将数据文件转换为上面这些平台上的数据文件格式。
下面是我做的实验：
source platform:
os: Windows
oracle version: 10.2.0.1.0
Target platform:
os:Linux
oracle version:10.2.0.1.0
Source database ( Windows platform), the following operations are done on Windows platform.
1, 最开始碰到的问题是要求进行CONVERT的数据库必须处于只读状态
c:\>sqlplus / as sysdba
SQL>shutdown immediate
SQL> startup open read only
2, Using RMAN to convert database
C:\>rman target /
Recovery Manager: Release 10.2.0.1.0 - Production on Fri Sep 6 09:52:17 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
connected to target database: XE (DBID=2705070866)
RMAN> convert database new database Watson
2> skip offline
3> transport script 'd:/trans_srcipt.sql'
4> to platform 'Linux IA (32-bit)'
5> format 'd:\trans\%U';
Starting convert at 06-SEP-13
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=33 devtype=DISK
Directory SYS.DATA_PUMP_DIR found in the database
Directory SYS.ORACLECLRDIR found in the database
User SYS with SYSDBA and SYSOPER privilege found in password file
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00003 name=C:\ORACLEXE\ORADATA\XE\SYSAUX.DBF
converted datafile=D:\TRANS\DATA_D-XE_I-2705070866_TS-SYSAUX_FNO-3_01OJ5L2R
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00001 name=C:\ORACLEXE\ORADATA\XE\SYSTEM.DBF
converted datafile=D:\TRANS\DATA_D-XE_I-2705070866_TS-SYSTEM_FNO-1_02OJ5L3K
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00006 name=C:\ORACLEXE\ORADATA\XE\PMDB_LOB1.DBF
converted datafile=D:\TRANS\DATA_D-XE_I-2705070866_TS-PMDB_NDX1_FNO-6_03OJ5L43
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00005 name=C:\ORACLEXE\ORADATA\XE\PMDB_DAT1.DBF
converted datafile=D:\TRANS\DATA_D-XE_I-2705070866_TS-PMDB_DAT1_FNO-5_04OJ5L4I
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:25
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00007 name=C:\ORACLEXE\ORADATA\XE\PMDB_NDX1.DBF
converted datafile=D:\TRANS\DATA_D-XE_I-2705070866_TS-PMDB_LOB1_FNO-7_05OJ5L5B
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:26
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00008 name=C:\ORACLEXE\ORADATA\XE\MMDB_DAT1.DBF
converted datafile=D:\TRANS\DATA_D-XE_I-2705070866_TS-MMDB_DAT1_FNO-8_06OJ5L65
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00009 name=C:\ORACLEXE\ORADATA\XE\MMDB_LOB1.DBF
converted datafile=D:\TRANS\DATA_D-XE_I-2705070866_TS-MMDB_NDX1_FNO-9_07OJ5L6K
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00010 name=C:\ORACLEXE\ORADATA\XE\MMDB_NDX1.DBF
converted datafile=D:\TRANS\DATA_D-XE_I-2705070866_TS-MMDB_LOB1_FNO-10_08OJ5L73
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:15
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00004 name=C:\ORACLEXE\ORADATA\XE\USERS.DBF
converted datafile=D:\TRANS\DATA_D-XE_I-2705070866_TS-USERS_FNO-4_09OJ5L7J
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
channel ORA_DISK_1: starting datafile conversion
input datafile fno=00002 name=C:\ORACLEXE\ORADATA\XE\UNDO.DBF
converted datafile=D:\TRANS\DATA_D-XE_I-2705070866_TS-UNDO_FNO-2_0AOJ5L7Q
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:07
Run SQL script D:\TRANS_SRCIPT.SQL on the target platform to create database
Edit init.ora file D:\TRANS\INIT_00OJ5L2R_1_0.ORA. This PFILE will be used to create the database on the target platform
To recompile all PL/SQL modules, run utlirp.sql and utlrp.sql on the target platform
To change the internal database identifier, use DBNEWID Utility
Finished backup at 06-SEP-13
RMAN>
Target database ( Linux platform)the following operations are done on Linux platform.
3, transfer the folder trans and the TRANS_SRCIPT.SQL to Linux /home/oracle like the following
[oracle@even ~]$ ll
total 268
drwxr-xr-x 2 oracle oinstall 4096 Dec 5 2012 Desktop
-rw-r--r-- 1 oracle oinstall 251438 Sep 22 2011 rlwrap-0.37.tar.gz
-rw-r--r-- 1 oracle oinstall 809 Sep 4 09:55 sqlnet.log
drwxr-xr-x 2 oracle oinstall 4096 Sep 6 2013 trans
-rw-r--r-- 1 oracle oinstall 2995 Sep 6 2013 TRANS_SRCIPT.SQL
4,create the related directory
[oracle@even trans]$ mkdir -p /u01/app/oracle/oradata/Watson
[oracle@even trans]$ mkdir -p /u01/app/oracle/admin/Watson/{a,b,c,d,u}dump
5, edit the init parameter file
[oracle@even trans]$ vi INIT_00OJ5L2R_1_0.ORA
# Please change the values of the following parameters:
# Please change the values of the following parameters:
control_files = "D:\TRANS\CF_D-WATSON_ID-2705070866_00OJ5L2R"
db_recovery_file_dest = "D:\TRANS\flash_recovery_area"
db_recovery_file_dest_size= 10737418240
audit_file_dest = "D:\TRANS\ADUMP"
background_dump_dest = "D:\TRANS\BDUMP"
user_dump_dest = "D:\TRANS\UDUMP"
core_dump_dest = "D:\TRANS\CDUMP"
db_name = "WATSON"
# Please review the values of the following parameters:
__shared_pool_size = 201326592
__large_pool_size = 4194304
__java_pool_size = 4194304
__streams_pool_size = 0
__db_cache_size = 591396864
remote_login_passwordfile= "EXCLUSIVE"
dispatchers = "(PROTOCOL=TCP) (SERVICE=XEXDB)"
# The values of the following parameters are from source database:
sessions = 49
sga_target = 805306368
compatible = "10.2.0.1.0"
undo_management = "AUTO"
undo_tablespace = "UNDO"
shared_servers = 4
job_queue_processes = 4
open_cursors = 300
os_authent_prefix = ""
pga_aggregate_target = 268435456
6, Edit the init parameter file to meet the new system’s requirements.
Create a flash back recovery directory FRA located on “/u01/app/oracle/oradata/FRA”
[oracle@even oradata]$ mkdir -p FRA
[oracle@even oradata]$ ll
total 12
drwxr-xr-x 2 oracle oinstall 4096 Sep 4 12:27 FRA
drwxr-x--- 2 oracle oinstall 4096 Sep 4 08:23 OMR
drwxr-xr-x 2 oracle oinstall 4096 Sep 4 12:19 Watson
[oracle@even oradata]$ pwd
/u01/app/oracle/oradata
[oracle@even oradata]$
[oracle@even trans]$ export ORACLE_SID=Watson
根据具体的需要修改上面的初始化参数，并利用这个初始化参数启动数据库，修改后的初始化参数如下：
[oracle@even trans]$ vi INIT_00OJ5L2R_1_0.ORA
# Please change the values of the following parameters:
control_files = "/u01/app/oracle/oradata/Watson/control01.dbf"
db_recovery_file_dest = "/u01/app/oracle/oradata/FRA"
db_recovery_file_dest_size= 10737418240
audit_file_dest = "/u01/app/oracle/admin/Watson/adump"
background_dump_dest = "/u01/app/oracle/admin/Watson/bdump"
user_dump_dest = "/u01/app/oracle/admin/Watson/udump"
core_dump_dest = "/u01/app/oracle/admin/Watson/cdump"
db_name = "WATSON"
# Please review the values of the following parameters:
__shared_pool_size = 201326592
__large_pool_size = 4194304
__java_pool_size = 4194304
__streams_pool_size = 0
__db_cache_size = 591396864
remote_login_passwordfile= "EXCLUSIVE"
dispatchers = "(PROTOCOL=TCP) (SERVICE=Watson)"
# The values of the following parameters are from source database:
sessions = 49
sga_target = 805306368
compatible = "10.2.0.1.0"
undo_management = "AUTO"
undo_tablespace = "UNDO"
shared_servers = 4
job_queue_processes = 4
open_cursors = 300
os_authent_prefix = ""
pga_aggregate_target = 268435456
7,create the init parameter initWatson.ora to $ORACLE_HOME/dbs
[oracle@even trans]$ cp INIT_00OJ5L2R_1_0.ORA $ORACLE_HOME/dbs
[oracle@even trans]$ cd $ORACLE_HOME/dbs
[oracle@even dbs]$ ll
total 48
-rw-rw---- 1 oracle oinstall 1544 Sep 3 04:23 hc_OMR.dat
-rw-r--r-- 1 oracle oinstall 1331 Sep 4 12:36 INIT_00OJ5L2R_1_0.ORA
-rw-r----- 1 oracle oinstall 12920 May 3 2001 initdw.ora
-rw-r----- 1 oracle oinstall 8385 Sep 11 1998 init.ora
-rw-rw---- 1 oracle oinstall 24 Sep 3 04:23 lkOMR
-rw-r----- 1 oracle oinstall 1536 Sep 3 05:32 orapwOMR
-rw-r----- 1 oracle oinstall 2560 Sep 4 12:00 spfileOMR.ora
[oracle@even dbs]$ cp INIT_00OJ5L2R_1_0.ORA initWatson.ora
[oracle@even dbs]$ ll
total 52
-rw-rw---- 1 oracle oinstall 1544 Sep 3 04:23 hc_OMR.dat
-rw-r--r-- 1 oracle oinstall 1331 Sep 4 12:36 INIT_00OJ5L2R_1_0.ORA
-rw-r----- 1 oracle oinstall 12920 May 3 2001 initdw.ora
-rw-r----- 1 oracle oinstall 8385 Sep 11 1998 init.ora
-rw-r--r-- 1 oracle oinstall 1331 Sep 4 12:37 initWatson.ora
-rw-rw---- 1 oracle oinstall 24 Sep 3 04:23 lkOMR
-rw-r----- 1 oracle oinstall 1536 Sep 3 05:32 orapwOMR
-rw-r----- 1 oracle oinstall 2560 Sep 4 12:00 spfileOMR.ora
8,startup the database
[oracle@even dbs]$ sqlplus / as sysdba
SQL*Plus: Release 10.2.0.1.0 - Production on Wed Sep 4 12:50:48 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount;
ORACLE instance started.
Total System Global Area 805306368 bytes
Fixed Size 1221972 bytes
Variable Size 209717932 bytes
Database Buffers 591396864 bytes
Redo Buffers 2969600 bytes
8, create spfile which is used for startup instance
SQL> create spfile from pfile;
File created.
8, restart the instance
SQL> startup force nomount;
ORACLE instance started.
Total System Global Area 805306368 bytes
Fixed Size 1221972 bytes
Variable Size 209717932 bytes
Database Buffers 591396864 bytes
Redo Buffers 2969600 bytes
9, copy the files in the directory /home/oracle/trans to /u01/app/oracle/oradata/Watson
[oracle@even trans]$ ll
total 2552388
-rw-r--r-- 1 oracle oinstall 262152192 Sep 6 2013 DATA_D-XE_I-2705070866_TS-MMDB_DAT1_FNO-8_06OJ5L65
-rw-r--r-- 1 oracle oinstall 262152192 Sep 6 2013 DATA_D-XE_I-2705070866_TS-MMDB_LOB1_FNO-10_08OJ5L73
-rw-r--r-- 1 oracle oinstall 262152192 Sep 6 2013 DATA_D-XE_I-2705070866_TS-MMDB_NDX1_FNO-9_07OJ5L6K
-rw-r--r-- 1 oracle oinstall 262152192 Sep 6 2013 DATA_D-XE_I-2705070866_TS-PMDB_DAT1_FNO-5_04OJ5L4I
-rw-r--r-- 1 oracle oinstall 262152192 Sep 6 2013 DATA_D-XE_I-2705070866_TS-PMDB_LOB1_FNO-7_05OJ5L5B
-rw-r--r-- 1 oracle oinstall 272637952 Sep 6 2013 DATA_D-XE_I-2705070866_TS-PMDB_NDX1_FNO-6_03OJ5L43
-rw-r--r-- 1 oracle oinstall 461381632 Sep 6 2013 DATA_D-XE_I-2705070866_TS-SYSAUX_FNO-3_01OJ5L2R
-rw-r--r-- 1 oracle oinstall 367009792 Sep 6 2013 DATA_D-XE_I-2705070866_TS-SYSTEM_FNO-1_02OJ5L3K
-rw-r--r-- 1 oracle oinstall 94380032 Sep 6 2013 DATA_D-XE_I-2705070866_TS-UNDO_FNO-2_0AOJ5L7Q
-rw-r--r-- 1 oracle oinstall 104865792 Sep 6 2013 DATA_D-XE_I-2705070866_TS-USERS_FNO-4_09OJ5L7J
-rw-r--r-- 1 oracle oinstall 1331 Sep 4 12:33 INIT_00OJ5L2R_1_0.ORA
[oracle@even trans]$ cp DATA* /u01/app/oracle/oradata/Watson
10, check the files whether is in it
[oracle@even Watson]$ ll
total 2552384
-rw-r--r-- 1 oracle oinstall 262152192 Sep 4 12:55 DATA_D-XE_I-2705070866_TS-MMDB_DAT1_FNO-8_06OJ5L65
-rw-r--r-- 1 oracle oinstall 262152192 Sep 4 12:56 DATA_D-XE_I-2705070866_TS-MMDB_LOB1_FNO-10_08OJ5L73
-rw-r--r-- 1 oracle oinstall 262152192 Sep 4 12:56 DATA_D-XE_I-2705070866_TS-MMDB_NDX1_FNO-9_07OJ5L6K
-rw-r--r-- 1 oracle oinstall 262152192 Sep 4 12:57 DATA_D-XE_I-2705070866_TS-PMDB_DAT1_FNO-5_04OJ5L4I
-rw-r--r-- 1 oracle oinstall 262152192 Sep 4 12:57 DATA_D-XE_I-2705070866_TS-PMDB_LOB1_FNO-7_05OJ5L5B
-rw-r--r-- 1 oracle oinstall 272637952 Sep 4 12:58 DATA_D-XE_I-2705070866_TS-PMDB_NDX1_FNO-6_03OJ5L43
-rw-r--r-- 1 oracle oinstall 461381632 Sep 4 12:58 DATA_D-XE_I-2705070866_TS-SYSAUX_FNO-3_01OJ5L2R
-rw-r--r-- 1 oracle oinstall 367009792 Sep 4 12:59 DATA_D-XE_I-2705070866_TS-SYSTEM_FNO-1_02OJ5L3K
-rw-r--r-- 1 oracle oinstall 94380032 Sep 4 12:59 DATA_D-XE_I-2705070866_TS-UNDO_FNO-2_0AOJ5L7Q
-rw-r--r-- 1 oracle oinstall 104865792 Sep 4 13:00 DATA_D-XE_I-2705070866_TS-USERS_FNO-4_09OJ5L7J
11, edit the TRANS_SRCIPT.SQL to create the control file
[oracle@even ~]$ vi TRANS_SRCIPT.SQL
The control file is the following:
SQL> CREATE CONTROLFILE REUSE SET DATABASE "WATSON" RESETLOGS NOARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 '/u01/app/oracle/oradata/Watson/ARCH_D-WATSON_ID-2705070866_S-10_T-1_A-823950165_00OJ5L2R' SIZE 50M,
GROUP 2 '/u01/app/oracle/oradata/Watson/ARCH_D-WATSON_ID-2705070866_S-11_T-1_A-823950165_00OJ5L2R' SIZE 50M
DATAFILE
'/u01/app/oracle/oradata/Watson/DATA_D-XE_I-2705070866_TS-SYSTEM_FNO-1_02OJ5L3K',
'/u01/app/oracle/oradata/Watson/DATA_D-XE_I-2705070866_TS-UNDO_FNO-2_0AOJ5L7Q',
'/u01/app/oracle/oradata/Watson/DATA_D-XE_I-2705070866_TS-SYSAUX_FNO-3_01OJ5L2R',
'/u01/app/oracle/oradata/Watson/DATA_D-XE_I-2705070866_TS-USERS_FNO-4_09OJ5L7J',
'/u01/app/oracle/oradata/Watson/DATA_D-XE_I-2705070866_TS-PMDB_DAT1_FNO-5_04OJ5L4I',
'/u01/app/oracle/oradata/Watson/DATA_D-XE_I-2705070866_TS-PMDB_NDX1_FNO-6_03OJ5L43',
'/u01/app/oracle/oradata/Watson/DATA_D-XE_I-2705070866_TS-PMDB_LOB1_FNO-7_05OJ5L5B',
'/u01/app/oracle/oradata/Watson/DATA_D-XE_I-2705070866_TS-MMDB_DAT1_FNO-8_06OJ5L65',
'/u01/app/oracle/oradata/Watson/DATA_D-XE_I-2705070866_TS-MMDB_NDX1_FNO-9_07OJ5L6K',
'/u01/app/oracle/oradata/Watson/DATA_D-XE_I-2705070866_TS-MMDB_LOB1_FNO-10_08OJ5L73'
CHARACTER SET AL32UTF8;
Control file created.
12,-- 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/Watson/DATA_D-WATSON_I-2705070866_TS-TEMP_FNO-1_00OJ5L2R'
SIZE 58720256 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
sql>@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
sql>@ ?/rdbms/admin/utlrp.sql
set feedback 6;
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> Rem Copyright (c) 2003, 2005, Oracle. 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 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 2013-09-04 13:12:14
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 2013-09-04 13:26:34
SQL>
SQL> Rem
SQL> Rem Re-enable functional indexes disabled by the recompile
SQL> Rem
SQL> DECLARE
2 TYPE tab_char IS TABLE OF VARCHAR2(32767) INDEX BY BINARY_INTEGER;
3 commands tab_char;
4 table_exists number;
5 BEGIN
6 -- Check for existence of the table marking disabled functional indices
7 SELECT count(*) INTO table_exists FROM DBA_OBJECTS
8 WHERE owner = 'SYS' and object_name = 'UTLIRP_ENABLED_FUNC_INDEXES' and
9 object_type = 'TABLE';
10
11 IF (table_exists > 0) THEN
12 -- Select indices to be re-enabled
13 EXECUTE IMMEDIATE q'+
14 SELECT 'ALTER INDEX "' || u.name || '"."' || o.name || '" ENABLE'
15 FROM utlirp_enabled_func_indexes e, ind$ i, obj$ o, user$ u
16 WHERE e.obj# = i.obj# AND i.obj# = o.obj# and o.owner# = u.user#
17 AND bitand(i.flags, 1024) != 0+'
18 BULK COLLECT INTO commands;
19
20 IF (commands.count() > 0) THEN
21 FOR i IN 1 .. commands.count() LOOP
22 EXECUTE IMMEDIATE commands(i);
23 END LOOP;
24 END IF;
25
26 EXECUTE IMMEDIATE 'DROP TABLE utlirp_enabled_func_indexes';
27 END IF;
28 END;
29 /
PL/SQL procedure successfully completed.
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
-------------------
7
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>
SQL> Rem =====================================================================
SQL> Rem Run component validation procedure
SQL> Rem =====================================================================
SQL>
SQL> SET serveroutput on
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> SQL> SQL>
SQL>
SQL>
13,database migration finished so far
14, check the related tablespaces whether it is in the database or not
SQL> select tablespace_name from dba_data_files;
TABLESPACE_NAME
------------------------------
MMDB_LOB1
MMDB_NDX1
MMDB_DAT1
PMDB_LOB1
PMDB_NDX1
PMDB_DAT1
USERS
SYSAUX
UNDO
SYSTEM
10 rows selected.
SQL> select tablespace_name from dba_temp_files;
TABLESPACE_NAME
------------------------------
TEMP
SQL> select instance_name,status from v$instance;
INSTANCE_NAME STATUS
---------------- ------------
Watson OPEN
From the above information, we can know that the migration of the database from windows os to Linux OS is sucesssful.
