Relocate datafiles for physical dataguard (Oracle 10.2)
We will relocate the following files at a physical data guard situation.
File Move file to
/u02/oradata/HRPRD/hrapp.dbf /u07
/u03/oradata/HRPRD/psundo03.dbf /u06
/u05/oradata/HRPRD/psindex04.dbf /u04
hrapp.dbf and psindex04.dbf are regular data files. Since psundo03.dbf is a data file in undo table space, it takes more steps.
1. Rename files at primary
SQL> col file_name format a40
SQL> select file_name, tablespace_name from dba_data_files where file_name like '%hrapp.dbf';
FILE_NAME TABLESPACE_NAME
---------------------------------------- ------------------------------
/u02/oradata/HRPRD/hrapp.dbf HRAPP
SQL> select file_name, tablespace_name from dba_data_files where file_name like '%psundo03.dbf';
FILE_NAME TABLESPACE_NAME
---------------------------------------- ------------------------------
/u03/oradata/HRPRD/psundo03.dbf PSUNDO
SQL> select file_name, tablespace_name from dba_data_files where file_name like '%psindex04.dbf';
FILE_NAME TABLESPACE_NAME
---------------------------------------- ------------------------------
/u05/oradata/HRPRD/psindex04.dbf PSINDEX
SQL> alter tablespace HRAPP offline;
Tablespace altered.
SQL> !mv /u02/oradata/HRPRD/hrapp.dbf /u07/oradata/HRPRD/hrapp.dbf
SQL> alter tablespace HRAPP rename datafile '/u02/oradata/HRPRD/hrapp.dbf' to '/u07/oradata/HRPRD/hrapp.dbf';
Tablespace altered.
SQL> alter tablespace HRAPP online;
Tablespace altered.
2. Connect to standby database
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> shutdown;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> ho
$ mv /u02/oradata/HRPRD/hrapp.dbf /u07/oradata/HRPRD/hrapp.dbf
$ exit
SQL> startup mount;
ORACLE instance started.
Total System Global Area 570425344 bytes
Fixed Size 2072872 bytes
Variable Size 293605080 bytes
Database Buffers 268435456 bytes
Redo Buffers 6311936 bytes
Database mounted.
SQL> alter system set standby_file_management=MANUAL;
System altered.
SQL> alter database rename file '/u02/oradata/HRPRD/hrapp.dbf' to '/u07/oradata/HRPRD/hrapp.dbf';
Database altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
The relocation of /u05/oradata/HRPRD/psindex04.dbf is the same as above.
3. Working on undo table space
Beside the steps for regular table space, we have to create a new undo table space undo02, then switch the current undo table space to undo02. After we finished the relocation, we drop undo02.
Primary side:
SQL> CREATE UNDO TABLESPACE undo02 DATAFILE '/u06/oradata/HRPRD/undo0201.dbf' SIZE 2048M AUTOEXTEND ON;
Tablespace created.
SQL> show parameter undo
NAME TYPE VALUE
------------------------------------ -------------------------------- ------------------------------
undo_management string AUTO
undo_retention integer 1800
undo_tablespace string psundo
SQL> alter system set undo_tablespace=undo02;
System altered.
SQL> alter tablespace psundo offline;
Tablespace altered.
SQL> !mv /u03/oradata/HRPRD/psundo03.dbf /u06/oradata/HRPRD/psundo03.dbf
SQL> alter tablespace psundo rename datafile '/u03/oradata/HRPRD/psundo03.dbf' to '/u06/oradata/HRPRD/psundo03.dbf';
Tablespace altered.
SQL> alter tablespace psundo online;
Tablespace altered.
SQL> alter system set undo_tablespace=psundo;
System altered.
SQL> drop tablespace undo02;
Tablespace dropped.
SQL>
Standby side:
SQL> alter database recover managed standby database cancel;
Database altered.
SQL> shutdown;
ORA-01109: database not open
Database dismounted.
ORACLE instance shut down.
SQL> !mv /u03/oradata/HRPRD/psundo03.dbf /u06/oradata/HRPRD/psundo03.dbf
SQL> startup mount;
ORACLE instance started.
Total System Global Area 570425344 bytes
Fixed Size 2072872 bytes
Variable Size 293605080 bytes
Database Buffers 268435456 bytes
Redo Buffers 6311936 bytes
Database mounted.
SQL> show parameter standby
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
standby_archive_dest string
/ops_bkup/HRPRD/arch
standby_file_management string
AUTO
SQL> alter system set standby_file_management=MANUAL;
System altered.
SQL> alter database rename file '/u03/oradata/HRPRD/psundo03.dbf' to '/u06/oradata/HRPRD/psundo03.dbf';
Database altered.
SQL> alter system set standby_file_management=AUTO;
System altered.
SQL> alter database recover managed standby database disconnect from session;
Database altered.
SQL>
