Flashback Drop – 2 Tables with the same name.

Flashback drop allows you to restore a table without having to perform a point-in-time recovery. In 10G the DROP DDL command has been changed internally to a rename the table and its associated objects (excluding foreign key constraints). Its a feature that I personally never have had to use. Although, leaving this feature enabled did cause some performance issues for me. (See: Previous blog posts about the Recycle bin feature.)

The basic syntax for restoring a dropped table is simply:

SQL> flashback table <tablename> to before drop;
Flashback complete.

Simple enough. But what if since the table was dropped, another table was created with the same name? By default, Flashback drop restores the most recent version of the table. In order to restore a previous copy you need to specify the recycle bin name. For example:

SQL> drop table EMP;

Table dropped.

SQL> create table EMP (name varchar2(50));

Table created.

SQL> insert into EMP values ('John Doe');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from emp;

NAME
--------------------------------------------------------------------------------
John Doe

SQL> drop table emp;

Table dropped.

SQL> select object_name, original_name, type, droptime from user_recyclebin;

OBJECT_NAME
--------------------------------------------------------------------------------
ORIGINAL_NAME
--------------------------------------------------------------------------------
TYPE
---------------------------------------------------------------------------
DROPTIME
---------------------------------------------------------
BIN$Yr7gB0huGergRAADuvZ6hQ==$0
EMP
TABLE
2009-02-12:14:39:14

BIN$Yr7gB0hvGergRAADuvZ6hQ==$0
EMP
TABLE
2009-02-12:14:39:53


SQL> flashback table "BIN$Yr7gB0huGergRAADuvZ6hQ==$0" to before drop;

Flashback complete.

SQL> desc emp;
Name Null? Type
---------------- -------- ----------------------------
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)

Windows, Firewalls and Redirects

We are in the middle of a network migration and we quickly discovered issues connecting to databases on Windows servers.   The two networks normally can’t talk to each other but to aid in the migration we placed some of the servers behind a NAT-enabled router.  You could ping the listener (tnsping) but when a connection was attempted the client would hang.  (SQL*Plus, Toad, etc)

One of our network admins noticed in a network trace that the listener would redirect the client to a specific port (which is normal) but it would tell the client to use the servers IP Address, not the NAT address.  So the client would try and connect to an IP address that didn’t ‘exist’.

There are two possible solutions to this problem.  The first, is to modify the firewall and enable SQLNet Inspection but it wasn’t successful for us.  We could connect to the database and execute small queries but large ones would hang after a certain amount of data was reached.   We disabled this feature and while the network team was investigating we tried the second option.

The second option is to add the parameter:

USE_SHARED_SOCKET = TRUE

to the servers registry (regedit) under \\HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\HOME<#>.   When this feature is enabled, it tells the Listener to spawn a new thread on the listening port.   As new connections arrive, they spawn threads as well, so in the end you have the listener and a number of connections all using port 1521.  The disadvantage to this option is that bouncing the listener also disconnections all sessions.

This option worked and since this setup is temporary we’ll stick with it.