Month: June 2020

Why database remote connection fail?? ORA – 01017

I create a test user and grant basic connect privilege to the database

SQL>  create user test identified by test;

User created.

SQL> grant connect,resource to test;

Grant succeeded.

I configure the tns entry and listener entry on remote server

# tnsnames.ora Network Configuration File: /oracle/base/product/12.1.0/dbhome_1/network/admin/tnsnames.ora
# Generated by Oracle configuration tools.

LISTENER_ORCL =
  (ADDRESS = (PROTOCOL = TCP)(HOST = orcl)(PORT = 1521))


ORCL =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = orcl)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcl)
    )
  )
ORCLDG =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.56.212)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = orcldg)(UR = A)
    )
  )

LISTENER=(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=IPC)(KEY=LISTENER))))            # line added by Agent
# listener.ora Network Configuration File: /grid/base/product/12.1.0/grid/network/admin/listener.ora
# Generated by Oracle configuration tools.



SID_LIST_LISTENER =
  (SID_LIST =
    (SID_DESC =
      (SID_NAME = orcl)
      (ORACLE_HOME = /grid/base/product/12.1.0/grid)
      (PROGRAM = extproc)
    )
  )




ENABLE_GLOBAL_DYNAMIC_ENDPOINT_LISTENER=ON              # line added by Agent
VALID_NODE_CHECKING_REGISTRATION_LISTENER=SUBNET                # line added by Agent

I try to connect to the rman from remote client !!

[oracle@orcldg ~]$ rman target test@orcl

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jun 30 23:04:50 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

target database Password:
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-00554: initialization of internal recovery manager package failed
RMAN-04005: error from target database:
ORA-01017: invalid username/password; logon denied

I get ORA – 01017 error!!

However i given the correct username and password with right service name

I am able to connect to sqlplus commandline using the same credentials though!

[oracle@orcldg ~]$ sqlplus test@orcl

SQL*Plus: Release 12.1.0.2.0 Production on Tue Jun 30 22:58:54 2020

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

Enter password:

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics
and Real Application Testing options

This happens because the user is not updated in the password file with admin privilege.If you need to connect to rman you need sysdba privilege otherwise you cant access even from local server!!

SQL> select * from v$pwfile_users;

USERNAME                       SYSDB SYSOP SYSAS SYSBA SYSDG SYSKM     CON_ID
------------------------------ ----- ----- ----- ----- ----- ----- ----------
SYS                            TRUE  TRUE  FALSE FALSE FALSE FALSE          0
SYSDG                          FALSE FALSE FALSE FALSE TRUE  FALSE          0
SYSBACKUP                      FALSE FALSE FALSE TRUE  FALSE FALSE          0
SYSKM                          FALSE FALSE FALSE FALSE FALSE TRUE           0

SQL> show parameter login

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
remote_login_passwordfile            string      EXCLUSIVE

SQL> select * from dba_sys_privs where grantee='TEST';

no rows selected

I grant sysdba privilege to the test user!!

SQL> grant sysdba to test;

Grant succeeded.

My connection is successful!

[oracle@orcldg ~]$ rman target test@orcl

Recovery Manager: Release 12.1.0.2.0 - Production on Tue Jun 30 23:13:45 2020

Copyright (c) 1982, 2014, Oracle and/or its affiliates.  All rights reserved.

target database Password:
connected to target database: ORCL (DBID=1559282684)

RMAN>

Sometimes silly errors eat up our time a lot!!

RAC database hung analysis

If rac database instance is slow due to deadlock or other blocking ,we can use oradebug hang analyzer to deep dive into the root of cause.

Lets troubleshoot with a simulation

I create a sample table for testing purpose

SQL> CREATE TABLE regions
   ( region_id NUMBER
   CONSTRAINT region_id_nn NOT NULL
   , region_name VARCHAR2(25)
   );
CREATE UNIQUE INDEX reg_id_pk
         ON regions (region_id);
ALTER TABLE regions
         ADD ( CONSTRAINT reg_id_pk
   PRIMARY KEY (region_id)
   ) ;  2    3    4    5


Table created.

SQL>   2
Index created.

SQL>   2    3    4


Table altered.

I insert rows on node 1 and donot commit the changes

