Bugra Canbakal's Oracle Blog

  • Home
  • Contact
  • SITE MAP
You are here: Home / Oracle / Oracle veritabanını başka bir alana taşıma

Oracle veritabanını başka bir alana taşıma

07:22 AM By Bugra Canbakal 1 Comment

1.AMAÇ VE KAPSAM

Oracle controlfile, logfile ve datafile taşıma işlemi

2.UYGULAMA

2.1. Control file’ın lokasyonunu değiştirme.

2.1.1 Control file şuanki lokasyonunu buluyoruz.

SQL> select name from v$controlfile;

NAME

————————————————————-

/U01/APP/ORACLE/ORADATA/CONTROL01.CTL

/U01/APP/ORACLE/ORADATA/CONTROL02.CTL

/U01/APP/ORACLE/ORADATA/CONTROL03.CTL

3 rows selected.

SQL>

2.1.2. Direk lokasyona mv scriptini hazırlamak için. (dosyanın tutulduğu lokasyona göre substr nin ayarlanması gerekir)

SQL> select ‘ mv ‘|| name || ‘ /yeni_disk/’ || substr(name,30) || ” from v$controlfile;

‘MV’||NAME||’/YENI_DISK/’||SUBSTR(NAME,30)||”

——————————————————————————–

mv /u01/app/oracle/oradata/orcl/control01.ctl /yeni_disk/control01.ctl

mv /u01/app/oracle/oradata/orcl/control02.ctl /yeni_disk/control02.ctl

mv /u01/app/oracle/oradata/orcl/control03.ctl /yeni_disk/control03.ctl

2.1.3. Control file’ın lokasyonunu spfile üstünde değiştiriyoruz.

SQL> ALTER SYSTEM SET control_files=’/u02/app/oracle/oradata/controlfile/CONTROL01.CTL’, ‘/u02/app/oracle/oradata/controlfile/CONTROL02.CTL’,’/u02/app/oracle/oradata/controlfile/CONTROL03.CTL’ SCOPE=SPFILE;

Database altered.

2.1.4. Veritabanını kapatıp controlfile’ları yeni lokasyonlarına taşıyoruz.

SQL> SHUTDOWN IMMEDIATE

Database closed.

Database dismounted.

ORACLE instance shut down.

$ mv /U01/APP/ORACLE/ORADATA/CONTROL01.CTL /u02/app/oracle/oradata/controlfile/CONTROL01.CTL

$ mv /U01/APP/ORACLE/ORADATA/CONTROL02.CTL /u02/app/oracle/oradata/controlfile/CONTROL02.CTL

$ mv /U01/APP/ORACLE/ORADATA/CONTROL03.CTL /u02/app/oracle/oradata/controlfile/CONTROL03.CTL

2.1.5. Sistemi yeniden açıyoruz.

SQL> STARTUP

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                   787968 bytes

Variable Size              61864448 bytes

Database Buffers          104857600 bytes

Redo Buffers                 262144 bytes

Database mounted.

SQL>

2.2. Logfile lokasyonunu değiştirme.

2.2.1. Logfile şuanki lokasyonunu buluyoruz.

SQL> SELECT member FROM v$logfile;

MEMBER

————————————————-

/U01/APP/ORACLE/ORADATA/REDO/REDO03.LOG

/U01/APP/ORACLE/ORADATA/REDO/REDO02.LOG

/U01/APP/ORACLE/ORADATA/REDO/REDO01.LOG

3 rows selected.

SQL>

2.2.2. Direk lokasyona mv scriptini hazırlamak için. (dosyanın tutulduğu lokasyona göre substr nin ayarlanması gerekir)

SQL> select ‘ mv ‘|| member || ‘ /yeni_disk/’ || substr(member,30) || ” from v$logfile;

‘MV’||MEMBER||’/YENI_DISK/’||SUBSTR(MEMBER,30)||”

——————————————————————————–

mv /u01/app/oracle/oradata/orcl/redo03.log /yeni_disk/redo03.log

mv /u01/app/oracle/oradata/orcl/redo02.log /yeni_disk/redo02.log

mv /u01/app/oracle/oradata/orcl/redo01.log /yeni_disk/redo01.log

2.2.3. Veritabanını kapatıyoruz.

SQL> SHUTDOWN IMMEDIATE

Database closed.

Database dismounted.

ORACLE instance shut down.

2.2.4. Logfileları yeni lokasyonlarına taşıyoruz.

$ mv /U01/APP/ORACLE/ORADATA/REDO/REDO03.LOG /u02/app/oracle/oradata/redo/REDO03.LOG

$ mv /U01/APP/ORACLE/ORADATA/REDO/REDO02.LOG /u02/app/oracle/oradata/redo/REDO02.LOG

