Upgrade Oracle 10g to 11g Journal

Planning:

Backing up Enterprise Manager Database Control Data
Catalog DB



Complete Checklist to Upgrade the Database to 11gR2 using DBUA [ID 870814.1]
https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=481180426703885&id=870814.1&_afrWindowMode=0&_adf.ctrl-state=mqfm1zxvp_311

How to Download and Run Oracle's Database Pre-Upgrade Utility [ID 884522.1]
https://support.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?_afrLoop=481752990964660&id=884522.1&_afrWindowMode=0&_adf.ctrl-state=mqfm1zxvp_324

Updating the RDBMS DST version in 11g Release 2 (11.2.0.1 and up) using DBMS_DST [ID 977512.1]
https://support.oracle.com/epmos/faces/ui/km/DocumentDisplay.jspx?_afrLoop=550068721629323&id=977512.1&_afrWindowMode=0&_adf.ctrl-state=136vgjhsg3_90

Check for INVALID database components and objects in the Source database
SQL> set pagesize500
       set linesize 100
      
       select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from
       dba_registry order by comp_name;
      
       select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from
       dba_objects where status='INVALID' order by owner,object_type;
      
       select owner,object_type,count(*) from dba_objects where status='INVALID' group by
       owner,object_type order by owner,object_type ;


$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql


Remove Public SYNONYM XMLCONCAT
sqlplus / as sysdba
SQL> select SYNONYM_NAME,OWNER from all_synonyms where SYNONYM_NAME = 'XMLCONCAT';

SQL> drop public synonym XMLCONCAT;

Synonym dropped.

Optimizer Statistics
EXECUTE dbms_stats.gather_dictionary_stats;
SQL> SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2
      WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE'  2  ;

no rows selected

SQL> SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2
      WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE'  2  ;

no rows selected

To decrease the amount of downtime incurred when collecting statistics, you can collect statistics prior to performing the actual database upgrade. As of Oracle Database 10g Release 1 (10.1), Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, you can enter the following:
$ sqlplus "/as sysdba"
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;


Post Upgrade

mkdir -p /usr2/app/oracle/product/11.2.0.3/admin/DBINSTANCE/adump

mkdir -p $ORACLE_HOME/admin/DBINSTANCE/cdump

SQL> show parameter core_dump_dest
SQL> alter system set core_dump_dest='/usr2/app/oracle/product/11.2.0.3/admin/DBINSTANCE/cdump' scope=both;
SQL> show parameter audit
SQL> alter system set audit_file_dest='/usr2/app/oracle/product/11.2.0.3/admin/DBINSTANCE/adump' scope=spfile;

SQL> ALTER SYSTEM SET COMPATIBLE = '11.2.0' SCOPE=spfile;

System altered.

SQL> shutdown immediate

show parameter DB_RECOVERY_FILE_DEST

alter system set db_recovery_file_dest='/usr2/app/oracle/product/11.2.0.3/flash_recovery_area/DBINSTANCE' scope=both;

check current RDBMS DST version and "DST UPGRADE STATUS" in your 11.2.0.x database.
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

Check UPFRONT using DBMS_DST if there is affected data that cannot be resolved automatically in your 11.2.0.x database.

$ cd $ORACLE_HOME/dbs

$ orapwd file=orapwDBINSTANCE entries=10

SQL> SELECT LOG_MODE FROM SYS.V$DATABASE;

LOG_MODE
------------
NOARCHIVELOG

QL> shutdown immediate
ORA-01109: database not open


Database dismounted.
ORACLE instance shut down.
SQL> startup mount
ORACLE instance started.

Total System Global Area 1820540928 bytes
Fixed Size                  2229304 bytes
Variable Size             452987848 bytes
Database Buffers         1358954496 bytes
Redo Buffers                6369280 bytes
Database mounted.
SQL> alter database archivelog;
Database altered.

SQL> alter database open;
Database altered.













Configure emca

Drop existing EM repository

emca -deconfig dbcontrol db -repos drop

if doesn't work,

Manually drop EM Repository
exec DBMS_AQADM.DROP_QUEUE_TABLE(queue_table=>'SYSMAN.MGMT_NOTIFY_QTABLE',force=>TRUE);

SHUTDOWN IMMEDIATE;

STARTUP RESTRICT;
EXEC sysman.emd_maintenance.remove_em_dbms_jobs;
EXEC sysman.setEMUserContext('',5);
REVOKE dba FROM sysman;


DECLARE
CURSOR c1 IS
 SELECT owner, synonym_name name FROM dba_synonyms
 WHERE table_owner = 'SYSMAN';

BEGIN
FOR r1 IN c1
LOOP IF r1.owner = 'PUBLIC' THEN
EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM '||r1.name;
ELSE
EXECUTE IMMEDIATE 'DROP SYNONYM '||r1.owner||'.'||r1.name;
 END IF;
 END LOOP;
END;
/
DROP ROLE mgmt_user;
DROP user mgmt_view cascade;
DROP user sysman cascade;
ALTER SYSTEM DISABLE RESTRICTED SESSION;


select username, account_status, created from dba_users where username in ('SYSMAN');

emca -config dbcontrol db -repos create -silent -respFile /usr2/app/oracle/product/11.2.0.3/admin/DBINSTANCE/emca.rsp

$ cat /usr2/app/oracle/product/11.2.0.3/admin/DBINSTANCE/emca.rsp
        ORACLE_HOSTNAME: dbserver.goweekend.ca
        SID: DBINSTANCE
        PORT: 1521
        ORACLE_HOME: /usr2/app/oracle/product/11.2.0.3
        DBSNMP_PWD: <password>
        SYSMAN_PWD: <password>
        SYS_PWD: <password>

or

emca -config dbcontrol db -repos create