11 Replies Latest reply: Feb 15, 2005 7:25 AM by Paul M. RSS

    alter system with scope=both

    96429
      Hi,

      I am trying to enable automatic log archiving at instance start up. I have tried to use the "scope=both" parameter to change both the current session and have it also update the spfile. I tried using this command:

      alter system archive log start to 'C:\oracle\ora92\database\dbname\archive' SCOPE=spfile;

      However it give me an error at the keyword SCOPE saying:

      ERROR at line 1:
      ORA-00933: SQL command not properly ended


      Any ideas on how to do this or do I have to export the spfile to a pfile, make the changes there and then create the pfile from the spfile ?


      Thanks in advance,

      Ned
        • 1. Re: alter system with scope=both
          390519
          If you want to enable archiving:

          (1) Shutdown the database.
          (2) [Back it up.]
          (3) startup mount;
          (4) alter database archivelog;
          (5) alter database open;
          (6) [Back it up again.]

          Then...
          (6) set LOG_ARCHIVE_START=true in the init parameter file
          or
          alter system set log_archive_start=true scope=spfile
          and
          restart the instance

          You should also configure LOG_ARCHIVE_DEST(_n).


          • 2. Re: alter system with scope=both
            96429
            Hi William,

            Thanks, I had done the previous 5 steps. It was step 6 that was causing me problems. It looks like you can't specify the path of the archive dest all within the same line when enabling automatic archiving using with Scope=spfile. I was trying to do it in one step:

            i.e.


            alter system archive log start to 'C:\oracle\ora92\database\dbname\archive' SCOPE=spfile;


            Regards,


            Ned

            • 3. Re: alter system with scope=both
              orafad
              You can not use scope in the archive log clause. Use it in set instead, e.g.
              alter system set param= ... scope= ... ;

              http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96540/statements_23a.htm
              • 4. Re: alter system with scope=both
                343717
                They are two seperate parameter entries try:

                alter system set LOG_ARCHIVE_DEST_1 = 'C:\oracle\ora92\database\dbname\archive' SCOPE=spfile;
                alter system LOG_ARCHIVE_START=TRUE SCOPE=spfile;

                • 5. Re: alter system with scope=both
                  433419
                  "alter system LOG_ARCHIVE_START=TRUE SCOPE=spfile;"

                  What is this statement for? To change to DB from NOARCHIVELOG to ARCHIVELOG ?
                  • 6. Re: alter system with scope=both
                    Paul M.
                    No, to change the DB from NOARCHIVELOG to ARCHIVELOG (or viceversa) you use ALTER DATABASE. This is to automatically archive redo logs when they are full (you could do that manually, not advisable) : of course the DB has to be in ARCHIVELOG.
                    • 7. Re: alter system with scope=both
                      414430
                      ur missing set key word
                      • 8. Re: alter system with scope=both
                        433419
                        Why we need to set it as automatically archive redo logs when they are full?

                        Isn't it if the DB is in ARCHIVELOG mode it will arhive the redologs automatically?
                        • 9. Re: alter system with scope=both
                          426756
                          Hi,
                          if u wants to run ur database in archive mode. then u have to add the following parameter in init.ora file..
                          log_archive_start = true
                          log_archive_dest = 'E:\BackuporacleAppdb'--specify ur archived location
                          log_archive_format = 'arch%s.txt'
                          LOG_ARCHIVE_MAX_PROCESSES=3(how many archiver u wants to run)

                          stutdown database
                          take a backup
                          create a spfile from pfile
                          startup mount
                          alter database open.

                          vinay agarwal
                          OCP


                          • 10. Re: alter system with scope=both
                            433419
                            If I change to archivelog mode in Mount state using alter database archivelog;

                            Will that also automatically set those parameter. Take 10g for example?

                            By default the destination of archive log files are stored in flash recovery area without any additional configuration. In that case i also assume now that those parameter such as log_archive_start=true will also be automatically be set when the DB is set to archivelog mode (in reference of 10g).

                            Correct me if i'm wrong...
                            • 11. Re: alter system with scope=both
                              Paul M.
                              You're right about 10g. Actually LOG_ARCHIVE_START parameter is deprecated in 10g (you don't find it in the Reference documentation).
                              I was referring to the other postings, which, of course were speaking of 9i.