PaulSofTech: 2018

Thursday, November 1, 2018

TNS Listener supports no services

For the case Oracle 11g database in linux, I encountered an issue where the database listener was unable to connect to any DB service although the associated database server was started, up and running. No matter For each time I issued lsnrctl start commands, the listener was unable to make tie to the running database service. The listener reported the following message at startup;

.
.
.
.
The listener supports no services
The command completed successfully

To overcome this issue, we are in need to leave the listener started and reboot the 11g database. After 11g DB restart, while issuing the lsnrctl status command and we saw that the services registered successfully with the listener.

Tuesday, August 7, 2018

Java Magazine: July/August 2018 Release

	July/August 2018

Cover	Table of Contents

Monday, April 16, 2018

Oracle DB user with password no expiry

Usually in default user profile, password expires for a user after certain period. If we want that password won't expire for a user as it may be required for the users used in the application end, we can implement that

by creating a new profile with LIMIT password no expired and
assigning this profile to our target users.

create profile pro_pass_no_exp limit password_life_time UNLIMITED;

Profile created.

alter user dbbl profile pro_pass_no_exp;

User altered.

Friday, April 13, 2018

CDB & PDB Operation

List of PLugabel Database in the Container Database:

select CON_ID, NAME, OPEN_MODE from V$PDBS;

Connecting PLuggable Database:

alter session set container = pdb6;

to connect with the container database:

ALTER SESSION SET CONTAINER = CDB$ROOT;

To get the name of connected database:

show con_name

Starting Plugable Database:

From the current PDB:
alter pluggable database open;
From Container Database:
alter pluggable database pdb6 open;

Shutting Down Plugable Database:

From the current PDB:
alter pluggable database close;
From Container Database
alter pluggable database pdb6 close;

List of Common users in CDB:

select distinct USERNAME from CDB_USERS where common = 'YES';

List of Modifiable parameters in PDB level:

set lines 200
col name for a35
select NAME, ISPDB_MODIFIABLE from V$PARAMETER;

Values of CON_ID and definition:
0 = The data pertains to the entire CDB
1= The data pertains to the root
2= The data pertains to the seed
3 - 254 = The data pertains to a PDB, Each PDB has its own container ID.

Thursday, April 12, 2018

Container(CDB) & Plug-able(PDB) Database

Plug-able Database & Container Database:

Background Processes	Shared by root CDB and All PDBs
Control File	Single Control file for entire CDB
Redo Log	Single Redo Log for entire CDB
SYSTEM Tablespace	Separate SYSAUX tablespace for the root and for each PDB.
SYSAUX Tablespace	Separate SYSAUX tablespace for the root and for each PDB.
Temporary Tablespace	one default temporary tablespace for the entire CDB; but we can can create additional temporary tablespaces in individual PDBs
Undo Tablespace	One active undo tablespace is needed for a single-instance CDB, or one active undo tablespace is needed for each instance of an Oracle RAC CDB.
Default Tablespace	We can specify a separate default tablespace for the root and for each PDB
Physical Datafiles	There are separate datafiles for the root, the seed, and each PDB.
Database Character-set	A CDB uses a single character set. All of the PDBs in the CDB use this character set.
`listener.ora`, `tnsnames.ora`, and `sqlnet.ora`	Single copy of `listener.ora`, `tnsnames.ora`, and `sqlnet.ora` file for an entire CDB. All of the PDBs in the CDB use these files.

Tuesday, April 10, 2018

Common users in Oracle DB 12c

In a Multi tenant database,we can create user accounts within the PDB's just like we normally do. This user is container oriented. And this user wont have any access to other containers.

For example, let we want to have a DBA account called paul that would be able to create tablespaces in any PDB. For this purpose we will have to create a new kind of user account called a common account.

I tried earlier as:

The common account naming format is similar to a normal account name - except that it starts with a special set of characters, C## by default. Too create a common user account called paul we will have to log into the root container and use the create user command as seen here:

SQL>create user c##paul identified by ;

When the grant is issued from the ROOT container, the default scope of that grant is just to the ROOT container. When a common user account is created, the account is created in all of the open PDB's of the pluggable database. At the same time, the account is not granted any privileges.

To provide the connection permission to this user for all available pdbs:

grant create session to c##paul container=all;

Or, we can use:

create user c##paul identified by oracle container=all;

Then we will have to grant sysdba role to c#paul for all container.

grant sysdba to c##paul container=all;

Saturday, March 31, 2018

Oracle RAC Scan Listener - (2)

To get scan name details:

These commands will have to executed by grid user.

bash-4.3$ srvctl config scan

SCAN name: SMDCMBDBL, Network: 1/XX.XX.10.0/255.255.255.0/nxge7
SCAN VIP name: scan1, IP: /xx.xx.xx.106/10.54.10.106
SCAN VIP name: scan2, IP: /xx.xx.xx.108/10.54.10.108
SCAN VIP name: scan3, IP: /xx.xx.xx.107/10.54.10.107

bash-4.3$ srvctl config scan_listener

SCAN Listener LISTENER_SCAN1 exists. Port: TCP:1521

SCAN Listener LISTENER_SCAN2 exists. Port: TCP:1521

SCAN Listener LISTENER_SCAN3 exists. Port: TCP:1521

To get the status of the scan listener:

bash-4.3$ srvctl status scan_listener

SCAN Listener LISTENER_SCAN1 is enabled

SCAN listener LISTENER_SCAN1 is running on node kdbpprp2

SCAN Listener LISTENER_SCAN2 is enabled

SCAN listener LISTENER_SCAN2 is running on node kdbpprp1

SCAN Listener LISTENER_SCAN3 is enabled

SCAN listener LISTENER_SCAN3 is running on node kdbpprp1

The above command shows the hostname on which the 3 different scan listeners are running.

DNS performs the initial load balancing while scan name is called. As 3 IPs are assigned with the
scan name initially though that can be extended later. While clients send request to DNS, DNS
provided three different IP on different request. I.e while we are trying to fetch the DNS with
scan name, we get IP addresses order in different order.

paul@DBBL-ITDD-PAUL:~$ nslookup kdbpprp

Server: 127.0.1.1

Address: 127.0.1.1#53