sybase logo

How to uninstall SAP ASE – Sybase 16 from linux

In this post, I am going to demonstrate step by step approach to remove SAP Adaptive Server Enterprise ( Sybase ) 16 from Linux host.

As a part of prerequisites kindly Stop all ASE services before instantiate uninstall.

Login as SYBASE user in order to uninstall sybase:

[root@Centsrv01 ASESuite]# su - sybase

Change directory to ‘/opt/sybase/sybuninstall/ASESuite’ and instantiate ‘uninstall’ as below. And follow the instruction and provide the inputs as needed in order to remove SAP ASE.

Uninstallation logs are as follows:

-bash-4.1$ cd /opt/sybase/sybuninstall/ASESuite
-bash-4.1$ ./uninstall

Graphical installers are not supported by the VM. The console mode will be used instead...

===============================================================================
SAP Adaptive Server Enterprise (created with InstallAnywhere)
-------------------------------------------------------------------------------

Preparing CONSOLE Mode Uninstallation...
===============================================================================
Uninstall SAP Adaptive Server Enterprise
----------------------------------------

About to uninstall...

SAP Adaptive Server Enterprise 16.0 GA PL01

This will remove features installed by InstallAnywhere. It will not remove
files and folders created after the installation.

PRESS  TO CONTINUE:

===============================================================================
Uninstall Options
-----------------

ENTER THE NUMBER FOR YOUR CHOICE, OR PRESS  TO ACCEPT THE DEFAULT:

->1- Completely remove all features and components.
2- Choose specific features that were installed by InstallAnywhere.

Please choose one of the following options:: 1

===============================================================================
Pre-Uninstall Summary
---------------------

SAP Adaptive Server Enterprise will be uninstalled from the following location:

/opt/sybase

with the following features:

Adaptive Server Enterprise
Additional ASE Language Modules
Open Client
DB-Library
Embedded SQL/C
Embedded SQL/Cobol
XA Interface Library for ASE Distributed Transaction Manager
Additional Connectivity Language Modules
ASE Extension Module for Python
ASE Database Driver for PERL
ASE Extension Module for PHP
jConnect 16.0 for JDBC
Interactive SQL
QPTune
SySAM License Utilities
SySAM License Server
SAP Control Center

PRESS  TO CONTINUE:

Remote Command and Control Agent for Adaptive Server
Management User Interface for SAP Adaptive Server
SNMP Support for Adaptive Server
ASE ODBC Driver
OData Server for ASE
PRESS  TO CONTINUE:

===============================================================================
Uninstalling ESQL/C 16.0 PL02...Success
Uninstalling SySAM License Server 2.3...Success
Uninstalling ASE Extension Module for PHP 16.0 PL02...Success
Uninstalling XA Interface Library for ASE Distributed Transaction Manager 16.0
PL02...Success
Uninstalling ESQL/Cobol 16.0 PL02...Success
Uninstalling Adaptive Server Enterprise Additional Language Modules 16.0 GA
PL01...Success
Uninstalling QPTune 3.0...Success
Uninstalling ASE Database Driver for PERL 16.0 PL02...Success
Uninstalling Management User Interface for SAP Adaptive Server Enterprise
3.3...Success
Uninstalling OData Server for ASE 16.0 GA...Success
Uninstalling ASE Extension Module for Python 16.0 PL02...Success
Uninstalling Additional Connectivity Language Modules 16.0 PL02...Success
Uninstalling Interactive SQL 16.0 PL01...Success
Uninstalling SAP Adaptive Server Enterprise 16.0 GA PL01...Success
Uninstalling DB-Library 16.0 PL02...Success
Uninstalling SAP ASE ODBC Driver 16.0 PL02...Success
Uninstalling Install/Extract Java 16.0 GA PL01...Success
Uninstalling SAP CT-DBCAPI 16.0 PL02...Success
Uninstalling SySAM License Utilities 2.3...Success
Uninstalling SAP jConnect 16.0 PL01...Success
Uninstalling Open Client 16.0 PL02...Success
Uninstalling Shared Library 1.0.0.5...Success
Uninstalling Connectivity Language Modules 16.0 PL02...Success
Uninstalling SCC - Remote Command and Control Agent for Adaptive Server 16.0 GA
PL01...Success
Uninstalling Language Modules 16.0 GA...Success
Uninstalling SCC - SNMP Support for Adaptive Server 2.0.0.8...Success
Uninstalling SAP Control Center Management Server 3.3...Success
Uninstalling SAP Java Runtime Environment 7.1.011...Success

===============================================================================
Uninstalling...
---------------
...*
*
*************************
*************************
*************************
************************
...*
*
*************************
*************************
*************************
************************

===============================================================================
Delete User Files
-----------------

The uninstaller has successfully uninstalled items created during installation.
However, the following files still exist in the install directory. These files
were created outside of the installer.

