OraCsdP: Oracle Migration Guide ( Upgrade , RU Patch )

1] Migrate to 11.2.0.4

Goal	Oracle 11G Migration
Source Version	11.2.0.2 or 11.2.0.3
Target Version	11.2.0.4

1.1 Prerequisite

Get info from Source DB :

Get Invalid Objects Before

column owner format a10;

column object_name format a25;

column object_type format a25;

select owner, object_name, object_type from dba_objects where status = 'INVALID' order by 1, 2, 3;

Source version shutdown

Listener & Instance

shutdown immediate

Copy Init file / Passwd File / TNS Files to New Oracle 11G Home

1.2 Upgrade

In New Oracle Home Environment :

Run Pre upgrade script, review, upgrade

$ mkdir /work/oracle/<db_name>/upgrade

$ cd /work/oracle/<db_name>/upgrade $

$ sqlplus / as sysdba

Connected to an idle instance.

SQL> startup upgrade

ORACLE instance started.

Database mounted.

Database opened.

SQL> spool utlu112i.log

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

SQL> spool off

SQL> spool upgrade.11.2.0.4.log

SQL> @catupgrd.sql

exit

1.3 Post Installation

Startup , check and compile Invalid Objects

$ sqlplus / as sysdba

Connected to an idle instance.

SQL> startup

ORACLE instance started.

Database mounted.

Database opened.

SQL> select * from registry$history order by id;

SQL> select * from registry$error;

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

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

Start Listener

Get Invalid Objects After

2] Upgrade to Oracle 19C

Goal

Oracle 19C Upgrade

Source

Version

11.2.0.4

or 12.2.0.1

Target

Version

19C :

19.3 + RU Patch JULY 2020

= 19.8

Oracle 19c - Complete Checklist for Manual Upgrades to Non-CDB Oracle Database 19c (Doc ID 2539778.1)

My ex. has 12C Source DB.

19C Engine Path : /oracle/19CDB

2.1 Upgrade Prerequisite N01

UNDER SOURCE ORACLE HOME

Remove Streams setup. For detailed steps, refer to the section "Removing

an Oracle Streams Configuration" in the Oracle Streams Concepts and

Administration Guide specific for the Oracle release from which you are

removing. For 12.1.0.2 and higher, procedure

dbms_streams_adm.remove_streams_configuration can be safely used.

$ sqlplus / as sysdba

SQL> exec dbms_streams_adm.remove_streams_configuration ;

PL/SQL procedure successfully completed.

· Datapump Pending Jobs

Run Dynamic SQL :

cat kill_dp_job.sql

-- For jobs that were stopped in the past and won't be restarted, delete the master table.

SELECT 'drop table '||o.owner||'."'||object_name||'" ;'

FROM dba_objects o, dba_datapump_jobs j

WHERE o.owner=j.owner_name AND o.object_name=j.job_name and state = 'NOT RUNNING'

AND j.job_name NOT LIKE 'BIN$%' order by 1 ;

$ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Fri Nov 6 15:49:28 2020

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

select 'drop table '||a.OWNER||'.'||OBJECT_NAME||' purge ; '

from dba_objects a , dba_external_tables b

where a.object_name like 'ET$%' and a.OBJECT_TYPE = 'TABLE' and a.owner=b.owner and a.object_name = b.table_name and DEFAULT_DIRECTORY_NAME like '%DP_DIR'

· DST Pending Update Operations

ALTER SESSION SET EVENTS '30090 TRACE NAME CONTEXT FOREVER, LEVEL 32';

exec dbms_dst.unload_secondary;

ALTER SESSION SET EVENTS '30090 TRACE NAME CONTEXT FOREVER, OFF';

SELECT PROPERTY_NAME, SUBSTR(property_value, 1, 30) value FROM DATABASE_PROPERTIES WHERE PROPERTY_NAME LIKE 'DST_%' ORDER BY PROPERTY_NAME;

DST_UPGRADE_STATE should be = NONE

· Get Invalid Objects Before

column owner format a10;

column object_name format a25;

column object_type format a25;

select owner, object_name, object_type from dba_objects where status = 'INVALID' order by 1, 2, 3;

· Preupgrade Check

Sous le Moteur 12.1 :

$ mkdir /work/oracle/<db_name>/upgrade

$ cd /work/oracle/<db_name>/upgrade $

$ java -jar /oracle/19CDB/rdbms/admin/preupgrade.jar DIR /work/oracle/<db_name>/upgrade

