Database upgrade from 11.2.0.4.0 to 12.1.0.2.0 on Windows server
Summary
Serve Namer=
Database Name=
1) Download preupgrade_12.1.0.2.0_15_crlf-The Preupgrade Assistant tool is a diagnostics utility which assesses the system for possible in-place upgrade limitations and provides a report with the
analysis results.
2) Download 12 database software(12.1.0.2.0 ) winx64_12102_database_1of2 and winx64_12102_database_2of2
3) Extract first zip file( winx64_12102_database_1of2) and extract second zip file in same folder ,give same folder name which extracted earlier and it prompt for merger ,Please continue for merger)
4) Check invalid objs for sys and system after running dictionary stats
5) Check tablespace free space as below and added space in system and sysaux tablespace if require
6) Check_stale_stats on production database and registry of database
7) Take backup of pfile,controlfile, inventory and listener ora ,tnsname.ora file.
8) Run the preupgrade tool and act as per recommendation
9)Take full backup
10) Bring listener down
11) Disable archivelog mode
12) Disable Backup jobs ( Taskschd.msc )
13 )Purge recyclebin
14) Installation of 12c software in new Home
15) Shutdown db and delete all oracle services from old oracle home (oracle database services and listener) and create new database service and start the listener from new Oracle Home
15) upgrade the database after setting new home
16) run post script and do verification
Details
Pre Check
Login on database as below
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\Users\user1>set ORACLE_SID=<Database Name>
C:\Users\user1>set ORACLE_HOME=e:\app\oracle\product\11.2.0.4\db1
C:\Users\user1>sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 11 08:24:09 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Check tablespace free space as below and added space in system and sysaux table if require
set pages 999 lines 100
col "Tablespace" for a50
col "Size MB" for 999999999
col "%Used" for 999
col "Add (80%)" for 999999
select tsu.tablespace_name "Tablespace"
, ceil(tsu.used_mb) "Size MB"
, 100 - floor(tsf.free_mb/tsu.used_mb*100) "%Used"
, ceil((tsu.used_mb - tsf.free_mb) / .75) - tsu.used_mb "Add (75%)"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name) tsu
, (select ts.tablespace_name
, nvl(sum(bytes)/1024/1024, 0) free_mb
from dba_tablespaces ts, dba_free_space fs
where ts.tablespace_name = fs.tablespace_name (+)
group by ts.tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
and 100 - floor(tsf.free_mb/tsu.used_mb*100) >= 75
order by 3,4
/
Tablespace Size MB %Used Add (75%)
-------------------------------------------------- ---------- ----- ----------
SPOTFIRE_DATA 1100 90 206
SYSAUX 12230 96 3299
SQL>
TABLESPACE_NAME TOTAL_IN_MB USED_IN_MB FREE_IN_MB PCT_USED
------------------------------ ----------- ---------- ---------- ---------
SYSAUX 12230.00 11645.63 583.38 95.22
UNDOTBS1 250.00 9.81 239.19 3.93
USERS 5.00 0.31 3.69 6.25
SYSTEM 2600.00 742.06 1856.94 28.54
if require ,Add space in tablespace
set num 5
COL file_name FORMAT A60
select file_name,bytes/1024/1024 mb ,AUTOEXTENSIBLE from dba_data_files where tablespace_name='&TSPAC' ;
SQL> alter datafile 'E:\APP\ORACLE\ORADATA\SYSAUX01.DBF' resize 14000m
;
SQL> alter database datafile 'E:\APP\ORACLE\ORADATA\SYSAUX01.DBF' resiz
e 14000m;
Database altered.
SQL> alter database datafile 'E:\APP\ORACLE\ORADATA\DBCENTQL\SPOTFIRE_DATA.DBF'
resize 3000m;
Database altered.
SQL> alter database datafile 'E:\APP\ORACLE\ORADATA\SYSTEM01.DBF' resiz
e 4000m;
Database altered.
SQL>
To check invalid,Statistics and registry status
select owner, object_name, object_type,status from dba_objects where owner in ('SYS','SYSTEM') and status like 'INVALID';
col TABLE_NAME for a30
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from dba_TAB_STATISTICS where STALE_STATS='YES';
set lines 200 pages 200
col action_time format a40;
col action format a30;
col namespace format a10;
col comments format a30;
col version format a13;
set lin 200 head on feed on pagesize 100;
select i.INST_ID,i.instance_name,r.* from gv$instance i, registry$history r;
col value format a70
select * from gv$diag_info where name='Diag Trace';
set lines 200 pages 200
column comp_name format a50
col SCHEMA format a20
SELECT comp_id,comp_name, status,schema version FROM dba_registry;
Run the utlrp to compile all objects
@?/rdbms/admin/utlrp.sql
Take backup of control file and spfile and listener.ora and tnsnames.ora file
create pfile='F:\backup_12c/DBCENTQL.ora' from spfile;
alter database backup controlfile to trace as 'F:\backup_12c/control1.ctl';
SQL> create pfile='F:\backup_12c/DBCENTQL.ora' from spfile;
File created.
SQL> alter database backup controlfile to trace as 'F:\backup_12c/control1.ctl'
;
Database altered.
Check archive log mode and disable it
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination E:\app\oracle\ARCH\ARCHDATA\DBCENTQL
Oldest online log sequence 8663
Next log sequence to archive 8665
Current log sequence 8665
shutdown database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Startup mount
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.1525E+10 bytes
Fixed Size 2291648 bytes
Variable Size 1979713600 bytes
Database Buffers 9529458688 bytes
Redo Buffers 13123584 bytes
Database mounted.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination E:\app\oracle\ARCH\ARCHDATA\DBCENTQL
Oldest online log sequence 8663
Current log sequence 8665
SQL>
SQL>
SQL> SELECT version FROM v$timezone_file;
VERSION
-------
14
1 row selected.
SQL>
SQL>
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
3 rows selected.
SQL>
Take backup of database(RMAN or Cold)
SQL> spool 'F:\backup_12c\logss.log'
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
E:\APP\ORACLE\ORADATA\SYSTEM01.DBF
E:\APP\ORACLE\ORADATA\SYSAUX01.DBF
E:\APP\ORACLE\ORADATA\UNDOTBS01.DBF
E:\APP\ORACLE\ORADATADBCENTQL\USERS01.DBF
8 rows selected.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
E:\APP\ORACLE\ORADATA\CONTROL01.CTL
E:\APP\ORACLE\FAST_RECOVERY_AREA\CONTROL02.CTL
2 rows selected.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
E:\APP\ORACLE\ORADATA\REDO03.LOG
E:\APP\ORACLE\ORADATA\REDO02.LOG
E:\APP\ORACLE\ORADATA\REDO01.LOG
3 rows selected.
check the stats
SQL> @E:\oracle\CHG0100819\check_stale_stats.sql
-- There are no stale statistics in APEX_030200 schema.
-- There are no stale statistics in CTXSYS schema.
-- There are no stale statistics in EXFSYS schema.
-- There are no stale statistics in MDSYS schema.
--------------------------------------------------------------------------------
-----------------------
-- OLAPSYS schema contains stale statistics use the following to gather the stat
istics --
--------------------------------------------------------------------------------
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('OLAPSYS',OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);
-- There are no stale statistics in ORDSYS schema.
-- There are no stale statistics in OWBSYS schema.
--------------------------------------------------------------------------------
-- SYS schema contains stale statistics use the following to gather the statisti
cs --
--------------------------------------------------------------------------------
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('SYS',OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);
--------------------------------------------------------------------------------
-- SYSMAN schema contains stale statistics use the following to gather the stati
stics --
--------------------------------------------------------------------------------
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('SYSMAN',OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);
-- There are no stale statistics in WMSYS schema.
-- There are no stale statistics in XDB schema.
12 Software Installation
Create new home
No need to create folder it will take automatically
F:\app\oracle\product\12.1.0\db1
Install the 12c Binary
run the Pre upgrade Tool from Old home
spool 'E:\oracle\CHG0100819/preupgrade.log'
@E:\oracle\CHG0100819\dbupgdiag.sql
@E:\oracle\CHG0100819\preupgrd.sql
SQL>
SQL> @E:\oracle\CHG0100819\preupgrd.sql
The syntax of the command is incorrect.
The syntax of the command is incorrect.
Loading Pre-Upgrade Package...
***************************************************************************
Executing Pre-Upgrade Checks in DBCENTQL...
***************************************************************************
************************************************************
====>> ERRORS FOUND for DBCENTQL <<====
The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
prior to attempting your upgrade.
Failure to do so will result in a failed upgrade.
You MUST resolve the above errors prior to upgrade
************************************************************
************************************************************
====>> PRE-UPGRADE RESULTS for DBCENTQL <<====
ACTIONS REQUIRED:
1. Review results of the pre-upgrade checks:
E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\preupgrade.log
2. Execute in the SOURCE environment BEFORE upgrade:
E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\preupgrade_fixups.sql
3. Execute in the NEW environment AFTER upgrade:
E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\postupgrade_fixups.sql
************************************************************
***************************************************************************
Pre-Upgrade Checks in DBCENTQL Completed.
***************************************************************************
***************************************************************************
***************************************************************************
SQL>
Upload db_upg_diag_DBCENTQL_11_Apr_2017_0905.log from "0" directory
@E:\CHG0100819\oracle_12c_software\preupgrade_12.1.0.2.0_15_crlf\preupgrd.sql
Oracle Database Pre-Upgrade Information Tool 04-11-2017 09:15:54
Script Version: 12.1.0.2.0 Build: 015
**********************************************************************
Database Name: DBCENTQL
Container Name: Not Applicable in Pre-12.1 database
Container ID: Not Applicable in Pre-12.1 database
Version: 11.2.0.4.0
Compatible: 11.2.0.4.0
Blocksize: 8192
Platform: Microsoft Windows x86 64-bit
Timezone file: V14
Database log mode: ARCHIVELOG
**********************************************************************
[Update parameters]
[Update Oracle Database 11.2.0.4.0 init.ora or spfile]
[The minimum memory/pool size recommendations are for the upgrade process
only. After the upgrade, these values can be reverted or adjusted as
applicable for the target release and production work.]
WARNING: --> "processes" needs to be increased to at least 300
**********************************************************************
**********************************************************************
[Renamed Parameters]
[No Renamed Parameters in use]
**********************************************************************
**********************************************************************
[Obsolete/Deprecated Parameters]
--> remote_os_authent 11.1 DESUPPORTED
[Changes required in Oracle Database init.ora or spfile]
**********************************************************************
[Component List]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> Oracle Enterprise Manager Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle Multimedia [upgrade] VALID
--> Oracle Spatial [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle Application Express [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
**********************************************************************
[Tablespaces]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
minimum required size: 1255 MB
--> SYSAUX tablespace is adequate for the upgrade.
minimum required size: 12585 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
minimum required size: 400 MB
--> TEMP tablespace is adequate for the upgrade.
minimum required size: 60 MB
[No adjustments recommended]
**********************************************************************
**********************************************************************
[Pre-Upgrade Checks]
**********************************************************************
WARNING: --> Process Count may be too low
Database has a maximum process count of 150 which is lower than the
default value of 300 for this release.
You should update your processes value prior to the upgrade
to a value of at least 300.
For example:
ALTER SYSTEM SET PROCESSES=400 SCOPE=SPFILE
/
or update your init.ora file.
WARNING: --> Enterprise Manager Database Control repository found in the database
In Oracle Database 12c, Database Control is removed during
the upgrade. To save time during the Upgrade, this action
can be done prior to upgrading using the following steps after
copying rdbms/admin/emremove.sql from the new Oracle home
- Stop EM Database Control:
$> emctl stop dbconsole
- Connect to the Database using the SYS account AS SYSDBA:
SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql
Without the set echo and serveroutput commands you will not
be able to follow the progress of the script.
INFORMATION: --> OLAP Catalog(AMD) exists in database
Starting with Oracle Database 12c, OLAP Catalog component is desupported.
If you are not using the OLAP Catalog component and want
to remove it, then execute the
ORACLE_HOME/olap/admin/catnoamd.sql script before or
after the upgrade.
INFORMATION: --> Older Timezone in use
Database is using a time zone file older than version 18.
After the upgrade, it is recommended that DBMS_DST package
be used to upgrade the 11.2.0.4.0 database time zone version
to the latest version which comes with the new release.
Please refer to My Oracle Support note number 1509653.1 for details.
INFORMATION: --> There are existing Oracle components that will NOT be
upgraded by the database upgrade script. Typically, such components
have their own upgrade scripts, are deprecated, or obsolete.
Those components are: OLAP Catalog,OWB
INFORMATION: --> Oracle Application Express (APEX) can be
manually upgraded prior to database upgrade
APEX is currently at version 3.2.1.00.12 and will need to be
upgraded to APEX version 4.2.5 in the new release.
Note 1: To reduce database upgrade time, APEX can be manually
upgraded outside of and prior to database upgrade.
Note 2: See MOS Note 1088970.1 for information on APEX
installation upgrades.
**********************************************************************
[Pre-Upgrade Recommendations]
**********************************************************************
*****************************************
********* Dictionary Statistics *********
*****************************************
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 ^^^
**********************************************************************
[Post-Upgrade Recommendations]
**********************************************************************
*****************************************
******** Fixed Object Statistics ********
*****************************************
Please create stats on fixed objects two weeks
after the upgrade using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
^^^ MANUAL ACTION SUGGESTED ^^^
**********************************************************************
************ Summary ************
0 ERRORS exist in your database.
2 WARNINGS that Oracle suggests are addressed to improve database performance.
4 INFORMATIONAL messages that should be reviewed prior to your upgrade.
After your database is upgraded and open in normal mode you must run
rdbms/admin/catuppst.sql which executes several required tasks and completes
the upgrade process.
You should follow that with the execution of rdbms/admin/utlrp.sql, and a
comparison of invalid objects before and after the upgrade using
rdbms/admin/utluiobj.sql
If needed you may want to upgrade your timezone data using the process
described in My Oracle Support note 1509653.1
***********************************
E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade
E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\preupgrade_fixups.sql
E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\postupgrade_fixups.sql
run pre upgrade fixup script and act as per above recommendation
@E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\preupgrade_fixups.sql
for removing dbconsole
In Oracle Database 12c, Database Control is removed during
the upgrade. To save time during the Upgrade, this action
can be done prior to upgrading using the following steps after
copying rdbms/admin/emremove.sql from the new Oracle home
- Stop EM Database Control:
$> emctl stop dbconsole or stop from service.msc and run below command
- Connect to the Database using the SYS account AS SYSDBA:
SET ECHO ON;
SET SERVEROUTPUT ON;
@F:\app\oracle\product\12.1.0.2\rdbms/admin\emremove.sql
Shutdown db and delete all oracle services from old oracle home (oracle database services and listener) and create new database service and start the listener from new Oracle Home
Note: open command prompt as administrator
a) delete database service and listener service from old oracle home as below
C:\Users\user1>set ORACLE_HOME=e:\app\oracle\product\11.2.0.4\db1
oradim -DELETE -SID DBCENTQL
b) set old oracle home and run below command to delete old listener
set ORACLE_HOME=e:\app\oracle\product\11.2.0.4\db1
sc delete OracleOraDb11g_home1TNSListenerlsitener
sc delete OracleOraDb11g_home1TNSListenerLISTENER_11g1104
sc delete OracleOraDb11g_home1TNSListener
sc delete OracleOraDb11g_home1ClrAgent
c) copy spfile to new home and create new service from new home
set ORACLE_HOME=D:\app\oracle\product\12.1.0.2
ORADIM -NEW -SID DBCENTQ8 -INTPWD oracle@123 -STARTMODE MANUAL
or
F:\app\oracle\product\12.1.0.2\database
set ORACLE_HOME=F:\app\oracle\product\12.1.0.2
set PATH=%ORACLE_HOME%/bin
echo %ORACLE_HOME
ORADIM -NEW -SID DBCENTQL -INTPWD oracle@123 -STARTMODE MANUAL
d) start new listener from new home as below
set ORACLE_HOME=F:\app\oracle\product\12.1.0.2
C:\Users\user1> lsnrctl start
you can see newly created listener in service panel(services.msc)
disable the scheduled backup
Implementation for database Upgrade
1) start database into upgrade mode with New Oracle Home and run catupgrd.sql
C:\Windows\system32>set ORACLE_SID=<Database_name>
C:\Windows\system32>set ORACLE_HOME=F:\app\oracle\product\12.1.0.2
C:\Windows\system32>sqlplus
SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 11 11:50:01 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to an idle instance.
SQL> startup upgrade
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.1576E+10 bytes
Fixed Size 6990320 bytes
Variable Size 2415920656 bytes
Database Buffers 9126805504 bytes
Redo Buffers 26562560 bytes
Database mounted.
Database opened.
SQL>
Example: Where parallelism is 6 ( n=6)
F:\backup_12c is backup location for log
cd %ORACLE_HOME%\rdbms\admin
%ORACLE_HOME%\perl\bin\perl catctl.pl -n 4 -l F:\backup_12c catupgrd.sql
Note:
I have seen that as many as 73 phases listed – some can act in parallel, other require serial execution. This will take some time depending on your system. If you wonder about the RESTART phases: those happen if timing dependencies make it necessary to rerun a certain action. The logfiles will be written by default into the directory from which you started catctl.pl, $ORACLE_HOME/rdbms/admin
Once the upgrade is finished it will shutdown the database and in the next phase restart it in normal mode.
F:\app\oracle\product\12.1.0.2\RDBMS\ADMIN>%ORACLE_HOME%\perl\bin\perl catctl.pl
-n 4 -l F:\backup_12c catupgrd.sql
Argument list for [catctl.pl]
SQL Process Count n = 4
SQL PDB Process Count N = 0
Input Directory d = 0
Phase Logging Table t = 0
Log Dir l = F:\backup_12c
Script s = 0
Serial Run S = 0
Upgrade Mode active M = 0
Start Phase p = 0
End Phase P = 0
Log Id i = 0
Run in c = 0
Do not run in C = 0
Echo OFF e = 1
No Post Upgrade x = 0
Reverse Order r = 0
Open Mode Normal o = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
Display Phases y = 0
Child Process I = 0
catctl.pl version: 12.1.0.2.0
Oracle Home = F:\app\oracle\product\12.1.0.2
Analyzing file catupgrd.sql
Log files in F:\backup_12c
catcon: ALL catcon-related output will be written to F:\backup_12c/catupgrd_catc
on_33408.lst
catcon: See F:\backup_12c/catupgrd*.log files for output generated by scripts
catcon: See F:\backup_12c/catupgrd_*.lst files for spool files, if any
Number of Cpus = 24
SQL Process Count = 4
------------------------------------------------------
Phases [0-73]
Serial Phase #: 0 Files: 1 Time: 803s
Serial Phase #: 1 Files: 5 Time: 109s
Restart Phase #: 2 Files: 1 Time: 1s
Parallel Phase #: 3 Files: 18 Time: 19s
Restart Phase #: 4 Files: 1 Time: 0s
Serial Phase #: 5 Files: 5 Time: 47s
Serial Phase #: 6 Files: 1 Time: 28s
Serial Phase #: 7 Files: 4 Time: 20s
Restart Phase #: 8 Files: 1 Time: 0s
Parallel Phase #: 9 Files: 62 Time: 62s
Restart Phase #:10 Files: 1 Time: 1s
Serial Phase #:11 Files: 1 Time: 33s
Restart Phase #:12 Files: 1 Time: 0s
Parallel Phase #:13 Files: 91 Time: 32s
Restart Phase #:14 Files: 1 Time: 0s
Parallel Phase #:15 Files: 111 Time: 45s
Restart Phase #:16 Files: 1 Time: 0s
Serial Phase #:17 Files: 3 Time: 4s
Restart Phase #:18 Files: 1 Time: 0s
Parallel Phase #:19 Files: 32 Time: 33s
Restart Phase #:20 Files: 1 Time: 1s
Serial Phase #:21 Files: 3 Time: 15s
Restart Phase #:22 Files: 1 Time: 1s
Parallel Phase #:23 Files: 23 Time: 111s
Restart Phase #:24 Files: 1 Time: 0s
Parallel Phase #:25 Files: 11 Time: 46s
Restart Phase #:26 Files: 1 Time: 0s
Serial Phase #:27 Files: 1 Time: 2s
Restart Phase #:28 Files: 1 Time: 0s
Serial Phase #:30 Files: 1 Time: 0s
Serial Phase #:31 Files: 257 Time: 46s
Serial Phase #:32 Files: 1 Time: 0s
Restart Phase #:33 Files: 1 Time: 0s
Serial Phase #:34 Files: 1 Time: 11s
Restart Phase #:35 Files: 1 Time: 0s
Restart Phase #:36 Files: 1 Time: 1s
Serial Phase #:37 Files: 4 Time: 83s
Restart Phase #:38 Files: 1 Time: 0s
Parallel Phase #:39 Files: 13 Time: 63s
Restart Phase #:40 Files: 1 Time: 0s
Parallel Phase #:41 Files: 10 Time: 13s
Restart Phase #:42 Files: 1 Time: 0s
Serial Phase #:43 Files: 1 Time: 12s
Restart Phase #:44 Files: 1 Time: 0s
Serial Phase #:45 Files: 1 Time: 12s
Serial Phase #:46 Files: 1 Time: 2s
Restart Phase #:47 Files: 1 Time: 1s
Serial Phase #:48 Files: 1 Time: 349s
Restart Phase #:49 Files: 1 Time: 0s
Serial Phase #:50 Files: 1 Time: 58s
Restart Phase #:51 Files: 1 Time: 1s
Serial Phase #:52 Files: 1 Time: 31s
Restart Phase #:53 Files: 1 Time: 0s
Serial Phase #:54 Files: 1 Time: 656s
Restart Phase #:55 Files: 1 Time: 0s
Serial Phase #:56 Files: 1 Time: 104s
Restart Phase #:57 Files: 1 Time: 1s
Serial Phase #:58 Files: 1 Time: 196s
Restart Phase #:59 Files: 1 Time: 0s
Serial Phase #:60 Files: 1 Time: 962s
Restart Phase #:61 Files: 1 Time: 1s
Serial Phase #:62 Files: 1 Time: 2537s
Restart Phase #:63 Files: 1 Time: 0s
Serial Phase #:64 Files: 1 Time: 5s
Serial Phase #:65 Files: 1 Calling sqlpatch with F:\app\oracle\product\12.1.0.
2\perl\bin\perl.exe -I F:\app\oracle\product\12.1.0.2\RDBMS\ADMIN -I F:\app\orac
le\product\12.1.0.2\sqlpatch F:\app\oracle\product\12.1.0.2\sqlpatch\sqlpatch.pl
-verbose -upgrade_mode_only > F:\backup_12c/catupgrd_datapatch_upgrade.log 2> F
:\backup_12c/catupgrd_datapatch_upgrade.err
returned from sqlpatch
A subdirectory or file F:\app\oracle\product\12.1.0.2\cfgtoollogs\DBCENTQL\upgra
de already exists.
Time: 113s
Serial Phase #:66 Files: 1 Time: 80s
Serial Phase #:68 Files: 1 Time: 0s
Serial Phase #:69 Files: 1 Calling sqlpatch with F:\app\oracle\product\12.1.0.
2\perl\bin\perl.exe -I F:\app\oracle\product\12.1.0.2\RDBMS\ADMIN -I F:\app\orac
le\product\12.1.0.2\sqlpatch F:\app\oracle\product\12.1.0.2\sqlpatch\sqlpatch.pl
-verbose > F:\backup_12c/catupgrd_datapatch_normal.log 2> F:\backup_12c/catupgr
d_datapatch_normal.err
returned from sqlpatch
Time: 106s
Serial Phase #:70 Files: 1
Serial Phase #:71 Files: 1 Time: 1s
Serial Phase #:72 Files: 1 Time: 0s
Serial Phase #:73 Files: 1 Time: 25s
Grand Total Time: 7181s
LOG FILES: (catupgrd*.log)
Upgrade Summary Report Located in:
F:\app\oracle\product\12.1.0.2\cfgtoollogs\DBCENTQL\upgrade\upg_summary.log
Grand Total Upgrade Time: [0d:1h:59m:41s]
F:\app\oracle\product\12.1.0.2\RDBMS\ADMIN>
Post Upgradation
1) start the database in normal mode and Run the Post-Upgrade Status Tool and post fixupscript
post upgradfile pscript
REM Post Upgrade Script Generated on: 2017-04-11 09:15:52
REM Generated by Version: 12.1.0.2 Build: 015
SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 750;
DECLARE
con_name varchar2(40);
BEGIN
execute immediate
'select dbms_preup.get_con_name from sys.dual' into con_name;
IF con_name = 'DBCENTQL' THEN
BEGIN
dbms_output.put_line ('Post Upgrade Fixup Script Generated on 2017-04-11 09:15:52 Version: 12.1.0.2 Build: 015');
dbms_output.put_line ('Beginning Post-Upgrade Fixups...');
END;
BEGIN
dbms_preup.clear_run_flag(FALSE);
END;
BEGIN
-- ***************** Fixup Details ***********************************
-- Name: OLD_TIME_ZONES_EXIST
-- Description: Check for use of older timezone data file
-- Severity: Informational
-- Action: ^^^ MANUAL ACTION REQUIRED ^^^
-- Fix Summary:
-- Update the timezone using the DBMS_DST package after upgrade is complete.
dbms_preup.run_fixup_and_report('OLD_TIME_ZONES_EXIST');
END;
BEGIN
-- ***************** Fixup Details ***********************************
-- Name: NOT_UPG_BY_STD_UPGRD
-- Description: Identify existing components that will NOT be upgraded
-- Severity: Informational
-- Action: ^^^ MANUAL ACTION REQUIRED ^^^
-- Fix Summary:
-- This fixup does not perform any action.
dbms_preup.run_fixup_and_report('NOT_UPG_BY_STD_UPGRD');
END;
BEGIN
dbms_output.put_line ('');
dbms_output.put_line ('**********************************************************************');
dbms_output.put_line (' [Post-Upgrade Recommendations]');
dbms_output.put_line ('**********************************************************************');
dbms_output.put_line ('');
END;
BEGIN
dbms_output.put_line (' *****************************************');
dbms_output.put_line (' ******** Fixed Object Statistics ********');
dbms_output.put_line (' *****************************************');
dbms_output.put_line ('');
dbms_output.put_line ('Please create stats on fixed objects two weeks');
dbms_output.put_line ('after the upgrade using the command:');
dbms_output.put_line (' EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;');
dbms_output.put_line ('');
dbms_output.put_line ('^^^ MANUAL ACTION SUGGESTED ^^^');
dbms_output.put_line ('');
END;
BEGIN dbms_preup.fixup_summary(FALSE); END;
BEGIN
dbms_output.put_line ('*************** Post Upgrade Fixup Script Complete ********************');
END;
END IF;
END;
/
-- Post Upgrade Script Closed At: 2017-04-11 09:16:14
REM __________________________________________________________________________
$ORACLE_HOME/rdbms/admin/utlu121s.sql which provides a summary of the upgrade at the end of the spool log.
It displays the status of the database components in the upgraded database and the time required to complete each component upgrade.
Any errors that occur during the upgrade are listed with each component and must be addressed.
E:\app\oracle\cfgtoollogs\DBCENTQJ\preupgrade\postupgrade_fixups.sql
@%ORACLE_HOME%\/rdbms/admin/utlu121s.sql
SQL> @%ORACLE_HOME%\/rdbms/admin/utlu121s.sql
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
CATCTL REPORT = F:\app\oracle\product\12.1.0.2\cfgtoollogs\DBCENTQL\upgrade\upg_
summary.log
PL/SQL procedure successfully completed.
Oracle Database 12.1 Post-Upgrade Status Tool 04-11-2017 14:03:02
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
Oracle Server UPGRADED 12.1.0.2.0 00:27:33
JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:05:46
Oracle Workspace Manager VALID 12.1.0.2.0 00:01:21
OLAP Analytic Workspace VALID 12.1.0.2.0 00:00:28
OLAP Catalog OPTION OFF 11.2.0.4.0 00:00:00
Oracle OLAP API VALID 12.1.0.2.0 00:00:33
Oracle XDK VALID 12.1.0.2.0 00:00:55
Oracle Text VALID 12.1.0.2.0 00:01:25
Oracle XML Database VALID 12.1.0.2.0 00:09:27
Oracle Database Java Packages VALID 12.1.0.2.0 00:00:19
Oracle Multimedia VALID 12.1.0.2.0 00:03:13
Spatial UPGRADED 12.1.0.2.0 00:15:59
Oracle Application Express VALID 4.2.5.00.08 00:40:41
Final Actions 00:02:45
Post Upgrade 00:03:59
Total Upgrade Time: 01:55:57
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- Update Summary Table with con_name and endtime.
SQL> --
SQL> UPDATE sys.registry$upg_summary SET reportname = :ReportName,
2 con_name = SYS_CONTEXT('USERENV','CON_NAME'
),
3 endtime = SYSDATE
4 WHERE con_id = -1;
1 row updated.
SQL> commit;
Commit complete.
SQL>
Important: The catuppst.sql script is run as part of the upgrade process unless the upgrade returns errors during the process. Check the log file for "BEGIN catuppst.sql" to verify that catuppst.sql ran during the upgrade process. If catuppst.sql has not run, then proceed to run catuppst.sql as shown in this step. Warning messages are also displayed when running catctl.pl indicating that catuppst.sql was not run during the upgrade.
Run catuppst.sql, located in the $ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode.
SQL> @catuppst.sql
@%ORACLE_HOME%\/rdbms/admin/catuppst.sql
This script can be run concurrently with utlrp.sql.
Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.
SQL> @utlrp.sql
@%ORACLE_HOME%\/rdbms/admin/utlrp.sql
Check for the integrity of the upgraded database by running dbupgdiag.sql script from the below article:
Check for the integrity of the upgraded database by running dbupgdiag.sql script from the below article:
Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)
If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.
After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.
1) Execute in the NEW environment AFTER upgrade:
E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\postupgrade_fixups.sql
3) complete stats on database
4) enable scheduled backup
5) enable archive mode
6) check invalid objects
SELECT DISTINCT object_name, object_type, owner FROM dba_objects WHERE status='INVALID' ;
SQL> select count(*) from dba_objects where status like '%INVALID%';
7) Please check upgrade logs and make sure catuppst.sql run without any error.
Run utluiobj.sql to validate that all expected packages and classes are valid :
SQL> @$ORACLE_HOME/rdbms/admin/utluiobj.sql
@%ORACLE_HOME%\/rdbms/admin/utluiobj.sql
Collect statistics on fixed object with following command :
SQL> execute dbms_stats.gather_fixed_object_stats
13) Check the database component status to confirm upgrade completion
Connect as SYS user to the database
col comp_id format a10
col comp_name format a30
col version format a10
col status format a8
select substr(comp_id,1,15) comp_id,substr(comp_name,1,30) comp_name,substr(version,1,10) version,status from dba_registry
copy listener.ora and tnsname.ora from old home to new home and check remote connectivity.
Summary
Serve Namer=
Database Name=
1) Download preupgrade_12.1.0.2.0_15_crlf-The Preupgrade Assistant tool is a diagnostics utility which assesses the system for possible in-place upgrade limitations and provides a report with the
analysis results.
2) Download 12 database software(12.1.0.2.0 ) winx64_12102_database_1of2 and winx64_12102_database_2of2
3) Extract first zip file( winx64_12102_database_1of2) and extract second zip file in same folder ,give same folder name which extracted earlier and it prompt for merger ,Please continue for merger)
4) Check invalid objs for sys and system after running dictionary stats
5) Check tablespace free space as below and added space in system and sysaux tablespace if require
6) Check_stale_stats on production database and registry of database
7) Take backup of pfile,controlfile, inventory and listener ora ,tnsname.ora file.
8) Run the preupgrade tool and act as per recommendation
9)Take full backup
10) Bring listener down
11) Disable archivelog mode
12) Disable Backup jobs ( Taskschd.msc )
13 )Purge recyclebin
14) Installation of 12c software in new Home
15) Shutdown db and delete all oracle services from old oracle home (oracle database services and listener) and create new database service and start the listener from new Oracle Home
15) upgrade the database after setting new home
16) run post script and do verification
Details
Pre Check
Login on database as below
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation. All rights reserved.
C:\Users\user1>set ORACLE_SID=<Database Name>
C:\Users\user1>set ORACLE_HOME=e:\app\oracle\product\11.2.0.4\db1
C:\Users\user1>sqlplus
SQL*Plus: Release 11.2.0.4.0 Production on Tue Apr 11 08:24:09 2017
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Check tablespace free space as below and added space in system and sysaux table if require
set pages 999 lines 100
col "Tablespace" for a50
col "Size MB" for 999999999
col "%Used" for 999
col "Add (80%)" for 999999
select tsu.tablespace_name "Tablespace"
, ceil(tsu.used_mb) "Size MB"
, 100 - floor(tsf.free_mb/tsu.used_mb*100) "%Used"
, ceil((tsu.used_mb - tsf.free_mb) / .75) - tsu.used_mb "Add (75%)"
from (select tablespace_name, sum(bytes)/1024/1024 used_mb
from dba_data_files group by tablespace_name) tsu
, (select ts.tablespace_name
, nvl(sum(bytes)/1024/1024, 0) free_mb
from dba_tablespaces ts, dba_free_space fs
where ts.tablespace_name = fs.tablespace_name (+)
group by ts.tablespace_name) tsf
where tsu.tablespace_name = tsf.tablespace_name (+)
and 100 - floor(tsf.free_mb/tsu.used_mb*100) >= 75
order by 3,4
/
Tablespace Size MB %Used Add (75%)
-------------------------------------------------- ---------- ----- ----------
SPOTFIRE_DATA 1100 90 206
SYSAUX 12230 96 3299
SQL>
TABLESPACE_NAME TOTAL_IN_MB USED_IN_MB FREE_IN_MB PCT_USED
------------------------------ ----------- ---------- ---------- ---------
SYSAUX 12230.00 11645.63 583.38 95.22
UNDOTBS1 250.00 9.81 239.19 3.93
USERS 5.00 0.31 3.69 6.25
SYSTEM 2600.00 742.06 1856.94 28.54
if require ,Add space in tablespace
set num 5
COL file_name FORMAT A60
select file_name,bytes/1024/1024 mb ,AUTOEXTENSIBLE from dba_data_files where tablespace_name='&TSPAC' ;
SQL> alter datafile 'E:\APP\ORACLE\ORADATA\SYSAUX01.DBF' resize 14000m
;
SQL> alter database datafile 'E:\APP\ORACLE\ORADATA\SYSAUX01.DBF' resiz
e 14000m;
Database altered.
SQL> alter database datafile 'E:\APP\ORACLE\ORADATA\DBCENTQL\SPOTFIRE_DATA.DBF'
resize 3000m;
Database altered.
SQL> alter database datafile 'E:\APP\ORACLE\ORADATA\SYSTEM01.DBF' resiz
e 4000m;
Database altered.
SQL>
To check invalid,Statistics and registry status
select owner, object_name, object_type,status from dba_objects where owner in ('SYS','SYSTEM') and status like 'INVALID';
col TABLE_NAME for a30
col PARTITION_NAME for a20
col SUBPARTITION_NAME for a20
select OWNER,TABLE_NAME,PARTITION_NAME,SUBPARTITION_NAME,NUM_ROWS,LAST_ANALYZED from dba_TAB_STATISTICS where STALE_STATS='YES';
set lines 200 pages 200
col action_time format a40;
col action format a30;
col namespace format a10;
col comments format a30;
col version format a13;
set lin 200 head on feed on pagesize 100;
select i.INST_ID,i.instance_name,r.* from gv$instance i, registry$history r;
col value format a70
select * from gv$diag_info where name='Diag Trace';
set lines 200 pages 200
column comp_name format a50
col SCHEMA format a20
SELECT comp_id,comp_name, status,schema version FROM dba_registry;
Run the utlrp to compile all objects
@?/rdbms/admin/utlrp.sql
Take backup of control file and spfile and listener.ora and tnsnames.ora file
create pfile='F:\backup_12c/DBCENTQL.ora' from spfile;
alter database backup controlfile to trace as 'F:\backup_12c/control1.ctl';
SQL> create pfile='F:\backup_12c/DBCENTQL.ora' from spfile;
File created.
SQL> alter database backup controlfile to trace as 'F:\backup_12c/control1.ctl'
;
Database altered.
Check archive log mode and disable it
SQL>
SQL> archive log list
Database log mode Archive Mode
Automatic archival Enabled
Archive destination E:\app\oracle\ARCH\ARCHDATA\DBCENTQL
Oldest online log sequence 8663
Next log sequence to archive 8665
Current log sequence 8665
shutdown database
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
Startup mount
SQL> startup mount
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.1525E+10 bytes
Fixed Size 2291648 bytes
Variable Size 1979713600 bytes
Database Buffers 9529458688 bytes
Redo Buffers 13123584 bytes
Database mounted.
SQL> alter database noarchivelog;
Database altered.
SQL> alter database open;
Database altered.
SQL> archive log list
Database log mode No Archive Mode
Automatic archival Disabled
Archive destination E:\app\oracle\ARCH\ARCHDATA\DBCENTQL
Oldest online log sequence 8663
Current log sequence 8665
SQL>
SQL>
SQL> SELECT version FROM v$timezone_file;
VERSION
-------
14
1 row selected.
SQL>
SQL>
SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
PROPERTY_NAME VALUE
------------------------------ ------------------------------
DST_PRIMARY_TT_VERSION 14
DST_SECONDARY_TT_VERSION 0
DST_UPGRADE_STATE NONE
3 rows selected.
SQL>
Take backup of database(RMAN or Cold)
SQL> spool 'F:\backup_12c\logss.log'
SQL> select name from v$datafile;
NAME
--------------------------------------------------------------------------------
E:\APP\ORACLE\ORADATA\SYSTEM01.DBF
E:\APP\ORACLE\ORADATA\SYSAUX01.DBF
E:\APP\ORACLE\ORADATA\UNDOTBS01.DBF
E:\APP\ORACLE\ORADATADBCENTQL\USERS01.DBF
8 rows selected.
SQL> select name from v$controlfile;
NAME
--------------------------------------------------------------------------------
E:\APP\ORACLE\ORADATA\CONTROL01.CTL
E:\APP\ORACLE\FAST_RECOVERY_AREA\CONTROL02.CTL
2 rows selected.
SQL> select member from v$logfile;
MEMBER
--------------------------------------------------------------------------------
E:\APP\ORACLE\ORADATA\REDO03.LOG
E:\APP\ORACLE\ORADATA\REDO02.LOG
E:\APP\ORACLE\ORADATA\REDO01.LOG
3 rows selected.
check the stats
SQL> @E:\oracle\CHG0100819\check_stale_stats.sql
-- There are no stale statistics in APEX_030200 schema.
-- There are no stale statistics in CTXSYS schema.
-- There are no stale statistics in EXFSYS schema.
-- There are no stale statistics in MDSYS schema.
--------------------------------------------------------------------------------
-----------------------
-- OLAPSYS schema contains stale statistics use the following to gather the stat
istics --
--------------------------------------------------------------------------------
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('OLAPSYS',OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);
-- There are no stale statistics in ORDSYS schema.
-- There are no stale statistics in OWBSYS schema.
--------------------------------------------------------------------------------
-- SYS schema contains stale statistics use the following to gather the statisti
cs --
--------------------------------------------------------------------------------
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('SYS',OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);
--------------------------------------------------------------------------------
-- SYSMAN schema contains stale statistics use the following to gather the stati
stics --
--------------------------------------------------------------------------------
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS('SYSMAN',OPTIONS=>'GATHER STALE', ESTIMATE_PERCENT => DBMS_STATS.AUTO_SAMPLE_SIZE, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE);
-- There are no stale statistics in WMSYS schema.
-- There are no stale statistics in XDB schema.
12 Software Installation
Create new home
No need to create folder it will take automatically
F:\app\oracle\product\12.1.0\db1
Install the 12c Binary
run the Pre upgrade Tool from Old home
spool 'E:\oracle\CHG0100819/preupgrade.log'
@E:\oracle\CHG0100819\dbupgdiag.sql
@E:\oracle\CHG0100819\preupgrd.sql
SQL>
SQL> @E:\oracle\CHG0100819\preupgrd.sql
The syntax of the command is incorrect.
The syntax of the command is incorrect.
Loading Pre-Upgrade Package...
***************************************************************************
Executing Pre-Upgrade Checks in DBCENTQL...
***************************************************************************
************************************************************
====>> ERRORS FOUND for DBCENTQL <<====
The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
prior to attempting your upgrade.
Failure to do so will result in a failed upgrade.
You MUST resolve the above errors prior to upgrade
************************************************************
************************************************************
====>> PRE-UPGRADE RESULTS for DBCENTQL <<====
ACTIONS REQUIRED:
1. Review results of the pre-upgrade checks:
E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\preupgrade.log
2. Execute in the SOURCE environment BEFORE upgrade:
E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\preupgrade_fixups.sql
3. Execute in the NEW environment AFTER upgrade:
E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\postupgrade_fixups.sql
************************************************************
***************************************************************************
Pre-Upgrade Checks in DBCENTQL Completed.
***************************************************************************
***************************************************************************
***************************************************************************
SQL>
Upload db_upg_diag_DBCENTQL_11_Apr_2017_0905.log from "0" directory
@E:\CHG0100819\oracle_12c_software\preupgrade_12.1.0.2.0_15_crlf\preupgrd.sql
Oracle Database Pre-Upgrade Information Tool 04-11-2017 09:15:54
Script Version: 12.1.0.2.0 Build: 015
**********************************************************************
Database Name: DBCENTQL
Container Name: Not Applicable in Pre-12.1 database
Container ID: Not Applicable in Pre-12.1 database
Version: 11.2.0.4.0
Compatible: 11.2.0.4.0
Blocksize: 8192
Platform: Microsoft Windows x86 64-bit
Timezone file: V14
Database log mode: ARCHIVELOG
**********************************************************************
[Update parameters]
[Update Oracle Database 11.2.0.4.0 init.ora or spfile]
[The minimum memory/pool size recommendations are for the upgrade process
only. After the upgrade, these values can be reverted or adjusted as
applicable for the target release and production work.]
WARNING: --> "processes" needs to be increased to at least 300
**********************************************************************
**********************************************************************
[Renamed Parameters]
[No Renamed Parameters in use]
**********************************************************************
**********************************************************************
[Obsolete/Deprecated Parameters]
--> remote_os_authent 11.1 DESUPPORTED
[Changes required in Oracle Database init.ora or spfile]
**********************************************************************
[Component List]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> Oracle Enterprise Manager Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle Multimedia [upgrade] VALID
--> Oracle Spatial [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle Application Express [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
**********************************************************************
[Tablespaces]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
minimum required size: 1255 MB
--> SYSAUX tablespace is adequate for the upgrade.
minimum required size: 12585 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
minimum required size: 400 MB
--> TEMP tablespace is adequate for the upgrade.
minimum required size: 60 MB
[No adjustments recommended]
**********************************************************************
**********************************************************************
[Pre-Upgrade Checks]
**********************************************************************
WARNING: --> Process Count may be too low
Database has a maximum process count of 150 which is lower than the
default value of 300 for this release.
You should update your processes value prior to the upgrade
to a value of at least 300.
For example:
ALTER SYSTEM SET PROCESSES=400 SCOPE=SPFILE
/
or update your init.ora file.
WARNING: --> Enterprise Manager Database Control repository found in the database
In Oracle Database 12c, Database Control is removed during
the upgrade. To save time during the Upgrade, this action
can be done prior to upgrading using the following steps after
copying rdbms/admin/emremove.sql from the new Oracle home
- Stop EM Database Control:
$> emctl stop dbconsole
- Connect to the Database using the SYS account AS SYSDBA:
SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql
Without the set echo and serveroutput commands you will not
be able to follow the progress of the script.
INFORMATION: --> OLAP Catalog(AMD) exists in database
Starting with Oracle Database 12c, OLAP Catalog component is desupported.
If you are not using the OLAP Catalog component and want
to remove it, then execute the
ORACLE_HOME/olap/admin/catnoamd.sql script before or
after the upgrade.
INFORMATION: --> Older Timezone in use
Database is using a time zone file older than version 18.
After the upgrade, it is recommended that DBMS_DST package
be used to upgrade the 11.2.0.4.0 database time zone version
to the latest version which comes with the new release.
Please refer to My Oracle Support note number 1509653.1 for details.
INFORMATION: --> There are existing Oracle components that will NOT be
upgraded by the database upgrade script. Typically, such components
have their own upgrade scripts, are deprecated, or obsolete.
Those components are: OLAP Catalog,OWB
INFORMATION: --> Oracle Application Express (APEX) can be
manually upgraded prior to database upgrade
APEX is currently at version 3.2.1.00.12 and will need to be
upgraded to APEX version 4.2.5 in the new release.
Note 1: To reduce database upgrade time, APEX can be manually
upgraded outside of and prior to database upgrade.
Note 2: See MOS Note 1088970.1 for information on APEX
installation upgrades.
**********************************************************************
[Pre-Upgrade Recommendations]
**********************************************************************
*****************************************
********* Dictionary Statistics *********
*****************************************
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 ^^^
**********************************************************************
[Post-Upgrade Recommendations]
**********************************************************************
*****************************************
******** Fixed Object Statistics ********
*****************************************
Please create stats on fixed objects two weeks
after the upgrade using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
^^^ MANUAL ACTION SUGGESTED ^^^
**********************************************************************
************ Summary ************
0 ERRORS exist in your database.
2 WARNINGS that Oracle suggests are addressed to improve database performance.
4 INFORMATIONAL messages that should be reviewed prior to your upgrade.
After your database is upgraded and open in normal mode you must run
rdbms/admin/catuppst.sql which executes several required tasks and completes
the upgrade process.
You should follow that with the execution of rdbms/admin/utlrp.sql, and a
comparison of invalid objects before and after the upgrade using
rdbms/admin/utluiobj.sql
If needed you may want to upgrade your timezone data using the process
described in My Oracle Support note 1509653.1
***********************************
E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade
E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\preupgrade_fixups.sql
E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\postupgrade_fixups.sql
run pre upgrade fixup script and act as per above recommendation
@E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\preupgrade_fixups.sql
for removing dbconsole
In Oracle Database 12c, Database Control is removed during
the upgrade. To save time during the Upgrade, this action
can be done prior to upgrading using the following steps after
copying rdbms/admin/emremove.sql from the new Oracle home
- Stop EM Database Control:
$> emctl stop dbconsole or stop from service.msc and run below command
- Connect to the Database using the SYS account AS SYSDBA:
SET ECHO ON;
SET SERVEROUTPUT ON;
@F:\app\oracle\product\12.1.0.2\rdbms/admin\emremove.sql
Shutdown db and delete all oracle services from old oracle home (oracle database services and listener) and create new database service and start the listener from new Oracle Home
Note: open command prompt as administrator
a) delete database service and listener service from old oracle home as below
C:\Users\user1>set ORACLE_HOME=e:\app\oracle\product\11.2.0.4\db1
oradim -DELETE -SID DBCENTQL
b) set old oracle home and run below command to delete old listener
set ORACLE_HOME=e:\app\oracle\product\11.2.0.4\db1
sc delete OracleOraDb11g_home1TNSListenerlsitener
sc delete OracleOraDb11g_home1TNSListenerLISTENER_11g1104
sc delete OracleOraDb11g_home1TNSListener
sc delete OracleOraDb11g_home1ClrAgent
c) copy spfile to new home and create new service from new home
set ORACLE_HOME=D:\app\oracle\product\12.1.0.2
ORADIM -NEW -SID DBCENTQ8 -INTPWD oracle@123 -STARTMODE MANUAL
or
F:\app\oracle\product\12.1.0.2\database
set ORACLE_HOME=F:\app\oracle\product\12.1.0.2
set PATH=%ORACLE_HOME%/bin
echo %ORACLE_HOME
ORADIM -NEW -SID DBCENTQL -INTPWD oracle@123 -STARTMODE MANUAL
d) start new listener from new home as below
set ORACLE_HOME=F:\app\oracle\product\12.1.0.2
C:\Users\user1> lsnrctl start
you can see newly created listener in service panel(services.msc)
disable the scheduled backup
Implementation for database Upgrade
1) start database into upgrade mode with New Oracle Home and run catupgrd.sql
C:\Windows\system32>set ORACLE_SID=<Database_name>
C:\Windows\system32>set ORACLE_HOME=F:\app\oracle\product\12.1.0.2
C:\Windows\system32>sqlplus
SQL*Plus: Release 12.1.0.2.0 Production on Tue Apr 11 11:50:01 2017
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Enter user-name: / as sysdba
Connected to an idle instance.
SQL> startup upgrade
ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance
ORACLE instance started.
Total System Global Area 1.1576E+10 bytes
Fixed Size 6990320 bytes
Variable Size 2415920656 bytes
Database Buffers 9126805504 bytes
Redo Buffers 26562560 bytes
Database mounted.
Database opened.
SQL>
Example: Where parallelism is 6 ( n=6)
F:\backup_12c is backup location for log
cd %ORACLE_HOME%\rdbms\admin
%ORACLE_HOME%\perl\bin\perl catctl.pl -n 4 -l F:\backup_12c catupgrd.sql
Note:
I have seen that as many as 73 phases listed – some can act in parallel, other require serial execution. This will take some time depending on your system. If you wonder about the RESTART phases: those happen if timing dependencies make it necessary to rerun a certain action. The logfiles will be written by default into the directory from which you started catctl.pl, $ORACLE_HOME/rdbms/admin
Once the upgrade is finished it will shutdown the database and in the next phase restart it in normal mode.
F:\app\oracle\product\12.1.0.2\RDBMS\ADMIN>%ORACLE_HOME%\perl\bin\perl catctl.pl
-n 4 -l F:\backup_12c catupgrd.sql
Argument list for [catctl.pl]
SQL Process Count n = 4
SQL PDB Process Count N = 0
Input Directory d = 0
Phase Logging Table t = 0
Log Dir l = F:\backup_12c
Script s = 0
Serial Run S = 0
Upgrade Mode active M = 0
Start Phase p = 0
End Phase P = 0
Log Id i = 0
Run in c = 0
Do not run in C = 0
Echo OFF e = 1
No Post Upgrade x = 0
Reverse Order r = 0
Open Mode Normal o = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
Display Phases y = 0
Child Process I = 0
catctl.pl version: 12.1.0.2.0
Oracle Home = F:\app\oracle\product\12.1.0.2
Analyzing file catupgrd.sql
Log files in F:\backup_12c
catcon: ALL catcon-related output will be written to F:\backup_12c/catupgrd_catc
on_33408.lst
catcon: See F:\backup_12c/catupgrd*.log files for output generated by scripts
catcon: See F:\backup_12c/catupgrd_*.lst files for spool files, if any
Number of Cpus = 24
SQL Process Count = 4
------------------------------------------------------
Phases [0-73]
Serial Phase #: 0 Files: 1 Time: 803s
Serial Phase #: 1 Files: 5 Time: 109s
Restart Phase #: 2 Files: 1 Time: 1s
Parallel Phase #: 3 Files: 18 Time: 19s
Restart Phase #: 4 Files: 1 Time: 0s
Serial Phase #: 5 Files: 5 Time: 47s
Serial Phase #: 6 Files: 1 Time: 28s
Serial Phase #: 7 Files: 4 Time: 20s
Restart Phase #: 8 Files: 1 Time: 0s
Parallel Phase #: 9 Files: 62 Time: 62s
Restart Phase #:10 Files: 1 Time: 1s
Serial Phase #:11 Files: 1 Time: 33s
Restart Phase #:12 Files: 1 Time: 0s
Parallel Phase #:13 Files: 91 Time: 32s
Restart Phase #:14 Files: 1 Time: 0s
Parallel Phase #:15 Files: 111 Time: 45s
Restart Phase #:16 Files: 1 Time: 0s
Serial Phase #:17 Files: 3 Time: 4s
Restart Phase #:18 Files: 1 Time: 0s
Parallel Phase #:19 Files: 32 Time: 33s
Restart Phase #:20 Files: 1 Time: 1s
Serial Phase #:21 Files: 3 Time: 15s
Restart Phase #:22 Files: 1 Time: 1s
Parallel Phase #:23 Files: 23 Time: 111s
Restart Phase #:24 Files: 1 Time: 0s
Parallel Phase #:25 Files: 11 Time: 46s
Restart Phase #:26 Files: 1 Time: 0s
Serial Phase #:27 Files: 1 Time: 2s
Restart Phase #:28 Files: 1 Time: 0s
Serial Phase #:30 Files: 1 Time: 0s
Serial Phase #:31 Files: 257 Time: 46s
Serial Phase #:32 Files: 1 Time: 0s
Restart Phase #:33 Files: 1 Time: 0s
Serial Phase #:34 Files: 1 Time: 11s
Restart Phase #:35 Files: 1 Time: 0s
Restart Phase #:36 Files: 1 Time: 1s
Serial Phase #:37 Files: 4 Time: 83s
Restart Phase #:38 Files: 1 Time: 0s
Parallel Phase #:39 Files: 13 Time: 63s
Restart Phase #:40 Files: 1 Time: 0s
Parallel Phase #:41 Files: 10 Time: 13s
Restart Phase #:42 Files: 1 Time: 0s
Serial Phase #:43 Files: 1 Time: 12s
Restart Phase #:44 Files: 1 Time: 0s
Serial Phase #:45 Files: 1 Time: 12s
Serial Phase #:46 Files: 1 Time: 2s
Restart Phase #:47 Files: 1 Time: 1s
Serial Phase #:48 Files: 1 Time: 349s
Restart Phase #:49 Files: 1 Time: 0s
Serial Phase #:50 Files: 1 Time: 58s
Restart Phase #:51 Files: 1 Time: 1s
Serial Phase #:52 Files: 1 Time: 31s
Restart Phase #:53 Files: 1 Time: 0s
Serial Phase #:54 Files: 1 Time: 656s
Restart Phase #:55 Files: 1 Time: 0s
Serial Phase #:56 Files: 1 Time: 104s
Restart Phase #:57 Files: 1 Time: 1s
Serial Phase #:58 Files: 1 Time: 196s
Restart Phase #:59 Files: 1 Time: 0s
Serial Phase #:60 Files: 1 Time: 962s
Restart Phase #:61 Files: 1 Time: 1s
Serial Phase #:62 Files: 1 Time: 2537s
Restart Phase #:63 Files: 1 Time: 0s
Serial Phase #:64 Files: 1 Time: 5s
Serial Phase #:65 Files: 1 Calling sqlpatch with F:\app\oracle\product\12.1.0.
2\perl\bin\perl.exe -I F:\app\oracle\product\12.1.0.2\RDBMS\ADMIN -I F:\app\orac
le\product\12.1.0.2\sqlpatch F:\app\oracle\product\12.1.0.2\sqlpatch\sqlpatch.pl
-verbose -upgrade_mode_only > F:\backup_12c/catupgrd_datapatch_upgrade.log 2> F
:\backup_12c/catupgrd_datapatch_upgrade.err
returned from sqlpatch
A subdirectory or file F:\app\oracle\product\12.1.0.2\cfgtoollogs\DBCENTQL\upgra
de already exists.
Time: 113s
Serial Phase #:66 Files: 1 Time: 80s
Serial Phase #:68 Files: 1 Time: 0s
Serial Phase #:69 Files: 1 Calling sqlpatch with F:\app\oracle\product\12.1.0.
2\perl\bin\perl.exe -I F:\app\oracle\product\12.1.0.2\RDBMS\ADMIN -I F:\app\orac
le\product\12.1.0.2\sqlpatch F:\app\oracle\product\12.1.0.2\sqlpatch\sqlpatch.pl
-verbose > F:\backup_12c/catupgrd_datapatch_normal.log 2> F:\backup_12c/catupgr
d_datapatch_normal.err
returned from sqlpatch
Time: 106s
Serial Phase #:70 Files: 1
Serial Phase #:71 Files: 1 Time: 1s
Serial Phase #:72 Files: 1 Time: 0s
Serial Phase #:73 Files: 1 Time: 25s
Grand Total Time: 7181s
LOG FILES: (catupgrd*.log)
Upgrade Summary Report Located in:
F:\app\oracle\product\12.1.0.2\cfgtoollogs\DBCENTQL\upgrade\upg_summary.log
Grand Total Upgrade Time: [0d:1h:59m:41s]
F:\app\oracle\product\12.1.0.2\RDBMS\ADMIN>
Post Upgradation
1) start the database in normal mode and Run the Post-Upgrade Status Tool and post fixupscript
post upgradfile pscript
REM Post Upgrade Script Generated on: 2017-04-11 09:15:52
REM Generated by Version: 12.1.0.2 Build: 015
SET ECHO OFF SERVEROUTPUT ON FORMAT WRAPPED TAB OFF LINESIZE 750;
DECLARE
con_name varchar2(40);
BEGIN
execute immediate
'select dbms_preup.get_con_name from sys.dual' into con_name;
IF con_name = 'DBCENTQL' THEN
BEGIN
dbms_output.put_line ('Post Upgrade Fixup Script Generated on 2017-04-11 09:15:52 Version: 12.1.0.2 Build: 015');
dbms_output.put_line ('Beginning Post-Upgrade Fixups...');
END;
BEGIN
dbms_preup.clear_run_flag(FALSE);
END;
BEGIN
-- ***************** Fixup Details ***********************************
-- Name: OLD_TIME_ZONES_EXIST
-- Description: Check for use of older timezone data file
-- Severity: Informational
-- Action: ^^^ MANUAL ACTION REQUIRED ^^^
-- Fix Summary:
-- Update the timezone using the DBMS_DST package after upgrade is complete.
dbms_preup.run_fixup_and_report('OLD_TIME_ZONES_EXIST');
END;
BEGIN
-- ***************** Fixup Details ***********************************
-- Name: NOT_UPG_BY_STD_UPGRD
-- Description: Identify existing components that will NOT be upgraded
-- Severity: Informational
-- Action: ^^^ MANUAL ACTION REQUIRED ^^^
-- Fix Summary:
-- This fixup does not perform any action.
dbms_preup.run_fixup_and_report('NOT_UPG_BY_STD_UPGRD');
END;
BEGIN
dbms_output.put_line ('');
dbms_output.put_line ('**********************************************************************');
dbms_output.put_line (' [Post-Upgrade Recommendations]');
dbms_output.put_line ('**********************************************************************');
dbms_output.put_line ('');
END;
BEGIN
dbms_output.put_line (' *****************************************');
dbms_output.put_line (' ******** Fixed Object Statistics ********');
dbms_output.put_line (' *****************************************');
dbms_output.put_line ('');
dbms_output.put_line ('Please create stats on fixed objects two weeks');
dbms_output.put_line ('after the upgrade using the command:');
dbms_output.put_line (' EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;');
dbms_output.put_line ('');
dbms_output.put_line ('^^^ MANUAL ACTION SUGGESTED ^^^');
dbms_output.put_line ('');
END;
BEGIN dbms_preup.fixup_summary(FALSE); END;
BEGIN
dbms_output.put_line ('*************** Post Upgrade Fixup Script Complete ********************');
END;
END IF;
END;
/
-- Post Upgrade Script Closed At: 2017-04-11 09:16:14
REM __________________________________________________________________________
$ORACLE_HOME/rdbms/admin/utlu121s.sql which provides a summary of the upgrade at the end of the spool log.
It displays the status of the database components in the upgraded database and the time required to complete each component upgrade.
Any errors that occur during the upgrade are listed with each component and must be addressed.
E:\app\oracle\cfgtoollogs\DBCENTQJ\preupgrade\postupgrade_fixups.sql
@%ORACLE_HOME%\/rdbms/admin/utlu121s.sql
SQL> @%ORACLE_HOME%\/rdbms/admin/utlu121s.sql
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
CATCTL REPORT = F:\app\oracle\product\12.1.0.2\cfgtoollogs\DBCENTQL\upgrade\upg_
summary.log
PL/SQL procedure successfully completed.
Oracle Database 12.1 Post-Upgrade Status Tool 04-11-2017 14:03:02
Component Current Version Elapsed Time
Name Status Number HH:MM:SS
Oracle Server UPGRADED 12.1.0.2.0 00:27:33
JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:05:46
Oracle Workspace Manager VALID 12.1.0.2.0 00:01:21
OLAP Analytic Workspace VALID 12.1.0.2.0 00:00:28
OLAP Catalog OPTION OFF 11.2.0.4.0 00:00:00
Oracle OLAP API VALID 12.1.0.2.0 00:00:33
Oracle XDK VALID 12.1.0.2.0 00:00:55
Oracle Text VALID 12.1.0.2.0 00:01:25
Oracle XML Database VALID 12.1.0.2.0 00:09:27
Oracle Database Java Packages VALID 12.1.0.2.0 00:00:19
Oracle Multimedia VALID 12.1.0.2.0 00:03:13
Spatial UPGRADED 12.1.0.2.0 00:15:59
Oracle Application Express VALID 4.2.5.00.08 00:40:41
Final Actions 00:02:45
Post Upgrade 00:03:59
Total Upgrade Time: 01:55:57
PL/SQL procedure successfully completed.
SQL>
SQL> --
SQL> -- Update Summary Table with con_name and endtime.
SQL> --
SQL> UPDATE sys.registry$upg_summary SET reportname = :ReportName,
2 con_name = SYS_CONTEXT('USERENV','CON_NAME'
),
3 endtime = SYSDATE
4 WHERE con_id = -1;
1 row updated.
SQL> commit;
Commit complete.
SQL>
Important: The catuppst.sql script is run as part of the upgrade process unless the upgrade returns errors during the process. Check the log file for "BEGIN catuppst.sql" to verify that catuppst.sql ran during the upgrade process. If catuppst.sql has not run, then proceed to run catuppst.sql as shown in this step. Warning messages are also displayed when running catctl.pl indicating that catuppst.sql was not run during the upgrade.
Run catuppst.sql, located in the $ORACLE_HOME/rdbms/admin directory, to perform upgrade actions that do not require the database to be in UPGRADE mode.
SQL> @catuppst.sql
@%ORACLE_HOME%\/rdbms/admin/catuppst.sql
This script can be run concurrently with utlrp.sql.
Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.
SQL> @utlrp.sql
@%ORACLE_HOME%\/rdbms/admin/utlrp.sql
Check for the integrity of the upgraded database by running dbupgdiag.sql script from the below article:
Check for the integrity of the upgraded database by running dbupgdiag.sql script from the below article:
Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)
If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database, until there is no change in the number of invalid objects.
After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.
1) Execute in the NEW environment AFTER upgrade:
E:\app\oracle\cfgtoollogs\DBCENTQL\preupgrade\postupgrade_fixups.sql
3) complete stats on database
4) enable scheduled backup
5) enable archive mode
6) check invalid objects
SELECT DISTINCT object_name, object_type, owner FROM dba_objects WHERE status='INVALID' ;
SQL> select count(*) from dba_objects where status like '%INVALID%';
7) Please check upgrade logs and make sure catuppst.sql run without any error.
Run utluiobj.sql to validate that all expected packages and classes are valid :
SQL> @$ORACLE_HOME/rdbms/admin/utluiobj.sql
@%ORACLE_HOME%\/rdbms/admin/utluiobj.sql
Collect statistics on fixed object with following command :
SQL> execute dbms_stats.gather_fixed_object_stats
13) Check the database component status to confirm upgrade completion
Connect as SYS user to the database
col comp_id format a10
col comp_name format a30
col version format a10
col status format a8
select substr(comp_id,1,15) comp_id,substr(comp_name,1,30) comp_name,substr(version,1,10) version,status from dba_registry
copy listener.ora and tnsname.ora from old home to new home and check remote connectivity.
No comments:
Post a Comment