Unix - Bits and Pieces Part 1

In this blog post series, I'll be updating the post with random unix commands that might be of use to DBAs at regular intervals.

1. Trim Alert log contents

It becomes tedious job when we have to deal with a huge alert log and it gets worse if we have to dig a month old or a week old alert log contents for troubleshooting. So the below command can be used to trim the contents of alert log for a particular period of time.

# This is the format used
sed -n '/From date pattern / , /To date pattern/p' < input_file > output_file
# This is an example
sed -n '/Jul 23 / , /Jul 26/p' < alter_ORACLESID.log > 4days_trimmed.txt

2. Details of CPU available

The details of memory information is available below
Memory Information of different OS platforms
The Details of CPU available can be found by using the commands below in Linux servers.

# -- gives number of CPU
nproc
# Details of CPU from /proc/cpuinfo in human readable format
lscpu
# Details from cpuinfo directly
cat /proc/cpuinfo

For HP-UX the below commands can be used

# For details of procs
ioscan -fnk|grep proc 
# For number of  procs
ioscan -k|grep processor|wc -l
# For details of overall system 
top

For AIX use any of the below.

prtconf -s
pmcycles -m
lsdev -Cc processor
bindprocessor -q

3. Create multiple directories under all directories

If we are required to create multiple directories inside all the available directories the below command can be used
For eg., lets take a directory where envmt has 4 directories dir1, dir2, dir3, dir4
Now we need to create control,bad,param,log directories under each of all the directories under envmt. Illustration is as follows

$ cd envmt
$ ls -lrt
total 16
drwxr-xr-x 2 oracle dba 4096 Jul 27 17:07 dir4
drwxr-xr-x 2 oracle dba 4096 Jul 27 17:07 dir3
drwxr-xr-x 2 oracle dba 4096 Jul 27 17:07 dir2
drwxr-xr-x 2 oracle dba 4096 Jul 27 17:07 dir1
$ ls -l dir1
total 0
$ ls -l dir2
total 0
$ for dir in */; do mkdir -- "$dir"/{control,bad,param,log}; done
$ ls -l dir1
total 16
drwxr-xr-x 2 oracle dba 4096 Jul 27 17:08 bad
drwxr-xr-x 2 oracle dba 4096 Jul 27 17:08 control
drwxr-xr-x 2 oracle dba 4096 Jul 27 17:08 log
drwxr-xr-x 2 oracle dba 4096 Jul 27 17:08 param
$ ls -l dir2
total 16
drwxr-xr-x 2 oracle dba 4096 Jul 27 17:08 bad
drwxr-xr-x 2 oracle dba 4096 Jul 27 17:08 control
drwxr-xr-x 2 oracle dba 4096 Jul 27 17:08 log
drwxr-xr-x 2 oracle dba 4096 Jul 27 17:08 param

Now control,bad,param,log directories are created in dir1, dir2, dir3, dir4 directories. This saves a lot of time while doing any job.

4. Search and replace in all files

The following command can be used to search and replace a word in all the files inside a directory. This is useful when we have configuration files stored where all the files should be updated with same username or servername, etc. 

# This is the format used
grep -rl 'search_string' ./ | xargs sed -i 's/search_string/replace_with_string/g'
# This is an example
grep -rl 'STDBCS01' ./ | xargs sed -i 's/STDBCS01/DVDBCS01/g'

If this find and replace is required for a single file, then it can be done by opening the file in vi editor

# open file
vi file_name
# Use below command to find and replace 
# g denotes global. Remove g if find and replace is required only for the particular line
:%s/search_string/replace_with_string/g
# save file and exit
:wq

5. Email attachment from servers

Email attachments can be sent with uuencode and the command is as below.
Tested in AIX and Linux

uuencode file1 file1 |mailx -s "Subject" nagulan.selvakumar@my.company.com

The below command is used to send multiple attachments in a single email

