./runInstaller: line 106: /: No such file or directory

Junior DBA’s struggle sometimes and scratch their head without knowing the cause of simple errors.

Instead of trying to search for the error on internet,it is better to look at the error and identify the cause.

I try to execute runInstaller from a directory path to install the grid software where the software files actually exists but the name of directory has spaces when i copied from windows using winscp like this

[root@exadb2 ~]# cd /cell/GRIDDATABASE\ 11G\ 64\ BIT/

The installation file should be unzipped completely before copying the files to linux.So runInstaller consider the space as multiple folders and couldnot find the absolute path from the root folder.

Another point to keep in mind is appropriate permissions on the folders with ownership.Followed by the oracle inventory file.

Once i renamed the directory with single name,runInstaller was able to identify the oui from the absolute path.

[root@exadb2 GRIDDATABASE 11G 64 BIT]# cd ..
[root@exadb2 cell]# mv GRIDDATABASE\ 11G\ 64\ BIT/ software
[root@exadb2 cell]# cd software/

[root@exadb2 software]# chmod -R 777 *
[root@exadb2 software]# cd grid/
[root@exadb2 grid]# ./runInstaller

The user is root. Oracle Universal Installer cannot continue installation if the user is root.
: No such file or directory
[root@exadb2 grid]# su - oracle
[oracle@exadb2 ~]$
[oracle@exadb2 ~]$ cd /cell/software/grid/
[oracle@exadb2 grid]$ ./runInstaller
You do not have sufficient permissions to access the inventory '/oracle/orainventory'. Installation cannot continue. It is required that the primary group of the install user is same as the inventory owner group. Make sure that the install user is part of the inventory owner group and restart the installer.: No such file or directory
[oracle@exadb2 grid]$ vi /etc/oraInst.loc
[oracle@exadb2 grid]$
[oracle@exadb2 grid]$ su
Password:
[root@exadb2 grid]#  vi /etc/oraInst.loc
[root@exadb2 grid]#
[root@exadb2 grid]# su - oracle
[oracle@exadb2 ~]$ cd /cell/
base/         home/         lost+found/   orainventory/ software/
[oracle@exadb2 ~]$ cd /cell/software/grid/
[oracle@exadb2 grid]$ ./runInstaller
Starting Oracle Universal Installer...

Checking Temp space: must be greater than 120 MB.   Actual 9496 MB    Passed
Checking swap space: must be greater than 150 MB.   Actual 2047 MB    Passed
Checking monitor: must be configured to display at least 256 colors
Does hard parsing lead to sort in memory???

I was running a simple sql select query to fetch some details from a table.I generated an execution plan for the query to check for optimizers plan for the query processing

I observe some interesting details on the execution plan results

SQL> select * from t2 where id between 10000 and 20000;

20002 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10102 |   256K| 11723   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   | 10102 |   256K| 11723   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"<=20000 AND "ID">=10000)


Statistics
----------------------------------------------------------
         47  recursive calls
          0  db block gets
      44387  consistent gets
      43008  physical reads
          0  redo size
     455310  bytes sent via SQL*Net to client
      15214  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
          3  sorts (memory)  <--------
          0  sorts (disk)
      20002  rows processed

I can observe 3 sorts in PGA when i ran the query for the first time.

I was checking for the reason behind the sorts without order by or group by clause

When i ran the query for the second time,there was no sorting on memory!! and there are no recursive calls which means the datablocks are read from buffer cache directly!!

SQL> /

20002 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1513984157

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 10102 |   256K| 11723   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| T2   | 10102 |   256K| 11723   (1)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("ID"<=20000 AND "ID">=10000)


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
      44341  consistent gets
      43007  physical reads
          0  redo size
     455310  bytes sent via SQL*Net to client
      15214  bytes received via SQL*Net from client
       1335  SQL*Net roundtrips to/from client
          0  sorts (memory) <-----
          0  sorts (disk)
      20002  rows processed

This concludes that whenever a new sql run on a database,the optimizer searches for the previous hash value of the query.If hash value for the query doesnot exist on the library cache,the optimizer has to hard parse the query with new hash value.

