Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "what is the method of renaming and migrating log files and data files?". In the operation of actual cases, many people will encounter such a dilemma. Next, let the editor lead you to learn how to deal with these situations! I hope you can read it carefully and be able to achieve something!

Purpose: in some cases, data files or log files must be renamed or migrated to a new location, but for these files to remain valid, you need to define a physical name or physical location for these files. for example, you need to rename the data file or log file when:

1. For performance or maintenance reasons, you need to move a database file to a different disk

two。 Restore a data file from the backup, but the disk on which the data file was previously stored has crashed, so you need to reassign the data file to a new disk

3. At the operating system level, a data file has been moved or renamed, but it is not renamed in the database layer, that is, the database, and the ORA-01157 and ORA-01110 error messages are obtained during database startup. If the database is open and the data file is renamed or moved to another location at the operating system level, ORA-01116 and ORA-01110 will be reported when the database is closed by normal or immediate

This topic will be discussed in three aspects:

Rename or move the data file when the database is open

Rename or move data files when the II database is closed

III rename or move log files

[note]

If the data file to be renamed or moved is a file in the SYSTEM tablespace, the II method must be used to achieve the ultimate goal, that is, "rename or move the data file when the database is closed", because when the database is open, the SYSTEM tablespace cannot be in the offline state of OFFLINE.

Let's explain the different scenarios:

Rename or move the data file when the database is open

The data file can be renamed or moved while the database is open, but the tablespace must be placed in the READ-ONLY state. In the READ-ONLY state, users can SELECT the tablespace, but not INSERTS UPDATES and DELETES the tablespace. The total time required for the tablespace READ-ONLY state depends on the size of the data file and the time it takes to copy a data file from one location to a new location

Place the tablespace in the READ-ONLY state and freeze the file header to prevent the data file header from being updated. Only when the data file is in the READ-ONLY state is it possible to copy the file while the database is open.

Use the following steps to rename or move the data file. In this case, the USERS table space is the table space where the data file resides:

1. Check how many data files are in the USERS tablespace

SQL > select file_name, status from dba_data_files where tablespace_name = 'USERS'

FILE_NAME STATUS

/ u01/oradata/test/users01.dbf AVAILABLE

two。 Ensure that in the returned query results, the status of all data files, that is, the value of STATUS, is AVAILABLE.

3. Set the USERS tablespace to the READ ONLY state

Altertablespace users read only

4. Make sure that the USERS tablespace has been placed in the READ ONLY state in the data dictionary

SQL > select tablespace_name, status from dba_tablespaces where tablespace_name = 'USERS'

TABLESPACE_NAME STATUS

-

USERS READ ONLY

5. Use the operating system command to copy the data file to a new location. After the data file is copied to a new location, the most important thing to do is to compare whether the size of the two data files is the same, because data loss may occur during the replication process.

ORACLE > cp / u01/oradata/test/users01.dbf / jiachunyun/test/users01.dbf

Note: similarly, you can use the above method to rename the data file and change the location of the data file. (I only changed the location / jiachunyun/test)

6. When the data file has been copied to the new location, place the USERS tablespace in the OFFLINE state

Altertablespace users offline

7. When the tablespace is placed in the OFFLINE state, what needs to be done is to rename the data file to a new location in the database layer, which updates the location information of the data file in the control file.

Alter database rename file'/ u01qoradata Universe TestUniverse users01.dbf'to'/ jiachunyun/test/users01.dbf'

8. After confirming that all rename operations are complete, put the USERS tablespace online, that is, ONLINE

Alter tablespace users online

9. When you reset the tablespace to the ONLINE state, you need to reset the tablespace to the READ WRITE read-write state.

Alter tablespace users read write

10. Check whether the state of the tablespace is READ WRITE

SQL > select tablespace_name, status from dba_tablespaces where tablespace_name = 'USERS'

TABLESPACE_NAME STATUS

-

USERS ONLINE

11. View the location of the modified data file

SQL > select file_name, status from dba_data_files where tablespace_name = 'USERS'

FILE_NAME STATUS

/ jiachunyun/test/users01.dbf AVAILABLE

13. At the operating system level, delete the old data files in the original old directory

