oracle迁移升级11.2.0.1到11.2.0.4_oracle 11.2.0.4

有一个测试数据库因为操作系统过于老旧，需要迁移升级。老的版本是11.2.0.1 ，打算升级到11.2.0.4版本

操作系统 
Oracle Linux 6(64 位)  => AnolisOS-7.9

数据库版本
oracle 11.2.0.1  => oracle 11.2.0.4

步骤如下：

1、新环境搭建11.2.0.4版本数据库

2、通过rman备份还原，将旧库的配置文件，在新库还原数据库

3、操作数据库升级

本文参考了以下文章

RMAN】Oracle11g使用rman迁移升级数据库（win_to_linux）

1 新环境搭建

修改主机名称

安装依赖包

yum install binutils compat-libstdc++-33 elfutils-libelf elfutils-libelf-devel glibc glibc-common glibc-devel gcc- gcc-c++ libaio-devel libaio libgcc libstdc++ libstdc++-devel make sysstat unixODBC unixODBC-devel pdksh ksh compat-libcap1

关闭防火墙

systemctl stop firewalld
 
systemctl disable firewalld

创建数据库组

groupadd oinstall
groupadd dba
groupadd oper
useradd -g oinstall -G dba,oper oracle

创建目录并且授权

mkdir -p /u01/app/11.2.0/grid
mkdir -p /u01/app/grid
mkdir -p /u01/app/oracle/product/11.2.0/db_1
chown -R oracle:oinstall /u01/app

设置用户环境变量

oracle用户登陆: vi ~/.bash_profile
 
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:.
export NLS_LANG=american_america.ZHS16GBK
export ORACLE_SID=jyzhao
export PATH=$PATH:$ORACLE_HOME/bin:.

设置用户参数

vi /etc/sysctl.conf
 
fs.aio-max-nr = 1048576
fs.file-max = 6815744
kernel.shmall = 2097152
#一般设置为系统内存75%单位是字节
kernel.shmmax = 12884901888
kernel.shmmni = 4096
kernel.sem = 250 32000 100 128
net.ipv4.ip_local_port_range = 9000 65500
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048586
 
使设置立即生效： /sbin/sysctl -p
 
 
vi /etc/security/limits.conf
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft stack 10240
 
vi /etc/pam.d/login
 
#oracle
session required /lib64/security/pam_limits.so
session required pam_limits.so

上传数据库安装包，解压，授权给oracle用户

创建静默安装数据库文件db_install文件

oracle.install.responseFileVersion=/oracle/install/rspfmt_dbinstall_response_schema_v11_2_0
oracle.install.option=INSTALL_DB_SWONLY
UNIX_GROUP_NAME=oinstall
INVENTORY_LOCATION=/u01/app/oracle/oraInventory
SELECTED_LANGUAGES=en
ORACLE_HOME=/u01/app/oracle/product/11.2.0/db_1
ORACLE_BASE=/u01/app/oracle
oracle.install.db.InstallEdition=EE
oracle.install.db.EEOptionsSelection=false
oracle.install.db.optionalComponents=oracle.rdbms.partitioning:11.2.0.4.0,oracle.oraolap:11.2.0.4.0,oracle.rdbms.dm:11.2.0.4.0,oracle.rdbms.dv:11.2.0.4.0,oracle.rdbms.lbac:11.2.0.4.0,oracle.rdbms.rat:11.2.0.4.0
oracle.install.db.DBA_GROUP=dba
oracle.install.db.OPER_GROUP=oinstall
oracle.install.db.isRACOneInstall=false
SECURITY_UPDATES_VIA_MYORACLESUPPORT=false
DECLINE_SECURITY_UPDATES=true
oracle.installer.autoupdates.option=SKIP_UPDATES

执行数据库静默安装，进入到database目录下执行

./runInstaller -silent -force -responseFile /home/oracle/db_install.rsp -ignorePrereq

执行完成后按照提示，将root.sh文件拷贝到root用户下执行。数据库安装完成

2 rman还原

创建pfile文件 在/tmp/pfile.txt

orcl.__db_cache_size=1207959552
orcl.__java_pool_size=67108864
orcl.__large_pool_size=67108864
orcl.__oracle_base='/u01/app/oracle'#ORACLE_BASE set from environment
orcl.__pga_aggregate_target=1744830464
orcl.__sga_target=3355443200
orcl.__shared_io_pool_size=0
orcl.__shared_pool_size=1879048192
orcl.__streams_pool_size=67108864
*.audit_file_dest='/u01/app/oracle/admin/orcl/adump'
*.audit_trail='db'
*.control_files='/data/oracle/orcl/controlfile/control01.ctl','/data/oracle/orcl/controlfile/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='swapdb'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=orclXDB)'
*.job_queue_processes=1000
#*.local_listener='LISTENER_ORCL'
*.memory_target=5049942016
*.nls_language='SIMPLIFIED CHINESE'
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=1500
*.remote_login_passwordfile='EXCLUSIVE'
*.resource_limit=TRUE
*.undo_tablespace='UNDOTBS1

启动到nomount，执行rman还原