/work/oracle/<db_name>/upgrade $ java -jar /oracle/19CDB/rdbms/admin/preupgrade.jar DIR /work/oracle/<db_name>/upgrade

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

PREUPGRADE SUMMARY

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

/work/oracle/<db_name>/upgrade/preupgrade.log

/work/oracle/<db_name>/upgrade/preupgrade_fixups.sql

/work/oracle/<db_name>/upgrade/postupgrade_fixups.sql

Execute fixup scripts as indicated below:

Before upgrade:

Log into the database and execute the preupgrade fixups

@/work/oracle/<db_name>/upgrade/preupgrade_fixups.sql

After the upgrade:

Log into the database and execute the postupgrade fixups

@/work/oracle/<db_name>/upgrade/postupgrade_fixups.sql

Preupgrade complete: 2020-11-06T15:15:12

/work/oracle/<db_name>/upgrade $

Step 5> Executed below steps as part of DBUA Warnings in above step.

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

SQL> alter system set xml_db_events=DISABLE Scope=BOTH;

System altered.

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

SET SERVER OUPUT ON;

SQL> @?/olap/admin/catnoamd.sql

SQL> drop user olapsys cascade;

User dropped.

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

SQL> drop package body SYS.FGAC_SECURITY;

Package body dropped.

SQL> drop public synonym X$KCBWDS;

Synonym dropped.

SQL> drop public synonym X$KGLLK;

Synonym dropped.

SQL> drop public synonym X$KSLEI;

Synonym dropped.

SQL> Drop Package SYS.WWV_FLOW_VAL ;

Package dropped.

SQL> Drop view SYS.X_$KSLEI ;

Drop view SYS. X_$KGLLK ;

Drop view SYS. X_$KCBWDS ;

View dropped.

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

SQL> @emremove.sql

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

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

PL/SQL procedure successfully completed.

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

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

Get Invalid objects

set pages 5000 lines 150

col owner for a15

col object_name for a30

col object_type for a15

spool inv.obj.lst

select owner,object_name,object_type from dba_objects where status !='VALID' order by 1,2,3 ;

Run generated Pre Upgrade Script.

/work/oracle/<db_name>/upgrade $ sqlplus / as sysdba @/work/oracle/<db_name>/upgrade/preupgrade_fixups.sql

2.2 Upgrade Prerequisite N02

STILL UNDER SOURCE ORACLE HOME

Prepare 19C Init file

2. Remove initialization parameters that Oracle has obsoleted or removed.

optimizer_adaptive_features

3. Review and remove any unnecessary HIDDEN/UNDERSCORE parameters.

$ sqlplus "/ as sysdba"

SQL> create pfile = '/work/oracle/<db_name>/pfile/init<DB_NAME>.preup.ora' from spfile ;

File created.

SQL> !cp -p /work/oracle/<db_name>/pfile/init<DB_NAME>.preup.ora /work/oracle/<db_name>/pfile/init<DB_NAME>.preup.ora.ori

SQL> !vi /work/oracle/<db_name>/pfile/init<DB_NAME>.preup.ora

SQL> !diff /work/oracle/<db_name>/pfile/init<DB_NAME>.preup.ora /work/oracle/<db_name>/pfile/init<DB_NAME>.preup.ora.ori

REMOVE ANY DOUBLE UNDERSCORE PARAMETERS

> <DB_NAME>.__data_transfer_cache_size=0

> <DB_NAME>.__db_cache_size=2701131776

> <DB_NAME>.__java_pool_size=50331648

> <DB_NAME>.__large_pool_size=16777216

> <DB_NAME>.__oracle_base='/oracle'#ORACLE_BASE set from environment

> <DB_NAME>.__pga_aggregate_target=503316480

> <DB_NAME>.__sga_target=3791650816

> <DB_NAME>.__shared_io_pool_size=201326592

> <DB_NAME>.__shared_pool_size=788529152

> <DB_NAME>.__streams_pool_size=0

ALSO REMOVE

> *.compatible='12.1.0'

> *.deferred_segment_creation=FALSE

> *.optimizer_adaptive_features=FALSE

> *.os_authent_prefix=''

> *.pre_page_sga=FALSE

> *.remote_os_authent=TRUE#Deprecate parameter

> *.utl_file_dir=''

ADD

< *.compatible='19.0.0'

Shutdown Source Listener & Instance

SQL> shut immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> !mv /work/oracle/<db_name>/pfile/spfile<DB_NAME>.ora /work/oracle/<db_name>/pfile/spfile<DB_NAME>.ora.12.1

