ORA-39142: incompatible version number 5.1 in dump file

Recently I faced error:

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 5.1 in dump file 

while making import table data on Oracle Database 11g.

Dump file was made from Oracle Database 12c (12.2.0.1.0) with parameter compatible to 12.2.0

SQL> show parameter compatible
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
compatible                           string      12.2.0
noncdb_compatible                    boolean     FALSE
 
SQL> 

and during impdp on Oracle Database 11g (11.2.0.4.0) with compatible parameter to 11.2.0.4.0 occurred error:

ORA-39001: invalid argument value
ORA-39000: bad dump file specification
ORA-39142: incompatible version number 5.1 in dump file "xxx.dmp"

Data Pump Import can always read Data Pump dump file sets created by older releases of the database but In a downgrade situation, when the target release of a Data Pump-based migration is lower than the source, the VERSION parameter should be explicitly specified to be the same version as the target. An exception is when the target release version is the same as the value of the COMPATIBLE initialization parameter on the source system; then VERSION does not need to be specified. In general however, Data Pump import cannot read dump file sets created by an Oracle release that is newer than the current release unless the VERSION parameter is explicitly specified
So solution is to specify value of target database compatible parameter in version parameter in expdp while exporting table.

for my situation on expdp on 12c database I specify version=11.2 and impdp on 11g run successfully.

on Oracle Database 12c (12.2.0.1.0) with compatible 12.2.0

expdp test_user/test_pass version=11.2 tables=test_table CONTENT=DATA_ONLY directory=TEST_DUMP dumpfile=test.dmp logfile=test_exp.log

on Oracle Database 11g (11.2.0.4.0) with compatible 11.2.0.4.0

impdp test_user/test_pass tables=test_table CONTENT=DATA_ONLY directory=TEST_DUMP dumpfile=test.dmp logfile=test_imp.log

How to find BLOB/CLOB columns size in Oracle tables

Here I'll show you how to calculate blob, clob columns sizes in tables.
1) Find the size of individual rows with the CLOB/BLOB column. DBMS_LOB.GETLENGTH returns the number of characters (bytes) in the CLOB/BLOB column.

SELECT DBMS_LOB.GetLength("BLOB_COLUMN") / 1024 / 1024 AS SizeMB
  FROM SCHEMA_NAME.TABLE_NAME_WITH_BLOB;

2) find the size of all rows with BLOB/CLOB for particular tables

SELECT SUM(DBMS_LOB.GetLength("BLOB_COLUMN")) / 1024 / 1024 AS SizeMB
  FROM SCHEMA_NAME.TABLE_NAME_WITH_BLOB

3) If you want to find all BLOB/CLOB sizes in your oracle database for a particular schema here is code:

DECLARE
    v_Size NUMBER := 0;
    v_TotalSize NUMBER := 0;
BEGIN
    FOR v_Rec IN (
                  SELECT OWNER || '.' || TABLE_NAME || '.' || COLUMN_NAME AS TableAndColumn,
                     'SELECT SUM(DBMS_LOB.GetLength("' || COLUMN_NAME || '"))/1024/1024 AS SizeMB FROM ' || OWNER || '.' || TABLE_NAME AS sqlstmt
                  FROM DBA_TAB_COLUMNS
                  WHERE DATA_TYPE LIKE '_LOB'
                        AND OWNER LIKE '%SCHEMA_NAME%' -- SCHEMA_NAME
                        AND TABLE_NAME NOT LIKE 'BIN$%' -- EXCLUDE DELETED TABLES FROM RECYCLE_BIN
                 )
    LOOP
        EXECUTE IMMEDIATE v_Rec.sqlstmt INTO v_Size;
 
        DBMS_OUTPUT.PUT_LINE (v_Rec.TableAndColumn || ' size in MB is ' || ROUND(NVL(v_Size,0),2));
        v_TotalSize := v_TotalSize + NVL(v_Size,0);
    END LOOP;
 
    DBMS_OUTPUT.PUT_LINE ('Total size in MB is ' || ROUND(v_TotalSize,2));
END;

Disk "DATA01" defines an unmarked device Dropping disk: failed

Hi,

Oracleasm deletedisk failed with error:

/usr/sbin/oracleasm deletedisk DATA01
Disk "DATA01" defines an unmarked device
Dropping disk: failed
Unable to delete disk "DATA01"

Here I'll show you step by step how to solve this problem :

/usr/sbin/oracleasm deletedisk DATA01
Disk "DATA01" defines an unmarked device
Dropping disk: failed
Unable to delete disk "DATA01"
-bash-3.2$ /usr/sbin/oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Cleaning disk "DATA01"
Scanning system for ASM disks...
-bash-3.2$ /usr/sbin/oracleasm deletedisk DATA01
Disk "DATA01" defines an unmarked device
Dropping disk: failed
Unable to delete disk "DATA01"
-bash-3.2$ /etc/init.d/oracleasm scandisks
-bash: /etc/init.d/oracleasm: Permission denied
-bash-3.2$ 
-bash-3.2$ 
-bash-3.2$ 
-bash-3.2$ id
uid=501(grid) gid=1000(oinstall) groups=1000(oinstall),1200(asmadmin),1300(asmdba),1301(asmoper)
-bash-3.2$ 
-bash-3.2$ 
-bash-3.2$ logout
[root@test-db ~]# 
[root@test-db ~]# 
[root@test-db ~]# 
[root@test-db ~]# 
[root@test-db ~]# 
[root@test-db ~]# 
[root@test-db ~]# 
[root@test-db ~]# 
[root@test-db ~]# 
[root@test-db ~]# /usr/sbin/oracleasm listdisks
DATA01
DATA02
[root@test-db ~]# 
[root@test-db ~]# 
[root@test-db ~]# 
[root@test-db ~]# /usr/sbin/oracleasm deletedisk DATA01
Disk "DATA01" defines an unmarked device
Dropping disk: done
[root@test-db ~]# /usr/sbin/oracleasm listdisks
DATA02
[root@test-db ~]# /usr/sbin/oracleasm scandisks
Reloading disk partitions: done
Cleaning any stale ASM disks...
Scanning system for ASM disks...
[root@test-db ~]# /usr/sbin/oracleasm listdisks
DATA02

Problem was that I tried to delete disk with grid user and it doesn't have permission to do that, therefore delete must use with root user.

How to Check and Modify Default Attributes of Tables and Indexes Partitions

Sometimes there is situation when you need to modify default attributes for tables, tables partitions, index or index partitions in Oracle database. When you modify default attributes, the new attributes affect only future partitions, or subpartitions, that are created. The default values can still be specifically overridden when creating a new partition or subpartition. Here I'll show you how to do these things:

Check Default atributes for partitioned Table :

SQL> SELECT DEF_TABLESPACE_NAME FROM DBA_PART_TABLES WHERE TABLE_NAME='TABLE_NAME';

Check Default atributes for partitioned Index :

SQL> SELECT DEF_TABLESPACE_NAME FROM DBA_PART_INDEXES WHERE INDEX_NAME='INDEX_NAME';

Modify Default Attributes for a Table :

