图文精华

发表新帖

广告

TDB ORACLE 10.2.0.4.0 W2K3 32位 to ORACLE 10.2.5.0 LINUX X86_64 测试

tdb
source： windows 2003 32位 oracle 10.2.0.4 32位
target:  rhel 5.5 x86_64   oracle 10.2.0.5 64位


sqlplus /nolog
connect /as sysDBA
shutdown immediate;
startup open read only;


C:\Documents and Settings\Administrator>mkdir C:\ORACLE\RPTREPOS\

rman target /

####convert a database on target platform
run
{
convert database
on target platform
convert script 'c:\oracle\rptrepos\rptrepos.cnv'
transport script 'c:\oracle\rptrepos\rptrepos.txp'
new database 'rptrepos'
db_file_name_convert = 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL' 'c:\oracle\reprepos';
}

RMAN> run
{
convert database
on target platform
convert script 'c:\oracle\rptrepos\rptrepos.cnv'
transport script 'c:\oracle\rptrepos\rptrepos.txp'
new database 'rptrepos'
db_file_name_convert = 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL' 'c:\oracle\reprepos';
}2> 3> 4> 5> 6> 7> 8> 9>

启动 convert 于 30-11月-16
使用通道 ORA_DISK_1

在数据库中找到外部表 SH.SALES_TRANSACTIONS_EXT

在数据库中找到目录 SYS.DATA_PUMP_DIR
在数据库中找到目录 SYS.ORACLE_OCM_CONFIG_DIR
在数据库中找到目录 SYS.ADMIN_DIR
在数据库中找到目录 SYS.WORK_DIR
在数据库中找到目录 SYS.DATA_FILE_DIR
在数据库中找到目录 SYS.LOG_FILE_DIR
在数据库中找到目录 SYS.MEDIA_DIR
在数据库中找到目录 SYS.XMLDIR
在数据库中找到目录 SYS.SUBDIR

在数据库中找到 BFILE PM.PRINT_MEDIA

在口令文件中找到用户 SYS (具有 SYSDBA and SYSOPER 权限)
通道 ORA_DISK_1: 开始检查数据文件
输入数据文件 fno=00001 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF
通道 ORA_DISK_1: 数据文件检查完毕, 用时: 00:00:00
通道 ORA_DISK_1: 开始检查数据文件
输入数据文件 fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
通道 ORA_DISK_1: 数据文件检查完毕, 用时: 00:00:00
通道 ORA_DISK_1: 开始检查数据文件
输入数据文件 fno=00005 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
通道 ORA_DISK_1: 数据文件检查完毕, 用时: 00:00:00
通道 ORA_DISK_1: 开始检查数据文件
输入数据文件 fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
通道 ORA_DISK_1: 数据文件检查完毕, 用时: 00:00:00
通道 ORA_DISK_1: 开始检查数据文件
输入数据文件 fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
通道 ORA_DISK_1: 数据文件检查完毕, 用时: 00:00:00
在目标平台上运行 SQL 脚本 C:\ORACLE\RPTREPOS\RPTREPOS.TXP 以创建数据库
编辑 init.ora 文件 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00RM7VMS_1_0.ORA。此 PFILE 将用于在目标平台上创建数据库据
在目标平台上运行 RMAN 脚本 C:\ORACLE\RPTREPOS\RPTREPOS.CNV 以转换数据文件
要重新编译所有 PL/SQL 模块, 请在目标平台上运行 utlirp.sql 和 utlrp.sql
要更改内部数据库标识符, 请使用 DBNEWID 实用程序
完成 backup 于 30-11月-16

RMAN>


C:\oracle\RPTREPOS>dir
驱动器 C 中的卷没有标签。
卷的序列号是 4890-4BD4

C:\oracle\RPTREPOS 的目录

2016-11-30  19:01    <DIR>          .
2016-11-30  19:01    <DIR>          ..
2016-11-30  18:56               852 RPTREPOS.CNV
2016-11-30  18:56             2,813 RPTREPOS.TXP
2016-11-30  19:01                 0 run
               3 个文件          3,665 字节
               2 个目录 24,301,318,144 可用字节



