Contents
1. Overview
2. Moving Datafiles while the Instance is Mounted
3. Moving Datafiles while the Instance is Open
4. Moving Online Redo Log Files
5. Moving Control Files
Overview:
Once a data file has been created in the database, it may be necessary to move it in order to better manage its size or I/O requirements. This article will provide several methods used by DBAs for moving datafiles, online redo log files and control files. In all of these methods, operating system commands are used to move the files while the Oracle commands serve primarily to reset the pointers to those files.
There are two methods for moving / renaming physical database files within Oracle. The first is to shut the database down, move (or rename) the file(s) using O/S commands, and finally, use the ALTER DATABASE command to reset the pointers to those files within Oracle.
The second method can be done while the database is running and uses the ALTER TABLESPACE command. The tablespace will need to be taken offline during the time the file(s) are being moved or renamed. Once the files are moved (or renamed), use the ALTER TABLESPACE command to reset the pointers within Oracle and finally, bring the tablespace back online. This method only applies to datafiles whose tablespaces do not include SYSTEM, ROLLBACK or TEMPORARY segments.
Following is an example of how to manipulate datafiles in a tablespace using both the alter database method and the alter tablespace method. All examples will use an Oracle9i databse (9.2.0.5.0) running on Sun Solaris 2.9.
XXXXXXXXXXXXXXXXXXXXXXXXXX
Moving Datafiles while the Instance is Mounted:
Moving or renaming a datafile while the database is in the MOUNT stage requires the use of the ALTER DATABASE command. When using the ALTER DATABASE method to move datafiles, the datafile is moved after the instance is shut down. A summary of the steps involved follows:
1. Shutdown the instance
2. Use operating system commands to move or rename the files(s).
3. Mount the database and use the ALTER DATABASE to rename the file within the database.
4. Opening the Database
% sqlplus "/ as sysdba"
SQL> shutdown immediate
SQL> !mv /u05/app/oradata/ORA920/indx01.dbf /u06/app/oradata/ORA920/indx01.dbf
SQL> startup mount
SQL> alter database rename file '/u05/app/oradata/ORA920/indx01.dbf' to '/u06/app/oradata/ORA920/indx01.dbf';
Do not disconnect after this step. Stay logged in
and proceed to open the database!
SQL> alter database open;
SQL> exit
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Moving Datafiles while the Instance is Open
Moving or renaming a datafile while the database is in the 'OPEN' stage requires the use of the ALTER TABLESPACE command. When using the ALTER TABLESPACE method to move datafiles, the datafile is moved while the instance is running. A summary of the steps involved follows:
1. Take the tablespace OFFLINE.
2. Use operating system commands to move or rename the file(s).
3. Use the ALTER TABLESPACE command to rename the file within the database.
4. Bring the tablespace back ONLINE.
NOTE: This method can only be used for non-SYSTEM tablespaces. It also cannot be used for tablespaces that contain active ROLLBACK segments or TEMPORARY segments.
% sqlplus "/ as sysdba"
SQL> alter tablespace INDX offline;
SQL> !mv /u05/app/oradata/ORA920/indx01.dbf /u06/app/oradata/ORA920/indx01.dbf
SQL> alter tablespace INDX
2 rename datafile '/u05/app/oradata/ORA920/indx01.dbf' to '/u06/app/oradata/ORA920/indx01.dbf';
Do not disconnect after this step. Stay logged in
and proceed to bring the tablespace back online!
SQL> alter tablespace INDX online;
SQL> exit
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Moving Online Redo Log Files
Online redo log files may be moved while the database is shutdown. Once renamed (or moved) the DBA should use the ALTER DATABASE command to update the data dictionary. A summary of the steps involved follows:
1. Shutdown the instance
2. Use operating system commands to move the datafile.
3. Mount the database and use ALTER DATABASE to rename the log file within the database.
4. Opening the Database
% sqlplus "/ as sysdba"
SQL> shutdown immediate
SQL> !mv /u06/app/oradata/ORA920/redo_g03a.log /u03/app/oradata/ORA920/redo_g03a.log
SQL> !mv /u06/app/oradata/ORA920/redo_g03b.log /u04/app/oradata/ORA920/redo_g03b.log
SQL> !mv /u06/app/oradata/ORA920/redo_g03c.log /u05/app/oradata/ORA920/redo_g03c.log
SQL> startup mount
SQL> alter database rename file '/u06/app/oradata/ORA920/redo_g03a.log' to '/u03/app/oradata/ORA920/redo_g03a.log';
SQL> alter database rename file '/u06/app/oradata/ORA920/redo_g03b.log' to '/u04/app/oradata/ORA920/redo_g03b.log';
SQL> alter database rename file '/u06/app/oradata/ORA920/redo_g03c.log' to '/u05/app/oradata/ORA920/redo_g03c.log';
Or
Use the ALTER DATABASE statement with the RENAME FILE clause to rename the database redo log files.
ALTER DATABASE
RENAME FILE '/diska/logs/log1a.rdo', '/diska/logs/log2a.rdo'
TO '/diskc/logs/log1c.rdo', '/diskc/logs/log2c.rdo';
Do not disconnect after this step. Stay logged in
and proceed to open the database!
SQL> alter database open;
SQL> exit
*******************************************************
Live example in our environment in production box:
1> Requirement :
Redo files are created in rd002 and rd003 for instance 5, once we have the rd005 file available all we need to do is
move /ora/rd003/SCRM01P/SCRM01P5redo*.log files to /ora/rd005/SCRM01P
In order to move redo to new file system
1. Check the redolog file group is not current and no longer active
2. Alter system drop logfile group (??)
3. Delete OS files.
4. Add redo making sure the rd003 replaced with rd005.
****************
Important : No Downtime Require for the below action.
Solution :
1. Run the following SQL to locate the current redo log groups and their members:
select l.group# group_number
, l.status group_status
, f.member group_member
, f.status file_status
from v$log l
, v$logfile f
where l.group# = f.group# and THREAD#=5
order by l.group#,f.member;
Note : Check the redolog file group is not current and no longer active, Than drop the redologfile from Thread#=5(Group 33 -40).
Steps 2 :=====>>>>> Drop the all inactive redolog from Thread#5(Group 33-40):
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo33b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 33 ‘/ora/rd005/SCRM01P/SCRM01P5redo33b.log';
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo34b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 34 '/ora/rd005/SCRM01P/SCRM01P5redo34b.log';
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo35b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 35 '/ora/rd005/SCRM01P/SCRM01P5redo35b.log';
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo36b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 36 '/ora/rd005/SCRM01P/SCRM01P5redo36b.log';
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo37b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 37 '/ora/rd005/SCRM01P/SCRM01P5redo37b.log';
Note : Status is giving active so please switch before proceeding the below unless become inactive don't proceed.
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo38b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 38 '/ora/rd005/SCRM01P/SCRM01P5redo38b.log';
Note : Status is giving Current so please switch before proceeding the below unless become inactive don't proceed.
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo39b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 39 '/ora/rd005/SCRM01P/SCRM01P5redo39b.log';
ALTER DATABASE DROP LOGFILE MEMBER '/ora/rd003/SCRM01P/SCRM01P5redo40b.log';
ALTER DATABASE ADD LOGFILE THREAD 5 Group 40 '/ora/rd005/SCRM01P/SCRM01P5redo40b.log';
Steps 3 =>>>> To check the new values of redolog files mirroring mount point for thread 5 with below sql statement.
SQL> select l.group# group_number
, l.status group_status
, f.member group_member
, f.status file_status
from v$log l
, v$logfile f
where l.group# = f.group# and THREAD#=5
order by l.group#,f.member;
Steps 4 ==> Remove the old redolog files(mirroring) for Thread 5 under mount point '/ora/rd003/SCRM01P/'.
Login in server nus986pc ==>>
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo33b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo34b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo35b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo36b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo37b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo38b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo39b.log
nus986pc$ rm -rf /ora/rd003/SCRM01P/SCRM01P5redo40b.log
select
a.ksppinm name,
b.ksppstvl value,
a.ksppdesc description
from
x$ksppi a,
x$ksppcv b
where
a.indx = b.indx
and
a.ksppinm = '_ksmg_granule_size';
NAME VALUE DESCRIPTION
------------------------------ ------------------------------ ------------------------
_ksmg_granule_size 16777216 granule size in bytes
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
Another Live schenario :
Requirement to rename the datafile without downtime:
vus252:MNCP:/ora/admin>. oraenv
ORACLE_SID = [MNCP] ?
vus252:MNCP:/ora/admin>sqlplus /nolog
SQL*Plus: Release 9.2.0.8.0 - Production on Mon Aug 9 15:17:41 2010
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
SQL> conn / as sysdba
Before shutdown the database you can do the all below action ==>>>
Check the alert log file.
Take the tablespace MNC_BULK_DATA and datafile(MNCPmnc_bulk_data_06.dbf) location from below query.
SQL> select tablespace_name||' '||file_name from dba_data_files where tablespace_name='MNC_BULK_DATA';
SQL > ALTER TABLESPACE MNC_BULK_DATA OFFLINE;
cp /ora/data/da001/MNCPmnc_bulk_data_06.dbf /ora/archive/MNCPmnc_bulk_data_06.dbf
SQL> ALTER TABLESPACE MNC_BULK_DATA RENAME DATAFILE '/ora/data/da001/MNCPmnc_bulk_data_06.dbf' TO '/ora/archive/MNCPmnc_bulk_data_06.dbf’;
SQL > ALTER TABLESPACE MNC_BULK_DATA ONLINE;
Check the new location of datafile for Table space MNC_BULK_DATA from below command.
SQL> select tablespace_name||' '||file_name from dba_data_files where tablespace_name='MNC_BULK_DATA';
Once everything goes well, Remove the files from old location
rm /ora/data/da001/MNCPmnc_bulk_data_06.dbf
You can check the space in /ora/data/da001/ it should be around 4% available capacity( df -k /ora/data/da001/).
No comments:
Post a Comment