SQL> !ls -ltr /work/oracle/<db_name>/pfile

total 48

-rw-r--r-- 1 oracle dba 2173 Nov 04 15:34 init<db_name>.ora

-rw-r----- 1 oracle dba 6656 Nov 09 14:23 spfile<DB_NAME>.ora.12.1

-rw-r--r-- 1 oracle dba 2539 Nov 09 14:41 init<DB_NAME>.preup.ora.ori

-rw-r--r-- 1 oracle dba 1996 Nov 09 14:45 init<DB_NAME>.preup.ora

SQL> create spfile = '/work/oracle/<db_name>/pfile/spfile<DB_NAME>.ora' from pfile ='/work/oracle/<db_name>/pfile/init<DB_NAME>.preup.ora' ;

$ lsnrctl stop LSN_<DB_NAME>

LSNRCTL for IBM/AIX RISC System/6000: Version 12.1.0.2.0 - Production on 04-JUN-2020 17:14:32

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=<DB_NAME>)(PORT=1525)))

The command completed successfully

/work/oracle/<db_name>/pfile $

2.2 Upgrade Prerequisite N03

GO UNDER NEW TARGET ORACLE HOME ENVIRONMENT

Copy Init file / Passwd File / TNS Files to New Oracle 19C Home

$ cd $TNS_ADMIN

LISTENER.ORA :

/oracle/19CDB/network/admin $ cat listener.ora

LSN_<DB_NAME>=

(DESCRIPTION=

(ADDRESS_LIST=

(ADDRESS=(PROTOCOL=tcp)(HOST=<HOST>)(PORT=1621))

(ADDRESS=(PROTOCOL=ipc)(KEY=extproc<db_name>))))

ADR_BASE_LSN_<DB_NAME>=/work/oracle/<db_name>/tnslsnr

/oracle/19CDB/network/admin $ cat tnsnames.ora

<DB_NAME> =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (COMMUNITY = cg13.tcp)(PROTOCOL = TCP)(Host = <DB_NAME>)(Port = 1525))

)

(CONNECT_DATA =

(service_name = <db_name>.cg13.fr)

)

/oracle/19CDB/network/admin $

SQLNET.ORA :

2) Explicitly set SQLNET.ALLOWED_LOGON_VERSION_SERVER in the 19

SQLNET.ORA to a non-Exclusive Mode value, such as "11". (This is a short

term approach and is not recommended because it will retain known

security risks associated with the 10G password version.)

Your database system has at least one account with only the 10G password

version (see the PASSWORD_VERSIONS column of DBA_USERS).

/oracle/19CDB/network/admin $ cat sqlnet.ora

NAMES.DIRECTORY_PATH= (LDAP, TNSNAMES, EZCONNECT)

# To specify a time interval, in minutes, to send a check to verify that client/server connections are active

SQLNET.EXPIRE_TIME=10

# To specify the time, in seconds, for a database server to complete a send operation to clients after establishing a connection.

SQLNET.SEND_TIMEOUT=600

# To specify the time, in seconds, for a client to connect with the database server and provide the necessary authentication information.

# SQLNET.INBOUND_CONNECT_TIMEOUT=60 sec (default)

# DAT Oracle 19C -- allow 10G Clients connexions

SQLNET.ALLOWED_LOGON_VERSION_CLIENT=8

SQLNET.ALLOWED_LOGON_VERSION_SERVER=8

/oracle/19CDB/network/admin $

/oracle $ cp -p /oracle/12CDB/dbs/orapw${ORACLE_SID} $ORACLE_HOME/dbs

/oracle $ lsnrctl start LSN_<DB_NAME>

2.3 Upgrade

Upgrade to 19C

$ cd $ORACLE_HOME/dbs

/oracle/19CDB/dbs $ cat > init<DB_NAME>.ora

SPFILE='/work/oracle/<db_name>/pfile/spfile<DB_NAME>.ora'

<CTRL+D>

<DB_NAME> sr-orapro-1-stb: /oracle/19CDB/dbs $ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 9 15:10:16 2020

Version 19.8.0.0.0

Connected to an idle instance.

SQL> startup upgrade

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 4294921672 bytes

Fixed Size 9063880 bytes

Variable Size 1157627904 bytes

Database Buffers 3103784960 bytes

Redo Buffers 24444928 bytes

Database mounted.

Database opened.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

/oracle $ $ORACLE_HOME/bin/dbupgrade

…

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

Phases [0-107] End Time:[2020_11_09 15:50:12]

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

