HAPPY NEW YEAR 2021

For Any queries, please mail us at support@funoracleapps.com

How to Upgrade Database From 11g To 12c Manually

No comments

How to Upgrade Database From 11g To 12c Manually


Old database version: /ebs121/oracle/DEV121/db/tech_st/11.1.0
New database version: /ebs121/oracle/DEV121/db/tech_st/12.1.0

PRECHECKS:

1.CHECK THE INVALID OBJECTS and components

 
SQL>  select comp_id,status from dba_registry;

COMP_ID                        STATUS
------------------------------ -----------
CATALOG                        VALID
CATPROC                        VALID


2. Check duplicate objects owned by system and sys

select object_name, object_type
from dba_objects
where object_name||object_type in
   (select object_name||object_type
    from dba_objects
    where owner = 'SYS')
and owner = 'SYSTEM';


OBJECT_NAME                                              OBJECT_TYPE
---------------------------------------------------   -------------------
DBMS_REPCAT_AUTH                                         PACKAGE BODY
AQ$_SCHEDULES_PRIMARY                                       INDEX
AQ$_SCHEDULES                                               TABLE
DBMS_REPCAT_AUTH                                           PACKAGE
     
If you found any other objects other than these four, then those need to be cleaned up.




3. Run utlrp.sql to validate invalid objects

SQL>@/ebs121/oracle/DEV121/db/tech_st/11.1.0/rdbms/admin/utlrp.sql
 

4. Run preupgrade tool

Copy the Pre-Upgrade Information Tool script preupgrd.sql and utluppkg.sql from the Oracle Database 12c Release 1 (12.1) i.e /ebs121/oracle/DEV121/db/tech_st/12.1.0/rdbms/admin   to /ebs121/oracle/DEV121/db/tech_st/11.1.0/rdbms/admin

SQL> @$ORACLE_HOME/rdbms/admin/preupgrd.sql
----It will generate below files

preupgrade.log
preupgrade_fixups.sql
postupgrade_fixups.sql
Execute the preupgrade_fixup.sql and check whether changes are reflecting or not.

SQL> @ /ebs121/oracle/cfgtoollogs/PROD/preupgrade/preupgrade_fixups.sql

If still, changes are not reflecting  Check the preupgrade_fixups.sql script and do the changes manually.

5. Dependencies on Network Utility Packages

SQL> SELECT * FROM DBA_DEPENDENCIES
WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_
INADDR','DBMS_LDAP')
AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS'); 

no rows selected
6. Take ddl backup of db_link backups:


7. Check the timezone version:

SQL> SELECT version FROM v$timezone_file;

VERSION
----------
14
 

For 12c, the new timezone is 18. So after the db upgrade is completed, we will upgrade the dst timezone from 14 to 18.

8. Gather Optimizer Statistics

 

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.
 


9. Verify That Materialized View Refreshes Have Completed Before Upgrading

SQL> 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; 2

no rows selected
 

10. Ensure That No Files Need Media Recovery Before Upgrading

 

SQL> SELECT * FROM v$recover_file;

no rows selected
 

11. Ensure That No Files Are in Backup Mode Before Upgrading 

 

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

no rows selected
 

12. Check Outstanding Distributed Transactions Before Upgrading

 

SQL> SELECT * FROM dba_2pc_pending;

no rows selected
 

13. Purge the Database Recycle Bin Before Upgrading :

 

SQL> PURGE DBA_RECYCLEBIN;

DBA Recyclebin purged.
 

14. Disable cronjob,

Take backup of crontab and comment the same.

15. Disable dbms_schduler jobs:

 
SQL> set pagesize 2000
SQL> set lines 2000
SQL> set long 99999
SQL> select owner,JOB_NAME,ENABLED,state from dba_scheduler_jobs;

OWNER                          JOB_NAME                       ENABL STATE
------------------------------ ------------------------------ ----- ---------------
SYS                            SM$CLEAN_AUTO_SPLIT_MERGE      FALSE DISABLED
SYS                            RSE$CLEAN_RECOVERABLE_SCRIPT   FALSE DISABLED
SYS                            BSLN_MAINTAIN_STATS_JOB        FALSE DISABLED
SYS                            DRA_REEVALUATE_OPEN_FAILURES   TRUE  SCHEDULED
SYS                            ORA$AUTOTASK_CLEAN             FALSE DISABLED
SYS                            FILE_WATCHER                   FALSE DISABLED
SYS                            HM_CREATE_OFFLINE_DICTIONARY   FALSE DISABLED
SYS                            AUTO_SPACE_ADVISOR_JOB         FALSE DISABLED
SYS                            GATHER_STATS_JOB               FALSE DISABLED
SYS                            FGR$AUTOPURGE_JOB              FALSE DISABLED
SYS                            PURGE_LOG                      FALSE DISABLED
ORACLE_OCM                     MGMT_STATS_CONFIG_JOB          FALSE DISABLED


