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.