RAC 数据文件建在本地处理办法-阿里云开发者社区

开发者社区> 数据库> 正文

RAC 数据文件建在本地处理办法

简介: rac 数据文件建立在本地处理方法。

本次实验所有操作是在实在数据库打开归档模式下完成的。
检查数据库是否打开归档。

SQL> select LOG_MODE from v$database;

LOG_MODE
------------
ARCHIVELOG

1,SYSTEM添加数据文件添加在本地,处理办法。

故障模拟:
SQL> alter tablespace SYSTEM add datafile '/soft/dbf/SYSTEM02.dbf' size 100m;

Tablespace altered.

处理方法:
1,关闭所节点数据库
2,启动数据库到mount状态
3,通过rman copy 处理故障文件
RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    770      SYSTEM               ***     +DATA/orcl/datafile/system.298.984761123
2    490      SYSAUX               ***     +DATA/orcl/datafile/sysaux.299.984761123
3    95       UNDOTBS1             ***     +DATA/orcl/datafile/undotbs1.304.984761123
4    5        USERS                ***     +DATA/orcl/datafile/users.263.984761123
5    313      EXAMPLE              ***     +DATA/orcl/datafile/example.300.984761123
6    25       UNDOTBS2             ***     +DATA/orcl/datafile/undotbs2.273.984761123
7    100      SYSTEM               ***     /soft/dbf/SYSTEM02.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/orcl/tempfile/temp.336.984761229

RMAN> backup as copy datafile 7 format '+data';

Starting backup at 21-AUG-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=36 instance=orcl1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00007 name=/soft/dbf/SYSTEM02.dbf
output file name=+DATA/orcl/datafile/system.266.984761643 tag=TAG20180821T165403 RECID=2 STAMP=984761644
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 21-AUG-18

RMAN> switch datafile 7 to copy;

datafile 7 switched to datafile copy "+DATA/orcl/datafile/system.266.984761643"

RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    770      SYSTEM               ***     +DATA/orcl/datafile/system.298.984761123
2    490      SYSAUX               ***     +DATA/orcl/datafile/sysaux.299.984761123
3    95       UNDOTBS1             ***     +DATA/orcl/datafile/undotbs1.304.984761123
4    5        USERS                ***     +DATA/orcl/datafile/users.263.984761123
5    313      EXAMPLE              ***     +DATA/orcl/datafile/example.300.984761123
6    25       UNDOTBS2             ***     +DATA/orcl/datafile/undotbs2.273.984761123
7    100      SYSTEM               ***     +DATA/orcl/datafile/system.266.984761643

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/orcl/tempfile/temp.336.984761229

RMAN> 

2,SYSAUX添加数据文件添加在本地,处理办法。

模拟故障:

SQL> alter tablespace SYSAUX add datafile '/soft/dbf/SYSAUX02.dbf' size 50m;

Tablespace altered.

故障处理:
SQL> select file_id,file_name from dba_data_files;

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------------------------------------
     4 +DATA/orcl/datafile/users.263.984761123
     3 +DATA/orcl/datafile/undotbs1.304.984761123
     2 +DATA/orcl/datafile/sysaux.299.984761123
     1 +DATA/orcl/datafile/system.298.984761123
     5 +DATA/orcl/datafile/example.300.984761123
     6 +DATA/orcl/datafile/undotbs2.273.984761123
     7 +DATA/orcl/datafile/system.266.984761643
     8 /soft/dbf/SYSAUX02.dbf

8 rows selected.

SQL> alter database datafile 8 offline;
Database altered.
RMAN> report schema;

Report of database schema for database with db_unique_name ORCL

List of Permanent Datafiles
===========================
File Size(MB) Tablespace           RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1    770      SYSTEM               ***     +DATA/orcl/datafile/system.298.984761123
2    490      SYSAUX               ***     +DATA/orcl/datafile/sysaux.299.984761123
3    95       UNDOTBS1             ***     +DATA/orcl/datafile/undotbs1.304.984761123
4    5        USERS                ***     +DATA/orcl/datafile/users.263.984761123
5    313      EXAMPLE              ***     +DATA/orcl/datafile/example.300.984761123
6    25       UNDOTBS2             ***     +DATA/orcl/datafile/undotbs2.273.984761123
7    100      SYSTEM               ***     +DATA/orcl/datafile/system.266.984761643
8    50       SYSAUX               ***     /soft/dbf/SYSAUX02.dbf

List of Temporary Files
=======================
File Size(MB) Tablespace           Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1    20       TEMP                 32767       +DATA/orcl/tempfile/temp.336.984761229

RMAN> backup as copy datafile 8 format '+data';

Starting backup at 21-AUG-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=29 instance=orcl1 device type=DISK
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=/soft/dbf/SYSAUX02.dbf
output file name=+DATA/orcl/datafile/sysaux.262.984761997 tag=TAG20180821T165957 RECID=4 STAMP=984761997
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 21-AUG-18
RMAN> switch datafile 8 to copy;

datafile 8 switched to datafile copy "+DATA/orcl/datafile/sysaux.262.984761997"

RMAN> 
###sqlplus 
SQL> recover datafile 8;
Media recovery complete.
SQL> alter database datafile 8 online;

Database altered.

SQL> 

3,新创建表空间建立在本地

模拟故障:
SQL> create tablespace TEST DATAFILE '/soft/dbf/test01.dbf' size 50m;

Tablespace created.

故障处理:

SQL> select file_id,file_name from dba_data_files;

   FILE_ID FILE_NAME
