ORACLE12.2 升級之路之 05(10.2.0.5異機升级到11.2.0.4)

#五、 A机原库升级前检查

#将B机11.2.0.4的utlu112i.sql，scp到A机上，进行升级检查。

#5.1 从B机上传utlu112i.sql脚本到A机

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

#5.2 A机执行utlu112i.sql进行升级前检查

[oracle@oracle10g oracle]$ sqlplus / as sysdba

SQL> spool upgrade5.info

SQL> @/home/oracle/utlu112i.sql

SQL> spool off

#5.3 根据检查结果调整A机数据库

#5.3.1 表空间对比满足条件

select file_name,tablespace_name,maxbytes/1024/1024 from dba_data_files

where tablespace_name in('SYSTEM','UNDOTBS1','SYSAUX')

union all

select file_name,tablespace_name,maxbytes/1024/1024 from dba_temp_files;

#5.3.5 A机运行utlrp.sql 脚本，重新编译无效对象

重新编译SYS 和SYSTEM 中的无效对象

记录无效对象：

col OWNER for a10;

col OBJECT_NAME for a30;

col OBJECT_TYPE for a10;

select * from registry$nonsys_inv_objs; //非sys/system的失效对象

select * from registry$sys_inv_objs; //sys/system的失效对象

SQL> @/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlrp.sql;

#5.3.6 A机执行PURGE DBA_RECYCLEBIN

SQL> PURGE DBA_RECYCLEBIN;

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

SQL> spool upgrade6.info

SQL> @/home/oracle/utlu112i.sql

SQL> spool off

#5.3.7 A机收集系统信息EXECUTE dbms_stats.gather_dictionary_stats;

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

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

#注意：升级之后执行utluiobj.sql

#原庫DB備份

./backfull_skipreadonly.sh

mkdir $ORACLE_BASE/orabak

1.2 备份脚本内容 vim backupdb.sh

#!/bin/bash

basedir=/u01/app/oracle/orabak

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

#Create pfile

sqlplus / as sysdba <<EOF

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

EOF

#RMAN BACKUP

/u01/app/oracle/product/10.2.0/db_1/bin/rman target / log=$basedir/backup_all_$date.log << EOF

RUN {

crosscheck archivelog all;

delete noprompt expired archivelog all;

delete noprompt archivelog all completed before 'sysdate-1';

allocate channel for maintenance type disk;

delete noprompt obsolete device type disk;

release channel;

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

}

allocate channel for maintenance type disk;

delete noprompt obsolete device type disk;

release channel;

EXIT;

EOF

/u01/app/oracle/product/10.2.0/db_1/bin/rman target=sys/123456 log=$logfile << EOF

set encryption on identified by 'mpbak00001' only;

RUN {

crosscheck archivelog all;

delete noprompt expired archivelog all;

delete noprompt archivelog all completed before 'sysdate-1';

backup device type disk tag '%TAG' as compressed backupset database include current controlfile skip readonly;

backup device type disk tag '%TAG' as compressed backupset archivelog all not backed up;

}

allocate channel for maintenance type disk;

delete noprompt obsolete device type disk;

release channel;

EXIT;

EOF

# end

[oracle@XAG140 ~]$ chmod +x backupdb.sh

================================================

# 複製原庫DB備份到目標機器上

#八、目標機器机升级数据库

#8.1 修改恢复后的数据库为非归档模式

#为预防升级测试过程中默认的归档空间不够，这里升级先把库开启为非归档模式。

[oracle@XAG140 ~]$ sqlplus / as sysdba

SQL>

startup mount;

select status from v$instance;

alter database noarchivelog;

[oracle@XAG140 orabak]$ vim pfile20180824.ora

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

*.sga_target=629145600

*.pga_aggregate_target=25165824

删除*.background_dump_dest和*.user_dump_dest

加上

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

修改

*.db_recovery_file_dest='/u03/fast_recovery_area'

*.compatible='11.2.0.4.0'

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

[oracle@mholddb orabak]$ cp pfile20180824.ora /u01/app/oracle/product/11.2.0/db_1/dbs/initMPACC.ora

[oracle@XAG140 ~]$ rman target /

RMAN> shutdown immediate;

手工刪除臨時及重做日誌文件

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

set DBID=3165604684

