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>

3 comments:

Denis said...

SQL> !mv /u02/oradata/HRPRD/hrapp.dbf /u07/oradata/HRPRD/hrapp.dbf

----

I don't have autual cases to oppose mv. However, I want to err on the safe side. I always use cp first then delete the old file

Denis said...

Hi, buddy, too busy to update your blog?

Michael Wang, PeopleSoft Oracle DBA said...

Sort of busy in something else than the DBA works. So didn't have much to post.