dg安装 - oracle 12c dg - 《Oracle 优化教程》

主库配置
备库配置
角色转换之Switchover主备互换
报错解决

规划

	主库	备库
ip	192.168.5.21	192.168.5.22
监听	动态/静态	静态
omf	启用	启用
db_recovery	启用	启用

主库配置

1.启动强制日志

SQL> alter   database force logging;

2.启动归档

修改归档文件格式

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

3.修改归档路径

mkdir   /opt/oracle/arc
SQL> alter system set log_archive_dest_1='location=/opt/oracle/arc' scope=spfile;
-----------------------------------------
使用快速闪回去存放归档
alter system set log_archive_dest_1='LOCATION=USE_DB_RECOVERY_FILE_DEST';

4.重启数据库

shutdown immediate;
startup mount;
SQL> alter   database   archivelog ;                                                                                                                            
SQL> alter   database   open;

5.设置初始化参数

转换参数可以先不添加

alter   system set log_archive_config='DG_CONFIG=(fx,sfx)' scope=spfile;
alter   system set log_archive_dest_1='location=/opt/oracle/arc  VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=fx' scope=spfile; 
alter system set log_archive_dest_2='SERVICE=sfx LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=sfx' scope=both;  
alter system set standby_file_management='AUTO' scope=both;
alter  system set fal_client='fx' scope=both; 
alter  system set fal_server='sfx' scope=both;
alter system set db_file_name_convert='/opt/oracle/oradata/SFX/datafile','/opt/oracle/oradata/FX/datafile' scope=spfile;
alter system set log_file_name_convert='/opt/oracle/oradata/SFX/onlinelog','/opt/oracle/oradata/FX/onlinelog','/opt/oracle/fast_recovery_area/SFX/onlinelog','/opt/oracle/fast_recovery_area/FX/onlinelog' scope=spfile;

备库配置

1.创建初始化参数文件initsfx.ora

 在主库设置了omf的情况下，备库如果不设置。就算指定了oradata中转换日志文件的参数也不会生效。<br />      所以备库需要添加db_create_file_dest=''  参数。

*.audit_file_dest='/opt/oracle/admin/SFX/adump'                                                                                                                                                  
*.compatible='19.0.0'                                                                                                                                                                            
*.control_files='/opt/oracle/oradata/SFX/controlfile/o1_mf_gyhdym0y_.ctl','/opt/oracle/fast_recovery_area/SFX/controlfile/o1_mf_gyhdym26_.ctl'#Restore Controlfile                               
*.db_create_file_dest='/opt/oracle/oradata'                                                                                                                                                      
*.db_file_name_convert='/opt/oracle/oradata/FX/datafile','/opt/oracle/oradata/SFX/datafile'                                                                                                      
*.db_name='fx'                                                                                                                                                                                   
*.db_recovery_file_dest='/opt/oracle/fast_recovery_area'                                                                                                                                         
*.db_recovery_file_dest_size=20g                                                                                                                                                                 
*.db_unique_name='sfx'                                                                                                                                                                           
*.fal_client='sfx'                                                                                                                                                                                
*.fal_server='fx'                                                                                                                                                                               
*.log_archive_config='DG_CONFIG=(fx,sfx)'                                                                                                                                                        
*.log_archive_dest_1='LOCATION=/opt/oracle/arc VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=sfx'                                                                                            
*.log_archive_dest_2='SERVICE=fx LGWR SYNC AFFIRM VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=fx'                                                                                    
*.log_file_name_convert='/opt/oracle/oradata/FX/onlinelog','/opt/oracle/oradata/SFX/onlinelog','/opt/oracle/fast_recovery_area/FX/onlinelog','/opt/oracle/fast_recovery_area/SFX/onlinelog'      
*.pga_aggregate_target=850m                                                                                                                                                                      
*.remote_login_passwordfile='EXCLUSIVE'                                                                                                                                                          
*.sga_target=2600m                                                                                                                                                                               
*.standby_file_management='AUTO'
*.log_archive_format='%t_%s_%r.arc'

2.创建目录

[oracle@12cdg dbs]$ mkdir  /opt/oracle/arc
[oracle@12cdg dbs]$ mkdir  /opt/oracle/oradata/SFX  -p                                                     
[oracle@12cdg dbs]$ mkdir  /opt/oracle/admin/SFX/adump -p
[oracle@12cdg SFX]$ mkdir /opt/oracle/oradata/SFX/datafile                                                 
[oracle@12cdg SFX]$ mkdir /opt/oracle/oradata/SFX/onlinelog
[oracle@12cdg ~]$ mkdir /opt/oracle/oradata/SFX/onlinelog -p                                                                     
[oracle@12cdg ~]$ mkdir /opt/oracle/fast_recovery_area/SFX/controlfile/ -p

3.启动到nomount阶段

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

create  spfile  from pfile;
startup nomount;

4.创建监听

测试监听

tnsping fx
tnsping sfx

5.主备库参数检查