/opt/sybase/SYBASE.csh
/opt/sybase/interfaces
/opt/sybase/SCC-3_3/log/repository.log
/opt/sybase/SCC-3_3/log/gateway-stats.log
/opt/sybase/SCC-3_3/log/alert-server.log
/opt/sybase/SCC-3_3/log/gateway.log
/opt/sybase/SCC-3_3/log/collection-stats.log
/opt/sybase/SCC-3_3/log/agent.log
/opt/sybase/install/sample_response.txt
/opt/sybase/install/sysam_utilities/bin/sysamcap
/opt/sybase/install/sysam_utilities/bin/cpuinfo
/opt/sybase/install/sysam_utilities/bin/lmutil
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/ja_JP/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/th_th/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/pt_PT/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/es_ES/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/zh_cn/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/pl_pl/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/es_es/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/pl_PL/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/fr_fr/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/fr_FR/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/pt_pt/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/de_de/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/de_DE/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/ja_jp/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/zh_CN/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/ko_KR/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/en_us/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/th_TH/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/ko_kr/sylapi.lcu
/opt/sybase/install/sysam_utilities/locales/unicode/sylapi/en_US/sylapi.lcu
/opt/sybase/install/setup.bin
/opt/sybase/install/archives/ie_java_mm/ie_java.iam.zip
/opt/sybase/install/archives/ie_java_mm/archives/version.txt
/opt/sybase/install/archives/ie_java_mm/archives/comp_size.properties
/opt/sybase/install/archives/version.txt
/opt/sybase/install/archives/ase_agent_mm/ase_agent.iam.zip
/opt/sybase/install/archives/ase_agent_mm/archives/version.txt
/opt/sybase/install/archives/ase_agent_mm/archives/comp_size.properties
/opt/sybase/install/archives/open_client_mm/archives/version.txt
/opt/sybase/install/archives/open_client_mm/archives/comp_size.properties
/opt/sybase/install/archives/open_client_mm/open_client.iam.zip
/opt/sybase/install/archives/conn_perl_mm/conn_perl.iam.zip
/opt/sybase/install/archives/conn_perl_mm/archives/version.txt
/opt/sybase/install/archives/conn_perl_mm/archives/comp_size.properties
/opt/sybase/install/archives/ase_snmp_agent_mm/archives/version.txt
/opt/sybase/install/archives/ase_snmp_agent_mm/archives/comp_size.properties
/opt/sybase/install/archives/ase_snmp_agent_mm/ase_snmp_agent.iam.zip
/opt/sybase/install/archives/dbisql_mm/archives/version.txt
/opt/sybase/install/archives/dbisql_mm/archives/comp_size.properties
/opt/sybase/install/archives/dbisql_mm/dbisql.iam.zip
/opt/sybase/install/archives/jconnect16_mm/archives/version.txt
/opt/sybase/install/archives/jconnect16_mm/archives/comp_size.properties
/opt/sybase/install/archives/jconnect16_mm/jconnect.iam.zip
/opt/sybase/install/archives/sysam_util_mm/sysam_util.iam.zip
/opt/sybase/install/archives/sysam_util_mm/archives/version.txt
/opt/sybase/install/archives/sysam_util_mm/archives/comp_size.properties
/opt/sybase/install/archives/sybpsu
/opt/sybase/install/archives/shared_lib_mm/archives/version.txt
/opt/sybase/install/archives/shared_lib_mm/archives/comp_size.properties
/opt/sybase/install/archives/shared_lib_mm/shared_lib.iam.zip
/opt/sybase/install/archives/SYBASE_ASE_XE.lic
/opt/sybase/install/archives/esqlc_mm/archives/version.txt
/opt/sybase/install/archives/esqlc_mm/archives/comp_size.properties
/opt/sybase/install/archives/esqlc_mm/esqlc.iam.zip
/opt/sybase/install/archives/asemanifest.mf
/opt/sybase/install/archives/sylapi/libsylapij.so
/opt/sybase/install/archives/esqlcobol_mm/esqlcobol.iam.zip
/opt/sybase/install/archives/esqlcobol_mm/archives/version.txt
/opt/sybase/install/archives/esqlcobol_mm/archives/comp_size.properties
/opt/sybase/install/archives/ase_mm/archives/version.txt
/opt/sybase/install/archives/ase_mm/archives/comp_size.properties
/opt/sybase/install/archives/ase_mm/ase.iam.zip
/opt/sybase/install/archives/dbcapi_mm/archives/version.txt
/opt/sybase/install/archives/dbcapi_mm/archives/comp_size.properties
/opt/sybase/install/archives/dbcapi_mm/dbcapi.iam.zip
/opt/sybase/install/archives/dblib_mm/dblib.iam.zip
/opt/sybase/install/archives/dblib_mm/archives/version.txt
/opt/sybase/install/archives/dblib_mm/archives/comp_size.properties
/opt/sybase/install/archives/lang_mm/lang.iam.zip
/opt/sybase/install/archives/lang_mm/archives/version.txt
/opt/sybase/install/archives/lang_mm/archives/comp_size.properties
/opt/sybase/install/archives/odbc_mm/archives/version.txt
/opt/sybase/install/archives/odbc_mm/archives/comp_size.properties
/opt/sybase/install/archives/odbc_mm/odbc.iam.zip
/opt/sybase/install/archives/ase_add_lm_mm/archives/version.txt
/opt/sybase/install/archives/ase_add_lm_mm/archives/comp_size.properties
/opt/sybase/install/archives/ase_add_lm_mm/ase_add_lm.iam.zip
/opt/sybase/install/archives/sysam_server_mm/sysam_server.iam.zip
/opt/sybase/install/archives/sysam_server_mm/archives/version.txt
/opt/sybase/install/archives/sysam_server_mm/archives/comp_size.properties
/opt/sybase/install/archives/SySAMLicenseInfo.xml
/opt/sybase/install/archives/conn_add_lm_mm/conn_add_lm.iam.zip
/opt/sybase/install/archives/conn_add_lm_mm/archives/version.txt
/opt/sybase/install/archives/conn_add_lm_mm/archives/comp_size.properties
/opt/sybase/install/archives/odata_mm/odata.iam.zip
/opt/sybase/install/archives/odata_mm/archives/version.txt
/opt/sybase/install/archives/odata_mm/archives/comp_size.properties
/opt/sybase/install/archives/SYBASE_ASE_DE.lic
/opt/sybase/install/archives/sapjre71_mm/archives/version.txt
/opt/sybase/install/archives/sapjre71_mm/archives/comp_size.properties
/opt/sybase/install/archives/sapjre71_mm/sapjre71.iam.zip
/opt/sybase/install/archives/conn_lm_mm/conn_lm.iam.zip
/opt/sybase/install/archives/conn_lm_mm/archives/version.txt
/opt/sybase/install/archives/conn_lm_mm/archives/comp_size.properties
/opt/sybase/install/archives/conn_php_mm/conn_php.iam.zip
/opt/sybase/install/archives/conn_php_mm/archives/version.txt
/opt/sybase/install/archives/conn_php_mm/archives/comp_size.properties
/opt/sybase/install/archives/conn_python_mm/archives/version.txt
/opt/sybase/install/archives/conn_python_mm/archives/comp_size.properties
/opt/sybase/install/archives/conn_python_mm/conn_python.iam.zip
/opt/sybase/install/archives/asemap_mm/archives/version.txt
/opt/sybase/install/archives/asemap_mm/archives/comp_size.properties
/opt/sybase/install/archives/asemap_mm/asemap.iam.zip
/opt/sybase/install/archives/xa_mm/xa.iam.zip
/opt/sybase/install/archives/xa_mm/archives/version.txt
/opt/sybase/install/archives/xa_mm/archives/comp_size.properties
/opt/sybase/install/archives/qptune_mm/archives/version.txt
/opt/sybase/install/archives/qptune_mm/archives/comp_size.properties
/opt/sybase/install/archives/qptune_mm/qptune.iam.zip
/opt/sybase/install/archives/scc_server_mm/scc_server.iam.zip
/opt/sybase/install/archives/scc_server_mm/archives/version.txt
/opt/sybase/install/archives/scc_server_mm/archives/comp_size.properties
/opt/sybase/install/archives/locales/unicode/sylapi/ja_JP/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/th_th/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/pt_PT/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/es_ES/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/zh_cn/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/pl_pl/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/es_es/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/pl_PL/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/fr_fr/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/fr_FR/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/pt_pt/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/de_de/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/de_DE/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/ja_jp/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/zh_CN/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/ko_KR/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/en_us/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/th_TH/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/ko_kr/sylapi.lcu
/opt/sybase/install/archives/locales/unicode/sylapi/en_US/sylapi.lcu
/opt/sybase/install/sysam_setup/sample_response.txt
/opt/sybase/install/sysam_setup/setup.bin
/opt/sybase/install/sysam_setup/archives/sysam_util_mm/sysam_util.iam.zip
/opt/sybase/install/sysam_setup/archives/sysam_util_mm/archives/comp_size.prope
rties
/opt/sybase/install/sysam_setup/archives/sysam_server_mm/sysam_server.iam.zip
/opt/sybase/install/sysam_setup/archives/sysam_server_mm/archives/comp_size.pro
perties
/opt/sybase/install/ThirdPartyLegal/FreeDownloadTermsMasterList.pdf
/opt/sybase/install/ThirdPartyLegal/FreeDownloadComponentList.CSI.2.9_M5_And_Ab
ove.pdf
/opt/sybase/install/ThirdPartyLegal/FreeDownloadComponentList.jConnect.16.0.pdf
/opt/sybase/install/ThirdPartyLegal/FreeDownloadComponentList.SDK.16.0.pdf
/opt/sybase/install/ThirdPartyLegal/FreeDownloadComponentList.SQLAnywhereODBCDr
iverManager.11.0.1.pdf
/opt/sybase/install/ThirdPartyLegal/FreeDownloadComponentList.ASE.16.0.pdf
/opt/sybase/install/ThirdPartyLegal/FreeDownloadComponentList.SCC.3.3.pdf
/opt/sybase/SYBASE.env
/opt/sybase/interf.old
/opt/sybase/SYBASE.sh
/opt/sybase/SYSAM-2_0/licenses/SYBASE.opt
/opt/sybase/SYSAM-2_0/licenses/SYBASE_ASE_DE.lic
/opt/sybase/ASE-16_0/sybase01.013
/opt/sybase/ASE-16_0/sybase01.018
/opt/sybase/ASE-16_0/sybase01.015
/opt/sybase/ASE-16_0/sybase01.krg
/opt/sybase/ASE-16_0/sybase01.019
/opt/sybase/ASE-16_0/sybase01.007
/opt/sybase/ASE-16_0/sybase01.001
/opt/sybase/ASE-16_0/install/RUN_sybase01_BS
/opt/sybase/ASE-16_0/install/sybase01.log
/opt/sybase/ASE-16_0/install/RUN_sybase01
/opt/sybase/ASE-16_0/install/sybase01_BS.log
/opt/sybase/ASE-16_0/sybase01.011
/opt/sybase/ASE-16_0/sybase01.cfg
/opt/sybase/ASE-16_0/sybase01.002
/opt/sybase/ASE-16_0/sybase01.016
/opt/sybase/ASE-16_0/init/logs/srvbuild1024.001-sybase01.rs
/opt/sybase/ASE-16_0/init/logs/configASELog.log
/opt/sybase/ASE-16_0/init/logs/configBSLog.log
/opt/sybase/ASE-16_0/init/logs/srvbuild1024.001
/opt/sybase/ASE-16_0/init/logs/srvbuild1024.002-sybase01_BS.rs
/opt/sybase/ASE-16_0/init/logs/srvbuild1024.002
/opt/sybase/ASE-16_0/sybase01.014
/opt/sybase/ASE-16_0/sybase01.017
/opt/sybase/ASE-16_0/sybase01.012
/opt/sybase/ASE-16_0/sysam/SYBASE.properties
/opt/sybase/ASE-16_0/sysam/SYB.properties
/opt/sybase/ASE-16_0/sysam/CENTSRV01.properties
/opt/sybase/ASE-16_0/sysam/sybase01.properties
/opt/sybase/ASE-16_0/sysam/sysam.properties.template
/opt/sybase/ASE-16_0/sybase01.bak
/opt/sybase/ASE-16_0/sybase01.005
/opt/sybase/ASE-16_0/sybase01.010
/opt/sybase/ASE-16_0/sybase01.004
/opt/sybase/ASE-16_0/sybase01.008
/opt/sybase/ASE-16_0/sybase01.009
/opt/sybase/ASE-16_0/sybase01.006
/opt/sybase/ASE-16_0/sybase01.003
/opt/sybase/.bash_history
/opt/sybase/ODATA-16_0/bin64/dbosrv16.ini
/opt/sybase/DBISQL-16_0/bin/dbisql.ini
/opt/sybase/DBISQL-16_0/bin/dbisql.000
/opt/sybase/DBISQL-16_0/bin/dbisql_64.rep
/opt/sybase/dir/sysprocs.dat
/opt/sybase/dir/sybsysdb.dat
/opt/sybase/dir/master.dat
/opt/sybase/dir/tempdbdev.dat

Do you want to delete all of these files? (Y/N): Y

===============================================================================
Uninstall Complete
------------------

Some items could not be removed.

-bash-4.1$

Note:

Remove additional files manually that you have created or modified after Sybase installation.

In my case, I have created “sybase” directory in /var/ and /opt/.

 -bash-4.1$ cd /opt/
 -bash-4.1$ rm -rf sybase/
 -bash-4.1$ cd /var/
 -bash-4.1$ rm -rf sybase/

 

Congratulations! SAP ASE is now completely removed from Linux host.

Thanks,

Stay Tune. 🙂

Oracle 11g Logo

TNS-01201: Listener cannot find executable string for SID string – Listener failed to start

Usually TNS-01201 listener error pop up when oracle can’t find its executable under ORACLE_HOME variable mentioned in listener.ora file. After investigating listener.ora file, Found same, ORACLE_HOME was set to wrong path. After setting proper path listener started successfully.

Have a look on error logs:

Error Log: While starting listener

[oracle@PR admin]$ lsnrctl start
...
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 11.2.0.1.0 - Production
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/PR/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PR.localdomain)(PORT=1521)))
TNS-01201: Listener cannot find executable /u01/oracle/product/11.2.0/dbhome_1/bin/oracle for SID RTS
Listener failed to start. See the error message(s) above...

Error Log: While tnsping

[oracle@PR admin]$ tnsping RTS
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = PR)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = RTS) (GLOBAL_NAME = RTS) (UR=A)))
TNS-12541: TNS:no listener

listener.ora file:

SID_LIST_LISTENER =
 (SID_LIST =
  (SID_DESC =
   (GLOBAL_DBNAME = RTS)
   (ORACLE_HOME = /u01/oracle/product/11.2.0/dbhome_1)
   (SID_NAME = RTS)
  )
 )
LISTENER =
 (DESCRIPTION =
  (ADDRESS = (PROTOCOL = TCP)(HOST = PR)(PORT = 1521))
 )
ADR_BASE_LISTENER = /u01/app/oracle

Solution:

Checkout above RED highlighted lines, mentioned ADR_BASE_LISTENER path is different then ORACLE_HOME. So after applying changes, listener started successfully. As follows:

