Looking at DBA_AUDIT_TRAIL

The view DBA_AUDIT_TRAIL displays all standard audit trail entries in an Oracle database. In my case, I was using Oracle Database 11g Enterprise Edition Release 11.2.0.2.0.

The following SQL can be used to look at entries in this view.

SELECT os_username,
       username,
       terminal,
       To_char(timestamp, 'YYYY-MM-DD HH24:MI:SS') AS tstamp,
       returncode,
       action_name
FROM   dba_audit_trail
order by tstamp asc;

Sample output:

OS_USERNAME  USERNAME  TERMINAL     TSTAMP   RETURNCODE ACTION_NAME

------------ ------------ ------------ ------------------- ---------- ----------------------------

oracle     DBSNMP  ~       2014-10-17 18:32:10 1017 LOGON

root     RLBUSY  unknown      2014-10-17 18:32:15    0 LOGOFF

abc082     IMPREP  MACH-42    2014-10-17 18:32:35    0 LOGOFF

oracle     DBSNMP  unknown      2014-10-17 18:32:36    0 LOGON

oracle     DBSNMP  unknown      2014-10-17 18:32:36    0 LOGOFF BY CLEANUP

oracle     DBSNMP  unknown      2014-10-17 18:32:36    0 LOGOFF BY CLEANUP

oracle     DBSNMP  unknown      2014-10-17 18:32:36    0 LOGON

oracle     DBSNMP  ~       2014-10-17 18:32:57    0 LOGON

oracle     DBSNMP  ~       2014-10-17 18:32:57    0 LOGOFF

FRED       DUMMY  ~       2014-10-17 18:33:00 1017 LOGON

oracle     DBSNMP  ~       2014-10-17 18:33:29 1017 LOGON

oracle     DBSNMP  unknown      2014-10-17 18:33:36    0 LOGOFF BY CLEANUP

oracle     DBSNMP  unknown      2014-10-17 18:33:36    0 LOGOFF BY CLEANUP

oracle     DBSNMP  unknown      2014-10-17 18:34:36    0 LOGON

oracle     DBSNMP  unknown      2014-10-17 18:34:36    0 LOGOFF BY CLEANUP

oracle     DBSNMP  ~       2014-10-17 18:34:39 1017 LOGON

oracle     DBSNMP  ~       2014-10-17 18:34:53    0 LOGOFF

oracle     DBSNMP  ~       2014-10-17 18:34:53    0 LOGON

root     RLBUSY  unknown      2014-10-17 18:35:00    0 LOGON

The RETURNCODE column is the number associated with the ORA-????? error code that was returned to the client computer:

RETURNCODE=0 indicates success

RETURNCODE=1017 indicates bad password

RETURNCODE=28000 indicates account is locked out

For confirmation of the meaning of the returncode value, execute the following code:

SQL> execute dbms_output.put_line(sqlerrm(-1017)) ;

and you should see:

ORA-01017: invalid username/password; logon denied

or from the O/S prompt:

[oracle@host ~]$ oerr ora 01017
01017, 00000, "invalid username/password; logon denied"
// *Cause:
// *Action:

See also

Oracle Support Note "Master Note For Oracle Database Auditing (Doc ID 1299033.1)"

Keywords: oracle audit trail failed login

PowerShell GetEnumerator

I was working with some PowerShell collections recently and forgot how to iterate over an object of the type System.Collections.ArrayList. So after a little digging around, I made some notes to remind myself.

The test code I used to create and populate the array was:


$myArray = New-Object -TypeName System.Collections.ArrayList(100);

$myArray.Add('hearts') | Out-Null;
$myArray.Add('clubs') | Out-Null;
$myArray.Add('diamonds') | Out-Null;
$myArray.Add('spades') | Out-Null;

Write-Host "Before trimming, capacity of the array: $($myArray.Capacity)";
$myArray.TrimToSize();
$myArray.Sort();
Write-Host "After trimming, capacity of the array: $($myArray.Capacity)";
Write-Host "Number of elements actually contained in the ArrayList: $($myArray.Count)";

I created myArray variable with a capacity of 100.  I didn't have to of course as I've only used the suit names found in a deck of playing cards, i.e., four names. This number is so trivial I could have created a new instance of the ArrayList class that is empty and has the default initial capacity. I'll mention later what the default initial capacity might be.

The return type of the ArrayList.Add Method is the type System.Int32, a 32-bit signed integer. Rather than see this value (which represents the ArrayList index at which the value has been added) I've suppressed the output by using the Out-Null Cmdlet. The Out-Null cmdlet sends output to NULL, in effect, deleting it.

Other ArrayList methods and properties have been used to give a little information regarding the array.

Property Capacity - in the first use of this property, the value returned is 100 which is the same value as the number of elements (capacity) used as an argument in the constructor.

In the second use of this property, we get a value of four which reflects the number of suits in our deck of cards. The value has changed because of the use of the TrimToSize method.