Drop online redolog group

It is very dangerous when we deal with online redolog files in database which records the active transaction changes into it and needed for media ,block and crash recovery based on their states

We have four usual states of redologs in a typical oracle database

1)active 2)inactive 3)current 4) unused

active – require for instance crash recovery in case of failure and block recovery

inactive – not require for recovery and no logs written by lgwr

current – lgwr writing the logs to current group

unused – states fresh newly added group

If we want to drop an online redolog group , we have to first bring the particular group to inactive state.

SQL> select a.bytes/1024/1024,a.thread#,a.sequence#,a.thread#,a.status,b.member from v$log a,v$logfile b where a.GROUP# = b.GROUP#;

A.BYTES/1024/1024    THREAD#  SEQUENCE#    THREAD# STATUS           MEMBER
----------------- ---------- ---------- ---------- ---------------- ----------------------
               50          1        213          1 INACTIVE         /app01/oradata/orcl12x/redo03.log
               50          1        215          1 CURRENT          /app01/oradata/orcl12x/redo02.log
               50          1        214          1 INACTIVE         /app01/oradata/orcl12x/redo01.log

If the redologs are switching frequently due to heavy transactions,then issue the checkpoint to save the changes to the datafile.

SQL> alter system checkpoint;

System altered.

Oracle forces the checkpoint on the instance and saves the buffered blocks to the disk

we can drop the logfiles when checkpoint completes.

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------------- ------------- --------- ------------ --------- ----------
         1          1        214   52428800        512          1 NO
INACTIVE               3922477 26-AUG-20      3931944 26-AUG-20          0

         2          1        215   52428800        512          1 NO
CURRENT                3931944 26-AUG-20   2.8147E+14                    0

         3          1        213   52428800        512          1 NO
INACTIVE               3811949 26-AUG-20      3922477 26-AUG-20          0

We can drop either group 1 or group 3 as we need atleast minimum two redo groups for logging and add new groups.

If there is frequent log switch happen again,we can add two more groups before dropping the existing one and drop the old groups

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter database drop logfile group 3;
alter database drop logfile group 3
*
ERROR at line 1:
ORA-01567: dropping log 3 would leave less than 2 log files for instance
orcl12x (thread 1)
ORA-00312: online log 3 thread 1: '/app01/oradata/orcl12x/redo03.log'

Once we drop the logfile,we can add a logfile with more size

SQL> alter database add logfile group 1 '/app01/oradata/orcl12x/redo01.log
  2  ' size 200M;

Database altered.

Now to verify the newly added logfile

SQL> select * from v$log;

    GROUP#    THREAD#  SEQUENCE#      BYTES  BLOCKSIZE    MEMBERS ARC
---------- ---------- ---------- ---------- ---------- ---------- ---
STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME     CON_ID
---------------- ------------- --------- ------------ --------- ----------
         1          1          0  209715200        512          1 YES
UNUSED                       0                      0                    0

         2          1        215   52428800        512          1 NO
CURRENT                3931944 26-AUG-20   2.8147E+14                    0

         3          1        213   52428800        512          1 NO
INACTIVE               3811949 26-AUG-20      3922477 26-AUG-20          0
TNS:listener could not find available handler with matching protocol stack
[oracle@orcl12x ~]$ oerr ORA 12516
12516, 00000, "TNS:listener could not find available handler with matching protocol stack"
// *Cause: None of the known and available service handlers for the given
// SERVICE_NAME support the client's protocol stack: transport, session,
// and presentation protocols.
// *Action: Check to make sure that the service handlers (e.g. dispatchers)
// for the given SERVICE_NAME are registered with the listener, are accepting
// connections, and that they are properly configured to support the desired
// protocols.

Client use the user process and try to connect to the oracle database where PMON monitors and registers the incoming connections.

PMON updates the service to the listener every now and then ,when a new connection connect to the database.

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     300

PROCESSES is the parameter which allocates the maximum load for a connection.

