Oracle 12c and above manually create pdb

1. Create via seed

CREATE PLUGGABLE DATABASE salespdb

ADMIN USER salesadm IDENTIFIED BY password

ROLES = (dba)

DEFAULT TABLESPACE sales

DATAFILE'/ home/oracle/scripts/ORCL/salespdb/sales01.dbf' SIZE 250m AUTOEXTEND ON

FILE_NAME_CONVERT = ('/ home/oracle/scripts/ORCL/pdbseed/','/home/oracle/scripts/ORCL/salespdb/')

STORAGE (MAXSIZE 2G)

PATH_PREFIX ='/ home/oracle/scripts/ORCL/salespdb/'

Description: / disk1/oracle/dbs/pdbseed/ is the seed database data file storage path, / disk1/oracle/dbs/salespdb/ is the new pdb database file storage path.

2. Create through an existing pdb (pdb must be in open mode)

ORA-65036: plug-in database SALESPDB is not open in the desired mode

CREATE PLUGGABLE DATABASE salespdb1 FROM salespdb

FILE_NAME_CONVERT = ('/ home/oracle/scripts/ORCL/salespdb/','/ home/oracle/scripts/ORCL/salespdb1/')

PATH_PREFIX ='/ home/oracle/scripts/ORCL/salespdb1'

3. Error after manual creation of pdb (RESTRICTED value belongs to yes)

SQL > show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

-

2 PDB$SEED READ ONLY NO

3 PDB READ WRITE NO

4 SALESPDB READ WRITE YES

5 HQ_PDB READ WRITE YES

6 SALESPDB1 READ WRITE NO

Select * from PDB_PLUG_IN_VIOLATIONS

Reason

After you create a PDB with a seed PDB or insert or clone method, you can view the status of the new PDB by querying the STATUS column of the CDB_PDBS view.

If you create public users and roles before opening a new PDB, you must synchronize PDB to retrieve new public users and roles from the root.

Synchronization is performed automatically when PDB is opened in read / write mode. If you open PDB in read-only mode, an error is returned.

You can view the violation description by querying the PDB_PLUG_IN_VIOLATIONS view.

3. Scheme

Therefore, the only thing you can do is to create the tablespace in PDBPROD2, close the database, and synchronize again.

Create tablespace sqlaudit_mon datafile'/ home/oracle/scripts/ORCL/salespdb/sqlaudit_mon.dbf' size 10m

Create tablespace sqlaudit_mon datafile'/ home/oracle/scripts/ORCL/salespdb1/sqlaudit_mon.dbf' size 10m

Query the restricted PDB under CDB:

Select con_id, name,open_mode,restricted from v$pdbs

Query the relevant restricted PDB in PDB:

Select instance_name,logins,status from gv$instance (v$containers can also be queried)

Change the pdb restricted mode under cdb:

Alter pluggable database pdb_name close immediate instance=all; (all except pdb1)

Alter pluggable database pdb_name open read write instance=all

Change the pdb restricted mode under pdb:

Alter session set container=pdb_name

Alter session set container=SALESPDB

Alter pluggable database close immediate

Alter pluggable database open

In restricted mode, specific users can be granted restricted session permissions for temporary login, remember revoke.