0

Oracle instance I set up is using USE_DB_RECOVERY_FILE_DEST for archivelog location.

log_archive_dest_1                   string

LOCATION=use_db_recovery_file_ dest, valid_for=(ALL_ROLES,ALL _LOGFILES)

I want to specify the exact location. Old and new location is same, only need to specify the path. I cannot restart the database as it is production server. Is there any way I can do it without restarting the database

3

3 Answers 3

1

There are the manuals Specifying Archive Destinations,LOG_ARCHIVE_DEST_n. But they are unclear about if it is possible to change it online.

  1. Try it out on a test instance. You always should try out thinks on a test instance. Especially if you are using some tips from the internet.
  2. If you an alter system set = ... scope=both; and the parameter cannot be specified in memory, you get a
ERROR at line 1:
ORA-02095: specified initialization parameter cannot be modified

error, but nothing bad happens. So you can try it out if the parameter is specified in memory.

  1. I tried to set the parameter to the value already set and it worked
SQL> alter system set log_archive_dest_1=
  2  'location=USE_DB_RECOVERY_FILE_DEST, valid_for=(ALL_LOGFILES,ALL_ROLES)'
  3  scope=both;

System altered.

SQL> 

If you change the destination for the archives and there are still archives in the old destination then the database knows this an therefore RMAN knows this and will not forget them but backup them when you do your next archive log backup.

2
  • Thanks @miracle173 . I forgot to set scope to both. I was only setting to spfile that's why it hasnt been showing up when I query
    – babuuz
    Commented Nov 18, 2015 at 1:13
  • +1 for answering and not referring folks to the Oracle docs.
    – DonBecker
    Commented Mar 28, 2018 at 2:54
-1

You can do this with sysdba login credentials and just enter this

SQL> alter system set LOG_ARCHIVE_DEST_1='LOCATION=C:\u01\app\oradata\ORCL\archive VALID_FOR=(ALL_LOGFILES,ALL_ROLES) DB_UNIQUE_NAME=orcl';

System altered.

SQL>

C:\u01\app\oradata\ORCL\archive is your custom location

Thanks

-3

It is not possible to change the location of archive log destination without restarting the database. If the you provide the scope as memory they you will get an error.

And if you provide the scope as spfile, then you need yo restart the database.

So I think you need to ask some downtime as do the operation.

enter image description here

enter image description here

8
  • 1
    This is incorrect. LOG_ARCHIVE_DEST_n can be modified without a restart, even with scope=memory. Commented Nov 16, 2015 at 13:30
  • Well I tried, I got the error. Can you please put the statement with the snapshot.
    – Jeethu
    Commented Nov 16, 2015 at 13:33
  • pastebin.com/cZQAFTk5 Commented Nov 16, 2015 at 13:38
  • @BalazsPapp check the edit and kindly tell me if its still possible. I have tried with double quotes also. It does not work. If my answer is wrong please suggest me what else can be done.
    – Jeethu
    Commented Nov 16, 2015 at 14:02
  • @Jeethu: if the parameter is not modifieable in meory , you get a different answer, see (2) from my answer. If you want an answer why your commands do not work open another question but please do not use this question to investigation of your problems
    – miracle173
    Commented Nov 16, 2015 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.