Category Archives: Misc

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.

 

 

Oracle APPS: Find out version of EBS

Below is one option using database:

SELECT release_name FROM APPS.FND_PRODUCT_GROUPS;

Oracle: Central inventory is not locked

Today while installing Oracle 12c R1, I have got an error that says “Central inventory is not locked”

Solution: It failed to acquire a lock on Inventory.

$ cat /etc/oraInst.loc
inventory_loc=/u01/app/12.1.0.2/grid/oraInventory
inst_group=oinstall

Then, went to /u01/app/12.1.0.2/grid/oraInventory directory and removed “locks” directory. This fixed the issue.

Netezza emulator download link

https://www14.software.ibm.com/webapp/iwm/web/preLogin.do?source=swg-im-ibmndn

informix: list all database servers running

Step1: Every informix server has /INFORMIXTMP folder

[ravin@test1 ~]$ cd /INFORMIXTMP
[ravin@test1 INFORMIXTMP]$ ls -la
total 12
drwxrwxr-t. 2 informix informix 4096 Feb 21 20:52 .
dr-xr-xr-x. 23 root root 4096 Feb 1 18:26 ..
-rw-rw-r–. 1 root informix 21 Jan 25 20:29 .infxdirs
srwxrwxrwx. 1 informix informix 0 Feb 20 21:26 VP.ids13.10100s
[ravin@test1 INFORMIXTMP]$ cat .infxdirs
/mnt/informix/IDS121

step2: Set INFORMIXDIR environment variable to above output

[ravin@test1 INFORMIXTMP]$ export INFORMIXDIR=/mnt/informix/IDS121
[ravin@test1 INFORMIXTMP]$ cd $INFORMIXDIR/bin
[ravin@test1 bin]$ ./onstat -g dis
Your evaluation license will expire on 2017-04-25 00:00:00
shared memory not initialized for INFORMIXSERVER ‘<NULL>’
There are 2 servers found
Server : ids121
Server Number : 0
Server Type : IDS
Server Status : Down
Server Version: IBM Informix Dynamic Server Version 12.10.FC5TL
Shared Memory : 0x44000000
INFORMIXDIR : /mnt/informix/IDS121
ONCONFIG : /mnt/informix/IDS121/etc/onconfig.ids121
SQLHOSTS : /mnt/informix/IDS121/etc/sqlhosts
Host : test1

Server : ids13
Server Number : 1
Server Type : IDS
Server Status : Up
Server Version: IBM Informix Dynamic Server Version 12.10.FC5TL
Shared Memory : 0x44000000
INFORMIXDIR : /mnt/informix/IDS121
ONCONFIG : /mnt/informix/IDS121/etc/onconfig.ids13
SQLHOSTS : /mnt/informix/IDS121/etc/sqlhosts
Host : test1

Note: Just knowing /INFORMIXDIR exists is enough to find out Informix database server information.

 

query to map table and dbspace

The below query can be used to map a table and dbspace in IBM Informix:

database sysmaster;
select dbinfo( “DBSPACE” , pe_partnum ) dbspace,
dbsname[1,10],
tabname
from sysptnext, outer systabnames
where pe_partnum = partnum
and tabname = ‘CUSTOMER’
order by dbspace;

(or)

database sysmaster;
select s2.name,s1.tabname,s1.owner from systabnames s1,sysdbspaces s2
where s2.dbsnum=trunc(s1.partnum/1048576)
and tabname = ‘CUSTOMER’
order by name,tabname,owner

ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Problem: When trying to truncate a table, getting below error

SQL> truncate table PROD.CUSTOMER;
truncate table PROD.CUSTOMER

ERROR at line 1:
ORA-02266: unique/primary keys in table referenced by enabled foreign keys

Solution: This error comes when we are trying to truncate a table which is a parent in a referential integrity. Before you truncate this parent table, you have to disable constraints on the child tables.

The below query will help you to get information about dependent constraints:

select ‘ALTER TABLE ‘ || OWNER || ‘.’ || TABLE_NAME||’ DISABLE CONSTRAINT ‘||CONSTRAINT_NAME||’;’ from dba_constraints
where R_CONSTRAINT_NAME='<Primary Key Constraint name from CUSTOMER table>’;

Once the above query returns disable commands, run the disable constraints, then try to drop Truncate table. It should work at this time!

Credits: The query is taken from http://stackoverflow.com/questions/4812146/truncate-table-in-oracle-getting-errors

 

 

touch command to preserve files original timestamp

Touch has two major usages:

(1) Creates ZERO byte file

(2) Changes file timestamp to current timestamp

However, you can use Touch command to preserve files current timestamp as well. This can be achieved using -r option of touch command.

-r RefFile
Uses the corresponding time of the file specified by the RefFile variable instead of the current time.

