• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar

The Geek Diary

CONCEPTS | BASICS | HOWTO

  • OS
    • Linux
    • CentOS/RHEL
    • Solaris
    • Oracle Linux
    • Linux Services
    • VCS
  • Database
    • oracle
    • oracle 12c
    • ASM
    • mysql
    • MariaDB
    • Data Guard
  • DevOps
    • Docker
    • Shell Scripting
  • Interview Questions
  • Big Data
    • Hadoop
    • Cloudera
    • Hortonworks HDP

How to rename database and then move datafile online in Oracle Database 12.2

By admin

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.

Filed Under: oracle, oracle 12c

Some more articles you might also be interested in …

  1. How to reclaim entire space of an oracle database table with “Truncate Table” statement
  2. How to Disable AUTOEXTEND Mode on a datafile in Oracle Database
  3. Troubleshooting Common ORA-1157 Errors (cannot identify/lock data file)
  4. How To Shrink A Temporary Tablespace in Oracle Database
  5. Oracle – How To Check if Autoextensible Datafiles Set To Maxsize Unlimited
  6. What Is Oracle Key Vault
  7. ORA-01031 When Compiling A Synonym
  8. How to relocate or move oracle database files using RMAN
  9. Beginners Guide to RMAN compression for backups
  10. Oracle Database: How To Use PROFILES To Limit User Resources

You May Also Like

Primary Sidebar

Recent Posts

  • SQL script to find tables that are fragmented
  • TRUNCATE TABLE not releasing space from tablespace
  • How to reclaim entire space of an oracle database table with “Truncate Table” statement
  • Oracle SQL Script to Report Tablespace Free and Fragmentation
  • Archives
  • Contact Us
  • Copyright

© 2021 · The Geek Diary