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*Plus: Release 10.2.0.4.0 - Production on Fri Dec 26 06:46:46 2014

Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.

Connected to:

Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

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;

    DBA Recyclebin purged.

#重新执行 @/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;

PL/SQL procedure successfully completed.

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

#原庫DB備份

[oracle@XAG140 ~]$ ./backfull_skipreadonly.sh

or

[oracle@XAG140 ~]$ mkdir $ORACLE_BASE/orabak

1.2 备份脚本内容 vim backupdb.sh

#!/bin/bash

#ScriptName:backup_all.sh

#Usage: backup all files in oracle user environment.

#ex: nohup /bin/bash backup_all.sh > backup.log &

#Author: Alfred Zhao

#Creation: 2015-09-11

#Version: 1.0.0

#Define variable <You may need to change the value of basedir.>

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

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

}

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*Plus: Release 11.2.0.4.0 Production on Fri Aug 24 09:27:46 2018

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

Connected to an idle instance.

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='/u01/app/oracle/fast_recovery_area'

    *.compatible='11.2.0.4.0'

修改后复制一份到$ORACLE_HOME/dbs/init$ORACLE_SID.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;

ORACLE instance started.

Total System Global Area 1603411968 bytes

Fixed Size                  2253664 bytes

Variable Size            570428576 bytes

Database Buffers        1023410176 bytes

Redo Buffers                7319552 bytes

Database mounted.

Database opened.

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;

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

Database mounted.

Database opened.

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

推荐阅读更多精彩内容