Oracle数据库RMAN备份及恢复

RMAN备份与恢复

一、 原理

和单机使用RMAN备份原理相同，利用其中一个节点做备份（数据都放在ASM共享存储上），恢复也在一个节点上做，恢复到共享存储上，最后2个节点都能正常启动

1.1查看归档日志

[root@redhat2 ~]# su - oracle redhat2-> sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 24 18:31:46 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to:

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

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options //查看归档模式和强制日志是否开启，这里都没有开启

SQL> select name,log_mode,force_logging from gv$database; NAME LOG_MODE FOR --------- ------------ --- DEVDB NOARCHIVELOG NO DEVDB NOARCHIVELOG NO

//开启强制日志模式，在一个节点上执行就可以了 SQL> alter database force logging; Database altered.

//再次查看发现强制日志模式已开启

SQL> select name,log_mode,force_logging from gv$database; NAME LOG_MODE FOR --------- ------------ --- DEVDB NOARCHIVELOG YES DEVDB NOARCHIVELOG YES SQL>

1.2开启归档模式

//开启归档模式需要关闭数据库，而且需要在mount模式下 [root@redhat1 ~]# su - grid //查看数据库状态 redhat1-> crs_stat -t

Name Type Target State Host ------------------------------------------------------------ ora.DATA.dg ora....up.type ONLINE ONLINE redhat1 ora.FLASH.dg ora....up.type ONLINE ONLINE redhat1 ora.GRIDDG.dg ora....up.type ONLINE ONLINE redhat1 ora....ER.lsnr ora....er.type ONLINE ONLINE redhat1

ora....N1.lsnr ora....er.type ONLINE ONLINE redhat2 ora.asm ora.asm.type ONLINE ONLINE redhat1 ora.devdb.db ora....se.type ONLINE ONLINE redhat1 ora.eons ora.eons.type ONLINE ONLINE redhat1 ora.gsd ora.gsd.type OFFLINE OFFLINE ora....network ora....rk.type ONLINE ONLINE redhat1 ora.oc4j ora.oc4j.type OFFLINE OFFLINE ora.ons ora.ons.type ONLINE ONLINE redhat1 ora....SM1.asm application ONLINE ONLINE redhat1 ora....T1.lsnr application ONLINE ONLINE redhat1 ora....at1.gsd application OFFLINE OFFLINE ora....at1.ons application ONLINE ONLINE redhat1 ora....at1.vip ora....t1.type ONLINE ONLINE redhat1 ora....SM2.asm application ONLINE ONLINE redhat2 ora....T2.lsnr application ONLINE ONLINE redhat2 ora....at2.gsd application OFFLINE OFFLINE ora....at2.ons application ONLINE ONLINE redhat2 ora....at2.vip ora....t1.type ONLINE ONLINE redhat2 ora.scan1.vip ora....ip.type ONLINE ONLINE redhat2 //查看集群节点运行状态

redhat1-> srvctl status database -d devdb Instance devdb1 is running on node redhat1 Instance devdb2 is running on node redhat2 //关闭集群节点实例

redhat1-> srvctl stop database -d devdb redhat1-> srvctl status database -d devdb Instance devdb1 is not running on node redhat1 Instance devdb2 is not running on node redhat2 //切换到oracle用户，挂启数据库 [root@redhat1 ~]# su - oracle redhat2-> sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 24 18:50:10 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to an idle instance. SQL> startup mount; ORACLE instance started.

Total System Global Area 1269366784 bytes Fixed Size Variable Size Redo Buffers

2212976 bytes

1023413136 bytes 8859648 bytes

Database Buffers 234881024 bytes Database mounted. //查看数据库是挂载状态

SQL> select open_mode from v$database;

OPEN_MODE

-------------------- MOUNTED

//查看归档模式还没有开启

SQL> select open_mode,log_mode from v$database; OPEN_MODE LOG_MODE

-------------------- ------------ MOUNTED

NOARCHIVELOG

//开启归档模式

SQL> alter database archivelog; Database altered. //查看归档模式已开启

SQL> select open_mode,log_mode from v$database; OPEN_MODE LOG_MODE

-------------------- ------------ MOUNTED

ARCHIVELOG

//关闭数据库，通过集群启动两个数据库节点 SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL>

//切换到grid用户，通过集群软件启动数据库实例 [root@redhat1 ~]# su - grid redhat1-> id uid=1100(grid)

redhat1-> srvctl start database -d devdb redhat1->

//切换到oracle用户，再次查看状态，归档模式和强制日志都是开启 [root@redhat2 ~]# su - oracle redhat2-> sqlplus / as sysdba;

SQL*Plus: Release 11.2.0.1.0 Production on Fri Jul 24 19:40:51 2015 Copyright (c) 1982, 2009, Oracle. All rights reserved. Connected to:

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

With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP, Data Mining and Real Application Testing options

SQL> select name,log_mode,force_logging from gv$database; NAME LOG_MODE FOR --------- ------------ --- DEVDB ARCHIVELOG YES DEVDB ARCHIVELOG YES SQL>

