Oracle upgrade steps

Upgrade steps
==============

Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1) (Doc ID 1503653.1)



1. Check Concurrent statistics gathering is set to FALSE

 SELECT dbms_stats.get_prefs('CONCURRENT') from dual;

if not, then

BEGIN
DBMS_STATS.SET_GLOBAL_PREFS('CONCURRENT','FALSE');
END;
/

2. Check for invalid objects and run utlrp.sql

3. Copy  preupgrd.sql and utluppkg.sql into temporary directory and execute preupgrd.sql script on database and fix the recommendations

4. Check parallel settings

5. Check for Events and hidden parameters and disable before upgrade

SELECT (translate(value,chr(13)||chr(10),' ')) FROM sys.v$parameter2 WHERE  UPPER(name) ='EVENT' AND  isdefault='FALSE';
SELECT (translate(value,chr(13)||chr(10),' ')) from sys.v$parameter2 WHERE UPPER(name) = '_TRACE_EVENTS' AND isdefault='FALSE';
SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';


6. Check Dependency on Network utility packages

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');

7. Gather Dictionary statisitics --> DONE

8. Check the recovery pending status

SELECT * FROM v$recover_file;

9. Purge DBA RECYCLE BIN

PURGE DBA_RECYCLEBIN


10. Shutdown the listener  --> DONE
11. Shutdown database --> DONE

12. Backup pfile,spfile, pwd file and update Pfile with following and place it in new Oracle home

#Recommended parameters for 12c
_optimizer_reduce_groupby_key =FALSE
_optimizer_aggr_groupby_elim=FALSE
_optimizer_unnest_scalar_sq=FALSE
_optimizer_coalesce_subqueries=FALSE
_use_single_log_writer=TRUE
#Parameters for parallelism
parallel_adaptive_multi_user=FALSE
parallel_degree_limit=20
parallel_degree_policy='AUTO'
parallel_force_local=TRUE
parallel_max_servers=32
parallel_min_servers=2
parallel_min_time_threshold=60
parallel_servers_target=28
parallel_threads_per_cpu=1
#


13. Update Environment variables for the oracle user to new userid and Oracle home and change the owner name of datafiles and other files

14. startup upgrade

15. Increase TEMP tablespace size to atleast 1.5GB after Upgrade resize to actual size

16. cd $ORACLE_HOME/rdbms/admin
    $ORACLE_HOME/perl/bin/perl catctl.pl -n  2 -l $ORACLE_HOME/diagnostics catupgrd.sql

17. Startup
18. Run utlu121s.sql --> DONE
19. Run utlrp.sql --> DONE
20. Modify Listener home and start it, following should be updated in sqlnet.ora file

for windows server after upgrade change below in sqlnet.ora
SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

SQLNET.INBOUND_CONNECT_TIMEOUT = 180
SQLNET.EXPIRE_TIME = 30

# allows inbound connections from Oracle Clients version >= 12.1
SQLNET.ALLOWED_LOGON_VERSION_SERVER = 12a
# allows outbound connections to Oracle Database Servers version >= 11.x (incl. PSU/CPU October 2012 or later)
SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 12

# Network Encryption (for inbound connections; acting as Database Server)
SQLNET.ENCRYPTION_SERVER = REQUIRED
SQLNET.ENCRYPTION_TYPES_SERVER = (AES256)
# Network Integrity (for inbound connections; acting as Database Server)
SQLNET.CRYPTO_CHECKSUM_SERVER = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_SERVER = (SHA1)

# Network Encryption (for outbound connections; acting as Database Client)
SQLNET.ENCRYPTION_CLIENT = REQUIRED
SQLNET.ENCRYPTION_TYPES_CLIENT = (AES256)
# Network Integrity (for outbound connections; acting as Database Client)
SQLNET.CRYPTO_CHECKSUM_CLIENT = REQUIRED
SQLNET.CRYPTO_CHECKSUM_TYPES_CLIENT = (SHA1)

21. Start db from spfile --> DONE
22. Rename the old ORACLE_HOME, pfile, spfile and listener file of Old ORACLE_HOME and Run Cockpit scan




/sysmgmt/opt/cfengine/sbin/cfagent -v -q -K



TIMEZONE UPGRADE
================
1. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

2. SELECT version FROM v$timezone_file;

3. purge dba_recyclebin;

4. EXEC DBMS_APPLICATION_INFO.SET_CLIENT_INFO('upg_tzv');

