How to Move / Rename datafile in Oracle 11g RAC – ASM ?
-- Used oracle version is 11.2.0.3 on AIX platform.
Rename or relocate of datafiles are possible in Oracle database. When you rename and relocate datafiles with these procedures, only the pointers to the datafiles, as recorded in the database control file, are changed. The procedures do not physically rename any operating system files, nor do they copy files at the operating system level. Renaming and relocating datafiles involves several steps. Read the steps and examples carefully before performing these procedures.
-- Create a tablespace and user in your test database.
SQL> create tablespace PAYROLL datafile '+DATA/prod/datafile/payroll_data.dbf' size 100M autoextend on;
Tablespace created.
SQL> grant connect,resource to payroll identified by payroll;
Grant succeeded.
-- See your tablespaces and datafiles in your database.
$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Apr 3 14:13:03 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=1981474880)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name PROD
List of Permanent Datafiles
-------------------------------
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 740 SYSTEM *** +DATA/prod/datafile/system.331.841567649
2 1310 SYSAUX *** +DATA/prod/datafile/sysaux.270.841567651
3 355 UNDOTBS1 *** +DATA/prod/datafile/undotbs1.335.841567651
4 5 USERS *** +DATA/prod/datafile/users.269.841567651
5 345 EXAMPLE *** +DATA/prod/datafile/example.279.841567765
6 650 UNDOTBS2 *** +DATA/prod/datafile/undotbs2.278.841567985
7 100 HR *** +DATA/prod/datafile/hr_data.dbf
8 100 PAYROLL *** +DATA/prod/datafile/payroll_data.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 42 TEMP 32767 +DATA/PROD/tempfile/temp.280.841567761
RMAN>
-- See all datafiles in your database
SQL> SELECT file_name FROM dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/prod/datafile/users.269.841567651
+DATA/prod/datafile/undotbs1.335.841567651
+DATA/prod/datafile/sysaux.270.841567651
+DATA/prod/datafile/system.331.841567649
+DATA/prod/datafile/example.279.841567765
+DATA/prod/datafile/undotbs2.278.841567985
+DATA/prod/datafile/hr_data.dbf
+DATA/prod/datafile/payroll_data.dbf
8 rows selected.
-- Find all diskgroups
SQL> select name from v$asm_diskgroup;
NAME
------------------------------
DATA
FRA
BACKUP
-- Procedures for Renaming and Relocating Datafiles in a Single Tablespace
Steps:
1) Take the tablespace that contains the datafiles offline. The database must be open.
For example
SQL> alter tablespace payroll offline;
Tablespace altered.
2) Rename the datafiles using rman
e.g.,
COPY DATAFILE '+DATA/prod/datafile/payroll_data.dbf' TO '+DATA/prod/datafile/payroll_data01.dbf';
RMAN> COPY DATAFILE '+DATA/prod/datafile/payroll_data.dbf' TO '+DATA/prod/datafile/payroll_data01.dbf';
Starting backup at 03-APR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+DATA/prod/datafile/payroll_data.dbf
output file name=+DATA/prod/datafile/payroll_data01.dbf tag=TAG20150403T151129 RECID=5 STAMP=876064291
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 03-APR-15
RMAN>
3) List in asmcmd
ASMCMD [+DATA/prod/datafile] > ls -la
WARNING:option 'a' is deprecated for 'ls'
please use 'absolutepath'
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE MAR 06 04:00:00 Y none => EXAMPLE.279.841567765
DATAFILE UNPROT COARSE APR 03 03:00:00 Y +DATA/prod/datafile/hr_data.dbf => HR.260.876022795
DATAFILE UNPROT COARSE APR 03 04:00:00 Y +DATA/prod/datafile/payroll_data01.dbf => PAYROLL.263.876026489
DATAFILE UNPROT COARSE APR 03 04:00:00 Y +DATA/prod/datafile/payroll_data.dbf => PAYROLL.268.876022907
DATAFILE UNPROT COARSE MAR 31 08:00:00 Y none => SYSAUX.270.841567651
DATAFILE UNPROT COARSE MAR 06 04:00:00 Y none => SYSTEM.331.841567649
DATAFILE UNPROT COARSE MAR 06 04:00:00 Y none => UNDOTBS1.335.841567651
DATAFILE UNPROT COARSE MAR 06 04:00:00 Y none => UNDOTBS2.278.841567985
DATAFILE UNPROT COARSE MAR 06 04:00:00 Y none => USERS.269.841567651
N hr_data.dbf => +DATA/prod/datafile/HR.260.876022795
N payroll_data.dbf => +DATA/prod/datafile/PAYROLL.268.876022907
N payroll_data01.dbf => +DATA/prod/datafile/PAYROLL.263.876026489
4) Rename datafile
$ sqlplus / as sysdba
SQL> ALTER DATABASE RENAME FILE '+DATA/prod/datafile/payroll_data.dbf' TO '+DATA/prod/datafile/payroll_data01.dbf';
Database altered.
5) Switch datafile in RMAN
$ rman target /
RMAN> SWITCH DATAFILE '+DATA/prod/datafile/payroll_data01.dbf' TO COPY;
using target database control file instead of recovery catalog
datafile 8 switched to datafile copy "+DATA/prod/datafile/payroll_data01.dbf"
RMAN>
6) Recover the database (optional, if you have taken downtime, then no need)
$ sqlplus / as sysdba
SQL> RECOVER DATAFILE '+DATA/prod/datafile/payroll_data01.dbf';
Media recovery complete.
If there is no more recovery required, you may receive below message:
SQL> RECOVER DATAFILE '+DATA/prod/datafile/payroll_data01.dbf';
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
7) Now online the datafile
SQL> ALTER DATABASE DATAFILE '+DATA/prod/datafile/payroll_data01.dbf' ONLINE;
Database altered.
-- Verify the new list datafile
SQL> SELECT file_name FROM dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/prod/datafile/users.269.841567651
+DATA/prod/datafile/undotbs1.335.841567651
+DATA/prod/datafile/sysaux.270.841567651
+DATA/prod/datafile/system.331.841567649
+DATA/prod/datafile/example.279.841567765
+DATA/prod/datafile/undotbs2.278.841567985
+DATA/prod/datafile/hr_data.dbf
+DATA/prod/datafile/payroll_data01.dbf
8 rows selected.
OR
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name PROD
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 740 SYSTEM *** +DATA/prod/datafile/system.331.841567649
2 1310 SYSAUX *** +DATA/prod/datafile/sysaux.270.841567651
3 355 UNDOTBS1 *** +DATA/prod/datafile/undotbs1.335.841567651
4 5 USERS *** +DATA/prod/datafile/users.269.841567651
5 345 EXAMPLE *** +DATA/prod/datafile/example.279.841567765
6 650 UNDOTBS2 *** +DATA/prod/datafile/undotbs2.278.841567985
7 100 HR *** +DATA/prod/datafile/hr_data.dbf
8 100 PAYROLL *** +DATA/prod/datafile/payroll_data01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 42 TEMP 32767 +DATA/PROD/tempfile/temp.280.841567761
RMAN>
Now all looks fine. The data-file successfully renamed. I verified the data also.
You can search from below links:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles005.htm
http://www.dba-oracle.com/t_rename_data_file.htm
http://oracle-base.com/articles/misc/renaming-or-moving-oracle-files.php
-- Used oracle version is 11.2.0.3 on AIX platform.
Rename or relocate of datafiles are possible in Oracle database. When you rename and relocate datafiles with these procedures, only the pointers to the datafiles, as recorded in the database control file, are changed. The procedures do not physically rename any operating system files, nor do they copy files at the operating system level. Renaming and relocating datafiles involves several steps. Read the steps and examples carefully before performing these procedures.
-- Create a tablespace and user in your test database.
SQL> create tablespace PAYROLL datafile '+DATA/prod/datafile/payroll_data.dbf' size 100M autoextend on;
Tablespace created.
SQL> grant connect,resource to payroll identified by payroll;
Grant succeeded.
-- See your tablespaces and datafiles in your database.
$ rman target /
Recovery Manager: Release 11.2.0.3.0 - Production on Fri Apr 3 14:13:03 2015
Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.
connected to target database: PROD (DBID=1981474880)
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name PROD
List of Permanent Datafiles
-------------------------------
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 740 SYSTEM *** +DATA/prod/datafile/system.331.841567649
2 1310 SYSAUX *** +DATA/prod/datafile/sysaux.270.841567651
3 355 UNDOTBS1 *** +DATA/prod/datafile/undotbs1.335.841567651
4 5 USERS *** +DATA/prod/datafile/users.269.841567651
5 345 EXAMPLE *** +DATA/prod/datafile/example.279.841567765
6 650 UNDOTBS2 *** +DATA/prod/datafile/undotbs2.278.841567985
7 100 HR *** +DATA/prod/datafile/hr_data.dbf
8 100 PAYROLL *** +DATA/prod/datafile/payroll_data.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 42 TEMP 32767 +DATA/PROD/tempfile/temp.280.841567761
RMAN>
-- See all datafiles in your database
SQL> SELECT file_name FROM dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/prod/datafile/users.269.841567651
+DATA/prod/datafile/undotbs1.335.841567651
+DATA/prod/datafile/sysaux.270.841567651
+DATA/prod/datafile/system.331.841567649
+DATA/prod/datafile/example.279.841567765
+DATA/prod/datafile/undotbs2.278.841567985
+DATA/prod/datafile/hr_data.dbf
+DATA/prod/datafile/payroll_data.dbf
8 rows selected.
-- Find all diskgroups
SQL> select name from v$asm_diskgroup;
NAME
------------------------------
DATA
FRA
BACKUP
-- Procedures for Renaming and Relocating Datafiles in a Single Tablespace
Steps:
1) Take the tablespace that contains the datafiles offline. The database must be open.
For example
SQL> alter tablespace payroll offline;
Tablespace altered.
2) Rename the datafiles using rman
e.g.,
COPY DATAFILE '+DATA/prod/datafile/payroll_data.dbf' TO '+DATA/prod/datafile/payroll_data01.dbf';
RMAN> COPY DATAFILE '+DATA/prod/datafile/payroll_data.dbf' TO '+DATA/prod/datafile/payroll_data01.dbf';
Starting backup at 03-APR-15
using channel ORA_DISK_1
channel ORA_DISK_1: starting datafile copy
input datafile file number=00008 name=+DATA/prod/datafile/payroll_data.dbf
output file name=+DATA/prod/datafile/payroll_data01.dbf tag=TAG20150403T151129 RECID=5 STAMP=876064291
channel ORA_DISK_1: datafile copy complete, elapsed time: 00:00:03
Finished backup at 03-APR-15
RMAN>
3) List in asmcmd
ASMCMD [+DATA/prod/datafile] > ls -la
WARNING:option 'a' is deprecated for 'ls'
please use 'absolutepath'
Type Redund Striped Time Sys Name
DATAFILE UNPROT COARSE MAR 06 04:00:00 Y none => EXAMPLE.279.841567765
DATAFILE UNPROT COARSE APR 03 03:00:00 Y +DATA/prod/datafile/hr_data.dbf => HR.260.876022795
DATAFILE UNPROT COARSE APR 03 04:00:00 Y +DATA/prod/datafile/payroll_data01.dbf => PAYROLL.263.876026489
DATAFILE UNPROT COARSE APR 03 04:00:00 Y +DATA/prod/datafile/payroll_data.dbf => PAYROLL.268.876022907
DATAFILE UNPROT COARSE MAR 31 08:00:00 Y none => SYSAUX.270.841567651
DATAFILE UNPROT COARSE MAR 06 04:00:00 Y none => SYSTEM.331.841567649
DATAFILE UNPROT COARSE MAR 06 04:00:00 Y none => UNDOTBS1.335.841567651
DATAFILE UNPROT COARSE MAR 06 04:00:00 Y none => UNDOTBS2.278.841567985
DATAFILE UNPROT COARSE MAR 06 04:00:00 Y none => USERS.269.841567651
N hr_data.dbf => +DATA/prod/datafile/HR.260.876022795
N payroll_data.dbf => +DATA/prod/datafile/PAYROLL.268.876022907
N payroll_data01.dbf => +DATA/prod/datafile/PAYROLL.263.876026489
4) Rename datafile
$ sqlplus / as sysdba
SQL> ALTER DATABASE RENAME FILE '+DATA/prod/datafile/payroll_data.dbf' TO '+DATA/prod/datafile/payroll_data01.dbf';
Database altered.
5) Switch datafile in RMAN
$ rman target /
RMAN> SWITCH DATAFILE '+DATA/prod/datafile/payroll_data01.dbf' TO COPY;
using target database control file instead of recovery catalog
datafile 8 switched to datafile copy "+DATA/prod/datafile/payroll_data01.dbf"
RMAN>
6) Recover the database (optional, if you have taken downtime, then no need)
$ sqlplus / as sysdba
SQL> RECOVER DATAFILE '+DATA/prod/datafile/payroll_data01.dbf';
Media recovery complete.
If there is no more recovery required, you may receive below message:
SQL> RECOVER DATAFILE '+DATA/prod/datafile/payroll_data01.dbf';
ORA-00283: recovery session canceled due to errors
ORA-00264: no recovery required
7) Now online the datafile
SQL> ALTER DATABASE DATAFILE '+DATA/prod/datafile/payroll_data01.dbf' ONLINE;
Database altered.
-- Verify the new list datafile
SQL> SELECT file_name FROM dba_data_files;
FILE_NAME
--------------------------------------------------------------------------------
+DATA/prod/datafile/users.269.841567651
+DATA/prod/datafile/undotbs1.335.841567651
+DATA/prod/datafile/sysaux.270.841567651
+DATA/prod/datafile/system.331.841567649
+DATA/prod/datafile/example.279.841567765
+DATA/prod/datafile/undotbs2.278.841567985
+DATA/prod/datafile/hr_data.dbf
+DATA/prod/datafile/payroll_data01.dbf
8 rows selected.
OR
RMAN> report schema;
using target database control file instead of recovery catalog
Report of database schema for database with db_unique_name PROD
List of Permanent Datafiles
===========================
File Size(MB) Tablespace RB segs Datafile Name
---- -------- -------------------- ------- ------------------------
1 740 SYSTEM *** +DATA/prod/datafile/system.331.841567649
2 1310 SYSAUX *** +DATA/prod/datafile/sysaux.270.841567651
3 355 UNDOTBS1 *** +DATA/prod/datafile/undotbs1.335.841567651
4 5 USERS *** +DATA/prod/datafile/users.269.841567651
5 345 EXAMPLE *** +DATA/prod/datafile/example.279.841567765
6 650 UNDOTBS2 *** +DATA/prod/datafile/undotbs2.278.841567985
7 100 HR *** +DATA/prod/datafile/hr_data.dbf
8 100 PAYROLL *** +DATA/prod/datafile/payroll_data01.dbf
List of Temporary Files
=======================
File Size(MB) Tablespace Maxsize(MB) Tempfile Name
---- -------- -------------------- ----------- --------------------
1 42 TEMP 32767 +DATA/PROD/tempfile/temp.280.841567761
RMAN>
Now all looks fine. The data-file successfully renamed. I verified the data also.
You can search from below links:
https://docs.oracle.com/cd/B28359_01/server.111/b28310/dfiles005.htm
http://www.dba-oracle.com/t_rename_data_file.htm
http://oracle-base.com/articles/misc/renaming-or-moving-oracle-files.php
I value the blog post.Really looking forward to read more. Really Cool.
ReplyDeleteBusiness analysis online online training
Cognos online online training
Core Java online online training
Django online online training
Go Language online online training
Hibernate online online training
Hyperion ESS Base online online training
Thanks for the blog article.Thanks Again. Keep writing.
ReplyDeletedata science training
python training
angular js training
selenium trainings
java training