排除日志不传输的错误
查看目前归档
col Archive_dest form a30 col error form a30 set linesize 200 SELECT DEST_ID "ID", STATUS "DB_status", DESTINATION "Archive_dest", ERROR FROM V$ARCHIVE_DEST WHERE DEST_ID <=5; SQL> SQL> SQL> SQL> ID DB_status Archive_dest ERROR ---------- --------- ------------------------------ ------------------------------ 1 VALID /u01/app/oracle/oradata/orcl/a rchivelog 2 DISABLED orcl_st ORA-16047: DGID mismatch between destination setting and target database $ oerr ora 16047 16047, 00000, "DGID mismatch between destination setting and target database" // *Cause: The DB_UNIQUE_NAME specified for the destination did not match // the DB_UNIQUE_NAME at the target database. // *Action: Make sure the DB_UNIQUE_NAME specified in the LOG_ARCHIVE_DEST_n // parameter matches the DB_UNIQUE_NAME parameter defined at the // destination.
还是不行，检查了好久配置都是对的
SQL> alter system set log_archive_dest_state_2=enable scope=both; System altered.
把归档路径激活即可。
使用rman增量恢复使备库追上主库
查看备库日志 /u01/app/oracle/diag/rdbms/orcl_st/orcl/trace/alert_orcl.log ，发现里面有这样的错误提示：
Sun Jan 19 14:49:39 2020 FAL[client]: Failed to request gap sequence GAP - thread 1 sequence 87-87 DBID 1441600768 branch 913820033 FAL[client]: All defined FAL servers have been attempted. ------------------------------------------------------------ Check that the CONTROL_FILE_RECORD_KEEP_TIME initialization parameter is defined to a value that's sufficiently large enough to maintain adequate log switch information to resolve archivelog gaps. ------------------------------------------------------------
查询主库的SCN
SQL> SELECT CURRENT_SCN FROM V$DATABASE; CURRENT_SCN ----------- 2854613
查询备库的SCN，取消备库的同步
SQL> SELECT CURRENT_SCN FROM V$DATABASE; CURRENT_SCN ----------- 2831128 SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL; Database altered.
使用rman备份增量的scn
RMAN> backup incremental from scn 2831128 database format '/u01/app/oracle/oradata/orcl/add_%U' tag 'to_standby';
把备份的文件拷贝过去
[oracle@rhel65 orcl]$ scp oracle@192.168.86.31:/u01/app/oracle/oradata/orcl/add_* . The authenticity of host '192.168.86.31 (192.168.86.31)' can't be established. RSA key fingerprint is 03:cc:f3:ff:e8:f0:91:3a:5d:ba:1b:9f:e4:4d:65:b7. Are you sure you want to continue connecting (yes/no)? yes Warning: Permanently added '192.168.86.31' (RSA) to the list of known hosts. oracle@192.168.86.31's password: add_09umckp6_1_1 100% 26MB 26.2MB/s 00:00 add_0aumckpv_1_1 100% 9664KB 9.4MB/s 00:00 [oracle@rhel65 orcl]$ scp add3_0* oracle@192.168.86.32:/u01/app/oracle/oradata/orcl oracle@192.168.86.32's password: add3_0gumcmp9_1_1 100% 1200KB 1.2MB/s 00:00 add3_0humcmq3_1_1 根据备库的 RMAN> BACKUP INCREMENTAL FROM SCN 2856854 database format '/u01/app/oracle/oradata/orcl/add3_%U';
在备库上恢复
RMAN> catalog start with '/u01/app/oracle/oradata/orcl'; searching for all files that match the pattern /u01/app/oracle/oradata/orcl List of Files Unknown to the Database ===================================== File Name: /u01/app/oracle/oradata/orcl/add3_0humcmq3_1_1 File Name: /u01/app/oracle/oradata/orcl/add3_0gumcmp9_1_1 Do you really want to catalog the above files (enter YES or NO)? yes cataloging files... cataloging done List of Cataloged Files ======================= File Name: /u01/app/oracle/oradata/orcl/add3_0humcmq3_1_1 File Name: /u01/app/oracle/oradata/orcl/add3_0gumcmp9_1_1 RMAN> recover database; RMAN> recover database; Starting recover at 19-JAN-20 using channel ORA_DISK_1 channel ORA_DISK_1: starting incremental datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set destination for restore of datafile 00001: /u01/app/oracle/oradata/orcl/system01.dbf destination for restore of datafile 00002: /u01/app/oracle/oradata/orcl/sysaux01.dbf destination for restore of datafile 00003: /u01/app/oracle/oradata/orcl/undotbs01.dbf destination for restore of datafile 00004: /u01/app/oracle/oradata/orcl/users01.dbf destination for restore of datafile 00005: /u01/app/oracle/oradata/orcl/example01.dbf channel ORA_DISK_1: reading from backup piece /u01/app/oracle/oradata/orcl/add3_0gumcmp9_1_1 channel ORA_DISK_1: piece handle=/u01/app/oracle/oradata/orcl/add3_0gumcmp9_1_1 tag=TAG20200119T155625 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 starting media recovery unable to find archived log archived log thread=1 sequence=0 RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of recover command at 01/19/2020 15:57:34 RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 0 and starting SCN of 2856854 RMAN> RMAN> recover database noredo; Starting recover at 19-JAN-20 using channel ORA_DISK_1 Finished recover at 19-JAN-20
在备库上查询：
SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION; Database altered. SQL> SELECT CURRENT_SCN FROM V$DATABASE; CURRENT_SCN ----------- 2856853
解决日志应用的问题
SQL> select * from v$dataguard_stats; NAME VALUE UNIT TIME_COMPUTED DATUM_TIME ------------------------ ---------------- ------------------------------ ------------------------------ ------------------------ transport lag +00 00:00:00 day(2) to second(0) interval 02/02/2020 11:12:02 02/02/2020 11:12:01 apply lag +09 23:44:19 day(2) to second(0) interval 02/02/2020 11:12:02 02/02/2020 11:12:01 apply finish time +00 00:03:13.988 day(2) to second(3) interval 02/02/2020 11:12:02 estimated startup time 13 second 02/02/2020 11:12:02
可以看到日志传输没有问题，但有近10天没有应用了。
select name,controlfile_time from v$database; NAME CONTROLFILE_TIME ------------------------ ------------------- ORCL 2020-01-23 11:27:41
启动日志应用
SQL> alter database recover managed standby database using current logfile disconnect; Database altered.
检查发现并没有变成 read only and apply
SQL> select d.DB_UNIQUE_NAME,d.open_mode,d.log_mode,d.DATABASE_ROLE,d.PROTECTION_MODE from v$database d; DB_UNIQUE_NAME OPEN_MODE LOG_MODE DATABASE_ROLE PROTECTION_MODE ------------------------------ -------------------- ------------ ---------------- -------------------- orcl_pd READ ONLY ARCHIVELOG PHYSICAL STANDBY MAXIMUM PERFORMANCE
检查发现后台的恢复进程并没有启动，因为文件6坏了。
rchived Log entry 199 added for thread 1 sequence 68 ID 0x5ce13d5f dest 1: MRP0: Background Media Recovery terminated with error 19729 Errors in file /u01/app/oracle/diag/rdbms/orcl_pd/orcl/trace/orcl_mrp0_32562.trc: ORA-19729: File 6 is not the initial version of the plugged in datafile ORA-01110: data file 6: '/u01/app/oracle/product/11.2.0/dbhome_1/test.dbf' Managed Standby Recovery not using Real Time Apply Recovery interrupted! Recovered data files to a consistent state at change 3716693 MRP0: Background Media Recovery process shutdown (orcl) Sun Feb 02 11:35:56 2020
这个应该是controlfile和datafile不同步造成的，需要从新传输datafile。
