Oracle数据库恢复演练

荞麦2年前技术文章1108

1、演练目的

验证核心系统数据库备份的有效性，在极端数据库故障情况下保证数据库存在一份可用的备份文件，为业务数据的安全提供保障。

 

2、演练准备

提供一台2C16G本地60G的阿里ecs服务器，操作系统镜像使用袋鼠云Oracle11G OS镜像，使用镜像初始化ecs后已经安装了Oracle 11gR2(11.2.0.4)软件，部署和配置ossutil，操作如下

下载ossutil64工具

cd  /home/oracle

wget http://docs-aliyun.cn-hangzhou.oss.aliyun-inc.com/assets/attach/50452/cn_zh/1524643963683/ossutil64?spm=a2c4g.11186623.2.3.bZ9D62  ./

chmod u+x ossutil64

配置ossutil64 连接信息

./ossutil64 config -e oss-cn-beijing.aliyuncs.com -i LTAImQ3lJiMWNkWK -k AhRuECrn16pSkSAxkQ96QIwFP0PygZ

从OSS上下载easydb自动上传的备份文件

./ossutil64 cp -r oss://beebox-db/oss-cn-beijing-internal.aliyuncs.com/20180801/iZ9zm93rjjdv8hZ:ORCL/ /home/oracle/oradata

 

 

3、恢复演练

3.1、启动到nomount状态

cat /u01/app/oracle/product/11.2.0/db_1/dbs/initORCL.ora

*.audit_file_dest='/u01/app/oracle/admin/ORCL/adump'

*.audit_trail='db'

*.compatible='11.2.0.4.0'

*.control_files='/data/oradata/orcl/control01.ctl','/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl'

*.db_block_size=8192

*.db_domain=''

*.db_name='ORCL'

*.db_recovery_file_dest='/u01/app/oracle/fast_recovery_area'

*.db_recovery_file_dest_size=4385144832

*.diagnostic_dest='/u01/app/oracle'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=ORCLXDB)'

*.log_archive_config='dg_config=(orcl,orcl_dg)'

*.log_archive_dest_1='LOCATION=/data/archivelog/orcl/ VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl'

*.log_archive_dest_state_2='ENABLE'

*.open_cursors=300

*.pga_aggregate_target=1073741824

*.processes=1000

*.remote_login_passwordfile='EXCLUSIVE'

*.sga_max_size=4831838208

*.sga_target=4831838208

Sqlplus  / as sysdba

SQL> startup nomount pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initORCL.ora'

ORACLE instance started.

Total System Global Area 4810256384 bytes

Fixed Size                  2261368 bytes

Variable Size            1241517704 bytes

Database Buffers         3556769792 bytes

Redo Buffers                9707520 bytes

SQL> create spfile  from pfile='/u01/app/oracle/product/11.2.0/db_1/dbs/initORCL.ora';

SQL> startup nomount force

ORACLE instance started.

Total System Global Area 4810256384 bytes

Fixed Size                  2261368 bytes

Variable Size            1241517704 bytes

Database Buffers         3556769792 bytes

Redo Buffers                9707520 bytes

 

3.2、恢复数据库控制文件

[oracle@iZ2zeeqh138yjl5wauo4wpZ dbs]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 1 13:04:35 2018

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

connected to target database: ORCL (not mounted)

RMAN> restore controlfile from '/oradata/backup_dir/ORCL_control_c-1446878566-20180731-00';

Starting restore at 01-AUG-18

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=771 device type=DISK

channel ORA_DISK_1: restoring control file

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

output file name=/data/oradata/orcl/control01.ctl

output file name=/u01/app/oracle/fast_recovery_area/ORCL/control02.ctl

Finished restore at 01-AUG-18

启动数据库到mount 状态

