Author Archive: RK

Oracle Database Cloud: Supported Database Editions and differences

Oracle cloud offers best-in-class services across

  1. Software as a Service (SaaS),
  2. Data as a Service (DaaS),
  3. Platform as a Service (PaaS), and
  4. Infrastructure as a Service (IaaS).

Oracle database cloud comes under oracle PaaS vertical.

Oracle Database cloud supports below database editions:

  1. Standard Edition (SE)
  2. Enterprise Edition (EE)
  3. Enterprise Edition – High Performance
  4. Enterprise Edition – Extreme Performance

Below is the difference between these 4 packages:

Standard Edition: Oracle Standard edition is offered as part of this

Enterprise Edition

  • Oracle Enterprise Edition
  • Data Masking and Subsetting Pack
  • Diagnostics and tuning packs
  • Real Application Testing (RAT)

Enterprise Edition – High Performance:

  • Enterprise Database +
  • Multitenant
  • Partitioning
  • Advanced compression
  • Advanced security
  • Label security
  • Database Vault
  • OLAP
  • Advanced analytics
  • Spatial & Graph
  • Database Lifecycle Management pack
  • Cloud Management pack for Oracle database

Enterprise Edition – Extreme Performance:

***This is the only choice for RAC instances in cloud.

  • High Performance Package +
  • RAC
  • In-memory database
  • Active Data Guard
Advertisements

query to find whether an object is table or view in postgresql

Postgresql has a default schema named information_schema, which consists of a set of views, that contain information about the objects defined in the current database.

Query:

select * from information_schema.tables where table_name like ‘%<object name>%’;

tables: This view contains all tables and views defined in the current database.

Granting sysdba in Oracle RAC

All users with SYSDBA privileges are in password file. But, in most RAC environments the password file is local. That is not shared between instances.

Hence, in RAC environments when you grant someone SYSDBA privilege then you must do the same in all RAC instances.

Related commands:

grant sysdba to <username>;

select * from gv$pwfile_users;

determine the installed XL C/C++ for AIX compiler version

$ /usr/vacpp/bin/xlC -qversion
IBM XL C/C++ for AIX, V11.1 (5724-X13)
Version: 11.01.0000.0015
or
$ /usr/vac/bin/xlc -qversion
IBM XL C/C++ for AIX, V11.1 (5724-X13)
Version: 11.01.0000.0015

Ref: http://www-01.ibm.com/support/docview.wss?uid=swg21652272

 

 

 

ORA-65096: invalid common user or role name

Problem: A simple “create user” statement is failing with below error.

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Apr 21 09:19:36 2017

Copyright (c) 1982, 2014, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

SQL> create user backup identified by password;
create user backup identified by password
*
ERROR at line 1:
ORA-65096: invalid common user or role name

Solution: This basic syntax is failing because the database was created as a pluggable database

SQL> select banner from v$version;

BANNER
——————————————————————————–
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
PL/SQL Release 12.1.0.2.0 – Production
CORE 12.1.0.2.0 Production
TNS for Linux: Version 12.1.0.2.0 – Production
NLSRTL Version 12.1.0.2.0 – Production

SQL> select cdb from v$database;

CDB

YES

There is an hidden parameter _oracle_script to workaround the above problem.

SQL> alter session set “_oracle_script” = TRUE;

Session altered.

SQL> create user backup identified by backup;

User created.

 

 

important configuration parameters

The below are important configuration parameters:

1) INFORMIXDIR: Is the directory where the Informix server is installed

2) INFORMIXSERVER: The Informix instance name

3) INFORMIXSQLHOSTS: This is optional one.
If no entry specified for this then the default entry for this is: $INFORMIXDIR/etc/sqlhosts

The line format in sqlhosts is as below:

<DB Server name> <protocol> <hostname> <port number>
Protocol:
ISAM => Standard Engine (SE) => Online Engine (IDS)
onsoctcp (or) onipcshm
Soc: Using socket
ipc: inter process communication
shm: Shared memory

ONCONFIG: This tells the Informix instance about configuration parameters

PATH: The path for executables. The $INFORMIXDIR/bin should be added to this.

LD_LIBRARY_PATH: Tells where to look for Informix library files on non AIX platform

SHLIBPATH: Tells where to look for Informix library files on AIX platforms
*************Sample Informix instance Environment file******************
export INFORMIXDIR=/opt/informix/run32
export INFORMIXSERVER=devc
export ONCONFIG=onconfig.devc
export TERM=vt100
export TERMCAP=$INFORMIXDIR/etc/termcap
export PATH=$INFORMIXDIR/bin:$PATH
export LD_LIBRARY_PATH=$INFORMIXDIR/lib:$LD_LIBRARY_PATH
export INFORMIXSQLHOSTS=$INFORMIXDIR/etc/sqlhosts
*****
***

Checking Informix database server status

To check the instance status type “onstat -“ from command prompt as shown in below screenshot

ids9> onstat –

Server Status.png
“On-Line” from above screenshot means the instance is up and running.
9.40.HC7: is the Informix version number
and the instance is on line from 212 days 19:25:41

Note: onstat is an utility that comes with Informix installation. The path of this utility is: $INFORMIXDIR/bin/onstat

Commands to switch between various Informix operating modes

Online Offline Quiescent Single/Admin
Online           — onmode -ky (or) onmode -k Gracefully: onmode –s (or) onmode –sy

Immediate: onmode –u (or) onmode -uy

onmode -j
Offline oninit -v

(or)

oninit -yv

           — oninit -s oninit -j
Quiescent/

Maintenance

onmode -m onmode –ky (or) onmode -k             — onmode -j
Single User / Administrative onmode  -m onmode -k onmode -s (graceful)

onmode -u (Immediate)

What are various Informix operating modes?

The IBM Informix database server has 4 principal modes of operation:

  1. Online
  2. Single User/Administrative
  3. Quiescent/Maintenance
  4. Offline

OFFLINE  <==>  Quiescent  <==>  Single User <==> ONLINE

1. Online: In this mode, users can connect with the database server and perform all database activities. This is normal operating mode of the database server

[informix@test1 ~]$ onstat –
IBM Informix Dynamic Server Version 12.10.FC5TL — On-Line — Up 36 days 04:41:25 — 164468 Kbytes

2. Offline: When the database server is not running. No shared memory is allocated

[informix@test1 ~]$ onstat –
shared memory not initialized for INFORMIXSERVER ‘ids13’

3. Single User/Administrative: This is the single user mode. This mode allows Informix user to perform all functions, including the issuance of SQL and DDL commands.

The -j -U option enables DBSA to designate specific users (in addition to the informix user) to access the database server.

[informix@test1 ~]$ onstat –
IBM Informix Dynamic Server Version 12.10.FC5TL — Single-User — Up 00:00:24 — 148084 Kbytes

4. Quiescent/Maintenance: Only administrators can access the database server to perform maintenance functions that do not involve the execution of SQL and DDL statements

[informix@test1 ~]$ onstat –
IBM Informix Dynamic Server Version 12.10.FC5TL — Quiescent — Up 00:01:37 — 148084 Kbytes

Oracle APPS: Find out version of EBS

Below is one option using database:

SELECT release_name FROM APPS.FND_PRODUCT_GROUPS;