Oracle数据库恢复演练

荞麦1年前技术文章861

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数据库备份的备份片有效且可用,能够在业务数据库极端情况下提供数据恢复功能。


相关文章

MySQL运维实战之元数据和数据字典

什么是元数据假设我们执行一个简单的SQL:select * from tab where col = 'value'...

Redis 哨兵机制

Redis 哨兵机制

前言Redis 主从复制模式下,一旦主节点出现了故障不可达,需要人工干预进行故障转移,无论对于 Redis 的应用方还是运维方都带来了很大的不便。对于应用方来说无法及时感知到主节点的变化,必然会造成一...

Hbase&Hive区别对比

Hbase和Hive定义区别Hbase,其实是Hadoop database的简称,是一种NoSQL数据库,主要适用于海量明细数据(十亿、百亿)的随机实时查询,如日志明细、交易清单、轨迹行为等。Hiv...

CDH实操--集群卸载

CDH实操--集群卸载

1、停止正在运行的服务a、控制台停止集群服务b、控制台停止Cloudera Management Servicec、命令行停止cm服务systemctl stop cloudera-scm-agent...

EasyMR如何为服务开启Kerberos

EasyMR如何为服务开启Kerberos

一、Hadoop为什么需要安全最早部署Hadoop集群时并没有考虑安全问题,未开启安全认证时,Hadoop 是以客户端提供的用户名作为用户凭证, 一般即是发起任务的Unix 用户。一般线上机器部署服务...

Hadoop配置LZO压缩

Hadoop配置LZO压缩

hadoop-lzo编译Hadoop支持LZO0. 环境准备maven(下载安装,配置环境变量,修改sitting.xml加阿里云镜像)gcc-c++zlib-develautoconfautomak...

发表评论    

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