广告

扫码关注官方微信

扫码下载APP
返回顶部
首页 > 资讯 > 数据库 >ORACLE中主从中断后如何恢复
  • 519

0
分享到

ORACLE中主从中断后如何恢复

2024-04-02 19:04:59 519人浏览 泡泡鱼
摘要

这篇文章给大家介绍oracle中主从中断后如何恢复，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。一、知识回顾1、正常情况我们在生产中配置ADG会使用最大可用模式配合参数lgwr和syn

这篇文章给大家介绍oracle中主从中断后如何恢复，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。

一、知识回顾
1、正常情况我们在生产中配置ADG会使用最大可用模式配合参数lgwr和sync。
sql> show parameter log_arcHive_dest_2
NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_2       string    SERVICE=stdtest lgwr sync affi
                               rm VALID_FOR=(ONLINE_LOGFILES,
                               PRIMARY_ROLE) DB_UNIQUE_NAME=s
                               tdtest
这种配置在保证备库同步情况不影响主库的情况下最大限度的保证了主备的实时性。

SQL> select name,dbid,database_role,protection_mode from v$database;

NAME  DBID DATABASE_ROLE    PROTECTION_MODE
--------- ---------- ---------------- --------------------
PRITEST   2249383711 PRIMARY       MAXIMUM AVaiLABILITY

2、监控进程
主库：
SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;
PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH   ARCH    145 CLOSING
ARCH   ARCH    135 CLOSING
ARCH   ARCH    141 CLOSING
ARCH   ARCH    146 CLOSING
LGWR   LGWR    147 WRITING

备库：
SQL> SELECT PROCESS, CLIENT_PROCESS, SEQUENCE#, STATUS FROM V$MANAGED_STANDBY;
PROCESS   CLIENT_P  SEQUENCE# STATUS
--------- -------- ---------- ------------
ARCH   ARCH    145 CLOSING
ARCH   ARCH    146 CLOSING
ARCH   ARCH      0 CONNECTED
ARCH   ARCH    144 CLOSING
RFS   ARCH      0 IDLE
RFS   UNKNOWN     0 IDLE
RFS   LGWR    147 IDLE
RFS   UNKNOWN     0 IDLE
MRP0   N/A    147 APPLYING_LOG

3、监控恢复操作的进程
SELECT ARCHIVED_THREAD#, ARCHIVED_SEQ#, APPLIED_THREAD#, APPLIED_SEQ# FROM V$ARCHIVE_DEST_STATUS;
ARCHIVED_THREAD# ARCHIVED_SEQ# APPLIED_THREAD# APPLIED_SEQ#
---------------- ------------- --------------- ------------
        1    146       0    0
        .......
        1    146       1  146
32 rows selected.

4、回顾三种同步模式
4.1 最大保护模式（Maximum Protection）
1）这种模式提供了最高级别的数据保护能力；
2）要求至少一个物理备库收到重做日志后，主库的事务才能够提交；
3）主库找不到合适的备库写入时，主库会自动关闭，防止未受保护的数据出现；
4）优点：该模式可以保证备库没有数据丢失；
5）缺点：主库的自动关闭会影响到主库的可用性，同时需要备库恢复后才能提交，对网络等客观条件要求非常的高，
         主库的性能会因此受到非常大的冲击。

4.2 最大可用性模式（Maximum Availability）
1）该模式提供了仅次于“最大保护模式”的数据保护能力；
2）要求至少一个物理备库收到重做日志后，主库的事务才能够提交；
3）主库找不到合适的备库写入时，主数据库不会关闭，在没有达到net_timeout之前主库会hang住，但是并不是shutdown。而后主数据库以
   最大性能模式运行直到故障消除，并且解决所有重做日志文件的中断。当所有中断解决之后，主数据库自动继续以最大可用性模式运行；
4）优点：该模式可以在没有问题出现的情况下，保证备库没有数据丢失，是一种折中的方法；
5）缺点：在正常运行的过程中缺点是主库的性能受到诸多因素的影响。

4.3 最大性能模式（Maximum PerfORMance）
1）该模式是默认模式，可以保证主数据库的最高可用性；
2）保证主库运行过程中不受备库的影响，主库事务正常提交，不因备库的任何问题影响到主库的运行；
3）优点：避免了备库对主数据库的性能和可用性影响；
4）缺点：如果与主库提交的事务相关的恢复数据没有发送到备库，这些事务数据将被丢失，不能保证数据无损失。


二、主备库同步中断后，如何恢复同步
在很多场合下主从同步中断，如恢复同步可能会遇到很多不同情况，现根据几个场景做以下实验。
实验一
主备中断后，当主库归档日志完整，备库启动后会自动恢复
1、查看主备库日志情况
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
     63
2、备库：
模拟备库故障，直接关机
SQL> shutdown abort
ORACLE instance shut down

3、主库：
SQL> alter system switch logfile;
System altered.
SQL> create table a (id integer);
Table created.
SQL> alter system switch logfile;
System altered.
SQL> insert into a values (11);
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
     68
4、备库：
模拟排除故障，重新同步备库。
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1185853440 bytes
Fixed Size      2252664 bytes
Variable Size    754974856 bytes
Database Buffers   419430400 bytes
Redo Buffers      9195520 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database recover managed standby database using current logfile disconnect from session;
Database altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
     68
