Tags
12.1.0.2., 12102, 12c, 12c post installation steps, cdb, connect to pdb, discard state, install 12c, open pdb, pdb, pluggable database, preserve pdb state, save state, unlock scott in 12c
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 : 12.1.0.1
1.
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.
For example,
Global Database Name : ORCL
Pluggable database name : PDBORCL
2.
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)
)
)
Where,
addressname = PDBORCL
hostname = localhost
port = 1521
sid = pdborcl
3.
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.
From 12.1.0.2 onward, you can preserve the PDB startup state through CDB restart. The 12.1.0.2 patchset introduced SAVE STATE and DISCARD STATE options:
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;
4.
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"
Xuân Hoàng Nguyễn said:
ORA-01109: database not open when I use:
ALTER USER scott ACCOUNT UNLOCK IDENTIFIED BY sys;
Lalit Kumar B said:
1. Please verify whether you are connecting to CDB or PDB.
2. Did you create the login trigger to open the PDB(s) at startup?
3. The error states that you haven’t yet opened the database.
Arifur Bhuyan said:
we need to execute “alter database pdborcl open;” after that we can execute the step to create trigger. These 2 steps were successful for me.
After that i tried to re-login using command “sqlplus sys/password@PDBORCL as sysdba”, but i’m getting below error. I tried restarting listener service.. no luck
ORA-12154: TNS:could not resolve the connect identifier specified
Lalit Kumar B said:
Did you add your PDB to your tnanames.ora file? See #2 above how to edit and add PDB to tnsnames.ora.
Mohammed Ismail said:
hi lalit,
when i first configured 12c i repeated the above steps then i could able to connect to users created in PDB.
when i restarted my system all changes are gone. Though i can connect to CDB and PDB sys users, i am not able to connect to local users in PDB. getting this error
SQL> alter user txs_sys account unlock;
User altered.
SQL> alter user txs_sys identified by txs_sys;
User altered.
SQL> grant connect,create session to txs_sys;
Grant succeeded.
SQL> conn txs_sys/txs_sys;
ERROR:
ORA-01017: invalid username/password; logon denie
Warning: You are no longer connected to ORACLE.
Please Suggest, if i hav missed anything.
Lalit Kumar B said:
Hi,
You are getting ORA-01017 because you are not providing the service name in the connection. You must connect is as:
You must also ensure you have the pluggable database entry in your tnsnames.ora.
Mohammed Ismail said:
Hi lalit, thanks for Quick reply
Problem is solved. 🙂
I have one more thing to query you …
D:\>sqlplus sys/system@xe as sysdba
SQL*Plus: Release 12.1.0.2.0 Production on Thu Dec 8 10:44:51 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Oracle Label Security, OLAP, Advanced Analytics
and Real Application Testing options
SQL> select username from dba_users;
USERNAME
——————————————————————————–
ORACLE_OCM
OJVMSYS
SYSKM
XS$NULL
GSMCATUSER
MDDATA
SYSBACKUP
DIP
SYSDG
APEX_PUBLIC_USER
SPATIAL_CSW_ADMIN_USR
USERNAME
——————————————————————————–
SPATIAL_WFS_ADMIN_USR
GSMUSER
AUDSYS
FLOWS_FILES
DVF
MDSYS
ORDSYS
DBSNMP
WMSYS
APEX_040200
APPQOSSYS
USERNAME
——————————————————————————–
GSMADMIN_INTERNAL
ORDDATA
CTXSYS
ANONYMOUS
XDB
ORDPLUGINS
DVSYS
SI_INFORMTN_SCHEMA
OLAPSYS
LBACSYS
OUTLN
USERNAME
——————————————————————————–
SYSTEM
SYS
35 rows selected.
SQL> conn txs_sys/txs_sys@xe
Connected.
SQL> conn sys/system@xe as sysdba
Connected.
SQL> create user test identified by test;
create user test identified by test
*
ERROR at line 1:
ORA-65096: invalid common user or role name
IN THE USERNAME LIST : I couldnot find the user i had created txs_sys earlier where has it gone. And also if i create a new user it is not creating at all.
All these things hav started happening since i restarted my system. everything was working fine earlier . Please help .
Lalit Kumar B said:
You are confusing between container database and pluggable database. Is XE created as CBD or PDB?
ORA-65096: invalid common user or role name means you are trying to create users in container and not pluggable database. You need to append C# to the username, however, I would suggest to create common users in PDB. Please read the documentation to learn more about the new 12C multi-tenant architecture.
Mohammed Ismail said:
Hi Lalit,
I understood your Point, Here is an important observation Please provide your views on the same.
Enter user-name: sys / as sysdba
Enter password:
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> select name,dbid,con_dbid,cdb,con_id from v$database;
NAME DBID CON_DBID CDB CON_ID
——— ———- ———- — ———-
ORCL 1456888454 1456888454 YES 0
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
XE READ WRITE
SQL> conn sys/system@xe as sysdba
Connected.
SQL> select name, open_mode from v$pdbs;
NAME OPEN_MODE
—————————— ———-
PDB$SEED READ ONLY
PDBORCL MOUNTED
SQL> select name,dbid,con_dbid,cdb,con_id from v$database;
NAME DBID CON_DBID CDB CON_ID
——— ———- ———- — ———-
XE 2810222692 2810222692 YES 0
SQL> create user te identified by te;
create user te identified by te
*
ERROR at line 1:
ORA-65096: invalid common user or role name
From this I understood that there are two CDBs ORCL and XE. Now my question is how can i create XE as PDB for ORCL.
Thank You.
Mohammed Ismail said:
Please see CDB variable is Yes in both the DB connections. With this i conclude that they are CDB. Please suggest further how can i identify which is CDB or PDB if it is not the case.
Lalit Kumar B said:
If you want to create a PDB, then either use the installer GUI to create a new PDB or see documentation to do it on command-line.
I see you already have a PDB named “PDBORCL”. It is not yet opened in Read-Write mode, it shows it is just mounted. See the above steps to open a PDB and then create users in PDBORCL.
Good luck!
Yasir Arshad said:
hi lalit, after installing 12 c I am unable to login. showing error of invalid username, later after connecting to command prompt I connected to sys/oracle as sysdba. but here I didn’t see any user like scott or hr. after query this command.
SELECT USERNAME FROM DBA_USERS;
I don’t know how to resolve this.
Lalit Kumar B said:
You need to install the sample schemas, see https://docs.oracle.com/database/121/COMSC/installation.htm#COMSC001
Remember to create them in PDB. All the scripts will be available in $ORACLE_HOME/demo/schema directory.
Pingback: error: ORA-65096: invalid common user or role name in oracle