Label

Monday, June 12, 2017

Database Upgrade 12c




---------------------------------------------------
1. Pre upgrades tasks
---------------------------------------------------
a) Full backup of source database.
b) Install 12c database binaries.
c) Create pfile from spfile on source database and get it shipped to new $ORACLE_HOME/dbs/ directory.
SQL > create pfile from spfile;
d) Edit the shipped pfile.
e) Remove parameter sec_case_sensitive_logon       -- obsolete in 12c
f) Change the value of DIAGNOSTIC_DEST to 12c $ORACLE_BASE
g) Copy the below file from 12c $ORACLE_HOME/rdbms/admin to 11g $ORACLE_HOME/rdbms/admin
                1.preupgrade.sql
2.  utluppkg.sql
3. emremove.sql    -- to remove EM repository data
4. catnoamd.sql    -- located in 12c $ORACLE_HOME/olap/admin  -- OLAP Catalog(AMD) desupported
5.olspreupgrade.sql  --if label security and database vault is configured.

h) Execute preupgrd.sql        -- it acts like utlu112i.sql in oracle 11g R2
                The sql script create 3 files in $ORACLE_BASE/cfgtoollogs/<dbname>/preupgrade
1.       Preupgrade.log    -- log generated by executing preupgrd.sql contains warnings and recommendation prior to upgrade
2.       Preupgrade_fixups.sql    -- execute it before upgrade and get warnings and recommendations fixed.
3.       Postupgrade_fixups.sql    -- execute it just after the upgrade.
Shut down the database, which is to be upgraded.
Stop the lower version listener and create a new one in 12c oracle home using NETCA.
Configure TNS entry in new oracle home using NETCA.

---------------------------------------------------------------
Upgrade Tasks           --   CATUPGRD.SQL replaced by CATCTL.PL
---------------------------------------------------------------

In Linux ---------
                Edit the oratab file, point the sid to new oracle home.
                Export ORACLE_SID=<dbname>
                . oraenv
In Windows ------
                Drop the sid with oradim utility by executing it from the old oracle home, close the session.
                Create the same sid with oradim utility by executing from the new oracle home 12c   
                Open the db in upgrade mode.






Execute Catupgrd.sql    -------------   OBSOLETE in 12c replaced by catctl.pl

While updating data dictionary we have to go in this way.
                Cd $ORACLE_HOME/rdbms/admin
                $ORACLE_HOME/perl/bin/perl catctl.pl –n 4 catupgrd.sql   -- Where parallelism is 4 (n=4)
               
From SQL prompt we have to provide the below mentioned command.
                SQL> @?/rdbms/admin/catupgrd.sql PARALLEL=NO


-----------------------------------------------
Post Upgrade Tasks
-----------------------------------------------

Startup the database in normal mode.
SQL > startup
Execute utlu121s.sql   -- List status of upgraded components.
SQL> @?/rdbms/admin/utlu121s.sql
Execute catuppst.sql   -- upgrade those components which don’t require db in upgrade mode.
SQL> @?/rdbms/admin/catuppst.sql
Execute utlrp.sql          -- Recompile the invalid objects.
SQL> @?/rdbms/admin/utlrp.sql
Execute utluiobj.sql     -- list invalid objects after upgrade.
SQL> @?/rdbms/admin/utluiobj.sql

Startup the db in upgrade mode to update the Time zone version to 18 as recommended in preupgrade_fixups.sql.

SQL> STARTUP UPGRADE
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
     FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
   
     PROPERTY_NAME                  VALUE
     ------------------------------ ------------------------------
     DST_PRIMARY_TT_VERSION        1 4
     DST_SECONDARY_TT_VERSION       0
     DST_UPGRADE_STATE              NONE

SQL> alter session set "_with_subquery"=materialize;
SQL> alter session set "_simple_view_merging"=TRUE;
SQL> exec DBMS_DST.BEGIN_PREPARE(18)

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
     FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
   
     PROPERTY_NAME                  VALUE
     ------------------------------ ------------------------------
     DST_PRIMARY_TT_VERSION         14
     DST_SECONDARY_TT_VERSION       18
     DST_UPGRADE_STATE              PREPARE