db_unique_name 需重启数据库
compatible='12.2.0'
log_archive_config='DG_CONFIG=(fx,sfx)'  是指的DB_UNIQUE_NAME值
log_archive_dest_1
log_archive_dest_2
log_archive_dest_state_1
log_archive_dest_state_2
ENABLE(默认)
DEFER:禁用redo传输
ALTERNATE:其他的传输失败,这个将启用。
db_file_name_convert
log_file_name_convert
standby_file_management
fal_client 指tnsname
fal_server

6.备库复制主库密码文件

重命名

 scp    oracle@192.168.5.21:/opt/oracle/product/19c/dbhome_1/dbs/orapwfx     orapwfx   
 mv orapwfx orapwsfx

7.使用Duplicate创建物理standby

rman备份集
备库此时处于nomount状态
rman target sys/Oracle123@fx  (必须显示指定密码,否则后面会报密码错误的提示)
RMAN> connect auxiliary sys/Oracle123@sfx
rman target sys/Oracle123@fx auxiliary sys/Oracle123@sfx
duplicate target database for standby from active database nofilenamecheck;

如果失败:
备库:
shutdown->startup nomount
数据文件都删除

主库rman链接备库

rman    target /
RMAN> connect auxiliary   sys/Oracle123@sfx 
RMAN> duplicate target database for standby from active database nofilenamecheck;

8.添加Standby日志组

Standby日志组=日志组+1