$ mv /U01/APP/ORACLE/ORADATA/REDO/REDO01.LOG /u02/app/oracle/oradata/redo/REDO01.LOG

2.2.5. Direk lokasyonu scriptle değiştirip komutu hazırlamak için. (dosyanın tutulduğu lokasyona göre substr nin ayarlanması gerekir)

SQL> select ‘ ALTER DATABASE RENAME FILE ”’|| member || ”’ to ”/yeni_disk/’ || substr(member,30) || ”’;’ from v$logfile;

‘ALTERDATABASERENAMEFILE”’||MEMBER||”’TO”/YENI_DISK/’||SUBSTR(MEMBER,30)||”’

——————————————————————————–

ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/orcl/redo03.log’ to ‘/yeni_

disk/redo03.log’;

ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/orcl/redo02.log’ to ‘/yeni_

disk/redo02.log’;

ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/orcl/redo01.log’ to ‘/yeni_

disk/redo01.log’;

2.2.6. Database mount posizyonunda açıp controlfile üstünde log dosyalarının yerini değiştiriyoruz.

SQL> STARTUP MOUNT

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                   787968 bytes

Variable Size              61864448 bytes

Database Buffers          104857600 bytes

Redo Buffers                 262144 bytes

Database mounted.

SQL> ALTER DATABASE RENAME FILE ‘/U01/APP/ORACLE/ORADATA/REDO/REDO03.LOG’ TO ‘/u02/app/oracle/oradata/redo/REDO03.LOG’;

Database altered.

SQL> ALTER DATABASE RENAME FILE ‘/U01/APP/ORACLE/ORADATA/REDO/REDO02.LOG’ TO ‘/u02/app/oracle/oradata/redo/REDO02.LOG’;

Database altered.

SQL> ALTER DATABASE RENAME FILE ‘/U01/APP/ORACLE/ORADATA/REDO/REDO01.LOG’ TO ‘/u02/app/oracle/oradata/redo/REDO01.LOG’;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

2.3. Datafile lokasyonunu değiştirme.

2.3.1. Datafileların şuanki lokasyonunu buluyoruz.

SQL> SELECT name FROM v$datafile;

NAME

———————————————————

/U01/APP/ORACLE/ORADATA/DF/SYSTEM01.DBF

/U01/APP/ORACLE/ORADATA/DF/UNDOTBS01.DBF

/U01/APP/ORACLE/ORADATA/DF/SYSAUX01.DBF

/U01/APP/ORACLE/ORADATA/DF/USERS01.DBF

4 rows selected.

2.3.2. Direk lokasyona mv scriptini hazırlamak için. ( dosyanın tutulduğu lokasyona göre substr nin ayarlanması gerekir)

SQL> select ‘ mv ‘|| name || ‘ /yeni_disk/’ || substr(name,30) || ” from v$datafile;

‘MV’||NAME||’/YENI_DISK/’||SUBSTR(NAME,30)||”

——————————————————————————–

mv /u01/app/oracle/oradata/orcl/system01.dbf /yeni_disk/system01.dbf

mv /u01/app/oracle/oradata/orcl/sysaux01.dbf /yeni_disk/sysaux01.dbf

mv /u01/app/oracle/oradata/orcl/undotbs01.dbf /yeni_disk/undotbs01.dbf

mv /u01/app/oracle/oradata/orcl/users01.dbf /yeni_disk/users01.dbf

mv /u01/app/oracle/oradata/orcl/example01.dbf /yeni_disk/example01.dbf

2.3.3. Veritabanını kapatıyoruz.

SQL> SHUTDOWN IMMEDIATE

Database closed.

Database dismounted.

ORACLE instance shut down.

2.3.4. Datafileları yeni lokasyonlarına kopyalıyoruz.

$ mv /U01/APP/ORACLE/ORADATA/DF/SYSTEM01.DBF /u02/app/oracle/oradata/datafile/SYSTEM01.DBF

$ mv /U01/APP/ORACLE/ORADATA/DF/UNDOTBS01.DBF /u02/app/oracle/oradata/datafile/UNDOTBS01.DBF

$ mv /U01/APP/ORACLE/ORADATA/DF/SYSAUX01.DBF /u02/app/oracle/oradata/datafile/SYSAUX01.DBF

$ mv /U01/APP/ORACLE/ORADATA/DF/USERS01.DBF /u02/app/oracle/oradata/datafile/USERS01.DBF

2.3.5. Veritabanını mount posizyonda açıp datafileların ismini değiştiriyoruz.Ve lokasyon değiştirme işlemi bittiğinde veritabanını açabiliriz.

SQL> STARTUP MOUNT

ORACLE instance started.

Total System Global Area  167772160 bytes

Fixed Size                   787968 bytes

Variable Size              61864448 bytes

Database Buffers          104857600 bytes

Redo Buffers                 262144 bytes