SQL> ALTER TABLE OWNER.TABLE_NAME MODIFY DEFAULT ATTRIBUTES TABLESPACE TABLESPACE_NAME;

Modify Default Attributes for a Index :

SQL> ALTER INDEX OWNER.INDEX_NAME MODIFY DEFAULT ATTRIBUTES TABLESPACE TABLESPACE_NAME;

Modify Default Attributes for a Table partitions :

SQL> ALTER TABLE OWNER.TABLE_NAME MODIFY DEFAULT ATTRIBUTES FOR PARTITION PARTITION_NAME TABLESPACE TABLESPACE_NAME;

Modify Default Attributes for a Index partitions :

SQL> ALTER INDEX OWNER.INDEX_NAME MODIFY DEFAULT ATTRIBUTES FOR PARTITION PARTITION_NAME TABLESPACE TABLESPACE_NAME;

How to resolve ORA-01111 ORA-01110 ORA-01157 in a physical standby database

The physical standby recovery process was terminated with the error below:

Standby Crash Recovery aborted due to error 1111.
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb1/trace/testdb1_ora_61878.trc:
ORA-01111: name for data file 8 is unknown - rename to correct file
ORA-01110: data file 8: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00008'
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01111: name for data file 8 is unknown - rename to correct file
ORA-01110: data file 8: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00008'
Completed Standby Crash Recovery.
Abort recovery for domain 0
Errors in file /u01/app/oracle/diag/rdbms/testdb/testdb1/trace/testdb1_ora_61878.trc:
ORA-10458: standby database requires recovery
ORA-01157: cannot identify/lock data file 8 - see DBWR trace file
ORA-01111: name for data file 8 is unknown - rename to correct file
ORA-01110: data file 8: '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00008'

How to resolve the errors so that the recovery process could proceed so that the physical standby could be in sync with the primary database?
Solution:
perform the following steps on the physical standby database to resolve the issue
On standby database run following command to find missing files

SQL> select FILE#||','||ERROR||','||STATUS||','||RECOVER||','||FUZZY||','||NAME 
from v$datafile_header where ERROR='FILE MISSING';

cancel the managed recovery

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Set standby_file_management=manual

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL;

If the physical standby database is RAC, then please make the change to all standby instances

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=MANUAL SID='*';

Rename the unknown datafile 8

alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00008' as 
'absolute path/real datafile name' ;

For ASM

alter database create datafile '/u01/app/oracle/product/11.2.0/dbhome_1/dbs/UNNAMED00008' as 
'+DG01' size 'actual size of datafile on Primary';

Set standby_file_management=auto

SQL>ALTER SYSTEM SET STANDBY_FILE_MANAGEMENT=AUTO SID='*';

Start managed recovery

SQL>ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;

Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated

In Oracle Database 12c command

SQL> alter database recover managed standby database disconnect from session using current logfile;

is deprecated and if you use it in alert log file you'll see warnings about it:

Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
Warning: ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE has been deprecated.
ALTER DATABASE RECOVER  managed standby database disconnect from session using current logfile  

Instead of this command we can use next command in oracle database 12c

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT NODELAY;

To start apply services on a physical standby database, ensure the physical standby database is started and mounted and then start Redo Apply as follows:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

This also automatically enables real-time apply provided the standby database is configured with a standby redo log and is in ARCHIVELOG mode.
Redo Apply can be run either as a foreground session or as a background process. To start Redo Apply in the foreground, issue the following SQL statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE;

If you start a foreground session, control is not returned to the command prompt until recovery is canceled by another session.
To start Redo Apply in the background, include the DISCONNECT keyword on the SQL statement. For example:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT;

or

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING ARCHIVED LOGFILE DISCONNECT;

This statement starts a detached server process and immediately returns control to the user. While the managed recovery process is performing recovery in the background, the foreground process that issued the RECOVER statement can continue performing other tasks. This command does not disconnect the current SQL session.

Queries on DBA_FREE_SPACE are Slow

One of our production database appeared that tablespaces size monitoring scirpt was very slow, we are using dba_free_space view in our script, Database version is 12.2.0.1.0.
For solving problem I started with gathering statistics:

begin
  dbms_stats.gather_system_stats();

end;
/
begin
  dbms_stats.gather_fixed_objects_stats();
  
end;
/
begin
  dbms_stats.gather_dictionary_stats();
  
end;

After that  queries on dba_free_space was little bit faster, but still slow.
Than I tried to view really what was behind dba_free_space and found that it also queries recyclebin and if in your recyclebin is many objects than it will be cause of slow queries on dba_free_space :

select TEXT from dba_views where view_name='DBA_FREE_SPACE';

select ts.name, fi.file#, f.block#,
       f.length * ts.blocksize, f.length, f.file#
from sys.ts$ ts, sys.fet$ f, sys.file$ fi
where ts.ts# = f.ts#
  and f.ts# = fi.ts#
  and f.file# = fi.relfile#
  and ts.bitmapped = 0
union all
select
       ts.name, fi.file#, f.ktfbfebno,
       f.ktfbfeblks * ts.blocksize, f.ktfbfeblks, f.ktfbfefno
from sys.ts$ ts, sys.x$ktfbfe f, sys.file$ fi
where ts.ts# = f.ktfbfetsn
  and f.ktfbfetsn = fi.ts#
  and f.ktfbfefno = fi.relfile#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
  and bitand(ts.flags, 4503599627370496) <> 4503599627370496
union all
select
       ts.name, fi.file#, u.ktfbuebno,
       u.ktfbueblks * ts.blocksize, u.ktfbueblks, u.ktfbuefno
from sys.recyclebin$ rb, sys.ts$ ts, sys.x$ktfbue u, sys.file$ fi
where ts.ts# = rb.ts#
  and rb.ts# = fi.ts#
  and u.ktfbuefno = fi.relfile#
  and u.ktfbuesegtsn = rb.ts#
  and u.ktfbuesegfno = rb.file#
  and u.ktfbuesegbno = rb.block#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
  and bitand(ts.flags, 4503599627370496) <> 4503599627370496
union all
select ts.name, fi.file#, u.block#,
       u.length * ts.blocksize, u.length, u.file#
from sys.ts$ ts, sys.uet$ u, sys.file$ fi, sys.recyclebin$ rb
where ts.ts# = u.ts#
  and u.ts# = fi.ts#
  and u.segfile# = fi.relfile#
  and u.ts# = rb.ts#
  and u.segfile# = rb.file#
  and u.segblock# = rb.block#
  and ts.bitmapped = 0
union all
select
       ts.name, fi.file#, f.extent_start,
       (f.extent_length_blocks_2K /(ts.blocksize/2048)) * ts.blocksize,
       (f.extent_length_blocks_2K / (ts.blocksize/2048)), fi.relfile#
from sys.ts$ ts, sys.new_lost_write_extents$ f, sys.file$ fi
where ts.ts# = f.extent_datafile_tsid
  and f.extent_datafile_tsid = fi.ts#
  and ts.bitmapped <> 0 and ts.online$ in (1,4) and ts.contents$ = 0
  and bitand(ts.flags, 4503599627370496) = 4503599627370496

Solution: just purge dba_recyclebin and queries will fly on dba_free_space

