For the purposes of this post, the following environment is used as an example to describe the procedure:
Container Database Name: CDB1 Pluggable Database Names: PDB1 Datafile Name: /tmp/STATSPACK.dbf
If you try to attempt to offline a datafile, it results in error when the database is running in NOARCHIVELOG mode and is not open in read write mode:
SQL> alter database datafile '/tmp/STATSPACK.dbf' offline; alter database datafile '/tmp/STATSPACK.dbf' offline * ERROR at line 1: ORA-00604: error occurred at recursive SQL level 1 ORA-01219: database or pluggable database not open: queries allowed on fixed tables or views only
To determine the cause, enable 10046 trace event and try to offline the datafile:
alter session set tracefile_identifier='event_10046'; alter session set events '10046 trace name context forever,level 12'; alter database datafile '/tmp/STATSPACK.dbf' offline;
Review the trace file generated by the 10046 event.
Processing Oradebug command 'setmypid' *** 2017-02-21T13:36:28.630992+01:00 (PDB1(3)) Oradebug command 'setmypid' console output: [none] *** 2017-02-21T13:36:38.063286+01:00 (PDB1(3)) Processing Oradebug command 'EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12' +++++++++ 10046 trace event at level 12 *** 2017-02-21T13:36:38.121570+01:00 (PDB1(3)) Oradebug command 'EVENT 10046 TRACE NAME CONTEXT FOREVER, LEVEL 12' console output:WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=198174189201 *** 2017-02-21T13:36:43.936339+01:00 (PDB1(3)) WAIT #0: nam='SQL*Net message from client' ela= 5814615 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=198180003894 WAIT #0: nam='Disk file operations I/O' ela= 17 FileOperation=8 fileno=0 filetype=8 obj#=-1 tim=198180004004 *** 2017-02-21T13:36:43.936438+01:00 (PDB1(3)) Processing Oradebug command 'TRACEFILE_NAME' *** 2017-02-21T13:36:43.936469+01:00 (PDB1(3)) Oradebug command 'TRACEFILE_NAME' console output: /u01/app/oracle/diag/rdbms/cdb1/CDB1/trace/CDB1_ora_32744.trc WAIT #0: nam='SQL*Net message to client' ela= 1 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=198180004067 *** 2017-02-21T13:36:51.167762+01:00 (PDB1(3)) WAIT #0: nam='SQL*Net message from client' ela= 7231241 driver id=1650815232 #bytes=1 p3=0 obj#=-1 tim=198187235319 XCTEND rlbk=0, rd_only=1, tim=198187235540 *** 2017-02-21T13:36:51.168323+01:00 (CDB$ROOT(1)) ===================== PARSING IN CURSOR #140359700677696 len=248 dep=1 uid=0 oct=3 lid=0 tim=198187235894 hv=1954812753 ad='7b67d9c8' sqlid='6qpmyqju884uj' select ruletyp#, ruleval, status, ltime from lockdown_prof$ where prof#=:1 and level#=:2 order by ltime END OF STMT PARSE #140359700677696:c=0,e=130,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=198187235890 ===================== PARSE ERROR #140359700677696:len=249 dep=1 uid=0 oct=3 lid=0 tim=198187236029 err=1219 select ruletyp#, ruleval, status, ltime from lockdown_prof$ +++++++++++++++++ error while performing select operation on lockdown_prof$
Information in trace file shows that there is a lockdown profile. Thus any command will attempt to read the lockdown_prof$ table in CDB$ROOT. This will wait as the CDB$ROOT is not open.
In other words, an ORA-01219 error raised for any command when:
1) CDB is not opened.
AND
2) a lockdown profile is set at CDB level.
Solution
Set pdb_lockdown=Null and attempt to offline/drop the datafile:
SQL> alter system set pdb_lockdown=''; System altered.
SQL> alter session set container=pdb1; Session altered. SQL> alter database datafile 23 offline for drop; Database altered. Database altered.
SQL> alter session set container=cdb$root; Session altered. SQL> alter database open; Database altered.