设计公司企业站,电子商务网站建设 asp,深圳网站平面设计,装修公司装饰1、起因
群友说有套数据库要进行迁移#xff0c;源端是套跑了十年的RAC#xff0c;目标段是个新的单机#xff08;都是同一架构平台#xff09;#xff0c;数据量约3T左右。
目前DATA目录存储和归档放在一起#xff0c;整个磁盘组只剩下了20G空间#xff0c;每间隔1小…1、起因
群友说有套数据库要进行迁移源端是套跑了十年的RAC目标段是个新的单机都是同一架构平台数据量约3T左右。
目前DATA目录存储和归档放在一起整个磁盘组只剩下了20G空间每间隔1小时左右就要清一理下归档要不库就爆了。
所以这个迁移要选个周末停机窗口停业务来进行迁移因为开业务归档就爆所以还是停机就不考虑为啥归档这么多的问题先。
按这个情况看从RAC迁移到单机常规的方法有几个
1、duplicate在线搭建ADG然后再切换需要看网络带宽这种方法一般是首选停机时间短
2、RAC端rman备份到NAS单机端挂载再恢复需要额外的存储空间这个过程也受到带宽限制整个时间需要备份恢复2个时间相加
3、OGG或者DSG需要了解业务结构需要测试避免恢复后有逻辑错误
4、RAC端EXPDP到NAS单机端挂载NAS再导入需要额外的存储空间需要的时间为导出导入时间之和还需要了解业务结构需要测试避免恢复后有逻辑错误
这里我就有个想法因为反正都要停机何不来个冷迁移试试之前并没有试过。
于是有了这一篇测试原理上来说是可行的没啥问题测试也确实没遇到库启动有任何问题。
2、测试环境
源端RAC192.168.56.30/31
数据库实例名orcl1/orcl2
redhat 6.9
oracle 11.2.0.4 rac
数据存储DATA磁盘组
目标端:192.168.56.99
数据库实例名orcl
redhat 6.9
oracle 11.2.0.4
数据存储/u01/app/oracle/oradata/orcl
3、测试过程
记录物理文件位置
在任意一个RAC节点登录数据库执行命令生成相关拷贝脚本和重命名脚本
拷贝脚本
set pagesize 500
set linesize 500
select cp ||||member|||| /mnt/orcl/ from v$logfile where member like DATA/orcl/onlinelog/%
union
select cp ||||name|||| /mnt/orcl/ from v$datafile where name like DATA/orcl/datafile/%
union
select cp ||||name|||| /mnt/orcl/ from v$controlfile where name like DATA/orcl/controlfile/%
union
select cp ||||name|||| /mnt/orcl/ from v$tempfile where name like DATA/orcl/tempfile/%;重命名脚本
set pagesize 500
set linesize 500
select alter database rename file ||||name|||| to ||chr(39)||replace(name,DATA/orcl/tempfile/,/u01/app/oracle/oradata/orcl/)||; from v$tempfile where name like DATA/orcl/tempfile/%
union
select alter database rename file ||||name|||| to ||chr(39)||replace(name,DATA/orcl/datafile/,/u01/app/oracle/oradata/orcl/)||; from v$datafile where name like DATA/orcl/datafile/%
union
select alter database rename file ||||member|||| to ||chr(39)||replace(member,DATA/orcl/onlinelog/,/u01/app/oracle/oradata/orcl/)||; from v$logfile where member like DATA/orcl/onlinelog/%;关闭数据库
关库前创建测试表
SQL select * from scottemp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------7369 SMITH CLERK 7902 17-DEC-80 800 207499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307566 JONES MANAGER 7839 02-APR-81 2975 207654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307698 BLAKE MANAGER 7839 01-MAY-81 2850 307782 CLARK MANAGER 7839 09-JUN-81 2450 107788 SCOTT ANALYST 7566 19-APR-87 3000 207839 KING PRESIDENT 17-NOV-81 5000 107844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307876 ADAMS CLERK 7788 23-MAY-87 1100 207900 JAMES CLERK 7698 03-DEC-81 950 307902 FORD ANALYST 7566 03-DEC-81 3000 207934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.SQL alter system checkpoint;System altered.使用集群命令正常关闭数据库
su - oracle
srvctl stop database -d orcl如果执行了上述命令库还卡着正常关闭不了可以尝试以下方式
方法1.先杀掉LOCALNO的会话进程结束后数据库会自动进行关闭
ps -ef |grep LOCALNO|grep -v grep|cut -c 9-15|xargs kill -9方法2.可以快速关闭数据库如果不放心可以再open最后正常关闭其实冷备份文件全abort也没有事会自动恢复
快速关闭的命令为
srvctl stop database -d orcl -o abort配置 NFS共享
单机端配置nfs服务器 yum install -y nfs-utils rpcbind编辑NFS配置文件这里做了个限制只允许192.168.56.31访问NFS
[rootoracle oracle]# cat /etc/exports
/u01/app/oracle/oradata 192.168.56.31(rw,sync,no_root_squash,no_all_squash,no_subtree_check)
[rootoracle oracle]# /etc/init.d/nfs startRAC节点安装nfs
yum install -y nfs-utilsRAC客户段挂载NFS192.168.56.31上操作
mount -t nfs -o rw,bg,hard,nointr,rsize32768,wsize32768,tcp,actimeo0,vers3,timeo600,nolock 192.168.56.99:/u01/app/oracle/oradata /mnt使用root创建目录给NFS目录赋予权限
mkdir -p /mnt/orcl
chown -R grid:asmadmin /mnt/
chmod -R 755 /mnt/orcl/官方文档对于不同版本操作系统挂载NFS是有要求的具体参照官方文档
Mount Options for Oracle files for RAC databases and Clusterware when used with NFS on NAS devices (Doc ID 359515.1)
这里注意与官方文档不同的地方是加了nolock如果不添加拷贝的时候会有如下报错,具体原因没查到为啥。
ASMCMD cp * /mnt/orcl
copying data/orcl/datafile/SYSTEM.256.1086172033 - /mnt/orcl/SYSTEM.256.1086172033
ASMCMD-8016: copy source data/orcl/datafile/SYSTEM.256.1086172033 and target /mnt/orcl/SYSTEM.256.1086172033 failed
ORA-19505: failed to identify file /mnt/orcl/SYSTEM.256.1086172033
ORA-27086: unable to lock file - already in use
Linux-x86_64 Error: 37: No locks available
Additional information: 10
ORA-15120: ASM file name /mnt/orcl/SYSTEM.256.1086172033 does not begin with the ASM prefix character
ORA-06512: at SYS.X$DBMS_DISKGROUP, line 415
ORA-06512: at line 3 (DBD ERROR: OCIStmtExecute)冷拷贝数据文件
使用grid用户登录到asmcmd命令行
粘贴生成的拷贝脚本等到拷贝完成
cp DATA/orcl/controlfile/current.260.1086172191 /mnt/orcl/
cp DATA/orcl/controlfile/current.261.1086172189 /mnt/orcl/
cp DATA/orcl/datafile/prod.273.1086172629 /mnt/orcl/
cp DATA/orcl/datafile/sysaux.257.1086172033 /mnt/orcl/
cp DATA/orcl/datafile/system.256.1086172033 /mnt/orcl/
cp DATA/orcl/datafile/undotbs1.258.1086172033 /mnt/orcl/
cp DATA/orcl/datafile/undotbs2.267.1086172237 /mnt/orcl/
cp DATA/orcl/datafile/users.259.1086172033 /mnt/orcl/
cp DATA/orcl/onlinelog/group_1.262.1086172195 /mnt/orcl/
cp DATA/orcl/onlinelog/group_1.263.1086172197 /mnt/orcl/
cp DATA/orcl/onlinelog/group_2.264.1086172199 /mnt/orcl/
cp DATA/orcl/onlinelog/group_2.265.1086172203 /mnt/orcl/
cp DATA/orcl/onlinelog/group_3.268.1086172281 /mnt/orcl/
cp DATA/orcl/onlinelog/group_3.269.1086172283 /mnt/orcl/
cp DATA/orcl/onlinelog/group_4.270.1086172287 /mnt/orcl/
cp DATA/orcl/onlinelog/group_4.271.1086172289 /mnt/orcl/
cp DATA/orcl/tempfile/temp.266.1086172211 /mnt/orcl/拷贝完成后卸载nfs
cd ~
su - root
umount /mnt恢复数据库
使用root用户
[rootoracle oracle]# chown -R oracle:dba /u01/app/oracle/oradata/
[rootoracle oracle]# chmod 755 /u01/app/oracle/oradata/切换到oracle用户创建spfile
参数文件里不存在的目录要手动创建一下
[rootoracle oracle]# su - oracle
[oracleoracle ~]$ cd $ORACLE_HOME/dbs
[oracleoracle dbs]$ cat initorcl.ora
*.audit_file_dest/u01/app/oracle/admin/orcl/adump
*.audit_traildb
*.compatible11.2.0.4.0
*.control_files/u01/app/oracle/oradata/orcl/current.260.1086172191,/u01/app/oracle/oradata/orcl/current.261.1086172189
*.db_block_size8192
*.db_domain
*.db_files1024
*.db_nameorcl
*.db_recovery_file_dest_size6005194752
*.db_recovery_file_dest/u01/app/oracle/fast_recover_area
*.db_create_file_dest/u01/app/oracle/oradata/orcl
*.diagnostic_dest/u01/app/oracle
*.log_archive_format%t_%s_%r.dbf
*.open_cursors300
*.processes150
*.remote_login_passwordfileexclusive
*.db_unique_nameorcl
*.service_namesorcl
*.undo_tablespaceUNDOTBS1
*.memory_target300m创建口令文件 orapwd file$ORACLE_HOME/dbs/orapworcl passwordoracle entries5启动数据库到mount模式
[oracleoracle dbs]$ sqlplus / as sysdbaSQL*Plus: Release 11.2.0.4.0 Production on Sat Mar 30 11:07:49 2024Copyright (c) 1982, 2013, Oracle. All rights reserved.Connected to an idle instance.SQL create spfile from pfile;File created.SQL startup mount;
ORACLE instance started.Total System Global Area 313159680 bytes
Fixed Size 2252824 bytes
Variable Size 222302184 bytes
Database Buffers 83886080 bytes
Redo Buffers 4718592 bytes
Database mounted.更改数据文件位置
alter database rename file DATA/orcl/datafile/prod.273.1086172629 to /u01/app/oracle/oradata/orcl/prod.273.1086172629;
alter database rename file DATA/orcl/datafile/sysaux.257.1086172033 to /u01/app/oracle/oradata/orcl/sysaux.257.1086172033;
alter database rename file DATA/orcl/datafile/system.256.1086172033 to /u01/app/oracle/oradata/orcl/system.256.1086172033;
alter database rename file DATA/orcl/datafile/undotbs1.258.1086172033 to /u01/app/oracle/oradata/orcl/undotbs1.258.1086172033;
alter database rename file DATA/orcl/datafile/undotbs2.267.1086172237 to /u01/app/oracle/oradata/orcl/undotbs2.267.1086172237;
alter database rename file DATA/orcl/datafile/users.259.1086172033 to /u01/app/oracle/oradata/orcl/users.259.1086172033;
alter database rename file DATA/orcl/onlinelog/group_1.262.1086172195 to /u01/app/oracle/oradata/orcl/group_1.262.1086172195;
alter database rename file DATA/orcl/onlinelog/group_1.263.1086172197 to /u01/app/oracle/oradata/orcl/group_1.263.1086172197;
alter database rename file DATA/orcl/onlinelog/group_2.264.1086172199 to /u01/app/oracle/oradata/orcl/group_2.264.1086172199;
alter database rename file DATA/orcl/onlinelog/group_2.265.1086172203 to /u01/app/oracle/oradata/orcl/group_2.265.1086172203;
alter database rename file DATA/orcl/onlinelog/group_3.268.1086172281 to /u01/app/oracle/oradata/orcl/group_3.268.1086172281;
alter database rename file DATA/orcl/onlinelog/group_3.269.1086172283 to /u01/app/oracle/oradata/orcl/group_3.269.1086172283;
alter database rename file DATA/orcl/onlinelog/group_4.270.1086172287 to /u01/app/oracle/oradata/orcl/group_4.270.1086172287;
alter database rename file DATA/orcl/onlinelog/group_4.271.1086172289 to /u01/app/oracle/oradata/orcl/group_4.271.1086172289;
alter database rename file DATA/orcl/tempfile/temp.266.1086172211 to /u01/app/oracle/oradata/orcl/temp.266.1086172211;校验数据文件位置是否正确
SQL select name from v$datafile;NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/system.256.1086172033
/u01/app/oracle/oradata/orcl/sysaux.257.1086172033
/u01/app/oracle/oradata/orcl/undotbs1.258.1086172033
/u01/app/oracle/oradata/orcl/users.259.1086172033
/u01/app/oracle/oradata/orcl/undotbs2.267.1086172237
/u01/app/oracle/oradata/orcl/prod.273.10861726296 rows selected.SQL select name from v$controlfile;NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/current.260.1086172191
/u01/app/oracle/oradata/orcl/current.261.1086172189SQL select member from v$logfile;MEMBER
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/orcl/group_2.264.1086172199
/u01/app/oracle/oradata/orcl/group_2.265.1086172203
/u01/app/oracle/oradata/orcl/group_1.262.1086172195
/u01/app/oracle/oradata/orcl/group_1.263.1086172197
/u01/app/oracle/oradata/orcl/group_3.268.1086172281
/u01/app/oracle/oradata/orcl/group_3.269.1086172283
/u01/app/oracle/oradata/orcl/group_4.270.1086172287
/u01/app/oracle/oradata/orcl/group_4.271.10861722898 rows selected.
这里确认无误后打开数据库
alter databsae open;
再校验关库迁移之前新创建的表
SQL set pagesize 500;
SQL set linesize 500;
SQL select * from scottemp;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
---------- ---------- --------- ---------- --------- ---------- ---------- ----------7369 SMITH CLERK 7902 17-DEC-80 800 207499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307566 JONES MANAGER 7839 02-APR-81 2975 207654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307698 BLAKE MANAGER 7839 01-MAY-81 2850 307782 CLARK MANAGER 7839 09-JUN-81 2450 107788 SCOTT ANALYST 7566 19-APR-87 3000 207839 KING PRESIDENT 17-NOV-81 5000 107844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307876 ADAMS CLERK 7788 23-MAY-87 1100 207900 JAMES CLERK 7698 03-DEC-81 950 307902 FORD ANALYST 7566 03-DEC-81 3000 207934 MILLER CLERK 7782 23-JAN-82 1300 1014 rows selected.完结撒花
后记
RAC冷份迁移没问题。
同样这也适用于RAC-RAC冷迁移ASM之间也可以通过网络拷贝、单机冷迁移到RAC。
就知不知道生产有没有机会用到。