purge dba_recyclebin;

12C Database reports KFOD Error During Oracle Exadata Database Machine Discovery in OEM: "DiscoveryWarning: kfod returns status 126"

During Oracle Exadata Database Machine Discovery in Oracle Enterprise Manager (OEM) appears error:

The following errors are found during discovery. 
Please examine the error messages and the targets being discovered if any. 
DiscoveryWarning: kfod returns status 126. 
Please check the OSSCONF environment variable and make sure that 
cellinit.ora and cellip.ora are readable by the EM agent. 
DiscoveryWarning: /u01/app/oracle/product/12.2.0.1/dbhome_1/bin/kfod: line 22: 
/u01/agent12c/agent12c/sysman/emd/%ORACLE_HOME%/bin/kfod.bin: No such file or directory 
/u01/app/oracle/product/12.2.0.1/dbhome_1/bin/kfod: line 22: 
exec: /u01/agent12c/agent12c/sysman/emd/%ORACLE_HOME%/bin/kfod.bin: 
cannot execute: No such file or directory

cause:This is a known bug in the kfod wrapper script: Unpublished BUG 19682778 - 121021GIPSU: KFOD FILE IN RAC HOME NOT CORRECT AFTER APPLYING GIPSU
Workaround:
- Change the first line in the kfod wrapper script "$OHOME/bin/kfod.bin"
       OHOME=%ORACLE_HOME%
       to
       OHOME=<DB_Home>      Eg. OHOME=/u01/app/oracle/product/12.2.0.1/dbhome_1

Your kfod file must look like this:
cd $ORACLE_HOME
pwd
/u01/app/oracle/product/12.2.0.1/dbhome_1
cd bin/

cat kfod

#!/bin/sh
#
# $Header: opsm/utl/kfod.sbs
#
# kfod
#
# Copyright (c) 2000, 2014, Oracle and/or its affiliates. All rights reserved.
#
#    NAME
#      kfod - KFOD utility
#
#    DESCRIPTION
#      This is a script which is a wrapper on top of kfod.bin
#      This script is only shipped to the DATABASE home
#
#    MODIFIED   (MM/DD/YY)
#        samjo   05/21/14 - Creation
OHOME=/u01/app/oracle/product/12.2.0.1/dbhome_1
ORACLE_HOME=${OHOME}
export ORACLE_HOME

exec $OHOME/bin/kfod.bin "$@"

How to delete old files in linux based system timestamp

In this blog post I'll show you how to delete old files based system timestamp, the system timestamp means when a file was created OR when it was last modified. for example if you want to find and delete files older than 30 days in your linux machine you need next commands :
to find list of files which are older than specified days for example 30

find /directory/path/ -type f -mtime +30 -print

to delete files older than specified days for example 30

find /directory/path/ -type f -mtime +30 -exec rm -f {} \; -exec printf "." \;

Here is video of these procedures - How to delete old files in linux How to delete old files in linux based system timestamp

Let's create small demo and delete some files from some directory which are older than 3 days :

cd /u01/app/oracle/diag/rdbms/testdb/testdb/alert/

