TDB 传输数据(源端转换数据方式)测试

tdb
source: windows 2003 32位 oracle 10.2.0.4 32位


SQL> select platform_id,platform_name from v$database
PLATFORM_ID PLATFORM_NAME
----------- -----------------------------------------------------------------------------------------------------
          7 Microsoft Windows IA (32-bit)



target:  rhel 5.5 x86_64   oracle 10.2.0.5 64位
SQL> select platform_id,platform_name from v$database


PLATFORM_ID PLATFORM_NAME
----------- -----------------------------------------------------------------------------------------------------
         13 Linux x86 64-bit



SQL> l
  1* select * from v$transportable_platform order by platform_id
SQL> /

PLATFORM_ID PLATFORM_NAME                             ENDIAN_FORMAT
----------- ---------------------------------------- --------------
          1 Solaris[tm] OE (32-bit)                     Big
          2 Solaris[tm] OE (64-bit)                     Big
          3 HP-UX (64-bit)                             Big
          4 HP-UX IA (64-bit)                             Big
          5 HP Tru64 UNIX                             Little
          6 AIX-Based Systems (64-bit)                     Big
          7 Microsoft Windows IA (32-bit)             Little
          8 Microsoft Windows IA (64-bit)             Little
          9 IBM zSeries Based Linux                     Big
         10 Linux IA (32-bit)                             Little
         11 Linux IA (64-bit)                             Little

PLATFORM_ID PLATFORM_NAME                             ENDIAN_FORMAT
----------- ---------------------------------------- --------------
         12 Microsoft Windows x86 64-bit             Little
         13 Linux x86 64-bit                             Little
         15 HP Open VMS                              Little
         16 Apple Mac OS                             Big
         17 Solaris Operating System (x86)             Little
         18 IBM Power Based Linux                     Big
         19 HP IA Open VMS                             Little
         20 Solaris Operating System (x86-64)             Little

可以确认platform_id 为7,13 endian_format 均为Little, 符合字节序相同条件
数据库版本10.2.0.1及之后版本支持TDB ,符合条件






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

验证数据库可传输:

set serveroutput on
declare
db_check boolean;
begin
db_check := dbms_tdb.check_db(target_platform_name => 'Linux x86 64-bit',skip_option => dbms_tdb.skip_offline);
if db_check
  then dbms_output.put('database can be transported to target platform');
  else dbms_output.put('database can not be transported to target platform');
end if;
db_check := dbms_tdb.check_external;
if db_check
   then dbms_output.put('database can be transported to target platform');
   else dbms_output.put('database can not be transported to target platform');
end if;
end;
/

database can be transported to target platformThe following external tables
exist in the database:
SH.SALES_TRANSACTIONS_EXT
The following directories exist in the database:
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
The following BFILEs exist in the database:
PM.PRINT_MEDIA

PL/SQL 过程已成功完成。




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

rman target /

####convert a database on target platform
run {
convert database
new database 'rptrepos'
transport script 'c:\oracle\rptrepos\rptrepos.sql'
to platform 'Linux x86 64-bit'
db_file_name_convert = 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL' 'c:\oracle\rptrepos';
}

C:\Documents and Settings\Administrator>rman target /

恢复管理器: Release 10.2.0.4.0 - Production on 星期四 12月 1 21:12:02 2016

Copyright (c) 1982, 2007, Oracle.  All rights reserved.

连接到目标数据库: ORCL (DBID=1456993145)

RMAN> run {
convert database
new database 'rptrepos'
transport script 'c:\oracle\rptrepos\rptrepos.sql'
to platform 'Linux x86 64-bit'
db_file_name_convert = 'C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL' 'c:\oracle\rptrepos';
}2> 3> 4> 5> 6> 7>

启动 convert 于 01-12月-16
使用目标数据库控制文件替代恢复目录
分配的通道: ORA_DISK_1
通道 ORA_DISK_1: sid=159 devtype=DISK

在数据库中找到外部表 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
已转换的数据文件 = C:\ORACLE\RPTREPOS\SYSTEM01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:07
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00003 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF
已转换的数据文件 = C:\ORACLE\RPTREPOS\SYSAUX01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:03
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00005 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF
已转换的数据文件 = C:\ORACLE\RPTREPOS\EXAMPLE01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00002 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\UNDOTBS01.DBF
已转换的数据文件 = C:\ORACLE\RPTREPOS\UNDOTBS01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
通道 ORA_DISK_1: 启动数据文件转换
输入数据文件 fno=00004 name=C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF
已转换的数据文件 = C:\ORACLE\RPTREPOS\USERS01.DBF
通道 ORA_DISK_1: 数据文件转换完毕, 经过时间: 00:00:01
在目标平台上运行 SQL 脚本 C:\ORACLE\RPTREPOS\RPTREPOS.SQL 以创建数据库
编辑 init.ora 文件 C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00RMDGD8_1_0.ORA。此 PFILE 将用于在目标平台上创建数据库据
要重新编译所有 PL/SQL 模块, 请在目标平台上运行 utlirp.sql 和 utlrp.sql
要更改内部数据库标识符, 请使用 DBNEWID 实用程序
完成 backup 于 01-12月-16