5. alter session set "_with_subquery"=materialize;

6. alter session set "_simple_view_merging"=TRUE;

7.  EXEC DBMS_DST.BEGIN_PREPARE(18);

8. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

9. TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;

10. TRUNCATE TABLE sys.dst$affected_tables;

11. TRUNCATE TABLE sys.dst$error_table;

12. set serveroutput on

13. BEGIN
DBMS_DST.FIND_AFFECTED_TABLES
(affected_tables => 'sys.dst$affected_tables',
log_errors => TRUE,
log_errors_table => 'sys.dst$error_table');
END;
/

14. SELECT * FROM sys.dst$affected_tables;

15. SELECT * FROM sys.dst$error_table;

16. SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1883';

17. SELECT * FROM sys.dst$error_table where ERROR_NUMBER= '1878';

18. SELECT * FROM sys.dst$error_table where ERROR_NUMBER not in ('1878','1883');

19. EXEC DBMS_DST.END_PREPARE;

20. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
 FROM DATABASE_PROPERTIES
 WHERE PROPERTY_NAME LIKE 'DST_%'
 ORDER BY PROPERTY_NAME;

21. shutdown immediate;
22. startup upgrade
23. set serveroutput on;
24. set timing on;

--> execute steps 1,3,9,10,11, 4~6

25. EXEC DBMS_DST.BEGIN_UPGRADE(18);

26. SELECT * FROM sys.dst$error_table;

27. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;

28. Shutdown/startup

29. alter session set "_with_subquery"=materialize;
    alter session set "_simple_view_merging"=TRUE;

30. set serveroutput on

VAR numfail number
BEGIN
DBMS_DST.UPGRADE_DATABASE(:numfail,
parallel => TRUE,
log_errors => TRUE,
log_errors_table => 'SYS.DST$ERROR_TABLE',
log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
error_on_overlap_time => FALSE,
error_on_nonexisting_time => FALSE);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
END;
/

SELECT * FROM sys.dst$error_table;

31. VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/

32. SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME;


33.SELECT version FROM v$timezone_file;

34. select TZ_VERSION from registry$database;

35. If output of above 2 queries differ then
update registry$database set TZ_VERSION = (select version FROM v$timezone_file);

36. Commit;


==========================================================

Patching Steps:
============

export ORACLE_HOME=/oracle/product/12.1.0.2/db_1
export PATH=$ORACLE_HOME/bin:$PATH

oracle@sdbt:/oradata/software/23054246> echo $ORACLE_HOME
/oracle/product/12.1.0.2/db_1
oracle@sdbt:/oradata/software/23054246> echo $PATH
/oracle/product/12.1.0.2/db_1/bin:/home/oracle/bin:/usr/local/bin:/usr/bin:/bin:/usr/bin/X11:/usr/X11R6/bin:/usr/games:/usr/lib/mit/bin:/usr/lib/mit/sbin:/oracle/product/12.1.0/db_1/bin
oracle@sdbt:/oradata/software/23054246>


oracle@sdbt:/oradata/software/23054246> /oracle/product/12.1.0.2/db_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
/oracle/app/product/12.1.0.2/dbhome_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./

/oracle/app/product/12.1.0.2/dbhome_1/OPatch/opatch prereq CheckConflictAgainstOHWithDetail -ph ./
/oracle/app/product/12.1.0.2/dbhome_1/OPatch/opatch apply

Oracle Interim Patch Installer version 12.1.0.1.3
Copyright (c) 2016, Oracle Corporation.  All rights reserved.

PREREQ session

Oracle Home       : /oracle/product/12.1.0.2/db_1
Central Inventory : /oracle/oraInventory
   from           : /oracle/product/12.1.0.2/db_1/oraInst.loc
OPatch version    : 12.1.0.1.3
OUI version       : 12.1.0.2.0
Log file location : /oracle/product/12.1.0.2/db_1/cfgtoollogs/opatch/opatch2016-08-03_12-44-17PM_1.log

Invoking prereq "checkconflictagainstohwithdetail"

Prereq "checkConflictAgainstOHWithDetail" passed.

OPatch succeeded.
oracle@sdbt:/oradata/software/23054246>

create or replace directory ORACLE_BASE as '/oracle';
create or replace directory ORACLE_HOME as '/oracle/product/12.1.0.2/db_1';

Comments

Popular Posts