uuencode r1.tar.gz r1.tar.gz > /tmp/out.mail
uuencode r2.tar.gz r2.tar.gz >> /tmp/out.mail
uuencode r3.tar.gz r3.tar.gz >> /tmp/out.mail
echo "msg body"  >> /tmp/out.mail
mail -s "Reports" nagulan.selvakumar@my.company.com < /tmp/out.mail

We do have another package "mutt" to send emails which can be utilized as below.
Tested in Linux

echo "This is the message body" | mutt -a file_to_attach -s "subject of message" nagulan.selvakumar@my.company.com 

Happy working!

Job Intervals and Date Arithmetic

There are times when a DBA has to schedule jobs from inside the database either via dbms_jobs or dbms_scheduler to automate the tasks. Most of the times the confusion arrives on how to schedule the tasks with date and time. The below can be used in the interval parameter to schedule a job on its desired time.

Every Second.

interval => 'SYSDATE +1/86400';

Every Minute.

interval => 'SYSDATE +1/1440';

Every Ten Minute.
interval => 'SYSDATE +10/1440';

Every Half Hour.
interval => 'SYSDATE +30/1440';

Every Hour.
interval => 'SYSDATE +60/1440';

Every Day.
interval => 'SYSDATE +1';

Every Day with Fixed Time. ex: every night 8:00 pm.
interval => 'trunc(SYSDATE +1) + 20/24'

Every Midnight 12:00.
interval => 'trunc(SYSDATE +1)'

Every Week
interval => 'trunc(SYSDATE +7)'

Every Month
interval => 'add_months(SYSDATE, 1)'

Happy scheduling!

MS SQL vs Oracle database operational differences

I was working on a project where I need to migrate a database running on MS SQL server 2000 to Oracle 11gR2. I was using Oracle SQL Developer to convert codes such as functions, procedures, etc,. I had to do a lot of programming logic changes. I have noticed a few differences in both the flavours of RDBMS which I'll try to cover it up in this post.
This post covers only the technical coding differences and not about the general differences such as adaptability, licensing, usage, etc,.

1. MS SQL 'IF EXISTS'

We have codes in MS SQL like

begin
if exists
(select * from TABLE_01 where COL_01 = @var_01)
insert into .. 
else 
do something ..
end if
end

In Oracle, the situation can be handled with MERGE statement for "if exists insert or update" only when the tables involved are 2.
An example of MERGE statement is as below

MERGE INTO TABLE_01 a
USING TABLE_02 b
 ON (a.first_name = b.first_name)
-- orabliss.blogspot.com
WHEN MATCHED
 THEN
  UPDATE
  SET a.last_name = b.last_name
WHEN NOT MATCHED
 THEN
  INSERT (
   first_name
   ,last_name
   ,school
   )
  VALUES (
   b.first_name
   ,b.last_name
   ,b.school
   );

You can't have more than 2 tables to use MERGE statement. Also if the above is not for "if exists insert or update", then Oracle can handle this as below.

DECLARE v_temp NUMBER ( 1, 0) : = 0;
BEGIN
 SELECT COUNT(*)
 INTO v_temp
 FROM TABLE_01
 WHERE KEY_COL = v_var_01; -- v_var_01 is value passed or already declared
 IF v_temp > 0 -- This becomes "IF EXISTS" of MS SQL
 Do some operation
 ELSE 
 Do other operation
 END IF;
END;
/

2. IDENTITY data type

In MS SQL database we have IDENTITY type which is basically a primary key sequentially inserted value column defined as below.

CREATE TABLE [dbo].[TABLE_01] (
 [SERIAL_ID] [SMALLINT] IDENTITY (1, 1) NOT NULL ,
 [COL_02] [CHAR] NOT NULL ,
 ..
 ..
 )
GO

