my-orcl@Areef DBA, my-orcl@Arif DBA: Manual Database Upgrade form 11204 to 12102.

Manual Database Upgrade form 11204 to 12102.

Hai, this article will illustrate you how to upgrade your oracle database from 11204 to 12102 on linux Platform.

Assumptions: requirements

11g ORACLE_HOME: /u01/app/oracle/product/11.2.0/dbhome_1

12c ORACLE_HOME: /u01/app/oracle/product/12.1.0/dbhome_1

Assumptions:

Machine	IP Address	DB Name	SID	Listener
RAC1	192.168.1.11	DELL	DELL	DELL

Linux Machine Details:

[root@rac1 ~]#

[root@rac1 ~]# lsb_release -a

LSB Version: :core-4.0-amd64:core-4.0-ia32:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-ia32:printing-4.0-noarch

Distributor ID: EnterpriseEnterpriseServer

Description: Enterprise Linux Enterprise Linux Server release 5.7 (Carthage)

Release: 5.7

Codename: Carthage

[root@rac1 ~]#

[root@rac1 ~]# arch

x86_64

[root@rac1 ~]#

[root@rac1 ~]# ps -ef | grep pmon

oracle 10403 1 0 08:36 ? 00:00:00 ora_pmon_DELL

root 10862 4501 0 08:41 pts/1 00:00:00 grep pmon

[root@rac1 ~]#

Oracle Database (DELL) details:

[root@rac1 ~]# su - oracle

[oracle@rac1 ~]$ cat dell.env

export ORACLE_SID=DELL

export ORACLE_BASE=/u01/app/oracle

export ORACLE_HOME=/u01/app/oracle/product/11.2.0/dbhome_1

export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH

export TNS_ADMIN=$ORACLE_HOME/network/admin

LD_LIBRARY_PATH=$ORACLE_HOME/lib:/lib/usr/lib; export LD_LIBRARY_PATH

[oracle@rac1 ~]$

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 Production on Tue Dec 19 08:51:46 2017

Connected to:

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL>

SQL> select name,controlfile_type,open_mode from v$database;

NAME CONTROL OPEN_MODE

--------- ------- --------------------

DELL CURRENT READ WRITE

SQL>SQL>

SQL> select name from v$controlfile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/DELL/control01.ctl

/u01/app/oracle/DELL/control02.ctl

SQL>

SQL> col tablespace_name for a20

SQL> select tablespace_name,file_name from dba_data_files;

TABLESPACE_NAME FILE_NAME

-------------------- ----------------------------------------

USERS /u01/app/oracle/DELL/users01.dbf

UNDOTBS1 /u01/app/oracle/DELL/undotbs01.dbf

SYSAUX /u01/app/oracle/DELL/sysaux01.dbf

SYSTEM /u01/app/oracle/DELL/system01.dbf

SQL>

SQL> select member from v$logfile;

MEMBER

--------------------------------------------------------------------------------

/u01/app/oracle/DELL/redo03.log

/u01/app/oracle/DELL/redo02.log

/u01/app/oracle/DELL/redo01.log

SQL>

SQL> select name from v$tempfile;

NAME

--------------------------------------------------------------------------------

/u01/app/oracle/DELL/temp01.dbf

SQL>

SQL> show parameter spfile;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

spfile string /u01/app/oracle/product/11.2.0

/dbhome_1/dbs/spfileDELL.ora

SQL>

SQL> show parameter PROCESSES;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

aq_tm_processes integer 1

db_writer_processes integer 1

gcs_server_processes integer 0

global_txn_processes integer 1

job_queue_processes integer 1000

log_archive_max_processes integer 4

processes integer 150

SQL>

SQL> select version from v$timezone_file;

VERSION

----------

SQL>

================================================================================================================

Archive Mode Status

SQL> archive log list;

Database log mode Archive Mode

Automatic archival Enabled

