This discussion is archived
7 Replies Latest reply: Dec 5, 2012 11:25 AM by EdStevens RSS

changing archive log mode without shutting down database

934646 Newbie
Currently Being Moderated
Hi all,

Is there any way to set the database to archive log mode before shutting down the database and when the database shuts and starts, the database should be automatically in archive log mode?

because we can not shutdown the database in weekdays and we have system reboot on weekend and we are not here on that day. So is there a way to set the database in archive log at the memory level so that when the database reboots, it should automatically start in archive log mode.

my environment is windows server 2008 R2 with oracle 11.2.0.3

Thanks in advance.

Edited by: 931643 on Dec 5, 2012 10:37 AM
  • 1. Re: changing archive log mode without shutting down database
    Max Seleznev Explorer
    Currently Being Moderated
    I'm afraid it's not possible.

    Since you mentioned the system restart I would assume you use some sort of automated process to bring the database up. Why not modify that process to include additional steps such as switching the database to archivelog mode and opening it?
  • 2. Re: changing archive log mode without shutting down database
    6363 Guru
    Currently Being Moderated
    The steps needed are documented and include required shutdowns

    http://docs.oracle.com/cd/E11882_01/server.112/e25494/archredo003.htm#autoId1
  • 3. Re: changing archive log mode without shutting down database
    934646 Newbie
    Currently Being Moderated
    thank you for the reply Max Seleznev,

    First I thought of writing script in order to include in that process. But we can not run any commands in the sqlplus using the windows jobs. for eg:

    you can write a batch file as below:

    cd \
    sqlplus / as sysdba
    select name,open_mode from v$database;

    when you run this, it will open until sqlplus prompt but then will not execute the query.

    that is the reason I am figuring out whether there is an alternate way.

    Thanks
  • 4. Re: changing archive log mode without shutting down database
    jgarry Guru
    Currently Being Moderated
    Why can you not either:

    Be there. This is a normal requirement for DBA work.

    Log in remotely. This is becoming a normal requirement for DBA work.

    You never know what is going to go wrong, from misconfigured archive areas to sessions recalcitrant about dying to hardware about to expire, so really, unusual administrative events do require an administrator.
  • 5. Re: changing archive log mode without shutting down database
    sb92075 Guru
    Currently Being Moderated
    931643 wrote:
    thank you for the reply Max Seleznev,

    First I thought of writing script in order to include in that process. But we can not run any commands in the sqlplus using the windows jobs. for eg:

    you can write a batch file as below:

    cd \
    sqlplus / as sysdba
    select name,open_mode from v$database;

    when you run this, it will open until sqlplus prompt but then will not execute the query.

    that is the reason I am figuring out whether there is an alternate way.

    Thanks
    when you do things wrong, you don't obtain desired results

    please desired SQL in separate file (run.sql) then do as below

    sqlplus scott/tiger @run.sql
  • 6. Re: changing archive log mode without shutting down database
    jgarry Guru
    Currently Being Moderated
    To run a sqlplus in a batch, you can either put the commands in a sql file and execute that from the bat, or echo commands into sqlplus through a pipe. Easily found with google: http://www.orafaq.com/forum/t/164135/0/
  • 7. Re: changing archive log mode without shutting down database
    EdStevens Guru
    Currently Being Moderated
    931643 wrote:
    thank you for the reply Max Seleznev,

    First I thought of writing script in order to include in that process. But we can not run any commands in the sqlplus using the windows jobs. for eg:

    you can write a batch file as below:

    cd \
    sqlplus / as sysdba
    select name,open_mode from v$database;

    when you run this, it will open until sqlplus prompt but then will not execute the query.
    What did you expect? Well, actually, I know what you expected. But think about it for a second. You have a batch file with the mentioned three lines. How does that batch file get processed .. what is happening when it is being processed?

    It is not being processed by sqlplus, it is being processed by the windows command processor. Just like the cmd.exe. Each line is read by the command processor and executed accordingly. So ....
    cd \
    the command processor calls 'cd' , and passes the value '\' to do with as it sees fit. 'cd' then changes the current directory of the session to whatever is indicated by the passed parameter. in this case, that value is '\' , so the resulting current directory is the base (root, in *nix terms) of the current drive.  When 'cd' has finished, it returns control to the command processor, who then reads the next line ....
    sqlplus / as sysdba
    the command processor calls sqlplus.exe and passes it the command line parms '/ as sysdba' to do with as it (sqlplus) sees fit. sqlplus does whatever it does with what it has been given In this case it tries to connect to whatever local database is indicated by the value of ORACLE_SID, then waits for more input. Whenever sqlplus exits (how does it know to exit?) it returns control to the command processor, who then reads the next line ....
    select name,open_mode from v$database;
    The command processor will read this line and attempt to find an executable file named 'select' and pass it the rest of the command line 'name,open_mode from v$database;'

    Hmm, what do you suppose will happen with that operation?

    that is the reason I am figuring out whether there is an alternate way.
    yes but you have to use the proper technique. And since you are building this into a production stop/start without testing the actual stop/start commands ... what do you suppose will be the outcome if something goes wrong?

    After hours and weekend work is one reason DBA's make more money than developers.


    >
    Thanks

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points