Migrating and upgrading an Oracle Database from 12c to 19c across different platforms (e.g., Windows to Linux, or different Unix flavors) requires a migration strategy that can handle both the software version jump and the cross-platform data conversion
- Oracle Data Pump (Export/Import):
- Best For: Smaller to medium-sized databases or scenarios where significant downtime is acceptable.
- Process: Use
expdpon the source 12c database to export schemas, users, and roles, thenimpdpon a pre-created 19c database on the target platform. - Pros: Handles different hardware, encryption, and character sets automatically.
- Best For: Large databases where copying datafiles is faster than logical exports.
- Process: Set source tablespaces to Read Only, copy datafiles to the target platform, and use Data Pump to move metadata.
- Cross-Platform Requirement: If the source and target have different endian formats, you must use RMAN or the
DBMS_FILE_TRANSFERpackage to convert the datafiles. - Full Transportable Export/Import: A streamlined version of TTS that can migrate an entire database (including system metadata) in one operation.
- Best For: Minimizing downtime for very large databases.
- Process: Uses a combination of RMAN incremental backups and Transportable Tablespaces to keep the target 19c database nearly synchronized with the source 12c database until the final cutover.
- Best For: Zero or near-zero downtime migrations.
- Process: Configure an "Extract" process on the 12c source and a "Replicat" on the 19c target. An initial load is performed (typically via Data Pump), and GoldenGate keeps them in sync until cutover
V4 Cross-Platform Incremental Backup (XTTS) is an Oracle database migration method that allows you to move large databases across different hardware architectures (e.g., from AIX to Linux) with minimal downtime. It uses Recovery Manager (RMAN) to capture block changes while the source system remains onlin
- Setup: The target database environment is provisioned with identical character sets, time zones, and database versions. You then download the V4 Perl scripts (from My Oracle Support Doc ID 2471245.1).
- Prepare & Level 0: A full backup of the source tablespaces is taken, converted to the target platform's "endian" format (if necessary), and transferred to the target.
- Roll Forward: RMAN creates incremental backups of the source database. These incremental changes are shipped and applied to the target to keep it synchronized with the source.
- Final Cutover: The source database is placed in read-only mode, the final incremental backup is applied, and the metadata is moved using Oracle Data Pump
- Near-Zero Downtime: Because most of the data is synchronized in the background via incremental backups, the actual cutover window is extremely brief.
- Endian Conversion: Handles cross-platform "endianness" natively (e.g., big-endian to little-endian).
- Database Upgrades: Can be used concurrently to upgrade database versions (e.g., from 12c to 19c) during the migration
Reduced Downtime Cross Platform Migration Of Oracle
19c Database From AIX To Linux Using Transportable
Tablespace
This procedure
covers the steps needed to use Oracle V4 Cross Platform Transportable
Tablespaces (XTTS) with RMAN incremental backups to migrate data between
systems that have different endian formats, with the least amount of
application downtime.
The first step will
be to copy a full backup from the source to the destination. Then, by using a
series of incremental backups, each smaller than the last, the data at the
destination system can be brought nearly current with the source system, before
any downtime is required. This procedure requires downtime only during the
final incremental backup, and the meta-data export/import.
Prerequisites
The following
prerequisites should be in place to ensure the success of the database
migration.
·
The current version does NOT support Windows as either source or
destination.
·
The source database must be in ARCHIVELOG mode.
·
Before running XTTs scripts, set
NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1. Other NLS_LANG settings may cause
errors.
·
RMAN on the source system must not have DEVICE TYPE
DISK configured with COMPRESSED.
·
RMAN on the source system must not have a BACKUP TYPE TO
COPY. The source must have a BACKUP TYPE TO BACKUPSET.
·
RMAN on the source system must not have a default channel
configured to type SBT. I.e., this procedure can only be used with
DISK channels.
·
RMAN on the source system must not have ANY
channel configuration limitations. For example, MAXSETSIZE, MAXPIECESIZE,
etc.
·
The set of tablespaces being moved must all be online and
contain no offline data files. Tablespaces must be READ WRITE.
·
The Oracle version of the source must be lower or equal to the
destination. Therefore, this procedure can be used as an upgraded
method. Transportable tablespace restrictions WILL apply.
·
The minimum version for source and destination is
11.2.0.3.
·
ASM can only be used for the final location of datafiles in the
destination, backups cannot be placed on ASM with this version.
·
The backup location of the destination MUST be a device with
read/write privileges. I.e., cannot be a READONLY device. This can
cause ORA-19624 on the backup set conversion.
·
The source and target database must use a compatible character
set and national character set.
·
Source and target character sets must match.
SQL> select * from nls_database_parameters
·
Source and target time zone versions must match.
SQL> select dbtimezone from v$instance;
·
The source database’s COMPATIBLE parameter must not be greater
than the destination database’s COMPATIBLE parameter. Execute the below queries
to validate the version and compatible parameters on the source and target.
SQL> select version from v$instance; SQL> show parameter compatible
For example, if the
source database compatible parameter is 12.0.0 and the target is 19.0.0, then
execute the following on the source database.
SQL> ALTER SYSTEM SET COMPATIBLE= '19.0.0.0' SCOPE=SPFILE;restart database;
·
Tablespace endianness is converted with the xttdriver.pl script
from Oracle support.
·
Tablespaces on the source database may not be encrypted with
Transparent Data Encryption
·
Check the endianness of both source and target
SQL> SELECT d.PLATFORM_NAME, ENDIAN_FORMATFROM V$TRANSPORTABLE_PLATFORM tp, V$DATABASE dWHERE tp.PLATFORM_ID = d.PLATFORM_ID;
Phase 1 – Initial Setup phase
Perform the
following steps to configure the environment to use Cross Platform Incremental
Backups:
1.
Install the desired Oracle
Database software on the destination system that will run the destination
database.
2.
Create a database on the
destination system to transport the tablespace(s) into and create the schema
users required for the tablespace transport.
3.
The list of users/schemas and
tablespaces to be migrated which are not maintained by Oracle should be created
on the destination database. The list can be found using the script below.
SQL> select username, default_tablespace from dba_users where oracle_maintained=’N’;
4.
Remove all users maintained by
oracle. Compare output the output from the below query
SQL> select username from dba_users where oracle_maintained=’Y’ ;UNDO tablespace is not migrated in this procedure.
5.
Verify Tablespaces to be
migrated are self-contained.
SQL> select 'EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('||distinct default_tablespace||', TRUE);' from dba_users where oracle_maintained=’N’;
Below is a sample
script that will be generated above.
EXECUTE DBMS_TTS.TRANSPORT_SET_CHECK('USERS', TRUE);SELECT * FROM TRANSPORT_SET_VIOLATIONS;
6.
It is recommended to check the
count of objects on the source database before migration.
SQL> select object_type, count (*) from dba_objects where owner not in ('SYS','SYSTEM') group by object_type order by 1;SQL> select segment_type,round(sum(bytes)/1024/1024) as SIZE_MB from dba_segments where owner not in ('SYS','SYSTEM') group by segment_type order by 2 desc;
7.
Create a new tablespace and
drop the USERS tablespace on the target database.
SQL> create tablespace migtab datafile '+DATADG' size 2G;SQL> alter database default tablespace migtab;SQL> drop tablespace users including contents and datafiles;
8.
Install xttconvert scripts on
the source system
On the source
system, as the oracle software owner, download and extract
rman_xttconvert_VER4.3.zip file containing the migration scripts
from Metalink document ID 2471245.1
Copy the zip file to
the source server and unzip
[oracle@source]$ pwd/home/oracle/xtt [oracle@source]$ unzip rman_xttconvert_VER4.zip Archive: rman_xttconvert_v3.zip inflating: xtt.properties inflating: xttcnvrtbkupdest.sql inflating: xttdbopen.sql inflating: xttdriver.pl inflating: xttprep.tmpl extracting: xttstartupnomount.sql
9.
Configure xtt.properties file
on the source system. Edit the xtt.properties file and set the following
parameters.
[oracle@source]$ cd /home/oracle/xtts[oracle@source]$ vi xtt.properties tablespaces=TABLESPACES TO BE MIGRATEDplatformid=6dest_datafile_location=+DATADGsrc_scratch_location=/rman/backupdest_scratch_location=/rman/restoreparallel=8usermantransport=1
·
On source: Location of backups as defined by
src_scratch_location parameter in the xtt.properties file.
·
On destination: Location of backups as defined by the dest_scratch_location parameter
in the xtt.properties file.
·
Location for datafiles on the destination is defined by
dest_datafile_location parameter in the xtt.properties file. For the
destination database using ASM, specify the ASM disk group.
·
The source platform ID can be generated with the below command.
·
For databases that are 12c and upwards, it is recommended set
usermantransport parameter to 1.
SQL> select plaftform_id from v$database;
·
It is recommended this be set
if the source database is running 12c or higher. This
causes new 12c (and higher) functionality to be used when this parameter is
set.
10.
Copy xttconvert scripts and
xtt.properties to the destination system
As the oracle
software owner copy all xttconvert scripts and the modified xtt.properties file
to the destination system.
[oracle@source]$ scp -r /home/oracle/xtt oracle@dest:/home/oracle/xtt
11.
Set TMPDIR environment variable
In the shell
environment on both source and destination systems, set the environment
variable TMPDIR to the location where the supporting scripts exist. Use
this shell to run the Perl script xttdriver.pl as shown in the steps below.
If TMPDIR is not set, output files are created and input files are
expected to be in /tmp.
On source
[oracle@source]$ export TMPDIR=/home/oracle/xtt
On destination
[oracle@dest]$ export TMPDIR=/home/oracle/xtt
Phase 2 – Prepare Phase
12.
Run the backup on the source
system.
During the Prepare
phase, datafiles of the tablespaces to be transported are backed up on source, backups
transferred to the destination system and restored by the xttdriver.pl
script.
[oracle@source]$ export ORACLE_SID=source DB SID[oracle@source]$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1[oracle@source]$ export TMPDIR=/home/oracle/xtt[oracle@source]$ nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup --debug 3 &
13.
Transfer the following files to
the destination system.
·
If the src_scratch_location on the source system and the
dest_scratch_location on the destination system refer to the same NFS storage
location, then the backups do not need to be copied as they are available in
the expected location on the destination system.
·
Backups created from source src_scratch_location to
destination dest_scratch_location
·
The res.txt file from source $TMPDIR to destination $TMPDIR:
In the example
below, scp is used to transfer the level=0 backup created by the
previous step from the source system to the destination system.
[oracle@source]$ scp /src_scratch_location/* oracle@dest:/dest_scratch[oracle@source]$ scp res.txt oracle@dest:/home/oracle/xtt
14.
Restore the datafiles on the
destination
On the destination
system, logged in as the oracle user with the environment (ORACLE_HOME and
ORACLE_SID environment variables) pointing to the destination database,
run the restore as follows:
[oracle@source]$ cd /home/oracle/xtts[oracle@source]$ export ORACLE_SID=TARGETDBID[oracle@source]$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1[oracle@source]$ export TMPDIR=/home/oracle/xtts[oracle@source]$ nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore --debug 3 &
Data files will be placed on the destination system in the
defined dest_datafile_location.
From asmcmd confirm data files have been restored.
You can at this
point proceed to Phase 4 or continue with Phase 3
Phase 3 – Roll Forward Phase
During
this phase, an incremental backup is created from the source database,
transferred to the destination system, converted to the destination system
endian format, then applied to the converted destination datafile copies to
roll them forward. This phase may be run multiple times. Each successive
incremental backup should take less time than the prior incremental backup and
will bring the destination datafile copies more current with the source
database. The data being transported (source) is fully accessible during
this phase.
15.
Create an incremental backup of
the tablespaces being transported on the source system. This step will create
an incremental backup for all tablespaces listed in
xtt.properties.
[oracle@source]$ export ORACLE_SID=source DB SID[oracle@source]$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1[oracle@source]$ export TMPDIR=/home/oracle/xtt[oracle@source]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup --debug 3
16.
Transfer the incremental
backup(s) (between src_scratch_location and dest_scratch_location) and the
res.txt (between the $TMPDIRs) from the source to the destination. The
list of incremental backup files from the current backup can be found in the
incrbackups.txt file on the source system.
If the
src_scratch_location on the source system and the dest_scratch_location on the
destination system refer to the same NFS storage location, then the backups do
not need to be copied as they are available in the expected location on the
destination system. However, the res.txt file MUST be copied after the
LAST incremental backup before it can be applied to destination
[oracle@source]$ scp `cat incrbackups.txt` oracle@dest:/dest_scratch_location[oracle@source]$ scp res.txt oracle@dest:/home/oracle/xtt
17.
Apply the incremental backup to
the datafile copies on the destination system
[oracle@source]$ cd /home/oracle/xtts[oracle@source]$ export ORACLE_SID=TARGETDBID[oracle@source]$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1[oracle@source]$ export TMPDIR=/home/oracle/xtts[oracle@source]$ nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl --restore --debug 3 &
Phase 4 – Final Incremental Backup with
downtime from the source
During this phase,
the source data is made READ ONLY and the destination data files are made
consistent with the source database by creating and applying a final
incremental backup. After the destination data files are made consistent, the
normal transportable tablespace steps are performed to export object metadata
from the source database and import it into the destination database. The
data being transported is accessible only in READ ONLY mode until the end of
this phase. Place the tablespaces in read-only mode in the source
database.
You can generate the
script using the below script for the selected tablespaces.
select 'ALTER TABLESPACE '||tablespace_name||' READ ONLY; ' from dba_tablespaces where tablespace_name in (‘Input the list of tablespaces to be migrated)
18. Option 1
Take the incremental
backup on the source system with the new 12c feature, which also runs the
export. The –bkexport option is used with the xttdriver.pl script
[oracle@source]$ cd /home/oracle/xtts[oracle@source]$ export ORACLE_SID=TARGETDBID[oracle@source]$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1[oracle@source]$ export TMPDIR=/home/oracle/xtts[oracle@source]$ nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl --bkpexport &
You might see an
error related to tablespace in read-only mode.
Transfer the final
incremental backup and export the file to the destination.
Apply the last
incremental backup to destination datafiles
The final
incremental backup must be applied to the destination data files using
“–resincrdmp”
oracle@source]$ cd /home/oracle/xtts[oracle@source]$ export ORACLE_SID=TARGETDBID[oracle@source]$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1[oracle@source]$ export TMPDIR=/home/oracle/xtts[oracle@dest]$ nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl –resincrdmp &
This step will apply
the last incremental backup to the data files. In addition, it will restore the
export dump file from the backup set and generate a script file, xttplugin.txt.
Option 2
Take the incremental
backup on the source system with -the backup and -restore options.
[oracle@source]$ cd /home/oracle/xtts[oracle@source]$ export ORACLE_SID=TARGETDBID[oracle@source]$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1[oracle@source]$ export TMPDIR=/home/oracle/xtts[oracle@source]$ nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl --backup &
You might see an
error related to tablespace in read-only mode.
Transfer the final
incremental backup and export the file to the destination.
Apply the last
incremental backup to destination datafiles
oracle@source]$ cd /home/oracle/xtts[oracle@source]$ export ORACLE_SID=TARGETDBID[oracle@source]$ export NLS_LANG=AMERICAN_AMERICA.WE8ISO8859P1[oracle@source]$ export TMPDIR=/home/oracle/xtts[oracle@dest]$ nohup $ORACLE_HOME/perl/bin/perl xttdriver.pl –restore &
Phase 5 – Transport Phase: Export
Metadata and Plug-in Tablespaces into Destination Database
19.
Run metadata export and import
using the export file
Create a directory
object in the source database
create directory migdir as '/ rman/backup’; vi exp_testmigration.par dumpfile=EXP_SOURCE_MIG _09Mar23.dmpdirectory=migdirexclude=statisticstablespaces=USERStransport_full_check=yeslogfile=EXP_ SOURCE_MIG _09Mar23.logmetrics=y [oracle@source]$ nohup expdp system/manager parfile= exp_testmigration.par &
Transfer the dump
files to the target server
20.
Import the metadata on the
target and plug the datafiles
sqlplus / as sysdba SQL> create a directory object on the destination databasecreate directory impdir as '/rman/restore'; SQL> grant read,write on directory impdir TO system;
Generate the list of
tablespaces and data files to be migrated using the xttdriver.pl script with -e
option
[oracle@source]$ $ORACLE_HOME/perl/bin/perl xttdriver.pl -e
This will generate a
sample Data Pump network_link transportable import command in the file
xttplugin.txt with the transportable tablespaces parameters
TRANSPORT_TABLESPACES and TRANSPORT_DATAFILES correctly set. In addition, a
data pump export file will also be created.
Edit the import par
file below and replace the tablespaces and data files to be transported.
[oracle@source]$ vi impmigration.par dumpfile= EXP_SOURCE_MIG _09Mar23.dmpdirectory=cmbdb_imp_dirtransport_datafiles='/dest_datafile_location/TS1.dbf','/dest_datafile_location/TS2.dbf'logfile=IMP_SOURCE_MIG _09Mar23.logfull=y [oracle@dest]$nohup impdp system/oracle parfile= impmigration.par &
There is the option
to run the import across the network using a public database link.
Phase 6 Validate the Transported
Data
Check the
destination database for objects, pl/sql objects, sequences, views, etc., that
are not transported by TTS.
SQL> select segment_type,round(sum(bytes)/1024/1024) as SIZE_MB from dba_segments where owner not in ('SYS','SYSTEM') group by segment_type order by 2 desc;
21.
Check tablespaces on target for
corruption
select 'VALIDATE TABLESPACE '||tablespace_name||' CHECK LOGICAL; ' from dba_tablespaces;
22.
Make destination tablespaces
read/write
Connect to the
destination database and execute the below command to generate the script to
alter the tablespaces.
select 'ALTER TABLESPACE '||distinct default_tablespace||' READ WRITE; ' from dba_users where oracle_maintained=’N’;
Compare the output
with a list of databases in dba_tablepaces and be sure to include all
tablespaces that have been migrated.
or
Cross platform Oracle Database Migration and upgradation from 12c to 19c on New Build Server
This note describes the procedure used to perform a minimal downtime platform migration from existing HP-UX to Linux-Red Hat Enterprise Linux 8 (New Build Server) as well as a database upgrade from Oracle 12c to Oracle 19c .
Database (12.1.0.2)
Migration(Cross Platform) from
HPUX to RHEL -Pre requisite
Pre-Requisite Phase-1
Red Hat Enterprise Linux 8 (Red Hat Enterprise Linux 8: 4.18.0-80.el8.x86_64 or later)
SSH Requirement (OpenSSH is installed on servers)
Check Kernel parameters using cat /etc/sysctl.conf
fs.file-max = 6815744
kernel.sem = 250 32000 100 128
kernel.shmmni = 4096
kernel.shmall = 1073741824
kernel.shmmax = 4398046511104
net.core.rmem_default = 262144
net.core.rmem_max = 4194304
net.core.wmem_default = 262144
net.core.wmem_max = 1048576
fs.aio-max-nr = 1048576
net.ipv4.ip_local_port_range = 9000 65500 #/sbin/sysctl -p (to apply above parameter)
vi /etc/security/limits.conf
oracle soft nofile 1024
oracle hard nofile 65536
oracle soft nproc 2047
oracle hard nproc 16384
oracle soft stack 10240
oracle hard stack 32768
install Linux packages which are required for ORACLE installation
bc,binutils,elfutils-libelf,elfutils-libelf-devel
,fontconfig-devel,glibc,glibc-devel,ksh,libaio
,libaio-devel,libXrender,libX11,libXau,libXi,libXtst,libgcc,libnsl
,librdmacm,libstdc++,libstdc++-devel,libxcb,libibverbs,make,policycoreutils
,policycoreutils-python-utils,smartmontools,sysstat
User and group
groupadd
-g 1001 oinstall
groupadd -g 1002 dba
groupadd -g 1003 oper
useradd -u 1001 -g oinstall -G dba,oper oracle
set Oracle user password=passwd oracle
Set SELinux = permissive and Disable firewall
vi /etc/selinux/config
SELINUX=permissive
# systemctl disable firewalld
Mount point for Oracle Binaries 12.1.0.2 and 19c /oracle02 100G
Mount point for Datafile /oradata 3.3 TB, /femarch_12c 200G , /femredo_12c 30G , /backup 800G
Create Oracle Home Directory,Datafile and grant Permission
mkdir -p /u01/app/oracle/product/12.1.0/dbhome_1
chown -R oracle:oinstall /oracle02
chmod -R 775 /oracle02
chown -R oracle:oinstall /oradata
chmod -R 775 /oradata
Set Profile for Oracle setup
download Oracle Binaries for 12.1.0.2 (Linux 64 bit) from edelivery.oracle.com
Copy Binary at RHEL mount point /u01/database
Install Oracle Binaries using Oracle Installer
Apply Latest availabe patches Patch 34057742 jul-22
Cross platform
migration from HP-UX (Oracle 11.2.0.4) to Linux(11.2.0.4)
Migration Process
1) provision DB instance
2) backup all TS on source ,transfer and restore
Prerequisite are same as for XTTS method
§ The source database
must have its COMPATIBLE parameter set to 10.2.0 or higher.
§ The source database’s
COMPATIBLE parameter must not be greater than the destination database’s
COMPATIBLE parameter.
§ The source database
must be in ARCHIVELOG mode.
§ RMAN on the source
system must not have DEVICE TYPE DISK configured with COMPRESSED.
§ The set of
tablespaces being moved must all be online, and contain no offline data files.
Tablespaces must be READ WRITE.
Step 1 -> Self Contains
Checks (Source DB online)
Check
the self-containing property of all the tablespace on source and it should’nt
have any violations.
Exec sys.dbms_tts.transport_set_check(‘STAGING’, TRUE);
EXEC sys.dbms_tts.transport_set_check('IX_CRM_REP_PHN_MISC,CRM_REP_DEMO_TRD_MEM,IX_CRM_REP_DEMO_TRD_MEM,CRM_REP_PSY_GRP_CI,IX_CRM_REP_PSY_GRP_CI,CRM_LOG_TBL,IX_CRM_LOG_TBL,CRM_SUMM_TBL,IX_CRM_SUMM_TBL,CRM_SUS_TBL',TRUE);
select * from sys.transport_set_violations;
Step 2 -> Check Endian
format (Source DB online)
Check
the platform details in our case it’s different endian that we need to do extra
conversion step later.
COLUMN
PLATFORM_NAME FORMAT A32
select * from v$transportable_platform order
by platform_id;
select
tablespace_name, status from dba_tablespaces;
Step 3 -> Install the destination databases software and create a blank database (Source DB online)
# Install Software
binaries with same components as in source DB.
#
Create a blank database using same configuration as source :
$
INIT parameters
$
UNDO, SYSTEM,TEMP and SYSAUX tablespace size
$
Character set and National Character set.
$
Change Maxlogfiles, maxlogmembers and the important one is MAXDATAFILES
#
Create manually or exp/imp non-default users in destination
database same as in source db.
ON
Source :
expdp userid=\’/ as sysdba\’ directory=TTS LOGFILE=user_imp_RMSP.log FULL=y
INCLUDE=USER,ROLE,ROLE_GRANT,PROFILE dumpfile=USER_ROLES_GRANT.dmp CONTENT=METADATA_ONLY
On Target
ON
Source :
select distinct default tablespace from dba_users ;
Create above all tablespaces
in destination database with just a single 1 mb file and drop after
imp or creation of users.
impdp userid=\’/ as sysdba\’ directory=TTS dumpfile=USER_ROLES_GRANT.dmp
/******** AS SYSDBA directory=tts
dumpfile=USER_ROLES_GRANT_cbs.dmp
1)
Create user, password verification fucntion,profile
and default tablespace and temp tablespace as part of pre-requisite before user import metadata on target
After
user metadata import , drop tablespace which was created as part pre-requisite
NOTE :- Before creating users, we need to create a dummy default tablespace in destination and drop after creating user, so later we will not face any kind of issue.
Step 4 -> Take RMAN Image copy of source database. (Source DB online)
Take
the RMAN image copy backup using below script, make necessary changes as per
the need. We don’t need SYSTEM, SYSAUX and UNDO but we can’t exclude
system and sysaux in full backup so due to huge size of UNDO in our case we are
excluding it and for system and sysaux we will not use while plugin the
tablespaces in destination database.
export
ORACLE_SID=SBMNEW
export ORACLE_HOME=/software/app/oracle/product/rdbms/11gR2
oracle@CBTDA201:{/backup/mig4may/}cat
RMAN_MIG04may.sh
#!/bin/ksh
rman log=/backup/mig4may/RMANBACKUP_$(date
+"%Y%m%d-%H%M%S").log<<EOF
connect target /
RUN {
set nocfau;
CONFIGURE EXCLUDE FOR TABLESPACE UNDOTBS1;
CONFIGURE DEVICE TYPE DISK PARALLELISM 15
BACKUP TYPE TO copy;
BACKUP INCREMENTAL LEVEL 0 as copy DATABASE
TAG 'FULL_SBMNEW' FORMAT '/backup/mig4may/%N_%f.xtf';
}
exit
EOF
oracle@CBTDA201:{/backup/mig4may/}
NOTE :- Make changes in undo tablespace name
if it’s different and also increase/decrease channels as per the server
capacity.
Copy RMAN backup to Target Server
nohup scp oracle@CBTDA201:/backup/mig/* .
nohup scp oracle@CBTDA201:/backup/mig/*.xts
. > nohup.out 2>&1
Step 5 > Apply incremental
on image copy backup taken in step 4. (Source DB online)
This
step is a repetitive until you have decided to do final cutover for migration.
rman
target / <>$LOG
RUN
{
set
nocfau;
CONFIGURE
DEVICE TYPE DISK PARALLELISM 25 BACKUP TYPE TO backupset;
BACKUP
INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘FULL_TEST’ DATABASE FORMAT
‘/backup/MIGRATION_TEST/BKP/%U’;
RECOVER
COPY OF DATABASE WITH TAG ‘FULL_TEST’;
}
NOTE :- TAG is important here and same as in
step 4.
Step 6 > Once final cutover
time is decided, we need to apply final INC on image copy. (Source
DB offline, downtime start)
6.1 > Put All the
permanent tablespaces in read only.
select
tablespace_name from dba_tablespaces
where contents = 'PERMANENT' and tablespace_name not in('SYSTEM','SYSAUX');
alter
tablespace tablespace_name read only;
6.2 > Apply Final
incremental on image copy :
rman
target / <>$LOG
RUN
{
set
nocfau;
CONFIGURE
DEVICE TYPE DISK PARALLELISM 25 BACKUP TYPE TO backupset;
BACKUP
INCREMENTAL LEVEL 1 FOR RECOVER OF COPY WITH TAG ‘FULL_TEST’ DATABASE FORMAT
‘/backup/MIGRATION_TEST/BKP/%U’;
RECOVER
COPY OF DATABASE WITH TAG ‘FULL_TEST’;
}
6.3 > Copy image copy to
Destination server or NFS mount the mount point.
Step 7 > Export the
tablespaces metadata using below expdp. . (Source DB offline)
On Source :
expdp userid=\’/ as sysdba\’ directory=TTS
parfile=exp.par dumpfile=TRANSPORT_META_FINAL.dmp logfile= TRANSPORT_META_FINAL.log
exp.par :
transport_tablespaces=USERS,ABC1,ABC2
-> All tablespaces name you want to transport except SYSTEM,SYSAUX,
UNDO and TEMP.
eg
[orasbm@CBSDB201 exp]$ exp1.par
transport_tablespaces=
,TOOLS
,FDM_TBLSPC
,SSO_MASTER
,IDX_SSO_MASTER
,FI_GENERAL
,FI_DEFAULT
,ACCT_DETAILS_1
,ACCT_DETAILS_2
,ACCT_DETAILS_3
,ACCT_MASTER
,ACCT_TRANS
Step 8 > Restoration
/Conversion of image backup. (Source DB offline)
While
expdp is running for step 6.4, we can parallel restore/convert image copy on
destination server once image backup scp/ftp completed.
Image
backup destination is: – /backup/MIGRATION_TEST/BKP/
Converted
Datafile location is: –
/u01/oradata/data/
rman
target /
RUN
{
set
nocfau;
convert
from platform ‘AIX-Based Systems (64-bit)’ datafile
‘/backup/MIGRATION_TEST/BKP/USERS_23.xtf’,
‘/backup/MIGRATION_TEST/BKP/USERS_41.xtf’,
‘/backup/MIGRATION_TEST/BKP/USERS_4.xtf’,
‘/backup/MIGRATION_TEST/BKP/USERS_38.xtf’,
‘/backup/MIGRATION_TEST/BKP/USERS_43.xtf’,
‘/backup/MIGRATION_TEST/BKP/USERS_42.xtf’,
‘/backup/MIGRATION_TEST/BKP/USERS_40.xtf’,
‘/backup/MIGRATION_TEST/BKP/XREF_28.xtf’
format
‘/u01/oradata/data/%N_%f.Dbf’ parallelism 60;
}
eg
[orasbm@CBSDB201
backup]$ cat rman_05may.sh
#!/bin/ksh
rman
log=/backup/RMAN_restore_$(date +"%Y%m%d-%H%M%S").log<<EOF
connect
target /
RUN
{
set
nocfau;
convert
from platform 'HP-UX IA (64-bit)'
datafile
'/backup/mig4may/ACCT_DETAILS_1_11.xtf',
'/backup/mig4may/ACCT_DETAILS_2_12.xtf',
'/backup/mig4may/ACCT_DETAILS_3_13.xtf',
'/backup/mig4may/ACCT_MASTER_14.xtf',
'/backup/mig4may/ACCT_TRANS_15.xtf',
'/backup/mig4may/ACCT_TRANS_312.xtf',
'/backup/mig4may/ACCT_TRANS_319.xtf',
'/backup/mig4may/ACCT_TRANS_325.xtf',
'/backup/mig4may/ACCT_TRANS_330.xtf',
'/backup/mig4may/TRANS_28.xtf',
'/backup/mig4may/WMS_HISTORY_49.xtf',
'/backup/mig4may/WMS_MASTER_48.xtf',
'/backup/mig4may/WMS_TRANS_50.xtf',
'/backup/mig4may/XCRMDEMOTRDMEMMGSS_225.xtf',
'/backup/mig4may/XCRMMAINENTRELMGSS_219.xtf'
format
'/findata_inst1/data/conv/%N_%f.dbf' parallelism 60;
}
Note: –
1. EXCLUDE SYSTEM AND SYSAUX
DATAFILES FROM THE BACKUP, WE DON’T NEED TO CONVERT OR PLUGIN
IN THE DESTINATION SIDE, ITS VERY IMPORT.
2. In our case source platform
is AIX 64 bit so change this accordingly.
Step 9> Plugin tablespaces into destination
database, once export is over as per step 7.
Note : Please check datafile count and status
and set the business tablespaces on the database to READ WRITE mode on target
database
Sho parameter db_file
Select tablespace_name,status from
dba_tablespaces;
Alter tablespace account_details read write
impdp \’/ as sysdba\’ directory=tts
dumpfile= TRANSPORT_META_FINAL.dmp logfile= TRANSPORT_META_FINAL.log parfile=imp.par EXCLUDE=TRIGGER
or
impdp directory=tts
dumpfile=TRANSPORT_META_FINAL.dmp logfile=META_FINAL.log parfile=imp.par EXCLUDE=TRIGGER
[orasbm@CBSDB201 exp]$ cat imp.par
transport_datafiles=
'/findata_inst1/SBMNEW2019/SBMUAT/DATA/SVS_SIGN_320.dbf',
'/findata_inst1/SBMNEW2019/SBMUAT/DATA/SVS_SIGN_306.dbf',
NOTE :- We exclude trigger as while
importing facing some issues and this is the workaround,so check accordingly.
After plugin all the tablespaces would be in
read only mode, so just make them read write on destination side.
Step 10 > Post Migration
missing objects Issues/Fixes (Source DB offline)
$
Missing Objects, it could be possible that multiple objects like public
synonyms, Materialized views are missing on destination side, we can check and
recreate accordingly as below:
§ Missing objects
details :- Create a db link LINK_OLD on destination database that point to
source database (sys user) .
set
pages 5000
set
lines 190
col
object_name for a55
select
a.owner, a.object_type, a.object_name , a.created
from
dba_objects@LINK_OLD a
where
a.owner not in (‘XDB’,’MDSYS’,’ORDDATA’,’SYS’,’SYSMAN’,’DBSNMP’,’SYSTEM’,
‘PERFSTAT’,’OUTLN’,’MDSYS’,’ORDSYS’,’DMSYS’,’CTXSYS’,’APPQOSSYS’,’DMSYS’,’MDSYS’,’WMSYS’,’EXFSYS’,’ODMRSYS’,’OLAPSYS’,’SQLTXPLAIN’,’SQLTXADMIN’)
and
not exists (select b.object_name from dba_objects b
where
b.owner = a.owner and b.object_type = a.object_type
and
b.object_name = a.object_name)
and
a.object_name not like ‘SYS_%’
order
by a.owner, a.object_type, a.object_name
/
§ For missing public
synonyms, use below pl/sql block to get the create statements and just simply
run on destination side:
set
serveroutput on
declare
cursor
cur_objects is
select
a.owner, a.SYNONYM_NAME,a.TABLE_NAME,a.TABLE_OWNER
from
dba_synonyms@LINK_OLD a
where
a.owner not in (‘XDB’,’MDSYS’,’ORDDATA’,’SYS’,’SYSMAN’,’DBSNMP’,’SYSTEM’,
‘PERFSTAT’,’OUTLN’,’MDSYS’,’ORDSYS’,’DMSYS’,’CTXSYS’,’APPQOSSYS’,’DMSYS’,’MDSYS’,’WMSYS’,’EXFSYS’)
and
not
exists (select b.SYNONYM_NAME from dba_synonyms b
where
b.owner = a.owner
and
b.SYNONYM_NAME = a.SYNONYM_NAME)
and
a.object_name not like ‘SYS_%’
order
by a.owner, a.SYNONYM_NAME;
begin
for
rec_objects in cur_objects loop
begin
DBMS_OUTPUT.PUT_LINE(‘create
public synonym “‘ || rec_objects.SYNONYM_NAME || ‘” for “‘
||rec_objects.TABLE_OWNER ||'”.”‘||rec_objects.TABLE_NAME || ‘”;’);
end;
end
loop;
end;
/
Database Upgrade from 12.1.0.2 to 19c Phase-2
Pre-Requisite Phase-2
1) download Oracle Binaries for 19c (Linux 64 bit) from edelivery.oracle.com
2) Create new home for 19c
3) Oracle 19c Binary copy at New Oracle Home
4) cp tns and listner and spfile from old oracle Home to New 19c home
5) Install 19c Binaries
6)Apply Latest patch on 19c (19.16.0.0.0) N-1
Steps to be follow as per Outcome from pre-upgrade log
purge recycle bin "purge recyclebin;
purge dba_recylebin;"
Move system.aud$ table to other tablespace or truncate system.aud$
SELECT count(*) FROM system.aud$; truncate table system.aud$;
JOB Queue Cleanup alter system set JOB_QUEUE_PROCESSES=0 scope=both;
SELECT * FROM sys.dba_2pc_pending; "delete from sys.pending_trans$ where local_tran_id ='';
delete from sys.pending_sessions$ where local_tran_id ='';
delete from sys.pending_sub_sessions$ where local_tran_id ='';
commit;"
Remove OLAP Catalog sqlplus / as sysdba @$ORACLE_HOME/olap/admin/catnoamd.sql
uninstall Label Security sqlplus / as sysdba @$ORACLE_HOME/rdbms/admin/catnools.sql
Recompile Invalid Object sta ?/rdbms/admin/utlrp.sql
Execute Gather State EXECUTE DBMS_STATS.GATHER_DICTIONARY_STATS;
Run Preupgrade fixup sql /oracle/preupgrade/preupgrade_fixups.sql
Check Object Status select count(*),status from dba_objects group by status;
shutdown 12c database shut immediate
Upgrade Activities
set Oracle NEW HOME export ORACLE_HOME=/oracle/app/oracle/product/19.0.0/dbhome_1
set PATH export PATH=$ORACLE_HOME/bin:$PATH
Create Direcory for upgrade mkdir -p /oracle19c/WHILEUPGRADE1/
database starup upgrade from 19c Binary startup upgrade
check status of database select instance_name,status from v$instance;
exit from sqlprompt sql>exit
Roll Back Plan
Downgrading database to previous version using Flashback database (FALLBACK PLAN)
Set New Home environment; /oracle19c_bc/app1/oracle/product/19.0.0/dbhome_1;
shutdown immediate; startup mount
restore guaranteed point; flashback database to restore point before_upgrd19c; shutdown immedaite
Set OLD home environment; /oracle/app/oracle/product/12.2.0/dbhome_1; startup mount
Open database using reset log; alter database open resetlogs;
Validate DBA Registery View; select comp_name, version, status from dba_registry;
Start Listener Services
Database Handover Application Team
Rollback Plan
Rollback Plan
No comments:
Post a Comment