C:\oracle\RPTREPOS>type RPTREPOS.TXP
-- 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:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00RM7VMS_1_0.ORA'
CREATE CONTROLFILE REUSE SET DATABASE "RPTREPOS" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCH_D-RPTREPOS_ID-1456993145_S-4_T-1_A-929212411_00RM7VMS'  SIZE 50M,
  GROUP 2 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCH_D-RPTREPOS_ID-1456993145_S-2_T-1_A-929212411_00RM7VMS'  SIZE 50M,
  GROUP 3 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCH_D-RPTREPOS_ID-1456993145_S-3_T-1_A-929212411_00RM7VMS'  SIZE 50M
DATAFILE
  'C:\ORACLE\REPREPOS\SYSTEM01.DBF',
  'C:\ORACLE\REPREPOS\UNDOTBS01.DBF',
  'C:\ORACLE\REPREPOS\SYSAUX01.DBF',
  'C:\ORACLE\REPREPOS\USERS01.DBF',
  'C:\ORACLE\REPREPOS\EXAMPLE01.DBF'
CHARACTER SET ZHS16GBK
;

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_D-RPTREPOS_I-1456993145_TS-TEMP_FNO-1_00RM7VMS'
     SIZE 20971520  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:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00RM7VMS_1_0.ORA'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00RM7VMS_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;




C:\oracle\RPTREPOS>type RPTREPOS.CNV




RUN {

  CONVERT DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT 'C:\ORACLE\REPREPOS\SYSTEM01.DBF';


  CONVERT DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT 'C:\ORACLE\REPREPOS\SYSAUX01.DBF';


  CONVERT DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT 'C:\ORACLE\REPREPOS\EXAMPLE01.DBF';


  CONVERT DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT 'C:\ORACLE\REPREPOS\UNDOTBS01.DBF';


  CONVERT DATAFILE 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT 'C:\ORACLE\REPREPOS\USERS01.DBF';


}

拷贝源端参数文件，数据文件及上面两个脚本至Linux平台

C:\oracle\RPTREPOS>sqlplus /nolog

SQL*Plus: Release 10.2.0.4.0 - Production on 星期三 11月 30 19:34:58 2016

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

SQL> connect /as sysdba
已连接。
SQL> show parameter spfile;

NAME                                     TYPE         VALUE
------------------------------------ ----------- ------------------------------
spfile                                     string         C:\ORACLE\PRODUCT\10.2.0\DB_1\
                                                 DATABASE\SPFILEORCL.ORA
SQL> show parameter control

NAME                                     TYPE         VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time             integer         7
控制文件不必拷贝，生成脚本目标端重建
control_files                             string         C:\ORACLE\PRODUCT\10.2.0\ORADA
                                                 TA\ORCL\CONTROL01.CTL, C:\ORAC
                                                 LE\PRODUCT\10.2.0\ORADATA\ORCL
                                                 \CONTROL02.CTL, C:\ORACLE\PROD
                                                 UCT\10.2.0\ORADATA\ORCL\CONTRO
                                                 L03.CTL
                                                 
拷贝下面数据文件至目标端
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF     
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF     
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF   
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSTEM01.DBF   
C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF     

SQL>
创建可编辑参数文件，拷贝至目标端
SQL> create pfile from spfile;
位于$ORACLE_BASE/admin/orcl/pfile/ 文件init.ora.102920161834276
##############################################################################
# Copyright (c) 1991, 2001, 2002 by Oracle Corporation
##############################################################################

###########################################
# NLS
###########################################
nls_language="SIMPLIFIED CHINESE"
nls_territory="CHINA"

###########################################
# SGA Memory
###########################################
sga_target=612368384

###########################################
# Job Queues
###########################################
job_queue_processes=10


dispatchers="(PROTOCOL=TCP) (SERVICE=orclXDB)"


