DB-hub Technology Oracle Lab – DataGuard 11.4 upgrade to 12c
Oracle Lab – DataGuard 11.4 upgrade to 12c
1.升级的步骤
1）暂停主库向备库传输归档日志
2）先升级备库的数据库软件，备库只需要升级数据库软件，备库的数据库升级通过与主库同步完成。
3）升级主库的数据库软件
4）升级主库的数据库
5）同步主备库
DataGuard 搭建请参考相关实验。
Primary
Host Name: beijing
Database Version: 11.2.0.4
Parameter:
log_archive_config = dg_config=(dgdemo_beijing,dgde mo_shanghai)
log_archive_dest_state_2 = ENABLE
log_archive_dest_2 = SERVICE=shanghai LGWR ASYNC VA LID _FOR =(ONLINE_LOGFILES,PRIMA RY_ROLE) DB_UNIQUE_NAME=dgdemo _shanghai
fal_client= beijing
fal_server= shanghai
Standby
Host Name: shanghai
Database Version: 11.2.0.4
Parameter:
log_archive_config = dg_config=(dgdemo_beijing,dgde mo_shanghai)
log_archive_dest_state_2 = ENABLE
log_archive_dest_2 = SERVICE=beijing LGWR ASYNC VA LID _FOR =(ONLINE_LOGFILES,PRIMA RY_ROLE) DB_UNIQUE_NAME=dgdemo _beijing
fal_client= shanghai
fal_server= beijing
2.暂停主库向备库传输归档日志
alter system set log_archive_dest_state_2=defer scope=both;
3.升级备库数据库软件
3.1.关闭备库和Listener
lsnrctl stop
sqlplus / as sysdba
shutdown immediate;
3.2.安装Oracle 12c
创建新的$ORACLE_HOME目录
$ mkdir -p /u01/app/oracle/product/12.2.0.1/dbhome_1
参考相关实验安装Oracle软件。
修改ORACLE_HOME变量值为新的目录
vi ~/.bash_profile
export ORACLE_HOME=$ORACLE_BASE/product/12.2.0.1/dbhome_1
source .bash_profile
3.3.拷贝旧的文件到新的目录
拷贝旧版本的spfile,Password file和network file(tnsnames.ora listener.ora sqlnet.ora)等到新的目录：
--network file（tnsnames.ora listener.ora sqlnet.ora）
cp /u01/app/oracle/product/11.2.0.4/network/admin/*.ora /u01/app/oracle/product/12.2.0.1/dbhome_1/network/admin/
--SPFILE
cp /u01/app/oracle/product/11.2.0.4/dbs/ spfiledgdemo.ora /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/
--Password File
cp /u01/app/oracle/product/11.2.0.4/dbs/ orapwdgdemo /u01/app/oracle/product/12.2.0.1/dbhome_1/dbs/
3.4.备库启动到mount状态
SQL> startup mount
3.5.启动listener
修正DG listener.ora 中静态监听内容（ORACLE_HOME）
SID_LIST_LISTENER=
(SID_DESC=(GLOBAL_DBNAME = DGDEMO)(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/dbhome_1)(SID_NAME = DGDEMO)
)
停止旧的Listener启动新的Listener。
4.主库升级准备
4.1.主库安装Oracle 12c软件
请参考相关的实验
4.2.备份数据库
--rman.sql
more rman.sql
run{
allocate channel d1 TYPE disk;
allocate channel d2 TYPE disk;
allocate channel d3 TYPE disk;
allocate channel d4 TYPE disk;
backup as compressed backupset database format '/home/oracle/rman/data_%d_%T_%s.bak' plus archivelog format '/home/oracle/rman/arch_%d_%T_%s.bak';
release channel d1;
release channel d2;
release channel d3;
release channel d4;
}
--rman.sh
cat rman.sh
#!/bin/bash
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=ORACLE_BASE/product/11.2.0
export ORACLE_SID=orcl
export LD_LIBRARY_PATH=ORACLE_HOME/lib
export PATH=PATH:ORACLE_HOME/bin:$HOME/bin
rman target / @/home/oracle/rman/rman.sql log=/home/oracle/rman/rman.log
运行备份脚本
nohup sh rman.sh &
或者，创建Flashback restore point
create restore point uggrade_12c guarantee flashback database;
4.3.预升级检查
运行utlu112i.sql:
$ sqlplus / as sysdba
SQL> SPOOL upgrade_info.log
SQL> @/u01/app/oracle/product/11.2.0.4/dbhome_1/rdbms/admin/utlu112i.sql
SQL> SPOOL OFF
根据提示修正不符合升级条件的地方。
运行preupgrade.jar：
export NEW_HOME={ORACLE_BASE}/product/12.2.0.1/dbhome_1ORACLE_HOME/jdk/bin/java –jar $NEW_HOME/rdbms/admin/preupgrade.jar FILE TEXT
Preupgrade generated files:
/u01/app/oracle/cfgtoollogs/dgdemo_beijing/preupgrade/preupgrade.log
/u01/app/oracle/cfgtoollogs/dgdemo_beijing/preupgrade/preupgrade_fixups.sql
/u01/app/oracle/cfgtoollogs/dgdemo_beijing/preupgrade/postupgrade_fixups.sql
@/u01/app/oracle/cfgtoollogs/dgdemo_beijing/preupgrade/preupgrade_fixups.sql
4.4.编译无效对象
检查无效对象：
select count(*) from dba_objects where status<>'VALID';
select owner, object_name, object_type from dba_objects where status !='VALID' order by owner, object_name;
select count(*) from dba_objects where status='INVALID';
utlrp.sql脚本可以在数据库运行的状态下编译数据库中的无效对象：
@?/rdbms/admin/utlrp.sql
可以运行多次。
4.5.收集统计信息
exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_fixed_objects_stats;
4.6.清空回收站
select count(*) from DBA_RECYCLEBIN;
purge DBA_RECYCLEBIN;
4.7.拷贝旧的文件到新的目录
拷贝参数文件spfile， network file（tnsnames.ora listener.ora sqlnet.ora）， 密码文件等到新的目录。
修正DG listener.ora 中静态监听内容（ORACLE_HOME）
SID_LIST_LISTENER=
(SID_DESC=(GLOBAL_DBNAME = DGDEMO)(ORACLE_HOME = /u01/app/oracle/product/12.2.0.1/dbhome_1)(SID_NAME = DGDEMO)
)
停止旧的Listener启动新的Listener。
4.8.重启数据库到upgrade状态
cd $ORACLE_HOME/dbs
startup mount pfile='initdgdemo.ora'
create spfile from pfile;
shutdown immediate
startup upgrade
5.主库升级
5.1.运行catctl.pl
并行运行脚本(12c新特性)，并行度8,会启动16个并行服务器进程(默认并行度为4)
cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl –n 8 catupgrd.sql
or:
$ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
5.2.检查dba_registry
col comp_name for a45
col status for a15
select comp_name, status from dba_registry order by 1;
5.3.重新编译无效对像
cd $ORACLE_HOME/rdbms/admin
ls –l utlrp.sql
@utlrp.sql
5.4.再次检查dba_registry
col comp_name for a45
col status for a15
select comp_name, status from dba_registry order by 1;
5.5.重新收集stat
exec dbms_stats.gather_dictionary_stats;
exec dbms_stats.gather_fixed_objects_stats;
5.6.Bounce Database
shutdown immediate
startup
6.同步主备库
6.1.主库启用归档传输
show parameter log_archive_dest_state_2
alter system set log_archive_dest_state_2=enable scope=both;
show parameter log_archive_dest_state_2
6.2.备库开启日志应用
alter database open;
alter database recover managed standby database using current logfile disconnect from session;
6.3.检查备库主库升级情况
col comp_name for a45
col status for a15
select comp_name, status from dba_registry order by 1;
set linesize 350
col ACTION_TIME for a35
col VERSION for a15
col ACTION for a25
col NAMESPACE for a15
select ACTION_TIME,ACTION,NAMESPACE,VERSION,ID from registry$history;
ACTION_TIME ACTION NAMESPACE VERSION ID
--------------------------------------------------------------------------- ------------------------------
01-JUN-19 01.43.53.548057 PM VIEW INVALIDATE 8289601
01-JUN-19 01.43.53.601682 PM UPGRADE SERVER 11.2.0.4.0
6.4.升级旧的定时任务脚本
比如：定期删除归档的自动运行脚本，需要根据新环境的变量修改脚本中ORACLE_HOME变量值。
确认变量值为新版本的变量值。
6.5.设置compatible参数
只需要在主库设置：
alter system set compatible=’12.0.0’scope=spfile;
需要重启数据库。