Archive destination USE_DB_RECOVERY_FILE_DEST

Oldest online log sequence 1

Next log sequence to archive 2

Current log sequence 2

SQL>

SQL> select username, account_status from dba_users;

================================================================================================================

Creation of User and table

SQL> grant connect, resource to u1 identified by u1;

Grant succeeded.

SQL> conn u1/u1

Connected.

SQL> show user

USER is "U1"

SQL> create table emp (EName varchar(15), ENo number);

Table created.

SQL> insert into emp (EName, ENo) values ('aaa',10);

1 row created.

SQL> insert into emp (EName, ENo) values ('bbb',20);

1 row created.

SQL> insert into emp (EName, ENo) values ('ccc',30);

1 row created.

SQL> commit;

Commit complete

SQL> select * from emp;

ENAME ENO

--------------- ----------

aaa 10

bbb 20

ccc 30

SQL>

================================================================================================================

Run pre Upgrade Script from 11g_Home

SQL>

SQL> @/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/preupgrd.sql;

Loading Pre-Upgrade Package...

***************************************************************************

Executing Pre-Upgrade Checks in DELL...

***************************************************************************

************************************************************

====>> ERRORS FOUND for DELL <<====

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

************************************************************

====>> PRE-UPGRADE RESULTS for DELL <<====

ACTIONS REQUIRED:

1. Review results of the pre-upgrade checks:

/u01/app/oracle/cfgtoollogs/DELL/preupgrade/preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:

/u01/app/oracle/cfgtoollogs/DELL/preupgrade/preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:

/u01/app/oracle/cfgtoollogs/DELL/preupgrade/postupgrade_fixups.sql

************************************************************

***************************************************************************

Pre-Upgrade Checks in DELL Completed.

***************************************************************************

SQL>

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@rac1 ~]$

[oracle@rac1 ~]$ cat /u01/app/oracle/cfgtoollogs/DELL/preupgrade/preupgrade.log

Oracle Database Pre-Upgrade Information Tool 12-19-2017 08:53:51

Script Version: 12.1.0.2.0 Build: 006

**********************************************************************

Database Name: DELL

Container Name: Not Applicable in Pre-12.1 database

Container ID: Not Applicable in Pre-12.1 database

Version: 11.2.0.4.0

Compatible: 11.2.0.4.0

Blocksize: 8192

Platform: Linux x86 64-bit

Timezone file: V14

**********************************************************************

[Update parameters]

[Update Oracle Database 11.2.0.4.0 init.ora or spfile]

--> If Target Oracle is 32-bit, refer here for Update Parameters:

WARNING: --> "processes" needs to be increased to at least 300

--> If Target Oracle is 64-bit, refer here for Update Parameters:

WARNING: --> "processes" needs to be increased to at least 300

**********************************************************************