Grand Total Time: 2323s

LOG FILES: (/oracle/19CDB/cfgtoollogs/<DB_NAME>/upgrade20201109151117/catupgrd*.log)

Upgrade Summary Report Located in:

/oracle/19CDB/cfgtoollogs/<DB_NAME>/upgrade20201109151117/upg_summary.log

Grand Total Upgrade Time: [0d:0h:38m:43s]

<DB_NAME> sr-orapro-1-stb: /oracle/19CDB/dbs $

Startup 19C Instance

/oracle/19CDB/dbs $ sqlplus / as sysdba

SQL*Plus: Release 19.0.0.0.0 - Production on Mon Nov 9 15:59:59 2020

Version 19.8.0.0.0

Connected to an idle instance.

SQL> STARTUP

ORA-32004: obsolete or deprecated parameter(s) specified for RDBMS instance

ORACLE instance started.

Total System Global Area 4294921672 bytes

Fixed Size 9063880 bytes

Variable Size 1241513984 bytes

Database Buffers 3019898880 bytes

Redo Buffers 24444928 bytes

Database mounted.

Database opened.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

2.4 Post-Upgrade actions

· Run Post Upgrade Status Toll

SQL> @?ORACLE_HOME/rdbms/admin/utlusts.sql

Note: While executing utlusts.sql, if "ORA-06502: PL/SQL: numeric or value error: character string buffer too small" is received, execute

$ sqlplus "/as sysdba"

SQL> STARTUP

SQL> @?ORACLE_HOME/rdbms/admin/utlusts.sql TEXT

Upgrade Time Zone & Post-Fixup Script

$ cat > post_upg.TZ.sql

conn / as sysdba

-- Check current settings.

SELECT * FROM v$timezone_file;

SHUTDOWN IMMEDIATE;

STARTUP UPGRADE;

-- Begin upgrade to the latest version.

SET SERVEROUTPUT ON

DECLARE

l_tz_version PLS_INTEGER;

BEGIN

l_tz_version := DBMS_DST.get_latest_timezone_version;

DBMS_OUTPUT.put_line('l_tz_version=' || l_tz_version);

DBMS_DST.begin_upgrade(l_tz_version);

END;

SHUTDOWN IMMEDIATE;

STARTUP;

-- Do the upgrade.

SET SERVEROUTPUT ON

DECLARE

l_failures PLS_INTEGER;

BEGIN

DBMS_DST.upgrade_database(l_failures);

DBMS_OUTPUT.put_line('DBMS_DST.upgrade_database : l_failures=' || l_failures);

DBMS_DST.end_upgrade(l_failures);

DBMS_OUTPUT.put_line('DBMS_DST.end_upgrade : l_failures=' || l_failures);

END;

-- Check new settings.

SELECT * FROM v$timezone_file;

COLUMN property_name FORMAT A30

COLUMN property_value FORMAT A20

SELECT property_name, property_value

FROM database_properties

WHERE property_name LIKE 'DST_%'

ORDER BY property_name;

exit;

< CTRL+D>

/work/oracle/<db_name>/upgrade $ sqlplus /nolog @post_upg.TZ.sql |tee post_upg.TZ.log

…

An upgrade window has been successfully ended.

DBMS_DST.end_upgrade : l_failures=0

PL/SQL procedure successfully completed.

FILENAME VERSION CON_ID

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

timezlrg_32.dat 32 0

PROPERTY_NAME PROPERTY_VALUE

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

DST_PRIMARY_TT_VERSION 32

DST_SECONDARY_TT_VERSION 0

DST_UPGRADE_STATE NONE

Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.8.0.0.0

/oracle/19CDB/dbs $

/work/oracle/<db_name>/upgrade $ sqlplus / as sysdba @/work/oracle/<db_name>/upgrade/postupgrade_fixups.sql

Executing Oracle POST-Upgrade Fixup Script

Auto-Generated by: Oracle Preupgrade Script

Version: 19.0.0.0.0 Build: 1

Generated on: 2020-11-09 14:33:13

For Source Database: <DB_NAME>

Source Database Version: 12.1.0.2.0

For Upgrade to Version: 19.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. dir_symlinks YES None.

13. post_dictionary YES None.

14. post_fixed_objects NO Informational only.

Further action is optional.

· Gather Stats on SYS objects ( Dictionary objects stats )

Non-CDB Oracle Database: Oracle recommends that you use the DBMS_STATS.GATHER_DICTIONARY_STATS procedure to gather these statistics. For example, enter the following SQL statement:

