四
升级逻辑
dg
至
12c
4.1 执行preupgrade.jar 升级预检
12c 用 preupgrade.jar 替换了 utlu112i.sql 来执行预检工作，这个 java 程序会生成两个 sql 脚本文件来修复一些简单的问题，比如提前收集字典统计信息之类，但是很多仍然需要手工操作
preupgrade.jar 语法如下
$Earlier_release_Oracle_home/jdk/bin/java -jar $New_release_Oracle_home/rdbms/admin/preupgrade.jar [FILE|TERMINAL] [TEXT|XML] [DIR output_dir]
FILE|TERMINAL 指定检查结果输出到终端还是文件，默认是文件
TEXT|XML 指定结果格式是文本还是XML ，默认是文本
DIR -
指定日志输出到
export ORACLE_BASE=/oracle/ora11204 注意要用 11g 的 jdk 来执行
export ORACLE_HOME=$ORACLE_BASE/db_1
export ORACLE_SID=tt
$ORACLE_HOME/jdk/bin/java -jar /oracle/12c/product/12.2.0/dbhome_1/rdbms/admin/preupgrade.jar file text dir ./precheck.log
Preupgrade generated files:
/home/oracle/precheck.log/preupgrade.log 检查结果
/home/oracle/precheck.log/preupgrade_fixups.sql 升级前预检问题修复脚本，检查结果中标记为AUTOFIXUP 的可以通过这个修复
/home/oracle/precheck.log/postupgrade_fixups.sql 升级后问题修复脚本
这里检查出以下问题
+ Update NUMERIC INITIALIZATION PARAMETERS to meet estimated minimums.
Parameter 12.2.0.1.0 minimum
--------- ------------------
processes 300
建议删除 sec_case_sensitive_logon 参数
删除em 组件减少升级停机时间（升级过程会自动删除，但是建议提前做掉）
Copy the $ORACLE_HOME/rdbms/admin/emremove.sql script from the target
12.2.0.1.0 ORACLE_HOME into the source 11.2.0.4.0 ORACLE_HOME.
Step 1: If database control is configured, stop EM Database Control,
using the following command
$> emctl stop dbconsole
Step 2: Connect to the database using the SYS account AS SYSDBA
SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql
Remove OLAP Catalog by running the 11.2.0.4.0 SQL script
$ORACLE_HOME/olap/admin/catnoamd.sql script.
The OLAP Catalog component, AMD, exists in the database.
Starting with Oracle Database 12c, the OLAP Catalog (OLAP AMD) is
desupported and will be automatically marked as OPTION OFF during the
database upgrade if present. Oracle recommends removing OLAP Catalog
(OLAP AMD) before database upgrade.
(AUTOFIXUP) Gather stale data dictionary statistics prior to database
upgrade in off-peak time using:
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
4.2 收集数据字典统计信息
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
4.3 确认物化视图都已停止刷新
SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s
WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
4.4 确认数据文件不需要介质恢复且不处于backup 模式
select * from v$recover_file;
SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
4.5 处理分布式事务
SQL> SELECT * FROM DBA_2PC_PENDING;
IF THIS RETURNS ROWS YOU SHOULD DO THE FOLLOWING:
SQL> SELECT LOCAL_TRAN_ID FROM DBA_2PC_PENDING;
SQL> EXECUTE DBMS_TRANSACTION.PURGE_LOST_DB_ENTRY('');
SQL> COMMIT;
4.6 创建dblink 的脚本
（ in case the database has to be downgraded again ）
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10)
||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10)
||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING
'''||L.HOST||''''
||chr(10)||';' TEXT
FROM SYS.LINK$ L, SYS.USER$ U
WHERE L.OWNER# = U.USER#;
4.7 清理回收站
PURGE DBA_RECYCLEBIN;
4.7 收集em 信息
emdwgrd -save -sid tt -path /home/oracle
降级回退的时候需要
另外，建议升级 12c 之前先删除 em 组件，减少升级时间
复制 12c 的 emremove.sql 脚本到 /tmp 目录
cp /oracle/12c/product/12.2.0/dbhome_1/rdbms/admin/emremove.sql /tmp
SET ECHO ON;
SET SERVEROUTPUT ON;
@/tmp/emremove.sql
4.8 修改必要参数
vi initorcl.ora
*.audit_file_dest='/oracle/12c/admin/orcl/adump'
*.audit_trail='NONE'
*.compatible='11.2.0.4.0'
*.control_files='/oracle/ora11204/oradata/orcl/std'
*.db_block_size=8192
*.db_domain=''
*.db_name='orcl'
*.diagnostic_dest='/oracle/12c'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.fal_client='DG'
*.fal_server='prod'
*.log_archive_dest_1='location=/oracle/ora11204/arch valid_for=(online_logfiles,all_roles)' 这个用来存放逻辑dg 写操作产生的归档
*.log_archive_dest_2='service=prod valid_for=(online_logfile,primary_role)'
*.log_archive_dest_3='LOCATION=/oracle/ora11204/arch/std VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)' 这个是为了接收生产传过来的归档
*.open_cursors=300
*.pga_aggregate_target=1203765248
*.processes=500
*.remote_login_passwordfile='EXCLUSIVE'
*.sga_target=3613392896
*.undo_tablespace='UNDOTBS1'
*.db_recovery_file_dest='/oracle/ora11204/fast_recovery_area'
*.db_recovery_file_dest_size='20G'
[oracle@backup ~]$ mkdir -p /oracle/12c/admin/orcl/adump
4.9 删除olap 组件
SQL> @?/olap/admin/catnoamd.sql
SQL> select distinct(trunc(last_refresh)) from dba_snapshot_refresh_times;
SQL>
select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ s
where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;
4.10 检查ADMINISTER DATABASE TRIGGER 权限用户
如果用户创建了数据库级别的触发器，则必须要拥有 ADMINISTER DATABASE TRIGGER 权限，但是创建完之后该权限可能被回收
SELECT OWNER, TRIGGER_NAME FROM DBA_TRIGGERS WHERE BASE_OBJECT_TYPE='DATABASE' AND
OWNER NOT IN (SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE
PRIVILEGE='ADMINISTER DATABASE TRIGGER');
grant ADMINISTER DATABASE TRIGGER to xxxx;
4.11 升级APEX
为了减少升级时间，可以提前升级 apex ，本例没有提前升级，在数据库升级过程中一起升级
The database contains APEX version 3.2.1.00.12 and will need to be upgraded to at least version 5.0.4.00.12.
To reduce database upgrade time, you can upgrade APEX manually before the database upgrade. Refer to My Oracle Support Note 1088970.1 for information on APEX installation upgrades.
|
----- 升级aplex: 下载对应安装包（ apex_5.0.4.zip ）， metlink 1088970.1 -- 升级apex 插件 cd /home/oracle/apex/ @apexins.sql sysaux sysaux temp /i/ select * from all_users; -- 配置，/home/oracle 为apex 的上层目录 @apex_epg_config.sql /home/oracle alter user anonymous account unlock; http://localhost:8080/apex/apex_admin admin/Root!@#123
卸载：
drop user APEX_050000 cascade; drop user FLOWS_FILES cascade; drop user APEX_PUBLIC_USER cascade;
|
4.12 升级数据库刷数据字典
以 12c 软件启动逻辑 dg
SQL> startup upgrade
nohup dbupgrade -oracleHome /ora12c/app/product/12.2.0/db_1 &
备注： Died at /ora12c/app/product/12.2.0/db_1/rdbms/admin/ catctl.pl line 7822.
报错，解决方法：
|
[ora12c@std ~]$ cd /ora12c/app/product/12.2.0/db_1/bin/ [ora12c@std bin]$ nohup ./dbupgrade & [ora12c@std bin]$ nohup: ignoring input and appending output to `nohup.out' |
4.13 重新打开数据库刷无效对象
Sqlplus / as sysdba
SQL> create spfile from pfile;
SQL> Startup
组件状态检查：
SQL> select comp_name,status,version from dba_registry;
修复无效对象：
[oracle@backup ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@backup admin]$ $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql
此时 dba_registry 内所有组件状态从 UPGRADED 变为 VALID
刷完之后对比之前的无效对象进行确认
select owner,object_name from dba_objects where status<> 'VALID' and object_name not in (select object_name from invalid_object_2019);
执行预检生成的修复脚本，包括重新收集 x$ 基表和字典 统计信息等
SQL> @/home/oracle/precheck.log/postupgrade_fixups.sql
4.14 复制tnsnames.ora 和密码文件到12c 环境
[oracle@high admin]$ cp /oracle/ora11204/db_1/network/admin/tnsnames.ora .
[oracle@high dbs]$ cp /oracle/ora11204/db_1/dbs/orapworcl .
五 第一次主备切换
5.1 启用sql apply 追平数据
逻辑 dg 再次启用 sql apply
SQL> ALTER DATABASE START LOGICAL STANDBY APPLY IMMEDIATE;
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'yy-mm-dd HH24:MI:SS';
SQL> SELECT SYSDATE, APPLIED_TIME FROM V$LOGSTDBY_PROGRESS;
SYSDATE APPLIED_TIME
----------------- -----------------
17-04-18 17:38:38 17-04-18 05:47:34
监控以上 SQL APPLY 进度，是否与当前时间接近，可以通过一个测试表来检查
5.2 处理不受 sql apply 支持的表
这里正式关闭应用，确保生产库没有连接
set lin 200
SET LONG 1000
SET PAGESIZE 180
SET LINESIZE 79
col EVENT_TIMESTAMP for a30;
col event for a30
col status for a30
SELECT EVENT_TIMESTAMP, EVENT, STATUS FROM DBA_LOGSTDBY_EVENTS ORDER BY EVENT_TIMESTAMP;
审查升级过程中的监控事件 , 在逻辑 dg 上查询 DBA_LOGSTDBY_EVENTS 视图，该视图记录了在逻辑备库上没有 applied 的 DDL 与 DML 操作
ORA-16226 错误表示相关 DDL 操作在逻辑备库上不受支持，常见的原因是该类操作涉及内部对象
ORA-16129 错误表示相关 DML 操作在逻辑备库上不受支持
如果有业务表出现上面的错误，则通过 impdp 将生产数同步到逻辑 dg
Impdp \”/ as sysdba\” NETWORK_LINK=databasea TABLES=scott.emp TABLE_EXISTS_ACTION=TRUNCATE
5.3 切换
生产状态
SQL> SELECT database_role,SWITCHOVER_STATUS FROM V$DATABASE;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
PRIMARY SESSIONS ACTIVE
|
注： SQL> alter system set log_archive_dest_3='LOCATION=/oradata/stdlog VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)' scope=both; SQL> alter system set log_archive_dest_state_3=enable;
|
逻辑 dg 状态
SQL> SELECT database_role,SWITCHOVER_STATUS FROM V$DATABASE;
DATABASE_ROLE SWITCHOVER_STATUS
---------------- --------------------
LOGICAL STANDBY NOT ALLOWED
将主库切换成逻辑 dg ，这条命令会等待事务完成
ALTER DATABASE COMMIT TO SWITCHOVER TO LOGICAL STANDBY;
切换完成之后，主备库 DATABASE_ROLE 都变成了 LOGICAL STANDBY ，将原先的逻辑 dg 切换成主库
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY;
现在由原逻辑 dg （ 12c 版本）接管应用
六 升级原生产库（当前的逻辑dg ）
6.1 闪回原生产
将原主库闪回到之前创建的还原点上（ 2.1 步骤创建的）
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP MOUNT;
SQL> flashback database to restore point pre_upgrade;
SQL> SHUTDOWN IMMEDIATE;
复制先生产库下 dbs 目录下密码文件和 network/admin 中的 listener.ora 、 tnsnames.ora 至新的 12c 目录中
6.2 准备参数文件
先从当前生产（原逻辑 dg ）拷贝相关文件
$ scp initorcl.ora orapworcl 192.168.200.34:/oracle/12c/product/12.2.0/dbhome_1/dbs
$ vi initorcl.ora 修改如下内容
*.control_files='/oracle/ora11204/oradata/orcl/control01.ctl'
*.fal_client='PROD'
*.fal_server='dg'
*.log_archive_dest_1='location=/oracle/ora11204/arch valid_for=(online_logfiles,all_roles)'
*.log_archive_dest_2='service=dg valid_for=(online_logfile,primary_role)'
*.log_archive_dest_3='LOCATION=/oracle/ora11204/arch/std VALID_FOR=(STANDBY_LOGFILES,STANDBY_ROLE)'
6.3 用12c 数据库软件加载原生产库
$ cp nsnames.ora /oracle/12c/product/12.2.0/dbhome_1/network/admin/
$ mkdir /oracle/12c/admin/orcl/adump
$ startup mount;
停止 11g 监听，启动 12c 监听
6.4 将原生产库切换回物理备库
SQL> ALTER DATABASE CONVERT TO PHYSICAL STANDBY;
转换完成后归档全部接收过来了
SQL> SHUTDOWN IMMEDIATE;
重启后开始追原逻辑 dg 在 12c 软件读写打开后产生的归档
SQL> STARTUP MOUNT;
SQL> RECOVER MANAGED STANDBY DATABASE DISCONNECT;
注： ORA-38760: This database instance failed to turn on flashback database 报错
解决：
|
create spfile from pfile; shutdown immediate; startup mount; RECOVER MANAGED STANDBY DATABASE DISCONNECT; |
等归档追平，就表示升级完成
6.5 第二次主备切换 （视需求而定）
第二次停机，主备物理切换，回到升级前的状态（ 12c 下），按需求来做（如果备机性能跟原生产相当则可以不做）
当前生产
SQL> SELECT database_role,SWITCHOVER_STATUS FROM V$DATABASE;
SQL> alter database commit to switchover to physical standby;
完成后自动关闭
SQL> startup
SQL> recover managed standby database using current logfile disconnect;
物理 dg
SQL> alter database commit to switchover to primary;
SQL> alter database open;
测试日志是否实时应用 .
6.6 删除强制闪回点
现主库删除强制闪回点
SQL> DROP RESTORE POINT PRE_UPGRADE;
确认参数 compatible 是否正确。
七 升级失败的回退措施
7.1 升级逻辑dg 失败
如果在第四大步升级逻辑 dg 至 12c 失败，则直接用 3.1 创建的闪回点闪回升级开始前的状态，生产不用做任何操作。
SQL>startup mount
SQL>flashback database to restore point pre_upgrade;
SQL>shutdown immediate
SQL>startup mount;
SQL>recover managed standby database disco