gid=1000(oinstall)

groups=1000(oinstall),1200(asmadmin),1201(asmdba),1202(asmoper),1300(dba)

1.3数据库备份

//查看日志信息 SQL> set line 200

SQL> select * from v$log;

GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE

MEMBERS ARC STATUS

FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME

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

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

1 1 7 52428800 512 2 CURRENT 28201762 2015/07/24 18:53:19 2.8147E+14 2 1 6 52428800 512

2 INACTIVE 28201760 2015/07/24 18:53:18 28201762

2015/07/24 18:53:19

3 2 3 52428800 512

2 CURRENT 28201758 2015/07/24 18:53:18 2.8147E+14 2015/07/24

18:53:18

4 2 2 52428800 512

2 INACTIVE 28132426 2015/07/24 09:00:33 28201752

2015/07/24 18:34:35 SQL>

//查看归档日志存放路径，这里并没有配置存放路径 SQL> show parameter log_archive; NAME

TYPE VALUE

------------------------------------ ----------- ------------------------------ log_archive_config string log_archive_dest string

log_archive_dest_1

string

log_archive_dest_10 string log_archive_dest_11 string log_archive_dest_12 string log_archive_dest_13 string log_archive_dest_14 string log_archive_dest_15 string log_archive_dest_16 string log_archive_dest_17 string NAME

TYPE VALUE

------------------------------------ ----------- ------------------------------ log_archive_dest_18 string log_archive_dest_19 string log_archive_dest_2

string

log_archive_dest_20 string log_archive_dest_21 string log_archive_dest_22 string

NO YES

NO

YES

log_archive_dest_23 string log_archive_dest_24 string log_archive_dest_25 string log_archive_dest_26 string log_archive_dest_27 string NAME

TYPE VALUE

------------------------------------ ----------- ------------------------------ log_archive_dest_28 string log_archive_dest_29 string log_archive_dest_3

string

log_archive_dest_30 string log_archive_dest_31 string log_archive_dest_4 log_archive_dest_5 log_archive_dest_6 log_archive_dest_7 log_archive_dest_8 log_archive_dest_9 NAME

string string string string string string

TYPE VALUE

------------------------------------ ----------- ------------------------------ log_archive_dest_state_1 string enable log_archive_dest_state_10 string enable log_archive_dest_state_11 string enable log_archive_dest_state_12 string enable log_archive_dest_state_13 string enable log_archive_dest_state_14 string enable log_archive_dest_state_15 string enable log_archive_dest_state_16 string enable log_archive_dest_state_17 string enable log_archive_dest_state_18 string enable log_archive_dest_state_19 string enable NAME

TYPE VALUE

------------------------------------ ----------- ------------------------------ log_archive_dest_state_2 string enable log_archive_dest_state_20 string enable log_archive_dest_state_21 string enable log_archive_dest_state_22 string enable log_archive_dest_state_23 string enable log_archive_dest_state_24 string enable log_archive_dest_state_25 string enable log_archive_dest_state_26 string enable log_archive_dest_state_27 string enable log_archive_dest_state_28 string enable log_archive_dest_state_29 string enable

ONLINELOG/

ASMCMD> cd ARCHIVELOG ASMCMD> ls 2015_08_17/

ASMCMD> cd 2015_08_17 ASMCMD> ls

thread_1_seq_56.345.887961653

2.3、生成测试数据，提交

SQL>create table myTestTable as

select rownum as id,

to_char(sysdate + rownum/24/3600, 'yyyy-mm-dd hh24:mi:ss') as inc_datetime, trunc(dbms_random.value(0, 100)) as random_id, dbms_random.string('x', 20) random_string from dual

connect by level <= 100000;

SQL>commit;

三、 使用RMAN备份数据库

3.1 脚本

export ORACLE_SID=devdb1 export BACKUP_DIR=/home/oracle

export RMAN_SCRIPTS=$BACKUP_DIR/scripts export RMAN_BACKUPSETS=$BACKUP_DIR/backupsets

export RMAN_LOG=$BACKUP_DIR/logs/rman_$ORACLE_SID.log export RMAN=$ORACLE_HOME/bin/rman

echo 'Starting Full Database Backup at '`date +%Y-%m-%d:%H:%M:%S` >>$RMAN_LOG $RMAN target / log $RMAN_LOG append <

CONFIGURE DEFAULT DEVICE TYPE TO DISK;

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT '$RMAN_BACKUPSETS/$ORACLE_SID-%U.rman'; CONFIGURE CONTROLFILE AUTOBACKUP ON; CONFIGURE

CONTROLFILE

AUTOBACKUP

FORMAT

FOR

DEVICE

TYPE

DISK

TO

'$RMAN_BACKUPSETS/$ORACLE_SID-%F.ctl'; CONFIGURE RETENTION POLICY TO REDUNDANCY 6;

backup as compressed backupset database format '$RMAN_BACKUPSETS/$ORACLE_SID-%U.db' plus

