OUI-10166: The permission 0755 cannot be set for the file rootconfig.sh 19c grid upgrade from 12c

 

While starting the actual upgrade, If you are seeing the error. as

OUI-10166: The permissions 0755 cannot be set for the file $GRID_HOME/crs/config/rootconfig.sh. OUI-10175: The effective user ID does not match the owner of the file or the process is not the user user.

 SOLUTION:

Check the owner of the file. $GRID_HOME/crs/config/rootconfig.sh . If it is owned by root , then change it to oracle and rerun the gridsetup.sh script.

chown oracle:oinstall rootconfig.sh

PRVF-5311 : File “/tmp/InstallActions error:

If at pre-requisite stage this error is coming, then you can try below solution.

Do below changes to scp. (

# Rename the original scp. ( find where is the scp executable file present)

mv /usr/bin/scp /usr/bin/scp.orig

# Create a new file .

vi /usr/bin/scp

# Add the below line to the new created file .
/usr/bin/scp.orig -T $*

# Change the file permission to 555.

chmod 555 /usr/bin/scp


RETRY THE UPGRADE PROCESS .

###Once upgrade is done , you can revert to the original

mv /usr/bin/scp.orig /usr/bin/scp




19c upgrade matrix

 

Migrate and upgrade from 11g to 18c

http://dbmentors.blogspot.com/2013/05/restore-rman-rac-database-backup-to.html

-MGMTDB:/oraclebase/app/oracle/product/18.0.0/grid:N

test:/oraclebase/app/oracle/product/18.0.0/db11g_home:N

11G HOME --/oraclebase/app/oracle/product/18.0.0/db11g_home

export ORACLE_SID=test1

alter system set cluster_database = False scope = SPFILE sid='*';

create pfile from spfile;

1.       Stop the db service

srvctl stop database -d test

2.        start the database in mount exclusive mode:

startup mount exclusive restrict

select instance_name,status,logins from v$Instance;

\

[oracle@ompl ~]$ srvctl status database -d test

Instance test1 is running on node ompl

Instance test2 is not running on node ompl002

create spfile='+DATA_DG01/test/spfiletest.ora' from pfile='/oraclebase/app/oracle/product/18.0.0/db11g_home/dbs/inittest.ora';

drop database

SQL> startup nomount pfile='/oraclebase/app/oracle/product/18.0.0/db11g_home/dbs/inittest.ora';

ORACLE instance started.

Total System Global Area 7.9369E+10 bytes

Fixed Size                  2236848 bytes

Variable Size            7516196432 bytes

Database Buffers         7.1672E+10 bytes

Redo Buffers              178704384 bytes

SQL> create spfile='+DATA_DG01/test/spfiletest.ora' from pfile='/oraclebase/app/oracle/product/18.0.0/db11g_home/dbs/inittest.ora';

File created.

SQL> shut immediate;

ORA-01507: database not mounted

ORACLE instance shut down.

SQL> startup nomount;

ORACLE instance started.

SQL> select dbid from v$database;

      DBID

----------

3447057297

______________________________________________________

node 2

[oracle@ompl002 dbs]$ cat inittest2.ora_bk

SPFILE='+DATA_DG01/test/spfiletest.ora'

*

ERROR at line 1:

ORA-01092: ORACLE instance terminated. Disconnection forced

ORA-30012: undo tablespace 'UNDOTBS02' does not exist or of wrong type

Process ID: 112727

Session ID: 667 Serial number: 3

startup with pfile

starup mount pfile='/orace/initdwpx.ora'

alter database open;

alter tablespace undotbs2 rename to undotdbs02;

exit

srvctl stop database -d test

check the spfile location on both the side

crsctl stat res -t

[oracle@ompl002 admin]$ srvctl status database -d test

Instance test1 is running on node ompl001

Instance test2 is running on node ompl002

srvctl stop database -d test

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

stop the cluster on second node

[oracle@ompl002 dbs]$ sqlplus sys/***@pdrl-scan

SQL*Plus: Release 18.0.0.0.0 - Production on Tue Jul 9 15:07:54 2019

Version 18.3.0.0.0

Copyright (c) 1982, 2018, Oracle.  All rights reserved.

ERROR:

ORA-12504: TNS:listener was not given the SERVICE_NAME in CONNECT_DATA

test =

  (DESCRIPTION =

    (ADDRESS = (PROTOCOL = TCP)(HOST = prddl-scan)(PORT = 1521))

    (CONNECT_DATA =

      (SERVER = DEDICATED)

      (SERVICE_NAME = test)

    )

  )

oragrid$ srvctl stop LISTENER -n OMPRD001

oragrid$srvctl stop LISTENER -n oracledb1

oragrid$srvctl stop LISTENER -n oracledb2

srvctl stop scan_listener

srvctl start scan_listener

-____________________________________________________________________________________

set dbid=344705297

RMAN_test_CTL_2019_07_02_01_43.bkp

run

{

allocate channel tape_01_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testadm02,CvInstanceName=Instance001)";

restore controlfile from 'c-3447057297-20190712-00';

}

output file name=+DATA_DG01/test/controlfile/current.267.1013439113

output file name=+DATA_DG02/test/controlfile/current.256.1013439113

sql> alter database mount;

SQL> select name from v$controlfile;

NAME

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

output file name=+DATA_DG01/test/controlfile/current.260.1013403753

output file name=+DATA_DG02/test/controlfile/current.256.1013403755

+DATA_DG01/test/controlfile/current.287.1013473803

+DATA_DG02/test/controlfile/current.279.1013473805

run {

allocate channel tape_01_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

allocate channel tape_02_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

allocate channel tape_03_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

allocate channel tape_04_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

allocate channel tape_05_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

allocate channel tape_06_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

allocate channel tape_07_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

allocate channel tape_08_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

allocate channel tape_09_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

allocate channel tape_10_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

#set until time "TO_DATE('2019-07-07 11:44','yyyy-mm-dd hh24:mi:ss')";

set newname for datafile 1 to '+DATA_DG03';

set newname for datafile 2 to '+DATA_DG03';

set newname for datafile 3 to '+DATA_DG03';

set newname for datafile 4 to '+DATA_DG03';

set newname for datafile 5 to '+DATA_DG03';

set newname for datafile 6 to '+DATA_DG03';

set newname for datafile 7 to '+DATA_DG01';

set newname for datafile 9 to '+DATA_DG01';

set newname for datafile 10 to '+DATA_DG02';

set newname for datafile 11 to '+DATA_DG01';

set newname for datafile 14 to '+DATA_DG03';

set newname for datafile 16 to '+DATA_DG02';

set newname for datafile 18 to '+DATA_DG02';

set newname for datafile 19 to '+DATA_DG02';

set newname for datafile 20 to '+DATA_DG02';

set newname for datafile 21 to '+DATA_DG02';

set newname for datafile 22 to '+DATA_DG02';

set newname for datafile 23 to '+DATA_DG03';

set newname for datafile 24 to '+DATA_DG03';

set newname for datafile 25 to '+DATA_DG03';

set newname for datafile 26 to '+DATA_DG03';

set newname for datafile 29 to '+DATA_DG03';

set newname for datafile 168 to '+DATA_DG03';

set newname for datafile 200 to '+DATA_DG02';

# restore the database and switch the datafile names

restore database;

switch datafile all;

recover database;

release channel tape_01_test;

release channel tape_02_test;

release channel tape_03_test;

release channel tape_04_test;

}

Restore archive

__________________________________________________________________________________________________

rman target / catalog=rmanadmin1/****@RMADB

connect catalog rmanadm/*****@RMADB

run

{

allocate channel tape_01_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

allocate channel tape_02_test type 'sbt_tape' PARMS="SBT_LIBRARY=/opt/commvault/Base/libobk.so,BLKSIZE=262144,

ENV=(CvClientName=testADM02,CvInstanceName=Instance001)";

set archivelog destination to '+RECO_DG01/archive';

restore archivelog from logseq=136422 until logseq=136640 thread=1;

release channel tape_01_test;

release channel tape_02_test;

}

restore archivelog from logseq=121835 until logseq=122150 thread=2;

recover database using backup controlfile until cancel;

AUTO

select 'alter database rename file '''||member||''' to '''||replace(member,'&sourcedb','&targetdb')||''';' from v$logfile;

alter database open RESETLOGS;

SQL> alter database open resetlogs;

alter database open resetlogs

*

ERROR at line 1:

ORA-19751: could not create the change tracking file

ORA-19750: change tracking file:

'+DATA_XDW/test/changetracking/ctf.281.816780643'

ORA-17502: ksfdcre:1 Failed to create file

+DATA_XDW/test/changetracking/ctf.281.816780643

ORA-17501: logical block size 4294967295 is invalid

ORA-15001: diskgroup "DATA_XDW" does not exist or is not mounted

ORA-17503: ksfdopn:2 Failed to open file

+DATA_XDW/test/changetracking/ctf.281.816780643

ORA-15001: diskgroup "DATA_XDW" does not exist or is not mounted

ORA-15001: diskgroup "DATA_XDW" does not exist or is not mounted

SQL> alter database disable block change tracking;

Database altered.

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

recover database until available redo;

select name from v$tempfile;

select member from v$logfile;

select name, open_mode, dbid from v$database;

NAME      OPEN_MODE                  DBID

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

test    READ WRITE           34470572097

SELECT NAME FROM V$TEMPFILE;

SHUT immediate;

startup mount;

nid target=/   or nid target=sys/ChangeMe2019

starup mount;

alter database open resetlogs;

alter system set cluster_database = True scope = SPFILE sid='*';

make changes in pfile;

Add the cluster parameter

create spfile='+DATA_DG01/test/spfiletest.ora' from pfile='/oraclebase/app/oracle/product/18.0.0/db11g_home/dbs/inittest.ora';

create pfile='/home/dba/initdwpxw.ora' from spfile;

check the pfile parameter

test2.thread=2

test1.thread=1

test2.instance_number=2

test1.instance_number=1

test2.undo_tablespace='UNDOTBS2'

test1.undo_tablespace='UNDOTBS1'

*.remote_listener='omprl-scan:1521'

*.dispatchers='(PROTOCOL=TCP) (SERVICE=testXDB)'

*.cluster_database=true

startup nomount pfile='/home/dba/initdwpxw.ora';

create spfile='+DATA_DG01' from pfile='/home/dba/initdwpxw.ora';

SHUT IMMEDIATE

ASMCMD> cd +DATA_DG01/test/parameterfile

ASMCMD> ls -l

check the spfile

on node 1

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

[oracle@rac1 dbs]$ cat initdwpxw1.ora

SPFILE='+DATA_DG01/test/parameterfile/spfile.267.924478703'

[oracle@rac1 dbs]$

rm spfile*

On node 2

[oracle@rac2 dbs]$ cat initdwpxw1.ora

SPFILE='+DATA_DG01/test/parameterfile/spfile.267.924478703'

[oracle@rac2 dbs]$

rm spfile*

node 1

. oraenv

test1

startup nomount;

show parameter pfile

alter database mount;

alter database open;

select name, open_mode from gv$database;

follow the above step on node 2 and start the db.

startup mount;

SQL> show parameter spfile;

NAME                                 TYPE        VALUE

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

spfile                               string      +DATA_DG01/test/spfiletest

                                                 .ora

SQL> show parameter cluster;

NAME                                 TYPE        VALUE

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

cluster_database                     boolean     TRUE

cluster_database_instances           integer     2

cluster_interconnects                string

select name,open_mode from gv$database;

select MEMBER from gv$logfile;

ADD DATABASE TO CLUSTER

crsctl check cluster -all

crsctl stat res -t

[oracle@rac1 ~]$ which srvctl

/oraclebase/app/oracle/product/18.0.0/dbhome_1/bin/srvctl

[oracle@rac1 ~]$ srvctl add database -d test -o /oraclebase/app/oracle/product/18.0.0/dbhome_1

srvctl config database -d test -a

 srvctl add instance -d test -i test1 -n ompl

 srvctl add instance -d test -i test2 -n ompl002

 srvctl config database -d test -a

database is already started in sqlplus

srvctl start database -d test

crsctl stat res ora.test.db -t

// some time disk may not appear just restart the db in cluster.

srvctl stop database -d test

srvctl start database -d test

add service

[oracle@rac1 ~]$ srvctl add service -d test -s test_SRV -r test1 -a test2

[oracle@rac1 ~]$ srvctl start service -d test -s test_SRV

[oracle@rac1 ~]$ srvctl status service -d test -s test_SRV

______________________________________________________________________________________________

change the archive to noarchive before compressing. else delete the archives.

[oracle@OMPRLE001 advance_compression]$ more check_what_to_execute

nohup ksh run_advance_compression.ksh > run_advance_compression.ksh.out &

[oracle@OMPRLD001 advance_compression]$ more run_advance_compression.ksh

nohup sqlplus /nolog @Advanced_compress_01.sql > Advanced_compress_01.sql.out &

nohup sqlplus /nolog @Advanced_compress_02a.sql > Advanced_compress_02a.sql.out &

nohup sqlplus /nolog @Advanced_compress_02b.sql > Advanced_compress_02b.sql.out &

nohup sqlplus /nolog @Advanced_compress_03a.sql > Advanced_compress_03a.sql.out &

nohup sqlplus /nolog @Advanced_compress_03b.sql > Advanced_compress_03b.sql.out &

nohup sqlplus /nolog @Advanced_compress_04.sql > Advanced_compress_04.sql.out &

nohup sqlplus /nolog @Advanced_compress_05.sql > Advanced_compress_05.sql.out &

nohup sqlplus /nolog @Advanced_compress_06.sql > Advanced_compress_06.sql.out &

nohup sqlplus /nolog @Advanced_compress_07.sql > Advanced_compress_07.sql.out &

nohup sqlplus /nolog @Advanced_compress_08.sql > Advanced_compress_08.sql.out &

__________________________________________________________________________________

++++++++++++++++++++++++++++++++++++++++++++++++++

uncompress

connect / as sysdba

set time on  timing on  echo on pagesize 200 linesize 200 feed on

show user

select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;

select name from v$database

alter table AZIwU.SALES_F move   nocompress;

alter table AZIwU.ZIU_DAILY_PLANNED_ORDER_FACTS move     nocompress;

alter table AZIwU.Z_INTPART_TAB nocompress;

alter table AZIwU.Z_TEST nocompress;

quit;

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

alter table test.PO_RECEIVING_FACTS move PARTITION SYS_P237220 ONLINE ROW STORE COMPRESS ADVANCED NOLOGGING PARALLEL 4;

_+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

:1,$ s/compress/ONLINE ROW STORE COMPRESS ADVANCED NOLOGGING PARALLEL 4/g

index

select 'alter index '||index_owner||'.'||index_name ||' REBUILD PARTITION ' || PARTITION_NAME || ' online nologging parallel 4 ;' from DBA_IND_PARTITIONS where STATUS='UNUSABLE';

move_nocompress

select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||'  PARTITION '||PARTITION_NAME||' uncompress; '  from all_tab_partitions WHERE table_name='CSW_OR_LINE_FACTS';

select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' uncompress; '  from all_tab_partitions WHERE table_name='CSW_OR_LINE_FACTS';

select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' compress; '  from all_tab_partitions WHERE table_name in ('SALES_F','ZIU_DAILY_PLANNED_ORDER_FACTS','Z_INTPART_TAB','Z_INTPART_TAB','Z_TEST');

select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' ONLINE ROW STORE COMPRESS ADVANCED NOLOGGING PARALLEL 4; '  from all_tab_partitions WHERE table_name in ('SALES_FACTS_NEW');

select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move SUBPARTITION '||SUBPARTITION_NAME||' compress; '  from all_tab_subpartitions WHERE table_name in ('KVI_SU','KVI_S','OMSG.MED','OMSG.KV');

 select 'alter table '||TABLE_OWNER||'.'||TABLE_NAME ||' move PARTITION '||PARTITION_NAME||' ONLINE ROW STORE COM

PRESS ADVANCED NOLOGGING PARALLEL 4; '  from all_tab_partitions WHERE table_name in ('W_AR_BALANCE_CUSTOMER_F','W_KRONOS_HOURS_F','W_OMNI_SALES_ORDER_LINE_F');

select * from OMSTG.KVI_SUMMARY_WBREAKS_STG where rownum<=1;

select * from OMSTG.KVI_SUMMARY_WBREAKS_FINAL_STG where rownum<=1;

select * from OMSTG.MED_KVI_SUMMARY where rownum<=1;

alter table OMSTG.MED_KVI_SUMMARY move PARTITION SYS_P254450 compress;

select * from OMSTG.KVI_SUMMARY_WBREAKS_STG where rownum<=1;

alter table DBMON.LOGIN_TRAIL move compress;

alter table DBMON.SALES_FACTS_TEST move compress;

alter table OBDW.W_GLLANCE_FS_BKP10062015 move compress;

alter table OBDW.W_GLALANCE_F_BKP10062015 move compress;

alter table test.AR_BANCE_FACTS_20180517 move compress;

if any table in advance compression.

header

SET HEADING ON SET PAGESIZE 50001. SET LINESIZE 80. SET TERMOUT OFF SET VERIFY OFF SET FEEDBACK OFF SET TRIMS OFF

connect / as sysdba

set time on  timing on  echo on pagesize 200 linesize 200 feed on

show user

select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;

select name from v$database;

set heading off

set pages 400 lines 400

connect / as sysdba

set time on  timing on  echo on pagesize 200 linesize 200 feed on

show user

select to_char(sysdate, 'YYYYMMDD HH24:MI:SS') from dual;

select name from v$database

++++++++++++++++++++++++++++++++++++++++++++++++++++

crsctl check cluster -all

Upgrade:

________

http://oracle-help.com/oracle-18c/upgrading-oracle-grid-infrastructure-11gr2-to-18c/

[oracle@localhost ~]$ export ORACLE_HOME=/u01/app/oracle/product/18.0.0.0/db_1

[oracle@localhost ~]$ export PATH=$ORACLE_HOME/bin:$ORACLE_HOME/OPatch:$PATH:.

upgrade

Empty Recycle bin.

SQL> PURGE DBA_RECYCLEBIN ;

DBA Recyclebin purged.

Run Gather statistics to finish upgrade soon.

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed

SQL> @emremove.sql

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

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

PL/SQL procedure successfully completed.

[oracle@test admin]$ cd $ORACLE_HOME/olap/admin/

SQL> @catnoamd.sql

/oraclebase/app/oracle/product/18.0.0/db11g_home/jdk/bin/java -jar /oraclebase/app/ora18c/product/18.0.0/software/rdbms/admin/preupgrade.jar FILE DIR /oraclebase/app/preupgrade

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

PREUPGRADE SUMMARY

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

/u01/preupgrade/preupgrade.log

/u01/preupgrade/preupgrade_fixups.sql

/u01/preupgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade log into the database and execute the preupgrade fixups

@/u01/preupgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups

@/u01/preupgrade/postupgrade_fixups.sql

Preupgrade complete: 2019-02-08T19:42:18

After run the above tool we will get below sql files.

[oracle@test ~]$ cd /u01/preupgrade/

[oracle@test preupgrade]$ ls -lrt

total 648

drwxr-xr-x 3 oracle oinstall 4096 Feb 8 19:38 

drwxr-xr-x 3 oracle oinstall 4096 Feb 8 19:42 oracle

-rw-r--r-- 1 oracle oinstall 7963 Feb 8 19:42 preupgrade_driver.sql

-rw-r--r-- 1 oracle oinstall 14846 Feb 8 19:42 dbms_registry_extended.sql

-rw-r--r-- 1 oracle oinstall 422048 Feb 8 19:42 preupgrade_package.sql

-rw-r--r-- 1 oracle oinstall 83854 Feb 8 19:42 preupgrade_messages.properties

-rw-r--r-- 1 oracle oinstall 14383 Feb 8 19:42 parameters.properties

drwxr-xr-x 3 oracle oinstall 4096 Feb 8 19:42 upgrade

-rw-r--r-- 1 oracle oinstall 50172 Feb 8 19:42 components.properties

-rw-r--r-- 1 oracle oinstall 1 Feb 8 19:42 checksBuffer.tmp

-rw-r--r-- 1 oracle oinstall 14521 Feb 8 19:42 preupgrade_fixups.sql

-rw-r--r-- 1 oracle oinstall 9914 Feb 8 19:42 postupgrade_fixups.sql

-rw-r--r-- 1 oracle oinstall 11816 Feb 8 19:42 preupgrade.log

 Run the preupgrade_fixup.sql

SQL> @/oraclebase/app/preupgrade/preupgrade_fixups.sql

Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by:       Oracle Preupgrade Script

                         Version: 18.0.0.0.0 Build: 1

Generated on:            2019-07-15 13:08:59

For Source Database:     test

Source Database Version: 11.2.0.3.0

For Upgrade to Version:  18.0.0.0.0

Preup                             Preupgrade

Action                            Issue Is

Number  Preupgrade Check Name     Remedied    Further DBA Action

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

    1.  invalid_objects_exist     NO          Manual fixup recommended.

    2.  network_acl_priv          NO          Manual fixup recommended.

    3.  exclusive_mode_auth       NO          Manual fixup recommended.

    4.  mv_refresh                NO          Informational only.

                                              Further action is optional.

    5.  pre_fixed_objects         YES         None.

The fixup scripts have been run and resolved what they can. However,

there are still issues originally identified by the preupgrade that

have not been remedied and are still present in the database.

Depending on the severity of the specific issue, and the nature of

the issue itself, that could mean that your database is not ready

for upgrade.  To resolve the outstanding issues, start by reviewing

the preupgrade_fixups.sql and searching it for the name of

the failed CHECK NAME or Preupgrade Action Number listed above.

There you will find the original corresponding diagnostic message

from the preupgrade which explains in more detail what still needs

to be done.

PL/SQL procedure successfully completed.

__________________________________________________________________

[oracle@test preupgrade]$ export ORACLE_SID=test1

[oracle@test preupgrade]$ export ORACLE_HOME=/oraclebase/app/oracle/product/18.0.0/db11g_home

[oracle@test preupgrade]$ export PATH=/oraclebase/app/oracle/product/18.0.0/db11g_home/bin:$PATH

[oracle@test ]$

[oracle@test ~]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Feb 8 19:48:36 2019

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> @/u01/preupgrade/preupgrade_fixups.sql

Executing Oracle PRE-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script

Version: 18.0.0.0.0 Build: 1

Generated on: 2019-02-08 19:42:16

For Source Database: DBWR18C

Source Database Version: 11.2.0.3.0

For Upgrade to Version: 18.0.0.0.0

Preup Preupgrade

Action Issue Is

Number Preupgrade Check Name Remedied Further DBA Action

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

1. parameter_min_val NO Manual fixup recommended.

2. em_present NO Manual fixup recommended.

3. amd_exists NO Manual fixup recommended.

4. apex_manual_upgrade NO Manual fixup recommended.

5. dictionary_stats YES None.

6. trgowner_no_admndbtrg NO Informational only.

Further action is optional.

7. mv_refresh NO Informational only.

Further action is optional.

8. pre_fixed_objects YES None.

9. tablespaces_info NO Informational only.

Further action is optional.

The fixup scripts have been run and resolved what they can. However,

there are still issues originally identified by the preupgrade that

have not been remedied and are still present in the database.

Depending on the severity of the specific issue, and the nature of

the issue itself, that could mean that your database is not ready

for upgrade. To resolve the outstanding issues, start by reviewing

the preupgrade_fixups.sql and searching it for the name of

the failed CHECK NAME or Preupgrade Action Number listed above.

There you will find the original corresponding diagnostic message

from the preupgrade which explains in more detail what still needs

to be done.

PL/SQL procedure successfully completed.

Check INVALID objects count before upgrade :

ORACLE_HOME/rdbms/admin/utlrp.sql

SQL> select count(*) from dba_objects where status='INVALID';

COUNT(*)

--------

0

http://www.acehints.com/2011/12/how-to-change-oracle-11g-10g-rac.html -- archivelog to noarchivelog

____________________________________________________________________________________________________________

Upgrade Database from 11.2.0.3 to 18c  using DBCA :-

DBUA performs some of the checks before actually starting the database upgrade. Some of the checks can be done manually to reduce downtime for the upgrade.

DBUA provides below options:

– Upgrade timezone.

– Gather dictionary statistics before upgrade.

– Make user tablespaces read only.

– Take RMAN backup before upgrade.

– Create Restore Point for Database Flashback

– Restore database backup to rollback upgrade

– Option to execute Custom scripts before and after upgrade

– show the location of DBUA logs and Alert log files.

– Option to upgrade existing listener to 18.x home or create a new listener in 18.x target home.

_____________________________________________________________________________________________

[oracle@test ~]$ export ORACLE_SID=test1

[oracle@test~]$ export ORACLE_HOME=/oraclebase/app/ora18c/product/18.0.0/software

[oracle@test ~]$ export PATH=/oraclebase/app/ora18c/product/18.0.0/software/bin/:$PATH

[oracle@test ~]$ dbua

Select database name to upgrade to 18c version.

________________________________________________________________________________

Preupgrade checks :

If you want to upgrade APEX version to 18.2,download APEX software 18.2 and run apexins.sql and upgrade it.

@apexins.sql APEX APEX TEMP /i/

After preupgrade warnings are resolved,select upgrade options to upgrade the database.

Select Recovery options to recover the database in case of issues.

Create listener or use existing listener which is running.

Select option if you want to configure EM express and port number.

Post Upgrade checks :

SQL> @postupgrade_fixups.sql

Session altered.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

Package created.

No errors.

Package body created.

PL/SQL procedure successfully completed.

No errors.

Package created.

No errors.

Package body created.

No errors.

WARNING - This script was generated for database DBWR18C.

Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script

Version: 18.0.0.0.0 Build: 1

Generated on: 2019-02-08 19:42:17

For Source Database: DBWR18C

Source Database Version: 11.2.0.3.0

For Upgrade to Version: 18.0.0.0.0

Preup Preupgrade

Action Issue Is

Number Preupgrade Check Name   Remedied Further DBA Action

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

10. depend_usr_tables          YES       None.

11. old_time_zones_exist       YES       None.

12. post_dictionary            YES       None.

13. post_fixed_objects         NO        Informational only.Further action is optional.

14. upg_by_std_upgrd           YES       None.

The fixup scripts have been run and resovled what they can. However,

there are still issues originally identified by the preupgrade that

have not been remedied and are still present in the database.

Depending on the severity of the specific issue, and the nature of

the issue itself, that could mean that your database upgrade is not

fully complete. To resolve the outstanding issues, start by reviewing

the postupgrade_fixups.sql and searching it for the name of

the failed CHECK NAME or Preupgrade Action Number listed above.

There you will find the original corresponding diagnostic message

from the preupgrade which explains in more detail what still needs

to be done.

PL/SQL procedure successfully completed.

Session altered.

Now the database upgrade is complete and the database is ready for normal use.

SQL> select VERSION,name,open_mode from v$instance,v$database;

VERSION          NAME           OPEN_MODE

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

18.0.0.0.0       DBWR18C        READ WRITE

SQL> SELECT * FROM v$timezone_file;

FILENAME              VERSION     CON_ID

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

timezlrg_31.dat         31          0

SQL> select TZ_VERSION from registry$database;

TZ_VERSION

----------

    31

Next Post 

Oracle 18c Database Upgrade From…

datafile

+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

set lines 100

set pages 500

set heading off

set feedback off

set trimspool on

select 'set newname for datafile '||file_id||' to '''||file_name||''';'

from dba_data_files order by file_id;

set newname for datafile 1 to '+DATA_XDW/test/datafile/system.262.816780069';

set newname for datafile 2 to '+DATA_XDW/test/datafile/sysaux.263.816780077';

set newname for datafile 3 to '+DATA_XDW/test/datafile/pbi.362.943098607';

set newname for datafile 4 to '+DATA_XDW/test/datafile/undotbs2.266.816780099';

set newname for datafile 5 to '+DATA_XDW/test/datafile/users.391.897502637';

set newname for datafile 6 to '+DATA_XDW/test/datafile/qltxplain_data01.333.897478069';

set newname for datafile 7 to '+RECO_XDW/test/datafile/stg_temp.679.1001862685';

set newname for datafile 9 to '+DATA_XDW/test/datafile/test.579.898105713';

set newname for datafile 10 to '+DATA_XDW/test/datafile/undotbs01.514.925655041';

set newname for datafile 11 to '+DATA_XDW/test/datafile/fods.580.898105755';

set newname for datafile 14 to '+DATA_XDW/test/datafile/other.290.816861941';

set newname for datafile 16 to '+DATA_XDW/test/datafile/omstg_xxeom_artctl_mv_stg.411.898689079';

set newname for datafile 18 to '+DATA_XDW/test/datafile/stg_hist.522.915645149';

set newname for datafile 19 to '+DATA_XDW/test/datafile/edw.441.920392165';

set newname for datafile 20 to '+DATA_XDW/test/datafile/undotbs01.548.925655127';

set newname for datafile 21 to '+DATA_XDW/test/datafile/undotbs01.528.925655137';

set newname for datafile 22 to '+DATA_XDW/test/datafile/mstg.287.892722509';

set newname for datafile 23 to '+DATA_XDW/test/datafile/undotbs01.527.925655147';

set newname for datafile 24 to '+DATA_XDW/test/datafile/bdw.403.892722977';

set newname for datafile 25 to '+DATA_XDW/test/datafile/undotbs01.525.925655155';

set newname for datafile 26 to '+DATA_XDW/test/datafile/undotbs01.521.925655165';

set newname for datafile 29 to '+DATA_XDW/test/datafile/sysaux.402.978467891';

set newname for datafile 168 to '+DATA_XDW/test/datafile/sysaux.462.869854087';

set newname for datafile 200 to '+DATA_XDW/test/datafile/system.602.893182993';

logfile

select 'alter database rename file '''||member||''' to '''||replace(member,'&sourcedb','&targetdb')||''';' from v$logfile;

ALTER DATABASE RENAME FILE ''+DATA/w5005pr/onlinelog/group_4.269.904561065'

multiplexing controlfile

sqlplus '/ as sysdba'

SQL> create pfile='/tmp/spfileBACKUP.ora' from spfile;

sqlplus '/ as sysdba'

SQL> select name from v$controlfile;

NAME

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

+DATA01/MYDB/controlfile/current.200.899418013

SQL> alter system set control_files='+DATA01/MYDB/controlfile/current.200.899418013','+FRA01' scope=spfile sid='*';

sqlplus '/ as sysdba'

SQL> shutdown immediate;

SQL> startup nomount;

rman

RMAN> connect target /

RMAN> restore controlfile from '+DATA1/MYDB/controlfile/current.200.899418013';

sqlplus '/ as sysdba'

SQL> alter system set control_files='+DATA01/MYDB/controlfile/current.200.899418013','+FRA01/MYDB/controlfile/current.200.800418013' scope=spfile sid='*';

alter database rename file '+DATA_XDW/test/onlinelog/group_1.259.932591795' to '+RECO_DG01/test/onlinelog/group_1.259.932591795';

alter database rename file '+DATA_XDW/test/onlinelog/group_1.258.932591799' to '+RECO_DG01/test/onlinelog/group_1.258.932591799';

alter database rename file '+DATA_XDW/test/onlinelog/group_2.261.932591803' to '+RECO_DG01/test/onlinelog/group_2.261.932591803';

alter database rename file '+DATA_XDW/test/onlinelog/group_2.260.932591807' to '+RECO_DG01/test/onlinelog/group_2.260.932591807';

alter database rename file '+DATA_XDW/test/onlinelog/group_3.269.932591811' to '+RECO_DG01/test/onlinelog/group_3.269.932591811';

alter database rename file '+DATA_XDW/test/onlinelog/group_3.268.932591815' to '+RECO_DG01/test/onlinelog/group_3.268.932591815';

alter database rename file '+DATA_XDW/test/onlinelog/group_4.271.932591821' to '+RECO_DG01/test/onlinelog/group_4.271.932591821';

alter database rename file '+DATA_XDW/test/onlinelog/group_4.270.932591823' to '+RECO_DG01/test/onlinelog/group_4.270.932591823';

alter database rename file '+DATA_XDW/test/onlinelog/group_5.274.932591829' to '+RECO_DG01/test/onlinelog/group_5.274.932591829';

alter database rename file '+DATA_XDW/test/onlinelog/group_5.273.932591833' to '+RECO_DG01/test/onlinelog/group_5.273.932591833';

alter database rename file '+DATA_XDW/test/onlinelog/group_6.276.932591837' to '+RECO_DG01/test/onlinelog/group_6.276.932591837';

alter database rename file '+DATA_XDW/test/onlinelog/group_6.275.932591841' to '+RECO_DG01/test/onlinelog/group_6.275.932591841';

alter database rename file '+DATA_XDW/test/onlinelog/group_7.278.932591845' to '+RECO_DG01/test/onlinelog/group_7.278.932591845';

alter database rename file '+DATA_XDW/test/onlinelog/group_7.277.932591849' to '+RECO_DG01/test/onlinelog/group_7.277.932591849';

alter database rename file '+DATA_XDW/test/onlinelog/group_8.280.932591853' to '+RECO_DG01/test/onlinelog/group_8.280.932591853';

alter database rename file '+DATA_XDW/test/onlinelog/group_8.279.932591857' to '+RECO_DG01/test/onlinelog/group_8.279.932591857';

alter database rename file '+DATA_XDW/test/onlinelog/group_9.336.819990135' to '+RECO_DG01/test/onlinelog/group_9.336.819990135';

alter database rename file '+DATA_XDW/test/onlinelog/group_9.337.819990137' to '+RECO_DG01/test/onlinelog/group_9.337.819990137';

alter database rename file '+DATA_XDW/test/onlinelog/group_10.338.819990141' to '+RECO_DG01/test/onlinelog/group_10.338.819990141';

alter database rename file '+DATA_XDW/test/onlinelog/group_10.339.819990145' to '+RECO_DG01/test/onlinelog/group_10.339.819990145';

alter database rename file '+DATA_XDW/test/onlinelog/group_11.340.819990165' to '+RECO_DG01/test/onlinelog/group_11.340.819990165';

alter database rename file '+DATA_XDW/test/onlinelog/group_11.341.819990169' to '+RECO_DG01/test/onlinelog/group_11.341.819990169';

alter database rename file '+DATA_XDW/test/onlinelog/group_12.342.819990173' to '+RECO_DG01/test/onlinelog/group_12.342.819990173';

alter database rename file '+DATA_XDW/test/onlinelog/group_12.343.819990177' to '+RECO_DG01/test/onlinelog/group_12.343.819990177';

alter database rename file '+DATA_XDW/test/onlinelog/group_13.344.819990201' to '+RECO_DG01/test/onlinelog/group_13.344.819990201';

alter database rename file '+DATA_XDW/test/onlinelog/group_13.345.819990205' to '+RECO_DG01/test/onlinelog/group_13.345.819990205';

alter database rename file '+DATA_XDW/test/onlinelog/group_14.346.819990209' to '+RECO_DG01/test/onlinelog/group_14.346.819990209';

alter database rename file '+DATA_XDW/test/onlinelog/group_14.347.819990213' to '+RECO_DG01/test/onlinelog/group_14.347.819990213';

alter database rename file '+DATA_XDW/test/onlinelog/group_15.348.819990229' to '+RECO_DG01/test/onlinelog/group_15.348.819990229';

alter database rename file '+DATA_XDW/test/onlinelog/group_15.349.819990233' to '+RECO_DG01/test/onlinelog/group_15.349.819990233';

alter database rename file '+DATA_XDW/test/onlinelog/group_16.350.819990237' to '+RECO_DG01/test/onlinelog/group_16.350.819990237';

alter database rename file '+DATA_XDW/test/onlinelog/group_16.351.819990239' to '+RECO_DG01/test/onlinelog/group_16.351.819990239';

alter database rename file '+DATA_XDW/test/onlinelog/group_17.352.819990269' to '+RECO_DG01/test/onlinelog/group_17.352.819990269';

alter database rename file '+DATA_XDW/test/onlinelog/group_17.353.819990273' to '+RECO_DG01/test/onlinelog/group_17.353.819990273';

SELECT FILE#, ERROR, ONLINE_STATUS, CHANGE#, TIME

FROM   V$RECOVER_FILE;

RUN

{

  # Set a new location for logs 1 through 100.

  SET ARCHIVELOG DESTINATION TO '/fs1/tmp';

  RESTORE ARCHIVELOG FROM SEQUENCE 1 UNTIL SEQUENCE 100;

  # Set a new location for logs 101 through 200.

  SET ARCHIVELOG DESTINATION TO '/fs2/tmp';

  RESTORE ARCHIVELOG FROM SEQUENCE 101 UNTIL SEQUENCE 200;

  # Set a new location for logs 201 through 300.

  SET ARCHIVELOG DESTINATION TO '/fs3/tmp';

  RESTORE ARCHIVELOG FROM SEQUENCE 201 UNTIL SEQUENCE 300;

  # restore and recover datafiles as needed

  .

  .

  .

}

________________________________________________

create.sql_prd

SQL> alter tablespace temp add tempfile '+DATA_DG03' size 30g;

Tablespace altered.

SQL> alter tablespace temp add tempfile '+DATA_DG03' size 30g;

Tablespace altered.

SQL> alter tablespace temp add tempfile '+DATA_DG03' size 30g;

Tablespace altered.

SQL> alter tablespace temp add tempfile '+DATA_DG03' size 30g;

Tablespace altered.

SQL> alter tablespace temp add tempfile '+DATA_DG03' size 30g;

Tablespace altered.

Upgrade Database from 11g to 12c manually and then moving a Non Container Database (Non CDB) to Container Database (CDB as PDB):

Current ORACLE_HOME= /u00/app/oracle/product/11.2.0/db_1

Target ORACLE_HOME= /u00/app/oracle/product/12.1.0/database

Download 12c binaries and unzip in new home:

-rwxr-xr-x 1 oracle oinstall 1673544724 Dec 19 10:18 linuxamd64_12102_database_1of2.zip

-rwxr-xr-x 1 oracle oinstall  1014530602 Dec 19 10:20 linuxamd64_12102_database_2of2.zip

Install 12c Database software :

BANK-> cd /u00/app/oracle/product/12.1.0/database/

BANK-> ./runInstaller oracle.install.option=INSTALL_DB_SWONLY \

> ORACLE_BASE=/u00/app/oracle \

> ORACLE_HOME=/u00/app/oracle/product/12.1.0/database \

> UNIX_GROUP_NAME=oinstall \

> oracle.install.db.DBA_GROUP=dba \

> oracle.install.db.OPER_GROUP=oper \

> oracle.install.db.BACKUPDBA_GROUP=dba \

> oracle.install.db.DGDBA_GROUP=dba \

> oracle.install.db.KMDBA_GROUP=dba \

> FROM_LOCATION=../stage/products.xml \

> INVENTORY_LOCATION=/u00/app/oraInventory \

> SELECTED_LANGUAGES=en \

> oracle.install.db.InstallEdition=EE \

> DECLINE_SECURITY_UPDATES=true  -silent -waitForCompletion

Sample output:

Starting Oracle Universal Installer...

Checking Temp space: must be greater than 500 MB.   Actual 1512 MB    Passed

Checking swap space: must be greater than 150 MB.   Actual 15297 MB    Passed

Preparing to launch Oracle Universal Installer from /tmp/OraInstall2016-12-19_02-00-46PM. Please wait ...

The installation of Oracle Database 12c was successful.

Please check '/u00/app/oraInventory/logs/silentInstall2016-12-19_02-25-07PM.log' for more details.

As a root user, execute the following script(s):

 1. /u00/app/oracle/product/12.1.0/database/root.sh

PRECHECKS:

1. CHECK THE INVALID OBJECTS( ALL SHOULD BE VALID)

SQL>  select comp_id,status from dba_registry;

COMP_ID                        STATUS

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

CATALOG                        VALID

CATPROC                        VALID

2. Check duplicate objects owned by system and sys

select object_name, object_type

from dba_objects

where object_name||object_type in

(select object_name||object_type

from dba_objects

where owner = 'SYS')

and owner = 'SYSTEM';

OBJECT_NAME                                              OBJECT_TYPE

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

DBMS_REPCAT_AUTH                                         PACKAGE BODY

AQ$_SCHEDULES_PRIMARY                                       INDEX

AQ$_SCHEDULES                                                          TABLE

DBMS_REPCAT_AUTH                                           PACKAGE

If you found any other objects other than these four, then   investigate or cleanup those  objects owned by sys and system.

3. Check the integrity of database.

4. Run utlrp.sql to validate invalid objects

sql>@utlrp.sql;

5. Run preupgrade tool

SQL> @preupgrd.sql;

Sample Output:

SQL> @preupgrd.sql;

Loading Pre-Upgrade Package...

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

Executing Pre-Upgrade Checks in UPGRD...

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

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

          ====>> ERRORS FOUND for UPGRD <<====

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

ACTIONS REQUIRED:

1. Review results of the pre-upgrade checks:

 /u00/app/oracle/cfgtoollogs/UPGRD/preupgrade/preupgrade.log

2. Execute in the SOURCE environment BEFORE upgrade:

 /u00/app/oracle/cfgtoollogs/UPGRD/preupgrade/preupgrade_fixups.sql

3. Execute in the NEW environment AFTER upgrade:

 /u00/app/oracle/cfgtoollogs/UPGRD/preupgrade/postupgrade_fixups.sql

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

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

Pre-Upgrade Checks in UPGRD Completed.

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

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

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

Execute the preupgrade_fixup.sql and check whether changes are reflecting or not.

SQL> @/u00/app/oracle/cfgtoollogs/UPGRD/preupgrade/preupgrade_fixups.sql

Sample Output:

SQL> @/u00/app/oracle/cfgtoollogs/UPGRD/preupgrade/preupgrade_fixups.sql;

Pre-Upgrade Fixup Script Generated on 2016-12-19 15:15:25  Version: 12.1.0.2 Build: 006

Beginning Pre-Upgrade Fixups...

Executing in container UPGRD

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

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.

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

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

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.

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

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

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

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

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

Check Tag:     APEX_UPGRADE_MSG

Check Summary: Check that APEX will need to be upgraded.

Fix Summary:   Oracle Application Express can be manually upgraded prior to database upgrade.

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

Fixup Returned Information:

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

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

                ************* Fixup Summary ************

 4 fixup routines generated INFORMATIONAL messages that should be reviewed.

**************** Pre-Upgrade Fixup Script Complete *********************

If still changes are not reflecting  Check the preupgrade_fixups.sql script and do the changes manually.

In my case, these changes  recommended where I changed below parameters.

– processes” needs to be increased to at least 300

– ALTER SYSTEM SET PROCESSES=300 SCOPE=SPFILE

– EXECUTE dbms_preup.purge_recyclebin_fixup;

– Alter system set job_queue_processes= 100

– EXECUTE dbms_stats.gather_dictionary_stats;

6. Dependencies on Network Utility Packages:

SQL> SELECT * FROM DBA_DEPENDENCIES

WHERE referenced_name IN ('UTL_TCP','UTL_SMTP','UTL_MAIL','UTL_HTTP','UTL_

INADDR','DBMS_LDAP')

AND owner NOT IN ('SYS','PUBLIC','ORDPLUGINS'); 2 3 4

no rows selected

7. Check the timezone version:

SQL> SELECT version FROM v$timezone_file;

VERSION

----------

14

For 12c, the new timezone is 18. So after the db upgrade is completed, we will upgrade the dst timezone from 14 to 18.

8. Optimizer Statistics

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

PL/SQL procedure successfully completed.

09. Ensure That No Files Need Media Recovery Before Upgrading

SQL> SELECT * FROM v$recover_file;

no rows selected

10. Ensure That No Files Are in Backup Mode before Upgrading

SQL> SELECT * FROM v$backup WHERE status != 'NOT ACTIVE';

no rows selected

11. Resolve Outstanding Distributed Transactions Before Upgrading

SQL> SELECT * FROM dba_2pc_pending;

12. Purge the Database Recycle Bin Before Upgrading :

SQL> PURGE DBA_RECYCLEBIN;

13. Synchronize the Standby Database with the Primary Database When Upgrading

SQL> SELECT SUBSTR(value,INSTR(value,'=',INSTR(UPPER(value),'SERVICE'))+1)

FROM v$parameter

WHERE name LIKE 'log_archive_dest%' AND UPPER(value) LIKE 'SERVICE%'; 2 3

14. Disable cronjob,

Take backup of crontab and comment the same.

15. Disable dbms_schduler jobs:

SQL> set pagesize 2000

SQL> set lines 2000

SQL> set long 99999

SQL> select owner,JOB_NAME,ENABLED,state from dba_scheduler_jobs;

Disable the scheduled jobs by using below command

SQL> execute dbms_scheduler.disable('DRA_REEVALUATE_OPEN_FAILURES);

16. Verify system and sys default tablespace.(Both should be system tablespace)

SQL> SELECT username, default_tablespace

FROM dba_users

WHERE username in ('SYS','SYSTEM'); 2 3

USERNAME DEFAULT_TABLESPACE

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

SYSTEM SYSTEM

SYS SYSTEM

17. Check whether database has any externally authenticated SSL users

sql>SELECT name FROM sys.user$

WHERE ext_username IS NOT NULL

AND password = 'GLOBAL';

18. Remove EM repository

---Enterprise Manager Database Control is superseded in 12c by Oracle Enterprise Manager Express . Therefore no repository is needed anymore .

---Run the below script

$emctl stop dbcontrol

SQL> @ ?/rdbms/admin/emremove.sql

19. Review any unnecessary hidden/underscore parameters

SQL> SELECT name, value from SYS.V$PARAMETER WHERE name LIKE '\_%' ESCAPE '\' order by name;

no rows selected

20. With this our precheck completes. Now upgrade phase.

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

UPGRADE:

21. stop the listener and shutdown the database.

lsnrctl stop

SQL>shutdown immediate

22.Update the ORACLE_HOME,PATH pointing to 12C Home.

export ORACLE_HOME= /apps/oracle/product/12.1.0.2/database

export PATH=$ORACLE_HOME/bin:$PATH

23. Move the spfile and password file from 11g oracle_home/dbs location to 12cHome/dbs location.

24. start the database in upgrade mode.

$ cd $ORACLE_HOME/rdbms/admin

$ sqlplus "/ as sysdba"

SQL> startup UPGRADE

SQL> exit

25.Run catupgrade script from os level with parallel.

cd $ORACLE_HOME/rdbms/admin

$ORACLE_HOME/perl/bin/perl catctl.pl -n  6 -l $ORACLE_HOME/diagnostics catupgrd.sql

Open another window and check the log.

Sample Output:

TESCOBANK-> $ORACLE_HOME/perl/bin/perl catctl.pl -n  6 -l $ORACLE_HOME/diagnostics catupgrd.sql

Argument list for [catctl.pl]

SQL Process Count     n = 6

SQL PDB Process Count N = 0

Input Directory       d = 0

Phase Logging Table   t = 0

Log Dir               l = /u00/app/oracle/product/12.1.0/database/diagnostics

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

Analyzing file catupgrd.sql

Log files in /u00/app/oracle/product/12.1.0/database/diagnostics

cd /u00/app/oracle/product/12.1.0/database/diagnostics

tail -100f catupgrd0.log

26. Run the Post-Upgrade Status l

---Run the Post-Upgrade Status Tool $ORACLE_HOME/rdbms/admin/utlu121s.sql which provides a summary of the upgrade

$ sqlplus "/as sysdba"

SQL> STARTUP

SQL> @utlu121s.sql

Sample Output:

SQL> @utlu121s.sql

PL/SQL procedure successfully completed.

PL/SQL procedure successfully completed.

 CATCTL REPORT = /u00/app/oracle/product/12.1.0/database/cfgtoollogs/UPGRD/upgrade/upg_summary.log

PL/SQL procedure successfully completed.

   

Oracle Database 12.1 Post-Upgrade Status Tool           12-20-2016 08:38:10

Component                               Current         Version  Elapsed Time

Name                                    Status          Number   HH:MM:SS

Oracle Server                          UPGRADED      12.1.0.2.0  00:11:27

JServer JAVA Virtual Machine              VALID      12.1.0.2.0  00:03:28

Oracle Workspace Manager                  VALID      12.1.0.2.0  00:00:58

OLAP Analytic Workspace                   VALID      12.1.0.2.0  00:00:22

OLAP Catalog                         OPTION OFF      11.2.0.4.0  00:00:00

Oracle OLAP API                           VALID      12.1.0.2.0  00:00:24

Oracle XDK                                VALID      12.1.0.2.0  00:00:41

Oracle Text                               VALID      12.1.0.2.0  00:00:54

Oracle XML Database                       VALID      12.1.0.2.0  00:02:08

Oracle Database Java Packages             VALID      12.1.0.2.0  00:00:14

Oracle Multimedia                         VALID      12.1.0.2.0  00:02:22

Spatial                                UPGRADED      12.1.0.2.0  00:05:08

Oracle Application Express                VALID     4.2.5.00.08  00:24:24

Final Actions                                                    00:01:33

Post Upgrade                                                     00:01:40

Total Upgrade Time: 00:56:14

27.Run Catuppst.sql

The catuppst.sql script is run as part of the upgrade process unless the upgrade returns errors during the process.

Check the log file for “BEGIN catuppst.sql” to verify that catuppst.sql ran during the upgrade process. If catuppst.sql has not run, then proceed to run catuppst.sql as shown in this step.

Warning messages are also displayed when running catctl.pl indicating that catuppst.sql was not run during the upgrade.

SQL>@catuppst.sql

Now upgrade is completed. Now Proceed with POST CHECK.

27. UPGRADE DST TIME ZONE:

Download the dst upgrade script from oracle

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=264501110788152&parent=DOCUMENT&sourceId=1509653.1&id=1585343.1&_afrWindowMode=0&_adf.ctrl-state=1wvfm6az4_4

unzip DBMS_DST_scriptsV1.9.zip

Archive:  DBMS_DST_scriptsV1.9.zip

   creating: DBMS_DST_scriptsV1.9/

  inflating: DBMS_DST_scriptsV1.9/countstarTSTZ.sql

  inflating: DBMS_DST_scriptsV1.9/countstatsTSTZ.sql

  inflating: DBMS_DST_scriptsV1.9/upg_tzv_apply.sql

  inflating: DBMS_DST_scriptsV1.9/upg_tzv_check.sql

$ cd DBMS_DST_scriptsV1.9

SQL> @countstatsTSTZ.sql

Purge the scheduler jobs

SQL> exec dbms_scheduler.purge_log;

Run upg_tzv_check.sql ( it will detect the highest installed DST patch automatically)

SQL> spool upg_tzv_check.log

SQL> @upg_tzv_check.sql;

Run upg_tzv_apply.sql ( It will do the actual dst upgrade)

SQL> @upg_tzv_apply.sql

Once dst upgrade is successful , validate the time_zone(It should be 18)

SQL> SELECT version FROM v$timezone_file;

   VERSION

----------

        18

1 row selected.

Once DST upgrade completes, Do the below post checks.

1. SQL> @/u00/app/oracle/cfgtoollogs/UPGRD/preupgrade/postupgrade_fixups.sql;

2. Change the ORACLE_HOME to 12c in listener.ora file.

3. Uncomment the crontab

4.Enable the jobs in dba_scheduler_jobs which we disabled before the upgrade.

5. run utluiobj.sql from $ORACLE_HOME/rdbms/admin/ to identify/compare any new invalid objects due to the upgrade.

12C Database upgrade completed.

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

Moving a regular Non CDB Database to CDB architecture. DBMS_PDB.DESCRIBEmetadata procedure is used for the same purpose.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> STARTUP MOUNT

ORACLE instance started.

Total System Global Area 5167382528 bytes

Fixed Size                  2935128 bytes

Variable Size            1056966312 bytes

Database Buffers         4093640704 bytes

Redo Buffers               13840384 bytes

Database mounted.

SQL> ALTER DATABASE OPEN READ ONLY;

Database altered.

SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;

NAME      OPEN_MODE

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

UPGRD     READ ONLY

2. Connect to the Non-CDB Database and execute DBMS_PDB.DESCRIBE procedure to generate an xml file that will help to build the Pluggable Database on the CDB Database

SQL> BEGIN

  DBMS_PDB.DESCRIBE(

    pdb_descr_file => '/u02/backup/ora12c.xml');

END;

/  

PL/SQL procedure successfully completed.

3. Shutdown the Non CDB  Database, and login to CDB Database  as SYS user

SQL> SHUTDOWN IMMEDIATE

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> exit

If the upgraded database is the only database on the current system, then you need to create a Container Database (CDB) first

Once the CDB is created you can proceed with the next step.

• Create a container database

DBCA SILENT installation:

TESCOBANK-> /u00/app/oracle/product/12.1.0/database/bin/dbca -silent \

> -createDatabase \

> -templateName General_Purpose.dbc \

> -gdbName CONUPGRD \

> -sid CONUPGRD1 \

> -createAsContainerDatabase true \

> -numberOfPdbs 2 \

> -pdbName pdbupgrd \

> -pdbadminUsername pdba \

> -pdbadminPassword oracle12c \

> -SysPassword oracle12c \

> -SystemPassword oracle12c \

> -emConfiguration NONE \

> -datafileDestination /u02/oradata \

> -storageType FS \

> -characterSet AL32UTF8 \

> -memoryPercentage 40

Copying database files

1% complete

2% complete

27% complete

Creating and starting Oracle instance

29% complete

32% complete

33% complete

34% complete

38% complete

42% complete

43% complete

45% complete

Completing Database Creation

48% complete

51% complete

53% complete

62% complete

70% complete

72% complete

Creating Pluggable Databases

78% complete

83% complete

100% complete

Look at the log file "/u00/app/oracle/cfgtoollogs/dbca/CONUPGRD/CONUPGRD.log" for further details.

TESCOBANK-> vi "/u00/app/oracle/cfgtoollogs/dbca/CONUPGRD/CONUPGRD.log"

TESCOBANK-> sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Tue Dec 20 11:42:13 2016

Copyright (c) 1982, 2014, Oracle.  All rights reserved.

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> select name,open_mode from v$database;

NAME      OPEN_MODE

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

CONUPGRD  READ WRITE

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDBUPGRD1                      READ WRITE NO

         4 PDBUPGRD2                      READ WRITE NO

SQL> show pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDBUPGRD1                      READ WRITE NO

         4 PDBUPGRD2                      READ WRITE NO

SQL> select pdb_id, con_uid, pdb_name from dba_pdbs order by 1;

    PDB_ID    CON_UID

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

PDB_NAME

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

         2 2721201891

PDB$SEED

         3  105835700

PDBUPGRD1

         4 2418664579

PDBUPGRD2

SQL> set pages 1000 lines 1000

SQL> select name from v$datafile;

    CON_ID NAME

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

         1 /u02/oradata/CONUPGRD/users01.dbf

         1 /u02/oradata/CONUPGRD/sysaux01.dbf

         1 /u02/oradata/CONUPGRD/undotbs01.dbf

         1 /u02/oradata/CONUPGRD/system01.dbf

         2 /u02/oradata/CONUPGRD/pdbseed/system01.dbf

         2 /u02/oradata/CONUPGRD/pdbseed/sysaux01.dbf

         3 /u02/oradata/CONUPGRD/pdbupgrd1/system01.dbf

         3 /u02/oradata/CONUPGRD/pdbupgrd1/sysaux01.dbf

         3 /u02/oradata/CONUPGRD/pdbupgrd1/pdbupgrd1_users01.dbf

         4 /u02/oradata/CONUPGRD/pdbupgrd2/system01.dbf

         4 /u02/oradata/CONUPGRD/pdbupgrd2/sysaux01.dbf

         4 /u02/oradata/CONUPGRD/pdbupgrd2/pdbupgrd2_users01.dbf

On CDB , Create the PDB Database by mapping the XML file that Is generated in step 2

SQL> SELECT NAME,OPEN_MODE FROM V$DATABASE;

NAME      OPEN_MODE

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

CONUPGRD  READ WRITE

SQL> CREATE PLUGGABLE DATABASE ORA12C USING '/u02/backup/ora12c.xml'

COPY

FILE_NAME_CONVERT = ('/u00/app/oracle/oradata/UPGRD/','/u02/oradata/datafile/CONUPGRD');   2    3

Pluggable database created.

From the parameter FILE_NAME_CONVERT,  "/u00/app/oracle/oradata/UPGRD/" is the source Database files location (Non CDB), and "/u02/oradata/datafile/CONUPGRD" is the target database files location (CDB/PDB).

When we execute the above command, Oracle copies files from Non CDB's location to CDBs and plugs the database into Container, and creates it.

Following query gives the status of the new PDB.

5. Connect to the new PDB that has been created on CDB Database  as SYS, and execute the "noncdb_to_pdb.sql" script.

SQL> sho pdbs;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED

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

         2 PDB$SEED                       READ ONLY  NO

         3 PDBUPGRD1                      READ WRITE NO

         4 PDBUPGRD2                      READ WRITE NO

         5 ORA12C                         MOUNTED

                         

SQL> ALTER SESSION SET CONTAINER=ORA12C;

Session altered.

SQL> @$ORACLE_HOME/rdbms/admin/noncdb_to_pdb.sql

SQL> ALTER PLUGGABLE DATABASE ORA12C OPEN;

Pluggable database altered.

SQL> SELECT NAME,OPEN_MODE FROM V$PDBS;

NAME                           OPEN_MODE

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

ORA12C                         READ WRITE

1 row selected.

SQL> select name,open_mode from v$database;

NAME      OPEN_MODE

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

CONUPGRD  READ WRITE

Ref :http://nayakvishwanath.blogspot.com/
for my reference