SQL> EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;

CDB: Oracle recommends that you use catcon to gather Data Dictionary statistics across the entire multitenant architecture

To gather dictionary statistics for all PDBs in a container database, use the following syntax

$ORACLE_HOME/perl/bin/perl $ORACLE_HOME/rdbms/admin/catcon.pl -l /tmp -b gatherstats -- --x"exec dbms_stats.gather_dictionary_stats"

Gather statistics on fixed objects after the upgrade and when there is a representative workload on the system using the command:

# 14) Gather fixed object stats.

sqlplus / as sysdba <<EOF

EXECUTE DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

exit;

EOF

PL/SQL procedure successfully completed.

SQL> Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.6.0.0.0

Compile invalid objects

<DB_NAME> sr-oraiqr-1: /work/oracle/<db_name>/upgrade $ cd $ORACLE_HOME/rdbms/admin ; $ORACLE_HOME/perl/bin/perl catcon.pl -n 1 -e -b utlrp -d '''.''' utlrp.sql

catcon::set_log_file_base_path: ALL catcon-related output will be written to [/oracle/19CDB/rdbms/admin/utlrp_catcon_53281188.lst]

catcon::set_log_file_base_path: catcon: See [/oracle/19CDB/rdbms/admin/utlrp*.log] files for output generated by scripts

catcon::set_log_file_base_path: catcon: See [/oracle/19CDB/rdbms/admin/utlrp_*.lst] files for spool files, if any

catcon.pl: completed successfully

<DB_NAME> sr-oraiqr-1: /oracle/19CDB/rdbms/admin $

· Review Invalid Objects After

column owner format a10;

column object_name format a25;

column object_type format a25;

select owner, object_name, object_type from dba_objects where status = 'INVALID' order by 1, 2, 3;

3] Migrate from Oracle 10G to 12C without installing the 10G engine on a new server

Complete Checklist for Manual Upgrades to Oracle Database 12c Release 1 (12.1) (Doc ID 1503653.1)

Change DB server & Upgrade Oracle Version from 10G to 12C

Component	Value	Description
Source
AIX	5.3 / 6.1	OS
Oracle	v 10.2.0.5	SGBDR
Target
AIX	7.2	OS
Oracle	v 12.1.0.2	SGBDR

3.1/ Pre-requisite

3.1.1 Pre-upg scripts on 10G DB

Copy the 12C scripts to the 10G machine.

$ scp -p /oracle/12CDB/rdbms/admin/preupgrd.sql /oracle/12CDB/rdbms/admin/utluppkg.sql DB:/oracle/10GDB/rdbms/admin

$ sqlplus / as sysdba

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

SQL> @/oracle/10GDB/cfgtoollogs/DB/preupgrade/preupgrade_fixups.sql

Take actions according to the results.

ALTER SYSTEM SET RESOURCE_LIMIT=FALSE SCOPE=SPFILE ;

EXECUTE dbms_stats.gather_dictionary_stats;

@$ORACLE_HOME/rdbms/admin/utlrp.sql

3.1.2 Prepare and copy Init file to 12C machine

SQL> create pfile='/tmp/initDB.ora' from SPFILE ;

File created.

$ cp -p /tmp/initDB.ora /tmp/initDB.ora_12C

REMOVE

< DB.__db_cache_size=1291845632

< DB.__java_pool_size=33554432

< DB.__large_pool_size=16777216

< DB.__shared_pool_size=234881024

< DB.__streams_pool_size=16777216

< *._b_tree_bitmap_plans=false

< *.background_dump_dest='/work/oracle/db/bdump'

< *.compatible='10.2.0'

< *.core_dump_dest='/work/oracle/db/cdump'

< *.disk_asynch_io=true

< *.filesystemio_options='SetAll'

< *.sga_target=1610612736

< *.user_dump_dest='/work/oracle/db/udump'

utl_file_dir

audit_trail

optimizer_features_enable

sql_trace

PLSQL_V2_COMPATIBILITY

ADD

audit_trail=db,extended

diagnostic_dest='/work/oracle/db'

compatible='12.1.0'

memory_target=4G

plsql_code_type='NATIVE'

temp_undo_enabled=TRUE

_use_single_log_writer=TRUE

PRE_PAGE_SGA=FALSE

nls_length_semantics=CHAR

OPTIMIZER_ADAPTIVE_FEATURES=FALSE

DEFERRED_SEGMENT_CREATION=FALSE

_optimizer_autostats_job=FALSE

filesystemio_options='SETALL'

