Direct 12c database Upgrade high level steps
1. Backup the source database. Enable flashback if possible and create a guaranteed restore point.
2. Direct upgrade to 12c is possible from 10.2.0.5, 11.1.0.7 and 11.2.0.2 so please check the source db version and if:
a. It is < 10.2.0.5, then upgrade it to 10.2.0.5 first.
b. If it is 11.1.0.7, then upgrade it to 11.1.0.7 first.
c. If it is 11.2.0.2 then upgrade it to 11.2.0.2 first.
Also, 12c database cannot be downgraded to 10g. it can only be downgraded to 11g.
3. Install the 12c binaries in a separate ORACLE_HOME directory.
/home/oracle/stage/database/runInstaller -silent -responseFile /home/oracle/stage/database/response/db_install.rsp
4. Run the pre upgrade tool. It is present in new ORACLE_HOME. You can run it from new ORACLE_HOME.
$ORACLE_HOME/rdbms/admin/preupgrd.sql
5. It will create the preupgrade fixup and postupgrade fix up scripts. Run them as suggested by this tool.
6. Purge recycle bin. DB control is no longer used in 12c. it is removed during the upgrade but to save the downtime, it recommends to remove it. it is also recommended by preupgrade tool.
7. Disable cron jobs and jobs scheduled outside the database until the upgrade is completed.
8. Shutdown the source db and listener.
9. Copy the parameter file, password file, network files in new ORACLE_HOME.
10. From Oracle Database 12c home, start up the database using STARTUP UPGRADE and execute catctl.pl
SQL> startup UPGRADE
$ORACLE_HOME/perl/bin/perl catctl.pl catupgrd.sql
11. Run post upgrade fixup scripts.
12. Run the Post-Upgrade Status tool to show a summary and status of the upgrade. If there are any errors or any component is INVALID, then troubleshoot to fix the error.
SQL> @@?/rdbms/admin/utlu121s.sql
13. 12c using the timezone with version 18. So it is not that it will recommended to update it post upgrade.
14. Recompile any invalid objects. You may specify parallelism for the script as a parameter.
SQL> @@?/rdbms/admin/utlrp 6
15. Run the utluiobj.sql (Upgrade Invalid Objects tool) script to identify new invalid objects after the upgrade. This script will work only if you have run the preupgrd.sql script before the upgrade. It outputs the difference between the invalid objects that exist after the upgrade and invalid objects that existed prior to the upgrade. Fix up the new invalid objects.
SQL > @?/rdbms/admin/utluiobj.sql
16. Start the listener from the new oracle home.
17. Enable back Database Vault if required.
18. Enable back the cron jobs.
19. Drop restore point if it is taking up huge space.
SELECT NAME,SCN,TIME,GUARANTEE_FLASHBACK_DATABASE,STORAGE_SIZE/1024/1024/1024 STORAGE_SIZE_GB,DATABASE_INCARNATION# FROM V$RESTORE_POINT;
drop restore point <restore Point name>;
20. Ask app team and all other stakeholder teams for confirmation if all is ok to set the compatible parameter to 12.1.0 to enable its features.
SQL> ALTER SYSTEM SET compatible = '12.1.0' SCOPE=spfile;
Preupgrade tool:
If $ORACLE_BASE is defined, the generated scripts and log files are saved in $ORACLE_BASE/cfgtoollogs/db_unique_name/preupgrade directory. If $ORACLE_BASE is not defined, then the generated scripts and log files are created in $ORACLE_HOME/cfgtoollogs/db_unique_name/preupgrade.
It will check the following and reports issues if found:
a) If the COMPATIBLE parameter is at 11.0.0 or higher. So for 10g databases, it cannot be downgraded. It will need to be restore from backup if needed for downgrade.
b) db parameters. If there is a need to remove hidden parameters, depreciated parameters, and underscore events.
c) components of the database
d) size and free space in system , sysaux, undo, temp tablespaces. If SYS and SYSTEM users have SYSTEM as their default tablespace.
e) resources count like processes,
f) users and roles with the same name e.g. AUDSYS, AUDIT_VIEWER etc.
g) DB control is no longer used in 12c. it is removed during the upgrade but to save the downtime, it recommends to remove it before using below:
- Stop EM Database Control:
$> emctl stop dbconsole
- Connect to the Database using the SYS account AS SYSDBA:
SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql
h) invalid objects,
i) gather stats
EXECUTE dbms_stats.gather_dictionary_stats;
Please create stats on fixed objects after the upgrade using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
j) If the JOB_QUEUE_PROCESSES value is set too low.
k) Purge recyclebin
l) Timezone. 12c using the timezone with version 18. So it is not that it will recommended to update it post upgrade using below:
-- Fix Summary:
-- Update the timezone using the DBMS_DST package after upgrade is complete.
dbms_preup.run_fixup_and_report('OLD_TIME_ZONES_EXIST');
END;
and creates two pre and post upgrade fixup scripts:
Pre-Upgrade Fixup Script (run in source database environment):
/u01/app/oracle/cfgtoollogs/ocad11/preupgrade/preupgrade_fixups.sql
Post-Upgrade Fixup Script (run shortly after upgrade):
/u01/app/oracle/cfgtoollogs/ocad11/preupgrade/postupgrade_fixups.sql
Fixup scripts must be reviewed prior to being executed.
These scripts fixup only the trivial issues which are non-impacting and which doesn’t require DBA attention. it does not fix the issues that could damage the database. For example, dropping Enterprise Manager Database Control, resizing tablespaces, dropping users and roles, gathering statistics, and so on must be fixed manually by the DBA. It will let you know to manually fix those issues before the upgrade.
m) If Database Vault is enabled. It may recommend disabling, because it is a requirement to disable before the upgrade and enable after the upgrade if needed.
n) If any files are in backup mode or in media-recovery-needed state.
o) If the standby database is in sync with the primary.
drop restore point <restore Point name>;
20. Ask app team and all other stakeholder teams for confirmation if all is ok to set the compatible parameter to 12.1.0 to enable its features.
SQL> ALTER SYSTEM SET compatible = '12.1.0' SCOPE=spfile;
Preupgrade tool:
If $ORACLE_BASE is defined, the generated scripts and log files are saved in $ORACLE_BASE/cfgtoollogs/db_unique_name/preupgrade directory. If $ORACLE_BASE is not defined, then the generated scripts and log files are created in $ORACLE_HOME/cfgtoollogs/db_unique_name/preupgrade.
It will check the following and reports issues if found:
a) If the COMPATIBLE parameter is at 11.0.0 or higher. So for 10g databases, it cannot be downgraded. It will need to be restore from backup if needed for downgrade.
b) db parameters. If there is a need to remove hidden parameters, depreciated parameters, and underscore events.
c) components of the database
d) size and free space in system , sysaux, undo, temp tablespaces. If SYS and SYSTEM users have SYSTEM as their default tablespace.
e) resources count like processes,
f) users and roles with the same name e.g. AUDSYS, AUDIT_VIEWER etc.
g) DB control is no longer used in 12c. it is removed during the upgrade but to save the downtime, it recommends to remove it before using below:
- Stop EM Database Control:
$> emctl stop dbconsole
- Connect to the Database using the SYS account AS SYSDBA:
SET ECHO ON;
SET SERVEROUTPUT ON;
@emremove.sql
h) invalid objects,
i) gather stats
EXECUTE dbms_stats.gather_dictionary_stats;
Please create stats on fixed objects after the upgrade using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
j) If the JOB_QUEUE_PROCESSES value is set too low.
k) Purge recyclebin
l) Timezone. 12c using the timezone with version 18. So it is not that it will recommended to update it post upgrade using below:
-- Fix Summary:
-- Update the timezone using the DBMS_DST package after upgrade is complete.
dbms_preup.run_fixup_and_report('OLD_TIME_ZONES_EXIST');
END;
and creates two pre and post upgrade fixup scripts:
Pre-Upgrade Fixup Script (run in source database environment):
/u01/app/oracle/cfgtoollogs/ocad11/preupgrade/preupgrade_fixups.sql
Post-Upgrade Fixup Script (run shortly after upgrade):
/u01/app/oracle/cfgtoollogs/ocad11/preupgrade/postupgrade_fixups.sql
Fixup scripts must be reviewed prior to being executed.
These scripts fixup only the trivial issues which are non-impacting and which doesn’t require DBA attention. it does not fix the issues that could damage the database. For example, dropping Enterprise Manager Database Control, resizing tablespaces, dropping users and roles, gathering statistics, and so on must be fixed manually by the DBA. It will let you know to manually fix those issues before the upgrade.
m) If Database Vault is enabled. It may recommend disabling, because it is a requirement to disable before the upgrade and enable after the upgrade if needed.
n) If any files are in backup mode or in media-recovery-needed state.
o) If the standby database is in sync with the primary.
Catctl.pl step:
Set the below parameters to the new ORACLE_HOME
ORACLE_HOME
ORACLE_SID
LD_LIBRARY_PATH
PATH
ORACLE_BASE
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> startup UPGRADE
$ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l $ORACLE_BASE/admin/$ORACLE_SID/upgrade catupgrd.sql
-n is for parallelism. 4 is the default no of parallelism.
-l is for explicit log location directory.
The db gets shutdown the upgrade completes. Check the upgrade logs and make sure that there is no error occurred in it.
Set the below parameters to the new ORACLE_HOME
ORACLE_HOME
ORACLE_SID
LD_LIBRARY_PATH
PATH
ORACLE_BASE
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> startup UPGRADE
$ORACLE_HOME/perl/bin/perl catctl.pl -n 6 -l $ORACLE_BASE/admin/$ORACLE_SID/upgrade catupgrd.sql
-n is for parallelism. 4 is the default no of parallelism.
-l is for explicit log location directory.
The db gets shutdown the upgrade completes. Check the upgrade logs and make sure that there is no error occurred in it.
No comments:
Post a Comment