Unlock the user in PDB database

Question

Everytime I turn on my computer and try to connect to HR database, an error pops up stating is blocked, then I go to SQLPLUS and log in as SYSDBA, and do the following script:

My question is, do I always have to do this? Is there any way to keep it always UNLOCKED?

1.- sqlplus / as sysdba

2.- show con_name;

3.- ALTER SESSION SET CONTAINER = orclpdb;

4.- COLUMN name FORMAT a20;

5.- SELECT name, open_mode from v$pdbs;

6.- ALTER PLUGGABLE DATABASE open;

7.- ALTER USER hr IDENTIFIED BY hr ACCOUNT UNLOCK;

8- conn hr/hr@orclpdb

9.- SHOW USER;

Answer 1

Please read Oracle 12c Post Installation Mandatory Steps

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

As you could see the PDB is still in mounted state and not open for read/write.

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.

Now unlock the user:

sqlplus SYS/password@PDBORCL AS SYSDBA

SQL> ALTER USER hr ACCOUNT UNLOCK IDENTIFIED BY hr;

sqlplus hr/hr@pdborcl

SQL> show user;
USER is "HR"

Now you need not open the PDBs manually, just connect to the PDB and the user you want to.

From 12.1.0.2 onward, you can preserve the PDB startup state through CDB restart:

ALTER PLUGGABLE DATABASE pdb_name OPEN;
ALTER PLUGGABLE DATABASE pdb_name SAVE STATE;

To discard the saved state:

ALTER PLUGGABLE DATABASE pdb_name DISCARD STATE;