archivelog format '$RMAN_BACKUPSETS/$ORACLE_SID-%U.arc' delete all input; delete noprompt obsolete; list backup summary; list backup by file; crosscheck archivelog all;

crosscheck backupset; exit; EOF

echo ''>>$RMAN_LOG

echo 'End Full Database Backup at '`date +%Y-%m-%d:%H:%M:%S` >>$RMAN_LOG echo ''>>$RMAN_LOG

3.2 执行备份脚本，执行后查看

redhat1-> ll total 1308352

-rw-r----- 1 oracle asmadmin 453228032 Aug 17 23:14 devdb1-11qes3fi_1_1.arc -rw-r----- 1 oracle asmadmin 455261696 Aug 17 23:15 devdb1-12qes3id_1_1.arc -rw-r----- 1 oracle asmadmin 401924096 Aug 17 23:18 devdb1-13qes3mk_1_1.db -rw-r----- 1 oracle asmadmin 9184256 Aug 17 23:18 devdb1-14qes3qo_1_1.arc -rw-r-----1oracle asmadmin 18808832 Aug 17 23:18 devdb1-c-778563805-20150817-00.ctl

redhat1-> rman

Recovery Manager: Release 11.2.0.1.0 - Production on Mon Aug 17 23:28:43 2015 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. connected to target database: DEVDB (DBID=778563805) RMAN> list backup of database summary;

using target database control file instead of recovery catalog List of Backups ===============

Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- ------------------- ------- ------- ---------- ---

11 B F A DISK 2015/07/23 04:12:45 1 1 NO TAG20150723T041239

12 B F A DISK 2015/07/23 04:13:19 1 1 NO TAG20150723T041239

15 B F A DISK 2015/07/23 04:14:05 1 1 NO TAG20150723T041239

16 B F A DISK 2015/07/23 04:14:22 1 1 NO TAG20150723T041239

20 B F X DISK 2015/07/23 20:18:59 1 1 YES TAG20150723T201749

24 B F X DISK 2015/07/23 22:26:23 1 1 YES TAG20150723T222509

29 B F A DISK 2015/08/17 23:18:33 1 1 YES TAG20150817T231635

RMAN>

RMAN> list backup of database;

List of Backup Sets ===================

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 11 Full 14.29M DISK 00:00:06 2015/07/23 04:12:45

BP Key: 11 Status: AVAILABLE Compressed: NO Tag: TAG20150723T041239 Piece Name: /rman_backup/Full_0cqco39n_1_1.bak List of Datafiles in backup set 11

File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ----

4 Full 27880714 2015/07/23 04:12:39 +DATA/devdb/datafile/users.259.885859951 7 Full 27880714 2015/07/23 04:12:39 +DATA/devdb/datafile/hstest_db

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 12 Full 72.17M DISK 00:00:38 2015/07/23 04:13:19

BP Key: 12 Status: AVAILABLE Compressed: NO Tag: TAG20150723T041239 Piece Name: /rman_backup/Full_0eqco39p_1_1.bak List of Datafiles in backup set 12

File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ----

3 Full 27880731 2015/07/23 04:12:43 +DATA/devdb/datafile/undotbs1.269.885859951 5 Full 27880731 2015/07/23 04:12:43 +DATA/devdb/datafile/example.265.885859951

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 15 Full 608.40M DISK 00:01:26 2015/07/23 04:14:05

BP Key: 15 Status: AVAILABLE Compressed: NO Tag: TAG20150723T041239 Piece Name: /rman_backup/Full_0dqco39n_1_1.bak List of Datafiles in backup set 15

File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ----

1 Full 27880716 2015/07/23 04:12:41 +DATA/devdb/datafile/system.260.885859951 6 Full 27880716 2015/07/23 04:12:41 +DATA/devdb/datafile/undotbs2.264.885859951

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 16 Full 925.01M DISK 00:01:43 2015/07/23 04:14:22

BP Key: 16 Status: AVAILABLE Compressed: NO Tag: TAG20150723T041239 Piece Name: /rman_backup/Full_0bqco39n_1_1.bak

List of Datafiles in backup set 16

File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ----

2 Full 27880676 2015/07/23 04:12:39 +DATA/devdb/datafile/sysaux.261.885859951

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 20 Full 379.14M DISK 00:01:10 2015/07/23 20:18:59 BP Key: 20 Status: EXPIRED Compressed: YES Tag: TAG20150723T201749 Piece Name: /home/oracle/backupsets/devdb1-0oqcprrd_1_1.db List of Datafiles in backup set 20

File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ----