SQL> TRUNCATE TABLE SYS.DST$TRIGGER_TABLE;
SQL> TRUNCATE TABLE sys.dst$affected_tables;
SQL> TRUNCATE TABLE sys.dst$error_table;


SQL> set serveroutput on
SQL> BEGIN
     DBMS_DST.FIND_AFFECTED_TABLES
     (affected_tables => 'sys.dst$affected_tables',
     log_errors => TRUE,
     log_errors_table => 'sys.dst$error_table');
     END;
     /
SQL> SELECT * FROM sys.dst$affected_tables;
SQL> SELECT * FROM sys.dst$error_table;
SQL> EXEC DBMS_DST.END_PREPARE;
-------------------------
Upgrade Timezone version
-------------------------

SQL> purge dba_recyclebin;
SQL> alter session set "_with_subquery"=materialize;
SQL> alter session set "_simple_view_merging"=TRUE;
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(18);

SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
     FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
   
     PROPERTY_NAME                  VALUE
     ------------------------------ ------------------------------
     DST_PRIMARY_TT_VERSION         18
     DST_SECONDARY_TT_VERSION      14
     DST_UPGRADE_STATE              UPGRADE

SQL> SELECT OWNER, TABLE_NAME, UPGRADE_IN_PROGRESS FROM ALL_TSTZ_TABLES where UPGRADE_IN_PROGRESS='YES';

11 rows selected.       ----- Number may be different

SQL> shutdown immediate
SQL> startup
SQL> alter session set "_with_subquery"=materialize;
SQL> alter session set "_simple_view_merging"=TRUE;

SQL> set serveroutput on
     VAR numfail number
     BEGIN
     DBMS_DST.UPGRADE_DATABASE(:numfail,
     parallel => TRUE,
     log_errors => TRUE,
     log_errors_table => 'SYS.DST$ERROR_TABLE',
     log_triggers_table => 'SYS.DST$TRIGGER_TABLE',
     error_on_overlap_time => FALSE,
     error_on_nonexisting_time => FALSE);
     DBMS_OUTPUT.PUT_LINE('Failures:'|| :numfail);
     END;
     /
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_STREAMS_QUEUE_TABLE_L"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_S"
Number of failures: 0
Table list: "IX"."AQ$_ORDERS_QUEUETABLE_L"
Number of failures: 0
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
Table list: "APEX_040200"."WWV_FLOW_WORKSHEET_NOTIFY"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_FEEDBACK_FOLLOWUP"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_FEEDBACK"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES2"
Number of failures: 0
Table list: "APEX_040200"."WWV_FLOW_DEBUG_MESSAGES"
Number of failures: 0
Failures:0

SQL> VAR fail number
     BEGIN
     DBMS_DST.END_UPGRADE(:fail);
     DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
     END;
     /

An upgrade window has been successfully ended.
Failures:0






SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
     FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;
   
     PROPERTY_NAME                  VALUE
     ------------------------------ ------------------------------
     DST_PRIMARY_TT_VERSION         18
     DST_SECONDARY_TT_VERSION       0
     DST_UPGRADE_STATE              NONE


SQL> SELECT * FROM v$timezone_file;

FILENAME                VERSION     CON_ID
-------------------- ---------- ----------
timezlrg_18.dat              18          0

If registry is not updated update it.

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
        14

SQL> update registry$database set TZ_VERSION = (select version FROM v$timezone_file);

1 row updated.

SQL> select TZ_VERSION from registry$database;

TZ_VERSION
----------
        18

SQL> SELECT value$ FROM sys.props$ WHERE NAME = 'DST_PRIMARY_TT_VERSION';

VALUE$
--------------------------------------------------------------------------------
18
    

Reset compatible parameter to 12.1.0
Startup the db in normal mode.

No comments:

Post a Comment