修改standby库的sys用户的密码导致DG无法传输日志及切换

主库DB_UNIQUE_NAME=DB1

备库DB_UNIQUE_NAME=DB2

1、由于备库只允许读，要修改sys用户的密码，须先进行主备切换，切换后的主备库状态如下

主库（原备库）：

SQL>  select open_mode,db_unique_name,switchover_status,database_role from v$database;
OPEN_MODE    DB_UNIQUE_NAME    SWITCHOVER_STATUS      DATABASE_ROLE
--------------------    --------------------------      ------------------------------      --------------------------
READ WRITE            DB2                                 TO STANDBY                         PRIMARY

备库（原主库）：

SQL>   select open_mode,db_unique_name,switchover_status,database_role from v$database;
OPEN_MODE     DB_UNIQUE_NAME  SWITCHOVER_STATUS    DATABASE_ROLE
-----------------------    -----------------------    --------------------------         --------------------------  

READ ONLY WITH APPLY         DB1          NOT ALLOWED               PHYSICAL STANDBY

2、修改sys、sysman、dbsnmp用户的密码

SQL> alter user sys identified by "3nManY]";
User altered.

SQL> alter user sysman identified by "3nManY]";
User altered.

SQL> alter user dbsnmp identified by "3nManY]";
User altered.

3、将主库切换到备库时报错，提示心跳失败 

SQL>  alter database commit to switchover to physical standby with session shutdown;
 alter database commit to switchover to physical standby with session shutdown
*
ERROR at line 1:
ORA-16038: log 3 sequence# 8315 cannot be archived
ORA-01031: insufficient privileges
ORA-00312: online log 3 thread 1:
'/dbdat/orclprd/flash_recovery_area/DB2/onlinelog/o1_mf_3_9z617rdb_.log'

4、查询主库运行状态，显示不正常

SQL>  select open_mode,db_unique_name,switchover_status,database_role from v$database;

OPEN_MODE     DB_UNIQUE_NAME    SWITCHOVER_STATUS    DATABASE_ROLE
--------------------    ---------------------------     ------------------------------       ---------------------------
READ WRITE                DB2                        FAILED DESTINATION                 PRIMARY

查看主库告警日志：

Mon Aug 25 12:58:34 2014
LGWR: Standby redo logfile selected for thread 1 sequence 8314 for destination LOG_ARCHIVE_DEST_2
Thread 1 advanced to log sequence 8314 (LGWR switch)
  Current log# 2 seq# 8314 mem# 0: /dbdat/orclprd/flash_recovery_area/DB2/onlinelog/o1_mf_2_9z617q1s_.log
Mon Aug 25 12:58:34 2014
Archived Log entry 33 added for thread 1 sequence 8313 ID 0x5282b2f6 dest 1:
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Waiting for all FAL entries to be archived...
All FAL entries have been archived.
Waiting for potential switchover target to become synchronized...
Active, synchronized Physical Standby  switchover target has been identified
Thread 1 cannot allocate new log, sequence 8315
Checkpoint not complete
  Current log# 2 seq# 8314 mem# 0: /dbdat/orclprd/flash_recovery_area/DB2/onlinelog/o1_mf_2_9z617q1s_.log
Mon Aug 25 12:58:35 2014
Error 1031 received logging on to the standby
Errors in file /opt/oracle/diag/rdbms/db2/orcl/trace/orcl_arc2_4755.trc:
ORA-01031: insufficient privileges
PING[ARC2]: Heartbeat failed to connect to standby 'DB1'. Error is 1031.
********************************************************************
LGWR: Resetting 'active' archival for destination LOG_ARCHIVE_DEST_2
********************************************************************
Destination LOG_ARCHIVE_DEST_2 no longer supports SYNCHRONIZATION

原因在于主备库的sys密码不一致，导致无法正常传输日志，需要重建口令文件

5、删除主备库的口令文件，然后在主库上创建口令文件并且传送到备库

$orapwd file=$ORACLE_HOME/dbs/orapworcl password=3nManY] entries=5
$scp $ORACLE_HOME/dbs/orapworcl oracle@172.18.70.11:$ORACLE_HOME/dbs/orapworcl

6、在主库上切换日志并查询切换状态

SQL> alter system switch logfile;

System altered.

SQL> select switchover_status from v$database;
SWITCHOVER_STATUS
--------------------
TO STANDBY

7、成功将主库切换到备库

SQL>  alter database commit to switchover to physical standby with session shutdown;
Database altered.
SQL> shutdown immediate;
ORA-01507: database not mounted
ORACLE instance shut down.
SQL> startup
ORACLE instance started.

Total System Global Area 1.3429E+10 bytes
Fixed Size    2217992 bytes
Variable Size 7583303672 bytes
Database Buffers 5771362304 bytes
Redo Buffers   71770112 bytes
Database mounted.

Database opened.

8、将备库切换到主库报错，提示要介质恢复

