oracle11gdataguard线上维护问题记录

oracle11gdataguard线上维护问题记录

1，问题汇总1： 主备切换问题
SQL> select switchover_status from v$database;


SWITCHOVER_STATUS
--------------------
NOT ALLOWED
SQL>
SQL>
SQL> alter database commit to switchover to physical standby;
alter database commit to switchover to physical standby
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
SQL>
此错误的因为主库中有活动的会话造成的，解决的办法为：
1.杀掉活动会话，重新执行该命令
2.在该命令后面加上with session shutdown即
ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PHYSICAL STANDBY WITH SESSION SHUTDOWN;
Database altered.
SQL>
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.


Total System Global Area 835104768 bytes
Fixed Size 2217952 bytes
Variable Size 545261600 bytes
Database Buffers 281018368 bytes
Redo Buffers 6606848 bytes
Database mounted.
SQL> select switchover_status from v$database;


SWITCHOVER_STATUS
--------------------
TO PRIMARY
SQL>
2，问题汇总2：备库
SQL>alter database commit to switchover to primary;
alter database commit to switchover to primary
*
ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected
SQL>
SQL> alter database commit to switchover to primary with session shutdown;
Database altered.
SQL> alter database open;
Database altered.
SQL> select open_mode,database_role from v$database;
OPEN_MODE DATABASE_ROLE
-------------------- ----------------
READ WRITE PRIMARY
SQL>
3，问题汇总：switchover报错
新主库：
******************************************************************
LGWR: Setting 'active' archival for destination LOG_ARCHIVE_DEST_2
******************************************************************
LGWR: Attempting destination LOG_ARCHIVE_DEST_2 network reconnect (16086)
LGWR: Destination LOG_ARCHIVE_DEST_2 network reconnect abandoned
Errors in file /oracle/app/oracle/diag/rdbms/standby/standby/trace/standby_lgwr_3868.trc:
ORA-16086: Redo data cannot be written to the standby redo log
Error 16086 for archive log file 2 to 'master1'
LGWR: Failed to archive log 2 thread 1 sequence 4162 (16086)
Thread 1 advanced to log sequence 4162 (LGWR switch)
Current log# 2 seq# 4162 mem# 0: /oracle/app/oracle/data/powerdes/redo02.log
Archived Log entry 7779 added for thread 1 sequence 4161 ID 0xcec579c0 dest 1:
Wed Dec 23 19:41:16 2015
ARC3: Standby redo logfile selected for thread 1 sequence 4161 for destination LOG_ARCHIVE_DEST_2
新备库：
RFS[20562]: Assigned to RFS process 48163
RFS[20562]: Identified database type as 'physical standby': Client is ARCH pid 3969
RFS[20562]: Selected log 4 for thread 1 sequence 6860 dbid -827206911 branch 897707073
Wed Dec 23 23:18:42 2015
Using STANDBY_ARCHIVE_DEST parameter default value as USE_DB_RECOVERY_FILE_DEST
Archived Log entry 7166 added for thread 1 sequence 6860 ID 0xcec579c0 dest 1:
Wed Dec 23 23:18:42 2015
RFS[20563]: Assigned to RFS process 48165
RFS[20563]: Identified database type as 'physical standby': Client is ARCH pid 3965
Wed Dec 23 23:18:43 2015
Media Recovery Log /oracle/app/oracle/flash_recovery_area/MASTER/archivelog/2015_12_23/o1_mf_1_6860_c7q72l9y_.arc
Media Recovery Waiting for thread 1 sequence 6861
Wed Dec 23 23:18:44 2015
RFS[20564]: Assigned to RFS process 48168
RFS[20564]: Identified database type as 'physical standby': Client is LGWR SYNC pid 3868
Primary database is in MAXIMUM AVAILABILITY mode
Standby controlfile consistent with primary
Standby controlfile consistent with primary
RFS[20564]: No standby redo logfiles of file size 52428800 AND block size 512 exist
RFS[20564]: No standby redo logfiles selected (reason:7)
Errors in file /oracle/app/oracle/diag/rdbms/master/powerdes/trace/powerdes_rfs_48168.trc:
ORA-16086: Redo data cannot be written to the standby redo log
解决方案：
主库备库删除standby redo log日志组
SQL> alter database drop logfile group 6;
Database altered.
SQL> alter database drop logfile group 5;
Database altered.
SQL> alter database drop logfile group 4;
Database altered.
SQL>
在新主库新从库上建立3个standby redo log
alter database add standby logfile group 4 ('/oracle/app/oracle/data/powerdes/redo_dg_01.log') size 50m;
alter database add standby logfile group 5 ('/oracle/app/oracle/data/powerdes/redo_dg_12.log') size 50m;
alter database add standby logfile group 6 ('/oracle/app/oracle/data/powerdes/redo_dg_13.log') size 50m;
然后在新从库执行：执行redo应用：
alter database recover managed standby database disconnect from session;
alter system set standby_file_management='AUTO';

问题原因是：切换的时候没有在原来备库上先停止redo应用：
alter database recover managed standby database disconnect from session;
alter database recover managed standby database cancel;