ORA-01516 nonexistent log file, datafile, or tempfile

ORA-01516 nonexistent log file, datafile, or tempfile

SQL> select file_name,file_id from dba_data_files where tablespace_name='DEV';

FILE_NAME

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

   FILE_ID

----------

/ora9i/dev01.dbf

        40

/ora9i/dev02.dbf

        41

/ora9i/dev03.dbf

        42

/ora9i/dev04.dbf

        43

Sql>alter database datafile ‘/ora9i/dev04.dbf’ resize 1500m;

Error at line 1:
ORA-01516: nonexistent log file, datafile, or tempfile ‘/ora9i/dev04.dbf’.

Extra special character or symbol has been added in the datafile during the datafile creation. So we should rename the datafile for further operations such as resize.

Here  i am going to rename to datafile using rman utility.

RMAN> run

2> {

3> backup datafile 43;

4> sql 'alter database datafile 43 offline';

5> set newname for datafile 43 to '/ora9i/new_dev04.dbf';

6> restore datafile 43;

7> switch datafile all;

8> recover datafile 43;

9> sql 'alter database datafile 43 online';

10> }

  Starting backup at 13-JUL-12

allocated channel: ORA_DISK_1

channel ORA_DISK_1: sid=197 devtype=DISK

allocated channel: ORA_DISK_2

channel ORA_DISK_2: sid=83 devtype=DISK

allocated channel: ORA_DISK_3

channel ORA_DISK_3: sid=189 devtype=DISK

allocated channel: ORA_DISK_4

channel ORA_DISK_4: sid=193 devtype=DISK

allocated channel: ORA_DISK_5

channel ORA_DISK_5: sid=179 devtype=DISK

allocated channel: ORA_DISK_6

channel ORA_DISK_6: sid=185 devtype=DISK

channel ORA_DISK_1: starting full datafile backupset

channel ORA_DISK_1: specifying datafile(s) in backupset

input datafile fno=00043 name=/ora9i/dev04.dbf

channel ORA_DISK_1: starting piece 1 at 13-JUL-12

channel ORA_DISK_1: finished piece 1 at 13-JUL-12

piece handle=/ora9i/oracle/dbs/jing155f_1_1 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:25

Finished backup at 13-JUL-12

Starting Control File and SPFILE Autobackup at 13-JUL-12

piece handle=/ora9i/oracle/dbs/c-3541388257-20120713-07 comment=NONE

Finished Control File and SPFILE Autobackup at 13-JUL-12

sql statement: alter database datafile 43 offline

executing command: SET NEWNAME

Starting restore at 13-JUL-12

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

using channel ORA_DISK_5

using channel ORA_DISK_6

channel ORA_DISK_1: starting datafile backupset restore

channel ORA_DISK_1: specifying datafile(s) to restore from backup set

restoring datafile 00043 to /ora9i/new_dev04.dbf

channel ORA_DISK_1: restored backup piece 1

piece handle=/ora9i/oracle/dbs/jing155f_1_1 tag=TAG20120713T222655 params=NULL

channel ORA_DISK_1: restore complete

Finished restore at 13-JUL-12

datafile 43 switched to datafile copy

input datafilecopy recid=23 stamp=788567291 filename=/ora9i/new_dev.dbf

starting full resync of recovery catalog

full resync complete

Starting recover at 13-JUL-12

using channel ORA_DISK_1

using channel ORA_DISK_2

using channel ORA_DISK_3

using channel ORA_DISK_4

using channel ORA_DISK_5

using channel ORA_DISK_6

starting media recovery

media recovery complete

Finished recover at 13-JUL-12

sql statement: alter database datafile 43 online

RMAN>

SQL> select file_name,file_id from dba_data_files where tablespace_name='DEV';

FILE_NAME

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

   FILE_ID

----------

/ora9i/dev01.dbf

        40

/ora9i/dev02.dbf

        41

/ora9i/dev03.dbf

        42

/ora9i/new_dev04.dbf

        43

Sql>alter database datafile ‘/ora9i/new_dev04.dbf’ resize 1500m;

Database altered.