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

or

 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

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

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;

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

最后编辑于 ：
©著作权归作者所有,转载或内容合作请联系作者
平台声明：文章内容（如有图片或视频亦包括在内）由作者上传并发布，文章内容仅代表作者本人观点，简书系信息发布平台，仅提供信息存储服务。

推荐阅读更多精彩内容