[oracle@PR admin]$ lsnrctl start
Starting /u01/app/oracle/product/11.2.0/dbhome_1/bin/tnslsnr: please wait...
System parameter file is /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Log messages written to /u01/app/oracle/diag/tnslsnr/PR/listener/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PR.localdomain)(PORT=1521)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=PR)(PORT=1521)))
STATUS of the LISTENER
------------------------
Alias LISTENER
Version TNSLSNR for Linux: Version 11.2.0.1.0 - Production
Start Date 29-AUG-2015 09:22:35
Uptime 0 days 0 hr. 0 min. 0 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u01/app/oracle/product/11.2.0/dbhome_1/network/admin/listener.ora
Listener Log File /u01/app/oracle/diag/tnslsnr/PR/listener/alert/log.xml
Listening Endpoints Summary...
 (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=PR.localdomain)(PORT=1521)))
Services Summary...
Service "RTS" has 1 instance(s).
Instance "RTS", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully

tnsping also successful to RTS SID

[oracle@PR admin]$ tnsping RTS
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS_LIST = (ADDRESS = (PROTOCOL = TCP)(HOST = PR)(PORT = 1521))) (CONNECT_DATA = (SERVICE_NAME = RTS) (GLOBAL_NAME = RTS) (UR=A)))
OK (0 msec)

Thanks,

Stay Tune. 🙂

Oracle 11g Logo

ORA-01182: cannot create database file X – file is in use or recovery ORA-01111: name for data file X is unknown – rename to correct file

On Physical Standby Data Guard, Following command failed with ORA-01182. Error itself self explanatory.
As a part of solution, you just need to End up recovery sessions and bounce back your database to Mount mode and issue SQL command again.

Error Log:

SQL> alter database create datafile '/u01/app/oracle/product/11.2.0/prod/dbs/UNNAMED00100' as '/u03/Oradata/Prod_OTHER_0072.DAT';
alter database create datafile '/u01/app/oracle/product/11.2.0/prod/dbs/UNNAMED00100' as '/u03/Oradata/Prod_OTHER_0072.DAT'
*
ERROR at line 1:
ORA-01182: cannot create database file 10 - file is in use or recovery
ORA-01111: name for data file 10 is unknown - rename to correct file
ORA-01110: data file 10:
'/u01/app/oracle/product/11.2.0/prod/dbs/UNNAMED00100'

You just can’t create a datafile that already being online or is being recovered.

Solution:

SQL> shutdown immediate;
SQL> startup mount;
SQL>  < Issue above SQL Command >;

 

Thanks,

Stay Tune. 🙂

Oracle 11g Logo

ORA-27047: unable to read the header block of file Linux-x86_64 Error: 25: Inappropriate ioctl for device ORA-27048: skgfifi: file header information is invalid ORA-27072: File I/O error

While recovering my manual physical standby data guard configuration, Every time oracle would apply all archive logs to standby database and end up with following mentioned errors.

As a part of troubleshooting, I have Verified:

  • Archive log sequence number on both the side but that was in place.
  • Block corruptions on datafiles level on both sides. It was clean.
  • Physical storage blocks, It was also clean.
  • Almost verify everything, that i can.

After scratching my head for several hours, I found non oracle files situated on FRA. After moving it from FRA, Recovery process successfully carried out. Non oracle file was only culprit to the following mentioned errors. It was very strange case I have faced in my life.

Solution:

Remove all non oracle files from FRA.

Error logs:

SQL> recover database using backup controlfile until cancel;
ORA-00279: change 15198931 generated at 07/23/2015 17:55:02 needed for thread 1
ORA-00289: suggestion :
/u02/oradata/flash_recovery_area/DB1/archivelog/2015_07_23/o1_mf_1_5180_bv1qlby6_.arc
ORA-00280: change 15198931 for thread 1 is in sequence #5180
Specify log: {=suggested | filename | AUTO | CANCEL}
auto
ORA-00279: change 15199107 generated at 07/23/2015 18:00:02 needed for thread 1
ORA-00289: suggestion :
/u02/oradata/flash_recovery_area/DB1/archivelog/2015_07_23/o1_mf_1_5181_bv1qvq3n_.arc
ORA-00280: change 15199107 for thread 1 is in sequence #5181
ORA-00278: log file '/u02/oradata/flash_recovery_area/DB1/archivelog/2015_07_23/o1_mf_1_5180_bv1qlby6_.arc' no longer needed for this recovery
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
ORA-27048: skgfifi: file header information is invalid
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 1
Additional information: 392
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 1
ORA-27048: skgfifi: file header information is invalid
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 1
Additional information: 392
ORA-27047: unable to read the header block of file
Linux-x86_64 Error: 25: Inappropri

Thanks,

Stay Tune. 🙂

Oracle 12c Logo

ORA-30013: undo tablespace ‘UNDOTBS1’ is currently in use

ORA-30013: undo tablespace ‘UNDOTBS1’ is currently in use

ORA error message itself is self explanatory, When undo tablespace is in use and we will try to drop that tablespace than oracle pop up with ORA-30013 error.

Solution:
Wait until active transactions are COMMITED or ROLLED BACK. If so, you can easily drop mentioned tablespace.
OR
You can kill that transaction if it unnecessary.

Consider following example:

SQL> create tablespace newundotbs datafile '/u01/app/oracle/oradata/prod/newundotbs1.dbf' size 200M autoextend on maxsize 500M;
Tablespace created.
SQL> alter system set UNDO_TABLESPACE=NEWUNDOTBS scope=spfile;
System altered.

Following command will failed because there is active transactions.

SQL> drop tablespace undotbs1 including contents and datafiles;
drop tablespace undotbs1 including contents and datafiles
*
ERROR at line 1:
ORA-30013: undo tablespace 'UNDOTBS1' is currently in use

Query dba_rollback_segs to know list of online segments:

SQL> select tablespace_name, owner, segment_name, status from dba_rollback_segs where TABLESPACE_NAME='UNDOTBS1' and status='ONLINE';

Retrive SID and SERIAL# from below mentioned query in order to kill session with ‘alter system kill session’ command.

SQL> select SID, substr(username,1,10) username,serial#,segment_name from v$transaction,dba_rollback_segs,v$session where saddr=ses_addr and xidusn=segment_id;
SQL> alter system kill session 'SID,SERIAL#';
SQL> drop tablespace undotbs1 including contents and datafiles;
Tablespace dropped.

Tablespace successfully dropped.

Thanks
Stay Tune 🙂

DBvisit Standby

DBVisit standby configuration failed with ORA-19504, ORA-27040, RMAN-06026 and RMAN-06023

While configuring DBVisit standby disaster recovery software at one of my client end environment, when DBVisit standby configuration initiated it was smooth during backup of primary database, than transferred backup to DR server. When RMAN backup arrived at DR server and started creating standby database ( restore activity by RMAN ) DBVisit popped up with following error messages:

Error message 1:

ORA-19504: failed to create file "/RTS/prod/df/RTSapp_lob.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory

Error message 2:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/18/2015 16:06:05
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 7 found to restore

Diagnosis:

After giving hard diagnosis on above red highlighted error messages, It is came to know that it was the problem with /RTS/prod/df/ path.

On primary server there was multiple datafiles locations on multiple storage LUN’s for various tablespaces. DBVisit simply copied references from primary database server and searching exact path on secondary database server. But obviously same path would not available on secondary database server. Plus While taking RMAN backup at primary database server, RMAN recorded all path and locations while backup and try to search same path and location at secondary database server while restore activity. Which is again impossible. Due to above reasons DBVisit finally came out of configuration activity with above mentioned errors.

Solution:

As a part of solution I would have two choices available in my hand, one is to discard all secondary server configuration and start from scratch (OS, oracle DB & DBVist installation) to meed exactly same path and partition scheme of primary database linux server.

Second is to create links between two directories.

So finally I have created directory link between /RTS/prod/df/ to the new datafile location of standby database server: /home/app/datafile/ifsprod/

lrwxrwxrwx. 1 oracle oinstall 27 Jun 18 18:21 df -> /u01/app/datafile/RTSprod/

After applying above fix, Resumes DBVisit configuration and it was successfully configured.

For more details on above DBVisit error, Please have a look here.

Dbvisit Standby Database Technology message from PR
Message received from process: dbvisit_setup --csd --web --ddc prod
(Dbvisit Standby: 7.0.38.13873 Process id: 6691)
201506181606 - Error executing RMAN command:
Recovery Manager: Release 11.2.0.1.0 - Production on Thu Jun 18 16:06:02 2015
Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.
RMAN> 
connected to target database: prod (DBID=4119037639, not open)
using target database control file instead of recovery catalog
RMAN> 
RMAN configuration parameters for database with db_unique_name prod are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 10 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F';
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 1 TIMES TO DISK;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/ORACLE/app/oracle/product/11.2.0/db_1/dbs/snapcf_prod.f';
echo set off
RMAN> 
allocated channel: C_DBVISIT
channel C_DBVISIT: SID=202 device type=DISK
executing command: SET NEWNAME
Starting restore at 2015-06-18:16:06:04
channel C_DBVISIT: starting datafile backup set restore
channel C_DBVISIT: specifying datafile(s) to restore from backup set
channel C_DBVISIT: restoring datafile 00007 to /RTS/prod/df/RTSapp_lob.dbf
channel C_DBVISIT: reading from backup piece /MISC/temp_dbvisit/dbv_prod_csd_dbf_7_9vq9pgmr_1_1.rman
channel C_DBVISIT: ORA-19870: error while restoring backup piece /home/temp_dbvisit/dbv_prod_csd_dbf_7_9vq9pgmr_1_1.rman
ORA-19504: failed to create file "/RTS/prod/df/RTSapp_lob.dbf"
ORA-27040: file create error, unable to create file
Linux-x86_64 Error: 2: No such file or directory
failover to previous backup
released channel: C_DBVISIT
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 06/18/2015 16:06:05
RMAN-06026: some targets not found - aborting restore
RMAN-06023: no backup or copy of datafile 7 found to restore
RMAN>
Recovery Manager complete.
Dbvisit Standby terminated.
Return code = 8002
(Tracefile required if contacting Dbvisit Standby support: /usr/local/dbvisit/standby/trace/5828_dbv_functions_prod_201506181606.trc (server:DR))
Dbvisit Standby terminated.
Return code = 66
(Tracefile required if contacting Dbvisit Standby support: /usr/local/dbvisit/standby/trace/6691_dbvisit_setup_csd_prod_201506181008.trc (server:PR))

 

