使用日志传输的方法在两个数据库之间同步数据

环境准备

源 oracle18:oracle18c-standby 192.168.17.26
目标 oracle18-2:oracle18c-primary 192.168.17.109
实例名:orcl
Oracle版本:18.03

提前准备

SELECT CURRENT_SCN FROM V$DATABASE;

alter session set NLS_date_FORMAT=‘yyyy-mm-dd amhh12:mi:ss’;

select checkpoint_time from v$datafile_header;

SQL>  SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
    2497376

SQL>  select dbid,checkpoint_change# from v$database; 

      DBID CHECKPOINT_CHANGE#
---------- ------------------
1517707395	      2485187

目标:

SQL>  select dbid,checkpoint_change# from v$database; 

      DBID CHECKPOINT_CHANGE#
---------- ------------------
1517707395	      2407208

SQL> SELECT CURRENT_SCN FROM V$DATABASE;

CURRENT_SCN
-----------
	  0
	  
SQL> select open_mode from v$database;

OPEN_MODE
--------------------
MOUNTED

     FILE# ONLINE  ONLINE_ ERROR								CHANGE# TIME	      CON_ID
---------- ------- ------- ----------------------------------------------------------------- ---------- --------- ----------
	 1 ONLINE  ONLINE									2407208 13-APR-21	   1
	 3 ONLINE  ONLINE									2407208 13-APR-21	   1
	 4 ONLINE  ONLINE									2407208 13-APR-21	   1
	 7 ONLINE  ONLINE									2407208 13-APR-21	   1
SQL> select file#,recover,fuzzy,CHECKPOINT_CHANGE# from v$datafile_header;

     FILE# REC FUZ CHECKPOINT_CHANGE#
---------- --- --- ------------------
	 1 NO  YES	      2498751
	 3 NO  YES	      2498751
	 4 NO  YES	      2498751
	 5     NO	      1559612
	 6     NO	      1559612
	 7 NO  YES	      2498751
	 8     NO	      1559612
	 9     NO	      1569930
	10     NO	      1569930
	11     NO	      1569930
	12     NO	      1569930

11 rows selected.
alter session set NLS_date_FORMAT='yyyy-mm-dd amhh12:mi:ss';
set linesize 200
col name form a30
select name,SEQUENCE#,FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE#,NEXT_TIME from V$ARCHIVED_LOG;

NAME				SEQUENCE# FIRST_CHANGE# FIRST_TIME	      NEXT_CHANGE# NEXT_TIME
------------------------------ ---------- ------------- --------------------- ------------ ---------------------
/u01/app/oracle/product/18.0.0	       34	2407208 2021-04-13 pm02:49:29	   2447653 2021-04-14 am12:02:04
/db_1/dbs/arch1_34_989926663.d
bf

/u01/app/oracle/product/18.0.0	       35	2447653 2021-04-14 am12:02:04	   2485187 2021-04-14 am09:00:35
/db_1/dbs/arch1_35_989926663.d
bf

/u01/app/oracle/product/18.0.0	       36	2485187 2021-04-14 am09:00:35	   2498751 2021-04-14 pm02:08:03
/db_1/dbs/arch1_36_989926663.d
bf

开始恢复

使用下面的命令把日志传输到目标端:

scp oracle@192.168.17.26:/u01/app/oracle/product/18.0.0/db_1/dbs/arch1_36_989926663.dbf .
scp oracle@192.168.17.26:/u01/app/oracle/product/18.0.0/db_1/dbs/arch1_35_989926663.dbf .
scp oracle@192.168.17.26:/u01/app/oracle/product/18.0.0/db_1/dbs/arch1_34_989926663.dbf .
RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE
SQL> RECOVER DATABASE UNTIL CANCEL USING BACKUP CONTROLFILE;
ORA-00279: change 2447653 generated at 04/14/2021 00:02:04 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/18.0.0/db_1/dbs/arch1_35_989926663.dbf
ORA-00280: change 2447653 for thread 1 is in sequence #35


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/arch1_35_989926663.dbf
ORA-00279: change 2485187 generated at 04/14/2021 09:00:35 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/18.0.0/db_1/dbs/arch1_36_989926663.dbf
ORA-00280: change 2485187 for thread 1 is in sequence #36
ORA-00278: log file '/home/oracle/arch1_35_989926663.dbf' no longer needed for
this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
/home/oracle/arch1_36_989926663.dbf
ORA-00279: change 2498751 generated at 04/14/2021 14:08:03 needed for thread 1
ORA-00289: suggestion :
/u01/app/oracle/product/18.0.0/db_1/dbs/arch1_37_989926663.dbf
ORA-00280: change 2498751 for thread 1 is in sequence #37
ORA-00278: log file '/home/oracle/arch1_36_989926663.dbf' no longer needed for
this recovery


Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
cancel
Media recovery cancelled.
SQL> 
SQL> alter database open resetlogs;

Database altered.

输入下面的文件进行恢复:

/home/oracle/arch1_34_989926663.dbf
/home/oracle/arch1_35_989926663.dbf
/home/oracle/arch1_36_989926663.dbf

查询是否有datafile需要恢复:

SQL> select * from V$RECOVER_FILE ;

如果使用RECOVER DATABASE USING BACKUP CONTROLFILE,也就是没有UNTIL CANCEL,会出现下面的错误:

SQL> alter database open RESETLOGS;
alter database open RESETLOGS
*
ERROR at line 1:
ORA-01113: file 1 needs media recovery
ORA-01110: data file 1: '/u01/app/oracle/oradata/ORCL/system01.dbf'

姚远ACE CSDN认证博客专家 ACE 华为云 MVP
Oracle ACE,华为云 MVP,Oracle10g,12c OCM; MySQL 5.6,5.7,8.0 OCP;CCNA; EMC Certified; IBM P Certified; RHCE; SQLServer 764; DB2 Certified; TOEIC 890;获得过两次国家部级科技进步奖;发明过两项计算机专利。微信:yaoyuanace 邮箱:yaoyuanace(at)qq.com
已标记关键词 清除标记
相关推荐
©️2020 CSDN 皮肤主题: Age of Ai 设计师:meimeiellie 返回首页