Oracle 灾难恢复以及11g新特性恢复指导-MySQL-Tutorial-php.cn

实验：数据库灾难恢复（数据文件、控制文件、参数文件、归档文件等丢失）法一：利用冷备法二：RMAN恢复及11g新特性(list/advise/repair failure,create spfile from memory) 1.配置catalog数据库 1）catalog目录库：创建大文件表空间、用户、授权 create

实验：数据库灾难恢复（数据文件、控制文件、参数文件、归档文件等丢失）

法一：利用冷备

法二：RMAN恢复及11g新特性(list/advise/repair failure,create spfile from memory)

1.配置catalog数据库

1）catalog目录库：创建大文件表空间、用户、授权

create bigfile tablespace rc_data datafile '/u01/app/oracle/oradata/ORCL/rc_data.dbf' size 20m;

create user rc_admin identified by oracle default tablespace rc_data;

grant connect,resource,recovery_catalog_owner to rc_admin;

2）创建catalog库
RMAN> rman catalog rc_admin/oracle@ORCL

RMAN> create catalog;

3）注册catalog库（在target库中）

RMAN> rman target / catalog rc_admin/oracle@ORCL

RMAN>register database;

4）配置target数据库

RMAN> rman target / catalog rc_admin/oracle@ORCL

RMAN>configure retention policy to recovery window of 7 days;
--修改保留策略

RMAN>configure controlfile autobackup on;
--打开控制文件自动备份

RMAN>configure device type disk parallelism 4;
--开启并行备份，行度设置为4

2.备份target数据库

当所有文件丢失时，使用RMAN，应连接catalog库（catalog库中含有控制文件等）

[oracle@jibo admin]$ rman target / catalog rc_admin/oracle@ORCL

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Oct 16 10:19:55 2014

connected to target database: PROD (DBID=270879665)
connected to recovery catalog database

RMAN> show all;

RMAN configuration parameters for database with db_unique_name PROD are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 4 BACKUP TYPE TO BACKUPSET;
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_PROD.f'; # default

RMAN> backup database include current controlfile plus archivelog delete all input;
--全库备份加上归档日志文件

3.模拟灾难：

1）删除所有数据文件：
SYS@PROD>select name from v$datafile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b393xosc_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_b393xovt_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_undotbs1_b393xq2d_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_users_b393xqpm_.dbf
/u01/app/oracle/oradata/PROD/datafile/o1_mf_example_b393xp04_.dbf
/u01/app/oracle/oradata/PROD/datafile/tbs_users1.dbf
/u01/app/oracle/oradata/PROD/datafile/tbs_users2.dbf
/u01/app/oracle/oradata/PROD/datafile/pitr.dbf
/u01/app/oracle/oradata/PROD/datafile/pitr_ind.dbf
/u01/app/oracle/oradata/PROD/arch_tbs.dbf

10 rows selected.