SQL> SQL> SQL> SQL>
SQL>
SQL> INSERT INTO regions VALUES
   ( 1
   , 'Europe'
   );
INSERT INTO regions VALUES
   ( 2
   , 'Americas'
   );
INSERT INTO regions VALUES
   ( 3
   , 'Asia'
   );
INSERT INTO regions VALUES
   ( 4
   , 'Middle East and Africa'
   );  2    3    4

1 row created.

SQL>   2    3    4
1 row created.

SQL>   2    3    4
1 row created.

SQL>   2    3    4
1 row created.

At the same time i insert the same rows on the same table with different user but from node 2!

SQL> select name from v$database;

NAME
---------
RACDB

SQL> select instance_name from v$instance;

INSTANCE_NAME
----------------
RACDB2

SQL> show user
USER is "TEST"
SQL> INSERT INTO sys.regions VALUES
   ( 1
   , 'Europe'
   );
INSERT INTO sys.regions VALUES
   ( 2
   , 'Americas'
   );
INSERT INTO sys.regions VALUES
   ( 3
   , 'Asia'
   );
INSERT INTO sys.regions VALUES
   ( 4
   , 'Middle East and Africa'
   );  2    3    4

The database is hung as node 1 blocks node 2 to insert rows

Enable the hang analyzer on one of the RAC nodes to generate trace files for analyzing

SQL> oradebug setmypid
oStatement processed.
SQL> radebug unlimit
oradebugStatement processed.
SQL> oradebug setmypid
SP2-0734: unknown command beginning "oradebugor..." - rest of line ignored.
SQL> oradebug setmypid
Statement processed.
SQL> oradebug unlimit
Statement processed.
SQL> oradebug setinst all
Statement processed.
SQL> oradebug -g all hanganalyze 3
Hang Analysis in /grid/base/diag/rdbms/racdb/RACDB1/trace/RACDB1_diag_3917.trc
SQL> oradebug -g all hanganalyze 3
Hang Analysis in /grid/base/diag/rdbms/racdb/RACDB1/trace/RACDB1_diag_3917.trc
SQL> oradebug -g all hanganalyze 3
Hang Analysis in /grid/base/diag/rdbms/racdb/RACDB1/trace/RACDB1_diag_3917.trc

In the hang trace file ,we can find the diagnostic details of RAC1 blocking RAC2 with wait event ,blocking id,waiting time etc which is pretty much valuable information!!

Chains most likely to have caused the hang:
 [a] Chain 1 Signature: <not in a wait><='rdbms ipc reply'<='enq: TX - row lock contention'
     Chain 1 Signature Hash: 0x7ba3a1e1

===============================================================================
Non-intersecting chains:

