1.前言
根据迁移规范要求，特编写xxx数据库迁移至linux环境操作方案。
2.方案描述
2.1 环境描述
源库数据量为20T，操作系统为Windows Server 2008 64 bit,数据库版本为oracle 11.2.0.1，目标库操作系统为Linux redhat 7.6，数据库版本为11.2.0.4。
详细信息如下：
源端数据库：
业务系统 | 数据库 | IP | 归档 | 强制日志 | 附加日志 | 操作系统 | 版本信息 | 存储容量（TB） | 归档空间 (GB) |
xxx | YWRZDB | 190.168.1.200 | NO | YES | YES | Windows | 11.2.0.1 单机 | 20T | 1T |
目标端数据库
业务系统 | 数据库 | IP | 归档 | 强制日志 | 附加日志 | 操作系统 | 版本信息 | 存储容量（TB） | 归档空间 (GB) |
xxx | YWRZDB | 190.168.1.2 | YES | YES | YES | RHEL7.6 | 11.2.0.4 单机 | 20T | 1T |
2.2 操作描述
针对XXX系统数据库现有环境分析，本次迁移属于异构平台不同版本数据库迁移，可通过ORACLE DATAGUARD技术实现生产数据库无缝迁移至linux环境。
操作过程如下：
准备阶段，开启归档，后在Linux环境搭建ORACLE DATAGUARD,建立Windows与Linux数据同步机制。该操作涉及调整生产环境部份数据库参数，搭建过程将申请停机作业。停机时间约一小时。
同步机制维护阶段，ORACLE DATAGUARD搭建完成后，应对同步机制进行检查，确保数据库同步机制正常运行，保障后续数据库具备主备切换条件。
数据库割接，当应用具备割接条件时，按客户确认的割接操作时间，完成Windows数据库（主库）与Linux数据库（备库）的切换工作，并交付给应用使用。
应用上线，数据库切换成功并交付应用使用后，应用进行相应测试，确认是否具备上线条件，客户最终确认应用切换成功，则数据库运行在Linux环境，否则按回退方案进行回退处理。
回退过程，客户决定回退至原生产环境，数据库侧将进行Linux数据库（主库）切换至原Windows数据库（备库）操作，操作完成后，应用侧修改数据库连接配置，回退至原生产环境，启动应用交付使用。
3.准备阶段
3.1 生产环境操作
1）主库环境检查
检查事项 | 检查方法 | 检查结果 | 调整方法 |
归档开启 | Archive log list | YES | shutdown immediate # 关闭数据库 startup mount # 启动实例并挂载数据库 alter database archivelog; # 更改数据库为归档模式 alter database open; # 打开数据库 |
FORCE LOGGING | select force_logging from v$database; | YES | alter database force logging; |
remote_login_passwordfile | show parameter remote_login_passwordfile | NONE | alter system set remote_login_passwordfile='EXCLUSIVE' scope=spfile sid='*'; shutdown immediate; startup; |
2）TNS配置
lfjyzw = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 190.168.1.200)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = lfjyzw) ) ) lfjyzwdg = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 190.168.1.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = lfjyzwdg) ) ) |
3）SQLNET白名单配置
vi /u01/11.2.0/grid/network/admin/sqlnet.ora #add |
4）TNSPING检查
tnsping shywrzdb |
5）密码文件
Oracle 11.0.2.0.1 WINDOWS路径 C:\app\liqi\product\11.2.0\dbhome_1\database |
6）添加Standby logfiles
a)日志检查： set lines 200 pages 300 col member for a60 select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$log a,v$logfile b where a.group#=b.group# union all select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$standby_log a,v$logfile b where a.group#=b.group#; THREAD# GROUP# MEMBER TYPE MB ---------- ---------- ------------------------------------------------------------ -------------- ---------- 1 3 C:\APP\LIQI\ORADATA\TOMDB\REDO03.LOG ONLINE 50 1 2 C:\APP\LIQI\ORADATA\TOMDB\REDO02.LOG ONLINE 50 1 1 C:\APP\LIQI\ORADATA\TOMDB\REDO01.LOG ONLINE 50 1 4 C:\APP\LIQI\ORADATA\TOMDB\STANDREDOLOG1 STANDBY 50 1 5 C:\APP\LIQI\ORADATA\TOMDB\STANDREDOLOG2 STANDBY 50 1 6 C:\APP\LIQI\ORADATA\TOMDB\STANDREDOLOG3 STANDBY 50 1 7 C:\APP\LIQI\ORADATA\TOMDB\STANDREDOLOG4 STANDBY 50 已选择7行。 b)添加语句示例： alter database add standby logfile thread 1 group 4 ' C:\APP\LIQI\ORADATA\TOMDB\STANDREDOLOG1 ' size 50M, group 5 ' C:\APP\LIQI\ORADATA\TOMDB\STANDREDOLOG2' size 50M, group 6 ' C:\APP\LIQI\ORADATA\TOMDB\STANDREDOLOG13' size 50M, group 7 ' C:\APP\LIQI\ORADATA\TOMDB\STANDREDOLOG14' size 50M; |
xxx数据库还未完成添加，该步骤需要修改路径。
7）主库参数调整
备份spfile： create pfile=' C:\dbbak \0211.ora' from spfile; 修改以下参数，其中yzdb,yzdbdg分别为primary,standby的db_unqiue_name/TNS-Alias,详见参数详解部分： alter system set log_archive_cnotallow='dg_cnotallow=(lfjyzw,lfjyzwdg)'; alter system set log_archive_dest_1='locatinotallow=F:/arch/ valid_for=(all_logfiles,all_roles) db_unique_name=ywrzdb'; alter system set log_archive_dest_2='service=lfjyzwdg lgwr async valid_for=(online_logfiles,primary_role) db_unique_name=lfjyzwdg'; alter system set log_archive_max_processes=30;#根据需求调整个数 --确认以下参数，若不为默认值则修改： alter system set remote_login_passwordfile=exclusive scope=spfile sid='*'; # remote_login_passwordfile=exclusive/shared alter system set log_archive_dest_state_1=enable sid='*'; alter system set log_archive_dest_state_2=enable sid='*'; --可修改归档日志格式，与datafile区分： alter system set log_archive_format='%t_%s_%r.dbf' scope=spfile sid='*'; --备角色参数： alter system set fal_server=standby sid='*'; alter system set standby_file_management=auto sid='*'; alter system set db_file_name_cnotallow='+DATADG/ywrzdb','+DATADG/shywrzdb' scope=spfile sid='*'; alter system set log_file_name_cnotallow='+arcdg/ywrzdb','+arcdg/shywrzdb' scope=spfile sid='*'; 注：以上参数，指定spfile修改的，在实例重启后生效。 |
8）rman全库备份
configure maxsetsize to 1024G; run { allocate channel c1 type disk; allocate channel c2 type disk; allocate channel c3 type disk; allocate channel c4 type disk; allocate channel c5 type disk; allocate channel c6 type disk; allocate channel c7 type disk; allocate channel c8 type disk; allocate channel c9 type disk; allocate channel c10 type disk; backup as compressed backupset full database FORMAT 'Z:\data_%T_%s'; backup current controlfile for standby format 'Z:\control.bak'; release channel c1; release channel c2; release channel c3; release channel c4; release channel c5; release channel c6; release channel c7; release channel c8; release channel c9; release channel c10; } |
至此，主库备份完成。
3.2 linux备库环境操作
1）配置环境变量
export ORACLE_SID= lfjyzwdg |
2）监听配置
LISTENER = (DESCRIPTION_LIST = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 190.168.1.2)(PORT = 1521)) (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521)) ) ) SID_LIST_LISTENER= (SID_LIST= (SID_DESC= (GLOBAL_DBNAME=lfjyzwdg) (ORACLE_HOME=/home/u01/app/oracle/product/11.2.0) (SID_NAME=lfjyzwdg)) ) DIAG_ADR_ENABLED_LISTENER=OFF |
3）TNS配置
lfjyzw = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 190.168.1.200)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = lfjyzw) ) ) lfjyzwdg = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = 190.168.1.2)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = lfjyzwdg) ) ) |
4)SQLNET白名单复制至备库
5)TNSPING检查
lsnrctl reload tnsping lfjyzw tnsping lfjyzwdg |
6)创建相关目录
3.创建目录 在各节点创建相关目录(oracle)： mkdir -p $ORACLE_BASE/admin/ywrzdbdg/{adump,dpdump,pfile} |
7)参数文件处理
4.参数文件 用primary的pfile加以修改，以保证某些参数与primary保持一致，注意主备库内存的大小适当的调整内存相关参数。 0211.ora 修改pfile为需要的standby pfile,特别注意以下事例的参数设定： *.db_file_name_cnotallow='E:\LFJYZW','/oradata/datafile' *.log_file_name_cnotallow='C:\LFJYZW','/oradata/redolog' *.fal_server='lfjyzwdg' *.db_unique_name='lfjyzwdg' *.log_archive_dest_1='LOCATION=/oradata/arch VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=lfjyzwdg' *.log_archive_dest_2='SERVICE=lfjyzw LGWR ASYNC REOPEN NET_TIMEOUT=300 VALID_FOR=(ONLINE_LOGFILE,PRIMARY_ROLE) DB_UNIQUE_NAME=lfjyzw' db_name=lfjyzw *.log_archive_cnotallow='DG_CONFIG=(lfjyzw,lfjyzwdg)' *.compatible='11.2.0.4.0' *.standby_file_management='AUTO' lfjyzwdg.__db_cache_size=70598524928 lfjyzwdg.__java_pool_size=268435456 lfjyzwdg.__large_pool_size=268435456 lfjyzwdg.__oracle_base='/home/u01/app'#ORACLE_BASE set from environment lfjyzwdg.__pga_aggregate_target=41339060224 lfjyzwdg.__sga_target=76772540416 lfjyzwdg.__shared_io_pool_size=0 lfjyzwdg.__shared_pool_size=5100273664 lfjyzwdg.__streams_pool_size=0 *.audit_file_dest='/home/u01/app/oracle/admin/lfjyzwdg/adump' *.audit_trail='db' *.control_files='/oradata/control01.ctl','/oradata/control02.ctl' *.db_block_size=8192 *.db_domain='' *.db_files=1024 *.db_name='lfjyzw' *.db_recovery_file_dest='/oradata/fast_recover_area' *.db_recovery_file_dest_size=4102029312 *.dispatchers='(PROTOCOL=TCP) (SERVICE=lfjyzwXDB)' *.log_archive_dest_1='locatinotallow=/oradata/arch/' *.memory_target=118111600640 *.open_cursors=300 *.processes=3000 *.remote_login_passwordfile='EXCLUSIVE' *.sessinotallow=1105 *.sga_max_size=96636764160 *.undo_tablespace='UNDOTBS1' SQL> startup nomount pfile='/home/oracle/0211.ora'; |
8)数据库恢复
a).恢复standby controlfile SQL> startup nomount; RMAN> RESTORE STANDBY CONTROLFILE FROM '/home/oracle/enmo/dbbak/CONTROL.BAK'; b).alter database mount; c).恢复数据文件 查询primary database的数据文件信息： set lines 300 pages 300 col name for a60 col member for a60 select file#,name from v$datafile union all select file#,name from v$tempfile; 恢复数据文件： 激活备份 catalog start with '/home/oracle/enmo/dbbak'; RMAN> run{ allocate channel d1 type disk; allocate channel d2 type disk; allocate channel d3 type disk; allocate channel d4 type disk; allocate channel d5 type disk; allocate channel d6 type disk; allocate channel d7 type disk; allocate channel d8 type disk; allocate channel d9 type disk; allocate channel d10 type disk; set newname for database to ' /oradata/datafile/%b'; restore database; switch datafile all; switch tempfile all; recover database; release channel c1; release channel c2; release channel c3; release channel c4; release channel c5; release channel c6; release channel c7; release channel c8; release channel c9; release channel c10; } 查看standby database log file: set lines 200 pages 300 col member for a60 select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$log a,v$logfile b where a.group#=b.group# union all select a.thread#,a.group#,b.member,b.type,a.bytes/1024/1024 MB from v$standby_log a,v$logfile b where a.group#=b.group#; c.创建spfile SQL> create spfile='+DATA' from pfile='?/dbs/initstandby1.ora'; 关闭数据库实例，修改各节点pfile,然后以spfile启动数据库： SQL> shutdown immediate 以spfile启动实例到mount阶段(Data Guard）阶段; --启动到mount： startup mount --应用日志 alter database recover managed standby database using current logfile disconnect from session; 备库查看应用日志延迟时间： col name for a25 col value for a20 col unit for a30 set lines 200 select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag'); select process,client_process,sequence#,status from v$managed_standby; |
4 同步机制维护阶段
1）观察日志传输 跟踪primary,standby database的alert log,观察是否有错误发生，也可以看到应用日志的相关信息。 确认standby是否应用日志： --primary端多切几次日志，观察alert log信息。 alter system switch logfile; alter system switch logfile; --观察主备库日志序列号 archive log list; --primary端查询v$archived_log视图，确认日志是否被应用： set lines 300 pages 300 col name for a20 select name,dest_id,thread#,sequence#,standby_dest,applied,registrar,completion_time from v$archived_log where standby_dest='YES' order by thread#,sequence#; --primary端查询primary,standby的最大日志序列号是否一致： select 'Primary :' "DB Role",thread#,max(sequence#) from v$archived_log where standby_dest='NO' group by thread# union select 'Standby :' "DB Role",thread#,max(sequence#) from v$archived_log where standby_dest='YES' and applied='YES' group by thread# order by thread#; |
5 数据库割接
1)验证DG同步机制
1、检查主备角色 select database_role,switchover_status from v$database; 切换主库前状态为to_standby为正常。 2、在主备库上分别检查同步进程LNS/RFS/MRP: set echo off set lines 500 pages 40 set heading on set verify off col process for a7 heading 'PROCESS' col pid for 999999 heading 'OS PID' col status for a15 heading 'PROCESS_STATUS' col client_process for a7 heading 'CLIENT|PROCESS' col client_pid for a10 heading 'CLIENT|PID' col g_t for a5 heading 'GROUP|THREAD' col blocks for 999999999 col block# for 999999999 select a.process, a.pid, a.status, a.client_process, a.client_pid, a.group#||'.'||a.thread# g_t, a.sequence#, a.block#, a.blocks, a.delay_mins, a.known_agents, a.active_agents from v$managed_standby a; |
2)主备库切换
a)在关闭前，确认备库无延时 col name for a25 col value for a20 col unit for a30 set lines 200 select name,value,unit,time_computed from v$dataguard_stats where name in ('transport lag','apply lag'); select process,client_process,sequence#,status from v$managed_standby; b)原主库 alter database commit to switchover to physical standby ; c)原备库 ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY; alter database open; |
3)执行数据库升级脚本
[oracle@dbserver admin]$ sqlplus / as sysdba SQL*Plus: Release 11.2.0.4.0 Production on Wed Feb 20 22:49:22 2023 Copyright (c) 1982, 2013, Oracle. All rights reserved. SQL> alter database open upgrade; ORACLE instance started. SQL> select status from v$instance; STATUS ------------ OPEN MIGRATE SQL> @$ORACLE_HOME/rdbms/admin/catupgrd.sql 说明：此脚本在该库无数据的情况下耗时15min 备注：以上catupgrd.sql脚本执行完后会shutdown immediate数据库，重启数据库即可。 3.4.8、编译失效对象 3.4.8.1、查询失效对象 SQL> select * from dba_objects where status !='VALID'; SQL> select count(*) from dba_objects where status='INVALID'; COUNT(*) ---------- 453 3.4.8.2、编译失效对象 运行utlrp.sql编译失效对象 SQL> @?/rdbms/admin/utlrp 至此数据库已经升级完成。重启数据库没有问题就是升级成功。 SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL> startup ORACLE instance started. |
至此，数据库割接完成。
6 应用测试及上线
数据库恢复后，应用进行测试并确认上线或回退。
7 回退过程
原window failover为主库
1、备库停止日志应用 alter database recover managed standby database cancel; //关闭日志传输 alter database recover managed standby database finish force; 2、查看备库状态 //此时应该是TO PRIMARY select switchover_status from v$database; 3、备库切换到主库 alter database commit to switchover to primary with session shutdown; //这里如果存在gap，会报ORA-16139，需要强制切换 alter database activate physical standby database; 4、开启新主库 alter database open; //查看新主库状态 select open_mode,database_role,switchover_status from v$database; |