SYS@PROD>!rm /u01/app/oracle/oradata/PROD/datafile/*.dbf

SYS@PROD>!rm /u01/app/oracle/oradata/PROD/arch_tbs.dbf

SYS@PROD>desc v$controlfile;
Name                                      Null?    Type
----------------------------------------- -------- ----------------------------
STATUS                                             VARCHAR2(7)
NAME                                               VARCHAR2(513)
IS_RECOVERY_DEST_FILE                              VARCHAR2(3)
BLOCK_SIZE                                         NUMBER
FILE_SIZE_BLKS                                     NUMBER

2）删除所有控制文件：
SYS@PROD>select name from v$controlfile;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/oradata/PROD/controlfile/o1_mf_b2255k12_.ctl
/u01/app/oracle/fast_recovery_area/PROD/controlfile/o1_mf_b2255kjo_.ctl

SYS@PROD>!rm /u01/app/oracle/oradata/PROD/controlfile/o1_mf_b2255k12_.ctl
SYS@PROD>!rm /u01/app/oracle/fast_recovery_area/PROD/controlfile/o1_mf_b2255kjo_.ctl

3）删除参数文件：
SYS@PROD>show parameter spfile;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
spfile                               string      /u01/app/oracle/product/11.2.0
                                                 /dbhome_1/dbs/spfilePROD.ora
SYS@PROD>!rm /u01/app/oracle/product/11.2.0/dbhome_1/dbs/spfilePROD.ora

4）删除归档文件：
desc v$archived_log --归档文件
SYS@PROD>select name from v$archived_log;

NAME
--------------------------------------------------------------------------------
/u01/app/oracle/fast_recovery_area/PROD/archivelog/2014_10_16/o1_mf_1_7_b3y4y1s8_.arc

/u01/app/oracle/fast_recovery_area/PROD/archivelog/2014_10_16/o1_mf_1_8_b3y56mno

...

8 rows selected.

SYS@PROD>!rm /u01/app/oracle/fast_recovery_area/PROD/archivelog/2014_10_16/*;

alter system checkpoint;
--触发错误

5）退出重新进入：
SYS@PROD>stutdown immediate
SP2-0734: unknown command beginning "stutdown i..." - rest of line ignored.
SYS@PROD>shutdown immediate
ORA-01116: error in opening database file 2
ORA-01110: data file 2: '/u01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_b393xovt_.dbf'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
SYS@PROD>shutdown abort
ORACLE instance shut down.
SYS@PROD>startup
ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/initPROD.ora'
--报错

4.利用catalog恢复target数据库

1）在catalog库查看target数据库DBID

sqlplus rc_admin/oracle

RC_ADMIN@ORCL>select * from rc_database;

    DB_KEY DBINC_KEY       DBID NAME     RESETLOGS_CHANGE# RESETLOGS
---------- ---------- ---------- -------- ----------------- ---------
       241        242 1385095721 ORCL                925702 03-SEP-14
         1         61 270879665 PROD               1107625 08-OCT-14

2）连接catalog数据库
[oracle@jibo ~]$ rman target / catalog rc_admin/oracle@ORCL

Recovery Manager: Release 11.2.0.4.0 - Production on Thu Oct 16 14:08:59 2014

connected to target database (not started)
connected to recovery catalog database
--可以连接，但数据库没有启动

3）设置dbid，并启动数据库到nomount状态
RMAN> set dbid=270879665;

executing command: SET DBID
database name is "PROD" and DBID is 270879665

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/initPROD.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

4)还原参数文件和数据文件
RMAN>restore spfile;

RMAN>restore controlfile;
或者：
RMAN>restore spfile from autobackup;
RMAN>restore controlfile from autobackup;
或者：
RMAN>restore spfile from '/u01/app/oracle/fast_recovery_area/PROD/autobackup/2014_10_16/o1_mf_s_861103939_b3yh28ts_.bkp';
RMAN>restore controlfile from '/u01/app/oracle/...';
--如果Oracle无法找到自动备份文件，则需要手工制定该文件的具体位置

5）启动数据库到mount状态并使用11g数据库的新特性（list/advise/repair failure）
RMAN> startup mount;

database is already started
database mounted
released channel: ORA_DISK_1
released channel: ORA_DISK_2
released channel: ORA_DISK_3
released channel: ORA_DISK_4

新特性：
list failure;
   --如果list failure 没有输出结果，则需要我们手动恢复数据库；
advise failure;
   --如果advise failure 输出的最后，显示没有可用的自动修复选项，则同样需要我们手动恢复；
repair failure;
   --三条命令顺序不能改变

补充：
如果list failure没有输出结果，
则可以尝试打开数据库，查看报错信息，然后进行相应的处理；
eg：
alter database open;
alter database open resetlogs;
restore datafile 1;
list failure;
advise failure;
repair failure;

指令：
RMAN>list failure;

RMAN>advise failure;

RMAN>repair failure;

具体执行过程：
RMAN> list failure;

List of Database Failures
=========================

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
1702       CRITICAL OPEN      16-OCT-14     Control file needs media recovery
1522       CRITICAL OPEN      16-OCT-14     System datafile 1: '/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b393xosc_.dbf' is missing
709        CRITICAL OPEN      08-OCT-14     System datafile 1: '/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b393xosc_.dbf' needs media recovery
469        CRITICAL OPEN      08-OCT-14     System datafile 1: '/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b2251bs1_.dbf' is missing
242        HIGH     OPEN      29-SEP-14     One or more non-system datafiles are missing
974        HIGH     OPEN      10-OCT-14     Tablespace 11: 'PITR_IND' is offline
644        HIGH     OPEN      08-OCT-14     One or more non-system datafiles need media recovery
817        HIGH     OPEN      09-OCT-14     Name for datafile 7 is unknown in the control file
808        HIGH     OPEN      09-OCT-14     Name for datafile 6 is unknown in the control file

RMAN> advise failure;

List of Database Failures
=========================

analyzing automatic repair options; this may take some time
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
analyzing automatic repair options complete

Not all specified failures can currently be repaired.
The following failures must be repaired before advise for others can be given.

Failure ID Priority Status    Time Detected Summary
---------- -------- --------- ------------- -------
1702       CRITICAL OPEN      16-OCT-14     Control file needs media recovery
1522       CRITICAL OPEN      16-OCT-14     System datafile 1: '/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b393xosc_.dbf' is missing
709        CRITICAL OPEN      08-OCT-14     System datafile 1: '/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b393xosc_.dbf' needs media recovery
469        CRITICAL OPEN      08-OCT-14     System datafile 1: '/u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b2251bs1_.dbf' is missing
242        HIGH     OPEN      29-SEP-14     One or more non-system datafiles are missing
644        HIGH     OPEN      08-OCT-14     One or more non-system datafiles need media recovery
817        HIGH     OPEN      09-OCT-14     Name for datafile 7 is unknown in the control file
808        HIGH     OPEN      09-OCT-14     Name for datafile 6 is unknown in the control file

Mandatory Manual Actions
========================
no manual actions available

Optional Manual Actions
=======================
1. If you have the correct version of the control file, then shutdown the database and replace the old control file
2. If file /u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b393xosc_.dbf was unintentionally renamed or moved, restore it
3. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b393xosc_.dbf, then replace it with the correct one
4. If file /u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b2251bs1_.dbf was unintentionally renamed or moved, restore it
5. If file /u01/app/oracle/oradata/PROD/ind_tbs.dbs was unintentionally renamed or moved, restore it
6. If file /u01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_b2251bvo_.dbf was unintentionally renamed or moved, restore it
7. If file /u01/app/oracle/oradata/PROD/datafile/o1_mf_undotbs1_b2251bw5_.dbf was unintentionally renamed or moved, restore it
8. If file /u01/app/oracle/oradata/PROD/datafile/o1_mf_users_b2251byw_.dbf was unintentionally renamed or moved, restore it
9. If file /u01/app/oracle/oradata/PROD/datafile/o1_mf_example_b2257d0c_.dbf was unintentionally renamed or moved, restore it
10. If file /u01/app/oracle/oradata/PROD/datafile/tbs_move_01.dbf was unintentionally renamed or moved, restore it
11. If file /u01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_b393xovt_.dbf was unintentionally renamed or moved, restore it
12. If file /u01/app/oracle/oradata/PROD/datafile/o1_mf_undotbs1_b393xq2d_.dbf was unintentionally renamed or moved, restore it
13. If file /u01/app/oracle/oradata/PROD/datafile/o1_mf_users_b393xqpm_.dbf was unintentionally renamed or moved, restore it
14. If file /u01/app/oracle/oradata/PROD/datafile/o1_mf_example_b393xp04_.dbf was unintentionally renamed or moved, restore it
15. If file /u01/app/oracle/oradata/PROD/datafile/tbs_users1.dbf was unintentionally renamed or moved, restore it
16. If file /u01/app/oracle/oradata/PROD/datafile/tbs_users2.dbf was unintentionally renamed or moved, restore it
17. If file /u01/app/oracle/oradata/PROD/datafile/pitr.dbf was unintentionally renamed or moved, restore it
18. If file /u01/app/oracle/oradata/PROD/datafile/pitr_ind.dbf was unintentionally renamed or moved, restore it
19. If file /u01/app/oracle/oradata/PROD/arch_tbs.dbf was unintentionally renamed or moved, restore it
20. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD/datafile/o1_mf_users_b393xqpm_.dbf, then replace it with the correct one
21. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD/datafile/tbs_move_01.dbf, then replace it with the correct one
22. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_b393xovt_.dbf, then replace it with the correct one
23. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD/datafile/o1_mf_undotbs1_b393xq2d_.dbf, then replace it with the correct one
24. If you restored the wrong version of data file /u01/app/oracle/oradata/PROD/datafile/pitr_ind.dbf, then replace it with the correct one
25. If the file exists, rename data file 7 to the name of the real file using ALTER DATABASE RENAME FILE command. If the file does not exist, create a new data file using ALTER DATABASE CREATE DATAFILE command.
26. If the file exists, rename data file 6 to the name of the real file using ALTER DATABASE RENAME FILE command. If the file does not exist, create a new data file using ALTER DATABASE CREATE DATAFILE command.

Automated Repair Options
========================
Option Repair Description
------ ------------------
1 Restore and recover database
Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_2998735934.hm

RMAN> repair failure;

Strategy: The repair includes complete media recovery with no data loss
Repair script: /u01/app/oracle/diag/rdbms/prod/PROD/hm/reco_2998735934.hm

contents of repair script:
   # restore and recover database
   restore database;
   recover database;
   alter database open resetlogs;

Do you really want to execute the above repair (enter YES or NO)? yes
executing repair script

Starting restore at 16-OCT-14
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4

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 00003 to /u01/app/oracle/oradata/PROD/datafile/o1_mf_undotbs1_b393xq2d_.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/app/oracle/oradata/PROD/datafile/o1_mf_users_b393xqpm_.dbf
channel ORA_DISK_1: restoring datafile 00010 to /u01/app/oracle/oradata/PROD/arch_tbs.dbf
channel ORA_DISK_1: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/backupset/2014_10_16/o1_mf_nnndf_TAG20141016T105733_b3yf194z_.bkp
channel ORA_DISK_2: starting datafile backup set restore
channel ORA_DISK_2: specifying datafile(s) to restore from backup set
channel ORA_DISK_2: restoring datafile 00005 to /u01/app/oracle/oradata/PROD/datafile/o1_mf_example_b393xp04_.dbf
channel ORA_DISK_2: restoring datafile 00008 to /u01/app/oracle/oradata/PROD/datafile/pitr.dbf
channel ORA_DISK_2: restoring datafile 00009 to /u01/app/oracle/oradata/PROD/datafile/pitr_ind.dbf
channel ORA_DISK_2: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/backupset/2014_10_16/o1_mf_nnndf_TAG20141016T105733_b3yf12ln_.bkp
channel ORA_DISK_3: starting datafile backup set restore
channel ORA_DISK_3: specifying datafile(s) to restore from backup set
channel ORA_DISK_3: restoring datafile 00002 to /u01/app/oracle/oradata/PROD/datafile/o1_mf_sysaux_b393xovt_.dbf
channel ORA_DISK_3: restoring datafile 00006 to /u01/app/oracle/oradata/PROD/datafile/tbs_users1.dbf
channel ORA_DISK_3: restoring datafile 00007 to /u01/app/oracle/oradata/PROD/datafile/tbs_users2.dbf
channel ORA_DISK_3: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/backupset/2014_10_16/o1_mf_nnndf_TAG20141016T105733_b3yf10lb_.bkp
channel ORA_DISK_4: starting datafile backup set restore
channel ORA_DISK_4: specifying datafile(s) to restore from backup set
channel ORA_DISK_4: restoring datafile 00001 to /u01/app/oracle/oradata/PROD/datafile/o1_mf_system_b393xosc_.dbf
channel ORA_DISK_4: reading from backup piece /u01/app/oracle/fast_recovery_area/PROD/backupset/2014_10_16/o1_mf_nnndf_TAG20141016T105733_b3yf0zo6_.bkp
channel ORA_DISK_1: piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2014_10_16/o1_mf_nnndf_TAG20141016T105733_b3yf194z_.bkp tag=TAG20141016T105733
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:04:30
channel ORA_DISK_2: piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2014_10_16/o1_mf_nnndf_TAG20141016T105733_b3yf12ln_.bkp tag=TAG20141016T105733
channel ORA_DISK_2: restored backup piece 1
channel ORA_DISK_2: restore complete, elapsed time: 00:05:01
channel ORA_DISK_3: piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2014_10_16/o1_mf_nnndf_TAG20141016T105733_b3yf10lb_.bkp tag=TAG20141016T105733
channel ORA_DISK_3: restored backup piece 1
channel ORA_DISK_3: restore complete, elapsed time: 00:11:10
channel ORA_DISK_4: piece handle=/u01/app/oracle/fast_recovery_area/PROD/backupset/2014_10_16/o1_mf_nnndf_TAG20141016T105733_b3yf0zo6_.bkp tag=TAG20141016T105733
channel ORA_DISK_4: restored backup piece 1
channel ORA_DISK_4: restore complete, elapsed time: 00:16:35
Finished restore at 16-OCT-14

Starting recover at 16-OCT-14
using channel ORA_DISK_1
using channel ORA_DISK_2
using channel ORA_DISK_3
using channel ORA_DISK_4
datafile 6 not processed because file is read-only
datafile 7 not processed because file is read-only

starting media recovery

archived log for thread 1 with sequence 13 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_1_b395df1j_.log
archived log for thread 1 with sequence 14 is already on disk as file /u01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_2_b395dqql_.log
archived log file name=/u01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_1_b395df1j_.log thread=1 sequence=13
archived log file name=/u01/app/oracle/fast_recovery_area/PROD/onlinelog/o1_mf_2_b395dqql_.log thread=1 sequence=14
media recovery complete, elapsed time: 00:00:30
Finished recover at 16-OCT-14

database opened
new incarnation of database registered in recovery catalog
starting full resync of recovery catalog
full resync complete
repair failure complete

RMAN>

5.查看恢复结果
1）登陆数据库，查看当前数据库状态，查看数据文件等重要文件的状态；

[oracle@jibo ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Thu Oct 16 14:59:53 2014

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

SYS@PROD>select status from v$instance;

STATUS
------------
OPEN

--数据库已经打开
或者：
SYS@PROD>select open_mode from v$database;

2)重新生成spfile；
数据库打开，查看spfile，没有spfile文件，生成spfile文件，再查看:

SYS@PROD>show parameter spfile;

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
spfile string

SYS@PROD>create spfile from memory;

File created.

SYS@PROD>shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SYS@PROD>startup;
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.

Total System Global Area 1068937216 bytes
Fixed Size                  2260088 bytes
Variable Size             285213576 bytes
Database Buffers          775946240 bytes
Redo Buffers                5517312 bytes
Database mounted.
Database opened.
SYS@PROD>show parameter spfile;

6.重新备份数据库

RMAN> backup database include current controlfile plus archivelog;