Page 1 of 2 12 LastLast
Results 1 to 15 of 26
  1. #1
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195

    Unanswered: Need to rollback but don't know how

    Can someone help me rollback a transaction? It's very important. Thanks, Jeremy
    Nothing better than a good ride.

  2. #2
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    I'm assuming that the trx has already been committed. You can use the
    logminer utility to read thru the redos (or archive logs) and within them
    you can find the SQL to redo any trx.

    EX:

    ----- Build the LogMiner dictionary -----
    -- EXECUTE DBMS_LOGMNR_D.BUILD( -
    -- DICTIONARY_FILENAME =>'LogMiner_Dictionary.ora', -
    -- DICTIONARY_LOCATION => 'C:\Oracle\oradata');

    ----- Specify logs you want to analyze ----

    EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
    LOGFILENAME => 'C:\Oracle\oradata\redo01.log', -
    OPTIONS => dbms_logmnr.NEW);

    -- Add other logs --

    EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
    LOGFILENAME => 'C:\Oracle\oradata\redo02.log', -
    OPTIONS => dbms_logmnr.ADDFILE);

    EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
    LOGFILENAME => 'C:\Oracle\oradata\redo03.log', -
    OPTIONS => dbms_logmnr.ADDFILE);

    -- Remove logs ---

    EXECUTE DBMS_LOGMNR.ADD_LOGFILE( -
    LOGFILENAME => 'C:\Oracle\oradata\redo02.log', -
    OPTIONS => dbms_logmnr.REMOVEFILE);

    --------- Start LogMiner ------------

    EXECUTE DBMS_LOGMNR.START_LOGMNR( -
    DICTFILENAME => 'C:\Oracle\oradata\bba_firm\LogMiner_Dictionary.or a', -
    STARTTIME => to_date('27-JUN-2002 08:55:00', 'DD-MON-YYYY HH24:MIS'), -
    ENDTIME => to_date('27-JUN-2002 09:45:00', 'DD-MON-YYYY HH24:MIS'));

    -- All info --
    SELECT operation, sql_redo FROM v$logmnr_contents;

    -- For a user --
    SELECT sql_redo, sql_undo FROM v$logmnr_contents
    WHERE username = 'RSSQL' AND SEG_NAME = 'PCTRL_SB_SPINA';

    -- Table hits --
    SELECT seg_owner, seg_name, count(*) AS Hits FROM
    v$logmnr_contents WHERE seg_name NOT LIKE '%$' GROUP BY
    seg_owner, seg_name;

  3. #3
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I did this:

    Code:
    UPDATE RT SET RT_DESCR = 'JEREMY'
    This is slightly modified. Is there anyway to undo something like that? I'm new so most of the stuff you said was Greek to me. I just need to undo one transaction. Thanks, Jeremy
    Nothing better than a good ride.

  4. #4
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    In SqlPlus just type

    rollback;

    This will rollback everything to your last commit point (ie since you last commited or rolled back - or if not, since your session started).

    Hth
    Bill

  5. #5
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I tried that and it didn't work. This commit was completed due to bad programming on my side. I'm trying to fix it. Thanks, Jeremy
    Nothing better than a good ride.

  6. #6
    Join Date
    Nov 2002
    Location
    Desk, slightly south of keyboard
    Posts
    697
    If it's been commited then I believe your only option is to follow gbrabhams advice.

    I have no idea if there are any other tools to do it in any simpler way.

    Hth
    Bill

  7. #7
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    That's where the problem comes in. How do I use his advice? I've never done anything like that. I'm a programmer. Thanks, Jeremy
    Nothing better than a good ride.

  8. #8
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Okay...I have created an Data Dictionary. How can I do what I need?
    Thanks, Jeremy
    Nothing better than a good ride.

  9. #9
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    EXAMPLE


    Requirements:
    Your init.ora file has a utl_file_dir entry pointing to a legal directory.
    If you had to make the change to the init.ora file, you’ve got to bounce the database.
    Creating a Dictionary File
    You create a dictionary file by mounting and opening a database and then extracting dictionary information into an external file. You must create the dictionary file from the same database that generated the log files you want to analyze. After it is created, you can use the dictionary file to analyze log files.
    Mount and then open the database whose files that you will want to analyze.
    1. Run the PL/SQL procedure DBMS_LOGMNR_D.BUILD. This procedure creates the dictionary file, which you should use to analyze log files.
    2. The dictionary file will be written out to the destination that you provide as a directory and filename on the command line when executing the procedure.

    Here is an example of what I used to build a dictionary file:
    In init.ora file: utl_file_dir = ‘/home/jtesta/PLAY/logminer’
    Here is script I used
    connect sys/xx
    -- load up the 2 standard packages under the sys schema
    @/oracle/OraHome1/rdbms/admin/dbmslogmnrd.sql
    @/oracle/OraHome1/rdbms/admin/dbmslogmnr.sql
    -- switch the logfile
    alter system switch logfile;
    -- connect as the logminer user(I created it)
    connect logminer/logminer
    --I’m pre-creating the tables BEFORE I create the dictionary file so the info
    -- I need is in the dictionary file
    @create_tables
    set echo on
    -- ok call the procedure to create the dictionary file. Notice the named notation for parameters.
    execute sys.dbms_logmnr_d.build(dictionary_filename => 'dictionary.ora', dictionary_location => ‘/home/jtesta/PLAY/logminer/dictionary.ora’);

    Processing archive/redo logs.

    Startup the database in either of two modes(nomount, mount, yes open is ok too).
    Decide what archive logs/redo logs that you want to process(look at timestamp on the archive logs).
    Run the dbms_logmnr.add_logfile procedure for each log you want to process.
    Once you have all the logs loaded up using the previous procedure, you can run the dbms_logmnr.start_logmnr procedure to populate the v$logmnr_contents view.
    Here is example code:

    execute sys.dbms_logmnr.add_logfile(logfilename => ‘/home/jtesta/PLAY/arch/arc01249.001’, options => sys.dbms_logmnr.new);

    execute sys.dbms_logmnr.add_logfile(logfilename => ‘/home/jtesta/PLAY/arch/arc01250.001', options => sys.dbms_logmnr.addfile);

    execute sys.dbms_logmnr.start_logmnr(dictfilename => ‘/home/jtesta/PLAY/logminer/dictionary.ora’);

    ….

    Here is example of SQL statement used to extract info from v$logmnr_contents:

    select sql_redo, sql_undo
    from v$logmnr_contents
    where seg_name in ('DEPT_TBL','EMP_TBL','EMP_HOLD','EMP_HISTORY') and
    username = 'LOGMINER';


    Hope this helps ...
    Gregg

  10. #10
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I have tried to set the utl_file_dir = 'E:/oracle/oradata' but to no avail. The package is installed. Got any ideas? Thanks, Jeremy
    Nothing better than a good ride.

  11. #11
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    If the utl_file_dir is not already set, you have to put it in the INITxxx.ora
    file and stop and start the database for this change to go into effect
    since this is not a dynamic parameter.

    Gregg

  12. #12
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    Gregg,
    Where is this file? I have modified the init.ora and restarted but it didn't take effect so I assume I've updated the wrong file. I have a database called QSDB if that helps. Also...are you in the US? If so, could I call you as this is very urgent? Later, Jeremy
    Nothing better than a good ride.

  13. #13
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I FINALLY got the file created. Now what? How do I undo the UPDATE transaction posted to the RT Table? Thanks, Jeremy
    Nothing better than a good ride.

  14. #14
    Join Date
    Apr 2003
    Location
    Greenville, SC (USA)
    Posts
    1,155
    To see if you have the utl_file_dir set you can (From SQLPlus) type :
    sql> SELECT VALUE FROM V$PARAMETER WHERE NAME = 'utl_file_dir';

    This will give you the name of the directory you will be using...

    It appears that you are on a NT based OS. That being the case,
    you can find the initialization file for the database in the registry:
    hkey_local_machine/software/oracle/home0/ and the key is
    ora_qsdb_pfile ... The qsdb may be substituted for the actual sid...
    The value here is the location of the init file ... probably called initqsdb.ora ...

    Once you "bump" the database with this change, you should be able
    to follow the steps for using logminer. It is a little tedious, but well
    worth it !!!

    Gregg

  15. #15
    Join Date
    Feb 2003
    Location
    In your thoughts
    Posts
    195
    I am getting an error. I got the utl_file_dir parameter to work finally but when I run the script you gave me...I get "Subscript outside of limit". Got any ideas how to resolve/workaround that? Thanks, Jeremy
    Nothing better than a good ride.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •