FLASHBACK DATABASE

I've used Oracle's flashback technology to restore data, but I never realized that it was possible to flashback the objects as well.

Typically, I've taken an export of the database objects at the point where I want to restore them, do my testing, drop all the users, recreate them and import the dump file back in to "restore."

A colleague of mine sent me a link about GUARANTEE FLASHBACK DATABASE. Pretty cool.

Fired up the local instance and began.

SELECT log_mode, flashback_on 
FROM v$database;

LOG_MODE     FLASHBACK_ON
------------ ------------------
ARCHIVELOG   YES

OK, I cheated and set this all up. My sandbox is typically set in NOARCHIVELOG mode and I had to figure out how to turn flashback on.

Now to create a restore point.

CREATE RESTORE POINT my_restore_point;

There are 2 types of restore points, Normal (the default) and Guarantee. Normal will age out of the control file after a set amount of time. Guarantee must be explicitly dropped. Oracle has the capability to store thousands of restore points.

So I mocked up a small example:

SYS@ELEVEN>SYS@ELEVEN>SYS@ELEVEN>DESC CJUSTICE.DEPT
 Name                       Null?    Type
 -------------------------- -------- --------------------- 
 DEPTNO                     NOT NULL NUMBER(2)
 DNAME                               VARCHAR2(14)
 LOC                                 VARCHAR2(13)

SYS@ELEVEN>ALTER TABLE CJUSTICE.DEPT ADD ( SOME_COLUMN NUMBER );

Table altered.

Elapsed: 00:00:00.87

SYS@ELEVEN>DESC CJUSTICE.DEPT

 Name                       Null?    Type
 -------------------------- -------- ---------------
 DEPTNO                     NOT NULL NUMBER(2)
 DNAME                               VARCHAR2(14)
 LOC                                 VARCHAR2(13)
 SOME_COLUMN                         NUMBER

SYS@ELEVEN>ALTER DATABASE CLOSE;

Database altered.

Elapsed: 00:00:05.57

SYS@ELEVEN>FLASHBACK DATABASE TO RESTORE POINT my_restore_point;

Flashback complete.

Elapsed: 00:00:05.84

SYS@ELEVEN>SHUTDOWN;

ORA-01109: database not open

Database dismounted.

ORACLE instance shut down.

SYS@ELEVEN>STARTUP;
ORACLE instance started.
Total System Global Area  535662592 bytes
Fixed Size                  1334380 bytes
Variable Size             327156628 bytes
Database Buffers          201326592 bytes
Redo Buffers                5844992 bytes
Database mounted.

ORA-01589: must use RESETLOGS or NORESETLOGS option for database open

SYS@ELEVEN>ALTER DATABASE OPEN RESETLOGS;

Database altered.

Elapsed: 00:00:25.18
SYS@ELEVEN>DESC CJUSTICE.DEPT

 Name                     Null?    Type
 ------------------------ -------- -------------------------------
 DEPTNO                   NOT NULL NUMBER(2)
 DNAME                             VARCHAR2(14)
 LOC                               VARCHAR2(13)

Voila!

Pretty amazing me thinks.

I still don't know if you can do this without shutting down the database. But it suits my purposes for the time being.

I'm constantly amazed at how much I don't know about Oracle.

A Java/PL/SQL Port Scanner II

In a post last week, I detailed my efforts in trying to use Java and the UTL_TCP to determine open ports on my DVR.

From that I learned a valuable lesson in infinite loops and also that you could DELETE from dba_jobs.

Since I was too lazy to diagnose the TNS errors I was receiving from using UTL_TCP and I knew that the Java class was working, I decided to combine the 2 approaches on Friday night.

There are 5 cable boxes in the house which means 5 IPs to check. Looking more closely at the MAC addresses, I realize 4 are the same (or roughly the same). That led me to believe that the 5th was the DVR. But I'm going to check them all for fun.

Start with the table to capture those open ports:

CREATE TABLE open_ports
(
  ip VARCHAR2(20),
  port NUMBER(5,0)
);

Then I take the java class (with the Main procedure) and convert it into a standard Java class that can be loaded into the database:

CREATE OR REPLACE AND COMPILE 
JAVA SOURCE NAMED "CheckPorts"
AS
import java.io.IOException;
import java.net.Socket;
import java.net.UnknownHostException;

public class CheckPorts
{
  public static String CheckPorts( String ip, String port ) throws UnknownHostException, IOException
  {
    try
    {
      Socket socket = new Socket( ip, Integer.parseInt( port ) );
      return "Y";
    }
    catch ( UnknownHostException e ){ return "N"; }
    catch ( IOException e ) { return "N"; }
    finally { }
  }
}
/

