[20160119]V$RMAN_OUTPUT的stamp.txt

[20160119]V$RMAN_OUTPUT的stamp.txt

--今天检查无意中发现2016/1/2日志切换频繁。http://blog.itpub.net/267265/viewspace-1979024/
--放假回来后检查发现dg磁盘空间满了，当时清除我并有仔细看，因为没有相关记录，我想看看当时如何执行一些操作，
--查询V$RMAN_OUTPUT发现里面并没有明显的时间记录，仅仅存在STAMP，SESSION_STAMP，RMAN_STATUS_STAMP字段。

--我自己想做一些尝试，是否可以把stamp转换为时间：

--//更正:严重错误,应该按照如下公式计算:
time = (((((yyyy - 1988) * 12 + mm - 1) * 31 + dd - 1) * 24 + hh) * 60 + mi) * 60 + ss;
--//参考新链接:
http://blog.itpub.net/267265/viewspace-2135046/

1.环境：
SYS@book> @ &r/ver1
PORT_STRING                    VERSION        BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx            11.2.0.4.0     Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production

--在测试环境下我重启数据库，我检查发现V$RMAN_OUTPUT没有输出，并且如果我登录rman后有输出，另外我发现stamp是增加的比原来。
--也就是讲这个东西应该是某个日期以来记录的一个单位。到底是什么呢？

2.测试：
--建立如下脚本执行它：

$ cat aa.rman
# !/bin/bash
rman target /  append <<eof host "date +'%Y/%m/%d %T'>> aa.log";
host "sleep 10";
host "date +'%Y/%m/%d %T'>> aa.log";
EOF

SYS@book> column OUTPUT format a52
SYS@book> select * from V$RMAN_OUTPUT where session_key=204;
SID RECID      STAMP SESSION_RECID SESSION_STAMP OUTPUT                                               RMAN_STATUS_RECID RMAN_STATUS_STAMP SESSION_KEY
--- ----- ---------- ------------- ------------- ---------------------------------------------------- ----------------- ----------------- -----------
101   190  901555071           204     901555071 connected to target database: BOOK (DBID=1337401710)               204         901555071         204
101   191  901555071           204     901555071                                                                    204         901555071         204
101   192  901555071           204     901555071                                                                    204         901555071         204
101   193  901555071           204     901555071 host command complete                                              204         901555071         204
101   194  901555071           204     901555071                                                                    204         901555071         204
101   195  901555071           204     901555071                                                                    204         901555071         204
101   196  901555081           204     901555071 host command complete                                              204         901555071         204
101   197  901555081           204     901555071                                                                    204         901555071         204
101   198  901555081           204     901555071                                                                    204         901555071         204
101   199  901555081           204     901555071 host command complete                                              204         901555071         204
101   200  901555082           204     901555071                                                                    204         901555071         204
101   201  901555082           204     901555071                                                                    204         901555071         204
101   202  901555082           204     901555071                                                                    204         901555071         204
101   203  901555082           204     901555071                                                                    204         901555071         204

14 rows selected.

$ cat aa.log
2016/01/19 15:57:51
2016/01/19 15:58:01

--从上面stamp的数值901555081-901555071=10,正好10秒。说明stamp的单位应该是秒。

SYS@book> select to_date('2016/01/19 15:57:51','yyyy/mm/dd hh24:mi:ss')-901555071/86400 C20 from dual;
C20
--------------------
1987-06-26 00:00:00

--从这里看出这个日期非常规整。不知道这个日期对于oracle意味着什么？

3.有了这个起点，确定时间就方便了。

SYS@book> select date '1987-06-26' +stamp/86400 c20 ,a.* from V$RMAN_OUTPUT a where a.session_key=204;
C20                  SID RECID      STAMP SESSION_RECID SESSION_STAMP OUTPUT                                               RMAN_STATUS_RECID RMAN_STATUS_STAMP SESSION_KEY
-------------------- --- ----- ---------- ------------- ------------- ---------------------------------------------------- ----------------- ----------------- -----------
2016-01-19 15:57:51  101   190  901555071           204     901555071 connected to target database: BOOK (DBID=1337401710)               204         901555071         204
2016-01-19 15:57:51  101   191  901555071           204     901555071                                                                    204         901555071         204
2016-01-19 15:57:51  101   192  901555071           204     901555071                                                                    204         901555071         204
2016-01-19 15:57:51  101   193  901555071           204     901555071 host command complete                                              204         901555071         204
2016-01-19 15:57:51  101   194  901555071           204     901555071                                                                    204         901555071         204
2016-01-19 15:57:51  101   195  901555071           204     901555071                                                                    204         901555071         204
2016-01-19 15:58:01  101   196  901555081           204     901555071 host command complete                                              204         901555071         204
2016-01-19 15:58:01  101   197  901555081           204     901555071                                                                    204         901555071         204
2016-01-19 15:58:01  101   198  901555081           204     901555071                                                                    204         901555071         204
2016-01-19 15:58:01  101   199  901555081           204     901555071 host command complete                                              204         901555071         204
2016-01-19 15:58:02  101   200  901555082           204     901555071                                                                    204         901555071         204
2016-01-19 15:58:02  101   201  901555082           204     901555071                                                                    204         901555071         204
2016-01-19 15:58:02  101   202  901555082           204     901555071                                                                    204         901555071         204
2016-01-19 15:58:02  101   203  901555082           204     901555071                                                                    204         901555071         204
14 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/267265/viewspace-1979123/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/267265/viewspace-1979123/