Since 12c was made available for Windows 64bit OS Desktop class, people started using it immediately. And, some/few/most/all must have encountered issues while installation and especially post installation. The most important of all is understanding the multitenant database architecture. Those who have no idea about the difference between Pluggable Databases (PDB) and Container Databases (CDB), run into trouble immediately post installation of 12c.
I won’t be getting deeper into the concepts, I would just demonstrate and explain the mandatory steps for 12c installation.
OS Platform : Windows 7 64bit, desktop class
DB version : 18.104.22.168
Oracle universal installer will take you to Step 6, “Typical Install Configuration” where you would see a check box to “Create as Container database and to name the Pluggable database. If you check the option, the installer creates a single container database (CDB) to host multiple separate pluggable databases (PDB). Name the PDB properly, I prefer to append PDB to the Global database name to remember it easily.
Global Database Name : ORCL
Pluggable database name : PDBORCL
Edit your tnsnames.ora file to add the PDB details. Based on above example :
PDBORCL = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = pdborcl) ) )
addressname = PDBORCL
hostname = localhost
port = 1521
sid = pdborcl
To open all/specific PDBs immediately after logon, create a AFTER STARTUP system level trigger in CDB.
Since, the PDBs are not open through a CDB start. Let’s see :
SHUTDOWN IMMEDIATE; STARTUP; SQL> SELECT name, open_mode FROM v$pdbs; NAME OPEN_MODE ------------------------------ ---------- PDB$SEED READ ONLY PDBP6 MOUNTED
So, in order to have all the PDBs automatically open, do this :
Do, “SQLPLUS / AS SYSDBA”, and then execute :
CREATE OR REPLACE TRIGGER open_pdbs AFTER STARTUP ON DATABASE BEGIN EXECUTE IMMEDIATE 'ALTER PLUGGABLE DATABASE ALL OPEN'; END open_pdbs; /
It creates a after startup system level trigger in CDB.
The most common misunderstanding is about “SQLPLUS / AS SYSDBA” usage.
Since we have checked the option to create a single CDB, the “SQLPLUS / AS SYSDBA” command will always log into CDB. Usually developers used to unlock the “SCOTT” account directly after logging as SYSDBA. But here is the trick :
“SCOTT” and other sample schemas are in the PDB and not in the CDB. So, you need to login as sysdba into PDB.
sqlplus SYS/password@PDBORCL AS SYSDBA SQL> ALTER USER scott ACCOUNT UNLOCK IDENTIFIED BY tiger; sqlplus scott/tiger@pdborcl SQL> show user; USER is "SCOTT"