首先做一系列的check
check 当前primary 的 standby redo log是否存在
SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ------------------------------------------------------- --- 3 ONLINE /oracle_asm/standby/itid1/datafile/itid/redo03.log NO 2 ONLINE /oracle_asm/standby/itid1/datafile/itid/redo02.log NO 1 ONLINE /oracle_asm/standby/itid1/datafile/itid/redo01.log NO 4 STANDBY /oracle_asm/standby/itid1/standby_redo/slog1.rdo NO 5 STANDBY /oracle_asm/standby/itid1/standby_redo/slog2.rdo NO 6 STANDBY /oracle_asm/standby/itid1/standby_redo/slog3.rdo NO 7 STANDBY /oracle_asm/standby/itid1/standby_redo/slog4.rdo NO
check standby 的redo log是否存在
SQL> select * from v$logfile; GROUP# STATUS TYPE MEMBER IS_ ---------- ------- ------- ------------------------------------------------------- --- 3 ONLINE /oracle_asm/standby/itid2/datafile/itid/redo03.log NO 2 ONLINE /oracle_asm/standby/itid2/datafile/itid/redo02.log NO 1 ONLINE /oracle_asm/standby/itid2/datafile/itid/redo01.log NO 4 STANDBY /oracle_asm/standby/itid1/standby_redo/slog1.rdo NO 5 STANDBY /oracle_asm/standby/itid1/standby_redo/slog2.rdo NO 6 STANDBY /oracle_asm/standby/itid1/standby_redo/slog3.rdo NO 7 STANDBY /oracle_asm/standby/itid2/standby_redo/slog1.rdo NO 8 STANDBY /oracle_asm/standby/itid2/standby_redo/slog2.rdo NO 9 STANDBY /oracle_asm/standby/itid2/standby_redo/slog3.rdo NO 10 STANDBY /oracle_asm/standby/itid2/standby_redo/slog4.rdo NO 11 STANDBY /oracle_asm/standby/itid2/datafile/log1.rdo NO 12 STANDBY /oracle_asm/standby/itid2/datafile/log2.rdo NO 13 STANDBY /oracle_asm/standby/itid2/datafile/log3.rdo NO
oops 现在才发现之前创建standby的时候创建的standby redo log太多了。其实group 11 12 13 是我想创建成为redo log的。但是不小心的加上了standby参数。从这里也可以看出创建standby redo 和 redo 的命令就在于有没有standby 这个参数。 不过奇怪的是，既然我创建redo的命令错了，那么为什么还是有redo生成了呢？ 这个也许是oracle自己生成的。
check primary的 log_archive_dest_n参数
SQL> select name,value from v$parameter where name like 'log_archive_dest_%' and name not like '%state%' and value is not null; NAME VALUE ---------------------------- ------------------------------------------------------------------------------------ log_archive_dest_1 LOCATION=/oracle_asm/standby/itid1/arch log_archive_dest_2 SERVICE=itid2 SYNC VALID_FOR=( ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=itid2 log_archive_dest_3 LOCATION=/oracle_asm/standby/itid1/arch_from_standbyredo VALID_FOR=(STANDBY_LOGFILE, STANDBY_ROLE)
check standby的 log_archive_dest_n参数
SQL> select name,value from v$parameter where name like 'log_archive_dest_%' and name not like '%state%' and value is not null; NAME VALUE ---------------------------- ------------------------------------------------------------------------------------ log_archive_dest_1 LOCATION=/oracle_asm/standby/itid2/arch log_archive_dest_2 SERVICE=itid1 ASYNC VALID_FOR=(ONLINE_LOGFILES,PRIMARY_ROLE) DB_UNIQUE_NAME=itid1 log_archive_dest_3 LOCATION=/oracle_asm/standby/itid2/arch_from_standbyredo VALID_FOR=(STANDBY_LOGFILE, STANDBY_ROLE)
查看primary的datafile 和 logfile convert设置
SQL> show parameter db_file_name_convert NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string /oracle_asm/standby/itid2/data file/itid, /oracle_asm/standby /itid1/datafile/itid SQL> show parameter log_file_name_convert NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_file_name_convert string /oracle_asm/standby/itid2/data file/itid, /oracle_asm/standby /itid1/datafile/itid
查看standby的datafile和 logfile convert 设置
SQL> show parameter db_file_name_convert NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ db_file_name_convert string /oracle_asm/standby/itid1/data file/itid, /oracle_asm/standby /itid2/datafile/itid SQL> show parameter log_file_name_convert NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ log_file_name_convert string /oracle_asm/standby/itid1/data file/itid, /oracle_asm/standby /itid2/datafile/itid
check primary的 FAL设置
SQL> show parameter fal NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_client string fal_server string itid2
check standby的 FAL 设置
SQL> show parameter fal NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ fal_client string fal_server string itid1
check primary的standby_file_management
SQL> show parameter STANDBY_FILE_MANAGEMENT NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_file_management string AUTO
check standby的standby_file_management
SQL> show parameter STANDBY_FILE_MANAGEMENT NAME TYPE VALUE ------------------------------------ ----------- ------------------------------ standby_file_management string AUTO
check primary是否可以进行转换
SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO STANDBY
转换primary为standby
SQL> alter database commit to switchover to physical standby with session shutdown; Database altered.
据说这一步会把control file 备份到当前session的trace。
check standby 可以转化成primary
SQL> select switchover_status from v$database; SWITCHOVER_STATUS -------------------- TO PRIMARY
要注意的是这一步必须是在primary -> standby之后才有效否则的话返回的结果就是not allowed
turn standby to primary
SQL> alter database commit to switchover to primary with session shutdown; Database altered.
open 新的primary database
SQL> select status from v$instance; STATUS ------------ MOUNTED SQL> alter database open; Database altered.
在11gr2中turn standby to primary之后 新的primary是mount状态。
在新的standby上应用redo
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION * ERROR at line 1: ORA-01665: control file is not a standby control file
这一步遇到了错误说不是standby control file
通过下列步骤可以fix
SQL> shutdown abort ORACLE instance shut down. SQL> startup mount ORACLE instance started. Total System Global Area 502181888 bytes Fixed Size 1345912 bytes Variable Size 385877640 bytes Database Buffers 109051904 bytes Redo Buffers 5906432 bytes Database mounted. SQL> alter database open read only 2 ; Database altered. SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION; Database altered.
虽然我不知道为什么这样会fix。
check一下redo传输和应用的情况
primary端的日志archive情况如下
SQL> select NAME,SEQUENCE#, FIRST_TIME, NEXT_TIME FROM V$ARCHIVED_LOG where sequence#>104 ORDER BY SEQUENCE#; NAME SEQUENCE# FIRST_TIM NEXT_TIME ------------------------------------------------------- ---------- --------- --------- /oracle_asm/standby/itid2/arch/1_105_840039496.dbf 105 12-MAR-14 12-MAR-14 itid1 105 12-MAR-14 12-MAR-14 /oracle_asm/standby/itid2/arch/1_106_840039496.dbf 106 12-MAR-14 12-MAR-14 itid1 106 12-MAR-14 12-MAR-14
standby端日志apply如下
SQL> SELECT NAME,SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG WHERE sequence#>100 ORDER BY SEQUENCE#; NAME SEQUENCE# APPLIED -------------------------------------------------------------------------------- ---------- --------- itid2 101 YES /oracle_asm/standby/itid1/arch/1_101_840039496.dbf 101 YES itid2 102 NO /oracle_asm/standby/itid1/arch/1_102_840039496.dbf 102 YES /oracle_asm/standby/itid1/arch/1_103_840039496.dbf 103 YES itid2 103 NO /oracle_asm/standby/itid1/arch/1_104_840039496.dbf 104 YES /oracle_asm/standby/itid1/arch/1_105_840039496.dbf 105 YES /oracle_asm/standby/itid1/arch/1_106_840039496.dbf 106 NO /oracle_asm/standby/itid1/arch_from_standbyredo/1_106_840039496.dbf 106 IN-MEMORY