Square DBA – Oracle DBA Talk

ORA-29701 Unable to Connect to Cluster Manager

I received an error when I was setting up a new test database. The new database was being set up via DBCA on a RedHat server. The error occurred during the ASM creation. Because this is a test server there are multiple Oracle products installed. I changed the environment variable to new point to the proper $ORACLE_HOME. From $ORACLE_HOME/bin i have deleted and recreated cluster services.

localconfig delete

localconfig add

Issue solved

How to put a database in archivelog mode?

How to put a database in archivelog mode? It’s pretty simple really.

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup mount;
ORACLE instance started.

Total System Global Area 1241513984 bytes
Fixed Size                   778976 bytes
Variable Size             333716768 bytes
Database Buffers          905969664 bytes
Redo Buffers                1048576 bytes
Database mounted.

SQL> alter database archivelog;

Database altered.

SQL> alter database open;

Database altered.

That is all there is to it. Now make sure you have properly set your LOG_ARCHIVE_DEST or it will cause issues later. To see what mode you database is in currently use this SQL:

SELECT LOG_MODE FROM SYS.V$DATABASE;

Flushing Oracle’s Cache

Have you ever wanted to test the timing on a SQL script? The first time you execute the script it executes in 5 minutes. The second time it executes it only takes 1 minute. Everything else being equal this is probably due to Oracle caching some or all of your data. This is a good thing, but not what you want when testing the speed of a script. You may want to make some changes and run the script again to test those changes. Caching the data will not give you a fair comparison. You need to flush Oracle’s cache. To flush the shared pool the command is simple.

alter system flush shared_pool;

Also as of Oracle 10G you can flush just the buffer cache with this command.

alter system flush buffer_cache;

To flush the buffer cache in 9i you can use the following (undocumented) command.

alter session set events = 'immediate trace name flush_cache';

Renaming an Oracle Table

Renaming a table in Oracle is a very simple process, but you do need to be aware of the results. When you rename a table objects like indexes do move with the rename. However be aware that references to the table from things like PL/SQL block do not. Objects that reference the table will probably be invalid after you rename the table. Here is the syntax for renaming the table:

alter table
   table_name
rename to
   new_table_name;

If you are renaming a table in another user’s schema be aware that you should not use the schema name in the new table name. If you do you will get an ORA-14047 error. This syntax is not correct.

alter table
   scott.table_name
rename to
   scott.new_table_name;

Running this will generate a “ORA-14047: ALTER TABLE|INDEX RENAME may not be combined with other operations” error.

However this is the correct syntax and will run just fine.

alter table
   scott.table_name
rename to
   new_table_name;

Set default date format in Oracle SQL*Plus

I find myself dealing with dates all the time in Oracle. I dislike Oracle’s default date format. I generally want to see dates with time and usually down to the second. To change the default date format you need to modify your NLS_DATE_FORMAT. Here is how you do it:

alter session set NLS_DATE_FORMAT=’PUT FORMAT HERE’;

For example

alter session set NLS_DATE_FORMAT=’yyyy/mm/dd hh24:mi:ss’;

Dynamically set the name of a spool file in SQL*Plus

Do you need to dynamically create a spool filename in SQL*Plus? You may have a need to set the name of a spool file based on the current time or possibly the database you are connected to. It’s often needed when scripts is run as scheduled job and there is a need to generate files with different names. Much of the time I do this in a shell script, but I do run across an occasion to do it SQL*Plus. Here is a solution for using date:

define logname=date
column clogname new_value logname
select 'prefix_'||to_char(sysdate, 'yyyymmdd') clogname from dual;
spool '&logname'

What about including the database name? Here is one I used today the uses the current date, database name, and a random number.

define logname=date
column clogname new_value logname
select substr(global_name,1,
 decode(dot,0,length(global_name),dot-1))||'-'||
 to_char(sysdate, 'yyyymmddhh24miss')||'-'||
round(dbms_random.value(10,99),0) clogname
from (select global_name, instr(global_name, '.')
dot from global_name);
spool c:\sqlpluslogs\&logname..txt 

How to escape special characters in Oracle

If you want to retrieve TABLE_NAMES from ALL_TABLES where the table name is like ‘ADD_’ using the following query, you may notice that the query is returning ADDRESS and ADD_CODES:

create table address (p1 number);
create table add_codes (p1 number);

select distinct table_name
 from all_tables
  where table_name like 'ADD_%';

TABLE_NAME
———-
ADDRESS
ADD_CODES

If you try to escape the ‘_’ character with the following query, you will still get the same result.

select distinct table_name from all_tables
  where table_name like 'ADD\_%'

Therefore the question is: How do you use LIKE to find data that contains an underscore or percent sign ? The answer is to escape the underscore and/or percent sign in the LIKE template. You have to designate what character you would like to use via the ESCAPE keyword. A slash is commonly used, but any character would actually work:

select distinct table_name
  from all_tables
where table_name like 'ADD\_%' ESCAPE '\';

“Argument list too long” on Linux

Ever get the error “Argument list too long” when working with files on Linux? I deal with a lot of files from telecommunication switches. These files contain call detail records (CDRs). I generally load these files into an Oracle database using sqlldr.

These files ad up quickly and space is valuable on the server. I need to get these files off to tape. Moving these files around I run into the error “Argument list too long”. This error will affect all regular system commands (ls *, cp *, rm *, etc…).

I filter the list of files through the find command, instructing it to properly handle each file based on a specified set of command-line parameters. Due to the built-in flexibility of the find command, this workaround is easy to use. It allows you to selectively work with subsets of files based on their name patterns, date stamps, permissions and even inode numbers. In addition, and perhaps most importantly, you can complete the entire task with a single command.

The main drawback to this method is the length of time required to complete the process. This procedure actually inspects the individual properties of each file before performing the designated operation. The overhead involved can be quite significant, and moving lots of files individually may take a long time. Here is the command I use to move the files.

[user@server1 archive]$ find $archive -type f -name '*'
-exec mv {} /old_array/backup/acme/. \; 

Oracle Alert Log via SQL with External Tables

Because I love to do things in the database I write little script and functions to keep me in SQLplus. Access the Alert Log is no different. In reality I access the Alert Log inside and outside the database. It’s too important to monitor in one place.

Starting in Oracle9i you can map external flat files to Oracle tables.
Mapping the Oracle alert log is easy and once defined, all you have to do is query it with standard SQL syntax:

create directory BDUMP as '/u01/app/oracle/admin/mysid/bdump';

create table
   alert_log ( msg varchar2(80) )
organization external (
   type oracle_loader
   default directory BDUMP
   access parameters (
      records delimited by newline
   )
   location('alrt_mysid.log')
)
reject limit 1000;

Now we can easily extract important Oracle alert log information without leaving SQL*Plus:

select msg from alert_log where msg like '%ORA-00600%';

Oracle Wildcards

Back from the Christmas holiday and looking forward to New Years.  This week is always slow around here, but I did get a couple of new questions today.  One was a pretty  basic question, but I thought it migh be helpful to someone out there.  What are Oracle wildcards?  There are basically two types, single character and multiple character wildcards.  % is used for multiple characters and _ is used for single characters.  Here are a couple examples.

Single:

SELECT *
FROM wildcard
WHERE test LIKE '23_5';

Multiple:

SELECT *
FROM wildcard
WHERE test LIKE '2%5';