How to solve the ORA-01516 problem caused by spaces in Oracle12c

This article introduces the relevant knowledge of "how to solve the ORA-01516 problem caused by spaces in Oracle12c". In the operation of actual cases, many people will encounter such a dilemma, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

On January 29th, 2019, zabbix displays an index tablespace alarm, so log in to the server to see:

10:02:22 SQL > col file_name for A50

10:02:42 SQL > select file_id,tablespace_name,file_name,bytes/1024/1024/1024 G from dba_data_files where tablespace_name='&ts' order by 1

Enter value for ts: TBS_LOGIDX

Old 1: select file_id,tablespace_name,file_name,bytes/1024/1024/1024 G from dba_data_files where tablespace_name='&ts' order by 1

New 1: select file_id,tablespace_name,file_name,bytes/1024/1024/1024 G from dba_data_files where tablespace_name='TBS_LOGIDX' order by 1

FILE_ID TABLESPACE_NAME FILE_NAME G

2 TBS_LOGIDX / u01/app/oracle/oradata/DLOG/tbs_logix01.dbf 31.9999847

18 TBS_LOGIDX / u01/app/oracle/oradata/DLOG/tbs_logix02.dbf 31.9570313

19 TBS_LOGIDX / u01/app/oracle/oradata/DLOG/tbs_unvidx03.dbf 31.9570313

3 rows selected.

Elapsed: 00:00:00.01

I was confused at a glance. I didn't know who was adding the data file before, but the name was wrong. Fortunately, this is a set of 12.2 databases that can rename data files online in the same way as moving data files online. A previous blog post briefly introduced:

Http://blog.itpub.net/31480688/viewspace-2214095/

Let's change the data file name with this method.

10:05:03 SQL > alter / * source only*/ database move datafile'/ u01qqqoradataDLOGHelp tbsspuunvidx03.dbf'oradata'/ u01Grex03.dbf' / u01qqr SQL > alter / * oradata'/ u01qqr

Alter / * source only*/ database move datafile'/ u01Greg source only*/ database move datafile'/ u01Greg _ to'/ u01Greg _ Universe _ unvidx03.dbf'oradata'/ u01qappAcle _ to'/ u01 _ applicaloradata _

*

ERROR at line 1:

ORA-01516: nonexistent log file, data file, or temporary file "/ u01/app/oracle/oradata/DLOG/tbs_unvidx03.dbf" in the current container

The result report data file does not exist. Operating system file check file exists

Oracle@ORADB-14417: / u01 ls ls-lrt tbs_unvi*

-rw-r- 1 oracle oinstall 1073750016 Jan 29 10:13 tbs_unvidx01.dbf

-rw-r- 1 oracle oinstall 34313609216 Jan 29 10:13 tbs_unvidx03.dbf

Query v$datafile data file exists

10:07:29 SQL > select name,status from v$datafile where file#=19

NAME

-

STATUS

-

/ u01/app/oracle/oradata/DLOG/tbs_unvidx03.dbf

ONLINE

1 row selected.

Elapsed: 00:00:00.02

Oracle database has never been a road to Huashan, but all roads lead to Rome, the solution is also very simple, it is possible to specify file_id.

10:14:09 SQL > alter / * source only*/ database move datafile 19 to'/ u01 apprenticeships oradataUniplicateDLOG Greater tbsroomlogix03.dbf'

Database altered.

Elapsed: 00:11:41.37

You can see the locks added by this process through toad

It's still a long time.

Finally, the problem was found when the mouse was selected in CRT.

This is the end of the content of "how to solve the ORA-01516 problem caused by spaces in Oracle12c". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!