AI 助理
备案控制台
开发者社区 数据库 文章 正文

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

2018-08-21 1278
版权
版权声明：
本文内容由阿里云实名注册用户自发贡献，版权归原作者所有，阿里云开发者社区不拥有其著作权，亦不承担相应法律责任。具体规则请查看《 阿里云开发者社区用户服务协议》和 《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容，填写 侵权投诉表单进行举报，一经查实，本社区将立刻删除涉嫌侵权内容。
简介： 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.
文章标签：
数据库
运维
关键词：
RAC数据
RAC文件
RAC数据文件
snowofsummer
目录
相关文章
姚远OracleACE
|
Oracle 关系型数据库 数据库
RAC中，控制文件的快照文件必须能够被所有节点的数据实例访问到 ORA-00245
RMAN在使用控制文件备份的时候，备份开始点需要最新的检查点信息以及文件头信息。
姚远OracleACE
119 0
jan1990
rac库数据文件创建在os本地的解决办法
按照影响程度大小，方案A需要down库，方案B需要offline tablespace，方案C只需offline datafile即可。综合来看，下面的Testcase_C_2的方法来的最简单。影响也最小。
jan1990
1246 0
snowofsummer
|
数据库
RAC 数据库数据库恢复数据文件显示为0
RAC数据库异机恢复数据文件显示为0
snowofsummer
1510 0
余二五
|
Oracle 关系型数据库 数据库
11g rac 添加一个控制文件
余二五
945 0
余二五
|
Oracle 关系型数据库 安全
一个RAC的ASM参数文件和库参数文件
余二五
1301 0
科技小能手
|
关系型数据库 数据库
AIX+RAC数据服务器开关机流程
科技小能手
1255 0
技术小美
|
Oracle 关系型数据库 数据库
oracle之 RAC本地数据文件迁移至ASM
技术小美
1309 0
技术小胖子
|
SQL Oracle 关系型数据库
11G Oracle RAC添加新表空间时数据文件误放置到本地文件系统的修正
技术小胖子
846 0
技术小胖子
|
SQL Oracle 关系型数据库
11G Oracle RAC添加新表空间时数据文件误放置到本地文件系统的修正
技术小胖子
1048 0
技术小胖子
|
NoSQL Oracle 关系型数据库
ORACLE RAC－－在RAC节点上测试产生并调试core文件
技术小胖子
1102 0