Cheers!!

Stay tune. 🙂

Oracle 11g Logo

Oracle 11gR2 software and database installation on CentOS 6.x (64-bit)

Here we are installing Oracle 11g Release-2 (11.2)(64-bit) on CentOS 6.x (64-bit)

Kindly have a look on Pre-requisites before proceeding for oracle 11gR2 installation.

Memory requirements:

Minimum 1 GB RAM, Recommended 2 GB or more than that.

Following commands will help you to determine RAM of your system:

 # grep MemTotal /proc/meminfo
 # free -m // shows RAM + SWAP

Note: If RAM of your system does not meet minimum requirement than increase it.

SWAP partition need for oracle installation is only depend on RAM installed in your system, according to oracle docs.

  1. If your RAM between 1 GB to 2 GB than your swap partition size should be 1.5 times the size of the RAM.
  2. If your RAM between 2 GB to 16 GB than your swap partition size should be equal to the size of the RAM.
  3. If your RAM more than 16 GB than your swap partition size should be 16 GB.

Disk space requirement:

/tmp directory size would be at least 1 GB.

Determine available disk space of /tmp directory with following command:

# df -h /tmp

Minimum need of total disk space of the system would directly depend upon size of your database, Oracle installation files take maximum 4.5 GB to 4.7GB of disk space.

Determine total disk space available with following:

# df -h

Verify your system architecture with following command:

# uname -m

If you fulfill your h/w pre-requisites than make sure following changes in your system in order to install oracle smoothly:

Update host file “/etc/hosts” with fully qualified name like below:

[root@c11g ~]# cat /etc/hosts
...
192.168.17.145 c11g.localdomain c11g

Note:
In my case “c11g.localdomain” is fully qualified name with machine name.

Oracle public yum repository are easily available, Here we use “oracle-rdbms-server-11gR2-preinstall” package to download latest Oracle Linux dependencies automatically.

[root@c11g ~]# cd /etc/yum.repos.d
[root@c11g yum.repos.d]# wget https://public-yum.oracle.com/public-yum-ol6.repo
[root@c11g yum.repos.d]# ll public-yum-ol6.repo
-rw-r–r–. 1 root root 5046 Jan 29 13:15 public-yum-ol6.repo

Install following package with YUM in order to complete oracle database installation pre-requisites automatically.

[root@c11g ~]# yum install oracle-rdbms-server-11gR2-preinstall

Above package installation may failed with “GPG key retrieval failed“, for solution download and verify GPG key that complies CentOS 6.x with the help of following and continue.

Error: GPG key retrieval failed: [Errno 14] Could not open/read file:///etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

[root@c11g ~]# wget https://public-yum.oracle.com/RPM-GPG-KEY-oracle-ol6 -O /etc/pki/rpm-gpg/RPM-GPG-KEY-oracle

Create oracle groups and users:

 groupadd -g 501 oinstall
 groupadd -g 502 dba
 groupadd -g 503 oper
 groupadd -g 504 asmadmin
 groupadd -g 506 asmdba
 groupadd -g 505 asmoper
 useradd -u 502 -g oinstall -G dba,asmdba,oper oracle

Set password for oracle user:

[root@c11g ~]# passwd oracle

Add nproc parameter to the “/etc/security/limits.d/90-nproc.conf” file.

[root@c11g ~]# cat /etc/security/limits.d/90-nproc.conf
# Default limit for number of user's processes to prevent
# accidental fork bombs.
# See rhbz #432903 for reasoning.
* soft nproc 1024
root soft nproc unlimited
# To this
* - nproc 16384

Set SELINUX ‘permissive’ by updating file “/etc/selinux/config”

SELINUX=permissive

Note: Reboot server after setting SELINUX disabled.

[root@c11g ~]# reboot

Create directory for oracle software and assigned proper permission in order to install oracle s/w smoothly.

[root@c11g ~]# mkdir -p /u01/app/oracle/product/11.2.0/dbhome_1
[root@c11g ~]# chown -R oracle:oinstall /u01
[root@c11g ~]# chmod -R 775 /u01

On GUI, open terminal prompt as a root user & issue the following command:

[root@c11g ~]# xhost +
access control disabled, clients can connect from any host
[root@c11g ~]# su - oracle

Run universal installer and follow the steps by step installation of oracle 11gR2:

./runIstaller

And follow the steps mentioned below:

Oracle 11g Release-2 (11.2)(64-bit) on CentOS 6.x (64-bit) - runInstaller

Oracle 11g Release-2 (11.2)(64-bit) on CentOS 6.x (64-bit) – runInstaller

 

Oracle 11g Release-2 installation on CentOS 6.x - Configure Security Updates

Oracle 11g Release-2 installation on CentOS 6.x – Configure Security Updates

 

You can directly create and configure a database by selecting first radio button. Other options are available to install oracle software only ( Not database ) and to upgrade existing database.

Oracle 11g Release-2 installation on CentOS 6.x - Configure Security Updates - Select Installation Option

Oracle 11g Release-2 installation on CentOS 6.x – Configure Security Updates – Select Installation Option

 

Select “Desktop Class” if you are installing on desktop else select “Server Class” in case of Server installation.

Oracle 11g Release-2 installation on CentOS 6.x - System Class

Oracle 11g Release-2 installation on CentOS 6.x – System Class

 

Ensure “Oracle base”, “Software location”, “Database file location”, “Database edition” and continue.

Provide administrative password according to oracle standards and continue.

Oracle 11g Release-2 installation on CentOS 6.x - Typical Install Configuration

Oracle 11g Release-2 installation on CentOS 6.x – Typical Install Configuration

 

Oracle 11g Release-2 installation on CentOS 6.x - Create Inventory

Oracle 11g Release-2 installation on CentOS 6.x – Create Inventory

 

Oracle 11g Release-2 installation on CentOS 6.x - Perform Prerequisites Check

Oracle 11g Release-2 installation on CentOS 6.x – Perform Prerequisites Check

 

If your system already installed latest version of mentioned packages, then you can ignore and continue. In my case, latest version available.

Oracle 11g Release-2 installation on CentOS 6.x - Perform Prerequisites Check

Oracle 11g Release-2 installation on CentOS 6.x – Perform Prerequisites Check

 

Ensure summary and continue.

Oracle 11g Release-2 installation on CentOS 6.x - Summary

Oracle 11g Release-2 installation on CentOS 6.x – Summary

 

Oracle 11g Release-2 installation on CentOS 6.x - Install Product

Oracle 11g Release-2 installation on CentOS 6.x – Install Product

 

Oracle 11g Release-2 installation on CentOS 6.x - Database Configuration Assistant

Oracle 11g Release-2 installation on CentOS 6.x – Database Configuration Assistant

After installation successfully completed, set environment variables ( like ORACLE_SID, ORACLE_HOME & PATH ) from oracle user and access your database after database startup as follows:

Oracle 11g Release-2 (11.2)(64-bit) on CentOS 6.x (64-bit) - Startup Database - SQL Prompt

Oracle 11g Release-2 (11.2)(64-bit) on CentOS 6.x (64-bit) – Startup Database – SQL Prompt

 

Cheers!!

Installation of Oracle 11g Release-2 (11.2)(64-bit) on CentOS 6.x (64-bit) successfully completed.

Stay Tune. 🙂

Oracle 12c Logo

ORA-03206: maximum file size of XXXX blocks in AUTOEXTEND clause is out of range

If you are creating SMALLFILE datafile (default) with 8k blocks with datafile size greater than 32 GB than oracle will pop up with ORA-03206 error.

Because oracle will allow only up to 32 GB of datafile size limit with 8k blocks, maximum data blocks per datafile : 2^22 -1 = 4194303. So oracle simply won’t allow you in this case.

If you wish more than 32 GB datafile with 8k blocks than your datafile should be create with BIGFILE keyword.

Calculations are as follows:

Oracle allows max number of datafile is 65533 and Maximum data blocks per datafile is 2^22 -1 = 4194303 blocks.

Formula to calculate max datafile size

max datafile size = db_block_size * maximum number of blocks

In our case:

max datafile size = 8 * 4194303 = 33554424/1024 = 32767.9921875 MB/1024 = 31.99999237060547 GB, i.e. 32 GB

Formula to calculate Max tablespace size:

Max tablespace size = 32 GB * max database file = 32 GB * 65533 = 2097056 GB/1024 = 2047.90625 TB

Error Log:

SQL> create temporary tablespace temp02
tempfile '/u02/oradata/db1/temp2_01.dbf' size 30G autoextend on maxsize 70G; 2
create temporary tablespace temp02
*
ERROR at line 1:
ORA-03206: maximum file size of (9175040) blocks in AUTOEXTEND clause is out of range

Solution:

Create tempfile with less than 32 GB size or use BIGFILE keyword.

In above example, I am facing ORA-03206 with tempfile creation, you may face the problem with datafile too. Same solution will be applicable.

Other area’s you may get same error:

If your existing datafile exceeds limit of 32 GB, than you will receive same error message, solution is to add new SMALLFILE datafile to the associated tablespace.

Stay Tune. 🙂

Oracle 11g Logo

ORA-19502: write error on file “”, block number xxxxxxxx (block size=8192)

Today morning, My one of the RMAN full database backup failed with RMAN-03002, ORA-19502 and ORA-27072 error. After diagnosing the problem it was space issue only.

There is no space available for RMAN full backup.

Error log:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of backup plus archivelog command at 08/06/2015 01:16:57
ORA-19502: write error on file "/BACKUP/PROD/weekly_full/bkup_20150606_0vq8p2h0_1_1.rman", block number 2873985 (block size=8192)
ORA-27072: File I/O error
Linux-x86_64 Error: 25: Inappropriate ioctl for device
Additional information: 4
Additional information: 2873985
Additional information: 696320

Solution:

Just reclaim the storage space by setting appropriate retention to RMAN backup policy and deleted some old files that not needed for any kind of recovery.

By doing above steps, RMAN backup started successfully.

Stay Tune. 🙂