RMAN>exit


恢复管理器完成。

C:\Documents and Settings\Administrator>cd /d C:\oracle\RPTREPOS

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

C:\oracle\RPTREPOS 的目录

2016-12-01  21:12    <DIR>          .
2016-12-01  21:12    <DIR>          ..
2016-12-01  21:12       104,865,792 EXAMPLE01.DBF
2016-12-01  21:12             2,813 RPTREPOS.SQL
2016-12-01  21:12       262,152,192 SYSAUX01.DBF
2016-12-01  21:12       503,324,672 SYSTEM01.DBF
2016-12-01  21:12        36,708,352 UNDOTBS01.DBF
2016-12-01  21:12         5,251,072 USERS01.DBF
               6 个文件    912,304,893 字节
               2 个目录 22,360,567,808 可用字节

C:\oracle\RPTREPOS>



C:\oracle\RPTREPOS>type RPTREPOS.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:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00RMDGD8_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_00RMDGD8'  SIZE 50M,
  GROUP 2 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCH_D-RPTREPOS_ID-1456993145_S-2_T-1_A-929212411_00RMDGD8'  SIZE 50M,
  GROUP 3 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\ARCH_D-RPTREPOS_ID-1456993145_S-3_T-1_A-929212411_00RMDGD8'  SIZE 50M
DATAFILE
  'C:\ORACLE\RPTREPOS\SYSTEM01.DBF',
  'C:\ORACLE\RPTREPOS\UNDOTBS01.DBF',
  'C:\ORACLE\RPTREPOS\SYSAUX01.DBF',
  'C:\ORACLE\RPTREPOS\USERS01.DBF',
  'C:\ORACLE\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 'C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\DATA_D-RPTREPOS_I-1456993145_TS-TEMP_FNO-1_00RMDGD8'
     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_00RMDGD8_1_0.ORA'
@@ ?/rdbms/admin/utlirp.sql
SHUTDOWN IMMEDIATE
STARTUP PFILE='C:\ORACLE\PRODUCT\10.2.0\DB_1\DATABASE\INIT_00RMDGD8_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>


拷贝源端参数文件,数据文件及上面一个脚本至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

###########################################
# Shared Server
###########################################
dispatchers="(PROTOCOL=TCP) (SERVICE=orclXDB)"

###########################################
# Miscellaneous
###########################################
compatible=10.2.0.3.0

###########################################
# Security and Auditing
###########################################
audit_file_dest=C:\oracle\product\10.2.0\admin\orcl\adump
remote_login_passwordfile=EXCLUSIVE

###########################################
# Archive
###########################################
log_archive_format=ARC%S_%R.%T

###########################################
# Sort, Hash Joins, Bitmap Indexes
###########################################
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    数据文件目录



调整参数文件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.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/rptrepos
[root@middle rptrepos]# 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 rptrepos]# chown oracleinstall *
[root@middle rptrepos]# 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

[oracle@middle ~]$ cat RPTREPOS.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='/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




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/oradata/rptrepos/SYSAUX01.DBF',
  '/u01/app/oracle/oradata/rptrepos/USERS01.DBF',
  '/u01/app/oracle/oradata/rptrepos/EXAMPLE01.DBF'
CHARACTER SET ZHS16GBK
;

Control file created.

SQL> exit

SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-01092: ORACLE instance terminated. Disconnection forced


日志:
Thu Dec 01 08:33:10 EST 2016
SMON: enabling cache recovery
Thu Dec 01 08:33:10 EST 2016
Errors in file /u01/app/oracle/admin/rptrepos/udump/rptrepos_ora_3285.trc:
ORA-00704: bootstrap process failure
ORA-39700: database must be opened with UPGRADE option
Thu Dec 01 08:33:10 EST 2016
Error 704 happened during db open, shutting down database
USER: terminating instance due to error 704
Instance terminated by USER, pid = 3285
ORA-1092 signalled during: ALTER DATABASE OPEN RESETLOGS...





