eBS: db tier migration – Oracle Technology in the Cloud

This note is about migrating a non-Linux eBS system, 12.1.3 to OCI, using the transportable tablespace method, as detailed in the MOS 2473448.1

This note is about the db tier migration.

As this point, the databases are assumed to have been already been transfered on the dbsystem, properly prepared according to this note.

#1 Use rman convert to copy the datafiles to ASM.

Warning: Do not use asmcmd cp, as the datafiles would be copied improperly over +DATA/ASM/DATAFILES, even so the alias will be created properly into the database PDB folder.

rman target / @aurman.dat

where aurman.dat inclused the following content:

CONVERT DATAFILE '/u01/app/oracle/backup/a_int02.dbf'                             FORMAT '+DATA';
CONVERT DATAFILE '/u01/app/oracle/backup/a_media01.dbf'                               FORMAT '+DATA';
...
CONVERT DATAFILE '/u01/app/oracle/backup/portal01.dbf'                                FORMAT '+DATA';

Check that the datafiles are properly copied


ASMCMD> ls -l
 Type      Redund  Striped  Time             Sys  Name
 DATAFILE  UNPROT  COARSE   APR 24 17:00:00  Y    APPS_TS_ARCHIVE.306.1006448641
 DATAFILE  UNPROT  COARSE   APR 24 17:00:00  Y    APPS_TS_INTERFACE.304.1006449553
 DATAFILE  UNPROT  COARSE   APR 24 15:00:00  Y    SYSAUX.270.1006370521
 DATAFILE  UNPROT  COARSE   APR 24 15:00:00  Y    SYSTEM.269.1006370521
 DATAFILE  UNPROT  COARSE   APR 24 15:00:00  Y    USERS.273.1006370791

#17 Create a directory impdp where metatafiles will be staged.

CREATE DIRECTORY <dir> AS ‘<location>

#18 Open a ‘tail’ session on the alert.log, to catch on issues otherwise not visible from the impdp session documented in the next section

#19 Run the metadata import

export ORACLE_PDB_SID=<pdb>
impdp system/<password> parfile=auimpfulltts.dat

That import is expected to run for 17hours on a VM2.2 shape.

#20 Revoke some privilege from system

revoke EXEMPT ACCESS POLICY from system;

#18 Make a second backup

#1 Provision a dbsystem with the latest supported version for eBS, for example 12.1.0.2.190115

#2 Setup the oracle account by copying the private key from opc to the oracle account

#3 Shutdown the database before applying the patches listed in the next section. Do use srvctl to shutdown the database to properly set the ORACLE_UNQNAME. The database name can be found from the command below

 srvctl config database
 <container>_fraYYY

#4 Transfer and apply all interoperability patches for
12.1.0.2.190115. They are 8 of them bundle from the patch
p29463188_R12_LINUX.zip

p20839705_12102170718ProactiveBP_Generic.zip
p22660003_12102180717ProactiveBP_Linux-x86-64.zip
p22731026_121027DBEngSysandDBIM_Generic.zip
p22747454_121020_Generic.zip
p22828765_121020_Generic.zip
p23645516_12102190115ProactiveBP_Linux-x86-64.zip
p24007737_12102170418ProactiveBP_Linux-x86-64.zip
p27051384_12102170117ProactiveBP_Linux-x86-64.zip
p29453877_12102190115ProactiveBP_Linux-x86-64.zip

Warning: Run Datapatch as the end

In addition, 27873364 must be applied on the top of 12.1.0.2.190115 to address the ORA-0600 [ktliReadStreamAdvanceBuf_2] [1957] when running adstats.sql

#5 Apply the same DST patch as was applied in the source database

For example apply the patch DST v32, deliverd from the patch 28125601

#6 Transfer the backup files on the /u01 filesystem, assuming there is enough disk space. Make sure datafiles for system, sysaux, temp, undo tablespaces were not copied over, same for logfiles

#7 Setup the NLS10 messages files (as usual)

perl $ORACLE_HOME/nls/data/old/cr9idata.pl

#8 Prepare appsutil

cd $ORACLE_HOME
mkdir appsutil
cd appsutil
ln -s $ORACLE_HOME/jdk/jre

#9 Save PDB state

ALTER PLUGGABLE DATABASE ebsdb SAVE STATE;

#10 Configure the grid listener

Add the following line to the tnsnames.ora file then reload the listener.


USE_SID_AS_SERVICE_LISTENER=ON
lsnrctl reload

#11 Define the folloowing aliases in the tnsnames.ora

<cont> =
   (DESCRIPTION=
     (ADDRESS=(PROTOCOL=tcp)(HOST=<hostname>)(PORT=1521))
     (CONNECT_DATA=
       (SERVICE_NAME=<PDB>)
       (INSTANCE_NAME=cont)
     )
   )
 
<PDB>=
   (DESCRIPTION=
     (ADDRESS=(PROTOCOL=tcp)(HOST=e<hostname>b)(PORT=1521))
     (CONNECT_DATA=
       (SERVICE_NAME = <pdb>.<domain>)
       (INSTANCE_NAME=<cont>)
     )
   )

extproc_connection_data =
   (DESCRIPTION=
     (ADDRESS_LIST =
       (ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC<PDB>))
     )
     (CONNECT_DATA=
       (SID=PLSExtProc)
       (PRESENTATION = RO)
     ) )

#12 Create a dummy init.ora file for the PDB

touch $ORACLE_HOME/dbs/init[PDB].ora

#13 Prepare the target PDB

export ORACLE_PDB_SID=ebsdb
sqlplus "/ as sysdba" @audb1210.sql
sqlplus system/xxx  @ausy1210.sql

#14 Run catmgr from both the container and the pdb

exec ctxsys.ctx_adm.set_parameter('file_access_role', 'public');

#15 Run adstats

$ export ORACLE_PDB_SID=<PDB>
sqlplus "/ as sysdba" <<EOF
alter system enable restricted session;
@adstats.sql 
EOF
sqlplus "/ as sysdba" <<EOF
alter system disable restricted session;
EOF

#16 Use rman convert to copy the datafiles to ASM.