【备份恢复】在 ARCHIVELOG 模式下执行数据库还原和恢复操作（源库备份源库恢复）...

最新推荐文章于 2022-07-08 10:10:35 发布
最新推荐文章于 2022-07-08 10:10:35 发布
阅读量478 收藏
点赞数
文章标签： 数据库

在 ARCHIVELOG 模式下执行数据库还原和恢复操作

本实验是在一个库中操作备份，然后删除三大文件，最后利用rman备份进行恢复数据库操作

1.查看归档与否：

SYS@ORA11GR2>archive log list;

Database log mode              No Archive Mode

Automatic archival             Disabled

Archive destination            /u01/app/oracle/product/11.2.0/dbhome_1/dbs/arch

Oldest online log sequence     14

Current log sequence           16

SYS@ORA11GR2>show parameter recover

NAME                                 TYPE        VALUE

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

db_recovery_file_dest                string

db_recovery_file_dest_size           big integer 0

db_unrecoverable_scn_tracking        boolean     TRUE

recovery_parallelism                 integer     0

SYS@ORA11GR2>

SYS@ORA11GR2>alter system set db_recovery_file_dest_size=3g;

 

System altered.

 

SYS@ORA11GR2>!mkdir -p /u01/app/FRA

 

SYS@ORA11GR2>alter system set db_recovery_file_dest='/u01/app/FRA';

 

System altered.

 

——设置归档为enable：

SYS@ORA11GR2>shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SYS@ORA11GR2>

SYS@ORA11GR2>startup mount;

ORACLE instance started.

 

Total System Global Area  830930944 bytes

Fixed Size                  2257800 bytes

Variable Size             503319672 bytes

Database Buffers          322961408 bytes

Redo Buffers                2392064 bytes

Database mounted.

SYS@ORA11GR2>

SYS@ORA11GR2>alter database archivelog;

 

Database altered.

 

SYS@ORA11GR2>archive log list;

Database log mode              Archive Mode

Automatic archival             Enabled

Archive destination            USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence     14

Next log sequence to archive   16

Current log sequence           16

SYS@ORA11GR2>alter database open;

 

Database altered.

 

2.将RMAN的自动备份控制文件功能开启：

（建议备份时也备份归档日志，以实现完全恢复，否则则需进行不完全恢复）

[oracle@wang ~]$ rman target /

 

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Oct 8 14:44:58 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database: ORA11GR2 (DBID=237843809)

 

RMAN> show all;

 

using target database control file instead of recovery catalog

RMAN configuration parameters for database with db_unique_name ORA11GR2 are:

CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default

CONFIGURE BACKUP OPTIMIZATION OFF; # default

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default

CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/snapcf_ORA11GR2.f'; # default

 

RMAN> configure controlfile autobackup on;

 

new RMAN configuration parameters:

CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters are successfully stored

 

RMAN>

 

2.备份数据库：

RMAN> backup database;

 

Starting backup at 08-OCT-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=49 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/ORA11GR2/system01.dbf

input datafile file number=00002 name=/u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

input datafile file number=00005 name=/u01/app/oracle/oradata/ORA11GR2/example01.dbf

input datafile file number=00003 name=/u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

input datafile file number=00004 name=/u01/app/oracle/oradata/ORA11GR2/users01.dbf

channel ORA_DISK_1: starting piece 1 at 08-OCT-16

channel ORA_DISK_1: finished piece 1 at 08-OCT-16

piece handle=/u01/app/FRA/ORA11GR2/backupset/2016_10_08/o1_mf_nnndf_TAG20161008T144726_czk5n060_.bkp tag=TAG20161008T144726 comment=NONE

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

Finished backup at 08-OCT-16

 

Starting Control File and SPFILE Autobackup at 08-OCT-16

piece handle=/u01/app/FRA/ORA11GR2/autobackup/2016_10_08/o1_mf_s_924706105_czk5otjm_.bkp comment=NONE

