6 Replies Latest reply: Apr 13, 2012 7:26 AM by sybrand_b RSS

    Archive log Destination to be changed in 11G

    poorna
      Hi,

      Oracle Version : 11.2.0.1(Standard Edition)
      Operating System : Linux

      Here i need to change my archive log destination for the below location to */u02/archive/qfapd5p/*
      SQL> show parameter log_ar
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      log_archive_config                   string
      log_archive_dest                     string      /dump/RMAN/archive/
      log_archive_dest_1                   string
      log_archive_dest_10                  string
      log_archive_dest_11                  string
      log_archive_dest_12                  string
      log_archive_dest_13                  string
      log_archive_dest_14                  string
      log_archive_dest_15                  string
      log_archive_dest_16                  string
      log_archive_dest_17                  string
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      log_archive_dest_18                  string
      log_archive_dest_19                  string
      log_archive_dest_2                   string
      log_archive_dest_20                  string
      log_archive_dest_21                  string
      log_archive_dest_22                  string
      log_archive_dest_23                  string
      log_archive_dest_24                  string
      log_archive_dest_25                  string
      log_archive_dest_26                  string
      log_archive_dest_27                  string
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      log_archive_dest_28                  string
      log_archive_dest_29                  string
      log_archive_dest_3                   string
      log_archive_dest_30                  string
      log_archive_dest_31                  string
      log_archive_dest_4                   string
      log_archive_dest_5                   string
      log_archive_dest_6                   string
      log_archive_dest_7                   string
      log_archive_dest_8                   string
      log_archive_dest_9                   string
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      log_archive_dest_state_1             string      enable
      log_archive_dest_state_10            string      enable
      log_archive_dest_state_11            string      enable
      log_archive_dest_state_12            string      enable
      log_archive_dest_state_13            string      enable
      log_archive_dest_state_14            string      enable
      log_archive_dest_state_15            string      enable
      log_archive_dest_state_16            string      enable
      log_archive_dest_state_17            string      enable
      log_archive_dest_state_18            string      enable
      log_archive_dest_state_19            string      enable
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      log_archive_dest_state_2             string      enable
      log_archive_dest_state_20            string      enable
      log_archive_dest_state_21            string      enable
      log_archive_dest_state_22            string      enable
      log_archive_dest_state_23            string      enable
      log_archive_dest_state_24            string      enable
      log_archive_dest_state_25            string      enable
      log_archive_dest_state_26            string      enable
      log_archive_dest_state_27            string      enable
      log_archive_dest_state_28            string      enable
      log_archive_dest_state_29            string      enable
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      log_archive_dest_state_3             string      enable
      log_archive_dest_state_30            string      enable
      log_archive_dest_state_31            string      enable
      log_archive_dest_state_4             string      enable
      log_archive_dest_state_5             string      enable
      log_archive_dest_state_6             string      enable
      log_archive_dest_state_7             string      enable
      log_archive_dest_state_8             string      enable
      log_archive_dest_state_9             string      enable
      log_archive_duplex_dest              string
      log_archive_format                   string      %t_%s_%r.dbf
      
      NAME                                 TYPE        VALUE
      ------------------------------------ ----------- ------------------------------
      log_archive_local_first              boolean     TRUE
      log_archive_max_processes            integer     4
      log_archive_min_succeed_dest         integer     1
      log_archive_start                    boolean     FALSE
      log_archive_trace                    integer     0
      SQL>
      Here my doubts are.

      1) Do I need to copy all the existing Archive logs to new location.
      2) And do I need to do any thing after copying those logs to new location.

      Please help me .

      Thanks & Regards,
      Poorna Prasad.S
        • 1. Re: Archive log Destination to be changed in 11G
          sybrand_b
          1) NO!!! Why? Think! They are registered in the controlfile with their full paths, why move them? The activity is 100 percent redundant!!!
          2) NO


          Can you make sure your 'alias' is in lower case. All upper case is pretty annoying.

          -------------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: Archive log Destination to be changed in 11G
            NikolayIvankin
            No, all archive log entries will remain at controlfile/catalog.
            But if you want, you can move all archive logs to new location by copying and CATALOG START WITH, or making a backup of backup to new location
            • 3. Re: Archive log Destination to be changed in 11G
              sybrand_b
              Please do not tell him to waste his time by
              a) redundant activities
              b) redundant questions

              ---------
              Sybrand Bakker
              Senior Oracle DBA
              • 4. Re: Archive log Destination to be changed in 11G
                poorna
                Hi,

                Can i do as mentioned below.

                First i will take Full Rman backup for my existing database and will put my database back to No archive log mode and change the archive log destination and revert it back to Archive log mode and again take the Full RMAN backup .


                In this way can i change the location.




                Thanks & Regards,
                Poorna Prasad.S
                • 5. Re: Archive log Destination to be changed in 11G
                  EdStevens
                  SIDDABATHUNI wrote:
                  Hi,

                  Can i do as mentioned below.

                  First i will take Full Rman backup for my existing database and will put my database back to No archive log mode and change the archive log destination and revert it back to Archive log mode and again take the Full RMAN backup .


                  In this way can i change the location.




                  Thanks & Regards,
                  Poorna Prasad.S
                  You can. But why?
                  All you need to do is
                  sql> alter system set log_archive_dest_1='<new location>' scope=both;
                  sql> alter system set log_archive_dest='' scope=both;
                  log_archive_dest was deprecated long ago and is retained only for backward compatibility. you should not be using it at all.
                  Changing an archivelog destination is dynamic. As soon as it is changed, then next archivelog to be writting will go to the new location.
                  When an archivelog is written, it's full directory specification is recorded into the control file. Changing the archivelog destination does not change that, so the existing files stay where they are, and their location remains recorded in the control file. LOG_ARCHIVE_DEST_n is used to tell the archiver where to write the logs. It is NOT used to locate the logs after the fact. That location of logs when needed is done by the fact that the full location was recorded in the control file at the time the log was written.

                  There is no need to stop the database.\
                  There is no need to take the database out of archivelog mode
                  There is no need to relocate the existing archivelogs
                  There is no need to take a special backup.
                  If you are doing proper backup and housekeeping on your archivelogs, the ones in the old location will eventually be deleted by said housekeeping processes. There is no need to move them.
                  • 6. Re: Archive log Destination to be changed in 11G
                    sybrand_b
                    The procedure is a 100 percent waste of time.
                    Nothing what you outline is required. Just alter system will work!

                    ---------
                    Sybrand Bakker
                    Senior Oracle DBA