Single Instance DB Upgrade From 12c to 19c
-Oracle 12c database should be up and running and it should be applied with latest patches
-Download latest 19c software's from oracle website.
-Also download latest Oracle Patches for 19c
-Start Installation of 19c software on separate mount point and apply latest patches which was downloaded earlier.
At this stages two separate database software will be ready one is 12cR2 and other will be 19c on two different mount points
High Level Steps:
The Pre-upgrade Information Tool is available in the new release Oracle home, in path ORACLE_HOME/rdbms/admin/preupgrade.jar
mkdir -p /u02/preupgrade
Run the pre-upgrade tool from 12c home referring file of new 19c home
/u01/app/oracle/product/12c/db_1/jdk/bin/java -jar /u02/app/oracle/product/19c/db_1/rdbms/admin/preupgrade.jar DIR /u02/preupgrade
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
purge dba_recyclebin;
purge recyclebin;
select name,open_mode,log_mode from v$database;
@/u02/preupgrade/preupgrade_fixups.sql
Performing the pre-upgrade actions
Run the preupgrade_fixups.sql
-Download latest 19c software's from oracle website.
-Also download latest Oracle Patches for 19c
-Start Installation of 19c software on separate mount point and apply latest patches which was downloaded earlier.
At this stages two separate database software will be ready one is 12cR2 and other will be 19c on two different mount points
High Level Steps:
The Pre-upgrade Information Tool is available in the new release Oracle home, in path ORACLE_HOME/rdbms/admin/preupgrade.jar
mkdir -p /u02/preupgrade
Run the pre-upgrade tool from 12c home referring file of new 19c home
/u01/app/oracle/product/12c/db_1/jdk/bin/java -jar /u02/app/oracle/product/19c/db_1/rdbms/admin/preupgrade.jar DIR /u02/preupgrade
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
purge dba_recyclebin;
purge recyclebin;
select name,open_mode,log_mode from v$database;
@/u02/preupgrade/preupgrade_fixups.sql
Performing the pre-upgrade actions
Run the preupgrade_fixups.sql
==> Need to resize datafile as per the recommendations
select file_name from dba_data_files;
alter database datafile '/u01/app/oradata/PROD/datafile/o1_mf_system_j87d12w1_.dbf' resize 1g;
select file_name from dba_data_files;
alter database datafile '/u01/app/oradata/PROD/datafile/o1_mf_system_j87d12w1_.dbf' resize 1g;
alter database datafile '/u01/app/oradata/PROD/datafile/o1_mf_sysaux_j87d2j6s_.dbf' resize 700m; alter database datafile '/u01/app/oradata/PROD/datafile/o1_mf_undotbs1_j87d3mbh_.dbf' resize 600m;
select file_name from dba_temp_files;
alter database tempfile '/u01/app/oradata/PROD/datafile/o1_mf_temp_j87d525p_.tmp' resize 300m;
==>Create restore point before_upgrade_19c guarantee flashback database;
alter database tempfile '/u01/app/oradata/PROD/datafile/o1_mf_temp_j87d525p_.tmp' resize 300m;
==>Create restore point before_upgrade_19c guarantee flashback database;
select NAME,GUARANTEE_FLASHBACK_DATABASE,TIME from v$restore_point;
sqlplus / as sysdba
shutdown immediate;
exit
Copy the spfile, password file and listener file from old ORACLE_HOME to new 19c ORACLE_HOME
cp orapwtest spfiletest.ora /u02/app/oracle/product/19c/db_1/dbs
ls -lrt /u02/app/oracle/product/19c/db_1/dbs
-rw-r--r--. 1 oracle oinstall 3079 May 14 2015 init.ora
-rw-r-----. 1 oracle oinstall 7680 Sep 23 21:12 orapwtest
-rw-r-----. 1 oracle oinstall 3584 Sep 23 21:12 spfiletest.ora
Stop listener running on 12c home and start it from 19c home. Start the database from 19c ORACLE_HOME and start the upgrade.
sqlplus / as sysdba
startup upgrade;
select name,open_mode,status from v$database, v$instance;
Run the DB Upgrade utility
$ORACLE_HOME/bin/dbupgrade -n 8
-Check output and wait for 30 min.
-Check the upgrade summary log,
-Startup database manually and
sqlplus / as sysdba
shutdown immediate;
exit
Copy the spfile, password file and listener file from old ORACLE_HOME to new 19c ORACLE_HOME
cp orapwtest spfiletest.ora /u02/app/oracle/product/19c/db_1/dbs
ls -lrt /u02/app/oracle/product/19c/db_1/dbs
-rw-r--r--. 1 oracle oinstall 3079 May 14 2015 init.ora
-rw-r-----. 1 oracle oinstall 7680 Sep 23 21:12 orapwtest
-rw-r-----. 1 oracle oinstall 3584 Sep 23 21:12 spfiletest.ora
Stop listener running on 12c home and start it from 19c home. Start the database from 19c ORACLE_HOME and start the upgrade.
sqlplus / as sysdba
startup upgrade;
select name,open_mode,status from v$database, v$instance;
Run the DB Upgrade utility
$ORACLE_HOME/bin/dbupgrade -n 8
-Check output and wait for 30 min.
-Check the upgrade summary log,
-Startup database manually and
startup;
SELECT name, open_mode, status, version from v$database, v$instance;
@$ORACLE_HOME/rdbms/admin/utlusts.sql TEXT
Recompile the INVALID Objects using utlrp.sql @$ORACLE_HOME/rdbms/admin/utlrp.sql
Connect to sqlplus and run the postupgrade_fixups.sql script
sqlplus / as sysdba
@/u02/preupgrade/postupgrade_fixups.sql
Upgrade the timezone file version
SELECT name, open_mode, status, version from v$database, v$instance;
@$ORACLE_HOME/rdbms/admin/utlusts.sql TEXT
Recompile the INVALID Objects using utlrp.sql @$ORACLE_HOME/rdbms/admin/utlrp.sql
Connect to sqlplus and run the postupgrade_fixups.sql script
sqlplus / as sysdba
@/u02/preupgrade/postupgrade_fixups.sql
Upgrade the timezone file version
sqlplus / as sysdba
SELECT version FROM v$timezone_file;
@$ORACLE_HOME/rdbms/admin/utltz_countstats.sql;
@$ORACLE_HOME/rdbms/admin/utltz_countstar.sql;
@$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql;
@$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql;
SELECT version FROM v$timezone_file;
Gather statistics on fixed objects
Connect to sqlplus as sys user and execute
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Now re-run the postupgrade_fixups.sql
select name from v$restore_point;
drop restore point BEFORE_UPGRADE_19C;
Update the compatible parameter:
alter system set compatible='19.0.0' scope=spfile;
shutdown immediate;
startup;
show parameter compatible;
select name, open_mode, version from v$database, v$instance;
select name,open_mode,database_role from V$database;
show parameter cluster
set lines 200 pages 500
col owner for a15
col OBJECT_NAME for a35
select owner,object_name,object_type,status from dba_objects where status='INVALID';
col COMP_NAME for a40
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
col ACTION_TIME for a30
col action for a30
col version for a10
col BUNDLE_SERIES for a30
col COMMENTS for a47
select ACTION_TIME,ACTION,VERSION,BUNDLE_SERIES,COMMENTS from dba_registry_history;
SELECT version FROM v$timezone_file;
@$ORACLE_HOME/rdbms/admin/utltz_countstats.sql;
@$ORACLE_HOME/rdbms/admin/utltz_countstar.sql;
@$ORACLE_HOME/rdbms/admin/utltz_upg_check.sql;
@$ORACLE_HOME/rdbms/admin/utltz_upg_apply.sql;
SELECT version FROM v$timezone_file;
Gather statistics on fixed objects
Connect to sqlplus as sys user and execute
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS
SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
SQL> EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Now re-run the postupgrade_fixups.sql
select name from v$restore_point;
drop restore point BEFORE_UPGRADE_19C;
Update the compatible parameter:
alter system set compatible='19.0.0' scope=spfile;
shutdown immediate;
startup;
show parameter compatible;
select name, open_mode, version from v$database, v$instance;
select name,open_mode,database_role from V$database;
show parameter cluster
set lines 200 pages 500
col owner for a15
col OBJECT_NAME for a35
select owner,object_name,object_type,status from dba_objects where status='INVALID';
col COMP_NAME for a40
select COMP_ID,COMP_NAME,VERSION,STATUS from dba_registry;
col ACTION_TIME for a30
col action for a30
col version for a10
col BUNDLE_SERIES for a30
col COMMENTS for a47
select ACTION_TIME,ACTION,VERSION,BUNDLE_SERIES,COMMENTS from dba_registry_history;
No comments:
Post a Comment