oracle转trs,Oracle 10.2.0.5 RMAN迁移并升级11.2.0.4一例

最新推荐文章于 2022-04-26 16:00:15 发布

如椽巨笔

最新推荐文章于 2022-04-26 16:00:15 发布

阅读量99

点赞数

文章标签： oracle转trs

一、环境介绍

1. 源数据库环境

操作系统版本: OEL 5.4 x64

数据库版本 : 10.2.0.5 x64

数据库sid名 : orcl

Oracle 10g 10.2.0.5(64bit)安装目录如下：

数据库软件：/u01/app/oracle/product/10.2.0/db_1

数据库文件：/u01/app/oracle/oradata/orcl

归档目录：/u01/archivelog

RMAN目录：/backup/dbbak/orabak

背景：一个老oracle10g数据库,RMAN 50G, 不大，因服务器坏一块盘，过保，计划迁移数据库并升级版本。

说明：源库要做一些基础环境的检查，表空间，用户名，数据文件以及是否有需要介质恢复的文件。

sqlplus / as sysdba >

dbstatus.log <

select instance_name, status from

v\$instance;

select dbid, open_mode from v\$database;

select file_name

from dba_data_files;

select file_name from dba_temp_files;

select member

from v\$logfile;

show parameter control

show parameter pfile

EOF

[oracle@db ~]$ cat dbstatus.log

SQL*Plus: Release 10.2.0.5.0 - Production on чǚ? 9? 18 14:40:57 2017

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>

INSTANCE_NAME STATUS

----------------

------------

orcl OPEN

SQL>

DBID OPEN_MODE

---------- ----------

1226188361 READ

WRITE

SQL>

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

/u01/app/oracle/oradata/orcl/trswcm_data01.dbf

/u01/app/oracle/oradata/orcl/trswcmvideo_data01.dbf

/u01/app/oracle/oradata/orcl/wtt.dbf

/u01/app/oracle/oradata/orcl/user02.dbf

/u01/app/oracle/oradata/orcl/jtbzoa

/u01/app/oracle/oradata/orcl/ELINK.dbf

/u01/app/oracle/oradata/orcl/USER03.dbf

/u01/app/oracle/oradata/orcl/user04.dbf

/u01/app/oracle/oradata/orcl/users05.dbf

14 rows selected.

SQL>

FILE_NAME

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/temp01.dbf

/u01/app/oracle/oradata/orcl/trswcm_temp01.dbf

/u01/app/oracle/oradata/orcl/trswcmvideo_temp01.dbf

SQL>

MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/oradata/orcl/redo03.log

/u01/app/oracle/oradata/orcl/redo02.log

/u01/app/oracle/oradata/orcl/redo01.log

SQL>

NAME TYPE

VALUE

------------------------------------ -----------

------------------------------

control_file_record_keep_time

integer 7

control_files string

/u01/app/oracle/oradata/orcl/c

ontrol01.ctl,

/u01/app/oracle/

oradata/orcl/control02.ctl,

01/app/oracle/oradata/orcl/con

trol03.ctl

SQL>

NAME TYPE

VALUE

------------------------------------ -----------

------------------------------

spfile string

/u01/app/oracle/product/10.2.0

/db_1/dbs/spfileorcl.ora

SQL> Disconnected from Oracle Database 10g

Enterprise Edition Release 10.2.0.5.0 - 64bit Production

With the

Partitioning, OLAP, Data Mining and Real Application Testing options

2. 目标数据库环境

操作系统版本: RHEL 6.5 x64

数据库版本 : 11.2.0.4 x64

数据库sid名 : orcl

原来有数据库，采用DBCA将数据库删除掉。

Oracle 11g 11.2.0.4(64bit)安装目录如下(未创建数据库)

数据库软件：/u01/app/oracle/product/11.2.0/db_1

数据库文件：/u01/app/oracle/oradata/orcl

归档目录：/u01/archivelog

RMAN目录：/backup/dbbak/orabak

3. 升级思路

1)Oracle 10.2.0.2 以后才可以升级到11g 11.2.0.4。

2)创建Oracle 11.2.0.4数据库相关目录。

在Oracle 10.2.0.5x64数据库上执行Oracle 11.2.0.4 x64数据库utlu112i.sql脚本。

4)备份Oracle

10.2.0.5x64数据库，并传到11g数据库服务器上。

