主库做不完全恢复后，备库无法应用日志解决办法（重建备库）

最新推荐文章于 2022-02-17 12:00:47 发布

雅冰石

最新推荐文章于 2022-02-17 12:00:47 发布

阅读量1.6k

点赞数

CC 4.0 BY-SA版权

分类专栏： 2 ORACLE 学习 # ORACLE RMAN # ORACLE RAC

本文链接：https://blog.youkuaiyun.com/yabingshi_tech/article/details/39320611

2 ORACLE 学习同时被 3 个专栏收录

306 篇文章

订阅专栏

ORACLE RMAN

20 篇文章

订阅专栏

ORACLE RAC

15 篇文章

订阅专栏

本文介绍了一个关于Oracle数据库不完全恢复的实验过程，包括主库的不完全恢复操作及随后因备库无法应用日志而进行的备库重建步骤。详细记录了从备份数据库、清空表数据到不完全恢复的全过程，并分享了解决方案。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

一：实验环境

主库：两个节点的集群

备库：单实例

操作系统：redhat

二：实验介绍

主库做不完全恢复后，备库无法应用日志。

这里通过重建备库的方法解决。

三：实验步骤

3.1：主库不完全恢复实验

3.1.1现有测试数据

SQL> select *

2 from scott.t;

----------

3.1.2查看数据库当前SCN

--供后面做不完全恢复用

SQL> select current_scn

2 from v$database;

CURRENT_SCN

-----------

762953

3.1.3备库数据库

RMAN> backup database format '/home/oracle/backup/%d_%s_%T.bak';

Starting backup at 16-SEP-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=143 instance=prod1 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=+DATA/prod/datafile/system.256.857444647

input datafile fno=00003 name=+DATA/prod/datafile/sysaux.257.857444649

input datafile fno=00005 name=+DATA/prod/datafile/example.264.857444773

input datafile fno=00002 name=+DATA/prod/datafile/undotbs1.258.857444651

input datafile fno=00006 name=+DATA/prod/datafile/undotbs2.265.857444885

input datafile fno=00004 name=+DATA/prod/datafile/users.259.857444651

channel ORA_DISK_1: starting piece 1 at 16-SEP-14

channel ORA_DISK_1: finished piece 1 at 16-SEP-14

piece handle=/home/oracle/backup/PROD_15_20140916.bak tag=TAG20140916T072842 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:08

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 16-SEP-14

channel ORA_DISK_1: finished piece 1 at 16-SEP-14

piece handle=/home/oracle/backup/PROD_16_20140916.bak tag=TAG20140916T072842 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:04

Finished backup at 16-SEP-14

3.1.4清空t表

SQL> delete

2 from scott.t;

6 rows deleted.

SQL> commit;

Commit complete.

SQL> select *

2 from scott.t;

no rows selected

3.1.5 不完全恢复

--在rac1上做不完全恢复:

3.1.5.1关闭主库所有实例

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

3.1.5.2 startup nomount

RMAN> startup nomount;

Oracle instance started

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 218105424 bytes

Database Buffers 62914560 bytes

Redo Buffers 2973696 bytes

3.1.5.3 恢复控制文件

RMAN> restore controlfile from '/home/oracle/backup/PROD_16_20140916.bak';

Starting restore at 16-SEP-14

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=147 instance=prod1 devtype=DISK

channel ORA_DISK_1: restoring control file

channel ORA_DISK_1: restore complete, elapsed time: 00:00:08

output filename=+DATA/prod/controlfile/current.260.857444741

output filename=+REDOLOG/prod/controlfile/current.256.857444743

Finished restore at 16-SEP-14

3.1.5.4 启动到mount状态

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

3.1.5.5 不完全恢复

RMAN> run {

2> set until scn 762953;

3> restore database;

4> recover database;

5> }

executing command: SET until clause

Starting restore at 16-SEP-14

Starting implicit crosscheck backup at 16-SEP-14

allocated channel: ORA_DISK_1