由于主库归档日志都在，在备库故障期间的log file gap，会被归档的日志文件由arch进程负责传输到从数据库。
同时通过LNSn把归档日志传到备库，备库RFS接受，MRP进程应用到standby redo log。
查看日志应用情况。
SQL> select sequence#, applied from v$archived_log;
 SEQUENCE# APPLIED
---------- ---------
  7 YES
 ......
 68 IN-MEMORY


实验二
由于归档丢失或备库控制文件损坏等，需主库全量备份恢复
1、备库模拟宕机，直接关闭数据库
SQL> shutdown abort
ORACLE instance shut down.

2、在备库宕机期间主库做一些操作
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
     70
SQL> create table b (name char(1));
Table created.
SQL> insert into b values ('a');
1 row created.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
     72
[oracle@pritest archivelog]$ pwd
/u01/app/oracle/archivelog
[oracle@pritest archivelog]$ ls -trl
......
-rw-r----- 1 oracle oinstall   444416 Nov 10 11:00 arch_1_69_956333727.arc
-rw-r----- 1 oracle oinstall  1224704 Nov 10 11:00 arch_1_70_956333727.arc
-rw-r----- 1 oracle oinstall  1097216 Nov 10 11:32 arch_1_71_956333727.arc
-rw-r----- 1 oracle oinstall  4003840 Nov 10 13:53 arch_1_72_956333727.arc

3、主库删掉备库宕机期间产生的归档日志
[oracle@pritest archivelog]$ rm arch_1_7*

4、启动备库
由于归档日志丢失，备库不能直接恢复
SQL> startup nomount
ORACLE instance started.
Total System Global Area 1185853440 bytes
Fixed Size      2252664 bytes
Variable Size    754974856 bytes
Database Buffers   419430400 bytes
Redo Buffers      9195520 bytes
SQL> alter database mount standby database;
Database altered.
SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-10458: standby database requires recovery
ORA-01196: file 1 is inconsistent due to a failed media recovery session
ORA-01110: data file 1: '/u01/app/oracle/oradata/pritest/system01.dbf'


5、备份主库控制文件和做全备，并同步主库
SQL> alter database create standby controlfile as '/tmp/controldg01.ctl';
Database altered.
[oracle@pritest pritest]$ scp -r /tmp/controldg01.ctl oracle@192.168.91.129:/u01/app/oracle/oradata/pritest/control01.ctl
oracle@192.168.91.129's passWord:
controldg01.ctl                               100% 9808KB   9.6MB/s   00:00   
[oracle@pritest pritest]$ scp -r /tmp/controldg01.ctl oracle@192.168.91.129:/u01/app/oracle/fast_recovery_area/pritest/control02.ctl
oracle@192.168.91.129's password:
controldg01.ctl                               100% 9808KB   9.6MB/s   00:00 


[oracle@pritest archivelog]$ rman target /
RMAN> backup database format '/u01/app/oracle/backup/full_%d_%T_%s_%U' plus archivelog FORMAT '/u01/app/oracle/backup/arc_%d_%s_%p.bak';
Starting backup at 2017-11-10 14:18:11
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 11/10/2017 14:18:13
RMAN-06059: expected archived log not found, loss of archived log compromises recoverability
ORA-19625: error identifying file /u01/app/oracle/archivelog/arch_1_7_956333727.arc
ORA-27037: unable to obtain file status
linux-x86_64 Error: 2: No such file or directory
Additional information: 3
RMAN> crosscheck archivelog all;
。。。。。。
RMAN> delete expired archivelog all;
released channel: ORA_DISK_1
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=38 device type=DISK
List of Archived Log Copies for database with db_unique_name PRITEST
=====================================================================
Key     Thrd Seq     S Low Time          
------- ---- ------- - -------------------
2       1    7       X 2017-10-02 16:29:17
        Name: /u01/app/oracle/archivelog/arch_1_7_956333727.arc
123     1    70      X 2017-11-10 11:00:02
        Name: /u01/app/oracle/archivelog/arch_1_70_956333727.arc
124     1    71      X 2017-11-10 11:00:05
        Name: /u01/app/oracle/archivelog/arch_1_71_956333727.arc
125     1    72      X 2017-11-10 11:32:15
        Name: /u01/app/oracle/archivelog/arch_1_72_956333727.arc

Do you really want to delete the above objects (enter YES or NO)? yes
deleted archived log
archived log file name=/u01/app/oracle/archivelog/arch_1_7_956333727.arc RECID=2 STAMP=956335716
deleted archived log
archived log file name=/u01/app/oracle/archivelog/arch_1_70_956333727.arc RECID=123 STAMP=959684405
deleted archived log
archived log file name=/u01/app/oracle/archivelog/arch_1_71_956333727.arc RECID=124 STAMP=959686335
deleted archived log
archived log file name=/u01/app/oracle/archivelog/arch_1_72_956333727.arc RECID=125 STAMP=959694835
Deleted 4 EXPIRED objects

RMAN> backup database format '/u01/app/oracle/backup/full_%d_%T_%s_%U' plus archivelog FORMAT '/u01/app/oracle/backup/arc_%d_%s_%p.bak';
......
Starting backup at 2017-11-10 15:43:09
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=81 RECID=141 STAMP=959701390
channel ORA_DISK_1: starting piece 1 at 2017-11-10 15:43:10
channel ORA_DISK_1: finished piece 1 at 2017-11-10 15:43:11
piece handle=/u01/app/oracle/backup/arc_PRITEST_20_1.bak tag=TAG20171110T154310 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2017-11-10 15:43:11