1 Full 28006859 2015/07/23 20:17:49 +DATA/devdb/datafile/system.260.885859951 2 Full 28006859 2015/07/23 20:17:49 +DATA/devdb/datafile/sysaux.261.885859951 3 Full 28006859 2015/07/23 20:17:49 +DATA/devdb/datafile/undotbs1.269.885859951 4 Full 28006859 2015/07/23 20:17:49 +DATA/devdb/datafile/users.259.885859951 5 Full 28006859 2015/07/23 20:17:49 +DATA/devdb/datafile/example.265.885859951 6 Full 28006859 2015/07/23 20:17:49 +DATA/devdb/datafile/undotbs2.264.885859951 7 Full 28006859 2015/07/23 20:17:49 +DATA/devdb/datafile/hstest_db

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 24 Full 384.10M DISK 00:01:14 2015/07/23 22:26:23 BP Key: 24 Status: EXPIRED Compressed: YES Tag: TAG20150723T222509 Piece Name: /home/oracle/backupsets/devdb1-0sqcq3a5_1_1.db List of Datafiles in backup set 24

File LV Type Ckp SCN Ckp Time Name ---- -- ---- ---------- ------------------- ----

1 Full 28039111 2015/07/23 22:25:10 +DATA/devdb/datafile/system.260.885859951 2 Full 28039111 2015/07/23 22:25:10 +DATA/devdb/datafile/sysaux.261.885859951 3 Full 28039111 2015/07/23 22:25:10 +DATA/devdb/datafile/undotbs1.269.885859951 4 Full 28039111 2015/07/23 22:25:10 +DATA/devdb/datafile/users.259.885859951 5 Full 28039111 2015/07/23 22:25:10 +DATA/devdb/datafile/example.265.885859951 6 Full 28039111 2015/07/23 22:25:10 +DATA/devdb/datafile/undotbs2.264.885859951 7 Full 28039111 2015/07/23 22:25:10 +DATA/devdb/datafile/hstest_db

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 29 Full 383.30M DISK 00:01:57 2015/08/17 23:18:33

BP Key: 29 Status: AVAILABLE Compressed: YES Tag: TAG20150817T231635 Piece Name: /home/oracle/backupsets/devdb1-13qes3mk_1_1.db List of Datafiles in backup set 29

File LV Type Ckp SCN Ckp Time Name

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

1 Full 30499173 2015/08/17 23:16:36 +DATA/devdb/datafile/system.260.885859951 2 Full 30499173 2015/08/17 23:16:36 +DATA/devdb/datafile/sysaux.261.885859951 3 Full 30499173 2015/08/17 23:16:36 +DATA/devdb/datafile/undotbs1.269.885859951 4 Full 30499173 2015/08/17 23:16:36 +DATA/devdb/datafile/users.259.885859951 5 Full 30499173 2015/08/17 23:16:36 +DATA/devdb/datafile/example.265.885859951 6 Full 30499173 2015/08/17 23:16:36 +DATA/devdb/datafile/undotbs2.264.885859951 7 Full 30499173 2015/08/17 23:16:36 +DATA/devdb/datafile/hstest_db

RMAN> list backup of controlfile;

List of Backup Sets ===================

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 13 Full 17.67M DISK 00:00:27 2015/07/23 04:13:21

BP Key: 13 Status: AVAILABLE Compressed: NO Tag: TAG20150723T041239 Piece Name: /rman_backup/Full_0fqco3a6_1_1.bak

Control File Included: Ckp SCN: 27880739 Ckp time: 2015/07/23 04:12:54

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 21 Full 1.06M DISK 00:00:03 2015/07/23 20:19:07 BP Key: 21 Status: EXPIRED Compressed: YES Tag: TAG20150723T201749 Piece Name: /home/oracle/backupsets/devdb1-0pqcprto_1_1.db

Control File Included: Ckp SCN: 28007020 Ckp time: 2015/07/23 20:19:04

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 26 Full 17.92M DISK 00:00:07 2015/07/24 00:20:36 BP Key: 26 Status: EXPIRED Compressed: NO Tag: TAG20150724T002029 Piece Name: /home/oracle/backupsets/devdb1-c-778563805-20150724-00.ctl Control File Included: Ckp SCN: 28048449 Ckp time: 2015/07/24 00:20:28

BS Key Type LV Size Device Type Elapsed Time Completion Time ------- ---- -- ---------- ----------- ------------ ------------------- 31 Full 17.92M DISK 00:00:03 2015/08/17 23:18:55

BP Key: 31 Status: AVAILABLE Compressed: NO Tag: TAG20150817T231852 Piece Name: /home/oracle/backupsets/devdb1-c-778563805-20150817-00.ctl Control File Included: Ckp SCN: 30503180 Ckp time: 2015/08/17 23:18:52

RMAN>

RMAN> list backup of archivelog all;

List of Backup Sets ===================

BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ------------------- 17 2.17M DISK 00:00:01 2015/07/23 04:19:02

BP Key: 17 Status: AVAILABLE Compressed: NO Tag: TAG20150723T041901 Piece Name: /rman_backup/ARC_0hqco3ll_1_1.bak

List of Archived Logs in backup set 17

Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- ---------

1 534 27874714 2015/07/23 03:35:56 27881425 2015/07/23 04:19:01

BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ------------------- 18 3.83M DISK 00:00:00 2015/07/23 04:19:02

BP Key: 18 Status: AVAILABLE Compressed: NO Tag: TAG20150723T041901 Piece Name: /rman_backup/ARC_0iqco3lm_1_1.bak