Oracle 11g Logo

How to configure Enterprise Manager with Database Configuration Assistant

This article is about step by step approach to configure EM database control with DBCA.

Where as in earlier article we Configure Enterprise Manager Database Control Manually with Enterprise Manager Configuration Assistant.

Before start configuration:

  1. Ensure your listener properly configured and started.
  2. Ensure environment variable properly set.
  3. Ensure password file is created. (Optional)
  4. Ensure your /etc/hosts file mentioned below two lines.
[oracle@prod ~]$ cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
192.168.17.146 prod.localdomain prod

Set Environment Variable and instantiate DBCA utility as below:

[oracle@prod ~]$ export ORACLE_SID=orcl
[oracle@prod ~]$ export ORACLE_UNQNAME=orcl
[oracle@prod ~]$ export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1/
[oracle@prod ~]$ export PATH=$PATH:/home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/
Instantiate Database Configuration Assistant

Instantiate Database Configuration Assistant

Database Configuration Assistant - Welcome

Select Configure Database Option and continue.

DBCA - Configure Database Option

DBCA – Configure Database Option

Select Database Instance and continue, In my case I have only ORCL database installed.

DBCA - Select Database Instance

DBCA – Select Database Instance

Select Configure Enterprise Manager check box and continue.

DBCA - Configure Enterprise Manager

DBCA – Configure Enterprise Manager

DBCA - Database content

DBCA – Database content

Provide SYS user credential and continue.

DBCA - Database Credentials

DBCA – Database Credentials

DBCA - Connection Mode

DBCA – Connection Mode

DBCA - Confirmation

DBCA – Confirmation

DBCA - Progress

DBCA – Progress

Configuration has completed successfully. Ensure your Database Control URL mentioned in snap shot, In my case its: https://prod.localdomain:5500/em.

Database Configuration Assistant - Enterprise Manager

Database Configuration Assistant – Enterprise Manager

To access your Enterprise Manager, Browse URL: https://localhost.localdomain:5500/em, like below and Confirm security Exception.

Enterprise Manager URL - Add Exception to browser

Enterprise Manager URL – Add Exception to browser

Enterprise Manager URL - Confirm Security Exception

Enterprise Manager URL – Confirm Security Exception

Provide Login credentials of SYS users to access EM for ORCL instance.

Enterprise Manager Login

Enterprise Manager Login

Enterprise Manager Instance Dashbord for ORCL.

Enterprise Manager Instance Dashbord

Enterprise Manager Instance Dashbord

Cheers!! Enterprise Manager configured Successfully.

Stay Tune. 🙂

Oracle 11g Logo

How to De-configure Enterprise Manager Database Control Manually with Enterprise Manager Configuration Assistant

Following is the only step by step approach to drop EM repos.

Before that:

  1. Ensure environment variable properly set.
  2. Ensure your listener properly configured and started.
  3. Ensure password file is created. (Optional)
  4. You need SYS,SYSMAN & DBSNMP password.
  5. Ensure your /etc/hosts file mentioned below two lines.
[oracle@prod ~]$ cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
192.168.17.146 prod.localdomain prod

Environment Variable:

export ORACLE_SID=orcl
export ORACLE_UNQNAME=orcl
export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1/
export PATH=$PATH:/home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/

Instantiate following command to deconfig Database Control with the help of EMCA.

[oracle@prod bin]$ ./emca -deconfig dbcontrol db -repos drop
STARTED EMCA at Apr 4, 2015 3:48:11 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:
Database SID: orcl
Listener port number: 1521
Password for SYS user: 
Password for SYSMAN user: 
Password for SYSMAN user: 
Do you wish to continue? [yes(Y)/no(N)]: Y
Apr 4, 2015 3:48:25 AM oracle.sysman.emcp.EMConfig perform
INFO: This operation is being logged at /home/oracle/app/oracle/cfgtoollogs/emca/orcl/emca_2015_04_04_03_48_10.log.
Apr 4, 2015 3:48:25 AM oracle.sysman.emcp.util.DBControlUtil stopOMS
INFO: Stopping Database Control (this may take a while) ...
Apr 4, 2015 3:48:31 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Dropping the EM repository (this may take a while) ...
Apr 4, 2015 3:50:13 AM oracle.sysman.emcp.EMReposConfig invoke
INFO: Repository successfully dropped
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 4, 2015 3:50:17 AM

Cheers!! Database Control de-configure successfully.

Stay Tune. 🙂

Oracle 11g Logo

Oracle Enterprise Manager failed to start, OC4J Configuration issue. Configure Enterprise Manager Database Control Manually with Enterprise Manager Configuration Assistant

OC4J Configuration issue… OC4J_DBConsole_ not found.

I am trying to start EM on my newly created database, while starting EM, getting failed with OC4J Configuration issue… while diagnosed there is no OC4J_DBConsole_prod directory exist in ORACLE_HOME. Means this may be the case where Database Control not configured while database created OR misconfigured.

Error log

[oracle@prod ~]$ emctl start dbconsole
OC4J Configuration issue. /home/oracle/app/oracle/product/11.2.0/dbhome_1/oc4j/j2ee/OC4J_DBConsole_prod.localdomain_orcl not found.

Solution is to Configure EM with the help of following “Enterprise Manager Configuration Assistant” (i.e. emca) utility from Oracle user: Before that:

  1. Ensure environment variable properly set like below.
  2. Ensure your listener properly configured and started.
  3. Ensure password file is created. (Optional)
  4. You need SYS,SYSMAN & DBSNMP password.
  5. Ensure your /etc/hosts file mentioned below two lines.
[oracle@prod ~]$ cat /etc/hosts
127.0.0.1 localhost.localdomain localhost
192.168.17.146 prod.localdomain prod

Environment Variable:

[oracle@prod ~]$ export ORACLE_SID=orcl
[oracle@prod ~]$ export ORACLE_UNQNAME=orcl
[oracle@prod ~]$ export ORACLE_HOME=/home/oracle/app/oracle/product/11.2.0/dbhome_1/
[oracle@prod ~]$ export PATH=$PATH:/home/oracle/app/oracle/product/11.2.0/dbhome_1/bin/

Config database control repos:

[oracle@prod ~]$ emca -config dbcontrol db -repos create
STARTED EMCA at Apr 4, 2015 4:02:34 AM
EM Configuration Assistant, Version 11.2.0.0.2 Production
Copyright (c) 2003, 2005, Oracle. All rights reserved.
Enter the following information:

Provide relevant information like below, orcl is my instance and 1521 is default listener port:

Database SID: orcl
Listener port number: 1521
Listener ORACLE_HOME [ /home/oracle/app/oracle/product/11.2.0/dbhome_1 ]: 
Password for SYS user: 
Password for DBSNMP user: 
Password for SYSMAN user: 
Email address for notifications (optional): 
Outgoing Mail (SMTP) server for notifications (optional): 
-----------------------------------------------------------------
You have specified the following settings
Database ORACLE_HOME ................ /home/oracle/app/oracle/product/11.2.0/dbhome_1
Local hostname ................ prod.localdomain
Listener ORACLE_HOME ................ /home/oracle/app/oracle/product/11.2.0/dbhome_1
Listener port number ................ 1521
Database SID ................ orcl
Email address for notifications ............... 
Outgoing Mail (SMTP) server for notifications ...............
-----------------------------------------------------------------
Do you wish to continue? [yes(Y)/no(N)]: Y
....
Enterprise Manager configuration completed successfully
FINISHED EMCA at Apr 4, 2015 4:10:55 AM

To access your Enterprise Manager, Browse URL: https://localhost.localdomain:5500/em, like below and Confirm security Exception.

Enterprise Manager URL - Add Exception to browser

Enterprise Manager URL – Add Exception to browser

 

Enterprise Manager URL - Confirm Security Exception

Enterprise Manager URL – Confirm Security Exception

 

Provide Login credentials of SYS users:

Enterprise Manager Login

Enterprise Manager Login

 

Enterprise Manager Instance Dashbord for ORCL, Now you are free to continue your work.

Enterprise Manager Instance Dashbord

Enterprise Manager Instance Dashbord

Cheers!! EM configured successfully.

Stay Tune. 🙂

PostgreSQL Logo

ANALYZE – PostgreSQL Database Maintenance

ANALYZE collects statistics about the database, i.e. contents of the tables and updates all statistics to pg_statistic system catalog.
Resulting Query planner uses pg_statistic system catalog to find efficient query execution plan.
ANALYZE acquiring only READ LOCK on target table, so that PostgreSQL can run parallel on same table.

Syntax:

ANALYZE [ VERBOSE ] [ table [ ( column [, ...] ) ] ]

Parameters:

Plain ANALYZE: Examines all tables in database.

ANALYZE table_name: Examines only provided table. In case of provides list of columns than ANALYZE only examines provided columns.

VERBOSE: Provides progress messages of the ANALYZE.

AutoVacuum daemon is take care of automatic analyzing of tables when they are first loaded to database, and throughout regular change in operations. When AutoVacuum is not configured than its recommended activity to perform ANALYZE as database maintenance task.

To simulate environment, Consider following examples on ANALYZE:

Ex 1 : Plain ANALYZE on Postgres default database.

Plain ANALYZE

Plain ANALYZE

 

Ex 2 : ANALYZE with VERBOSE on Postgres default database.

ANALYZE with VERBOSE

ANALYZE with VERBOSE

 

Ex 3 : ANALYZE specific table with VERBOSE on Postgres default database.

ANALYZE specific table with VERBOSE

ANALYZE specific table with VERBOSE

Note: Table demo is created for only to demonstrate above ANALYZE execution, No rows inserted into it. Thats why message tab shows 0 live rows and 0 dead rows.

Cheers!!

PostgreSQL Logo

VACUUM – PostgreSQL Database Maintenance

VACUUM Performs key role in PostgreSQL database maintenance task for performance gain.

Key Benefits to perform VACUUM periodically is to Reclaim storage space occupied by dead tuples. When delete or obsolete performed, tuples are not remove physically i.e. dead tuples. With VACUUM we can reclaim that occupied space.

Syntax:

VACUUM [ ( { FULL | FREEZE | VERBOSE | ANALYZE } [, ...] ) ] [ table [ (column [, ...] ) ] ]

