Migrating DB to new storage using RMAN

Let's continue from my earlier post Moving an Oracle Database to New ASM Disk Groups

We will discuss how to move or migrate Oracle database from one storage to new storage with less downtime .
This will be approach 2:

RMAN – SWITCH DATABASE TO COPY

Scenario: 
Database name: TESTDB
Current data disk group: +DATAC2
Current recovery disk group: +RECOC2
Target data disk group: +DATAC3
Target recovery disk group: +RECOC3

The objective is to move all database files from the existing ASM disk groups to the new ones with minimal risk and controlled execution.

Step 1: Login into database and collect DB info such as  ( DB files, control files , redo log files , temp files,password and spill etc)

SET LINES 300 PAGES 300

COL name FORMAT a100

SELECT name FROM v$controlfile;

SELECT file#, name FROM v$datafile;

SELECT file#, name FROM v$tempfile;

show parameter db_recovery_file_dest

show parameter db_create_file_dest

set pages 300 lines 300

column REDOLOG_FILE_NAME format a60

SELECT

 a.GROUP#,

 a.THREAD#,

 a.SEQUENCE#,

 a.ARCHIVED,

 a.STATUS,

 b.MEMBER AS REDOLOG_FILE_NAME,

 (a.BYTES/1024/1024) AS SIZE_MB

FROM v$log a

JOIN v$logfile b ON a.Group#=b.Group#

ORDER BY a.GROUP# ;

srvctl config database -d TESTDB

(example)

Spfile: +DATAC2/TESTDB/PARAMETERFILE/spfile.391.1203429481

Password file: +DATAC2/TESTDB/PASSWORD/pwdTESTDB.265.1203428827

It's better to drop and recreate redo log files with location pointing to new ASM disk group.

SQL> alter database add logfile thread 1 group 11 ('+DATAC3','+RECOC3') size 200M;

SQL> alter database drop logfile group 2 >>>>> drop old redo logs that are created at DATAC2 old storage one by one.

Step2:  Take a backup of database into new storage using RMAN.

RMAN> list copy of database;  >>>> This list will probably  be empty if you have never taken a database copy before

RMAN> run

{

allocate channel c1 device type disk format '+DATAC3';

allocate channel c2 device type disk format '+DATAC3';

allocate channel c3 device type disk format '+DATAC3';

allocate channel c4 device type disk format '+DATAC3';

backup as copy database format '+DATAC3';

}

Step3:   Create a pfile , make necessary changes 

example :

*.control_files='+DATAC3/TESTDB/CONTROLFILE/control_01.ctl','+RECOC3/TESTDB/CONTROLFILE/control_02.ctl'

*.db_create_file_dest='+DATAC3'

*.db_recovery_file_dest='+DATAC3

Step4:  shutdown the database , copy control files using asm copy

example :

ASMCMD [+DATAC2/TESTDB/CONTROLFILE] > cp +DATAC2/TESTDB/CONTROLFILE/Current.2883.1203428949 +DATAC3/TESTDB/CONTROLFILE/control_01.ctl

Step4:  switch database to the copy of database created in step#2

SQL> startup mount pfile ='/home/oracle/pfile_TESTDB_move_X11.ora';

RMAN> switch database to copy;

RMAN> recover database;

SQL> alter database open;

Step5:   create  SPFILE, copy password file and modify database configuration using srvctl.

Example:

SQL> create spfile='+DATAC3/TESTDB/PARAMETERFILE/spfileTESTDB.ora' from pfile='/home/oracle/pfile_TESTDB_move_X11.ora';

ASMCMD [+RECOC3/TESTDB] > pwcopy --dbuniquename TESTDB +DATAC2/TESTDB/PASSWORD/pwdTESTDB.265.1203428827 +DATAC3/TESTDB/PASSWORD/pwdbTESTDB.ora -f

copying +DATAC2/TESTDB/PASSWORD/pwdTESTDB.265.1203428827 -> +DATAC3/TESTDB/PASSWORD/pwdbTESTDB.ora

SQL> shut immediate

#srvctl config database -d TESTDB

#srvctl modify database -d TESTDB -spfile +DATAC3/TESTDB/PARAMETERFILE/spfileTESTDB.ora -pwfile +DATAC3/TESTDB/PASSWORD/pwdbTESTDB.ora

#srvctl config database -d TESTDB

#srvctl start database -d TESTDB

Step 6:   We need to re-create temples to pointing to new disk locations, as RMAN switch to copy will not perform backup and restore of temples to new storage.