[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: 1245 MB

--> SYSAUX tablespace is adequate for the upgrade.

minimum required size: 1401 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

[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: --> 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.

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

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

INFORMATION: --> Oracle Application Express (APEX) can be

manually upgraded prior to database upgrade

APEX is currently at version 3.2.1.00.12 and will need to be

upgraded to APEX version 4.2.5 in the new release.

Note 1: To reduce database upgrade time, APEX can be manually

upgraded outside of and prior to database upgrade.

Note 2: See MOS Note 1088970.1 for information on APEX

installation upgrades.

**********************************************************************

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

2 WARNINGS that Oracle suggests are addressed to improve database performance.

4 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

***********************************

[oracle@rac1 ~]$

Start of optional Checks before performing Upgrade

SQL>

SQL> select USERNAME, DEFAULT_TABLESPACE from dba_users where username in ('SYS','SYSTEM');

USERNAME DEFAULT_TABLESPACE

------------------------------ ------------------------------

SYSTEM SYSTEM

SYS SYSTEM

SQL>

SQL> select * from v$backup where status != 'NOT ACTIVE';

no rows selected

SQL>

If you get any rows for above command then run next 3 commands

SQL>

SQL> select * from dba_2pc_pending;

no rows selected

SQL>

SQL> select local_tran_id from dba_2pc_pending;

no rows selected

SQL> exec dbms_transaction.purge_lost_db_entry('');

BEGIN dbms_transaction.purge_lost_db_entry(''); END;

ERROR at line 1:

ORA-06510: PL/SQL: unhandled user-defined exception

ORA-06512: at "SYS.DBMS_TRANSACTION", line 105

ORA-06512: at line 1

SQL>

SQL> commit;

Commit complete.

SQL>

Auditing

SQL>

SQL> select owner,tablespace_name from dba_tables where table_name='AUD$';

OWNER TABLESPACE_NAME

------------------------------ ------------------------------

SYS SYSTEM

SQL>

Externally authenticate users

SQL>

SQL> select name from sys.user$ where ext_username is not null and password= 'GLOBAL';

no rows selected

SQL>

End of optional Checks before performing Upgrade

Performing All Pre-Upgrade Checks as per Log file

SQL>

SQL> ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE;

System altered.

SQL>

Delete EMC because 12c uses enterprise manager express control default

SQL>

SQL> @/u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/emremove.sql

old 69: IF (upper('&LOGGING') = 'VERBOSE')

new 69: IF (upper('VERBOSE') = 'VERBOSE')

PL/SQL procedure successfully completed.

SQL>

SQL> EXECUTE dbms_stats.gather_dictionary_stats;

PL/SQL procedure successfully completed.

SQL>

SQL> EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

PL/SQL procedure successfully completed.

SQL>

SQL> purge dba_recyclebin;

DBA Recyclebin purged.

SQL>

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL>

SQL> exit

Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

[oracle@rac1 ~]$

[oracle@rac1 ~]$ cd $ORACLE_HOME/dbs

[oracle@rac1 dbs]$

Copy Parameter file to New_Home

[oracle@rac1 dbs]$

[oracle@rac1 dbs]$ cp spfileDELL.ora /u01/app/oracle/product/12.1.0/dbhome_1/dbs/

[oracle@rac1 dbs]$

[oracle@rac1 dbs]$ lsnrctl stop listener

LSNRCTL for Linux: Version 11.2.0.4.0 - Production on 19-DEC-2017 09:32:47

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.dell.com)(PORT=1521)))

The command completed successfully

[oracle@rac1 dbs]$

Create new Listener in new Oracle_Home:

[oracle@rac1 ~]$

[oracle@rac1 ~]$ . 12102.env

[oracle@rac1 ~]$

[oracle@rac1 ~]$ netca

Oracle Net Services Configuration:

Configuring Listener:LISTENER

Listener configuration complete.

Oracle Net Listener Startup:

Running Listener Control:

/u01/app/oracle/product/12.1.0/dbhome_1/bin/lsnrctl start LISTENER

Listener Control complete.

Listener started successfully.

Oracle Net Services configuration successful. The exit code is 0

[oracle@rac1 ~]$

[oracle@rac1 ~]$ lsnrctl status listener

LSNRCTL for Linux: Version 12.1.0.2.0 - Production on 19-DEC-2017 09:34:28

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=rac1.dell.com)(PORT=1521)))

STATUS of the LISTENER

------------------------

Alias LISTENER

Version TNSLSNR for Linux: Version 12.1.0.2.0 - Production

Start Date 19-DEC-2017 09:34:11

Uptime 0 days 0 hr. 0 min. 17 sec

Trace Level off

Security ON: Local OS Authentication

SNMP OFF

Listener Parameter File /u01/app/oracle/product/12.1.0/dbhome_1/network/admin/listener.ora

Listener Log File /u01/app/oracle/diag/tnslsnr/rac1/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=rac1.dell.com)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=EXTPROC1521)))

The listener supports no services

The command completed successfully

[oracle@rac1 ~]$

