This discussion is archived
2 Replies Latest reply: Apr 16, 2012 7:19 PM by user11982706 RSS

11g Upgrade Behavior

user11982706 Newbie
Currently Being Moderated
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.

Legend

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