Crosschecked 9 objects

Finished implicit crosscheck backup at 16-SEP-14

Starting implicit crosscheck copy at 16-SEP-14

using channel ORA_DISK_1

Crosschecked 4 objects

Finished implicit crosscheck copy at 16-SEP-14

searching for all files in the recovery area

cataloging files...

no files cataloged

using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to +DATA/prod/datafile/system.256.857444647

restoring datafile 00002 to +DATA/prod/datafile/undotbs1.258.857444651

restoring datafile 00003 to +DATA/prod/datafile/sysaux.257.857444649

restoring datafile 00004 to +DATA/prod/datafile/users.259.857444651

restoring datafile 00005 to +DATA/prod/datafile/example.264.857444773

restoring datafile 00006 to +DATA/prod/datafile/undotbs2.265.857444885

channel ORA_DISK_1: reading from backup piece /home/oracle/backup/PROD_13_20140916.bak

channel ORA_DISK_1: restored backup piece 1

piece handle=/home/oracle/backup/PROD_13_20140916.bak tag=TAG20140916T054147

channel ORA_DISK_1: restore complete, elapsed time: 00:00:26

Finished restore at 16-SEP-14

Starting recover at 16-SEP-14

using channel ORA_DISK_1

starting media recovery

archive log thread 1 sequence 3 is already on disk as file +DATA/prod/1_3_858395720.arc

archive log thread 1 sequence 4 is already on disk as file +DATA/prod/1_4_858395720.arc

archive log thread 1 sequence 5 is already on disk as file +DATA/prod/1_5_858395720.arc

archive log thread 1 sequence 6 is already on disk as file +DATA/prod/1_6_858395720.arc

archive log thread 1 sequence 7 is already on disk as file +DATA/prod/1_7_858395720.arc

archive log thread 1 sequence 8 is already on disk as file +DATA/prod/1_8_858395720.arc

archive log thread 1 sequence 9 is already on disk as file +DATA/prod/1_9_858395720.arc

archive log thread 1 sequence 10 is already on disk as file +DATA/prod/1_10_858395720.arc

archive log thread 1 sequence 11 is already on disk as file +DATA/prod/1_11_858395720.arc

archive log thread 1 sequence 12 is already on disk as file +DATA/prod/onlinelog/group_1.261.858408013

archive log thread 1 sequence 13 is already on disk as file +DATA/prod/onlinelog/group_2.262.858408017

archive log thread 2 sequence 12 is already on disk as file +DATA/prod/2_12_858395720.arc

archive log thread 2 sequence 13 is already on disk as file +DATA/prod/2_13_858395720.arc

archive log thread 2 sequence 14 is already on disk as file +DATA/prod/2_14_858395720.arc

archive log thread 2 sequence 15 is already on disk as file +DATA/prod/2_15_858395720.arc

archive log thread 2 sequence 16 is already on disk as file +DATA/prod/2_16_858395720.arc

archive log thread 2 sequence 17 is already on disk as file +DATA/prod/2_17_858395720.arc

archive log thread 2 sequence 18 is already on disk as file +DATA/prod/2_18_858395720.arc

archive log thread 2 sequence 19 is already on disk as file +DATA/prod/2_19_858395720.arc

archive log thread 2 sequence 20 is already on disk as file +DATA/prod/onlinelog/group_3.266.858408021

archive log thread 2 sequence 21 is already on disk as file +DATA/prod/onlinelog/group_4.267.858408027

archive log filename=+DATA/prod/1_3_858395720.arc thread=1 sequence=3

archive log filename=+DATA/prod/2_12_858395720.arc thread=2 sequence=12

archive log filename=+DATA/prod/1_4_858395720.arc thread=1 sequence=4

archive log filename=+DATA/prod/2_13_858395720.arc thread=2 sequence=13

archive log filename=+DATA/prod/2_14_858395720.arc thread=2 sequence=14

archive log filename=+DATA/prod/1_5_858395720.arc thread=1 sequence=5

