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>
|
|