-------------------------------------------------------------------------------
Chain 1:
-------------------------------------------------------------------------------
    Oracle session identified by:
    {
                instance: 2 (racdb.racdb2)
                   os id: 5922
              process id: 40, oracle@RAC2 (TNS V1-V3)
              session id: 60
        session serial #: 67
    }
    is waiting for 'enq: TX - row lock contention' with wait info:
    {
                      p1: 'name|mode'=0x54580004
                      p2: 'usn<<16 | slot'=0x6000e
                      p3: 'sequence'=0x32b
            time in wait: 5 min 50 sec
           timeout after: never
                 wait id: 97
               blocking: 0 sessions
             current sql: INSERT INTO sys.regions VALUES
   ( 1
   , 'Europe'
   )
             short stack: ksedsts()+265<-ksdxfstk()+19<-ksdxcb()+1646<-sspuser()+100<-semtimedop()+36<-sskgpwwait()+202<-skgpwwait()+138<-ksliwat()+1602<-kslwaitctx()+138<-kjusuc()+6900<-ksipgetctxi()+1641<-ksqcmi()+23174<-ksqgtlctx()+3204<-ksqgelctx()+469<-ktuGetTxForXid()+95<-ktcwit1()+272<-ktbgtl0()+958<-kdiins0()+46577<-kdiinsp()+61<-kauxsin()+1634<-qesltcLoadIndexList()+718<-qesltcLoadIndexes()+43<-qerltcSimpleSingleInsRowCBK()+65<-qerltcSingleRowLoad()+228<-qerltcFetch()+301<-insexe()+567<-opiexe()+4953<-kpoal8()+1811<-opiodr()+962<-
            wait history:
              * time between current wait and wait #1: 0.002054 sec
              1.       event: 'SQL*Net message from client'
                 time waited: 26.277790 sec
                     wait id: 96              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #1 and #2: 0.000002 sec
              2.       event: 'SQL*Net message to client'
                 time waited: 0.000001 sec
                     wait id: 95              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
              * time between wait #2 and #3: 0.000031 sec
              3.       event: 'SQL*Net message from client'
                 time waited: 0.000146 sec
                     wait id: 94              p1: 'driver id'=0x62657100
                                              p2: '#bytes'=0x1
    }
    and is blocked by
 => Oracle session identified by:
    {
                instance: 1 (racdb.racdb1)
                   os id: 11456


oradebug is the advisable tool in times of performance issues!!

Until the session 1 in node 1 releases the lock from the rows in the table,the other session couldnot modify the rows in the same table.So if the user doesnot commit and flush the block changes to the disk ,other sessions have to sit idle and wait in the queue!!

IS OCR backup automatic?

Oracle cluster registry is a crucial file which stores the cluster configuration information and all the node database instance details.

We can choose separate diskgroups for OCR as candidate disk to store it on ASM diskgroup during installation

Without OCR file ,the clusterware couldnot identify the instance information of the neighbouring nodes!!

CRS daemon stores the configuration of OCR and takes the automatic backup of OCR for every 4 hours on the RAC

OCR architecture:

In 11gr2 and 12c versions,OCR can have upto five mirrored copies

The storage where datafiles reside should be accessed by all the nodes.

The CRS daemon is responsible for writing the data and disk I/O from the cache in the memory

To get the details of the previous backups we can use the ocrconfig!!

[oracle@RAC1 ~]$ ocrconfig -showbackup

rac2     2019/12/16 15:49:43     /grid/11.2.0/grid/cdata/RAC-scan/backup00.ocr

rac2     2019/12/16 11:47:27     /grid/11.2.0/grid/cdata/RAC-scan/backup01.ocr

rac3     2019/11/08 02:15:53     /grid/11.2.0/grid/cdata/RAC-scan/backup02.ocr

rac2     2019/12/16 11:47:27     /grid/11.2.0/grid/cdata/RAC-scan/day.ocr

rac2     2019/12/16 11:47:27     /grid/11.2.0/grid/cdata/RAC-scan/week.ocr

rac2     2019/07/16 16:03:14     /grid/backup_20190716_160314.ocr

rac2     2019/07/16 13:41:40     /grid/11.2.0/grid/cdata/RAC-scan/backup_20190716_134140.ocr

rac2     2019/07/16 13:12:30     /grid/11.2.0/grid/cdata/RAC-scan/backup_20190716_131230.ocr

rac2     2019/07/16 13:07:47     /grid/11.2.0/grid/cdata/RAC-scan/backup_20190716_130747.ocr

To change the backup of OCR to a dedicated directory

[oracle@RAC1 ~]$ ocrconfig -backuploc /home/oracle

To list the OCR and its mirror disks and to verify the integrity of each file

[oracle@RAC1 ~]$ ocrcheck
Status of Oracle Cluster Registry is as follows :
         Version                  :          3
         Total space (kbytes)     :     262120
         Used space (kbytes)      :       3732
         Available space (kbytes) :     258388
         ID                       : 2047356075
         Device/File Name         :       +EXT
                                    Device/File integrity check succeeded

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

                                    Device/File not configured

         Cluster registry integrity check succeeded

         Logical corruption check bypassed due to non-privileged user

To verify the integrity from all the nodes!

[oracle@RAC1 ~]$ cluvfy comp ocr -n all -verbose

Verifying OCR integrity

Checking OCR integrity...

Checking the absence of a non-clustered configuration...
All nodes free of non-clustered, local-only configurations


ASM Running check passed. ASM is running on all specified nodes

Checking OCR config file "/etc/oracle/ocr.loc"...

OCR config file "/etc/oracle/ocr.loc" check successful


Disk group for ocr location "+EXT" available on all the nodes


NOTE:
This check does not verify the integrity of the OCR contents. Execute 'ocrcheck' as a privileged user to verify the contents of OCR.

OCR integrity check passed

Verification of OCR integrity was successful.

To take manual backup of OCR

[oracle@RAC1 ~]$ ocrdump <filename>

Sometimes there can be situations to take manual backups of OCR though it is automatic!!

How to connect mysql server remotely?

1)Connect to database