[oracle@testdb alert]$ ls -lrt
total 1531540
-rw-r----- 1 oracle asmadmin 10485828 Oct 25  2016 log_115.xml
-rw-r----- 1 oracle asmadmin 10485847 Oct 26  2016 log_116.xml
-rw-r----- 1 oracle asmadmin 10485837 Oct 26  2016 log_117.xml
-rw-r----- 1 oracle asmadmin 10485885 Jun  6  2017 log_118.xml
-rw-r----- 1 oracle asmadmin 10485826 Jun  6  2017 log_119.xml
-rw-r----- 1 oracle asmadmin 10485965 Jun  7  2017 log_120.xml
-rw-r----- 1 oracle asmadmin 10485877 Jun  7  2017 log_121.xml
-rw-r----- 1 oracle asmadmin 10485788 Jun  8  2017 log_122.xml
-rw-r----- 1 oracle asmadmin 10485821 Jun  8  2017 log_123.xml
-rw-r----- 1 oracle asmadmin 10485835 Jun  8  2017 log_124.xml
-rw-r----- 1 oracle asmadmin 10485975 Jun  9  2017 log_125.xml
-rw-r----- 1 oracle asmadmin 10485870 Jun  9  2017 log_126.xml
-rw-r----- 1 oracle asmadmin 10486074 Jun 10  2017 log_127.xml
-rw-r----- 1 oracle asmadmin 10485926 Jun 10  2017 log_128.xml
-rw-r----- 1 oracle asmadmin 10485938 Jun 10  2017 log_129.xml
-rw-r----- 1 oracle asmadmin 10486197 Jun 11  2017 log_130.xml
-rw-r----- 1 oracle asmadmin 10485909 Jun 11  2017 log_131.xml
-rw-r----- 1 oracle asmadmin 10486043 Jun 12  2017 log_132.xml
-rw-r----- 1 oracle asmadmin 10485964 Jun 12  2017 log_133.xml
-rw-r----- 1 oracle asmadmin 10485806 Jun 12  2017 log_134.xml
-rw-r----- 1 oracle asmadmin 10485807 Jun 13  2017 log_135.xml
-rw-r----- 1 oracle asmadmin 10485863 Jun 13  2017 log_136.xml
-rw-r----- 1 oracle asmadmin 10485801 Jun 14  2017 log_137.xml
-rw-r----- 1 oracle asmadmin 10486012 Jun 14  2017 log_138.xml
-rw-r----- 1 oracle asmadmin 10485960 Jun 14  2017 log_139.xml
-rw-r----- 1 oracle asmadmin 10485860 Jun 15  2017 log_140.xml
-rw-r----- 1 oracle asmadmin 10485939 Jun 15  2017 log_141.xml
-rw-r----- 1 oracle asmadmin 10485939 Jun 16  2017 log_142.xml
-rw-r----- 1 oracle asmadmin 10485780 Jun 16  2017 log_143.xml
-rw-r----- 1 oracle asmadmin 10485805 Jun 16  2017 log_144.xml
-rw-r----- 1 oracle asmadmin 10485949 Jun 17  2017 log_145.xml
-rw-r----- 1 oracle asmadmin 10485930 Jun 17  2017 log_146.xml
-rw-r----- 1 oracle asmadmin 10485872 Jun 17  2017 log_147.xml
-rw-r----- 1 oracle asmadmin 10485873 Jun 18  2017 log_148.xml
-rw-r----- 1 oracle asmadmin 10485949 Jun 18  2017 log_149.xml
-rw-r----- 1 oracle asmadmin 10485881 Jun 19  2017 log_150.xml
-rw-r----- 1 oracle asmadmin 10485836 Jun 19  2017 log_151.xml
-rw-r----- 1 oracle asmadmin 10485853 Jun 19  2017 log_152.xml
-rw-r----- 1 oracle asmadmin 10485936 Jun 20  2017 log_153.xml
-rw-r----- 1 oracle asmadmin 10485991 Jun 20  2017 log_154.xml
-rw-r----- 1 oracle asmadmin 10485812 Jun 21  2017 log_155.xml
-rw-r----- 1 oracle asmadmin 10485861 Jun 21  2017 log_156.xml
-rw-r----- 1 oracle asmadmin 10486012 Jun 21  2017 log_157.xml
-rw-r----- 1 oracle asmadmin     3048 Jun 21  2017 log_158.xml
-rw-r----- 1 oracle asmadmin 10486092 Jun 22  2017 log_159.xml
-rw-r----- 1 oracle asmadmin 10485946 Jun 22  2017 log_160.xml
-rw-r----- 1 oracle asmadmin 10485863 Jun 23  2017 log_161.xml
-rw-r----- 1 oracle asmadmin 10485854 Jun 23  2017 log_162.xml
-rw-r----- 1 oracle asmadmin 10485936 Jun 23  2017 log_163.xml
-rw-r----- 1 oracle asmadmin 10485948 Jun 24  2017 log_164.xml
-rw-r----- 1 oracle asmadmin 10485882 Jun 24  2017 log_165.xml
-rw-r----- 1 oracle asmadmin 10485814 Jun 25  2017 log_166.xml
-rw-r----- 1 oracle asmadmin 10485879 Jun 25  2017 log_167.xml
-rw-r----- 1 oracle asmadmin 10485939 Jun 25  2017 log_168.xml
-rw-r----- 1 oracle asmadmin 10485821 Jun 26  2017 log_169.xml
-rw-r----- 1 oracle asmadmin 10485939 Jun 26  2017 log_170.xml
-rw-r----- 1 oracle asmadmin 10485936 Jun 27  2017 log_171.xml
-rw-r----- 1 oracle asmadmin 10485876 Jun 27  2017 log_172.xml
-rw-r----- 1 oracle asmadmin 10486169 Jun 27  2017 log_173.xml
-rw-r----- 1 oracle asmadmin 10485951 Jun 28  2017 log_174.xml
-rw-r----- 1 oracle asmadmin 10486182 Jun 28  2017 log_175.xml
-rw-r----- 1 oracle asmadmin 10486140 Jun 29  2017 log_176.xml
-rw-r----- 1 oracle asmadmin 10485821 Jun 29  2017 log_177.xml
-rw-r----- 1 oracle asmadmin 10486098 Jun 29  2017 log_178.xml
-rw-r----- 1 oracle asmadmin 10485856 Jun 30  2017 log_179.xml
-rw-r----- 1 oracle asmadmin 10485849 Jun 30  2017 log_180.xml
-rw-r----- 1 oracle asmadmin 10486028 Jun 30  2017 log_181.xml
-rw-r----- 1 oracle asmadmin 10485768 Jul  1  2017 log_182.xml
-rw-r----- 1 oracle asmadmin 10485880 Jul  1  2017 log_183.xml
-rw-r----- 1 oracle asmadmin 10485774 Jul  2  2017 log_184.xml
-rw-r----- 1 oracle asmadmin 10485947 Jul  2  2017 log_185.xml
-rw-r----- 1 oracle asmadmin 10485896 Jul  2  2017 log_186.xml
-rw-r----- 1 oracle asmadmin 10485964 Jul  3  2017 log_187.xml
-rw-r----- 1 oracle asmadmin 10485810 Jul  3  2017 log_188.xml
-rw-r----- 1 oracle asmadmin 10485920 Jul  4  2017 log_189.xml
-rw-r----- 1 oracle asmadmin 10485827 Jul  4  2017 log_190.xml
-rw-r----- 1 oracle asmadmin 10485771 Jul  4  2017 log_191.xml
-rw-r----- 1 oracle asmadmin 10486064 Jul  5  2017 log_192.xml
-rw-r----- 1 oracle asmadmin 10485802 Jul  5  2017 log_193.xml
-rw-r----- 1 oracle asmadmin 10485868 Jul  6  2017 log_194.xml
-rw-r----- 1 oracle asmadmin 10485879 Jul  6  2017 log_195.xml
-rw-r----- 1 oracle asmadmin 10485782 Jul  6  2017 log_196.xml
-rw-r----- 1 oracle asmadmin 10485860 Jul  7  2017 log_197.xml
-rw-r----- 1 oracle asmadmin 10485893 Jul  7  2017 log_198.xml
-rw-r----- 1 oracle asmadmin 10485981 Jul  8  2017 log_199.xml
-rw-r----- 1 oracle asmadmin 10485869 Jul  8  2017 log_200.xml
-rw-r----- 1 oracle asmadmin 10485920 Jul  8  2017 log_201.xml
-rw-r----- 1 oracle asmadmin 10485915 Jul  9  2017 log_202.xml
-rw-r----- 1 oracle asmadmin 10486110 Jul  9  2017 log_203.xml
-rw-r----- 1 oracle asmadmin 10486011 Jul 10  2017 log_204.xml
-rw-r----- 1 oracle asmadmin 10485770 Jul 10  2017 log_205.xml
-rw-r----- 1 oracle asmadmin 10485821 Jul 10  2017 log_206.xml
-rw-r----- 1 oracle asmadmin 10485869 Jul 11  2017 log_207.xml
-rw-r----- 1 oracle asmadmin 10485779 Jul 11  2017 log_208.xml
-rw-r----- 1 oracle asmadmin 10485845 Jul 12  2017 log_209.xml
-rw-r----- 1 oracle asmadmin 10485843 Jul 12  2017 log_210.xml
-rw-r----- 1 oracle asmadmin 10485779 Jul 12  2017 log_211.xml
-rw-r----- 1 oracle asmadmin 10485780 Jul 13  2017 log_212.xml
-rw-r----- 1 oracle asmadmin 10485889 Jul 13  2017 log_213.xml
-rw-r----- 1 oracle asmadmin 10485833 Jul 13  2017 log_214.xml
-rw-r----- 1 oracle asmadmin 10485968 Jul 14  2017 log_215.xml
-rw-r----- 1 oracle asmadmin 10485797 Jul 14  2017 log_216.xml
-rw-r----- 1 oracle asmadmin 10485875 Jul 15  2017 log_217.xml
-rw-r----- 1 oracle asmadmin 10485790 Jul 15  2017 log_218.xml
-rw-r----- 1 oracle asmadmin 10485869 Jul 15  2017 log_219.xml
-rw-r----- 1 oracle asmadmin 10485923 Jul 16  2017 log_220.xml
-rw-r----- 1 oracle asmadmin 10485963 Jul 16  2017 log_221.xml
-rw-r----- 1 oracle asmadmin 10485852 Jul 17  2017 log_222.xml
-rw-r----- 1 oracle asmadmin 10485846 Jul 17  2017 log_223.xml
-rw-r----- 1 oracle asmadmin 10485941 Jul 17  2017 log_224.xml
-rw-r----- 1 oracle asmadmin 10485942 Jul 18  2017 log_225.xml
-rw-r----- 1 oracle asmadmin 10485878 Jul 18  2017 log_226.xml
-rw-r----- 1 oracle asmadmin 10485851 Jul 19  2017 log_227.xml
-rw-r----- 1 oracle asmadmin 10486089 Jul 19  2017 log_228.xml
-rw-r----- 1 oracle asmadmin 10485952 Sep 14 18:43 log_229.xml
-rw-r----- 1 oracle asmadmin 10485981 Sep 15 04:01 log_230.xml
-rw-r----- 1 oracle asmadmin 10486100 Sep 15 13:21 log_231.xml
-rw-r----- 1 oracle asmadmin 10485764 Oct 13 00:29 log_232.xml
-rw-r----- 1 oracle asmadmin 10486093 Oct 13 09:48 log_233.xml
-rw-r----- 1 oracle asmadmin 10485787 Oct 13 19:10 log_234.xml
-rw-r----- 1 oracle asmadmin 10486118 Oct 14 04:38 log_235.xml
-rw-r----- 1 oracle asmadmin 10486024 Oct 14 14:05 log_236.xml
-rw-r----- 1 oracle asmadmin 10485823 Oct 14 23:34 log_237.xml
-rw-r----- 1 oracle asmadmin 10485821 Oct 15 09:01 log_238.xml
-rw-r----- 1 oracle asmadmin 10485866 Oct 15 18:28 log_239.xml
-rw-r----- 1 oracle asmadmin 10485821 Oct 16 03:55 log_240.xml
-rw-r----- 1 oracle asmadmin 10485868 Oct 16 13:21 log_241.xml
-rw-r----- 1 oracle asmadmin 10485789 Oct 16 22:50 log_242.xml
-rw-r----- 1 oracle asmadmin 10485813 Oct 17 08:15 log_243.xml
-rw-r----- 1 oracle asmadmin 10485841 Oct 17 17:41 log_244.xml
-rw-r----- 1 oracle asmadmin 10485800 Oct 18 03:07 log_245.xml
-rw-r----- 1 oracle asmadmin 10485883 Oct 18 12:35 log_246.xml
-rw-r----- 1 oracle asmadmin 10485793 Oct 18 22:00 log_247.xml
-rw-r----- 1 oracle asmadmin 10485964 Oct 19 07:28 log_248.xml
-rw-r----- 1 oracle asmadmin 10486018 Oct 19 16:56 log_249.xml
-rw-r----- 1 oracle asmadmin 10485935 Oct 20 02:22 log_250.xml
-rw-r----- 1 oracle asmadmin 10486117 Oct 20 11:47 log_251.xml
-rw-r----- 1 oracle asmadmin 10485802 Oct 20 21:15 log_252.xml
-rw-r----- 1 oracle asmadmin 10485801 Oct 21 06:44 log_253.xml
-rw-r----- 1 oracle asmadmin 10486078 Oct 21 16:11 log_254.xml
-rw-r----- 1 oracle asmadmin 10485771 Oct 22 01:39 log_255.xml
-rw-r----- 1 oracle asmadmin 10485850 Oct 22 11:08 log_256.xml
-rw-r----- 1 oracle asmadmin 10486064 Oct 22 20:35 log_257.xml
-rw-r----- 1 oracle asmadmin 10486135 Oct 23 06:01 log_258.xml
-rw-r----- 1 oracle asmadmin 10485845 Oct 23 15:27 log_259.xml
-rw-r----- 1 oracle asmadmin 10485768 Oct 24 00:56 log_260.xml
-rw-r----- 1 oracle asmadmin 10485821 Oct 24 10:23 log_261.xml
-rw-r----- 1 oracle asmadmin 10486102 Oct 24 19:49 log_262.xml
-rw-r----- 1 oracle asmadmin 10486166 Oct 25 05:18 log_263.xml
-rw-r----- 1 oracle asmadmin 10485830 Dec 22 13:50 log_264.xml
-rw-r----- 1 oracle asmadmin  4378328 Jan 29 14:04 log.xml

