ORACLE数据文件名导致的奇怪问题 (51天)

jeanron100

发布于 2018-03-13 18:37:54

9270

发布于 2018-03-13 18:37:54

文章被收录于专栏：杨建荣的学习笔记

今天创建了一些表空间，准备做data guard来看看效果。

为了方便起见，我用gridcontrol来做，主库也开了Omf,省去了好多步骤。

一路点下来，就等gc的那个状态变成对号了，结果装了近20分钟，alert日志开始报错。

******************** WARNING *************************** The errors during Server autobackup are not fatal, as it is attempted after sucessful completion of the command. However, it is recomended to take an RMAN control file backup as soon as possible because the Autobackup failed with the following error: ORA-19583: conversation terminated due to error ORA-19914: unable to encrypt backup ORA-28365: wallet is not open 我一看，主库的钱包是没开，然后开了钱包

alter system set encryption wallet open identified by oracle123;

然后行这次应该没问题了吧，第二遍

简单清理了一下，继续

但是过了一会，还是同样的错误，还是大红叉。


Database mounted.
SQL> Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
With the Partitioning, Oracle Label Security, OLAP and Data Mining options
dgcreate.DGrenameFiles: ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/disk_4/system01.dbf' to '/u01/app/db/oradata/STDBY/datafile/o1_mf_system_88z23chp_.dbf'
dgcreate.DGrenameFiles: SELECT STATUS FROM V$DATAFILE WHERE NAME = '/u01/app/oracle/oradata/disk_4/system01.dbf'
dgcreate.DGrenameFiles: ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/disk_4/sysaux01.dbf' to '/u01/app/db/oradata/STDBY/datafile/o1_mf_sysaux_88z24s8m_.dbf'
dgcreate.DGrenameFiles: SELECT STATUS FROM V$DATAFILE WHERE NAME = '/u01/app/oracle/oradata/disk_4/sysaux01.dbf'
dgcreate.DGrenameFiles: ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/disk_1/tbs01.dbf' to '/u01/app/db/oradata/STDBY/datafile/o1_mf_tbs1_88z25q3r_.dbf'
dgcreate.DGrenameFiles: SELECT STATUS FROM V$DATAFILE WHERE NAME = '/u01/app/oracle/oradata/disk_1/tbs01.dbf'
dgcreate.DGrenameFiles: ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/disk_2/tbs02.dbf' to '/u01/app/db/oradata/STDBY/datafile/o1_mf_tbs1_88z265gd_.dbf'
dgcreate.DGrenameFiles: SELECT STATUS FROM V$DATAFILE WHERE NAME = '/u01/app/oracle/oradata/disk_2/tbs02.dbf'
dgcreate.DGrenameFiles: ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/disk_3/tbs03.dbf' to '/u01/app/db/oradata/STDBY/datafile/o1_mf_tbs1_88z26o67_.dbf'
dgcreate.DGrenameFiles: SELECT STATUS FROM V$DATAFILE WHERE NAME = '/u01/app/oracle/oradata/disk_3/tbs03.dbf'
dgcreate.DGrenameFiles: ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/disk_3/tbs3.dbf' to '/u01/app/db/oradata/STDBY/datafile/o1_mf_tbs3_88z274k8_.dbf'
dgcreate.DGrenameFiles: SELECT STATUS FROM V$DATAFILE WHERE NAME = '/u01/app/oracle/oradata/disk_3/tbs3.dbf'
dgcreate.DGrenameFiles: ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/disk_4/undotbs2.dbf' to '/u01/app/db/oradata/STDBY/datafile/o1_mf_undotbs2_88z27mbc_.dbf'
dgcreate.DGrenameFiles: SELECT STATUS FROM V$DATAFILE WHERE NAME = '/u01/app/oracle/oradata/disk_4/undotbs2.dbf'
dgcreate.DGrenameFiles: ALTER DATABASE RENAME FILE '/u01/app/oracle/oradata/disk_5/indx.dbf' to '/u01/app/db/oradata/STDBY/datafile/o1_mf_indx_88z282wc_.dbf'
SQL Error: ORA-01511: error in renaming log/data files
ORA-01516: nonexistent log file, datafile, or tempfile "/u01/app/oracle/oradata/disk_5/indx.dbf" (DBD ERROR: OCIStmtExecute)

然后我看到后台再反复重启有关共享服务器的进程，我想是不是也有一定的影响，

我先没关共享。看看这个数据文件先，

来到相应的目录

[oracle@oel1 disk_5]$ cd indx.dbf
bash: cd: indx.dbf: No such file or directory
[oracle@oel1 disk_5]$ ls
indx.dbf   PODD  temp01.dbf  tools.dbf
[oracle@oel1 disk_5]$ ll indx*
-rw-r----- 1 oracle dba 41951232 Oct 30 16:06 indx.dbf 
[oracle@oel1 disk_5]$ ll indx.dbf
ls: indx.dbf: No such file or directory
[oracle@oel1 disk_5]$

奇怪的事情就发生了，我是眼睁睁的看着那个文件，但是就是ll不出来，

我想这还蹊跷，如果是drop tablespace xxx 没有including contents and datafiles cascade constraint 可能数据文件还不会立即删除，会保留相应的句柄，但是这个表空间我没做drop操作啊。

我查了下状态

