Category Archives: Oracle

TCP protocol error – TNS-01153: Failed to process string, TNS-12538: TNS:no such protocol adapter

Problem appeared after server reboot. Both database and instance were ok but listener refused to start with errors in trace:

nsglma:Reporting the following error stack:
TNS-01150: The address of the specified listener name is incorrect
TNS-01153: Failed to process string: (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))

We spent significant time and tried several ways to resolve it (configuring another listeners with different ports, change hostnames, etc) but none helped. Looked like not a configuration issue.

Later when no options left, we just tnsping the listener on another server. This time we got different but more promising error:

TNS-12538: TNS:no such protocol adapter

Everything was checked one more time – no mistakes in connection string, no network partitions and no problem with remote listener. Most likely it was caused by protocol itself.

So, we did some checks according to oracle doc; (Doc ID 435795.1) by using utility adapters (displays the configured Oracle transport protocols, naming methods, and security options)

cd $ORACLE_HOME/bin
./adapters

Installed Oracle Net transport protocols are:

IPC
BEQ

Error!!! TCP/IP is not completely installed!
Present in libntcp10, but
missing from ntcontab.o...
Error!!! SSL is not completely installed!

Present in libntcps10, but missing from ntcontab.o...
RAW
Error!!!
SDP/IB is not completely installed!
Present in libntcp10, but missing from ntcontab.o...

Which points a problem with TCP protocol – TCP/IP is not completely installed!

As for solution oracle suggest following:

1. connect as the owner of oracle

2. make sure that ORACLE_HOME and LD_LIBRARY_PATH are set correctly
ORACLE_HOME=</oracle10/product>
LD_LIBRARY_PATH=$ORACLE_HOME/lib

3. shutdown all Oracle instances and processes running from this Oracle Home

4. execute following commands:
$ script /tmp/relink_adapt.out
$ cd $ORACLE_HOME/bin
$ ./gennttab
$ cd $ORACLE_HOME/network/lib
$ make -f ins_net_client.mk ntcontab.o
$ cd $ORACLE_HOME/bin
$ genclntsh
$ cd $ORACLE_HOME/rdbms/lib
$ make -f ins_rdbms.mk install
$ cd $ORACLE_HOME/sqlplus/lib
$ make -f ins_sqlplus.mk install
$ cd $ORACLE_HOME/network/lib
$ make -f ins_net_client.mk install
$ cd $ORACLE_HOME/network/lib
$ make -f ins_net_server.mk install
$ exit

Then again try:
$ cd $ORACLE_HOME/bin
$ adapters oracle

All now seems ok:
Net Protocol Adapters linked with oracle are:
BEQ
IPC
TCP/IP
RAW
SSL

Actually this never happen if the system installed and maintained correctly but this one seems got really bad care.

Thanks,

Upgrade Oracle Database from 10GR2 to 11GR2 on another host

In this post we will discuss about restoring 10g database into new host with 11g oracle software installed.
Source database is 10.2.0.1, O/S is Windows server 2003 64 bit
On destination server installed oracle database software 11.2.0.1, O/S is Windows server 2012 64 bit

Note : direct upgrade between these versions is not possible, see Oracle Documentation http://docs.oracle.com/cd/E11882_01/server.112/e23633/preup.htm#UPGRD12358, hence we need to upgrade source database version from 10.2.0.1 to 10.2.0.2 or(higher)

after applying patchset 10.2.0.5, we can continue upgrading to 11g on another host.

The steps for 10g database:
1) Run the utlu112i.sql Pre-upgrade script. You can find this script under @%ORACLE_HOME%\rdbms\admin\. It must be copied from the 11g database software.

SQL> @%ORACLE_HOME%\rdbms\admin\utlu112i.sql

it performs following operation

SQL> ALTER TABLE registry$database ADD (tz_version NUMBER);
SQL> UPDATE registry$database set tz_version =4;
SQL> ALTER  PACKAGE “SYS”.”DBMS_REGISTRY”  COMPILE BODY;
SQL> ALTER VIEW “SYS”.”DBA_REGISTRY_DATABASE”  COMPILE;

2) Connect to 10g database and take RMAN full backup.

