遷移redo log到不同的儲存路徑

實際運維過程中，我們也經常需要遷移redo log到不同的儲存路徑，或者修改redo log的大小等等，下面整理了兩種遷移redo log的方式。

1) 方案1:停止資料庫例項遷移redo log

a、查詢資料redo log

SQL> select group#,type,member from v$logfile;

GROUP# TYPE MEMBER

--------- ------- ---------------------------

3 ONLINE /u01/oradata/tydb/redo03.log

2 ONLINE /u01/oradata/tydb/redo02.log

1 ONLINE /u01/oradata/tydb/redo01.log

b、關閉資料庫

$ sqlplus / as sysdba

SQL> shutdown immediate

Database closed.

Database dismounted.

ORACLE instance shut down.

c、複製redo log到新的儲存路徑

$ cp /u01/oradata/tydb/redo03.log /u01/oradata/redo03.log

$ cp /u01/oradata/tydb/redo02.log /u01/oradata/redo02.log

$ cp /u01/oradata/tydb/redo01.log /u01/oradata/redo01.log

d、將資料庫啟動到mount狀態

$ sqlplus / as sysdba

SQL> startup mount

ORACLE instance started.

Total System Global Area 599785472 bytes

Fixed Size 2022632 bytes

Variable Size 171967256 bytes

Database Buffers 423624704 bytes

Redo Buffers 2170880 bytes

Database mounted.

e、重新命名redo log成員

SQL> alter database rename file '/u01/oradata/tydb/redo03.log','/u01/oradata/tydb/redo02.log','/u01/oradata/tydb/redo01.log' to '/u01/oradata/redo03.log','/u01/oradata/redo02.log','/u01/oradata/redo01.log';

Database altered.

f、開啟資料庫

SQL> alter database open;

Database altered.

SQL> select open_mode from v$database;

OPEN_MODE

----------

READ WRITE

g、檢查確認日誌遷移成功

SQL> select group#,type,member from v$logfile;

GROUP# TYPE MEMBER

---------- ------- ----------------------------------------

3 ONLINE /u01/oradata/redo03.log

2 ONLINE /u01/oradata/redo02.log

1 ONLINE /u01/oradata/redo01.log

由於這種方式需要停止資料庫進行操作，很多核心業務系統是很難接受的，而且在redo log使用ASM儲存的情況下，或者需要將檔案系統或者裸裝置遷移到ASM中都會變得非常不便，特別是在10g版本中，ASM和檔案系統不能直接copy，需要藉助Oracle提供的程式包進行複製；所以在大多數的情況下，都是不會採用以上遷移方案的，更多的會選擇下面的線上遷移方案。

2) 方案2:線上遷移redo log

a、查詢當前redo log組

SQL> select group#,type,member from v$logfile;

GROUP# TYPE MEMBER

---------- ------- ----------------------------------------

3 ONLINE /u01/oradata/redo03.log

2 ONLINE /u01/oradata/redo02.log

1 ONLINE /u01/oradata/redo01.log

b、新增新的redo log組

SQL> alter database add logfile group 4('/u01/oradata/tydb/redo04_1.log','/u01/oradata/tydb/redo04_2.log') size 100M;

Database altered.

SQL> alter database add logfile group 5('/u01/oradata/tydb/redo05.log') size 100m;

Database altered.

SQL> alter database add logfile group 6('/u01/oradata/tydb/redo06.log') size 100m;

Database altered.

這裡順便操作了一個redo log組中新增兩個日誌成員的情況；如果是RAC環境，只需在group前加上thread關鍵字即可(如：alter database add logfile thread 2 group 5('/u01/oradata/tydb/redo05.log') size 100m;)。

c、檢視新增新日誌組後的日誌情況

SQL> select group#,type,member from v$logfile;

GROUP# TYPE MEMBER

---------- ------- ----------------------------------------

3 ONLINE /u01/oradata/redo03.log

2 ONLINE /u01/oradata/redo02.log

1 ONLINE /u01/oradata/redo01.log

4 ONLINE /u01/oradata/tydb/redo04_1.log

4 ONLINE /u01/oradata/tydb/redo04_2.log

54 ONLINE /u01/oradata/tydb/redo05.log

6 ONLINE /u01/oradata/tydb/redo06.log

7 rows selected.

SQL> select group#,archived,status from v$log;

GROUP# ARC STATUS

---------- --- ----------------

1 NO CURRENT

2 YES UNUSED

3 NO INACTIVE

4 YES UNUSED

6 YES UNUSED

54 YES UNUSED

6 rows selected.

d、刪除舊的日誌組

SQL> alter database drop logfile group 3;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter database drop logfile group 1;

Database altered.

SQL> alter system switch logfile;

System altered.

SQL> alter system checkpoint;

System altered.

SQL> alter database drop logfile group 2;

Database altered.

e、檢查遷移後的redo log

SQL> select group#,type,member from v$logfile;

GROUP# TYPE MEMBER

---------- ------- ----------------------------------------

4 ONLINE /u01/oradata/tydb/redo04_1.log

4 ONLINE /u01/oradata/tydb/redo04_2.log

5 ONLINE /u01/oradata/tydb/redo05.log

6 ONLINE /u01/oradata/tydb/redo06.log

SQL> select group#,archived,status from v$log;

GROUP# ARC STATUS

---------- --- ----------------

4 NO CURRENT

6 YES INACTIVE

5 YES INACTIVE

至此，redo log 已經遷移成功。

f、檢查之前的redo log檔案是否已經成功刪除，沒有刪除可以手動刪除

$ ls -l /u01/oradata/redo*

-rw-r----- 1 oracle oinstall 52429312 Jan 22 00:20 /u01/oradata/redo01.log

-rw-r----- 1 oracle oinstall 52429312 Jan 22 00:21 /u01/oradata/redo02.log

-rw-r----- 1 oracle oinstall 52429312 Jan 21 23:59 /u01/oradata/redo03.log

$ rm redo*

Created by Tony.Tang[TangYun]2015.02
－－－－－－－－－－－－－－－－－－－－－－－－End－－－－－－－－－－－－－－－－－－－－－－－－－－－

Redo Log之二：遷移redo log到不同的儲存路徑

遷移redo log到不同的儲存路徑

1) 方案1:停止資料庫例項遷移redo log

2) 方案2:線上遷移redo log

相關文章