[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 upgrade pfile='/u01/app/oracle/product/10.2.0/db_1/dbs/initrptrepos.ora';
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
Database mounted.
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> connect /as sysdba     
Connected.
SQL> recover database;
Media recovery complete.
SQL> select open_mode from v$database;

OPEN_MODE
----------
MOUNTED

SQL> alter database open upgrade;

Database altered.

SQL>
SQL> select open_mode from v$database;

OPEN_MODE
----------
READ WRITE




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

Tablespace altered.

@@ ?/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> col comp_name for a30;
SQL> col status for a30;
SQL> l
  1* select comp_name,status from dba_registry
SQL> /

COMP_NAME                      STATUS
------------------------------ ------------------------------
Oracle Database Catalog Views  VALID
Oracle Database Packages and T VALID
ypes

Oracle Workspace Manager       VALID
JServer JAVA Virtual Machine   VALID
Oracle XDK                     VALID
Oracle Database Java Packages  VALID
Oracle Expression Filter       VALID
Oracle Data Mining             VALID
Oracle Text                    VALID
Oracle XML Database            VALID
Oracle Rule Manager            VALID
Oracle interMedia              VALID
OLAP Analytic Workspace        VALID
Oracle OLAP API                VALID
OLAP Catalog                   VALID
Spatial                        VALID
Oracle Enterprise Manager      VALID
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 ~]$


最后调整监听:
[oracle@middle admin]$ pwd
/u01/app/oracle/product/10.2.0/db_1/network/admin
[oracle@middle admin]$ cat tnsnames.ora
# tnsnames.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = middle)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )
RPTREPOS =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = middle)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = rptrepos)
    )
  )

[oracle@middle admin]$ vi listener.ora
[oracle@middle admin]$ cat listener.ora
# listener.ora Network Configuration File: /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
# Generated by Oracle configuration tools.

SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = PLSExtProc)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (PROGRAM = extproc)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = orcl)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = orcl)
    )
    (SID_DESC =
      (GLOBAL_DBNAME = rptrepos)
      (ORACLE_HOME = /u01/app/oracle/product/10.2.0/db_1)
      (SID_NAME = rptrepos)
    )
  )

LISTENER =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = middle)(PORT = 1521))
    )
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC0))
    )
  )

[oracle@middle admin]$ lsnrctl start

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 01-DEC-2016 09:05:04

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Starting /u01/app/oracle/product/10.2.0/db_1/bin/tnslsnr: please wait...

TNSLSNR for Linux: Version 10.2.0.5.0 - Production
System parameter file is /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=middle)(PORT=1521)))
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=middle)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date                01-DEC-2016 09:05:04
Uptime                    0 days 0 hr. 0 min. 0 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=middle)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "LSExtProc" has 1 instance(s).
  Instance "LSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "rptrepos" has 1 instance(s).
  Instance "rptrepos", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
[oracle@middle admin]$ sqlplus system/oracle@rptrepos

SQL*Plus: Release 10.2.0.5.0 - Production on Thu Dec 1 09:05:14 2016

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


Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter system register;

System altered.

SQL> !lsnrctl status

LSNRCTL for Linux: Version 10.2.0.5.0 - Production on 01-DEC-2016 09:05:25

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=middle)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for Linux: Version 10.2.0.5.0 - Production
Start Date                01-DEC-2016 09:05:04
Uptime                    0 days 0 hr. 0 min. 21 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Listener Parameter File   /u01/app/oracle/product/10.2.0/db_1/network/admin/listener.ora
Listener Log File         /u01/app/oracle/product/10.2.0/db_1/network/log/listener.log
Listening Endpoints Summary...
  (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=middle)(PORT=1521)))
  (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
Services Summary...
Service "LSExtProc" has 1 instance(s).
  Instance "LSExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "orcl" has 1 instance(s).
  Instance "orcl", status UNKNOWN, has 1 handler(s) for this service...
Service "rptrepos" has 2 instance(s).
  Instance "rptrepos", status UNKNOWN, has 1 handler(s) for this service...
  Instance "rptrepos", status READY, has 1 handler(s) for this service...
Service "rptrepos_XPT" has 1 instance(s).
  Instance "rptrepos", status READY, has 1 handler(s) for this service...
Service "rptreposlXDB" has 1 instance(s).
  Instance "rptrepos", status READY, has 1 handler(s) for this service...
The command completed successfully

SQL>
标签: 暂无标签
dongxujian

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

转播转播 分享分享 分享淘帖
回复

使用道具

您需要登录后才可以回帖 登录 | 加入社区

本版积分规则

意见
反馈