A database can be upgraded either using DBUA or Manual method. It is advisable to go with a manual method for Production database so that troubleshooting will be easy.
Requirements and recommendations for target ORACLE_HOME
- Verify the whether your operating system is certified for 12.2. Click here to launch certification portal
- Install 12.2.0.1, You can refer to the Installation steps below: Oracle Database 12c Release 2 (12.2) installation on Linux 7
- Download and install latest PSU if any
- Copy spfile or pfile from source ORACLE_HOME (under $ORACLE_HOME/dbs) to target home
- Remove any _parameter, obsolete and deprecated parameters in pfile
- Note min value of COMPATIBLE parameter to upgrade 12.2 is “11.2.0”, make sure you have COMPATIBLE parameter is set to 11.2.0 or greater
Upgrade Path for 12.2 Oracle database
Minimum version of the database that can be directly upgraded to Oracle 12c Release 2 (12.2)
Intermediate upgrades needs to be carried for following releases
- If you are upgrading from release 11.2.0.2 or 11.1.0.7, then you must first upgrade to Oracle Database 11g release 2 (11.2.0.4).
- If you are upgrading from release 10.2.0.2, 10.2.0.3, 10.2.0.4,10.2.0.5 or 10.1.0.5, then you must first upgrade to release 11.2.0.4 or 12.1.0.2.
- If you are upgrading from release 9.2.0.8, then you must first upgrade to a sequence of intermediate Oracle Database releases:
Current ORACLE_HOME= /u01/app/oracle/product/11.2.0.4/db_1
Target ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1
Requirements and recommendations for Source database
1.CHECK THE INVALID COMPONENT( ALL SHOULD BE VALID)
orcl >alter session set nls_date_format='DD-MON-YYYY HH24:mi:ss';
col object_name for a35
col owner for a30
col object_type for a20
orcl >select object_name,owner,object_type,LAST_DDL_TIME, count(*) from dba_objects where status !='VALID' group by owner,object_type,object_name,LAST_DDL_TIME;
no rows selected
4. Check the integrity of the database by running dbupgdiag.sql
orcl >set lines 2000 pages 50
col COMP_NAME for a40
col status for a20
col comp_id for a20
col version for a30
select comp_id, comp_name, status, version from dba_registry;
COMP_ID COMP_NAME STATUS VERSION
-------------------- ---------------------------------------- -------------------- ------------------------------
OWB OWB VALID 11.2.0.4.0
APEX Oracle Application Express VALID 3.2.1.00.12
EM Oracle Enterprise Manager VALID 11.2.0.4.0
AMD OLAP Catalog VALID 11.2.0.4.0
SDO Spatial VALID 11.2.0.4.0
ORDIM Oracle Multimedia VALID 11.2.0.4.0
XDB Oracle XML Database VALID 11.2.0.4.0
CONTEXT Oracle Text VALID 11.2.0.4.0
EXF Oracle Expression Filter VALID 11.2.0.4.0
RUL Oracle Rules Manager VALID 11.2.0.4.0
OWM Oracle Workspace Manager VALID 11.2.0.4.0
CATALOG Oracle Database Catalog Views VALID 11.2.0.4.0
CATPROC Oracle Database Packages and Types VALID 11.2.0.4.0
JAVAVM JServer JAVA Virtual Machine VALID 11.2.0.4.0
XML Oracle XDK VALID 11.2.0.4.0
CATJAVA Oracle Database Java Packages VALID 11.2.0.4.0
APS OLAP Analytic Workspace VALID 11.2.0.4.0
XOQ Oracle OLAP API VALID 11.2.0.4.0
18 rows selected.
orcl >
- Ensure that all database components provided by Oracle are VALID in the source database prior to starting the upgrade.
2. CHECK THE INVALID OBJECTS( ALL SHOULD BE VALID)
col object_name for a35
col owner for a30
col object_type for a20
orcl >select object_name,owner,object_type,LAST_DDL_TIME, count(*) from dba_objects where status !='VALID' group by owner,object_type,object_name,LAST_DDL_TIME;
no rows selected
- Ensure that all database objects provided by Oracle are VALID in the source database prior to starting the upgrade.
3. Check duplicate objects owned by system and sys
orcl >select object_name, object_type
from dba_objects
where object_name||object_type in
(select object_name||object_type
from dba_objects
where owner = 'SYS')
and owner = 'SYSTEM';
OBJECT_NAME OBJECT_TYPE
----------------------------------- --------------------
AQ$_SCHEDULES TABLE
AQ$_SCHEDULES_PRIMARY INDEX
DBMS_REPCAT_AUTH PACKAGE
DBMS_REPCAT_AUTH PACKAGE BODY
4 rows selected.
- The above duplicate objects are permissible duplicate objects. If you found any other objects other than these four, then those need to be cleaned up.
orcl >@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.
5. Run utlrp.sql to validate invalid objects
orcl >@?/rdbms/admin/utlrp.sql
If the Oracle owned objects cannot be validated then it is suggested to open an SR to investigate why the object/objects cannot be validated.
After validating the invalid objects, re-run dbupgdiag.sql in the database once again and make sure that everything is fine.
5. Run utlrp.sql to validate invalid objects
orcl >@?/rdbms/admin/utlrp.sql
If the Oracle owned objects cannot be validated then it is suggested to open an SR to investigate why the object/objects cannot be validated.
After validating the invalid objects, re-run dbupgdiag.sql in the database once again and make sure that everything is fine.
6. Run preupgrade tool
$Earlier_release_Oracle_home/jdk/bin/java -jar $New_release_Oracle_home/rdbms/admin/preupgrade.jar FILE TEXT DIR output_dir
e.g,
/u01/app/oracle/product/11.2.0.4/db_1/jdk/bin/java -jar /u01/app/oracle/product/12.2.0.1/db_1/rdbms/admin/preupgrade.jar FILE TEXT DIR /home/oracle/upgrade
/home/oracle/upgrade/preupgrade.log
/home/oracle/upgrade/preupgrade_fixups.sql
/home/oracle/upgrade/postupgrade_fixups.sql
FILE - Use this option to direct output to a file
TEXT - Use this option to specify log should be in Text format (other option is to have XML output)
DIR - Logs will be created under <output_dir>
It is recommended to execute pre-upgrade fixup script, if any, which are AUTO FIXABLE
7. Dependencies on Network Utility Packages
orcl > 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');
no rows selected
8. Create a script for DBLINKs (in case the database has to be downgraded again).
orcl > 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#;
9. Check the timezone version:
orcl >col version for 9999999
orcl >SELECT version FROM v$timezone_file;
VERSION
--------
14
orcl > 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');
no rows selected
8. Create a script for DBLINKs (in case the database has to be downgraded again).
orcl > 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#;
orcl >col version for 9999999
orcl >SELECT version FROM v$timezone_file;
VERSION
--------
14
1 row selected.
For 12.2, the new timezone is 26. So after the db upgrade is completed, we will upgrade the dst timezone from 14 to 26.
orcl > EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
11. Verify That Materialized View Refreshes Have Completed Before Upgrading
orcl >SELECT local_tran_id FROM dba_2pc_pending;
orcl > EXECUTE dbms_transaction.purge_lost_db_entry('');
orcl > COMMIT;
15. Purge the Database Recycle Bin Before Upgrading :
orcl >PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.
16. Synchronize the Standby Database with the Primary Database When Upgrading
a. To check if a standby database exists and to synchronize it:
orcl >SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)
FROM v$parameter
WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%'; 2 3
no rows selected
orcl >
b. If the query in the previous step returns a row, then synchronize the standby database with the primary database.
17. Disable all batch and cron jobs
Take backup of crontab and comment the same.
18. Disable dbms_schduler jobs: ( Optional)
--- Disable the scheduled jobs by using below command
orcl > execute dbms_scheduler.disable('DRA_REEVALUATE_OPEN_FAILURES);
19. Verify SYS and SYSTEM Default tablespace (Both should be system tablespace)
orcl >SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM');
USERNAME DEFAULT_TABLESPACE
---------------------------- ----------------------
SYSTEM SYSTEM
SYS SYSTEM
2 rows selected.
If DEFAULT_TABLESPACE is anything other than SYSTEM tablespace, modify the default tablespace for user SYS and SYSTEM to SYSTEM by using the command below:
orcl >ALTER user SYS default tablespace SYSTEM;
orcl >ALTER user SYSTEM default tablespace SYSTEM;
20. Check whether database has any externally authenticated SSL users
b. Create a guaranteed flashback restore point
select * from v$restore_point;
First, while still on the 11.2. release, obtain the current setting:
When 'concurrent statistics gathering' is not set to 'FALSE', change the value to 'FALSE before the upgrade.
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','FALSE');
END;
/
For 12.2, the new timezone is 26. So after the db upgrade is completed, we will upgrade the dst timezone from 14 to 26.
10. Optimizer Statistics
Oracle recommends collecting statistics the night before starting the upgrade to decrease the amount of downtime.
Oracle recommends collecting statistics the night before starting the upgrade to decrease the amount of downtime.
orcl > EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
PL/SQL procedure successfully completed.
11. Verify That Materialized View Refreshes Have Completed Before Upgrading
orcl > 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; 2
no rows selected
12. Ensure That No Files Need Media Recovery Before Upgrading
orcl > SELECT * FROM v$recover_file;
where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8; 2
no rows selected
12. Ensure That No Files Need Media Recovery Before Upgrading
orcl > SELECT * FROM v$recover_file;
no rows selected
13. Ensure That No Files Are in Backup Mode Before Upgrading
orcl > SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
no rows selected
14. Resolve Outstanding Distributed Transactions Before Upgrading
orcl > SELECT * FROM dba_2pc_pending;
no rows selected
If the query in the previous step returns any rows, then issue the following
statements:
13. Ensure That No Files Are in Backup Mode Before Upgrading
orcl > SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
no rows selected
14. Resolve Outstanding Distributed Transactions Before Upgrading
orcl > SELECT * FROM dba_2pc_pending;
no rows selected
If the query in the previous step returns any rows, then issue the following
statements:
orcl >SELECT local_tran_id FROM dba_2pc_pending;
orcl > EXECUTE dbms_transaction.purge_lost_db_entry('');
orcl > COMMIT;
15. Purge the Database Recycle Bin Before Upgrading :
orcl >PURGE DBA_RECYCLEBIN;
DBA Recyclebin purged.
a. To check if a standby database exists and to synchronize it:
orcl >SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)
FROM v$parameter
WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%'; 2 3
no rows selected
orcl >
b. If the query in the previous step returns a row, then synchronize the standby database with the primary database.
- Make sure all the logs are transported to the standby server after a final log switch in the primary.
- Start the recovery of the standby database with the NODELAY option.
17. Disable all batch and cron jobs
Take backup of crontab and comment the same.
18. Disable dbms_schduler jobs: ( Optional)
orcl >select owner,JOB_NAME,ENABLED,state from dba_scheduler_jobs;
OWNER JOB_NAME ENABL STATE
------------------------------ ------------------------------------------- ------------ ---------------
SYS XMLDB_NFS_CLEANUP_JOB FALSE DISABLED
SYS SM$CLEAN_AUTO_SPLIT_MERGE TRUE SCHEDULED
SYS RSE$CLEAN_RECOVERABLE_SCRIPT TRUE SCHEDULED
SYS FGR$AUTOPURGE_JOB FALSE DISABLED
SYS BSLN_MAINTAIN_STATS_JOB TRUE SCHEDULED
SYS DRA_REEVALUATE_OPEN_FAILURES TRUE SCHEDULED
SYS HM_CREATE_OFFLINE_DICTIONARY FALSE DISABLED
SYS ORA$AUTOTASK_CLEAN TRUE SCHEDULED
SYS FILE_WATCHER FALSE DISABLED
SYS PURGE_LOG TRUE SCHEDULED
ORACLE_OCM MGMT_STATS_CONFIG_JOB TRUE SCHEDULED
ORACLE_OCM MGMT_CONFIG_JOB TRUE SCHEDULED
EXFSYS RLM$SCHDNEGACTION TRUE SCHEDULED
EXFSYS RLM$EVTCLEANUP TRUE SCHEDULED
14 rows selected.
orcl > execute dbms_scheduler.disable('DRA_REEVALUATE_OPEN_FAILURES);
19. Verify SYS and SYSTEM Default tablespace (Both should be system tablespace)
orcl >SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM');
USERNAME DEFAULT_TABLESPACE
---------------------------- ----------------------
SYSTEM SYSTEM
SYS SYSTEM
2 rows selected.
If DEFAULT_TABLESPACE is anything other than SYSTEM tablespace, modify the default tablespace for user SYS and SYSTEM to SYSTEM by using the command below:
orcl >ALTER user SYS default tablespace SYSTEM;
orcl >ALTER user SYSTEM default tablespace SYSTEM;
20. Check whether database has any externally authenticated SSL users
orcl >SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL
AND password = 'GLOBAL';
no rows selected
21. Location of datafiles, redo logs and control files
Note the location of datafiles, redo logs and control files. Also take a backup of all configuration files like listener.ora, tnsnames.ora, etc. from $ORACLE_HOME.
orcl > SELECT name FROM v$controlfile;
orcl >SELECT file_name FROM dba_data_files;
orcl > SELECT group#, member FROM v$logfile;
22. Remove EM repository (optional)
----Enterprise Manager Database Control is superseded in 12c by Oracle Enterprise Manager Express . Therefore no repository is needed anymore .
---Run the below script
$emctl stop dbcontrol
orcl > @ ?/rdbms/admin/emremove.sql
23. Run olspreupgrade.sql (optional)
Copy the olspreupgrde.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.
- It prepares the move of AUD$ table from SYSTEM to SYS.
- It processes the audit records to minimize downtime.
- It moves records to an Interim temporary table.
orcl >@?/rdbms/admin/olspreupgrade.sql
Function created.
No errors.
Function created.
No errors.
Function created.
No errors.
****THIS SCRIPT IS NEEDED ONLY IF ORACLE LABEL SECURITY OR DATABASE VAULT ARE CONFIGURED*****
PL/SQL procedure successfully completed.
No errors.
****THIS SCRIPT IS NEEDED ONLY IF ORACLE LABEL SECURITY OR DATABASE VAULT ARE CONFIGURED*****
PL/SQL procedure successfully completed.
No errors.
orcl > SELECT OWNER,TRIGGER_NAME FROM DBA_TRIGGERS WHERE TRIM(BASE_OBJECT_TYPE)='DATABASE'
AND OWNER NOT IN (SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE PRIVILEGE='ADMINISTER DATABASE TRIGGER');
OWNER TRIGGER_NAME
------------------------------ ------------------------------
SYSMAN MGMT_STARTUP
1 row selected.
26. Disable default password_verify_function
orcl > alter profile default limit password_verify_function null;
a. Take database full backup.
RUN
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE FORMAT 'controlfile location and name';
}
AND password = 'GLOBAL';
no rows selected
21. Location of datafiles, redo logs and control files
Note the location of datafiles, redo logs and control files. Also take a backup of all configuration files like listener.ora, tnsnames.ora, etc. from $ORACLE_HOME.
orcl > SELECT name FROM v$controlfile;
orcl >SELECT file_name FROM dba_data_files;
orcl > SELECT group#, member FROM v$logfile;
22. Remove EM repository (optional)
----Enterprise Manager Database Control is superseded in 12c by Oracle Enterprise Manager Express . Therefore no repository is needed anymore .
---Run the below script
$emctl stop dbcontrol
orcl > @ ?/rdbms/admin/emremove.sql
Copy the olspreupgrde.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.
- It prepares the move of AUD$ table from SYSTEM to SYS.
- It processes the audit records to minimize downtime.
- It moves records to an Interim temporary table.
orcl >@?/rdbms/admin/olspreupgrade.sql
Function created.
No errors.
Function created.
No errors.
Function created.
No errors.
****THIS SCRIPT IS NEEDED ONLY IF ORACLE LABEL SECURITY OR DATABASE VAULT ARE CONFIGURED*****
PL/SQL procedure successfully completed.
No errors.
****THIS SCRIPT IS NEEDED ONLY IF ORACLE LABEL SECURITY OR DATABASE VAULT ARE CONFIGURED*****
PL/SQL procedure successfully completed.
No errors.
orcl >
24. Review and Remove any unnecessary hidden/underscore parameters
orcl > SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;
24. Review and Remove any unnecessary hidden/underscore parameters
orcl > SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;
no rows selected
25. Disable any custom triggers that would fire before/after DDL and enable them after the upgrade is complete.
orcl > SELECT OWNER,TRIGGER_NAME FROM DBA_TRIGGERS WHERE TRIM(BASE_OBJECT_TYPE)='DATABASE'
AND OWNER NOT IN (SELECT GRANTEE FROM DBA_SYS_PRIVS WHERE PRIVILEGE='ADMINISTER DATABASE TRIGGER');
OWNER TRIGGER_NAME
------------------------------ ------------------------------
SYSMAN MGMT_STARTUP
1 row selected.
You can disable triggers using this sql command
orcl > alter trigger SYSMAN.MGMT_STARTUP disable;
orcl > select * from dba_profiles where RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION' and PROFILE='DEFAULT';
PROFILE RESOURCE_NAME RESOURCE LIMIT
------------------------------ -------------------------------- -------- ----------------------------------------
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD VERIFY_FUNCTION_11G
1 row selected.
Profile altered.
orcl >select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
------------------------------ ------------------------------ ------------------------ ------------------------------
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
orcl >select * from dba_profiles where profile='DEFAULT' and RESOURCE_NAME='PASSWORD_VERIFY_FUNCTION';
PROFILE RESOURCE_NAME RESOURCE_TYPE LIMIT
------------------------------ ------------------------------ ------------------------ ------------------------------
DEFAULT PASSWORD_VERIFY_FUNCTION PASSWORD NULL
27. Move the spfile and password file from 11g oracle_home/dbs location to 12cHome/dbs location and copy all network files as well.
28. Before starting the upgrade,
{
ALLOCATE CHANNEL chan_name TYPE DISK;
BACKUP DATABASE FORMAT 'some_backup_directory%U' TAG before_upgrade;
BACKUP CURRENT CONTROLFILE FORMAT 'controlfile location and name';
}
b. Create a guaranteed flashback restore point
enable flashback and create restore point, so that in case upgrade fails, we can restore it using guarantee restore point.
Enable flashback
orcl >alter system set db_recovery_file_dest_size=10G scope=both;
System altered.
orcl >alter database flashback on;
Database altered.
orcl >CREATE RESTORE POINT BEFORE_UPGRADE GUARANTEE FLASHBACK DATABASE;
Restore point created.
29. Backup spfile.
orcl >create pfile='/home/oracle/upgrade/pfileorcl.ora' from spfile;
File created.
30. If you are using a cluster database, set the parameter CLUSTER_DATABASE=FALSE during the upgrade and set it back to true after the upgrade.
orcl >alter system set cluster_database=false scope=spfile sid='*';
System altered.
31. Before starting the manual upgrade it is required to change the preference for 'concurrent statistics gathering' on the current release if the current setting is not set to 'FALSE'
First, while still on the 11.2. release, obtain the current setting:
orcl >SELECT dbms_stats.get_prefs('CONCURRENT') from dual;
DBMS_STATS.GET_PREFS('CONCURRENT')
--------------------------------------------------------------------------------
FALSE
BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','FALSE');
END;
/
UPGRADE DATABASE to 12.2
1. Stop the listener and shutdown the database from 11g home.
lsnrctl stop listener
orcl > shu immediate
2. Update the following environment variables point to the Oracle 12c Release 1 (12.1) directories
4. start the database in upgrade mode.
lsnrctl stop listener
orcl > shu immediate
export ORACLE_BASE=/u01/app/oracle
export ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1
export PATH=$ORACLE_HOME/bin:$PATH
export LD_LIBRARY_PATH=$ORACLE_HOME/lib:$LD_LIBRARY_PATH
export SHLIB_PATH=$ORACLE_HOME/lib:$SHLIB_PATH
[oracle@joshi upgrade]$ env | grep ORA
ORACLE_SID=orcl
ORACLE_BASE=/u01/app/oracle
ORACLE_HOME=/u01/app/oracle/product/12.2.0.1/db_1
3. Update the oratab entry to set the new ORACLE_HOME pointing to ORCL and disable automatic startup.
cat /etc/oratab
orcl:/u01/app/oracle/product/12.2.0.1/db_1:Y
#orcl:/u01/app/oracle/product/11.2.0.4/db_1:Y
cat /etc/oratab
orcl:/u01/app/oracle/product/12.2.0.1/db_1:Y
#orcl:/u01/app/oracle/product/11.2.0.4/db_1:Y
orcl > startup UPGRADE
5. Run upgrade
./dbupgrade
6. Run the Post-Upgrade Status Tool
Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu122s.sql which provides a summary of the upgrade
orcl > STARTUP
orcl > @$ORACLE_HOME/rdbms/admin/utlu122s.sql
Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu122s.sql which provides a summary of the upgrade
orcl > STARTUP
orcl > @$ORACLE_HOME/rdbms/admin/utlu122s.sql
Verify the upgrade log whether catuppst.sql has been executed or not. If not, execute it manually from new ORACLE_HOME, located at $ORACLE_HOME/rdbms/admin directory
6.a Run catuppst.sql
orcl >@?/rdbms/admin/catuppst.sql
orcl >@?/rdbms/admin/utlrp.sql
Now Proceed with Post Upgrade Steps:
8. UPGRADE TIME ZONE
d) validate the time_zone(It should be 26)
11.
unzip the file
[oracle@joshi upgrade]$ unzip DBMS_DST_scriptsV1.9.zip
Archive: DBMS_DST_scriptsV1.9.zip
creating: DBMS_DST_scriptsV1.9/
inflating: DBMS_DST_scriptsV1.9/countstarTSTZ.sql
inflating: DBMS_DST_scriptsV1.9/countstatsTSTZ.sql
inflating: DBMS_DST_scriptsV1.9/upg_tzv_apply.sql
inflating: DBMS_DST_scriptsV1.9/upg_tzv_check.sql
[oracle@joshi upgrade]$
a) Run countstatsTSTZ.sql
b) Run upg_tzv_check.sql
c) if upg_tzv_check.sql has run sucessfully , run upg_tzv_apply.sql
orcl > SELECT version FROM v$timezone_file;
VERSION
----------
26
9. Run post upgrade fixups
orcl >@/home/oracle/upgrade/postupgrade_fixups.sql
10. Change the ORACLE_HOME to 12.2 in listener.ora file.
a) Enable password verify function which was disabled prior to upgrade.
orcl >alter profile default limit password_verify_function VERIFY_FUNCTION_11G;
Profile altered.
b) Enable trigger which was disabled prior to upgrade
orcl >alter trigger SYSMAN.MGMT_STARTUP enable;
Trigger altered.
orcl >alter profile default limit password_verify_function VERIFY_FUNCTION_11G;
Profile altered.
b) Enable trigger which was disabled prior to upgrade
orcl >alter trigger SYSMAN.MGMT_STARTUP enable;
Trigger altered.
13. Run $ORACLE_HOME/rdbms/admin/utlrp.sql script
14. drop restore point BEFORE_UPGRADE;
orcl > drop restore point BEFORE_UPGRADE;
Restore point dropped.
NOTE- Database cannot be downgraded once compatible parameter is updated.
15. Update the compatible parameter and restart database.
Perform a backup of your database before you raise the COMPATIBLE initialization parameter (optional).
14. drop restore point BEFORE_UPGRADE;
orcl > drop restore point BEFORE_UPGRADE;
Restore point dropped.
orcl > alter system set compatible='12.2.0.1' scope=spfile;
IF UPGRADE FAILS:
1. Shutdown immediate;
2. set ORACLE_HOME to 11g home
7. drop restore point before_upgrade;
orcl > shutdown immediate;
orcl > startup
16. If you are using Oracle Clusterware, then you must upgrade the Oracle Clusterware keys for the database.
srvctl upgrade database -db orcl -o ORACLE_HOME
17. Check for the SQLNET.ALLOWED_LOGON_VERSION Parameter Behavior
Connections to Oracle Database from clients earlier than release 10g fail with the error ORA-28040: No matching authentication protocol.
Add below to sqlnet.ora file under RDBMS HOME.
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=11
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
18. Update the new home in cron scripts. If you are using OEM, update the targets with 12.2 home.Connections to Oracle Database from clients earlier than release 10g fail with the error ORA-28040: No matching authentication protocol.
Add below to sqlnet.ora file under RDBMS HOME.
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=11
SQLNET.ALLOWED_LOGON_VERSION_SERVER=11
IF UPGRADE FAILS:
1. Shutdown immediate;
2. set ORACLE_HOME to 11g home
3. Startup mount.
4. select * from v$restore_point;
5. flashback database to restore point before_upgrade.
6. alter database open resetlogs;