In Oracle we don't have this functionality until Oracle release 12c. So we have to create a sequence, create a trigger and use the trigger to fetch the next_value from the sequence every time the table is inserted with a new row.
Starting Oracle 12c, we have this IDENTITY column implemented which internally creates a sequence and increments when row inserted.

3. Data handling

We are fetching data into variable from a table to do some process. The predicate filter columns are not primary key columns.

MS SQL code

SELECT @Id_No = ID_NO
 ,@First_Name = FIRST_NAME
 ,@R_Code = R_CODE
 ,@Last_Name = LAST_NAME
FROM TABLE_01
WHERE CLASS_NO = @Class_No
 AND STD_QTY = @StdQty;

Oracle Code

SELECT ID_NO
 ,FIRST_NAME
 ,R_CODE
 ,LAST_NAME
INTO v_Id_No
 ,v_First_Name
 ,v_R_Code
 ,v_Last_Name
FROM TABLE_01
WHERE CLASS_NO = v_Class_No
 AND STD_QTY = v_StdQty;

Here we have 3 conditions either...
1. We have a single row returned as expected
2. We don't have any matching rows
3. We have more then 1 row for the combination of predicate

When we have the first condition satisfied, then both MS SQL and Oracle handles data in the same way.
When we have no matching rows, MS SQL by default assigns null values to all the variables whereas in Oracle the code errors out as NO_DATA_FOUND exception.
So here we need to handle the exception explicitly. One might think this is bad, but this is advantageous than MS SQL as we would know whether we have data for this combination or not before processing with the data using exception handler as below

EXCEPTION 
 WHEN NO_DATA_FOUND THEN
 -- handle exception stmt

Now coming to the third condition, when we have more than 1 row, MS SQL by default assigns the last fetched data to the variables ignoring all other values.
Oracle errors out with another exception TOO_MANY_ROWS.
If filter column is a primary key, we won't encounter this issue, but in our case it is not.
When one encounters TOO_MANY_ROWS exception (if expected), this can be handled through a loop statement to process the operation for every rows returned.

EXCEPTION WHEN TOO_MANY_ROWS THEN LOOP
FOR i IN
(SELECT v_Id_No
FROM TABLE_01
WHERE CLASS_NO = v_Class_No
 AND STD_QTY = v_StdQty)
 -- Do something
 ..
 ..
 END LOOP;

If you are concerned only about particular fields and not all other fields involved, this can also be handled with limiting the query to get only one row as below.

SELECT ID_NO
 ,FIRST_NAME
 ,R_CODE
 ,LAST_NAME
INTO v_Id_No
 ,v_First_Name
 ,v_R_Code
 ,v_Last_Name
FROM TABLE_01
WHERE CLASS_NO = v_Class_No
 AND STD_QTY = v_StdQty 
 -- This will limit the code to fetch only one row
 and rownum =1;

4. Transaction Control

In MS SQL, when we run a block of statements/queries, it treats each statement/query as a single unit. For eg. check the block below.

insert into table t1 values (v1);
insert into table t1 values (v2);
delete from t1 where field=v1;
exec some_proc;

We have 3 statements and when we run this in MS SQL database, then each statement will be committed individually irrespective of their previous statement is success or failure where as in Oracle all the 3 statements are treated as a single unit and should be committed (COMMIT) or rolled back (ROLLBACK) for all the changes to take effect at once.  As queries are executed and commands are issued, changes are made only in memory and nothing is committed until an explicit COMMIT statement is given (exclusion for DDL statements where an immediate commit is issued after execution).

In order to make the MS SQL group the all the transactions to a single group, BEGIN TRANSACTION with either COMMIT or ROLLBACK has to be specified as below.

BEGIN TRANSACTION
insert into table t1 values (v1);
insert into table t1 values (v2);
delete from t1 where field=v1;
exec some_proc;
COMMIT; -- or ROLLBACK

5. Find and replace

