This discussion is archived
6 Replies Latest reply: Dec 10, 2012 11:17 PM by Lukas Bica RSS

Senario for RMAN.

VIRU Newbie
Currently Being Moderated
Hi,

I am using Oracle database 11g R2.

In an Database schema I have many objects and is operational all the time. (can't shutdown the db)
Tester wants to run an huge batch operation in which he will modify many objects while performing many transaction.
For being on safe side I will take an backup before batch starts.
If anything goes wrong I would revert back the schema from the backup.

How could I do this in Rman ?

Note:- One schema will have one tablespace only.

can we take an RMAN tablespace backup before the batch start ?
and then restore & recover from the tablespace backup if anything goes wrong in the schema.

Previously I used to do the above activity using DATAPUMP but it use to take lot of time.
I assume doing with RMAN tablespace backup and restore will be very fast.

Please guide me with proper suggestions to work on.

I tried this with an dummy schema. I logged into the schema and created 4 tables(no data). Then i took the tablespace rman backup. Then i dropped the 4 tables. then i tried to restore and recover the tablesapace backup. Assuming that I should get my 4 tables again. But i got the below error :-
RMAN> sql 'alter tablespace test1 offline immediate';

sql statement: alter tablespace test1 offline immediate


RMAN>  restore tablespace users;

Starting restore at 28-NOV-12
using channel ORA_DISK_1
using channel ORA_DISK_2

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00004 to /data01/oradata/TESTDB/TESTDB/users01.dbf
channel ORA_DISK_1: reading from backup piece /flash/RMAN/TESTDB/backupset/2012_11_28/o1_mf_nnndf_TAG20121128T102719_8cc6fhbq_.bkp
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of restore command at 11/28/2012 10:43:36
ORA-19870: error while restoring backup piece /flash/RMAN/TESTDB/backupset/2012_11_28/o1_mf_nnndf_TAG20121128T102719_8cc6fhbq_.bkp
ORA-19573: cannot obtain exclusive enqueue for datafile 4
 
Thanks in advance.
  • 1. Re: Senario for RMAN.
    Lukas Bica Explorer
    Currently Being Moderated
    Hello,
    have you been thinking about flashback database / table? It's easy to use, if you have configured flashback on your database, you will do only:

    flashback table <table_name> to scn 1234567;
    or
    flashback table <table_name> to restore point <restore_poin_name>;

    Example of configuration:
    conn / as sysdba
    
    set linesize 121
    col name format a30
    col value format a30
    
    SELECT name, value
    FROM gv$parameter
    WHERE name LIKE '%flashback%';
    
    shutdown immediate;
    
    startup mount exclusive;
    
    alter database archivelog;
    
    alter database flashback on;
    
    alter database open;
    
    SELECT flashback_on, log_mode
    FROM v$database;
    
    -- normal restore point
    CREATE RESTORE POINT before_damage;
    
    set linesize 121
    col name format a15
    col time format a32
    
    SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size
    FROM gv$restore_point;
    
    -- do some serious damage
    conn uwclass/uwclass
    
    truncate table airplanes;
    drop table servers;
    
    -- log back in as SYS and repair the DB
    conn / as sysdba
    
    shutdown immediate;
    
    startup mount;
    
    flashback database to restore point before_damage;
    
    alter database open resetlogs;
    
    conn uwclass/uwclass
    
    desc airplanes
    
    SELECT COUNT(*) FROM airplanes;
    
    desc servers
    
    SELECT COUNT(*) FROM servers;
    
    CREATE RESTORE POINT del_rec;
    
    SELECT name, scn, time, database_incarnation#, guarantee_flashback_database, storage_size
    FROM gv$restore_point;
    
    ALTER TABLE servers ENABLE ROW MOVEMENT;
    
    DELETE FROM servers WHERE rownum < 101;
    
    COMMIT;
    
    FLASHBACK TABLE servers TO RESTORE POINT del_rec;
    
    SELECT COUNT(*) FROM servers;
    Another example is on web:
    http://www.orafaq.com/node/1847


    Another option is to use transportable tablespace
    When you wanted to restore the schema data you would simply drop that tablespace including contents and datafiles and then re-attach the old data files (transport them in) - before test you exported metadata + copy datafiles to backup location.

    http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/tspaces013.htm

    Example of usage (for your case - source and destination database is the same):
    http://www.oracle-base.com/articles/misc/transportable-tablespaces.php

    Lukas

    Edited by: Lukas Bica on 28.11.2012 7:50
  • 2. Re: Senario for RMAN.
    VIRU Newbie
    Currently Being Moderated
    Thanks for your detail reply.

    As I have mentioned in my first post I can't shutdown the database. Also in flashback database we can revert back the whole dB not only a particular schema and a huge amount of flash logs will be generated.

    The solution to take an DB in mount mode will not work for my senario.

    I will look into transportable tablespaces.
    Also is their any other RMAN technique ? in which I can work with database open.

    can you Provide me any example in transportable tablespace ?

    Thanks.

    Edited by: VIRU on Nov 28, 2012 12:54 AM
  • 3. Re: Senario for RMAN.
    Lukas Bica Explorer
    Currently Being Moderated
    Example is on following web:
    http://www.oracle-base.com/articles/misc/transportable-tablespaces.php

    In your case source and destination database is the same.

    Before tests you will do steps from example - Source database (check tablespace, tablespace to read only, expdp metadata, copy datafiles, tablespace to read write).
    Than you can do tests...
    After this you will drop tablespace including contents and datafiles + steps from example - Destination database (copy datafiles back and import metadata).


    It's not easy to use RMAN in this scenario and with transportable tablespace it will be faster.

    Lukas
  • 4. Re: Senario for RMAN.
    VIRU Newbie
    Currently Being Moderated
    I tried RMAN with transport tablespace but got the error below :-
    Performing export of metadata...
       EXPDP> Starting "SYS"."TSPITR_EXP_FtDE":
       EXPDP> Processing object type TRANSPORTABLE_EXPORT/PLUGTS_BLK
    
    Removing automatic instance
    shutting down automatic instance
    RMAN-00571: ===========================================================
    RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
    RMAN-00571: ===========================================================
    RMAN-03002: failure of transport tablespace command at 11/29/2012 13:46:28
    RMAN-06136: ORACLE error from auxiliary database: ORA-01097: cannot shutdown while in a transaction - commit or rollback first
    RMAN-06136: ORACLE error from auxiliary database: ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select timestamp, flags from...","SQLA","tmp")
    ORA-25254: time-out in LISTEN while waiting for a message
    what Should be done in this case ?

    I can see an new pmon process running here which is created by RMAN while performing this activity. But the tablespace has not been transported.
  • 5. Re: Senario for RMAN.
    Lukas Bica Explorer
    Currently Being Moderated
    Hello,
    you don't need to use RMAN... in this scenario you aren't creating new database!
    You are just extracting metadata of objects from tablespace and copying tablespace in read-only mode to some disk on your server.

    After this you can do test...

    After tests you will drop tablespace from database, copy datafiles back from temporary location and import metadata.

    Example (test is changing data in tablespace TEST_DATA):

    Prerequisities:

    Check if all data are in tablespace TEST_DATA (indexes for tables are in this tablespace, constraints - both tables are in this tablespace...)
    CONN / AS SYSDBA
    EXEC DBMS_TTS.TRANSPORT_SET_CHECK(ts_list => 'TEST_DATA', incl_constraints => TRUE);
    
    PL/SQL procedure successfully completed.
    
    SQL>
    
    SELECT * FROM transport_set_violations;
    
    no rows selected
    
    SQL>
    you need directory for metadata export
        CONN / AS SYSDBA
        CREATE OR REPLACE DIRECTORY temp_dir AS '/tmp/';
        GRANT READ, WRITE ON DIRECTORY temp_dir TO system;
    Steps before test:
        SQL> ALTER TABLESPACE test_data READ ONLY;
    
        Tablespace altered.
    
        SQL>
    You can export the tablespace metadata.
        $ expdp userid=system/password directory=temp_dir transport_tablespaces=test_data dumpfile=test_data.dmp logfile=test_data_exp.log
    Copy tablespace datafiles to temp for example.
    cp /<path to datafiles>/TEST_DATA_*.dbf /tmp
    The source tablespace can now be switched back to read/write mode.
        ALTER TABLESPACE test_data READ WRITE;
    
        Tablespace altered.
    
        SQL>
    do the tests

    Steps for bring the tablespace to state before test.

    drop that tablespace
    drop tablespace TEST_DATA including contents and datafiles;
    copy datafiles from temp
    cp /tmp/TEST_DATA_*.dbf /<path to datafiles>
    import the tablespace metadata.
     $ impdp userid=system/password directory=temp_dir dumpfile=test_data.dmp logfile=test_data_imp.log transport_datafiles='/<path to datafiles>/test_data01.dbf'
    Switch the tablespace into read write mode.
        SQL> ALTER TABLESPACE test_data READ WRITE;
    
        Tablespace altered.
    
        SQL>
    and that's all
  • 6. Re: Senario for RMAN.
    Lukas Bica Explorer
    Currently Being Moderated
    Hello,
    does transportable tablespace help to you? Or you are trying to do TSPITR (tablespace point-in-time recovery)?

    Here's good example of TSPITR: http://oracleinstance.blogspot.co.uk/2010/10/rman-tspitr-demo.html

    Lukas

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points