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

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’;