Mas Bambang.,
ada 2 metoda yang bisa digunakan untuk memindahkan datafile kita ke lokasi tertentu.,. ONLINE dan OFFILINE OFFLINE dilakukan pada saat database dalam kondisi MOUNT., namun sebaiknya gunakan ONLINE database Sepengetahuan saya.,untuk mengganti path tidak perlu mengcereate control file., Create controlfile dilakukan pada saat CURRENT control file hilang /corrupt., atau ingin memindahkan database ke mesin yang lain namun nama database-nya ingin dirubah., Caranya., ========== SQL> conn / as sysdba Connected. SQL> SELECT file_name, tablespace_name, online_status 2 FROM dba_data_files; FILE_NAME TABLESPACE_NAME ONLINE_ ------------------------------------------------------------ ------------------------------ ------- C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF USERS ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF SYSAUX ONLINE .. . . C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF EXAMPLE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\PERFSTAT_TBS01.SQL PERFSTAT_TBS ONLINE C:\MSSM01.DBF MSSM ONLINE SQL> ALTER TABLESPACE mssm OFFLINE; Tablespace altered. SQL> SELECT file_name, tablespace_name, online_status 2 FROM dba_data_files; FILE_NAME TABLESPACE_NAME ONLINE_ ------------------------------------------------------------ ------------------------------ ------- C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF USERS ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF SYSAUX ONLINE .. . . C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\PERFSTAT_TBS01.SQL PERFSTAT_TBS ONLINE C:\MSSM01.DBF MSSM OFFLINE -- COPY datafile yang akan dipindahkan ke path yang baru -- SQL> HOST COPY C:\MSSM01.dbf D:\lokasi_baru\MMS01.dbf SQL> ALTER DATABASE RENAME FILE 'C:\MSSM01.DBF' TO 'D:\lokasi_baru\MMS01.dbf'; Database altered. SQL> ALTER TABLESPACE mssm ONLINE; Tablespace altered. SQL> SELECT file_name, tablespace_name, online_status 2 FROM dba_data_files; FILE_NAME TABLESPACE_NAME ONLINE_ ------------------------------------------------------------ ------------------------------ ------- C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\USERS01.DBF USERS ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\SYSAUX01.DBF SYSAUX ONLINE .. . . C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\EXAMPLE01.DBF EXAMPLE ONLINE C:\ORACLE\PRODUCT\10.2.0\ORADATA\ORCL\PERFSTAT_TBS01.SQL PERFSTAT_TBS ONLINE C:\MSSM01.DBF MSSM ONLINE --- On Fri, 7/11/08, Bambang HP <[EMAIL PROTECTED]> wrote: From: Bambang HP <[EMAIL PROTECTED]> Subject: [indo-oracle] ORA-01503:CREATE CONTROLFILE FAILED To: [email protected] Date: Friday, July 11, 2008, 10:48 AM Teman-teman mohon bantuannya.. saya ingin mengganti path datafile, saya menjalankannya di SQL SQL>@/opt/oracle/ control.sql isi dari control.sql sebagai berikut: STARTUP NOMOUNT CREATE CONTROLFILE REUSE DATABASE "PROD" NORESETLOGS NOARCHIVELOG MAXLOGFILES 16 MAXLOGMEMBERS 3 MAXDATAFILES 100 MAXINSTANCES 8 MAXLOGHISTORY 292 LOGFILE GROUP 1 '/opt/oracle/ oradata/prod/ redo01.log' SIZE 50M, GROUP 2 '/opt/oracle/ oradata/prod/ redo02.log' SIZE 50M, GROUP 3 '/opt/oracle/ oradata/prod/ redo03.log' SIZE 50M -- STANDBY LOGFILE DATAFILE '/opt/oracle/ oradata/system01 .dbf', '/opt/oracle/ oradata/undotbs0 1.dbf', '/opt/oracle/ oradata/sysaux01 .dbf', '/opt/oracle/ oradata/users01. dbf' CHARACTER SET WE8ISO8859P1 ; -- Commands to re-create incarnation table -- Below log names MUST be changed to existing filenames on -- disk. Any one log file from each branch can be used to -- re-create incarnation records. -- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/ flash_recovery_ area/prod/ archivelog/ 2008_07_10/ o1_mf_1_1_ %u_.arc'; -- ALTER DATABASE REGISTER LOGFILE '/opt/oracle/ flash_recovery_ area/prod/ archivelog/ 2008_07_10/ o1_mf_1_1_ %u_.arc'; -- Recovery is required if any of the datafiles are restored backups, -- or if the last shutdown was not normal or immediate. RECOVER DATABASE -- Database can now be opened normally. ALTER DATABASE OPEN; setelah saya jalankan keluar error:ORA-01503: CREATE CONTROLFILE FAILED Mohon bantuannya terima kasih [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