Finished Control File and SPFILE Autobackup at 08-OCT-16

 

3.删除所有控制文件、数据文件、日志文件，以及参数文件：

[oracle@wang ~]$ cd /u01/app/oracle/oradata/ORA11GR2/

[oracle@wang ORA11GR2]$ ls

control01.ctl  redo01.log  sysaux01.dbf  undotbs01.dbf

control02.ctl  redo02.log  system01.dbf  users01.dbf

example01.dbf  redo03.log  temp01.dbf

[oracle@wang ORA11GR2]$ rm *

[oracle@wang ORA11GR2]$ ls

[oracle@wang ORA11GR2]$ cd $ORACLE_HOME/dbs

[oracle@wang dbs]$ ls spfileORA11GR2.ora initORA11GR2.ora

initORA11GR2.ora  spfileORA11GR2.ora

[oracle@wang dbs]$ rm spfileORA11GR2.ora initORA11GR2.ora

[oracle@wang dbs]$ ls spfileORA11GR2.ora initORA11GR2.ora

ls: spfileORA11GR2.ora: No such file or directory

ls: initORA11GR2.ora: No such file or directory

[oracle@wang dbs]$

 

4.执行恢复：

1）修改环境变量$ORACLE_SID

[oracle@wang ~]$ echo $ORACLE_SID

ORA11GR2

[oracle@wang ~]$ export ORACLE_SID=SF

[oracle@wang ~]$ echo $ORACLE_SID

SF

 

2）登录RMAN(可以在没有参数文件的情况下登录实例)

[oracle@wang ~]$ rman target /

 

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Oct 8 14:56:11 2016

 

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

 

connected to target database (not started)

 

RMAN> startup nomount;

 

startup failed: ORA-01078: failure in processing system parameters

LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initSF.ora'

 

starting Oracle instance without parameter file for retrieval of spfile

Oracle instance started

 

Total System Global Area    1068937216 bytes

 

Fixed Size                     2260088 bytes

Variable Size                281019272 bytes

Database Buffers             780140544 bytes

Redo Buffers                   5517312 bytes

 

3）恢复参数文件：

RMAN> restore spfile to '$ORACLE_HOME/dbs/spfileORA11GR2.ora' from '/u01/app/FRA/ORA11GR2/autobackup/2016_10_08/o1_mf_s_924706105_czk5otjm_.bkp';

 

Starting restore at 08-OCT-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=19 device type=DISK

 

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/app/FRA/ORA11GR2/autobackup/2016_10_08/o1_mf_s_924706105_czk5otjm_.bkp

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete

Finished restore at 08-OCT-16

——验证：

[oracle@wang dbs]$ pwd

/u01/app/oracle/product/11.2.0/dbhome_1/dbs

[oracle@wang dbs]$ ls spfileORA11GR2.ora

spfileORA11GR2.ora

 

4）关闭实例及重新以恢复的参数文件（即ORA11GR2）登录到实例：

[oracle@wang ~]$ echo $ORACLE_SID

SF

[oracle@wang ~]$ export ORACLE_SID=ORA11GR2

[oracle@wang ~]$ echo $ORACLE_SID

ORA11GR2

[oracle@wang ~]$

——验证可以登录到实例（以ORA11GR2）

SYS@ORA11GR2>startup nomount;

ORACLE instance started.

 

Total System Global Area  830930944 bytes

Fixed Size                  2257800 bytes

Variable Size             503319672 bytes

Database Buffers          322961408 bytes

Redo Buffers                2392064 bytes

SYS@ORA11GR2>

SYS@ORA11GR2>show parameter spfile

 

NAME                        TYPE           VALUE

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

spfile string u01/app/oracle/product/11.2.0                                                 /dbhome_1/dbs/spfileORA11GR2.ora

 

5)继续以RMAN恢复控制文件：（重储）

