How to Offline a PDB Datafile in NOARCHIVELOG mode CDB which is not Open in Read Write (Doc ID 2240730.1)

1. pdb 下的datafile 只能在pdb下操作，不能在cdb下操作

For the purposes of this document, the following fictitious environment is used as an example to describe the procedure:

Container Database Name: CDB1
Pluggable Database Names: PDB1
Datafile Name: /tmp/STATSPACK.dbf

*********************

Attempt to offline a datafile 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

Changes

Unable to open the CDB, when one of the datafiles, belonging to PDB is missing from the filesystem.

SQL> alter database open;
alter database open
*
ERROR at line 1:
ORA-01157: cannot identify/lock data file 23 - see DBWR trace file
ORA-01110: data file 23: '/tmp/STATSPACK.dbf'

But I cannot offline the datafile:

SQL> select name, open_mode from v$pdbs;

NAME
--------------------------------------------------------------------------------
OPEN_MODE
----------
PDB$SEED
MOUNTED

PDB1
MOUNTED

SQL> alter session set container=PDB1;

Session altered.

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

Cause

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: <none>
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.

1.252 PDB_LOCKDOWN

PDB_LOCKDOWN determines the PDB lockdown profile that applies to a PDB.

A PDB lockdown profile is a mechanism to restrict operations (such as setting values of certain parameters and using certain options) that can be performed by users connected to a given PDB. You can also restrict execution of any packages that allow network access, for example, UTL_SMTP.

You create lockdown profiles using the SQL CREATE LOCKDOWN PROFILE statement. Then you can set a profile using the SQL ALTER SESSION or ALTER SYSTEM statement. See the Examples section.

This parameter can be set using the ALTER SYSTEM statement with scope set to MEMORY, SPFILE, or BOTH.

The lockdown profile for PDBs can be specified by a common user with common ALTER SYSTEM or common SYSDBA privilege.

If a PDB lockdown profile is dropped, any PDB to which the dropped profile was assigned (by means of storing the dropped profile name in the PDB_LOCKDOWN parameter) will continue to have its PDB_LOCKDOWN parameter set to the dropped lockdown profile name. However, the PDB will not have any restrictions imposed by the dropped lockdown profile.

Lockdown profiles can now be created in an application root and are referred to as application lockdown profiles.

A CDB common user with common SYSDBA or common ALTER SYSTEM privilege can only set PDB_LOCKDOWN to a CDB lockdown profile. Similarly, an application common user with application common SYSDBA or application common ALTER SYSTEM privilege can only set PDB_LOCKDOWN to an application lockdown profile.

An application common user cannot overwrite PDB_LOCKDOWN if PDB_LOCKDOWN is already set to a CDB lockdown profile in an application root or application PDB.

If the PDB_LOCKDOWN parameter in a PDB is set to the name of a lockdown profile different from that in its ancestor (for a CDB, the CDB root or, for application PDBs, the application root), the following will govern the interaction between restrictions imposed by these profiles:

• If the PDB_LOCKDOWN parameter in a PDB (including an application PDB) is set to a CDB lockdown profile, lockdown profiles specified by the PDB_LOCKDOWN parameter in CDB root (and for application PDBs, the application root) are ignored.

• If the PDB_LOCKDOWN parameter in an application PDB is set to an application lockdown profile while the PDB_LOCKDOWN parameter in the application root or CDB root is set to a CDB lockdown profile, in addition to the rules stipulated in the application lockdown profile, the DISABLE rules from the CDB lockdown profile set in its nearest ancestor (that is, an application root or CDB root) are inherited.

• If there are conflicts between rules comprising the CDB lockdown profile and the application lockdown profile, the rules in the CDB lockdown profile will take precedence (for example, the OPTION_VALUE clause of a CDB lockdown profile will take precedence over the OPTION_VALUE clause of an application lockdown profile).

Examples

This example shows how the SYS user can connect to the database AS SYSDBA and use the CREATE LOCKDOWN PROFILE statement in the root of a CDB to define a new lockdown profile. After defining the new lockdown profile, the SYS user can assign the new lockdown profile to a PDB using the PDB_LOCKDOWN parameter:

Copy

SQL> ALTER SESSION SET CONTAINER=CDB$ROOT; Session altered. SQL> CREATE LOCKDOWN PROFILE MYPROFILE; Lockdown Profile created. SQL> ALTER SESSION SET CONTAINER=CDB1_PDB1; Session altered. SQL> ALTER SYSTEM SET PDB_LOCKDOWN=MYPROFILE; System altered. SQL> SHOW PARAMETER PDB_LOCKDOWN NAME TYPE VALUE -------------- ----------- --------- pdb_lockdown string MYPROFILE SQL>