Plain VACUUM:

  • VACUUM process all tables.
  • Reclaim and make space available for reuse.
  • No exclusive lock on table, operates in parallel with normal reading and writing.
  • Reclaimed extra space reused for the table instead of return back to OS.(in most cases)
  • Recommended on daily basis.

FULL:

  • Exclusive lock on table while VACUUM.
  • Rewrites whole table into a new disk file with no extra space.
  • Reclaimed extra space return back to OS.
  • Slower than Plain VACUUM.
  • Recommended weekly not for daily used,

FREEZE:

  • Selects aggressive “freezing” of tuples.

VERBOSE:

  • Provides progress messages of the VACUUM.

ANALYZE:

  • Enhance execution of query by determining most efficient way by updating statistics used by planner.

Notes:

  • User need to be table owner or Super user to perform VACUUM.
  • After large amount of DML operations, It’s highly recommended to perform VACUUM ANALYZE in order to update system catalogs, It allows query planner to determine best future plan for query execution.
  • If large amount of update OR delete transactions suppose to perform on table, It’s good idea to FULL VACUUM that table immediately for performance point of view.
  • VACUUM increases number of I/O during activity, which may be culprit for poor DB performance for the session, So you can use Cost-based Vacuum Delay feature.

Examples:

Ex 1: Plain VACUUM on PostgreSQL default database.

Plain VACUUM

Plain VACUUM

 

Ex 2 : Plain VACUUM with Verbose on PostgreSQL default database.

Plain Vacuum with Verbose

Plain Vacuum with Verbose

 

Ex 3 : FULL VACUUM on PostgreSQL default database.

VACUUM Full

VACUUM Full

 

Ex 4 : FULL VACUUM with Verbose on PostgreSQL default database.

Vacuum Full with Verbose

Vacuum Full with Verbose

 

Ex 5 : FULL VACUUM with ANALYZE and Verbose on PostgreSQL default database.

Vacuum Full with Analyze and Verbose

Vacuum Full with Analyze and Verbose

 

Ex 6 : VACUUM on specific table with ANALYZE and verbose, In my case demo table is created to simulate example only.

VACUUM Table

VACUUM Table

 

This is how the vacuum is beneficial for postgreSQL database maintenance activity.

Oracle 11g Logo

How to relocate/move oracle datafiles in Oracle 11g

We can move/relocate oracle datafile to different location, In this article, we will move datafile with the help of Recovery Manager.

Consider step by step approach to move oracle datafile as below:

Step I:

Get database datafile ID those you wanted to move with the help of REPORT SCHEMA rman command as below:

 RMAN> REPORT SCHEMA;
 Report of database schema for database with db_unique_name PROD
 List of Permanent Datafiles
 ===========================
 File Size(MB) Tablespace RB segs Datafile Name
 ---- -------- -------------------- ------- ------------------------
 1 8880 SYSTEM YES /DATA1/database/prod/system01.dbf
 2 2710 SYSAUX NO /DATA1/database/prod/sysaux01.dbf
 3 17255 UNDOTBS1 YES /DATA1/database/prod/undotbs01.dbf
 4 25 USERS NO /DATA1/database/prod/users01.dbf
 5 6444 PROD_DATA NO /DATA1/database/prod/prodapp_data.dbf
 6 5634 PROD_INDEX NO /DATA1/database/prod/prodapp_index.dbf
 7 32744 PROD_LOB NO /DATA1/database/prod/prodapp_lob.dbf
 List of Temporary Files
 =======================
 File Size(MB) Tablespace Maxsize(MB) Tempfile Name
 ---- -------- -------------------- ----------- --------------------
 1 500 TEMP_NEW 32767 /DATA/database/prod/temp_01.dbf

Note: To simulate scenario, I am going to move PROD_LOB tablespace( i.e. FILE ID:7 ) from ‘/DATA1/database/prod/’ to new location ‘/NewData/prodDB/df/’.

Step II:

Offline tablespace that you want to move.

 RMAN> SQL 'ALTER TABLESPACE PROD_LOB OFFLINE';

Step III:

Copy all the datafiles to destination, In our case PROD_LOB tablespace having only one datafile.

 RMAN> COPY DATAFILE 7 TO '/NewData/prodDB/df/prodapp_lob.dbf';

Step IV:

Switch back to the new datafile copy(s)

 RMAN> SWITCH DATAFILE 7 TO COPY;

Step V:

Bring offline Tablespace to online state with the help of SQL command: ‘ALTER TABLESPACE’.

 RMAN> SQL 'ALTER TABLESPACE PROD_LOB ONLINE';

Step VI:

Ensure above changes with the help of REPORT SCHEMA rman command as below:
 RMAN> REPORT SCHEMA;
 ...
 File Size(MB) Tablespace RB segs Datafile Name
 ---- -------- -------------------- ------- ------------------------
 ...
 7 32744 PROD_LOB NO /NewData/prodDB/df/prodapp_lob.dbf

Step VII:

If Tablespace get back to online state successfuly, than old datafile is useless. Remove it.

 RMAN> HOST 'rm /DATA1/database/prod/prodapp_lob.dbf';

Cheers!! Datafile relocated successfully.

Related Article

Online Move Datafiles in Oracle 12c R1 without getting file offline

 

Oracle 12c Logo

Creating Partial Redaction Policy – Data Redaction Part-IV

Here we are going to discuss Partial Redaction Policy with following points where as in my earlier article we have discussed Full Data Redaction Policy.

  1. Create Partial Redaction Policy
  2. Syntax
  3. Hands-on on Partial Redaction Policy using fixed character short-cut.
  4. Hands-on on Partial Redaction Policy using character data type.
  5. Hands-on on Partial Redaction Policy using number data type.

Lets consider one by one as below:

Create Partial Redaction Policy

Partial Redaction Policy is about Portion of the data would be redacted. For Ex: In your bank monthly statement email, Your account number might redacted in order to read only last 4 digit and rest might replace with ‘*’ or Big DOT.

Note: We can only redact column with character, number, or date-time data types.

Syntax of Creating Partial Redaction Policy

 DBMS_REDACT.ADD_POLICY (
 object_schema IN VARCHAR2 := NULL,
 object_name IN VARCHAR2,
 column_name IN VARCHAR2 := NULL,
 policy_name IN VARCHAR2,
 function_type IN BINARY_INTEGER := NULL,
 function_parameters IN VARCHAR2 := NULL,
 expression IN VARCHAR2,
 enable IN BOOLEAN := TRUE);

function_type: In order to create Partial Redaction, we use DBMS_REDACT.PARTIAL function type.

Hands-on on Partial Redaction Policy using fixed character short-cuts

We can create partial redaction policy with the help of DBMS_REDACT.ADD_POLICY with predefined fixed character short-cut function parameters.

Some function_parameters parameter shortcuts are as below:

DBMS_REDACT.REDACT_US_SSN_F5: Redact first 5 numbers of social security number. Datatype: VARCHAR2. Ex: 546-76-3245 becomes XXX-XX-3245.
DBMS_REDACT.REDACT_US_SSN_L4: Redact last 4 numbers of social security number. Datatype: VARCHAR2. Ex: 546-76-3245 becomes 546-76-XXXX.
DBMS_REDACT.REDACT_US_SSN_ENTIRE: Redact entire social security number. Datatype: VARCHAR2. Ex: 546-76-3245 becomes XXX-XX-XXXX.
DBMS_REDACT.REDACT_NUM_US_SSN_F5: Redact first 5 numbers of social security number. Datatype: NUMBER. Ex: 546763245 becomes XXXXX3245.
DBMS_REDACT.REDACT_NUM_US_SSN_L4: Redact last 4 numbers of social security number. Datatype: NUMBER. Ex: 54676XXXX.
DBMS_REDACT.REDACT_NUM_US_SSN_ENTIRE: Redact entire social security number. Datatype: NUMBER. Ex: 546763245 becomes XXXXXXXXX.
DBMS_REDACT.REDACT_ZIP_CODE: Redact entire 5 digit postal code. Datatype: VARCHAR2. Ex: 96745 becomes XXXXX.
DBMS_REDACT.REDACT_NUM_ZIP_CODE: Redact entire 5 digit postal code. Datatype: NUMBER. Ex: 96745 becomes XXXXX.
DBMS_REDACT.REDACT_DATE_MILLENNIUM: Redacts dates.(DD-MON-YY) becomes 01-JAN-00 (January 1, 2000)
DBMS_REDACT.REDACT_DATE_EPOCH: Redacts all dates to 01-JAN-70.
DBMS_REDACT.REDACT_CCN16_F12: Redact first 12 digit of 16 digit credit card number. 1234-5678-9101-1213 becomes ****-****-****-1213.

To simulate scenario, consider following hands-on on redaction of US social security number upto first 5 number with the help of fixed character short-cuts.

Create table EMPSSN with id,name and ssn column as character datatype as below:

SQL> create table EMPSSN(
 id number(3),
 name varchar2(10),
 ssn varchar2(11));

Insert values accordingly:

 SQL> insert into EMPSSN values(1,'xyz','456-54-3456');
 SQL> insert into EMPSSN values(2,'pqr','955-23-3600');
 SQL> insert into EMPSSN values(3,'abc','652-52-4958');
 SQL> commit;

Query SSN column from EMPSSN table:

 SQL> select * from EMPSSN;
 ID         NAME       SSN
 ---------- ---------- -----------
 1          xyz        456-54-3456
 2          pqr        955-23-3600
 3          abc        652-52-4958

Create partial redacted policy with DBMS_REDACT.REDACT_US_SSN_F5 character short-cut as below:

 BEGIN
 DBMS_REDACT.ADD_POLICY(
 object_schema => 'C##SCOTT',
 object_name => 'EMPSSN',
 column_name => 'ssn',
 policy_name => 'scott_empssn_ssn',
 function_type => DBMS_REDACT.PARTIAL,
 function_parameters => DBMS_REDACT.REDACT_US_SSN_F5,
 expression => '1=1');
 END;
 /

