ORA-65016: FILE_NAME_CONVERT must be specified
While creating the pluggable database with SQLPLUS window, getting the following error:
Error:
SQL> CREATE PLUGGABLE DATABASE pdbsalesdb ADMIN USER pdbuser IDENTIFIED BY password;
CREATE PLUGGABLE DATABASE pdbsalesdb ADMIN USER pdbuser IDENTIFIED BY password
*
ERROR at line 1:
ORA-65016: FILE_NAME_CONVERT must be specified
Cause
Syntax error need to use FILE_NAME_CONVERT option to specified path otherwise Oracle Managed Files (OMF) parameter set db_create_file_dest
Solution
You need to provide the location information where your PDB datafile created. PDB database creation is process of cloning of PDBSEED database. It is default template for PDBs.
You can correct it with several ways. Oracle provide many statement for creating PDBs from PDBSEED database.
1. Use FILE_NAME_CONVERT in syntax.
-- You need to check the file location of PDB$SEED database
SQL> alter session set container=PDB$SEED;
Session altered.
SQL> select FILE_NAME from dba_data_files;
FILE_NAME
------------------------------------------------------------
D:\ORACLE\18.0.0\ORADATA\XE\PDBSEED\UNDOTBS01.DBF
D:\ORACLE\18.0.0\ORADATA\XE\PDBSEED\SYSAUX01.DBF
D:\ORACLE\18.0.0\ORADATA\XE\PDBSEED\SYSTEM01.DBF
-- Create the database PDB2 from PDB$SEED.
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1
FILE_NAME_CONVERT=('D:\ORACLE\18.0.0\ORADATA\XE\PDBSEED\','D:\ORACLE\18.0.0\ORADATA\XE\PDB2\');
2. Use Oracle Managed files method.
ALTER SYSTEM SET db_create_file_dest = 'D:\ORACLE';
CREATE PLUGGABLE DATABASE pdb2 ADMIN USER pdb_adm IDENTIFIED BY Password1;
3. You have alternative method for 1 step.
ALTER SESSION SET PDB_FILE_NAME_CONVERT='D:\ORACLE\18.0.0\ORADATA\XE\PDBSEED\','D:\ORACLE\18.0.0\ORADATA\XE\PDB3\';
CREATE PLUGGABLE DATABASE pdb3 ADMIN USER pdb_adm IDENTIFIED BY Password1;
Check Status and open_mode of PDBs
COLUMN pdb_name FORMAT A20
SELECT pdb_name, status
FROM dba_pdbs
ORDER BY pdb_name;
PDB_NAME STATUS
-------------------- ---------
PDB$SEED NORMAL
PDB1 NEW
PDB2 NORMAL
XEPDB1 NORMAL
COLUMN name FORMAT A18
SELECT name, open_mode
FROM v$pdbs
ORDER BY name;
NAME OPEN_MODE
------------------ ----------
PDB$SEED READ ONLY
PDB1 MOUNTED
PDB2 READ WRITE
XEPDB1 READ WRITE
HI, thanks for the article.
Although if you don’t switch back to ROOT
alter session set container=CDB$ROOT;
you get ORA-16000 error.
Thanks for the working example. As DJR mentioned you have to switch back to root container to make this command work.
I saw that you also have a separate post for this – https://smarttechways.com/2019/03/05/ora-16000-database-or-pluggable-database-open-for-read-only-access/
Thanks