Start 12c database in upgrade mode:

[oracle@rac1 ~]$

[oracle@rac1 ~]$ which sqlplus

/u01/app/oracle/product/12.1.0/dbhome_1/bin/sqlplus

[oracle@rac1 ~]$

[oracle@rac1 ~]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 19 09:36:16 2017

Connected to an idle instance.

SQL>

SQL> startup upgrade

ORACLE instance started.

Total System Global Area 792723456 bytes

Fixed Size 2929400 bytes

Variable Size 314576136 bytes

Database Buffers 469762048 bytes

Redo Buffers 5455872 bytes

Database mounted.

Database opened.

SQL>

Open Alert Log file.

SQL>

SQL> Show parameter back_

SQL> select name, VALUE from v$diag_info;

SQL> exit

Disconnected from Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

[oracle@rac1 ~]$

[oracle@rac1 ~]$ cd $ORACLE_HOME/rdbms/admin

[oracle@rac1 admin]$

[oracle@rac1 admin]$ ll catctl.pl catupgrd.sql

-rw-r--r-- 1 oracle oinstall 186503 Jun 22 2014 catctl.pl

-rw-r--r-- 1 oracle oinstall 8290 Apr 19 2014 catupgrd.sql

[oracle@rac1 admin]$

[oracle@rac1 admin]$ $ORACLE_HOME/perl/bin/perl catctl.pl -n 8 catupgrd.sql

Argument list for [catctl.pl]

SQL Process Count n = 8

SQL PDB Process Count N = 0

Input Directory d = 0

Phase Logging Table t = 0

Log Dir l = 0

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 = /u01/app/oracle

Analyzing file catupgrd.sql

Log files in /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin

catcon: ALL catcon-related output will be written to catupgrd_catcon_10487.lst

catcon: See catupgrd*.log files for output generated by scripts

catcon: See catupgrd_*.lst files for spool files, if any

Number of Cpus = 1

SQL Process Count = 8

------------------------------------------------------

Phases [0-73]

Serial Phase #: 0 Files: 1 Time: 84s

Serial Phase #: 1 Files: 5 Time: 25s

Restart Phase #: 2 Files: 1 Time: 0s

Parallel Phase #: 3 Files: 18 Time: 12s

Restart Phase #: 4 Files: 1 Time: 0s

Serial Phase #: 5 Files: 5 Time: 12s

Serial Phase #: 6 Files: 1 Time: 8s

Serial Phase #: 7 Files: 4 Time: 5s

Restart Phase #: 8 Files: 1 Time: 0s

Parallel Phase #: 9 Files: 62 Time: 48s

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: 13s

Restart Phase #:14 Files: 1 Time: 0s

Parallel Phase #:15 Files: 111 Time: 26s

Restart Phase #:16 Files: 1 Time: 0s

Serial Phase #:17 Files: 3 Time: 1s

Restart Phase #:18 Files: 1 Time: 0s

Parallel Phase #:19 Files: 32 Time: 33s

Restart Phase #:20 Files: 1 Time: 0s

Serial Phase #:21 Files: 3 Time: 4s

Restart Phase #:22 Files: 1 Time: 1s

Parallel Phase #:23 Files: 23 Time: 80s

Restart Phase #:24 Files: 1 Time: 0s

Parallel Phase #:25 Files: 11 Time: 54s

Restart Phase #:26 Files: 1 Time: 0s

Serial Phase #:27 Files: 1 Time: 1s

Restart Phase #:28 Files: 1 Time: 0s

Serial Phase #:30 Files: 1 Time: 0s

Serial Phase #:31 Files: 257 Time: 12s

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: 1s

Serial Phase #:37 Files: 4 Time: 31s

Restart Phase #:38 Files: 1 Time: 1s

Parallel Phase #:39 Files: 13 Time: 49s

Restart Phase #:40 Files: 1 Time: 1s

