Migrating a 19c Non-ASM PDB Database to ASM (Single Node RAC)
The first step in the migration process is to identify the locations of the following database components:
- Parameter files (SPFILE/PFILE)
- Datafiles
- Tempfiles
- Redo log files
Step 1: Move Parameter Files
Before migrating the database, identify and relocate the necessary parameter files. Run the following queries to check the current file locations:
SELECT name FROM v$controlfile; — Check control file locations
SELECT name FROM v$datafile; — List all datafile locations
SELECT name FROM v$tempfile; — Identify tempfile locations
SELECT member FROM v$logfile; — Retrieve redo log file members
SELECT name FROM v$controlfile;
Select name from v$datafile;
Select member from v$logfile;
Select name from v$tempfile;
Step 2: Migrating SPFILE from Non-ASM to ASM
To migrate the SPFILE from a file system to ASM, follow these steps:
1. Verify the Current SPFILE/PFILE Location
Run the following command to check the current parameter file location:
SHOW PARAMETER pfile;
If the database is using an SPFILE, the command will return its location. Otherwise, it indicates the use of a PFILE.
2. Connect to RMAN
Launch RMAN and establish a connection to the target database:
rman target /
3. Create a Backup of the SPFILE
Execute the following command in RMAN to back up the SPFILE:
BACKUP AS BACKUPSET SPFILE;
Error: (You might face the permission error)
Resolution:
Provide correct privilages;
[root@oracle bin]# chmod 6751 /u01/app/19.0.0/grid/bin/oracle
Step 3: Create a New PFILE and Move the Existing SPFILE
To migrate the SPFILE, follow these steps:
1. Generate a PFILE from the Existing SPFILE
Run the following SQL command:
CREATE PFILE FROM SPFILE;
This creates a text-based parameter file (PFILE) from the current SPFILE.
2. Rename the Existing SPFILE
Move the current SPFILE as a backup:
mv spfileorcl.ora spfileorcl.ora_old
3. Update the PFILE with the New SPFILE Location
Edit the newly created PFILE using a text editor:
vi initorcl.ora
Add the following entry in the PFILE to point to the new SPFILE location in ASM:
SPFILE='+DATA/spfileorcl.ora'
Step 7: Restart the Database and Verify the SPFILE Location
1. Restart (Bounce) the Database
Shut down and restart the database to apply changes:
SHUTDOWN IMMEDIATE;
STARTUP;
2. Verify the New SPFILE Location
Run the following command to confirm that the database is now using the SPFILE from ASM:
SHOW PARAMETER spfile;
Step 8: Move Control Files to ASM
1. Connect to RMAN
Launch RMAN and connect to the target database:
rman target /
2. Restore Control Files to ASM
Execute the following commands to move control files from the file system to ASM:
RESTORE CONTROLFILE TO ‘+DATA’ FROM ‘/u01/app/oracle/oradata/ORCL/controlfile/o1_mf_mxv63lv2_.ctl’;
RESTORE CONTROLFILE TO ‘+DATA’ FROM ‘/u01/app/oracle/fast_recovery_area/ORCL/controlfile/o1_mf_mxv63lvy_.ctl’;
3. Mount the Database and Update Control File Parameter
Connect to SQL*Plus:
sqlplus / as sysdba
Ensure the database is in NOMOUNT mode:
SHUTDOWN IMMEDIATE;
STARTUP NOMOUNT;
ALTER SYSTEM SET CONTROL_FILES=’+DATA/ORCL/CONTROLFILE/current.258.1196616523′,’+DATA/ORCL/CONTROLFILE/current.259.1196616667′ SCOPE=SPFILE;
SHUTDOWN IMMEDIATE;
STARTUP MOUNT;
Step 4: Move All Datafiles to ASM
1. Take a Backup of Datafiles
Before migrating datafiles to ASM, create a backup using RMAN:
rman target /
Run the following command to back up the entire database as image copies in ASM:
BACKUP AS COPY DATABASE FORMAT '+DATA';
RMAN: Switch Database to copy;
Step 5: Switch Database to ASM Copies
After backing up the datafiles to ASM, instruct RMAN to switch the database to use these copies.
1. Connect to RMAN
rman target /
2. Switch the Database to ASM Copies
SWITCH DATABASE TO COPY;
3. Verify the Datafile Locations
To confirm that all datafiles are now in ASM, run:
SELECT name FROM v$datafile;
Step 6: Migrate Tempfiles to ASM
1. Connect to RMAN
rman target /
2. Move Tempfiles to ASM
Run the following RMAN block to relocate tempfiles from the file system to ASM:
RUN {
SET NEWNAME FOR TEMPFILE ‘/u01/app/oracle/oradata/ORCL/datafile/o1_mf_temp_mxv63xkl_.tmp’ TO ‘+DATA’;
SET NEWNAME FOR TEMPFILE ‘/u01/app/oracle/oradata/ORCL/datafile/temp012025-03-21_22-31-20-316-PM.dbf’ TO ‘+DATA’;
SET NEWNAME FOR TEMPFILE ‘/u01/app/oracle/oradata/ORCL/30DEE200DAB0101FE0658AE675483B1A/datafile/o1_mf_temp_mxv9p612_.dbf’ TO ‘+DATA’;
SWITCH TEMPFILE ALL;
}
3. Verify the Tempfile Location
After the migration, confirm that the tempfiles are now in ASM by running:
SELECT name FROM v$tempfile;
Now Bounce the database and check: –
Select name from v$tempfile;
Step 7: Migrate Redo Log Files to ASM
1. Check Existing Redo Log File Locations
Run the following SQL query to list the current redo log files:
SELECT GROUP#, MEMBER, STATUS FROM v$logfile;
Step 8: Migrate Redo Log Files to ASM
1. Add New Redo Log File Members in ASM
Run the following commands to add new redo log members in ASM for each group:
ALTER DATABASE ADD LOGFILE MEMBER ‘+DATA’ TO GROUP 1;
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE ADD LOGFILE MEMBER ‘+DATA’ TO GROUP 2;
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE ADD LOGFILE MEMBER ‘+DATA’ TO GROUP 3;
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE ADD LOGFILE MEMBER ‘+DATADISK’ TO GROUP 1;
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE ADD LOGFILE MEMBER ‘+DATADISK’ TO GROUP 2;
ALTER SYSTEM SWITCH LOGFILE;
ALTER DATABASE ADD LOGFILE MEMBER ‘+DATADISK’ TO GROUP 3;
ALTER SYSTEM SWITCH LOGFILE;
2. Verify the New Redo Log Files
After adding the new members, confirm their locations by running:
SELECT GROUP#, MEMBER, STATUS FROM v$logfile;
3. Drop Old Redo Log Files from the File System
Once the new log files are active and verified, remove the old ones:
ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/ORCL/redo01.log';
ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/ORCL/redo02.log';
ALTER DATABASE DROP LOGFILE MEMBER '/u01/app/oracle/oradata/ORCL/redo03.log';
4. Verify the Redo Log File Changes
Run the following command again to ensure only ASM-based log files remain:
SELECT GROUP#, MEMBER, STATUS FROM v$logfile;
Database Migration to ASM is Complete!
All database components, including SPFILE, control files, datafiles, tempfiles, and redo log files, have been successfully migrated to ASM.