Oracle 12c Internal Users

DBA_USERS in oracle 12c included a new column ORACLE_MAINTAINED which will show if the user is oracle maintained or No.

SQL> desc dba_users
Name Null? Type
———————————————– ——–
USERNAME NOT NULL VARCHAR2(128)
USER_ID NOT NULL NUMBER
PASSWORD VARCHAR2(4000)
ACCOUNT_STATUS NOT NULL VARCHAR2(32)
LOCK_DATE DATE
EXPIRY_DATE DATE
DEFAULT_TABLESPACE NOT NULL VARCHAR2(30)
TEMPORARY_TABLESPACE NOT NULL VARCHAR2(30)
CREATED NOT NULL DATE
PROFILE NOT NULL VARCHAR2(128)
INITIAL_RSRC_CONSUMER_GROUP VARCHAR2(128)
EXTERNAL_NAME VARCHAR2(4000)
PASSWORD_VERSIONS VARCHAR2(12)
EDITIONS_ENABLED VARCHAR2(1)
AUTHENTICATION_TYPE VARCHAR2(8)
PROXY_ONLY_CONNECT VARCHAR2(1)
COMMON VARCHAR2(3)
LAST_LOGIN TIMESTAMP(9) WITH TIME ZONE
ORACLE_MAINTAINED VARCHAR2(1)

SQL> select username,oracle_maintained,account_status from dba_users where oracle_maintained=’Y’ ;

USERNAME O ACCOUNT_STATUS
——————– – ——————————–
GSMCATUSER Y EXPIRED & LOCKED
SYSBACKUP Y EXPIRED & LOCKED
OUTLN Y OPEN
SYSDG Y EXPIRED & LOCKED
DBSNMP Y OPEN
ORACLE_OCM Y EXPIRED & LOCKED
SYSKM Y EXPIRED & LOCKED
XS$NULL Y EXPIRED & LOCKED
GSMUSER Y EXPIRED & LOCKED
AUDSYS Y EXPIRED & LOCKED
SYS Y OPEN
SYSTEM Y OPEN
DIP Y EXPIRED & LOCKED
ANONYMOUS Y LOCKED
XDB Y EXPIRED & LOCKED
APPQOSSYS Y EXPIRED & LOCKED
GSMADMIN_INTERNAL Y EXPIRED & LOCKED

17 rows selected.

Convert Non-CDB to PDB

1) Shut down the non CDB database and startup in read only mode

SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP OPEN READ ONLY;
ORACLE instance started.

Total System Global Area 1073741824 bytes
Fixed Size 2932632 bytes
Variable Size 905969768 bytes
Database Buffers 155189248 bytes
Redo Buffers 9650176 bytes
Database mounted.
Database opened.

2) Describe the Non-CDB using the DBMS_PDB.DESCRIBE procedure. This procedure creates an XML file in the same way that the unplug operation does for a PDB.

SQL> BEGIN
DBMS_PDB.DESCRIBE(
pdb_descr_file => ‘/apps/oracle/home/TEST12c.xml’);
END;
/ 2 3 4 5

PL/SQL procedure successfully completed.

3) Shutdown the non-CDB database

SHUTDOWN IMMEDIATE;SQL>
Database closed.
Database dismounted.
ORACLE instance shut down.

4) Need to check the compatibility of Non-CDB to be converted to Pluggable DB So, connect to the container DB (CDB) and run the following

SQL>
SET SERVEROUTPUT ON
SQL> SQL> DECLARE
2 compatible CONSTANT VARCHAR2(3) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY(
3 pdb_descr_file => ‘/apps/oracle/home/TEST12c.xml’,
4 pdb_name => ‘TESTPDB2’)
5 WHEN TRUE THEN ‘YES’
6 ELSE ‘NO’
END;
7 8 BEGIN
9 DBMS_OUTPUT.PUT_LINE(compatible);
10 END;
/ 11
YES

PL/SQL procedure successfully completed.

5) Create Pluggable database from the XML file, here we are using the same datafiles that were there earlier for the Non CDB DB.

SQL> CREATE PLUGGABLE DATABASE TESTPDB2 using ‘/apps/oracle/home/TEST12c.xml’ nocopy tempfile reuse;

Pluggable database created.

6) Check the status of the created PDB

SQL> select CON_ID, NAME, OPEN_MODE from V$PDBS;

CON_ID NAME OPEN_MODE
———- —————————— ———-
2 PDB$SEED READ ONLY
3 TESTPDB READ WRITE
4 TESTPDB2 MOUNTED
SQL> select PDB_NAME, DBID , CON_ID, STATUS from CDB_PDBS;

PDB_NAME DBID CON_ID STATUS
——————– ———- ———- ———
TESTPDB 977767011 3 NORMAL
PDB$SEED 4077181153 2 NORMAL
TESTPDB2 2198019070 4 NEW
SQL> alter session set container=TESTPDB2;

Session altered.

SQL> show con_name;

CON_NAME
——————————
TESTPDB2

7) Execute the script to update metadata

SQL> !echo $ORACLE_HOME
/apps/oracle/product/12.1.0

SQL> @?/rdbms/admin/noncdb_to_pdb.sql

SQL>
SQL>
SQL> WHENEVER SQLERROR CONTINUE;

8) Open the new PDB database

SQL> alter session set container=TESTPDB2;

Session altered.

SQL> ALTER PLUGGABLE DATABASE OPEN;

Pluggable database altered.

SQL> alter session SET CONTAINER =CDB$ROOT;

Session altered.

SQL> SELECT name, open_mode FROM v$pdbs;

NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
TESTPDB READ WRITE
TESTPDB2 READ WRITE

3 rows selected.

SQL> select PDB_NAME, DBID , CON_ID, STATUS from CDB_PDBS;

PDB_NAME DBID CON_ID STATUS
——————– ———- ———- ———
TESTPDB 977767011 3 NORMAL
PDB$SEED 4077181153 2 NORMAL
TESTPDB2 2198019070 4 NORMAL

3 rows selected.