1 2 3 4 5 Previous Next 68 Replies Latest reply: Apr 16, 2012 5:37 AM by __OUTSIDER___ Go to original post RSS
      • 60. Re: RMAN duplicate database
        CKPT
        __OUTSIDER___ wrote:
        CKPT wrote:
        You missing only one data file, you already dropped, Now try to open database after recovery need(if) .
        Can't open database or recovery. Oracle still needs some old logs. I think the only way is drop this datafile and
        make fresh backup.
        Can I drop TB SYSAUX and recreate without this datafile.
        Which is the best way to delete unusable datafile?
        SYSAUX tablespace is mandatory. you cant skip it.

        Post

        SQL> select tablespace_name,file_name from dba_data_files where file_id=32;

        check whether this datafile is it from SYSAUX or normal, even if you have only one datafile in tablespace then you can drop datafile,
        if normal tablespace you drop the tablespace completely.

        You can do from new instance , no need to go drop and take backup from source. Check above workaround if not we can see from source. Post what i requested.
        • 61. Re: RMAN duplicate database
          __OUTSIDER___
          CKPT wrote:
          Post

          SQL> select tablespace_name,file_name from dba_data_files where file_id=32;
          SQL> select tablespace_name,file_name from dba_data_files where file_id=32;
          
          TABLESPACE_NAME                FILE_NAME
          ------------------------------ -------------------------------------
          SYSAUX                         /storage/oraData/RBS/ts_log_data002
          check whether this datafile is it from SYSAUX or normal, even if you have only one datafile in tablespace then you can drop datafile,
          if normal tablespace you drop the tablespace completely.
          No this datafile is from SYSAUX and in my case I have no option. 2 datafiles in "SYSAUX" TB
          SQL> select tablespace_name,file_name from dba_data_files where tablespace_name = 'SYSAUX';
          
          TABLESPACE_NAME                FILE_NAME
          ------------------------------ ------------------------------------------
          SYSAUX                         /u01/app/oracle/RBS/sysaux01.dbf
          SYSAUX                         /storage/oraData/RBS/ts_log_data002
          
          SQL>
          ./thanks
          • 62. Re: RMAN duplicate database
            CKPT
            Can you try with just OFFLINE & without drop? check http://oracleinstance.blogspot.in/2010/09/sysaux-datafile-or-tablespace-corrupted.html
            • 63. Re: RMAN duplicate database
              __OUTSIDER___
              CKPT wrote:
              Can you try with just OFFLINE & without drop? check http://oracleinstance.blogspot.in/2010/09/sysaux-datafile-or-tablespace-corrupted.html
              OK , I will check thanks.
              • 64. Re: RMAN duplicate database
                920176
                RMAN duplicate command full explanation.

                http://oracle-mag.com/oracle-backup-and-recovery/duplicating-database-using-rman-duplicate-command/

                Edited by: user2872615 on May 21, 2012 12:00 AM
                • 65. Re: RMAN duplicate database
                  __OUTSIDER___
                  user2872615 wrote:
                  RMAN duplicate command full explanation.

                  [http://oracle-mag.com/oracle-backup-and-recovery/duplicating-database-using-rman-duplicate-command/]
                  Thanks for link but after all we decided that my problem is that I have one datafile that in RECOVERY mode and
                  I can't recover this datafile because some of required log is missing. Also I can't delete datafile, I can't delete
                  tablespace because tablespace is SYSAUX. I even can't delete datafile from EM web control. Of course I can export whole DB and after import it but this is very large DB and also production so I can't have long down time. I think that I can do alter database backup controlfile to trace and after recreate control file (RESETLOG ) with edited script.

                  But unfortunately I also have problems here. Currently I think about this possibility and testing it in test DB but still unsuccessful. if anybody can help me please share your knowledges.

                  I understand that Oracle can't found the one archivelog but how can I backup that log?

                  Here what I did....

                  1. create one datafile in SYSAUX tablespace
                  2. offline it with alter database datafile 'PATH/datafile' offline drop
                  3. backup DB plus archivelog
                  4 cerate controlfile trace file with alter database backup controlfile to trace as PATH/file
                  5 edit trace file and delete offline dropped datafile information

                  I use trace file Set #2. RESETLOGS case because with NORESETLOGS after successful controlfile recreation my dropped datafile is still exists in controlfile as MISSING00002.....

                  controlfile tarce file
                  STARTUP NOMOUNT
                  CREATE CONTROLFILE REUSE DATABASE "XXX" RESETLOGS  ARCHIVELOG
                      MAXLOGFILES 16
                      MAXLOGMEMBERS 3
                      MAXDATAFILES 100
                      MAXINSTANCES 8
                      MAXLOGHISTORY 2920
                  LOGFILE
                    GROUP 1 '/u01/app/oracle/XXX/redo01.log'  SIZE 50M,
                    GROUP 2 '/u01/app/oracle/XXX/redo02.log'  SIZE 50M,
                    GROUP 3 '/u01/app/oracle/XXX/redo03.log'  SIZE 50M
                  DATAFILE
                    '/u01/app/oracle/XXX/system01.dbf',
                    '/u01/app/oracle/XXX/sysaux01.dbf',
                    '/u01/app/oracle/XXX/users01.dbf',
                    '/storage/oraData/XXX/qweate_data000',
                    '/storage/oraData/XXX/qweinc_blob000',
                    '/storage/oraData/XXX/qweout_data000',
                    '/storage/oraData/XXX/qweoons_index000',
                    '/storage/oraData/XXX/qwess_data000',
                    '/storage/oraData/XXX/qwemnt_data000',
                    '/storage/oraData/XXX/qwefi_data000',
                    '/storage/oraData/XXX/qweic_data000',
                    '/storage/oraData/XXX/qweic_int_data000',
                    '/storage/oraData/XXX/qweaate_index000',
                    '/storage/oraData/XXX/qwer_index000',
                    '/storage/oraData/XXX/qwei_index000',
                    '/storage/oraData/XXX/qweot_int_index000',
                    '/storage/oraData/XXX/qweoeeections_blob000',
                    '/storage/oraData/XXX/qwefi_index000',
                    '/storage/oraData/XXX/qweeee_index000',
                    '/storage/oraData/XXX/qweout_int_data000',
                    '/storage/oraData/XXX/qwecase_data000',
                    '/storage/oraData/XXX/qwere_data000',
                    '/storage/oraData/XXX/qweic_int_index000',
                    '/storage/oraData/XXX/qwel_index000',
                    '/storage/oraData/XXX/qwece_index000',
                    '/storage/oraData/XXX/qwesss_index000',
                    '/storage/oraData/XXX/qwe4566_index000',
                    '/u01/app/oracle/XXX/undutbs0001.dbf'
                  CHARACTER SET WE8ISO8859P1
                  ;
                  VARIABLE RECNO NUMBER;
                  EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('CONTROLFILE AUTOBACKUP','ON');
                  VARIABLE RECNO NUMBER;
                  EXECUTE :RECNO := SYS.DBMS_BACKUP_RESTORE.SETCONFIG('DEVICE TYPE','DISK BACKUP TYPE TO COPY PARALLELISM 1');
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18417_7rlx8zds_.arc';
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18417_7rlrntsn_.arc';
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18417_7rlobgcq_.arc';
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18417_7rlobbn3_.arc';
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18418_7rlx91ls_.arc';
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18418_7rlrnz09_.arc';
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18418_7rlrmc5z_.arc';
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18419_7rlx93qw_.arc';
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18419_7rltthcp_.arc';
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18419_7rlttdg6_.arc';
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18420_7rlx95xd_.arc';
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18420_7rlx8wcx_.arc';
                  RECOVER DATABASE USING BACKUP CONTROLFILE
                  ALTER DATABASE OPEN RESETLOGS;
                  ALTER TABLESPACE TEMP ADD TEMPFILE '/u01/app/oracle/XXX/temp01.dbf'
                       SIZE 11240M REUSE AUTOEXTEND ON NEXT 655360  MAXSIZE 11240M;
                  My archived logs
                   RMAN> list archivelog all;
                  
                  List of Archived Log Copies
                  Key     Thrd Seq     S Low Time  Name
                  ------- ---- ------- - --------- ----
                  9207    1    18417   A 17-MAR-12 /storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18417_7rlx8zds_.arc
                  9202    1    18417   A 17-MAR-12 /storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18417_7rlrntsn_.arc
                  9200    1    18417   A 17-MAR-12 /storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18417_7rlobgcq_.arc
                  9199    1    18417   A 17-MAR-12 /storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18417_7rlobbn3_.arc
                  9208    1    18418   A 14-APR-12 /storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18418_7rlx91ls_.arc
                  9203    1    18418   A 14-APR-12 /storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18418_7rlrnz09_.arc
                  9201    1    18418   A 14-APR-12 /storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18418_7rlrmc5z_.arc
                  9209    1    18419   A 14-APR-12 /storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18419_7rlx93qw_.arc
                  9205    1    18419   A 14-APR-12 /storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18419_7rltthcp_.arc
                  9204    1    18419   A 14-APR-12 /storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18419_7rlttdg6_.arc
                  9210    1    18420   A 14-APR-12 /storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18420_7rlx95xd_.arc
                  9206    1    18420   A 14-APR-12 /storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18420_7rlx8wcx_.arc
                  Result
                  [oracle@fgdb2 tmp]$ sqlplus / as sysdba
                  
                  SQL> @XXX_RESET.trc
                  ORACLE instance started.
                  
                  Total System Global Area 2147483648 bytes
                  Fixed Size                  1274716 bytes
                  Variable Size             603983012 bytes
                  Database Buffers         1526726656 bytes
                  Redo Buffers               15499264 bytes
                  
                  Control file created.
                  PL/SQL procedure successfully completed.
                  
                  PL/SQL procedure successfully completed.
                  
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18417_7rlx8zds_.arc'
                  *
                  ERROR at line 1:
                  ORA-00604: error occurred at recursive SQL level
                  
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18417_7rlrntsn_.arc'
                  *
                  ERROR at line 1:
                  ORA-00604: error occurred at recursive SQL level
                  
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18417_7rlobgcq_.arc'
                  *
                  ERROR at line 1:
                  ORA-00604: error occurred at recursive SQL level
                  
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18417_7rlobbn3_.arc'
                  *
                  ERROR at line 1:
                  ORA-00604: error occurred at recursive SQL level
                  
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18418_7rlx91ls_.arc'
                  *
                  ERROR at line 1:
                  ORA-00604: error occurred at recursive SQL level
                  
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18418_7rlrnz09_.arc'
                  *
                  ERROR at line 1:
                  ORA-00604: error occurred at recursive SQL level
                  
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18418_7rlrmc5z_.arc'
                  *
                  ERROR at line 1:
                  ORA-00604: error occurred at recursive SQL level
                  
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18419_7rlx93qw_.arc'
                  *
                  ERROR at line 1:
                  ORA-00604: error occurred at recursive SQL level
                  
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18419_7rltthcp_.arc'
                  *
                  ERROR at line 1:
                  ORA-00604: error occurred at recursive SQL level
                  
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18419_7rlttdg6_.arc'
                  *
                  ERROR at line 1:
                  ORA-00604: error occurred at recursive SQL level
                  
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18420_7rlx95xd_.arc'
                  *
                  ERROR at line 1:
                  ORA-00604: error occurred at recursive SQL level
                  
                  ALTER DATABASE REGISTER LOGFILE '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18420_7rlx8wcx_.arc'
                  *
                  ERROR at line 1:
                  ORA-00604: error occurred at recursive SQL level
                  
                  ORA-00279: change 613190225 generated at 04/14/2012 18:37:29 needed for thread
                  1
                  ORA-00289: suggestion :
                  /storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18421_%u_.arc
                  ORA-00280: change 613190225 for thread 1 is in sequence #18421
                  
                  Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                  
                  ORA-00308: cannot open archived log
                  '/storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18421_%u_.arc'
                  ORA-27037: unable to obtain file status
                  Linux Error: 2: No such file or directory
                  Additional information: 3
                  SQL>
                  thanks for your time.....
                  • 66. Re: RMAN duplicate database
                    __OUTSIDER___
                    Finally I found the way for this situation. I just give Oracle last redo log file
                    ORA-00279: change 613190225 generated at 04/14/2012 18:37:29 needed for thread
                    1
                    ORA-00289: suggestion :
                    /storage/oracle_XXX_backup/XXX/archivelog/2012_04_14/o1_mf_1_18421_%u_.arc
                    ORA-00280: change 613190225 for thread 1 is in sequence #18421

                    Specify log: {<RET>=suggested | filename | AUTO | CANCEL}
                    /storage/oracleData/XXX/redo03.log Log applied. Media recovery complete. SQL>
                    but when I select

                    RECOVER /storage/oracle_XXX_backup/XXX/MISSING00006 :(

                    Is there anybody knows how can I delete this ....?

                    In this stage I can't backup my DB, of course I can with rman skip option , but however after I can't restore this or duplicate......
                    • 67. Re: RMAN duplicate database
                      CKPT
                      Hi,

                      Thanks for your update,
                      When it is requesting for ~7 months older archive, with current redo(archived) it's not possible. For sure datafile will be at least offline prior to perform recovery.

                      Can you check what is the status of that datafile?
                      To drop you can go with alter database datafile offline drop/alter tablespce drop datafile.
                      • 68. Re: RMAN duplicate database
                        __OUTSIDER___
                        Hi, thanks for reply,

                        CKPT wrote:
                        Hi,

                        Thanks for your update,
                        When it is requesting for ~7 months older archive, with current redo(archived) it's not possible. For sure datafile will be at least offline prior to perform recovery.
                        Can you check what is the status of that datafile?
                        I did this with new created DB for test, of course this will not work with old DB.
                        To drop you can go with alter database datafile offline drop/alter tablespce drop datafile.
                        SQL> select FILE#,CREATION_TIME,TS#,STATUS from v$datafile where file#=32;
                        
                             FILE# CREATION_        TS# STATUS
                        ---------- --------- ---------- -------
                                32 11-OCT-11          2 RECOVER
                        
                        SQL> alter database datafile 32 offline drop;
                        
                        Database altered.
                        
                        SQL> select FILE#,CREATION_TIME,TS#,STATUS from v$datafile where file#=32;
                        
                             FILE# CREATION_        TS# STATUS
                        ---------- --------- ---------- -------
                                32 11-OCT-11          2 RECOVER
                        
                        SQL> alter tablespace SYSAUX drop datafile 32;
                        alter tablespace SYSAUX drop datafile 32
                        *
                        ERROR at line 1:
                        ORA-03264: cannot drop offline datafile of locally managed tablespace
                        
                        SQL> alter database datafile 32 offline;
                        
                        Database altered.
                        
                        SQL> select FILE#,CREATION_TIME,TS#,STATUS from v$datafile where file#=32;
                        
                             FILE# CREATION_        TS# STATUS
                        ---------- --------- ---------- -------
                                32 11-OCT-11          2 RECOVER
                        
                        SQL>
                        
                        RMAN> run
                                {
                                set until sequence =21000;
                                restore datafile 32;
                                recover datafile 32;
                                }
                        
                        executing command: SET until clause
                        
                        Starting restore at 16-APR-12
                        using channel ORA_DISK_1
                        
                        creating datafile fno=32 name=/u01/app/oracle/XXX/ts_log_data002
                        restore not done; all files readonly, offline, or already restored
                        Finished restore at 16-APR-12
                        
                        Starting recover at 16-APR-12
                        using channel ORA_DISK_1
                        
                        starting media recovery
                        media recovery complete, elapsed time: 00:00:06
                        
                        Finished recover at 16-APR-12
                             
                        SQL> select FILE#,CREATION_TIME,TS#,STATUS from v$datafile where file#=32;
                        
                             FILE# CREATION_        TS# STATUS
                        ---------- --------- ---------- -------
                                32 11-OCT-11          2 RECOVER
                        
                        SQL>
                        SQL>alter database datafile 32 online;
                         alter database datafile 32 online
                        *
                        ERROR at line 1:
                        ORA-01113: file 32 needs media recovery if it was restored from backup, or END
                        BACKUP if it was not
                        ORA-01110: data file 32: '/u01/app/oracle/XXX/ts_log_data002'
                        I think if it will be placed online I can delete the datafile....... what do you think ?
                        Do you know some method with re-creation of controlfile which can help me to delete this datafile or something else?
                        1 2 3 4 5 Previous Next