List of Archived Logs in backup set 18

Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- ---------

2 280 27874718 2015/07/23 03:35:58 27881421 2015/07/23 04:18:59

BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ------------------- 19 29.67M DISK 00:00:05 2015/07/23 20:17:46

BP Key: 19 Status: EXPIRED Compressed: YES Tag: TAG20150723T201740 Piece Name: /home/oracle/backupsets/devdb1-0nqcprr5_1_1.arc

List of Archived Logs in backup set 19

Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- ---------

1 534 27874714 2015/07/23 03:35:56 27881425 2015/07/23 04:19:01 1 535 27881425 2015/07/23 04:19:01 27978703 2015/07/23 17:00:15 1 536 27978703 2015/07/23 17:00:15 28002124 2015/07/23 19:55:42 1 537 28002124 2015/07/23 19:55:42 28002513 2015/07/23 19:58:26 1 538 28002513 2015/07/23 19:58:26 28002661 2015/07/23 19:59:00

1 539 28002661 2015/07/23 19:59:00 28006534 2015/07/23 20:16:20 1 540 28006534 2015/07/23 20:16:20 28006827 2015/07/23 20:17:33 2 280 27874718 2015/07/23 03:35:58 27881421 2015/07/23 04:18:59 2 281 27881421 2015/07/23 04:18:59 28002128 2015/07/23 19:55:42 2 282 28002128 2015/07/23 19:55:42 28002517 2015/07/23 19:58:29 2 283 28002517 2015/07/23 19:58:29 28002632 2015/07/23 19:58:56 2 284 28002632 2015/07/23 19:58:56 28006538 2015/07/23 20:16:23 2 285 28006538 2015/07/23 20:16:23 28006831 2015/07/23 20:17:36

BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ------------------- 22 76.50K DISK 00:00:00 2015/07/23 20:19:14

BP Key: 22 Status: EXPIRED Compressed: YES Tag: TAG20150723T201914 Piece Name: /home/oracle/backupsets/devdb1-0qqcpru2_1_1.arc

List of Archived Logs in backup set 22

Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- ---------

1 541 28006827 2015/07/23 20:17:33 28007044 2015/07/23 20:19:09 2 286 28006831 2015/07/23 20:17:36 28007028 2015/07/23 20:19:09

BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ------------------- 23 17.18M DISK 00:00:03 2015/07/23 22:25:08

BP Key: 23 Status: EXPIRED Compressed: YES Tag: TAG20150723T222505 Piece Name: /home/oracle/backupsets/devdb1-0rqcq3a1_1_1.arc

List of Archived Logs in backup set 23

Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- ---------

1 542 28007044 2015/07/23 20:19:09 28039095 2015/07/23 22:24:56 2 287 28007028 2015/07/23 20:19:09 28032125 2015/07/23 22:02:00 2 288 28032125 2015/07/23 22:02:00 28039091 2015/07/23 22:24:55

BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ------------------- 25 16.50K DISK 00:00:00 2015/07/23 22:26:29

BP Key: 25 Status: EXPIRED Compressed: YES Tag: TAG20150723T222629 Piece Name: /home/oracle/backupsets/devdb1-0tqcq3cl_1_1.arc

List of Archived Logs in backup set 25

Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- ---------

1 543 28039095 2015/07/23 22:24:56 28039257 2015/07/23 22:26:25

2 289 28039091 2015/07/23 22:24:55 28039261 2015/07/23 22:26:26

BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ------------------- 27 432.23M DISK 00:01:16 2015/08/17 23:14:06

BP Key: 27 Status: AVAILABLE Compressed: YES Tag: TAG20150817T231248 Piece Name: /home/oracle/backupsets/devdb1-11qes3fi_1_1.arc

List of Archived Logs in backup set 27

Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- ---------

