6 Replies Latest reply on Dec 11, 2012 7:17 AM by Lukas Bica-Oracle

    Senario for RMAN.


      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-Oracle
          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;
          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;
          DELETE FROM servers WHERE rownum < 101;
          FLASHBACK TABLE servers TO RESTORE POINT del_rec;
          SELECT COUNT(*) FROM servers;
          Another example is on web:

          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.


          Example of usage (for your case - source and destination database is the same):


          Edited by: Lukas Bica on 28.11.2012 7:50
          • 2. Re: Senario for RMAN.
            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 ?


            Edited by: VIRU on Nov 28, 2012 12:54 AM
            • 3. Re: Senario for RMAN.
              Lukas Bica-Oracle
              Example is on following web:

              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.

              • 4. Re: Senario for RMAN.
                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-Oracle
                  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):


                  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.
                  SELECT * FROM transport_set_violations;
                  no rows selected
                  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.
                  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.
                  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.
                  and that's all
                  • 6. Re: Senario for RMAN.
                    Lukas Bica-Oracle
                    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