[oracle@iZ2zeeqh138yjl5wauo4wpZ dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Wed Aug 1 13:05:27 2018

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> alter database mount;

Database altered.

 

3.3、恢复数据库

##注册备份片恢复数据库

[oracle@iZ2zeeqh138yjl5wauo4wpZ dbs]$ rman target  /

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 1 13:06:37 2018

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

connected to target database: ORCL (DBID=1446878566, not open)

RMAN> catalog start with '/oradata/backup_dir';

Starting implicit crosscheck backup at 01-AUG-18

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=762 device type=DISK

Crosschecked 49 objects

Finished implicit crosscheck backup at 01-AUG-18

Starting implicit crosscheck copy at 01-AUG-18

using channel ORA_DISK_1

Crosschecked 2 objects

Finished implicit crosscheck copy at 01-AUG-18

searching for all files in the recovery area

cataloging files...

no files cataloged

searching for all files that match the pattern /oradata/backup_dir

List of Files Unknown to the Database

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

File Name: /oradata/backup_dir/dtstack_backup_full_ORCL_20180731_105132.log

File Name: /oradata/backup_dir/ORCL_control_c-1446878566-20180731-01

File Name: /oradata/backup_dir/ORCL_db_full_20180731_i9t9cg5q_1_1

File Name: /oradata/backup_dir/ORCL_db_arch_20180731_ibt9cg8s_1_1

File Name: /oradata/backup_dir/ORCL_db_arch_ORCL_20180731_589

File Name: /oradata/backup_dir/dtstack_backup_arch_ORCL_20180731_174028.log

File Name: /oradata/backup_dir/ORCL_control_c-1446878566-20180731-02

File Name: /oradata/backup_dir/ORCL_control_c-1446878566-20180731-00

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...

cataloging done

List of Cataloged Files

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

File Name: /oradata/backup_dir/ORCL_control_c-1446878566-20180731-01

File Name: /oradata/backup_dir/ORCL_db_full_20180731_i9t9cg5q_1_1

File Name: /oradata/backup_dir/ORCL_db_arch_20180731_ibt9cg8s_1_1

File Name: /oradata/backup_dir/ORCL_db_arch_ORCL_20180731_589

File Name: /oradata/backup_dir/ORCL_control_c-1446878566-20180731-02

File Name: /oradata/backup_dir/ORCL_control_c-1446878566-20180731-00

List of Files Which Where Not Cataloged

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

File Name: /oradata/backup_dir/dtstack_backup_full_ORCL_20180731_105132.log

  RMAN-07517: Reason: The file header is corrupted

File Name: /oradata/backup_dir/dtstack_backup_arch_ORCL_20180731_174028.log

  RMAN-07517: Reason: The file header is corrupted

RMAN> restore database;

Starting restore at 01-AUG-18

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 /data/oradata/orcl/system01.dbf

channel ORA_DISK_1: restoring datafile 00002 to /data/oradata/orcl/sysaux01.dbf

channel ORA_DISK_1: restoring datafile 00003 to /data/oradata/orcl/undotbs01.dbf

channel ORA_DISK_1: restoring datafile 00004 to /data/oradata/orcl/users01.dbf

channel ORA_DISK_1: restoring datafile 00005 to /data/oradata/orcl/goods_data_01.dbf

channel ORA_DISK_1: restoring datafile 00006 to /data/oradata/orcl/ztc_data.dbf

channel ORA_DISK_1: reading from backup piece /oradata/backup_dir/ORCL_db_full_20180731_i9t9cg5q_1_1

channel ORA_DISK_1: piece handle=/oradata/backup_dir/ORCL_db_full_20180731_i9t9cg5q_1_1 tag=FULL_BK

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:02:45

Finished restore at 01-AUG-18  

 

 

3.4、追加归档

RMAN> recover database;

Starting recover at 01-AUG-18

using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=3023

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=3024

channel ORA_DISK_1: reading from backup piece /oradata/backup_dir/ORCL_db_arch_20180731_ibt9cg8s_1_1

channel ORA_DISK_1: piece handle=/oradata/backup_dir/ORCL_db_arch_20180731_ibt9cg8s_1_1 tag=TAG20180731T105316

channel ORA_DISK_1: restored backup piece 1

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

archived log file name=/data/archivelog/orcl/1_3023_919097833.dbf thread=1 sequence=3023

archived log file name=/data/archivelog/orcl/1_3024_919097833.dbf thread=1 sequence=3024

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=3025

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=3026

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=3027

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=3028

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=3029

channel ORA_DISK_1: reading from backup piece /oradata/backup_dir/ORCL_db_arch_ORCL_20180731_589

channel ORA_DISK_1: piece handle=/oradata/backup_dir/ORCL_db_arch_ORCL_20180731_589 tag=TAG20180731T174033

channel ORA_DISK_1: restored backup piece 1

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

archived log file name=/data/archivelog/orcl/1_3025_919097833.dbf thread=1 sequence=3025

archived log file name=/data/archivelog/orcl/1_3026_919097833.dbf thread=1 sequence=3026

archived log file name=/data/archivelog/orcl/1_3027_919097833.dbf thread=1 sequence=3027

archived log file name=/data/archivelog/orcl/1_3028_919097833.dbf thread=1 sequence=3028

archived log file name=/data/archivelog/orcl/1_3029_919097833.dbf thread=1 sequence=3029

unable to find archived log

archived log thread=1 sequence=3030

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

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

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

RMAN-03002: failure of recover command at 08/01/2018 13:12:43

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

继续下载归档注册备份片，继续恢复数据库

[oracle@iZ2zeeqh138yjl5wauo4wpZ ~]$ ./ossutil64 cp -r oss://oss-cn-beijing-internal.aliyuncs.com/20180801/iZ9zm93rjjdv8hZ:ORCL/ /home/oracle/oradata

Error: oss: service returned error: StatusCode=400, ErrorCode=InvalidBucketName, ErrorMessage=The specified bucket is not valid., RequestId=5B61436944ABABC2D267A989, Bucket=oss-cn-beijing-internal.aliyuncs.com!

[oracle@iZ2zeeqh138yjl5wauo4wpZ ~]$ ./ossutil64 cp -r oss://beebox-db/oss-cn-beijing-internal.aliyuncs.com/20180801/iZ9zm93rjjdv8hZ:ORCL/ /home/oracle/oradata/20180801

Succeed: Total num: 3, size: 510,387,022. OK num: 3(download 3 objects).                                 

9.735331(s) elapsed

[oracle@iZ2zeeqh138yjl5wauo4wpZ ~]$ cd /home/oracle/oradata/20180801

[oracle@iZ2zeeqh138yjl5wauo4wpZ 20180801]$ cd oss-cn-beijing-internal.aliyuncs.com/20180801/iZ9zm93rjjdv8hZ\:ORCL/

[oracle@iZ2zeeqh138yjl5wauo4wpZ iZ9zm93rjjdv8hZ:ORCL]$

[oracle@iZ2zeeqh138yjl5wauo4wpZ iZ9zm93rjjdv8hZ:ORCL]$ ls

dtstack_backup_arch_ORCL_20180801_131506.log  ORCL_control_c-1446878566-20180801-00  ORCL_db_arch_ORCL_20180801_591

[oracle@iZ2zeeqh138yjl5wauo4wpZ iZ9zm93rjjdv8hZ:ORCL]$ cp -r  ./*   /oradata/backup_dir/

[oracle@iZ2zeeqh138yjl5wauo4wpZ backup_dir]$ rman target  /   

Recovery Manager: Release 11.2.0.4.0 - Production on Wed Aug 1 13:23:24 2018

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

connected to target database: ORCL (DBID=1446878566, not open)

 

RMAN> catalog start with '/oradata/backup_dir';

using target database control file instead of recovery catalog

searching for all files that match the pattern /oradata/backup_dir

List of Files Unknown to the Database

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

File Name: /oradata/backup_dir/dtstack_backup_full_ORCL_20180731_105132.log

File Name: /oradata/backup_dir/ORCL_control_c-1446878566-20180731-01

File Name: /oradata/backup_dir/dtstack_backup_arch_ORCL_20180801_131506.log

File Name: /oradata/backup_dir/ORCL_control_c-1446878566-20180801-00

File Name: /oradata/backup_dir/dtstack_backup_arch_ORCL_20180731_174028.log

File Name: /oradata/backup_dir/ORCL_control_c-1446878566-20180731-02

File Name: /oradata/backup_dir/ORCL_control_c-1446878566-20180731-00

File Name: /oradata/backup_dir/ORCL_db_arch_ORCL_20180801_591

 

Do you really want to catalog the above files (enter YES or NO)? yes

cataloging files...

cataloging done

 

List of Cataloged Files

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

File Name: /oradata/backup_dir/ORCL_control_c-1446878566-20180731-01

File Name: /oradata/backup_dir/ORCL_control_c-1446878566-20180801-00

File Name: /oradata/backup_dir/ORCL_control_c-1446878566-20180731-02

File Name: /oradata/backup_dir/ORCL_control_c-1446878566-20180731-00

File Name: /oradata/backup_dir/ORCL_db_arch_ORCL_20180801_591

 

List of Files Which Where Not Cataloged

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

File Name: /oradata/backup_dir/dtstack_backup_full_ORCL_20180731_105132.log

  RMAN-07517: Reason: The file header is corrupted

File Name: /oradata/backup_dir/dtstack_backup_arch_ORCL_20180801_131506.log

  RMAN-07517: Reason: The file header is corrupted

File Name: /oradata/backup_dir/dtstack_backup_arch_ORCL_20180731_174028.log

  RMAN-07517: Reason: The file header is corrupted

RMAN> recover database;

Starting recover at 01-AUG-18

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=771 device type=DISK

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=3030

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=3031

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=3032

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=3033

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=3034

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=3035

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=3036

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=3037

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=3038

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=3039

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=3040

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=3041

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=3042

channel ORA_DISK_1: reading from backup piece /oradata/backup_dir/ORCL_db_arch_ORCL_20180801_591

channel ORA_DISK_1: piece handle=/oradata/backup_dir/ORCL_db_arch_ORCL_20180801_591 tag=TAG20180801T131508

channel ORA_DISK_1: restored backup piece 1

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

archived log file name=/data/archivelog/orcl/1_3030_919097833.dbf thread=1 sequence=3030

archived log file name=/data/archivelog/orcl/1_3031_919097833.dbf thread=1 sequence=3031

archived log file name=/data/archivelog/orcl/1_3032_919097833.dbf thread=1 sequence=3032

archived log file name=/data/archivelog/orcl/1_3033_919097833.dbf thread=1 sequence=3033

archived log file name=/data/archivelog/orcl/1_3034_919097833.dbf thread=1 sequence=3034

archived log file name=/data/archivelog/orcl/1_3035_919097833.dbf thread=1 sequence=3035

archived log file name=/data/archivelog/orcl/1_3036_919097833.dbf thread=1 sequence=3036

archived log file name=/data/archivelog/orcl/1_3037_919097833.dbf thread=1 sequence=3037

archived log file name=/data/archivelog/orcl/1_3038_919097833.dbf thread=1 sequence=3038

archived log file name=/data/archivelog/orcl/1_3039_919097833.dbf thread=1 sequence=3039

archived log file name=/data/archivelog/orcl/1_3040_919097833.dbf thread=1 sequence=3040

archived log file name=/data/archivelog/orcl/1_3041_919097833.dbf thread=1 sequence=3041

archived log file name=/data/archivelog/orcl/1_3042_919097833.dbf thread=1 sequence=3042

unable to find archived log

archived log thread=1 sequence=3043

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

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

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

RMAN-03002: failure of recover command at 08/01/2018 13:24:33

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

数据库恢复到SCN为38883284

 

3.5、打开数据库验证

SQL>  alter database open RESETLOGS;

Database altered.

SQL> select to_char(scn_to_timestamp(38883284),'YYYY-MM-DD HH24:MI:SS') scn_date , to_char(sysdate,'YYYY-MM-DD HH24:MI:SS') now_date from dual;

SCN_DATE            NOW_DATE

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

2018-08-01 13:05:11 2018-08-01 13:39:41

数据库已经将数据追加到了2018-08-01 13:05:11，恢复演练验证成功

 

4、演练结果

  本次数据库恢复演练成功，easydb数据库备份的备份片有效且可用，能够在业务数据库极端情况下提供数据恢复功能。


返回列表

上一篇：等待事件latch: cache buffers chains 的分析与优化

下一篇：Linux进程管理详解

相关文章

MySQL运维实战之ProxySQL（9.7）改写SQL

使用查询修改功能，可以在不改变应用程序的情况下，修改SQL语句。比如，我们可以使用SQL改写的功能，给SQL添加hint，以此来优化性能。delete from mysql_que...

C++ 编程：数组的定义

1. 什么是数组？数组让能够按顺序将一系列相同类型的数据存储到内存中 C++ 中的数组可分为静态数组 与 动态数组 两种。2. 静态数组首先介绍声明一个 静态数组 的语法：/* ElementType...

11g单实例adg部署

一、环境规划搭建adg需要备端完成rdbms软件安装这一步和监听配置，不需要安装数据库。注意：db_unique_name 主备库不能相同db_name主备库需保持一致主备库DB版本需保持一致信息项主...

EMR-flinksql运行失败问题

EMR-flinksql运行失败问题

运行flinksqlsql-client.sh报错：[root@emr1 bin]# ./sql-client.shSLF4J: Class path contains multiple SLF4J...

oracle adg切换

1 主库转备库注意：原则上至多只能有一个主库，但是备库可以多个，所以需要先进行主库转备库操作。1.1 查看主库角色切换状态SYS@proe>select name,database_role,s...

Kafka优化参数

一、配置文件Kafka的配置文件为 config/server.properties，在此文件中进行 Kafka 的基础配置，例如端口、日志目录、Zookeeper 信息和 Broker ID 等还可...

发表评论    

◎欢迎参与讨论，请在这里发表您的看法、交流您的观点。

©Copyrights 2016-2022 YUNCHE 浙ICP备2021017017号