Database mounted.

SQL> ALTER DATABASE RENAME FILE ‘/U01/APP/ORACLE/ORADATA/DF/SYSTEM01.DBF’ TO ‘/u02/app/oracle/oradata/datafile/SYSTEM01.DBF’;

Database altered.

SQL> ALTER DATABASE RENAME FILE ‘/U01/APP/ORACLE/ORADATA/DF/UNDOTBS01.DBF’ TO ‘/u02/app/oracle/oradata/datafile/UNDOTBS01.DBF’;

Database altered.

SQL> ALTER DATABASE RENAME FILE ‘/U01/APP/ORACLE/ORADATA/DF/SYSAUX01.DBF ‘ TO ‘/u02/app/oracle/oradata/datafile/SYSAUX01.DBF ‘;

Database altered.

SQL> ALTER DATABASE RENAME FILE ‘/U01/APP/ORACLE/ORADATA/DF/USERS01.DBF’ TO ‘/u02/app/oracle/oradata/datafile/USERS01.DBF’;

Database altered.

SQL> ALTER DATABASE OPEN;

Database altered.

2.3.6. Lokasyonu scriptle değiştirip komutu hazırlamak için. ( dosyanın tutulduğu lokasyona göre substr nin ayarlanması gerekir)

SQL> select ‘ ALTER DATABASE RENAME FILE ”’|| name || ”’ to ”/yeni_disk/’ || substr(name,30) || ”’;’ from v$datafile;

‘ALTERDATABASERENAMEFILE”’||NAME||”’TO”/YENI_DISK/’||SUBSTR(NAME,30)||”’;’

——————————————————————————–

ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/orcl/system01.dbf’ to ‘/yen

i_disk/system01.dbf’;

ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/orcl/sysaux01.dbf’ to ‘/yen

i_disk/sysaux01.dbf’;

ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/orcl/undotbs01.dbf’ to ‘/ye

ni_disk/undotbs01.dbf’;

ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/orcl/users01.dbf’ to ‘/yeni

_disk/users01.dbf’;

‘ALTERDATABASERENAMEFILE”’||NAME||”’TO”/YENI_DISK/’||SUBSTR(NAME,30)||”’;’

——————————————————————————–

ALTER DATABASE RENAME FILE ‘/u01/app/oracle/oradata/orcl/example01.dbf’ to ‘/ye

ni_disk/example01.dbf’;

Share this:

  • Click to share on LinkedIn (Opens in new window) LinkedIn
  • Click to share on X (Opens in new window) X

Filed Under: Oracle Tagged With: oracle, oracle database taşıma, oracle veritabanı taşıma

Comments

  1. Berkay Ünlü says

    Monday June 27th, 2016 at 02:57 PM

    İyi günler Buğra Bey, 2.1.2. de bulunan ve dosya uzantısının parçalanmasını sağlayan sql komutunu biraz daha açıklamanız mümkün mü? özellikle ” ” yerleştirmede sıkıntılı durum var galiba.

  2. Bugra Canbakal says

    Tuesday June 28th, 2016 at 05:41 AM

    Merhaba Berkay bey,

    Saniirim siteyi baska template gecirirken bazi sikintilar olusmus ondan dolayi “” lar queryde bulunmaktaydi. Bana sadece controlfile in isimleri gerkemekteydi bundan dolayi oncelikle “/U01/APP/ORACLE/ORADATA/” dizininde kac karakter olduguna baktim. Toplam olarak 29 karakter bulunmaktaydi bunun ustunde substr(name,30) fonksiyonunu kullanarak 29. karakterden sonrasini aldim.

Leave a ReplyCancel reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

About Me



Language:

  • English
  • Turkish

Categories

  • Oracle
    • RAC – Real Application Cluster
    • RMAN
    • Single Node
    • Standby – Dataguard
  • OS
    • Linux

Blogroll

  • Emre Baransel
  • Gökhan Atıl
  • H. Tonguc Yılmaz
  • Kamil Türkyılmaz
  • Tanel Poder
  • Turkce Oracle
  • Turkish Oracle User Group
  • Uwe Hesse
  • Zekeriya Beşiroğlu

Archives

  • April 2014
  • March 2014
  • August 2013
  • June 2013
  • March 2013
  • January 2013
  • December 2012
  • November 2012
  • July 2012
  • June 2012
  • May 2012
  • January 2012
  • November 2011
  • October 2011
  • August 2011
  • July 2011
  • June 2011
  • February 2011
  • January 2011
  • December 2010
  • November 2010
  • June 2010
OCP
Privacy & Cookies: This site uses cookies. By continuing to use this website, you agree to their use.
To find out more, including how to control cookies, see here: Cookie Policy

[footer_backtotop]

Copyright © 2010-2014 Bugra Canbakal. All rights reserved.