1 8 28256105 2015/07/24 23:55:45 28350950 2015/07/25 09:38:42 1 9 28350950 2015/07/25 09:38:42 28494216 2015/07/25 23:20:35 1 10 28494216 2015/07/25 23:20:35 28553872 2015/07/26 06:00:47 1 11 28553872 2015/07/26 06:00:47 28611146 2015/07/26 10:06:02 1 12 28611146 2015/07/26 10:06:02 28736917 2015/07/26 23:04:02 1 13 28736917 2015/07/26 23:04:02 28854298 2015/07/27 12:32:49 1 14 28854298 2015/07/27 12:32:49 28920752 2015/07/27 19:07:17 1 15 28920752 2015/07/27 19:07:17 28942542 2015/07/27 19:26:38 1 16 28942542 2015/07/27 19:26:38 28942591 2015/07/27 19:26:40 1 17 28942821 2015/07/27 19:27:47 28963134 2015/07/27 19:54:17 1 18 28963134 2015/07/27 19:54:17 28963504 2015/07/27 19:54:51 1 19 28963581 2015/07/27 19:55:25 29044186 2015/08/11 22:36:44 1 20 29044186 2015/08/11 22:36:44 29063950 2015/08/11 23:06:45 1 21 29063950 2015/08/11 23:06:45 29072162 2015/08/11 23:07:16 1 22 29072162 2015/08/11 23:07:16 29079185 2015/08/11 23:07:49 1 23 29079185 2015/08/11 23:07:49 29084697 2015/08/11 23:08:15 1 24 29084697 2015/08/11 23:08:15 29091762 2015/08/11 23:08:47 1 25 29091762 2015/08/11 23:08:47 29097992 2015/08/11 23:09:16 1 26 29097992 2015/08/11 23:09:16 29103209 2015/08/11 23:09:37 1 27 29103209 2015/08/11 23:09:37 29108408 2015/08/11 23:09:58 1 28 29108408 2015/08/11 23:09:58 29113615 2015/08/11 23:10:21 1 29 29113615 2015/08/11 23:10:21 29118608 2015/08/11 23:10:42 1 30 29118608 2015/08/11 23:10:42 29123514 2015/08/11 23:11:01 1 31 29123514 2015/08/11 23:11:01 29128328 2015/08/11 23:11:22 1 32 29128328 2015/08/11 23:11:22 29133595 2015/08/11 23:11:53 2 5 28253004 2015/07/24 23:21:52 28329268 2015/07/25 08:24:02 2 6 28329268 2015/07/25 08:24:02 28334517 2015/07/25 08:24:25 2 7 28334517 2015/07/25 08:24:25 28364971 2015/07/25 10:05:05 2 8 28364971 2015/07/25 10:05:05 28424494 2015/07/25 16:00:07 2 9 28424494 2015/07/25 16:00:07 28519900 2015/07/26 02:56:25 2 10 28519900 2015/07/26 02:56:25 28578616 2015/07/26 08:25:15 2 11 28578616 2015/07/26 08:25:15 28584653 2015/07/26 08:25:42 2 12 28584653 2015/07/26 08:25:42 28611042 2015/07/26 10:05:59

2 13 28611042 2015/07/26 10:05:59 28649938 2015/07/26 14:00:46 2 14 28649938 2015/07/26 14:00:46 28729670 2015/07/26 22:06:06 2 15 28729670 2015/07/26 22:06:06 28809493 2015/07/27 08:26:22 2 16 28809493 2015/07/27 08:26:22 28816032 2015/07/27 08:26:52 2 17 28816032 2015/07/27 08:26:52 28887563 2015/07/27 17:00:05 2 18 28887563 2015/07/27 17:00:05 28920749 2015/07/27 19:07:16 2 19 28920749 2015/07/27 19:07:16 28920758 2015/07/27 19:07:19 2 20 28921665 2015/07/27 19:08:39 28942544 2015/07/27 19:26:38 2 21 28942544 2015/07/27 19:26:38 28963136 2015/07/27 19:54:18 2 22 28963136 2015/07/27 19:54:18 28996237 2015/07/27 22:01:43 2 23 28996237 2015/07/27 22:01:43 29044183 2015/08/11 22:36:44 2 24 29044183 2015/08/11 22:36:44 29044645 2015/08/11 22:37:17 2 25 29052259 2015/08/11 22:38:09 29079607 2015/08/11 23:07:53 2 26 29079607 2015/08/11 23:07:53 29098129 2015/08/11 23:09:19 2 27 29098129 2015/08/11 23:09:19 29115901 2015/08/11 23:10:32 2 28 29115901 2015/08/11 23:10:32 29129307 2015/08/11 23:11:31

BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ------------------- 28 434.17M DISK 00:01:37 2015/08/17 23:15:58

BP Key: 28 Status: AVAILABLE Compressed: YES Tag: TAG20150817T231248 Piece Name: /home/oracle/backupsets/devdb1-12qes3id_1_1.arc

List of Archived Logs in backup set 28

Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- ---------

1 33 29133595 2015/08/11 23:11:53 29138668 2015/08/11 23:12:14 1 34 29138668 2015/08/11 23:12:14 29144218 2015/08/11 23:12:35 1 35 29144218 2015/08/11 23:12:35 29188308 2015/08/12 05:00:07 1 36 29188308 2015/08/12 05:00:07 29285481 2015/08/12 18:54:33 1 37 29285481 2015/08/12 18:54:33 29320563 2015/08/12 22:00:53 1 38 29320563 2015/08/12 22:00:53 29352310 2015/08/12 23:07:59 1 39 29352310 2015/08/12 23:07:59 29385926 2015/08/13 02:35:00 1 40 29385926 2015/08/13 02:35:00 29487893 2015/08/13 16:00:34 1 41 29487893 2015/08/13 16:00:34 29562846 2015/08/13 23:09:36 1 42 29562846 2015/08/13 23:09:36 29591748 2015/08/14 00:26:54 1 43 29591748 2015/08/14 00:26:54 29681569 2015/08/14 11:00:34 1 44 29681569 2015/08/14 11:00:34 29759142 2015/08/14 21:03:43 1 45 29759142 2015/08/14 21:03:43 29793348 2015/08/14 22:00:36 1 46 29793348 2015/08/14 22:00:36 29805899 2015/08/14 22:02:43 1 47 29805899 2015/08/14 22:02:43 29822752 2015/08/14 23:11:05 1 48 29822752 2015/08/14 23:11:05 29926615 2015/08/15 10:00:05 1 49 29926615 2015/08/15 10:00:05 29966939 2015/08/15 14:01:57 1 50 29966939 2015/08/15 14:01:57 30038861 2015/08/15 22:02:00

