To Know details about Linux OS :Oracle, PostgreSQL, MySQL and MongoDB

In this small article, we are going to see different commands to check the OS version in Linux and other details about the operating system

Here are a few files and commands that can be used

1. Command uname:

Usage uname -a

[oracle@localhost ~]$  uname -a

Linux localhost.localdomain 5.15.0-8.91.4.1.el8uek.x86_64 #2 SMP Tue Mar 7 18:28:34 PST 2023 x86_64 x86_64 x86_64 GNU/Linux

[oracle@localhost ~]$

This command displays the type of os, OS release, OS version version, hardware name, and processor type

2. Command lsb_release:

Usage:lsb_release -a

[root@localhost ~]# lsb_release -a

LSB Version:   :core-4.1-amd64:core-4.1-noarch

Distributor ID:     OracleServer

Description:   Oracle Linux Server release 8.7

Release:  8.7

Codename: n/a

[root@localhost ~]#

This command would display information including the Distributor ID, Description, OS Release, and Codename.

3. File /etc/os-release

Usage: cat /etc/os-release   or more /etc/os-release

This file contains almost all the details about the operating system

4. File /etc/issue:

Usage:  cat /etc/issue  or more /etc/issue

[root@localhost ~]# cat /etc/issue

\S

Kernel \r on an \m

[root@localhost ~]#

This command will give an idea about the kernel details of the operating system

Based on the requirement, you can use a command or file. 

Read More

Read Only Oracle user in Oracle 23c database

In earlier releases of Oracle database editions, we can see common users, local users, or normal users, to make them read-only, we had to create special read-only roles and grant those to users, but with Oracle 23c, you can create special read-only users and can convert them to read write as well whenever required.

This read-only user can only perform select operations and completely restrict write operations

In the below example we will create read-only users in oracle 23c database and try to perform different operations 

SQL> conn sys/oracle@FREEPDB1 as sysdba

Connected.

SQL> show user

USER is "SYS"

SQL> show con_name

 

CON_NAME

------------------------------

FREEPDB1

SQL> 

use the below statement to create a read-only user in the Oracle 23c database and grant DBA privileges to the user

 

SQL> create user technodb identified by tech123 quota unlimited on users read only;

 

User created.

 

SQL> set lines 200 pages 2000 

SQL> col username for a20

SQL>select username, read_only from dba_users where username='TECHNODB';

USERNAME       REA

-------------------- ---

TECHNODB       YES

 

SQL> grant dba to technodb;

 

Grant succeeded.

 

Now we will try to create table with account

SQL>conn technodb/tech123@freepdb1

 

SQL> create table tab1 (id integer);

create table tab1 (id integer)

*

ERROR at line 1:

ORA-28194: Can perform read operations only

 

 

To convert read only user to read write, we can use below option 

SQL> alter user technodb read write;

 

User altered.

 

SQL> 

SQL> select username, read_only from dba_users where username='TECHNODB';

 

USERNAME       READ_ONLY

-------------------- --------------------

TECHNODB       NO

 

 

Now you can see you will be able to perform write operations as well

 

SQL> conn technodb/tech123@freepdb1

Connected.

SQL> 

SQL> create table tab1(id integer);

 

Table created.

 

SQL> 

 

 One thing to note that, read-only users can execute procedures that does not perform any DML/DDL statements in it.

 

 

Read More

Oracle : TNSNAMES.ORA file

In Oracle, the tnsnames.ora file important configuration file that is used in Oracle database connection's

default location of tnsnames.ora file is $ORACLE_HOME/network/admin but it can be modified or kept at other locations as well and create soft links at the default location. while using a location other than the default location, we need to use the TNS_ADMIN parameter to load settings while starting the listener of the database.

In case you are not sure about the location of tnsnames.ora file, you can execute below commands on Unix shell

$env |grep ORACLE_HOME

or use below command 

echo $ORACLE_HOME

Syntax used in tnsnames.ora file

testdb.domain.com

(DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST =  HOSTNAME.DOMAIN.COM) (PORT = 1521) 

)

(CONNECT_DATA =)

    ( SERVICE_NAME=tstdb.domain.com)

)

This file can be modified very easily, take backup before  modifying anything in this file

on Unix use vi editor to open the file and change anything we want then save using :wq

On Windows, one can use Notepad editor and save using ctrl+S or the save option

Read More

Multiplexing of control files in oracle database

As an Oracle DBA, we might come across a situation where the Oracle database has only one control file, which is considered dangerous to the database, as failure or corruption of a single control file cause issue to database availability. 

For example, in the below database we have only one control file

 

SQL> select name from v$controlfile;

 

NAME

-----------------------------------------------------------------

C:\APP\ORACLE\ORADATA\ASGARD\CONTROL01.CTL

 

As standard, we should have at least 2 control files.  converting one control file to multiple control files termed as multiplexing of control files. We will perform multiplexing of control activity. 

We will convert this single control to two control files, that will provide security to the database.

Step 1 :  Check existing control file name and location using either of the below command 

 

SQL> select name from v$controlfile;

 

NAME

-----------------------------------------------------------------

C:\APP\ORACLE\ORADATA\ASGARD\CONTROL01.CTL

 

SQL> show parameter control_file

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

control_file_record_keep_time        integer     7

control_files                        string      C:\APP\ORACLE\ORADATA\ASGARD\C

                                                 ONTROL01.CTL

Step 2 : check whether database is running on pfile or spfile using below command 

 

SQL> show parameter pfile

 

NAME               TYPE        VALUE

------------------ ----------- ------------------------------

spfile             string      C:\APP\ORACLE\PRODUCT\11.2.0.4                                                 \DATABASE\SPFILEASGARD.ORA

SQL>

 

If this command gives output, then consider oracle database is running with spfile (server parameter pfile)

Step 3 :  Create pfile with spfile, so that we can add new control file to the database

 

SQL> create pfile from spfile;

 

File created.

 

SQL>

 

Step 4 : Shutdown database and copy existing control file to new location and add location in pfile as well 

 

SQL>    shu immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

 

 

C:\Users>copy "C:\app\oracle\oradata\asgard\CONTROL01.CTL" C:\app\oracle\oradata\ctrl\CONTROL02.CTL

        1 file(s) copied.

 

 

*.control_files='C:\app\oracle\oradata\asgard\control01.ctl','C:\app\oracle\oradata\ctrl\CONTROL02.CTL'

 

Step 5 : Once all above steps are successfully. create spfile from pfile using below command, while database is down 

 

SQL> create spfile from pfile;

 

File created.

 

SQL>

 

Once spfile is created, start the database using startup command

 

Once database is started, you will be able to see two control files, and we are done with our task, i.e. multiplexing of control file

 

SQL> startup

ORACLE instance started.

 

Total System Global Area 3156725760 bytes

Fixed Size                  2179896 bytes

Variable Size            1795165384 bytes

Database Buffers         1342177280 bytes

Redo Buffers               17203200 bytes

Database mounted.

Database opened.

 

SQL> select name from v$controlfile;

 

NAME

-----------------------------------------------------------------

C:\APP\ORACLE\ORADATA\ASGARD\CONTROL01.CTL

C:\APP\ORACLE\ORADATA\CTRL\CONTROL02.CTL

 

SQL>

 

This completes DBA’s task of multiplexing of control file.

Read More