ITPub博客

首页 > Linux操作系统 > Linux操作系统 > ASM管理环境----数据文件丢失介质恢复(MEDIA RECOVERY)

ASM管理环境----数据文件丢失介质恢复(MEDIA RECOVERY)

原创 Linux操作系统 作者:wailon 时间:2013-11-18 20:27:56 0 删除 编辑

与文件系统的介质恢复最大的区别在于,虽然指定相同路径相同文件名,但由于OMF管理的原因,
ASM不可能建立原来一样的文件名,那只是ASM Alias别名。

[root@vmrac1 ~]# su - oracle
[oracle@vmrac1 ~]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 28 20:08:44 2013
Copyright (c) 1982, 2011, Oracle.  All rights reserved.

Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

-- 查看后台管理进程
SQL> select name,description from v$bgprocess where paddr<>'00';

NAME  DESCRIPTION
----- ----------------------------------------------------------------
PMON  process cleanup
VKTM  Virtual Keeper of TiMe process
GEN0  generic0
DIAG  diagnosibility process
DBRM  DataBase Resource Manager
RSMN  Remote Slave Monitor
PING  interconnect latency measurement
PSP0  process spawner 0
ACMS  Atomic Controlfile to Memory Server
DIA0  diagnosibility process 0
LMON  global enqueue service monitor

NAME  DESCRIPTION
----- ----------------------------------------------------------------
LMD0  global enqueue service daemon 0
LMS0  global cache service process 0
RMS0  rac management server
LMHB  lm heartbeat monitor
MMAN  Memory Manager
DBW0  db writer process 0
ARC0  Archival Process 0
ARC1  Archival Process 1
ARC2  Archival Process 2
ARC3  Archival Process 3
LGWR  Redo etc.

NAME  DESCRIPTION
----- ----------------------------------------------------------------
LCK0  Lock Process 0
CKPT  checkpoint
CTWR  Change Tracking Writer
SMON  System Monitor Process
SMCO  Space Manager Process
RECO  distributed recovery
GTX0  Global Txn process 0
CJQ0  Job Queue Coordinator
RCBG  Result Cache: Background
QMNC  AQ Coordinator
RBAL  ASM Rebalance master

NAME  DESCRIPTION
----- ----------------------------------------------------------------
ASMB  ASM Background
MARK  mark AU for resync koordinator
MMON  Manageability Monitor Process
MMNL  Manageability Monitor Process 2

37 rows selected.

-- 查看数据库目前所用的数据文件
SQL> select name from v$datafile;

NAME
---------------------------------------------------------------------
+DATA/rac/datafile/system.256.829110499
+DATA/rac/datafile/sysaux.257.829110501
+DATA/rac/datafile/undotbs1.258.829110501
+DATA/rac/datafile/users.259.829110501
+DATA/rac/datafile/example.267.829110655
+DATA/rac/datafile/undotbs2.268.829111091

6 rows selected.

SQL> host
[oracle@vmrac1 ~]$ rman target /

Recovery Manager: Release 11.2.0.3.0 - Production on Mon Oct 28 20:20:06 2013

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

connected to target database: RAC (DBID=2417092639)

-- 查看当前备份文件
RMAN> list backup;

using target database control file instead of recovery catalog

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

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
4       Incr 3  144.00K    DISK        00:00:02     28-OCT-13     
        BP Key: 4   Status: AVAILABLE  Compressed: NO  Tag: TAG20131028T175756
        Piece Name: +DATA/rac/backupset/2013_10_28/nnndn3_tag20131028t175756_0.277.830023077
  List of Datafiles in backup set 4
  File LV Type Ckp SCN    Ckp Time  Name
  ---- -- ---- ---------- --------- ----
  1    3  Incr 1332188    28-OCT-13 +DATA/rac/datafile/system.256.829110499
  2    3  Incr 1332188    28-OCT-13 +DATA/rac/datafile/sysaux.257.829110501
  3    3  Incr 1332188    28-OCT-13 +DATA/rac/datafile/undotbs1.258.829110501
  4    3  Incr 1332188    28-OCT-13 +DATA/rac/datafile/users.259.829110501
  5    3  Incr 1332188    28-OCT-13 +DATA/rac/datafile/example.267.829110655
  6    3  Incr 1332188    28-OCT-13 +DATA/rac/datafile/undotbs2.268.829111091

