當(dāng)前位置：首頁 > 编程资源 > 编程问答 >内容正文

编程问答

oracle修改10到20,Oracle 10.2.0.5 RMAN迁移并升级11.2.0.4一例

發(fā)布時間：2024/10/6 编程问答 41 豆豆

生活随笔收集整理的這篇文章主要介紹了 oracle修改10到20,Oracle 10.2.0.5 RMAN迁移并升级11.2.0.4一例小編覺得挺不錯的,現(xiàn)在分享給大家,幫大家做個參考.

一、環(huán)境介紹

1. 源數(shù)據(jù)庫環(huán)境

操作系統(tǒng)版本: OEL 5.4 x64

數(shù)據(jù)庫版本? : 10.2.0.5 x64

數(shù)據(jù)庫sid名 : orcl

Oracle 10g 10.2.0.5(64bit)安裝目錄如下：

數(shù)據(jù)庫軟件：/u01/app/oracle/product/10.2.0/db_1

數(shù)據(jù)庫文件：/u01/app/oracle/oradata/orcl

歸檔目錄：/u01/archivelog

RMAN目錄：/backup/dbbak/orabak

背景：一個老oracle10g數(shù)據(jù)庫,RMAN 50G, 不大，因服務(wù)器壞一塊盤，過保，計劃遷移數(shù)據(jù)庫并升級版本。

說明：源庫要做一些基礎(chǔ)環(huán)境的檢查，表空間，用戶名，數(shù)據(jù)文件以及是否有需要介質(zhì)恢復(fù)的文件。

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, /u

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. 目標(biāo)數(shù)據(jù)庫環(huán)境

操作系統(tǒng)版本: RHEL 6.5 x64

數(shù)據(jù)庫版本? : 11.2.0.4 x64

數(shù)據(jù)庫sid名 : orcl

原來有數(shù)據(jù)庫，采用DBCA將數(shù)據(jù)庫刪除掉。

Oracle 11g 11.2.0.4(64bit)安裝目錄如下(未創(chuàng)建數(shù)據(jù)庫)

數(shù)據(jù)庫軟件：/u01/app/oracle/product/11.2.0/db_1

數(shù)據(jù)庫文件：/u01/app/oracle/oradata/orcl

歸檔目錄：/u01/archivelog

RMAN目錄：/backup/dbbak/orabak

3. 升級思路

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

2)創(chuàng)建Oracle 11.2.0.4數(shù)據(jù)庫相關(guān)目錄。

3) 在Oracle 10.2.0.5x64數(shù)據(jù)庫上執(zhí)行Oracle 11.2.0.4 x64數(shù)據(jù)庫utlu112i.sql腳本。

4)備份Oracle 10.2.0.5x64數(shù)據(jù)庫，并傳到11g數(shù)據(jù)庫服務(wù)器上。

5)還原10g數(shù)據(jù)庫到11g庫上，并升級。

二、RMAN備份源10g數(shù)據(jù)庫

1. 在Oracle 10g庫上執(zhí)行utlu112i.sql腳本

道先需要將11g $ORACLE_HOME/rdbms/admin下的utlu112i.sql腳本傳到10g的$ORACLE_HOME/rdbms/admin目錄下并執(zhí)行。

并在10g上執(zhí)行，這個腳本可以檢查升級前的一些信息，必須要執(zhí)行，否則在恢復(fù)時會出現(xiàn)錯誤。

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

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

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 MB

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

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

#執(zhí)行過程中無錯誤,

#說明：應(yīng)用根據(jù)執(zhí)行的情況需要進(jìn)行如下處理。

(1)原庫存在無效對象，需要運行utlrp.sql 腳本，重新編譯無效對象

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

重新執(zhí)行 @/home/oracle/utlu112i.sql，查看是否重新編譯,

(2)執(zhí)行PURGE DBA_RECYCLEBIN

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.

(3)收集系統(tǒng)信息EXECUTE dbms_stats.gather_dictionary_stats;

在升級前收集字典統(tǒng)計信息，否則預(yù)升級工具( utlu102i.sql)會花費更長時間

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

2. 備份源數(shù)據(jù)庫

備份腳本內(nèi)容

#!/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=/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';

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

說明：記錄數(shù)據(jù)庫DBID(DBID=1349744318)，恢復(fù)時有用。

3. 將/u01/orabak目錄拷貝到11g數(shù)據(jù)庫服務(wù)器上。

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數(shù)據(jù)庫恢復(fù)準(zhǔn)備

1. 創(chuàng)建11g數(shù)據(jù)庫基本目錄

# 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. 創(chuàng)建規(guī)檔日志文件

mkdir -p /u01/archivelog

四、恢復(fù)數(shù)據(jù)庫

1. 恢復(fù)參數(shù)文件

說明：由于10g實例名與11g實例名與數(shù)據(jù)庫目錄一致，不需要手動創(chuàng)建參數(shù)文件與控制文件。

# 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的設(shè)定是否符合目的機(jī)器的配置

*.sga_target=629145600

*.pga_aggregate_target=25165824

刪除*.background_dump_dest和*.user_dump_dest

加上

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

修改后復(fù)制一份到$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啟動數(shù)據(jù)庫

[oracle@mholddb orabak]$ rman target /

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

connected to target database (not started)

RMAN> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initorcl.ora';

Oracle 實例已啟動

3. 恢復(fù)控制文件

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. 恢復(fù)數(shù)據(jù)庫

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