1 2 Previous Next 17 Replies Latest reply: Apr 2, 2013 11:51 AM by Levi Pereira RSS

    tablespace incomplete recovery

    Neo-b
      Hello All,

      I am using Oracle 11g R2.

      there was users mistake on my database on one tablespace the data is not OK. and they asked me to restore this database to 9:00 Am
      I am planing to restore only this table space and recover it until 9:00 am?

      What is the best solution, is the below correct ?
      alter tablespace TS_NAME offline immediate;
      restore tablespace TS_NAME;
      recover tablespace TS_NAME until time "to_date('2013-04-02:09:00:00', 'YYYY-MM-DD:HH24:MI:SS')";
      alter tablespace TS_NAME online;
      Regards,
        • 1. Re: tablespace incomplete recovery
          mseberg
          Hello;

          When compare to my notes it looks good :
           SQL> alter tablespace users offline;
          RMAN> restore tablespace users;
          RMAN> recover tablespace users until time "to_date('DATE')";
          SQL> alter tablespace users online;
          I generally use a Tablespace Point-in-Time Recovery or restore to a test server and data pump.

          http://docs.oracle.com/cd/B19306_01/backup.102/b14191/rcmtspit.htm#i1014116

          Best Regards

          mseberg
          • 2. Re: tablespace incomplete recovery
            Neo-b
            Thanks I tried on a test database and got the below errors:

            Below is the full log:
            RMAN> RECOVER TABLESPACE TS_NAME until time "to_date('2013-04-02:09:00:00', 'YYYY-MM-DD:HH24:MI:SS')";
            
            Starting recover at 02-APR-13
            using channel ORA_DISK_1
            RMAN-05026: WARNING: presuming following set of tablespaces applies to specified point-in-time
            
            List of tablespaces expected to have UNDO segments
            Tablespace SYSTEM
            Tablespace UNDOTBS1
            Tablespace UNDOTBS2
            
            Creating automatic instance, with SID='rztq'
            
            initialization parameters used for automatic instance:
            db_name=DBNAME
            db_unique_name=rztq_tspitr_DBNAME
            compatible=11.2.0.0.0
            db_block_size=8192
            db_files=200
            sga_target=280M
            processes=50
            #No auxiliary destination in use
            #No auxiliary parameter file used
            
            
            starting up automatic instance DBNAME
            
            Oracle instance started
            
            Total System Global Area     292278272 bytes
            
            Fixed Size                     2220880 bytes
            Variable Size                134220976 bytes
            Database Buffers             146800640 bytes
            Redo Buffers                   9035776 bytes
            Automatic instance created
            Running TRANSPORT_SET_CHECK on recovery set tablespaces
            TRANSPORT_SET_CHECK completed successfully
            
            Removing automatic instance
            shutting down automatic instance
            Oracle instance shut down
            Automatic instance removed
            RMAN-00571: ===========================================================
            RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
            RMAN-00571: ===========================================================
            RMAN-03002: failure of recover command at 04/02/2013 15:16:17
            RMAN-05002: aborting Tablespace Point-in-Time Recovery
            RMAN-05517: tempfile +DATA/preprod/tempfile/temp.263.783132549 conflicts with file used by target database
            RMAN-05001: auxiliary file name +DATA/preprod/datafile/sysaux.257.783132465 conflicts with a file used by the target database
            RMAN-05001: auxiliary file name +DATA/preprod/datafile/undotbs2.264.783132567 conflicts with a file used by the target database
            RMAN-05001: auxiliary file name +DATA/preprod/datafile/undotbs1.258.783132465 conflicts with a file used by the target database
            RMAN-05001: auxiliary file name +DATA/preprod/datafile/system.256.783132465 conflicts with a file used by the target database
            • 3. Re: tablespace incomplete recovery
              mseberg
              Hello again;

              Yes. I would really go with Tablespace Point-in-Time Recovery instead of this method.

              I just tested TSPITR and it still works.

              http://neeraj-dba.blogspot.com/2011/11/rman-tablespace-point-in-time.html

              http://www.oradba.ch/2011/09/tablespace-point-in-time-recovery-and-oracle-11-2-0-2/

              http://oracleinstance.blogspot.com/2011/08/rman-tablespace-point-in-time-recovery.html

              http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmtspit.htm

              http://oracledbabay.blogspot.com/2012/09/tablespace-point-in-time-recovery.html

              Best Regards

              mseberg
              • 4. Re: tablespace incomplete recovery
                Shivananda Rao
                Hello,

                When you are performing a TSPITR, might be you need to provide any auxiliary destination. Please refer the document link already provided by Michael Seberg.
                Also do refer this http://docs.oracle.com/cd/E11882_01/backup.112/e10642/rcmtspit.htm

                Regards,
                Shivananda
                • 5. Re: tablespace incomplete recovery
                  Neo-b
                  Thanks for the info,

                  The auxiliary location is only used during the recovery?? as temporary location ?

                  Regards,
                  • 6. Re: tablespace incomplete recovery
                    mseberg
                    Hello again;

                    The auxiliary location is only used during the recovery?? as temporary location ?

                    Yes and yes.

                    The auxiliary location is a place to recreate the tablespace so the process can export from it. ( the data you need )

                    Oracle Note

                    RMAN: Tablespace Point In Time Recovery (TSPITR) Procedure. [ID 109979.1]

                    Best Regards

                    mseberg

                    Edited by: mseberg on Apr 2, 2013 8:09 AM
                    • 7. Re: tablespace incomplete recovery
                      Neo-b
                      Thanks one more question, the auxiliary instance will also be dropped after recovery finishes?

                      Regards,
                      • 8. Re: tablespace incomplete recovery
                        mseberg
                        No. You should clean it up and watch for Oracle not putting your tablespace back on-line after the recover.


                        Best Regards

                        mseberg
                        • 9. Re: tablespace incomplete recovery
                          Shivananda Rao
                          Hello,

                          During the TSPITR, RMAN automatically creates a temporary auxiliary instance with its datafiles placed under the auxiliary destination specified. Once the recovery is done, I feel that RMAN drops this temporary instance.

                          Yes, it does remove the temporary auxiliary instance automatically. A few links with the logs

                          http://oracleinstance.blogspot.in/2011/08/rman-tablespace-point-in-time-recovery.html
                          http://gavinsoorma.com/2009/11/11g-release-2-tablespace-point-in-time-recovery-recover-from-dropped-tablespace/

                          Regards,
                          Shivananda
                          • 10. Re: tablespace incomplete recovery
                            Neo-b
                            Now i have two contradicted questions:

                            mseberg said:

                            No. You should clean it up and watch for Oracle not putting your tablespace back on-line after the recover.
                            • 11. Re: tablespace incomplete recovery
                              Shivananda Rao
                              Please recheck my previous post updated with a few helpful links (having the complete log of TSPITR).


                              Regards,
                              Shivananda
                              • 12. Re: tablespace incomplete recovery
                                mseberg
                                Hello;

                                We might not be talking the same thing.

                                This file for example is leftover from my test a few weeks ago, oracle did not clean it up. The Aux instance was cleaned up.
                                /u01/oradata/BACKUP2/tspitr_sxyD_22204.dmp
                                Sorry for the confusion.

                                My Log answers your other question :
                                Removing automatic instance
                                Automatic instance removed
                                auxiliary instance file /u01/oradata/BACKUP2/YELLOW/datafile/o1_mf_temp_8lzkbg9v_.tmp deleted
                                auxiliary instance file /u01/oradata/BACKUP2/YELLOW/onlinelog/o1_mf_3_8lzkbfz2_.log deleted
                                auxiliary instance file /u01/oradata/BACKUP2/YELLOW/onlinelog/o1_mf_2_8lzkbfrg_.log deleted
                                auxiliary instance file /u01/oradata/BACKUP2/YELLOW/onlinelog/o1_mf_1_8lzkbfds_.log deleted
                                auxiliary instance file /u01/oradata/BACKUP2/YELLOW/datafile/o1_mf_sysaux_8lzk942g_.dbf deleted
                                auxiliary instance file /u01/oradata/BACKUP2/YELLOW/datafile/o1_mf_undotbs1_8lzk9316_.dbf deleted
                                auxiliary instance file /u01/oradata/BACKUP2/YELLOW/datafile/o1_mf_system_8lzk9m3r_.dbf deleted
                                auxiliary instance file /u01/oradata/BACKUP2/YELLOW/controlfile/o1_mf_8lzk8vkl_.ctl deleted
                                Finished recover at 28-FEB-13
                                Best Regards

                                mseberg

                                Edited by: mseberg on Apr 2, 2013 8:31 AM
                                • 13. Re: tablespace incomplete recovery
                                  Neo-b
                                  Hello Again,

                                  i got the below error at the end of TSPITR:
                                  Removing automatic instance
                                  shutting down automatic instance
                                  RMAN-00571: ===========================================================
                                  RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
                                  RMAN-00571: ===========================================================
                                  RMAN-03002: failure of recover command at 04/02/2013 16:50:00
                                  RMAN-06136: ORACLE error from auxiliary database: ORA-01041: internal error. hostdef extension doesn't exist
                                  RMAN-06136: ORACLE error from auxiliary database: ORA-00603: ORACLE server session terminated by fatal error
                                  ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")
                                  ORA-04031: unable to allocate 56 bytes of shared memory ("streams pool","unknown object","streams pool","fixed allocation callback")
                                  Process ID: 2032496
                                  Session ID: 6 Serial number: 5
                                  • 14. Re: tablespace incomplete recovery
                                    Levi Pereira
                                    Hi,

                                    Try it :
                                    vi /tmp/auxinstparams.ora
                                    SHARED_POOL_SIZE=300M;
                                    
                                    
                                    RUN {
                                    SET AUXILIARY INSTANCE PARAMETER FILE TO '/tmp/auxinstparams.ora';
                                    .
                                    .
                                    .
                                    }
                                    1 2 Previous Next