ORA-01031: insufficient privileges ALTER PLUGGABLE DATABASE to OPEN

Question

I'm currently working with Oracle database version 12c. I have created a common admin user - "C##ADMIN" with DBA Role. But when trying to execute the following script:

ALTER PLUGGABLE DATABASE TESTPDB OPEN;

I hit error:

ORA-00990: missing or invalid privilege

How should I assign the correct roles/privileges to "C##ADMIN" in order to run script above?

Answer 1

Just grant SYSDBA/DBA privilege to the user to all containers and login as SYSDBA to perform the task.

Demo:

SQL> create user c##admin identified by c##admin container=all;

User created.

SQL> grant sysdba to c##admin container=all;

Grant succeeded.

SQL> conn c##admin as sysdba
Enter password:
Connected.
SQL> alter pluggable database orclpdb open;

Answer 2

ALTER PLUGGABLE DATABASE

To specify the pdb_change_state clause, the current container must be the PDB whose state you want to change and you must be authenticated AS SYSBACKUP, AS SYSDBA, AS SYSDG, or AS SYSOPER.

To specify the pdb_change_state_from_root clause, the current container must be the root, you must be authenticated AS SYSBACKUP, AS SYSDBA, AS SYSDG, or AS SYSOPER, and the SYSBACKUP, SYSDBA, SYSDG, or SYSOPER privilege must be either granted to you commonly, or granted to you locally in the root and locally in the PDB(s) whose state(s) you want to change.

Answer 3

connect to your pluggable database:

CONN system/syspasss@//localhost:1521/pluggableDBname

create user marcopolo identified by marco9274 container=current;

You cannot create a local role with container=ALL. It doesn’t work, so use

container=current 

Than you can do

grant sysdba to marcopolo container=current;

connect as that user

conn marcopolo as sysdba

And do your mojo now.