disk_asynch_io=TRUE

$ scp /tmp/initDB.ora_12C target_srv:/work/oracle/db/pfile

3.1.3 Take an Incremental backup on Source

$ ./rman_backup_db.ksh DB DSK INCR ONLINE

Find the time of the end of Backup.

Finished backup at APR 04 2018 11:44:04

And make available the Source dumps to the Target Server on same Path.

3.1.4 Create target directories and password file

mkdir -p /work/oracle/db/adump

mkdir -p /work/oracle/db/bdump

mkdir -p /work/oracle/db/cdump

mkdir -p /work/oracle/db/udump

mkdir -p /work/oracle/db/pfile

mkdir -p /work/oracle/db/exp

mkdir -p /work/oracle/db/dpdump

mkdir -p /work/oracle/db/tnsadmin

mkdir -p /work/oracle/db/rman

mkdir -p /rdo1/db/ctl

mkdir -p /rdo2/db/ctl

mkdir -p /work/oracle/db/noarc/

mkdir /work/oracle/db/rman/scr

/oracle/12CDB/dbs $ orapwd file=orapw${ORACLE_SID} password=<psw>

3.2 Upgrade

3.2.1 Start target Instance in NOMOUNT Mode using PFile

DB target_srv: /oracle/12CDB/dbs $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 4 08:37:20 2018

Connected to an idle instance.

SQL> startup nomount pfile=/work/oracle/db/pfile/initDB.ora_12C

3.2.2 Duplicate RMAN

Plug Backup end-date into RMAN Script :

DB target_srv: /work/oracle/db/rman/scr $ cat DB_Duplicate_10G_12C.scr

# RMAN DUPLICATE 10G to 12C

connect auxiliary sys/<psw>vn

connect catalog IRC/<psw>@IRC_PRO

run {

DUPLICATE DATABASE 'DB' TO 'DB'

UNTIL TIME "to_date('2018-04-04 11:44:05','YYYY-MM-DD HH24:MI:SS')"

NOOPEN

NOFILENAMECHECK;

}

Run Restore Duplicate :

$ export NLS_DATE_FORMAT='MON DD YYYY HH24:MI:SS'

$ time rman @DB_Duplicate_10G_12C.scr |tee DB_Duplicate_10G_12C.log

Leaving database unopened, as requested

Cannot remove created server parameter file

Finished Duplicate Db at APR 09 2018 17:18:15

Recovery Manager complete.

3.2.3 Open Target Instance

DB target_srv: $ sqlplus / as sysdba

SQL*Plus: Release 12.1.0.2.0 Production on Wed Apr 4 12:59:07 2018

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> alter database open resetlogs upgrade;

Database altered.

SQL> select name from v$tempfile ;

/data/db/temp2_02.dbf

/data/db/temp2.dbf

alter database tempfile '/data/db/temp2_02.dbf' drop;

alter database tempfile '/data/db/temp2.dbf' drop;

Database altered.

SQL> !rm /data/db/temp2_02.dbf /data/db/temp2.dbf

SQL> alter tablespace TEMP add tempfile '/data/db/temp.dbf' size 24G AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED ;

alter tablespace TEMP add tempfile '/data/db/temp_2.dbf' size 1G AUTOEXTEND ON NEXT 64M MAXSIZE UNLIMITED ;

Tablespace altered.

SQL> truncate Table SYS.AUD$ ;

Table truncated.

3.2.4 UPGRADE

$ cd $ORACLE_HOME/rdbms/admin

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

Total Upgrade Time: 00:49:56

$ sqlplus "/as sysdba"

SQL> STARTUP

SQL> @utlu121s.sql

Run utlrp.sql to recompile any remaining stored PL/SQL and Java code in another session.

SQL> @utlrp.sql

• Upgrade du DST Daylight Savings Time

And post-config

spool upg_tzv_check.log

@upg_tzv_check.sql

spool off

spool upg_tzv_apply.log

@upg_tzv_apply.sql

@?/rdbms/admin/emremove.sql

drop user ORACLE_OCM cascade ;

SQL> ALTER SYSTEM SET RESOURCE_LIMIT=FALSE SCOPE=SPFILE ;

System altered.

exit

4] Move a non CDB 12C Oracle Database from Linux to AIX using 12C FULL TRANSPORTABLE TABLESPACE EXPORT / IMPORT

Component

Source Version

Target Version

Linux CentOS release 6.8

AIX 7.2 TL1 SP3

Oracle

12C R1 12.1.0.2