Parallel Phase #:41 Files: 10 Time: 10s

Restart Phase #:42 Files: 1 Time: 0s

Serial Phase #:43 Files: 1 Time: 4s

Restart Phase #:44 Files: 1 Time: 0s

Serial Phase #:45 Files: 1 Time: 4s

Serial Phase #:46 Files: 1 Time: 0s

Restart Phase #:47 Files: 1 Time: 0s

Serial Phase #:48 Files: 1 Time: 67s

Restart Phase #:49 Files: 1 Time: 1s

Serial Phase #:50 Files: 1 Time: 28s

Restart Phase #:51 Files: 1 Time: 0s

Serial Phase #:52 Files: 1 Time: 12s

Restart Phase #:53 Files: 1 Time: 0s

Serial Phase #:54 Files: 1 Time: 248s

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: 93s

Restart Phase #:59 Files: 1 Time: 1s

Serial Phase #:60 Files: 1 Time: 397s

Restart Phase #:61 Files: 1 Time: 0s

Serial Phase #:62 Files: 1 Time: 944s

Restart Phase #:63 Files: 1 Time: 0s

Serial Phase #:64 Files: 1 Time: 1s

Serial Phase #:65 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/dbhome_1/lib; export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0/dbhome_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin -I /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose -upgrade_mode_only > catupgrd_datapatch_upgrade.log 2> catupgrd_datapatch_upgrade.err

returned from sqlpatch

Time: 34s

Serial Phase #:66 Files: 1 Time: 34s

Serial Phase #:68 Files: 1 Time: 0s

Serial Phase #:69 Files: 1 Calling sqlpatch with LD_LIBRARY_PATH=/u01/app/oracle/product/12.1.0/dbhome_1/lib; export LD_LIBRARY_PATH;/u01/app/oracle/product/12.1.0/dbhome_1/perl/bin/perl -I /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin -I /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch /u01/app/oracle/product/12.1.0/dbhome_1/rdbms/admin/../../sqlpatch/sqlpatch.pl -verbose > catupgrd_datapatch_normal.log 2> catupgrd_datapatch_normal.err

returned from sqlpatch

Time: 34s

Serial Phase #:70 Files: 1 Time: 18s

Serial Phase #:71 Files: 1 Time: 0s

Serial Phase #:72 Files: 1 Time: 0s

Serial Phase #:73 Files: 1 Time: 17s

Grand Total Time: 2524s

LOG FILES: (catupgrd*.log)

Upgrade Summary Report Located in:

/u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/DELL/upgrade/upg_summary.log

Grand Total Upgrade Time: [0d:0h:42m:4s]

[oracle@rac1 admin]$

[oracle@rac1 admin]$ ls -ltr

-rw-r--r-- 1 oracle oinstall 2721374 Dec 19 10:19 catupgrd7.log

-rw-r--r-- 1 oracle oinstall 2789260 Dec 19 10:19 catupgrd6.log

-rw-r--r-- 1 oracle oinstall 1660710 Dec 19 10:19 catupgrd5.log

-rw-r--r-- 1 oracle oinstall 4078880 Dec 19 10:19 catupgrd4.log

-rw-r--r-- 1 oracle oinstall 3457397 Dec 19 10:19 catupgrd3.log

-rw-r--r-- 1 oracle oinstall 1917916 Dec 19 10:19 catupgrd2.log

-rw-r--r-- 1 oracle oinstall 3145158 Dec 19 10:19 catupgrd1.log

-rw-r--r-- 1 oracle oinstall 353113549 Dec 19 10:20 catupgrd0.log

[oracle@rac1 admin]$

[oracle@rac1 admin]$ tail –f catupgrd0.log

Post Upgrade Steps

[oracle@rac1 admin]$
[oracle@rac1 admin]$ ll utlu121s.sql catuppst.sql

-rw-r--r-- 1 oracle oinstall 676 Jan 29 2014 utlu121s.sql