compatible=10.2.0.3.0


audit_file_dest=C:\oracle\product\10.2.0\admin\orcl\adump
remote_login_passwordfile=EXCLUSIVE


log_archive_format=ARC%S_%R.%T


pga_aggregate_target=203423744

###########################################
# Database Identification
###########################################
db_domain=""
db_name=orcl

###########################################
# File Configuration
###########################################
control_files=("C:\oracle\product\10.2.0\oradata\orcl\control01.ctl", "C:\oracle\product\10.2.0\oradata\orcl\control02.ctl", "C:\oracle\product\10.2.0\oradata\orcl\control03.ctl")
db_recovery_file_dest=C:\oracle\product\10.2.0\flash_recovery_area
db_recovery_file_dest_size=2147483648

###########################################
# Cursors and Library Cache
###########################################
open_cursors=300

###########################################
# System Managed Undo and Rollback Segments
###########################################
undo_management=AUTO
undo_tablespace=UNDOTBS1

###########################################
# Diagnostics and Statistics
###########################################
background_dump_dest=C:\oracle\product\10.2.0\admin\orcl\bdump
core_dump_dest=C:\oracle\product\10.2.0\admin\orcl\cdump
user_dump_dest=C:\oracle\product\10.2.0\admin\orcl\udump

###########################################
# Processes and Sessions
###########################################
processes=150

###########################################
# Cache and I/O
###########################################
db_block_size=8192
db_file_multiblock_read_count=16

临时表空间不必拷贝，目标端新建



目标端Linux平台调整

收集目标端目录相关信息：

SQL> connect /as sysdba
Connected.
SQL> show parameter db_recovery

NAME                                     TYPE         VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest                     string         /u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size             big integer 2G
SQL> show parameter control

NAME                                     TYPE         VALUE
------------------------------------ ----------- ------------------------------
control_file_record_keep_time             integer         7
control_files                             string         /u01/app/oracle/oradata/orcl/control01.ctl, /u01/app/oracle/oradata/orcl/control02.ctl, /u01/app/oracle/oradata/orcl/control03.ctl
SQL> select file_name from dba_data_files;

FILE_NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/users01.dbf
/u01/app/oracle/oradata/orcl/sysaux01.dbf
/u01/app/oracle/oradata/orcl/undotbs01.dbf
/u01/app/oracle/oradata/orcl/system01.dbf
/u01/app/oracle/oradata/orcl/example01.dbf

SQL> show parameter pfile;

NAME                                     TYPE         VALUE
------------------------------------ ----------- ------------------------------
spfile                                     string         /u01/app/oracle/product/10.2.0/db_1/dbs/spfileorcl.ora
SQL> show parameter comp

NAME                                     TYPE         VALUE
------------------------------------ ----------- ------------------------------
compatible                             string         10.2.0.5.0
nls_comp                             string
plsql_compiler_flags                     string         INTERPRETED, NON_DEBUG
plsql_v2_compatibility                     boolean         FALSE


SQL> show parameter audit

NAME                                     TYPE         VALUE
------------------------------------ ----------- ------------------------------
audit_file_dest                      string         /u01/app/oracle/admin/orcl/adump


目标端创建项目目录

su - oracle


mkdir -p /u01/app/oracle/admin/rptrepos/adump
mkdir -p /u01/app/oracle/admin/rptrepos/bdump
mkdir -p /u01/app/oracle/admin/rptrepos/cdump
mkdir -p /u01/app/oracle/admin/rptrepos/udump
mkdir -p /u01/app/oracle/oradata/rptrepos

如下：

[oracle@middle ~]$ mkdir -p /u01/app/oracle/admin/rptrepos/adump
[oracle@middle ~]$ mkdir -p /u01/app/oracle/admin/rptrepos/bdump
[oracle@middle ~]$ mkdir -p /u01/app/oracle/admin/rptrepos/cdump
[oracle@middle ~]$ mkdir -p /u01/app/oracle/admin/rptrepos/udump
[oracle@middle ~]$ mkdir -p /u01/app/oracle/oradata/rptrepos    数据文件目录
[oracle@middle ~]$ mkdir -p /u01/app/oracle/oradata/rptrepostmp 转换前数据文件所在目录


