Linux: Number of days before password expired

While creating a script to send an email alert when a linux accounts password is about to expire, we needed to determine the number of days remaining before expiry. A quick search of Google surprisingly returned only methods that access /etc/shadow, which is not possible for standard users. However, we did manage to come up with a solution.

The following can be used to echo the number of days remaining to stdout:

EXPIRE_DATE=$(chage -l $USER | awk -F ':' '/^Password expires/ { print $NF }')
[[ $(echo $EXPIRES | grep -c 'never') -gt 0 ]] && echo "never" ||  echo "( $(date -d "$EXPIRE_DATE" "+%s") - $(date +%s) ) / 86400" | bc

It’s worth mentioning the “-d” option of the date command that allows a human readable date to be passed in – and which is automatically parsed without needing to specify a format specification. Very useful!

This article was first published on http://www.dba-resources.com.

Linux: Number of days before password expires

While creating a script to send an email alert when a linux accounts password is about to expire, we needed to determine the number of days remaining before expiry. A quick search of Google surprisingly returned only methods that access /etc/shadow, which is not possible for standard users. However, we did manage to come up with a solution.

The following can be used to echo the number of days remaining to stdout:

EXPIRE_DATE=$(chage -l $USER | awk -F ':' '/^Password expires/ { print $NF }')
[[ $(echo $EXPIRES | grep -c 'never') -gt 0 ]] && echo "never" ||  echo "( $(date -d "$EXPIRE_DATE" "+%s") - $(date +%s) ) / 86400" | bc

It’s worth mentioning the “-d” option of the date command that allows a human readable date to be passed in – which is automatically parsed without needing to specify a format specification.

Very useful!

This article was first published on http://www.dba-resources.com.

Kornshell function declarations

Over the years I have seen and maintained many kornshell scripts written by DBAs, and programming style can provoke strong reactions on the “right” way of doing things.

In this article, I will explain the different ways in which functions can be defined in Kornshell and explain why I personally prefer one method over another.

Declaring functions

The two methods in Kornshell for declaring functions are shown below.

The first, is to use the dedicated Function keyword:

function helloWorld
{ 
  echo "Hello World" 
}

The second way, known as the POSIX Standard, is to use empty parentheses () :

goodbyeWorld()
{ 
  echo "Goodbye World" 
}

Which method to use

When deciding which particular method to use to declare your functions there is one important difference that you must be aware of. The parentheses method does not support local variables.

Here’s a simple test:

#!/bin/ksh

function k
{
  kv1=kv1
  typeset kv2=kv2
  echo inside k
  echo "  kv1: $kv1"
  echo "  kv2: $kv2"
}

p()
{
  pv1=pv1
  typeset pv2=pv2
  echo inside p
  echo "  pv1: $pv1"
  echo "  pv2: $pv2"
}

k
echo outside k
echo "  kv1: $kv1"
echo "  kv2: $kv2"
p
echo outside p
echo "  pv1: $pv1"
echo "  pv2: $pv2"

And the output generated:

inside k
  kv1: kv1
  kv2: kv2
outside k
  kv1: kv1
  kv2:
inside p
  pv1: pv1
  pv2: pv2
outside p
  pv1: pv1
  pv2: pv2

From this we can see that  the local variable kv2 is defined only within the function k and not available outside the function, whereas local variable pv2 is still defined when function p exits.

Although this may seem trivial, tracking down a bug caused by this behaviour will be very difficult. As a result, my advice would be to always use the function keyword to declare your functions.

A final word of advice regarding function naming

When reading and maintaining code written by others it can be very difficult at times to know whether a a line of code refers to a user-defined function or an external command. For example, is the following line of code using a function or a command?

columns "$LIST_OF_DATABASES"

(In case you’re wondering, it must be a user defined function, as the external command for printing columns is “column” – without the “s”).

To make your code more readable, I suggest prefixing every function you define with “FN_” – something I borrowed from my earliest programming days writing programs in BBC Basic!

Isn’t this much clearer?

FN_columns "$LIST_OF_DATABASES"

 

