Oracle Data Guard 主备切换和几个常见报错处理

1 Oracle Data Guard主备切换

1.1原主数据库执行

先关闭主库，再关闭备库，启动时先启动备库再启动主库。

1.1.1 将原主库启动到nomount的状态

1. SQL> Alter database commit to switchover to physical standby with session shutdown;
2. SQL> shutdown immediate;
3. SQL> startup nomount;

1.1.2更改主库为备库

1. SQL> alter database mount standby database;
2. SQL> alter database recover managed standby database disconnect from session;
4. 如果配置了 standby redo log 并需要启用实时同步则执行以下代码
5. SQL>alter database recover managed standby database using current logfile disconnect from session;

1.2原备库执行

1.2.1 将原备库切换为主

1. SQL> select switchover_status from v$database;
2. SQL> select * from v$version where rownum<2;
3. SQL> alter database commit to switchover to primary with session shutdown;
4. 如果备库还有未应用的日志则执行
5. SQL>alter database recover managed standby database disconnect from session;
6. SQL> shutdown immediate
7. SQL> startup

1.3 检查

1. 切换日志进行检查
2. SQL> select max(sequence#) from v$log;
3. SQL>select sequence#,applied from v$archived_log;
4. SQL> alter system switch logfile;

2 常见错误处理

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

解决办法
alter database recover managed standby database cancel;
alter database open;
alter database recover managed standby database using current logfile disconnect ;

2.2 ora-16004，ora-01152，ora-01110 从库启动报错，

解决办法：
SQL> alter database recover managed standby database using current logfile disconnect from session; （需要在备库建立standby logfile,否则会出现ORA-38500错误）
在主库
sql> alter system switch logfile;
在备库
SQL> alter database recover managed standby database cancel;
SQL> alter database open;

3 DG常用SQL命令

检测Data Guard方式的命令

1. SQL>SELECT PROTECTION_MODE FROM V$DATABASE;

修改Data Guard保护模式

1. SQL> ALTER DATABASE SET STANDBY DATABASE TO MAXIMIZE {AVAILABILITY | PERFORMANCE | PROTECTION};

对单实例数据库或RAC中的当前实例执行日志切换;

1. SQL> ALTER SYSTEM SWITCH LOGFILE

设置自动归档时间间隔（单位是秒）

1. SQL>alter system set archive_lag_target=1800 scope=both;

物理备库开启实时应用

1. SQL>alter database recover managed standby database using current logfile disconnect from session;

取消归档日志时间间隔-物理备库

1. SQL> alter database recover managed standby database nodelay ;

在主库和从库查看归档日志是否同步:

1. SQL>SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG ORDER BY SEQUENCE#;

手动切换归档,查看归档文件是否自动同步到备用库. 切换归档:

1. SQL> alter system switch logfile;

查看主备切换状态:

1. SQL>select switchover_status from v$database;