有一个测试数据库因为操作系统过于老旧,需要迁移升级。老的版本是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;
后面正常启动数据库就完成了。之后就是把监听安排上就可以正常访问