调整参数文件init.ora.102920161834276


cat initrptrepos.ora
nls_language="SIMPLIFIED CHINESE"
nls_territory="CHINA"
sga_target=612368384
job_queue_processes=10
dispatchers="(PROTOCOL=TCP) (SERVICE=rptreposlXDB)"
####modify to 10.2.0.5.0
compatible=10.2.0.5.0
audit_file_dest=/u01/app/oracle/admin/rptrepos/adump
remote_login_passwordfile=EXCLUSIVE
log_archive_format=ARC%S_%R.%T
pga_aggregate_target=203423744
db_domain=""
db_name=rptrepos
control_files=("/u01/app/oracle/admin/rptrepos/control01.ctl", "/u01/app/oracle/admin/rptrepos/control02.ctl", "/u01/app/oracle/admin/rptrepos/control03.ctl")
db_recovery_file_dest=/u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size=2147483648
open_cursors=300
undo_management=AUTO
undo_tablespace=UNDOTBS1
background_dump_dest=/u01/app/oracle/admin/rptrepos/bdump
core_dump_dest=/u01/app/oracle/admin/rptrepos/cdump
user_dump_dest=/u01/app/oracle/admin/rptrepos/udump
processes=150
db_block_size=8192
db_file_multiblock_read_count=16

调整生成脚本
C:\oracle\RPTREPOS>type RPTREPOS.CNV




RUN {

  CONVERT DATAFILE '/u01/app/oracle/oradata/rptrepos/SYSTEM01.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT '/u01/app/oracle/oradata/rptrepos/SYSTEM01.DBF';


  CONVERT DATAFILE '/u01/app/oracle/oradata/rptrepos/SYSAUX01.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT '/u01/app/oracle/oradata/rptrepos/SYSAUX01.DBF';


  CONVERT DATAFILE '/u01/app/oracle/oradata/rptrepos/EXAMPLE01.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT '/u01/app/oracle/oradata/rptrepos/EXAMPLE01.DBF';


  CONVERT DATAFILE '/u01/app/oracle/oradata/rptrepos/UNDOTBS01.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT '/u01/app/oracle/oradata/rptrepos/UNDOTBS01.DBF';


  CONVERT DATAFILE '/u01/app/oracle/oradata/rptrepos/USERS01.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT '/u01/app/oracle/oradata/rptrepos/USERS01.DBF';


}


调整脚本：

C:\oracle\RPTREPOS>type RPTREPOS.TXP
-- 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/product/10.2.0/db_1/dbs/initrptrepos.ora'
CREATE CONTROLFILE REUSE SET DATABASE "RPTREPOS" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/rptrepos/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/rptrepos/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/rptrepos/redo03.log'  SIZE 50M
DATAFILE
  '/u01/app/oracle/oradata/rptrepos/SYSTEM01.DBF',
  '/u01/app/oracle/oradata/rptrepos/UNDOTBS01.DBF',
  '/u01/app/oracle/oradata/rptrepos/SYSAUX01.DBF',
  '/u01/app/oracle/oradata/rptrepos/USERS01.DBF',
  '/u01/app/oracle/oradata/rptrepos/EXAMPLE01.DBF'
CHARACTER SET ZHS16GBK
;

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/rptrepos/temp01.dbf'
     SIZE 20971520  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/product/10.2.0/db_1/dbs/initrptrepos.ora'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='/u01/app/oracle/product/10.2.0/db_1/dbs/initrptrepos.ora'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;



调整完毕后拷贝参数文件，数据文件至目标端相关目录


