VMS: ALTER TABLESPACE/DATABASE TO RENAME FILES on VMS [ID 9560.1]

rongshiyuan發表於2013-01-25
Database
VMS: ALTER TABLESPACE/DATABASE TO RENAME FILES on VMS [ID 9560.1]
 
PURPOSE This article describes the use of ALTER DATABASE and ALTER TABLESPACE commands to change the distribution of database files on disks, and to add files to existing tablespaces on VMS. Advantages of using ALTER TABLESPACE are also discussed. SCOPE & APPLICATION This article, while originally written for Oracle6, can be used by DBAs renaming database files on Oracle6, Oracle7 or Oracle8. Note: if there are large number of updates needed, it might be easier to recreate the controlfile which allows the relocating of files. See Note:1012929.6 HOW TO RECREATE THE CONTROL FILE RELATED DOCUMENTS Note:10662.1 UNIX: RENAMING DATABASE FILES ON UNIX ADDITIONAL KEYWORDS Renaming; Rename Prior to using ALTER DATABASE or ALTER TABLESPACE to make structural changes to your database you should ensure that you have a recent backup. After making structural changes to the database, if the database is running in ARCHIVELOG mode you can backup only the control file and any data files that were added after the last database backup. However, if the database is running in NOARCHIVELOG mode then you should do a full database backup. (See the Oracle Database Administrator's Guide for a complete discussion of backup and recovery.) The following syntax is used in the examples and denotes the expanded file specification. Note that the string can include concealed device logicals. :[ ALTER DATABASE -------------- The ALTER DATABASE command is used to rename system tablespace files and redo log files. ALTER DATABASE can also be used to rename non-system tablespace files. However, by using the ALTER TABLESPACE command this can be accomplished without shutting down the database. RENAME A SYSTEM TABLESPACE OR REDO LOG FILE OR MOVE THE FILE TO A NEW LOCATION 1. Get the fully qualified file name from the data dictionary and then shutdown the database. SQL> connect internal SQL> select FILE_NAME from DBA_DATA_FILES; SQL> select NAME from V$LOGFILE; SQL> disconnect SQL> shutdown 2. Copy the file to the new location using the backup command. (The backup qualifier "/delete" will delete the original file if the copy is successful.) $ backup/ignore=(interlock,nobackup) - :[] - :[] 3. Mount the database in exclusive mode, but do not open it. SQLDBA> startup exclusive mount 4. Rename the file in ORACLE using the exact string selected from DBA_DATA_FILES or V$LOGFILE (Step 1.) SQL> connect internal SQL> ALTER DATABASE SQL> rename file ':[]' SQL> to ':[]'; SQL> ALTER DATABASE open; 5. Backup the control file and any data files added after the last backup or do a complete database backup using your existing backup routine. SQL> ALTER DATABASE backup controlfile SQL> to ':[]'; SQL> ALTER TABLESPACE begin backup; SQL> host $ backup/ignore=(interlock,nobackup) - :[] - :[] ! repeat for each file $ logoff SQL> ALTER TABLESPACE end backup; ALTER TABLESPACE ---------------- The ALTER TABLESPACE command is used to rename non-system tablespace files and to add files to existing tablespaces. Note: The ALTER DATABASE command must be used to rename system tablespace files RENAME A NON-SYSTEM TABLESPACE FILE OR MOVE IT TO A NEW LOCATION 1. Get the fully qualified file name from the data dictionary. SQL> connect internal SQL> select FILE_NAME from DBA_DATA_FILES; 2. If possible, startup the database in DBA mode to prevent users from accessing the tablespace. SQL> shutdown SQL> startup dba open 3. Take the tablespace containing the file to be moved offline. SQL> connect internal SQL> ALTER TABLESPACE offline; 4. Copy the file to the new location using the backup command. (The backup qualifier "/delete" will delete the original file if the copy is successful.) $ backup/ignore=(interlock,nobackup) - :[] - :[] 5. Rename the file in ORACLE using the exact string from the FILE_NAME column of the data dictionary view DBA_DATA_FILES (Step 1.) SQL> connect internal SQL> ALTER TABLESPACE SQL> rename datafile ':[]' SQL> to ':[]'; 6. Bring online the tablespace containing the file that was moved. SQL> connect internal SQL> ALTER TABLESPACE online; 7. Startup the database in NORMAL mode if it was running in DBA mode. SQL> shutdown SQL> startup open ADD A FILE TO A TABLESPACE 1. Add the file to the tablespace, where is in bytes. SQL> connect internal SQL> ALTER TABLESPACE SQL> add datafile ':[]' SQL> size reuse; --------------------------------------------------------------------------- Oracle Worldwide Customer Support

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/17252115/viewspace-753284/，如需轉載，請註明出處，否則將追究法律責任。

相關文章