alter database add standby logfile group 4  size 200m;
alter database add standby logfile group 5  size 200m;
alter database add standby logfile group 6  size 200m;
alter database add standby logfile group 7  size 200m;
alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;
alter database drop standby logfile group 7;
视图:
v$logfile
v$standby_log
--------------------------------------------------------------
select decode(g.THREAD#, null, vs.THREAD#, g.THREAD#) THREAD#,
       decode(g.GROUP#, null, vs.GROUP#, g.GROUP#) GROUP#,
       decode(g.BYTES, null, vs.BYTES / 1024 / 1024, g.BYTES / 1024 / 1024) BYTES,
       decode(g.STATUS, null, vs.STATUS, g.STATUS) STATUS,
       decode(g.ARCHIVED, null, vs.ARCHIVED, g.ARCHIVED) ARCHIVED,
       --decode(g.GROUP#,null,'STANDBY REDO','REDO') type,
       gf.TYPE,
       gf.MEMBER
  from v$logfile gf
  left join v$log g
    on gf.GROUP# = g.GROUP#
  left join v$standby_log vs
    on gf.GROUP# = vs.GROUP#
 order by 1, 2;

9.开起实时同步

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

10.更改保护模式:

主库备库都需要执行 3 选 1

alter database set standby database to maximize performance;最大性能
alter database set standby database to maximize availability;最大可用
alter database set standby database to maximize protection;最大保护

11.【DG启停顺序】

启动:
先备库，后主库
关闭:
先主库，后备库
前提:干净的关闭

12.解决同步故障

1.查下主库2节点，备库的配置
2.检查tns配置
3.检查fal_client,fal_server
4.查下备库是否在实时应用模式
5.查询相关视图
v$managed_standby(备库查询)
RFS：传输进程
MRP0:日志应用进程
v$archive_dest(主库)
v$archive_dest_status(主库)
 select dest_name,status,error from v$archive_dest_status where dest_id = 2;
 select dest_name,status,error from v$archive_dest;

角色转换之Switchover主备互换

查看角色:primary和standby

select database_role from v$database;

切换:
1.switchover主备互换
2.failover故障转移
switchover:主库和备库相互转换角色。不会丢失数据。
failover：当主库不可用，将standby转换为primary。最大保护模式和最大可用模式下,可以保证不丢失数据。
switchover操作步骤:
1阶段:主库->备库
在此阶段，dg临时具有2个备用数据库（过渡状态）
2阶段:备库->主库

1.主库检查配置文件和standby redo log

redo +1
v$standby_log
alter  DATABASE  add standby logfile size 200m;
alter  DATABASE  add standby logfile size 200m;
alter  DATABASE  add standby logfile size 200m;
alter  DATABASE  add standby logfile size 200m;
------------------------------------------------------
select decode(g.THREAD#, null, vs.THREAD#, g.THREAD#) THREAD#,
       decode(g.GROUP#, null, vs.GROUP#, g.GROUP#) GROUP#,
       decode(g.BYTES, null, vs.BYTES / 1024 / 1024, g.BYTES / 1024 / 1024) BYTES,
       decode(g.STATUS, null, vs.STATUS, g.STATUS) STATUS,
       decode(g.ARCHIVED, null, vs.ARCHIVED, g.ARCHIVED) ARCHIVED,
       --decode(g.GROUP#,null,'STANDBY REDO','REDO') type,
       gf.TYPE,
       gf.MEMBER
  from v$logfile gf
  left join v$log g
    on gf.GROUP# = g.GROUP#
  left join v$standby_log vs
    on gf.GROUP# = vs.GROUP#
 order by 1, 2;

2.主库检查是否可以进行转换

SWITCHOVER_STATUS：TO STANDBY 则可以进行转换，其它值则需要进行排查异常

SQL> select name,database_role,switchover_status from v$database;                                                                
NAME      DATABASE_ROLE    SWITCHOVER_STATUS                                                                                     
--------- ---------------- --------------------                                                                                  
FX        PRIMARY          TO STANDBY

备库为

SQL> select name,database_role,switchover_status from v$database;                                                                
NAME      DATABASE_ROLE    SWITCHOVER_STATUS                                                                                     
--------- ---------------- --------------------                                                                                  
FX        PHYSICAL STANDBY NOT ALLOWED

验证是否有gap:

select status, gap_status from v$archive_dest_status where dest_id = 2;

最保险的还要执行,insert

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

3.主库执行切换

12c新语法:

alter database switchover to sfx verify;
alter database switchover to sfx ;
alter database switchover to sfx  force;     #强制切换
原主库状态为:shutdown
原备库状态为:mount
旧语法新功能:
swithover切换
alter database commit to switchover to physical standby with session shutdown;(会话连接着)
执行完毕实例关闭了
alter database commit to switchover to physical standby;（旧语法新功能）

4.启动数据库开启同步

startup
查询状态:
select open_mode,database_role from v$database;
开启实时应用:
alter database recover managed standby database disconnect;
--------------------------------------------------------------------

5.注意:

如果是生产环境，数据库运行时间很长,切换的时候时间会很长。
主备库:

alter system flush buffer_cache;
alter system checkpoint;
shutdown immediate

重启启动实例做切换

6.重新初始化备库

进入备库rman
rman   target / 
alter database recover managed standby database cancel;
RMAN> RECOVER STANDBY DATABASE FROM SERVICE orcl;
shutdown immediate;
startup
alter database recover managed standby database disconnect;

报错解决

ORA-01103: database name ‘FX’ in control file is not ‘SFX’

没有添加db_unique_name=’sfx’参数

ORA-10456:cannot open standby database;media recovery session may be in progress

在搭dataguard练习中，主库和从库已经配置好，主库已经执行过rman的duplicate操作，要把从库起到open状态时，报了上面错误：
解决方法：

先在备库停止standby
SQL>alter database recover managed standby database cancel;
这时再执行
SQL>alter database open;
库就正常open啦
然后再启动日志应用
SQL>alter database recover managed standby database using current logfile disconnect;

报ORA-16086: Redo data cannot be written to the standby redo log

standby日志文件大小跟主库日志文件大小不一致，取消同步，删除standby文件重建
alter database add standby logfile group 4 '/opt/oracle/oradata/SFX/onlinelog/standby01.log' size 300m;
alter database add standby logfile group 5 '/opt/oracle/oradata/SFX/onlinelog/standby02.log' size 300m;
alter database add standby logfile group 6 '/opt/oracle/oradata/SFX/onlinelog/standby03.log' size 300m;
alter database add standby logfile group 7 '/opt/oracle/oradata/SFX/onlinelog/standby04.log' size 300m;
alter database drop standby logfile group 4;
alter database drop standby logfile group 5;
alter database drop standby logfile group 6;
alter database drop standby logfile group 7;

ORA-01665: control file is not a standby control file

rman 进入备库直接进行主库到备库恢复
注意此语句在19c 测试支持，12c 未测试

rman   target / 
RMAN> RECOVER STANDBY DATABASE FROM SERVICE orcl;
可能遇到MAN-05535: warning: All redo log files were not defined properly.  错误
直接忽略可以不处理
最后开启同步
alter database recover managed standby database disconnect;

最大保护模式主数据库不能启动

报错信息：
SQL> startup mount;                                                                                                              
ORACLE instance started.                                                                                                         
Total System Global Area 2466249672 bytes                                                                                        
Fixed Size                  8899528 bytes                                                                                        
Variable Size             536870912 bytes                                                                                        
Database Buffers         1912602624 bytes                                                                                        
Redo Buffers                7876608 bytes                                                                                        
Database mounted.                                                                                                                
SQL> alter database set standby database to maximize protection;                                                                 
Database altered.                                                                                                                
SQL> alter database open;                                                                                                        
alter database open                                                                                                              
*                                                                                                                                
ERROR at line 1:                                                                                                                 
ORA-03113: end-of-file on communication channel                                                                                  
Process ID: 43659                                                                                                                
Session ID: 9 Serial number: 16405           
数据库状态：
 select database_role,protection_mode,protection_level from v$database;
 开启最大可用模式，protection_level为RESYNCHRONIZATION，
 DG的配置存在问题：
 检查点：
 1）log_archive_dest_2参数是否配置正确
 2）standby log是否创建成功
 3）主库是否开启了闪回
 select flashback_on from v$database;
 alter database flashback on;
#如果3点均满足，但是状态依然不对，建议重建standby log