win->linux
1.单机到单机迁移（尽量减少停机时间）
使用dataguard技术，可以跨系统、跨版本
停机时间:数据库升级时间，应用切换时间，IP地址切换等因素，需要做充分测试。
数据不能丢失

主库/win 备库/linux
ip 192.168.5.87 192.168.5.144
监听 动态/静态 静态
omf 启用 启用
db_recovery 启用 启用
db_name orcl orcl
db_unique_name orcl dgorcl

主库配置

1.启动强制日志

  1.  alter   database force logging;

2.启动归档

修改归档文件格式

  1.  alter  system set log_archive_format='%t_%s_%r.arc'  scope=spfile;

3.修改归档路径

  1. 使用快速闪回去存放归档
  2. alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

4.重启数据库

  1. shutdown immediate;
  2. startup mount;
  3. SQL> alter   database   archivelog ;                                                                                                                            
  4. SQL> alter   database   open;

5.设置初始化参数

—注意windows路径需要大写

  1. alter   system set log_archive_config='DG_CONFIG=(orcl,dgorcl)' scope=spfile;
  3. alter   system set log_archive_dest_1='location=USE_DB_RECOVERY_FILE_DEST  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl' scope=spfile; 
  5. alter system set log_archive_dest_2='SERVICE=tnsdgorcl LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=dgorcl' scope=both;  
  8. alter system set standby_file_management='AUTO' scope=both;
  10. alter  system set fal_client='tnsorcl' scope=both; 
  13. alter  system set fal_server='tnsdgorcl' scope=both;
  14. alter system set db_file_name_convert='/opt/oracle/oradata/orcl','D:\U01\APP\ORACLE\ORADATA\ORCL' scope=spfile;
  16. alter system set log_file_name_convert='/opt/oracle/oradata/orcl','D:\U01\APP\ORACLE\ORADATA\ORCL','/opt/oracle/fast_recovery_area/orcl/onlinelog','D:\U01\APP\RECOVERY_AREA\ORCL\ONLINELOG' scope=spfile;

备库配置

1.创建初始化参数文件

  1. *.audit_file_dest='/opt/oracle/admin/orcl/adump'                                                                                                                                                  
  2. *.compatible='12.2.0'                                                                                                                                                                            
  3. *.control_files='/opt/oracle/oradata/orcl/controlfile/control01.ctl','/opt/oracle/fast_recovery_area/orcl/controlfile/control02.ctl'#Restore Controlfile                                                                                                                                                                                
  4. *.db_file_name_convert='D:\U01\APP\ORACLE\ORADATA\ORCL','/opt/oracle/oradata/orcl/'                                                                                                      
  5. *.db_name='orcl'                                                                                                                                                                                   
  6. *.db_recovery_file_dest='/opt/oracle/fast_recovery_area/'                                                                                                                                         
  7. *.db_recovery_file_dest_size=20g                                                                                                                                                                 
  8. *.db_unique_name='dgorcl'                                                                                                                                                                           
  9. *.fal_client='tnsdgorcl'                                                                                                                                                                                
  10. *.fal_server='tnsorcl'                                                                                                                                                                               
  11. *.log_archive_config='DG_CONFIG=(orcl,dgorcl)'                                                                                                                                                        
  12. *.log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=dgorcl'                                                                                            
  13. *.log_archive_dest_2='SERVICE=tnsorcl LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=orcl'                                                                                    
  14. *.log_file_name_convert='D:\U01\APP\ORACLE\ORADATA\ORCL','/opt/oracle/oradata/orcl/','D:\U01\APP\RECOVERY_AREA\ORCL\ONLINELOG','/opt/oracle/fast_recovery_area/orcl/onlinelog/'      
  15. *.pga_aggregate_target=850m                                                                                                                                                                      
  16. *.remote_login_passwordfile='EXCLUSIVE'                                                                                                                                                          
  17. *.sga_target=2600m                                                                                                                                                                               
  18. *.standby_file_management='AUTO'

2.创建目录

  1.  mkdir -p  /opt/oracle/fast_recovery_area/orcl/onlinelog

3.启动到nomount阶段

注意:以spfile启动实例，不要用pfile启动

  1. create  spfile  from pfile;
  2. startup nomount;

4.创建监听

注意静态监听sid一定要大写

  1.  tnsping 192.168.5.87 1522
  2. tnsping 192.168.5.144 1522

linux备库 listener.ora 

  1. SID_LIST_LISTENER =                                                                                                                                              
  2.   (SID_LIST =                                                                                                                                                    
  3.     (SID_DESC =                                                                                                                                                  
  4.       (GLOBAL_DBNAME = orcl)                                                                                                                                     
  5.       (ORACLE_HOME = /opt/oracle/product/12.2.0/dbhome_1)                                                                                                        
  6.       (SID_NAME = ORCL)                                                                                                                                          
  7.     )                                                                                                                                                            
  8.   )                                                                                                                                                              
  10. LISTENER =                                                                                                                                                       
  11.   (DESCRIPTION =                                                                                                                                                 
  12.     (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.144)(PORT = 1522))                                                                                              
  13.   )                                                                                                                                                              
  15. ADR_BASE_LISTENER = /opt/oracle

linux备库 cat tnsnames.ora 

  1. TNSDGORCL =                                                                                                                                                      
  2.   (DESCRIPTION =                                                                                                                                                 
  3.     (ADDRESS_LIST =                                                                                                                                              
  4.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.144)(PORT = 1522))                                                                                            
  5.     )                                                                                                                                                            
  6.     (CONNECT_DATA =                                                                                                                                              
  7.       (SERVICE_NAME = orcl)                                                                                                                                      
  8.     )                                                                                                                                                            
  9.   )                                                                                                                                                              
  11. TNSORCL =                                                                                                                                                        
  12.   (DESCRIPTION =                                                                                                                                                 
  13.     (ADDRESS_LIST =                                                                                                                                              
  14.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.87)(PORT = 1522))                                                                                             
  15.     )                                                                                                                                                            
  16.     (CONNECT_DATA =                                                                                                                                              
  17.       (SERVICE_NAME = orcl)                                                                                                                                      
  18.     )                                                                                                                                                            
  19.   )