13 rows selected.

--- Disable the scheduled jobs by using below command

SQL> execute dbms_scheduler.disable('DRA_REEVALUATE_OPEN_FAILURES);

 

16. Verify system and sys default tablespace.(Both should be system tablespace)

 
SQL> SELECT username, default_tablespace
FROM dba_users
WHERE username in ('SYS','SYSTEM'); 2 3

USERNAME DEFAULT_TABLESPACE
------------------------------ ------------------------------
SYSTEM SYSTEM
SYS SYSTEM
 

17. Review and Remove any unnecessary hidden/underscore parameters

 

SQL> SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;

no rows selected
 
-----------------------------------------------------------------------
Enable flashback

SQL> alter system set db_recovery_file_dest_size=10G scope=both;

System altered.

SQL> alter system set db_recovery_file_dest='/oradump/himanshu' scope=both;

System altered.

SQL> alter database flashback on;

Database altered.

SQL> startup force
Now create restore point:

CREATE RESTORE POINT BEFORE_UPGRADE GUARANTEE FLASHBACK DATABASE;

Restore point created.
 

UPGRADE:
 

18. stop the listener and shutdown the database.

lsnrctl stop DEV121

SQL>shutdown immediate

19.Update the ORACLE_HOME,PATH pointing to 12C Home.

export ORACLE_HOME=/ebs121/oracle/DEV121/db/tech_st/12.1.0

export PATH=$ORACLE_HOME/bin:$PATH

export LD_LIBRARY_PATH=$ORACLE_HOME/lib

export CLASS_PATH=$ORACLE_HOME/jlib

export ORACLE_SID=DEV121
export PERL5LIB=$ORACLE_HOME/perl/lib/5.14.1:$ORACLE_HOME/perl/lib/site_perl/5.14.1

20. Move the spfile and password file from 11g oracle_home/dbs location to 12cHome/dbs location.

 
21. start the database in upgrade mode.

$ cd $ORACLE_HOME/rdbms/admin
$ pwd
/ebs121/oracle/DEV121/db/tech_st/12.1.0/rdbms/admin

$ sqlplus "/ as sysdba"
SQL> startup UPGRADE
SQL> exit

 
22.Run catupgrade script from os level with parallel=4 as below.

cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 -l $ORACLE_HOME/diagnostics catupgrd.sql

Monitor the log.

cd /ebs121/oracle/DEV121/db/tech_st/12.1.0/diagnostics
tail -100f catupgrd0.log

 
23. Run the Post-Upgrade Status Tool

 ---Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu121s.sql which provides a summary of the upgrade

$ sqlplus "/as sysdba"
SQL> STARTUP
SQL> @utlu121s.sql

24.Run Catuppst.sql

              The catuppst.sql script is run as part of the upgrade process unless the upgrade returns errors during the process. Check the log file for “BEGIN catuppst.sql” to verify that catuppst.sql ran during the upgrade process. If catuppst.sql has not run, then proceed to run catuppst.sql as shown in this step. Warning messages are also displayed when running catctl.pl indicating that catuppst.sql was not run during the upgrade.

SQL>@$ORACLE_HOME/rdbms/admin/catuppst.sql
 

This completes our upgrade

25. UPGRADE DST TIME ZONE:

 

Download the dst upgrade script from oracle

 

--- unzip the file in
$ unzip DBMS_DST_scriptsV1.9.zip
Archive: DBMS_DST_scriptsV1.9.zip
creating: DBMS_DST_scriptsV1.9/
inflating: DBMS_DST_scriptsV1.9/countstarTSTZ.sql
inflating: DBMS_DST_scriptsV1.9/countstatsTSTZ.sql
inflating: DBMS_DST_scriptsV1.9/upg_tzv_apply.sql
inflating: DBMS_DST_scriptsV1.9/upg_tzv_check.sql

----

$ cd DBMS_DST_scriptsV1.9

-- run countstatsTSTZ.sql script.


SQL> spool countstatsTSTZ.log
SQL> @countstatsTSTZ.sql
.
Purge Scheduler job

SQL> exec dbms_scheduler.purge_log;

PL/SQL procedure successfully completed.
 

Run upg_tzv_check.sql ( it will detect the highest installed DST patch automatically)

 

SQL> spool upg_tzv_check.log
SQL> @upg_tzv_check.sql
INFO: Starting with RDBMS DST update preparation.
INFO: NO actual RDBMS DST update will be done by this script.
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: Doing checks for known issues ...
INFO: Database version is 12.1.0.2 .
INFO: Database RDBMS DST version is DSTv14 .
INFO: No known issues detected.
INFO: Now detecting new RDBMS DST version.
A prepare window has been successfully started.
INFO: Newest RDBMS DST version detected is DSTv18 .
INFO: Next step is checking all TSTZ data.
INFO: It might take a while before any further output is seen ...
A prepare window has been successfully ended.
INFO: A newer RDBMS DST version than the one currently used is found.
INFO: Note that NO DST update was yet done.
INFO: Now run upg_tzv_apply.sql to do the actual RDBMS DST update.
INFO: Note that the upg_tzv_apply.sql script will
INFO: restart the database 2 times WITHOUT any confirmation or prompt.
SQL> spool off
SQL> spool upg_tzv_apply.log

 

Run upg_tzv_apply.sql ( It will do the actual dst upgrade)

 

SQL> spool upg_tzv_apply.log
SQL> @upg_tzv_apply.sql
INFO: If an ERROR occurs the script will EXIT sqlplus.
INFO: The database RDBMS DST version will be updated to DSTv18 .
WARNING: This script will restart the database 2 times
WARNING: WITHOUT asking ANY confirmation.
WARNING: Hit control-c NOW if this is not intended.
INFO: Restarting the database in UPGRADE mode to start the DST upgrade.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 2147483648 bytes
Fixed Size 2916104 bytes
Variable Size 1677721848 bytes
Database Buffers 452984832 bytes
Redo Buffers 13860864 bytes
Database mounted.
Database opened.
INFO: Starting the RDBMS DST upgrade.
INFO: Upgrading all SYS owned TSTZ data.
INFO: It might take time before any further output is seen ...
An upgrade window has been successfully started.
INFO: Restarting the database in NORMAL mode to upgrade non-SYS TSTZ data.
Database closed.
Database dismounted.
ORACLE instance shut down.
ORACLE instance started.

Total System Global Area 3247483648 bytes
Fixed Size 3016104 bytes
Variable Size 2477721848 bytes
Database Buffers 452984832 bytes
Redo Buffers 13860864 bytes
Database mounted.
Database opened.
INFO: Upgrading all non-SYS TSTZ data.
INFO: It might take time before any further output is seen ...
INFO: Do NOT start any application yet that uses TSTZ data!
INFO: Next is a list of all upgraded tables:
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_S"
Number of failures: 0
Table list: "GSMADMIN_INTERNAL"."AQ$_CHANGE_LOG_QUEUE_TABLE_L"
Number of failures: 0
INFO: Total failures during update of TSTZ data: 0 .
An upgrade window has been successfully ended.
INFO: Your new Server RDBMS DST version is DSTv18 .
INFO: The RDBMS DST update is successfully finished.
INFO: Make sure to exit this sqlplus session.
INFO: Do not use it for timezone related selects.


 

Once dst upgrade is successful , validate the time_zone(It should be 18)

1. SQL>@/ebs121/oracle/cfgtoollogs/DEV121/preupgrade/postupgrade_fixup.sql

2. Change the ORACLE_HOME to 12c in listener.ora file.

3. Uncomment the crontab

4.Enable the jobs in dba_scheduler_jobs which we disabled before the upgrade.

5. Run utluiobj.sql from $ORACLE_HOME/rdbms/admin/ to identify/compare any new invalid objects due to the upgrade.

6. Run $ORACLE_HOME/rdbms/admin/utlrp.sql script

 

SQL> SELECT version FROM v$timezone_file;

VERSION
----------
18
SQL> drop restore point BEF_UPGRADE;

7. Update the compatible parameter and restart the database.

NOTE- Database cannot be downgraded once a compatible parameter is updated. 

--- MAKE SURE TO DROP THE RESTORE POINT:


SQL> drop restore point BEF_UPGRADE;

Restore point dropped.

-- Now alter the compatible parameter

SQL>alter system set compatible='12.1.0.2' scope=spfile;

shutdown immediate;
startup

 

NOTE – Once compatible is set to higher version, downgrade is not possible.

Upgrade is completed.

 

In case UPGRADE FAILS

1. Shutdown immediate;

2. set ORACLE_HOME to 11g 

3. Start up mount ( with the 11g spfile)

4. select * from v$restore_point;

5. flashback database to restore point before_upgrade. ( this restore point was created before upgrade)

6. alter database open resetlogs;






If you like please follow and comment

No comments :

Post a Comment