[20190412]bash顯示日期相減.txt

[20190412]bash顯示日期相減.txt

--//別人的系統前一段時間大量出現log file switch (checkpoint incomplete),我想了解日誌大概的切換情況.

--//結果對方資料庫控制檔案已經重建,無法透過一些檢視瞭解具體情況.我想alert檔案裡面儲存這些資訊.

--//在自己的系統測試看看,如何獲得日誌切換的時間間隔.

1.過濾切換日誌時的時間:

$ grep -B1 "(LGWR switch)" alert_hrp430.log  | grep "^... Apr 0[789].*2019$"

Sun Apr 07 01:11:04 2019

Sun Apr 07 10:54:46 2019

Sun Apr 07 19:00:34 2019

Mon Apr 08 01:11:04 2019

Mon Apr 08 08:29:24 2019

Mon Apr 08 11:05:25 2019

Mon Apr 08 18:06:26 2019

Tue Apr 09 01:11:04 2019

Tue Apr 09 08:00:42 2019

Tue Apr 09 08:57:09 2019

Tue Apr 09 10:01:06 2019

Tue Apr 09 11:31:46 2019

Tue Apr 09 15:50:09 2019

Tue Apr 09 17:49:43 2019

--//簡單1點把這個結果匯入資料庫表中再計算容易許多.

2.轉化為1970/1/1開始的秒數:

$ grep -B1 "(LGWR switch)" alert_hrp430.log  | grep "^... Apr 0[789].*2019$" | xargs -I{} date -d "{}" "+%s"

1554570664

1554605686

1554634834

1554657064

1554683364

1554692725

1554717986

1554743464

1554768042

1554771429

1554775266

1554780706

1554796209

1554803383

--//驗證看看是否轉化正確

$ date -d "1970-01-01 00:00:00 UTC 1554570664 seconds" +"%Y-%m-%d %T"

2019-04-07 01:11:04

3.如何實現上下相減呢?

$ grep -B1 "(LGWR switch)" alert_hrp430.log  | grep "^... Apr 0[789].*2019$" | xargs -I{} date -d "{}" "+%s" | awk 'NR==1{a=$1} NR>1{print $1-a;a=$1}'

35022

29148

22230

26300

9361

25261

25478

24578

3387

3837

5440

15503

7174

--//這樣就能獲得切換的時間(單位秒),驗證看看.

SYS@hrp430> select (to_date('Apr 07 10:54:46 2019','MON DD HH24:MI:SS YYYY')-to_date('Apr 07 01:11:04 2019','MON DD HH24:MI:SS YYYY'))*86400 N10 from dual ;

                  N10

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

                35022

--//OK完全正確.再修改看看:

$ grep -B1 "(LGWR switch)" alert_hrp430.log  | grep "^... Apr 0[789].*2019$" | xargs -I{} date -d "{}" "+%Y-%m-%d:%T %s" |  awk 'NR==1 {a=$1;b=$2} NR>1 {print $1,"-",a,$2-b;a=$1;b=$2}'

2019-04-07:10:54:46 - 2019-04-07:01:11:04 35022

2019-04-07:19:00:34 - 2019-04-07:10:54:46 29148

2019-04-08:01:11:04 - 2019-04-07:19:00:34 22230

2019-04-08:08:29:24 - 2019-04-08:01:11:04 26300

2019-04-08:11:05:25 - 2019-04-08:08:29:24 9361

2019-04-08:18:06:26 - 2019-04-08:11:05:25 25261

2019-04-09:01:11:04 - 2019-04-08:18:06:26 25478

2019-04-09:08:00:42 - 2019-04-09:01:11:04 24578

2019-04-09:08:57:09 - 2019-04-09:08:00:42 3387

2019-04-09:10:01:06 - 2019-04-09:08:57:09 3837

2019-04-09:11:31:46 - 2019-04-09:10:01:06 5440

2019-04-09:15:50:09 - 2019-04-09:11:31:46 15503

2019-04-09:17:49:43 - 2019-04-09:15:50:09 7174

--//這樣就比較好觀察了.再貼一個我們生產系統30分鐘定時切換的情況:

$ grep -B2 "(LGWR switch)" alert_fyhis1.log  | grep "^... Apr 12.*2019$" | xargs -I{} date -d "{}" "+%Y-%m-%d:%T %s" |  awk 'NR==1 {a=$1;b=$2} NR>1 {print $1,"-",a,$2-b;a=$1;b=$2}'

2019-04-12:00:31:22 - 2019-04-12:00:01:22 1800

2019-04-12:01:01:22 - 2019-04-12:00:31:22 1800

2019-04-12:01:13:44 - 2019-04-12:01:01:22 742       <--這裡是備份時日誌切換的情況.

2019-04-12:01:43:46 - 2019-04-12:01:13:44 1802

2019-04-12:02:13:47 - 2019-04-12:01:43:46 1801

2019-04-12:02:43:46 - 2019-04-12:02:13:47 1799

2019-04-12:03:13:49 - 2019-04-12:02:43:46 1803

2019-04-12:03:31:05 - 2019-04-12:03:13:49 1036

2019-04-12:04:01:13 - 2019-04-12:03:31:05 1808

2019-04-12:04:31:13 - 2019-04-12:04:01:13 1800

...

--//注這臺機器沒有使用實時傳輸日誌,要修改引數為-B2,不然無法顯示時間資訊.

--

Fri Apr 12 08:31:16 2019

LGWR: Standby redo logfile selected for thread 1 sequence 45458 for destination LOG_ARCHIVE_DEST_2

Thread 1 advanced to log sequence 45458 (LGWR switch)

--