win 主库 listener.ora 

  1. SID_LIST_LISTENER =
  2.   (SID_LIST =
  3.     (SID_DESC =
  4.       (SID_NAME = CLRExtProc)
  5.       (ORACLE_HOME = D:\u01\app\oracle\product\12.2.0\dbhome_1)
  6.       (PROGRAM = extproc)
  7.       (ENVS = "EXTPROC_DLLS=ONLY:D:\u01\app\oracle\product\12.2.0\dbhome_1\bin\oraclr12.dll")
  8.     )
  9.     (SID_DESC =
  10.       (GLOBAL_DBNAME = orcl)
  11.       (ORACLE_HOME = D:\u01\app\oracle\product\12.2.0\dbhome_1)
  12.       (SID_NAME = ORCL)
  13.     )
  14.   )
  16. LISTENER =
  17.   (DESCRIPTION_LIST =
  18.     (DESCRIPTION =
  19.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  20.     )
  21.     (DESCRIPTION =
  22.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.87)(PORT = 1522))
  23.     )
  24.   )
  26. ADR_BASE_LISTENER = D:\u01\app\oracle\product\12.2.0\dbhome_1\log

win 主库 tnsnames.ora 

  1. TNSGDORCL =
  2.   (DESCRIPTION =
  3.     (ADDRESS_LIST =
  4.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.144)(PORT = 1522))
  5.     )
  6.     (CONNECT_DATA =
  7.       (SERVICE_NAME = orcl)
  8.     )
  9.   )
  11. ORACLR_CONNECTION_DATA =
  12.   (DESCRIPTION =
  13.     (ADDRESS_LIST =
  14.       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))
  15.     )
  16.     (CONNECT_DATA =
  17.       (SID = CLRExtProc)
  18.       (PRESENTATION = RO)
  19.     )
  20.   )
  22. TNSORCL =
  23.   (DESCRIPTION =
  24.     (ADDRESS_LIST =
  25.       (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.5.87)(PORT = 1522))
  26.     )
  27.     (CONNECT_DATA =
  28.       (SERVICE_NAME = orcl)
  29.     )
  30.   )

5.主备库参数检查

  1. db_unique_name 需重启数据库
  2. compatible='12.2.0'
  3. log_archive_config='DG_CONFIG=(fx,sfx)'  是指的DB_UNIQUE_NAME值
  4. log_archive_dest_1
  5. log_archive_dest_2
  6. log_archive_dest_state_1
  7. log_archive_dest_state_2
  8. ENABLE(默认)
  9. DEFER:禁用redo传输
  10. ALTERNATE:其他的传输失败,这个将启用。
  11. db_file_name_convert
  12. log_file_name_convert
  13. standby_file_management
  14. fal_client 指tnsname
  15. fal_server

7.使用Duplicate创建物理standby

  1. rman target sys/Oracle123@tnsorcl 
  2. connect auxiliary sys/Oracle123@tnsdgorcl ;
  4. duplicate target database for standby from active database nofilenamecheck;
  6. alter   database open;

8.添加Standby日志组

  1. alter database add standby logfile group 4  size 200m;
  2. alter database add standby logfile group 5  size 200m;
  3. alter database add standby logfile group 6  size 200m;
  4. alter database add standby logfile group 7  size 200m;
  7. alter database drop standby logfile group 4;
  8. alter database drop standby logfile group 5;
  9. alter database drop standby logfile group 6;
  10. alter database drop standby logfile group 7;
  12. 视图:
  13. v$logfile
  14. v$standby_log
  15. --------------------------------------------------------------
  16. select decode(g.THREAD#, null, vs.THREAD#, g.THREAD#) THREAD#,
  17.        decode(g.GROUP#, null, vs.GROUP#, g.GROUP#) GROUP#,
  18.        decode(g.BYTES, null, vs.BYTES / 1024 / 1024, g.BYTES / 1024 / 1024) BYTES,
  19.        decode(g.STATUS, null, vs.STATUS, g.STATUS) STATUS,
  20.        decode(g.ARCHIVED, null, vs.ARCHIVED, g.ARCHIVED) ARCHIVED,
  21.        --decode(g.GROUP#,null,'STANDBY REDO','REDO') type,
  22.        gf.TYPE,
  23.        gf.MEMBER
  24.   from v$logfile gf
  25.   left join v$log g
  26.     on gf.GROUP# = g.GROUP#
  27.   left join v$standby_log vs
  28.     on gf.GROUP# = vs.GROUP#
  29.  order by 1, 2;

9.开起实时同步

  1. 开启redo应用(实时同步)commit
  2. 自Oracle Database 12.1起，USING CURRENT LOGFILE子句已弃用
  3. alter database recover managed standby database using current logfile disconnect from session;弃用
  4. alter database recover managed standby database using current logfile disconnect;弃用
  6. alter database recover managed standby database disconnect;
  8. 停止redo应用
  9. alter database recover managed standby database cancel;
  10. 开启redo应用(日志切换才会同步)
  11. alter database recover managed standby database using archived logfile disconnect;
  12. alter system switch logfile;
  13. 查看状态:
  14. select name,open_mode,database_role,protection_mode,protection_level from v$database;

10.数据测试

  1. create   table   t (id int);
  2. insert into t values (100);

10.关闭同步

  1. alter database recover managed standby database cancel;