Memindahkan atau Me-rename Datafile

Kadang kita perlu memindahkan datafile dari satu tempat (disk/file system/directory/drive) ke tempat yang lainnya. Atau kadang juga kita perlu me-rename datafile karena ada salah ketik waktu membuatnya. Baik memindahkan file dari satu tempat ke tempat lain, maupun merename datafile di tempat yang sama, intinya adalah sama saja.

Misalkan kita ingin memindahkan/me-rename datafile dari ‘/oradata/oracle/ts/users01.dbf’ ke ‘/oradata/oracle/ts/users02.dbf’

Pada database yang NOARCHIVELOG:

  1. Shutdown database
    SQL> shutdown immediate
    
  2. Pindahkan/move/rename datafile. Di Windows bisa pakai Windows explorer. Di unix gunakan command ini:
    mv /oradata/oracle/ts/users01.dbf /oradata/oracle/ts/users02.dbf
    
  3. Startup mount database
    SQL> startup mount
    
  4. Rename datafile di level database
    SQL> alter database rename file '/oradata/oracle/ts/users01.dbf' 
    to '/oradata/oracle/ts/users02.dbf';
    
  5. Setelah itu, open database
    SQL> alter database open;
    

Pada database yang ARCHIVELOG:

  1. Tidak perlu shutdown database. Cukup offline-kan datafile yang bersangkutan
    SQL> alter database datafile '/oradata/oracle/ts/users01.dbf' offline;
  2. Pindahkan/move/rename datafile. Di Windows bisa pakai Windows explorer. Di unix gunakan command ini:
    mv /oradata/oracle/ts/users01.dbf /oradata/oracle/ts/users02.dbf
  3. Rename datafile di level database
    SQL> alter database rename file '/oradata/oracle/ts/users01.dbf' 
    to '/oradata/oracle/ts/users02.dbf';
    
  4. Setelah itu, recover datafile yang telah di-rename tersebut
    SQL> recover datafile '/oradata/oracle/ts/users02.dbf';
  5. Terakhir, online-kan datafile yang telah di-rename tersebut
    alter database datafile '/oradata/oracle/ts/users01.dbf' online;

Pada metode pertama di atas (untuk database NOARCHIVELOG), mau tidak mau database tersebut tidak bisa diakses karena harus di-shutdown dulu (ada downtime).

Sementara pada metode kedua (untuk database ARCHIVELOG) database masih bisa diakses (baik query maupun transaksi), kecuali data (bytes) yang secara intrinsik disimpan di datafile tersebut tidak bisa diakses. Misalkan datafile diatas adalah milik tablespace USERS, dan tablespace USERS punya dua datafiles; maka data yang secara intriksik ada di datafile lain (bukan yang di-offline-kan tersebut) masih bisa diakses.

This entry was posted in Administration and tagged , , , , , , . Bookmark the permalink.

13 Responses to Memindahkan atau Me-rename Datafile

  1. murdi says:

    bagaimana jika kita inginmemindahkan lokasi datafile saja tamapa merename datafile..??
    misal lokasi awal di :
    /db/oracle/OraHome_1/oradata/DB_DWH/staging01.dbf

    lokasi tujuan :
    /db3/oradata/staging01.dbf

    dan apakah dalam process ini pertu adanya downtime user yg akses ke datafile staging01.dbf
    Status database saat ini dalam keadaan ARCHIVELOG.

  2. rohmadne says:

    Memindahkan datafile di OS HARUS disertai dengan me-rename datafile di database, tidak bisa tidak, alias wajib 🙂

    Meskipun database anda ARCHIVELOG, data yang secara instrinsik tersimpan di datafile tsb tidak akan bisa diakses karena datafilenya OFFLINE.

  3. rojie says:

    cara mengambil seluruh database yang ada di oracle,dan supaya tidak susah2 membuat lagi,,bagaimana cara mudahnya….

  4. yudi says:

    om, NOARCHIVELOG sama ARCHIVELOG itu untuk apa ya??

  5. cimohai says:

    terima kasih infonya mas.

  6. wendra aria says:

    makasih atas informasinya….

  7. I want to convey my admiration for your generosity giving support to persons who really want help with this niche. Your special commitment to getting the solution all over was extremely insightful and has truly enabled those like me to achieve their endeavors. This helpful key points denotes this much a person like me and especially to my fellow workers. With thanks; from everyone of us.

  8. Herman says:

    gan, kl cara untuk install companion oracle 10g gmn yah? kasih tw dunk step2-nya.
    Trims

  9. On April 30, 2009, Chrysler LLC decided to try and
    help you to keep yoour problems in perspective.
    Gradually as time went buy some you know 2, 3, 000
    and 100, 000 failures each year as well” Ebert & Griffin, R. You also prrobably know that Thomas Edixon was trying to say.

  10. ?he above and below signs are what I c?ll “Faux Bytes. Your subconscious mind actually controls telepathy and it can be encouraged with the right telepathy techniques. If we eat over processed foods the brain is likely to become constricted, and like any improperly fed muscle, weaken and not be able to do one of it’s jobs, which is to keep your reasoning sharp: to choose to eat properly and to feel full at the appropriate time.

  11. Garlic and Asparagus will make your urine smell and tast bad.
    They have also been used as a popular traditional Indian medicine that is extensively used to treat several
    kinds of diseases. What your ex boyfriend needs is some space for a while.

  12. halo e liquid Memindahkan atau Me-rename Datafile | Rohmad.net

  13. What should a website design company remember when coming up with a site for kids.
    Responsive design ensures that the layout of a website will
    probably be adjusted responding to the elements
    for example the width of the device that the visitor is using to access the website.
    Get a free of charge quote from e – Pixeltechnologies for PHP
    Web Development, applications services, interactive web
    designing, professional web design, custom web designing, flash animation, real estate property,
    affordable web site design outsourcing, cheap php outsourcing,
    joomla, component, ecommerce website design, solutions, open source project management.

Leave a Reply

Your email address will not be published. Required fields are marked *