2 Replies Latest reply: Apr 16, 2012 9:19 PM by user11982706 RSS

    11g Upgrade Behavior

    user11982706
      OS Version : AIX6.1
      DB Version : 11.2.0.1 & 11.2.0.3

      Recently while trying to simulate a error, i came across a strange behavior. I am not sure if it happens in 9i / 10g also. Here is scenario:


      1) I have a test database say "core" with version 11.2.0.1 (ORACLE_HOME=/u01/app/oracore/product/11.2.0.1)

      2) I have installed the 11.2.0.3 version on same server. (ORACLE_HOME=/u01/app/oracore/product/11203)

      3) I have copied spfile & password from 11.2.0.1 home to 11203 home.

      4) Now i have shutdown "core" database from 11.2.0.1 home as
      SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 16 18:14:07 2012
      
      Copyright (c) 1982, 2009, Oracle.  All rights reserved.
      
      SQL> conn sys as sysdba
      Enter password:
      Connected.
      SQL> select name, open_mode from v$database;
      
      NAME      OPEN_MODE
      --------- --------------------
      CORE      READ WRITE
      
      SQL> shutdown immediate;
      Database closed.
      Database dismounted.
      ORACLE instance shut down.
      5) Now, i have started the "core" database from 11203 home and got error which is expected as
      SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 16 18:16:26 2012
      
      Copyright (c) 1982, 2011, Oracle.  All rights reserved.
      
      SQL> conn sys as sysdba
      Enter password:
      Connected to an idle instance.
      SQL> startup
      ORACLE instance started.
      
      Total System Global Area 4275781632 bytes
      Fixed Size                  2228344 bytes
      Variable Size             822087560 bytes
      Database Buffers         3439329280 bytes
      Redo Buffers               12136448 bytes
      Database mounted.
      ORA-01092: ORACLE instance terminated. Disconnection forced
      ORA-00704: bootstrap process failure
      ORA-39700: database must be opened with UPGRADE option
      Process ID: 51904546
      Session ID: 33 Serial number: 3
      6) now i started the "code" database in upgrade mode with 11203 home as
      SQL*Plus: Release 11.2.0.3.0 Production on Mon Apr 16 18:18:04 2012
      
      Copyright (c) 1982, 2011, Oracle.  All rights reserved.
      
      SQL> conn sys as sysdba
      Enter password:
      Connected to an idle instance.
      SQL> startup upgrade
      ORACLE instance started.
      
      Total System Global Area 4275781632 bytes
      Fixed Size                  2228344 bytes
      Variable Size             822087560 bytes
      Database Buffers         3439329280 bytes
      Redo Buffers               12136448 bytes
      Database mounted.
      Database opened.
      SQL>
      7) Now, i didn't ran the catupgrade script yet and created some user (i should get some error that db is in upgrade mode etc.) and shutdown the DB after that as
      SQL> create user test1 identified by test1;
      
      User created.
      
      SQL> grant connect,resource to test1;
      
      Grant succeeded.
      8) Now i have started the "core" database again with 11.2.0.1 home and expected to get some error while starting, but didn't get any error as
      SQL*Plus: Release 11.2.0.1.0 Production on Mon Apr 16 18:23:32 2012
      
      Copyright (c) 1982, 2009, Oracle.  All rights reserved.
      
      SQL> conn sys as sysdba
      Enter password:
      Connected to an idle instance.
      SQL> startup
      ORACLE instance started.
      
      Total System Global Area 4275781632 bytes
      Fixed Size                  2213632 bytes
      Variable Size             822085888 bytes
      Database Buffers         3439329280 bytes
      Redo Buffers               12152832 bytes
      Database mounted.
      Database opened.
      9) To my surprise, i am able to see the "TEST1" user in dba_users list.
      SQL> select username from dba_users;
      
      USERNAME
      ------------------------------
      OUTLN
      TEST1
      SYS
      SYSTEM
      ORACLE_OCM
      DIP
      APPQOSSYS
      DBSNMP
      
      8 rows selected.
      10) Also, i am able to connect from this user and create table as
      SQL> conn test1/test1
      Error accessing PRODUCT_USER_PROFILE
      Warning:  Product user profile information not loaded!
      You may need to run PUPBLD.SQL as SYSTEM
      Connected.
      SQL> show user
      USER is "TEST1"
      SQL> create table abc (name varchar2(10));
      
      Table created.
      
      SQL> insert into abc values ('test');
      
      1 row created.
      
      SQL> commit;
      
      Commit complete.
      Here is output of dba_registry from 11.2.0.1 version after all the above steps
      SQL> select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
      
      COMP_ID                        COMP_NAME                                          VERSION                        STATUS
      ------------------------------ -------------------------------------------------- ------------------------------ -----------
      CATALOG                        Oracle Database Catalog Views                      11.2.0.1.0                     VALID
      CATPROC                        Oracle Database Packages and Types                 11.2.0.1.0                     VALID
      My doubt is that, is it a normal behavior of all oracle versions ?
      When i opened DB with 11203 home and tried to create user, i should have got error........

      Please suggest, if i am missing something.