5)还原10g数据库到11g库上，并升级。

二、RMAN备份源10g数据库

1. 在Oracle 10g库上执行utlu112i.sql脚本

道先需要将11g

$ORACLE_HOME/rdbms/admin下的utlu112i.sql脚本传到10g的$ORACLE_HOME/rdbms/admin目录下并执行。

并在10g上执行，这个脚本可以检查升级前的一些信息，必须要执行，否则在恢复时会出现错误。

scp $ORACLE_HOME/rdbms/admin/utlu112i.sql 192.168.0.96:/home/oracle/

SQL> spool upgrade.info

SQL> @/home/oracle/utlu112i.sql

Oracle Database 11.2 Pre-Upgrade Information Tool 09-18-2017

14:26:54

Script Version: 11.2.0.4.0 Build:

007

**********************************************************************

Database:

**********************************************************************

-->

name: ORCL

--> version: 10.2.0.5.0

--> compatible:

10.2.0.3.0

--> blocksize: 8192

--> platform: Linux x86

64-bit

--> timezone file:

**********************************************************************

Tablespaces:

[make adjustments in the current

environment]

**********************************************************************

-->

SYSTEM tablespace is adequate for the upgrade.

.... minimum required size:

1480 MB

--> UNDOTBS1 tablespace is adequate for the upgrade.

....

minimum required size: 400 MB

--> SYSAUX tablespace is adequate for the

upgrade.

.... minimum required size: 837 MB

--> TEMP tablespace is

adequate for the upgrade.

.... minimum required size: 60

**********************************************************************

Flashback:

OFF

**********************************************************************

Update

Parameters: [Update Oracle Database 11.2 init.ora or spfile]

Note:

Pre-upgrade tool was run on a lower version 64-bit

database.

**********************************************************************

-->

If Target Oracle is 32-Bit, refer here for Update Parameters:

-- No update

parameter changes are required.

--> If Target Oracle is 64-Bit, refer here for Update Parameters:

-- No

update parameter changes are

required.

**********************************************************************

Renamed

Parameters: [Update Oracle Database 11.2 init.ora or

spfile]

**********************************************************************

No renamed parameters found. No changes are

required.

**********************************************************************

Obsolete/Deprecated

Parameters: [Update Oracle Database 11.2 init.ora or

spfile]

**********************************************************************

-->

background_dump_dest 11.1 DEPRECATED replaced by

"diagnostic_dest"

--> user_dump_dest 11.1 DEPRECATED

replaced by "diagnostic_dest"

**********************************************************************

Components:

[The following database components will be upgraded or

installed]

**********************************************************************

-->

Oracle Catalog Views [upgrade] VALID

--> Oracle Packages and

Types [upgrade] VALID

--> JServer JAVA Virtual Machine [upgrade]

VALID

--> Oracle XDK for Java [upgrade] VALID

--> Oracle

Workspace Manager [upgrade] VALID

--> OLAP Analytic Workspace

[upgrade] VALID

--> OLAP Catalog [upgrade]

VALID

--> Oracle Text [upgrade] VALID

--> Oracle

XML Database [upgrade] VALID

--> Oracle Java Packages

[upgrade] VALID

--> Oracle interMedia [upgrade]

VALID

--> Spatial [upgrade] VALID

--> Data

Mining [upgrade] VALID

--> Expression Filter

[upgrade] VALID

--> Rule Manager [upgrade]

VALID

--> Oracle OLAP API [upgrade]

VALID

**********************************************************************

Miscellaneous

Warnings

**********************************************************************

WARNING:

--> Database is using a timezone file older than version 14.

.... After

the release migration, it is recommended that DBMS_DST package

.... be used

to upgrade the 10.2.0.5.0 database timezone version

.... to the latest

version which comes with the new release.

WARNING: --> Database contains

INVALID objects prior to upgrade.

.... The list of invalid SYS/SYSTEM objects

was written to

.... registry$sys_inv_objs.

.... The list of non-SYS/SYSTEM

objects was written to

.... registry$nonsys_inv_objs.

.... Use

utluiobj.sql after the upgrade to identify any new invalid

.... objects due

to the upgrade.

.... USER FKSOA has 3 INVALID objects.

.... USER ZWPORTAL

has 1 INVALID objects.

