ORACLE主从中断后，如何恢复（单实例）

最新推荐文章于 2022-09-02 18:59:23 发布

csygkk2842

最新推荐文章于 2022-09-02 18:59:23 发布

阅读量365

点赞数

文章标签：数据库

一、知识回顾
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 hh24: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.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/30590361/viewspace-2147428/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/30590361/viewspace-2147428/

csygkk2842

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
ORACLE主从中断后，如何恢复（单实例）

一、知识回顾1、正常情况我们在生产中配置ADG会使用最大可用模式配合参数lgwr和sync。SQL> show parameter log_archive_dest_2NAME TYPE...
复制链接

扫一扫