find /u01/app/oracle/diag/rdbms/testdb/testdb/alert -type f -mtime +3 -exec rm -f {} \; -exec printf "." \;
......................................................................................................................................................[oracle@testdb alert]$
[oracle@testdb alert]$ ls -lrt
total 4300
-rw-r----- 1 oracle asmadmin 4398534 Jan 29 14:05 log.xml

How to change initrans on table for avoiding enq: TX – allocate ITL entry

In this blog post I will show you how to change initrans on table in Oracle Database without recreate table, for tuning enq: TX – allocate ITL entry waits in database.
Here is video of these procedures - Change INITRANS on table in Oracle database
First of all what is initrans? it is initial number of concurrent entries allocated within each data block allocated to the database object, this value can range from 1 to 255 and default is 1 with following exceptions:

• The default INITRANS value for a cluster is 2 or the default INITRANS value of the tablespace in which the cluster resides, whichever is greater.
• The default value for an index is 2.

Each transaction that updates a block requires a transaction entry in the block. This parameter ensures that a minimum number of concurrent transactions can update the block and helps avoid the overhead of dynamically allocating a transaction entry.

1) check if table have child tables with pk-fk relationship, if table have child table(s) then consider to change initrans also for child table(s)

select *
  from dba_constraints a, dba_constraints b
 where a.constraint_name = b.r_constraint_name
   and a.table_name = 'TABLE_NAME';

2) check if Table have indexes, then you must rebuild indexes, and also change initrans for indexes.
commands will be like:

ALTER TABLE TABLE_NAME MOVE INITRANS 10;
ALTER INDEX TABLE_NAME_PK REBUILD INITRANS 20;
ALTER INDEX TABLE_NAME_I1 REBUILD INITRANS 20;
......

3) check invalid objects and compile if any.

Let's demonstrate small demo :