After creating above policy, query SSN column from EMPSSN, you will find redacted Social security number with last 4 digit visible.

 SQL> select * from EMPSSN;
 ID         NAME       SSN
 ---------- ---------- -----------
 1          xyz        XXX-XX-3456
 2          pqr        XXX-XX-3600
 3          abc        XXX-XX-4958

This is about Partial Redaction Policy using fixed character short-cuts

 

Hands-on on Partial Redaction Policy using character data type

To simulate scenario create EMPSSN_CHAR table with id, name and ssn column with datatype: varchar2.

 SQL> create table EMPSSN_CHAR(
 id number(3),
 name varchar2(10),
 ssn varchar2(11));

Insert values accordingly:

 SQL> insert into EMPSSN_CHAR values(1,'xyz','456-54-3456');
 SQL> insert into EMPSSN_CHAR values(2,'pqr','955-23-3600');
 SQL> insert into EMPSSN_CHAR values(3,'abc','652-52-4958');
 SQL> commit;

Query SSN column from EMPSSN_CHAR as below:

 SQL> select * from EMPSSN_CHAR;
 ID         NAME       SSN
 ---------- ---------- -----------
 1          xyz        456-54-3456
 2          pqr        955-23-3600
 3          abc        652-52-4958

Now, create partial redaction policy function_type: DBMS_REDACT.PARTIAL

 BEGIN
 DBMS_REDACT.ADD_POLICY(
 object_schema => 'C##SCOTT',
 object_name => 'EMPSSN_CHAR',
 column_name => 'ssn',
 policy_name => 'c##scott_EMPSSN_CHAR_ssn',
 function_type => DBMS_REDACT.PARTIAL,
 function_parameters => 'VVVFVVFVVVV,VVV-VV-VVVV,*,1,5',
 expression => '1=1');
 END;
 /

Function parameter gives idea about formatting of redaction output.
1st Parameter: Character ‘V’ can be potentially redacted. And character ‘F’ can be use for formatting character like hyphens or blank spaces.
2nd Parameter: asterisk(*) use to mask character, specifies the character to be used for the redaction.
3rd Parameter: Starting digit position: In our case: 1
4th Parameter: Ending digit position: In our case: 5

After creating above policy, query SSN column from EMPSSN_CHAR table, you will find Social Security Number redacted upto first 5 characters and visible last 4 characters, as below:

SQL> select * from EMPSSN_CHAR;
 ID         NAME       SSN
 ---------- ---------- -----------
 1          xyz        ***-**-3456
 2          pqr        ***-**-3600
 3          abc        ***-**-4958

This is about Partial Redaction Policy using character data type.

 

Hands-on on Partial Redaction Policy using number data type

To simulate scenario create EMPSSN_NUM table with id, name and ssn column with datatype: number as below:

 SQL> create table EMPSSN_NUM(
 id number(3),
 name varchar2(10),
 ssn number(9));

Query SSN column from EMPSSN_NUM table as below:

 SQL> select * from EMPSSN_NUM;
 ID NAME SSN
 ---------- ---------- ----------
 1 xyz 456543456
 2 pqr 955233600
 3 abc 652524958

Insert values accordingly to above created table:

 SQL> insert into EMPSSN_NUM values(1,'xyz',456543456);
 SQL> insert into EMPSSN_NUM values(2,'pqr',955233600);
 SQL> insert into EMPSSN_NUM values(3,'abc',652524958);
 SQL> commit;

Now, create partial redaction policy function_type: DBMS_REDACT.PARTIAL

 BEGIN
 DBMS_REDACT.ADD_POLICY(
 object_schema => 'C##SCOTT',
 object_name => 'EMPSSN_NUM',
 column_name => 'ssn',
 policy_name => 'C##SCOTT_EMPSSN_NUM_ssn',
 function_type => DBMS_REDACT.PARTIAL,
 function_parameters => '0,1,5',
 expression => '1=1');
 END;
 /

Function parameter gives idea about formatting of redaction output.
1st parameter: Mask character.
2nd parameter: Starting digit position.
3rd parameter: Ending digit position.

Query SSN column from EMPSSN_NUM table as below, you will find first 5 character of SSN column has been redacted with zero ‘0’.

SQL> select * from EMPSSN_NUM;
 ID         NAME       SSN
 ---------- ---------- ----------
 1          xyz             3456
 2          pqr             3600
 3          abc             4958

This is about Partial Redaction Policy using number data type.

Stay Tune. 🙂

Oracle 12c Logo

Full Data Redaction Policy – Data Redaction Part-III

Full Data Redaction can redact entire column data. Redacted value returned to the querying application depends on the data type of the column. Ex: Character data type would redact to single space or NUMBER data type would redact zero.

Here we are going to discuss following topics where as in my earlier article we covered Configuring Data Redaction Policies.

  • Creating full data redaction policy.
  • Syntax
  • Examples of full data redaction policies.
    • Example based on Number data type.
    • Example based on Character data type.
  • Altering the Default Full Data Redaction Value.
  • Hands-on on altering the Default Full Data Redaction Value.

Lets start one by one:

Creating full data redaction policy

To create data redaction policy with full redaction, you need to set unction_type parameter to DBMS_REDACT.FULL in DBMS_REDACT.ADD_POLICY syntax. By default, Number would be redacted by zero (0) and character would be redacted by single space. Default values can be modify with the help of DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure.

Syntax for creating Full Data Redaction policy:

DBMS_REDACT.ADD_POLICY (
 object_schema IN VARCHAR2 := NULL, 
 object_name IN VARCHAR2,
 column_name IN VARCHAR2 := NULL,
 policy_name IN VARCHAR2,
 function_type IN BINARY_INTEGER := NULL,
 expression IN VARCHAR2,
 enable IN BOOLEAN := TRUE);

Examples of full data redaction policies

Example 1:

Let me demonstrate full data redaction for all the values of MOBILE column from C##SCOTT.EMP table. When any user will access the MOBILE column, expression parameter would apply policy except users who have been granted with EXEMPT REDACTION POLICY system privilege.

Let me connect to C##SCOTT user and create EMP table to simulate environment.

SQL> show user
USER is "C##SCOTT"
create table EMP
(
empid number(5),
ename varchar2(30),
mobile number(11)
);
Table created.

Insert below 5 records to EMP table:

SQL> insert into EMP values(1,'scott',1234567898);
SQL> insert into EMP values(2,'system',1234567898);
SQL> insert into EMP values(3,'sys',1234567898);
SQL> insert into EMP values(4,'abc',1234567898);
SQL> insert into EMP values(5,'xyz',1234567898);
SQL> commit;

Query EMP table:

SQL> select * from emp;
EMPID     ENAME                          MOBILE
---------- ------------------------------ ----------
 1         scott                          1234567898
 2         system                         1234567898
 3         sys                            1234567898
 4         abc                            1234567898
 5         xyz                            1234567898

Create full data redaction policy with name: ‘full_redact_mobile’ on MOBILE column of EMP table as below:

SQL> BEGIN
 DBMS_REDACT.ADD_POLICY(
 object_schema => 'C##SCOTT',
 object_name => 'EMP',
 column_name => 'MOBILE',
 policy_name => 'full_redact_mobile',
 function_type => DBMS_REDACT.FULL,
 expression => '1=1');
END;
/
 function_type => DBMS_REDACT.FULL,
                  *
ERROR at line 7:
ORA-06550: line 7, column 24:
PLS-00201: identifier 'DBMS_REDACT' must be declared
ORA-06550: line 2, column 2:
PL/SQL: Statement ignored

Above full data redaction policy creation failed with “PLS-00201: identifier ‘DBMS_REDACT’ must be declared” Because we are forgotten to grant DBMS_REDACT procedure execute permission to C##SCOTT user.

Grant EXECUTE permission to C##SCOTT user as below:

SQL> grant execute on DBMS_REDACT to C##SCOTT;
Grant succeeded.

Policy created successfully after assigning execute privilege:

 SQL> BEGIN
 DBMS_REDACT.ADD_POLICY(
 object_schema => 'C##SCOTT',
 object_name => 'EMP',
 column_name => 'MOBILE',
 policy_name => 'full_redact_mobile',
 function_type => DBMS_REDACT.FULL,
 expression => '1=1');
END;
/
PL/SQL procedure successfully completed.

Now query EMP table and you will find redacted MOBILE column as zero (default value)

SQL> select * from emp;
EMPID     ENAME                          MOBILE
---------- ------------------------------ ----------
 1         scott                          0
 2         system                         0
 3         sys                            0
 4         abc                            0
 5         xyz                            0

This is about full data redaction in terms of NUMBER.

 

Example 2:

Let me demonstrate full data redaction for all the values of ENAME column from C##SCOTT.NEW_EMP table.
Note: We can’t consider same EMP table for character data redaction because according to data redaction rules, only one redaction policy can be configure per table.

Creating NEW_EMP table as below:

create table NEW_EMP
(
empid number(5),
ename varchar2(30),
mobile number(11)
);

Insert below values to NEW_EMP as below:

SQL> insert into NEW_EMP values(1,'test',1234567898);
SQL> insert into NEW_EMP values(2,'test',1234567898);
SQL> insert into NEW_EMP values(3,'test',1234567898);
SQL> commit;

Query NEW_EMP table:

 SQL> select * from new_emp;
 EMPID     ENAME                          MOBILE
---------- ------------------------------ ----------
 1         test                           1234567898
 2         test                           1234567898
 3         test                           1234567898

Create full data redaction policy with name:’full_redact_ename’ on ENAME column on newly created table. i.e. NEW_EMP:

 SQL> BEGIN
 DBMS_REDACT.ADD_POLICY(
 object_schema => 'C##SCOTT',
 object_name => 'NEW_EMP',
 column_name => 'ENAME',
 policy_name => 'full_redact_ename',
 function_type => DBMS_REDACT.FULL,
 expression => '1=1');
 END;
 /

Query NEW_EMP table and you will find ENAME column is redacted with single blank space (default) as below:

 SQL> select * from new_emp;
 EMPID     ENAME                          MOBILE
---------- ------------------------------ ----------
 1                                        1234567898
 2                                        1234567898
 3                                        1234567898

 

Altering the Default Full Data Redaction Value.