---------- ----------------------------------------------------------------------------------------------------
     4 +DATA/orcl/datafile/users.263.984761123
     3 +DATA/orcl/datafile/undotbs1.304.984761123
     2 +DATA/orcl/datafile/sysaux.299.984761123
     1 +DATA/orcl/datafile/system.298.984761123
     5 +DATA/orcl/datafile/example.300.984761123
     6 +DATA/orcl/datafile/undotbs2.273.984761123
     7 +DATA/orcl/datafile/system.266.984761643
     8 +DATA/orcl/datafile/sysaux.262.984761997
     9 /soft/dbf/test01.dbf

9 rows selected.

SQL> alter datafile 9 offline;
alter datafile 9 offline
      *
ERROR at line 1:
ORA-00940: invalid ALTER command


SQL> alter database datafile 9 offline;

Database altered.

### rman
RMAN> backup as copy datafile 9 format '+data';

Starting backup at 21-AUG-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00009 name=/soft/dbf/test01.dbf
output file name=+DATA/orcl/datafile/test.265.984762523 tag=TAG20180821T170843 RECID=6 STAMP=984762524
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:01
Finished backup at 21-AUG-18

RMAN> switch datafile 9 to copy;

datafile 9 switched to datafile copy "+DATA/orcl/datafile/test.265.984762523"

##sqlplus

SQL> recover datafile 9;
Media recovery complete.
SQL>  alter database datafile 9  online;

Database altered.

12c online move datafile

alter database move datafile 15 to '+DATA/CDB/DATAFILE/user02.dbf';

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------------------------------------
     1 +DATA/CDB/DATAFILE/system.290.985011811
     3 +DATA/CDB/DATAFILE/sysaux.289.985011873
     4 +DATA/CDB/DATAFILE/undotbs1.293.985011907
     5 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/system.300.985012289
     6 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/sysaux.299.985012289
     7 +DATA/CDB/DATAFILE/users.294.985011909
     8 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/undotbs1.301.985012289
     9 +DATA/CDB/DATAFILE/undotbs2.303.985012575
    10 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/system.308.985012979
    11 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/sysaux.309.985012979
    12 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/undotbs1.307.985012979
    13 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/undo_2.311.985013015
    14 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/users.312.985013017
    15 /soft/user02.dbf


SQL> alter database move datafile 15 to '+DATA/CDB/DATAFILE/user02.dbf';

Database altered.

SQL> select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------------------------------------
     1 +DATA/CDB/DATAFILE/system.290.985011811
     3 +DATA/CDB/DATAFILE/sysaux.289.985011873
     4 +DATA/CDB/DATAFILE/undotbs1.293.985011907
     5 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/system.300.985012289
     6 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/sysaux.299.985012289
     7 +DATA/CDB/DATAFILE/users.294.985011909
     8 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/undotbs1.301.985012289
     9 +DATA/CDB/DATAFILE/undotbs2.303.985012575
    10 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/system.308.985012979
    11 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/sysaux.309.985012979
    12 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/undotbs1.307.985012979
    13 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/undo_2.311.985013015
    14 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/users.312.985013017
    15 +DATA/CDB/DATAFILE/user02.dbf

14 rows selected.

SQL>

SYSTEM ONLINE MOVE:

alter database move datafile 16 to '+data/cdb/datafile/system02.dbf';

SQL>  select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------------------------------------
     1 +DATA/CDB/DATAFILE/system.290.985011811
     3 +DATA/CDB/DATAFILE/sysaux.289.985011873
     4 +DATA/CDB/DATAFILE/undotbs1.293.985011907
     5 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/system.300.985012289
     6 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/sysaux.299.985012289
     7 +DATA/CDB/DATAFILE/users.294.985011909
     8 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/undotbs1.301.985012289
     9 +DATA/CDB/DATAFILE/undotbs2.303.985012575
    10 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/system.308.985012979
    11 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/sysaux.309.985012979
    12 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/undotbs1.307.985012979
    13 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/undo_2.311.985013015
    14 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/users.312.985013017
    15 +DATA/CDB/DATAFILE/user02.dbf
    16 /soft/system02.dbf

15 rows selected.

SQL> alter  database move datafile 16 to '+data/cdb/datafile/system02.dbf';

Database altered.

SQL>  select file#,name from v$datafile;

     FILE# NAME
---------- --------------------------------------------------------------------------------
     1 +DATA/CDB/DATAFILE/system.290.985011811
     3 +DATA/CDB/DATAFILE/sysaux.289.985011873
     4 +DATA/CDB/DATAFILE/undotbs1.293.985011907
     5 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/system.300.985012289
     6 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/sysaux.299.985012289
     7 +DATA/CDB/DATAFILE/users.294.985011909
     8 +DATA/CDB/64A52F53A7693286E053CDA9E80AED76/DATAFILE/undotbs1.301.985012289
     9 +DATA/CDB/DATAFILE/undotbs2.303.985012575
    10 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/system.308.985012979
    11 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/sysaux.309.985012979
    12 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/undotbs1.307.985012979
    13 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/undo_2.311.985013015
    14 +DATA/CDB/7429AF9E7B2A4EF9E053CB00A8C08F1D/DATAFILE/users.312.985013017
    15 +DATA/CDB/DATAFILE/user02.dbf
    16 +DATA/cdb/datafile/system02.dbf

15 rows selected.

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

分享:
数据库
使用钉钉扫一扫加入圈子
+ 订阅

分享数据库前沿,解构实战干货,推动数据库技术变革

其他文章