Method TrimToSize - sets the capacity to the actual number of elements in the ArrayList. In our example, four. Of course, we could have used a value four in our constructor knowing what we were going to add to the arraylist. But this is just to show what can be done. Especially if you might not know what the number of items you're going to load into the arraylist will be. It's easy to specify the correct value in small demonstrations like this.

Method Sort - sort the elements in the arraylist.

Property Count - confirms the number of elements that are actually in the ArrayList.

Enumerating over the collection - example 1

$myenum = $myArray.GetEnumerator();

while($myenum.MoveNext())

{

    Write-Host "Our playing card is now: $($myenum.Current)";

}

The property $myenum.Current represents the current element in the collection.

For details of the methods and properties of variable myenum, have a look at the IEnumerator interface.

Enumerating over the collection - example 2

$myArray.GetEnumerator() | ForEach-Object {

   Write-Host "Our playing card is now: $_";

}

Keywords: enum enumerator iterate

Aberdeen accommodation

I'm currently looking for accommodation in Aberdeen prior to starting work as an Oracle Database Administrator (DBA).


Please click here for a few more details.

RMAN things

I've put together some Oracle RMAN information in a Google document.


Click here to read the document.



Keywords: oracle rman ianm blog

Temporary Tablespaces

If you have reason to do some work on your temporary tablespaces, the following code may help.


-- Create the new temp tablespace.
CREATE TEMPORARY TABLESPACE temp2
TEMPFILE '/u01/oradata/temp02.dbf' SIZE 10M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- Make this the default temp tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

-- Drop the original temp tablespace.
DROP TABLESPACE temp INCLUDING CONTENTS AND DATAFILES;

-- Create the original tablespace again.
CREATE TEMPORARY TABLESPACE temp
TEMPFILE '/u01/oradata/temp01.dbf' SIZE 10M
AUTOEXTEND ON NEXT 1M MAXSIZE UNLIMITED
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

-- Make this the default temp tablespace.
ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp;

-- Drop the unwanted second temp tablespace.
DROP TABLESPACE temp2 INCLUDING CONTENTS AND DATAFILES;

-- If you have a temporary tablespace without a datafile as
-- sometimes happens with an RMAN restore, this will add
-- a datafile to the tablespace.
ALTER TABLESPACE TEMP
ADD TEMPFILE '/u01/oradata/temp01.dbf' SIZE 10000M
AUTOEXTEND ON NEXT 8K MAXSIZE UNLIMITED;



Keywords: oracle temp tablespace database

Write-Host Considered Harmful

I read an interesting article written by Jeffrey Snover on the topic of Write-Host vs Write-Output. The article can be seen here.

In a nutshell:

The correct cmdlet to use is Write-Output. Using Write-Output will display the results to the screen when you run you script by itself but, it will also allow your script to be used in a pipeline (or foreach loop) and have the results used by other scripts/cmdlets.

See also

Why I Use Write-Host In PowerShell
This article gives some of the benefits of using Write-Host cmdlet.



Keywords: powershell write-host write-output

PowerShell enumerated types

The following code shows how to create your own enumerated types and a possible way of using the type.

$myenum = "
   Namespace myspace
   {
      public enum cards
      {
         hearts,
         clubs,
         diamonds,
         spades
      }
   }
";

try {
  [myspace.cards]
} catch {
  Add-Type -TypeDefinition $myenum -Language CSharpVersion3;
}

Write-Host "Assigning fred an enum value";
$fred=[myspace.cards]::hearts;

switch ($fred)
{
    ([myspace.cards]::hearts) {"Fred is now hearts"; Break}
    ([myspace.cards]::clubs) {"Fred is now clubs"; Break}
    ([myspace.cards]::diamonds) {"Fred is now diamonds"; Break}
    ([myspace.cards]::spades) {"Fred is now spades"; Break}
}

Write-Host "All done now!";

The try/catch block is used to avoid the error: Add-Type : Cannot add type. The type name 'myspace.cards' already exists. At line:1 char:1 + Add-Type -TypeDefinition $myenum -Language CSharpVersion3; + ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ + CategoryInfo : InvalidOperation: (myspace.cards:String) [Add-Type], Exception + FullyQualifiedErrorId : TYPE_ALREADY_EXISTS,Microsoft.PowerShell.Commands.AddTypeCommand if the assembly is already loaded. A namespace is used in the creation of this enum because as the documentation reminds us:

Include a namespace declaration in your type definition. If you omit the namespace declaration, your type might have the same name as another type or the shortcut for another type, causing an unintentional overwrite. For example, if you define a type called "Exception", scripts that use Exception" as the shortcut for System.Exception will fail.

The following code can be used to look at the definitions for the enumerated type.

foreach ($m in [System.Enum]::GetValues([ConsoleColor]))
{ Write-Host ([int]$m): $m }

Keywords: powershell enum namespace