#窗口1 执行sql启动数据库
startup nomount pfile='/tmp/pfile.txt';
 
#窗口2 执行restore
 
rman target /
 
 
restore controlfile from '/data/oracle/xxxxxxxxx/ctl';
 
#启动到mount
alter database mount;
 
#将rman备份集注册到控制文件上
catalog start with '/data/backup/';

为了保障数据库还原稳定性，编写sh脚本将rman还原动作放到服务器上运行

在/home/oracle传教rman.sh文件，执行还原

nohup /home/oracle/rman.sh &

 
RMAN_LOG_FILE=full`date +%Y%m%d`.log
 
su - oracle -c "rman target / log=/home/oracle/$RMAN_LOG_FILE append" <<EOF
run{
set newname for datafile 1 to '/data/oracle/orcl/datafile/system01.dbf';
set newname for datafile 2 to '/data/oracle/orcl/datafile/sysaux01.dbf';
set newname for datafile 3 to '/data/oracle/orcl/datafile/undotbs01.dbf';
set newname for datafile 4 to '/data/oracle/orcl/datafile/users01.dbf';
set newname for datafile 5 to '/data/oracle/orcl/datafile/system02.dbf';
set newname for datafile 6 to '/data/oracle/orcl/datafile/sysaux02.dbf';
set newname for datafile 7 to '/data/oracle/orcl/datafile/sales.dbf';
set newname for datafile 8 to '/data/oracle/orcl/datafile/wms.dbf';
set newname for datafile 9 to '/data/oracle/orcl/datafile/logistic.dbf';
set newname for datafile 10 to '/data/oracle/orcl/datafile/pm.dbf';
set newname for datafile 11 to '/data/oracle/orcl/datafile/DMTEST01.dbf';
set newname for datafile 12 to '/data/oracle/orcl/datafile/DMTEST02.dbf';
set newname for datafile 13 to '/data/oracle/orcl/datafile/system03.dbf';
set newname for datafile 14 to '/data/oracle/orcl/datafile/DMTEST03.dbf';
set newname for datafile 15 to '/data/oracle/orcl/datafile/data_e3.dbf';
set newname for datafile 16 to '/data/oracle/orcl/datafile/idx_e3.dbf';
set newname for datafile 17 to '/data/oracle/orcl/datafile/TCBJMKG_DATA.dbf';
set newname for datafile 18 to '/data/oracle/orcl/datafile/wmsyl01.dbf';
set newname for datafile 19 to '/data/oracle/orcl/datafile/ZJOMSTEST.dbf';
set newname for datafile 20 to '/data/oracle/orcl/datafile/maiytb01.dbf';
set newname for datafile 21 to '/data/oracle/orcl/datafile/maiytb02.dbf';
set newname for datafile 22 to '/data/oracle/orcl/datafile/maiytb03.dbf';
allocate channel c1 device type disk;
allocate channel c2 device type disk;
allocate channel c3 device type disk;
allocate channel c4 device type disk;
restore database;
switch datafile all;
recover database;
release channel c1;
release channel c2;
release channel c3;
release channel c4;
}
exit;
EOF

还原成功后执行打开数据库

如果只是正常rman备份还原，只需要执行打开数据库

alter database open resetlogs

我们这里是11.2.0.1数据库还原到11.2.0.4 ，直接resetlogs会报错，要执行以下脚本

alter database open resetlogs upgrade;

打开数据库后不要着急坐任何操作，根据自己的online日志和临时表空间情况，先增加日志和临时表空间。

添加日志组，不然后面升级时候日志组切换过于频繁，我这里增加了3组500m的日志

alter database add logfile group 4 ('/data/oracle/orcl/onlinelog/redo04.log') size 500m;
 
alter database add logfile group 5 ('/data/oracle/orcl/onlinelog/redo05.log') size 500m; 
 
alter database add logfile group 6 ('/data/oracle/orcl/onlinelog/redo06.log') size 500m;

增加临时表空间

create temporary tablespace temp02 tempfile '/data/oracle/orcl/tempfile/temp02.dbf' size 10G;
 
alter database default temporary tablespace temp02;
 
drop tablespace temp including contents and datafiles cascade constraints;

3 操作数据库升级

接下来就是操作升级数据库，为了保障升级过程稳定，同样写了sh脚本在服务器上运行

nohup /home/oracle/upgrd.sh &

vi upgrd.sh
 
su - oracle -c "sqlplus / as sysdba" <<EOF
run{
@?/rdbms/admin/catbundle.sql psu apply;
@?/rdbms/admin/utlrp.sql;
@?/rdbms/admin/catupgrd.sql;
show parameter name;
exit;
EOF

第一个，这个主要目的是将补丁信息更新到sql中，也会执行其他东西，也就是视图dba_registry_history
第二个，重新编译无效对象

第三个最重要，本次环境为11.2.0.1 到 11.2.0.4 ，也是必须执行的脚本，如果你不想每次shutdown数据库后，startup时还需要 alter database open upgrade  那就执行此步骤。

脚本执行完成后，数据库会自动执行shutdown immediate；

后面正常启动数据库就完成了。之后就是把监听安排上就可以正常访问