Then I create a PL/SQL wrapper around the Java class:

CREATE OR REPLACE
FUNCTION check_port( p_ip IN VARCHAR2, p_port IN VARCHAR2 ) RETURN VARCHAR2
AS LANGUAGE JAVA 
NAME 'CheckPorts.CheckPorts( java.lang.String, java.lang.String ) return String';
/
show errors

Then I wrap that call up in a standalone procedure (remember, this is just for fun).

CREATE OR REPLACE
PROCEDURE check_port_insert
  ( p_ip IN VARCHAR2,
    p_port IN INTEGER )
IS
  l_port VARCHAR2(1);
BEGIN
  l_port := check_port( p_ip, TO_CHAR( p_port ) );

  IF l_port = 'Y' THEN
    INSERT INTO open_ports( ip, port )
    VALUES( p_ip, p_port );
  END IF;  
EXCEPTION
  WHEN others THEN
    NULL;
END check_port_insert;
/

No, I didn't re-raise after the WHEN OTHERS, had I been doing this seriously, I would have trapped it or better yet, would have caught the specific exception.

Finally my anonymous block to call check_port_insert and submit as a job (for threading). I picked 30 processes at a time so as not to overload my laptop...on the first go anyway.

DECLARE
  TYPE r_record IS TABLE OF OPEN_PORTS.IP%TYPE;
  l_table R_RECORD := R_RECORD();
  job_count PLS_INTEGER;
  job_number PLS_INTEGER;