configure device type sbt_tabe clear;

configure channel device type sbt_tabe clear;

set decryption identified by 'mpbak00001';

--catalog start with '/u01/app/oracle/fast_recovery_area/MPACC/backupset/2018_08_24/';

--RMAN> restore controlfile from '/u01/app/oracle/fast_recovery_area/MPACC/backupset/2018_08_24/o1_mf_ncsnf__TAG_fqyxw45g_.bkp';

3. 恢复控制文件

RMAN> restore controlfile from '/u01/app/oracle/orabak/ctl_MPACC_20180824_32_1';

RMAN>

alter database mount;

configure device type sbt_tabe clear;

configure channel device type sbt_tabe clear;

delete noprompt expired backupset;

crosscheck backupset;

RMAN> restore database; 【如有read only 表空間則先手工將文件拷貝到對應的目標目錄】; or [全備份用后面詞句] restore database check readonly;

RMAN> list backup of archivelog all;

List of Backup Sets

===================

BS Key Size Device Type Elapsed Time Completion Time

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

25 149.50K DISK 00:00:01 24-AUG-18

BP Key: 25 Status: AVAILABLE Compressed: NO Tag: TAG20180824T122744

Piece Name: /u01/app/oracle/orabak/arch_MPACC_20180824_31_1

List of Archived Logs in backup set 25

Thrd Seq Low SCN Low Time Next SCN Next Time

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

1 59 1004433 24-AUG-18 1004738 24-AUG-18

1 60 1004738 24-AUG-18 1004745 24-AUG-18

1 61 1004745 24-AUG-18 1004752 24-AUG-18

1 62 1004752 24-AUG-18 1004759 24-AUG-18

1 63 1004759 24-AUG-18 1004765 24-AUG-18

RMAN> recover database until scn 1004765; --可選1 （1 or 2 選其 1）

RMAN> exit;

[oracle@XAG140 ~]$ sqlplus / as sysdba

------------------------------------------------可選2------------------------------------------------------------------------------

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 973942 generated at 08/22/2018 15:21:06 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/oradata/MPACC/redo03.log

ORA-00280: change 973942 for thread 1 is in sequence #36

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

auto

Log applied.

Media recovery complete.

SQL>

SQL> recover database using backup controlfile until cancel;

ORA-00279: change 973942 generated at 08/22/2018 15:21:06 needed for thread 1

ORA-00289: suggestion : /u01/app/oracle/oradata/MPACC/redo03.log

ORA-00280: change 973942 for thread 1 is in sequence #36

Specify log: {<RET>=suggested | filename | AUTO | CANCEL}

cancel

Media recovery cancelled.

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

SQL> alter database open resetlogs upgrade;

2）重建temp临时表空间数据文件

SQL> alter tablespace temp add tempfile '/u01/app/oracle/oradata/MPACC/temp02.dbf' size 100m reuse autoextend on next 10m maxsize 1000m;

#手工升级库

SQL> SPOOL /home/oracle/update112.log

#重建数据字典

SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/catupgrd.sql

SQL> SPOOL OFF

7. 执行utlu112s.sql脚本

这个脚本显示升级过程的一个摘要。不需要在upgrade 模式下。

[oracle@mholddb ~]$ sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.4.0 Production on Mon Sep 18 21:03:52 2017

Connected to an idle instance.

SQL> startup;

SQL> exec dbms_stats.gather_dictionary_stats;

#编译无效对象

SQL> select count(*) from dba_invalid_objects;

SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlrp.sql

9.4 查看失效对象

SQL> select count(*) from dba_invalid_objects;

#检查升级后状态

#查看版本

SQL>select * from v$version;

9.5 运行升级后检查脚本

SQL> @/u01/app/oracle/product/11.2.0/db_1/rdbms/admin/utlu112s.sql

9.6 数据库层面应用PSU

cd $ORACLE_HOME/rdbms/admin/

[oracle@XAG140 admin]$ sqlplus / as sysdba

SQL> @catbundle.sql psu apply

检查opatch补丁相关信息：

#4.验证PSU补丁是否应用成功

[oracle@XAG140 OPatch]$ pwd

/u01/app/oracle/product/11.2.0/db_1/OPatch

[oracle@XAG140 OPatch]$

[oracle@XAG140 OPatch]$ ./opatch lspatches

