Tuesday, September 25, 2012

Unix Script for renaming archivelog file with have different SID

Below useful unix script while performing Test Server Refresh.

cd /oracle/<TSID>/saparch
for i in 'ls|grep <SSID>';
do
file_name='echo $i |cut -dD -f2';
mv $i <TSID>${file_name};
done

Friday, September 21, 2012

Monitoring how much time physical standby database behind primary.

SQL> select * from V$DATAGUARD_STATS;

NAME                      VALUE              UNIT                           TIME_COMPUTED                  DATUM_TIME
------------------------- ------------------ ------------------------------ ------------------------------ ------------------------------
transport lag             +00 00:00:00       day(2) to second(0) interval   09/03/2012 18:32:37            09/03/2012 18:32:35
apply lag                 +00 12:05:02       day(2) to second(0) interval   09/03/2012 18:32:37            09/03/2012 18:32:35
apply finish time         +00 00:00:04.972   day(2) to second(3) interval   09/03/2012 18:32:37
estimated startup time    23                 second                         09/03/2012 18:32:37

SQL>

if you see above apply lag showing 12 hrs 05 min and 2 sec behind primary.

but when check archived log list @ primary.
SQL> archive log list;
Database log mode              Archive Mode
Automatic archival             Enabled
Archive destination            USE_DB_RECOVERY_FILE_DEST
Oldest online log sequence     1145
Next log sequence to archive   1148
Current log sequence           1148
SQL>

as per above current redo log sequence 1148

Now when i checked @ standby server last applied log sequence number.

SQL> select sequence# last_sequence#,archived,applied,status from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log);

LAST_SEQUENCE# ARC APPLIED   S
-------------- --- --------- -
          1147 YES YES       A

SQL>

its showing 1147

last archived log already applied @ standby, So how apply tag showing value of 12 hrs and 05 min and 2 sec behind primary ?

Check last archived log time at primary.
[root@testsap1 ~]# ls -la /oracle/EHP/oraarch/ | grep 1147
-rw-r-----  1 oraehp dba 43153408 Sep  3 06:22 EHParch1_1147_790785542.dbf
[root@testsap1 ~]#

Its difference of last archived time and current time. ( current time 18:27:03 last archive log (1147) @ 06:22 ).

Tuesday, September 11, 2012

Installed RPM Package 32bit / 64bit

Normally we execute below command to know package installed or not and you get result similar to below.
[root@testsap1 ~]# rpm -qa |grep libaio-0.3.106
libaio-0.3.106-5
libaio-0.3.106-5

Need to execute as below to know installed package are 32bit or 64bit ?
[root@testsap1 ~]# rpm -q --qf '%{NAME}-%{VERSION}(%{ARCH})\n' libaio-0.3.106
libaio-0.3.106(x86_64)
libaio-0.3.106(i386)
[root@testsap1 ~]#