SQL> select file_name from dba_temp_files order by 1;

SQL> select distinct tablespace_name from dba_temp_files;

set lines 300 pages 300

col file_name for a80

SQL> create temporary tablespace temp tempfile '+DATAC3' size 1G autoextend on maxsize unlimited;

Step7 :  congrats, you have migrated your database to new storage . Just do a final checks - Run all the queries and commands mentioned in step#1 . Make sure , no DB related files pointing to old storage.

Moving an Oracle Database to New ASM Disk Groups

 Let’s consider a common DBA requirement: moving an Oracle database to new ASM disk groups.

Scenario: 
Database name: TESTDB
Current data disk group: +DATAC2
Current recovery disk group: +RECOC2
Target data disk group: +DATAC3
Target recovery disk group: +RECOC3

The objective is to move all database files from the existing ASM disk groups to the new ones with minimal risk and controlled execution.

There are two supported approaches to achieve this:

1. ALTER DATABASE MOVE DATAFILE
2. RMAN – SWITCH DATABASE TO COPY

Lets explore approach 1 first - ALTER DATABASE MOVE DATAFILE

Step 1: Collect Database File Information

SET LINES 300 PAGES 300

COL name FORMAT a100

SELECT name FROM v$controlfile;

SELECT file#, name FROM v$datafile;

SELECT file#, name FROM v$tempfile;

srvctl config database -d TESTDB ---> to find location of spfile and password file.

Step2: Generate data files movement SQLs and execute ( downtime preferred to avoid issues)

