Search This Blog

Total Pageviews

Friday 27 January 2017

How to Convert Database from Standard to Enterprise Edition



Oracle Standard Edition to the Enterprise Edition ...



Following are the steps required to upgrade from Standard Edition database to Enterprise Edition:


#oraeed:/u01/app/oracle/product/11.2.0/dbhome_3:N  Old Home for Standard Edition

oraeed:/u01/app/oracle/product/11.2.4/db_1  new home for Enterprise Edition


steps to upgrade a Standard Edition database to Enterprise Edition on 11gr2:

1. Install new Home for Enterprise Edition 

2. Take database  backup  !!!!!!!!!!!!!!!!!!!!

3. copy spfile from old home to new home 

 cd /u01/app/oracle/product/11.2.0/dbhome_3/dbs/
 ls -ltr
 cp spfileoraeed.ora  /u01/app/oracle/product/11.2.4/db_1/dbs/


edit this file /etc/oratab

from 
#oraeed:/u01/app/oracle/product/11.2.0/dbhome_3:N
to 
oraeed:/u01/app/oracle/product/11.2.4/db_1

. oraenv for new home

4) Startup database with new home

sqlplu> startup upgrade ;

5. 
sqlplus>
spool /tmp/upgrade.log
@?/rdbms/admin/catupgrd.sql


@?/rdbms/admin/catproc.sql

check invalid objects 

SQL> SELECT count(*) FROM dba_invalid_objects;

  COUNT(*)
----------
       420


SQL> @?/rdbms/admin/utlrp.sql



PL/SQL procedure successfully completed.


Function dropped.


PL/SQL procedure successfully completed.

SQL> SELECT count(*) FROM dba_invalid_objects;

  COUNT(*)
----------
         0

SELECT distinct object_name FROM dba_invalid_objects;


SQL>  select version from v$timezone_file;

   VERSION
----------
        11

SQL> select * from v$version;

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
PL/SQL Release 11.2.0.4.0 - Production
CORE    11.2.0.4.0      Production
TNS for Linux: Version 11.2.0.4.0 - Production
NLSRTL Version 11.2.0.4.0 - Production

  
set linesize 200
COL PRODUCT FORMAT A45
COL VERSION FORMAT A15
COL STATUS FORMAT A25 
SELECT * FROM PRODUCT_COMPONENT_VERSION ;

PRODUCT                                       VERSION         STATUS
--------------------------------------------- --------------- -------------------
NLSRTL                                        11.2.0.4.0      Production
Oracle Database 11g Enterprise Edition        11.2.0.4.0      64bit Production
PL/SQL                                        11.2.0.4.0      Production
TNS for Linux:                                11.2.0.4.0      Production


SQL> SQL> SQL> def
DEFINE _DATE           = "27-JAN-17" (CHAR)
DEFINE _CONNECT_IDENTIFIER = "oraeed" (CHAR)
DEFINE _USER           = "SYS" (CHAR)
DEFINE _PRIVILEGE      = "AS SYSDBA" (CHAR)
DEFINE _SQLPLUS_RELEASE = "1102000400" (CHAR)
DEFINE _EDITOR         = "ed" (CHAR)
DEFINE _O_VERSION      = "Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production  !!!!!!!!!!!!!!!!



If required .. 

SQL>  exec DBMS_REGISTRY_SYS.VALIDATE_CATPROC;

PL/SQL procedure successfully completed.




PRODUCT                                       VERSION         STATUS
--------------------------------------------- --------------- -------------------------
NLSRTL                                        11.2.0.4.0      Production
Oracle Database 11g Enterprise Edition        11.2.0.4.0      64bit Production
PL/SQL                                        11.2.0.4.0      Production
TNS for Linux:                                11.2.0.4.0      Production


set pagesize 200
SELECT comp_id, version, status FROM dba_registry

COMP_ID                        VERSION         STATUS
------------------------------ --------------- -------------------------
OWB                            11.2.0.1.0      VALID
APEX                           3.2.1.00.10     VALID
EM                             11.2.0.4.0      VALID
AMD                            11.2.0.4.0      VALID
SDO                            11.2.0.4.0      VALID
ORDIM                          11.2.0.4.0      VALID
XDB                            11.2.0.4.0      VALID
CONTEXT                        11.2.0.4.0      VALID
EXF                            11.2.0.4.0      VALID
RUL                            11.2.0.4.0      VALID
OWM                            11.2.0.4.0      VALID
CATALOG                        11.2.0.4.0      VALID
CATPROC                        11.2.0.4.0      VALID
JAVAVM                         11.2.0.4.0      VALID
XML                            11.2.0.4.0      VALID
CATJAVA                        11.2.0.4.0      VALID
APS                            11.2.0.4.0      VALID
XOQ                            11.2.0.4.0      VALID

18 rows selected.

Oracle DBA

anuj blog Archive