Oracle News

During the upgrading of an Oracle database from 10g to 10.2.0.4 under Solaris I ran into a number of problems. So this is a log of what went wrong and how to get around the problems. This log refers to a single instance non-Rac database environment. All tasks should be performed under the oracle user except where otherwise stated.

1). You first need to download and unzip the p6810189_10204_Solaris-64.zip to /tmp for example.

2). Shutdown the database instance and check that all oracle processes have terminated.

3). Now start the runInstaller in the unzipped tmp directory in Disk1 ie. /tmp/oracle/Disk1

4). When prompted run the root.sh script as root.

5). Startup the database with sqlplus as sys in upgrade mode "startup upgrade".

6). Execute the upgrade assistant "dbua" and follow the instructions.

Possible problems are:

1). You can get an ORA-01507 during the dbua execution. Under solaris that resulted from a discrepancy between the ORACLE_HOME and the oratab entry. Correcting that was tricky for two reasons. Firstly you need root rights to change the oratab and secondly I found two versions of oratab in /etc and /var/opt/oracle.

2). Because the ORACLE_HOME was not setup correctly I needed to alter the root.sh script. Also the rdbms/install/rootadd.sh and the oraInst.loc in ORACLE_HOME had an incorrect path. Although the paths actually mapped to the same physical directories they used alternate linked paths and where as such not identical this caused the dbua to throw an ORA-01507 saying that the "'Dbua thinks this is a Rerun operation" and the database is not mounted - which was actually all rubbish.

Oracle's Flashback functionality is a really great feature and it comes in all shapes and sizes except "schema". If you are writing a test suite and want exactly the same data conditions per test, it would be fantastic to build the schema and fill it with initial data then run 100 different tests, which always reset to the initial data to the original timestamp. That would be "Flashback Schema", which neither 10g nor 11g have on offer. But hey folks, let's do-it-ourselves!

The following code is a simple implementation of Flashback Schema with some restrictions regarding the kind of objects, which can be flashed:

CREATE OR REPLACE procedure Flashback_Schema( p_flashback2date in DATE ) AS

--

-- Author: Paul Lewin / 20th March 2009

-- Version 1.1 / 25th March 2009

-- Description:  Flashback an Oracle schema to a past point-in-time

-- Field of use: Not for production but for testing and simulation (QA)

--

-- Setup the instance:

--  Set the retention period for the instance before use (in seconds) 86400 = 24 Hrs

--  Make sure the UNDO-Tablespace is large enough to handle all changes for 24 Hrs

--  ALTER SYSTEM SET UNDO_RETENTION = 86400;

--  ALTER TABLESPACE UNDOTBS1 RETENTION GUARANTEE;

--

-- Setup the user (as System):

--  Grant execute on DBMS_FLASHBACK to username;

--  Grant FLASHBACK ANY TABLE to username;

--  Grant execute any procedure to username;

--  Grant SELECT ANY TRANSACTION to username;

--

-- Calling:

--  Use the following call in each schema to reset the data to a specific point-in-time

--  EXEC Flashback_Schema_To_Timestamp( TO_DATE('18.03.2009 10:50','DD.MM.YYYY HH24:MI') );

--

-- Restrictions:

--  This will not work on schemas, which include materialized views.

--  Truncating tables will make this functionality in-operable.

--  Using alter table or tablespace commands will also make this functionality in-operable.

-- Cursor builds a list of execute immediate commands to enable row movement

CURSOR c_cmd_move_enable is

  select

    'alter table '||table_name||' enable row movement'

  from

    user_tables

  where

    temporary='N'

  and

    row_movement='DISABLED';

-- Cursor builds a list of execute immediate commands to revoke previously enabled row movement

CURSOR c_cmd_move_disable is

  select

    'alter table '||table_name||' disable row movement'

  from

    user_tables

  where

    temporary='N'

  and

    row_movement='DISABLED';

-- Get a list of tables in the current schema

CURSOR c_cmd_flashback_list is

  select

    table_name

  from

    user_tables

  where

    temporary='N'

  and

    table_name not in (select mview_name from user_mviews)

  Order By

    table_name;

v_cmd varchar2(250 char);

v_cmd_flashback varchar2(32000 char) := 'flashback table ';

v_flashback_date date;

v_table_cnt number := 0;

v_reset_enable := 0;

Begin

               /* Default flashback by 1 hour */

  if p_flashback2date is NULL

  then

    v_flashback_date := sysdate - (1/24); /* flashback 1 hour */

  else

    v_flashback_date := p_flashback2date;

  end if;

  Open c_cmd_move_enable;

  Open c_cmd_move_disable;

  Open c_cmd_flashback_list;

  /* All tables must have row movement allowed! */

  loop

    FETCH c_cmd_move_enable into v_cmd;

    EXIT WHEN c_cmd_move_enable%NOTFOUND;