STUFF function serves the find and replace task in MS SQL which is powerful function used in many places.
REGEXP_REPLACE function is even more powerful compared to MS SQL's stuff as the control to user on how to find and replace is very useful in complex programming.

6. Objects

MS SQL stored procedures can return values whereas Oracle procedures will not return values. We can use OUT or IN OUT argument to get the required output. Oracle FUNCTIONs serves the purpose of doing the same as procedures and can return values.
In Oracle, a group of procedures and functions can be collectively put in as PACKAGE whereas in MS SQL this can't be done.

7. Miscellaneous

a) There a few data type differences that has to be taken care while converting from MS SQL to Oracle database. A few noticeable differences are in this Oracle document link.

b) User defined error messages can be created using sp_addmessage stored procedure in MS SQL. In Oracle, the user messages doesn't need to be created separately and can be raised as an user defined exception which ranges from -20000 to -20999.

The more we share, the more we learn. I'll update the post as and when I get additional differences while working on the flavours again.

Happy working!

Query elapsed time

Many times, for performance point of view we would like to know the elapsed time of a query. May be for comparison purpose or for documenting the elapsed time in good and bad scenarios. The following piece of code will get you the elapsed time for any query.
This code uses the dbms_utility.get_time procedure before and after the query is run so that we get the elapsed time. Make sure you run the query as a single block so we don't have an additional time added to the difference.

set serveroutput on
variable n number
exec :n := dbms_utility.get_time
-- Place your actual query here
select * from dual;
-- Query ends here
begin
   dbms_output.put_line
   ( (round((dbms_utility.get_time - :n)/100,2)) || ' seconds' );
end;
/

Reference oracle_document and Asktom site

Happy Working!

Datapump Export using Datapump API

The Data Pump API, DBMS_DATAPUMP, provides a high-speed mechanism to move all or part of the data and metadata for a site from one database to another. The Data Pump Export and Data Pump Import utilities are based on the Data Pump API.

