重庆思庄Oracle、Redhat认证学习论坛

标题: 11g rac添加数据文件至本地文件系统的修正演练 [打印本页]

作者: denglj 时间: 2022-11-4 11:48
标题: 11g rac添加数据文件至本地文件系统的修正演练

文档课题：11g rac添加数据文件至本地文件系统的修正演练.

系统：centos7.9 64位

数据库：11.2.0.4 64位

环境：rac(双节点)+ dg

1、模拟场景

节点1执行.

SQL>create tablespace ZL_SIG_DISEASE DATAFILE'D:\app\Administrator\oradata\ZLDRGS\ZL_SIG_DISEASE.DBF' SIZE 50m autoextendon;

Tablespacecreated.

SQL>col file_name for a90

SQL>col tablespace_name for a15

SQL>select file_name,tablespace_name,autoextensible,bytes/1024/1024 fromdba_data_files

FILE_NAME TABLESPACE_NAME AUT BYTES/1024/1024

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

+DATA/orcl/datafile/system.259.1117373983 SYSTEM YES 1350

+DATA/orcl/datafile/sysaux.260.1117373985 SYSAUX YES 600

+DATA/orcl/datafile/undotbs1.261.1117373985 UNDOTBS1 YES 810

+DATA/orcl/datafile/undotbs2.263.1117373991 UNDOTBS2 YES 200

+DATA/orcl/datafile/users.264.1117373991 USERS YES 5

+DATA/orcl/datafile/users.277.1117483957 USERS YES 50

/u01/app/oracle/product/11.2.0/db_1/dbs/D:appAdministratororadataZLDRGSZL_SIG_DISEASE.DBF ZL_SIG_DISEASE YES 50

7 rowsselected.

节点2执行.

SQL>col file_name for a90

SQL>col tablespace_name for a15

SQL>select file_name,tablespace_name,autoextensible,bytes/1024/1024 fromdba_data_files;

ERROR:

ORA-01157:cannot identify/lock data file 7 - see DBWR trace file

ORA-01110:data file 7:

'/u01/app/oracle/product/11.2.0/db_1/dbs/D:appAdministratororadataZLDRGSZL_SIG_D

ISEASE.DBF'

no rowsselected

备库执行.

SQL>set line 200

SQL>select file_name,tablespace_name,autoextensible,bytes/1024/1024 fromdba_data_files

FILE_NAME TABLESPACE_NAME AUT BYTES/1024/1024

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

/u01/app/oracle/oradata/system.259.1117373983 SYSTEM YES 1350

/u01/app/oracle/oradata/sysaux.260.1117373985 SYSAUX YES 600

/u01/app/oracle/oradata/undotbs1.261.1117373985 UNDOTBS1 YES 810

/u01/app/oracle/oradata/undotbs2.263.1117373991 UNDOTBS2 YES 200

/u01/app/oracle/oradata/users.264.1117373991 USERS YES 5

/u01/app/oracle/oradata/users.277.1117483957 USERS YES 50

/u01/app/oracle/product/11.2.0/db_1/dbs/D:appAdministratororadataZLDRGSZL_SIG_DISEASE.DBF ZL_SIG_DISEASE YES 50

7 rowsselected.

SQL>show parameter db_cre

NAME TYPE VALUE

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

db_create_file_dest string

db_create_online_log_dest_1 string

db_create_online_log_dest_2 string

db_create_online_log_dest_3 string

db_create_online_log_dest_4 string

db_create_online_log_dest_5 string

小结：异常成功模拟，可以看到节点1会生成?/dbs/D:appAdministratororadataZLDRGSZL_SIG_DISEASE.DBF文件，节点2查询报错ORA-01110，备库也会生成相应的数据文件.

2、解决方案

2.1、直接drop

有时为误操作导致，并不是真的想添加表空间，此时直接drop该表空间即可.

节点1执行.

SQL>drop tablespace ZL_SIG_DISEASE including contents and datafiles cascadeconstraints;

Tablespacedropped.

节点1&2&备库查询.

SQL>col file_name for a90

SQL>col tablespace_name for a15

SQL>set line 200

SQL>select file_name,tablespace_name,autoextensible,bytes/1024/1024 fromdba_data_files;

FILE_NAME TABLESPACE_NAME AUTBYTES/1024/1024

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