数据文件：
[root@middle rptrepos]# pwd
/u01/app/oracle/oradata/rptrepostmp
[root@middle rptrepostmp]# ls -l
total 891816
-rw-r--r-- 1 root root 104865792 Nov 30 07:15 EXAMPLE01.DBF
-rw-r--r-- 1 root root 262152192 Nov 30 07:16 SYSAUX01.DBF
-rw-r--r-- 1 root root 503324672 Nov 30 07:15 SYSTEM01.DBF
-rw-r--r-- 1 root root  36708352 Nov 30 07:15 UNDOTBS01.DBF
-rw-r--r-- 1 root root   5251072 Nov 30 07:15 USERS01.DBF
[root@middle rptrepostmp]# chown oracleinstall *
[root@middle rptrepostmp]# ls -l
total 891816
-rw-r--r-- 1 oracle oinstall 104865792 Nov 30 07:15 EXAMPLE01.DBF
-rw-r--r-- 1 oracle oinstall 262152192 Nov 30 07:16 SYSAUX01.DBF
-rw-r--r-- 1 oracle oinstall 503324672 Nov 30 07:15 SYSTEM01.DBF
-rw-r--r-- 1 oracle oinstall  36708352 Nov 30 07:15 UNDOTBS01.DBF
-rw-r--r-- 1 oracle oinstall   5251072 Nov 30 07:15 USERS01.DBF



[oracle@middle dbs]$ pwd
/u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@middle dbs]$
[oracle@middle dbs]$ pwd
/u01/app/oracle/product/10.2.0/db_1/dbs
[oracle@middle dbs]$ cat initrptrepos.ora
nls_language="SIMPLIFIED CHINESE"
nls_territory="CHINA"
sga_target=612368384
job_queue_processes=10
dispatchers="(PROTOCOL=TCP) (SERVICE=rptreposlXDB)"
####modify to 10.2.0.5.0
compatible=10.2.0.5.0
audit_file_dest=/u01/app/oracle/admin/rptrepos/adump
remote_login_passwordfile=EXCLUSIVE
log_archive_format=ARC%S_%R.%T
pga_aggregate_target=203423744
db_domain=""
db_name=rptrepos
control_files=("/u01/app/oracle/admin/rptrepos/control01.ctl", "/u01/app/oracle/admin/rptrepos/control02.ctl", "/u01/app/oracle/admin/rptrepos/control03.ctl")
db_recovery_file_dest=/u01/app/oracle/flash_recovery_area
db_recovery_file_dest_size=2147483648
open_cursors=300
undo_management=AUTO
undo_tablespace=UNDOTBS1
background_dump_dest=/u01/app/oracle/admin/rptrepos/bdump
core_dump_dest=/u01/app/oracle/admin/rptrepos/cdump
user_dump_dest=/u01/app/oracle/admin/rptrepos/udump
processes=150
db_block_size=8192
db_file_multiblock_read_count=16


[oracle@middle ~]$ ls -l
pwd
/home/oracle
total 8
-rw-r--r-- 1 oracle oinstall  896 Nov 30 07:32 RPTREPOS.CNV
-rw-r--r-- 1 oracle oinstall 2600 Nov 30 07:33 RPTREPOS.TXP
[oracle@middle ~]$ cat RPTREPOS.CNV
RUN {

  CONVERT DATAFILE '/u01/app/oracle/oradata/rptrepostmp/SYSTEM01.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT '/u01/app/oracle/oradata/rptrepos/SYSTEM01.DBF';


  CONVERT DATAFILE '/u01/app/oracle/oradata/rptrepostmp/SYSAUX01.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT '/u01/app/oracle/oradata/rptrepos/SYSAUX01.DBF';


  CONVERT DATAFILE '/u01/app/oracle/oradata/rptrepostmp/EXAMPLE01.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT '/u01/app/oracle/oradata/rptrepos/EXAMPLE01.DBF';


  CONVERT DATAFILE '/u01/app/oracle/oradata/rptrepostmp/UNDOTBS01.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT '/u01/app/oracle/oradata/rptrepos/UNDOTBS01.DBF';


  CONVERT DATAFILE '/u01/app/oracle/oradata/rptrepostmp/USERS01.DBF'
  FROM PLATFORM 'Microsoft Windows IA (32-bit)'
  FORMAT '/u01/app/oracle/oradata/rptrepos/USERS01.DBF';


}
[oracle@middle ~]$ cat RPTREPOS.TXP
-- 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/product/10.2.0/db_1/dbs/initrptrepos.ora'
CREATE CONTROLFILE REUSE SET DATABASE "RPTREPOS" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/rptrepos/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/rptrepos/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/rptrepos/redo03.log'  SIZE 50M
DATAFILE
  '/u01/app/oracle/oradata/rptrepos/SYSTEM01.DBF',
  '/u01/app/oracle/oradata/rptrepos/UNDOTBS01.DBF',
  '/u01/app/oracle/oradata/rptrepos/SYSAUX01.DBF',
  '/u01/app/oracle/oradata/rptrepos/USERS01.DBF',
  '/u01/app/oracle/oradata/rptrepos/EXAMPLE01.DBF'