27734982;Database Patch Set Update : 11.2.0.4.180717 (27734982)

OPatch succeeded.

[oracle@XAG140 OPatch]$ ./opatch lsinventory

9. 检查数据库状态

（1）检查组件状态

SQL> select comp_name,version, status from dba_registry;

（2）检查补丁状态

set line 150

col ACTION_TIME for a30

col ACTION for a15

col NAMESPACE for a8

col VERSION for a10

col BUNDLE_SERIES for a5

col COMMENTS for a30

select * from dba_registry_history;

10，其它生成参数文件

通过pfile创建spfile文件。

SQL> create spfile from pfile;

create spfile from pfile

ERROR at line 1:

ORA-32002: cannot create SPFILE already being used by the instance

SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initMPACC.ora';

ORACLE instance started.

Total System Global Area 906088448 bytes

Fixed Size 2258520 bytes

Variable Size 297798056 bytes

Database Buffers 599785472 bytes

Redo Buffers 6246400 bytes

ORA-00214: control file '/u01/app/oracle/oradata/MPACC/control01.ctl' version 2126 inconsistent with file

'/u01/app/oracle/oradata/MPACC/control03.ctl' version 2046

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL> exit

[oracle@XAG140 OPatch]$

[oracle@XAG140 OPatch]$ rm /u01/app/oracle/oradata/MPACC/control03.ctl

[oracle@XAG140 OPatch]$ cp /u01/app/oracle/oradata/MPACC/control01.ctl /u01/app/oracle/oradata/MPACC/control03.ctl

SQL> startup mount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initMPACC.ora';

SQL> create spfile from pfile;

SQL> shutdown immediate;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> startup;

11，生成密码文件

$ orapwd file='/u01/app/oracle/product/11.2.0/db_1/dbs/orapwMPACC' password=123456 entries=10 force=y

12，修改compatible参数：

SQL> --ALTER SYSTEM SET COMPATIBLE = '11.2.0.4.0' SCOPE=SPFILE;

13, Timezone数据库层面的升级，下面是手动执行情况，还有脚本升级方式，暂略。

注意：该步骤是否执行是和Step 6中的检查结果相关的，只有当Timezone的版本小于14时，才需要执行该步骤。

主要参考：Updating the RDBMS DST version in 11gR2 (11.2.0.1 and up) using DBMS_DST [ID 977512.1]

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

ORACLE12.2 升級之路之 06(10.2.0.5異機升级到11.2.0.4后Oracle TimeZone升级)

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

14. 如果在升级过程中遇到问题，可以重新执行升级脚本（参数）

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

1）Shut down the database as follows:

SQL>SHUTDOWN IMMEDIATE

2）Restart thedatabasein UPGRADE mode:

SQL> STARTUPUPGRADE

3）Rerun catupgrd.sql: --升级DB

SQL>@catupgrd.sql

4）Rerun utlu112s.sql: --报告升级的过程的摘要

SQL> @utlu112s.sql

5）迁移10g的Baseline 到11g

SQL> @?/rdbms/admin/catuppst.sql

6）编译无效对象：

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

注意：升级之后执行utluiobj.sql 列出失效对象的对比信息。

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

到此，已完成数据库异机升级操作。

最後 dbca 添加em 插件

------------------------10g 寫法--------------------------------------

select t.area,count(1)

,wm_concat(t.name1) as name1_list

from

(

select 'name1' as name1,'MP' as area from dual

union all

select 'name2' as name1,'MP' as area from dual

) t

group by t.area;

-------------------------11g寫法（升級後）-------------------------------------

select t.area,count(1)

,listagg(t.name1,',') within group(order by t.name1) as name1_list

from

(

select 'name1' as name1,'MP' as area from dual

union all

select 'name2' as name1,'MP' as area from dual

) t

group by t.area;

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

最后编辑于：2021.07.13 12:04:44

