本文主要是介绍12C 新特性，MOVE DATAFILE 在线移动 包括system， 附带改名 NID ，cdb_data_files视图坏了，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
ALTER DATABASE MOVE DATAFILE 可以改名 可以move file，全部一个命令。 resue 可以重用，keep好像不生效！！！
system照移动不误--------
SQL> select file_name, status, online_status from dba_data_files where tablespace_name='SYSTEM';
FILE_NAME
--------------------------------------------------------------------------------
STATUS ONLINE_
--------- -------
+DATA/CDB/DATAFILE/system.360.1179031227
AVAILABLE SYSTEM
SQL> ALTER DATABASE MOVE DATAFILE '+DATA/CDB/DATAFILE/system.360.1179031227' to '+DATA1';
Database altered.
SQL> select file_name, status, online_status from dba_data_files where tablespace_name='SYSTEM';
FILE_NAME
--------------------------------------------------------------------------------
STATUS ONLINE_
--------- -------
+DATA1/CDB/DATAFILE/system.273.1179053405
AVAILABLE SYSTEM
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
[oracle@rac3 rman_backup]$ rman target /
Recovery Manager: Release 19.0.0.0.0 - Production on Sat Sep 7 10:50:49 2024
Version 19.20.0.0.0
Copyright (c) 1982, 2019, Oracle and/or its affiliates. All rights reserved.
rconnected to target database: CDB (DBID=2265125454)
RMAN> eport schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name CDB
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 1180 SYSTEM YES +DATA1/CDB/DATAFILE/system.273.1179053405
3 1000 SYSAUX NO +DATA/CDB/DATAFILE/sysaux.363.1179031173
4 615 UNDOTBS1 YES +DATA/CDB/DATAFILE/undotbs1.267.1179031137
5 510 PDB$SEED:SYSTEM NO +DATA/CDB/0633F844101D69CBE0636401A8C09D55/DATAFILE/system.264.1179031187
6 480 PDB$SEED:SYSAUX NO +DATA/CDB/0633F844101D69CBE0636401A8C09D55/DATAFILE/sysaux.274.1179031151
7 28 USERS NO +DATA/CDB/DATAFILE/users.269.1179031137
8 215 PDB$SEED:UNDOTBS1 NO +DATA/CDB/0633F844101D69CBE0636401A8C09D55/DATAFILE/undotbs1.261.1179031145
9 250 UNDOTBS2 YES +DATA/CDB/DATAFILE/undotbs2.284.1179031141
10 75 UNDOTBS3 YES +DATA/CDB/DATAFILE/undotbs3.268.1179031141
11 540 PDB:SYSTEM NO +DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/system.266.1179031203
12 490 PDB:SYSAUX NO +DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/sysaux.270.1179031159
13 215 PDB:UNDOTBS1 NO +DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undotbs1.265.1179031165
14 215 PDB:UNDO_3 NO +DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undo_3.282.1179031135
15 215 PDB:UNDO_4 NO +DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/undo_4.258.1179031133
16 5 PDB:USERS NO +DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/users.347.1179031135
20 100 TEST NO +DATA1/CDB/DATAFILE/test.277.1179053323
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 44 TEMP 32767 +DATA/CDB/TEMPFILE/temp.263.1173663343
2 36 PDB$SEED:TEMP 32767 +DATA/CDB/0633F844101D69CBE0636401A8C09D55/TEMPFILE/temp.275.1179031289
3 100 PDB:TEMP 32767 +DATA/CDB/06344F62B8C65A17E0636401A8C0F073/TEMPFILE/temp.293.1179031289
RMAN>
目标
在这个版本，可以在线移动一个正在被访问的数据文件；就算是system表空间中的数据文件也可以。
可以在线移动数据文件，表示当用户正在访问系统的时候，很多维护操作可以在线执行，例如，将数据文件移动到其他存储设备或者移动到Oracle ASM。这确保了服务的连续性，并且满足正常运行时的服务水平协议（SLA）。
解决方案
在 12C，当数据文件处于在线状态并且正在被访问的时候，可以执行下面的操作：1. 重命名在线数据库文件 2. 迁移在线数据库文件 3. 拷贝在线数据文件 4. 迁移在线数据文件并且覆盖现有文件 5. 迁移在线数据文件到 oracle ASM以下是每一个操作的例子：1. 重命名在线数据库文件： ===============================
SQL> CREATE TABLESPACE test DATAFILE '/bugmnt/em/app/oracle/oradata/<SID>/test.dbf' SIZE 50M
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO; 2 3
Tablespace created.
SQL> select file_name, status, online_status from dba_data_files;
FILE_NAME
----------------------------------------------------------------------------------------------------
STATUS ONLINE_
--------- -------
/bugmnt/em/app/oracle/oradata/<SID>/system01.dbf
AVAILABLE SYSTEM
/bugmnt/em/app/oracle/oradata/<SID>/sysaux01.dbf
AVAILABLE ONLINE
/bugmnt/em/app/oracle/oradata/<SID>/undotbs01.dbf
AVAILABLE ONLINE
/bugmnt/em/app/oracle/oradata/<SID>/users01.dbf
AVAILABLE ONLINE
/bugmnt/em/app/oracle/oradata/<SID>/test.dbf
AVAILABLE ONLINE
SQL> ALTER DATABASE MOVE DATAFILE '/bugmnt/em/app/oracle/oradata/<SID>/test.dbf'
TO '/bugmnt/em/app/oracle/oradata/<SID>/test_renamed.dbf';
Database altered.
SQL> select file_name, status, online_status from dba_data_files where file_name='/bugmnt/em/app/oracle/oradata/<SID>/test_renamed.dbf';
FILE_NAME
----------------------------------------------------------------------------------------------------
STATUS ONLINE_
--------- -------
/bugmnt/em/app/oracle/oradata/<SID>/test_renamed.dbf
AVAILABLE ONLINE
2. 迁移在线数据库文件： ===================================
SQL> ALTER DATABASE MOVE DATAFILE '/bugmnt/em/app/oracle/oradata/<SID>/test_renamed.dbf'
TO '/bugmnt/em/app/oracle/oradata/test_renamed.dbf';
Database altered.
SQL> select file_name, status, online_status from dba_data_files where file_name='/bugmnt/em/app/oracle/oradata/<SID>/test_renamed.dbf';
no rows selected
SQL> select file_name, status, online_status from dba_data_files;
FILE_NAME
----------------------------------------------------------------------------------------------------
STATUS ONLINE_
--------- -------
/bugmnt/em/app/oracle/oradata/<SID>/system01.dbf
AVAILABLE SYSTEM
/bugmnt/em/app/oracle/oradata/<SID>/sysaux01.dbf
AVAILABLE ONLINE
/bugmnt/em/app/oracle/oradata/<SID>/undotbs01.dbf
AVAILABLE ONLINE
/bugmnt/em/app/oracle/oradata/<SID>/users01.dbf
AVAILABLE ONLINE
/bugmnt/em/app/oracle/oradata/test_renamed.dbf
AVAILABLE ONLINE
3. 拷贝在线数据文件： =================================
SQL> ALTER DATABASE MOVE DATAFILE '/bugmnt/em/app/oracle/oradata/<SID>/test_renamed.dbf'
TO '/bugmnt/em/app/oracle/oradata/test_renamed.dbf' keep;
Database altered.
SQL> select file_name, status, online_status from dba_data_files where file_name='/bugmnt/em/app/oracle/oradata/<SID>/test_renamed.dbf';
no rows selected
SQL> select file_name, status, online_status from dba_data_files;
FILE_NAME
----------------------------------------------------------------------------------------------------
STATUS ONLINE_
--------- -------
/bugmnt/em/app/oracle/oradata/<SID>/system01.dbf
AVAILABLE SYSTEM
/bugmnt/em/app/oracle/oradata/<SID>/sysaux01.dbf
AVAILABLE ONLINE
/bugmnt/em/app/oracle/oradata/<SID>/undotbs01.dbf
AVAILABLE ONLINE
/bugmnt/em/app/oracle/oradata/<SID>/users01.dbf
AVAILABLE ONLINE
/bugmnt/em/app/oracle/oradata/test_renamed.dbf
AVAILABLE ONLINE
-- 期望看到：
-- /bugmnt/em/app/oracle/oradata/<SID>/test_renamed.dbf
-- AVAILABLE ONLINE
-- 但是，当试图将数据文件移动至/bugmnt/em/app/oracle/oradata/<SID> 的时候，会看到下面的错误
-- 这证明数据文件在原来的路径是确实存在的：
SQL> ALTER DATABASE MOVE DATAFILE '/bugmnt/em/app/oracle/oradata/test_renamed.dbf'
TO '/bugmnt/em/app/oracle/oradata/<SID>/test_renamed.dbf'; 2
ALTER DATABASE MOVE DATAFILE '/bugmnt/em/app/oracle/oradata/test_renamed.dbf'
*
ERROR at line 1:
ORA-01119: error in creating database file
'/bugmnt/em/app/oracle/oradata/<SID>/test_renamed.dbf'
ORA-27038: created file already exists
Additional information: 1
4. 迁移在线数据文件并且覆盖现有文件： =====================================================================
SQL> ALTER DATABASE MOVE DATAFILE '/bugmnt/em/app/oracle/oradata/test_renamed.dbf'
TO '/bugmnt/em/app/oracle/oradata/<SID>/test_renamed.dbf' REUSE;
Database altered.
SQL> select file_name, status, online_status from dba_data_files where file_name='/bugmnt/em/app/oracle/oradata/<SID>/test_renamed.dbf';
FILE_NAME
----------------------------------------------------------------------------------------------------
STATUS ONLINE_
--------- -------
/bugmnt/em/app/oracle/oradata/<SID>/test_renamed.dbf
AVAILABLE ONLINE
SQL> select file_name, status, online_status from dba_data_files;
FILE_NAME
----------------------------------------------------------------------------------------------------
STATUS ONLINE_
--------- -------
/bugmnt/em/app/oracle/oradata/<SID>/system01.dbf
AVAILABLE SYSTEM
/bugmnt/em/app/oracle/oradata/<SID>/sysaux01.dbf
AVAILABLE ONLINE
/bugmnt/em/app/oracle/oradata/<SID>/undotbs01.dbf
AVAILABLE ONLINE
/bugmnt/em/app/oracle/oradata/<SID>/users01.dbf
AVAILABLE ONLINE
/bugmnt/em/app/oracle/oradata/<SID>/test_renamed.dbf
AVAILABLE ONLINE
5. 迁移在线数据文件到 oracle ASM 的例子： ================================================
在下面的例子中，将数据文件 user1.dbf 从/u01/oracle/rbdb1/ 移动至 Oracle ASM 。
ALTER DATABASE MOVE DATAFILE '/u01/oracle/rbdb1/user1.dbf' TO '+dgroup_01/data/orcl/datafile/user1.dbf';在下面的例子中，将数据文件从一个 Oracle ASM 位置，移动到另一个 Oracle ASM 位置。
ALTER DATABASE MOVE DATAFILE '+dgroup_01/data/orcl/datafile/user1.dbf' TO '+dgroup_02/data/orcl/datafile/user1.dbf';
--------------------------
APPLIES TO:
Oracle Database - Enterprise Edition - Version 12.2.0.1 to 12.2.0.1 [Release 12.2]
Oracle Database Exadata Cloud Machine - Version N/A and later
Oracle Cloud Infrastructure - Database Service - Version N/A and later
Oracle Database Cloud Exadata Service - Version N/A and later
Oracle Database Exadata Express Cloud Service - Version N/A and later
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
--------------863800.1 to perform preparing task of renaming the database
GOAL
The goal of this note is to describe how to change DBID AND/OR DB_NAME of a database.
Note:
Changing the DBID of a database is a serious procedure.
When the DBID of a database is changed, all previous backups and archived logs of the database become unusable. This is similar to creating a database except that the data is already in the datafiles. After you change the DBID, backups and archive logs that were created prior to the change can no longer be used because they still have the original DBID, which does not match the current DBID. You must open the database with the RESETLOGS option, which re-creates the online redo logs and resets their sequence to 1 (see the Oracle Database Administrator's Guide). Consequently, you should make a backup of the whole database immediately after changing the DBID.
If you change DBID at Primary in a Standby Environment, the STANDBY Database must be rebuild.
Without Standby recreated, you will see messages like this example in Alert.log:
.
Standby database ID mismatch [0x80a847ae:0x396b3b92] (2158512046:963328914)
Mon Apr 07 12:18:26 2014
Standby database ID mismatch [0x80a847ae:0x396b3b92] (2158512046:963328914)
.
Changing the DBNAME without changing the DBID does not require you to open with the RESETLOGS option, so database backups and archived logs are not invalidated. However, changing the DBNAME does have consequences. You must change the DB_NAME initialization parameter after a database name change to reflect the new name. Also, you may have to re-create the Oracle password file. If you restore an old backup of the control file (before the name change), then you should use the initialization parameter file and password file from before the database name change.
For RAC Databases, ensure that you shutdown all instances, start only one instance with CLUSTER_DATABASE=FALSE and perform below activities. After the activity is performed, start all rac instances normally.
This procedure can also be executed to change the name of a container database.
SOLUTION
Changing the DBID and Database Name (DB_NAME)
1. Ensure you have valid cold/hot backup of database. If it's a hot backup (RMAN or OS), ensure you also have all the archived logs and backup of all Online Redo logs (after database is consistently shutdown).
2. export ORACLE_HOME=<Path of the Oracle Home> -- Unix and Linux
set ORACLE_HOME=<Path of the Oracle Home> -- Windows
3. cd <Oracle Home>/bin
4. Drop the dbconsole :
Refer to the following note for more information:
Note.278100.1 How To Drop, Create And Recreate DB Control In A 10g Database.
5. Bring the database to the Mount stage:
SQL> STARTUP MOUNT
6. If you need to use TNS to connect to database, make sure that you have DB_OLD specified in the tnsnames.ora and listener is started.
You can also check if you able to connect to the database using sqlplus :
$ sqlplus sys/<password>@DB_OLD
7. Issue following command to invoke NID utility:
$ nid TARGET=SYS/<password>@<service> DBNAME=<NEW DB name>
or
$ nid TARGET=SYS/<password> DBNAME=<NEW DB name>
The DBNEWID utility performs validations in the headers of the datafiles and control files before attempting I/O to the files. If validation is successful, then DBNEWID prompts you to confirm the operation (unless you specify a log file, in which case it does not prompt), changes the DBID (and the DBNAME, if specified, as in this example) for each datafile, including offline normal and read-only datafiles, shuts down the database, and then exits.
The following is an example of what the output for this would look like:
DBNEWID: Release 10.2.0.4.0 - Production on Mon Jul 27 18:29:06 2009
Copyright (c) 1982, 2007, Oracle. All rights reserved.
Connected to database <OLD DB Name> (DBID=729333573)
Connected to server version 10.2.0
Control Files in database:
D<PATH>\CONTROL01.CTL
D<PATH>\CONTROL02.CTL
D<PATH>\CONTROL03.CTL
Change database ID and database name <OLD DB Name> to <NEW DB Name>? (Y/[N]) => Y
Proceeding with operation
Changing database ID from 729333573 to 2473929266
Changing database name from <OLD DB Name> to <NEW DB Name>
Control File D:<PATH>\CONTROL01.CTL - modified
Control File D:<PATH>\CONTROL02.CTL - modified
Control File D:<PATH>\CONTROL03.CTL - modified
Datafile D:<PATH>\SYSTEM01.DBF - dbid changed, wrote new name
Datafile D:<PATH>\UNDOTBS01.DBF - dbid changed, wrote new name
Datafile D:<PATH>\SYSAUX01.DBF - dbid changed, wrote new name
Datafile D:<PATH>\USERS01.DBF - dbid changed, wrote new name
Datafile D:<PATH>\EXAMPLE01.DBF - dbid changed, wrote new name
Datafile D:<PATH>\TEST.DBF - dbid changed, wrote new name
Datafile D:<PATH>\TEMP01.DBF - dbid changed, wrote new name
Datafile D:<PATH>\TEMP011.DBF - dbid changed, wrote new name
Control File D:<PATH>\CONTROL01.CTL - dbid changed, wrote new name
Control File D:<PATH>\CONTROL02.CTL - dbid changed, wrote new name
Control File D:<PATH>\CONTROL03.CTL - dbid changed, wrote new name
Instance shut down
Database name changed to <NEW DB Name>
Modify parameter file and generate a new password file before restarting.
Database ID for database <NEW DB Name> changed to 2473929266.
All previous backups and archived redo logs for this database are unusable.
Database is not aware of previous backups and archived logs in Recovery Area.
Database has been shutdown, open database with RESETLOGS option.
Succesfully changed database name and ID.
DBNEWID - Completed succesfully.
If validation is not successful, then DBNEWID terminates and leaves the target database intact, as shown in the following sample output. You can open the database, fix the error, and then either resume the DBNEWID operation or continue using the database without changing its DBID.
8. Change the DB_NAME in the pfile/spfile:
Set the DB_NAME initialization parameter in the initialization parameter file (PFILE) to the new
database name.
The NID utility does not change the server parameter file (SPFILE). Therefore, if you use SPFILE to start your Oracle database, you must re-create the initialization parameter file from the server parameter file, remove the server parameter file, change the DB_NAME in the initialization parameter file, and then re-create the server parameter file.
9. If you are using ASM and want to change the file name, follow this Article at this point
Note 564993.1 How to rename/move a datafile in the same ASM diskgroup
10. NID Utility will shutdown the database after the execution. So mount the database once again.
SQL> STARTUP MOUNT
11. Open the database in RESETLOGS mode:
SQL> ALTER DATABASE OPEN RESETLOGS;
Make a new database backup. Because you reset the online redo logs, the old backups and archived logs are no longer usable in the current incarnation of the database.
12. Recreate the password file since the DB NAME has been changed:
$ orapwd file=<fname> password=<password> entries=<users> force=<y/n> nosysdba=<y/n>
where
file - name of password file (mand),
password - password for SYS (mand),
entries - maximum number of distinct DBA,
force - whether to overwrite existing file (opt),
nosysdba - whether to shut out the SYSDBA logon (opt for Database Vault only).
Note: There are no spaces around the equal-to (=) character.
On Unix/Linux the passwordfile convention is : $ORACLE_HOME/dbs/orapw<$ORACLE_SID>
On MS Windows the passwordfile convention is : %ORACLE_HOME%\database\PWD<%ORACLE_SID%>.ORA
13. Change the $ORACLE_HOME/network/admin/tnsnames.ora file wherever it has the old db name.
14. If there is a static registration of the database in the listener.ora file then change the database name in the following file $ORACLE_HOME/network/admin/listener.ora.
15. Change of Global Database Names:
If you are dealing with a database in a distributed database system, then each database should have a unique global database name. The DBNEWID utility does not change global database names. This can only be done with the SQL ALTER DATABASE statement, for which the syntax is as follows:
ALTER DATABASE RENAME GLOBAL_NAME TO <newname>.<domain>;
The global database name is made up of a database name and a domain, which are determined by the DB_NAME and DB_DOMAIN initialization parameters when the database is first created.
Windows specific steps:
16. Recreate the Database Service :
$ oradim -NEW -SID prod -STARTMODE auto -PFILE <....\init<NEW Oracle_Sid>.ora>
17. Recreate the DB Console service :
$ emca -config dbcontrol db -repos recreate
This command will ask you the new SID and will delete and recreate the service.
Refer to the following note for more information:
Note.278100.1 How To Drop, Create And Recreate DB Control In A 10g Database
Changing Only the Database ID
1-6 except 4. Follow the steps 1 to 6 (except step 4) given in above section.
7. Issue following command to invoke the NID utility:
$ nid TARGET=SYS/<password>@<service>
or
$ nid TARGET=SYS/<password>
8. NID Utility will shutdown the database after the execution. So mount the database once again.
SQL> STARTUP MOUNT
9. Open the database in RESETLOGS mode:
SQL> ALTER DATABASE OPEN RESETLOGS;
Make a new database backup. Because you reset the online redo logs, the old backups and archived logs are no longer usable in the current incarnation of the database.
Changing Only the Database Name
1-6. Follow the steps 1 to 6 given in first section.
7. Invoke the NID utility using following command
You must specify both the DBNAME and SETNAME parameters.
$ nid TARGET=SYS/<password>@<service> DBNAME=<NEW DB Name> SETNAME=YES
or
$ nid TARGET=SYS/<password> DBNAME=<NEW DB Name> SETNAME=YES
8. Change the DB_NAME in the pfile/spfile: Follow step 8 from the first section.
9. If you are using ASM and want to change the file name, follow this Article at this point
Note 564993.1 How to rename/move a datafile in the same ASM diskgroup
10. Start up the database in normal mode.
SQL> STARTUP mount
11 - 16 Follow steps 11 to 16 given in the first section as applicable.
Logs for NID :
The operation performed by NID is recorded in the alert file:
For example:
*** DBNEWID utility started ***
DBNAME will be changed from <OLD DB Name> to new DBNAME of <NEW DB Name>
Starting datafile conversion
Datafile conversion complete
Database name changed to <NEW database name>.
Modify parameter file and generate a new password file before restarting.
Successfully changed database name.
*** DBNEWID utility finished successfully ***
------------------------------------------全部过程开始---------------------------------------------------------------------
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/datafile/
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-----啥玩意这里还是ORCL122吧
'/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
NOTE:
Above error states that you need not mention the target path/filename for an OMF. So we need to perform below instead:
-----------文档也不说清楚，你得改了db_create_file_dest
我的测试
SQL> select file_name, status, online_status from dba_data_files where tablespace_name='TEST';
FILE_NAME
--------------------------------------------------------------------------------
STATUS ONLINE_
--------- -------
+DATA1/CDB/DATAFILE/test.277.1179053323
AVAILABLE ONLINE
SQL> show parameters create
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_key_vector_create_pushdown_threshol integer 20000
d
create_bitmap_area_size integer 8388608
create_stored_outlines string
db_create_file_dest string +DATA
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
SQL> alter database move datafile '+DATA1/CDB/DATAFILE/test.277.1179053323';
Database altered.
SQL> select file_name, status, online_status from dba_data_files where tablespace_name='TEST';
FILE_NAME
--------------------------------------------------------------------------------
STATUS ONLINE_
--------- -------
+DATA/CDB/DATAFILE/test.259.1179054363
AVAILABLE ONLINE
SQL>
------------------------------------
ALTER DATABASE MOVE DATAFILE '/refresh/home/app/oracle/oradata/ORCL122/datafile/o1_mf_system_dfb7twmf_.dbf'; 没头没尾也可以，前提是db_create_file_dest
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';
---------------------------pdb的要在pdb 移动也要注意参数create file--------------------
SQL> select file_name, status, online_status from cdb_data_files where tablespace_name='SYSTEM';
FILE_NAME
--------------------------------------------------------------------------------
STATUS ONLINE_
--------- -------
+DATA1/CDB/DATAFILE/system.273.1179053405
AVAILABLE SYSTEM
SQL> alter session set container=pdb;
Session altered.
SQL> select file_name, status, online_status from cdb_data_files where tablespace_name='SYSTEM';
FILE_NAME
--------------------------------------------------------------------------------
STATUS ONLINE_
--------- -------
+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/system.266.1179031203
AVAILABLE SYSTEM
SQL> show pdbs;
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 PDB READ WRITE YES
SQL> exit
Disconnected from Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
[oracle@rac3 rman_backup]$ s
SQL*Plus: Release 19.0.0.0.0 - Production on Sat Sep 7 11:09:57 2024
Version 19.20.0.0.0
Copyright (c) 1982, 2022, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.20.0.0.0
SQL> select file_name, status, online_status from cdb_data_files where tablespace_name='SYSTEM';--------------------为什么看不到pdb的system？？？
FILE_NAME
--------------------------------------------------------------------------------
STATUS ONLINE_
--------- -------
+DATA1/CDB/DATAFILE/system.273.1179053405
AVAILABLE SYSTEM
SQL> alter database move datafile '+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/system.266.1179031203';
alter database move datafile '+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/system.266.1179031203'
*
ERROR at line 1:
ORA-01516: nonexistent log file, data file, or temporary file "11" in the
current container
SQL> alter session set container=pdb;
Session altered.
SQL> alter database move datafile '+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/system.266.1179031203';
Database altered.
SQL> select file_name, status, online_status from cdb_data_files where tablespace_name='SYSTEM';
FILE_NAME
--------------------------------------------------------------------------------
STATUS ONLINE_
--------- -------
+DATA/CDB/06344F62B8C65A17E0636401A8C0F073/DATAFILE/system.287.1179054659
AVAILABLE SYSTEM
SQL>
---------------------------------------------
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.
------------cdb_data_files 看不到文件了-----------------------------
APPLIES TO:
Oracle Database - Enterprise Edition - Version 19.3.0.0.0 and later
Information in this document applies to any platform.
SYMPTOMS
DBA_DATA_FILES or CDB_DATA_FILES view does not show all data files about refreshable PDB.
SQL> alter pluggable database <Refreshable_PDB> open read only;
Pluggable database altered.
SQL> alter session set container=<Refreshable_PDB>;
Session altered.
SQL> -- Some data files are not displayed
SQL> select file_name from cdb_data_files where con_id=3;
FILE_NAME
--------------------------------------------------------------------------------
<DIR>/cdb1_pdb1_refresh_db.f
SQL> -- All data files are displayed
SQL> select name from v$datafile where con_id=3;
NAME
--------------------------------------------------------------------------------
<DIR>/cdb1_pdb1_refresh_db.f
<DIR>/cdb1_pdb1_refresh_ax.f
<DIR>/cdb1_pdb1_refresh_xdb.f
<DIR>/cdb1_pdb1_refresh_undo.dbf
CHANGES
CAUSE
The development team is still investigating this issue in Bug 30536162.
Bug 30536162 - DBA_DATA_FILES VIEW DOES NOT SHOW REFRESHABLE PDB DATA FILES
SOLUTION
If you want to get data file information about the refreshable PDB, access to v$datafile instead of DBA_DATA_FILES / CDB_DATA_FILES.
这篇关于12C 新特性，MOVE DATAFILE 在线移动 包括system， 附带改名 NID ，cdb_data_files视图坏了的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