[oracle@testdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Tue Jan 9 16:42:12 2018

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> set linesize 500
SQL> select *
  from dba_constraints a, dba_constraints b
 where a.constraint_name = b.r_constraint_name
   and a.table_name = 'TABLE_NAME';

no rows selected

SQL> select table_name, INI_TRANS
  from dba_tables
 where table_name = 'TABLE_NAME';

TABLE_NAME                      INI_TRANS
------------------------------ ----------
TABLE_NAME                              1

SQL> SELECT table_name, index_name, status, ini_trans
  FROM DBA_INDEXES
 WHERE TABLE_NAME = 'TABLE_NAME'; 

TABLE_NAME                     INDEX_NAME                     STATUS    INI_TRANS
------------------------------ ------------------------------ -------- ----------
TABLE_NAME                     TABLE_NAME_PK                  VALID             2
TABLE_NAME                     TABLE_NAME_I1                  VALID             2

SQL> ALTER TABLE TABLE_NAME MOVE INITRANS 10;

Table altered.

SQL> select table_name, INI_TRANS
  from dba_tables
 where table_name = 'TABLE_NAME';

TABLE_NAME                      INI_TRANS
------------------------------ ----------
TABLE_NAME                             10

SQL> SELECT table_name, index_name, status, ini_trans
  FROM DBA_INDEXES
 WHERE TABLE_NAME = 'TABLE_NAME';

TABLE_NAME                     INDEX_NAME                     STATUS    INI_TRANS
------------------------------ ------------------------------ -------- ----------
TABLE_NAME                     TABLE_NAME_PK                  UNUSABLE          2
TABLE_NAME                     TABLE_NAME_I1                  UNUSABLE          2

SQL> ALTER INDEX TABLE_NAME_PK REBUILD INITRANS 20;

Index altered.

SQL> ALTER INDEX TABLE_NAME_I1 REBUILD INITRANS 20;

Index altered.

SQL> SELECT table_name, index_name, status, ini_trans
  FROM DBA_INDEXES
 WHERE TABLE_NAME = 'TABLE_NAME';

TABLE_NAME                     INDEX_NAME                     STATUS    INI_TRANS
------------------------------ ------------------------------ -------- ----------
TABLE_NAME                     TABLE_NAME_PK                  VALID            20
TABLE_NAME                     TABLE_NAME_I1                  VALID            20

SQL>

How to kill all users processes LOCAL=NO

We often get questions about how to kill all users processes with single command and remain database in open mode.Here I write how to kill all LOCAL=NO processes in oracle database, it means kill every process except oracle background processes, which are not marked as LOCAL=NO and database will go in normal mode without crash. Of course our database is installed on unix (Linux) system and every oracle process have it's operating system process.

db_name : testdb
Version :   11.2.0.3.0
Server:      Red Hat Enterprise Linux Server release 6.5

Here is video for visual understanding - kill LOCAL=NO processes

login on server where database reside with database software owner user and find all users processes with command :

ps -ef | grep LOCAL=NO | grep testdb

NOTE : If you have multiple database on same server ensure that your ORACLE_SID is set correctly.

kill all LOCAL=NO processes with command:

[oracle@testdb ~]$ export ORACLE_SID=testdb
[oracle@testdb ~]$ kill -9 `ps -ef | grep LOCAL=NO | grep $ORACLE_SID | grep -v grep | awk '{print $2}'`

Demo :

[oracle@testdb ~]$ ps -ef | grep LOCAL=NO | grep testdb
oracle    3368     1  0 13:19 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3374     1  0 13:20 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3401     1  0 13:20 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3407     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3409     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3411     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3413     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3415     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3418     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3420     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3422     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3424     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3426     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3428     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
oracle    3430     1  0 13:21 ?        00:00:00 oracletestdb (LOCAL=NO)
[oracle@testdb ~]$ echo $ORACLE_SID
testdb
[oracle@testdb ~]$ export ORACLE_SID=testdb
[oracle@testdb ~]$ kill -9 `ps -ef | grep LOCAL=NO | grep $ORACLE_SID | grep -v grep | awk '{print $2}'`
[oracle@testdb ~]$ ps -ef | grep LOCAL=NO | grep testdb
[oracle@testdb ~]$
[oracle@testdb ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Dec 22 13:33:20 2017

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


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Data Mining
and Real Application Testing options

SQL> select status from v$instance;

STATUS
------------
OPEN

SQL>

Package cvuqdisk missing in Grid 12c

During Grid Infrastructure 12c R2 Installation faced below problem

Physical memory and swap size is understandable, but before this cvuqdisk-1.0.10-1 package was not known package for me.
Solution : You do not need download cvuqdisk-1.0.10-1.rpm package, it is inside grid infrastructure installation software, so you can find it under $GRID_HOME/cv/rpm.

with root user :

cd /u0/app/12.2.0.1/grid/cv/rpm/
# ls
cvuqdisk-1.0.10-1.rpm

# rpm -Uvh cvuqdisk-1.0.10-1.rpm 
Preparing...                ########################################### [100%]
Using default group oinstall to install package
   1:cvuqdisk               ########################################### [100%]

That's all, Click Check again and missing package problem disappear

ORA-01950: no privileges on tablespace in Oracle database 12c

Recently I faced error ORA-01950: no privileges on tablespace during import schema in oracle database 12c R2, which was exported from 11g R2.
Cause: In 11g resource role implicitly contains unlimited tablespace privilege, but in 12c resource role doesn't contain unlimited tablespace privilege. Because my user have resource role and in 12c database it doesn't contain tablespace privilege we got :

ORA-01950: no privileges on tablespace USERS

Solution:

ALTER USER "user_name" quota unlimited on "tablespace_name";

or

grant unlimited tablespace to "user_name";

after that import run successfully.

ORA-29907 : found duplicate labels in primary invocations

I have a mailmessages table with two different text indexes on subject and body columns and when I try to execute query:

select *
  from mailmessages
 where (CONTAINS(body, '%oracle%', 1) > 0)
    or (CONTAINS(subject, '%oracle%', 1) > 0)

Appears next error:

ORA-29907 : found duplicate labels in primary invocations

Solution : Problem is that we are using same label 1 for both search criteria, therefore we can execute above query without labels or with different labels :

select *
  from mailmessages
 where (CONTAINS(body, '%oracle%', 1) > 0)
    or (CONTAINS(subject, '%oracle%', 2) > 0)

or

select *
  from mailmessages
 where (CONTAINS(body, '%oracle%') > 0)
    or (CONTAINS(subject, '%oracle%') > 0)

Oracle Text index size

Oracle Text index is a very powerful feature for searching user specified text in big text columns for example in clob type columns. As a Database Administrator (DBA) you need to monitor it's sizes and growth, but it's segments are not shown in dba_segments view, because every text index internally consists several tables with names prefixed 'DR$', some of these tables have their own indexes and some of them are Index Organized Tables (IOT). Therefore when you need to find actual size of oracle text index you have to consider all 'DR$' tables and their indexes.
Here is script which calculates all text indexes sizes in Oracle Database:

------ partitioned text index size
(select table_name, x.index_name, sum(MB) MB
  from (select substr(table_name, 4, instr(table_name, '#', -1) - 4) index_name,
               sum(bytes) / 1024 / 1024 MB
          from dba_tables t, dba_segments s
         where t.table_name = s.segment_name
           and t.table_name like 'DR$%$%'
         group by substr(table_name, 4, instr(table_name, '#', -1) - 4)
        union
        select substr(table_name, 4, instr(table_name, '#', -1) - 4) index_name,
               sum(bytes) / 1024 / 1024 MB
          from dba_indexes i, dba_segments s
         where i.index_name = s.segment_name
           and i.table_name like 'DR$%$%'
         group by substr(table_name, 4, instr(table_name, '#', -1) - 4)) x,
       dba_indexes ind
 where x.index_name = ind.index_name
 group by table_Name, x.index_name
)
union 
--- not partitioned text index size
(select table_name, x.index_name, sum(MB) MB
  from (select substr(table_name, 4, instr(table_name, '$', -1) - 4) index_name,
               sum(bytes) / 1024 / 1024 MB
          from dba_tables t, dba_segments s
         where t.table_name = s.segment_name
           and t.table_name like 'DR$%$%'
         group by substr(table_name, 4, instr(table_name, '$', -1) - 4)
        union
        select substr(table_name, 4, instr(table_name, '$', -1) - 4) index_name,
               sum(bytes) / 1024 / 1024 MB
          from dba_indexes i, dba_segments s
         where i.index_name = s.segment_name
           and i.table_name like 'DR$%$%'
         group by substr(table_name, 4, instr(table_name, '$', -1) - 4)) x,
       dba_indexes ind
 where x.index_name = ind.index_name
 group by table_Name, x.index_name
 )
 order by table_name,  index_name;

Failed To Launch Process: Auth Fail During Infiniband Switch Discovery In OEM

Below error is reported during Infiniband switch discovery in Oracle Enterprise Manager (OEM) 12c Release 4 (12.1.0.4.0)

Failed To Launch process: Auth Fail 

However you are able to ssh to same switch using nm2user, without any errors.
This is caused due to the setting "PasswordAuthentication no" in the IB Switch's
/etc/ssh/sshd_config file, which disables clear text password authentication:

#cat /etc/ssh/sshd_config

# To disable tunneled clear text passwords, change to no here!
#PasswordAuthentication yes
# NM2 change - set PermitEmptyPasswords to no
PermitEmptyPasswords no
# NM2 change - set PasswordAuthentication to no
PasswordAuthentication no

Solution :
A workaround to complete the discovery is to include (uncomment) "PasswordAuthentication yes" in the sshd_config file and remove (comment) "PasswordAuthentication no", and restart SSHD using /etc/init.d/sshd restart.

DBA_JOBS_RUNNING view is not showing all running DBMS_JOBS across the nodes in RAC

If you have Real Application Clusters Database (RAC) and you want to see currently running DBMS_JOBS across all nodes DBA_JOBS_RUNNING view in RAC is not showing all running jobs in Oracle 11g, It shows running jobs on instance where you querying DBA_JOBS_RUNNING, because it uses v$lock instead of gv$lock. It Seems like that:

create or replace view dba_jobs_running as
select v.SID, v.id2 JOB, j.FAILURES,
    LAST_DATE, substr(to_char(last_date,'HH24:MI:SS'),1,8) LAST_SEC,
    THIS_DATE, substr(to_char(this_date,'HH24:MI:SS'),1,8) THIS_SEC,
    j.field1 INSTANCE
  from sys.job$ j, v$lock v
  where v.type = 'JQ' and j.job (+)= v.id2;
comment on column DBA_JOBS_RUNNING.SID is 'Identifier of process which is executing the job.  See v$lock.';
comment on column DBA_JOBS_RUNNING.JOB is 'Identifier of job.  This job is currently executing.';
comment on column DBA_JOBS_RUNNING.FAILURES is 'How many times has this job started and failed since its last success?';
comment on column DBA_JOBS_RUNNING.LAST_DATE is 'Date that this job last successfully executed';
comment on column DBA_JOBS_RUNNING.LAST_SEC is 'Same as LAST_DATE.  This is when the last successful execution started.';
comment on column DBA_JOBS_RUNNING.THIS_DATE is 'Date that this job started executing (usually null if not executing)';
comment on column DBA_JOBS_RUNNING.THIS_SEC is 'Same as THIS_DATE.  This is when the last successful execution started.';
comment on column DBA_JOBS_RUNNING.INSTANCE is 'The instance number restricted to run the job';

In RAC we can use next script to find all running jobs across all nodes:

select v.SID,
       v.id2 JOB,
       j.FAILURES,
       LAST_DATE,
       substr(to_char(last_date, 'HH24:MI:SS'), 1, 8) LAST_SEC,
       THIS_DATE,
       substr(to_char(this_date, 'HH24:MI:SS'), 1, 8) THIS_SEC,
       v.INST_ID instance
  from sys.job$ j, gv$lock v
 where v.type = 'JQ'
   and j.job(+) = v.id2;

Also you can create some view according to this query and call it dba_jobs_running_rac or something like that and in RAC database use that view to find all running jobs across all nodes.
For example :

create or replace view dba_jobs_running_rac as
select v.SID,
       v.id2 JOB,
       j.FAILURES,
       LAST_DATE,
       substr(to_char(last_date, 'HH24:MI:SS'), 1, 8) LAST_SEC,
       THIS_DATE,
       substr(to_char(this_date, 'HH24:MI:SS'), 1, 8) THIS_SEC,
       v.INST_ID instance
  from sys.job$ j, gv$lock v
 where v.type = 'JQ'
   and j.job(+) = v.id2;

select * from dba_jobs_running_rac

How to use vi editor in SQL PLUS

Here I'll show you how to use vi editor in SQL PLUS, It is very easy and comfortable when working with SQLPLUS and writing scripts.
vi is a screen-oriented text editor originally created for the Unix operating system.
Here is video of these procedures

Tempororay define editor in session level

SQL> define_editor=vi
SQL> select * from duall;
select * from duall
              *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL> ed
Wrote file afiedt.buf

  1* select * from dual
SQL> /

D
-
X

SQL>

To make permanent above settings for SQLPLUS utility you need to define it in $ORACLE_HOME/sqlplus/admin/glogin.sql and it will be effective for all session with sqlplus.

vi $ORACLE_HOME/sqlplus/admin/glogin.sql
define _editor='vi'

How to add oracle database with custom character set into OEM12c

If you have custom characterset database and you want to add this database into Oracle Enterprise Manager(OEM), during addition it definitely fails and here I''ll try to help you.
Our environment:
Operating System is CentOS release 6.6 (Final) x86_64 and installed Oracle Enterprise Manager 12c Release 4 (12.1.0.4.0), Repository Database - Oracle Database 12c (12.1.0.2.0) exist on same host which character set is AL32UTF8.
We have Oracle Database 11g (11.2.0.3) with custom character set on another host, also we have installed agent and our goal is to monitor that database with OEM12c, hence we want to add this database into OEM.
Our custom character set nlt file is: lx22900.nlt, you also need its nlb file.
1)
Copy lx0boot.nlb, lx1boot.nlb, lx22900.nlb files into :

$ORACLE_HOME/nls/data/

/u1/app/oracle/oem12c/Oracle_WT/nls/data/

$OMS_HOME/nls/data/

2)
Add custom locale definitions to java components with Ginstall utility

