|
文档课题：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认证学习论坛
( 渝ICP备12004239号-4 )
发表于 2022-11-4 11:48:42
QQ好友和群
QQ空间
腾讯微博
腾讯朋友
收藏
支持
反对