BS Key  Type LV Size       Device Type Elapsed Time Completion Time
------- ---- -- ---------- ----------- ------------ ---------------
5       Incr 3  17.70M     DISK        00:00:01     28-OCT-13     
        BP Key: 5   Status: AVAILABLE  Compressed: NO  Tag: TAG20131028T175756
        Piece Name: +DATA/rac/backupset/2013_10_28/ncsnn3_tag20131028t175756_0.278.830023081
  SPFILE Included: Modification time: 28-OCT-13
  SPFILE db_unique_name: RAC
  Control File Included: Ckp SCN: 1332189      Ckp time: 28-OCT-13

[oracle@vmrac1 ~]$ exit
exit

-- 新建表空间WAILON
SQL> create tablespace wailon datafile size 10m;

Tablespace created.

-- 增加表空间USERS的数据文件
SQL> alter tablespace users add datafile size 1m;

Tablespace altered.

SQL> select name from v$datafile;

NAME
--------------------------------------------------------------------------------
+DATA/rac/datafile/system.256.829110499
+DATA/rac/datafile/sysaux.257.829110501
+DATA/rac/datafile/undotbs1.258.829110501
+DATA/rac/datafile/users.259.829110501
+DATA/rac/datafile/example.267.829110655
+DATA/rac/datafile/undotbs2.268.829111091
+DATA/rac/datafile/wailon.282.830031681
+DATA/rac/datafile/users.283.830031699

8 rows selected.

-- 在新建的表空间WAILON上创建表
SQL> create user wailon identified by wailon default tablespace wailon;

User created.

SQL> grant connect,resource to wailon;

Grant succeeded.

SQL> alter user wailon quota 10m on wailon;

User altered.

SQL> conn wailon/wailon
Connected.

SQL> create table test as select * from user_tables;

Table created.

SQL> create table test2 as select * from test;

Table created.

SQL> select tablespace_name,table_name from user_tables;

TABLESPACE_NAME                TABLE_NAME
------------------------------ ------------------------------
WAILON                         TEST
WAILON                         TEST2

-- 模拟数据文件丢失,出现故障
[oracle@vmrac1 ~]$ srvctl stop database -d rac
[oracle@vmrac1 ~]$ su - grid
Password:
[grid@vmrac1 ~]$ rlwrap asmcmd -p
ASMCMD [+] > ls
DATA/
ASMCMD [+] > cd data
ASMCMD [+data] > ls
RAC/
vmrac/
ASMCMD [+data] > cd rac
ASMCMD [+data/rac] > ls
ARCHIVELOG/
BACKUPSET/
CHANGETRACKING/
CONTROLFILE/
DATAFILE/
ONLINELOG/
PARAMETERFILE/
TEMPFILE/
spfilerac.ora
ASMCMD [+data/rac] > cd datafile
ASMCMD [+data/rac/datafile] > ls
EXAMPLE.267.829110655
SYSAUX.257.829110501
SYSTEM.256.829110499
UNDOTBS1.258.829110501
UNDOTBS2.268.829111091
USERS.259.829110501
USERS.283.830031699
WAILON.282.830031681
ASMCMD [+data/rac/datafile] > rm USERS.283.830031699
ASMCMD [+data/rac/datafile] > rm WAILON.282.830031681
ASMCMD [+data/rac/datafile] > exit