SELECT 'ALTER DATABASE MOVE DATAFILE ''' || name || ''' TO ''+DATAC3'';' FROM v$datafile;

Move control file and spfile 

SQL> create pfile='/home/oracle/pfile_TESTDB.ora' from spfile;

SQL> alter system set control_files='+DATAC3/TESTDB/CONTROLFILE/controlfile_01.ctl' scope=spfile sid='*';

SQL> shut immediate;

SQL>startup nomount

SQL> show parameter control_file

RMAN> restore controlfile from '+DATAC2/TESTDB/CONTROLFILE/current.268.1202816633';

RMAN> alter database mount;

alter database mount;

RMAN> alter database open;

alter database open;

SQL> alter system set db_create_file_dest='+DATAC3' scope=both sid='*';

SQL> alter system set db_create_online_log_dest_1='+DATAC3' scope=both sid='*';

Step3: Move online redo log files and temp files to new ASM DG 

Get information regarding redo logs 

set lines 300

column REDOLOG_FILE_NAME format a60

SELECT

 a.GROUP#,

 a.THREAD#,

 a.SEQUENCE#,

 a.ARCHIVED,

 a.STATUS,

 b.MEMBER AS REDOLOG_FILE_NAME,

 (a.BYTES/1024/1024) AS SIZE_MB

FROM v$log a

JOIN v$logfile b ON a.Group#=b.Group#

ORDER BY a.GROUP# ;

Online redo logs cannot be moved directly. The recommended approach is to add new redo log groups on the target ASM disk groups and then drop the old ones.

SQL> alter database add logfile thread 1 group 11 ('+DATAC3','+RECOC3') size 512M;

SQL> alter database drop logfile group 1;

Moving temporary files requires special handling, as tempfiles cannot be moved directly. The only supported approach is to drop and recreate the temporary tablespaces.

This will show you the current tempfiles and their associated temporary tablespaces

SQL>select file_name from dba_temp_files order by 1

SQL> select distinct tablespace_name from dba_temp_files;

The default temporary tablespace cannot be dropped directly. To proceed, you must first create an alternate temporary tablespace (for example, TEMP2) and set it as the default.

SQL>CREATE TEMPORARY TABLESPACE temp2 TEMPFILE '+DATAC3' SIZE 15G;

SQL>ALTER DATABASE DEFAULT TEMPORARY TABLESPACE temp2;

SQL> drop tablespace TEMP including contents and datafiles;

SQL> create temporary tablespace temp tempfile '+DATAC3' size 15G;

SQL> drop tablespace TEMP2 including contents and datafiles;

Step4: lastly , don't forget to move SPFILE and password file . You have already found the location of spfile and password file earlier  using - srvctl config database -d TESTDB

ASMCMD > pwcopy --dbuniquename TESTDB +DATAC2/TESTDB /PASSWORD/pwdbipuat.293.1201771211 +DATAC3/TESTDB /PASSWORD/pwdbTESTDB.ora -f

You need to update database config as  below and verify.

#srvctl modify database -d TESTDB -spfile +DATAC3/TESTDB /PARAMETERFILE/spfileTESTDB.ora -pwfile +DATAC3/TESTDB /PASSWORD/pwdbTESTDB.ora

Convert Physical Standby To Snapshot Standby and Revert

Oracle Snapshot Database is a fully writable point-in-time clone of a physical standby, ideal for testing and reporting. Snapshot database uses flashback logs to revert to physical standby.

Please note that converting a physical standby to a snapshot standby can be performed with minimal manual effort by using DGMGRL.

Scenario 1: When DG broker is not setup.

-- Check database role , flashback status and cancel the MRP process

SQL> select name, open_mode, database_role from v$database;

SQL> select flashback_on from v$database;

SQL> alter database recover managed standby database cancel;

SQL> alter database flashback on;

-- Convert standby database to snapshot database.

SQL> alter database convert to snapshot standby;

SQL> alter database open;

SQL> select name, open_mode, database_role from v$database;

SQL> alter pluggable database all open read write instances=all;

--Restart the database

srvctl stop database -d DRDBNAME

srvctl start database -d DRDBNAME

sqlplus "/as sysdba"

alter pluggable database all open read write instances=all;

-- how to revert snapshot standby database to physical standby

In Standby database ,bounce and mount the database.

( This is for RAC setup . In case, you have single instance database ignore below, just mount the database)

Open the database in Mount mode on only one instance and shutdown in all other nodes before executing convert to physical standby statement.

srvctl stop database -d DRDBNAME

srvctl start instance -d DRDBNAME -i DRDBNAME1 -o mount

SQL> alter database convert to physical standby;

--Bounce the database,

srvctl stop database -d DRDBNAME

srvctl start database -d DRDBNAME -o mount

SQL> select status,instance_name,database_role,open_mode from v$database,gv$Instance;

--And don't forget to start the Media recovery process

SQL> alter database recover managed standby database disconnect from session;

====================================

Scenario 2: When DG broker is enabled.

SQL> select name, open_mode, database_role,flashback_on from v$database;
NAME           OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
--------- -------------------- ---------------- ------------------
DRDBNAME         MOUNTED              PHYSICAL STANDBY YES

host>dgmgrl sys@DRDBNAME

DGMGRL> show configuration;
Configuration - prdcbrm
  Protection Mode: MaxPerformance
  Members:
  prdbname      - Primary database
  drdbname       - Physical standby database 
Fast-Start Failover:  Disabled
Configuration Status:
SUCCESS   (status updated 40 seconds ago)

DGMGRL> convert database drdbname to snapshot standby;

Converting database "drdbname " to a Snapshot Standby database, please wait...

Database "drdbname " converted successfully

--- verify from SQL prompt 

SQL> select name, open_mode, database_role,flashback_on from v$database;
NAME      OPEN_MODE            DATABASE_ROLE    FLASHBACK_ON
--------- -------------------- ---------------- ------------------
DRDBNAME   READ WRITE           SNAPSHOT STANDBY YES

SQL> alter pluggable database all open read write instances=all;

--Once activity completes in snapshot database ( e.g. application POC , expdp etc etc) , you can revert the database role to physical standby. Just connect to DGMGRL and fire -

DGMGRL> CONVERT DATABASE drdbname to PHYSICAL STANDBY;

Converting database "drdbname" to a Physical Standby database, please wait...

Operation requires a connection to database "prdbname"

Connecting ...

Connected to "prdbname"

Connected as SYSDBA.

Oracle Clusterware is restarting database "drdbname" ...

Connected to "drdbname"

Continuing to convert database "drdbname" ...

Database "drdbname" converted successfully

And its all done ! Enjoy !

WhoAmI

I’m a database guy with 15+ years of convincing Oracle databases to behave (mostly).

By day, I tune queries, break performance myths, and chase alert logs like a detective.
By night, I’m a beginner in AI, teaching machines while they quietly judge me back.
I blog about DBA life, DBA-to-AI adventures, and the occasional tech humour .

If it involves data, clouds, or learning something new the hard way — I would love to hear about it.

Create ,drop, clone pluggable databases and solve wallet issue

 Case1 : DB files are in ASM storage

Verify the default location where database files are configured to be created.

SQL>Show parameter db_create_file_dest

If the PDB already exists, it must be dropped before proceeding.

SQL>alter session set container=CDB$ROOT;

SQL>ALTER PLUGGABLE DATABASE MYPDB close immediate instances=ALL;

SQL>DROP PLUGGABLE DATABASE MYPDB  INCLUDING DATAFILES;

If this is the first time the PDB is being created, follow the steps outlined below.

SQL>CREATE PLUGGABLE DATABASE MYPDB ADMIN USER admin IDENTIFIED BY  <password>;

SQL>ALTER PLUGGABLE DATABASE MYPDB open instances = all;

It is important to save the PDB state; otherwise, the PDB may start in the MOUNTED state each time the container database is restarted.

SQL>ALTER PLUGGABLE DATABASE MYPDB save state instances = all;

SQL>alter session set container= MYPDB ;

Check default tablespaces exists or not ? If USERS or TEMP tablespaces don't exists ,please create as below.

SQL> select distinct tablespace_name from dba_data_files;

SQL>CREATE TABLESPACE "USERS" DATAFILE SIZE 10M REUSE AUTOEXTEND ON MAXSIZE UNLIMITED;

=============================

Case2 : When DB files are stored in filesystem 

Please change file location as per your environment. 

Note here - I am using PDB$SEED . It acts as a template for creating new pluggable databases (PDBs). 

CREATE PLUGGABLE DATABASE MYPDB ADMIN USER admin IDENTIFIED BY "*****" DEFAULT TABLESPACE "USERS" DATAFILE '/u01/app/oracle/oradata/BSSOSSCDB/MYPDB/users01.dbf' SIZE 100M REUSE AUTOEXTEND ON file_name_convert = ('/u01/app/oracle/oradata/BSSOSSCDB/pdbseed/','/u01/app/oracle/oradata/BSSOSSCDB/MYPDB/');

ALTER PLUGGABLE DATABASE MYPDB open instances = all;

ALTER PLUGGABLE DATABASE MYPDB save state instances = all;

===================================

Case3 : New tablespace  creation failing in PDB due to TDE configuration.

In some scenarios, creating a new tablespace in a PDB may fail because Transparent Data Encryption (TDE) is configured at the container (CDB) level.

To verify the wallet status, connect to the root container and run the following SQL:

alter session set container=CDB$ROOT;

SET LINESIZE 200

COLUMN wrl_parameter FORMAT A50

SELECT * FROM v$encryption_wallet; 

If the encryption wallet is not open, you must open it using the TDE master key password. Once the wallet is opened, you will be able to create new database objects, including tablespaces, in the PDB.

alter session set container=MYPDB;

ADMINISTER KEY MANAGEMENT SET KEY USING TAG 'TAG_NAME' FORCE KEYSTORE IDENTIFIED BY Master_Key_pwd WITH BACKUP ;

=====================================

Case 4: Clone an Existing PDB to a New PDB with All Datafiles

An existing pluggable database (PDB) can be cloned to a new PDB within the same container database for proof-of-concept (PoC) purposes. This cloning method copies all datafiles from the source PDB.

Please note that this operation requires downtime for the source PDB, as the source PDB must be closed during the cloning process.

SQL> alter pluggable database MYPDB close immediate instances = all;

SQL> alter pluggable database MYPDB open read only instances = all;

SQL> CREATE PLUGGABLE DATABASE MYPDBNEW FROM MYPDB;

######In case wallet is configured

SQL> CREATE PLUGGABLE DATABASE MYPDBNEW FROM MYPDB keystore identified by "Master_Key_pwd";

SQL>  alter pluggable database MYPDB close immediate instances = all;

SQL>  alter pluggable database MYPDB open read write instances = all;

SQL> alter pluggable database MYPDBNEW open read write instances = all;

SQL> ALTER PLUGGABLE DATABASE MYPDBNEW save state instances = all; 

=====================================

Case 5: PDBs are hosted in file system

alter pluggable database MYPDB close immediate instances = all;

alter pluggable database MYPDB open read only instances = all;

SQL> CREATE PLUGGABLE DATABASE PINPDB2 FROM PINPDB1 file_name_convert = ('/u01/app/oracle/oradata/BSSTMP/MYPDB/','/u01/app/oracle/oradata/BSSTMP/MYPDBNEW/');

alter pluggable database MYPDB close immediate instances = all;

alter pluggable database MYPDB open read write instances = all;

alter pluggable database MYPDBNEW open read write instances = all;

ALTER PLUGGABLE DATABASE MYPDBNEW save state instances = all;