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;
No comments:
Post a Comment