Moving database files in Oracle

Reproduced:

Moving database files in Oracle

The--oracle database consists of three files, data files, control files, and online log files.

--Due to changes in disk space, or adjustments based on database disk I/O performance,

--we might consider moving the database files.

--The following is an example of the LUnix platform, which discusses three kinds of database file moving methods respectively.

One. Move data files:

--You can move data files in two ways with ALTER Database,alter tablespace.

1. ALTER database method;

--With this method, you can move data files of any tablespace.

STEP 1. The following database:

$ sqlplus/nolog

Sql> CONNECT INTERNAL;

Sql> SHUTDOWN;

Sql> EXIT;

STEP 2: Move data files with operating system commands

--Move the data file ' Test.ora ' from the/ora/oracle/data1 directory to the/ORA/ORACLE/DATA2 directory:

$ mv/ora/oracle/data1/test.ora/ora/oracle/data2

STEP 3. Mount database, rename the data file with the ALTER DATABASE command:

$ sqlplus/nolog

Sql> CONNECT INTERNAL;

Sql> STARTUP MOUNT;

sql> ALTER DATABASE RENAME FILE '/ora/oracle/data1/test.ora ' to '/ora/oracle/data2/test.ora ';

STEP 4. Open database:.

sql> ALTER DATABASE OPEN;

Sql>select name,status from V$datafile;

2. ALTER Tablespace Method:

--In this way, this data file is required to be neither a system table space nor a table space containing an active rollback segment or temporary segment.

STEP1. Offline the tablespace in which this data file resides:

$ sqlplus/nolog

Sql> CONNECT INTERNAL;

sql> ALTER tablespace test OFFLINE;

Sql> EXIT;

STEP2. To move a data file with an operating system command:

The data file ' Test.ora ' from/ora/oracle/

The Data1 directory is moved to the/ORA/ORACLE/DATA2 directory:

$ mv/ora/oracle/data1/test.ora/ora/oracle/data2

STEP3. Change the data file name with the ALTER TABLESPACE command:

$ sqlplus/nolog

Sql> CONNECT INTERNAL;

sql> ALTER tablespace test RENAME datafile '/ora/oracle/data1/test.ora ' to '/ora/oracle/data2/test.ora ';

STEP4. Place this data file in the Tablespace online:

sql> ALTER tablespace test ONLINE;

Sql> SELECT name,status from V$datafile;

Two. Mobile control file:

--control file at INIT. Ora file specified in the. Mobile control file is relatively simple, under the database,

--Edit Init.ora, move the control file, restart the database.

STEP 1. The following database:

$ sqlplus/nolog

Sql> CONNECT INTERNAL;

Sql> SHUTDOWN;

Sql> EXIT;

STEP 2: Move the control file with the operating system command.

--Move the control file ' Ctl3orcl.ora ' from the/ora/oracle/data1 directory to the/ORA/ORACLE/DATA2 directory:

$ mv/ora/oracle/data1/ctrlorcl3.ora/ora/oracle/data2

STEP 3. Edit Init.ora File:

Init. Ora file in the $oracle_home/dbs directory,

Modify the parameter "Control_files", which specifies the control file after the move:

Control_files = (/ora/oracle/data1/ctrlorcl1.ora,/ora/oracle/data1/ctrlorcl2.ora,/ora/oracle/data2/ctrlorcl3.ora )

STEP 4. To restart the database:

$ sqlplus/nolog

Sql> CONNECT INTERNAL;

Sql> STARTUP;

Sql>select name from V$controlfile;

Sql> EXIT;

Three. Move the online log file:

STEP 1. To stop the database:

$ sqlplus/nolog

Sql> CONNECT INTERNAL;

Sql> SHUTDOWN;

Sql> EXIT;

STEP 2. To move an online log file with an operating system command:

--Move the online log file ' Redolog1.ora ' from the/ora/oracle/data1 directory to the/ORA/ORACLE/DATA2 directory:

$ mv/ora/oracle/data1/redolog1.ora/ora/oracle/data2

STEP 3. Mount database, change the online log file name with the ALTER DATABASE command:.

$ sqlplus/nolog

Sql> CONNECT INTERNAL;

Sql> STARTUP MOUNT;

sql> ALTER DATABASE RENAME FILE '/ora/oracle/data1/redolog1.ora ' to '/ora/oracle/data2/redolog1.ora ';

STEP 4. Restart the database:.

sql> ALTER DATABASE OPEN;

Sql>select MEMBER from V$logfile;

Documented for easy routine maintenance