Complete Steps:
============
Note:
-------
Below Action Plan is for Non RAC DB, If it is RAC means, Upgrade the CRS to 12c and proceed with db upgrade. Only change is just disable the cluster db parameter initially and enable back once upgrade complete.
1. Set the blackouts & perform pre Health Checks.
2. Backup the Database and File System.
2.1 Take Cold Backup.
2.2 Start up the DB only
2.3 Check for the integrity of the source database.
Note 556610.1 Script to Collect DB Upgrade/Migrate Diagnostic Information (dbupgdiag.sql)
If the dbupgdiag.sql script reports any invalid objects, run $ORACLE_HOME/rdbms/admin/utlrp.sql (multiple times) to validate the invalid objects in the database
until there is no change in the number of invalid objects.
$ cd $ORACLE_HOME/rdbms/admin
$ sqlplus "/ as sysdba"
SQL> @utlrp.sql
3. Install the 12C DB from the "12C DB Software" location and run the new Pre-Upgrade Information Tool & verify
3.1 SQL> @$NEW_ORACLE_HOME/rdbms/admin/preupgrd.sql
preupgrade.log,
preupgrade_fixups.sql
postupgrade_fixups.sql
files are created in $ORACLE_HOME/cfgtoollogs/$ORACLE_SID/preupgrade/
which is under the $ORACLE_HOME that needs to be upgraded.
3.2 Run "preupgrade_fixups.sql" script
3.3 Execute the suggested changes from preupgrade_fixups.sql script.
Verify all items are fixed.
4. Disable the Processes & Jobs
4.1 Invalids compilation and backup:
SET LINES 400
SET PAGES 400
COL OBJECT_NAME FOR A30
select count(*) from registry$sys_inv_objs;
select count(*) from registry$nonsys_inv_objs;
select * from registry$nonsys_inv_objs;
select owner,count(object_name) INVALIDS from dba_objects where status ='INVALID' group by owner;
select count(object_type),object_type from dba_objects where status='INVALID'group by object_type;
SELECT COUNT(*) FROM DBA_OBJECTS WHERE STATUS='INVALID';
select object_name,object_type,owner from dba_objects where status='INVALID';
4.2 Compile Invalid Objects
@$ORACLE_HOME/rdbms/admin/utlrp.sql
4.3 If Invalids remain same, try to compile manually by logging into the respective owner
4.4 If Invalids still remains same, Proceed further by taking invalids backup
create table invalids_bkp_rfcnum as (select object_name,owner from registry$nonsys_inv_objs);
4.5 Disable process, jobs
spool /tmp/before_upgrade_values.txt
show parameters job_queue_process
select job_name, enabled from dba_scheduler_jobs;
alter system set job_queue_processes=0;
4.5.1 Disable the Scheduler Jobs.
select job_name, enabled from dba_scheduler_jobs;
exec dbms_scheduler.disable('&Job_Name');
OR
select 'exec dbms_scheduler.disable('''||owner||'.'||JOB_NAME||''');' from dba_scheduler_jobs where STATE<>'DISABLED' and STATE<>'SUCCEEDED' order by NEXT_RUN_DATE desc;
select username, account_status from dba_users order by 1 asc;
spool off
4.6 Purge Recycle Bin =========> verify in "preupgrade_fixups.sql", if it's taken care ignore it.
purge dba_recyclebin;
4.7 Gather the Stats. =========> verify in "preupgrade_fixups.sql", if it's taken care ignore it.
exec dbms_stats.gather_dictionary_stats;
4.8 Backup the Pfile, Add the hidden parameters in PFILE and comment it.
SELECT name,description from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\';
4.9 Verify the Events. Comment the "Event" parameter in PFILE.
4.10 Get DBA_REGISTRY information
sqlplus '/ as sysdba'
select COMP_ID,COMP_NAME,VERSION,STATUS,MODIFIED from dba_registry;
4.11 Check Time Zone, if it is 14 & below, its eligible for upgrade, which is taken care in further steps.
SELECT version FROM v$timezone_file;
4.12 Verify That Materialized View Refreshes Have Completed Before Upgrading
Before upgrading Oracle Database, you must wait until all materialized views have completed refreshing.
Run the following query to determine if there are any materialized view refreshes still in progress:
select s.obj#,o.obj#,s.containerobj#,lastrefreshdate,pflags,xpflags,o.name,o.owner#, bitand(s.mflags, 8) from obj$ o, sum$ s
where o.obj# = s.obj# and o.type# = 42 AND bitand(s.mflags, 8) = 8;
#### If the above query returns any row, then use Note 1442457.1 : During 11g Upgrade, Mview refresh warning ###
4.13 Ensure That No Files Need Media Recovery Before Upgrading
SELECT * FROM v$recover_file;
4.14 Ensure That No Files Are in Backup Mode Before Upgrading
SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';
4.15 Resolve Outstanding Distributed Transactions Before Upgrading, To resolve outstanding distributed transactions:
1. Issue the following statement:
SQL> SELECT * FROM dba_2pc_pending;
2. If the query in the previous step returns any rows, then issue the following statements:
SQL> SELECT local_tran_id FROM dba_2pc_pending;
SQL> EXECUTE dbms_transaction.purge_lost_db_entry('');
SQL> COMMIT;
4.16 Verify SYS and SYSTEM Default tablespace
Ensure the users SYS and SYSTEM have 'SYSTEM' as their default tablespace. You must have sufficient space in the 'SYSTEM' tablespace
SQL> SELECT username, default_tablespace FROM dba_users WHERE username in ('SYS','SYSTEM');
4.17 Check whether database has any externally authenticated SSL users
SQL> SELECT name FROM sys.user$ WHERE ext_username IS NOT NULL AND password = 'GLOBAL';
4.18 Check OLS / DB valult is enabled. If Disabled proceed further.
SELECT * FROM V$OPTION WHERE PARAMETER = 'Oracle Database Vault';
4.19 Stop the listener for the database.
$ lsnrctl stop
Just Copy the files from old "$ORACLE_HOME/network/admin" to new "$ORACLE_HOME/network/admin" and change the instance names in all related files.
4.12 Shut Down the DB.
shutdown immediate
exit;
6. Reset environment variables with oraenv
6.1 Copy the parameter file [PFILE & SPFILE] and password files from the old oracle home to the 12c home.
6.2 Edit the "/etc/oratab" file, setting the new ORACLE_HOME value.
6.2.1 Change the .profile & .bash_profile with new DB path, i.e, with new ORACLE_HOME
6.2.2 Add below parameters in PFILE
DIAGNOSTIC_DEST=/test/log
6.3.3 Comment below parameters in PFILE as they are deprecated.
Note: CORE_DUMP_DEST is not deprecated in 12C
SEC_CASE_SENSITIVE_LOGON
USER_DUMP_DEST
BACKGROUND_DUMP_DEST
6.3.4 Change /etc/oratab & set the below settings in VNC
export ORACLE_BASE=/test/oracle
export ORACLE_HOME=/test/oracle/product/12102
export PATH=$PATH:$HOME/bin:/usr/local/git/bin:$ORACLE_HOME/bin:$ORACLE_HOME/ctx/bin:$ORACLE_HOME/OPatch:$ORACLE_HOME/perl/bin:$ORACLE_HOME/oui/bin:$PATH
export LD_LIBRARY_PATH=$LD_LIBRARY_PATH:$ORACLE_HOME/lib:$ORACLE_HOME/ctx/lib
7. Start the database in upgrade mode in VNC session.
$ sqlplus / as sysdba
SQL> SPOOL upgrade_12c.log
SQL> STARTUP UPGRADE;
Monitor the alert log file and check for any errors.
SQL> EXIT;
You can alter the level of parallelism using the "-n" parameter.
For example, the following command runs the upgrade with a parallel level of 6.
Run the catctl.pl script from the new Oracle home.
In this release, the new Upgrade Utility, catctl.pl, replaces catupgrd.sql.
cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 -l $ORACLE_HOME/diagnostics catupgrd.sql
8. Once Upgrade completed, Startup the DB.
Post Upgrade Steps:
====================
9. Run the "utlu121s.sql" script to check the summary of the upgrade results.
$ sqlplus / as sysdba
SQL> @utlu121s.sql
10. If we had no errors, the "catuppst.sql" script would have been run as part of the upgrade. If we face any errors, we need to run it manually
after fixing the errors.
$ cd $ORACLE_HOME/rdbms/admin
SQL> @catuppst.sql
11. Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.
SQL> @utlrp.sql
12. Check for the integrity of the upgraded database by running dbupgdiag.sql script from the below article:
13. After validating the invalid objects, re-run dbupgdiag.sql in the upgraded database once again and make sure that everything is fine.
14. If the "postupgrade_fixups.sql" file contained any recommendations, run it now.
SQL> @$ORACLE_HOME/cfgtoollogs/$ORACLE_SID/preupgrade/postupgrade_fixups.sql
15. Gather the Stats and recompile objects.
-- The following item is probably included in your postupgrade_fixups.sql script.Verify.
EXECUTE DBMS_STATS.gather_fixed_objects_stats;
-- Recompile invalid objects.
@utlrp.sql
-- Check for newly invalid objects.
@utluiobj.sql
-- Run again to check the final outcome of the upgrade.
@utlu121s.sql
16. Update the Time Zone.
Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12cR1 database . (Doc ID 1585343.1)
17. Reset the Job queue processes
alter system set job_queue_processes=10;
select job_name, enabled from dba_scheduler_jobs;
Enable the Scheduler Jobs.
select job_name, enabled from dba_scheduler_jobs;
exec dbms_scheduler.enable('&Job_Name');
18. Verify DBA_REGISTRY information
sqlplus '/ as sysdba'
select COMP_ID,COMP_NAME,VERSION,STATUS,MODIFIED from dba_registry;
19. Identify Invalid Objects With the "utluiobj" Script
Before the upgrade ,the list of invalid SYS/SYSTEM objects is written to registry$sys_inv_objs and non-SYS/SYSTEM objects was written to registry$nonsys_inv_objs by the Pre-Upgrade Information Tool
After the upgrade, run utluiobj.sql from $ORACLE_HOME/rdbms/admin/ to identify/compare any new invalid objects due to the upgrade.
20. Raise an SR with EM team to update the targets info with new versions
21. Unset the blackouts , start up MT services & perform post Health Checks.
22. Once the customer does the post release testing, start up the DB with COMPATIBLE=12.1.0.2
Comments
Post a Comment