In this post lets see how to perform a schema level export using the datapump API.
There are a few reasons (as I'm thinking as of now) to perform export/import through API rather then command mode interface is as follows.
1. When you don't have access to database server to perform an export but you only have access to the database and you know the pre created directory object.
2. When you perform a remote operation on a database from another server (say an application server) so you would like to perform export from the same connection
3. When you would like to schedule a database job from within the database itself without the necessity to create OS scripts to invoke export.

As a prerequisite, to perform export using Datapump API, exp_full_database role has to be granted to the user performing expdp directly rather then through a role. (citation needed here as I tried granted through a role to fail)
I've also modified the API code to accept schema name to be exported as input so that we can store it as a procedure and invoke as and when required. The code is as below.

CREATE OR REPLACE PROCEDURE Proc_bkp_schema 
-- orabliss.blogspot.com
(v_schema IN VARCHAR2)
IS
   dp_handle    NUMBER;
   job_status   VARCHAR2 (30);
   v_dt         NUMBER;
   v_sch_name   VARCHAR2(30);
   v_filename   VARCHAR2(30);
   v_logname    VARCHAR2(30);
BEGIN
   SELECT TO_NUMBER (TO_CHAR (SYSDATE, 'yyyymmdd')) INTO v_dt FROM DUAL;
   v_sch_name := 'IN ('''||v_schema||''')';
   v_filename := ''||v_schema||'_'||v_dt||'.dmp'; -- dumpfile name
   v_logname  := ''||v_schema||'_'||v_dt||'.log'; -- logfile name

   -- schema export mode
   dp_handle := DBMS_DATAPUMP.open (operation => 'EXPORT', job_mode => 'SCHEMA');

   -- dump file
   DBMS_DATAPUMP.add_file (
      handle      => dp_handle,
      filename    => v_filename,
      directory   => 'DATAPUMP',
      filetype    => SYS.DBMS_DATAPUMP.KU$_FILE_TYPE_DUMP_FILE);

   -- log file
   DBMS_DATAPUMP.add_file (
      handle      => dp_handle,
      filename    => v_logname,
      directory   => 'DATAPUMP',
      filetype    => SYS.DBMS_DATAPUMP.KU$_FILE_TYPE_LOG_FILE);

   -- specify schema name
   DBMS_DATAPUMP.metadata_filter (handle   => dp_handle,
                                  name     => 'SCHEMA_EXPR',
                                  VALUE    => v_sch_name);

   DBMS_DATAPUMP.start_job (dp_handle);

   DBMS_DATAPUMP.wait_for_job (handle => dp_handle, job_state => job_status);

   DBMS_OUTPUT.put_line (
         'DataPump Export - '
      || TO_CHAR (SYSDATE, 'DD/MM/YYYY HH24:MI:SS')
      || ' Status '
      || job_status);

   DBMS_DATAPUMP.detach (handle => dp_handle);
END;
/

The procedure can be called as below.

begin PROC_BKP_SCHEMA ('ORACLE'); end;
/

This procedure above accepts a single schema as input. The same procedure can be altered to accept many users through a 'for loop' and export several schemas.
You might also make use of metadata_filter with 'NAME_EXPR' to filter out only required tables and data_filter to filter out partitions of table using the datapump API.

Happy working!

Create and manage ACL

Oracle allows access to external network services using several PL/SQL APIs (UTL_TCP, UTL_SMTP, UTL_MAIL, UTL_HTTP andUTL_INADDR), all of which are implemented using the TCP protocol. Sometime when configuring jobs inside database to send emails from within the database, we get the following error.

ORA-24247: network access denied by access control list (ACL)

The reason being the user is not authorised by Oracle to send emails. In this case one should create ACL (Access Control List) and grant permission to access external network or send emails. Oracle 11g introduces fine grained access to network services using access control lists (ACL) in the XML DB repository, allowing control over which users access which network resources, regardless of package grants. So as a prerequisite XML DB component has to be installed on the database. 

The following code creates an ACL and grants permission to a user.

BEGIN
  DBMS_NETWORK_ACL_ADMIN.create_acl (
    acl          => 'email_access.xml', -- ACL file name
    description  => 'Email access to users', -- Description
    principal    => 'USER', -- username
    is_grant     => TRUE, 
    privilege    => 'connect',
    start_date   => SYSTIMESTAMP,
    end_date     => NULL);
  COMMIT;
END;
/

BEGIN
  DBMS_NETWORK_ACL_ADMIN.assign_acl (
    acl         => 'email_access.xml',
    host        => '*', -- all hosts, you can specify which hosts can be accessed
    lower_port  => NULL,
    upper_port  => NULL); 

 COMMIT;
END;
/

Now after creating ACL and granting it to the user and assigning the hosts, the user can send emails without issue from inside the database.

To set the email server, the following code can be issued.

-- replace localhost with mail server name
execute dbms_scheduler.set_scheduler_attribute('email_server','localhost');

We can now check to confirm whether the user is able to send emails from the database by the following block. Connect as granted user and run this

DECLARE
   v_mailsever_host VARCHAR2(30) := 'localhost'; -- replace with mail server name
   v_mailsever_port PLS_INTEGER  := 25; -- 25 is default port
   l_mail_conn  UTL_SMTP.CONNECTION;
BEGIN
   l_mail_conn := UTL_SMTP.OPEN_CONNECTION( v_mailsever_host, v_mailsever_port);
END;
/

Now we see the emails are sent without any errors or issues.

If we need to add another user 'USER2' to ACL, the following can be issued in the database.

  exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('email_access.xml' ,'USER2', TRUE, 'connect');
  exec DBMS_NETWORK_ACL_ADMIN.ADD_PRIVILEGE('email_access.xml' ,'USER2', TRUE, 'resolve');

Now users USER and USER2 will be able to send emails from inside the database using PL/SQL APIs listed above.

Happy working!