This article was first published on http://www.dba-resources.com.

Wrapping PL/SQL code from within the database

If you have ever wanted to wrap PL/SQL  code that already exists within the database, then you can do so using the simple procedure below which uses a combination of DBMS_METADATA and DBMS_DDL.

Our reason for doing this was because our code is deployed directly from source control into the database and needs to be wrapped after deployment to stop casual viewing of the source. We didn’t want to have to stop wrapped code in source control, and didn’t have access to the wrap tool from the deployment server.

Here’s the procedure:

CREATE OR REPLACE PROCEDURE wrap_existing_pkg( p_pkg_name varchar2 )
AUTHID CURRENT_USER IS        
  l_body      clob;   
  l_src       DBMS_SQL.VARCHAR2A;
  l_idx       number :=1;   
  l_amount    number := 32767;
  l_offset    number := 1;
  l_length    number;
BEGIN
  -- NOTE: Use of this package requires that the user has been granted
  --       execute permission on DBMS_DDL by SYS.

  l_body   := dbms_metadata.get_ddl( 'PACKAGE_BODY', p_pkg_name);                     
  l_length := dbms_lob.getlength( l_body );
  dbms_lob.open( l_body, 0);
  while ( l_offset < l_length ) loop            
    dbms_lob.read( l_body, l_amount, l_offset, l_src(l_idx) );
    l_offset := l_offset + l_amount;
    l_idx := l_idx+1;
  end loop;                
  dbms_lob.close( l_body );
  dbms_ddl.create_wrapped( l_src, 1, l_idx-1 );
END;
/

To wrap a package body called ‘ENCRYPTION_TOOLS’ you would execute the following:

execute wrap_existing_pkg('ENCRYPTION_TOOLS');

That’s it!

This article was first published on http://www.dba-resources.com.

Scripting: Is the DB DataGuarded?

Whilst developing some administrative functions within PL/SQL I needed to know whether the database that the code was being run in was part of an Oracle DataGuard setup, which you would think would be a trivial exercise.

My first thought was that we could determine this via v$database. If the DATABASE_ROLE column in v$database contains STANDBY then we know it’s a DataGuard configuration… but what if the database role is PRIMARY? how then can we determine if that’s a stand-alone database or a DataGuard primary database?

Next, I thought looking in  v$parameter might reveal some useful parameters such as dg_broker_config_file. However this only tells us whether a DataGuard configuration has been setup using DataGuard Broker – not whether the configuration is active, or whether it was configured manually (without broker).

My final solution was to query v$archive_dest_status to determine if there were any valid remote destinations. This isn’t foolproof by any means, but covers the most of the likely scenarios. If you have any better solutions then please post them in the comments.

Here’s the query I used within a PL/SQL block:

DECLARE
  l_isDataguarded number;
BEGIN
  select count(*) into l_isDataguarded from v$archive_dest_status 
    where status='VALID' and type!='LOCAL';
 
  if (l_isDataguarded>0) then
    -- The database is part of a valid dataguard configuration
  else
    -- The database is standalone
 end if; END; /

 

This article was first published on http://www.dba-resources.com.

Continue SCP’ing file(s) after log out

The unix command scp is very useful for transferring files between two hosts over a secure connection, but sometimes you need to kick off an scp command that will take several hours to complete and you want to disconnect and go home.

Using “screen”

One approach is to use the screen command (available only on linux AFAIK) to start the initial transfer, then detach by pressing CTRL-A followed by d.

The command will continue to run even after you have logged out, and screen has the bonus advantage that you can subsequently log back in to the machine and re-connect to the command being run at any time to see how things are progressing.

Exmpale:

oralinux001> screen
 
$ scp linuxamd64_12c_database_1of2.zip oralinux002:/oracle01/staging
<CTRL-A>
d
[detached]
 
oralinux001 > screen -ls

There is a screen on:
14623.pts-0.oralinux001 (Detached)
1 Socket in /var/run/screen/S-oracle.

To reconnect to the session, just use the -r flag (remember to detach from the screen session again as above if you want to disonnect and log out)

screen -r 14623.pts-0.oralinux001