人面猴
序言：七十年代末，一起剥皮案震惊了整个滨河市，随后出现的几起案子，更是在滨河造成了极大的恐慌，老刑警刘岩，带你破解...
沈念sama阅读 194,761评论 5赞 460
死咒
序言：滨河连续发生了三起死亡事件，死亡现场离奇诡异，居然都是意外死亡，警方通过查阅死者的电脑和手机，发现死者居然都...
沈念sama阅读 81,953评论 2赞 371
救了他两次的神仙让他今天三更去死
文/潘晓璐我一进店门，熙熙楼的掌柜王于贵愁眉苦脸地迎上来，“玉大人，你说我怎么就摊上这事。” “怎么了？”我有些...
开封第一讲书人阅读 141,998评论 0赞 320
道士缉凶录：失踪的卖姜人
文/不坏的土叔我叫张陵，是天一观的道长。经常有香客问我，道长，这世上最难降的妖魔是什么？我笑而不...
开封第一讲书人阅读 52,248评论 1赞 263
港岛之恋（遗憾婚礼）
正文为了忘掉前任，我火速办了婚礼，结果婚礼上，老公的妹妹穿的比我还像新娘。我一直安慰自己，他们只是感情好，可当我...
茶点故事阅读 61,130评论 4赞 356
恶毒庶女顶嫁案：这布局不是一般人想出来的
文/花漫我一把揭开白布。她就那样静静地躺着，像睡着了一般。火红的嫁衣衬着肌肤如雪。梳的纹丝不乱的头发上，一...
开封第一讲书人阅读 46,145评论 1赞 272
城市分裂传说
那天，我揣着相机与录音，去河边找鬼。笑死，一个胖子当着我的面吹牛，可吹牛的内容都是我干的。我是一名探鬼主播，决...
沈念sama阅读 36,550评论 3赞 381
双鸳鸯连环套：你想象不到人心有多黑
文/苍兰香墨我猛地睁开眼，长吁一口气：“原来是场噩梦啊……” “哼！你这毒妇竟也来了？” 一声冷哼从身侧响起，我...
开封第一讲书人阅读 35,236评论 0赞 253
万荣杀人案实录
序言：老挝万荣一对情侣失踪，失踪者是张志新（化名）和其女友刘颖，没想到半个月后，有当地人在树林里发现了一具尸体，经...
沈念sama阅读 39,510评论 1赞 291
护林员之死
正文独居荒郊野岭守林人离奇死亡，尸身上长有42处带血的脓包…… 初始之章·张勋以下内容为张勋视角年9月15日...
茶点故事阅读 34,601评论 2赞 310
白月光启示录
正文我和宋清朗相恋三年，在试婚纱的时候发现自己被绿了。大学时的朋友给我发了我未婚夫和他白月光在一起吃饭的照片。...
茶点故事阅读 36,376评论 1赞 326
活死人
序言：一个原本活蹦乱跳的男人离奇死亡，死状恐怖，灵堂内的尸体忽然破棺而出，到底是诈尸还是另有隐情，我是刑警宁泽，带...
沈念sama阅读 32,247评论 3赞 313
日本核电站爆炸内幕
正文年R本政府宣布，位于F岛的核电站，受9级特大地震影响，放射性物质发生泄漏。R本人自食恶果不足惜，却给世界环境...
茶点故事阅读 37,613评论 3赞 299
男人毒药：我在死后第九天来索命
文/蒙蒙一、第九天我趴在偏房一处隐蔽的房顶上张望。院中可真热闹，春花似锦、人声如沸。这庄子的主人今日做“春日...
开封第一讲书人阅读 28,911评论 0赞 17
一桩弑父案，背后竟有这般阴谋
文/苍兰香墨我抬头看了看天上的太阳。三九已至，却和暖如春，着一层夹袄步出监牢的瞬间，已是汗流浃背。一阵脚步声响...
开封第一讲书人阅读 30,191评论 1赞 250
情欲美人皮
我被黑心中介骗来泰国打工，没想到刚下飞机就差点儿被人妖公主榨干…… 1. 我叫王不留，地道东北人。一个月前我还...
沈念sama阅读 41,532评论 2赞 342
代替公主和亲
正文我出身青楼，却偏偏与公主长得像，于是被迫代替她去往敌国和亲。传闻我的和亲对象是个残疾皇子，可洞房花烛夜当晚...
茶点故事阅读 40,739评论 2赞 335

ORACLE12.2 升級之路 之 05(10.2.0.5異機升级到11.2.0.4)

推荐阅读更多精彩内容

ORACLE12.2 升級之路之 05(10.2.0.5異機升级到11.2.0.4)