This note is about migrating an eBS database running 12.2(.8) from on-premise to a dbsystem 12c on OCI.
The initial on-premise database backup destination may be either disk or a bucket storage. This note is about the first method.
This note is applicable for db12c only, as db19c requires addition patches and new scripts to be executed, for example 28371446:R12.TXK.C.
Transfer both the database backup, the appsutil.tar and the sqlpatch.tar files on dbsystem newly provisioned.
Note:
a) choose carefully the character set AND national character set; for demo/vision instances, it must be AL32UTF8/UTF8.
b) eBS VM will run more smoothly on a Standard2.1 or higher shape.
c) do not specify any pdb to be created with the same name as the database to be restored.
Log on the dbsystem as grid (for the first steps), then oracle to:
#1 Update $GRID_HOME/network/admin/listener.ora with the following parameter
USE_SID_AS_SERVICE_LISTENER=ON
If this step is missed, the script txkPostPDBCreationTasks.pl will later most likely return ORA-12505 errors.
#2 Reload the grid listener
srvctl stop listener srvctl start listener
#3 Unzip the appsutil in the ORACLE_HOME
#4 Apply the following extra patches in the $ORACLE_HOME
30083041: CLOUD EBS RELEASE 12.2 CONSOLIDATED DATABASE FIXES FOR DBSI JUL 2019
Important: Do NOT apply the regular consolidated patch for Jul19.
Note that at this point, the patch 17537119 provides an “ETCC” script designed to check that all database prerequisite are in place.
#5 Run datapatch on the cdb database to take into account the newly patches applied
Note: If datapatch for 22747454 return some ORA-29532 error, run the below as SYS and restart datapatch or catmgd.sql (MOS1908767.1):
call dbms_java.grant_policy_permission('JAVA_ADMIN','SYS','javax.management.MBeanServerPermission','*');
call dbms_java.grant_policy_permission('JAVA_ADMIN','SYS','javax.management.MBeanPermission','*');
To workaround the following issue, touch the missing file.
Patch 29857136 rollback (pdb EBSDB): WITH ERRORS logfile: /u01/app/oracle/cfgtoollogs/sqlpatch/29857136/23005461/29857136_rollback_<...>.log (errors) E
#6 Restore the database on a non-CDB on the same ORACLE_HOME as the dbsystem CDB database
At the end of the restore, the database is running as a non-CDB, with the datafiles stored into ASM at the location +DATA/<SID>.
#7 Run datapatch on the restored database
If all prereqs have been installed on the database before the backup was made, the $ORACLE_HOME/sqlpatch directory should include all metadata. If not, then leverage the sqlpatch.tar file to stage the relevant patch information under $ORACLE_HOME/sqlpatch.
This step may run for 1 hour.
rror at line 2026: SP2-0310: unable to open file "/u01/app/oracle/product/12.1.0.2/dbhome_1/sqlpatch/29857136/23005461/rollback_files/rdbms/admin/nothing.sql"
#8 Restart the non-cdb database in read-only.
Note: If the non-cdb database is not started in read-only, subsequent steps may fail with the error ORA-65139, with the risk to corrupt this database.
#9 Run the TXK utilities to generate the PDB descriptor and detect any plugin violation.
export LPDB=ebsdb
export ORACLE_SID=${LPDB}
export TNS_ADMIN=$ORACLE_HOME/network/admin
sqlplus '/as sysdba' <<EOF
BEGIN DBMS_PDB.DESCRIBE(pdb_descr_file => '$ORACLE_HOME/dbs/${LPDB}_PDBDesc.xml');
END;
/
EOF
cd $ORACLE_HOME/appsutil
. ./txkSetCfgCDB.env dboraclehome=$ORACLE_HOME
perl $ORACLE_HOME/appsutil/bin/txkChkPDBCompatability.pl -dboraclehome=$ORACLE_HOME -outdir=${LLOG} -cdbsid=${LCDB} -pdbsid=${LPDB}
Do NOT ignore character set violation warnings. They cannot be escaped from the utility, especially warning related to character sets.
If the dbsystem is running Entreprise Edition instead of Entreprise Edition – High-Performance, olap may need to be installed manually as follow
stop all databasechopt enable olap <= this will relink the executablechmod 6751 ?/bin/oraclechgrpo asmadmin ?/bin/oraclestart all database sqlplus / as sysdba @?/olap/admin/cataps.sql sqlplus / as sysdba @?/olap/admin/catxoq.sql
#10 Plug the non-cdb as a pdb inside the cdb
export LCDB=cdb
export LPDB=ebsdb
export ORACLE_SID=${LCDB}
export TNS_ADMIN=$ORACLE_HOME/network/admin
perl $ORACLE_HOME/appsutil/bin/txkCreatePDB.pl -dboraclehome=$ORACLE_HOME -outdir=${LLOG} -cdbsid=${LCDB} -pdbsid=${LPDB} -noncdbdatadir=+DATA -pdbdatadir=+DATA
The command above may run for 1 hour on a Standard2.8 shape, and 6 or more hours on a Standard2.1 shape, also depending the size of the database.
#11 Stop the non-cdb database. Do NOT attempt to drop it.
#12 Enable archivelog on the cdb.
This require a restart of the database
#13 [Optional] Run datapatch on the cdb.
Note: This step should not be necessary, unless some patches were present in the cdb but not in the non-cdb, with the requirement to start the database cdb and all pdb upgrade mode.
Check the violation status to look for any violation related to the patch level:
SELECT * FROM pdb_plug_in_violations;
#13 [Optional] Unlock the system password
ALTER USER system ACCOUNT UNLOCK;
#14 Regenerate the context file and run autoconfig
export LCDB=cdb
export LPDB=ebsdb
export LLOG=/home/oracle/wip
export LAPPS=apps
export LSYSTEM=manager
cd $ORACLE_HOME/appsutil
. ./txkSetCfgCDB.env dboraclehome=$ORACLE_HOME
export ORACLE_SID=${LCDB}
export TNS_ADMIN=${ORACLE_HOME}/network/admin
{ echo ${LAPPS}; echo ${LSYSTEM}; } | perl ${ORACLE_HOME}/appsutil/bin/txkPostPDBCreationTasks.pl -dboraclehome=${ORACLE_HOME} -outdir=${LLOG} -cdbsid=${LCDB} -pdbsid=${LPDB} -appsuser=apps -promptmsg=hide -servicetype=dbsystem
Specify the option servicetype=dbsystem or the script may improperly try to bound the listener, running as ‘grid’ on a dbsystem.
If the script enters into an infinite loop “Enter Hostname of Database Server”, check the very few lines before this loop starts, the issue may be related to a wrong JRE stack, with the error “UnsatisfiedLinkError exception loading native library: njni12” being returned.
#15 Update the CTX library
export ORACLE_SID=${LCDB}
cd $ORACLE_HOME/appsutil/context/<context>
sqlplus / as sysdba @adupdlib so
#16 Update the master key encryption.
This step is required or backups may fail (https://docs.cloud.oracle.com/iaas/Content/Database/Troubleshooting/Backup/backupfail.htm)
Run this first command to identify the database id
# dbcli list-databases ID DB Name DB Type DB Version CDB Class Shape Storage Status DbHomeID ---------------------------------------- ---------- -------- -------------------- ---------- -------- -------- ---------- ------------ ---------------------------------------- <id> dev Si 12.1.0.2.190716 true Oltp ASM Configured <dbhomeid>
Then run the following command to add the key
# dbcli update-tdekey -i <id> -n ebsdb -p TDE Admin wallet password:
#17 Check the patch level
Do NOT continue with the autoconfig setup until the script below return the PDB name. If not, run the etcc script to check for any missing database patch:
export ORACLE_PDB_SID=${LPDB}
sqlplus / as sysdba <<EOF
select SYS_CONTEXT('USERENV','DB_NAME') from dual ;
EOF