Example:

$ ls -ltr 2.sql
-rw-rw-r– 1 ravi ravi 3081 May 20 2016 2.sql

Note: 2.sql file is created on 20/May/2016. Size of 2.sql is 3081

$ touch -r 2.sql 2.sql.origtime

$ ls -ltr 2.sql 2.sql.origtime
-rw-rw-r– 1 ravi ravi 0 May 20 2016 2.sql.origtime
-rw-rw-r– 1 ravi ravi 3081 May 20 2016 2.sql

Note: 2.sql.origtime is created with same timestamp as 2.sql

$ vi 2.sql

Note: Modified 2.sql

$ ls -ltr 2.sql 2.sql.origtime
-rw-rw-r– 1 ravi ravi 0 May 20 2016 2.sql.origtime
-rw-rw-r– 1 ravi ravi 3129 Jan 24 09:55 2.sql

Note: 2.sql size is changed to 3129. Also timestamp of 2.sql is modified

$ touch -r 2.sql.origtime 2.sql

Note: Run touch command to reverse timestamps

$ ls -ltr 2.sql 2.sql.origtime
-rw-rw-r– 1 ravi ravi 0 May 20 2016 2.sql.origtime
-rw-rw-r– 1 ravi ravi 3129 May 20 2016 2.sql

Note: You can see from above that 2.sql file’s timestamp is modified to original

 

DB2 LUW product end of support (EOS) dates

The below Technote from IBM will talk on the end dates. This is an important link to bookmark as an IBM DB2 Architect/DBA/Developer.

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

DB2 LUW: Recovery history file

(0) What is Recovery history file?

A file stores information about various operations against database. This information is used during restore/recovery.

(1) Location of Recovery history file:

Location: DBPATH/<instance name>/<Node Name>/SQLXXXXX/db2rhist.asc

This recovery history log file has a backup. Backup file name is: db2rhist.bak

$ls -l db2rhist.*
-rw-r—– 1 db2inst1 db2iadm1 272491532 Dec 22 05:35 db2rhist.asc
-rw-r—– 1 db2inst1 db2iadm1 272491532 Dec 22 05:35 db2rhist.bak

How to get DBPATH of a database?

From below “list db directory” entry, DBPATH is /mnt/DB2_data/ts1

Database 1 entry:

Database alias = SAMPLE
Database name = SAMPLE
Local database directory = /mnt/DB2_data/ts1
Database release level = f.00
Comment =
Directory entry type = Indirect
Catalog database partition number = 0
Alternate server hostname =
Alternate server port number =

(2) When Recovery history file is created?

Scope of recovery history file is database. Whenever you create a new database (CREATE DATABASE command), a recovery history file is created inside DBPATH.

(3) When Recovery history file is updated?

This file is updated automatically during below operations:

  • A database or table spaces are backed up
  • A database or table spaces are restored
  • A database or table spaces are rolled forward
  • A database is automatically rebuilt and more than one image is restored
  • A table space is created
  • A table space is altered
  • A table space is quiesced
  • A table space is renamed
  • A table space is dropped
  • A table is loaded
  • A table is dropped (when dropped table recovery is enabled and you are using recoverable logging)
  • A table is reorganized
  • On-demand log archiving is invoked
  • A new log file is written to (when using recoverable logging)
  • A log file is archived (when using recoverable logging)
  • A database is recovered
  • A failed restore database or table space operation

(4) How to see entries of Recovery history file?

There are two ways:

(a) LIST HISTORY Command

db2 => ? list history backup
LIST HISTORY {BACKUP | ROLLFORWARD | REORG |
CREATE TABLESPACE | ALTER TABLESPACE | DROPPED TABLE | LOAD |
RENAME TABLESPACE | ARCHIVE LOG}
{ALL | SINCE timestamp |CONTAINING {schema.object_name | object_name}}
FOR [DATABASE] database-alias

To list backup history since 10/SEP/2015
********************************
db2 => list history backup since 20150910 for database SAMPLE

To list backup history on a specific object
*********************************
Please note that you can’t take backup at table level. You can take backup at tablespace level (or) database level
db2 => list history backup containing <tablespace name> for database SAMPLE

(b) History via SQL

Select substr(comment,1,20), end_time from sysibmadm.DB_history Where comment like ‘DB2 BACKUP%’ Order by end_time desc FETCH FIRST 3 ROWS ONLY

(5) Pruning (removing) recovery history file

Ans: PRUNE HISTORY command

db2 ? prune history
PRUNE HISTORY timestamp [WITH FORCE OPTION] [AND DELETE]

(6) Automating recovery history file pruning

The following configuration parameters help us to automatically manage the recovery history file:

  • num_db_backups
  • rec_his_retentn
  • auto_del_rec_obj