1. check unsupported objects on primary database :
SQL> select * from dba_rolling_unsupported ;
if find any - export them (if possible, or treat them in other way) and remove them from the database, then import after upgrade if needed
SQL> select 'drop table '||owner||'.'||table_name||' ;' from dba_rolling_unsupported ;
2. check unsupported objects again
2.2 create tracking table on the primary
SQL> create table c##ddi.tracking_table (phase number, text varchar2(4000)) ;
SQL> insert into c##ddi.tracking_table values (1, 'Start') ;
3. although using DG broker is supported during rolling upgrade (starting from version 12.2), i would recommend to disable data guard broker on primary and all standby databases (i caught some bugs on it) :
% dgmgrl sys/aaa
DGMGRL> disable configuration ;
SQL> alter system set dg_broker_start = false ;
3.1 configure archivelog destination for future primary database (in case dataguard broker is disabled) :
SQL> alter system set log_archive_dest_2 = 'service="dg-bbb-test1-loc1" ASYNC NOAFFIRM delay=0 optional compression=disable max_failure=0 max_connections=1 reopen=60 db_unique_name="bbb_tes
t1_loc1" net_timeout=30 valid_for=(online_logfile,all_roles)'
4. initialize rolling upgrade plan
SQL> begin dbms_rolling.init_plan (future_primary => 'bbb_test1_loc2') ; end ;
5. query rolling plan
SQL> select scope, name, curval from dba_rolling_parameters order by scope, name;
6. build the plan
SQL> begin dbms_rolling.build_plan ; end ;
6.1 verify the plan
SQL> select batchid,source,target,description,phase,status from dba_rolling_plan ;
7. start the plan
SQL> begin dbms_rolling.start_plan ; end ;
7.1 look through the events history :
SQL> select event_time,type,message from dba_rolling_events ;
8. insert data into tracking table on the primary (may be created earlier)
SQL> insert into c##ddi.tracking_table values (2, 'plan started, bbb_test1_loc2 is now TLS') ;
8.1 query this table on the TLS. you must see the data there as well
9. UPGRADE TLS to 19c
9.1 prepare cfg file upg1.cfg like this :
global.autoupg_log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade
#
# Database number 1 - Full DB/CDB upgrade
#
upg1.log_dir=/u01/app/oracle/cfgtoollogs/autoupgrade/bbb_test1_loc2
upg1.sid=bbb_test1_loc2
upg1.source_home=/u01/app/oracle/product/12.2/db_202201 # Path of the source ORACLE_HOME
upg1.target_home=/u01/app/oracle/product/19/db_1917 # Path of the target ORACLE_HOME
upg1.start_time=NOW # Optional. [NOW | +XhYm (X hours, Y minutes after launch) | dd/mm/yyyy hh:mm:ss]
upg1.run_utlrp=yes # Optional. Whether or not to run utlrp after upgrade
upg1.timezone_upg=yes # Optional. Whether or not to run the timezone upgrade
upg1.target_version=19 # Oracle version of the target ORACLE_HOME. Only required when the target Oracle database version is 12.2
upg1.remove_underscore_parameter=yes
upg1.restoration=no
do other required staff (configure wallet to be opened automatically and so on)
9.2 run autoupgrade in analyze mode :
$ /u01/app/oracle/product/19/db_1917/jdk/bin/java -jar /mnt/tst/install/oracle/autoupgrade/autoupgrade.jar -restore_on_fail -config /export/home/oracle/migration/bbb_test1_loc2_to_19c/upg1.cfg -mode analyze
check logfiles carefully
9.3 run autoupgrade in deploy mode :
$ /u01/app/oracle/product/19/db_1917/jdk/bin/java -jar /mnt/tst/install/oracle/autoupgrade/autoupgrade.jar -restore_on_fail -config /export/home/oracle/migration/bbb_test1_loc2_to_19c/upg1.cfg -mode deploy
9.4 you might be encountered into error like :
Error: UPG-1524
Cause: PDBs have been found that are either MOUNTED or RESTRICTED. The following PDBs need attention: [PDB1, PDB2]
For further details, see the log file located at /u01/app/oracle/cfgtoollogs/autoupgrade/bbb_test1_loc2/bbb_test1_loc2/101/autoupgrade_20230223_user.log]
the main cause is configured "read only" open mode in GI :
% srvctl config database
Database unique name: bbb_test1_loc2
Database name: bbbtest1
Oracle home: /u01/app/oracle/product/19/db_1917
Oracle user: oracle
Spfile: +DATAC7/BBB_TEST1_M8F/PARAMETERFILE/spfile.299.1129575269
Password file: +datac7/pw_files/orapwbbb_test1_loc2
Domain:
Start options: read only
Stop options: immediate
Database role: PHYSICAL_STANDBY
Management policy: AUTOMATIC
Server pools:
Disk Groups: RECOC7,DATAC7
Mount point paths:
Services: service1_prim,service2_prim
Type: SINGLE
OSDBA group: dba
OSOPER group: dba
Database instance: bbb_test1_loc2
Configured nodes: loc2-p0l2-bbb-adm
CSS critical: no
CPU count: 0
Memory target: 0
Maximum memory: 0
Default network number for database services:
Database is administrator managed
fix :
% srvctl modify database -d $ORACLE_SID -startoption "open"
!!! BE AWARE OF SERVICES WHICH MIGHT BE RUN AFTER REOPENING BEING UPGRADED DATABASE !!! you'd better remove services beforehand, and then recreate them after all works will have been completed :
% srvctl remove service ...
% srvctl add service ...
9.5 after successful upgrade procedure on TLS restart SQL APPLY on TLS manually (transient logical standby database)
SQL> alter database start logical standby apply immediate ;
9.6 make sure you've created log_archive_dest_n parameter(s) back to original primary database and all standby databases as well. it will be used after switchover
9.7 insert into protocol table values (on primary) :
SQL> insert into c##ddi.tracking_table values (3, 'TLS upgraded, bbb_test1_loc1 is still PRIMARY') ;
SQL> commit ;
check on TSL weather new data has appeared :
SQL> select * from c##ddi.tracking_table ;
10. SWITCHOVER
10.1 from primary database (12c) execute :
SQL> begin dbms_rolling.switchover; end
/
10.2 check on the former primary which now must be logical standby (and transient logical standby should already have been primary):
SQL> select * from v$dataguard_config ;
Now you can check the application and restart it if needed.
10.3 restart former primary database under new OH (19)
% srvctl stop database -d $ORACLE_SID
% srvctl remove database -d $ORACLE_SID
switch to new ORACLE_HOME and add database using srvctl :
% srvctl add database -d bbb_test1_loc1 -o /u01/app/oracle/product/19/db_1917 -pwfile +datac7/pw_files/orapwbbb_test1_loc1 -spfile +datac7/sp_files/spfilebbb_test1_loc1.ora -role logical_st
andby -startoption mount -stopoption immediate -instance bbb_test1_loc1 -dbtype single -dbname bbbtest1 -policy automatic -node $(hostname)
% srvctl start database -d bbb_test1_loc1
10.4 you may encounter the error :
PRCR-1079 : Failed to start resource ora.bbb_test1_loc1.db
CRS-5017: The resource action "ora.bbb_test1_loc1.db start" encountered the following error:
ORA-01078: failure in processing system parameters
LRM-00101: unknown parameter name '_gc_cpu_time'
in this case you'd better to recreate spfile and remove all underscore parameters, which have left from oracle 12 :
SQL> create pfile='/export/home/oracle/pfile' from spfile='+datac7/sp_files/spfilebbb_test1_loc1.ora' ;
go to host end edit pfile (rem all underscore parameters)
SQL> create spfile='+datac7/sp_files/spfilebbb_test1_loc1.ora' from pfile='/export/home/oracle/pfile' ;
$ srvctl start database -d bbb_test1_loc1
former primary database must be started in mount state
10.5 if needed, copy files from old ORACLE HOME to new (*.ora files etc.)
10.6 on the new primary, consult upgrade plan and event log once again
SQL> select event_time,type,message from dba_rolling_events ;
SQL> select batchid,source,target,description,phase,status from dba_rolling_plan ;
11 FINISH phase
11.1 before running finish_plan, set log_archive_dest_state_n=enable for all standbys of new primary database
11.2 run from new primary database :
SQL> begin dbms_rolling.finish_plan ; end ;
/
SQL> select event_time,type,message from dba_rolling_events ;
SQL> select * from v$dataguard_config ;
11.3 insert into protocol table values :
SQL> insert into c##ddi.tracking_table values (4, 'FINISH') ;
SQL> commit ;
12 POSTUPGRADE tasks
12.1 modify clusterware configuration according to new roles (if not already done). you may need add custom services as it were for former primary :
for new primary :
$ srvctl modify database -d $ORACLE_SID -startoption open -role primary
for former primary :
$ srvctl modify database -d $ORACLE_SID -role physical_standby
$ srvctl add service -d $ORACLE_SID -s service1_prim -pdb pdb1 -role primary -policy automatic -failovertype session -failovermethod basic -failoverretry 10 -failoverdelay 10
$ srvctl add service -d $ORACLE_SID -s service2_prim -pdb pdb2 -role primary -policy automatic -failovertype session -failovermethod basic -failoverretry 10 -failoverdelay 10
12.2 restore Data Guard Broker configuration on both databases :
SQL> alter system set dg_broker_start = true ;
% dgmgrl sys/aaa
DGMGRL> enable configuration ;
after enabling configuration, the new membership roles should be synchronized, but the error can occur :
Error: ORA-16700: The standby database has diverged from the primary database.
in this case, recreate dataguard configuration :
DGMGRL> remove configuration ;
Removed configuration
DGMGRL> show configuration ;
ORA-16532: Oracle Data Guard broker configuration does not exist
DGMGRL> create configuration bbbtest1_dg as primary database is bbb_test1_loc2 connect identifier is "dg-bbb-test1-loc2" ;
DGMGRL> add database bbb_test1_loc1 as connect identifier is "dg-bbb-test1-loc1" ;
in case of Error: ORA-16698: member has a LOG_ARCHIVE_DEST_n parameter with SERVICE attribute set
remove log_archive_dest_n parameter from being added database :
SQL> alter system set log_archive_dest_2='' scope=memory ;
SQL> alter system reset log_archive_dest_2 ;
DGMGRL> show configuration ;
DGMGRL> enable configuration ;
12.3 if needed, do switchover back to the former primary database
connect to dg broker (primary database) using tns :
$ dgmgrl sys@dg-bbb-test1-loc2 as sysdba
DGMGRL> switchover to bbb_test1_loc1 ;
12.4 it has noticed that new spfile (with default name, as autoupgrade.jar utility does) was created for upgraded database (transient logical standby). it has default name and contains lots of underscore parameters. after switchover one needs to fix this:
SQL> create pfile='/export/home/oracle/pfile' from spfile ;
edit pfile if needed (remove unnecessary underscore parameters) and recreate spfile with required custom name (if needed):
SQL> create spfile='+datac7/sp_files/spfilebbb_test1_loc2.ora' from pfile='/export/home/oracle/pfile' ; -- do it on the running instance in order to update clusterware registry
$ srvctl stop database -d $ORACLE_SID
$ srvctl start database -d $ORACLE_SID -o nomount
SQL> alter database mount ;
SQL> alter database open read only ; -- opened read only required on standby to enable optimizer fixes
enable optimizer fixes on the standby and primary databases :
SQL> begin dbms_optim_bundle.enable_optim_fixes ('ON','BOTH') ; END ;
/
restart physical standby :
$ srvctl stop database -d $ORACLE_SID
$ srvctl start database -d $ORACLE_SID
12.5 RUN BACKUP OF LEVEL 0 of the new PRIMARY
12.6 set some parameters (recommended by Oracle Migration Team)
alter system set deferred_segment_creation=false ;
alter system set "_cursor_obsolete_threshold"=1024 scope=spfile ;
alter system set "_enable_ptime_update_for_sys"=true ;
alter system set optimizer_adaptive_plans=true ;
12.7 check database directory objects and do all the staff related to postupgrade tasks (gather dictionary stats after 10 days of work, increase compatible etc.)
12.8 check that database parameters are in sync between primary and standby databases
12.9 reconfigure databases in Oracle Enterprise Manager to new Oracle Home if needed
