Clemens' Blogs

Migrating an Oracle database to another server

There are several situations when you have to migrate your Oracle databases to a new server. This could be due to hardware lifecycle reasons for on-prem systems or you need to upgrade your Operating System (OS) from Enterprise Linux 8 to Enterprise Linux 9. In this blog I wanted to talk about my recommended methods for such migrations considering ease of use and reduced downtime. I do not cover migrations to the Oracle Cloud here, because the recommended way is to use the Zero Downtime Migration tool from Oracle for that.

For a migration to another server, we have different possibilities:

  • Data Pump expdp/impdp
  • Logical replication with e.g. Golden Gate
  • Setup of a Standby DB with Data Guard (or third party products like dbvisit standby for Standard Edition 2 DBs) and switchover during cutover
  • Using a refreshable PDB in case the multitenant architecture is already used. During migration, stop the source PDB and do a final refresh, stop refreshing and open the target PDB read/write.
  • Relocate a PDB
  • Unplug PDB, copy PDB-related files and Plug-In the PDB
  • RMAN backup and restore. To reduce downtime this can also be combined with incremental backups restored regularly on the target until cutover, when a last incremental backup is applied to the target DB.
  • RMAN duplicate
  • Data Pump Full Transportable, where you set your source tablespaces read only, export the metadata and physically move datafiles to the target, where you can import the metadata.
  • Transportable tablespaces. This can be combined with Incremental Backups to do a cross platform migration to a different endian as described in MOS Note „V4 Reduce Transportable Tablespace Downtime using Cross Platform Incremental Backup (Doc ID 2471245.1)“
  • Detaching ASM devices from the old server and attaching them to the new server.
    REMARK: This is kind of what happens when migrating to a new OS-version on the Oracle Database Appliance with Data Preserving Reprovisioning (DPR). See the blogs from my colleague Jérôme Duba on that: https://www.dbi-services.com/blog/author/jerome-dubar/
  • Just copy (e.g. with scp) all needed files to the new server

There are even more possibilities, but with above list you should find a method which fits your needs. Some of the methods above do require to be on the same Operating System and hardware architecture (no endian change), and some of them are totally independent on platform, version or endian change (like the logical migrations with data pump or Golden Gate).

One of the best methods in my view is the possibility of refreshable PDBs, because

  • it is very easy to do
  • provides a short downtime during cutover
  • allows a fallback as the previous PDB is still available
  • allows migrating PDBs individually at different times
  • allows migrating non-CDBs to PDBs as well. I.e. I can refresh a non-CDB to a PDB.
  • it is available since 12.2. and can also be used with Standard Edition 2 (SE2) DBs
  • allows going to a different Release Update (RU)
  • even allows going to a different major release and run the PDB upgrade afterwards on the target CDB
  • if the source PDB is on SE2 then the target PDB can also be on Enterprise Edition (EE)
  • moving Transparent Data Encrypted PDBs is almost as easy as moving non-encrypted PDBs
  • the inital copy of the PDB can be done very fast as Oracle is using a block-level-copy mechanism when cloning a PDB and parallelism is allowed as well on EE
  • we can use 3 PDBs per CDB since 19c without licensing the Multitenant Option. This provides some flexibility on which CDB to move the PDB to

You may check this blog with the steps to do when migrating through the refreshable PDB mechanism.

Can we migrate a 19c database to 23ai with refreshable PDBs? Yes, we can do that as shown below:

REMARK: The whole process described below can be done with the autoupgrade tool automatically. However, to see each step separately, I do this manually here.

1. Preparing the source CDB, which is on 19.22.:

sys@CDB0> select force_logging from v$database;

FORCE_LOGGING
---------------------------------------
YES

sys@CDB0> create user c##refresh_pdbs identified by welcome1 container=all;

User created.

sys@CDB0> grant create session, create pluggable database to c##refresh_pdbs container=all;

Grant succeeded.

2. Create the refreshable PDB

To have a connection between the Oracle Cloud and my on-prem 19.22.-DB I used the method described here through a ssh-tunnel:
https://www.ludovicocaldara.net/dba/push-pdb-to-cloud/

On the target server:

[oracle@db23aigi ~]$ sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems on Fri Apr 4 15:16:15 2025
Version 23.7.0.25.01

Copyright (c) 1982, 2024, Oracle.  All rights reserved.


Connected to:
Oracle Database 23ai EE High Perf Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.7.0.25.01

SQL> show pdbs

    CON_ID CON_NAME			  OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
	 2 PDB$SEED			  READ ONLY  NO
	 3 PDB1 			  READ WRITE NO
SQL> exit
Disconnected from Oracle Database 23ai EE High Perf Release 23.0.0.0.0 - for Oracle Cloud and Engineered Systems
Version 23.7.0.25.01
[oracle@db23aigi ~]$ cat clone_db.sh 
SRC_PDB=$1
TGT_PDB=$2
ALIAS=$3
 
export ORACLE_HOME=/u01/app/oracle/product/23.0.0.0/dbhome_1
export ORACLE_SID=DB23AIGI
 