1 51 30038861 2015/08/15 22:02:00 30065489 2015/08/16 00:00:49 1 52 30065489 2015/08/16 00:00:49 30123776 2015/08/16 06:00:56 1 53 30123776 2015/08/16 06:00:56 30182355 2015/08/16 12:00:49 1 54 30182355 2015/08/16 12:00:49 30238051 2015/08/16 18:03:37 1 55 30238051 2015/08/16 18:03:37 30286649 2015/08/16 23:13:14 1 56 30286649 2015/08/16 23:13:14 30362245 2015/08/17 08:00:52 1 57 30362245 2015/08/17 08:00:52 30452656 2015/08/17 19:52:45 1 58 30452656 2015/08/17 19:52:45 30496910 2015/08/17 23:12:39 2 29 29129307 2015/08/11 23:11:31 29144337 2015/08/11 23:12:37 2 30 29144337 2015/08/11 23:12:37 29272438 2015/08/12 17:00:12 2 31 29272438 2015/08/12 17:00:12 29352463 2015/08/12 23:08:02 2 32 29352463 2015/08/12 23:08:02 29480659 2015/08/13 15:00:30 2 33 29480659 2015/08/13 15:00:30 29546342 2015/08/13 22:01:37 2 34 29546342 2015/08/13 22:01:37 29681304 2015/08/14 11:00:32 2 35 29681304 2015/08/14 11:00:32 29793490 2015/08/14 22:00:39 2 36 29793490 2015/08/14 22:00:39 29893353 2015/08/15 06:00:52 2 37 29893353 2015/08/15 06:00:52 30037528 2015/08/15 22:00:46 2 38 30037528 2015/08/15 22:00:46 30123800 2015/08/16 06:00:57 2 39 30123800 2015/08/16 06:00:57 30286729 2015/08/16 23:13:16 2 40 30286729 2015/08/16 23:13:16 30423569 2015/08/17 16:00:20 2 41 30423569 2015/08/17 16:00:20 30483165 2015/08/17 22:01:39 2 42 30483165 2015/08/17 22:01:39 30496914 2015/08/17 23:12:41

BS Key Size Device Type Elapsed Time Completion Time ------- ---------- ----------- ------------ ------------------- 30 8.76M DISK 00:00:03 2015/08/17 23:18:51

BP Key: 30 Status: AVAILABLE Compressed: YES Tag: TAG20150817T231848 Piece Name: /home/oracle/backupsets/devdb1-14qes3qo_1_1.arc

List of Archived Logs in backup set 30

Thrd Seq Low SCN Low Time Next SCN Next Time ---- ------- ---------- ------------------- ---------- ---------

1 59 30496910 2015/08/17 23:12:39 30503166 2015/08/17 23:18:44 2 43 30496914 2015/08/17 23:12:41 30503163 2015/08/17 23:18:43 RMAN>

3.3 查看ASM中的归档数据，已经自动被删除了

[root@redhat1 ~]# su - grid redhat1-> asmcmd ASMCMD> ls DATA/ FLASH/ GRIDDG/

ASMCMD> cd FLASH ASMCMD> ls DEVDB/

ASMCMD> cd DEVDB ASMCMD> ls CONTROLFILE/ ONLINELOG/ ASMCMD>

3.4 模拟参数文件、数据文件、日志文件、控制文件被破坏

RAC停了再模拟被删除，否则数据文件删不了 $ export ORACLE_SID=+ASM1 [grid@rac1 ~]$ asmcmd ASMCMD> ls DATA/ FRA/ OCR_VOTE/ ASMCMD> cd DATA ASMCMD> ls PROD/

ASMCMD> cd PROD ASMCMD> ls CONTROLFILE/ DATAFILE/ ONLINELOG/ PARAMETERFILE/ TEMPFILE/ spfileprod.ora ASMCMD> cd DATAFILE ASMCMD> ls

SYSAUX.257.840287025 SYSTEM.256.840287025 UNDOTBS1.258.840287025 UNDOTBS2.264.840287183 USERS.259.840287025 ASMCMD> rm -rf * ASMCMD> ls

ASMCMD-8002: entry 'DATAFILE' does not exist in directory '+DATA/PROD/'

ASMCMD> cd CONTROLFILE ASMCMD> ls

Current.260.840287101 ASMCMD> rm -rf Current*

ASMCMD> cd FRA ASMCMD> ls PROD/

ASMCMD> cd prod/controlfile ASMCMD> ls

Current.256.840287101 ASMCMD> rm -rf Current* ASMCMD> ls

ASMCMD-8002: entry 'controlfile' does not exist in directory '+FRA/prod/'