BEGIN
  l_table.EXTEND;
  l_table(1) := '192.168.1.102';
  l_table.EXTEND;
  l_table(2) := '192.168.1.100';
  l_table.EXTEND;
  l_table(3) := '192.168.1.101';
  l_table.EXTEND;
  l_table(4) := '192.168.1.103';
  l_table.EXTEND;
  l_table(5) := '192.168.1.104';
  <>
  FOR i IN 1..l_table.COUNT LOOP
    <>
    FOR j IN 1..10000 LOOP

      SELECT COUNT(*)
      INTO job_count
      FROM user_jobs;

      IF job_count < 30 THEN
        dbms_job.submit
          ( job => job_number,
            what => 'BEGIN ' || 
                    '  check_port_insert( ''' || l_table(i) || ''',' || j || '); ' ||
                    '  COMMIT; ' ||
                    'END;',
            next_date => SYSDATE );

        COMMIT;
      ELSE
        dbms_lock.sleep( 5 );
      END IF;
    END LOOP;    
  END LOOP;
END;
/

A table of IP addresses to check each of the 5 known IPs. I used 10,000 as the number of ports to check (I think it goes higher...again, just being a lot lazy). Then a check against USER_JOBS to see if there were 30 jobs or not, if so, wait 5 seconds (DBMS_LOCK.SLEEP) otherwise submit another job.

I probably could have sped things up and not brought down my system by submitting 100 jobs...I'll probably try that tonight.

So here are the results:

PL/SQL procedure successfully completed.

Elapsed: 02:33:18.98
CJUSTICE@ELEVEN>SELECT * FROM open_ports;

IP                         PORT
-------------------- ----------
192.168.1.102              7501
192.168.1.102              8082

2 rows selected.

50K ports in 2 hours and 33 minutes. Is that good? I never let the free port scanner I downloaded go this far so I have no idea. 2 ports open out of 50K. Not a lot. Then I used Telnet to connect to those ports:

Microsoft Telnet> o 192.168.1.102 7501
Connecting To 192.168.1.102...

No go. How about 8082?

Microsoft Telnet> o 192.168.1.102 8082
Connecting To 192.168.1.102...
HTTP/1.1 200 OK
Server: gSOAP/2.7
Content-Type: text/xml; charset=utf-8
Content-Length: 0
Connection: close

Connection to host lost.

Press any key to continue...

OK, so that's probably the port used to pull down the schedule/user guide.

Anyway, I got to try something new and learn a little bit about ports/sockets. I'm not done yet. Next I need to figure out how many possible ports there are...well, this tells me. So I guess I'll have to expand my search. At least now I can concentrate on the DVR (.102) but I still have to check out about 50K ports it looks like.

Things I've Learned This Week

You can in fact DELETE from DBA_JOBS. Not that I had ever tried before this weekend, I just assumed it was a view (still might be with an INSTEAD OF trigger).

I also learned how to reset a sequence without dropping and recreating it. This was courtesy of my crazy DBA, oraclue.

Example:

SQL> CREATE SEQUENCE TEST_SEQ
  2    START WITH 10
  3    INCREMENT BY 10
  4    MINVALUE -1000;

Sequence created.

SQL> SELECT test_seq.nextval FROM dual CONNECT BY LEVEL <= 10;

   NEXTVAL
----------
        10
        20
        30
        40
        50
        60
        70
        80
        90
       100

10 rows selected.

SQL> SELECT * FROM user_sequences WHERE sequence_name = 'TEST_SEQ';

SEQUENCE  MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
-------- ---------- ---------- ------------ - - ---------- -----------
TEST_SEQ      -1000 1.0000E+27           10 N N         20         210

SQL> ALTER SEQUENCE TEST_SEQ INCREMENT BY -10;

Sequence altered.

SQL> SELECT * FROM user_sequences WHERE sequence_name = 'TEST_SEQ';

SEQUENCE  MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
-------- ---------- ---------- ------------ - - ---------- -----------
TEST_SEQ      -1000 1.0000E+27          -10 N N         20          90

SQL> SELECT test_seq.nextval FROM dual CONNECT BY LEVEL <= 10;

   NEXTVAL
----------
        90
        80
        70
        60
        50
        40
        30
        20
        10
         0

10 rows selected.

SQL> SELECT * FROM user_sequences WHERE sequence_name = 'TEST_SEQ';

SEQUENCE  MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
-------- ---------- ---------- ------------ - - ---------- -----------
TEST_SEQ      -1000 1.0000E+27          -10 N N         20        -110

SQL> ALTER SEQUENCE TEST_SEQ INCREMENT BY 10;

Sequence altered.

SQL> SELECT * FROM user_sequences WHERE sequence_name = 'TEST_SEQ';

SEQUENCE  MIN_VALUE  MAX_VALUE INCREMENT_BY C O CACHE_SIZE LAST_NUMBER
-------- ---------- ---------- ------------ - - ---------- -----------
TEST_SEQ      -1000 1.0000E+27           10 N N         20          10

SQL> SELECT test_seq.nextval FROM dual CONNECT BY LEVEL <= 10;

   NEXTVAL
----------
        10
        20
        30
        40
        50
        60
        70
        80
        90
       100

10 rows selected.

Debugging ApEx with FOP

I've been helping a friend out, Daniel McGhan, with an ApEx application he's created. Now by "friend" I mean he's the annoying heckler from my very first ApEx presentation. We also co-presented in September. Now when I say "co-presented" I mean he did everything. I was completely unprepared (and unemployed at the time).

Anyway, he's off galavanting in Europe right now and I offered to help support his application is his absence (slacker).

Of course he leaves me with a whopper of a problem...the PDFs won't generate correctly.

I've been out of the daily ApEx world for going on 2 years now. I try to keep up by reading blogs and such, but there's no replacement for doing it every day.

So we've got the latest version of ApEx (3.1.2) running on Oracle XE (dev environment) to be deployed on a 10.2.0.3 Enterprise Edition Oracle Server running on Windows. At least I understand Windows.

Dan is using FOP to render the PDFs. I have no idea what it means, only that it is Java based. Do I put it in the database? No. It runs from the OC4J server. OK, I have a little experience with that...but it was mostly command line, at some point they (being Oracle) finally made a nice and pretty front end (where the hell was that before?).

Back to ApEx. Interactive Reports. Check the box for PDFs (I'm skipping the whole setting it up through the administrative interface on purpose, but rest-assured, it was set up). I open the page, click on the Interactive Reports thingy, select PDF and Open. Invalid File Type. What? Tried saving it to file, same error.

Maybe this latest greatest Adobe Reader (9 something) can't read...but their latest greatest should be able to read any pdf created by any version right? I'll just open up the file in Notepad. Big clue...right at the top:

ORA-29273: HTTP request failed
ORA-06512: at "SYS.UTL_HTTP", line 1186
ORA-12570: TNS:packet reader failure

Awesome! It's an Oracle error...I can fix that (hopefully).

I went through the forums which let me here. Didn't really help me much. But I did realize the OC4J server wasn't running. Barnacles.

Started it through the provided .bat file. Go through the steps again, still invalid. But I have a new error in the file:

500 Internal Server Error
OracleJSP:
An error occurred. Consult your application/system administrator for support.
Programmers should consider setting the init-param debug_mode to "true" to see the 
complete exception message.

Closer.

This is where my old command line skills came in handy. .bat file looks like this:

set ORACLE_HOME=C:\OAS\oc4j_101340
set JAVA_HOME=C:\Program Files\Java\jre1.6.0_07\
cd C:\OAS\oc4j_101340\bin
oc4j -start

I know where the oc4j file is now, let's start it from the command line:

c:\java -jar c:\oas\oc4j_101340\j2ee\home\oc4j.jar

08/11/19 21:57:01 Oracle Containers for J2EE 10g (10.1.3.4.0)  initialized

Run the PDF again, check console to see what happened. An error! Some sort of jsp exception which of course I don't have and can't repeat now.

So I add -Dbuild.debug=true to the command. Right in the middle of it I see

!!!!!cannot find javac compiler!!!!!!

If ever a clue there was.

Which java is it using? The JRE? Of course there's no compiler, that's a runtime environment. Where's JDeveloper? It has a compiler. c:\jdeveloper\jdk Plug it in a voila! It works.

So apparently it has to be compiled on first use (it's been awhile since I've used J2EE), and that's the reason I can't (or won't) recreate the problem.

A small lesson learned in...something.

A Java PL/SQL Port Scanner

Saturday night I started to wonder how I could get the movies of my DVR. Recently Verizon FIOS added a boatload of HD stations so I've naturally begun to record those. Problem is, the 50 hours or so I have available are quickly eaten up by the HD versions.

We have the multi-room DVR setup, which means we have one DVR but we can view those movies from any TV (with a cable box). Naturally all these boxes are on my internal network (router provided by Verizon).

I understand that with some boxes you can go out and buy additional storage, but I wanted a DIY solution. So, if I could find an open port on one of these boxes, then perhaps I could figure out a way to get the movies onto my computer.

Java to the rescue. Well, not really, but I have always wanted to learn more about that kind of lower level programming (sockets/ports/TCP/whatever). See, I don't really know what I'm talking about which is why I did it.

Since I use JDeveloper for writing SQL and PL/SQL, creating a java class (with the Main method) is easy.

import java.io.IOException;
import java.net.Socket;
import java.net.UnknownHostException;
import java.util.Date;

public class Class1
{
  private static void p ( String text )
  {
    System.out.println( text );
  }

  public static void main(String[] args) throws UnknownHostException, IOException
  {
    Date startDate = new Date();
    String machine = "192.168.1.102";
    Thread thread = new Thread();

    Socket socket;
    int j = 0;
    int x = 0;
    p( "start date/time:  " + startDate.toString() );
    for ( int i = 1000; i < 10000; i++ )
    {
      x++;
      p( "checking port:  " + machine + ":" + i );
      try
      {
        socket = new Socket( machine, i );
        p( "Port open:  " + machine + ":" + i );
        j++;
      }
      catch (UnknownHostException e) { }
      catch (IOException e) { }
      finally
      {
      }
    }
    p( "ports checked:  " + x );
    p( "ports open:  " + j );
    p( "start date/time:  " + startDate.toString() );
    p( "end date/time:    " + new Date().toString() );
  }
}

As LC would say, "That's not so bad."

Only it was slow, 1 port at a time. I did learn that trying to open up a port connection is s l o w. How can I speed this up?

I could make it threaded right? No. That would take too much time.

But hey, I know how to do it in PL/SQL (threaded that is). UTL_TCP should work nicely. I've never had to use it, so a great opportunity to familiarize myself with it (not that it's needed very often).

So I start Oracle XE and initially just try it out to see how it works:

DECLARE
  c UTL_TCP.CONNECTION;
BEGIN
  c := utl_tcp.open_connection
        ( remote_host => '192.168.1.2',
          remote_port => 1,
          tx_timeout => 1 );
  
  utl_tcp.close_connection( c );
EXCEPTION
  WHEN others THEN
    utl_tcp.close_connection( c );
END check_port;
/

Easy enough. Now let's start use DBMS_JOB to "thread" this.

DECLARE
  TYPE r_record IS TABLE OF OPEN_PORTS.IP%TYPE INDEX BY BINARY_INTEGER;
  l_table R_RECORD;
  job_count PLS_INTEGER;
  job_number PLS_INTEGER;
BEGIN
  l_table(1) := '192.168.1.102';
  
  FOR i IN 1..l_table.COUNT LOOP
    FOR j IN 1..10000 LOOP
      SELECT COUNT(*)
      INTO job_count
      FROM user_jobs;

      WHILE job_count < 20 LOOP
      
        dbms_job.submit
          ( job => job_number,
            what => 'BEGIN check_port( ''' || l_table(i) || ''',' || j || '); COMMIT; END;' );

        COMMIT;
      END LOOP;
    END LOOP;    
  END LOOP;
END;
/

So it's running, and running, and running...I opened up another session and did a count on user_jobs. 10000. 12000. 20000. Uh, I think there's something wrong here.

I finally kill the session and there are 36,446 jobs in the queue. All checking the same port.

Then I can't even issue a simple SELECT statement. Maximum number of sessions reached or some such rubbish. Just a reminder, it was Saturday night and I was just trying to see if I could do this quickly.

Since I couldn't connect, I tried bouncing the database (a loud gasp from the DBAs out there). Come on! It's a local XE instance...there's nothing important there.

Start it back up, connect as SYS and still can't issue any queries. I do notice as I hit the / sign and the Enter key a bunch of times that once in a while it will let me do a query. So I create a quick PL/SQL block to remove all the jobs from the queue.

BEGIN
  FOR i IN ( SELECT job FROM user_jobs ) LOOP
    dbms_job.remove( i.job );
  END LOOP;
END;
/

I go through the / + Enter key about 50 times until it finally runs. Success!

Or so I think. Still receiving the max sessions error. There are still 36,446 jobs in user_jobs. I can't remove them anymore as I get "job doesn't exist" when I try to dbms_job.remove it again. Hmmm...How about?

DELETE FROM dba_jobs;

36,446 rows deleted.

What?

How can that be? I didn't expect that to work at all...but it did. I have yet to look up the particulars of my actions (deleting from dba_jobs) but it did the job.

And yes, I'm still planning on writing this (correctly) in the near future...I want more movies!

My Virtual [Column] Failure

Well, I wouldn't really say a failure, but it's certainly been a trial.

We're trying to create a new process that will detect fraud. Much of the data is sensitive in nature ('natch, thanks Jake).

Step 1: Create a encrypted tablespace

CREATE TABLESPACE encrypted_data
  DATAFILE 'c:\oracle\oradata\eleven\encrypted_data_001.dbf'
  ENCRYPTION USING '3DES168'
  DEFAULT STORAGE (ENCRYPT);

Note, I did not do that as our production DBA won't let me near the physical machine (thankfully).

OK, an encrypted tablespace. What else can we do to protect the data? Encrypt it using DBMS_CRYPTO!

Step 2: Create the encryption/decryption routine. For added security, put it in the SYS schema. I decided on that because even if you have EXECUTE ANY PROCEDURE, you can't execute procedures owned by SYS (without being SYS of course). I'll skip the example as it's linked above.

This has to be fast, really fast. How can I index?

Well, I could index the RAW column, encrypt the incoming text/number and then compare...but we need the ability to do partial matches.

Virtual Columns to the rescue! It's not that exciting really, but a good opportunity to put them to use.

So here's the definition of the table:

CREATE TABLE t
(
  id NUMBER(10)
    CONSTRAINT pk_id PRIMARY KEY,
  name_e RAW(256),
  name AS ( CAST( sys.e_pkg.decrypt( name_e ) AS VARCHAR2(20) ) ) VIRTUAL,
  dob_e RAW(256),
  dob AS ( CAST( sys.e_pkg.decrypt( name_e ) AS VARCHAR2(11) ) ) VIRTUAL
);

Instead of doing the decryption in a view I can do it in the table and the best part of all is that you can index those virtual columns.

CREATE INDEX name_idx ON t( name );

CREATE INDEX ssn_idx ON t( ssn );

I loaded some records into the table and began testing. Wall clock results weren't too bad, less than a second for searches (~1 million records) on SSN.

This is where the problems began.

The actual table has some 60 columns. Since I'm lazy, I created a procedure with an input parameter of T%ROWTYPE. You can use the CAST columns (VARCHAR2) to hold the incoming values, encrypt them and put them in their proper column.

I initially had difficulties using the %ROWTYPE so I went back to named parameters (p_name, p_ssn, etc). What I didn't realize is that the length of the values were no longer constrained. Running some tests I managed to successfully insert some data. Much of it longer than the CAST would allow. When performing a SELECT on the table, Oracle gave me the wonderful

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

.After a bit of searching, I found the offending records and tried to DELETE them:

CJUSTICE@SANDBOX>DELETE FROM t WHERE id = 10;
DELETE FROM t WHERE decisionid = 10
     *

ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "SYS.E_PKG", line 42

What? I'm just trying to DELETE a row...not SELECT from it.

So I leave this to the internals experts out there. Why does Oracle attempt to decrypt the column on a DELETE?