.... USER XCJ has 1 INVALID objects.

.... USER

XCJOANEW has 1 INVALID objects.

.... USER EDTEST has 1 INVALID

objects.

.... USER NYTOA has 1 INVALID objects.

WARNING: --> Your

recycle bin is turned on and currently contains no objects.

.... Because it

is REQUIRED that the recycle bin be empty prior to upgrading

.... and your

recycle bin is turned on, you may need to execute the command:

PURGE

DBA_RECYCLEBIN

.... prior to executing your upgrade to confirm the recycle

bin is empty.

WARNING: --> JOB_QUEUE_PROCESS value must be updated

....

Your current setting of "10" is too low.

.... Starting with Oracle Database 11g Release 2 (11.2), setting

....

JOB_QUEUE_PROCESSES to 0 causes both DBMS_SCHEDULER and

.... DBMS_JOB jobs to

not run. Previously, setting JOB_QUEUE_PROCESSES

.... to 0 caused DBMS_JOB

jobs to not run, but DBMS_SCHEDULER jobs were

.... unaffected and would still

run. This parameter must be updated to

.... a value greater than 16 (default

value is 1000) prior to upgrade.

.... Not doing so will affect the running of

utlrp.sql after the

upgrade

**********************************************************************

Recommendations

**********************************************************************

Oracle

recommends gathering dictionary statistics prior to

upgrading the

database.

To gather dictionary statistics execute the following

command

while connected as SYSDBA:

EXECUTE dbms_stats.gather_dictionary_stats;

**********************************************************************

SQL>

SQL> spool off

#执行过程中无错误,

#说明：应用根据执行的情况需要进行如下处理。

(1)原库存在无效对象，需要运行utlrp.sql 脚本，重新编译无效对象

SQL> @?/rdbms/admin/utlrp.sql;

重新执行 @/home/oracle/utlu112i.sql，查看是否重新编译,

(2)执行PURGE DBA_RECYCLEBIN

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

(3)收集系统信息EXECUTE dbms_stats.gather_dictionary_stats;

在升级前收集字典统计信息，否则预升级工具( utlu102i.sql)会花费更长时间

SQL> EXECUTE

dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully

completed.

2. 备份源数据库

备份脚本内容

#!/bin/bash

# ScriptName:rmanbakup.sh

# Usage: backup all files in

oracle user environment.

# Author: koumm

# Creation: 2017-07-31

Version: 1.0.0

#Define variable

basedir.>

basedir=/u01/orabak

date=`date +%Y%m%d`

#Create pfile

sqlplus / as sysdba <

create

pfile='$basedir/pfile$date.ora' from spfile;

EOF

#RMAN BACKUP

rman target / log=$basedir/backup_all_$date.log

run{

allocate channel c1 device type disk;

allocate channel

c2 device type disk;

backup database filesperset 4 format

'$basedir/full_%d_%T_%s_%p';

sql 'alter system archive log current';

sql

'alter system archive log current';

sql 'alter system archive log

current';

sql 'alter system archive log current';

backup archivelog all

format '$basedir/arch_%d_%T_%s_%p' delete input;

backup current controlfile

format '$basedir/ctl_%d_%T_%s_%p';

release channel c1;

release channel

c2;

}

EOF

说明：记录数据库DBID(DBID=1349744318)，恢复时有用。

3. 将/u01/orabak目录拷贝到11g数据库服务器上。

scp -r orabak xxxx:/u01/

[root@mholddb orcl]# ll -h /backup/dbbak/orabak/

总用量 55G

-rw-r----- 1

oracle oinstall 42M 9月 18 20:07 arch_ORCL_20170918_17_1

-rw-r----- 1 oracle

oinstall 13M 9月 18 20:15 arch_ORCL_20170918_18_1

-rw-r----- 1 oracle

oinstall 3.0K 9月 18 20:07 arch_ORCL_20170918_19_1

-rw-r--r-- 1 oracle

oinstall 6.2K 9月 18 20:12 backup_all_20170918.log

-rw-r----- 1 oracle

oinstall 9.9M 9月 18 20:07 ctl_ORCL_20170918_20_1

-rw-r----- 1 oracle

oinstall 20G 9月 18 20:11 full_ORCL_20170918_11_1

-rw-r----- 1 oracle

oinstall 3.1G 9月 18 20:12 full_ORCL_20170918_12_1

