This discussion is archived
11 Replies Latest reply: Feb 15, 2005 5:25 AM by Paul M. RSS

alter system with scope=both

96429 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    "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. Oracle ACE
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    ur missing set key word
  • 8. Re: alter system with scope=both
    433419 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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. Oracle ACE
    Currently Being Moderated
    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.