RMAN> restore controlfile from '/u01/app/FRA/ORA11GR2/autobackup/2016_10_08/o1_mf_s_924706105_czk5otjm_.bkp';

 

Starting restore at 08-OCT-16

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=21 device type=DISK

 

channel ORA_DISK_1: restoring control file

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

output file name=/u01/app/oracle/oradata/ORA11GR2/control01.ctl

output file name=/u01/app/oracle/oradata/ORA11GR2/control02.ctl

Finished restore at 08-OCT-16

 

——启动数据库到mount下：

RMAN> alter database mount;

 

database mounted

released channel: ORA_DISK_1

 

6）恢复数据库：（重储数据库）

RMAN> restore database;

 

Starting restore at 08-OCT-16

Starting implicit crosscheck backup at 08-OCT-16

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=18 device type=DISK

Crosschecked 1 objects

Finished implicit crosscheck backup at 08-OCT-16

 

Starting implicit crosscheck copy at 08-OCT-16

using channel ORA_DISK_1

Finished implicit crosscheck copy at 08-OCT-16

 

searching for all files in the recovery area

cataloging files...

cataloging done

 

List of Cataloged Files

=======================

File Name: /u01/app/FRA/ORA11GR2/autobackup/2016_10_08/o1_mf_s_924706105_czk5otjm_.bkp

 

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/ORA11GR2/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/ORA11GR2/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /u01/app/oracle/oradata/ORA11GR2/example01.dbf

channel ORA_DISK_1: reading from backup piece /u01/app/FRA/ORA11GR2/backupset/2016_10_08/o1_mf_nnndf_TAG20161008T144726_czk5n060_.bkp

channel ORA_DISK_1: piece handle=/u01/app/FRA/ORA11GR2/backupset/2016_10_08/o1_mf_nnndf_TAG20161008T144726_czk5n060_.bkp tag=TAG20161008T144726

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:01:15

Finished restore at 08-OCT-16

 

7）恢复数据库（recover，因为是热备数据库且处于归档模式）

RMAN> recover database;

 

Starting recover at 08-OCT-16

using channel ORA_DISK_1

 

starting media recovery

unable to find archived log

archived log thread=1 sequence=14

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of recover command at 10/08/2016 15:26:39

RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 14 and starting SCN of 1201609

此处我们会发现，在recover的时候，由于备份归档日志，所以，提示只能恢复到SCN 1201609

因为备份整库时只是备份到当时的点，而且用的是旧版的控制文件恢复数据库，所以只能恢复到旧版控制为文件记录的最新scn

——解决：

RMAN> run{                          

2>  SET UNTIL SCN 1201609;       

3>  RESTORE DATABASE;           

4>  SWITCH DATAFILE ALL;        

5>  RECOVER DATABASE;           

6>  }                           

 

executing command: SET until clause

 

Starting restore at 08-OCT-16

using channel ORA_DISK_1

 

skipping datafile 1; already restored to file /u01/app/oracle/oradata/ORA11GR2/system01.dbf

skipping datafile 2; already restored to file /u01/app/oracle/oradata/ORA11GR2/sysaux01.dbf

skipping datafile 3; already restored to file /u01/app/oracle/oradata/ORA11GR2/undotbs01.dbf

skipping datafile 4; already restored to file /u01/app/oracle/oradata/ORA11GR2/users01.dbf

skipping datafile 5; already restored to file /u01/app/oracle/oradata/ORA11GR2/example01.dbf

restore not done; all files read only, offline, or already restored

Finished restore at 08-OCT-16

 

 

Starting recover at 08-OCT-16

using channel ORA_DISK_1

 

starting media recovery

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

 

Finished recover at 08-OCT-16

 

——打开数据库：

RMAN> alter database open;

 

RMAN-00571: ===========================================================

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============

RMAN-00571: ===========================================================

RMAN-03002: failure of alter db command at 10/08/2016 15:32:17

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

 