NON CDB

12C R1 12.1.0.2

NON CDB

Target DB & Listener should be created as a prerequisite.

There is different Endian.

SELECT * FROM V$TRANSPORTABLE_PLATFORM order by 1 ;

PLATFORM_ID PLATFORM_NAME ENDIAN_FORMAT

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

6 AIX-Based Systems (64-bit) Big

11 Linux IA (64-bit) Little

Master Note for Transportable Tablespaces (TTS) -- Common Questions and Issues (Doc ID 1166564.1)

Starting with 12c, you may find the full transportable feature more efficient for moving source databases with RDBMS version 11.2.0.3 and up to 12c.

"Full transportable is more automated than transportable tablespaces because it moves the metadata and user data that resides in non-transportable tablespaces than would previously have been moved in multiple operations.

This makes the full transportable feature useful for efficiently moving a database to a new computer system or upgrading to a new release of Oracle Database.

4.1 Source Environment

Recyclebin Purge

select distinct owner , ts_name from DBA_RECYCLEBIN where owner not in ('SYS') ;

SELECT distinct 'purge tablespace '||ts_name||' user "'||owner||'" ;' FROM DBA_RECYCLEBIN where owner not in ('SYS') and ts_name is not null order by 1 ;

purge tablespace APP1_DAT user "APP1"

…

purge tablespace APPN_DAT user "APPN" ;

Shutdown LSNR

On the source database, place all the user-defined tablespaces into read-only mode.

set serveroutput on

DECLARE

tablespace_is_already_ro EXCEPTION;

PRAGMA EXCEPTION_INIT(tablespace_is_already_ro, -1644);

tablespace_is_not_read_only EXCEPTION;

PRAGMA EXCEPTION_INIT(tablespace_is_not_read_only, -1646);

begin

for i in ( select distinct tablespace_name from dba_tablespaces where tablespace_name not in ('SYSTEM','SYSAUX','UNDOTBS1','TEMP') )

loop

BEGIN

EXECUTE IMMEDIATE 'alter tablespace '||i.tablespace_name||' READ &action ' ;

-- if errors, we don't want to stop, if a tablespace is already READ ONLY, no big deal

-- ORA-01644: tablespace 'SCH_DAT' is already read only

EXCEPTION WHEN tablespace_is_already_ro THEN NULL;

WHEN tablespace_is_not_read_only THEN NULL;

END;

end loop;

end;

ARG : ONLY

On the source database host, execute Data Pump Export and perform a full transportable export.

To perform a full transportable export, Specify the parameters FULL=YES and TRANSPORTABLE=ALWAYS.

$ time expdp system full=y transportable=always version=12 directory=DB12C_DP_DIR dumpfile=DB12C_full_tts_180116.dmp metrics=y exclude=statistics logfile=DB12C_full_tts_export_180116.log

Export: Release 12.1.0.2.0 - Production on Tue Jan 16 17:26:53 2018

…

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

Dump file set for SYSTEM.SYS_EXPORT_FULL_01 is:

/DPUMP_IQR/oracle/DB12C/DB12C_full_tts_180116.dmp

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

Datafiles required for transportable tablespace ADB_DAT:

/data/db12c/app1_dat_01.dbf

…

Datafiles required for transportable tablespace XGE_DAT:

/data/db12c/appn_dat.dbf

Job "SYSTEM"."SYS_EXPORT_FULL_01" completed with 348 error(s) at Tue Jan 16 17:36:11 2018 elapsed 0 00:09:10

real 9m20.115s

Transport the tablespace data files and the export dump file from source to target

You can load and convert the data files by using the RMAN CONVERT command, or the PUT_FILE procedure in the DBMS_FILE_TRANSFER package.

List Datafiles of TS :

SQL> set pages 5000 lines 150

SQL> select distinct file_name from dba_data_files a where

exists ( select '1' from dba_tablespaces b where a.tablespace_name=b.tablespace_name and tablespace_name not in ('SYSAUX','SYSTEM','UNDOTBS1','TEMP') )

order by 1 ;

FILE_NAME

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

/data/db12c/app1_dat.dbf

…

/data/db12c/appn_dat.dbf

Create TNS Alias to connect to Target ( DB12CNEW )

DB12CNEW =