-rw-r----- 1 oracle

oinstall 18G 9月 18 20:07 full_ORCL_20170918_13_1

-rw-r----- 1 oracle

oinstall 14G 9月 18 20:15 full_ORCL_20170918_14_1

-rw-r----- 1 oracle

oinstall 9.9M 9月 18 20:07 full_ORCL_20170918_15_1

-rw-r----- 1 oracle

oinstall 96K 9月 18 20:03 full_ORCL_20170918_16_1

-rw-r--r-- 1 oracle

oinstall 1.1K 9月 18 20:21 pfile20170918.ora

三、11g数据库恢复准备

1. 创建11g数据库基本目录

# su - oracle

mkdir -p /u01/app/oracle/admin/orcl/{adump,dpdump,pfile,scripts}

mkdir

-p /u01/app/oracle/oradata/orcl

mkdir -p

/u01/app/oracle/flash_recovery_area/ORCL

2. 创建规档日志文件

mkdir -p /u01/archivelog

四、恢复数据库

1. 恢复参数文件

说明：由于10g实例名与11g实例名与数据库目录一致，不需要手动创建参数文件与控制文件。

# vi pfile20170801.ora

orcl.__db_cache_size=1056964608

orcl.__java_pool_size=16777216

orcl.__large_pool_size=16777216

orcl.__shared_pool_size=503316480

orcl.__streams_pool_size=0

*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'

*.compatible='10.2.0.3.0'

*.control_files='/u01/app/oracle/oradata/orcl/control01.ctl','/u01/app/oracle/oradata/orcl/control02.ctl','/u01/app/oracle/oradata/orcl/control03.ctl'

*.core_dump_dest='/u01/app/oracle/admin/orcl/cdump'

*.db_block_size=8192

*.db_domain=''

*.db_file_multiblock_read_count=16

*.db_name='orcl'

*.db_recovery_file_dest='/u01/app/oracle/flash_recovery_area'

*.db_recovery_file_dest_size=2147483648

*.dispatchers='(PROTOCOL=TCP)

(SERVICE=orclXDB)'

*.fast_start_mttr_target=60

*.job_queue_processes=10

*.log_archive_dest_1='location=/u01/archivelog'

*.nls_territory='CHINA'

*.open_cursors=3000

*.pga_aggregate_target=848297984

*.processes=500

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_target=1610612736

*.undo_management='AUTO'

*.undo_tablespace='UNDOTBS1'

*.diagnostic_dest='/u01/app/oracle'

注意下sga和pga的设定是否符合目的机器的配置

*.sga_target=629145600

*.pga_aggregate_target=25165824

删除*.background_dump_dest和*.user_dump_dest

加上

*.diagnostic_dest='/u01/app/oracle'

修改后复制一份到$ORACLE_HOME/dbs/init$ORACLE_SID.ora

[oracle@mholddb orabak]$ cp pfile20170918.ora

/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora

2. nomount启动数据库

[oracle@mholddb orabak]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Sep 18 20:22:21

2017

reserved.

connected to target database (not started)

RMAN> startup nomount

pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora';

Oracle 实例已启动

3. 恢复控制文件

RMAN> restore controlfile from

'/backup/dbbak/orabak/ctl_ORCL_20170918_20_1';

Starting restore at 18-SEP-17

using target database control file instead

of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1:

SID=197 device type=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore

complete, elapsed time: 00:00:01

output file

name=/u01/app/oracle/oradata/orcl/control01.ctl

output file

name=/u01/app/oracle/oradata/orcl/control02.ctl

output file

name=/u01/app/oracle/oradata/orcl/control03.ctl

Finished restore at

18-SEP-17

RMAN>

4. 恢复数据库

RMAN> shutdown immediate;

Oracle instance shut down

原备份日志backup_all_20170801.log中有DBID记录。

RMAN> set DBID=1226188361

executing command: SET DBID

RMAN> startup mount;

connected to target database (not started)

Oracle instance

started

database mounted

Total System Global Area 1219260416 bytes

Fixed Size 2252744 bytes

Variable Size

704643128 bytes

Database Buffers 503316480 bytes

Redo

Buffers 9048064 bytes

RMAN>

RMAN> delete noprompt expired backupset;

RMAN> crosscheck

backupset;

using channel ORA_DISK_1