Using “nohup”

If the screen command is not available due to the version of linux/unix being used you may be able to use the nohup command to run the scp in the background. However it does require user equivalence to be configured (negating the need to enter a password) as well as creating a temporary script and a somewhat cryptic form of nohup.
  • Ensure that you have set up user equivalence between the two hosts. This is to remove the need to specify the password for the remote host on the terminal.
  • Now put your scp command in a script (in this case called copy.ksh) and be sure to specify the flags  -q (to disable the progress meter) and -B (to run in”batch!” mode).

#!/bin/ksh
scp -q -B linuxamd64_12c_database_1of2.zip oralinux002:/oracle01/staging

  • Finally, run the script using the follwoing nohup call:
chmod 700 copy.ksh
( nohup ./copy.ksh & ) &
To test that the connection survives a log out, you should immediately log out from the system where the command was issued, and log on to the destination system. Do an ls -l a few times to ensure that the size of the files being transferred are increasing. If the size is not increasing then the command was unsuccessful (and will be in a “zombie” state on the source server).

This article was first published on http://www.dba-resources.com.

Starting a Batch (DOS) file minimized

In my article “How to backup your Evernote notes regularly” I supplied a windows batch file that could be used with the Windows Scheduler to automatically backup your Evernote Notes. In use, however, it soon becomes annoying that the scheduler launches a DOS window to run the script right in the middle of the screen.

The answer to this problem is to specify the command to be used by the Scheduler as follows:

%comspec% /c start “” /min “C:pathtoyourbatchfile.bat”

Note that when you click OK to save the command above you will be warned that you have included arguments in the program text box. You should dismiss the warning by selecting “No” and the command will appear as you specified.

Note: Your batch file must contain an “exit” as the last command in the file, otherwise the minimized command window will not be closed.

This article was first published on http://www.dba-resources.com.

AJAX Tabs in Bootstrap 2.1

Note: When this article was written, the code was tested against Bootstrap v2.1.1 and jQuery v1.8.1 although as it is very simple it should work on the latest versions.

I’m using Twitter Bootstrap in a new project and want to have the content of tabs dynamically loaded via AJAX.  Although in previous projects I have used  jQueryUI to achieve this I wanted to keep the look of all the user interface elements consistent throughout. As a result, I whipped up a simple piece of jQuery to do this and thought I’d share it in case it’s useful to others.

First, we need to output the HTML markup to get Twitter Bootstrap to display the tabs correctly on the page. We do this with a simple <ul> construct, shown below:

<ul id="MainTabs" class="nav nav-tabs">
  <li><a data-target="#apple" data-toggle="tab" href="/apple.html">apple</a></li>
  <li><a data-target="#banana" data-toggle="tab" href="/banana.html">banana</a></li>
  <li><a data-target="#cherry" data-toggle="tab" href="/cherry.html">cherry</a></li>
</ul>

<div class="tab-content">
  <div class="tab-pane" id="apple">Loading...</div>
  <div class="tab-pane" id="banana">Loading...</div>
  <div class="tab-pane" id="cherry">Loading...</div>
</div>

Things to note:

  • We have given our <ul> construct an id of MainTabs. If you want to change this, make sure you also change the references to it in the jQuery code below.
  • Each <li> item contains a data-target attribute which corresponds to a <div> in the second section of code (which is where the content loaded via AJAX will be placed).
  • Each <li> item contains an href attribute which specifies the URL the AJAX call will use to fetch the content. This can be any URL (such as a PHP script) and can contain parameters as normal.

Now all we need to do is output some jQuery code to ensure that the content is loaded whenever the user clicks on a tab:

$(function() {
  $("#MainTabs").tab();
  $("#MainTabs").bind("show", function(e) {    
    var contentID  = $(e.target).attr("data-target");
    var contentURL = $(e.target).attr("href");
    if (typeof(contentURL) != 'undefined')
      $(contentID).load(contentURL, function(){ $("#MainTabs").tab(); });
    else
      $(contentID).tab('show');
  });
  $('#MainTabs a:first').tab("show");
});

