Rename database and then move datafile online
APPLIES TO:
Oracle Database – Enterprise Edition – Version 12.2.0.1 to 12.2.0.1 [Release 12.2]
Information in this document applies to any platform.
GOAL
This note illustrates how to rename a database in 12.2 and then move the datafiles to another location online (given the DB name is changed, we expect the datafile are moved to another directory whose directory name is the new DB name). This is for education purpose only, in case you want to do this in production system, do verify the steps in testing env first.
For more details of renaming database via nid, please refer to note 863800.1 – How to Change the DBID, DBNAME Using NID Utility, for more details of moving datafile online, please refer to note 1566797.1 – 12C New Feature : Move a Datafile Online.
SOLUTION
We will rename database from ORCL122 to CDB122, and move datafiles from /refresh/home/app/oracle/oradata/ORCL122/ to /refresh/home/app/oracle/oradata/CDB122/
1. Refer to note 863800.1 to perform preparing task of renaming the database
eg:
a). backup database
b). set ORACLE_SID to ORCL122
c). shutdown database, and then start it to mount status
SQL> shutdown immediate
SQL> startup mount
2. Rename the database via nid
/refresh/home/app/oracle/oradata/ORCL122> nid target=/ dbname=cdb122
DBNEWID: Release 12.2.0.1.0 – Production on Fri Apr 14 14:30:00 2017
Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved.
Connected to database ORCL122 (DBID=13079153)
Connected to server version 12.2.0
Control Files in database:
/refresh/home/app/oracle/oradata/ORCL122/controlfile/o1_mf_dfb7wsvs_.ctl
Change database ID and database name ORCL122 to CDB122? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 13079153 to 3343116648
Changing database name from ORCL122 to CDB122
Control File /refresh/home/app/oracle/oradata/ORCL122/controlfile/o1_mf_dfb7wsvs_.ctl – modified
Datafile /refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_system_dfb7twmf_.db – dbid changed, wrote new name
Datafile /refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_sysaux_dfb7vd61_.db – dbid changed, wrote new name
Datafile /refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_undotbs1_dfb7vv92_.db – dbid changed, wrote new name
Datafile /refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_system_dfb7x4sk_.db – dbid changed, wrote new name
Datafile /refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_sysaux_dfb7x4sh_.db – dbid changed, wrote new name
Datafile /refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_users_dfb7vwdl_.db – dbid changed, wrote new name
Datafile /refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_undotbs1_dfb7x4sl_.db – dbid changed, wrote new name
Datafile /refresh/home/app/oracle/oradata/ORCL122/4B7B16FF7034241BE053F525410A839A/datafile/o1_mf_system_dfb885l1_.db – dbid changed, wrote new name
Datafile /refresh/home/app/oracle/oradata/ORCL122/4B7B16FF7034241BE053F525410A839A/datafile/o1_mf_sysaux_dfb885nr_.db – dbid changed, wrote new name
Datafile /refresh/home/app/oracle/oradata/ORCL122/4B7B16FF7034241BE053F525410A839A/datafile/o1_mf_undotbs1_dfb885o2_.db – dbid changed, wrote new name
Datafile /refresh/home/app/oracle/oradata/ORCL122/4B7B16FF7034241BE053F525410A839A/datafile/o1_mf_users_dfb88tyc_.db – dbid changed, wrote new name
Datafile /refresh/home/app/oracle/oradata/ORCL122/4B7B1870424224B2E053F525410AEFC7/datafile/o1_mf_system_dfb88vsp_.db – dbid changed, wrote new name
Datafile /refresh/home/app/oracle/oradata/ORCL122/4B7B1870424224B2E053F525410AEFC7/datafile/o1_mf_sysaux_dfb88vss_.db – dbid changed, wrote new name
Datafile /refresh/home/app/oracle/oradata/ORCL122/4B7B1870424224B2E053F525410AEFC7/datafile/o1_mf_undotbs1_dfb88vsv_.db – dbid changed, wrote new name
Datafile /refresh/home/app/oracle/oradata/ORCL122/4B7B1870424224B2E053F525410AEFC7/datafile/o1_mf_users_dfb898v6_.db – dbid changed, wrote new name
Datafile /refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_temp_dfb7x2g8_.tm – dbid changed, wrote new name
Datafile /refresh/home/app/oracle/oradata/ORCL122/datafile/temp012017-03-24_19-01-34-291-PM.db – dbid changed, wrote new name
Datafile /refresh/home/app/oracle/oradata/ORCL122/4B7B16FF7034241BE053F525410A839A/datafile/o1_mf_temp_dfb885oc_.db – dbid changed, wrote new name
Datafile /refresh/home/app/oracle/oradata/ORCL122/4B7B1870424224B2E053F525410AEFC7/datafile/o1_mf_temp_dfb88vsy_.db – dbid changed, wrote new name
Control File /refresh/home/app/oracle/oradata/ORCL122/controlfile/o1_mf_dfb7wsvs_.ctl – dbid changed, wrote new name
Instance shut down
Database name changed to CDB122.
Modify parameter file and generate a new password file before restarting.
Database ID for database CDB122 changed to 3343116648.
All previous backups and archived redo logs for this database are unusable.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID – Completed succesfully.
d). modify spfile to rename ORCL122 to CDB122 (except controlfile location)
e). set ORACLE_SID to new one: CDB122
f). open the database with resetlogs option/or nonresetlogs option
3. create the new directories for new location of those datafiles
mkdir -p /refresh/home/app/oracle/oradata/CDB122/4B7B16FF7034241BE053F525410A839A/datafile
mkdir -p /refresh/home/app/oracle/oradata/CDB122/4B7B1870424224B2E053F525410AEFC7/datafile
4. Move datafiles to new location
SQL> select con_id,name,open_mode from v$pdbs;
CON_ID NAME OPEN_MODE
———- ——————————————————————————————————————————– ———-
2 PDB$SEED READ ONLY
3 ORCLPDB11 MOUNTED
4 ORCLPDB12 MOUNTED
SQL> select con_id, dbid, con_uid, guid, name, open_mode from v$containers;
CON_ID DBID CON_UID GUID NAME OPEN_MODE
———- ———- ———- ——————————– —————————————————————————————— ———-
1 3343116648 1 4700A987085A3DFAE05387E5E50A8C7B CDB$ROOT READ WRITE
2 4157681951 4157681951 4B7B0324135C1A5DE053F525410AD371 PDB$SEED READ ONLY
3 3845353805 3845353805 4B7B16FF7034241BE053F525410A839A ORCLPDB11 MOUNTED
4 923485889 923485889 4B7B1870424224B2E053F525410AEFC7 ORCLPDB12 MOUNTED
SQL> select con_id,file#, name FROM v$datafile order by con_id;
CON_ID FILE#
———- ———-
NAME
——————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————————–
1 1
/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_system_dfb7twmf_.dbf
1 3
/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_sysaux_dfb7vd61_.dbf
1 4
/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_undotbs1_dfb7vv92_.dbf
1 7
/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_users_dfb7vwdl_.dbf
2 5
/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_system_dfb7x4sk_.dbf
2 6
/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_sysaux_dfb7x4sh_.dbf
2 8
/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_undotbs1_dfb7x4sl_.dbf
3 9
/refresh/home/app/oracle/oradata/ORCL122/4B7B16FF7034241BE053F525410A839A/datafile/o1_mf_system_dfb885l1_.dbf
3 12
/refresh/home/app/oracle/oradata/ORCL122/4B7B16FF7034241BE053F525410A839A/datafile/o1_mf_users_dfb88tyc_.dbf
3 11
/refresh/home/app/oracle/oradata/ORCL122/4B7B16FF7034241BE053F525410A839A/datafile/o1_mf_undotbs1_dfb885o2_.dbf
3 10
/refresh/home/app/oracle/oradata/ORCL122/4B7B16FF7034241BE053F525410A839A/datafile/o1_mf_sysaux_dfb885nr_.dbf
4 15
/refresh/home/app/oracle/oradata/ORCL122/4B7B1870424224B2E053F525410AEFC7/datafile/o1_mf_undotbs1_dfb88vsv_.dbf
4 14
/refresh/home/app/oracle/oradata/ORCL122/4B7B1870424224B2E053F525410AEFC7/datafile/o1_mf_sysaux_dfb88vss_.dbf
4 13
/refresh/home/app/oracle/oradata/ORCL122/4B7B1870424224B2E053F525410AEFC7/datafile/o1_mf_system_dfb88vsp_.dbf
4 16
/refresh/home/app/oracle/oradata/ORCL122/4B7B1870424224B2E053F525410AEFC7/datafile/o1_mf_users_dfb898v6_.dbf
15 rows selected.
ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_system_dfb7x4sk_.dbf’ TO ‘/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_system_dfb7x4sk_.dbf’;
SQL> ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_system_dfb7x4sk_.dbf’ TO ‘/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_system_dfb7x4sk_.dbf’;
ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_system_dfb7x4sk_.dbf’ TO ‘/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_system_dfb7x4sk_.dbf’
*
ERROR at line 1:
ORA-01276: Cannot add file
/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_system_dfb7x4sk_.dbf.
File has an Oracle Managed Files file name
Above error states that you need not mention the target path/filename for an OMF. So we need to perform below instead:
ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_system_dfb7twmf_.dbf’;
ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_sysaux_dfb7vd61_.dbf’;
ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_undotbs1_dfb7vv92_.dbf’;
ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_users_dfb7vwdl_.dbf’;
ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_sysaux_dfb7vd61_.dbf’;
ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_undotbs1_dfb7vv92_.dbf’;
ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_users_dfb7vwdl_.dbf’;
alter session set container=ORCLPDB11;
ALTER PLUGGABLE DATABASE ORCLPDB11 RENAME FILE ‘/refresh/home/app/oracle/oradata/ORCL122/4B7B16FF7034241BE053F525410A839A/datafile/o1_mf_system_dfb885l1_.dbf’ to ‘/refresh/home/app/oracle/oradata/CDB122/4B7B16FF7034241BE053F525410A839A/datafile/o1_mf_system_dfb885l1_.dbf’;
ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/ORCL122/4B7B16FF7034241BE053F525410A839A/datafile/o1_mf_system_dfb885l1_.dbf’;
ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/ORCL122/4B7B16FF7034241BE053F525410A839A/datafile/o1_mf_users_dfb88tyc_.dbf’;
ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/ORCL122/4B7B16FF7034241BE053F525410A839A/datafile/o1_mf_undotbs1_dfb885o2_.dbf’;
ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/ORCL122/4B7B16FF7034241BE053F525410A839A/datafile/o1_mf_sysaux_dfb885nr_.dbf’;
ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/ORCL122/4B7B1870424224B2E053F525410AEFC7/datafile/o1_mf_system_dfb88vsp_.dbf’;
ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/ORCL122/4B7B1870424224B2E053F525410AEFC7/datafile/o1_mf_sysaux_dfb88vss_.dbf’;
ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/ORCL122/4B7B1870424224B2E053F525410AEFC7/datafile/o1_mf_undotbs1_dfb88vsv_.dbf’;
ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/ORCL122/4B7B1870424224B2E053F525410AEFC7/datafile/o1_mf_users_dfb898v6_.dbf’;
ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_system_dfb7x4sk_.dbf’;
ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_sysaux_dfb7x4sh_.dbf’;
ALTER DATABASE MOVE DATAFILE ‘/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_undotbs1_dfb7x4sl_.dbf’;
5. Move controlfile to new location
Shutdown database, move control file, and modify spfile accordingly
mkdir -p /refresh/home/app/oracle/oradata/CDB122/controlfile/
mv /refresh/home/app/oracle/oradata/ORCL122/controlfile/o1_mf_dfb7wsvs_.ctl /refresh/home/app/oracle/oradata/CDB122/controlfile/
7. Verify the new location of datafiles
Start database again, and run below
SQL> select con_id,file#, name FROM v$datafile order by con_id;
CON_ID FILE#
———- ———-
NAME
——————————————————————————–
1 1
/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_system_dh15r936_.dbf
1 3
/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_sysaux_dh15wkqr_.dbf
1 4
/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_undotbs1_dh1648rg_.dbf
CON_ID FILE#
———- ———-
NAME
——————————————————————————–
1 7
/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_users_dh164hqh_.dbf
2 5
/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_system_dh17311c_.dbf
2 6
/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_sysaux_dh173hb3_.dbf
CON_ID FILE#
———- ———-
NAME
——————————————————————————–
2 8
/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_undotbs1_dh174hqw_.dbf
3 9
/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_system_dh16ghsy_.dbf
3 12
/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_users_dh16k1nm_.dbf
CON_ID FILE#
———- ———-
NAME
——————————————————————————–
3 11
/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_undotbs1_dh16k97v_.dbf
3 10
/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_sysaux_dh16klo3_.dbf
4 15
/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_undotbs1_dh16x55y_.dbf
CON_ID FILE#
———- ———-
NAME
——————————————————————————–
4 14
/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_sysaux_dh16wn7o_.dbf
4 13
/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_system_dh16wfyp_.dbf
4 16
/refresh/home/app/oracle/oradata/CDB122/datafile/o1_mf_users_dh16xj2o_.dbf
15 rows selected.
show parameter create
NAME TYPE VALUE
———————————— ———– ——————————
create_bitmap_area_size integer 8388608
create_stored_outlines string
db_create_file_dest string /refresh/home/app/oracle/oradata <<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<<< OMF Path
db_create_online_log_dest_1 string
db_create_online_log_dest_2 string
db_create_online_log_dest_3 string
db_create_online_log_dest_4 string
db_create_online_log_dest_5 string
8. Recreate redo files and specify new location of REDO logfiles to /refresh/home/app/oracle/oradata/CDB122/ if it is required.
WikiDBA 