This post 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).
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. backup database.
2. set ORACLE_SID to ORCL122.
3. shutdown database, and then start it to mount status:
SQL> shutdown immediate SQL> startup mount
4. Rename the database via nid.
$ 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.
5. modify spfile to rename ORCL122 to CDB122 (except controlfile location).
6. set ORACLE_SID to new one: CDB122.
7. open the database with resetlogs option/or nonresetlogs option.
8. create the new directories for new location of those datafiles
$ mkdir -p /refresh/home/app/oracle/oradata/CDB122/datafile/ $ mkdir -p /refresh/home/app/oracle/oradata/CDB122/4B7B16FF7034241BE053F525410A839A/datafile $ mkdir -p /refresh/home/app/oracle/oradata/CDB122/4B7B1870424224B2E053F525410AEFC7/datafile
9. 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.
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'; 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'; 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' * 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
NOTE: Above error states that you need not mention the target path/filename for an OMF. So we need to perform below instead.
SQL> ALTER DATABASE MOVE DATAFILE '/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_system_dfb7twmf_.dbf'; SQL> ALTER DATABASE MOVE DATAFILE '/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_sysaux_dfb7vd61_.dbf'; SQL> ALTER DATABASE MOVE DATAFILE '/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_undotbs1_dfb7vv92_.dbf'; SQL> ALTER DATABASE MOVE DATAFILE '/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_users_dfb7vwdl_.dbf'; SQL> ALTER DATABASE MOVE DATAFILE '/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_sysaux_dfb7vd61_.dbf'; SQL> ALTER DATABASE MOVE DATAFILE '/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_undotbs1_dfb7vv92_.dbf'; SQL> ALTER DATABASE MOVE DATAFILE '/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_users_dfb7vwdl_.dbf';
SQL> alter session set container=ORCLPDB11; SQL> 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'; SQL> ALTER DATABASE MOVE DATAFILE '/refresh/home/app/oracle/oradata/ORCL122/4B7B16FF7034241BE053F525410A839A/datafile/o1_mf_system_dfb885l1_.dbf'; SQL> ALTER DATABASE MOVE DATAFILE '/refresh/home/app/oracle/oradata/ORCL122/4B7B16FF7034241BE053F525410A839A/datafile/o1_mf_users_dfb88tyc_.dbf'; SQL> ALTER DATABASE MOVE DATAFILE '/refresh/home/app/oracle/oradata/ORCL122/4B7B16FF7034241BE053F525410A839A/datafile/o1_mf_undotbs1_dfb885o2_.dbf'; SQL> ALTER DATABASE MOVE DATAFILE '/refresh/home/app/oracle/oradata/ORCL122/4B7B16FF7034241BE053F525410A839A/datafile/o1_mf_sysaux_dfb885nr_.dbf'; SQL> ALTER DATABASE MOVE DATAFILE '/refresh/home/app/oracle/oradata/ORCL122/4B7B1870424224B2E053F525410AEFC7/datafile/o1_mf_system_dfb88vsp_.dbf'; SQL> ALTER DATABASE MOVE DATAFILE '/refresh/home/app/oracle/oradata/ORCL122/4B7B1870424224B2E053F525410AEFC7/datafile/o1_mf_sysaux_dfb88vss_.dbf'; SQL> ALTER DATABASE MOVE DATAFILE '/refresh/home/app/oracle/oradata/ORCL122/4B7B1870424224B2E053F525410AEFC7/datafile/o1_mf_undotbs1_dfb88vsv_.dbf'; SQL> ALTER DATABASE MOVE DATAFILE '/refresh/home/app/oracle/oradata/ORCL122/4B7B1870424224B2E053F525410AEFC7/datafile/o1_mf_users_dfb898v6_.dbf'; SQL> ALTER DATABASE MOVE DATAFILE '/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_system_dfb7x4sk_.dbf'; SQL> ALTER DATABASE MOVE DATAFILE '/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_sysaux_dfb7x4sh_.dbf'; SQL> ALTER DATABASE MOVE DATAFILE '/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_undotbs1_dfb7x4sl_.dbf';
10. 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/
11. 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.
SQL> 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
12. Recreate redo files and specify new location of REDO logfiles to /refresh/home/app/oracle/oradata/CDB122/ if it is required.