Oracle数据库恢复演练
1、演练目的
验证核心系统数据库备份的有效性，在极端数据库故障情况下保证数据库存在一份可用的备份文件，为业务数据的安全提供保障。
2、演练准备
提供一台2C16G本地60G的阿里ecs服务器，操作系统镜像使用袋鼠云Oracle11G OS镜像，使用镜像初始化ecs后已经安装了Oracle 11gR2(11.2.0.4)软件，部署和配置ossutil，操作如下
下载ossutil64工具 cd /home/oracle 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数据库备份的备份片有效且可用，能够在业务数据库极端情况下提供数据恢复功能。
