Monthly Archives: April, 2017

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.

 

 

Advertisements

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;

db2: find out agent id from PID

Scenario: The below steps explain to find out AGENT ID for a process id: 20513654

Step1: Get snapshot for all applications running

db2 “connect to TESTDB1”

db2 “get snapshot for applications on TESTDB1” > /tmp/err2

Now, open /tmp/err2 and search for 20513654. Then you see content as below in /tmp/err2:

***

Application Snapshot

Application handle = 7978
Application status = UOW Waiting
Status change time = 04/11/2017 07:36:23.948027
Application code page = 819
Application country/region code = 1
DUOW correlation token = *LOCAL.db2inst1.170411082659
Application name = cicsas
Application ID = *LOCAL.db2inst1.170411082659
Sequence number = 00001
TP Monitor client user ID =
TP Monitor client workstation name =
TP Monitor client application name =
TP Monitor client accounting string =

Connection request start timestamp = 04/11/2017 04:26:59.284367
Connect request completion timestamp = 04/11/2017 04:26:59.284827
Application idle time = 29 minutes 18 seconds
CONNECT Authorization ID = CICS
Client login ID = cics
Configuration NNAME of client = SERVER1
Client database manager product ID = SQL10014
Process ID of client application = 20513654
Platform of client application = AIX
Communication protocol of client = Local Client

Inbound communication address = *LOCAL.db2inst1

***

Step2: From above, the agent id is 7978

/mnt/db2home:> db2 list applications | grep -i 7978
CICS cicsas 7978 *LOCAL.db2inst1.170411082659 TESTDB1 1

Step3: Kill the application id 7978

/mnt/db2home:> db2 “force application (7978)”
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.

 

Thanks!

 

Oracle: RMAN connections (vs) sqlplus connection

RMAN connections to a database are specified and authenticated in the same way as SQL*Plus connections to a database.

The only difference is that RMAN connections to a target or auxiliary database require either the SYSDBA or SYSBACKUP privilege. Any user can be granted this privilege.

***

$ rman

Recovery Manager: Release 12.1.0.2.0 – Production on Mon Apr 10 09:31:25 2017

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

RMAN> connect target “ravi@TESTDB1 as sysbackup”

target database Password:
connected to target database: TESTDB1 (DBID=123456789)

***

As a best practise: As shown above, enter passwords in RMAN only when requested by an RMAN prompt. Good security practise requires that you not enter passwords in plain text on command line.

 

Ref: https://docs.oracle.com/database/121/BRADV/rcmquick.htm#BRADV89348