$ORACLE_HOME/bin/sqlplus -s / as sysdba <<EOF
        set timing on
        create database link prod_clone_link connect to c##refresh_pdbs
          identified by welcome1 using '$ALIAS';
        create pluggable database $2 from $1@prod_clone_link refresh mode manual;
        dbms_session.sleep(120);
        alter pluggable database $2 refresh;
        alter pluggable database $2 refresh mode none;
        exit
EOF
[oracle@db23aigi ~]$

On the source-server:

oracle@pm-DB-OEL8:~/Keys/dbi-OCI/dbi3oracle/DB-systems/db23aigi/ [cdb0 (CDB$ROOT)] ssh -i ./ssh-key-2025-04-04.key opc@<public-ip-OCI> -R 1522:pm-DB-OEL8:1521 "sudo -u oracle /home/oracle/clone_db.sh PROD PROD23AI localhost:1522/PROD_PRI"

Database link created.

Elapsed: 00:00:00.01

Pluggable database created.

Elapsed: 00:06:16.42

Pluggable database altered.

Elapsed: 00:00:14.99

Pluggable database altered.

Elapsed: 00:00:00.78
oracle@pm-DB-OEL8:~/Keys/dbi-OCI/dbi3oracle/DB-systems/db23aigi/ [cdb0 (CDB$ROOT)]

3. Upgrade the PDB to 23ai on the target server

SQL> alter pluggable database PROD23AI open upgrade;

Pluggable database altered.

SQL> select name, open_mode, restricted from v$pdbs where name='PROD23AI';

NAME				 OPEN_MODE  RES
-------------------------------- ---------- ---
PROD23AI			 MIGRATE    YES

SQL> 

[oracle@db23aigi ~]$ $ORACLE_HOME/bin/dbupgrade -c "PROD23AI" -l /tmp
....
Upgrade Summary Report Located in:
/tmp/upg_summary.log

     Time: 673s For PDB(s)

Grand Total Time: 673s 

 LOG FILES: (/tmp/catupgrd*.log)


Grand Total Upgrade Time:    [0d:0h:11m:13s]
[oracle@db23aigi ~]$

REMARK: As mentioned initially I should have used autoupgrade for the whole process (or just the upgrade) here as $ORACLE_HOME/bin/dbupgrade has been desupported in 23ai, but for demonstration purposes of refreshable PDBs it is OK.

4. Final steps after the upgrade

-- check the PDB_PLUG_IN_VIOLATIONS view for unresolved issues
SQL> alter session set container=PROD23AI;

Session altered.

SQL> select type, cause, message 
from PDB_PLUG_IN_VIOLATIONS 
where name='PROD23AI' and status != 'RESOLVED';  2    3  

TYPE		CAUSE			       MESSAGE
--------------- ------------------------------ ------------------------------------------------------------------------------------------
WARNING 	is encrypted tablespace?       Tablespace SYSTEM is not encrypted. Oracle Cloud mandates all tablespaces should be encrypted.
WARNING 	is encrypted tablespace?       Tablespace SYSAUX is not encrypted. Oracle Cloud mandates all tablespaces should be encrypted.
WARNING 	is encrypted tablespace?       Tablespace USERS is not encrypted. Oracle Cloud mandates all tablespaces should be encrypted.
WARNING 	Traditional Audit	       Traditional Audit configuration mismatch between the PDB and CDB$ROOT

SQL> administer key management set key using tag 'new own key' force keystore identified by "<wallet password>" with backup;

keystore altered.

SQL> alter tablespace users encryption online  encrypt;

Tablespace altered.

SQL> alter tablespace sysaux encryption online  encrypt;

Tablespace altered.

SQL> alter tablespace system encryption online  encrypt;

Tablespace altered.

SQL> exec dbms_pdb.CLEAR_PLUGIN_VIOLATIONS;

PL/SQL procedure successfully completed.

SQL> select type, cause, message 
from PDB_PLUG_IN_VIOLATIONS 
where name='PROD23AI' and status != 'RESOLVED';

no rows selected


-- Recompile invalid objects using the utlrp.sql script:
SQL> alter session set container=PROD23AI;
 
Session altered.
 
SQL> @?/rdbms/admin/utlrp.sql
 
PL/SQL procedure successfully completed.

-- Downtime ends. Check the DBA_REGISTRY_SQLPATCH view:
SQL> alter session set container=PROD23AI;
 
Session altered.
 
SQL> select patch_id, patch_type, status, description, action_time from dba_registry_sqlpatch order by action_time desc;

  PATCH_ID PATCH_TYPE STATUS	 DESCRIPTION						      ACTION_TIME
---------- ---------- ---------- ------------------------------------------------------------ --------------------------------
  37366180 RU	      SUCCESS	 Database Release Update : 23.7.0.25.01 (37366180) Gold Image 04-APR-25 04.00.06.975353 PM

Summary:

If you haven’t done this yet, then I do recommend to migrate to the multitenant architecture as soon as possible. It makes several DBA tasks so much easier. Especially the migration to a new server with refreshable PDBs is very easy to do with low downtime, high flexibility and almost no impact on the source PDB during refreshes. On top of it you do not lose your source PDB during the process and may go back to it in case tests show that the target is not working correctly.

web24522998

, , , ,

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert