Upgrade Database to 12C
I. Pre Upgrade activities
1. Check the upgrade Path : For most of the cases below 11gr2, there is no direct path available to upgrade to 12C. And it will be two hop upgrade.
2. Take the backup of the source system. Perform Cold/Hot Backup
sqlplus / as sysdbashut immediate
exit
cd /data1/oracle12c/oradata/orcl12c1
cp -rp orcl12c1 orcl12c1_bkp
sqlplus / as sysdba
startup
3. Ensure there is no INVALID and Duplicates in SYS/SYSTEM
select object_name from dba_objects where status !='VALID' and owner like '%SYS%';
no rows selected
no rows selected
Ensure that you do not have duplicate objects in the SYS and SYSTEM schema
set lines 200
col object_name for a30
col owner for a20
select object_name,owner, status from dba_objects where owner='SYS' and object_name in (select object_name from dba_objects where owner='SYSTEM');
select object_name,owner, status,object_type from dba_objects where object_name in (select object_name from dba_objects where owner='SYS' and object_name in (select object_name from dba_objects where owner='SYSTEM'));
col object_name for a30
col owner for a20
select object_name,owner, status from dba_objects where owner='SYS' and object_name in (select object_name from dba_objects where owner='SYSTEM');
select object_name,owner, status,object_type from dba_objects where object_name in (select object_name from dba_objects where owner='SYS' and object_name in (select object_name from dba_objects where owner='SYSTEM'));
The following duplicate objects are permissible duplicate objects:
OBJECT_NAME OBJECT_TYPE
--------------------------- ------------------
AQ$_SCHEDULES TABLE
AQ$_SCHEDULES_PRIMARY INDEX
DBMS_REPCAT_AUTH PACKAGE
DBMS_REPCAT_AUTH PACKAGE BODY
4.Check for the integrity of the source database. Run dbupgdiag.sql
Download dbupgdiag.sql from Oracle Note 556610.1.Copy the file dbupgdiag.sql to source server /tmp
[oracle@TSTSRV ~]$ ls -lrt /tmp/dbupgdiag.sql
-rw-r--r-- 1 oracle dba 24140 Apr 7 14:40 /tmp/dbupgdiag.sql
[oracle@TSTSRV ~]$
[oracle@TSTSRV tmp]$ sqlplus / as sysdba
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORCL12C1 READ WRITE
SQL> alter session set nls_language='American';
SQL> @dbupgdiag.sql
-rw-r--r-- 1 oracle dba 24140 Apr 7 14:40 /tmp/dbupgdiag.sql
[oracle@TSTSRV ~]$
[oracle@TSTSRV tmp]$ sqlplus / as sysdba
SQL> select name, open_mode from v$database;
NAME OPEN_MODE
--------- --------------------
ORCL12C1 READ WRITE
SQL> alter session set nls_language='American';
SQL> @dbupgdiag.sql
Enter location for Spooled output:
Enter value for 1: /tmp
*** End of LogFile ***
Upload db_upg_diag_orcl12c1_07_Apr_2015_0424.log from "/tmp" directory
================================Observation: Look for the messages,
A. If CLUSTER_DATABASE is set to TRUE, change it to FALSE before
B. Component Status - All should be valid or else check with Oracle
Comp ID Component Status Version Org_Version Prv_Version
------- ---------------------------------- --------- -------------- -------------- --------------
AMD OLAP Catalog VALID 11.2.0.3.0
APEX Oracle Application Express VALID 3.2.1.00.12
APS OLAP Analytic Workspace VALID 11.2.0.3.0
CATALOG Oracle Database Catalog Views VALID 11.2.0.3.0
CATJAVA Oracle Database Java Packages VALID 11.2.0.3.0
CATPROC Oracle Database Packages and Types VALID 11.2.0.3.0
CONTEXT Oracle Text VALID 11.2.0.3.0
EM Oracle Enterprise Manager VALID 11.2.0.3.0
EXF Oracle Expression Filter VALID 11.2.0.3.0
JAVAVM JServer JAVA Virtual Machine VALID 11.2.0.3.0
ORDIM Oracle Multimedia VALID 11.2.0.3.0
OWB OWB VALID 11.2.0.3.0
OWM Oracle Workspace Manager VALID 11.2.0.3.0
RUL Oracle Rules Manager VALID 11.2.0.3.0
SDO Spatial VALID 11.2.0.3.0
XDB Oracle XML Database VALID 11.2.0.3.0
XML Oracle XDK VALID 11.2.0.3.0
XOQ Oracle OLAP API VALID 11.2.0.3.0
C. List of Invalid Database Objects Owned by SYS / SYSTEM
D. Duplicate Objects Owned by SYS and SYSTEM
5. Run utlrp.sql to make objects VALID
cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba"
@utlrp.sql
select count(*) from dba_objects where status !='VALID';
COUNT(*)
----------
0
sqlplus "/ as sysdba"
@utlrp.sql
select count(*) from dba_objects where status !='VALID';
COUNT(*)
----------
0
6. Run the preupgrade script preupgrd.sql
Install 12C RDBMS in a seperate location.go to the RDBMS location in 12c
cd $ORACLE_HOME/rdbms/admin
cp preupgrd.sql /tmp
cp utluppkg.sql /tmp
Copy the Pre-Upgrade Information Tool script preupgrd.sql and utluppkg.sql from the Oracle Database 12c Release 1 (12.1) cp preupgrd.sql /tmp
cp utluppkg.sql /tmp
$ORACLE_HOME/rdbms/admin directory to the $ORACLE_HOME/rdbms/admin directory of the source Oracle Home.
export ORACLE_SID=orcl12c1
export ORACLE_HOME=/data1/aacg/db11203/product/11.2.0
export PATH=/data1/aacg/db11203/product/11.2.0/bin:$PATH
cd /tmp
[oracle@TSTSRV tmp]$ sqlplus / as sysdba
SQL> @preupgrd.sql
====>> PRE-UPGRADE RESULTS for ORCL12C1 <<====export ORACLE_HOME=/data1/aacg/db11203/product/11.2.0
export PATH=/data1/aacg/db11203/product/11.2.0/bin:$PATH
cd /tmp
[oracle@TSTSRV tmp]$ sqlplus / as sysdba
SQL> @preupgrd.sql
ACTIONS REQUIRED:
1. Review results of the pre-upgrade checks:
/data1/aacg/db11203/product/11.2.0/cfgtoollogs/orcl12c1/preupgrade/preupgrade.log
2. Execute in the SOURCE environment BEFORE upgrade:
/data1/aacg/db11203/product/11.2.0/cfgtoollogs/orcl12c1/preupgrade/preupgrade_fixups.sql
3. Execute in the NEW environment AFTER upgrade:
/data1/aacg/db11203/product/11.2.0/cfgtoollogs/orcl12c1/preupgrade/postupgrade_fixups.sql
************************************************************
***************************************************************************
Pre-Upgrade Checks in ORCL12C1 Completed.
Observation:
1. ERROR: --> TEMP tablespace is not large enough for the upgrade.
select * from V$SORT_SEGMENT;
alter tablespace TEMP add tempfile '/data1/oracle12c/oradata/orcl12c1/temp01.dbf' size 500M;
Tablespace altered.
2. Please gather dictionary statistics 24 hours prior to upgrading the database.
To gather dictionary statistics execute the following command while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
^^^ MANUAL ACTION SUGGESTED ^^^
EXECUTE dbms_stats.gather_dictionary_stats;
PL/SQL procedure successfully completed.
@/data1/aacg/db11203/product/11.2.0/cfgtoollogs/orcl12c1/preupgrade/preupgrade_fixups.sql
SQL> @/data1/aacg/db11203/product/11.2.0/cfgtoollogs/orcl12c1/preupgrade/preupgrade_fixups.sql
Pre-Upgrade Fixup Script Generated on 2015-04-07 18:24:51 Version: 12.1.0.2 Build: 006
Beginning Pre-Upgrade Fixups...
Executing in container ORCL12C1
**********************************************************************
7. Deprecated CONNECT Role
Please ensure any deprecated roles are updated.8. Update Access Control Lists and Network Utility Packages
Check the preupgrade logs for any dependency on ACL/data1/aacg/db11203/product/11.2.0/cfgtoollogs/orcl12c1/preupgrade/preupgrade.log
9.Dependencies on Network Utility Packages
This will be used Post upgrade
col OWNER for a15
col name for a25
col TYPE for a20
col REFERENCED_OWNER for a15
col REFERENCED_LINK_NAME for a2
col REFERENCED_TYPE for a10
col DEPENDENCY_TYPE for a4
SELECT * FROM DBA_DEPENDENCIES
WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR','DBMS_LDAP') AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');
col name for a25
col TYPE for a20
col REFERENCED_OWNER for a15
col REFERENCED_LINK_NAME for a2
col REFERENCED_TYPE for a10
col DEPENDENCY_TYPE for a4
SELECT * FROM DBA_DEPENDENCIES
WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_INADDR','DBMS_LDAP') AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS');
OWNER NAME TYPE REFERENCED_OWNE REFERENCED_NAME REFERENCED RE DEPE
--------------- ------------------------- -------------------- --------------- -------------------- ---------- -- ----
MDSYS SDO_OLS PACKAGE BODY PUBLIC UTL_HTTP SYNONYM HARD
APEX_030200 WWV_FLOW_HELP PACKAGE BODY APEX_030200 UTL_HTTP SYNONYM HARD
APEX_030200 WWV_FLOW_MAIL PACKAGE BODY APEX_030200 UTL_SMTP SYNONYM HARD
APEX_030200 WWV_FLOW_PRINT_UTIL PACKAGE BODY APEX_030200 UTL_HTTP SYNONYM HARD
APEX_030200 WWV_FLOW_LDAP PACKAGE PUBLIC DBMS_LDAP SYNONYM HARD
APEX_030200 WWV_FLOW_LDAP PACKAGE BODY PUBLIC DBMS_LDAP SYNONYM HARD
APEX_030200 WWV_FLOW_WEB_SERVICES PACKAGE BODY APEX_030200 UTL_HTTP SYNONYM HARD
APEX_030200 UTL_HTTP SYNONYM SYS UTL_HTTP PACKAGE HARD
APEX_030200 UTL_SMTP SYNONYM SYS UTL_SMTP PACKAGE HARD
APEX_030200 WWV_FLOW_CUSTOM_AUTH_LDAP PACKAGE BODY PUBLIC DBMS_LDAP SYNONYM HARD
APEX_030200 WWV_FLOW_DISP_PAGE_PLUGS PACKAGE BODY APEX_030200 UTL_HTTP SYNONYM HARD
11 rows selected.
10. Check for any encryption related to database link passwords. In some cases, it requiers to downgrade.
SELECT 'CREATE '||DECODE(U.NAME,'PUBLIC','public ')||'DATABASE LINK '||CHR(10) ||DECODE(U.NAME,'PUBLIC',Null, 'SYS','',U.NAME||'.')|| L.NAME||chr(10) ||'CONNECT TO ' || L.USERID || ' IDENTIFIED BY "'||L.PASSWORD||'" USING '''||L.HOST||'''' ||chr(10)||';' TEXT FROM SYS.LINK$ L, SYS.USER$ U WHERE L.OWNER# = U.USER#;
no rows selected
no rows selected
11. Updating the RDBMS DST version in 12c Release
12. Optimizer Statistics
$ sqlplus "/as sysdba"
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
13. Save Database Control Files and Data with the emdwgrd Utility
This step may fail, if you do not have SYS password. if you do not have the SYS password, create the password file.
[oracle@TSTSRV dbs]$ orapwd file=orapworcl12c1 password=Welcome123 entries=5 force=y
export ORACLE_SID=orcl12c1
export ORACLE_HOME=/data1/aacg/db11203/product/11.2.0
export PATH=/data1/aacg/db11203/product/11.2.0/bin:$PATH
LD_LIBRARY_PATH=/data1/aacg/db11203/product/11.2.0/lib
SHLIB_PATH=/data1/aacg/db11203/product/11.2.0/lib
cd /data1/oracle12c/12c/ ####------ 12C RDBMS HOME
emdwgrd -save -sid orcl12c1 -path /data1/oracle12c/12c/emdwgrd_11203
export ORACLE_SID=orcl12c1
export ORACLE_HOME=/data1/aacg/db11203/product/11.2.0
export PATH=/data1/aacg/db11203/product/11.2.0/bin:$PATH
LD_LIBRARY_PATH=/data1/aacg/db11203/product/11.2.0/lib
SHLIB_PATH=/data1/aacg/db11203/product/11.2.0/lib
cd /data1/oracle12c/12c/ ####------ 12C RDBMS HOME
emdwgrd -save -sid orcl12c1 -path /data1/oracle12c/12c/emdwgrd_11203
[oracle@TSTSRV 12c]$ emdwgrd -save -sid orcl12c1 -path /data1/oracle12c/12c/emdwgrd_11203
Enter sys password for database orcl12c1?
DBI connect('orcl12c1','sys',...) failed: ORA-12154: TNS:could not resolve the connect identifier specified (DBD ERROR: OCIServerAttach) at /data1/aacg/db11203/product/11.2.0/bin/emdwgrd.pl line 1334
Tue Apr 7 19:14:24 2015 - Connect failed
----Check your listener if it has any services running for your database.
----Check your tnsnames.ora if it has right entry for database
[oracle@TSTSRV 12c]$ emdwgrd -save -sid orcl12c1 -path /data1/oracle12c/12c/emdwgrd_11203
Enter sys password for database orcl12c1?
DBI connect('orcl12c1','sys',...) failed: ORA-01031: insufficient privileges (DBD ERROR: OCISessionBegin) at /data1/aacg/db11203/product/11.2.0/bin/emdwgrd.pl line 1334
Tue Apr 7 19:26:42 2015 - Connect failed
-----Check if your password file in the ORACLE_HOME/dbs is correct
[oracle@TSTSRV 12c]$ emdwgrd -save -sid orcl12c1 -path /data1/oracle12c/12c/emdwgrd_11203
Enter sys password for database orcl12c1?
Database Unique Name : orcl12c1
Tue Apr 7 19:30:19 2015 - Validating DB Connection to orcl12c1 ... pass
Tue Apr 7 19:30:19 2015 - Verify EM DB Control files ... cannot find emd.properties ... failed
Error: DB Control location not found.
[oracle@TSTSRV 12c]$
14.Verify That Materialized View Refreshes Have Completed Before Upgrading
select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ s
where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;
no rows selected
no rows selected
15. Ensure That No Files Need Media Recovery Before Upgrading
SELECT * FROM v$recover_file;
no rows selected
no rows selected
16. Ensure That No Files Are in Backup Mode Before Upgrading
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
no rows selected
no rows selected
17. Resolve Outstanding Distributed Transactions Before Upgrading
SQL> SELECT * FROM dba_2pc_pending;
no rows selected
no rows selected
18. Purge the Database Recycle Bin Before Upgrading
SQL> PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.
DBA Recyclebin purged.
19. Synchronize the Standby Database with the Primary Database When Upgrading
20. Verify SYS and SYSTEM Default tablespace
SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM');
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYS SYSTEM
SYSTEM SYSTEM
USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYS SYSTEM
SYSTEM SYSTEM
21. Check whether database has any externally authenticated SSL users
SELECT name FROM sys.user$
WHERE ext_username IS NOT NULL
AND password = 'GLOBAL';
no rows selected
no rows selected
22. Location of datafiles, redo logs and control files
SELECT name FROM v$controlfile;
NAME
--------------------------------------------------------------------------------
/data1/oracle12c/oradata/orcl12c1/control01.ctl
/data1/oracle12c/oradata/orcl12c1/control02.ctl
SELECT file_name FROM dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/data1/oracle12c/oradata/orcl12c1/users01.dbf
/data1/oracle12c/oradata/orcl12c1/undotbs01.dbf
/data1/oracle12c/oradata/orcl12c1/sysaux01.dbf
/data1/oracle12c/oradata/orcl12c1/system01.dbf
SELECT group#, member FROM v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------------------------------------
3 /data1/oracle12c/oradata/orcl12c1/redo03.log
2 /data1/oracle12c/oradata/orcl12c1/redo02.log
1 /data1/oracle12c/oradata/orcl12c1/redo01.log
NAME
--------------------------------------------------------------------------------
/data1/oracle12c/oradata/orcl12c1/control01.ctl
/data1/oracle12c/oradata/orcl12c1/control02.ctl
SELECT file_name FROM dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
/data1/oracle12c/oradata/orcl12c1/users01.dbf
/data1/oracle12c/oradata/orcl12c1/undotbs01.dbf
/data1/oracle12c/oradata/orcl12c1/sysaux01.dbf
/data1/oracle12c/oradata/orcl12c1/system01.dbf
SELECT group#, member FROM v$logfile;
GROUP# MEMBER
---------- --------------------------------------------------------------------------------
3 /data1/oracle12c/oradata/orcl12c1/redo03.log
2 /data1/oracle12c/oradata/orcl12c1/redo02.log
1 /data1/oracle12c/oradata/orcl12c1/redo01.log
23.Remove Enterprise Manager Database Control repository
Enterprise Manager Database Control is superseded in 12c by Oracle Enterprise Manager Express.Therefore no repository is needed anymore.
Remove Enterprise Manager Database Control repository MANUALLY using the following command.
Note : You will get the emremove.sql script in the Oracle 12c home .
Copy the emremove.sql script from the 12c $ORACLE_HOME/rdbms/admin to the source $ORACLE_HOME/rdbms/admin and then execute on the source database prior to upgrade.
[oracle@TSTSRV config]$ cd /data1/oracle12c/12c/rdbms/admin
[oracle@TSTSRV admin]$ cp emremove.sql $ORACLE_HOME/rdbms/admin
[oracle@TSTSRV admin]$ cd $ORACLE_HOME/rdbms/admin
SQL> @ ?/rdbms/admin/emremove.sql
old 69: IF (upper('&LOGGING') = 'VERBOSE')
new 69: IF (upper('VERBOSE') = 'VERBOSE')
PL/SQL procedure successfully completed.
[oracle@TSTSRV admin]$ cp emremove.sql $ORACLE_HOME/rdbms/admin
[oracle@TSTSRV admin]$ cd $ORACLE_HOME/rdbms/admin
SQL> @ ?/rdbms/admin/emremove.sql
old 69: IF (upper('&LOGGING') = 'VERBOSE')
new 69: IF (upper('VERBOSE') = 'VERBOSE')
PL/SQL procedure successfully completed.
24. Run olspreupgrade.sql - this is applicable only if you have data vault or OLS
Not required, as you do not have DB vault
25.Review and Remove any unnecessary hidden/underscore parameters
SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;
no rows selected
no rows selected
II. Upgrade activities
26. Prepare 12C Home
Copy following configuration files from the $ORACLE_HOME of the database being upgraded to the new Oracle Home for Oracle Database 12c - Parameter file (spfile or pfile)
- Password file (orapwsid)
- Sqlnet.orac/listener.ora/tnsnames.ora/ldap.ora
27. Remove any deprecated parameters from Pfile/spfile
The SEC_CASE_SENSITIVE_LOGON parameter is deprecated in 12.1The DIAGNOSTIC_DEST initialization parameter replaces the USER_DUMP_DEST, BACKGROUND_DUMP_DEST
If you are using a cluster database, set the parameter CLUSTER_DATABASE=FALSE
If you are upgrading from 10.2.0.5 then make sure that the COMPATIBLE initialization parameter is set to at least 11.0.0
*.diagnostic_dest='/data1/oracle12c/12c'
*.audit_file_dest='/data1/oracle12c/12c/admin/orcl12c1/adump'
orcl12c1.__oracle_base='/data1/oracle12c'#ORACLE_BASE set from environment
28. Stop the listener for the database and Create a new listener for Oracle Database 12c Release 1 (12.1) database
export ORACLE_SID=orcl12c1
export ORACLE_HOME=/data1/oracle12c/12c
export PATH=/data1/oracle12c/12c/bin:$PATH
export LD_LIBRARY_PATH=/data1/oracle12c/12c/lib
export TNS_ADMIN=/data1/oracle12c/12c/network/admin
SHLIB_PATH=/data1/oracle12c/12c/lib
export ORACLE_HOME=/data1/oracle12c/12c
export PATH=/data1/oracle12c/12c/bin:$PATH
export LD_LIBRARY_PATH=/data1/oracle12c/12c/lib
export TNS_ADMIN=/data1/oracle12c/12c/network/admin
SHLIB_PATH=/data1/oracle12c/12c/lib
29. Shutdown the database running from OLD HOME.
SQL> shut immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL>
30. Make sure the following environment variables point to the Oracle 12c Release 1 (12.1) directories
export ORACLE_BASE=/data1/oracle12c/
export ORACLE_SID=orcl12c1
export ORACLE_HOME=/data1/oracle12c/12c
export PATH=/data1/oracle12c/12c/bin:$PATH
export LD_LIBRARY_PATH=/data1/oracle12c/12c/lib
export TNS_ADMIN=/data1/oracle12c/12c/network/admin
SHLIB_PATH=/data1/oracle12c/12c/lib
export ORACLE_SID=orcl12c1
export ORACLE_HOME=/data1/oracle12c/12c
export PATH=/data1/oracle12c/12c/bin:$PATH
export LD_LIBRARY_PATH=/data1/oracle12c/12c/lib
export TNS_ADMIN=/data1/oracle12c/12c/network/admin
SHLIB_PATH=/data1/oracle12c/12c/lib
31. Update oratab
orcl12c1:/data1/oracle12c/12c:N32. Upgrading Database to 12cR1
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> startup UPGRADE
SQL> exit
SQL> startup UPGRADE
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
SQL> !mkdir -p /data1/oracle12c/12c/admin/orcl12c1/adump
SQL> startup UPGRADE
ORACLE instance started.
Total System Global Area 3774873600 bytes
Fixed Size 3717568 bytes
Variable Size 1962935872 bytes
Database Buffers 1795162112 bytes
Redo Buffers 13058048 bytes
Database mounted.
Database opened.
SQL>
SQL> exit
Run the catctl.pl script from the new Oracle home for upgrading the DB
Example: Where parallelism is 6 ( n=6)
cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl -n 2 -l $ORACLE_HOME/diagnostics catupgrd.sql
$ sqlplus "/ as sysdba"
SQL> startup UPGRADE
SQL> exit
SQL> startup UPGRADE
ORA-09925: Unable to create audit trail file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9925
SQL> !mkdir -p /data1/oracle12c/12c/admin/orcl12c1/adump
SQL> startup UPGRADE
ORACLE instance started.
Total System Global Area 3774873600 bytes
Fixed Size 3717568 bytes
Variable Size 1962935872 bytes
Database Buffers 1795162112 bytes
Redo Buffers 13058048 bytes
Database mounted.
Database opened.
SQL>
SQL> exit
Run the catctl.pl script from the new Oracle home for upgrading the DB
Example: Where parallelism is 6 ( n=6)
cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl -n 2 -l $ORACLE_HOME/diagnostics catupgrd.sql
III. Post Upgrade activities
33. Review the upgrade logs
[oracle@TSTSRV ~]$ cd /data1/oracle12c/12c/cfgtoollogs/orcl12c1/upgrade/vi upg_summary.log
Oracle Database 12.1 Post-Upgrade Status Tool 04-07-2015 21:53:51
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
Oracle Server UPGRADED 12.1.0.2.0 00:21:57
JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:04:10
Oracle Workspace Manager VALID 12.1.0.2.0 00:01:06
OLAP Analytic Workspace VALID 12.1.0.2.0 00:00:28
OLAP Catalog OPTION OFF 11.2.0.3.0 00:00:00
Oracle OLAP API VALID 12.1.0.2.0 00:00:36
Oracle XDK VALID 12.1.0.2.0 00:01:01
Oracle Text VALID 12.1.0.2.0 00:01:10
Oracle XML Database VALID 12.1.0.2.0 00:07:29
Oracle Database Java Packages VALID 12.1.0.2.0 00:00:16
Oracle Multimedia VALID 12.1.0.2.0 00:02:46
Spatial UPGRADED 12.1.0.2.0 00:12:49
Oracle Application Express VALID 4.2.5.00.08 00:27:50
Final Actions 00:02:04
Post Upgrade 00:02:08
Total Upgrade Time: 01:26:44
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.21
Grand Total Upgrade Time: [0d:1h:29m:58s]
34. start the DB and perform the validations
SQL> startup mount;
ORACLE instance started.
Total System Global Area 3774873600 bytes
Fixed Size 3717568 bytes
Variable Size 1962935872 bytes
Database Buffers 1795162112 bytes
Redo Buffers 13058048 bytes
Database mounted.
SQL>
SQL> alter database open;
Database altered.
SQL>
select COMP_NAME,VERSION,STATUS from dba_registry
COMP_NAME VERSION STATUS
---------------------------------------------------------------------- ------------------------------ -----------
Oracle Application Express 4.2.5.00.08 VALID
OWB 11.2.0.3.0 VALID
OLAP Catalog 11.2.0.3.0 OPTION OFF
Spatial 12.1.0.2.0 UPGRADED
Oracle Multimedia 12.1.0.2.0 VALID
Oracle XML Database 12.1.0.2.0 VALID
Oracle Text 12.1.0.2.0 VALID
Oracle Workspace Manager 12.1.0.2.0 VALID
Oracle Database Catalog Views 12.1.0.2.0 UPGRADED
Oracle Database Packages and Types 12.1.0.2.0 UPGRADED
JServer JAVA Virtual Machine 12.1.0.2.0 VALID
Oracle XDK 12.1.0.2.0 VALID
Oracle Database Java Packages 12.1.0.2.0 VALID
OLAP Analytic Workspace 12.1.0.2.0 VALID
Oracle OLAP API 12.1.0.2.0 VALID
15 rows selected.
ORACLE instance started.
Total System Global Area 3774873600 bytes
Fixed Size 3717568 bytes
Variable Size 1962935872 bytes
Database Buffers 1795162112 bytes
Redo Buffers 13058048 bytes
Database mounted.
SQL>
SQL> alter database open;
Database altered.
SQL>
select COMP_NAME,VERSION,STATUS from dba_registry
COMP_NAME VERSION STATUS
---------------------------------------------------------------------- ------------------------------ -----------
Oracle Application Express 4.2.5.00.08 VALID
OWB 11.2.0.3.0 VALID
OLAP Catalog 11.2.0.3.0 OPTION OFF
Spatial 12.1.0.2.0 UPGRADED
Oracle Multimedia 12.1.0.2.0 VALID
Oracle XML Database 12.1.0.2.0 VALID
Oracle Text 12.1.0.2.0 VALID
Oracle Workspace Manager 12.1.0.2.0 VALID
Oracle Database Catalog Views 12.1.0.2.0 UPGRADED
Oracle Database Packages and Types 12.1.0.2.0 UPGRADED
JServer JAVA Virtual Machine 12.1.0.2.0 VALID
Oracle XDK 12.1.0.2.0 VALID
Oracle Database Java Packages 12.1.0.2.0 VALID
OLAP Analytic Workspace 12.1.0.2.0 VALID
Oracle OLAP API 12.1.0.2.0 VALID
15 rows selected.
35. Run the Post-Upgrade Status Tool
$ORACLE_HOME/rdbms/admin/utlu121s.sql which provides a summary of the upgrade at the end of the spool log.@$ORACLE_HOME/rdbms/admin/utlu121s.sql
36.Check the log file for "BEGIN catuppst.sql"
Verify that catuppst.sql ran during the upgrade process. If catuppst.sql has not run, then proceed to run catuppst.sql manually.
cd /data1/oracle12c/12c/diagnostics
[oracle@TSTSRV diagnostics]$ grep -i "BEGIN catuppst.sql" catupgrd0.log
21:51:16 SQL> Rem BEGIN catuppst.sql
21:51:16 SQL> Rem *********************************************************************
21:51:16 SQL> Rem BEGIN catuppst.sql
21:51:16 SQL> Rem *********************************************************************
21:51:16 SQL> Rem Set identifier to POSTUP for errorlogging
21:51:16 SQL>
21:51:16 SQL> SET ERRORLOGGING ON TABLE SYS.REGISTRY$ERROR IDENTIFIER 'POSTUP';
21:51:16 SQL>
21:51:16 SQL> SELECT dbms_registry_sys.time_stamp('POSTUP_BGN') as timestamp from dual;