/u01/app/oracle/oradata/system.259.1117373983 SYSTEM YES 1350

/u01/app/oracle/oradata/sysaux.260.1117373985 SYSAUX YES 600

/u01/app/oracle/oradata/undotbs1.261.1117373985 UNDOTBS1 YES 810

/u01/app/oracle/oradata/undotbs2.263.1117373991 UNDOTBS2 YES 200

/u01/app/oracle/oradata/users.264.1117373991 USERS YES 5

/u01/app/oracle/oradata/users.277.1117483957 USERS YES 50

6 rowsselected.

2.2、修改路径

节点1执行.

SQL>create tablespace ZL_SIG_DISEASE DATAFILE 'D:\app\Administrator\oradata\ZLDRGS\ZL_SIG_DISEASE.DBF'SIZE 50m autoextend on;

Tablespacecreated.

SQL>col file_name for a90

SQL>col tablespace_name for a15

SQL>select file_name,tablespace_name,autoextensible,bytes/1024/1024 fromdba_data_files

FILE_NAME TABLESPACE_NAMEAUT BYTES/1024/1024

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

+DATA/orcl/datafile/system.259.1117373983 SYSTEM YES 1350

+DATA/orcl/datafile/sysaux.260.1117373985 SYSAUX YES 600

+DATA/orcl/datafile/undotbs1.261.1117373985 UNDOTBS1 YES 810

+DATA/orcl/datafile/undotbs2.263.1117373991 UNDOTBS2 YES 200

+DATA/orcl/datafile/users.264.1117373991 USERS YES 5

+DATA/orcl/datafile/users.277.1117483957 USERS YES 50

/u01/app/oracle/product/11.2.0/db_1/dbs/D:appAdministratororadataZLDRGSZL_SIG_DISEASE.DBF ZL_SIG_DISEASE YES 50

SQL>alter tablespace ZL_SIG_DISEASE offline;

Tablespacealtered.

RMAN>copy datafile '/u01/app/oracle/product/11.2.0/db_1/dbs/D:appAdministratororadataZLDRGSZL_SIG_DISEASE.DBF'to '+data';

Startingbackup at 11-OCT-22

usingtarget database control file instead of recovery catalog

allocatedchannel: ORA_DISK_1

channelORA_DISK_1: SID=199 instance=orcl1 device type=DISK

channelORA_DISK_1: starting datafile copy

inputdatafile file number=00007name=/u01/app/oracle/product/11.2.0/db_1/dbs/D:appAdministratororadataZLDRGSZL_SIG_DISEASE.DBF

outputfile name=+DATA/orcl/datafile/zl_sig_disease.279.1117806753tag=TAG20221011T135232 RECID=2 STAMP=1117806753

channelORA_DISK_1: datafile copy complete, elapsed time: 00:00:01

Finishedbackup at 11-OCT-22

SQL>alter database rename file'/u01/app/oracle/product/11.2.0/db_1/dbs/D:appAdministratororadataZLDRGSZL_SIG_DISEASE.DBF'to '+DATA/orcl/datafile/zl_sig_disease.279.1117806753';

Databasealtered.

SQL>alter tablespace ZL_SIG_DISEASE online;

Tablespacealtered.

SQL>select name from v$datafile;

NAME

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

+DATA/orcl/datafile/system.259.1117373983

+DATA/orcl/datafile/sysaux.260.1117373985

+DATA/orcl/datafile/undotbs1.261.1117373985

+DATA/orcl/datafile/undotbs2.263.1117373991

+DATA/orcl/datafile/users.264.1117373991

+DATA/orcl/datafile/users.277.1117483957

+DATA/orcl/datafile/zl_sig_disease.279.1117806753

7 rowsselected.

节点2执行.

SQL>select name from v$datafile;

NAME

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

+DATA/orcl/datafile/system.259.1117373983

+DATA/orcl/datafile/sysaux.260.1117373985

+DATA/orcl/datafile/undotbs1.261.1117373985

+DATA/orcl/datafile/undotbs2.263.1117373991

+DATA/orcl/datafile/users.264.1117373991

+DATA/orcl/datafile/users.277.1117483957

+DATA/orcl/datafile/zl_sig_disease.279.1117806753

7 rowsselected.

结论：可以看到此数据文件在节点2能正常显示.

欢迎光临重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/)