A.Installation of 12c software in the target host
B. Pre-upgrade steps in the source database for database upgradation to 12c from 11.2.0.3
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
C. RMAN duplicate and upgradation of database to 12c from 11.2.0.3
1.start the auxiliary database using the pfile with only parameter db_name
[oracle@ol6-112-rac1 dbs]$ cat initnew12cdb.ora
db_name=new12cdb
[oracle@ol6-112-rac1 dbs]$ export ORACLE_SID=new12cdb
[oracle@ol6-112-rac1 dbs]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 20 10:59:14 2014
Copyright (c) 1982, 2013, Oracle. All rights reserved.
Connected to an idle instance.
SQL> startup nomount pfile=?/dbs/initnew12cdb.ora
ORACLE instance started.
Total System Global Area 217157632 bytes
Fixed Size 2286656 bytes
Variable Size 159386560 bytes
Database Buffers 50331648 bytes
Redo Buffers 5152768 bytes
SQL>
2. Create the password file for the auxiliary database
[oracle@ol6-112-rac1 dbs]$ orapwd file=orapwnew12cdb entries=10 password=sys123 force=y
[oracle@ol6-112-rac1 dbs]$ ls orapwnew12cdb
orapwnew12cdb
3.create a dedicated listener configuration for the auxiliary database
[oracle@ol6-112-rac1 admin]$ cat listener.ora
LISTEN_FOR_NEW12CDB=
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.0.111)(PORT = 1524))
)
)
SID_LIST_LISTEN_FOR_NEW12CDB=
(SID_LIST=
(SID_DESC=
(ORACLE_HOME=/u03/app/oracle/product/12.1.0/dbhome_1)
(SID_NAME=new12cdb)
)
)
[oracle@ol6-112-rac1 admin]$ lsnrctl start LISTEN_FOR_NEW12CDB
LSNRCTL for Linux: Version 12.1.0.1.0 - Production on 20-JUN-2014 11:06:43
Copyright (c) 1991, 2013, Oracle. All rights reserved.
Starting /u03/app/oracle/product/12.1.0/dbhome_1/bin/tnslsnr: please wait...
TNSLSNR for Linux: Version 12.1.0.1.0 - Production
System parameter file is /u03/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Log messages written to /u03/app/oracle/diag/tnslsnr/ol6-112-rac1/listen_for_new12cdb/alert/log.xml
Listening on: (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.111)(PORT=1524)))
Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=192.168.0.111)(PORT=1524)))
STATUS of the LISTENER
------------------------
Alias LISTEN_FOR_NEW12CDB
Version TNSLSNR for Linux: Version 12.1.0.1.0 - Production
Start Date 20-JUN-2014 11:06:44
Uptime 0 days 0 hr. 0 min. 1 sec
Trace Level off
Security ON: Local OS Authentication
SNMP OFF
Listener Parameter File /u03/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora
Listener Log File /u03/app/oracle/diag/tnslsnr/ol6-112-rac1/listen_for_new12cdb/alert/log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=192.168.0.111)(PORT=1524)))
Services Summary...
Service "new12cdb" has 1 instance(s).
Instance "new12cdb", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully
4.Create the netservice name for the auxiliary database in $ORACLE_HOME/network/admin/tnsnames.ora file
[oracle@ol6-112-rac1 admin]$ vi tnsnames.ora
[oracle@ol6-112-rac1 admin]$ tnsping new12cdb
TNS Ping Utility for Linux: Version 12.1.0.1.0 - Production on 20-JUN-2014 11:14:51
Copyright (c) 1997, 2013, Oracle. All rights reserved.
Used parameter files:
Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = ol6-112-rac1)(PORT = 1524)) (CONNECT_DATA = (SERVER = DEDICATED) (Service_NAME = new12cdb)))
OK (10 msec)
5.Check the auxiliary database is connecting using the password file
[oracle@ol6-112-rac1 dbs]$ sqlplus sys/sys123@new12cdb as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 20 11:18:23 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>
6.Check the backup of the source database is present in the backup location
[oracle@ol6-112-rac1 backup_db2]$ pwd
/u03/backup_dupdb/backup_db2
[oracle@ol6-112-rac1 backup_db2]$ ls -ltr
total 1544280
-rw-r----- 1 oracle oinstall 10158080 Jun 19 15:41 cntrl_c-575818374-20140619-00
-rw-r----- 1 oracle oinstall 30208 Jun 19 15:41 03pb86i8_1_1
-rw-r----- 1 oracle oinstall 301484544 Jun 19 15:41 01pb86d5_1_1
-rw-r----- 1 oracle oinstall 1269661696 Jun 19 15:44 02pb86e9_1_1
7.Duplicate the database using backup pieces in the backup location and connect only to the auxiliary database using the script.
run
{
duplicate database 'dupdb' to 'new12cdb'
backup location='/u03/backup_dupdb/backup_db2'
spfile
set db_name='new12cdb'
set control_files='/u03/oradata/new12cdb/control01.ctl','/u03/fra/new12cdb/control02.ctl'
set db_unique_name='new12cdb'
set audit_file_dest='/u01/app/orcl/oracle/admin/new12cdb/adump'
set dispatchers='(PROTOCOL=TCP) (SERVICE=new12cdbXDB)'
set memory_target='1024M'
set memory_max_target='1024M'
set sga_target='0'
set pga_aggregate_target='0'
set local_listener=''
set log_archive_dest_1='LOCATION=/u03/fra'
set log_file_name_convert='/u03/fra/DUPDB/onlinelog','/u03/oradata/new12cdb'
noopen #not open the database in resetlogs mode
db_file_name_convert='/u03/oradata/dupdb','/u03/oradata/new12cdb';
}
8.Output of execution of duplicate script
[oracle@ol6-112-rac1 rman]$ rman auxiliary="sys/sys123@new12cdb" @ rman_duplicate_noopen.cmd | tee -a rman_duplicate_noopen.log
Recovery Manager: Release 12.1.0.1.0 - Production on Fri Jun 20 11:37:18 2014
Copyright (c) 1982, 2013, Oracle and/or its affiliates. All rights reserved.
connected to auxiliary database: NEW12CDB (not mounted)
RMAN> run
2> {
3> set newname for tempfile 1 to '/u03/oradata/new12cdb/temp01.dbf';
4> duplicate database 'dupdb' to 'new12cdb'
5> backup location='/u03/backup_dupdb/backup_db2'
6> spfile
7> set db_name='new12cdb'
8> set control_files='/u03/oradata/new12cdb/control01.ctl','/u03/fra/new12cdb/control02.ctl'
9> set db_unique_name='new12cdb'
10> set audit_file_dest='/u01/app/orcl/oracle/admin/new12cdb/adump'
11> set dispatchers='(PROTOCOL=TCP) (SERVICE=new12cdbXDB)'
12> set memory_target='1024M'
13> set memory_max_target='1024M'
14> set sga_target='0'
15> set pga_aggregate_target='0'
16> set local_listener=''
17> set log_archive_dest_1='LOCATION=/u03/fra'
18> set log_file_name_convert='/u03/fra/DUPDB/onlinelog','/u03/oradata/new12cdb'
19> noopen #not open the database in resetlogs mode
20> db_file_name_convert='/u03/oradata/dupdb','/u03/oradata/new12cdb';
21> }
22>
23>
24>
executing command: SET NEWNAME
Starting Duplicate Db at 20-JUN-14
contents of Memory Script:
{
restore clone spfile to '/u03/app/oracle/product/12.1.0/dbhome_1/dbs/spfilenew12cdb.ora' from
'/u03/backup_dupdb/backup_db2/cntrl_c-575818374-20140619-00';
sql clone "alter system set spfile= ''/u03/app/oracle/product/12.1.0/dbhome_1/dbs/spfilenew12cdb.ora''";
}
executing Memory Script
Starting restore at 20-JUN-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=20 device type=DISK
channel ORA_AUX_DISK_1: restoring spfile from AUTOBACKUP /u03/backup_dupdb/backup_db2/cntrl_c-575818374-20140619-00
channel ORA_AUX_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 20-JUN-14
sql statement: alter system set spfile= ''/u03/app/oracle/product/12.1.0/dbhome_1/dbs/spfilenew12cdb.ora''
contents of Memory Script:
{
sql clone "alter system set db_name =
''NEW12CDB'' comment=
''duplicate'' scope=spfile";
sql clone "alter system set db_name =
''new12cdb'' comment=
'''' scope=spfile";
sql clone "alter system set control_files =
''/u03/oradata/new12cdb/control01.ctl'', ''/u03/fra/new12cdb/control02.ctl'' comment=
'''' scope=spfile";
sql clone "alter system set db_unique_name =
''new12cdb'' comment=
'''' scope=spfile";
sql clone "alter system set audit_file_dest =
''/u01/app/orcl/oracle/admin/new12cdb/adump'' comment=
'''' scope=spfile";
sql clone "alter system set dispatchers =
''(PROTOCOL=TCP) (SERVICE=new12cdbXDB)'' comment=
'''' scope=spfile";
sql clone "alter system set memory_target =
1024M comment=
'''' scope=spfile";
sql clone "alter system set memory_max_target =
1024M comment=
'''' scope=spfile";
sql clone "alter system set sga_target =
0 comment=
'''' scope=spfile";
sql clone "alter system set pga_aggregate_target =
0 comment=
'''' scope=spfile";
sql clone "alter system set local_listener =
'''' comment=
'''' scope=spfile";
sql clone "alter system set log_archive_dest_1 =
''LOCATION=/u03/fra'' comment=
'''' scope=spfile";
sql clone "alter system set log_file_name_convert =
''/u03/fra/DUPDB/onlinelog'', ''/u03/oradata/new12cdb'' comment=
'''' scope=spfile";
shutdown clone immediate;
startup clone nomount;
}
executing Memory Script
sql statement: alter system set db_name = ''NEW12CDB'' comment= ''duplicate'' scope=spfile
sql statement: alter system set db_name = ''new12cdb'' comment= '''' scope=spfile
sql statement: alter system set control_files = ''/u03/oradata/new12cdb/control01.ctl'', ''/u03/fra/new12cdb/control02.ctl'' comment= '''' scope=spfile
sql statement: alter system set db_unique_name = ''new12cdb'' comment= '''' scope=spfile
sql statement: alter system set audit_file_dest = ''/u01/app/orcl/oracle/admin/new12cdb/adump'' comment= '''' scope=spfile
sql statement: alter system set dispatchers = ''(PROTOCOL=TCP) (SERVICE=new12cdbXDB)'' comment= '''' scope=spfile
sql statement: alter system set memory_target = 1024M comment= '''' scope=spfile
sql statement: alter system set memory_max_target = 1024M comment= '''' scope=spfile
sql statement: alter system set sga_target = 0 comment= '''' scope=spfile
sql statement: alter system set pga_aggregate_target = 0 comment= '''' scope=spfile
sql statement: alter system set local_listener = '''' comment= '''' scope=spfile
sql statement: alter system set log_archive_dest_1 = ''LOCATION=/u03/fra'' comment= '''' scope=spfile
sql statement: alter system set log_file_name_convert = ''/u03/fra/DUPDB/onlinelog'', ''/u03/oradata/new12cdb'' comment= '''' scope=spfile
Oracle instance shut down
connected to auxiliary database (not started)
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 671089920 bytes
Database Buffers 390070272 bytes
Redo Buffers 5480448 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''DUPDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
sql clone "alter system set db_unique_name =
''NEW12CDB'' comment=
''Modified by RMAN duplicate'' scope=spfile";
shutdown clone immediate;
startup clone force nomount
restore clone primary controlfile from '/u03/backup_dupdb/backup_db2/cntrl_c-575818374-20140619-00';
alter clone database mount;
}
executing Memory Script
sql statement: alter system set db_name = ''DUPDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
sql statement: alter system set db_unique_name = ''NEW12CDB'' comment= ''Modified by RMAN duplicate'' scope=spfile
Oracle instance shut down
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 671089920 bytes
Database Buffers 390070272 bytes
Redo Buffers 5480448 bytes
Starting restore at 20-JUN-14
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
channel ORA_AUX_DISK_1: restoring control file
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u03/oradata/new12cdb/control01.ctl
output file name=/u03/fra/new12cdb/control02.ctl
Finished restore at 20-JUN-14
database mounted
released channel: ORA_AUX_DISK_1
allocated channel: ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: SID=19 device type=DISK
contents of Memory Script:
{
set until scn 1286566;
set newname for datafile 1 to
"/u03/oradata/new12cdb/system01.dbf";
set newname for datafile 2 to
"/u03/oradata/new12cdb/sysaux01.dbf";
set newname for datafile 3 to
"/u03/oradata/new12cdb/undotbs01.dbf";
set newname for datafile 4 to
"/u03/oradata/new12cdb/users01.dbf";
set newname for datafile 5 to
"/u03/oradata/new12cdb/example01.dbf";
restore
clone database
;
}
executing Memory Script
executing command: SET until clause
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
executing command: SET NEWNAME
Starting restore at 20-JUN-14
using channel ORA_AUX_DISK_1
channel ORA_AUX_DISK_1: starting datafile backup set restore
channel ORA_AUX_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_AUX_DISK_1: restoring datafile 00001 to /u03/oradata/new12cdb/system01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00002 to /u03/oradata/new12cdb/sysaux01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00003 to /u03/oradata/new12cdb/undotbs01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00004 to /u03/oradata/new12cdb/users01.dbf
channel ORA_AUX_DISK_1: restoring datafile 00005 to /u03/oradata/new12cdb/example01.dbf
channel ORA_AUX_DISK_1: reading from backup piece /u03/backup_dupdb/backup_db2/02pb86e9_1_1
channel ORA_AUX_DISK_1: piece handle=/u03/backup_dupdb/backup_db2/02pb86e9_1_1 tag=TAG20140619T153121
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:02:05
Finished restore at 20-JUN-14
contents of Memory Script:
{
switch clone datafile all;
}
executing Memory Script
datafile 1 switched to datafile copy
input datafile copy RECID=6 STAMP=850736385 file name=/u03/oradata/new12cdb/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=7 STAMP=850736385 file name=/u03/oradata/new12cdb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=8 STAMP=850736385 file name=/u03/oradata/new12cdb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=9 STAMP=850736385 file name=/u03/oradata/new12cdb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=10 STAMP=850736385 file name=/u03/oradata/new12cdb/example01.dbf
contents of Memory Script:
{
set until scn 1286566;
recover
clone database
delete archivelog
;
}
executing Memory Script
executing command: SET until clause
Starting recover at 20-JUN-14
using channel ORA_AUX_DISK_1
starting media recovery
channel ORA_AUX_DISK_1: starting archived log restore to default destination
channel ORA_AUX_DISK_1: restoring archived log
archived log thread=1 sequence=8
channel ORA_AUX_DISK_1: reading from backup piece /u03/backup_dupdb/backup_db2/03pb86i8_1_1
channel ORA_AUX_DISK_1: piece handle=/u03/backup_dupdb/backup_db2/03pb86i8_1_1 tag=TAG20140619T153327
channel ORA_AUX_DISK_1: restored backup piece 1
channel ORA_AUX_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u03/fra/1_8_850604301.dbf thread=1 sequence=8
channel clone_default: deleting archived log(s)
archived log file name=/u03/fra/1_8_850604301.dbf RECID=1 STAMP=850736387
media recovery complete, elapsed time: 00:00:00
Finished recover at 20-JUN-14
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 671089920 bytes
Database Buffers 390070272 bytes
Redo Buffers 5480448 bytes
contents of Memory Script:
{
sql clone "alter system set db_name =
''NEW12CDB'' comment=
''Reset to original value by RMAN'' scope=spfile";
sql clone "alter system reset db_unique_name scope=spfile";
}
executing Memory Script
sql statement: alter system set db_name = ''NEW12CDB'' comment= ''Reset to original value by RMAN'' scope=spfile
sql statement: alter system reset db_unique_name scope=spfile
Oracle instance started
Total System Global Area 1068937216 bytes
Fixed Size 2296576 bytes
Variable Size 671089920 bytes
Database Buffers 390070272 bytes
Redo Buffers 5480448 bytes
sql statement: CREATE CONTROLFILE REUSE SET DATABASE "NEW12CDB" RESETLOGS ARCHIVELOG
MAXLOGFILES 16
MAXLOGMEMBERS 3
MAXDATAFILES 100
MAXINSTANCES 8
MAXLOGHISTORY 292
LOGFILE
GROUP 1 ( '/u03/oradata/new12cdb/o1_mf_1_9t3m1q2g_.log' ) SIZE 50 M REUSE,
GROUP 2 ( '/u03/oradata/new12cdb/o1_mf_2_9t3m1tcw_.log' ) SIZE 50 M REUSE,
GROUP 3 ( '/u03/oradata/new12cdb/o1_mf_3_9t3m1y6l_.log' ) SIZE 50 M REUSE
DATAFILE
'/u03/oradata/new12cdb/system01.dbf'
CHARACTER SET AL32UTF8
contents of Memory Script:
{
set newname for tempfile 1 to
"/u03/oradata/new12cdb/temp01.dbf";
switch clone tempfile all;
catalog clone datafilecopy "/u03/oradata/new12cdb/sysaux01.dbf",
"/u03/oradata/new12cdb/undotbs01.dbf",
"/u03/oradata/new12cdb/users01.dbf",
"/u03/oradata/new12cdb/example01.dbf";
switch clone datafile all;
}
executing Memory Script
executing command: SET NEWNAME
renamed tempfile 1 to /u03/oradata/new12cdb/temp01.dbf in control file
cataloged datafile copy
datafile copy file name=/u03/oradata/new12cdb/sysaux01.dbf RECID=1 STAMP=850736407
cataloged datafile copy
datafile copy file name=/u03/oradata/new12cdb/undotbs01.dbf RECID=2 STAMP=850736407
cataloged datafile copy
datafile copy file name=/u03/oradata/new12cdb/users01.dbf RECID=3 STAMP=850736407
cataloged datafile copy
datafile copy file name=/u03/oradata/new12cdb/example01.dbf RECID=4 STAMP=850736407
datafile 2 switched to datafile copy
input datafile copy RECID=1 STAMP=850736407 file name=/u03/oradata/new12cdb/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=2 STAMP=850736407 file name=/u03/oradata/new12cdb/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=3 STAMP=850736407 file name=/u03/oradata/new12cdb/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=4 STAMP=850736407 file name=/u03/oradata/new12cdb/example01.dbf
Leaving database unopened, as requested
Finished Duplicate Db at 20-JUN-14
Recovery Manager complete.
9.Open the database in reselogs upgrade mode
[oracle@ol6-112-rac1 rman]$ sqlplus / as sysdba
SQL*Plus: Release 12.1.0.1.0 Production on Fri Jun 20 11:41:52 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> select name,open_Mode from v$database;
NAME OPEN_MODE
--------- --------------------
NEW12CDB MOUNTED
SQL> alter database open resetlogs upgrade;
Database altered.
SQL> select instance_name,status,logins from v$instance;
INSTANCE_NAME STATUS LOGINS
---------------- ------------ ----------
new12cdb OPEN MIGRATE RESTRICTED
10.Upgrade the database using perl script with parallelism 5
[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 &
Analyzing file catupgrd.sql
Serial Phase #: 0 Files: 1 Time: 292s
Serial Phase #: 1 Files: 3 Time: 76s
Restart Phase #: 2 Files: 1 Time: 2s
Parallel Phase #: 3 Files: 18 Time: 29s
Restart Phase #: 4 Files: 1 Time: 0s
Serial Phase #: 5 Files: 5 Time: 28s
Serial Phase #: 6 Files: 1 Time: 17s
Serial Phase #: 7 Files: 3 Time: 14s
Restart Phase #: 8 Files: 1 Time: 0s
Parallel Phase #: 9 Files: 60 Time: 95s
Restart Phase #:10 Files: 1 Time: 0s
Serial Phase #:11 Files: 1 Time: 27s
Restart Phase #:12 Files: 1 Time: 0s
Parallel Phase #:13 Files: 199 Time: 108s
Restart Phase #:14 Files: 1 Time: 0s
Serial Phase #:15 Files: 3 Time: 2s
Restart Phase #:16 Files: 1 Time: 0s
Parallel Phase #:17 Files: 33 Time: 67s
Restart Phase #:18 Files: 1 Time: 0s
Serial Phase #:19 Files: 3 Time: 14s
Restart Phase #:20 Files: 1 Time: 0s
Parallel Phase #:21 Files: 23 Time: 151s
Restart Phase #:22 Files: 1 Time: 1s
Parallel Phase #:23 Files: 11 Time: 98s
Restart Phase #:24 Files: 1 Time: 0s
Serial Phase #:25 Files: 1 Time: 60s
Restart Phase #:26 Files: 1 Time: 0s
Parallel Phase #:27 Files: 0 Time: 0s
Serial Phase #:28 Files: 1 Time: 0s
Parallel Phase #:29 Files: 130 Time: 16s
Serial Phase #:30 Files: 1 Time: 0s
Restart Phase #:31 Files: 1 Time: 1s
Serial Phase #:32 Files: 1 Time: 0s
Parallel Phase #:33 Files: 122 Time: 21s
Serial Phase #:34 Files: 1 Time: 0s
Restart Phase #:35 Files: 1 Time: 0s
Serial Phase #:36 Files: 4 Time: 85s
Restart Phase #:37 Files: 1 Time: 1s
Parallel Phase #:38 Files: 13 Time: 113s
Restart Phase #:39 Files: 1 Time: 0s
Parallel Phase #:40 Files: 10 Time: 16s
Restart Phase #:41 Files: 1 Time: 0s
Serial Phase #:42 Files: 1 Time: 13s
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: 567s
Restart Phase #:48 Files: 1 Time: 2s
Serial Phase #:49 Files: 2 Time: 594s
Restart Phase #:50 Files: 1 Time: 4s
Serial Phase #:51 Files: 2 Time: 2784s
Restart Phase #:52 Files: 1 Time: 6s
Serial Phase #:53 Files: 1 Time: 5s
Serial Phase #:54 Files: 1 Time: 332s
Serial Phase #:55 Files: 1 Time: 190s
Serial Phase #:56 Files: 1 Time: 98s
Grand Total Time: 5936s
11..Run the post upgrade fixup script created in the preupgrade step against the target database.
[oracle@ol6-112-rac1 tmp]
[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
$ 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.
12.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