run {
ALLOCATE CHANNEL RMAN_BACK_CH01 TYPE DISK;
backup tag test_full database format "E:\databasefiles_%d_%u_%s_%T";
sql 'ALTER SYSTEM ARCHIVE LOG CURRENT';
BACKUP archivelog all format "E:\archivelogs_%d_%u_%s_%T";
backup current controlfile format "E:\control01.cf";
RELEASE CHANNEL RMAN_BACK_CH01;
}

3) Copy 10g database backup files and archive files to 11g database server.

The steps for 11g database:
1) Create temporary pfile in $ORACLE_HOME$\dbs and change path of directories parameters as you have.

2) Open the database in NOMOUNT state.

rman target /

RMAN> startup nomount;

3) Restore controlfile from backup.

RMAN> restore controlfile from 'D:\backup\control01.cf';

4) Open the database in MOUNT state.

RMAN> alter database mount;

5) Make crosscheck backup and archive log, for delete expireds, Catalog RMAN backup files and archive log files.

RMAN> crosscheck backup;
RMAN> crosscheck archivelog all;
RMAN> delete expired backup;
RMAN> delete expired archivelog all; 
RMAN> catalog start with 'D:\backup';

6) restore and recover 10g database backup

RMAN> run
{
allocate channel c1 device type disk FORMAT 'D:\backup';
SET NEWNAME FOR DATAFILE 1 TO ‘D:\oradata\sd01\SYSTEM01.DBF';
SET NEWNAME FOR DATAFILE 2 TO ‘D:\oradata\sd01\UNDOTBS01.DBF';
SET NEWNAME FOR DATAFILE 3 TO ‘D:\oradata\sd01\SYSAUX01.DBF';
SET NEWNAME FOR DATAFILE 4 TO ‘D:\oradata\sd01\USERS01.DBF';
SET NEWNAME FOR DATAFILE 5 TO ‘D:\oradata\sd01\USERS02.DBF';
SET NEWNAME FOR DATAFILE 6 TO ‘D:\oradata\sd01\USERS03.DBF';
SET NEWNAME FOR DATAFILE 7 TO ‘D:\oradata\sd01\USERS04.DBF';
restore database;
switch datafile all;
recover database;
}

7) open database with upgrade mode

SQL> shutdown immediate;
SQL> startup upgrade;

8) Run the upgrade script

SQL> SPOOL upgradelog.log
SQL> @%ORACLE_HOME%\rdbms\admin\catupgrd.sql
SQL> SPOOL off

9) If our 10g and 11g database os platforms are different then you must run utlmmig.sql script

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP UPGRADE
SQL> SPOOL upgradelog.log
SQL> @%ORACLE_HOME%\rdbms\admin\utlmmig.sql
SQL> SPOOL off

10) open the database

SQL> SHUTDOWN IMMEDIATE
SQL> STARTUP

11) Run the Post-Upgrade script

SQL> @%ORACLE_HOME%\rdbms\admin\utlu112s.sql

12) Compile invalid objects

SQL> @%ORACLE_HOME%\rdbms\admin\utlrp.sql

that’s all, have a good upgrade.

ORA-04021: Timeout occurred while waiting to lock object

When developer is trying to recompile PL/SQL package session hangs until get this error:

ORA-04021: timeout occurred while waiting to lock object

Basically, whilst someone or something else (a scheduled job perhaps?) is executing the package, then you won’t be able to perform the recompile. To get around this, you need to identify the locking session and kill it.

Executing this script as SYS (or another user with the appropriate privileges) will prompt you for the package name and reveal the culprit(s):

SELECT s.sid,
       l.lock_type,
       l.mode_held,
       l.mode_requested,
       l.lock_id1,
       'alter system kill session '''|| s.sid|| ','|| s.serial#|| ''' immediate;' kill_sid
FROM   dba_lock_internal l,
       v$session s
WHERE  s.sid = l.session_id
AND    UPPER(l.lock_id1) LIKE '%&package_name%'
AND    l.lock_type = 'Body Definition Lock'
/