archive log filename=+DATA/prod/2_15_858395720.arc thread=2 sequence=15

archive log filename=+DATA/prod/1_6_858395720.arc thread=1 sequence=6

archive log filename=+DATA/prod/1_7_858395720.arc thread=1 sequence=7

archive log filename=+DATA/prod/1_8_858395720.arc thread=1 sequence=8

archive log filename=+DATA/prod/1_9_858395720.arc thread=1 sequence=9

archive log filename=+DATA/prod/1_10_858395720.arc thread=1 sequence=10

archive log filename=+DATA/prod/2_16_858395720.arc thread=2 sequence=16

archive log filename=+DATA/prod/2_17_858395720.arc thread=2 sequence=17

archive log filename=+DATA/prod/2_18_858395720.arc thread=2 sequence=18

archive log filename=+DATA/prod/1_11_858395720.arc thread=1 sequence=11

archive log filename=+DATA/prod/2_19_858395720.arc thread=2 sequence=19

archive log filename=+DATA/prod/onlinelog/group_1.261.858408013 thread=1 sequence=12

archive log filename=+DATA/prod/onlinelog/group_3.266.858408021 thread=2 sequence=20

archive log filename=+DATA/prod/onlinelog/group_4.267.858408027 thread=2 sequence=21

archive log filename=+DATA/prod/onlinelog/group_2.262.858408017 thread=1 sequence=13

media recovery complete, elapsed time: 00:00:05

Finished recover at 16-SEP-14

3.1.5.6 打开库

RMAN> alter database open resetlogs;

database opened

3.1.5.7 验证

SQL> select *

2 from scott.t;

----------

3.1.5.8 查看备库归档日志是否应用

此后，主库切换日志的话，备库无法应用新接收的归档日志：

NAME APP FIRST_TIM

-------------------------------------------------- --- ---------

/arch/sdyprod/1_6_858395720.arc YES 16-SEP-14

/arch/sdyprod/1_5_858395720.arc YES 16-SEP-14

/arch/sdyprod/1_7_858395720.arc YES 16-SEP-14

/arch/sdyprod/1_8_858395720.arc YES 16-SEP-14

/arch/sdyprod/1_9_858395720.arc YES 16-SEP-14

/arch/sdyprod/1_10_858395720.arc YES 16-SEP-14

/arch/sdyprod/1_11_858395720.arc YES 16-SEP-14

/arch/sdyprod/2_19_858395720.arc YES 16-SEP-14

/arch/sdyprod/2_20_858395720.arc YES 16-SEP-14

/arch/sdyprod/1_12_858395720.arc YES 16-SEP-14

/arch/sdyprod/2_21_858395720.arc NO 16-SEP-14

NAME APP FIRST_TIM

-------------------------------------------------- --- ---------

/arch/sdyprod/1_1_858411261.arc NO 16-SEP-14

/arch/sdyprod/1_13_858395720.arc NO 16-SEP-14

/arch/sdyprod/2_1_858411261.arc NO 16-SEP-14

3.2：重建备库

3.2.1 备份主库

--主库刚open resetlogs，故需要重新备份：

RMAN> backup database format '/home/oracle/backup/%d_%s_%T.bak';

Starting backup at 16-SEP-14

using channel ORA_DISK_1

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00001 name=+DATA/prod/datafile/system.256.857444647

input datafile fno=00003 name=+DATA/prod/datafile/sysaux.257.857444649

input datafile fno=00005 name=+DATA/prod/datafile/example.264.857444773

input datafile fno=00002 name=+DATA/prod/datafile/undotbs1.258.857444651

input datafile fno=00006 name=+DATA/prod/datafile/undotbs2.265.857444885

input datafile fno=00004 name=+DATA/prod/datafile/users.259.857444651

channel ORA_DISK_1: starting piece 1 at 16-SEP-14

channel ORA_DISK_1: finished piece 1 at 16-SEP-14

piece handle=/home/oracle/backup/PROD_21_20140916.bak tag=TAG20140916T085755 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:26

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