-rw-r--r-- 1 oracle oinstall 12226 Jun 12 2014 catuppst.sql

[oracle@rac1 admin]$

[oracle@rac1 admin]$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 19 11:13:20 2017

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> @utlu121s.sql

PL/SQL procedure successfully completed.

CATCTL REPORT = /u01/app/oracle/product/12.1.0/dbhome_1/cfgtoollogs/DELL/upgrade/upg_summary.log

PL/SQL procedure successfully completed.

Oracle Database 12.1 Post-Upgrade Status Tool 12-19-2017 11:14:07

Component Current Version Elapsed Time

Name Status Number HH:MM:SS

Oracle Server UPGRADED 12.1.0.2.0 00:08:52

JServer JAVA Virtual Machine VALID 12.1.0.2.0 00:01:07

Oracle Workspace Manager VALID 12.1.0.2.0 00:00:38

OLAP Analytic Workspace VALID 12.1.0.2.0 00:00:11

OLAP Catalog OPTION OFF 11.2.0.4.0 00:00:00

Oracle OLAP API VALID 12.1.0.2.0 00:00:13

Oracle XDK VALID 12.1.0.2.0 00:00:27

Oracle Text VALID 12.1.0.2.0 00:00:31

Oracle XML Database VALID 12.1.0.2.0 00:03:35

Oracle Database Java Packages VALID 12.1.0.2.0 00:00:08

Oracle Multimedia VALID 12.1.0.2.0 00:01:33

Spatial UPGRADED 12.1.0.2.0 00:06:36

Oracle Application Express VALID 4.2.5.00.08 00:15:08

Final Actions 00:00:54

Post Upgrade 00:00:03

Total Upgrade Time: 00:40:30

PL/SQL procedure successfully completed.

SQL>

SQL> --

SQL> -- Update Summary Table with con_name and endtime.

SQL> --

SQL> UPDATE sys.registry$upg_summary SET reportname = :ReportName,

2 con_name = SYS_CONTEXT('USERENV','CON_NAME'),

3 endtime = SYSDATE

4 WHERE con_id = -1;

1 row updated.

SQL> commit;

Commit complete.

SQL>

SQL> @catuppst.sql

SQL> Rem

SQL> Rem $Header: rdbms/admin/catuppst.sql /st_rdbms_12.1/1 2014/06/11 20:58:01 surman Exp $

SQL> Rem

SQL> Rem catuppst.sql

SQL> Rem

SQL> Rem NAME

SQL> Rem catuppst.sql - CATalog UPgrade PoST-upgrade actions

SQL> Rem

SQL> Rem DESCRIPTION

SQL> Rem This post-upgrade script performs remaining upgrade actions that

SQL> Rem do not require that the database be open in UPGRADE mode.

SQL> Rem Automatically apply the latest PSU.

SQL> Rem

SQL> Rem NOTES

SQL> Rem You must be connected AS SYSDBA to run this script.

SQL> Rem

. . . . . .

PL/SQL procedure successfully completed.

SQL> set serveroutput off

SQL>

SQL> Rem =======================================================================

SQL> Rem Gather Fixed Objects Stats end

SQL> Rem =======================================================================

SQL>

SQL> Rem =======================================================================

SQL> Rem Signal 'end' of catuppst.sql before catbundle.sql is executed

SQL> Rem =======================================================================

SQL> SELECT dbms_registry_sys.time_stamp('POSTUP_END') as timestamp from dual;

TIMESTAMP

--------------------------------------------------------------------------------

COMP_TIMESTAMP POSTUP_END 2017-12-19 11:15:08

SQL>

SQL> Rem Set errorlogging off

SQL> SET ERRORLOGGING OFF;

SQL>

SQL> Rem

SQL> Rem Set _ORACLE_SCRIPT to false

SQL> Rem

SQL> ALTER SESSION SET "_ORACLE_SCRIPT"=false;

