7 Replies Latest reply: Dec 5, 2012 1:25 PM by EdStevens RSS

    changing archive log mode without shutting down database

    934646
      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
          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
            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
              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
                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
                  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
                    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
                      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