root@MySql:/# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 29
Server version: 10.3.22-MariaDB-0ubuntu0.19.10.1 Ubuntu 19.10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

2)Create new user and grant privileges

You have to select the ipaddress of host

Check host ip

root@MySql:/# ifconfig

Flush the privileges to make the changes permanent on disk

MariaDB [(none)]> create user 'test'@'10.0.0.5' identified by 'password';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> grant all on *.* to 'test'@'10.0.0.5';
Query OK, 0 rows affected (0.000 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.000 sec)

Check the user status

MariaDB [(none)]> select user,host from mysql.user;
+--------+----------------+
| user   | host           |
+--------+----------------+
| test   | 10.0.0.5       |
+--------+----------------+

Connect remotely by mysql workbench

Database –> Connect to database

Click on the plus symbol to connect to database

Fill the connection details and give the password to connect

You are successfully connected from remote client!!!

Migrate tables from mariadb to oracle

There are very few ways to migrate tables from mariadb(mysql) to oracle.

1)We can use mysql workbench and sql developer to migrate but there are efficient tools which can perform it in faster way.

1)One of the tool used is MysqltoOracle

You can download the tool from this link.

https://www.withdata.com/mysqltooracle/

MysqltoOracle is an open source tool which ease data migration from multiple cross platform databases.

Mariadb version : Server version: 10.3.22-MariaDB

Oracle version : 12.1.0.2.0

SOURCE : MySql –> PLATFORM : Mariadb –> TABLE : dba.projects

DESTINATION : orcl –> PLATFORM : Oracle –> TABLE : hr.projects

MariaDB [(none)]> use TEST
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
MariaDB [TEST]> show tables;
+----------------+
| Tables_in_TEST |
+----------------+
| contacts       |
| projects       |
+----------------+
2 rows in set (0.000 sec)


================================================================================
SQL> select table_name from dba_tables where owner='HR';

TABLE_NAME
--------------------------------------------------------------------------------
COUNTRIES
JOB_HISTORY
EMPLOYEES
JOBS
DEPARTMENTS
LOCATIONS
REGIONS
CONTACTS

SQL> !echo $ORACLE_SID
orcl

1)Enter the login details of mariadb and connect to host.

Enter the host IP address followed by mysql default port 3306 and user credentials.

If you face issues to connect to mysql database remotely,you can refer this post!!

https://wordpress.com/block-editor/post/alphaoragroup.com/441

2) Click on Oracle(target) and give the login details of oracle

choose TCP/IP Connect

Give the server IP on server field

Default oracle port is 1521

Give the username of database and password

Click on connect and ok

Choose the wizard as per your requiement

You can follow the steps which are demonstrated from this link for more understanding of table migration.

https://www.withdata.com/mysqltooracle/convert-table.html

1)Choose the schema

2)Select the tables to migrate and choose the import type to either append/replace/Struct Only based on the requirement.

Verify the tables which should be mapped

After performing the steps you can validate the tables from your destination

The tables are migrated to oracle successfully which has been verified!!!

Mariadb installation in Ubuntu

Ubuntu version : Server version: 10.3.22-MariaDB-0ubuntu0.19.10.1 Ubuntu 19.10

Mariadb version : mariadb-server-10.3

To install mariadb on ubuntu using apt-get package

apt-get install mariadb mariadb-server -Y

List of installed mariadb packages

root@MySql:/home/kishan# apt list --installed|grep mariadb

WARNING: apt does not have a stable CLI interface. Use with caution in scripts.

