某Oracle OA数据库原机迁移方案

某Oracle OA数据库原机迁移方案

• 背景
  • 因前期配置数据文件目录挂载在裸盘，随着数据量不断增长，剩余空间无法满足需求。

(1) 查看pfile文件

• 修改pfile文件

su - oracle

sqlplus / as sysdba


create pfile='/home/oracle/new_oadb_20240603.pfile' from spfile;

*._allow_level_without_connect_by=TRUE
*.audit_file_dest='/u01/app/oracle/admin/oadb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/oradata/oadb/control01.ctl','/oradata/oadb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='oadb'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oadbXDB)'
*.log_archive_dest_1='location=/u01/archivelog'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=3300917248
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

• 备份一份pfile文件，以便回退使用

vi /home/oracle/new_oadb_20240603.pfile

## 修改*._allow_level_without_connect_by=TRUE
*.audit_file_dest='/u01/app/oracle/admin/oadb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/oadb/control01.ctl','/u01/app/oracle/fast_recovery_area/oadb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='oadb'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oadbXDB)'
*.log_archive_dest_1='location=/u01/archivelog'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=3300917248
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

(2) 备份数据库

lsnrctl stop 

sqlplus / as sysdba

shutdown immediate;

start MOUNT;

• 数据全备

vi  /home/oracle/a.sh

##----------------------------------------------------------------------------------------
echo `date +"%Y%m%d"` > /u01/backup/shell/rman_backup_date.info
RMANDATE=`cat /u01/backup/shell/rman_backup_date.info`

## begin rman 
rman log=/backup/oadb/full_OADB_${RMANDATE}.log  <<EOF
connect target /;
crosscheck archivelog all;
crosscheck backup;
run
{
allocate channel c1 type disk;
backup AS COMPRESSED BACKUPSET database tag 'fullbackup' format '/backup/oadb/full_data_${RMANDATE}_%d_%I.%s' ;
release channel c1;

allocate channel c2 type disk;
backup current controlfile format '/backup/ /full_controlfile_${RMANDATE}_%d_%I.%s' ;
release channel c2;

sql 'alter system archive log current'; 

allocate channel c3 type disk;
backup AS COMPRESSED BACKUPSET  archivelog all format  '/backup/oadb/full_arch_${RMANDATE}_%d_%I_%h_%e.%s';
release channel c3;
}
EOF

## change rman backup file mode
chmod 744 /backup/oadb/*
##----------------------------------------------------------------------------------------
chmod u+x /home/oracle/a.sh

/home/oracle/a.sh

##----------------------------------------------------------------------------------------

(3) 使用new pfile启动到mount状态

sqlplus / as sysdba


start nomount pfile='/home/oracle/new_oadb_20240603.pfile';

(4) 恢复控制文件

• ## 环境准备

## mkdir -p /oradata/oadb/


rman target /

restore controlfile from '/u01/oadb/oa_rman/full_data_XXXXXXXX_OADB_XXXXXXXX.12396';

startup nomount;

• 冷备迁移方案

cp -r /u01/app/oracle/oradata/oadb/* /oradata/oadb/

(5) 启动数据库

sqlplus / as sysdba

create spfile from pfile='/home/oracle/new_oadb_20240603.pfile';

shutdown immediate;


startup mount;

alter database open;

(6) 回退方案

vi /home/oracle/old_oadb_20240001.pfile

*._allow_level_without_connect_by=TRUE
*.audit_file_dest='/u01/app/oracle/admin/oadb/adump'
*.audit_trail='db'
*.compatible='11.2.0.0.0'
*.control_files='/u01/app/oracle/oradata/oadb/control01.ctl','/u01/app/oracle/fast_recovery_area/oadb/control02.ctl'
*.db_block_size=8192
*.db_domain=''
*.db_name='oadb'
*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'
*.db_recovery_file_dest_size=4322230272
*.diagnostic_dest='/u01/app/oracle'
*.dispatchers='(PROTOCOL=TCP) (SERVICE=oadbXDB)'
*.log_archive_dest_1='location=/u01/archivelog'
*.log_archive_format='%t_%s_%r.dbf'
*.memory_target=3300917248
*.nls_territory='CHINA'
*.open_cursors=300
*.processes=2000
*.remote_login_passwordfile='EXCLUSIVE'
*.undo_tablespace='UNDOTBS1'

• 启动原来的库

sqlplus / as sysdba

startup nomount pfile='/home/oracle/old_oadb_20240001.pfile';

create spfile from pfile='/home/oracle/old_oadb_20240001.pfile';


-- 恢复控制文件
-- restore controlfile from '/u01/oadb/oa_rman/full_data_XXXXXXXX_OADB_XXXXXXXX.12396';


alter database mount;

alter database open;

lsnrctl stop