including current control file in backupset

including current SPFILE in backupset

channel ORA_DISK_1: starting piece 1 at 16-SEP-14

channel ORA_DISK_1: finished piece 1 at 16-SEP-14

piece handle=/home/oracle/backup/PROD_22_20140916.bak tag=TAG20140916T085755 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:02

Finished backup at 16-SEP-14

3.2.2 在主库创建备库控制文件

（前提是先要删除该路径下的备库控制文件）

[oracle@rac1 ~]$ rm sdyprod_control01.ctl

SQL> alter database create standby controlfile as '/home/oracle/sdyprod_control01.ctl';

3.2.3 关备库

SQL> shutdown abort;

ORACLE instance shut down.

3.2.4 删掉数据文件，控制文件，日志文件

[oracle@dg sdyprod]$ cd /u01/app/oracle/admin/sdyprod

[oracle@dg sdyprod]$ ls

adump bdump cdump sdyprod_control01.ctl udump

[oracle@dg sdyprod]$ rm sdyprod_control01.ctl

[oracle@dg sdyprod]$cd /u01/app/oracle/oradata/sdyprod

[oracle@dg sdyprod]$ rm *

[oracle@dg sdyprod]$ ls

Database altered.

3.2.5 将主库下的备库控制文件拷贝到备库控制文件所在地

[oracle@rac1 ~]$ scp sdyprod_control01.ctl 192.168.8.225:/u01/app/oracle/admin/sdyprod/

oracle@192.168.8.225's password:

sdyprod_control01.ctl

3.2.6 将主库的备份文件拷贝到备库

[oracle@rac1 backup]$ scp PROD_21_20140916.bak 192.168.8.225:/home/oracle/backup/

oracle@192.168.8.225's password:

PROD_21_20140916.bak 100% 622MB 14.8MB/s 00:42

[oracle@rac1 backup]$

3.2.7 将备库启动到mount状态

SQL> startup mount;

ORACLE instance started.

Total System Global Area 285212672 bytes

Fixed Size 1218992 bytes

Variable Size 104859216 bytes

Database Buffers 176160768 bytes

Redo Buffers 2973696 bytes

Database mounted.

3.2.8 restore database

RMAN> restore database;

Starting restore at 16-SEP-14

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=152 devtype=DISK

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00001 to /u01/app/oracle/oradata/sdyprod/system.256.857444647

restoring datafile 00002 to /u01/app/oracle/oradata/sdyprod/undotbs1.258.857444651

restoring datafile 00003 to /u01/app/oracle/oradata/sdyprod/sysaux.257.857444649

restoring datafile 00004 to /u01/app/oracle/oradata/sdyprod/users.259.857444651

restoring datafile 00005 to /u01/app/oracle/oradata/sdyprod/example.264.857444773

restoring datafile 00006 to /u01/app/oracle/oradata/sdyprod/undotbs2.265.857444885

channel ORA_DISK_1: reading from backup piece /home/oracle/backup/PROD_21_20140916.bak

channel ORA_DISK_1: restored backup piece 1

piece handle=/home/oracle/backup/PROD_21_20140916.bak tag=TAG20140916T085755

channel ORA_DISK_1: restore complete, elapsed time: 00:00:16

Finished restore at 16-SEP-14

3.2.9 应用日志：

SQL> alter database recover managed standby database disconnect from session;

Database altered.

3.2.10查看是否应用日志

--在主库多切换几次日志，查看v$archived_log是否应用，

SQL> select name,applied

from v$archived_log;

NAME APP FIRST_TIM

-------------------------------------------------- --- ---------

/arch/sdyprod/2_3_858411261.arc YES 16-SEP-14

/arch/sdyprod/1_5_858411261.arc YES 16-SEP-14

/arch/sdyprod/2_4_858411261.arc YES 16-SEP-14

/arch/sdyprod/1_6_858411261.arc NO 16-SEP-14

可以应用了。