java -cp /u1/app/oracle/oem12c/oms/jlib/orai18n-tools.jar:

/u1/app/oracle/oem12c/oms/jlib/orai18n-utility.jar:

/u1/app/oracle/oem12c/oms/jlib/orai18n-collation.jar:

/u1/app/oracle/oem12c/oms/jlib/orai18n-lcsd.jar:

/u1/app/oracle/oem12c/oms/jlib/orai18n-mapping.jar:

/u1/app/oracle/oem12c/oms/jlib/orai18n-net.jar:

/u1/app/oracle/oem12c/oms/jlib/orai18n-servlet.jar:

/u1/app/oracle/oem12c/oms/jlib/orai18n-translation.jar:

/u1/app/oracle/oem12c/oms/jlib/orai18n.jar:

/u1/app/oracle/oem12c/oms/lib/xmlparserv2.jar

Ginstall -a lx22900.nlt

output must be :

oracle/i18n/data/lx22900.glb has been successfully stored in gdk_custom.jar CharConv1ByteBuilder lx22900.nlt

3)
create a custom character set file custom_orai18n-mapping.jar, that includes our custom characterset which for example is GEO8:

java -jar $OMS_HOME/jlib/orai18n.jar -custom-charsets-jar custom_orai18n-mapping.jar -charset GEO8

Added Character set : GEO8

Check it:

java -jar custom_orai18n-mapping.jar

Oracle Globalization Development Kit - 11.1.0.7.0 - Production
This custom character set jar/zip file was created with the following command: 

