12.2 UPGRADE
There are three main steps for upgrade database to 12.2
I have divided in preupgrade,upgrade and postupgrade here
Scenario – Here i have stand alone database 11.2.0.4 db11g and i am going to upgrade in to 12c 12.2.0.1 and then convert into pluggable database in newly created empty container database db12
Source – 11.2.0.4 RAC database – db11g
Target – 12.2.0.1 RAC container database db12
Pre-upgrade
Run this pre-upgrade tool on 11g database
export ORACLE_SID=db11g_1
/opt/oracle/ora11204/jdk/bin/java -jar /opt/oracle/ora12201/rdbms/admin/preupgrade.jar
Preupgrade generated files:
/opt/oracle/ora11204/cfgtoollogs/DB11G/preupgrade/preupgrade.log
/opt/oracle/ora11204/cfgtoollogs/DB11G/preupgrade/preupgrade_fixups.sql
/opt/oracle/ora11204/cfgtoollogs/DB11G/preupgrade/postupgrade_fixups.sql
Check invalid objects
set pagesize500
set linesize 100
select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;
select substr(object_name,1,40) object_name,substr(owner,1,15) owner,object_type from dba_objects where status=’INVALID’ order by owner,object_type;
select owner,object_type,count(*) from dba_objects where status=’INVALID’ group by owner,object_type order by owner,object_type ;
Run this if there is invalid
@$ORACLE_HOME/rdbms/admin/utlrp.sql
Gathering Optimizer Statistics to Decrease Oracle Database Downtime
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
Verifying Materialized View Refreshes are Complete Before Upgrade
Use this procedure to query the system to determine if there are any materialized view refreshes still in progress. Before upgrading Oracle Database, you must wait until all materialized views have
completed refreshing.
SELECT o.name FROM sys.obj$ o, sys.user$ u, sys.sum$ s WHERE o.type# = 42 AND bitand(s.mflags, 8) =8;
Check of TIMESTAMP WITH TIMEZONE Datatype
Ensuring That No Files Are in Backup Mode and no files need media recovery Before Upgrading
Execute below query to check for the status of the backup:
SQL> SELECT * FROM v$backup WHERE status != ‘NOT ACTIVE’;
Ensure that no files require media recovery:
SQL> SELECT * FROM v$recover_file;
Purging Recycle Bin before upgrade
PURGE DBA_RECYCLEBIN;
HIDDEN PARAMETER
Remove hidden parameter or underscore parameter from pfile or spfile before upgrade
grant this to user if required
GRANT ADMINISTER DATABASE TRIGGER TO DBADMIN;
mv refresh
refresh Materialized view before upgrade
Check the accounts use Case-Insensitive password version
Log in to SQL*Plus as an administrative user, and enter the following SQL query
SELECT USERNAME,PASSWORD_VERSIONS FROM DBA_USERS;
If there are any 10g versions, suggest you to refer Oracle documentation to fix 10g versions, failing to do so, user accounts with LOCKED after upgrade is completed.
About Password Case Sensitivity
Ensure that you do not have the deprecated parameter SEC_CASE_SENSITIVE_LOGON set to FALSE.
In DB11G –
12.2 Upgrade : Pre-Upgrade message “exclusive_mode_auth Failed Manual fixup recommended” (Doc ID 2310808.1)
Before upgrade these are the settings
sec_case_sensitive_logon – TRUE – It require to keep this for upgrade – check after upgrade
password version – 10g
select USERNAME from DBA_USERS where ( PASSWORD_VERSIONS = ’10G ‘ or PASSWORD_VERSIONS = ’10G HTTP ‘) and USERNAME <> ‘ANONYMOUS’;
Starting with Oracle Database release 12.2, Exclusive Mode is the default password-based authentication mode. In Exclusive Mode, accounts which only have the 10G password version (see DBA_USERS.PASSWORD_VERSIONS) are no longer accessible. If your system has any account with only the 10G password version, Exclusive Mode must be switched off prior to the upgrade. See the Network Reference Manual chapter about the SQLNET.ORA parameter SQLNET.ALLOWED_LOGON_VERSION_SERVER for more details on Exclusive Mode. Refer to the Oracle Database Readme 12c Release 2 (12.2) for further information regarding Bug 22031049. (more details)
12.2 Upgrade : Pre-Upgrade message “exclusive_mode_auth Failed Manual fixup recommended” (Doc ID 2310808.1)
Upgrade
run pre upgrade steps to avoid any warning and error
start DBUA from 12c home
/opt/oracle/12.2.0.1/bin/dbua
add /etc/oratab entry if 11g database does not appear and start dbua again
run this sql on 11g database to see user with password version
select USERNAME, PASSWORD_VERSIONS
from DBA_USERS
where ( PASSWORD_VERSIONS = ’10G ‘
or PASSWORD_VERSIONS = ’10G HTTP ‘)
and USERNAME <> ‘ANONYMOUS’;
purge recyclebin
purge dba_recyclebin;
you can ignore this error
Refresh MV if needed – i ignore this steps
EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
i took rman level 0 backup so
At this point upgraded database is standalone database 12c.
if you want to make it pluggable database there are some extra steps required like
- create container database – here i have page link that will show you how to create empty container database db12 using dbca
- convert into plug gable database
Post-upgrade
create container database
create empty container database db12
Convert upgraded database to pluggable
$ . oraenv
ORACLE_SID = [upg12c] ? db11g
The Oracle base remains unchanged with value /home/oracle/app/oracle
$ sqlplus / as sysdba
show parameter compatible;
alter system set compatible= ‘12.2.0’ scope=spfile;
shutdown immediate
startup open read only;
Generate the XML description file – this file will contain the information describing the database structure. To create it the database has to be in read only mode:
SQL> ! rm /home/oracle/upgrade/db11g_describe.xml
BEGIN
DBMS_PDB.DESCRIBE(pdb_descr_file => ‘/home/oracle/upgrade/db11g_describe.xml’);
END;
/
SQL> shutdown immediate
SQL> exit
Check Compatibility of Pluggable Database 11g with Container Database db12
connect to container database db12 and Run DBMS_PDB.CHECK_PLUG_COMPATIBILITY()
$ export ORACLE_SID=db12_1
$ sqlplus / as sysdba
SQL>
SET SERVEROUTPUT ON
SPOOL /home/oracle/upgrade/db11g_check_plug_compatibility.log
DECLARE
compatible CONSTANT VARCHAR2(30) := CASE DBMS_PDB.CHECK_PLUG_COMPATIBILITY( pdb_descr_file => ‘/home/oracle/upgrade/db11g_describe.xml’, pdb_name => ‘db11g’) WHEN TRUE THEN ‘COMPATIBLE :)’ ELSE ‘!!!NOT_COMPATIBLE 😦 !!!!’
END;
BEGIN
DBMS_OUTPUT.PUT_LINE(compatible);
END;
/
Run Query against PDB_PLUG_IN_VIOLATION to see any issues
SQL>
set pages 100
set lines 200
col time for a20
col cause for a20
col message for a70
col name for a10
col action for a50
select * from PDB_PLUG_IN_VIOLATIONS where name=’DB11G’;
Plugin your stand-alone database db11g – also rename if you want
SQL> create pluggable database db11g using ‘/home/oracle/upgrade/db11g_describe.xml’ copy tempfile reuse;
Pluggable database created.
Perform Sanity Checks against Pluggable Database
SQL>
alter session set container=db11g;
alter session set optimizer_adaptive_features=false;
spool /home/oracle/upgrade/db11g_noncdb_to_pdb.log
@?/rdbms/admin/noncdb_to_pdb.sql
exit
Open the Pluggable Database
sqlplus / as sysdba
show pdbs;
SQL> alter pluggable database db11g open;
Run Query against PDB_PLUG_IN_VIOLATION to see any issues
SQL>
set pages 100
set lines 200
col time for a20
col cause for a20
col message for a70
col name for a10
col action for a50
select * from PDB_PLUG_IN_VIOLATIONS where name = ‘DB11G’ and type = ‘ERROR’ and status <> ‘RESOLVED’;
Check REGISTRY components in the Container and Pluggable databases.
set pagesize 9999 linesize 200
col comp_name format a40;
SELECT comp_id,comp_name,status,version FROM dba_registry;
alter session set container=db11g;
SELECT comp_id,comp_name,status,version FROM dba_registry;
run this on PDB and CDB
@?/rdbms/admin/utlrp.sql
check timezone version in CDB and PDB
select VERSION FROM v$timezone_file;
SELECT TZ_VERSION from registry$database;
if it does not match then run this
update registry$database set TZ_VERSION = (select version FROM v$timezone_file);
commit;
create service for PDB
srvctl add service -d db11gc -pdb db11g -s db11gsvc
srvctl modify service -d db11gc -pdb db11g -s db11gsvc -P BASIC -e SELECT -m BASIC -z 180 -w 5
srvctl start service -d db11gc -s db11gsvc
Change LDAP or TNS entry if required
Take Level 0 backup
Apply datapatch for new created database or upgraded database or new pluggable database as per patch readme
export ORACLE_SID=db12_1
sqlplus /nolog
Connect / as sysdba
Startup – no need to start if it is up – do show pdbs; –pdb should be open for datapatch
alter pluggable database all open;
quit
cd $ORACLE_HOME/OPatch
./datapatch -verbose
Check for SUCCESS
Check for the log files – $ORACLE_BASE/cfgtoollogs/sqlpatch/26710464/<unique patch ID>
26710464_apply_<database SID>_<CDB name>_<timestamp>.log
Check for invalids and PSUversion
cd $ORACLE_HOME/rdbms/admin
sqlplus /nolog
SQL> CONNECT / AS SYSDBA
SQL> @utlrp.sql
SET LINESIZE 400
COLUMN action_time FORMAT A20
COLUMN action FORMAT A10
COLUMN status FORMAT A10
COLUMN description FORMAT A40
COLUMN version FORMAT A10
COLUMN bundle_series FORMAT A10
SELECT TO_CHAR(action_time, ‘DD-MON-YYYY HH24:MI:SS’) AS action_time,
action,
status,
description,
version,
patch_id,
bundle_series
FROM sys.dba_registry_sqlpatch
ORDER by action_time;
POST UPGRADE ISSUE
Problem:
12.2 DBCA does not update oratab with the database name in RAC environment.
Solution:
It is expected behaviour in RAC Database creations from 12.2.0.1. Use srvctl to get instance name and oracle home information.
For single instance, database entry gets updated in the /etc/oratab.
12.2 Real Application Cluster DBCA Do Not Update Oratab (Doc ID 2278584.1)
Change .profile
Check dblink and fix if any issue
Connect with container database and change parameter
alter system set sec_case_sensitive_logon=false scope=both sid=’*’;
Upgrade set sec_case_sensitive_logon to TRUE –so set to FALSE
Modify sqlnet.ora for 10g invalid password issue after upgrade
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
.NET application return truncated string with 12c client. But it works with 11g client. In toad it works with 12c though so it strange issue.
Connection issue who have 11g client –ORA-28040 : No matching authentication protocol
Add below entry on server side of sqlnet.ora on both node
SQLNET.ALLOWED_LOGON_VERSION_CLIENT=10
SQLNET.ALLOWED_LOGON_VERSION_SERVER=10
Invalids in SYS schema – V$XS_SESSION_ROLE in PDB but not in CDB
-bash-4.2$ sqlplus sys/@db12 as sysdba
SQL> alter session set container=db11g;
Session altered.
SQL> show con_name;
CON_NAME
——————————
db11g
SQL>
SQL> DROP VIEW SYS.V$XS_SESSION_ROLE;
DROP VIEW SYS.V$XS_SESSION_ROLE
*
ERROR at line 1:
ORA-02030: can only select from fixed tables/views
If you get above error then do option 2 recommended by oracle development. At this time it is not documented in oracle
SQL> update obj$ set status = 1 where name = ‘V$XS_SESSION_ROLE’ and subname = ‘$DEPRECATED$’;
1 row updated.
select owner,OBJECT_NAME,OBJECT_TYPE,status from dba_objects where object_name = ‘V$XS_SESSION_ROLE’;
SYS V$XS_SESSION_ROLE VIEW VALID
PUBLIC V$XS_SESSION_ROLE SYNONYM VALID
UTL_FILE directory
The UTL_FILE_DIR initialization parameter is deprecated in Oracle Database 12c Release 2 (12.2.0.1) and may not be supported in a future release.
In the past, accessible directories for PL/SQL file I/O were specified in the initialization file using the UTL_FILE_DIR parameter. However, UTL_FILE_DIR access is no longer recommended. Oracle recommends that you instead use the directory object feature, which replaces UTL_FILE_DIR. Directory objects offer more flexibility and granular control to the UTL_FILE application administrator, can be maintained dynamically (that is, without shutting down the database), and are consistent with other Oracle tools.
Check instance, database, version
select * from gv$instance;
select * from gv$database;
select * from gv$version;
Alert log error
Buffer Cache Full DB Caching mode changing from FULL CACHING ENABLED to FULL CACHING DISABLED
Full DB Caching disabled: DEFAULT_CACHE_SIZE should be at least 283902 MBs bigger than current size.
db11g(3):Errors in file /acfs_mnt/diag/rdbms/trace/1_j006_17433166.trc:
ORA-12012: error on auto execute of job “abc”.”PRC_JOB”
ORA-29280: invalid directory path
ORA-06512: at “SYS.UTL_FILE”, line 41
ORA-06512: at “SYS.UTL_FILE”, line 478
ORA-06512: at “MED.PRC_JOB”, line 63
Errors in file /acfs_mnt/diag/rdbms///trace/1_j001_9961876.trc:
ORA-12012: error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_37″
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
Please refer
ORA-12012 Error on auto execute of job “SYS”.”ORA$AT_OS_OPT_SY_<NN> in 12.2 Database ( Doc ID 2127675.1 )
Next Steps:-
Please run dbms_stats.init_package() in the container database to create the tasks correctly:
$ sqlplus / as sysdba
EXEC dbms_stats.init_package();
Once done, please execute below query ..
column name format A35
set linesize 120
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’);
Output of the query will look like this:
NAME CTIME HOW_CREATED
———————————– ———- ——————————
AUTO_STATS_ADVISOR_TASK 14-APR-16 CMD
INDIVIDUAL_STATS_ADVISOR_TASK 14-APR-16 CMD
db11g(3):ERROR: Unable to normalize symbol name for the following short stack (at offset 494):
dbgexProcessError()+228<-dbgeExecuteForError()+116<-dbgePostErrorKGE()+2056<-dbkePostKGE_kgsf()+88<-kgeade()+444<-kgesev()+148<-kgesec1()+60<-pppjmpl_local()+328<-pppjmp()+708<-pdz2Mz2_Asser
t_Print()+1364<-pdz2M14_AssertO()+176<-pdy3M90_Allocate_One_Constant()+996<-pdy3M02_Allocate_Constants()+1460<-pdy1F82_Write_MCode()+164<-pdy1F01_Driver()+264<-pdw0F82_Run_Code_Gen()+208<-pd
w0F01_Code_Gen()+372<-phpcog()+28<-phpcmp()+1616<-pcicmp0()+472<-kkxcmp0()+968<-rpiswu2()+580<-kkxcmp()+248<-IPRA.$kkpalt()+2892<-kkpalt()+160<-opiexe()+20268<-opiosq0()+4972<-kpooprx()+412<
-kpoal8()+828<-opiodr()+1248<-ttcpip()+616<-opitsk()+1888<-opiino()+948<-opiodr()+1248<-opidrv()+996<-sou2o()+184<-opimai_real()+680<-ssthrdmain()+460<-main()+208<-__start()+112
Errors in file /acfs_mnt/diag/rdbms//trace/_1_ora_53674430.trc (incident=66072) (PDBNAME=db11g):
ORA-06544: PL/SQL: internal error, arguments: [*** ASSERT at file pdy3.c, line 1931; Layout Mismatch – 2 < 16; PKG_FORM_PARM___76748[1625, 1]], [], [], [], [], [], [], []
db11g(3):Incident details in: /acfs_mnt/diag/rdbms///incident/incdir_66072/1_ora_53674430_i66072.trc
Errors in file /acfs_mnt/diag/rdbms///trace/1_ora_53674430.trc (incident=66074) (PDBNAME=db11g):
ORA-06544: PL/SQL: internal error, arguments: [*** ASSERT at file pdy3.c, line 1931; Layout Mismatch – 2 < 16; PKG_FORM_76748[1625, 1]], [], [], [], [], [], [], []
db11g(3):Incident details in: /acfs_mnt/diag/rdbms///incident/incdir_66074/1_ora_53674430_i66074.trc
2018-01-12T10:55:32.311247-06:00
WARNING: too many parse errors, count=600 SQL hash=0x50366ef0
PARSE ERROR: ospid=50987508, error=942 for statement:
2018-01-12T10:55:32.316163-06:00
SELECT av.version_dt FROM rob.application_version av WHERE av.app_cd = :1
Additional information: hd=70001008dae60b8 phd=70001007196ea38 flg=0x101476 cisid=157 sid=157 ciuid=157 uid=157
2018-01-12T10:55:32.321188-06:00
—– PL/SQL Call Stack —–
object line object
handle number name
Solution: developer require to change SQL or code
Redo warning
*****************************************************************
An internal routine has requested a dump of selected redo.
This usually happens following a specific internal error, when
analysis of the redo logs will help Oracle Support with the
diagnosis.
It is recommended that you retain all the redo logs generated (by
all the instances) during the past 12 hours, in case additional
redo dumps are required to help with the diagnosis.
*****************************************************************
SRDC – Required Diagnostic Data Collection for Redo Log Corruption ( Doc ID 1671530.1 )
Kindly take a look at – An Internal Routine Has Requested A Dump Of Selected Redo – Message Appears In Alert Log ( Doc ID 2295361.1 )
Solution:
ignore it if there is no error after this message. Also monitor log if error occurs sometime
Partition message in alert log – as per SR
TABLE SYS.WRI$_OPTSTAT_HISTHEAD_HISTORY: ADDED INTERVAL PARTITION SYS_P
These are partitions of an internal table that collects AWR statistics – Alert Log Shows Messages Starting “TABLE WRI$_OPTSTAT_* ADDED INTERVAL PARTITION…” ( Doc ID 2018434.1 ) –
ORA-00060: deadlock resolved; details in file /acfs_mnt/diag/rdbms//1/trace/_1_j002_32178392.trc
2018-03-10T10:12:17.283105-06:00
Errors in file /acfs_mnt/diag/rdbms//_1/trace/_1_j002_32178392.trc:
ORA-12012: error on auto execute of job “SYS”.”ORA$AT_SA_SPC_SY_5115″
ORA-00060: deadlock detected while waiting for resource
ORA-06512: at “SYS.DBMS_SPACE”, line 2741
ORA-06512: at “SYS.DBMS_HEAT_MAP_INTERNAL”, line 716
ORA-06512: at “SYS.DBMS_HEAT_MAP_INTERNAL”, line 1164
ORA-06512: at “SYS.DBMS_HEAT_MAP”, line 228
ORA-06512: at “SYS.DBMS_SPACE”, line 2747
2018-03-10T10:32:13.788957-06:00
SOLUTION / ACTION PLAN
======================
Your issue looks similar to Doc ID 2321020.1
In 12.2 Auto Space Advisor Job Fails With ORA-60 ( Doc ID 2321020.1 )
Until Bug 24687075 gets fixed, you can disable the space advisory job as
workaround:
SQL> EXEC DBMS_AUTO_TASK_ADMIN.DISABLE(‘AUTO SPACE ADVISOR’,NULL, NULL);
Problem: invalid SQLTXADMIN.SQLT$A after upgrade to 12c
Description: upgrade database from 11.2 to 12.2 and it made invalid SQLTXADMIN.SQLT$A
Recompile does not work either.
Solution: Drop sqlt
Drop sqlt:
@/sqlt/install/sqdrop.sql;
if you can not find it in your environment then download it from oracle support and drop and reinstall if needed.
WikiDBA 