about archivelog and database status

From: Dino Hsu <dino1_at_ms1.hinet.net>
Date: Wed, 22 Aug 2001 00:44:15 +0800
Message-ID: <2i05otomqhaooh8a2oh0gmrm63a23lsjvn@4ax.com>

Dear all,

Quoting a book: (Jason Couchman, p.667)
"The following code block shows how to put the database into archivelog mode:
SQL> alter database dismount;
Database altered.
SQL> alter database archivelog;
Database altered.
SQL> alter database open;
Database altered."

I tried it, but:
SQL> alter database archivelog;
*

ORA-01507: database not mounted.

Also in Administrator's Guide, Chapter 3 Starting up and Shutting down:

<quote start>

Starting an Instance Without Mounting a Database You can start an instance without mounting a database. Typically, you do so only during database creation. Use the STARTUP command with the NOMOUNT option:

STARTUP NOMOUNT; Starting an Instance and Mounting a Database You can start an instance and mount a database without opening it, allowing you to perform specific maintenance operations. For example, the database must be mounted but not open during the following tasks:

Renaming datafiles, as described in Chapter 10, "Managing Datafiles".

Adding, dropping, or renaming redo log files, as described in Chapter 6, "Managing the Online Redo Log".

Enabling and disabling redo log archiving options, as described in Chapter 7, "Managing Archived Redo Logs".

Performing full database recovery. Database recovery is the topic of Oracle8i Backup and Recovery Guide and Oracle8i Recovery Manager User's Guide and Reference.

Start an instance and mount the database, but leave it closed by using the STARTUP command with the MOUNT option:

STARTUP MOUNT; <quote end>

In short, in 'nomount' status, we can only create a database, and nothing else can be done. While in 'mount' status, we can rename datafiles, add/drop/rename log files, alter archivelog mode, and perform recovery. Therefore, 'alter database dismount' is incorrect for changing archivelog mode.

Let's also look at the following commands:

<commands start>

SQL> alter database close;
alter database close
*

ERROR at line 1:
ORA-01093: ALTER DATABASE CLOSE only permitted with no sessions connected

SQL> select sid, serial#, username, osuser, status from v$session;

       SID SERIAL# USERNAME OSUSER STATUS

---------- ---------- ---------------- --------------- --------
         1          1                  SYSTEM          ACTIVE
         2          1                  SYSTEM          ACTIVE
         3          1                  SYSTEM          ACTIVE
         4          1                  SYSTEM          ACTIVE
         5          1                  SYSTEM          ACTIVE
         6          1                  SYSTEM          ACTIVE
         7         37                                  ACTIVE
         8         37                                  ACTIVE
         9         37                                  ACTIVE
        10         37                                  ACTIVE
        11          1 SYS              DINO-C2\DINO    ACTIVE

SQL> alter database dismount;
alter database dismount
*

ERROR at line 1:
ORA-01106: database must be closed before dismounting

SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> startup mount pfile=c:\ora81\network\admin\initorcl.ora ORACLE instance started.

Total System Global Area 83658780 bytes

Fixed Size                    75804 bytes
Variable Size              56414208 bytes
Database Buffers           27090944 bytes
Redo Buffers                  77824 bytes

Database mounted.
SQL> alter database archivelog;

Database altered.

SQL> alter database noarchivelog;

Database altered.

SQL> alter database dismount;

Database altered.

SQL> alter database open;
alter database open
*

ERROR at line 1:
ORA-01507: database not mounted

SQL> alter database mount;

Database altered.

SQL> alter database open;

Database altered.

SQL> <commands end>

I found the following points:
1.'alter database close' is used to go from 'open' to 'mount' status, but there are some unknown sessions existing that prevents us from doing this. We cannot go from 'open' to 'nomount', either. Therefore, we can only 'shutdown immediate' and then 'startup mount'. 2.We can go from 'shutdown' to either one of 'nomount', 'mount', or 'open' with no problem; we can also go from 'mount' to either 'open' or 'nomount'.
3.We cannot go from 'nomount' directly to 'open', we have to go to 'mount' first and then 'open'.
4.'alter database archivelog/noarchivelog' can be performed only when the database is 'mount', not when 'nomount' or 'open'.

Anyone confirms all my statements are correct? Thanks in advance.

Dino Received on Tue Aug 21 2001 - 11:44:15 CDT