3 Replies Latest reply: May 5, 2007 11:59 AM by Paul M. RSS

    Alter database into Archivelog mode.

    535930
      HI Team,

      I am using ORACLE 9i.

      I want to convert database into archivelog mode from noarchivelog mode.

      Using the following SQL Commands I can able to convert my database into the Archivelog.

      SQL> connect sys as sysdba
      Enter password: *****************
      Connected.
      SQL> alter database archivelog;
      alter database archivelog
      *
      ERROR at line 1:
      ORA-01126: database must be mounted EXCLUSIVE and not open for this operation

      SQL> startup mount;
      ORA-01081: cannot start already-running ORACLE - shut it down first
      SQL> shutdown immediate;
      Database closed.
      Database dismounted.
      ORACLE instance shut down.
      SQL> startup mount;
      ORACLE instance started.

      Total System Global Area 252779340 bytes
      Fixed Size 453452 bytes
      Variable Size 125829120 bytes
      Database Buffers 125829120 bytes
      Redo Buffers 667648 bytes
      Database mounted.
      SQL> alter database archivelog;

      Database altered.


      My problem is that I had to shutdown the database before converting it in to the archivelog mode...

      Is there any way that without shuting down the database i can switch to archivelog mode.

      Please comments...

      Shatrughan
        • 1. Re: Alter database into Archivelog mode.
          Paul M.
          Is there any way that without shuting down the database i can switch to archivelog mode.
          No. The documentation says :

          ARCHIVELOG | NOARCHIVELOG

          Use the ARCHIVELOG clause and NOARCHIVELOG clause only if your instance has the database mounted but not open, with Real Application Clusters disabled.
          • 2. Re: Alter database into Archivelog mode.
            573303
            I wonder if we shutdown immediate one's database..do it's listener terminated too???
            • 3. Re: Alter database into Archivelog mode.
              Paul M.
              if we shutdown immediate one's database..do
              it's listener terminated too???
              The database registration with the listener yes, the listener itself no. That is, if you execute a command like "lsnrctl stat" you won't see the database anymore, but the listener is still active. Example :
              [ora102 ~ db102]$ lsnrctl stat

              LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 05-MAY-2007 18:57:51

              Copyright (c) 1991, 2005, Oracle.  All rights reserved.

              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
              STATUS of the LISTENER
              ------------------------
              Alias                     LISTENER
              Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
              Start Date                05-MAY-2007 18:27:34
              Uptime                    0 days 0 hr. 30 min. 16 sec
              Trace Level               off
              Security                  ON: Local OS Authentication
              SNMP                      OFF
              Listener Parameter File   /home/ora102/OraHome1/network/admin/listener.ora
              Listener Log File         /home/ora102/OraHome1/network/log/listener.log
              Listening Endpoints Summary...
                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
                (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
              Services Summary...
              Service "PLSExtProc" has 1 instance(s).
                Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
              Service "db102" has 1 instance(s).
              Instance "db102", status READY, has 2 handler(s) for this service...
              The command completed successfully
              [ora102 ~ db102]$ sqlplus / as sysdba

              SQL*Plus: Release 10.2.0.1.0 - Production on Sat May 5 18:58:03 2007

              Copyright (c) 1982, 2005, Oracle.  All rights reserved.


              Connected to:
              Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
              With the Partitioning, OLAP and Data Mining options

              SYS@db102 SQL> shutdown immediate
              Database closed.
              Database dismounted.
              ORACLE instance shut down.
              SYS@db102 SQL> exit
              Disconnected from Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
              With the Partitioning, OLAP and Data Mining options
              [ora102 ~ db102]$ lsnrctl stat

              LSNRCTL for Linux: Version 10.2.0.1.0 - Production on 05-MAY-2007 18:58:33

              Copyright (c) 1991, 2005, Oracle.  All rights reserved.

              Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521)))
              STATUS of the LISTENER
              ------------------------
              Alias                     LISTENER
              Version                   TNSLSNR for Linux: Version 10.2.0.1.0 - Production
              Start Date                05-MAY-2007 18:27:34
              Uptime                    0 days 0 hr. 30 min. 58 sec
              Trace Level               off
              Security                  ON: Local OS Authentication
              SNMP                      OFF
              Listener Parameter File   /home/ora102/OraHome1/network/admin/listener.ora
              Listener Log File         /home/ora102/OraHome1/network/log/listener.log
              Listening Endpoints Summary...
                (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=localhost)(PORT=1521)))
                (DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC0)))
              Services Summary...
              Service "PLSExtProc" has 1 instance(s).
                Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
              The command completed successfully
              [ora102 ~ db102]$