Oracle 線上日誌管理
--新增線上日誌組
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 0 52428800 512 1 YES UNUSED 1339338 2015/5/9 21 1339339 2015/5/9 21
3 1 0 52428800 512 1 YES UNUSED 0 0
2 1 2 52428800 512 1 NO CURRENT 1339339 2015/5/9 21 281474976710
SQL> alter database add logfile group 4('F:\DATA\DATAFILE\MXQ\REDO04.LOG','F:\DATA\DATAFILE\MXQ\REDO05.LOG') size 10m;
Database altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 0 52428800 512 1 YES UNUSED 1339338 2015/5/9 21 1339339 2015/5/9 21
4 1 0 10485760 512 2 YES UNUSED 0 0
3 1 0 52428800 512 1 YES UNUSED 0 0
2 1 2 52428800 512 1 NO CURRENT 1339339 2015/5/9 21 281474976710
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
1 ONLINE F:\DATA\DATAFILE\MXQ\REDO01.LOG NO
2 ONLINE F:\DATA\DATAFILE\MXQ\REDO02.LOG NO
3 ONLINE F:\DATA\DATAFILE\MXQ\REDO03.LOG NO
4 ONLINE F:\DATA\DATAFILE\MXQ\REDO04.LOG NO
4 ONLINE F:\DATA\DATAFILE\MXQ\REDO05.LOG NO
--新增日誌成員
SQL> ALTER DATABASE ADD LOGFILE MEMBER 'F:\DATA\DATAFILE\MXQ\REDO07.LOG' TO GROUP 4;
Database altered
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
1 ONLINE F:\DATA\DATAFILE\MXQ\REDO01.LOG NO
2 ONLINE F:\DATA\DATAFILE\MXQ\REDO02.LOG NO
3 ONLINE F:\DATA\DATAFILE\MXQ\REDO03.LOG NO
4 ONLINE F:\DATA\REDO06.LOG NO
4 ONLINE F:\DATA\DATAFILE\MXQ\REDO05.LOG NO
4 ONLINE F:\DATA\DATAFILE\MXQ\REDO07.LOG NO
6 rows selected
--刪除日誌成員
物理檔案是不會自動刪除需要手動刪除日誌檔案
SQL> ALTER DATABASE drop LOGFILE MEMBER 'F:\DATA\DATAFILE\MXQ\REDO07.LOG';
Database altered
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
1 ONLINE F:\DATA\DATAFILE\MXQ\REDO01.LOG NO
2 ONLINE F:\DATA\DATAFILE\MXQ\REDO02.LOG NO
3 ONLINE F:\DATA\DATAFILE\MXQ\REDO03.LOG NO
4 ONLINE F:\DATA\REDO06.LOG NO
4 ONLINE F:\DATA\DATAFILE\MXQ\REDO05.LOG NO
--刪除線上日誌組
物理檔案是不會自動刪除需要手動刪除日誌檔案
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 0 52428800 512 1 YES UNUSED 1339338 2015/5/9 21 1339339 2015/5/9 21
2 1 2 52428800 512 1 NO CURRENT 1339339 2015/5/9 21 281474976710
3 1 0 52428800 512 1 YES UNUSED 0 0
4 1 0 10485760 512 2 YES UNUSED 0 0
SQL> alter database drop logfile group 4;
Database altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 0 52428800 512 1 YES UNUSED 1339338 2015/5/9 21 1339339 2015/5/9 21
2 1 2 52428800 512 1 NO CURRENT 1339339 2015/5/9 21 281474976710
3 1 0 52428800 512 1 YES UNUSED 0 0
SQL>
資料要啟動mount狀態
在把原來物理檔案複製新的路徑下面在執行下面語句進行移動正常重新命名
SQL> startup mount;
ORACLE 例程已經啟動。
Total System Global Area 3390558208 bytes
Fixed Size 2259920 bytes
Variable Size 1879049264 bytes
Database Buffers 1493172224 bytes
Redo Buffers 16076800 bytes
資料庫裝載完畢。
SQL> alter database rename file 'F:\DATA\DATAFILE\MXQ\REDO04.LOG' to 'F:\DATA\RE
DO04.LOG';
資料庫已更改。
SQL> alter database rename file 'F:\DATA\REDO04.LOG' to 'F:\DATA\REDO06.LOG';
資料庫已更改。
SQL> alter database open;
資料庫已更改。
--重新初始化線上日誌組
SQL> alter database clear logfile group 3;
資料庫已更改。
--重新初始化當前沒有歸檔線上日誌住
SQL> alter database clear unarchived logfile group 1;
資料庫已更改。
線上調整線上日誌組大小
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 0 52428800 512 1 YES UNUSED 1339338 2015/5/9 21 1339339 2015/5/9 21
2 1 2 52428800 512 1 NO CURRENT 1339339 2015/5/9 21 281474976710
3 1 0 52428800 512 1 YES UNUSED 0 0
--新增兩個日誌組
SQL> alter database add logfile group 4('F:\DATA\DATAFILE\MXQ\REDO04.LOG') size 100m;
Database altered
SQL>
SQL> alter database add logfile group 5('F:\DATA\DATAFILE\MXQ\REDO05.LOG') size 100m;
Database altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 0 52428800 512 1 YES UNUSED 1339338 2015/5/9 21 1339339 2015/5/9 21
2 1 2 52428800 512 1 NO CURRENT 1339339 2015/5/9 21 281474976710
3 1 0 52428800 512 1 YES UNUSED 0 0
4 1 0 104857600 512 1 YES UNUSED 0 0
5 1 0 104857600 512 1 YES UNUSED 0 0
SQL> alter system switch logfile;
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 3 52428800 512 1 NO CURRENT 1342022 2015/5/9 22 281474976710
2 1 2 52428800 512 1 YES ACTIVE 1339339 2015/5/9 21 1342022 2015/5/9 22
3 1 0 52428800 512 1 YES UNUSED 0 0
4 1 0 104857600 512 1 YES UNUSED 0 0
5 1 0 104857600 512 1 YES UNUSED 0 0
--切換日誌兩次
SQL> alter system checkpoint;
System altered
SQL> alter system switch logfile;
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 3 52428800 512 1 YES ACTIVE 1342022 2015/5/9 22 1342068 2015/5/9 22
2 1 2 52428800 512 1 YES ACTIVE 1339339 2015/5/9 21 1342022 2015/5/9 22
3 1 4 52428800 512 1 YES ACTIVE 1342068 2015/5/9 22 1342078 2015/5/9 22
4 1 5 104857600 512 1 NO CURRENT 1342078 2015/5/9 22 281474976710
5 1 0 104857600 512 1 YES UNUSED 0 0
--讓在日誌組歸檔
SQL> alter system checkpoint;
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 3 52428800 512 1 YES INACTIVE 1342022 2015/5/9 22 1342068 2015/5/9 22
2 1 2 52428800 512 1 YES INACTIVE 1339339 2015/5/9 21 1342022 2015/5/9 22
3 1 4 52428800 512 1 YES INACTIVE 1342068 2015/5/9 22 1342078 2015/5/9 22
4 1 5 104857600 512 1 NO CURRENT 1342078 2015/5/9 22 281474976710
5 1 0 104857600 512 1 YES UNUSED 0 0
--刪除線上日誌組1、2、3
SQL> alter database drop logfile group 1;
Database altered
SQL> alter database drop logfile group 2;
Database altered
SQL> alter database drop logfile group 3;
Database altered
--新增在日誌組1、2、3
SQL> alter database add logfile group 1('F:\DATA\DATAFILE\MXQ\REDO01.LOG') size 100m;
Database altered
SQL> alter database add logfile group 2('F:\DATA\DATAFILE\MXQ\REDO02.LOG') size 100m;
Database altered
SQL> alter database add logfile group 3('F:\DATA\DATAFILE\MXQ\REDO03.LOG') size 100m;
Database altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 0 104857600 512 1 YES UNUSED 0 0
2 1 0 104857600 512 1 YES UNUSED 0 0
3 1 0 104857600 512 1 YES UNUSED 0 0
4 1 5 104857600 512 1 NO CURRENT 1342078 2015/5/9 22 281474976710
5 1 0 104857600 512 1 YES UNUSED 0 0
--切換日誌兩次
SQL> alter system switch logfile;
System altered
SQL> alter system switch logfile;
System altered
SQL> alter system checkpoint;
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 6 104857600 512 1 YES INACTIVE 1342251 2015/5/9 22 1342254 2015/5/9 22
2 1 7 104857600 512 1 NO CURRENT 1342254 2015/5/9 22 281474976710
3 1 0 104857600 512 1 YES UNUSED 0 0
4 1 5 104857600 512 1 YES INACTIVE 1342078 2015/5/9 22 1342251 2015/5/9 22
5 1 0 104857600 512 1 YES UNUSED 0 0
--刪除線上日誌組4、5
SQL> alter database drop logfile group 4;
Database altered
SQL> alter database drop logfile group 5;
Database altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 6 104857600 512 1 YES INACTIVE 1342251 2015/5/9 22 1342254 2015/5/9 22
2 1 7 104857600 512 1 NO CURRENT 1342254 2015/5/9 22 281474976710
3 1 0 104857600 512 1 YES UNUSED 0 0
ADD LOGFILE GOURP COMPLETE
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 0 52428800 512 1 YES UNUSED 1339338 2015/5/9 21 1339339 2015/5/9 21
3 1 0 52428800 512 1 YES UNUSED 0 0
2 1 2 52428800 512 1 NO CURRENT 1339339 2015/5/9 21 281474976710
SQL> alter database add logfile group 4('F:\DATA\DATAFILE\MXQ\REDO04.LOG','F:\DATA\DATAFILE\MXQ\REDO05.LOG') size 10m;
Database altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 0 52428800 512 1 YES UNUSED 1339338 2015/5/9 21 1339339 2015/5/9 21
4 1 0 10485760 512 2 YES UNUSED 0 0
3 1 0 52428800 512 1 YES UNUSED 0 0
2 1 2 52428800 512 1 NO CURRENT 1339339 2015/5/9 21 281474976710
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
1 ONLINE F:\DATA\DATAFILE\MXQ\REDO01.LOG NO
2 ONLINE F:\DATA\DATAFILE\MXQ\REDO02.LOG NO
3 ONLINE F:\DATA\DATAFILE\MXQ\REDO03.LOG NO
4 ONLINE F:\DATA\DATAFILE\MXQ\REDO04.LOG NO
4 ONLINE F:\DATA\DATAFILE\MXQ\REDO05.LOG NO
--新增日誌成員
SQL> ALTER DATABASE ADD LOGFILE MEMBER 'F:\DATA\DATAFILE\MXQ\REDO07.LOG' TO GROUP 4;
Database altered
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
1 ONLINE F:\DATA\DATAFILE\MXQ\REDO01.LOG NO
2 ONLINE F:\DATA\DATAFILE\MXQ\REDO02.LOG NO
3 ONLINE F:\DATA\DATAFILE\MXQ\REDO03.LOG NO
4 ONLINE F:\DATA\REDO06.LOG NO
4 ONLINE F:\DATA\DATAFILE\MXQ\REDO05.LOG NO
4 ONLINE F:\DATA\DATAFILE\MXQ\REDO07.LOG NO
6 rows selected
--刪除日誌成員
物理檔案是不會自動刪除需要手動刪除日誌檔案
SQL> ALTER DATABASE drop LOGFILE MEMBER 'F:\DATA\DATAFILE\MXQ\REDO07.LOG';
Database altered
SQL> select * from v$logfile;
GROUP# STATUS TYPE MEMBER IS_RECOVERY_DEST_FILE
---------- ------- ------- -------------------------------------------------------------------------------- ---------------------
1 ONLINE F:\DATA\DATAFILE\MXQ\REDO01.LOG NO
2 ONLINE F:\DATA\DATAFILE\MXQ\REDO02.LOG NO
3 ONLINE F:\DATA\DATAFILE\MXQ\REDO03.LOG NO
4 ONLINE F:\DATA\REDO06.LOG NO
4 ONLINE F:\DATA\DATAFILE\MXQ\REDO05.LOG NO
--刪除線上日誌組
物理檔案是不會自動刪除需要手動刪除日誌檔案
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 0 52428800 512 1 YES UNUSED 1339338 2015/5/9 21 1339339 2015/5/9 21
2 1 2 52428800 512 1 NO CURRENT 1339339 2015/5/9 21 281474976710
3 1 0 52428800 512 1 YES UNUSED 0 0
4 1 0 10485760 512 2 YES UNUSED 0 0
SQL> alter database drop logfile group 4;
Database altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 0 52428800 512 1 YES UNUSED 1339338 2015/5/9 21 1339339 2015/5/9 21
2 1 2 52428800 512 1 NO CURRENT 1339339 2015/5/9 21 281474976710
3 1 0 52428800 512 1 YES UNUSED 0 0
SQL>
資料要啟動mount狀態
在把原來物理檔案複製新的路徑下面在執行下面語句進行移動正常重新命名
SQL> startup mount;
ORACLE 例程已經啟動。
Total System Global Area 3390558208 bytes
Fixed Size 2259920 bytes
Variable Size 1879049264 bytes
Database Buffers 1493172224 bytes
Redo Buffers 16076800 bytes
資料庫裝載完畢。
SQL> alter database rename file 'F:\DATA\DATAFILE\MXQ\REDO04.LOG' to 'F:\DATA\RE
DO04.LOG';
資料庫已更改。
SQL> alter database rename file 'F:\DATA\REDO04.LOG' to 'F:\DATA\REDO06.LOG';
資料庫已更改。
SQL> alter database open;
資料庫已更改。
--重新初始化線上日誌組
SQL> alter database clear logfile group 3;
資料庫已更改。
--重新初始化當前沒有歸檔線上日誌住
SQL> alter database clear unarchived logfile group 1;
資料庫已更改。
線上調整線上日誌組大小
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 0 52428800 512 1 YES UNUSED 1339338 2015/5/9 21 1339339 2015/5/9 21
2 1 2 52428800 512 1 NO CURRENT 1339339 2015/5/9 21 281474976710
3 1 0 52428800 512 1 YES UNUSED 0 0
--新增兩個日誌組
SQL> alter database add logfile group 4('F:\DATA\DATAFILE\MXQ\REDO04.LOG') size 100m;
Database altered
SQL>
SQL> alter database add logfile group 5('F:\DATA\DATAFILE\MXQ\REDO05.LOG') size 100m;
Database altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 0 52428800 512 1 YES UNUSED 1339338 2015/5/9 21 1339339 2015/5/9 21
2 1 2 52428800 512 1 NO CURRENT 1339339 2015/5/9 21 281474976710
3 1 0 52428800 512 1 YES UNUSED 0 0
4 1 0 104857600 512 1 YES UNUSED 0 0
5 1 0 104857600 512 1 YES UNUSED 0 0
SQL> alter system switch logfile;
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 3 52428800 512 1 NO CURRENT 1342022 2015/5/9 22 281474976710
2 1 2 52428800 512 1 YES ACTIVE 1339339 2015/5/9 21 1342022 2015/5/9 22
3 1 0 52428800 512 1 YES UNUSED 0 0
4 1 0 104857600 512 1 YES UNUSED 0 0
5 1 0 104857600 512 1 YES UNUSED 0 0
--切換日誌兩次
SQL> alter system checkpoint;
System altered
SQL> alter system switch logfile;
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 3 52428800 512 1 YES ACTIVE 1342022 2015/5/9 22 1342068 2015/5/9 22
2 1 2 52428800 512 1 YES ACTIVE 1339339 2015/5/9 21 1342022 2015/5/9 22
3 1 4 52428800 512 1 YES ACTIVE 1342068 2015/5/9 22 1342078 2015/5/9 22
4 1 5 104857600 512 1 NO CURRENT 1342078 2015/5/9 22 281474976710
5 1 0 104857600 512 1 YES UNUSED 0 0
--讓在日誌組歸檔
SQL> alter system checkpoint;
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 3 52428800 512 1 YES INACTIVE 1342022 2015/5/9 22 1342068 2015/5/9 22
2 1 2 52428800 512 1 YES INACTIVE 1339339 2015/5/9 21 1342022 2015/5/9 22
3 1 4 52428800 512 1 YES INACTIVE 1342068 2015/5/9 22 1342078 2015/5/9 22
4 1 5 104857600 512 1 NO CURRENT 1342078 2015/5/9 22 281474976710
5 1 0 104857600 512 1 YES UNUSED 0 0
--刪除線上日誌組1、2、3
SQL> alter database drop logfile group 1;
Database altered
SQL> alter database drop logfile group 2;
Database altered
SQL> alter database drop logfile group 3;
Database altered
--新增在日誌組1、2、3
SQL> alter database add logfile group 1('F:\DATA\DATAFILE\MXQ\REDO01.LOG') size 100m;
Database altered
SQL> alter database add logfile group 2('F:\DATA\DATAFILE\MXQ\REDO02.LOG') size 100m;
Database altered
SQL> alter database add logfile group 3('F:\DATA\DATAFILE\MXQ\REDO03.LOG') size 100m;
Database altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 0 104857600 512 1 YES UNUSED 0 0
2 1 0 104857600 512 1 YES UNUSED 0 0
3 1 0 104857600 512 1 YES UNUSED 0 0
4 1 5 104857600 512 1 NO CURRENT 1342078 2015/5/9 22 281474976710
5 1 0 104857600 512 1 YES UNUSED 0 0
--切換日誌兩次
SQL> alter system switch logfile;
System altered
SQL> alter system switch logfile;
System altered
SQL> alter system checkpoint;
System altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 6 104857600 512 1 YES INACTIVE 1342251 2015/5/9 22 1342254 2015/5/9 22
2 1 7 104857600 512 1 NO CURRENT 1342254 2015/5/9 22 281474976710
3 1 0 104857600 512 1 YES UNUSED 0 0
4 1 5 104857600 512 1 YES INACTIVE 1342078 2015/5/9 22 1342251 2015/5/9 22
5 1 0 104857600 512 1 YES UNUSED 0 0
--刪除線上日誌組4、5
SQL> alter database drop logfile group 4;
Database altered
SQL> alter database drop logfile group 5;
Database altered
SQL> select * from v$log;
GROUP# THREAD# SEQUENCE# BYTES BLOCKSIZE MEMBERS ARCHIVED STATUS FIRST_CHANGE# FIRST_TIME NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- ---------- -------- ---------------- ------------- ----------- ------------ -----------
1 1 6 104857600 512 1 YES INACTIVE 1342251 2015/5/9 22 1342254 2015/5/9 22
2 1 7 104857600 512 1 NO CURRENT 1342254 2015/5/9 22 281474976710
3 1 0 104857600 512 1 YES UNUSED 0 0
ADD LOGFILE GOURP COMPLETE
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/29065182/viewspace-1631744/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- RAC 線上日誌的管理
- oracle效能調優:管理oracle日誌之調整線上日誌檔案Oracle
- 【REDO】Oracle 日誌挖掘，分析歸檔日誌線上日誌主要步驟Oracle
- 線上日誌損壞
- 非線上日誌丟失
- oracle 線上日誌全部丟失的資料恢復Oracle資料恢復
- Oracle日誌管理Oracle
- ORACLE的日誌管理Oracle
- 收集、分析線上日誌資料實戰——ELK
- 線上日誌檔案損壞恢復方法
- oracle之 Oracle歸檔日誌管理Oracle
- oracle 日誌檔案管理Oracle
- Oracle歸檔日誌管理技巧Oracle
- Oracle 日誌管理一例Oracle
- 18【線上日誌分析】之Spark on Yarn配置日誌Web UI(HistoryServer服務)SparkYarnWebUIServer
- 06【線上日誌分析】之KafkaOffsetMonitor監控工具的搭建Kafka
- Oracle archive log 歸檔日誌管理OracleHive
- 使用外部表管理Oracle 告警日誌Oracle
- 控制檔案 線上日誌 回滾表空間 筆記筆記
- 16【線上日誌分析】之grafana-4.1.1 Install和新建日誌分析的DashBoardGrafana
- 節點2線上日誌生成歸檔日誌在節點1上的初步分析
- oracle11G歸檔日誌管理Oracle
- Oracle 監聽器日誌配置與管理Oracle
- Oracle的日誌管理攻略(網文摘錄)Oracle
- 22【線上日誌分析】之專案第二階段概述
- 11【線上日誌分析】之redis-3.2.5 install(單節點)Redis
- 08【線上日誌分析】之Flume Agent(聚合節點) sink to kafka clusterKafka
- 00【線上日誌分析】之專案概述和GitHub專案地址Github
- Oracle10g 應用日誌演算法的變化 - 可以使用resetlogs之後的歸檔及線上日誌Oracle應用日誌演算法
- oracle logminer分析線上 離線日誌方法Oracle
- 20170315測試線上日誌與備用日誌大小不一樣
- [20181225]如何清除註冊的線上日誌.txt
- 17【線上日誌分析】之使用IDEA將工程Build成jar包IdeaUIJAR
- 前線上日誌檔案損壞與ora-600 [4000]處理
- Oracle RAC中使用RMAN管理歸檔日誌Oracle
- 管理oracle日誌之調整檢查點Oracle
- 2 Day DBA-管理Oracle例項-管理聯機重做日誌-切換日誌檔案Oracle
- 2 Day DBA-管理Oracle例項-管理聯機重做日誌Oracle