7 Replies Latest reply: Feb 15, 2011 9:34 PM by Salman Qureshi RSS

    archive log mode in oracle 10g on windows environment

    788095
      Hi All,

      I have a production database (Oracle 10g 10.2.0.1.0) on windows 2003 server. yesterday i put the database into archivelog mode. when i query for spfile location it is shown in ORACLE_HOME\dbs location.
      but when i created pfile using the spfile it is created at ORACLE_HOME\database location. and there is another spfile also. i set the log_archive_dest at a location other than flash_recovery_area in pfile, but it is showing the DB_RECOVER_AREA.

      today i seen archives are creating in both locations.

      can a database have two spfiles. and working on them ?

      can i remove a spfile in /dbs location.

      pls. give me suggestion to rectify this

      thanks and regards.
        • 1. Re: archive log mode in oracle 10g on windows environment
          ora_tech
          Hi,

          show parameter archive and check location for archive logs.


          thanks,
          X A H E E R
          • 2. Re: archive log mode in oracle 10g on windows environment
            779621
            Hi,

            A database can NEVER have 2 SPFILES

            You SHOULD NOT remove the SPFILE from $ORACLE_HOME/dbs/ location since it is the default location also dynamic file.


            Regards

            Edited by: Girish on Feb 15, 2011 12:05 AM
            • 3. Re: archive log mode in oracle 10g on windows environment
              Salman Qureshi
              Hi,
              On windows platform, spfile and initfiles are by default created under ORACLE_HOME\database directory and this is also the default location, so, your spfile or initfile in this directory are actually in use.
              i set the log_archive_dest at a location other than flash_recovery_area in pfile, but it is showing the DB_RECOVER_AREA.
              You need to unset the "db_recovery_file_dest" parameter first.
              alter system set db_recovery_file_dest='';
              Now set your log_archive_dest as follows
              alter system set log_archive_dest_1="location=your_location";
              Don't user older "log_archive_dest" parameter

              Salman
              • 4. Re: archive log mode in oracle 10g on windows environment
                Aman....
                785092 wrote:
                Hi All,

                I have a production database (Oracle 10g 10.2.0.1.0) on windows 2003 server. yesterday i put the database into archivelog mode. when i query for spfile location it is shown in ORACLE_HOME\dbs location.
                but when i created pfile using the spfile it is created at ORACLE_HOME\database location. and there is another spfile also. i set the log_archive_dest at a location other than flash_recovery_area in pfile, but it is showing the DB_RECOVER_AREA.

                today i seen archives are creating in both locations.

                can a database have two spfiles. and working on them ?

                can i remove a spfile in /dbs location.

                pls. give me suggestion to rectify this

                thanks and regards.
                Show us the cut/paste of the sql prompt showing the output of ,
                show parameter archive
                show parameter spfile
                On Windows, the database folder is the working one, dbs is meant for *nix systems. So the former one is going to working and is supposed to be used by oracle. Show us the output of the above commands so that we can see what's going on exactly.

                Aman....
                • 5. Re: archive log mode in oracle 10g on windows environment
                  EdStevens
                  Salman Qureshi wrote:
                  Hi,
                  On windows platform, spfile and initfiles are by default created under ORACLE_HOME\database directory and this is also the default location, so, your spfile or initfile in this directory are actually in use.
                  i set the log_archive_dest at a location other than flash_recovery_area in pfile, but it is showing the DB_RECOVER_AREA.
                  You need to unset the "db_recovery_file_dest" parameter first.
                  alter system set db_recovery_file_dest='';
                  Now set your log_archive_dest as follows
                  alter system set log_archive_dest_1="location=your_location";
                  Don't user older "log_archive_dest" parameter

                  Salman
                  Your assertion that "You need to unset the "db_recovery_file_dest" parameter first." is patently false.
                  DB_RECOVERY_FILE_DEST is used for more than just archivelogs. Setting LOG_ARCHIVE_DEST_n to a location other than USE_DB_RECOVERY_FILE_DEST even while DB_RECOVERY_FILE_DEST is set is perfectly acceptable. In fact it is required if you want to continue to use the FRA for things other than archivelogs. Things, like - oh, say - backups!
                  =~=~=~=~=~=~=~=~=~=~=~= PuTTY log 2011.02.15 07:42:18 =~=~=~=~=~=~=~=~=~=~=~=
                  login as: oracle
                  oracle@vmlnx01's password: 
                  Last login: Tue Feb 15 07:01:51 2011 from 192.168.160.1
                  
                  [oracle@vmlnx01 ~]$ sqlplus / as sysdba
                  First, note the date and time of logon, to compare to archivelog timestamps later ...
                  SQL*Plus: Release 10.2.0.4.0 - Production on Tue Feb 15 07:42:27 2011
                  
                  Copyright (c) 1982, 2007, Oracle.  All Rights Reserved.
                  
                  
                  Connected to:
                  Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
                  With the Partitioning, OLAP, Data Mining and Real Application Testing options
                  Next, note the values for the log_archive_dest_n parameters. Actually, the value for #10 is the default if no log_arch_dest_n parms are set, but I like to set it explicitly to avoid ambiguity.
                  SQL> show parameter log_archive_dest_
                  
                  NAME     TYPE VALUE
                  ------------------------------------ ----------- ------------------------------
                  log_archive_dest_1     string
                  log_archive_dest_10     string LOCATION=USE_DB_RECOVERY_FILE_
                   DEST
                  log_archive_dest_2     string
                  log_archive_dest_3     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_2     string enable
                  log_archive_dest_state_3     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
                  And note the setting for db_recovery_file_dest ...
                  SQL> show parameter db_recovery_file_dest
                  
                  NAME     TYPE VALUE
                  ------------------------------------ ----------- ------------------------------
                  db_recovery_file_dest     string /orafra
                  db_recovery_file_dest_size     big integer 4G
                  Now lets check what's in the recovery dest. Notice there is no directory timestamped today (15 Feb), so no archivelogs have been generated for today.
                  SQL> !ls -l /orafra/VLNXORA1/archivelog
                  total 28
                  drwxr-x--- 2 oracle oinstall 4096 Feb 11 17:53 2011_02_08
                  drwxr-x--- 2 oracle oinstall 4096 Feb 11 17:53 2011_02_09
                  drwxr-x--- 2 oracle oinstall 4096 Feb 11 17:53 2011_02_10
                  drwxr-x--- 2 oracle oinstall 4096 Feb 11 17:53 2011_02_11
                  drwxr-x--- 2 oracle oinstall 4096 Feb 12 06:00 2011_02_12
                  drwxr-x--- 2 oracle oinstall 4096 Feb 13 11:00 2011_02_13
                  drwxr-x--- 2 oracle oinstall 4096 Feb 14 22:00 2011_02_14
                  So lets force a log switch and check the results
                  SQL> alter system switch logfile;
                  
                  System altered.
                  
                  SQL> !ls -l /orafra/VLNXORA1/archivelog
                  total 32
                  drwxr-x--- 2 oracle oinstall 4096 Feb 11 17:53 2011_02_08
                  drwxr-x--- 2 oracle oinstall 4096 Feb 11 17:53 2011_02_09
                  drwxr-x--- 2 oracle oinstall 4096 Feb 11 17:53 2011_02_10
                  drwxr-x--- 2 oracle oinstall 4096 Feb 11 17:53 2011_02_11
                  drwxr-x--- 2 oracle oinstall 4096 Feb 12 06:00 2011_02_12
                  drwxr-x--- 2 oracle oinstall 4096 Feb 13 11:00 2011_02_13
                  drwxr-x--- 2 oracle oinstall 4096 Feb 14 22:00 2011_02_14
                  drwxr-x--- 2 oracle oinstall 4096 Feb 15 07:43 2011_02_15
                  
                  SQL> !ls -l /orafra/VLNXORA1/archivelog/2011_02_15
                  total 1892
                  -rw-r----- 1 oracle oinstall 1931776 Feb 15 07:43 o1_mf_1_82_6oo0qomc_.arc
                  So we see that, as expected, the archivelog was written to the FRA. Note the log sequence # of 82
                  Also, let's check my "alternate" location, as yet undefinded to the db ..
                  SQL> !ls -l /oraarch/vlnxora1
                  total 0
                  No files there.
                  Now we will change the archivelog destination. Note that I am NOT touching the FRA definition
                  SQL> alter system set log_archive_dest_1='location=/oraarch/vlnxora1' scope=both;
                  
                  System altered.
                  
                  SQL> alter system set log_archive_dest_10 = '' SCOPE=both;
                  
                  System altered.
                  
                  SQL> show parameter log_archive_dest_
                  
                  NAME     TYPE VALUE
                  ------------------------------------ ----------- ------------------------------
                  log_archive_dest_1     string location=/oraarch/vlnxora1
                  log_archive_dest_10     string
                  log_archive_dest_2     string
                  log_archive_dest_3     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
                  log_archive_dest_state_1     string enable
                  
                  NAME     TYPE VALUE
                  ------------------------------------ ----------- ------------------------------
                  log_archive_dest_state_10     string enable
                  log_archive_dest_state_2     string enable
                  log_archive_dest_state_3     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
                  SQL> show parameter db_recovery_file_dest
                  
                  NAME     TYPE VALUE
                  ------------------------------------ ----------- ------------------------------
                  db_recovery_file_dest     string /orafra
                  db_recovery_file_dest_size     big integer 4G
                  So, above we see that I do not have an archivelog destination set to the FRA, but the FRA is still set. I did not unset it, as you asserted was necessary. I still want my backups to go there.

                  Next we'll do another log switch and check the results.
                  SQL> alter system switch logfile;
                  
                  System altered.
                  First, we'll check the (now unused) FRA destination. Notice that there is still just the one archivelog, sequence 82.
                  SQL> !ls -l /orafra/VLNXORA1/archivelog/2011_02_15
                  total 1892
                  -rw-r----- 1 oracle oinstall 1931776 Feb 15 07:43 o1_mf_1_82_6oo0qomc_.arc
                  Now check the new, non-fra destination. Notice that it now has an archivelog, sequence 83
                  SQL> !ls -l /oraarch/vlnxora1
                  total 96
                  -rw-r----- 1 oracle oinstall 92160 Feb 15 07:45 1_83_732127364.dbf
                  
                  SQL> exit
                  Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Production
                  With the Partitioning, OLAP, Data Mining and Real Application Testing options
                  [oracle@vmlnx01 ~]$ exit
                  logout
                  • 6. Re: archive log mode in oracle 10g on windows environment
                    EdStevens
                    785092 wrote:
                    Hi All,

                    I have a production database (Oracle 10g 10.2.0.1.0) on windows 2003 server. yesterday i put the database into archivelog mode. when i query for spfile location it is shown in ORACLE_HOME\dbs location.
                    but when i created pfile using the spfile it is created at ORACLE_HOME\database location. and there is another spfile also. i set the log_archive_dest at a location other than flash_recovery_area in pfile, but it is showing the DB_RECOVER_AREA.

                    today i seen archives are creating in both locations.

                    can a database have two spfiles. and working on them ?

                    can i remove a spfile in /dbs location.

                    pls. give me suggestion to rectify this

                    thanks and regards.
                    A database cannot have two spfiles. Aman has already asked for
                    show parameter archive
                    show parameter spfile
                    I'd also like to see the exact command and results you used to create the pfile. So far you are telling us your interpretation of what you think you did and see. It would be more helpful to have proof, from a cut'n'paste of a session demonstrating your assertions. I trust evidence over testimony.
                    • 7. Re: archive log mode in oracle 10g on windows environment
                      Salman Qureshi
                      Hi,
                      Your assertion that "You need to unset the "db_recovery_file_dest" parameter first." is patently false.
                      Thanks for correction

                      Salman