java -jar orai18n.jar -custom-charsets-jar custom_orai18n-mapping.jar -charset GEO8

4)
Copy generated files into these directories:

cp gsstemp.zip gdk_custom.jar custom_orai18n-mapping.jar /u1/app/oracle/oem12c/Oracle_WT/oui/jlib/

cp gsstemp.zip gdk_custom.jar custom_orai18n-mapping.jar /u1/app/oracle/oem12c/Oracle_WT/jlib/

cp gsstemp.zip gdk_custom.jar custom_orai18n-mapping.jar /u1/app/oracle/oem12c/oracle_common/modules/oracle.nlsrtl_11.1.0/

cp gsstemp.zip gdk_custom.jar custom_orai18n-mapping.jar /u1/app/oracle/oem12c/oracle_common/oui/jlib/

cp gsstemp.zip gdk_custom.jar custom_orai18n-mapping.jar /u1/app/oracle/oem12c/oms/oui/jlib/

cp gsstemp.zip gdk_custom.jar custom_orai18n-mapping.jar /u1/app/oracle/oem12c/oms/jlib/

cp gsstemp.zip gdk_custom.jar custom_orai18n-mapping.jar /u1/app/oracle/oem12c/wlserver_10.3/server/ext/jdbc/oracle/11g/

cp gsstemp.zip gdk_custom.jar custom_orai18n-mapping.jar /u1/app/oracle/agent/core/12.1.0.4.0/oui/jlib/

cp gsstemp.zip gdk_custom.jar custom_orai18n-mapping.jar /u1/app/oracle/agent/core/12.1.0.4.0/jlib/

cp gsstemp.zip gdk_custom.jar custom_orai18n-mapping.jar /u1/app/oracle/product/11.2.0/owb/lib/patches/

cp gsstemp.zip gdk_custom.jar custom_orai18n-mapping.jar /u1/app/oracle/product/11.2.0/inventory/Scripts/ext/jlib/

cp gsstemp.zip gdk_custom.jar custom_orai18n-mapping.jar /u1/app/oracle/product/11.2.0/sqldeveloper/jlib/

cp gsstemp.zip gdk_custom.jar custom_orai18n-mapping.jar /u1/app/oracle/product/11.2.0/oui/jlib/

cp gsstemp.zip gdk_custom.jar custom_orai18n-mapping.jar /u1/app/oracle/product/11.2.0/oc4j/jdbc/lib/

cp gsstemp.zip gdk_custom.jar custom_orai18n-mapping.jar /u1/app/oracle/product/11.2.0/jlib/

cp gsstemp.zip gdk_custom.jar custom_orai18n-mapping.jar /u1/app/database/stage/ext/jlib/

5)
Add CLASSPATH and LD_LIBRARY_PATH environment variables into .bash_profile file
Load .bash_profile parameters with command :

source .bash_profile
 

Check.bash_profile parameters with command

Cat .bash_profile

ORACLE_SID=OEM12C
 
ORACLE_BASE=/u1/app/oracle
 
ORACLE_HOME=$ORACLE_BASE/product/11.2.0
 
OMS_HOME=/u1/app/oracle/oem12c/oms
 
AGENT_HOME=/u1/app/oracle/agent/core/12.1.0.4.0
 
PATH=$PATH:$ORACLE_HOME/bin:$ORACLE_HOME/OPatch
 
CLASSPATH=/u1/app/oracle/oem12c/oms/jlib/custom_orai18n-mapping.jar:/u1/app/oracle/oem12c/oms/jlib/orai18n-tools.jar:/u1/app/oracle/oem12c/oms/jlib/orai18n-utility.jar:/u1/app/oracle/oem12c/oms/jlib/orai18n-collation.jar:/u1/app/oracle/oem12c/oms/jlib/orai18n-lcsd.jar:/u1/app/oracle/oem12c/oms/jlib/orai18n-mapping.jar:/u1/app/oracle/oem12c/oms/jlib/orai18n-net.jar:/u1/app/oracle/oem12c/oms/jlib/orai18n-servlet.jar:/u1/app/oracle/oem12c/oms/jlib/orai18n-translation.jar:/u1/app/oracle/oem12c/oms/jlib/orai18n.jar:/u1/app/oracle/oem12c/oms/jlib/:gdk_custom.jar:/u1/app/oracle/oem12c/oms/jlib/:gsstemp.zip
 
LD_LIBRARY_PATH=$OMS_HOME/jlib
 
export ORACLE_BASE ORACLE_HOME ORACLE_SID OMS_HOME AGENT_HOME PATH CLASSPATH LD_LIBRARY_PATH
 

6)
Restart OMS, AGENT and repository database

$OMS_HOME/bin/emctl stop oms –all
 
$AGENT_HOME/bin/emctl stop agent
 
sqlplus / as sysdba
 
SQL> shutdown immediate;
 
SQL> startup;

$OMS_HOME/bin/emctl start oms

$AGENT_HOME/bin/emctl start agent

7)
Copy gsstemp.zip and gdk_custom.jar into client's agent jlib directory where resides database with custom character set, for example:

cp gsstemp.zip gdk_custom.jar oracle@testhost:/u1/agent12c/core/12.1.0.4.0/jlib

and restart client's agent.
That's all now you can add custom character set database into oem12c.

Create RPM packages local repository for Oracle Enterprise Linux 7.0 on Virtual Machine

In this blog post I''ll show you how to create local RPM packages repository for Oracle Enterprise Linux 7.0. Let's start.
First of all we need connected Linux 7.0 ISO, go to the machine setting and check connected checkbox, under ISO image file choose Linux 7.0 installation file as shown below

with root user

mkdir -p /mnt/cdrom
mount -t iso9660 -o ro /dev/sr0 /mnt/cdrom

mkdir -p /repo
cd /mnt/cdrom/Packages
cp * /repo/

cd /repo
rpm -Uvh deltarpm-3.6-3.el7.x86_64.rpm
rpm -Uvh python-deltarpm-3.6-3.el7.x86_64.rpm
rpm -Uvh createrepo-0.9.9-23.el7.noarch.rpm

createrepo .
Spawning worker 0 with 4292 pkgs
Workers Finished
Saving Primary metadata
Saving file lists metadata
Saving other metadata
Generating sqlite DBs
Sqlite DBs complete

mv /etc/yum.repos.d/public-yum-ol7.repo /etc/yum.repos.d/public-yum-ol7.repo.bkp

create rhel_repo.repo file under /etc/yum.repos.d/ and write next parameters

vi /etc/yum.repos.d/rhel_repo.repo

[rhel_repo]
name=repo
baseurl="file:///repo/"
gpgcheck=0
enabled=1

now you can check local repository

yum repolist
Loaded plugins: langpacks
rhel_repo                                                                                                                                                                     | 2.9 kB  00:00:00
rhel_repo/primary_db                                                                                                                                                          | 3.8 MB  00:00:00
repo id                                                                                           repo name                                                                                    status
rhel_repo                                                                                         repo                                                                                         4,292
repolist: 4,292

that's all now you have local RPM packages repository for Oracle Enterprise Linux 7.0