RMAN> alter database open resetlogs;（属于不完全恢复）

 

database opened

 

8）验证数据库恢复回来了：

SYS@ORA11GR2>select status from v$instance;

 

STATUS

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

OPEN

 

SYS@ORA11GR2>show parameter name

 

NAME                                 TYPE        VALUE

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

cell_offloadgroup_name               string

db_file_name_convert                 string

db_name                              string      ORA11GR2

db_unique_name                       string      ORA11GR2

global_names                         boolean     FALSE

instance_name                        string      ORA11GR2

lock_name_space                      string

log_file_name_convert                string

processor_group_name                 string

service_names                        string      ORA11GR2

SYS@ORA11GR2>

SYS@ORA11GR2>!ls /u01/app/oracle/oradata/ORA11GR2/

control01.ctl  example01.dbf  redo02.log  sysaux01.dbf  temp01.dbf     users01.dbf

control02.ctl  redo01.log     redo03.log  system01.dbf  undotbs01.dbf

源库执行数据库还原和恢复操作完成！！！！！！！！！！！！


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

转载于:http://blog.itpub.net/31397003/viewspace-2126537/

congjiu2607
关注
  • 0
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
oracle recover database,standby库，在sqlplus下用recover standby database进行手工恢复
weixin_36371906的博客
04-05 922
一个standby库出问题，丢了1个归档检查主库的日志，发现丢失的归档的SEQ的redo还未被覆盖，马上将其拷贝出来传递到备库上尝试catalog，虽然没有报错，但是实际没有注册成功RMAN> catalog archivelog '/u01/app/oracle/oradata/ORCL/redo03_281.arc';cataloged archived logarchived log ...
Oracle DBA数据库备份与恢复详解最佳实践
10-12
总的来说，Oracle DBA在进行数据库备份与恢复时，应根据业务需求和数据库工作模式选择合适的备份策略，同时定期测试恢复流程，确保在实际灾难情况下能够快速有效地恢复数据。此外，详细记录备份过程和恢复步骤，以及...
使用archive log恢复data file
呆瓜呆呆的专栏
09-02 1593
--创建测试表空间 create tablespace test2 datafile '/u01/app/oracle/oradata/normal/test2_01.dbf' size 10M reuse; --切换日志并查看创建的表空间的 创建scn SQL> alter system switch logfile; SQL> / SQL> / SQL> / SQL> select fi
ARCHIVELOG模式下的恢复
09-24 233
ARCHIVELOG模式下的恢复完全恢复:1.从备份中还原所有数据库数据文件.2.还原所有备份的归档的重做日志.3.加载数据库STARTUP MOUNT;4.恢复数据库RECOVER DATABASE.5.应用归档日志中的重做日...
【转】恢复archivelog介绍
ckykdwr884269的博客
05-31 587
恢复archivelog介绍(即restore archivelog介绍) 恢复archivelog介绍(即restore archivelog介绍) 作者:George...
IBM Cloud对象存储系列之Archive存储及恢复
brown_xdz的博客
06-30 690
对象存储是一种提供海量存储和低成本访问的存储服务，具备高性能、高可靠、安全、低成本等特性，并且通过标准的S3/Swift接口提供非结构化数据（图片、音视频、文本等格式文件）的存储服务，可以满足用户在任何地方通过互联网对数据进行管理和访问的需求。 为了满足各种业务场景的数据访问，使客户以最低成本来使用对象存储服务，云厂商都会提供多种存储类型，例如IBM Cloud就提供了覆盖热数据到冷数据访问的多种存储类型。 “Smart Tier”存储类型：该存储类型专为希望在数据访问模式发生变化时自动优化存储成本而不
ORACLE数据库备份和恢复.doc
06-03
配置闪回恢复区和 ARCHIVELOG 模式，可以在创建数据库时执行，也可以在 Enterprise Manager 中配置。步骤如下： * 在操作系统提示符下为闪回恢复区创建一个目录。 * 打开浏览器，并指定用户名为 SYS，口令为 SYSDBA...
Oracle9i数据库的备份与恢复.pptx
04-17
在实际操作中，可以使用Oracle企业管理器或PL/SQL命令来查看和管理数据库的备份模式。例如，通过执行`ARCHIVE LOG LIST;`命令可以查询当前数据库的备份模式。 总的来说，Oracle9i数据库的备份与恢复是一项复杂而...
大型数据库技术-实验六 oracle备份恢复.doc
08-21
通过`archive log list`命令查看归档状态，`startup mount`进入挂载状态，`alter database archivelog`开启归档日志模式，然后执行`alter database open`打开数据库。使用`begin backup`和`end backup`命令进行表...
实验14 Oracle数据库备份与恢复.doc
06-04
实验 14 介绍了 Oracle 数据库备份与恢复的方法，並演示了备份和恢复的步骤。 一、实验目的与要求 * 了解常见数据备份与恢复的方法 * 学会 Oracle 备份方案的制定 二、实验内容与步骤 ### 14.1 Oracle 物理备份...
oracle日志恢复启用命令,oracle 日志恢复数据详解
weixin_29465407的博客
04-03 1040
1：首先查找redo，如果redo有可供恢复的信息，就那redo中的信息进行恢复，此时一般在恢复时，类似如下:SQL> recover database;Media recovery complete.2：如果在redo中没有找到可供恢复的信息，oracle会去找archive log进行恢复，此时会有下面的提示：SQL> recover database;ORA-00279: cha...
Oracle数据库recover database的四条语句
杜金亮的博客
03-11 5826
1 recover database using backup controlfile 2 recover database until cancel 3 recover database using backup controlfile until cancel; 4 recover database until cancel using backup controlfile; 1. ...
数据库热备份与冷备份，archivelog与unarchivelog（归档模式与非归档模式）
晓飛的博客
09-06 1819
                             热备份是在数据库运行的情况下，采用archivelog mode方式备份数据库的方法。即热备份是系统处于正常运转状态下的备份。所以，如果你有一个冷备份而且又有热备份文件，在发生问题时，就可以利用这些资料恢复更多的信息。热备份要求数据库在Archivelog()方式下操作，并需要大量的档案空间。一旦数据库运行在archivelog状态下，就可...