SQL>  alter database commit to switchover to primary with session shutdown;
 alter database commit to switchover to primary with session shutdown
*
ERROR at line 1:
ORA-16139: media recovery required

9、查询原备库运行状态，显示需要进行恢复

SQL>   selectopen_mode,db_unique_name,database_role from v$database;

OPEN_MODE    DB_UNIQUE_NAME    SWITCHOVER_STATUS  DATABASE_ROLE
---------------------     -----------------------      ---------------------------      --------------------------- 

READ ONLY         DB1                RECOVERY NEEDED       PHYSICALSTANDBY      

查看备库告警日志：

[oracle@DB1trace]$ tail alert_orcl.log
Database not available for switchover
  End-Of-REDO archived log file has been received
  Archived log files detected beyond End-Of-REDO
  Incomplete recovery SCN:0:-1259951315 archive SCN:0:-1259944318
Database not available for switchover
  End-Of-REDO archived log file has been received
  Archived log files detected beyond End-Of-REDO
  Incomplete recovery SCN:0:-1259951315 archive SCN:0:-1259944318
Switchover: Media recovery required - standby not in limbo
ORA-16139 signalled during: alter database commit to switchover to primary withsession shutdown...

10、在备库上应用日志

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

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SWITCHOVER LATENT

备库切换状态显示仍然不正常，还要进一步应用日志

SQL>  alter database recover managed standby database disconnect from session;

Database altered.

11、查询备库切换状态显示正常

SQL> select switchover_status from v$database;

SWITCHOVER_STATUS
--------------------
SESSIONS ACTIVE

查看备库告警日志：

Mon Aug 25 14:53:16 2014
 alter database recover managed standby database disconnect from session
Attempt to start background Managed Standby Recovery process (orcl)
Mon Aug 25 14:53:16 2014
MRP0 started with pid=152, OS id=7305 
MRP0: Background Managed Standby Recovery process started (orcl)
 started logmerger process
Mon Aug 25 14:53:21 2014
Managed Standby Recovery not using Real Time Apply
Parallel Media Recovery started with 32 slaves
Waiting for all non-current ORLs to be archived...
All non-current ORLs have been archived.
Media Recovery Log /dbdat/orclprd/oracle/archivelog/1_8316_840379839.dbf
Media Recovery Log /dbdat/orclprd/oracle/archivelog/1_8317_840379839.dbf
Completed:  alter database recover managed standby database disconnect from session
Media Recovery Log /dbdat/orclprd/oracle/archivelog/1_8318_840379839.dbf
Media Recovery Log /dbdat/orclprd/oracle/archivelog/1_8319_840379839.dbf
Media Recovery Log /dbdat/orclprd/oracle/archivelog/1_8320_840379839.dbf
Media Recovery Log /dbdat/orclprd/oracle/archivelog/1_8321_840379839.dbf
Media Recovery Log /dbdat/orclprd/oracle/archivelog/1_8322_840379839.dbf
Media Recovery Log /dbdat/orclprd/oracle/archivelog/1_8323_840379839.dbf
Media Recovery Log /dbdat/orclprd/oracle/archivelog/1_8324_840379839.dbf
Media Recovery Log /dbdat/orclprd/oracle/archivelog/1_8325_840379839.dbf
Media Recovery Log /dbdat/orclprd/oracle/archivelog/1_8326_840379839.dbf
Media Recovery Log /dbdat/orclprd/oracle/archivelog/1_8327_840379839.dbf
Media Recovery Log /dbdat/orclprd/oracle/archivelog/1_8328_840379839.dbf
Media Recovery Log /dbdat/orclprd/oracle/archivelog/1_8329_840379839.dbf
Media Recovery Log /dbdat/orclprd/oracle/archivelog/1_8330_840379839.dbf
Media Recovery Log /dbdat/orclprd/oracle/archivelog/1_8331_840379839.dbf
Identified End-Of-Redo for thread 1 sequence 8331
Resetting standby activation ID 1384297206 (0x5282b2f6)
Media Recovery End-Of-Redo indicator encountered
Media Recovery Applied until change 3035022978
MRP0: Media Recovery Complete: End-Of-REDO (orcl)
MRP0: Background Media Recovery process shutdown (orcl)
Mon Aug 25 14:54:04 2014

12、成功将备库切换到主库

SQL> ALTER DATABASE COMMIT TO SWITCHOVER TO PRIMARY   WITH SESSION SHUTDOWN;
Database altered.

SQL>  select open_mode,db_unique_name,switchover_status,database_role from v$database;

OPEN_MODE     DB_UNIQUE_NAME     SWITCHOVER_STATUS      DATABASE_ROLE
--------------------     ---------------------------       ------------------------------         ------------------------
READ WRITE                    DB1                       TO STANDBY                                 PRIMARY

SQL> alter database open;
Database altered.

总结：直接执行alter user sys identified by修改密码，这种方式是错误的，会导致两库因sys密码不一致而无法通信，我们应该用orapwd修改两库的sys密码。