CHARACTER SET ZHS16GBK
;

-- Database can now be opened zeroing the online logs.
ALTER DATABASE OPEN RESETLOGS;

-- Commands to add tempfiles to temporary tablespaces.
-- Online tempfiles have complete space information.
-- Other tempfiles may require adjustment.
ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/rptrepos/temp01.dbf'
     SIZE 20971520  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/product/10.2.0/db_1/dbs/initrptrepos.ora'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='/u01/app/oracle/product/10.2.0/db_1/dbs/initrptrepos.ora'
-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql
set feedback 6;
[oracle@middle ~]$





[oracle@middle ~]$ echo $ORACLE_SID
rptrepos
[oracle@middle ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Nov 30 07:38:44 2016

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initrptrepos.ora' nomount
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                    2098208 bytes
Variable Size                  167775200 bytes
Database Buffers          436207616 bytes
Redo Buffers                    6287360 bytes


RMAN> @a.cv

RMAN> RUN {
2>
3>   CONVERT DATAFILE '/u01/app/oracle/oradata/rptrepostmp/SYSTEM01.DBF'
4>   FROM PLATFORM 'Microsoft Windows IA (32-bit)'
5>   FORMAT '/u01/app/oracle/oradata/rptrepos/SYSTEM01.DBF';
6>
7>
8>   CONVERT DATAFILE '/u01/app/oracle/oradata/rptrepostmp/SYSAUX01.DBF'
9>   FROM PLATFORM 'Microsoft Windows IA (32-bit)'
10>   FORMAT '/u01/app/oracle/oradata/rptrepos/SYSAUX01.DBF';
11>
12>
13>   CONVERT DATAFILE '/u01/app/oracle/oradata/rptrepostmp/EXAMPLE01.DBF'
14>   FROM PLATFORM 'Microsoft Windows IA (32-bit)'
15>   FORMAT '/u01/app/oracle/oradata/rptrepos/EXAMPLE01.DBF';
16>
17>
18>   CONVERT DATAFILE '/u01/app/oracle/oradata/rptrepostmp/UNDOTBS01.DBF'
19>   FROM PLATFORM 'Microsoft Windows IA (32-bit)'
20>   FORMAT '/u01/app/oracle/oradata/rptrepos/UNDOTBS01.DBF';
21>
22>
23>   CONVERT DATAFILE '/u01/app/oracle/oradata/rptrepostmp/USERS01.DBF'
24>   FROM PLATFORM 'Microsoft Windows IA (32-bit)'
25>   FORMAT '/u01/app/oracle/oradata/rptrepos/USERS01.DBF';
26>
27>
28> }
Starting backup at 30-NOV-16
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: sid=151 devtype=DISK
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/app/oracle/oradata/rptrepostmp/SYSTEM01.DBF
converted datafile=/u01/app/oracle/oradata/rptrepos/SYSTEM01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:03
Finished backup at 30-NOV-16

Starting backup at 30-NOV-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/app/oracle/oradata/rptrepostmp/SYSAUX01.DBF
converted datafile=/u01/app/oracle/oradata/rptrepos/SYSAUX01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 30-NOV-16

Starting backup at 30-NOV-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/app/oracle/oradata/rptrepostmp/EXAMPLE01.DBF
converted datafile=/u01/app/oracle/oradata/rptrepos/EXAMPLE01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 30-NOV-16

Starting backup at 30-NOV-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/app/oracle/oradata/rptrepostmp/UNDOTBS01.DBF
converted datafile=/u01/app/oracle/oradata/rptrepos/UNDOTBS01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 30-NOV-16

Starting backup at 30-NOV-16
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile conversion
input filename=/u01/app/oracle/oradata/rptrepostmp/USERS01.DBF
converted datafile=/u01/app/oracle/oradata/rptrepos/USERS01.DBF
channel ORA_DISK_1: datafile conversion complete, elapsed time: 00:00:01
Finished backup at 30-NOV-16

RMAN> **end-of-file**

RMAN> exit


Recovery Manager complete.
[oracle@middle rptrepos]$ ls -l
total 891820
-rw-r--r-- 1 oracle oinstall       911 Nov 30 08:06 a.cv
-rw-r----- 1 oracle oinstall 104865792 Nov 30 08:06 EXAMPLE01.DBF
-rw-r----- 1 oracle oinstall 262152192 Nov 30 08:06 SYSAUX01.DBF
-rw-r----- 1 oracle oinstall 503324672 Nov 30 08:06 SYSTEM01.DBF
-rw-r----- 1 oracle oinstall  36708352 Nov 30 08:06 UNDOTBS01.DBF
-rw-r----- 1 oracle oinstall   5251072 Nov 30 08:06 USERS01.DBF


SQL> CREATE CONTROLFILE REUSE SET DATABASE "RPTREPOS" RESETLOGS  ARCHIVELOG
    MAXLOGFILES 16
    MAXLOGMEMBERS 3
    MAXDATAFILES 100
    MAXINSTANCES 8
    MAXLOGHISTORY 292
LOGFILE
  GROUP 1 '/u01/app/oracle/oradata/rptrepos/redo01.log'  SIZE 50M,
  GROUP 2 '/u01/app/oracle/oradata/rptrepos/redo02.log'  SIZE 50M,
  GROUP 3 '/u01/app/oracle/oradata/rptrepos/redo03.log'  SIZE 50M
DATAFILE
  '/u01/app/oracle/oradata/rptrepos/SYSTEM01.DBF',
  '/u01/app/oracle/oradata/rptrepos/UNDOTBS01.DBF',
  '/u01/app/oracle/o  2    3    4    5    6    7    8    9   10   11   12   13   14  radata/rptrepos/SYSAUX01.DBF',
  '/u01/app/oracle/oradata/rptrepos/USERS01.DBF',
  '/u01/app/oracle/oradata/rptrepos/EXAMPLE01.DBF'
CHARACTER SET ZHS16GBK
; 15   16   17   18  

Control file created.

SQL> exit


[oracle@middle ~]$ echo $ORACLE_SID
rptrepos
[oracle@middle ~]$ sqlplus /nolog

SQL*Plus: Release 10.2.0.5.0 - Production on Wed Nov 30 08:14:27 2016

Copyright (c) 1982, 2010, Oracle.  All Rights Reserved.

SQL> connect /as sysdba
Connected to an idle instance.
SQL> startup pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initrptrepos.ora' nomount;
ORACLE instance started.

Total System Global Area  612368384 bytes
Fixed Size                    2098208 bytes
Variable Size                  167775200 bytes
Database Buffers          436207616 bytes
Redo Buffers                    6287360 bytes
SQL> alter database mount;

Database altered.

SQL> alter database open upgrade;
alter database open upgrade
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery if it was restored from backup, or END
BACKUP if it was not
ORA-01110: data file 1: '/u01/app/oracle/oradata/rptrepos/SYSTEM01.DBF'


SQL> recover database;
Media recovery complete.
SQL> alter database open upgrade;

Database altered.

SQL>
SQL> ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/oradata/rptrepos/temp01.dbf'
     SIZE 20971520  AUTOEXTEND ON NEXT 655360  MAXSIZE 32767M;  2  

Tablespace altered.

SQL>
SHUTDOWN IMMEDIATE
STARTUP UPGRADE PFILE='/u01/app/oracle/product/10.2.0/db_1/dbs/initrptrepos.ora'
@@ ?/rdbms/admin/catupgrd.sql
##############################################################################################################################3
因为源端版本10.2.0.4 目标端版本10.2.0.5.0 故要执行此脚本升级，源端目标端版本相同时此脚本跳过,开始时调整源端compatible=10.2.0.3.0
参数为10.2.0.5与目标端版本相同，但还是要执行catupgrd.sql升级，否则只执行utlirp.sql后执行STARTUP PFILE='/u01/app/oracle/product/10.2.0/db_1/dbs/initrptrepos.ora'
后数据库实例报错异常终止，提示
ARC1: Archival started
LGWR: STARTING ARCH PROCESSES COMPLETE
ARC1 started with pid=17, OS id=4094
Wed Nov 30 08:13:46 EST 2016
Thread 1 advanced to log sequence 3 (thread open)
Thread 1 opened at log sequence 3
  Current log# 3 seq# 3 mem# 0: /u01/app/oracle/oradata/rptrepos/redo03.log
Successful open of redo thread 1
Wed Nov 30 08:13:46 EST 2016
MTTR advisory is disabled because FAST_START_MTTR_TARGET is not set
Wed Nov 30 08:13:46 EST 2016
Errors in file /u01/app/oracle/admin/rptrepos/udump/rptrepos_ora_4075.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 4075
ORA-1092 signalled during: alter database open...
##########################################################


@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='/u01/app/oracle/product/10.2.0/db_1/dbs/initrptrepos.ora'
create spfile from pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initrptrepos.ora';
shutdown immediate;
startup



-- The following step will recompile all PL/SQL modules.
-- It may take serveral hours to complete.
@@ ?/rdbms/admin/utlrp.sql

检查目标端状态：

SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE
SQL> select * from scott.dept;

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON
SQL> select banner from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE        10.2.0.5.0        Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production
SQL> host;
[oracle@middle ~]$ arch
x86_64
[oracle@middle ~]$ cat /etc/redhat-release
Red Hat Enterprise Linux Server release 4.5 (Tikanga)
[oracle@middle ~]$


标签： 暂无标签
dongxujian

写了 86 篇文章，拥有财富 384，被 13 人关注

0
举报 收藏
转播转播 分享分享 分享淘帖
回复

使用道具

dongxujian
P4 | 发表于 2016-11-30 22:12:56
run
{
convert database
on target platform
convert script 'c:\oracle\rptrepos\rptrepos.cnv'
transport script 'c:\oracle\rptrepos\rptrepos.txp'
new database 'rptrepos'
db_file_name_convert = 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL' 'c:\oracle\reprepos';
}
本例中使用的是目标端文件转换方式
回复

使用道具

dongxujian
P4 | 发表于 2016-11-30 22:14:52
补充一下，开始的时候执行下面两个包：
dbms_tts.check_db
dbms_tts.check_external
验证源端数据库处于合适的状态

评分

参与人数 1金币 +2 收起 理由
iidba + 2 赞一个!

查看全部评分

回复

使用道具

高级模式
B Color Image Link Quote Code Smilies
您需要登录后才可以回帖 登录 | 加入社区

本版积分规则

关于我们· 联系我们· 加入我们· 聚知识· 合作伙伴      

© 2020 iiDBA Powered by Discuz! X3.4 Comsenz Inc.

iiDBA / Smrz 京ICP备12024223-8号 Wxb 不良信息举报电话：13718043309

意见
反馈