--  dbms_output.put_line( 'EXECUTE IMMEDIATE ' || v_cmd );

    EXECUTE IMMEDIATE v_cmd;

    v_reset_enable := 1;

  end loop;

  /* Get a list of tables in this schema */

  /* in a comma separated list with 6 names per line */

  loop

    FETCH c_cmd_flashback_list into v_cmd;

    EXIT WHEN c_cmd_flashback_list%NOTFOUND;

    if v_table_cnt > 0

    then

                              v_cmd_flashback := v_cmd_flashback || ', ' || v_cmd;

      if mod(v_table_cnt,6) = 0

      then

                              v_cmd_flashback := v_cmd_flashback || chr(13);

      end if;

    else

                              v_cmd_flashback := v_cmd_flashback || v_cmd; -- first table doesn't need a comma

    end if;

    v_table_cnt := v_table_cnt + 1;

  end loop;

  v_cmd_flashback := v_cmd_flashback || ' TO TIMESTAMP :1';

  /* Flashback all tables in one transaction */

--dbms_output.put_line( 'EXECUTE IMMEDIATE ' || v_cmd_flashback || ' using parameter ' || to_char(v_flashback_date, 'DD.MM.YYYY HH24:MI') );

  EXECUTE IMMEDIATE v_cmd_flashback using v_flashback_date;

  /* restore row movement to all tables which previously had row movement allowed */

  loop

    FETCH c_cmd_move_disable into v_cmd;

    EXIT WHEN c_cmd_move_disable%NOTFOUND;

    v_reset_enable := 0;

--  dbms_output.put_line( 'EXECUTE IMMEDIATE ' || v_cmd );

    EXECUTE IMMEDIATE v_cmd;

  end loop;

  Close c_cmd_flashback_list;

  Close c_cmd_move_disable;

  Close c_cmd_move_enable;

EXCEPTION

    WHEN OTHERS THEN

        IF c_cmd_flashback_list%ISOPEN

        THEN

            CLOSE c_cmd_flashback_list;

        END IF;

        IF c_cmd_move_disable%ISOPEN

        THEN

            CLOSE c_cmd_move_disable;

        END IF;

        IF c_cmd_move_enable%ISOPEN

        THEN

            IF v_reset_enable = 1

            THEN

               /* restore row movement to all tables which previously had row movement allowed */

              loop

                FETCH c_cmd_move_disable into v_cmd;

                EXIT WHEN c_cmd_move_disable%NOTFOUND;

--              dbms_output.put_line( 'EXECUTE IMMEDIATE ' || v_cmd );

                EXECUTE IMMEDIATE v_cmd;

              end loop;

            END IF;

            CLOSE c_cmd_move_enable;

        END IF;

        RAISE;

End;

/

When using Oracle 10g features around the flashback technology it's important to know the differences between the individual options and their respective restrictions. The Flashback Table, Flashback Query, Flashback Transaction Query and Flashback Version Query are all based on undo (or rollback) data option, which primarily provides read consistency and rolling back transactions in Oracle 10g. However, Flashback Database is based on before images in the flashback logs and Flashback Drop is based on another 10g feature called the Recycle Bin, which is there to manage dropped database objects until their space is required to store new data.

Oracle Metalink has some very useful documents relating to these features:

• Note 249319.1 - Configure flashback database
• Note 270060.1 - Use Flashback Table Feature and Resolve errors
• Note 270535.1 - Restrictions on Flashback Table Feature
• Note 317499.1 - 10G Oracle Flashback Transaction Query - Introduction and usage
• Note 369755.1 - Flashback Logs-Space management
• Note:435998.1 - What Do All 10g Flashback Features Rely on and what are their Limitations
  

Finally with Oracle Release 11g we get case sensitive passwords on user accounts. What sounds so simple and normal has taken decades to arrive in the functionality list of Oracles RDBMS. For those who never expected Oracle to jump that gap, there is a compatibility mode, which allows you to use password naming conventions compatible with previous versions ie. non-case-sensitive. As usual major changes don't have to be adopted immediately.

Note: Even if a major change is more of a major impact than anything else. ;-)

Back in September Oracle released the 10.2.0.4 version of Oracle Warehouse Builder for Windows and other platforms. If you go looking for the client download, don't look for a Windows XP client just head directly for the patch on metalink (https://metalink.oracle.com/). The patch-id is 7005587 and you will need the 32 bit client for windows.

Further details are here on the Oracle OWB blog.

> sqlplus /nolog

SQL> connect / as sysdba

SQL> alter system set remote_login_passwordfile=EXCLUSIVE scope=spfile;

that should do the trick although you will need to bounce the instance before it will take effect.

You may also need to re-generate the sys password file like this:

SQL> orapwd file=orapwinst password=pwd123 force=y