Monday, June 2, 2008

Relocate datafiles for physical dataguard (Oracle 10.2)

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>