NOTE: If your dba_lock_internal view doesn’t exist, you can create this by running: $ORACLE_HOME/rdbms/admin/catblock.sql
check what is doing this session

SELECT s.sid,
       NVL(s.username, 'ORACLE PROC') username,
       s.osuser,
       p.spid os_pid,
       s.program,
       t.sql_text
FROM   v$session s,
       v$sqltext t,
       v$process p
WHERE  s.sql_hash_value = t.hash_value
AND    s.paddr = p.addr
AND    s.sid = &session_id
AND    t.piece = 0 -- optional to list just the first line
ORDER BY s.sid, t.hash_value, t.piece
/

run kill command from the output of first script

alter system kill session 'SID,SERIAL#' immediate;

after that developer can recompile package.

ORA-21561: OID generation failed

when creating database with dbca, shown error : ORA-21561: OID generation failed.

SOLUTION:

problem is most likely machine hosts file. check if there is written host ip and hostname, if this is missing then write it.

with root user:

# vi /etc/hosts

machine_ip      hostname

re-run dbca and you will see that above error is disappeared.
that’s all.

How to rebuild unusable indexes partition in oracle

Here i’ll show you how to find and rebuild unusable indexes partition in oracle:

SQL> select distinct status from dba_ind_partitions;
STATUS
——–
USABLE
UNUSABLE
SQL>select count(1) from dba_ind_partitions where status='UNUSABLE';
  COUNT(1)
———-
         2

we have two unusable indexes, get code for rebuild this indexes

SQL>select  ' alter index '||index_owner||'.'||index_name||' rebuild partition '||partition_name||';' from dba_ind_partitions where status='UNUSABLE';

and execute that code

SQL>alter index IKA.TEST_TBL_idx rebuild partition P_2014_02;
Index altered.
alter index IKA.TEST_TBL_idx rebuild partition P_2014_03;
Index altered.

check that we have all indexes usable state

SQL>select distinct status from dba_ind_partitions;
STATUS
——–
USABLE

that’s all.

ORA-00600: ORA-10567: ORA-10560: Physical standby redo applying error

It is not the first time we face the problem with applying redo on physical standby. Very old environment – Oracle 10gr2 with raw devices on old SUN storage believed to be suffering from physical lost writes. Particularly at the heavy load on primary where loads of redo log files are being generated.
So, the errors in alert.log are as follow:

ORA-00600: internal error code, arguments: [3020], [87], [154002], [365058450], [], [], [], []
ORA-10567: Redo is inconsistent with data block (file# 87, block# 154002)
ORA-10564: tablespace CB_IND
ORA-01110: data file 87: '/dev/vx/rdsk/ora02dg/cb_ind05'
ORA-10560: block type '0'

Famous ORA-00600 error when metalink gets handful. It says:

“This error can be reported if any of these updates are lost for some reason.
Therefore, thoroughly check your operating system and disk hardware.
In the case of a lost update, restore an old copy of the datafile and attempt to recover and roll forward again. (Doc ID 30866.1)”

Our only option was a restore by oracle as os investigation would take significant time. Even more, you still need to restore damaged file from oracle at the end. So, as a recommendation we suggest not thinking much and start immediate restore.

Steps are as follow:

1. primary database –

 backup as copy datafile 87 format '/share/sbtarch/ind5'; 

(raw devices add more trick to backup and restore operations. In case of normal fs it would be possible to put corresponding TS in backup mode and just a copy the datafile )

2. primary database –

 copy current controlfile for standby TO '/share/sbtarch/control03.dbf'  

3. standby database – mount instance with newly copied control file

4. standby database –

 	run { 
             ALLOCATE CHANNEL ch02 TYPE DISK;
 	     restore datafile 87; 
            }

5. on standby (in case of rman catalog you might avoid this step)

 catalog start with '/oracle/arch';  after control file copied 

6. on standby –

 ALTER DATABASE RECOVER MANAGED STANDBY DATABASE NODELAY disconnect from session; 

At this point, recovery started and all archived log files in queue applied successfully.

Thanks,

ERROR: ORA-09817: Write to audit file failed.

In my alert log file appear this error

ERROR:
ORA-09817: Write to audit file failed.
Linux-x86_64 Error: 28: No space left on device
Additional information: 12
ORA-09945: Unable to initialize the audit trail file
Linux-x86_64 Error: 28: No space left on device

Its because there is not left space on mount point where my audit files are generated.
check it :

df -h
Filesystem            Size  Used Avail Use% Mounted on
/dev/mapper/vg_node1230231-lv_root
                       34G   33G   0G   100% /
tmpfs                 4.0G  1.9G  2.2G  46% /dev/shm
/dev/sda1             485M   55M  405M  12% /boot

my audit destination is /u01/app/oracle/admin/orcl/adump, which you can find with parameter audit_file_dest.

solution:
1) You can add some space on your mount point.
2) Delete some audit files from /u01/app/oracle/admin/orcl/adump.

