Migrate On-Premise Oracle (Standard Edition) Database on Windows to OCI DBCS
Contents
Recently, I migrated the on-premise standard edition Oracle database on the Windows platform to DBCS in OCI.
I am writing this blog to mention all the issues/challenges that I faced during this migration. I am not writing all the commands here but the procedure on how to do that.
Pre-requisite: Compute instance with all the Oracle binaries installed and DBCS in OCI
First Challenge: Which Migration method to use?
I thought of a few methods but finally used the manual DG (Data Guard) method.
ZDM (Zero Downtime Migration) = We can not use it because the source is on Windows.
DMS (OCI Database Migration) = We can not use it because the source is on Windows.
Expdp/Impdp = We can not use it because the source is a standard edition and does not support encryption and parallelism. (Hard to export a big database without these 2 features)
Finally, I chose the old manual way to create the Physical standby method, But this method also was not easy and faced many issues, let’s discuss those issues.
Second Challenge: We can not create standby directly on DBCS, So I have to create a compute Instance in OCI for standby purposes and later I will move the DB to DBCS (will show in this blog later).
So now we have on-premises Oracle standard edition DB on Windows and compute instance in OCI, make sure we can access compute instance from Windows machine and access source DB from OCI compute instance.
Third Challenge: Recovery Issue
I created the standby database using the below steps.
Step-by-Step Guide on Creating Physical Standby Using RMAN DUPLICATE…FROM ACTIVE DATABASE (Doc ID 1075908.1)
From the above Doc when you run the below step
f. On the primary system invoke the RMAN executable and connect to the primary and the auxiliary database (Example: the standby)
It will restore the database but fail during recovery with the below error message.
RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03002: failure of Duplicate Db command at 09/25/2023 00:11:27
RMAN-05501: aborting duplication of target database
RMAN-03015: error occurred in stored script Memory Script
RMAN-03009: failure of sql command on ORA_DISK_1 channel at 09/25/2023 00:11:27
RMAN-10038: database session for channel ORA_DISK_1 terminated unexpectedlySo I used below MOS Doc below to resolve the above issue
Step By Step Guide On How To Recreate Standby Control File When Datafiles Are On ASM And Using Oracle Managed Files (Doc ID 734862.1)
Precisely I used below steps:
a. Create the Standby control file on primary database
b. Copy the control file backup to the standby system (compute Instance)
c. Shutdown standby DB and startup in nomount
d. Connect to RMAN and restore standby controlfile and rename
rman target /
RMAN> restore standby controlfile from '/u01/STDBYCTL.BKP';
RMAN> alter database mount;
RMAN> catalog start with '/u01/app/oracle/oradata/dev1_stby/DEV1_STBY/';
RMAN> switch database to copy;
RMAN> exit
Now standby is almost ready
Fourth Challenge: Log shipping
Standard Edition database doesn’t support advanced Data Guard features like switchover, automatic log shipping, etc. So we have to manually copy the archive logs from primary to standby.
On Standby it will apply logs automatically if the MRP process running because Standby is Enterprise Edition.
So I used below steps:
1. Start managed recovery on standby
recover managed standby database disconnect from session nodelay;
2. Check mrp process
ps -ef | grep mrp
3. Now check the primary and standby in sync at this point
target:
SQL> select current_SCN from v$database;
CURRENT_SCN
-----------
1750369
Source:
SQL> select current_SCN from v$database;
CURRENT_SCN
-----------
1895707
4. Now we need to find the archive logs between above 2 SCN and move those archives from primary to standby side manually due to standard edition of DB at source side. Use below command to find archives.
RMAN> list archivelog scn between 1750369 and 1895707;
5. Copy all the archives from above output to standby side manually.
6. Register all the archivelog files on standby database using below command.
alter database register logfile 'logfile_name.ARC';
7. Once all archive log registered, MRP process apply those archives on standby.
8. Now check if both DB in sync.
Once both DBs are in sync, it’s time for cutover.
Fifth Challenge: Switchover
As the source is a standard edition database, we can do the switchover, we have to activate the standby database.
Steps I used:
1. Stop the primary DB if required for consistency purpose.
2. Activate standby database
SQL> recover managed standby database cancel;
Media recovery complete.
SQL> alter database recover managed standby database finish;
Database altered.
SQL> alter database activate standby database;
Database altered.
SQL> select name,open_mode,database_role from v$database;
NAME OPEN_MODE DATABASE_ROLE
--------- -------------------- ----------------
DEV1 MOUNTED PRIMARY
SQL> alter database open;
Database altered.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 FSDEV MOUNTED
SQL> alter pluggable database FSDEV open;
Warning: PDB altered with errors.
Now database is migrated to OCI compute instance, we just need to do few more post migration steps.
3. Add tempfiles in PDB.
4. Check the PDB plugin violation to find out why PDB open in restricted mode.
SQL> select line,message,status from pdb_plug_in_violations where name='FSDEV' order by time,line;
LINE MESSAGE STATUS
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------ ---------
1 Database option OWM mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING
1 No release updates are installed in the CDB but '19.8.0.0.0 Release_Update 2007241916' is installed in the PDB PENDING
5. If you see above RU is missing, so we have to apply datapatch.
[oracle@fsdev_compute OPatch]$ ./datapatch -verbose
6. Once datapatch applied successfully, stop/start the database and this time PDB will start without restriction.
7. Check any invalid objects and run utlrp script.
SQL> @$ORACLE_HOME/rdbms/admin/utlrp.sql
At this point, migration is completed to compute instance but if we need to use DBCS service in OCI, we have to move this PDB to DBCS.
Last challenge: move PDB from compute instance to DBCS
I used the PDB cloning method using DB link (you can use any other method as well), now DBCS is the target, and standby DB (which is opened now) is the source.
Steps:
1. create tns entry on DBCS side to connect standby db which was opened in last step.
2. Create user and provide grants on source side (standby side)
I will use system user so not creating the user but need to provide below grants.
SQL> grant create session, create pluggable database to system container=all;
Grant succeeded.
3. Create db link on DBCS side
SQL> create database link fsdev_link connect to system identified by password using 'dev1_stby';
Database link created.
SQL> select * from dual@fsdev_link;
D
-
X
4. Create pluggable database FSDEV on DBCS
create pluggable database FSDEV from FSDEV@fsdev_link CREATE_FILE_DEST='+DATA';
alter pluggable database FSDEV open;
5. We will see database is open in restricted mode now, so we have to check PDB violations.
SQL> select line,message,status from pdb_plug_in_violations where name='FSDEV' order by time,line;
You will see many errors regarding tablespaces because tablespaces are not encrypted and on DBCS its mandatory to use TDE/encryption. so we can encrypt later.
But I saw one of error is..
LINE MESSAGE STATUS
---------- ------------------------------------------------------------------------------------------------------------------------------------------------------ ---------
1 CDB parameter processes mismatch: Previous 320 Current 400 PENDING
2 CDB parameter db_block_size mismatch: Previous 16384 Current 8192 PENDING
3 CDB parameter compatible mismatch: Previous '12.1.0.2' Current '19.0.0.0' PENDING
4 CDB parameter open_links_per_instance mismatch: Previous 10 Current 4 PENDING
5 CDB parameter max_pdbs mismatch: Previous 4 Current 4098 PENDING
1 Database option CONTEXT mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING
2 Database option DV mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING
3 Database option OLS mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING
4 Database option ORDIM mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING
5 Database option OWM mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING
6 Database option SDO mismatch: PDB installed version NULL. CDB installed version 19.0.0.0.0. PENDING
1 Interim patch 34786990/25032666 (OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)): Installed in the CDB but not in the PDB PENDING
1 Tablespace SYSTEM is not encrypted. Oracle Cloud mandates all tablespaces should be encrypted. PENDING
One issue that we need to address is..
1 Interim patch 34786990/25032666 (OJVM RELEASE UPDATE: 19.18.0.0.230117 (34786990)): Installed in the CDB but not in the PDB PENDING
6. As per above error, we have to apply the datapatch on FSDEV PDB.
[oracle@dbcs-dev admin]$ /u01/app/oracle/product/19.0.0.0/dbhome_1/OPatch/datapatch -verbose -apply 34786990/25032666 -force -pdbs FSDEV
7. Now we wont see that patch violation , so restart the PDB again and it will open in no restricted mode
create dblink for the pdb on the standby db that was opened.
8. Now check the INVALID objects and run the utlrp script.
10. Cloning id done. The last thing left is encryption.
To check on PDB, I saw wallet is already there but no key (DBCS already created wallet on CDB side)
SQL> alter session set container=FSDEV;
Session altered.
SQL> SELECT KEY_ID, CREATION_TIME FROM V$ENCRYPTION_KEYS ORDER BY 1;
no rows selected
SQL> select CON_ID,STATUS,WRL_PARAMETER WALLET_DIR,WALLET_TYPE from V$ENCRYPTION_WALLET;
CON_ID STATUS
---------- ------------------------------
WALLET_DIR
--------------------------------------------------------------------------------
WALLET_TYPE
--------------------
4 OPEN_NO_MASTER_KEY
AUTOLOGIN
SQL>
11. Create the key:
SQL> ADMINISTER KEY MANAGEMENT SET KEY FORCE KEYSTORE IDENTIFIED BY password WITH BACKUP;
keystore altered.
SQL> set linesize 200
set CON_ID 20
col KEY_ID for a60
col KEYSTORE_TYPE for a20
select CON_ID,KEY_ID,KEYSTORE_TYPE from v$encryption_keys;SQL> SP2-0158: unknown SET option "CON_ID"
SQL> SQL> SQL>
CON_ID KEY_ID KEYSTORE_TYPE
---------- ------------------------------------------------------------ --------------------
4 ATdvIGfuhr987AcMK7sAAAAAAAAAAAAAfvAAAAAAA SOFTWARE KEYSTORE
SQL> set linesize 200
col WALLET_DIR for a32
col status for a21
select STATUS,WRL_PARAMETER WALLET_DIR,WALLET_TYPE from V$ENCRYPTION_WALLET;
SQL> SQL> SQL>
Status WALLET_DIR WALLET_TYPE
--------------------- -------------------------------- --------------------
OPEN AUTOLOGIN
12. Create a script to encrypt all tablespaces and run it.
SQL> spool /u01/app/oracle/tablespace_encryption/encryption.sql
SQL> set trimspool on linesize 1000 pages 1000 feedback off head off
select 'alter tablespace '||tablespace_name||' encryption online encrypt;'
from dba_tablespaces
where encrypted = 'NO' and contents='PERMANENT'
order by tablespace_name;
SQL> set timing on
SQL> set echo on
SQL> @encyption.sql
13. Check if any datafile let without encryption:
SQL> select name,encrypted from v$datafile_header where encrypted='NO';
Full migration completed – Thanks for reading!