When the load on the database increase and reaches to maximum utilization, PMON updates the listener using service update and listener assumes resource starvation even though there are enough processes to handle the requests.

So the listener marks the status as BLOCKED to stop incoming connections.You can see the status below. Sometimes it is pretty much frustating to spend our valuable time troubleshooting a silly error right 🙂

[oracle@orcl12x ~]$ lsnrctl services

LSNRCTL for Linux: Version 12.1.0.2.0 – Production on 15-JUL-2020 09:35:57

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

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=orcl12x)(PORT=1521)))
Services Summary…
Service “orcl12x” has 1 instance(s).
Instance “orcl12x”, status BLOCKED, has 1 handler(s) for this service…
Handler(s):
“DEDICATED” established:0 refused:0 state:ready
LOCAL SERVER
The command completed successfully

One of the reason for this error due to less process allocation in the database

  1. Increase the process to a higher value to avoid listener handler error!!!
SQL> select count(*) from v$process;

  COUNT(*)
----------
        22

SQL> alter system set processes=500 scope=spfile;

System altered.

Check the max utilization of the process.Follow this rule of thumb!!

if max_utilization = limit_value then go ahead and increase the process limit to a high value.So that application team would be happy always!!

SQL> select * from v$resource_limit where resource_name in ('processes');

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION INITIAL_AL
------------------------------ ------------------- --------------- ----------
LIMIT_VALU     CON_ID
---------- ----------
processes                                       22              24        300

2. Second reason might be due to JDBC connection pool errors.If the client is trying to connect to the database using java connection.

By default connection pooling is enabled if not,enable and increase the connection pooling using max pool size

Connection pooling reuses the connections to the database instead of establishing new connection.

How to avoid ORA-00018 maximum number of sessions exceeded

Oracle doc says

PROCESSES specifies the maximum number of operating system user processes that can simultaneously connect to Oracle. Its value should allow for all background processes such as locks, job queue processes, and parallel execution processes.

SESSIONS specifies the maximum number of sessions that can be created in the system. Because every login SESSIONS specifies the maximum number of sessions that can be created in the system. Because every login requires a session, this parameter effectively determines the maximum number of concurrent users in the system. You should always set this parameter explicitly to a value equivalent to your estimate of the maximum number of concurrent users, plus the number of background processes, plus approximately 10% for recursive sessions.

Number of sessions = (1.5 * PROCESSES) + 22

The total process and session limit on the database which says the number of users who can connect to the database and process which can be allocated to users

SQL> show parameter processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     300

SQL> show parameter session

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sessions                             integer     472

Identify the total sessions currently connected to database currently


SQL> select count(1) from v$session;

  COUNT(1)
----------
        52

SQL> select count(1) from v$process;

  COUNT(1)
----------
        63

Total active sessions on the database

SQL> select sid,serial#,username from v$session where status='ACTIVE';

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
        37      17176 SYS
        40      10985 SYS
        43      31501 SYS
        45      50043 SYS
        47      54359 SYS

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
        67      59264 C##GGUSRS
        73      31623 C##GGUSRS
        74      19460 C##GGUSRS
         85       6987 SYS

       SID    SERIAL# USERNAME
---------- ---------- ------------------------------
        89       9809 C##GGUSRS
        97      16161 C##GGUSRS
        99      25511 SYS

Identify the resource current and max utilized

SQL> select resource_name,current_utilization,max_utilization from v$resource_limit where resource_name in ('processes','sessions');

RESOURCE_NAME                  CURRENT_UTILIZATION MAX_UTILIZATION
------------------------------ ------------------- ---------------
processes                                       73              86
sessions                                        85             116

Calculate the load of the session history on database and allocate double the values of utilized sessions and processes parameter to avoid ORA-00018 maximum number of sessions exceeded.

Example: If my max utilization of processes are 500 ,it is always recommended to allocate 1000 for processes parameter likewise for sessions

SQL> show parameter processes;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
processes                            integer     500

SQL> alter system set processes=1000 scope= spfile
System altered.
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!!!