SQL> select tablespace_name,status from dba_data_files;
TABLESPACE_NAME                STATUS
------------------------------ ---------
TBS3                           AVAILABLE
TBS1                           AVAILABLE
TBS1                           AVAILABLE
TBS1                           AVAILABLE
SYSAUX                         AVAILABLE
SYSTEM                         AVAILABLE
UNDOTBS2                       AVAILABLE
INDX                           AVAILABLE

查看datafile也看不出什么蹊跷。

可能是什么其他的地方吧。

我用如下的sql导处数据文件的状态。

select f.tablespace_name||'|'||f.file_name||'|'||f.autoextensible||'|'||f.bytes/1024/1024||'M' ||'|'|| t.initial_extent||'|'||t.next_extent||'|'||t.extent_management||'|'||t.segment_space_management||'|'||t.bigfile from dba_tablespaces t,dba_data_files f where t.tablespace_name=f.tablespace_name;

结果如下：

TBS1|/u01/app/oracle/oradata/disk_1/tbs01.dbf|NO|5M|1048576|1048576|LOCAL|AUTO|N
O
SYSAUX|/u01/app/oracle/oradata/disk_4/sysaux01.dbf|NO|325M|65536||LOCAL|AUTO|NO
UNDOTBS|/u01/app/oracle/oradata/disk_5/undotbs01.dbf|YES|200M|65536||LOCAL|MANUAL|NO
SYSTEM|/u01/app/oracle/oradata/disk_4/system01.dbf|NO|325M|65536||LOCAL|MANUAL|NO
EXAMPLE|/u01/app/oracle/oradata/disk_5/example.dbf|YES|400M|1048576|1048576|LOCAL|AUTO|NO
INDX|/u01/app/oracle/oradata/disk_5/indx.dbf |NO|40M|65536||LOCAL|AUTO|NO

最后一行的 indx.dbf后面还有个空格，原来是这个文件导致的！！！

我有个好习惯，之前执行的语句都保留了下来，一翻，验证了我的想法。

create tablespace example datafile '/u01/app/oracle/oradata/disk_5/example.dbf' size 400M autoextend on maxsize 4g extent management local uniform. size 1M;
create tablespace indx datafile '/u01/app/oracle/oradata/disk_5/indx.dbf 'size 40M;

接下来该处理这个问题了。

        10--文件号是10
/u01/app/oracle/oradata/disk_5/indx.dbf
SQL> alter database datafile 10 offline;
Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/disk_5/indx.dbf ' to '/u01/app/oracle/oradata/disk_5/indx.dbf'
  2  ;
alter database rename file '/u01/app/oracle/oradata/disk_5/indx.dbf ' to '/u01/app/oracle/oradata/disk_5/indx.dbf'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 10 - new file
'/u01/app/oracle/oradata/disk_5/indx.dbf' not found
ORA-01110: data file 10: '/u01/app/oracle/oradata/disk_5/indx.dbf '
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

貌似我写的文件空格不够标准？？？

SQL> select '|'||file_name||'|' from dba_data_files where file_id=10;
'|'||FILE_NAME||'|'
--------------------------------------------------------------------------------
|/u01/app/oracle/oradata/disk_5/indx.dbf |
SQL> alter database rename file '/u01/app/oracle/oradata/disk_5/indx.dbf 'to '/u01/app/oracle/oradata/disk_5/indx.dbf1';
alter database rename file '/u01/app/oracle/oradata/disk_5/indx.dbf 'to '/u01/app/oracle/oradata/disk_5/indx.dbf1'
*
ERROR at line 1:
ORA-01511: error in renaming log/data files
ORA-01141: error renaming data file 10 - new file
'/u01/app/oracle/oradata/disk_5/indx.dbf1' not found
ORA-01110: data file 10: '/u01/app/oracle/oradata/disk_5/indx.dbf '
ORA-27037: unable to obtain file status
Linux Error: 2: No such file or directory
Additional information: 3

还是不行，晕了，这个问题还真是费劲啊。

这样做，

[oracle@oel1 disk_5]$ ll
total 54856
-rw-r----- 1 oracle dba  5251072 Oct 30 18:01 indx.dbf 
drwxr-x--- 4 oracle dba     4096 Oct 30 10:27 PODD
-rw-r----- 1 oracle dba 20979712 Oct 30 08:34 temp01.dbf
-rw-r----- 1 oracle dba 50339840 Oct 30 18:01 tools.dbf
[oracle@oel1 disk_5]$ mv indx* indx.dbf1

SQL> alter database datafile 10 offline;
Database altered.
SQL> alter database rename file '/u01/app/oracle/oradata/disk_5/indx.dbf ' to '/u01/app/oracle/oradata/disk_5/indx.dbf1'
  2  ;
Database altered.

终于搞定了。

第5遍开始做dg

这次很快。中途抛了几个ORA错误提提神，影响不啊，最后终于看到了一个对号。

查看后台，正常了。

MRP0: Background Managed Standby Recovery process started (STDBY)
Managed Standby Recovery not using Real Time Apply
Media Recovery Waiting for thread 1 sequence 235
Tue Oct 30 17:57:22 2012
Completed: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE  THROUGH ALL SWITCHOVER DISCONNECT  NODELAY

本文参与腾讯云自媒体同步曝光计划，分享自微信公众号。

原始发表：2014-04-23，如有侵权请联系 cloudcommunity@tencent.com 删除

数据库