v$rman_backup_job_details.END_TIME problem
From: Sreejith S Nair <sreejithsna_at_gmail.com>
Date: Sat, 10 Aug 2013 09:57:16 +0530
Message-Id: <B7371CF4-015D-434B-9757-231906A26634_at_gmail.com>
Sreejith_Nair 09-Aug-2013 06:04
Hi List,
We have daily incremental, every two hour archivelog and weekly dull backups scheduled for our production database. The database is registered with timezone 'Australia' SQL> select sysdate from dual;
09-AUG-2013 15:45
SQL> !date
Fri Aug 9 15:45:48 EST 2013
SQL> !env | grep TZ
TZ=Australia/NSW
SQL> !crontab -l | grep rman
0 12 * * 0 sh /projects1/oramroprod/dba_scripts/rman_fullbackup_mroprod.sh 0 12 * * 1-6 sh /projects1/oramroprod/dba_scripts/rman_incrbackup_mroprod.sh 0 00,02,04,06,08,10,14,16,18,20,22 * * * sh /projects1/oramroprod/dba_scripts/rman_archbackup_mroprod.sh SQL> select * from v$version;
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod PL/SQL Release 10.2.0.3.0 - Production CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production SQL> !uname -a
SunOS ibs-ash-sr120 5.10 Generic_147441-12 i86pc i386 i86pc SQL> !cat /etc/release
I am finding a strange inconsistency on the data in v$rman_backup_job_details. The database is very small and backup duration will not exceed 30 minutes.However for INCRMENTAL LEVEL 1 and LEVEL 0 backups are showing DURATION as 10 hours!.
Date: Sat, 10 Aug 2013 09:57:16 +0530
Message-Id: <B7371CF4-015D-434B-9757-231906A26634_at_gmail.com>
Sreejith_Nair 09-Aug-2013 06:04
Hi List,
We have daily incremental, every two hour archivelog and weekly dull backups scheduled for our production database. The database is registered with timezone 'Australia' SQL> select sysdate from dual;
SYSDATE
09-AUG-2013 15:45
SQL> !date
Fri Aug 9 15:45:48 EST 2013
SQL> !env | grep TZ
TZ=Australia/NSW
SQL> !crontab -l | grep rman
0 12 * * 0 sh /projects1/oramroprod/dba_scripts/rman_fullbackup_mroprod.sh 0 12 * * 1-6 sh /projects1/oramroprod/dba_scripts/rman_incrbackup_mroprod.sh 0 00,02,04,06,08,10,14,16,18,20,22 * * * sh /projects1/oramroprod/dba_scripts/rman_archbackup_mroprod.sh SQL> select * from v$version;
BANNER
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod PL/SQL Release 10.2.0.3.0 - Production CORE 10.2.0.3.0 Production
TNS for Solaris: Version 10.2.0.3.0 - Production NLSRTL Version 10.2.0.3.0 - Production SQL> !uname -a
SunOS ibs-ash-sr120 5.10 Generic_147441-12 i86pc i386 i86pc SQL> !cat /etc/release
Oracle Solaris 10 8/11 s10x_u10wos_17b X86
Copyright (c) 1983, 2011, Oracle and/or its affiliates. All rights reserved.
Assembled 23 August 2011
SQL>
I am finding a strange inconsistency on the data in v$rman_backup_job_details. The database is very small and backup duration will not exceed 30 minutes.However for INCRMENTAL LEVEL 1 and LEVEL 0 backups are showing DURATION as 10 hours!.
The RMAN backup jobs run by cron are scheduled to run at 12 GMT. Backup job starts as expected.But v$rman_backup_job_details.END_TIME is updated in 'Australia' timezone while start time is updated in GMT.
Another thing is for archivelog backups END_TIME is updated in GMT only.
Any clues ?
SQL> set lines 300
alter session set nls_date_format='DD-MON-YYYY HH24:MI';
select INPUT_TYPE,START_TIME,END_TIME,STATUS,ELAPSED_SECONDS/60/60 ELAPSED_HR from v$rman_backup_job_details where START_TIME > trunc(sysdate-7) order by START_TIME asc;SQL>
Session altered.
INPUT_TYPE START_TIME END_TIME STATUS ELAPSED_HRI had posted same question in OTN yesterday. Posting it here for more audience.
------------- ----------------- ----------------- ----------------------- ----------
ARCHIVELOG 07-AUG-2013 16:00 07-AUG-2013 16:01 COMPLETED .016944444 ARCHIVELOG 07-AUG-2013 18:00 07-AUG-2013 18:01 COMPLETED .013055556 ARCHIVELOG 07-AUG-2013 18:00 07-AUG-2013 18:01 COMPLETED .008333333 ARCHIVELOG 07-AUG-2013 20:00 07-AUG-2013 20:00 COMPLETED .007222222 ARCHIVELOG 08-AUG-2013 02:00 08-AUG-2013 02:01 COMPLETED .028611111 ARCHIVELOG 08-AUG-2013 08:00 08-AUG-2013 08:01 COMPLETED .015555556 ARCHIVELOG 08-AUG-2013 10:00 08-AUG-2013 10:02 COMPLETED .030833333 ARCHIVELOG 08-AUG-2013 10:00 08-AUG-2013 10:01 COMPLETED .009166667 ARCHIVELOG 08-AUG-2013 12:00 08-AUG-2013 12:01 COMPLETED .016388889 DB INCR 08-AUG-2013 12:00 08-AUG-2013 22:00 COMPLETED 10.0002778 ARCHIVELOG 08-AUG-2013 14:00 08-AUG-2013 14:01 COMPLETED .021666667 INPUT_TYPE START_TIME END_TIME STATUS ELAPSED_HR
------------- ----------------- ----------------- ----------------------- ----------
ARCHIVELOG 08-AUG-2013 14:00 08-AUG-2013 14:00 COMPLETED .006388889 ARCHIVELOG 08-AUG-2013 16:00 08-AUG-2013 16:01 COMPLETED .022222222 ARCHIVELOG 08-AUG-2013 16:00 08-AUG-2013 16:01 COMPLETED .017222222 ARCHIVELOG 08-AUG-2013 18:00 08-AUG-2013 18:01 COMPLETED .014722222 ARCHIVELOG 08-AUG-2013 18:00 08-AUG-2013 18:00 COMPLETED .0075 ARCHIVELOG 08-AUG-2013 20:00 08-AUG-2013 20:00 COMPLETED .005833333 ARCHIVELOG 09-AUG-2013 08:00 09-AUG-2013 08:01 COMPLETED .018333333 ARCHIVELOG 09-AUG-2013 10:00 09-AUG-2013 10:01 COMPLETED .023611111 ARCHIVELOG 09-AUG-2013 12:00 09-AUG-2013 12:01 COMPLETED .018611111
Sreejith
Sent from my iPhone
--
http://www.freelists.org/webpage/oracle-l
Received on Sat Aug 10 2013 - 06:27:16 CEST