Oracle Data File migration

Windows XP oracle9.2.0.1.0sid: The test database is installed on disk D. Now, the control files, database files, log files, and temporary files under D: ORACLEORADATATEST are moved to F: TEST. First, close the database and copy the files under D: ORACLEORADATATEST to F: TEST. Edit D: oracleora92datab first

Windows xp oracle 9. 2.0.1.0 sid: The test database is installed on disk D. Now, the control files, database files, log files, and temporary files under D: \ ORACLE \ ORADATA \ TEST are moved to disk F: \ TEST. First, close the database and copy the files under D: \ ORACLE \ ORADATA \ TEST to F: \ TEST. First edit D: \ oracle \ ora92 \ datab

Windows xp

Oracle 9. 2.0.1.0

Sid: test

The database is installed on disk D. Now, the control files, database files, log files, and temporary files under D: \ ORACLE \ ORADATA \ TEST are moved to F: \ TEST.

First, close the database and copy the files under D: \ ORACLE \ ORADATA \ TEST to F: \ TEST.

First, edit D: \ oracle \ ora92 \ database \ INITTEST. ORA and change it:

Control_files = ("F: \ TEST \ control01.ctl", "F: \ TEST \ control02.ctl", "F: \ TEST \ control03.ctl ")

SQL> create spfile from pfile;


SQL> startup mount;


Query Data File Location: select name from v $ datafile;

Query Log File Location: select * from v $ logfile;

Query Control File Location: select name from v $ controlfile;

View the temporary file location: select * from v $ tempfile;


Move log files:

Alter database rename file 'd: \ ORACLE \ ORADATA \ TEST \ REDO01.LOG 'to 'f: \ TEST \ redo01.log ';
Alter database rename file 'd: \ ORACLE \ ORADATA \ TEST \ REDO02.LOG 'to 'f: \ TEST \ redo02.log ';
Alter database rename file 'd: \ ORACLE \ ORADATA \ TEST \ REDO03.LOG 'to 'f: \ TEST \ redo03.log ';

Move data files:

Alter database rename file 'd: \ ORACLE \ ORADATA \ TEST \ SYSTEM01.DBF 'to 'f: \ TEST \ SYSTEM01.DBF ';
Alter database rename file 'd: \ ORACLE \ ORADATA \ TEST \ UNDOTBS01.DBF 'to 'f: \ TEST \ undotbs01.dbf ';
Alter database rename file 'd: \ ORACLE \ ORADATA \ TEST \ DRSYS01.DBF 'to 'f: \ TEST \ drsys01.dbf ';
Alter database rename file 'd: \ ORACLE \ ORADATA \ TEST \ INDX01.DBF 'to 'f: \ TEST \ INDX01.DBF ';
Alter database rename file 'd: \ ORACLE \ ORADATA \ TEST \ ODM01.DBF 'to 'f: \ TEST \ ODM01.DBF ';
Alter database rename file 'd: \ ORACLE \ ORADATA \ TEST \ TOOLS01.DBF 'to 'f: \ TEST \ tools01.dbf ';
Alter database rename file 'd: \ ORACLE \ ORADATA \ TEST \ USERS01.DBF 'to 'f: \ TEST \ users01.dbf ';
Alter database rename file 'd: \ ORACLE \ ORADATA \ TEST \ XDB01.DBF 'to 'f: \ TEST \ xdb01.dbf ';

In this case, SQL> alter database open;

Temporary File Location transfer:

After the database is open, run:

SQL> alter database tempfile 'temporary file path' drop;

The database has been changed.

SQL> alter tablespace temp add tempfile 'temporary file path' size 100 M reuse;

The tablespace has been changed.

SQL> select * from v $ tempfile;

Restart the database. OK. The migration is complete.

Requirement: Oracle database files are stored on drive C. As the data files are getting bigger and bigger, I want to move some data files to drive D.

Environment: Oracle9i

Procedure:

1. Sqlplus/nolog
2. Connect/as sysdba;
3. Shutdown immediate;
4. Startup mount;
5. Alter database rename file 'C: \ ora92 \ oradata \ trans. dbf' to 'd: \ ora92 \ oradata \ trans. dbf ';
6. Alter database open;

Note:

• The data file in the temp tablespace cannot be moved.

Oracle startup Methods

1. startup nomount

Non-installation startup. In this mode, you can execute: re-build the control file and re-build the database.

Read the init. ora file and start the instance, that is, start the SGA and background processes. To start the process, you only need the init. ora file.

2. startup mount dbname

Install and start. In this mode, you can run the following commands: Database Log archiving, database media recovery, online or offline data files, data file locating, and redo log files.

Execute "nomount", open the control file, and confirm the location of the data file and the on-line log file. However, the data file and log file are not verified at this time.

3. startup open dbname

Run "nomount" first, then "mount", and then open all database files including Redo log files. In this way, you can access data in the database.

4. startup, which is equal to the following three commands

Startup nomount

Refer:

Temporary data file change path Google

Alter database mount

Alter database open