We check to see if the “data-target” attribute is set before loading the content. This allows us to mix pre-populated tabs (specified in-line in the page HTML) with dynamic tabs which are loaded via AJAX.

Line 11 uses a:first to ensure that the first tab is selected when the page is initially loaded. If you need to select a different tab on page load (perhaps you are generating the markup from within a PHP script) then you can replace a:first with a[data-target="#apple"] where apple is the id of the tab you want selected.

 

This article was first published on http://www.dba-resources.com.

Database version dependent SQL scripts

I’ve been working with Oracle databases since version 7.1 and over time I’ve built up quite a collection of scripts – some of which were improved as later versions of the database provided additional information in the data dictionary (in some cases columns were renamed completely).

However because I never know when I might be working on a system running an earlier version of Oracle I sometimes keep multiple versions of scripts, usually with a suffix of v7, v8, etc. However, wouldn’t it be nice if you didn’t have to specify the version suffix – if somehow the correct script was run automatically?

Here’s how I do it…

However because I  never know when I might be working on a system running an earlier version of Oracle I sometimes keep multiple versions of scripts, usually with a suffix of v7, v8, etc. However, wouldn’t it be nice if you didn’t have to specify the version suffix – if somehow the correct script was run automatically?

Here’s how I do it…

As an example, I have a simple script called find.sql that I use whenever I want to find a database object that matches a search string, like so:

@find USERS

OWNER         OBJECT_NAME                           OBJECT_TYPE
------------- ------------------------------------- ------------
PUBLIC        ALL_USERS                             SYNONYM
PUBLIC        DBA_USERS                             SYNONYM
PUBLIC        DBA_USERS_WITH_DEFPWD                 SYNONYM
PUBLIC        PROXY_USERS                           SYNONYM
PUBLIC        PROXY_USERS_AND_ROLES                 SYNONYM
PUBLIC        USER_USERS                            SYNONYM
PUBLIC        V$PWFILE_USERS                        SYNONYM
SYS           ALL_USERS                             VIEW
SYS           DBMS_LDAP_UTL.GET_SUBSCRIBED_USERS    CODE
SYS           USER_USERS                            VIEW

The find.sql script actually calls one of two other scripts (find.v7-8.sql or find.v9.sql) depending upon the version of Oracle in use because in Oracle9i and later  there is a dictionary view called ALL_PROCEDURES which I used  to enhance the code to also search for procedure or function names that match the search string.

So, the contents of find.sql are as follows

col script new_value script
set term off
select decode(substr(version,1,instr(version,'.')-1)
             , 7 , 'v7-8'
             , 8 , 'v7-8'
             , 'v9+') script
  from product_component_version
 where lower(product) like 'oracle%edition%';
set term on
@find.&script..sql

Let’s break down the script to see how we can automatically get the version of Oracle and then call a different script depending upon the result.

First, we tell SQL*Plus that we want to define a new variable called script which will be derived from the result of a SQL query:

col script new_value script

Now we turn off the terminal output so that the result of the query where we select the version number of the database from PRODUCT_COMPONENT_VERSION is hidden. In this scenario  we’re only interested in the number before the first period so we use substr to extract it. We then wrap this result in DECODE statement to specify the script will have a suffix of “v7-8” for versions 7 and 8, and “v9+” for all other versions.

set term off
select decode(substr(version,1,instr(version,'.')-1)
             , 7 , 'v7-8'
             , 8 , 'v7-8'
             , 'v9+') script
  from product_component_version
 where lower(product) like 'oracle%edition%';

Note: The result is aliased as “script” because that’s the name of the column we specified would hold the value to be assigned to the variable script.

Finally, we turn the terminal output back on and use  “@” in conjunction with the variable script to call the correct SQL script. (The double period is needed because the first period signifies the end of the variable name, and the second period is the beginning of the file .sql extension).

set term on
@find.&script..sql

Of course, this technique can be adapted to check more than just the major version number or to check something entirely different such as the name of the user currently logged on or whether a particular database option was enabled.

 

This article was first published on http://www.dba-resources.com.