i don’t need that audit files, so i deleted it

cd /u01/app/oracle/admin/orcl/adump
rm *

after that everything goes fine.

EXECUTE IMMEDIATE ORA-01031: insufficient privileges

EXECUTE IMMEDIATE Feature allows you to execute DYNAMIC SQL Statements inside the PL/SQL Procedure, but here i show you interesting thing about this feature.
Here is Video of these procedures
let’s demonstrate small demo

SQL> create user qobesa identified by qobesa;

User created.

SQL>  grant connect,resource to qobesa;

Grant succeeded.

Our user called qobesa has privileges to connect database, create table and others because resource role contains privileges such as ‘create table’, test it

sqlplus qobesa/qobesa

SQL> create table test (id number);

Table created.

let’s create simple procedure which creates table with execute immediate

SQL> create or replace procedure test_proc
  2  is
  3  begin
  4  EXECUTE IMMEDIATE 'CREATE TABLE TEST1(ID NUMBER)';
  5  end;
  6  /

Procedure created.

and execute it

SQL> exec test_proc
BEGIN test_proc; END;

*
ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "QOBESA.TEST_PROC", line 4
ORA-06512: at line 1

got an error “insufficient privileges” – Do we have this privilege?, YES. we have. It has been grouped under RESOURCE role and the RESOURCE role has been granted to this user. Then, What is stopping us?
In STORED PROCEDURE, roles are DISABLED. Hence any PRIVILEGES granted by a ROLE to USER, will NOT be in effect. The PRIVILEGES MUST be directly granted to the user.
Therefore we can grant create table privilege to our user and will work, test it

SQL> conn sys / as sysdba
Enter password: 
Connected.
SQL> grant create table to qobesa;

Grant succeeded.
SQL> conn qobesa/qobesa
Connected.
SQL> exec test_proc

PL/SQL procedure successfully completed.

It’s all, good luck.

How to clear alerts in enterprise manager (database control)

When alert appear in enterprise manager and with some action solve, it may stay in enterprise manager as shown picture:
1
Here i’ll show you how to clear this unnecessary information.
login into database with sysman user and execute this script:

SELECT t.target_name,
       t.target_type,
       collection_timestamp,
       MESSAGE,
          'exec em_severity.delete_current_severity('''
       || t.target_guid
       || ''','''
       || metric_guid
       || ''','''
       || key_value
       || ''')'
          em_severity
  FROM    sysman.mgmt_targets t
       INNER JOIN
          sysman.mgmt_current_severity s
       ON t.target_guid = s.target_guid;

result:
2

after that run this scripts

SQL> exec em_severity.delete_current_severity('C894D538793FFA3B4727BCBCD84D4925','3F2BBE6BF105C0D16A3710F2868B8399','RECOVERY AREA');

PL/SQL procedure successfully completed.

SQL> commit;

Commit complete.

same way you can clear entire alerts.

DIM-00014: Cannot open the Windows NT Service Control Manager

When creating oracle database on Windows with dbca occurs error:
DIM-00014: Cannot open the Windows NT Service Control Manager.
O/S-Error: (OS 5) Access is denied.
Its means that the user from which you are using dbca has not sufficient privileges. But most of the users complaint that they are performing this task from Administrator user or they have only one account in windows and its administrator, but still they are facing this problem.
So the solution is very simple:
just run cmd with “run as administrator” and after that run dbca, now everything works fine.