ASMCMD> rm -rf spfileprod.ora ASMCMD> ls ONLINELOG/ TEMPFILE/

ASMCMD> cd DATA ASMCMD> ls PROD/

ASMCMD> cd prod ASMCMD> ls ONLINELOG/ TEMPFILE/

ASMCMD> cd ONLIELOG

ASMCMD-8002: entry 'ONLIELOG' does not exist in directory '+DATA/prod/' ASMCMD> cd ONLINELOG ASMCMD> ls

group_1.261.840287105 group_2.262.840287107 group_3.265.840287279 group_4.266.840287281 ASMCMD> rm -rf group*

3.5 尝试启动数据库，报错，提示文件不存在

[oracle@rac1 ~]$ srvctl start database -d prod PRCR-1079 : Failed to start resource ora.prod.db

CRS-5017: The resource action \ORA-01078: failure in processing system parameters

ORA-01565: error in identifying file '+DATA/prod/spfileprod.ora' ORA-17503: ksfdopn:2 Failed to open file +DATA/prod/spfileprod.ora ORA-15056: additional error message

ORA-17503: ksfdopn:2 Failed to open file +DATA/prod/spfileprod.ora ORA-15173: entry 'spfileprod.ora' does not exist in directory 'prod'

ORA-06512: at line 4 .

CRS-2674: Start of 'ora.prod.db' on 'rac1' failed

CRS-5017: The resource action \ORA-01078: failure in processing system parameters

ORA-01565: error in identifying file '+DATA/prod/spfileprod.ora' ORA-17503: ksfdopn:2 Failed to open file +DATA/prod/spfileprod.ora ORA-15056: additional error message

ORA-17503: ksfdopn:2 Failed to open file +DATA/prod/spfileprod.ora ORA-15173: entry 'spfileprod.ora' does not exist in directory 'prod' ORA-06512: at line 4 .

CRS-2674: Start of 'ora.prod.db' on 'rac2' failed

CRS-2632: There are no more servers to try to place resource 'ora.prod.db' on that would satisfy its placement policy

For details refer to \in

\

For details refer to \in

\

四、 恢复

4.1 将数据库启动到nomount状态，利用RMAN备份做恢复

[oracle@rac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Fri Mar 7 22:48:16 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database (not started) RMAN> set DBID=252125436; executing command: SET DBID RMAN> startup nomount;

startup failed: ORA-01078: failure in processing system parameters ORA-01565: error in identifying file '+DATA/prod/spfileprod.ora' ORA-17503: ksfdopn:2 Failed to open file +DATA/prod/spfileprod.ora ORA-15056: additional error message

ORA-17503: ksfdopn:2 Failed to open file +DATA/prod/spfileprod.ora ORA-15173: entry 'spfileprod.ora' does not exist in directory 'prod' ORA-06512: at line 4

starting Oracle instance without parameter file for retrieval of spfile Oracle instance started

Total System Global Area 158662656 bytes Fixed Size 2226456 bytes

Variable Size 117442280 bytes Database Buffers 33554432 bytes Redo Buffers 5439488 bytes

4.2 利用备份的控制文件恢复参数文件

RMAN> restore spfile from '/home/oracle/backupsets/prod1-c-252125436-20140307-00.ctl';

Starting restore at 2014-03-07 22:51:27

using target database control file instead of recovery catalog allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=13 device type=DISK channel

ORA_DISK_1:

restoring

spfile

from

/home/oracle/backupsets/prod1-c-252125436-20140307-00.ctl

channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete Finished restore at 2014-03-07 22:51:28

ASMCMD> exit

[grid@rac1 ~]$ asmcmd ASMCMD> ls DATA/ FRA/ OCR_VOTE/ ASMCMD> cd DATA ASMCMD> ls DB_UNKNOWN/ PROD/

ASMCMD> cd PROD ASMCMD> ls TEMPFILE/ spfileprod.ora

spfileprod.ora文件已经恢复回来

4.3 恢复控制文件

RMAN> shutdown immediate;

Oracle instance shut down

RMAN> startup;

connected to target database (not started) Oracle instance started

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

AUTOBACKUP

RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of startup command at 03/07/2014 22:53:59

ORA-00205: error in identifying control file, check alert log for more info

SQL> select status from v$instance; STATUS ------------ STARTED

SQL> show parameter pfile;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------ spfile string +DATA/prod/spfileprod.ora SQL>

RMAN> restore

controlfile

'/home/oracle/backupsets/prod1-c-252125436-20140307-00.ctl';

Starting restore at 2014-03-07 22:56:10 allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=144 instance=prod1 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/prod/controlfile/current.265.841618571 output file name=+FRA/prod/controlfile/current.256.841618571 Finished restore at 2014-03-07 22:56:12

4.4 启动到mount状态

RMAN> alter database mount;

database mounted

released channel: ORA_DISK_1

RMAN> list backup of database;

List of Backup Sets ===================

from

本文来源：https://www.bwwdw.com/article/ozi2.html