(DESCRIPTION =

(ADDRESS_LIST =

(ADDRESS = (COMMUNITY = cg13.tcp)(PROTOCOL = TCP)(Host = <NEWHOST>)(Port = 1524))

)

(CONNECT_DATA =

(service_name = db12c.cg13.fr)

)

Run shell : DBMS_FILE_TRANSFER.PUT_FILE does Endian CONVERT

$ cat li_dbf_src.txt

/data/db12c/app1_dat.dbf

/data/db12c/appn_dat.dbf

$ cat Trans_TTS.sh

#!/bin/ksh

usr_sql_dir=/<path>/scripts/datapump

# A MODIFIER

BDs=DB12C ; system_psw_bds=cos91TGHoe

BDc=DB12CNEW ; system_psw_bdc=cos91TGHoe

# FIN A MODIFIER

ora_cmd="CREATE DATABASE LINK ${BDc}_DBL CONNECT TO system IDENTIFIED BY ${system_psw_bdc} USING '${BDc}';"

sqlplus -s system/${system_psw_bds}@${BDs} <<!

${ora_cmd}

for i in $( cat li_dbf_src.txt ) ; do

dir_bd=${i%/*} ; File_name=${i##*/}

ora_cmd="CREATE DIRECTORY DIR_SRC AS '${dir_bd}';"

sqlplus -s system/${system_psw_bds}@${BDs} <<!

${ora_cmd}

ora_cmd="CREATE DIRECTORY DIR_TRG AS '${dir_bd}';"

sqlplus -s system/${system_psw_bdc}@${BDc} <<!

${ora_cmd}

print "BEGIN" > ${usr_sql_dir}/DBMS_FILE_TRANSFER.sql

print "DBMS_FILE_TRANSFER.PUT_FILE(" >> ${usr_sql_dir}/DBMS_FILE_TRANSFER.sql

print "source_directory_object => 'DIR_SRC'," >> ${usr_sql_dir}/DBMS_FILE_TRANSFER.sql

print "source_file_name => '${File_name}'," >> ${usr_sql_dir}/DBMS_FILE_TRANSFER.sql

print "destination_directory_object => 'DIR_TRG'," >> ${usr_sql_dir}/DBMS_FILE_TRANSFER.sql

print "destination_file_name => '${File_name}'," >> ${usr_sql_dir}/DBMS_FILE_TRANSFER.sql

print "destination_database => '${BDc}_DBL');" >> ${usr_sql_dir}/DBMS_FILE_TRANSFER.sql

print "END;" >> ${usr_sql_dir}/DBMS_FILE_TRANSFER.sql

print "/" >> ${usr_sql_dir}/DBMS_FILE_TRANSFER.sql

print "exit" >> ${usr_sql_dir}/DBMS_FILE_TRANSFER.sql

print "Xfer de ${dir_bd}/${File_name} : ${BDs} vers ${BDc} **"

sqlplus -s system/${system_psw_bds}@${BDs} @${usr_sql_dir}/DBMS_FILE_TRANSFER.sql

ora_cmd="DROP DIRECTORY DIR_TRG ;"

sqlplus -s system/${system_psw_bdc}@${BDc} <<!

${ora_cmd}

ora_cmd="DROP DIRECTORY DIR_SRC ;"

sqlplus -s system/${system_psw_bds}@${BDs} <<!

${ora_cmd}

done

Run shell

$./Trans_TTS.sh

Transfer SQL Example.

$ cat DBMS_FILE_TRANSFER.sql

BEGIN

DBMS_FILE_TRANSFER.PUT_FILE(

source_directory_object => 'DIR_SRC',

source_file_name => 'appn_dat.dbf',

destination_directory_object => 'DIR_TRG',

destination_file_name => 'appn_dat.dbf',

destination_database => 'DB12CNEW_DBL');

END;

exit

4.2 Target Environment

Invoke full transportable import on the destination database

SQL> CREATE DIRECTORY DB12C_DP_DIR AS '/DPUMP_IQR/oracle/DB12C' ;

Directory created.

Get DBF list on source db.

-- get dbf from source

select distinct ' '''||file_name||''',' from dba_data_files a where

exists ( select '1' from dba_tablespaces b where a.tablespace_name=b.tablespace_name and tablespace_name not in ('SYSAUX','SYSTEM','UNDOTBS1','TEMP') )

order by 1

$ time impdp system directory=DB12C_DP_DIR dumpfile=DB12C_full_tts_180116.dmp logfile=DB12C_i_full_tts__180116.log metrics=y \

transport_datafiles='/data/db12c/app1_dat_01.dbf',\

…

'/data/db12c/appn_dat.dbf'

OraCsdP

Categories

mercredi 11 novembre 2020

Oracle Migration Guide ( Upgrade , RU Patch )