DDL_LOCK_TIMEOUT Parameter

The DDL (Data Definition Language) commands required to put an exclusive lock on internal structure of the database. If when trying to configure one we  cannot must to another user / process has one yet, we will receive the following message;

ORA-00054: resource busy

To try to reduce this have basically two options, one is launching the command repeatedly, until we get lucky and run (better known as pooling) or use the parameter DDL_LOCK_TIMEOUT. This parameter indicates the number of seconds a DDL statement should wait to get set the lock, before we get an ORA-00054 error.

You can set the parameter level DDL_LOCK_TIMEOUT at session or system level. You can see below an example;

 
SQL> REM
SQL> REM Set DDL_LOCK_TIMEOUT to 60 seconds
SQL> REM
SQL> ALTER SESSION SET DDL_LOCK_TIMEOUT = 60;

Session altered.

SQL> REM
SQL> REM The next alter will wait for one minute, after that it get a ORA-00054
SQL> REM
SQL> ALTER TABLE EXEMPLE_ANR ADD (NEW_COLUMN VARCHAR2(10));

ORA-00054: resource busy and acquire with NOWAIT specified or timeout expired

SQL>

HTH – Antonio NAVARRO

Advertisements

ORA-02096 Error Set Recyclebin Parameter

I get the following error when I try to put off the Recycle Bin;

ORA-02096: specified initialization parameter is not modifiable with this option

Since version 11g must use the DEFERRED clause. It means it takes effect in subsequent sessions. An example of this is the following;

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on

SQL> alter system set recyclebin=off;
alter system set recyclebin=off
                              *
ERROR at line 1:
ORA-02096: specified initialization parameter is not modifiable with this option


SQL> ALTER SYSTEM SET recyclebin = OFF DEFERRED;

System altered.

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      on
SQL>
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
$ ###
$ ###
$ ### You must exit from current session to get the new settings
$ ###
$ ###
$ sqlplus '/ as sysdba'

SQL*Plus: Release 11.2.0.4.0 Production on Wed Apr 29 18:21:57 2015

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> show parameter recyclebin

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
recyclebin                           string      OFF

HTh – Antonio NAVARRO

Is Changeperm.sh Script Neccesary?

Today doubt has arisen with a coworker if it remains necessary to run the script Changeperm.sh in version 11g. This script gives permissions for third-party providers or other to files as listener.ora, tnsnames.ora, etc.

When looking at the script engine software, we see that is not shown, in our case the version is 11.2.0.4. Consulting MOS (formerly Metalink) can see the specifics in note 834626.1, please read for more information, but this script is no longer necessary.

HTH – Antonio NAVARRO

Using Notepad++ To Edit Files In Unix, Linux, OpenVMS…

Notepad ++ is a powerful tool that has become over time a substitute for notepad and in many cases UltraEdit, especially by the column-mode. One of the features that has included in the latest version is the ability to edit files in external systems such as Unix, Linux, OpenVMS, etc, etc … This should enable click in SHOW NPPFTP WINDOW as shown in the screenshot below ;

npp_ftp

Once here must configure the connection to the machine that interests us.

HTH – Antonio NAVARRO

How To Unsecure Oracle Grid Control Agent

Often when you install a grid control agent that is installed by default with encryption. The problem may arise when the OMS is not using encryption, so it will not work. To unset encryption you can follow the steps below. However, it is not good security policy; this configuration (send decrypted data), although we are in a secure network (Perimeter security). My recommendation is to always use encryption.

Check the current settings. We see https…

 $ emctl status agent                                              
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0   
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------   
Agent Version     : 11.1.0.1.0                                    
OMS Version       : 11.1.0.1.0                                    
Protocol Version  : 11.1.0.0.0                                    
Agent Home        : /export/home/agent11/agent11g                 
Agent binaries    : /export/home/agent11/agent11g                 
Agent Process ID  : 15871                                         
Parent Process ID : 15808                                         
Agent URL         : http://saturn:3872/emd/main/                 
Repository URL    : https://oms.jupiter.com:4819/em/upload/     
Started at        : 2015-04-23 17:06:58                           
Started by user   : agent11                                       
Last Reload       : 2015-04-23 17:12:10                           
Last successful upload                       : (none)             
Last attempted upload                        : (none)             
Total Megabytes of XML files uploaded so far :     0.00           
Number of XML files pending upload           :       78           
Size of XML files pending upload(MB)         :    37.49           
Available disk space on upload filesystem    :    20.08%          
Last attempted heartbeat to OMS              : 2015-04-23 17:14:41
Last successful heartbeat to OMS             : unknown            
---------------------------------------------------------------   
Agent is Running and Ready        

Stop the agent;

 $ emctl stop agent                                                
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0   
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Stopping agent ... stopped.                                       

Change the file ..//agent11g/sysman/config/emd.properties. Change the REPOSITORY_URL variable below;

 
# FROM 
# REPOSITORY_URL=https://oms.jupiter.com:4819/em/upload/
# SET TO
REPOSITORY_URL=http://oms.jupiter.com:4819/em/upload/

Startup the agent;

 $ emctl start agent                                               
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0   
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
Starting agent ....................................... started.   

Check again to confirm the change, now we see http;

 $ emctl status agent                                              
Oracle Enterprise Manager 11g Release 1 Grid Control 11.1.0.1.0   
Copyright (c) 1996, 2010 Oracle Corporation.  All rights reserved.
---------------------------------------------------------------   
Agent Version     : 11.1.0.1.0                                    
OMS Version       : 10.2.0.5.0                                    
Protocol Version  : 10.2.0.5.0                                    
Agent Home        : /export/home/agent11/agent11g                 
Agent binaries    : /export/home/agent11/agent11g                 
Agent Process ID  : 21688                                         
Parent Process ID : 21607                                         
Agent URL         : http://saturn-2:3872/emd/main/                 
Repository URL    : http://oms.jupiter.com:4819/em/upload/      
Started at        : 2015-04-23 17:15:22                           
Started by user   : agent11                                       
Last Reload       : 2015-04-23 17:15:22                           
Last successful upload                       : 2015-04-23 17:16:29
Total Megabytes of XML files uploaded so far :    12.04           
Number of XML files pending upload           :        8           
Size of XML files pending upload(MB)         :     1.15           
Available disk space on upload filesystem    :    20.39%          
Last successful heartbeat to OMS             : 2015-04-23 17:16:05
---------------------------------------------------------------   

HTH – Antonio NAVARRO

How To Unset Variable In Korn Shell

We often have to remove environment variables in unix systems. For KSH becomes as follows;

 jupiter:/optim/prod/server/db_1/bin=> echo $LD_LIBRARY_PATH
/app/oracle/database/netins/product/11.2.0/dbhome_1/lib:/usr/lib
jupiter:/optim/prod/server/db_1/bin=> unset LD_LIBRARY_PATH
jupiter:/optim/prod/server/db_1/bin=> echo $LD_LIBRARY_PATH

HTH – Antonio NAVARRO