A.Installation of 12c software in the target host
B. Upgradation of database from 11.2.0.3 to 12.1.0.1.0
1.copy the preupgrade.sql and utluppkg.sql script from the 12.1.0.1.0 $ORACLE_HOME/rdbms/admin location to the source database /tmp directory
[oracle@ol6-112-rac1 admin]$ scp preupgrd.sql utluppkg.sql oracle@ol6-112-racDat2:/tmp
preupgrd.sql 100% 5231 5.1KB/s 00:00
utluppkg.sql 100% 373KB 372.9KB/s 00:00
100% 5231 5.1KB/s 00:00
2.Run the preupgrade.sql
-rw-r--r-- 1 oracle oinstall 5231 Jun 19 14:56 preupgrd.sql
-rw-r--r-- 1 oracle oinstall 381893 Jun 19 14:56 utluppkg.sql
[oracle@ol6-112-rac2 tmp]$ echo $ORACLE_SID
dupdb
[oracle@ol6-112-rac2 tmp]$ sqlplus / as sysdba @preupgrd.sql
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 19 14:58:00 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Loading Pre-Upgrade Package...
Executing Pre-Upgrade Checks...
Pre-Upgrade Checks Complete.
************************************************************
Results of the checks are located at:
/u01/app/orcl/oracle/cfgtoollogs/DUPDB/preupgrade/preupgrade.log
Pre-Upgrade Fixup Script (run in source database environment):
/u01/app/orcl/oracle/cfgtoollogs/DUPDB/preupgrade/preupgrade_fixups.sql
Post-Upgrade Fixup Script (run shortly after upgrade):
/u01/app/orcl/oracle/cfgtoollogs/DUPDB/preupgrade/postupgrade_fixups.sql
************************************************************
Fixup scripts must be reviewed prior to being executed.
************************************************************
************************************************************
====>> USER ACTION REQUIRED <<====
************************************************************
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.
You MUST resolve the above errors prior to upgrade
************************************************************
3.Check the logs and preupgrade fixup scripts
[oracle@ol6-112-rac2 tmp]$ sqlplus / as sysdba @preupgrd.sql
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 19 14:58:00 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
Loading Pre-Upgrade Package...
Executing Pre-Upgrade Checks...
Pre-Upgrade Checks Complete.
************************************************************
Results of the checks are located at:
/u01/app/orcl/oracle/cfgtoollogs/DUPDB/preupgrade/preupgrade.log
Pre-Upgrade Fixup Script (run in source database environment):
/u01/app/orcl/oracle/cfgtoollogs/DUPDB/preupgrade/preupgrade_fixups.sql
Post-Upgrade Fixup Script (run shortly after upgrade):
/u01/app/orcl/oracle/cfgtoollogs/DUPDB/preupgrade/postupgrade_fixups.sql
************************************************************
Fixup scripts must be reviewed prior to being executed.
************************************************************
************************************************************
====>> USER ACTION REQUIRED <<====
************************************************************
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.
You MUST resolve the above errors prior to upgrade
************************************************************
SQL> exit
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@ol6-112-rac2 tmp]$ more /u01/app/orcl/oracle/cfgtoollogs/DUPDB/preupgrade/preupgrade.log
Oracle Database Pre-Upgrade Information Tool 06-19-2014 14:58:21
Script Version: 12.1.0.1.0 Build: 006
**********************************************************************
Database Name: DUPDB
Version: 11.2.0.3.0
Compatible: 11.2.0.0.0
Blocksize: 8192
Platform: Linux x86 64-bit
Timezone file: V14
**********************************************************************
[Renamed Parameters]
[No Renamed Parameters in use]
**********************************************************************
**********************************************************************
[Obsolete/Deprecated Parameters]
[No Obsolete or Desupported Parameters in use]
**********************************************************************
[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
--> 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
--> Expression Filter [upgrade] VALID
--> Rule Manager [upgrade] VALID
--> Oracle Application Express [upgrade] VALID
--> Oracle OLAP API [upgrade] VALID
**********************************************************************
[Tablespaces]
**********************************************************************
--> SYSTEM tablespace is adequate for the upgrade.
minimum required size: 1231 MB
--> SYSAUX tablespace is adequate for the upgrade.
minimum required size: 1469 MB
--> UNDOTBS1 tablespace is adequate for the upgrade.
minimum required size: 400 MB
--> TEMP tablespace is adequate for the upgrade.
minimum required size: 60 MB
--> EXAMPLE tablespace is adequate for the upgrade.
minimum required size: 310 MB
[No adjustments recommended]
**********************************************************************
**********************************************************************
[Pre-Upgrade Checks]
**********************************************************************
WARNING: --> Process Count may be too low
Database has a maximum process count of 150 which is lower than the
default value of 300 for this release.
You should update your processes value prior to the upgrade
to a value of at least 300.
For example:
ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE
or update your init.ora file.
WARNING: --> Inactive DBIDs found in AWR
AWR contains inactive DBIDs which may need additional updating after
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: --> Existing DBMS_LDAP dependent objects
Database contains schemas with objects dependent on DBMS_LDAP package.
Refer to the Upgrade Guide for instructions to configure Network ACLs.
USER APEX_030200 has dependent objects.
INFORMATION: --> OLAP Catalog(AMD) exists in database
Starting with Oracle Database 12c, OLAP is desupported.
If you are not using the OLAP Catalog component and want
to remove it, then execute the
ORACLE_HOME/oraolap/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 11.2.0.3.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.
**********************************************************************
[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 ************
0 ERRORS exist in your database.
4 WARNINGS that Oracle suggests are addressed to improve database performance.
2 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 977512.1
***********************************
3.Run the preupgrade fixup script present in the /u01/app/orcl/oracle/cfgtoollogs/DUPDB/preupgrade/preupgrade_fixups.sql
[oracle@ol6-112-rac2 preupgrade]$ sqlplus / as sysdba
SQL*Plus: Release 11.2.0.3.0 Production on Thu Jun 19 15:06:53 2014
Copyright (c) 1982, 2011, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
SQL> @ preupgrade_fixups.sql
Pre-Upgrade Fixup Script Generated on 2014-06-19 14:58:18 Version: 12.1.0.1 Build: 006
Beginning Pre-Upgrade Fixups...
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
**********************************************************************
Check Tag: DEFAULT_PROCESS_COUNT
Check Summary: Verify min process count is not too low
Fix Summary: Review and increase if needed, your PROCESSES value.
**********************************************************************
Fixup Returned Information:
WARNING: --> Process Count may be too low
Database has a maximum process count of 150 which is lower than the
default value of 300 for this release.
You should update your processes value prior to the upgrade
to a value of at least 300.
For example:
ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE
or update your init.ora file.
**********************************************************************
PL/SQL procedure successfully completed.
**********************************************************************
Check Tag: EM_PRESENT
Check Summary: Check if Enterprise Manager is present
Fix Summary: Execute emremove.sql prior to upgrade.
**********************************************************************
Fixup Returned Information:
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.
**********************************************************************
PL/SQL procedure successfully completed.
**********************************************************************
Check Tag: DBMS_LDAP_DEPENDENCIES_EXIST
Check Summary: Check for dependency on DBMS_LDAP package
Fix Summary: Network Objects must be reviewed manually.
**********************************************************************
Fixup Returned Information:
WARNING: --> Existing DBMS_LDAP dependent objects
Database contains schemas with objects dependent on DBMS_LDAP package.
Refer to the Upgrade Guide for instructions to configure Network ACLs.
USER APEX_030200 has dependent objects.
**********************************************************************
PL/SQL procedure successfully completed.
**********************************************************************
Check Tag: AMD_EXISTS
Check Summary: Check to see if AMD is present in the database
Fix Summary: Manually execute ORACLE_HOME/oraolap/admin/catnoamd.sql script to remove OLAP.
**********************************************************************
Fixup Returned Information:
INFORMATION: --> OLAP Catalog(AMD) exists in database
Starting with Oracle Database 12c, OLAP is desupported.
If you are not using the OLAP Catalog component and want
to remove it, then execute the
ORACLE_HOME/oraolap/admin/catnoamd.sql script before or
after the upgrade.
**********************************************************************
PL/SQL procedure successfully completed.
**********************************************************************
[Pre-Upgrade Recommendations]
**********************************************************************
PL/SQL procedure successfully completed.
*****************************************
********* 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 ^^^
PL/SQL procedure successfully completed.
**************************************************
************* Fixup Summary ************
4 fixup routines generated INFORMATIONAL messages that should be reviewed.
PL/SQL procedure successfully completed.
**************** Pre-Upgrade Fixup Script Complete *********************
PL/SQL procedure successfully completed.
4.Copy the rdbms/admin/emremove.sql from the new oracle home to the source database and run the script against the database to be upgraded
oracle@ol6-112-rac1 admin]$ pwd /u03/app/oracle/product/12.1.0/dbhome_1/rdbms/admin [oracle@ol6-112-rac1 admin]$ scp emremove.sql oracle@ol6-112-rac2:/tmp emremove.sql 100% 19KB 19.2KB/s 00:00 [oracle@ol6-112-rac1 admin]$ ============================= [oracle@ol6-112-rac2 tmp]$ export ORACLE_UNQNAME=dupdb [oracle@ol6-112-rac2 tmp]$ emctl stop dbconsole OC4J Configuration issue. /u01/app/orcl/oracle/product/11.2.0.3/db_1/oc4j/j2ee/OC4J_DBConsole_ol6-112-rac2_dupdb not found. ========================================= SQL> set echo on SQL> set serveroutput on SQL> @emremove.sql SQL> Rem SQL> Rem $Header: rdbms/admin/emremove.sql /main/2 2012/07/27 01:19:53 spramani Exp $ SQL> Rem SQL> Rem emremove.sql SQL> Rem SQL> Rem Copyright (c) 2012, Oracle and/or its affiliates. All rights reserved. SQL> Rem SQL> Rem NAME SQL> Rem emremove.sql - This script removes EM Schema from RDBMS SQL> Rem SQL> Rem DESCRIPTION Dropping synonym : SMP_EMD_NVPAIR ... Dropping synonym : SMP_EMD_NVPAIR_ARRAY ... Dropping synonym : SMP_EMD_STRING_ARRAY ... Dropping synonym : SMP_EMD_STRING_ARRAY_ARRAY ... Dropping synonym : SMP_EMD_TARGET_OBJ ... Dropping synonym : SMP_EMD_TARGET_OBJ_ARRAY ... 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. ============================
5.gather dictionary statistics
SQL> SQL> EXECUTE dbms_stats.gather_dictionary_stats; PL/SQL procedure successfully completed.
6.configure the controlfile autobackup 'ON' on the source database and take full backup of the database.
[oracle@ol6-112-rac2 rman]$ [oracle@ol6-112-rac2 rman]$ rman target="/" Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jun 19 14:36:28 2014 Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved. connected to target database: DUPDB (DBID=575818374) RMAN> configure controlfile autobackup on; new RMAN configuration parameters: CONFIGURE CONTROLFILE AUTOBACKUP ON; new RMAN configuration parameters are successfully stored
7. Scrip to take full backup of database plus archivelog
run
{
set controlfile autobackup format for device type disk to '/u03/backup_db2/cntrl_%F';
backup database
format="/u03/backup_db2/%U"
plus archivelog
format="/u03/backup_db2/%U";
}
8. Take backup of the database
[oracle@ol6-112-rac2 rman]$ rman target="/"
Recovery Manager: Release 11.2.0.3.0 - Production on Thu Jun 19 15:30:37 2014
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: DUPDB (DBID=575818374)
RMAN> @ rman_backup_db_full.cmd
RMAN> run
2> {
3> set controlfile autobackup format for device type disk to '/u03/backup_db2/cntrl_%F';
4> backup database
5> format="/u03/backup_db2/%U"
6> plus archivelog
7> format="/u03/backup_db2/%U";
8> }
executing command: SET CONTROLFILE AUTOBACKUP FORMAT
Starting backup at 19-JUN-14
current log archived
using target database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=42 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1 RECID=1 STAMP=850663295
input archived log thread=1 sequence=2 RECID=2 STAMP=850663336
input archived log thread=1 sequence=3 RECID=3 STAMP=850663357
input archived log thread=1 sequence=4 RECID=4 STAMP=850663378
input archived log thread=1 sequence=5 RECID=5 STAMP=850663396
input archived log thread=1 sequence=6 RECID=6 STAMP=850663676
input archived log thread=1 sequence=7 RECID=7 STAMP=850663844
channel ORA_DISK_1: starting piece 1 at 19-JUN-14
channel ORA_DISK_1: finished piece 1 at 19-JUN-14
piece handle=/u03/backup_db2/01pb86d5_1_1 tag=TAG20140619T153045 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:35
Finished backup at 19-JUN-14
Starting backup at 19-JUN-14
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u03/oradata/dupdb/system01.dbf
input datafile file number=00002 name=/u03/oradata/dupdb/sysaux01.dbf
input datafile file number=00005 name=/u03/oradata/dupdb/example01.dbf
input datafile file number=00003 name=/u03/oradata/dupdb/undotbs01.dbf
input datafile file number=00004 name=/u03/oradata/dupdb/users01.dbf
channel ORA_DISK_1: starting piece 1 at 19-JUN-14
channel ORA_DISK_1: finished piece 1 at 19-JUN-14
piece handle=/u03/backup_db2/02pb86e9_1_1 tag=TAG20140619T153121 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:02:05
Finished backup at 19-JUN-14
Starting backup at 19-JUN-14
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=8 RECID=8 STAMP=850664007
channel ORA_DISK_1: starting piece 1 at 19-JUN-14
channel ORA_DISK_1: finished piece 1 at 19-JUN-14
piece handle=/u03/backup_db2/03pb86i8_1_1 tag=TAG20140619T153327 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 19-JUN-14
Starting Control File and SPFILE Autobackup at 19-JUN-14
piece handle=/u03/backup_db2/cntrl_c-575818374-20140619-00 comment=NONE
Finished Control File and SPFILE Autobackup at 19-JUN-14
RMAN> **end-of-file**
9.copy the backup pieces and the password file to the target location with the new 12c database home installed.
[oracle@ol6-112-rac2 u03]$ scp -r backup_db2/ ol6-112-rac1:/u03/backup_dupdb cntrl_c-575818374-20140619-00 100% 9920KB 9.7MB/s 00:00 03pb86i8_1_1 100% 30KB 29.5KB/s 00:00 01pb86d5_1_1 100% 288MB 10.3MB/s 00:28 02pb86e9_1_1 100% 1211MB 7.6MB/s 02:40 [oracle@ol6-112-rac2 dbs]$ scp initdupdb.ora oracle@ol6-112-rac1:/u03/app/oracle/product/12.1.0/dbhome_1/dbs initdupdb.ora 100% 1057 1.0KB/s 00:00
10.Start the instance in nomount mode in the target database to be upgraded.
[oracle@ol6-112-rac1 dbs]$ sqlplus / as sysdba SQL*Plus: Release 12.1.0.1.0 Production on Thu Jun 19 15:58:50 2014 Copyright (c) 1982, 2013, Oracle. All rights reserved. Connected to an idle instance. SQL> startup nomount pfile=?/dbs/initdupdb.ora ORACLE instance started. Total System Global Area 275578880 bytes Fixed Size 2287432 bytes Variable Size 176163000 bytes Database Buffers 92274688 bytes Redo Buffers 4853760 bytes
11.Restore the controlfile and change the database to mount mode
[oracle@ol6-112-rac1 backup_db2]$ echo $ORACLE_SID dupdb [oracle@ol6-112-rac1 backup_db2]$ rman target="/" Recovery Manager: Release 12.1.0.1.0 - Production on Thu Jun 19 16:02:36 2014 Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved. connected to target database: DUPDB (not mounted) RMAN> restore controlfile from '/u03/backup_dupdb/backup_db2/cntrl_c-575818374-20140619-00'; Starting restore at 19-JUN-2014 16:02:51 using target database control file instead of recovery catalog allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK channel ORA_DISK_1: restoring control file channel ORA_DISK_1: restore complete, elapsed time: 00:00:03 output file name=/u03/oradata/dupdb/control01.ctl output file name=/u03/fra/dupdb/control02.ctl Finished restore at 19-JUN-2014 16:02:55 RMAN> sql "alter database mount"; sql statement: alter database mount released channel: ORA_DISK_1
12.Catalog the backuppieces
RMAN> catalog start with '/u03/backup_dupdb/backup_db2'; Starting implicit crosscheck backup at 19-JUN-2014 16:04:33 allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=20 device type=DISK Crosschecked 3 objects Finished implicit crosscheck backup at 19-JUN-2014 16:04:34 Starting implicit crosscheck copy at 19-JUN-2014 16:04:34 using channel ORA_DISK_1 Finished implicit crosscheck copy at 19-JUN-2014 16:04:34 searching for all files in the recovery area cataloging files... no files cataloged searching for all files that match the pattern /u03/backup_dupdb/backup_db2 List of Files Unknown to the Database ===================================== File Name: /u03/backup_dupdb/backup_db2/cntrl_c-575818374-20140619-00 File Name: /u03/backup_dupdb/backup_db2/01pb86d5_1_1 File Name: /u03/backup_dupdb/backup_db2/03pb86i8_1_1 File Name: /u03/backup_dupdb/backup_db2/02pb86e9_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: /u03/backup_dupdb/backup_db2/cntrl_c-575818374-20140619-00 File Name: /u03/backup_dupdb/backup_db2/01pb86d5_1_1 File Name: /u03/backup_dupdb/backup_db2/03pb86i8_1_1 File Name: /u03/backup_dupdb/backup_db2/02pb86e9_1_1
13.restore the database
RMAN> restore database from tag="TAG20140619T153121" 2> ; Starting restore at 19-JUN-2014 16:08:08 using channel ORA_DISK_1 channel ORA_DISK_1: starting datafile backup set restore channel ORA_DISK_1: specifying datafile(s) to restore from backup set channel ORA_DISK_1: restoring datafile 00001 to /u03/oradata/dupdb/system01.dbf channel ORA_DISK_1: restoring datafile 00002 to /u03/oradata/dupdb/sysaux01.dbf channel ORA_DISK_1: restoring datafile 00003 to /u03/oradata/dupdb/undotbs01.dbf channel ORA_DISK_1: restoring datafile 00004 to /u03/oradata/dupdb/users01.dbf channel ORA_DISK_1: restoring datafile 00005 to /u03/oradata/dupdb/example01.dbf channel ORA_DISK_1: reading from backup piece /u03/backup_dupdb/backup_db2/02pb86e9_1_1 channel ORA_DISK_1: piece handle=/u03/backup_dupdb/backup_db2/02pb86e9_1_1 tag=TAG20140619T153121 channel ORA_DISK_1: restored backup piece 1 channel ORA_DISK_1: restore complete, elapsed time: 00:02:34 Finished restore at 19-JUN-2014 16:10:42
14.recover the database
RMAN> recover database until sequence 9 thread 1; Starting recover at 19-JUN-2014 16:14:36 using channel ORA_DISK_1 starting media recovery archived log for thread 1 with sequence 8 is already on disk as file /u03/fra/1_8_850604301.dbf archived log file name=/u03/fra/1_8_850604301.dbf thread=1 sequence=8 media recovery complete, elapsed time: 00:00:01 Finished recover at 19-JUN-2014 16:14:3715. open the database in resetlogs upgrade mode
RMAN> alter database open resetlogs upgrade; Statement processed
16.start the catupgrd.sql script to upgrade the database,but it is deprecated in 12.1 , advice note to run the perl script
SQL> select STATUS,INSTANCE_NAME,LOGINS from v$instance;
STATUS INSTANCE_NAME LOGINS
------------ ---------------- ----------
OPEN MIGRATE dupdb RESTRICTED
SQL>
SQL>
SQL>
SQL>
SQL> @ ?/rdbms/admin/catupgrd.sql
Session altered.
DOC>######################################################################
DOC>######################################################################
DOC> NOTE
DOC>
DOC> The catupgrd.sql is being deprecated in the 12.1 release of the
DOC> Oracle Database. Customers are encouraged to use catctl.pl as
DOC> the replacement for catupgrd.sql when upgrading the database dictionary.
DOC>
DOC> cd $ORACLE_HOME/rdbms/admin
DOC> $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql
DOC>
DOC> Refer to the Oracle Database Upgrade Guide for more information.
DOC>
DOC> This database upgrade procedure must be called with the following
DOC> argument when invoking from the SQL prompt:
DOC>
DOC> @catupgrd.sql PARALLEL=NO
DOC>
DOC>######################################################################
DOC>######################################################################
DOC>#
old 2: WHERE UPPER('&&1') = 'PARALLEL=NO' OR
new 2: WHERE UPPER('') = 'PARALLEL=NO' OR
old 3: UPPER('&&1') = 'PARALLEL=YES'
new 3: UPPER('') = 'PARALLEL=YES'
SELECT (to_number(count(*)))/(to_number(count(*))) FROM DUAL
*
ERROR at line 1:
ORA-01476: divisor is equal to zero
Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
[oracle@ol6-112-rac1 trace]$
[oracle@ol6-112-rac1 trace]$ echo $ORACLE_SID
dupdb
[oracle@ol6-112-rac1 trace]$ cd $ORACLE_HOME/rdbms/admin
[oracle@ol6-112-rac1 admin]$ echo $ORACLE_HOME
/u03/app/oracle/product/12.1.0/dbhome_1
[oracle@ol6-112-rac1 admin]$ nohup $ORACLE_HOME/perl/bin/perl catctl.pl -n 4 catupgrd.sql &
[1] 1707
[oracle@ol6-112-rac1 admin]$ nohup: ignoring input and appending output to `nohup.out'
Analyzing file catupgrd.sql
Serial Phase #: 0 Files: 1 Time: 340s
Serial Phase #: 1 Files: 3 Time: 147s
Restart Phase #: 2 Files: 1 Time: 3s
Parallel Phase #: 3 Files: 18 Time: 41s
Restart Phase #: 4 Files: 1 Time: 0s
Serial Phase #: 5 Files: 5 Time: 43s
Serial Phase #: 6 Files: 1 Time: 30s
Serial Phase #: 7 Files: 3 Time: 20s
Restart Phase #: 8 Files: 1 Time: 0s
Parallel Phase #: 9 Files: 60 Time: 117s
Restart Phase #:10 Files: 1 Time: 0s
Serial Phase #:11 Files: 1 Time: 36s
Restart Phase #:12 Files: 1 Time: 0s
Parallel Phase #:13 Files: 199 Time: 109s
Restart Phase #:14 Files: 1 Time: 1s
Serial Phase #:15 Files: 3 Time: 1s
Restart Phase #:16 Files: 1 Time: 0s
Parallel Phase #:17 Files: 33 Time: 76s
Restart Phase #:18 Files: 1 Time: 0s
Serial Phase #:19 Files: 3 Time: 14s
Restart Phase #:20 Files: 1 Time: 1s
Parallel Phase #:21 Files: 23 Time: 147s
Restart Phase #:22 Files: 1 Time: 0s
Parallel Phase #:23 Files: 11 Time: 101s
Restart Phase #:24 Files: 1 Time: 0s
Serial Phase #:25 Files: 1 Time: 55s
Restart Phase #:26 Files: 1 Time: 0s
Parallel Phase #:27 Files: 0 Time: 1s
Serial Phase #:28 Files: 1 Time: 0s
Parallel Phase #:29 Files: 130 Time: 18s
Serial Phase #:30 Files: 1 Time: 0s
Restart Phase #:31 Files: 1 Time: 0s
Serial Phase #:32 Files: 1 Time: 0s
Parallel Phase #:33 Files: 122 Time: 29s
Serial Phase #:34 Files: 1 Time: 0s
Restart Phase #:35 Files: 1 Time: 1s
Serial Phase #:36 Files: 4 Time: 86s
Restart Phase #:37 Files: 1 Time: 1s
Parallel Phase #:38 Files: 13 Time: 100s
Restart Phase #:39 Files: 1 Time: 0s
Parallel Phase #:40 Files: 10 Time: 18s
Restart Phase #:41 Files: 1 Time: 0s
Serial Phase #:42 Files: 1 Time: 11s
Restart Phase #:43 Files: 1 Time: 0s
Serial Phase #:44 Files: 1 Time: 6s
Serial Phase #:45 Files: 1 Time: 1s
Restart Phase #:46 Files: 1 Time: 0s
Serial Phase #:47 Files: 2 Time: 603s
Restart Phase #:48 Files: 1 Time: 3s
Serial Phase #:49 Files: 2 Time: 630s
Restart Phase #:50 Files: 1 Time: 0s
Serial Phase #:51 Files: 2 Time: 3389s
Restart Phase #:52 Files: 1 Time: 7s
Serial Phase #:53 Files: 1 Time: 5s
Serial Phase #:54 Files: 1 Time: 338s
Serial Phase #:55 Files: 1 Time: 200s
Serial Phase #:56 Files: 1 Time: 117s
Grand Total Time: 6846s
17.copy the post upgrade fixup script from the source database to the target database, created by running the preupgrade.sh script
[oracle@ol6-112-rac2 tmp]$ scp /u01/app/orcl/oracle/cfgtoollogs/DUPDB/preupgrade/postupgrade_fixups.sql oracle@ol6-112-rac1:/tmp postupgrade_fixups.sql 100% 2382 2.3KB/s 00:00
18.Run the post upgrade fixup script
[oracle@ol6-112-rac1 tmp]$ clear
[oracle@ol6-112-rac1 tmp]$ ls post*
postupgrade_fixups.sql
[oracle@ol6-112-rac1 tmp]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 20 09:22:44 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options
SQL> @ postupgrade_fixups.sql
Post Upgrade Fixup Script Generated on 2014-06-19 14:58:18 Version: 12.1.0.1 Build: 006
Beginning Post-Upgrade Fixups...
PL/SQL procedure successfully completed.
PL/SQL procedure successfully completed.
**********************************************************************
Check Tag: AWR_DBIDS_PRESENT
Check Summary: Verify if AWR contains inactive DBIDs
Fix Summary: The inactive DBIDs in AWR may need additional updating after upgrading.
**********************************************************************
Fixup Returned Information:
WARNING: --> Inactive DBIDs found in AWR
AWR contains inactive DBIDs which may need additional updating after
upgrading.
**********************************************************************
PL/SQL procedure successfully completed.
**********************************************************************
Check Tag: OLD_TIME_ZONES_EXIST
Check Summary: Check for use of older timezone data file
Fix Summary: Update the timezone using the DBMS_DST package after upgrade is complete.
**********************************************************************
Fixup Returned Information:
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 12.1.0.1.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.
**********************************************************************
PL/SQL procedure successfully completed.
**********************************************************************
[Post-Upgrade Recommendations]
**********************************************************************
PL/SQL procedure successfully completed.
*****************************************
******** 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 ^^^
PL/SQL procedure successfully completed.
**************************************************
************* Fixup Summary ************
2 fixup routines generated INFORMATIONAL messages that should be reviewed.
PL/SQL procedure successfully completed.
*************** Post Upgrade Fixup Script Complete ********************
PL/SQL procedure successfully completed.
19.Upgrade the timezone version to 18;
conn / as sysdba shutdown immediate; startup upgrade; set serveroutput on – check if previous prepare window is ended SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE ‘DST_%’ ORDER BY PROPERTY_NAME; – output should be – PROPERTY_NAME VALUE – —————————- —————————— – DST_PRIMARY_TT_VERSION– DST_SECONDARY_TT_VERSION 0 – DST_UPGRADE_STATE NONE purge dba_recyclebin; TRUNCATE TABLE SYS.DST$TRIGGER_TABLE; TRUNCATE TABLE sys.dst$affected_tables; TRUNCATE TABLE sys.dst$error_table; alter session set “_with_subquery”=materialize; alter session set “_simple_view_merging”=TRUE; EXEC DBMS_DST.BEGIN_UPGRADE(18); - check if this select SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE ‘DST_%’ ORDER BY PROPERTY_NAME; – gives this output: – PROPERTY_NAME VALUE – ————————— —————————— – DST_PRIMARY_TT_VERSION – DST_SECONDARY_TT_VERSION – DST_UPGRADE_STATE UPGRADE shutdown immediate startup alter session set “_with_subquery”=materialize; alter session set “_simple_view_merging”=TRUE; 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; / – ouput of this will be a list of tables like: – Table list: SYSMAN.AQ$_MGMT_NOTIFY_QTABLE_S – Number of failures: 0 – if there where no failures then end the upgrade. VAR fail number BEGIN DBMS_DST.END_UPGRADE(:fail); DBMS_OUTPUT.PUT_LINE(’Failures:’|| :fail); END; / – output that will be seen: – An upgrade window has been successfully ended. – Failures:0 SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE ‘DST_%’ ORDER BY PROPERTY_NAME; – needed output: – PROPERTY_NAME VALUE – —————————- —————————— – DST_PRIMARY_TT_VERSION – DST_SECONDARY_TT_VERSION 0 – DST_UPGRADE_STATE NONE SELECT * FROM v$timezone_file; FILENAME VERSION ——————– ———- timezlrg_14.dat 18 select TZ_VERSION from registry$database; update registry$database set TZ_VERSION = (select version FROM v$timezone_file); commit; SQL> select * from v$timezone_file; FILENAME VERSION CON_ID -------------------- ---------- ---------- timezlrg_18.dat 18 0