To alter, Use DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure to modify default value. for Ex: zero is the default redacted value in case of full redaction for number data type. In case of modification of this value we need to run DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES procedure. This modification is applicable to every data redaction policy within instance.

Note: Database bounce would require to take effect of changed value.

Find current default value by querying REDACTION_VALUES_FOR_TYPE_FULL data dictionary view. With this data dictionary view you can find out current default values of NUMBER_VALUE, BINARY_FLOAT_VALUE, BINARY_DOUBLE_VALUE, CHAR_VALUE, VARCHAR_VALUE, NCHAR_VALUE, NVARCHAR_VALUE, DATE_VALUE, TIMESTAMP_VALUE, TIMESTAMP_WITH_TIME_ZONE_VALUE, BLOB_VALUE, CLOB_VALUE and NCLOB_VALUE data type.

Lets consider hands-on to change full data redaction default value for number data type, i.e. zero (0)

Connect as sysdba and query REDACTION_VALUES_FOR_TYPE_FULL data dictionary view for default full data redacted value for NUMBER data type as below:

[oracle@OL712c ~]$ sqlplus / as sysdba
SQL> select NUMBER_VALUE from REDACTION_VALUES_FOR_TYPE_FULL;
NUMBER_VALUE
------------
 0

Execute ‘UPDATE_FULL_REDACTION_VALUES’ of ‘DBMS_REDACT’ procedure in order to change default value to three (3) as below:

SQL> EXEC DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES (number_val => 3);
PL/SQL procedure successfully completed.

Likewise you can modify following values:

binfloat_val : BINARY_FLOAT data type.
bindouble_val : BINARY_DOUBLE data type.
char_val : CHAR data type.
varchar_val : VARCHAR2 data type.
nchar_val : NCHAR data type.
nvarchar_val : NVARCHAR2 data type.
date_val : DATE data type.
ts_val : TIMESTAMP data type.
tswtz_val : TIMESTAMP WITH TIME ZONE data type.
blob_val : BLOB data type.
clob_val : CLOB data type.
nclob : NCLOB data type.

Again query REDACTION_VALUES_FOR_TYPE_FULL data dictionary view and default data redaction value for NUMBER has changed to three (3).

SQL> select NUMBER_VALUE from REDACTION_VALUES_FOR_TYPE_FULL;
NUMBER_VALUE
------------
 3

Bounce back your instance in order to reflect change in your environment:

SQL> shutdown immediate;
SQL> startup;
SQL> exit;

Connect to C##SCOTT user and query EMP table, you will find data redacted value for NUMBER data type to three (3).

[oracle@OL712c ~]$ sqlplus c##scott/tiger
SQL> select * from emp;
 EMPID     ENAME                          MOBILE
---------- ------------------------------ ----------
 1         scott                          3
 2         system                         3
 3         sys                            3
 4         abc                            3
 5         xyz                            3

This is about Full Data Redaction Policy, Kindly stay tune with my next article about Partial Redaction Policy.

Oracle 12c Logo

ORA-01045: user C##SCOTT lacks CREATE SESSION privilege; logon denied

Oracle Error:

ORA-01045: user C##SCOTT lacks CREATE SESSION privilege; logon denied

Scenario:

Here, I was trying to access C##SCOTT user immediately after creation of it, Above mentioned oracle error itself self-explanatory. I had forgotten to grant CONNECT privilege to C##SCOTT user. After granting mentioned privileges, I was able to connect C##SCOTT user.

Error logs and solution:

SQL> create user C##SCOTT identified by tiger default tablespace users;
User created.
SQL> exit

Trying to connect C##SCOTT user:

[oracle@OL712c ~]$ sqlplus c##scott/tiger
SQL*Plus: Release 12.1.0.2.0 Production on Wed Dec 17 05:13:19 2014
Copyright (c) 1982, 2014, Oracle. All rights reserved.
ERROR:
ORA-01045: user C##SCOTT lacks CREATE SESSION privilege; logon denied

Connect as sysdba and grant required privileges:

[oracle@OL712c ~]$ sqlplus / as sysdba
SQL> grant connect,resource to C##SCOTT;
Grant succeeded.
SQL> exit

Successfully connect C##SCOTT after assigning required privileges.

[oracle@OL712c ~]$ sqlplus c##scott/tiger
SQL> show user;
USER is "C##SCOTT"

It was only problem with required privilege.

Oracle 12c Logo

ORA-01950: no privileges on tablespace ‘USERS’

Oracle Error:

ORA-01950: no privileges on tablespace ‘USERS’

Scenario:

I am trying to insert values in EMP table of C##SCOTT schema, but getting filled with above mentioned oracle error, Error itself self explanatory. I have forgotten to assign quota space for C##SCOTT on USERS tablespace. After assigning quota space on USERS, everything was smooth.

Error logs and solution:

On C##SCOTT schema:

SQL> show user;
USER is "C##SCOTT"
SQL> insert into EMP values(1,'scott',1234567898);
insert into EMP values(1,'scott',1234567898)
 *
ERROR at line 1:
ORA-01950: no privileges on tablespace 'USERS'

Connect as sysdba and assign quota to C##SCOTT on USERS tablespace:

[oracle@OL712c ~]$ sqlplus / as sysdba
SQL> show user;
USER is "SYS"
SQL> alter user c##scott quota unlimited on users;
User altered.

On C##SCOTT schema again:

SQL> show user;
USER is "C##SCOTT"
SQL> insert into EMP values(1,'scott',1234567898);
1 row created.

In my case, it was problem with quota allocation for C##SCOTT schema on USERS tablspace.

Oracle 12c Logo

Configuring Data Redaction Policies – Data Redaction part-II

Oracle Data Redaction policies are responsible to redact data in column based on column data type as well as type of redaction. We can enable and disable policies according to our convenience.

In this article we going to discuss on following topics where as in my earlier article we already cover Data Redaction Introduction part.

  1. About Data Redaction Policies
  2. DBMS package used for Data Redaction and list of procedures in the package.
  3. Privilege required to execute Data Redaction
  4. Ask yourself sort of questions before planning Data Redaction Policies
  5. General syntax

Lets start one by one:

About Data Redaction Policies:

Policies can defines the conditions into which data redaction going to occurs for table/view.  Policies who defines kind of redaction to perform, How the data redaction should occur and when data redaction should take place.
Data redaction policies can be fully redact, partially redact, randomly redact and No redact for test purpose.

Policy can be defined with a policy expression which allows for different application users to be presented with either redacted data or actual data, based on whether the policy expression returns TRUE or FALSE. Redaction takes place when the boolean result of evaluating the policy expression is TRUE.

DBMS_REDACT DBMS package is used for Data Redaction and list of procedures in the package are as follows:

DBMS_REDACT.ADD_POLICY : Used to add policy to a table or view.
DBMS_REDACT.ALTER_POLICY : Used to modify already created policy.
DBMS_REDACT.UPDATE_FULL_REDACTION_VALUES : Globally updates the full redaction value for a given data type. Instance restart will required for the same.
DBMS_REDACT.ENABLE_POLICY : Used to enable policy.
DBMS_REDACT.DISABLE_POLICY : Used to disable policy.
DBMS_REDACT.DROP_POLICY : Used to drop policy.

Privilege required to execute Data Redaction

To create data redaction policy, user must have EXECUTE permission/privilege on DBMS_REDACT PL/SQL package. No need of any privileges required to access the underlying tables or views that will be protected against policies.

Ask yourself following questions before planning Data Redaction Policies:

  1. Have you granted with EXECUTE privilege on the DBMS_REDACT PL/SQL package?
  2. Which data type of the table/view column that you want to redact?
  3. Do you use desire column in Oracle Virtual Private Database (VPD) row filtering condition? Because we cant use same column for data redaction.
  4. Which type of redaction you want to perform on table/view? (i.e. full, random, partial, regular expressions, or none)
  5. Which users to apply the Data Redaction policy to?

Note: When you create policy, it will be automatically ready to redact data.

General syntax of the DBMS_REDACT.ADD_POLICY Procedure:

DBMS_REDACT.ADD_POLICY procedure is used to create data redaction policy.

Syntax is as follows:

 DBMS_REDACT.ADD_POLICY (
 object_schema IN VARCHAR2 := NULL,
 object_name IN VARCHAR2 := NULL,
 policy_name IN VARCHAR2, 
 policy_description IN VARCHAR2 := NULL,
 column_name IN VARCHAR2 := NULL,
 column_description IN VARCHAR2 := NULL,
 function_type IN BINARY_INTEGER := DBMS_REDACT.FULL,
 function_parameters IN VARCHAR2 := NULL,
 expression IN VARCHAR2,
 enable IN BOOLEAN := TRUE,
 regexp_pattern IN VARCHAR2 := NULL,
 regexp_replace_string IN VARCHAR2 := NULL,
 regexp_position IN BINARY_INTEGER :=1,
 regexp_occurrence IN BINARY_INTEGER :=0,
 regexp_match_parameter IN VARCHAR2 := NULL);

Details of above syntax parameter:

object_schema: Schema of the object on which the policy will be applied.

object_name: Name of object on which the Data Redaction policy applies.

policy_name: Name of the policy. Should be unique through out instance. Find already created policies by querying data dictionary view: REDACTION_POLICIES.

policy_description: Purpose to create it with precise description.

column_name: Column name on which policy applies.

  • We can apply policy on multiple columns.
  • We can define only one policy on a table/view.
  • No columns are redacted by the policy, in case you don’t specify a column. (for example, by entering NULL)
  • We can’t define a policy on a column that is involved in the SQL expression of any virtual column.

column_description: Precise description of column that are going to be redacted.

function_type: defines types of data redacted policies. Note: Default function_type would be full redaction in case of we omit to specify it.

function_parameters: In case of partial redaction, parameter specifies how column redaction should appear.

expression: Specifies a Boolean SQL expression to decide how the policy is applied.

enable: If parameter set to TRUE, policy will automatically enable at the time of creation. else FALSE will be considered it to enable letter on.

Rest of all parameters: all used for regular expression to redact data, either full or partial. If regexp_pattern parameter don’t match with anything in target data than full redaction will be take place.

Kindly stay tune with my next article about Hand-on on Full Data Redaction Policy.