[oracle@vmrac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Mon Oct 28 20:31:09 2013

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

Connected to an idle instance.

-- 启动数据库出错,无法找到数据文件
SQL> startup
ORACLE instance started.

Total System Global Area  463478784 bytes
Fixed Size                  2229384 bytes
Variable Size             213912440 bytes
Database Buffers          239075328 bytes
Redo Buffers                8261632 bytes
Database mounted.
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '+DATA/rac/datafile/wailon.282.830031681'


SQL> recover database;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 7: '+DATA/rac/datafile/wailon.282.830031681'
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '+DATA/rac/datafile/wailon.282.830031681'

-- 无法恢复,创建对应的数据文件
SQL> alter database create datafile 7;

Database altered.

SQL> recover datafile 7;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 7: '+DATA/rac/datafile/wailon.282.830031681'
ORA-01157: cannot identify/lock data file 7 - see DBWR trace file
ORA-01110: data file 7: '+DATA/rac/datafile/wailon.282.830031681'

SQL> set line 120
SQL> col name for a60
SQL> select file#,name from v$datafile;

     FILE# NAME
---------- ------------------------------------------------------------
         1 +DATA/rac/datafile/system.256.829110499
         2 +DATA/rac/datafile/sysaux.257.829110501
         3 +DATA/rac/datafile/undotbs1.258.829110501
         4 +DATA/rac/datafile/users.259.829110501
         5 +DATA/rac/datafile/example.267.829110655
         6 +DATA/rac/datafile/undotbs2.268.829111091
         7 +DATA/rac/datafile/wailon.282.830031681
         8 +DATA/rac/datafile/users.283.830031699

8 rows selected.

SQL> alter database create datafile '+DATA/rac/datafile/users.283.830031699';

Database altered.

SQL> recover datafile 8;
ORA-00283: recovery session canceled due to errors
ORA-01110: data file 8: '+DATA/rac/datafile/users.283.830031699'
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01110: data file 8: '+DATA/rac/datafile/users.283.830031699'

-- 仍然无法恢复

SQL> host
[oracle@vmrac1 ~]$ su - grid
Password:

-- 创建对应的数据文件,留意文件名称不一样
[grid@vmrac1 ~]$ asmcmd -p
ASMCMD [+] > cd data/rac/datafile
ASMCMD [+data/rac/datafile] > ls
EXAMPLE.267.829110655
SYSAUX.257.829110501
SYSTEM.256.829110499
UNDOTBS1.258.829110501
UNDOTBS2.268.829111091
USERS.259.829110501
USERS.283.830032525
WAILON.282.830032351
ASMCMD [+data/rac/datafile] > exit
[grid@vmrac1 ~]$ exit
logout
[oracle@vmrac1 ~]$ sqlplus / as sysdba

-- 修改控制文件中的指针
SQL> alter database rename file '+DATA/rac/datafile/users.283.830031699' to '+DATA/rac/datafile/USERS.283.830032525';

Database altered.

SQL> alter database rename file '+DATA/rac/datafile/wailon.282.830031681' to '+DATA/rac/datafile/WAILON.282.830032351';

Database altered.

-- 再次执行恢复
SQL> recover datafile 7;
Media recovery complete.
SQL> recover datafile 8;
Media recovery complete.

-- 成功恢复后打开数据库
SQL> alter database open;

Database altered.

-- 检查数据文件,验证数据是否恢复
SQL> set line 120
SQL> col file_name for a60
SQL> select file_name,tablespace_name,bytes/1048576 from dba_data_files;

FILE_NAME                                                    TABLESPACE_NAME                BYTES/1048576
------------------------------------------------------------ ------------------------------ -------------
+DATA/rac/datafile/wailon.282.830032351                      WAILON                                    10
+DATA/rac/datafile/users.259.829110501                       USERS                                      5
+DATA/rac/datafile/undotbs1.258.829110501                    UNDOTBS1                                 100
+DATA/rac/datafile/sysaux.257.829110501                      SYSAUX                                   570
+DATA/rac/datafile/system.256.829110499                      SYSTEM                                   720
+DATA/rac/datafile/example.267.829110655                     EXAMPLE                              313.125
+DATA/rac/datafile/undotbs2.268.829111091                    UNDOTBS2                                  25
+DATA/rac/datafile/users.283.830032525                       USERS                                      1

8 rows selected.

SQL> conn wailon/wailon
Connected.
SQL> select tablespace_name,table_name from user_tables;

TABLESPACE_NAME                TABLE_NAME
------------------------------ ------------------------------
WAILON                         TEST
WAILON                         TEST2

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/429786/viewspace-776892/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-11-08

  • 博文量
    51
  • 访问量
    290584