Oracle recover database的四种语句区别
fengq_ing的博客
07-08 762
oracle recover databse恢复
聊聊RMAN的ARCHIVELOG DELETION参数
Data & Analysis
12-28 2816
RMAN是Oracle推出的官方备份还原工具。经过几个大版本的发展，RMAN已经支持多种备份介质和恢复策略的主要工具，也是业界普遍认可是Oracle备份还原官方策略。 Archivelog是Oracle备份还原策略的重要组成元素，不完全备份+连续的归档日志可以让我们将数据库恢复到发生故障点，实现数据的无损失恢复。但是，现实生活中archive log给没有经验的运维人员也带来了不少的问题，归档空...
RMAN-Configuring an Archived Redo Log Deletion Policy
congjiong8459的博客
02-25 276
在RMAN的备份策略中有两条关于归档日志的策略： 一个是归档日志备份备份副本的个数。 CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; ...
Rman备份之configure语法
czmmiao的专栏
10-28 263
CONFIGURE Syntax CONFIGURE {ARCHIVELOG DELETION POLICY {CLEAR | TO {APPLIED ON STANDBY | NONE }} | AUXNAME FOR DATAFILE datafileSpec {CLEAR | TO ' filename '...
RMAN中常用configure命令
junmail的专栏
11-08 1161
1 显示当前的配置信息 =========================== 1.01 RMAN> show all; 1.02 查询RMAN设置中非默认值:     SQL> select name,value from v$rman_configure; 2. 常用的configure选项 =========================== 2.01 保存策略 (reten...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值