Oracle 12c ORA-01516: nonexistent log file, data file, or temporary file "10"

標籤：ora-01516

執行alter database move操作報錯ORA-01516:

SQL> alter database move datafile ‘/u01/app/oracle/oradata/cdb/pdb/pdb_users01.dbf‘ to ‘/u01/app/pdb_user01.dbf‘;

alter database move datafile ‘/u01/app/oracle/oradata/cdb/pdb/pdb_users01.dbf‘ to ‘/u01/app/pdb_user01.dbf‘

*

ERROR at line 1:

ORA-01516: nonexistent log file, data file, or temporary file "10"

[[email protected] ~]$ oerr ora 1516

01516, 00000, "nonexistent log file, data file, or temporary file \"%s\""

// *Cause:  An attempt was made to use ALTER DATABASE to rename

//          a log file, data file, or temporary file; or to change attributes

//          of a data file or temporary file (for example, resize, autoextend,

//          online or offline); or to re-create or move a data file.

//          The attempt failed because the specified file

//          is not known to the database‘s control file

//          or is not of a type supported by the request.

// *Action: Specify the name or number of an existing file

//          of the correct type, as appropriate.

//          Check the relevant V$ table for a list of possible files.

查看file 10是否存在：

[[email protected] backup]$ du -sh /u01/app/oracle/oradata/cdb/pdb/pdb_users01.dbf

5.1M/u01/app/oracle/oradata/cdb/pdb/pdb_users01.dbf

檔案10存在。

這個時候想到log file、temporary file難道只能在con_name為自己資料庫可用？帶著疑問做測試。

SQL> show con_name

CON_NAME

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

CDB$ROOT

首先用alter pluggable database move命令來看看是否在CDB資料庫中能否一定PDB檔案。

SQL> alter pluggable database move datafile ‘/u01/app/oracle/oradata/cdb/pdb/pdb_users01.dbf‘ to  ‘/u01/app/pdb_user01.dbf‘ ;

alter pluggable database move datafile ‘/u01/app/oracle/oradata/cdb/pdb/pdb_users01.dbf‘ to  ‘/u01/app/pdb_user01.dbf‘ ;

                                                                                          *

ERROR at line 1:

ORA-01916: keyword ONLINE, OFFLINE, RESIZE, AUTOEXTEND or END/DROP expected

顯然，在CDB中不支援alter pluggable database move命令

接下來在pdb中執行alter pluggable database move操作

SQL> show pdbs        

    CON_ID CON_NAME  OPEN MODE  RESTRICTED

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

2 PDB$SEED  READ ONLY  NO

3 PDB  READ WRITE NO

SQL> alter session set container=pdb;

Session altered.

SQL> alter database move datafile ‘/u01/app/oracle/oradata/cdb/pdb/pdb_users01.dbf‘ to ‘/u01/app/pdb_user01.dbf‘;

Database altered.

SQL> select name,status from v$datafile where file#=10;

NAME                                                                             STATUS

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

/u01/app/pdb_user01.dbf                                                          ONLINE

成功！

   總結:儘管oracle 12c記錄檔共用，但是cdb和各個pdb只能在各自資料庫使用自己的日誌。

本文出自 “知行合一” 部落格，請務必保留此出處http://dovelauren.blog.51cto.com/9876026/1720631

Oracle 12c ORA-01516: nonexistent log file, data file, or temporary file "10"