---------------------------------------------------
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