ORACLE > rm-rf / u01/oradata/test/users01.dbf

Rename or move data files when the II database is closed

1. View the location of the data file in the USERS tablespace

SQL > select file_name,tablespace_name,file_id from dba_data_files wheretablespace_name = 'USERS'

FILE_NAME TABLESPACE_NAME FILE_ID

-

/ u01/oradata/users01.dbf USERS 4

two。 Shut down the database

Shutdown immediate

3. Rename or move the data file to a new location at the operating system level, or move to a new location and rename

ORACLE > cp / u01/oradata/test/users01.dbf / jiachunyun/test/users01.dbf, etc.

4. Start the database to mount state

Startup mount

[description]: this operation reads the control file, but does not mount the data file

5. Rename data files within the database

Alter database rename file'/ u01qoradata Universe users01.dbf'to'/ jiachunyun/test/users01.dbf'

Alter database rename file'/ u01qoradata jiachunyun/test/sysaux01.dbf' Sysaux01.dbf 'oradata'

Undotbs01.dbf'to'/ jiachunyun/test/undotbs01.dbf' of alter database rename file'/ u01 to oradata

Alter database rename file'/ u01qoradata Universe users01.dbf'to'/ jiachunyun/test/users01.dbf'

6. Open the database

Alter database open

7. Query the v$dbfile view to confirm that the modification has been successful

SQL > select name from v$datafile

NAME

/ jiachunyun/test/users01.dbf

/ jiachunyun/test/sysaux01.dbf

/ jiachunyun/test/undotbs01.dbf

/ jiachunyun/test/users01.dbf

/ jiachunyun/test/undotb1.dbf

8. At the operating system level, delete the old data file from the old directory location

ORACLE > rm-rf / u01/oradata/users01.dbf, etc.

When the database is closed, rename or move the control file [note] it is best to divide it into three different disks in order to break a disk

1. View the location of the control file in the USERS tablespace

SQL > select name from v$controlfile

NAME

-

/ u01/oradata/control01.ctl

/ u01/oradata/control02.ctl

2 change the path of the second control file and tell spfile, (how many files are written, the original / u01/oradata/test of 02, new / jiachunyun/test)

SQL > alter system set control_files='/u01/oradata/test/control01.ctl','/jiachunyun/test/control02.ctl' scope=spfile

two。 Shut down the database

Shutdown immediate

3. Rename or move the control file to a new location at the operating system level, or move to a new location and rename

ORACLE > cp / u01/oradata/test/control01.ctl / jiachunyun/test/control02.ctl-- 02 path must correspond to the above alter path

4. Start the database and verify

SQL > startup

SQL > select name from v$controlfile

5. Add the third control file 03

SQL > alter system set control_file='/u01/oradata/test/control01.ctl','/jiachunyun/test/control02.ctl','/u01/oradata/test/control03.ctl' scope=spfile

6. Shut down the database

Shutdown immediate

7. Rename or move the control file to a new location at the operating system level

ORACLE > cp / u01/oradata/test/control01.ctl / jiachunyun/test/control03.ctl

8. Start the database and verify

SQL > startup

SQL > select name from v$controlfile

NAME

/ u01/oradata/test/control01.ctl

/ jiachunyun/test/control02.ctl

/ u01/oradata/test/control03.ctl

III rename or move log files

1. Shut down the database

two。 At the operating system level, rename or move the log file to a new location, or move to a new location and rename

ORACLE > cp / u01/oradata/test/redo01.log / jiachunyun/test/redo01.log, etc.

3. Start the database to the mount state

Startup mount

4. At the database level, rename the log file

Alter database rename file'/ u01qoradata to'/jiachunyun/test/redo01.log';, etc. TestUnix redo01.log'

5. Open the database

Alter database open

6. At the operating system level, delete the old data file from the old directory location

ORACLE > rm-rf / u01/oradata/test/redo01.log

7. Verification

ORACLE > select mane from v$logfile

This is the end of the content of "what is the method of database renaming and migrating log files and data files". Thank you for reading. If you want to know more about the industry, you can follow the website, the editor will output more high-quality practical articles for you!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.