Minimal downtime database upgrade from Oracle 10g to Oracle 12c
- Posted by Gavin Soorma
- On December 8, 2014
- 0 Comments
This note describes the procedure of migrating as well as upgrading an Oracle 10g Release 2 database to Oracle 12c (12.1.0.2) using a combination of RMAN Incremental backups and the new Oracle 12c command line parallel upgrade utility.
This method minimises the outage required for the migration as well as database upgrade and the outage is limited to the time it takes to backup the last set of archive log files generated since the last Level 1 incremental backup and the time taken to run the catupgrd.sql upgrade script (which can now be run from the command line in parallel).
In this example we are moving a database from one Linux host 5.3 to another Linux 5.7 host, moving from ASM to non-ASM as well as upgrading the database.
Let us take a look at the steps involved.
Copy the new 12c Pre-Upgrade scripts to the Oracle 10g database server and execute it in the Oracle 10.2.0.5 database
[oracle@cls18 ~]$ mkdir preupgrade
[oracle@cls18 ~]$ cd preupgrade/
[oracle@cls18 preupgrade]$ scp -rp oracle@198.168.82.211:/app/oracle/product/12.1.0.2/dbhome_1/preupgrade/* .
oracle@198.168.82.211's password:
emremove.sql 100% 19KB 19.2KB/s 00:00
preupgrd.sql 100% 14KB 13.8KB/s 00:00
utluppkg.sql 100% 484KB 483.9KB/s 00:00
[oracle@cls18 preupgrade]$ sqlplus sys as sysdba
SQL*Plus: Release 10.2.0.5.0 - Production on Mon Dec 1 09:25:44 2014
Copyright (c) 1982, 2010, Oracle. All Rights Reserved.
Enter password:
Connected to:
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bit Production
With the Partitioning, Real Application Clusters, OLAP, Data Mining
and Real Application Testing options
SQL> @preupgrd.sql
Loading Pre-Upgrade Package...
***************************************************************************
Executing Pre-Upgrade Checks in DEV18...
***************************************************************************
************************************************************
====>> ERRORS FOUND for DEV18 <<====
The following are *** ERROR LEVEL CONDITIONS *** that must be addressed
prior to attempting your upgrade.
Failure to do so will result in a failed upgrade.
1) Check Tag: COMPATIBLE_PARAMETER
Check Summary: Verify compatible parameter value is valid
Fixup Summary:
""compatible" parameter must be increased manually prior to upgrade."
+++ Source Database Manual Action Required +++
2) Check Tag: PURGE_RECYCLEBIN
Check Summary: Check that recycle bin is empty prior to upgrade
Fixup Summary:
"The recycle bin will be purged."
You MUST resolve the above errors prior to upgrade
************************************************************
************************************************************
====>> PRE-UPGRADE RESULTS for DEV18 <<====
ACTIONS REQUIRED:
1. Review results of the pre-upgrade checks:
/app/DEV18/product/product/10.2.0/db_1/cfgtoollogs/DEV18/preupgrade/preupgrade.log
2. Execute in the SOURCE environment BEFORE upgrade:
/app/DEV18/product/product/10.2.0/db_1/cfgtoollogs/DEV18/preupgrade/preupgrade_fixups.sql
3. Execute in the NEW environment AFTER upgrade:
/app/DEV18/product/product/10.2.0/db_1/cfgtoollogs/DEV18/preupgrade/postupgrade_fixups.sql
************************************************************
***************************************************************************
Pre-Upgrade Checks in DEV18 Completed.
***************************************************************************
***************************************************************************
***************************************************************************
SQL>
Review the preupgrade.log file
[oracle@cls18 preupgrade]$ cat /app/DEV18/product/product/10.2.0/db_1/cfgtoollogs/DEV18/preupgrade/preupgrade.log
Oracle Database Pre-Upgrade Information Tool 12-01-2014 09:26:09
Script Version: 12.1.0.2.0 Build: 006
**********************************************************************
Database Name: DEV18
Container Name: Not Applicable in Pre-12.1 database
Container ID: Not Applicable in Pre-12.1 database
Version: 10.2.0.5.0
Compatible: 10.2.0.3.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone file: V14
**********************************************************************
[Update parameters]
--> If Target Oracle is 64-bit, refer here for Update Parameters:
WARNING: --> "sga_target" needs to be increased to at least 650117120
**********************************************************************
**********************************************************************
[Renamed Parameters]
[No Renamed Parameters in use]
**********************************************************************
**********************************************************************
[Obsolete/Deprecated Parameters]
--> background_dump_dest 11.1 DESUPPORTED replaced by "diagnostic_dest"
--> user_dump_dest 11.1 DESUPPORTED replaced by "diagnostic_dest"
[Changes required in Oracle Database init.ora or spfile]
**********************************************************************
[Component List]
**********************************************************************
--> Oracle Catalog Views [upgrade] VALID
--> Oracle Packages and Types [upgrade] VALID
--> JServer JAVA Virtual Machine [upgrade] VALID
--> Oracle XDK for Java [upgrade] VALID
--> Real Application Clusters [upgrade] VALID
--> Oracle Workspace Manager [upgrade] VALID
--> OLAP Analytic Workspace [upgrade] VALID
--> Oracle Enterprise Manager Repository [upgrade] VALID
--> Oracle Text [upgrade] VALID
--> Oracle XML Database [upgrade] VALID
--> Oracle Java Packages [upgrade] VALID
--> Oracle Multimedia [upgrade] VALID
--> Oracle Spatial [upgrade] VALID
--> Data Mining [upgrade] VALID
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
**********************************************************************
[Tablespaces]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
minimum required size: 1259 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
minimum required size: 400 MB
--> SYSAUX tablespace is adequate for the upgrade.
minimum required size: 1552 MB
--> TEMP tablespace is adequate for the upgrade.
minimum required size: 60 MB
[No adjustments recommended]
**********************************************************************
**********************************************************************
[Pre-Upgrade Checks]
**********************************************************************
ERROR: --> Compatible set too low
"compatible" currently set at 10.2.0.3.0 and must
be set to at least 11.0.0 prior to upgrading the database.
Do not make this change until you are ready to upgrade
because a downgrade back to 10.2 is not possible once compatible
has been raised.
Update your init.ora or spfile to make this change.
WARNING: --> "ORACLE_OCM" user found in database
This is an internal account used by Oracle Configuration Manager.
Please drop this user prior to upgrading.
WARNING: --> Enterprise Manager Database Control repository found in the database
In Oracle Database 12c, Database Control is removed during
the upgrade. To save time during the Upgrade, this action
can be done prior to upgrading using the following steps after
copying rdbms/admin/emremove.sql from the new Oracle home
- 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
Without the set echo and serveroutput commands you will not
be able to follow the progress of the script.
WARNING: --> "DMSYS" schema exists in the database
The DMSYS schema (Oracle Data Mining) will be removed
from the database during the database upgrade.
All data in DMSYS will be preserved under the SYS schema.
Refer to the Oracle Data Mining User's Guide for details.
WARNING: --> Database contains INVALID objects prior to upgrade
The list of invalid SYS/SYSTEM objects was written to
registry$sys_inv_objs.
The list of non-SYS/SYSTEM objects was written to
registry$nonsys_inv_objs unless there were over 5000.
Use utluiobj.sql after the upgrade to identify any new invalid
objects due to the upgrade.
INFORMATION: --> OLAP Catalog(AMD) exists in database
Starting with Oracle Database 12c, OLAP Catalog component is desupported.
If you are not using the OLAP Catalog component and want
to remove it, then execute the
ORACLE_HOME/olap/admin/catnoamd.sql script before or
after the upgrade.
INFORMATION: --> Older Timezone in use
Database is using a time zone file older than version 18.
After the upgrade, it is recommended that DBMS_DST package
be used to upgrade the 10.2.0.5.0 database time zone version
to the latest version which comes with the new release.
Please refer to My Oracle Support note number 977512.1 for details.
ERROR: --> RECYCLE_BIN not empty.
Your recycle bin contains 56 object(s).
It is REQUIRED that the recycle bin is empty prior to upgrading.
Immediately before performing the upgrade, execute the following
command:
EXECUTE dbms_preup.purge_recyclebin_fixup;
WARNING: --> Existing schemas with network ACLs exist
Database contains schemas with objects dependent on network packages.
Refer to the Upgrade Guide for instructions to configure Network ACLs.
USER UTILITY has dependent objects.
USER DBAMON has dependent objects.
USER ORACLE_OCM has dependent objects.
INFORMATION: --> There are existing Oracle components that will NOT be
upgraded by the database upgrade script. Typically, such components
have their own upgrade scripts, are deprecated, or obsolete.
Those components are: OLAP Catalog
**********************************************************************
[Pre-Upgrade Recommendations]
**********************************************************************
*****************************************
********* Dictionary Statistics *********
*****************************************
Please gather dictionary statistics 24 hours prior to
upgrading the database.
To gather dictionary statistics execute the following command
while connected as SYSDBA:
EXECUTE dbms_stats.gather_dictionary_stats;
^^^ MANUAL ACTION SUGGESTED ^^^
**********************************************************************
[Post-Upgrade Recommendations]
**********************************************************************
*****************************************
******** Fixed Object Statistics ********
*****************************************
Please create stats on fixed objects two weeks
after the upgrade using the command:
EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
^^^ MANUAL ACTION SUGGESTED ^^^
**********************************************************************
************ Summary ************
2 ERRORS exist that must be addressed prior to performing your upgrade.
5 WARNINGS that Oracle suggests are addressed to improve database performance.
3 INFORMATIONAL messages that should be reviewed prior to your upgrade.
After your database is upgraded and open in normal mode you must run
rdbms/admin/catuppst.sql which executes several required tasks and completes
the upgrade process.
You should follow that with the execution of rdbms/admin/utlrp.sql, and a
comparison of invalid objects before and after the upgrade using
rdbms/admin/utluiobj.sql
If needed you may want to upgrade your timezone data using the process
described in My Oracle Support note 1509653.1
***********************************
In the Oracle 10g database execute the Pre-Upgrade fixup scripts
SQL> EXECUTE dbms_preup.purge_recyclebin_fixup; PL/SQL procedure successfully completed. SQL> EXECUTE dbms_stats.gather_dictionary_stats; PL/SQL procedure successfully completed. SQL> SET ECHO ON; SQL> SET SERVEROUTPUT ON; SQL> @emremove.sql ..... Dropping synonym : MGMT$TARGET_PROPERTIES ... Dropping synonym : MGMT$TARGET_TYPE ... Finished phase 5 Starting phase 6 : Dropping Oracle Enterprise Manager related other roles ... Finished phase 6 The Oracle Enterprise Manager related schemas and objects are dropped. Do the manual steps to studown the DB Control if not done before running this script and then delete the DB Control configuration files PL/SQL procedure successfully completed.
Take a level 0 RMAN Incremental backup
RMAN> run {
2> allocate channel c1 type disk;
3> backup incremental level 0 database format '/app/DEV18/oradump/bkp_lev0.%U';
4> release channel c1;
5> }
released channel: ORA_SBT_TAPE_1
released channel: ORA_SBT_TAPE_2
released channel: ORA_SBT_TAPE_3
released channel: ORA_SBT_TAPE_4
released channel: ORA_SBT_TAPE_5
allocated channel: c1
channel c1: sid=266 instance=DEV181 devtype=DISK
Starting backup at 01-DEC-14
channel c1: starting incremental level 0 datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00010 name=+DEV18_DATA_01/DEV18/datafile/utility_data.266.798291365
input datafile fno=00003 name=+DEV18_DATA_01/DEV18/datafile/sysaux.257.747180651
...
...
input datafile fno=00015 name=+DEV18_DATA_01/DEV18/datafile/rpo_data.276.798291365
input datafile fno=00020 name=+DEV18_DATA_01/DEV18/datafile/dw_data.282.821809927
channel c1: starting piece 1 at 01-DEC-14
channel c1: finished piece 1 at 01-DEC-14
piece handle=/app/DEV18/oradump/bkp_lev0.8ppp2gus_1_1 tag=TAG20141201T094147 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:25
Finished backup at 01-DEC-14
Starting Control File and SPFILE Autobackup at 01-DEC-14
piece handle=/app/DEV18/product/product/10.2.0/db_1/dbs/c-1508569629-20141201-00 comment=NONE
Finished Control File and SPFILE Autobackup at 01-DEC-14
released channel: c1
Copy the password file and init.ora from 10g source to Oracle 12c target database environment
oracle@cls18 dbs]$ scp -rp orapwDEV181 oracle@198.168.82.211:/app/oracle/product/12.1.0/dbhome_1/dbs oracle@198.168.82.211's password: orapwDEV181 100% 1536 1.5KB/s 00:00 [oracle@cls18 dbs]$ scp -rp initDEV181.ora oracle@198.168.82.211:/app/oracle/product/12.1.0/dbhome_1/dbs oracle@198.168.82.211's password: initDEV181.ora
Edit the init.ora parameter file in the 12c database environment and make changes like ….
remove deprecated 10g parameters like background and core dumps
change compatible
add diagnostic_dest
change control file location as in this example we are moving from ASM source on 10g to non-ASM target in 12c
add db_file_name_convert and log_file_name_convert
In the Oracle 12c environment start the instance in NOMOUNT state
[oracle@vosap02 dbs]$ export ORACLE_SID=DEV181 [oracle@vosap02 dbs]$ sqlplus sys as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Sun Nov 30 23:08:13 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Enter password: Connected to an idle instance. SQL> startup nomount; ORACLE instance started. Total System Global Area 880803840 bytes Fixed Size 2930416 bytes Variable Size 469764368 bytes Database Buffers 402653184 bytes Redo Buffers 5455872 bytes
Restore control file backup
[oracle@vosap02 backup]$ pwd /app/oracle/oradata/DEV181/backup [oracle@vosap02 backup]$ ls bkp_lev0.8ppp2gus_1_1 c-1508569629-20141201-00 [oracle@vosap02 backup]$ rman target / Recovery Manager: Release 12.1.0.2.0 - Production on Sun Nov 30 23:14:52 2014 Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved. connected to target database: DEV18 (not mounted) RMAN> restore controlfile from '/app/oracle/oradata/DEV181/backup/c-1508569629-20141201-00'; Starting restore at 30-NOV-14 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=12 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:01 output file name=/app/oracle/oradata/DEV181/control01.ctl output file name=/app/oracle/oradata/DEV181/control02.ctl output file name=/app/oracle/oradata/DEV181/control03.ctl Finished restore at 30-NOV-14 RMAN> alter database mount; Statement processed released channel: ORA_DISK_1 RMAN> catalog start with '/app/oracle/oradata/DEV181/backup'; searching for all files that match the pattern /app/oracle/oradata/DEV181/backup List of Files Unknown to the Database ===================================== File Name: /app/oracle/oradata/DEV181/backup/bkp_lev0.8ppp2gus_1_1 File Name: /app/oracle/oradata/DEV181/backup/c-1508569629-20141201-00 Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: /app/oracle/oradata/DEV181/backup/bkp_lev0.8ppp2gus_1_1 File Name: /app/oracle/oradata/DEV181/backup/c-1508569629-20141201-00Regards,
Restore Level 0 backup
In this example we are changing the data file names at the database level via the SET NEWNAME FOR DATABASE command.
We can also change the data file names at the individual data file level using the SET NEWNAME FOR DATAFILE command
RMAN> run {
2> allocate channel c1 type disk;
3> allocate channel c2 type disk;
4> SET NEWNAME FOR DATABASE TO '/app/oracle/oradata/DEV181/%b';
5> SET NEWNAME FOR tempfile 1 TO '/app/oracle/oradata/DEV181/%b';
6> restore database;
7> switch datafile all;
8> switch tempfile all;
9> release channel c1;
10> release channel c2;
11> }
allocated channel: c1
channel c1: SID=12 device type=DISK
allocated channel: c2
channel c2: SID=249 device type=DISK
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 30-NOV-14
channel c1: starting datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
channel c1: restoring datafile 00001 to /app/oracle/oradata/DEV181/system.256.747180649
channel c1: restoring datafile 00002 to /app/oracle/oradata/DEV181/undotbs1.258.747180651
channel c1: restoring datafile 00003 to /app/oracle/oradata/DEV181/sysaux.257.747180651
...
...
channel c1: reading from backup piece /app/DEV18/oradump/bkp_lev0.8ppp2gus_1_1
channel c1: errors found reading piece handle=/app/DEV18/oradump/bkp_lev0.8ppp2gus_1_1
channel c1: failover to piece handle=/app/oracle/oradata/DEV181/backup/bkp_lev0.8ppp2gus_1_1 tag=TAG20141201T094147
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:15
Finished restore at 30-NOV-14
datafile 1 switched to datafile copy
input datafile copy RECID=18 STAMP=865034590 file name=/app/oracle/oradata/DEV181/system.256.747180649
datafile 2 switched to datafile copy
input datafile copy RECID=19 STAMP=865034590 file name=/app/oracle/oradata/DEV181/undotbs1.258.747180651
datafile 3 switched to datafile copy
input datafile copy RECID=20 STAMP=865034591 file name=/app/oracle/oradata/DEV181/sysaux.257.747180651
datafile 4 switched to datafile copy
input datafile copy RECID=21 STAMP=865034591 file name=/app/oracle/oradata/DEV181/users.259.747180651
...
...
...
input datafile copy RECID=33 STAMP=865034591 file name=/app/oracle/oradata/DEV181/dw_data.282.821809927
datafile 21 switched to datafile copy
input datafile copy RECID=34 STAMP=865034591 file name=/app/oracle/oradata/DEV181/gg_data.283.838134609
renamed tempfile 1 to /app/oracle/oradata/DEV181/temp.264.747180711 in control file
renamed tempfile 2 to /app/oracle/oradata/DEV181/temp_others.281.798291417 in control file
released channel: c1
released channel: c2
Take a level 0 RMAN Incremental backup
At this stage there is no application outage and to simulate that we make some changes in the source 10g database
SQL> create table system.myobjects_1 2 tablespace users as select * from dba_objects; Table created.
[oracle@cls18 oradump]$ rman target /
Recovery Manager: Release 10.2.0.5.0 – Production on Mon Dec 1 12:29:49 2014
Copyright (c) 1982, 2007, Oracle. All rights reserved.
connected to target database: DEV18 (DBID=1508569629)
RMAN> run {
allocate channel c1 type disk;
backup incremental level 1 database format ‘/app/DEV18/oradump/bkp_lev1.%U’;
release channel c1;
}
2> 3> 4> 5>
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: sid=238 instance=DEV181 devtype=DISK
Starting backup at 01-DEC-14
channel c1: starting incremental level 1 datafile backupset
channel c1: specifying datafile(s) in backupset
input datafile fno=00010 name=+DEV18_DATA_01/DEV18/datafile/utility_data.266.798291365
input datafile fno=00003 name=+DEV18_DATA_01/DEV18/datafile/sysaux.257.747180651
…
…
input datafile fno=00020 name=+DEV18_DATA_01/DEV18/datafile/dw_data.282.821809927
channel c1: starting piece 1 at 01-DEC-14
channel c1: finished piece 1 at 01-DEC-14
piece handle=/app/DEV18/oradump/bkp_lev1.8rpp2qri_1_1 tag=TAG20141201T123040 comment=NONE
channel c1: backup set complete, elapsed time: 00:00:07
Finished backup at 01-DEC-14
Starting Control File and SPFILE Autobackup at 01-DEC-14
piece handle=/app/DEV18/product/product/10.2.0/db_1/dbs/c-1508569629-20141201-01 comment=NONE
Finished Control File and SPFILE Autobackup at 01-DEC-14
released channel: c1
Copy the level 1 incremental backup to target and register
[oracle@cls18 oradump]$ scp -rp bkp_lev1.8rpp2qri_1_1 oracle@198.168.82.211:/app/oracle/oradata/DEV181/backup oracle@198.168.82.211's password: bkp_lev1.8rpp2qri_1_1 100% 108MB 18.0MB/s 00:06 RMAN> catalog start with '/app/oracle/oradata/DEV181/backup'; searching for all files that match the pattern /app/oracle/oradata/DEV181/backup List of Files Unknown to the Database ===================================== File Name: /app/oracle/oradata/DEV181/backup/bkp_lev1.8rpp2qri_1_1 Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: /app/oracle/oradata/DEV181/backup/bkp_lev1.8rpp2qri_1_1RMAN>
Recover the database – note it will fail when it tries to apply a non-existent archive log file
RMAN> run {
2> allocate channel c1 type disk;
3> recover database;
4> }
allocated channel: c1
channel c1: SID=12 device type=DISK
Starting recover at 30-NOV-14
channel c1: starting incremental datafile backup set restore
channel c1: specifying datafile(s) to restore from backup set
destination for restore of datafile 00001: /app/oracle/oradata/DEV181/system.256.747180649
destination for restore of datafile 00002: /app/oracle/oradata/DEV181/undotbs1.258.747180651
destination for restore of datafile 00003: /app/oracle/oradata/DEV181/sysaux.257.747180651…
...
...
...
destination for restore of datafile 00021: /app/oracle/oradata/DEV181/gg_data.283.838134609
channel c1: reading from backup piece /app/oracle/oradata/DEV181/backup/bkp_lev1.8rpp2qri_1_1
channel c1: piece handle=/app/oracle/oradata/DEV181/backup/bkp_lev1.8rpp2qri_1_1 tag=TAG20141201T123040
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:03
starting media recovery
unable to find archived log
archived log thread=1 sequence=53490
released channel: c1
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of recover command at 11/30/2014 23:36:09
RMAN-06054: media recovery requesting unknown archived log for thread 1 with sequence 53490 and starting SCN of 5982863583825
RMAN>
Let us make some more changes in the database – perform a final archive logfile switch to ensure all changes written to disk from log buffer cache
OUTAGE STARTS NOW!
SQL> create table system.myobjects_2 2 tablespace users as select * from dba_objects; Table created. SQL> alter system switch logfile; System altered
Take a backup of the archive log files generated since the last Level 1 incremental backup using the command
BACKUP ARCHIVELOG ALL NOT BACKED UP 1 TIMES
Copy the archivelog backup to target and register
[oracle@cls18 oradump]$ scp -rp bkp_arch.8tpp2rb6_1_1 oracle@198.168.82.211:/app/oracle/oradata/DEV181/backup oracle@198.168.82.211's password: bkp_arch.8tpp2rb6_1_1 100% 2101MB 19.3MB/s 01:49 RMAN> catalog start with '/app/oracle/oradata/DEV181/backup'; searching for all files that match the pattern /app/oracle/oradata/DEV181/backup List of Files Unknown to the Database ===================================== File Name: /app/oracle/oradata/DEV181/backup/bkp_arch.8tpp2rb6_1_1 Do you really want to catalog the above files (enter YES or NO)? YES cataloging files... cataloging done List of Cataloged Files ======================= File Name: /app/oracle/oradata/DEV181/backup/bkp_arch.8tpp2rb6_1_1
Run a LIST BACKUP OF ARCHIVELOG command and note the last archive log file which has been backed up
RMAN> list backup of archivelog all; ... ... ... 1 53495 5982863638922 01-DEC-14 5982863650457 01-DEC-14 1 53496 5982863650457 01-DEC-14 5982863662947 01-DEC-14 1 53497 5982863662947 01-DEC-14 5982863673387 01-DEC-14 1 53498 5982863673387 01-DEC-14 5982863674424 01-DEC-14 1 53499 5982863674424 01-DEC-14 5982863674684 01-DEC-14
Recover the database until the sequence number noted above + 1 (53499+1)
oracle@vosap02 backup]$ rman target /
Recovery Manager: Release 12.1.0.2.0 - Production on Sun Nov 30 23:44:43 2014
Copyright (c) 1982, 2014, Oracle and/or its affiliates. All rights reserved.
connected to target database: DEV18 (DBID=1508569629, not open)
RMAN> run {
2> allocate channel c1 type disk;
3> set until sequence 53500 thread 1;
4> recover database;
5> }
using target database control file instead of recovery catalog
allocated channel: c1
channel c1: SID=12 device type=DISK
executing command: SET until clause
Starting recover at 30-NOV-14
starting media recovery
channel c1: starting archived log restore to default destination
channel c1: restoring archived log
archived log thread=1 sequence=53490
channel c1: restoring archived log
...
...
channel c1: restoring archived log
archived log thread=1 sequence=53498
channel c1: restoring archived log
archived log thread=1 sequence=53499
channel c1: reading from backup piece /app/oracle/oradata/DEV181/backup/bkp_arch.8tpp2rb6_1_1
channel c1: piece handle=/app/oracle/oradata/DEV181/backup/bkp_arch.8tpp2rb6_1_1 tag=TAG20141201T123859
channel c1: restored backup piece 1
channel c1: restore complete, elapsed time: 00:00:03
archived log file name=/app/oracle/oradata/DEV181/arch/1_53490_747180704.log thread=1 sequence=53490
archived log file name=/app/oracle/oradata/DEV181/arch/1_53491_747180704.log thread=1 sequence=53491
archived log file name=/app/oracle/oradata/DEV181/arch/1_53492_747180704.log thread=1 sequence=53492
archived log file name=/app/oracle/oradata/DEV181/arch/1_53493_747180704.log thread=1 sequence=53493
archived log file name=/app/oracle/oradata/DEV181/arch/1_53494_747180704.log thread=1 sequence=53494
archived log file name=/app/oracle/oradata/DEV181/arch/1_53495_747180704.log thread=1 sequence=53495
archived log file name=/app/oracle/oradata/DEV181/arch/1_53496_747180704.log thread=1 sequence=53496
archived log file name=/app/oracle/oradata/DEV181/arch/1_53497_747180704.log thread=1 sequence=53497
archived log file name=/app/oracle/oradata/DEV181/arch/1_53498_747180704.log thread=1 sequence=53498
archived log file name=/app/oracle/oradata/DEV181/arch/1_53499_747180704.log thread=1 sequence=53499
media recovery complete, elapsed time: 00:00:02
Finished recover at 30-NOV-14
released channel: c1
Open the database with RESETLOGS UPGRADE
Ignore the error related to block change tracking – we will disable that.
RMAN> alter database open resetlogs upgrade; RMAN-00571: =========================================================== RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS =============== RMAN-00571: =========================================================== RMAN-03002: failure of sql statement command at 11/30/2014 23:47:30 ORA-19751: could not create the change tracking file ORA-19750: change tracking file: '+DEV18_DATA_01/DEV18/changetracking/ctf.278.864472519' ORA-17502: ksfdcre:1 Failed to create file +DEV18_DATA_01/DEV18/changetracking/ctf.278.864472519 ORA-17501: logical block size 4294967295 is invalid ORA-29701: unable to connect to Cluster Synchronization Service ORA-17503: ksfdopn:2 Failed to open file +DEV18_DATA_01/DEV18/changetracking/ctf.278.864472519 ORA-15001: diskgroup "DEV18_DATA_01" does not exist or SQL> alter database disable block change tracking; Database altered.
Shut down the database and open it in the Oracle 12c environment in STARTUP UPGRADE mode
[oracle@vosap02 backup]$ sqlplus sys as sysdba SQL*Plus: Release 12.1.0.2.0 Production on Mon Dec 1 02:08:11 2014 Copyright (c) 1982, 2014, Oracle. All rights reserved. Enter password: Connected to: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options SQL> shutdown immediate; ORA-01109: database not open Database dismounted. ORACLE instance shut down. SQL> startup upgrade; ORACLE instance started. Total System Global Area 880803840 bytes Fixed Size 2930416 bytes Variable Size 469764368 bytes Database Buffers 402653184 bytes Redo Buffers 5455872 bytes Database mounted. Database opened.
Execute the catctl.pl perl script which will call the catupgrd.sql in parallel mode
[oracle@vosap02 oracle]$ cd $ORACLE_HOME/rdbms/admin
$ORACLE_HOME/perl/bin/perl catctl.pl -n 4 -l /app/oracle catupgrd.sql
[oracle@vosap02 admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 -l /app/oracle catupgrd.sql
Argument list for [catctl.pl]
SQL Process Count n = 4
SQL PDB Process Count N = 0
Input Directory d = 0
Phase Logging Table t = 0
Log Dir l = /app/oracle
Script s = 0
Serial Run S = 0
Upgrade Mode active M = 0
Start Phase p = 0
End Phase P = 0
Log Id i = 0
Run in c = 0
Do not run in C = 0
Echo OFF e = 1
No Post Upgrade x = 0
Reverse Order r = 0
Open Mode Normal o = 0
Debug catcon.pm z = 0
Debug catctl.pl Z = 0
Display Phases y = 0
Child Process I = 0
catctl.pl version: 12.1.0.2.0
Oracle Base = /app/oracle
Analyzing file catupgrd.sql
Log files in /app/oracle
catcon: ALL catcon-related output will be written to /app/oracle/catupgrd_catcon_26248.lst
catcon: See /app/oracle/catupgrd*.log files for output generated by scripts
catcon: See /app/oracle/catupgrd_*.lst files for spool files, if any
Number of Cpus = 2
SQL Process Count = 4
------------------------------------------------------
Phases [0-73]
Serial Phase #: 0 Files: 1 Time: 138s
Serial Phase #: 1 Files: 5 Time: 31s
Restart Phase #: 2 Files: 1 Time: 0s
Parallel Phase #: 3 Files: 18 Time: 9s
Restart Phase #: 4 Files: 1 Time: 0s
Serial Phase #: 5 Files: 5 Time: 14s
Serial Phase #: 6 Files: 1 Time: 10s
Serial Phase #: 7 Files: 4 Time: 5s
Restart Phase #: 8 Files: 1 Time: 1s
Parallel Phase #: 9 Files: 62 Time: 26s
Restart Phase #:10 Files: 1 Time: 0s
Serial Phase #:11 Files: 1 Time: 10s
Restart Phase #:12 Files: 1 Time: 0s
Parallel Phase #:13 Files: 91 Time: 9s
Restart Phase #:14 Files: 1 Time: 0s
Parallel Phase #:15 Files: 111 Time: 14s
Restart Phase #:16 Files: 1 Time: 0s
Serial Phase #:17 Files: 3 Time: 0s
Restart Phase #:18 Files: 1 Time: 0s
Parallel Phase #:19 Files: 32 Time: 19s
Restart Phase #:20 Files: 1 Time: 0s
Serial Phase #:21 Files: 3 Time: 5s
Restart Phase #:22 Files: 1 Time: 0s
Parallel Phase #:23 Files: 23 Time: 69s
Restart Phase #:24 Files: 1 Time: 0s
Parallel Phase #:25 Files: 11 Time: 39s
Restart Phase #:26 Files: 1 Time: 0s
Serial Phase #:27 Files: 1 Time: 0s
Restart Phase #:28 Files: 1 Time: 0s
Serial Phase #:30 Files: 1 Time: 0s
Serial Phase #:31 Files: 257 Time: 15s
Serial Phase #:32 Files: 1 Time: 0s
Restart Phase #:33 Files: 1 Time: 0s
Serial Phase #:34 Files: 1 Time: 3s
Restart Phase #:35 Files: 1 Time: 0s
Restart Phase #:36 Files: 1 Time: 0s
Serial Phase #:37 Files: 4 Time: 44s
Restart Phase #:38 Files: 1 Time: 0s
Parallel Phase #:39 Files: 13 Time: 50s
Restart Phase #:40 Files: 1 Time: 0s
Parallel Phase #:41 Files: 10 Time: 6s
Restart Phase #:42 Files: 1 Time: 0s
Serial Phase #:43 Files: 1 Time: 6s
Restart Phase #:44 Files: 1 Time: 0s
Serial Phase #:45 Files: 1 Time: 39s
Serial Phase #:46 Files: 1 Time: 0s
Restart Phase #:47 Files: 1 Time: 0s
Serial Phase #:48 Files: 1 Time: 99s
Restart Phase #:49 Files: 1 Time: 0s
Serial Phase #:50 Files: 1 Time: 113s
Restart Phase #:51 Files: 1 Time: 0s
Serial Phase #:52 Files: 1 Time: 21s
Restart Phase #:53 Files: 1 Time: 0s
Serial Phase #:54 Files: 1 Time: 162s
Restart Phase #:55 Files: 1 Time: 0s
Serial Phase #:56 Files: 1 Time: 48s
Restart Phase #:57 Files: 1 Time: 0s
Serial Phase #:58 Files: 1 Time: 154s
Restart Phase #:59 Files: 1 Time: 0s
Serial Phase #:60 Files: 1 Time: 242s
Restart Phase #:61 Files: 1 Time: 0s
Serial Phase #:62 Files: 1 Time: 36s
Restart Phase #:63 Files: 1 Time: 0s
Serial Phase #:64 Files: 1 Time: 2s
Serial Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/app/oracle/product/12.1.0/dbhome_1/lib; export LD_LIBRARY_PATH;/app/oracle/product/12.1.0/dbhome_1/perl/bin/perl -I /app/oracle/product/12.1.0/dbhome_1/rdbms/admin -I /app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch /app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only > /app/oracle/catupgrd_datapatch_upgrade.log 2> /app/oracle/catupgrd_datapatch_upgrade.err
returned from sqlpatch
Time: 21s
Serial Phase #:66 Files: 1 Time: 31s
Serial Phase #:68 Files: 1 Time: 0s
Serial Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/app/oracle/product/12.1.0/dbhome_1/lib; export LD_LIBRARY_PATH;/app/oracle/product/12.1.0/dbhome_1/perl/bin/perl -I /app/oracle/product/12.1.0/dbhome_1/rdbms/admin -I /app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch /app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose > /app/oracle/catupgrd_datapatch_normal.log 2> /app/oracle/catupgrd_datapatch_normal.err
returned from sqlpatch
Time: 32s
Serial Phase #:70 Files: 1 Time: 73s
Serial Phase #:71 Files: 1 Time: 0s
Serial Phase #:72 Files: 1 Time: 0s
Serial Phase #:73 Files: 1 Time: 21s
Grand Total Time: 1620s
LOG FILES: (catupgrd*.log)
Upgrade Summary Report Located in:
/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/DEV18/upgrade/upg_summary.log
Grand Total Upgrade Time: [0d:0h:27m:0s]
Review the upgrade summary log file
[oracle@vosap02 oracle]$ cat /app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/DEV18/upgrade/upg_summary.log Oracle Database 12.1 Post-Upgrade Status Tool 12-01-2014 02:37:12 Component Current Version Elapsed Time Name Status Number HH:MM:SS Oracle Server UPGRADED 12.1.0.2.0 00:09:22 JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:01:38 Oracle Real Application Clusters OPTION OFF 12.1.0.2.0 00:00:02 Oracle Workspace Manager VALID 12.1.0.2.0 00:00:34 OLAP Analytic Workspace VALID 12.1.0.2.0 00:00:19 OLAP Catalog OPTION OFF 10.2.0.5.0 00:00:00 Oracle OLAP API VALID 12.1.0.2.0 00:00:22 Oracle XDK VALID 12.1.0.2.0 00:01:52 Oracle Text VALID 12.1.0.2.0 00:00:37 Oracle XML Database VALID 12.1.0.2.0 00:02:05 Oracle Database Java Packages VALID 12.1.0.2.0 00:00:10 Oracle Multimedia VALID 12.1.0.2.0 00:02:33 Spatial UPGRADED 12.1.0.2.0 00:04:01 Final Actions 00:00:50 Post Upgrade 00:01:09 Total Upgrade Time: 00:25:57 PL/SQL procedure successfully completed. Elapsed: 00:00:00.12 Grand Total Upgrade Time: [0d:0h:27m:0s]
Run post-upgrade steps
Upgrade timezone data to version 18
SQL> startup upgrade
ORACLE instance started.
Total System Global Area 880803840 bytes
Fixed Size 2930416 bytes
Variable Size 469764368 bytes
Database Buffers 402653184 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_14.dat 14 0
SQL> EXEC DBMS_DST.BEGIN_UPGRADE(18);
PL/SQL procedure successfully completed.
SQL> SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value
FROM DATABASE_PROPERTIES
WHERE PROPERTY_NAME LIKE 'DST_%'
ORDER BY PROPERTY_NAME; 2 3 4
PROPERTY_NAME
--------------------------------------------------------------------------------
VALUE
--------------------------------------------------------------------------------
DST_PRIMARY_TT_VERSION
18
DST_SECONDARY_TT_VERSION
14
DST_UPGRADE_STATE
UPGRADE
SQL> shutdown immediate;
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup;
ORACLE instance started.
Total System Global Area 880803840 bytes
Fixed Size 2930416 bytes
Variable Size 469764368 bytes
Database Buffers 402653184 bytes
Redo Buffers 5455872 bytes
Database mounted.
Database opened.
SQL> set serverout on
SQL> 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;
/SQL> 2 3 4 5 6 7 8 9 10 11
Table list: "ROSODS"."STS_TEST"
Number of failures: 0
Table list: "RH_SCHEDULE"."SCHED_TRAIN_LOCATION"
Number of failures: 0
Table list: "RH_SCHEDULE"."SCHED_RPO"
Number of failures: 0
Table list: "RH_SCHEDULE"."SCHED_MASTER_TRAIN_BUNDLE"
Number of failures: 0
Table list: "RH_SCHEDULE"."SCHED_MASTER_TRAIN"
Number of failures: 0
Table list: "RH_SCHEDULE"."SCHED_CHANGE_REASON"
Number of failures: 0
Table list: "STG_OWNER"."STG_TRAIN_SCHED_RPO"
Number of failures: 0
Table list: "STG_OWNER"."STG_TRAIN_SCHEDULE_LOCATION"
Number of failures: 0
Table list: "STG_OWNER"."STG_TRAIN_SCHEDULE"
Number of failures: 0
Table list: "STG_OWNER"."STG_OUT_TRAIN_SCHED_MQ2_EXT"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_WEIGHBRIDGE_VEHICLE"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_WEIGHBRIDGE_READING"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_TRAIN_RUNNING"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_SPEED_RESTRICTION_LOC"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_SPEED_RESTRICTION"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_OUT_VEHICLES_TRIP_EXT"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_LOAD_DUMP"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_CONSIST_TRAIN"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_CONSIST_RAKE"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_CONSIST_AEI_VEHICLE"
Number of failures: 0
Table list: "STG_OWNER"."STAGING_CONSIST"
Number of failures: 0
Table list: "RPO"."STG_RPO_PLANNING_CHANGE"
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
Failures:0
PL/SQL procedure successfully completed.
SQL> VAR fail number
BEGIN
DBMS_DST.END_UPGRADE(:fail);
DBMS_OUTPUT.PUT_LINE('Failures:'|| :fail);
END;
/SQL> 2 3 4 5
An upgrade window has been successfully ended.
Failures:0
PL/SQL procedure successfully completed.
SQL> SELECT * FROM v$timezone_file;
FILENAME VERSION CON_ID
-------------------- ---------- ----------
timezlrg_18.dat 18 0
Recompile INVALID Objects
Run utlrp followed by utluiobj.sql
SQL> @utluiobj.sql . Oracle Database 12.1 Post-Upgrade Invalid Objects Tool 12-01-2014 19:21:50 . This tool lists post-upgrade invalid objects that were not invalid prior to upgrade (it ignores pre-existing pre-upgrade invalid objects). . Owner Object Name Object Type . PL/SQL procedure successfully completed.
A best practice is to gather system statistics 24-48 hours after the upgrade
SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('start');
<< Run it for several hours during periods of normal workload>>
SQL> exec DBMS_STATS.GATHER_SYSTEM_STATS('stop');
0 Comments