mariadb-client-10.3/eoan-updates,eoan-security,now 1:10.3.22-0ubuntu0.19.10.1 amd64 [installed,automatic]
mariadb-client-core-10.3/eoan-updates,eoan-security,now 1:10.3.22-0ubuntu0.19.10.1 amd64 [installed,automatic]
mariadb-common/eoan-updates,eoan-updates,eoan-security,eoan-security,now 1:10.3.22-0ubuntu0.19.10.1 all [installed,automatic]
mariadb-server-10.3/eoan-updates,eoan-security,now 1:10.3.22-0ubuntu0.19.10.1 amd64 [installed,automatic]
mariadb-server-core-10.3/eoan-updates,eoan-security,now 1:10.3.22-0ubuntu0.19.10.1 amd64 [installed,automatic]
mariadb-server/eoan-updates,eoan-updates,eoan-security,eoan-security,now 1:10.3.22-0ubuntu0.19.10.1 all [installed]

To get a brief info on the mariadb package available in the repository

root@MySql:/home/kishan# apt-cache show mariadb-server
Package: mariadb-server
Architecture: all
Version: 1:10.3.22-0ubuntu0.19.10.1
Priority: optional
Section: universe/database
Source: mariadb-10.3
Origin: Ubuntu
Maintainer: Ubuntu Developers <ubuntu-devel-discuss@lists.ubuntu.com>
Original-Maintainer: Debian MySQL Maintainers <pkg-mysql-maint@lists.alioth.debian.org>
Bugs: https://bugs.launchpad.net/ubuntu/+filebug
Installed-Size: 66
Depends: mariadb-server-10.3 (>= 1:10.3.22-0ubuntu0.19.10.1)
Filename: pool/universe/m/mariadb-10.3/mariadb-server_10.3.22-0ubuntu0.19.10.1_all.deb
Size: 12700
MD5sum: 825b924e52bfca65f7f65b6fb91ff992
SHA1: 6fb3c9ec5c2de7ba17824d7915f8bd7182f55251
SHA256: 10a6ee64aea66cbdef81f029df2d7503f53c1bb3fbc6b1e278106814e8601d53
Homepage: https://mariadb.org/
Description-en: MariaDB database server (metapackage depending on the latest version)
 This is an empty package that depends on the current "best" version of
 mariadb-server (currently mariadb-server-10.3), as determined by the MariaDB
 maintainers. Install this package if in doubt about which MariaDB
 version you need. That will install the version recommended by the
 package maintainers.
 .
 MariaDB is a fast, stable and true multi-user, multi-threaded SQL database
 server. SQL (Structured Query Language) is the most popular database query
 language in the world. The main goals of MariaDB are speed, robustness and
 ease of use.
Description-md5: 47753d361ef73aaa0d808a49d4717d3f


Start Mariadb services

1)Check the status of the service

root@MySql:/home/kishan# systemctl status mariadb
● mariadb.service - MariaDB 10.3.22 database server
   Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled)
   Active: active (running) since Sun 2020-06-07 12:10:15 IST; 39min ago
     Docs: man:mysqld(8)
           https://mariadb.com/kb/en/library/systemd/
 Main PID: 721 (mysqld)
   Status: "Taking your SQL requests now..."
    Tasks: 30 (limit: 2320)
   Memory: 93.5M
   CGroup: /system.slice/mariadb.service
           └─721 /usr/sbin/mysqld

Jun 07 12:10:07 MySql systemd[1]: Starting MariaDB 10.3.22 database server...
Jun 07 12:10:11 MySql mysqld[721]: 2020-06-07 12:10:11 0 [Note] /usr/sbin/mysqld (mysqld 10.3.22-MariaDB-0ubuntu0.19.10.1) startin
Jun 07 12:10:15 MySql systemd[1]: Started MariaDB 10.3.22 database server.

2)If service is not active,then start the service manually

root@MySql:/home/kishan# systemctl start mariadb

3)The service is up and running.Try to connect to mariadb using root

root@MySql:/home/kishan# mysql

Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.3.22-MariaDB-0ubuntu0.19.10.1 Ubuntu 19.10

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

4)List the databases

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| TEST               |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
4 rows in set (0.061 sec)

5)check the current user

MariaDB [(none)]> select current_user();
+----------------+
| current_user() |
+----------------+
| root@localhost |
+----------------+
1 row in set (0.000 sec)

6)check the list of users and host details

MariaDB [(none)]> select user,host from mysql.user;
+--------+----------------+
| user | host |
+--------+----------------+
| kishan | localhost|
| root | localhost|
| test | localhost|

These are basic things to install and practice mariadb!!