Category Archives: Misc

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;

Advertisements

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