12cR2 Migration 하면서 변경 파라미터

11g -> 12cR2 Migration 하면서, 변경한 소소한 파라미터 

----------------------------------------------------------------------------------------------------------------

가. alert log 에 "Resize operation completed for file#" 방지 

----------------------------------------------------------------------------------------------------------------

참조 

: Resize Operation Completed For File# (문서 ID 1982901.1)

요약 

: 이슈 발생 시, 분석을 위해서 설정, 이행 시, 아래와 같이 해당 기능 Off 시킬것

  SQL>alter system set "_disable_file_resize_logging"=TRUE ;

 
2018-02-26T14:55:14.210194+09:00
Resize operation completed for file# 10, old size 15155200K, new size 15257600K
Resize operation completed for file# 9, old size 15052800K, new size 15155200K
Resize operation completed for file# 10, old size 15257600K, new size 15360000K
Resize operation completed for file# 9, old size 15155200K, new size 15257600K
Resize operation completed for file# 10, old size 15360000K, new size 15462400K
Resize operation completed for file# 9, old size 15257600K, new size 15360000K
2018-02-26T14:55:15.203130+09:00
Resize operation completed for file# 10, old size 15462400K, new size 15564800K
Resize operation completed for file# 9, old size 15360000K, new size 15462400K
Resize operation completed for file# 10, old size 15564800K, new size 15667200K
Resize operation completed for file# 9, old size 15462400K, new size 15564800K
Resize operation completed for file# 10, old size 15667200K, new size 15769600K
2018-02-26T14:55:19.324447+09:00
ALTER SYSTEM SET _disable_file_resize_logging=TRUE SCOPE=BOTH;
... 더 이상 alert log 에 안찍힘 

----------------------------------------------------------------------------------------------------------------

나. alert log 에 "Using deprecated SQLNET.ALLOWED_LOGON_VERSION parameter" 방지 

----------------------------------------------------------------------------------------------------------------

참조 

: 12c Database Alert.log File Shows The Message: Using Deprecated SQLNET.ALLOWED_LOGON_VERSION Parameter (문서 ID 2111876.1)

요약

: SQLNET.ALLOWED_LOGON_VERSION --> 폐지예정, 가능한 사용하지 말것 

  새로운 파라미터로 대체 할것

  SQLNET.ALLOWED_LOGON_VERSION_SERVER = n

  SQLNET.ALLOWED_LOGON_VERSION_CLIENT = n 

  

<< 참고 >> 

Deprecated(폐지예정 - 사용은 가능하지만, 미승인)

Obsolete(폐지된 - 사용불가 )

Desupproted(폐지된)

 
cat alert.log
2018-02-26T14:57:11.359331+09:00
Using deprecated SQLNET.ALLOWED_LOGON_VERSION parameter.
Using deprecated SQLNET.ALLOWED_LOGON_VERSION parameter.
 
cat sqlnet.ora
# sqlnet.ora Network Configuration File: /app/oracle/product/12.2.0.1/network/admin/sqlnet.ora
# Generated by Oracle configuration tools.
 
NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)
## SQLNET.ALLOWED_LOGON_VERSION = 9
SQLNET.ALLOWED_LOGON_VERSION_SERVER = 11
SQLNET.ALLOWED_LOGON_VERSION_CLIENT = 11
 
 
 

----------------------------------------------------------------------------------------------------------------

다. alert log 에 "ORA-12012 , ORA-20001 AND ORA-06512 FROM ORA$AT_OS_OPT_SY_XXX" 방지 

----------------------------------------------------------------------------------------------------------------

참조 

: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/readm/dbca-known-bugs.html#GUID-DBD9265E-2964-42AA-A534-FB4B9F35BB72

요약 

: 12CR2 DBCA Bug 

  EXEC dbms_stats.init_package();

cat alert
Errors in file
/u01/app/oracle/diag/rdbms/orcl/ORCL1/trace/ORCL1_j000_73185.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_OS_OPT_SY_936"
ORA-20001: Statistics Advisor: Invalid task name for the current user
ORA-06512: at "SYS.DBMS_STATS", line 47207
ORA-06512: at "SYS.DBMS_STATS_ADVISOR", line 882
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 20059
ORA-06512: at "SYS.DBMS_STATS_INTERNAL", line 22201
ORA-06512: at "SYS.DBMS_STATS", line 47197
2017-03-13T00:47:21.394481+00:00
 
Workaround
-- Advisor Task 없음 확인 
select name, ctime, how_created
from sys.wri$_adv_tasks
where owner_name = 'SYS'
and name in ('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK')
 
-- 초기화(?) 
EXEC dbms_stats.init_package();
 
-- Task 확인
 select name, ctime, how_created from sys.wri$_adv_tasks where owner_name
= 'SYS' and name in
('AUTO_STATS_ADVISOR_TASK','INDIVIDUAL_STATS_ADVISOR_TASK');