Session altered.

SQL>

SQL> Rem *********************************************************************

SQL> Rem END catuppst.sql

SQL> Rem *********************************************************************

SQL>

to check components validity

SQL>

SQL> set pagesize500

SQL> set linesize 100

SQL>

SQL> select substr(comp_name,1,40) comp_name, status, substr(version,1,10) version from dba_registry order by comp_name;

COMP_NAME STATUS VERSION

---------------------------------------- ----------- ----------

JServer JAVA Virtual Machine VALID 12.1.0.2.0

OLAP Analytic Workspace VALID 12.1.0.2.0

OLAP Catalog OPTION OFF 11.2.0.4.0

OWB VALID 11.2.0.4.0

Oracle Application Express VALID 4.2.5.00.0

Oracle Database Catalog Views UPGRADED 12.1.0.2.0

Oracle Database Java Packages VALID 12.1.0.2.0

Oracle Database Packages and Types UPGRADED 12.1.0.2.0

Oracle Multimedia VALID 12.1.0.2.0

Oracle OLAP API VALID 12.1.0.2.0

Oracle Text VALID 12.1.0.2.0

Oracle Workspace Manager VALID 12.1.0.2.0

Oracle XDK VALID 12.1.0.2.0

Oracle XML Database VALID 12.1.0.2.0

Spatial UPGRADED 12.1.0.2.0

15 rows selected.

SQL>

EMC port check

SQL>

SQL> select dbms_xdb_config.gethttpsport from dual;
dbms_xdb_config.gethttpsport from dual;

GETHTTPSPORT

------------

To set EMC port

SQL>

SQL> exec dbms_xdb_config.sethttpsport(5500);

To Check Container Database status

SQL>
SQL> select name,user,created,cdb,con_id,con_dbid from v$database;

NAME USER CREATED CDB CON_ID CON_DBID

--------- ------------------------------ --------- --- ---------- ----------

DELL SYS 19-DEC-17 NO 0 3933872234

SQL>

Time Zone version of Upgraded Database we can upgrade it

SQL>
SQL> select version from v$timezone_file;

VERSION

----------

SQL>

Scripts to automatically update the RDBMS DST (timezone) version in an 11gR2 or 12c database. (Doc ID 1585343.1)

Updating the RDBMS DST version in 12c Release 1 (12.1.0.1 and up) using DBMS_DST (Doc ID 1509653.1)

Updating the RDBMS DST version in 11g Release 2 (11.2.0.1 and up) using DBMS_DST (Doc ID 977512.1)

SQL> conn u1/u1

Connected.

SQL>

SQL> select * from emp;

ENAME ENO

--------------- ----------

aaa 10

bbb 20

ccc 30

SQL>

Thus we have successfully upgraded Oracle Database from 11204 to 12102.

This Article explains how to Upgrade ORACLE DATABASE in Linux Environment.

Thanks for Reading

Regards,

Mohammed Areefuddin.

Suggested Topics :

Database Upgrade from 11.2.0.4 to 12.1.0.2 Manual Upgrade and DBUA Upgrade Installer Upgrade
11.2.0.1 to 11.2.0.4 Manual Upgrade

Linux	DATABASE	RMAN	RAC	EBS
5.7 Install	DATABASE Install		11201 Install	R1226 Install
Open Filer Instl	Active DataGaurd	Rman DataGuard	RAC DataGuard	EBS DataGuard
		RMAN DB CLONE	RAC DB Clone	R1226 Clone
	ASMCMD ASM Migrt	RMAN ASM Migrt		RMAN Clone
	11204 M-Upgrade			11202 EBS Upgrade
	12102 M-Upgrade			12102 EBS UPGRADE


Linux Basics	DATABASE Basics	RMAN Basics	RAC Basic	EBS Basics

Tuesday, December 19, 2017

Manual Database Upgrade form 11204 to 12102.

No comments:

Post a Comment