4 Replies Latest reply: Mar 9, 2013 2:48 PM by Christpj RSS

    DELETE COPY EXCLUDING ARCHIVELOG

    Christpj
      Hello Experts ,
      I am in a need of your help for the below my query:
      ORACLE 10G

      DB_RECOVERY_FILE_DEST = C:\oraclexe\oradata\TESTDB\flash_recovery_area
      LOG_ARCHIVE_DEST_1='LOCATION=C:\oraclexe\oradata\TESTDB\arch\arch1'
      LOG_ARCHIVE_DEST_2='LOCATION=C:\oraclexe\oradata\TESTDB\arch\arch2'

      I have taken set of backup copy and i now want to delete them.. so i wish to use the systax "DELETE COPY". But it also includes my archive logs in the LOG_ARCHIVE_DEST_n location. How can i use the 'DELETE COPY .... ' command to exclude the archive logs ...

      ================
      OUTPUT:

      RMAN> DELETE COPY;

      released channel: ORA_DISK_1
      allocated channel: ORA_DISK_1
      channel ORA_DISK_1: sid=36 devtype=DISK

      List of Datafile Copies
      Key File S Completion Time Ckp SCN Ckp Time Name
      ------- ---- - --------------- ---------- --------------- ----
      41 1 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\O1_MF_SYSTEM_8MPMQD0H_.DBF
      35 1 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\O1_MF_SYSTEM_8MPKPC6T_.DBF
      29 1 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\O1_MF_SYSTEM_8MPKM3YH_.DBF
      43 2 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\O1_MF_UNDO_8MPMR2GZ_.DBF
      37 2 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\O1_MF_UNDO_8MPKQ9KR_.DBF
      31 2 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\O1_MF_UNDO_8MPKMT8M_.DBF
      42 3 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\O1_MF_SYSAUX_8MPMQV7W_.DBF
      36 3 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\O1_MF_SYSAUX_8MPKPTCT_.DBF
      30 3 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\O1_MF_SYSAUX_8MPKMM3M_.DBF
      44 4 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\O1_MF_HRTBS_8MPMR9LK_.DBF
      38 4 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\O1_MF_HRTBS_8MPKQJQP_.DBF
      32 4 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\O1_MF_HRTBS_8MPKN1L3_.DBF
      45 5 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\O1_MF_TEST_8MPMRDRO_.DBF
      39 5 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\O1_MF_TEST_8MPKQMX0_.DBF
      33 5 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\O1_MF_TEST_8MPKN4PH_.DBF

      List of Control File Copies
      Key S Completion Time Ckp SCN Ckp Time Name
      ------- - --------------- ---------- --------------- ----
      46 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\CONTROLFILE\O1_MF_MYCOPY_8MPMRHXC_.CTL
      40 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\CONTROLFILE\O1_MF_TAG20130309T195722_8MPKQQ1N_
      TL
      34 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\CONTROLFILE\O1_MF_MYCOPY_8MPKN7VH_.CTL

      List of Archived Log Copies
      Key Thrd Seq S Low Time Name
      ------- ---- ------- - --------- ----
      191 1 40 A 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\ARCH\ARCH2\ARC00040_0809123036.001
      190 1 40 A 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\ARCH\ARCH1\ARC00040_0809123036.001

      Do you really want to delete the above objects (enter YES or NO)? no

      RMAN>

      =======================


      Thanks and Regards,
      Christo

      Edited by: Christpj on Mar 9, 2013 8:19 AM
        • 1. Re: DELETE COPY EXCLUDING ARCHIVELOG
          mseberg
          Hello;

          DELETE COPY OF DATAFILE LIKE ' C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\DATAFILE\%';

          Best Regards

          mseberg
          • 2. Re: DELETE COPY EXCLUDING ARCHIVELOG
            Christpj
            Hello Mseberg,

            Great !

            Thanks for the reply .Now i got some idea. But still my controlfile copy remains in the flash recovery area..So i have to go again to delete this controlfile copies...

            So any other options available to delete all backup copies (datafiles,controlfiles,backup copy of archivedlog files if any ) by a single command except that archivelog file in the LOG_ARCHIVE_DEST_n

            And also i am confused that why my archive log file also listed here (LIST COPY) eventhough i didnt copied it using RMAN and i didnt placed it in flash_recovery area..

            ===========================
            Output:
            After the delete datafilecopy


            RMAN> list copy;


            List of Control File Copies
            Key S Completion Time Ckp SCN Ckp Time Name
            ------- - --------------- ---------- --------------- ----
            46 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\CONTROLFILE\O1_MF_MYCOPY_8MPMRHXC_.CTL
            40 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\CONTROLFILE\O1_MF_TAG20130309T195722_8MPKQQ1N_.C
            TL
            34 A 09-MAR-13 620684 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\CONTROLFILE\O1_MF_MYCOPY_8MPKN7VH_.CTL

            List of Archived Log Copies
            Key Thrd Seq S Low Time Name
            ------- ---- ------- - --------- ----
            191 1 40 A 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\ARCH\ARCH2\ARC00040_0809123036.001
            190 1 40 A 09-MAR-13 C:\ORACLEXE\ORADATA\TESTDB\ARCH\ARCH1\ARC00040_0809123036.001

            RMAN>

            ======================

            -Christo
            • 3. Re: DELETE COPY EXCLUDING ARCHIVELOG
              mseberg
              Hello again;

              Sorry. I almost gave that example too

              So

              DELETE COPY OF CONTROLFILE LIKE 'C:\ORACLEXE\ORADATA\TESTDB\FLASH_RECOVERY_AREA\TESTDB\CONTROLFILE\%';

              You could skip the % and do it file by file too.

              Best Regards

              mseberg
              • 4. Re: DELETE COPY EXCLUDING ARCHIVELOG
                Christpj
                Thanks

                Could you please clarify my doubt!

                // And also i am confused that why my archive log file also listed here (LIST COPY) eventhough i didnt copied it using RMAN and i didnt placed it in flash_recovery area.. //

                Regards
                Christo