RMAN> exit

[oracle@pritest backup]$ ll
total 1152500
-rw-r----- 1 oracle oinstall   72775680 Nov 10 15:42 arc_PRITEST_16_1.bak
-rw-r----- 1 oracle oinstall   25392128 Nov 10 15:42 arc_PRITEST_17_1.bak
-rw-r----- 1 oracle oinstall      22528 Nov 10 15:43 arc_PRITEST_20_1.bak
-rw-r----- 1 oracle oinstall 1071833088 Nov 10 15:43 full_PRITEST_20171110_18_0isj7ob8_1_1
-rw-r----- 1 oracle oinstall   10125312 Nov 10 15:43 full_PRITEST_20171110_19_0jsj7ocb_1_1
[oracle@pritest backup]$ pwd
/u01/app/oracle/backup
[oracle@pritest backup]$ scp -r ./* oracle@192.168.91.129:/u01/app/oracle/backup/
oracle@192.168.91.129's password:
arc_PRITEST_11_1.bak                          100%   69MB  69.4MB/s   00:01   
arc_PRITEST_12_1.bak                          100%   23MB  23.0MB/s   00:00   
arc_PRITEST_15_1.bak                          100%   40KB  39.5KB/s   00:00   
full_PRITEST_20171110_13_0dsj7m2i_1_1         100% 1023MB  39.3MB/s   00:26   
full_PRITEST_20171110_14_0esj7m4b_1_1         100% 9888KB   9.7MB/s   00:00  


6、用备份恢复备库
SQL> shutdown abort
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

[oracle@stdtest backup]$ rman target /
RMAN> catalog start with '/u01/app/oracle/backup';
Starting implicit crosscheck backup at 2017-11-10 15:47:17
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=32 device type=DISK
Finished implicit crosscheck backup at 2017-11-10 15:47:18
Starting implicit crosscheck copy at 2017-11-10 15:47:18
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 2017-11-10 15:47:18
searching for all files in the recovery area
cataloging files...
no files cataloged
searching for all files that match the pattern /u01/app/oracle/backup
List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/backup/arc_PRITEST_11_1.bak
File Name: /u01/app/oracle/backup/arc_PRITEST_12_1.bak
File Name: /u01/app/oracle/backup/arc_PRITEST_17_1.bak
File Name: /u01/app/oracle/backup/arc_PRITEST_15_1.bak
File Name: /u01/app/oracle/backup/arc_PRITEST_16_1.bak
File Name: /u01/app/oracle/backup/full_PRITEST_20171110_18_0isj7ob8_1_1
File Name: /u01/app/oracle/backup/full_PRITEST_20171110_19_0jsj7ocb_1_1
File Name: /u01/app/oracle/backup/full_PRITEST_20171110_14_0esj7m4b_1_1
File Name: /u01/app/oracle/backup/full_PRITEST_20171110_13_0dsj7m2i_1_1
File Name: /u01/app/oracle/backup/arc_PRITEST_20_1.bak
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/backup/arc_PRITEST_11_1.bak
File Name: /u01/app/oracle/backup/arc_PRITEST_12_1.bak
File Name: /u01/app/oracle/backup/arc_PRITEST_17_1.bak
File Name: /u01/app/oracle/backup/arc_PRITEST_15_1.bak
File Name: /u01/app/oracle/backup/arc_PRITEST_16_1.bak
File Name: /u01/app/oracle/backup/full_PRITEST_20171110_18_0isj7ob8_1_1
File Name: /u01/app/oracle/backup/full_PRITEST_20171110_19_0jsj7ocb_1_1
File Name: /u01/app/oracle/backup/full_PRITEST_20171110_14_0esj7m4b_1_1
File Name: /u01/app/oracle/backup/full_PRITEST_20171110_13_0dsj7m2i_1_1
File Name: /u01/app/oracle/backup/arc_PRITEST_20_1.bak

RMAN> restore database;
Starting restore at 2017-11-10 15:18:38
Starting implicit crosscheck backup at 2017-11-10 15:18:38
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 2017-11-10 15:18:39
Starting implicit crosscheck copy at 2017-11-10 15:18:39
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 2017-11-10 15:18:39
searching for all files in the recovery area
cataloging files...
no files cataloged
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/app/oracle/oradata/pritest/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/pritest/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/pritest/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/pritest/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/full_PRITEST_20171110_13_0dsj7m2i_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/full_PRITEST_20171110_13_0dsj7m2i_1_1 tag=TAG20171110T150346
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 2017-11-10 15:19:14

RMAN> recover database;
Starting recover at 2017-11-10 15:49:39
using channel ORA_DISK_1
starting media recovery
archived log for thread 1 with sequence 80 is already on disk as file /u01/app/oracle/archivelog/arch_1_80_956333727.arc
archived log for thread 1 with sequence 81 is already on disk as file /u01/app/oracle/archivelog/arch_1_81_956333727.arc
archived log for thread 1 with sequence 82 is already on disk as file /u01/app/oracle/archivelog/arch_1_82_956333727.arc
archived log for thread 1 with sequence 83 is already on disk as file /u01/app/oracle/archivelog/arch_1_83_956333727.arc
archived log file name=/u01/app/oracle/archivelog/arch_1_80_956333727.arc thread=1 sequence=80
archived log file name=/u01/app/oracle/archivelog/arch_1_81_956333727.arc thread=1 sequence=81
archived log file name=/u01/app/oracle/archivelog/arch_1_82_956333727.arc thread=1 sequence=82
archived log file name=/u01/app/oracle/archivelog/arch_1_83_956333727.arc thread=1 sequence=83
unable to find archived log
archived log thread=1 sequence=84
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/10/2017 15:49:40
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 84 and starting SCN of 1089419

这报错可以忽略，主机84号归档日志还未归档。

SQL> alter database open read only;
Database altered.

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

7、验证主备库日志同步
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
     83

实验三
1、场景介绍
当主备同步中断了，备库想快一点恢复，偏偏这个时候归档太多恢复不过来或者说需要的归档直接丢了，可以选择
重新搭建备库。如果库小的话还是可以的，但是如果主库比较大可能耗费的时间会很久，而且容易出一些问题。单单是全库
备份恢复这个时间就不会短，更何况中间还会涉及到很多东西。其实利用基于scn的备份去恢复我们的备库，从而绕开中间
过多或者丢失的归档。
我们都知道我们传统的dg都是属于物理dg，下面是物理dg的简单解释：
物理备用数据库：以基于块对块的主数据库同样的磁盘数据库结构，物理备用数据库物理等同于主数据库。
特性：
1.数据库的每一个块的内容包括块的逻辑位置都和主库完全一致
2.DG通过执行重做应用，维护物理备用数据库
3.物理STANDBY 打开flashbackdatabase后可以完全读写打开
4.物理备用数据库使用通过oracle恢复机制，从归档重做日志文件或直接从备系统上的备重做日志文件用用重做数据来恢复。
5.物理备用数据库可用于执行备份
6.物理备用数据库使用重做应用技术使用低级别的恢复机制应用更改，绕过了所有SQL基本代码层，因此应用海量重做数据最有效，
  性能大于逻辑备份。
我们找到备库端数据文件中最低的scn，然后在主库去基于这个scn进行备份，这个时候rman回去扫描整个主库的块，如果块内的scn小于
备库端数据文件中最低的scn，则证明这个块从备库应用到的时间点到现在是没有改变的，就忽略掉这个块。如果块内的scn大于备库端数据
文件中最低的scn证明在这个阶段这个快进行了修改，就记录下这个块的内容。等拿到备库端去恢复的时候就替换这个块的内容。
官方文档mos的id(Doc ID 836986.1)，大家可自行去查看。
Steps to perform for Rolling Forward aPhysical Standby Database using RMAN Incremental Backup.

2、开始模拟实验
2.1 直接关闭备库数据库
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
    123
SQL> shutdown abort
ORACLE instance shut down.

2.2 在备库宕机期间主库数据变化
SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
    123

SQL> alter database enable block change tracking using file '/u01/app/oracle/oradata/pritest/rman_change_track.bct' reuse;
Database altered.
SQL> col filename for a55
SQL> select * from v$block_change_tracking;
STATUS    FILENAME       BYTES
---------- ------------------------------------------------------- ----------
ENABLED    /u01/app/oracle/oradata/pritest/rman_change_track.bct     11599872

SQL> update a set id=18;
1 row updated.
SQL> commit;
Commit complete.
SQL> alter system switch logfile;
System altered.
SQL> create table ee (id integer);
Table created.
SQL> alter system switch logfile;
System altered.
SQL> alter table ee move tablespace USERS;
Table altered.
SQL> alter system switch logfile;
System altered.

2.3 模拟主库丢失归档日志
[oracle@pritest archivelog]$ ls -trl
...
-rw-r----- 1 oracle oinstall   989184 Nov 16 16:20 arch_1_124_956333727.arc
-rw-r----- 1 oracle oinstall    24576 Nov 16 16:21 arch_1_125_956333727.arc
-rw-r----- 1 oracle oinstall    31232 Nov 16 16:21 arch_1_126_956333727.arc
-rw-r----- 1 oracle oinstall    17920 Nov 16 16:22 arch_1_127_956333727.arc

[oracle@pritest archivelog]$ rm arch_1_125_956333727.arc arch_1_126_956333727.arc

2.4 查找备库数据文件最低的scn
[oracle@stdtest ~]$ sqlplus / as sysdba
SQL> startup mount
SQL> select CHECKPOINT_CHANGE#  from v$datafile_header order by 1;
CHECKPOINT_CHANGE#
------------------
    1160832
    1160832
    1160832
    1160832

SQL> select CHECKPOINT_CHANGE#  from v$database  order by 1;
CHECKPOINT_CHANGE#
------------------
    1159823

SQL> shutdown abort
ORACLE instance shut down.


2.5 备份主库控制文件,更新备库控制文件
SQL> alter database create standby controlfile as '/tmp/controldg01.ctl';
Database altered.
[oracle@pritest ~]$ scp -r /tmp/controldg01.ctl oracle@192.168.91.129:/u01/app/oracle/oradata/pritest/control01.ctl
oracle@192.168.91.129's password:
controldg01.ctl                               100% 9872KB   9.6MB/s   00:00   
[oracle@pritest ~]$ scp -r /tmp/controldg01.ctl oracle@192.168.91.129:/u01/app/oracle/fast_recovery_area/pritest/control02.ctl
oracle@192.168.91.129's password:
controldg01.ctl                               100% 9872KB   9.6MB/s   00:00  

2.6 执行基于SCN的增量备份
[oracle@pritest backup]$ rman target /
RMAN> run {
sql 'alter system switch logfile';
backup incremental from scn 1159823 database format '/u01/app/oracle/backup/incre_%d_%T_%s_%U' tag 'FORSTANDBY';
}2> 3> 4>

using target database control file instead of recovery catalog
sql statement: alter system switch logfile

Starting backup at 2017-11-14 11:07:02
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=53 device type=DISK
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/app/oracle/oradata/pritest/system01.dbf
input datafile file number=00002 name=/u01/app/oracle/oradata/pritest/sysaux01.dbf
input datafile file number=00003 name=/u01/app/oracle/oradata/pritest/undotbs01.dbf
input datafile file number=00004 name=/u01/app/oracle/oradata/pritest/users01.dbf
channel ORA_DISK_1: starting piece 1 at 2017-11-14 11:07:02
channel ORA_DISK_1: finished piece 1 at 2017-11-14 11:07:03
piece handle=/u01/app/oracle/backup/incre_PRITEST_20171114_32_10sjhpmm_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
including current control file in backup set
channel ORA_DISK_1: starting piece 1 at 2017-11-14 11:07:04
channel ORA_DISK_1: finished piece 1 at 2017-11-14 11:07:05
piece handle=/u01/app/oracle/backup/incre_PRITEST_20171114_33_11sjhpmn_1_1 tag=FORSTANDBY comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 2017-11-14 11:07:05

RMAN> list backupset; (我有点不解？)
using target database control file instead of recovery catalog
specification does not match any backup in the repository

[oracle@pritest backup]$ ls -trl
total 13808
-rw-r----- 1 oracle oinstall  3981312 Nov 16 16:28 incre_PRITEST_20171116_39_17sjnl9v_1_1
-rw-r----- 1 oracle oinstall 10158080 Nov 16 16:28 incre_PRITEST_20171116_40_18sjnla3_1_1

[oracle@pritest backup]$ scp -r ./incre_PRITEST_20171116* oracle@192.168.91.129:/u01/app/oracle/backup
oracle@192.168.91.129's password:
incre_PRITEST_20171114_32_10sjhpmm_1_1        100%   16MB  15.6MB/s   00:00   
incre_PRITEST_20171114_33_11sjhpmn_1_1        100% 9920KB   9.7MB/s   00:00 


2.7 备库恢复控制文件
[oracle@stdtest backup]$ rman target/
RMAN> startup mount

Oracle instance started
database mounted

Total System Global Area    1185853440 bytes

Fixed Size                     2252664 bytes
Variable Size                754974856 bytes
Database Buffers             419430400 bytes
Redo Buffers                   9195520 bytes

RMAN> catalog start with '/u01/app/oracle/backup';

Starting implicit crosscheck backup at 2017-11-16 16:31:25
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=30 device type=DISK
Finished implicit crosscheck backup at 2017-11-16 16:31:26

Starting implicit crosscheck copy at 2017-11-16 16:31:26
using channel ORA_DISK_1
Crosschecked 4 objects
Finished implicit crosscheck copy at 2017-11-16 16:31:26

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/app/oracle/fast_recovery_area/STDTEST/autobackup/2017_11_13/o1_mf_s_959944964_f0l4lkk8_.bkp
File Name: /u01/app/oracle/fast_recovery_area/STDTEST/autobackup/2017_11_13/o1_mf_s_959707108_f0kyqno7_.bkp

searching for all files that match the pattern /u01/app/oracle/backup

List of Files Unknown to the Database
=====================================
File Name: /u01/app/oracle/backup/incre_PRITEST_20171116_40_18sjnla3_1_1
File Name: /u01/app/oracle/backup/incre_PRITEST_20171116_39_17sjnl9v_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/backup/incre_PRITEST_20171116_40_18sjnla3_1_1
File Name: /u01/app/oracle/backup/incre_PRITEST_20171116_39_17sjnl9v_1_1


2.8 恢复数据库
restore 是还原，文件级的恢复。就是物理文件还原。
recover 是恢复，数据级的恢复。逻辑上恢复，比如应用归档日志、重做日志，全部同步，保持一致。
这里数据文件是基于原来的，所以不需要 restore database。

RMAN> recover database;
Starting recover at 2017-11-16 16:32:15
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/pritest/system01.dbf
destination for restore of datafile 00002: /u01/app/oracle/oradata/pritest/sysaux01.dbf
destination for restore of datafile 00003: /u01/app/oracle/oradata/pritest/undotbs01.dbf
destination for restore of datafile 00004: /u01/app/oracle/oradata/pritest/users01.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/backup/incre_PRITEST_20171116_39_17sjnl9v_1_1
channel ORA_DISK_1: piece handle=/u01/app/oracle/backup/incre_PRITEST_20171116_39_17sjnl9v_1_1 tag=FORSTANDBY
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01

starting media recovery

archived log for thread 1 with sequence 131 is already on disk as file /u01/app/oracle/archivelog/arch_1_131_956333727.arc
archived log for thread 1 with sequence 132 is already on disk as file /u01/app/oracle/archivelog/arch_1_132_956333727.arc
archived log for thread 1 with sequence 133 is already on disk as file /u01/app/oracle/archivelog/arch_1_133_956333727.arc
archived log file name=/u01/app/oracle/archivelog/arch_1_131_956333727.arc thread=1 sequence=131
archived log file name=/u01/app/oracle/archivelog/arch_1_132_956333727.arc thread=1 sequence=132
archived log file name=/u01/app/oracle/archivelog/arch_1_133_956333727.arc thread=1 sequence=133
unable to find archived log
archived log thread=1 sequence=134
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/16/2017 16:32:16
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 134 and starting SCN of 1162339
这报错可以忽略，主机134号归档日志还未归档。

2.9 启动数据库应用MRP并且验证主备同步
[oracle@stdtest ~]$ sqlplus / as sysdba
SQL> alter database open read only;
Database altered.

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

SQL> select max(sequence#) from v$archived_log;
MAX(SEQUENCE#)
--------------
    133

SQL> select * from ee;
no rows selected


3、知识补充
我们在进行基于scn增量备份的时候他需要去扫描全库，再做两个scn点的差集，去判断这个块会不会需不需要进行备份，
那么如果主库很大，那么做增量备份的也不会很快。当然会比全部备份快很多，他只需要扫描所有的块，需要记录的不一定会很多。


实验四
1、场景
由于一些测试必须使用生产环境，可以拿备库给做测试，测试完后再恢复。
主要流程是备库开启数据库闪回，把备库从PHYSICAL STANDBY模式切换到SNAPSHOT STANDBY模式，该模式可读写，等测试完切回
PHYSICAL STANDBY模式，利用闪回恢复到测试之前的状态，再应用归档日志恢复原状。

2、先查看备库是否开启闪回
SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
NO

SQL> alter database recover managed standby database cancel;
Database altered.

SQL> alter database flashback on;
Database altered.

SQL> select flashback_on from v$database;
FLASHBACK_ON
------------------
YES

SQL> show parameter DB_RECOVERY_FILE_DEST
NAME         TYPE  VALUE
------------------------------------ ----------- ------------------------------
db_recovery_file_dest       string  /u01/app/oracle/fast_recovery_area
db_recovery_file_dest_size      big integer 4182M


查看闪回数据文件
SQL> !ls -trl /u01/app/oracle/fast_recovery_area/STDTEST/flashback
total 102416
-rw-r----- 1 oracle oinstall 52436992 Nov 17 09:51 o1_mf_f0wj4n6t_.flb
-rw-r----- 1 oracle oinstall 52436992 Nov 17 10:42 o1_mf_f0wj4jkw_.flb


3、查看备库当前状态
SQL> select database_role,db_unique_name,open_mode from v$database;
DATABASE_ROLE  DB_UNIQUE_NAME   OPEN_MODE
---------------- ------------------------------ --------------------
PHYSICAL STANDBY stdtest   READ ONLY WITH APPLY

4、切换到 snapshot standby
SQL> alter database convert to snapshot standby;
Database altered.

5、查看日志
[oracle@stdtest trace]$tail -f /u01/app/oracle/diag/rdbms/stdtest/pritest/trace/alert_pritest.log
Fri Nov 17 10:19:35 2017
alter database convert to snapshot standby
Created guaranteed restore point SNAPSHOT_STANDBY_REQUIRED_11/17/2017 10:19:35
Killing 4 processes with pids 2902,2896,2898,2900 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 2850
All dispatchers and shared servers shutdown
CLOSE: killing server sessions.
CLOSE: all sessions shutdown successfully.
Fri Nov 17 10:19:38 2017
SMON: disabling cache recovery
Begin: Standby Redo Logfile archival
End: Standby Redo Logfile archival
RESETLOGS after incomplete recovery UNTIL CHANGE 1170706
Resetting resetlogs activation ID 2249370806 (0x8612acb6)
Online log /u01/app/oracle/oradata/pritest/redo01.log: Thread 1 Group 1 was previously cleared
Online log /u01/app/oracle/oradata/pritest/redo02.log: Thread 1 Group 2 was previously cleared
Online log /u01/app/oracle/oradata/pritest/redo03.log: Thread 1 Group 3 was previously cleared
Standby became primary SCN: 1170704
Fri Nov 17 10:19:38 2017
Setting recovery target incarnation to 3
AUDIT_TRAIL initialization parameter is changed back to its original value as specified in the parameter file.
CONVERT TO SNAPSHOT STANDBY: Complete - Database mounted as snapshot standby
Completed: alter database convert to snapshot standby

6、开打备库
SQL> select database_role,open_mode from v$database;
DATABASE_ROLE  OPEN_MODE
---------------- --------------------
SNAPSHOT STANDBY MOUNTED

SQL>  alter database open;
Database altered.

7、对备库做一些操作
SQL> select * from ee;
no rows selected

SQL> drop table ee purge;
Table dropped.

SQL> create user test identified by test123;
User created.

SQL> grant dba to test;
Grant succeeded.

SQL> conn test/test123
Connected.
SQL> create table abc as select * from dba_users;
Table created.

8、在SNAPSHOT STANDBY模式下，日志正常传输但不应用，下面语句进行确认
SQL> col CTIME for a18
SQL> col NAME for a15
SQL> col VALUE for a13
SQL> col DATUM_TIME for a20
SQL> select to_char(SYSDATE,'yyyymmdd hh34:mi:ss') CTIME,NAME,VALUE,DATUM_TIME
SQL> from V$DATAGUARD_STATS WHERE NAME LIKE '%lag';
CTIME     NAME     VALUE  DATUM_TIME
------------------ --------------- ------------- --------------------
20171117 11:03:21  transport lag   +00 00:00:00  11/17/2017 11:03:20
20171117 11:03:21  apply lag    +00 00:44:20  11/17/2017 11:03:20
看日志我们是 10:19 切换成，大致是44分钟。

9、切回 PHYSICAL STADNBY
SQL> alter database convert to physical standby;
alter database convert to physical standby
*
ERROR at line 1:
ORA-01126: database must be mounted in this instance and not open in any instance

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount
......
Database mounted.
SQL> alter database convert to physical standby;
Database altered.

10、切换完成后，发现闪回日志恢复完成，同时自动删除闪回日志，日志如下：
Fri Nov 17 11:12:58 2017
alter database convert to physical standby
ALTER DATABASE CONVERT TO PHYSICAL STANDBY (pritest)
Killing 3 processes with pids 3489,3491,3493 (all RFS) in order to disallow current and future RFS connections. Requested by OS process 3487
Flashback Restore Start
Flashback Restore Complete
Drop guaranteed restore point
Guaranteed restore point  dropped
Clearing standby activation ID 2253336566 (0x864f2ff6)
The primary database controlfile was created using the
'MAXLOGFILES 16' clause.
There is space for up to 13 standby redo logfiles
Use the following SQL commands on the standby database to create
standby redo logfiles that match the primary database:
ALTER DATABASE ADD STANDBY LOGFILE 'srl1.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl2.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl3.f' SIZE 52428800;
ALTER DATABASE ADD STANDBY LOGFILE 'srl4.f' SIZE 52428800;
Shutting down archive processes
Archiving is disabled
Fri Nov 17 11:12:59 2017
ARCH shutting down
ARC3: Archival stopped
Fri Nov 17 11:12:59 2017
ARCH shutting down
ARC2: Archival stopped
Fri Nov 17 11:12:59 2017
ARCH shutting down
ARC1: Archival stopped
Fri Nov 17 11:12:59 2017
ARCH shutting down
ARC0: Archival stopped
Completed: alter database convert to physical standby

11、开启同步主库
SQL> startup mount force
ORACLE instance started.
Total System Global Area 1185853440 bytes
Fixed Size      2252664 bytes
Variable Size    754974856 bytes
Database Buffers   419430400 bytes
Redo Buffers      9195520 bytes
Database mounted.
SQL> select database_role,db_unique_name,open_mode from v$database;
DATABASE_ROLE  DB_UNIQUE_NAME   OPEN_MODE
---------------- ------------------------------ --------------------
PHYSICAL STANDBY stdtest   MOUNTED

SQL> alter database open read only;
Database altered.

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


12、验证是否恢复到最初切换前

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

DATABASE_ROLE  DB_UNIQUE_NAME   OPEN_MODE
---------------- ------------------------------ --------------------
PHYSICAL STANDBY stdtest   READ ONLY WITH APPLY

SQL> select * from ee;
no rows selected

SQL> conn test/test123
ERROR:
ORA-01017: invalid username/password; loGon denied
Warning: You are no longer connected to ORACLE.

关于ORACLE中主从中断后如何恢复就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

您可能感兴趣的文档:
点击免费下载>>软考高级考试备考技巧/历年真题/备考精华资料

--结束END--

本文标题: ORACLE中主从中断后如何恢复

本文链接: https://www.lsjlt.com/news/56749.html(转载时请注明来源链接)

有问题或投稿请发送至: 邮箱/279061341@qq.com    QQ/279061341

本篇文章演示代码以及资料文档资料下载

下载Word文档到电脑，方便收藏和打印～

下载Word文档
去做题
猜你喜欢
  • ORACLE中主从中断后如何恢复
    这篇文章给大家介绍ORACLE中主从中断后如何恢复，内容非常详细，感兴趣的小伙伴们可以参考借鉴，希望对大家能有所帮助。一、知识回顾1、正常情况我们在生产中配置ADG会使用最大可用模式配合参数lgwr和syn...
    99+
    2022-10-18
  • mysql主从出现问题后应该如何诊断故障点及恢复数据
    本文主要给大家介绍mysql主从出现问题后应该如何诊断故障点及恢复数据，文章内容都是笔者用心摘选和编辑的，具有一定的针对性，对大家的参考意义还是比较大的，下面跟笔者一起了解下mysql主从出现问题后应该如何...
    99+
    2022-10-18
  • MySQL主从复制断开如何修复
    本篇文章为大家展示了MySQL主从复制断开如何修复，内容简明扼要并且容易理解，绝对能使你眼前一亮，通过这篇文章的详细介绍希望你能有所收获。      主从复制关系断裂，有各种各样的原因。有些时候，我们没有时间去...
    99+
    2023-06-14
  • oracle怎么从dmp中恢复指定表
    要从dmp文件中恢复指定表，可以使用Oracle的impdp工具。以下是使用impdp工具恢复指定表的步骤：1. 打开命令行窗口，使...
    99+
    2023-09-29
    oracle
  • MySQL恢复：使用从库备份，主库binlog，从库中继日志恢复数据库
    小生博客：http://xsboke.blog.51cto.com -------谢谢您的参考，如有疑问，欢迎交流 环境说明: mysql主从复制,主库A宕机,将从...
    99+
    2022-10-18
  • 判断GTID复制中主从是否同步脚本
    判断GTID复制中从库有没有与主库同步show slave stautus\G中：当Retrieved_Gtid_Set = Executed_Gtid_Set 表示从库已经和主库完成同步#!/b...
    99+
    2022-10-18
  • 如何实现mysql主从复制及数据备份恢复
    这篇文章给大家分享的是有关如何实现mysql主从复制及数据备份恢复的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。1、主从服务器分别作以下操作：  1.1、版本一致 &n...
    99+
    2022-10-19
  • Oracle误删除表数据后如何恢复
    Oracle误删除表数据后如何恢复，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。　　Oracle误删除表数据后怎么快速恢复　　一、undo...
    99+
    2022-10-18
  • 虚拟主机出现故障后如何恢复
    如果虚拟主机出现故障，可以尝试以下步骤来恢复：1. 检查故障原因：首先需要确定故障的原因，例如网络问题、硬件故障等。可以通过查看日志...
    99+
    2023-09-06
    虚拟主机
  • mysql中如何配置主从复制
    这篇文章主要介绍了mysql中如何配置主从复制，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。对于现在的系统来说，在业务复杂的系统中，数据库往...
    99+
    2022-10-18
  • Mysql5.7中如何搭建主从复制
    这篇文章主要介绍了Mysql5.7中如何搭建主从复制，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。一、概述主从复制可以实现对数据库备份和读写...
    99+
    2022-10-18
  • mongoDB中如何搭建主从复制
    mongoDB中如何搭建主从复制，相信很多没有经验的人对此束手无策，为此本文总结了问题出现的原因和解决方法，通过这篇文章希望你能解决这个问题。详解mongoDB主从复制搭建详细过程主 192.16...
    99+
    2022-10-18
  • Redis中如何实现主从复制
    Redis中如何实现主从复制，很多新手对此不是很清楚，为了帮助大家解决这个难题，下面小编将为大家详细讲解，有这方面需求的人可以来学习下，希望你能有所收获。Redis复制概论数据库复制指的是发生在不同数据库实...
    99+
    2022-10-18
  • win8点击高对比度主题后如何恢复?win8点击高对比度主题后恢复的方法
    有时，也许我们“手贱”，不知道对电脑进行了设置，使电脑变成了下面这样； 方法/步骤： 1.有时我们在不知情的情况下，电脑突然变成让我们惊呆的样子； 2.这是因为电脑设置了【高对比...
    99+
    2022-06-04
    主题 高对比度 方法
  • oracle delete误删除表数据后如何恢复
    1、根据时间进行恢复 此种方式需要我们大致知道执行delete语句的时间。 查询系统当前时间：select to_char(sysdate,‘yyyy...
    99+
    2022-11-13
  • 在Mysql5.7中如何搭建主从复制
    这篇文章主要为大家展示了“在Mysql5.7中如何搭建主从复制”，内容简而易懂，条理清晰，希望能够帮助大家解决疑惑，下面让小编带领大家一起研究并学习一下“在Mysql5.7中如何搭建主从复制”这篇文章吧。一...
    99+
    2022-10-18
  • mysql 中的备份恢复,分区分表,主从复制,读写分离
    目录mysql 的备份和恢复mysql 的分区分表mysql 的主从复制读写分离mysql 的备份和恢复 创建备份管理员创建备份管理员，并授予管理员相应的权限备份所需权限：selec...
    99+
    2022-11-13
  • mysql 中的备份恢复,分区分表,主从复制,读写分离
    目录mysql 的备份和恢复mysql 的分区分表mysql 的主从复制读写分离mysql 的备份和恢复 创建备份管理员创建备份管理员，并授予管理员相应的权限备份所需权限：select,reload,lock ...
    99+
    2022-09-05
  • Oracle DG从库Rman如何实现备份恢复测试
    这篇文章主要介绍了Oracle DG从库Rman如何实现备份恢复测试，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。1.系统初始化和数据库安装...
    99+
    2022-10-19
  • mysql中如何通过备份恢复搭建从库
    这篇文章主要介绍了mysql中如何通过备份恢复搭建从库，具有一定借鉴价值，感兴趣的朋友可以参考下，希望大家阅读完这篇文章之后大有收获，下面让小编带着大家一起了解一下。 MYSQ...
    99+
    2022-10-19
软考高级职称资格查询
软考职称历年真题下载
软考职称资料下载
热门wiki
近期文章
推荐阅读
热门问答
热门标签
编程网，编程工程师的家园，是目前国内优秀的开源技术社区之一，形成了由开源软件库、代码分享、资讯、协作翻译、讨论区和博客等几大频道内容，为IT开发者提供了一个发现、使用、并交流开源技术的平台。

  • 官方手机版

  • 微信公众号

  • 商务合作