Search This Blog

Friday, 24 November 2017

Indexes with the density below 75%:

Recommendation to rebuild:

==========================================================================
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>Indexes that should be rebuilt - list of indexes with density below 75%</TITLE> -
<STYLE type='text/css'> -
<!-- BODY {background: #ffffff} --> -
</STYLE>" -
BODY "TEXT='#212121'"
--TABLE "WIDTH='70%' BORDER='1'"

set termout off;
set echo off;
set verify off
SET FEEDBACK OFF;


begin
DBMS_OUTPUT.put_line('Script Runtime: '||(DBMS_UTILITY.get_time - :l_start));
:l_start := DBMS_UTILITY.get_time;
end;
/

set echo on
column filename new_val filename
column filename2 new_val filename2
select 'sparse_indexes_' ||instance_name||'_'||(select to_char(sysdate, 'yyyymmdd' ) from dual)||'.html' filename from v$instance;
select 'rebuild_indexes_' ||instance_name||'_'||(select to_char(sysdate, 'yyyymmdd' ) from dual)||'.sql' filename2 from v$instance;
set echo off

set define &

spool &filename

column index_name format a40
column part_name format a20
column subpart_name format a20
column last_ddl_time  format a13
column x noprint new_value x
column last_analyzed format a13
column compression format a11
define density='75'
define obj = 'nvl(isp.obj#, nvl(ip.obj#,i.obj#))'
define objp = 'nvl(ip.obj#,i.obj#)'
define rowcnt = 'decode(&obj, isp.obj#, isp.rowcnt, ip.obj#, ip.rowcnt, i.rowcnt)'
define leafcnt = 'decode(&obj, isp.obj#, isp.leafcnt, ip.obj#, ip.leafcnt, i.leafcnt)'
define pctf = 'decode(&obj, isp.obj#, isp.pctfree$, ip.obj#, ip.pctfree$, i.pctfree$)'
define initr = 'decode(&obj, isp.obj#, isp.initrans, ip.obj#, ip.initrans, i.initrans)'
define last_analyzed = 'nvl(decode(&obj, isp.obj#, isp.analyzetime, ip.obj#, ip.analyzetime, i.analyzetime),to_date(''01.01.1900'',''dd.mm.yyyy''))'
define compress = 'decode(&obj, isp.obj#, ''N/A'', ip.obj#, decode(bitand(ip.flags, 1024), 0, ''DISABLED'', 1024, ''ENABLED'',''N/A''), decode(bitand(i.flags, 32), 0, ''DISABLED'', 32, ''ENABLED'',''N/A''))'

alter session set nls_date_format='DD-MON-YYYY';

SET MARKUP HTML ENTMAP OFF
PROMPT <br></br>
SET MARKUP HTML ENTMAP ON

select
  instance_name as Database_Name,
  (SELECT SYS_CONTEXT('USERENV','HOST') FROM dual) as Hostname,
  (select sysdate from dual) as Report_created
from
  v$instance;

SET MARKUP HTML ENTMAP OFF
PROMPT <br></br>
SET MARKUP HTML ENTMAP ON

select  /*ORACONF*/ /*+ ordered */
  u.name ||'.'|| o.name  index_name,
  op.subname part_name,
  decode(&obj, isp.obj#, o.subname, '') subpart_name,
  to_char(100*(1 - floor( &leafcnt -
    &rowcnt * (sum(h.avgcln) + 10) / ((p.value - 66 - &initr * 24)*(1 - &pctf/100))
  )/&leafcnt),'999.00') ||'%' density,
  floor( &leafcnt -
  &rowcnt * (sum(h.avgcln) + 10) / ((p.value - 66 - &initr * 24)*(1 - &pctf/100))
  ) extra_blocks,
  max(o.mtime) last_ddl_time,
  decode(max(&last_analyzed),to_date('01.01.1900','dd.mm.yyyy'),'not analyzed',max(&last_analyzed)) last_analyzed,
  max(&compress) compression,
  (select index_type as index_type from dba_indexes where index_name=o.name and owner=u.name) index_type,
  (select sum(bytes/1024/1024) as index_size from dba_segments where segment_name=o.name and owner=u.name) index_size_in_MB
from
  sys.ind$  i,
  sys.icol$  ic,
  ( select obj#, part#, bo#, ts#, rowcnt, leafcnt, initrans, pctfree$, analyzetime, flags from sys.indpart$
    union all
    select  /*ORACONF*/ obj#, part#, bo#, defts#, rowcnt, leafcnt, definitrans, defpctfree, analyzetime, flags from sys.indcompart$ ) ip,
  sys.indsubpart$ isp,
  ( select ts#, blocksize value
    from sys.ts$
      )  p,
  sys.hist_head$  h,
  sys.obj$  o,
  sys.user$  u,
  sys.obj$  op
where
  i.obj# = ip.bo#(+) and
  ip.obj# = isp.pobj#(+) and
  &leafcnt > 1 and
  i.type# in (1) and -- exclude special types
  i.pctthres$ is null and -- exclude IOT secondary indexes
  decode(&obj, isp.obj#, isp.ts#, ip.obj#, ip.ts#, i.ts#) = p.ts# and
  ic.obj# = i.obj# and
  h.obj# = i.bo# and
  h.intcol# = ic.intcol# and
  o.obj# = &obj and
  o.owner# != 0 and
  u.user# = o.owner# and
  op.obj# = &objp
group by
  u.name,
  o.name,
  op.subname,
  decode(&obj, isp.obj#, o.subname, ''),
  &rowcnt,
  &leafcnt,
  &initr,
  &pctf,
  p.value
  having
    100*(1 - floor( &leafcnt -
    &rowcnt * (sum(h.avgcln) + 10) / ((p.value - 66 - &initr * 24)*(1 - &pctf/100))
    )/&leafcnt) <= nvl('&density','75') and
    floor( &leafcnt -
      &rowcnt * (sum(h.avgcln) + 10) / ((p.value - 66 - &initr * 24)*(1 - &pctf/100))
    ) > 0
order by
  5 desc, 4
/


undefine rowcnt
undefine leafcnt
undefine pctf
undefine initr
undefine last_analyzed
undefine compress
undefine obj
undefine density
spool off
set markup html off;
set termout on;
--prompt ORACONF:  sparse_tables.html
set termout off;


spool &filename2
SET   SERVEROUTPUT  OFF
set pages 0

set lines 200
column index_name format a200
define density='75'
define obj = 'nvl(isp.obj#, nvl(ip.obj#,i.obj#))'
define objp = 'nvl(ip.obj#,i.obj#)'
define rowcnt = 'decode(&obj, isp.obj#, isp.rowcnt, ip.obj#, ip.rowcnt, i.rowcnt)'
define leafcnt = 'decode(&obj, isp.obj#, isp.leafcnt, ip.obj#, ip.leafcnt, i.leafcnt)'
define pctf = 'decode(&obj, isp.obj#, isp.pctfree$, ip.obj#, ip.pctfree$, i.pctfree$)'
define initr = 'decode(&obj, isp.obj#, isp.initrans, ip.obj#, ip.initrans, i.initrans)'
define last_analyzed = 'nvl(decode(&obj, isp.obj#, isp.analyzetime, ip.obj#, ip.analyzetime, i.analyzetime),to_date(''01.01.1900'',''dd.mm.yyyy''))'
define compress = 'decode(&obj, isp.obj#, ''N/A'', ip.obj#, decode(bitand(ip.flags, 1024), 0, ''DISABLED'', 1024, ''ENABLED'',''N/A''), decode(bitand(i.flags, 32), 0, ''DISABLED'', 32, ''ENABLED'',''N/A''))'

select  /*ORACONF*/ /*+ ordered */
  'alter index '||u.name ||'.'|| o.name||' rebuild online parallel 4;'  index_name
from
  sys.ind$  i,
  sys.icol$  ic,
  ( select obj#, part#, bo#, ts#, rowcnt, leafcnt, initrans, pctfree$, analyzetime, flags from sys.indpart$
    union all
    select  /*ORACONF*/ obj#, part#, bo#, defts#, rowcnt, leafcnt, definitrans, defpctfree, analyzetime, flags from sys.indcompart$ ) ip,
  sys.indsubpart$ isp,
  ( select ts#, blocksize value
    from sys.ts$
      )  p,
  sys.hist_head$  h,
  sys.obj$  o,
  sys.user$  u,
  sys.obj$  op
where
  i.obj# = ip.bo#(+) and
  ip.obj# = isp.pobj#(+) and
  &leafcnt > 1 and
  i.type# in (1) and -- exclude special types
  i.pctthres$ is null and -- exclude IOT secondary indexes
  decode(&obj, isp.obj#, isp.ts#, ip.obj#, ip.ts#, i.ts#) = p.ts# and
  ic.obj# = i.obj# and
  h.obj# = i.bo# and
  h.intcol# = ic.intcol# and
  o.obj# = &obj and
  o.owner# != 0 and
  u.user# = o.owner# and
  op.obj# = &objp
group by
  u.name,
  o.name,
  op.subname,
  decode(&obj, isp.obj#, o.subname, ''),
  &rowcnt,
  &leafcnt,
  &initr,
  &pctf,
  p.value
  having
    100*(1 - floor( &leafcnt -
    &rowcnt * (sum(h.avgcln) + 10) / ((p.value - 66 - &initr * 24)*(1 - &pctf/100))
    )/&leafcnt) <= nvl('&density','75') and
    floor( &leafcnt -
      &rowcnt * (sum(h.avgcln) + 10) / ((p.value - 66 - &initr * 24)*(1 - &pctf/100))
    ) > 0
/

undefine rowcnt
undefine leafcnt
undefine pctf
undefine initr
undefine last_analyzed
undefine compress
undefine obj
undefine density
spool off


begin
DBMS_OUTPUT.put_line('Script Runtime: '||(DBMS_UTILITY.get_time - :l_start));
:l_start := DBMS_UTILITY.get_time;
end;
/
============================================================================
Output :  (If you run above sql scrip(any_name.sql). As an output are two files)
Note: please check from the report if all indexes are normal indexes (bit map indexes are not possible to rebuild online)
=> html report (sparse_indexes_<SID_<DATE>.html)  -with all indexes with the density below 75%.
=> sql file (rebuild_indexes_<SID>_<DATE>.sql) - you can run it to rebuild indexes




Performance details on specific query exection :


To get the details about query executions,reads:

set lines 190 pages 200;
col START_TIME for a20;
col SQL_ID for a20;

select        to_char(s.BEGIN_INTERVAL_TIME,'MON-DD:HH24:MI') START_TIME, sql_id,
                sum(ss.executions_delta) sum_execs,
                sum(ss.disk_reads_delta) sum_disk_reads,
                sum(ss.buffer_gets_delta) sum_buffer_gets,
                sum(ss.rows_processed_delta) sum_rows_processed,
                sum(ss.cpu_time_delta)/1000000 sum_cpu_time,
                sum(ss.elapsed_time_delta)/1000000 sum_elapsed_time
        from    dba_hist_sqlstat        ss,
                dba_hist_snapshot       s
        where   ss.dbid = s.dbid
        and     ss.instance_number = s.instance_number
        and     ss.snap_id = s.snap_id
        and     ss.sql_id =   ('8drpg36hd9jy3') --- set sql_id
        and     ss.executions_delta > 0
        and     s.begin_interval_time >= sysdate-20    ------- how many days back in time to look
        group by to_char(s.BEGIN_INTERVAL_TIME,'MON-DD:HH24:MI'), sql_id
        order by 1 desc;


 START_TIME           SQL_ID                SUM_EXECS SUM_DISK_READS SUM_BUFFER_GETS SUM_ROWS_PROCESSED SUM_CPU_TIME SUM_ELAPSED_TIME
-------------------- -------------------- ---------- -------------- --------------- ------------------ ------------ ----------------
NOV-22:14:00         8drpg36hd9jy3                 1          88763          801169                311    11.287735       120.676823
NOV-22:11:00         8drpg36hd9jy3                 1         105158         1288418                316    15.681973       689.428523
NOV-22:10:00         8drpg36hd9jy3                 1          11085           68606                  0     6.379018       342.579322

3 rows selected.
 To GET Plan_hash_value on specific SQL_ID:
 
select ss.snap_id, ss.instance_number node, begin_interval_time, sql_id, plan_hash_value,
nvl(executions_delta,0) execs,
(elapsed_time_delta/decode(nvl(executions_delta,0),0,1,executions_delta))/1000000 avg_etime,
(buffer_gets_delta/decode(nvl(buffer_gets_delta,0),0,1,executions_delta)) avg_lio
from DBA_HIST_SQLSTAT S, DBA_HIST_SNAPSHOT SS
where sql_id in ('2w8sgb99bfy33')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
-- order by 3,2,1
order by begin_interval_time desc
/


   SNAP_ID       NODE BEGIN_INTERVAL_TIME            SQL_ID               PLAN_HASH_VALUE      EXECS  AVG_ETIME    AVG_LIO
---------- ---------- ------------------------------ -------------------- --------------- ---------- ---------- ----------
     27147          1 22-NOV-17 02.00.23.243 PM      8drpg36hd9jy3             1183286463          1 120.676823     801169
     27144          1 22-NOV-17 11.00.33.416 AM      8drpg36hd9jy3             1183286463          1 689.428523    1288418
     27143          1 22-NOV-17 10.00.16.173 AM      8drpg36hd9jy3             1183286463          1 342.579322      68606

 

Tuesday, 15 August 2017

Archive log generation on an hourly basis:


Archive log generation on an hourly basis ,I got this from
http://www.dbapundits.com/blog/query-script/hourly-daily-archive-generation/




set pages 1000
select trunc(COMPLETION_TIME,'HH') Hour,thread# , round(sum(BLOCKS*BLOCK_SIZE)/1048576) MB,count(*) Archives from v$archived_log
group by trunc(COMPLETION_TIME,'HH'),thread#  order by 1 ;



======================================================================
Gather schema stats in background:I got this from my colleague(Terry) 

----------------------------------------------------------------------
nohup ./gather_stats_RWSH023P.ksh >output.log &

[orcldb@dcplnx22502624 sql]$ cat gather_stats_RWSH023P.ksh
sqlplus / as sysdba << eof
set time on timing on echo on;
spool gather_stats_RWSH023P.log
exec dbms_stats.gather_schema_stats(ownname => 'JOBPRO',cascade => TRUE);
spool off
exit
eof

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


To Monitor stats job (%percentage)

select OPNAME from v$session_longops;

 
COLUMN sid FORMAT A10
COLUMN serial# FORMAT 9999999
COLUMN machine FORMAT A30
COLUMN progress_pct FORMAT 99999999.00
COLUMN elapsed FORMAT A10
COLUMN remaining FORMAT A10

SELECT s.sid,
       s.serial#,
       s.machine,
       ROUND(sl.elapsed_seconds/60) || ':' || MOD(sl.elapsed_seconds,60) elapsed,
       ROUND(sl.time_remaining/60) || ':' || MOD(sl.time_remaining,60) remaining,
       ROUND(sl.sofar/sl.totalwork*100, 2) progress_pct
FROM   v$session s,
       v$session_longops sl
WHERE  s.sid     = sl.sid
AND    s.serial# = sl.serial#
and opname ='Gather Schema Statistics'; 



=============================================================================

Monday, 14 August 2017

REFRESH through RMAN Backup

Primary Database Information
*************************************

  Host   : CYBERJAYA (10.122.164.82)
  DB_NAME  : XESH999R
  Disk_Group : /u01/nfs/oradata, /u01/zfs

Standby Database Information
*************************************

  Host   :   PUTRAJAYA (10.122.164.39)
  DB_NAME  : XESH999R
  DB_UNIQUE_NAME: XESH999R_sty
  Disk_Group : /u01/nfs2/oradata, /u01/zfs


========================================================
Basic pre-check before deploying Active Standby database server.
------------------------------------------------------------------------------------------------

(i) Primary Server, Instance, Databases details.
 ********************************************************


  NAME      INSTANCE_NAME    HOST_NAME            DATABASE_ROLE        LOG_MODE        LOGINS     OPEN_MODE       STARTUP_TIME         VERSION
  --------- ---------------- -------------------- -------------------- --------------- ---------- --------------- -------------------- -----------
  XESH999R      XESH999R             CYBERJAYA.localdomain   PRIMARY              ARCHIVELOG      ALLOWED    READ WRITE      2017:08:14  11.2.0.4.0



 (ii) Archive log Should be enable & Flash Recovery Area Details.
 ******************************************************************************
 
  SQL> archive log list

  Database log mode              Archive Mode
  Automatic archival             Enabled
  Archive destination            USE_DB_RECOVERY_FILE_DEST
  Oldest online log sequence     18
  Next log sequence to archive   20
  Current log sequence           20

  SQL> show parameter recovery

  NAME                                 TYPE        VALUE
  ------------------------------------ ----------- ------------------------------
  db_recovery_file_dest                string      /u01/zfs/XESH999R
  db_recovery_file_dest_size           big integer 4182M
  recovery_parallelism                 integer     0


 (iii) File-System Details of PUTRAJAYA Side.
 ************************************************

  [oracle@CYBERJAYA ~]$ df -h /u01/nfs2/oradata
  Filesystem            Size  Used Avail Use% Mounted on
  /dev/mapper/vg_PUTRAJAYA-lv_root
       86G   22G   60G  27% /
   
  [oracle@CYBERJAYA ~]$ df -h /u01/zfs
  Filesystem            Size  Used Avail Use% Mounted on
  /dev/mapper/vg_PUTRAJAYA-lv_root
       86G   22G   60G  27% /

 
 

Step: 1
==============================================
Set environments and start rman for XESH999R ( @Primary Site).
--------------------------------------------------------------------------------
 
[oracle@m4xa103 ~]$ . oraenv
ORACLE_SID = [oracle] ? XESH999R
The Oracle base has been set to /u01/app/oracle
[oracle@m4xa103 ~]$

[oracle@m4xa103 ~]$ rman PUTRAJAYA /

Recovery Manager: Release 11.2.0.4.0 - production on Sat Aug 5 13:03:57 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to PUTRAJAYA database: XESH999R (DBID=3010714195)

Step: 2
============================================================================
Now check old backup details, but we need new backup with autocontrolfile ( @Primary Site).
------------------------------------------------------------------------------------------------------------------------------------


RMAN> list backup;
using PUTRAJAYA database control file instead of recovery catalog
specification does not match any backup in the repository

Step: 3
===========================================================================
Check controlfile autobakcup details, It's should ON, if Not then enable it. ( @Primary Site).
----------------------------------------------------------------------------------------------------------------------------------


RMAN> show all;

using PUTRAJAYA database control file instead of recovery catalog
RMAN configuration parameters for database with db_unique_name XESH999R are:
CONFIGURE RETENTION POLICY TO REDUNDANCY 1; # default
CONFIGURE BACKUP OPTIMIZATION OFF; # default
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP OFF; # default
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK PARALLELISM 1 BACKUP TYPE TO BACKUPSET; # default
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE; # default
CONFIGURE SNAPSHOT CONTROLFILE NAME TO '/u01/app/oracle/product/11.2.0/db_1/dbs/snapcf_XESH999R.f'; # default

RMAN> CONFIGURE CONTROLFILE AUTOBACKUP ON;

new RMAN configuration parameters:
CONFIGURE CONTROLFILE AUTOBACKUP ON;
new RMAN configuration parameters are successfully stored


Step: 4
===============================================
Take full databae backup with archivelog ( @Primary Site).
----------------------------------------------------------------------------------


RMAN> backup database plus archivelog;

Starting backup at 14-AUG-17
current log archived
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=44 device type=DISK
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=4 RECID=1 STAMP=950913403
input archived log thread=1 sequence=5 RECID=2 STAMP=950913414
input archived log thread=1 sequence=6 RECID=3 STAMP=950915922
input archived log thread=1 sequence=7 RECID=4 STAMP=950915925
input archived log thread=1 sequence=8 RECID=5 STAMP=950918291
input archived log thread=1 sequence=9 RECID=6 STAMP=950918867
input archived log thread=1 sequence=10 RECID=7 STAMP=950918898
input archived log thread=1 sequence=11 RECID=8 STAMP=950919116
input archived log thread=1 sequence=12 RECID=9 STAMP=950919118
input archived log thread=1 sequence=13 RECID=10 STAMP=950919119
input archived log thread=1 sequence=14 RECID=16 STAMP=950919173
input archived log thread=1 sequence=15 RECID=18 STAMP=950919788
input archived log thread=1 sequence=16 RECID=20 STAMP=950919790
input archived log thread=1 sequence=17 RECID=22 STAMP=950919791
input archived log thread=1 sequence=18 RECID=24 STAMP=950919793
input archived log thread=1 sequence=19 RECID=26 STAMP=950919794
input archived log thread=1 sequence=20 RECID=28 STAMP=950919795
input archived log thread=1 sequence=21 RECID=30 STAMP=950919796
input archived log thread=1 sequence=22 RECID=32 STAMP=950919796
input archived log thread=1 sequence=23 RECID=34 STAMP=950919796
input archived log thread=1 sequence=24 RECID=36 STAMP=950919797
input archived log thread=1 sequence=25 RECID=38 STAMP=950919797
input archived log thread=1 sequence=26 RECID=41 STAMP=950973779
input archived log thread=1 sequence=27 RECID=42 STAMP=951224601
input archived log thread=1 sequence=28 RECID=43 STAMP=951224614
input archived log thread=1 sequence=29 RECID=44 STAMP=951224875
channel ORA_DISK_1: starting piece 1 at 14-AUG-17
channel ORA_DISK_1: finished piece 1 at 14-AUG-17
piece handle=/u01/zfs/XESH999R/XESH999R/backupset/2017_08_14/o1_mf_annnn_TAG20170814T130755_drbxgnbo_.bkp tag=TAG20170814T130755 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07
Finished backup at 14-AUG-17

Starting backup at 14-AUG-17
using channel ORA_DISK_1
channel ORA_DISK_1: starting full datafile backup set
channel ORA_DISK_1: specifying datafile(s) in backup set
input datafile file number=00001 name=/u01/nfs/oradata/XESH999R/XESH999R/system01.dbf
input datafile file number=00002 name=/u01/nfs/oradata/XESH999R/XESH999R/sysaux01.dbf
input datafile file number=00014 name=/u01/nfs/oradata/XESH999R/XESH999R/example01.dbf
input datafile file number=00003 name=/u01/nfs/oradata/XESH999R/XESH999R/undotbs01.dbf
input datafile file number=00004 name=/u01/nfs/oradata/XESH999R/XESH999R/users01.dbf
channel ORA_DISK_1: starting piece 1 at 14-AUG-17
channel ORA_DISK_1: finished piece 1 at 14-AUG-17
piece handle=/u01/zfs/XESH999R/XESH999R/backupset/2017_08_14/o1_mf_nnndf_TAG20170814T130803_drbxgwpf_.bkp tag=TAG20170814T130803 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:01:25
Finished backup at 14-AUG-17

Starting backup at 14-AUG-17
current log archived
using channel ORA_DISK_1
channel ORA_DISK_1: starting archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=30 RECID=45 STAMP=951224968
channel ORA_DISK_1: starting piece 1 at 14-AUG-17
channel ORA_DISK_1: finished piece 1 at 14-AUG-17
piece handle=/u01/zfs/XESH999R/XESH999R/backupset/2017_08_14/o1_mf_annnn_TAG20170814T130928_drbxkk2x_.bkp tag=TAG20170814T130928 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:01
Finished backup at 14-AUG-17

Starting Control File and SPFILE Autobackup at 14-AUG-17
piece handle=/u01/zfs/XESH999R/XESH999R/autobackup/2017_08_14/o1_mf_s_951224970_drbxklvm_.bkp comment=NONE
Finished Control File and SPFILE Autobackup at 14-AUG-17

RMAN> exit


Step: 5
===========================================
Creare some directiories on PUTRAJAYA side ( @PUTRAJAYA Site).
----------------------------------------------------------------------------

[oracle@PUTRAJAYA ~]$ mkdir -p  /u01/zfs/XESH999R

[oracle@PUTRAJAYA ~]$ mkdir -p /u01/nfs2/oradata/XESH999R

[oracle@PUTRAJAYA ~]$ mkdir -p /u01/app/oracle/admin/XESH999R/adump

Step: 6
===================================================================
Tranfer backupset,autobackup & password file to PUTRAJAYA location  ( @Primary Site).
-------------------------------------------------------------------------------------------------------------------


[oracle@m4xa103 XESH999R]$ pwd
/u01/zfs/XESH999R/XESH999R
[oracle@m4xa103 XESH999R]$ ls -lrth
total 16K
drwxr-x--- 2 oracle oinstall 4.0K Aug  2 16:30 flashback
drwxr-x--- 5 oracle oinstall 4.0K Aug  5 13:03 archivelog
drwxr-x--- 3 oracle oinstall 4.0K Aug  5 13:07 backupset
drwxr-x--- 3 oracle oinstall 4.0K Aug  5 13:09 autobackup

[oracle@m4xa103 XESH999R]$ scp -r backupset autobackup oracle@192.168.56.102:/u01/zfs/XESH999R/XESH999R
oracle@192.168.56.102's password:

o1_mf_annnn_TAG20170814T130755_drbxgnbo_.bkp                   100%   67MB  67.3MB/s   00:01
o1_mf_annnn_TAG20170814T130928_drbxkk2x_.bkp                   100%   57KB  56.5KB/s   00:00
o1_mf_nnndf_TAG20170814T130803_drbxgwpf_.bkp                   100% 1085MB  60.3MB/s   00:18
o1_mf_s_951224970_drbxklvm_.bkp                                100% 9888KB   9.7MB/s   00:00

[oracle@m4xa103 XESH999R]$ cd /u01/app/oracle/XESH999Ruct/11.2.0/db_1/dbs/
[oracle@m4xa103 dbs]$ ls -lrth

total 19M
-rw-r--r--. 1 oracle oinstall 2.8K May 15  2009 init.ora
-rw-r-----. 1 oracle oinstall   24 Jul 26 12:46 lkXESH999R
-rw-r-----. 1 oracle oinstall 2.5K Jul 26 14:22 orapwXESH999R
-rw-rw----. 1 oracle oinstall 1.6K Aug  1 00:35 hc_XESH999R.dat
-rw-r-----. 1 oracle oinstall 3.5K Aug  1 00:36 spfileXESH999R.ora
-rw-r-----. 1 oracle oinstall 9.4M Aug  1 00:54 snapcf_XESH999R.f
-rw-r-----  1 oracle oinstall   24 Aug  1 21:15 lkXESH999R
-rw-r-----  1 oracle oinstall 1.5K Aug  1 21:17 orapwXESH999R
-rw-rw----  1 oracle oinstall 1.6K Aug  5 13:03 hc_XESH999R.dat
-rw-r-----  1 oracle oinstall 3.5K Aug  5 13:04 spfileXESH999R.ora
-rw-r-----  1 oracle oinstall 9.6M Aug  5 13:09 snapcf_XESH999R.f

[oracle@m4xa103 dbs]$ scp orapwXESH999R oracle@192.168.56.102:/u01/app/oracle/XESH999Ruct/11.2.0/db_1/dbs/
oracle@192.168.56.102's password:

orapwXESH999R                                                    100% 1536     1.5KB/s   00:00


Step: 7
=============================================================================
On PUTRAJAYA server start database via rman and restore spfile from autobackup  ( @PUTRAJAYA Site).
-------------------------------------------------------------------------------------------------------------------------------------

[oracle@PUTRAJAYA ~]$ vi /etc/oratab

[oracle@PUTRAJAYA ~]$ . oraenv
ORACLE_SID = [oracle] ? XESH999R
The Oracle base has been set to /u01/app/oracle

[oracle@PUTRAJAYA dbs]$ rman PUTRAJAYA /

Recovery Manager: Release 11.2.0.4.0 - XESH999Ruction on Sat Aug 5 13:18:43 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to PUTRAJAYA database (not started)

RMAN> startup nomount force;

startup failed: ORA-01078: failure in processing system parameters
LRM-00109: could not open parameter file '/u01/app/oracle/XESH999Ruct/11.2.0/db_1/dbs/initXESH999R.ora'

starting Oracle instance without parameter file for retrieval of spfile
Oracle instance started

Total System Global Area    1068937216 bytes

Fixed Size                     2260088 bytes
Variable Size                281019272 bytes
Database Buffers             780141444 bytes
Redo Buffers                   5517312 bytes

RMAN> restore spfile from '/u01/zfs/XESH999R/XESH999R/autobackup/2017_08_14/o1_mf_s_951224970_drbxklvm_.bkp';

Starting restore at 14-AUG-17
using channel ORA_DISK_1

channel ORA_DISK_1: restoring spfile from AUTOBACKUP /u01/zfs/XESH999R/XESH999R/autobackup/2017_08_14/o1_mf_s_951224970_drbxklvm_.bkp
channel ORA_DISK_1: SPFILE restore from AUTOBACKUP complete
Finished restore at 14-AUG-17

RMAN> exit


Step: 8
==========================================================================================
Come to PUTRAJAYA SQL*Plus promte and create pfile from spfile and do some modification acording to PUTRAJAYA server.  ( @PUTRAJAYA Site).
--------------------------------------------------------------------------------------------------------------------------------------------------------------


[oracle@PUTRAJAYA ~]$ . oraenv
ORACLE_SID = [oracle] ? XESH999R
The Oracle base has been set to /u01/app/oracle

[oracle@PUTRAJAYA ~]$ cd $ORACLE_HOME/dbs
[oracle@PUTRAJAYA dbs]$ ls -lrth

-rw-r-----  1 oracle oinstall 3.5K Aug  5 13:28 spfileXESH999R.ora
[oracle@PUTRAJAYA dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 XESH999Ruction on Sat Aug 5 13:30:41 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit XESH999Ruction
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> create pfile from spfile;

File created.

SQL> exit


[oracle@PUTRAJAYA dbs]$ ls -lrth

-rw-r-----  1 oracle oinstall 3.5K Aug  5 13:28 spfileXESH999R.ora
-rw-r--r--  1 oracle oinstall 1.4K Aug  5 13:30 initXESH999R.ora

[oracle@PUTRAJAYA dbs]$ vi initXESH999R.ora

*.audit_file_dest='/u01/app/oracle/admin/XESH999R/adump'
*.control_files='/u01/nfs2/oradata/XESH999R/XESH999R/control01.ctl','/u01/nfs2/oradata/XESH999R/XESH999R/control02.ctl'
*.db_recovery_file_dest='/u01/zfs/XESH999R'

:wq


Step: 9
======================================================================================
Remove spfile from dbs location, down previous stared instnace  & start it again via pfile  ( @PUTRAJAYA Site).
------------------------------------------------------------------------------------------------------------------------------------------------------

[oracle@PUTRAJAYA dbs]$ rm -rf spfileXESH999R.ora

[oracle@PUTRAJAYA dbs]$ ps -ef | grep pmon | grep -v grep
oracle    2437     1  0 13:19 ?        00:00:00 ora_pmon_XESH999R
[oracle@PUTRAJAYA dbs]$
[oracle@PUTRAJAYA dbs]$ !s
sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 XESH999Ruction on Sat Aug 5 13:37:09 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit XESH999Ruction
With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> shut immediate
ORA-01507: database not mounted


[oracle@PUTRAJAYA dbs]$ sqlplus / as sysdba

SQL*Plus: Release 11.2.0.4.0 XESH999Ruction on Sat Aug 5 13:38:03 2017

Copyright (c) 1982, 2013, Oracle.  All rights reserved.

Connected to an idle instance.

SQL> startup nomount
ORACLE instance started.

Total System Global Area 1653518336 bytes
Fixed Size                  2253784 bytes
Variable Size            1006636072 bytes
Database Buffers          637534208 bytes
Redo Buffers                7094272 bytes
SQL> create spfile from pfile;

File created.

SQL> exit


Step: 10
=====================================================================================
Now start rman and restore controlfile from autobackup & put database in mount stage  ( @PUTRAJAYA Site).

[oracle@PUTRAJAYA dbs]$ rman PUTRAJAYA /

Recovery Manager: Release 11.2.0.4.0 - Production on Sat Aug 5 13:38:27 2017

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

connected to PUTRAJAYA database: XESH999R (not mounted)

RMAN> restore controlfile from '/u01/zfs/XESH999R/XESH999R/autobackup/2017_08_14/o1_mf_s_951224970_drbxklvm_.bkp';

Starting restore at 14-AUG-17
using PUTRAJAYA database control file instead of recovery catalog
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK

channel ORA_DISK_1: restoring control file
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
output file name=/u01/nfs2/oradata/XESH999R/XESH999R/control01.ctl
output file name=/u01/nfs2/oradata/XESH999R/XESH999R/control02.ctl
Finished restore at 14-AUG-17

RMAN> alter database mount;

database mounted
released channel: ORA_DISK_1

Step: 11
========================================================
Now tell your newly stared db to your backup location  ( @PUTRAJAYA Site).
-------------------------------------------------------------------------------------------------

RMAN> catalog start with '/u01/zfs/XESH999R/XESH999R/';

Starting implicit crosscheck backup at 14-AUG-17
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=20 device type=DISK
Crosschecked 3 objects
Finished implicit crosscheck backup at 14-AUG-17

Starting implicit crosscheck copy at 14-AUG-17
using channel ORA_DISK_1
Crosschecked 2 objects
Finished implicit crosscheck copy at 14-AUG-17

searching for all files in the recovery area
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/zfs/XESH999R/XESH999R/autobackup/2017_08_14/o1_mf_s_951224970_drbxklvm_.bkp

searching for all files that match the pattern /u01/zfs/XESH999R/XESH999R/

List of Files Unknown to the Database
=====================================
File Name: /u01/zfs/XESH999R/XESH999R/autobackup/2017_08_14/o1_mf_s_951224970_drbxklvm_.bkp

Do you really want to catalog the above files (enter YES or NO)? yes
cataloging files...
cataloging done

List of Cataloged Files
=======================
File Name: /u01/zfs/XESH999R/XESH999R/autobackup/2017_08_14/o1_mf_s_951224970_drbxklvm_.bkp


Step: 12
=============================================
Finally restore & recovery your database  ( @PUTRAJAYA Site).
-------------------------------------------------------------------------------

RMAN> run {
2> SET NEWNAME FOR DATAFILE 1 TO "/u01/nfs2/oradata/XESH999R/XESH999R/system01.dbf";                                                               
3> SET NEWNAME FOR DATAFILE 2 TO "/u01/nfs2/oradata/XESH999R/XESH999R/sysaux01.dbf";                                                               
4> SET NEWNAME FOR DATAFILE 3 TO "/u01/nfs2/oradata/XESH999R/XESH999R/undotbs01.dbf";                                                              
5> SET NEWNAME FOR DATAFILE 4 TO "/u01/nfs2/oradata/XESH999R/XESH999R/users01.dbf";                                                                
6> SET NEWNAME FOR DATAFILE 5 TO "/u01/nfs2/oradata/XESH999R/XESH999R/example01.dbf";
7> restore database;
8> switch datafile all;
9> recover database;
10> }

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

executing command: SET NEWNAME

Starting restore at 14-AUG-17
using channel ORA_DISK_1

channel ORA_DISK_1: starting datafile backup set restore
channel ORA_DISK_1: specifying datafile(s) to restore from backup set
channel ORA_DISK_1: restoring datafile 00001 to /u01/nfs2/oradata/XESH999R/XESH999R/system01.dbf
channel ORA_DISK_1: restoring datafile 00002 to /u01/nfs2/oradata/XESH999R/XESH999R/sysaux01.dbf
channel ORA_DISK_1: restoring datafile 00003 to /u01/nfs2/oradata/XESH999R/XESH999R/undotbs01.dbf
channel ORA_DISK_1: restoring datafile 00004 to /u01/nfs2/oradata/XESH999R/XESH999R/users01.dbf
channel ORA_DISK_1: restoring datafile 00014 to /u01/nfs2/oradata/XESH999R/XESH999R/example01.dbf
channel ORA_DISK_1: reading from backup piece /u01/zfs/XESH999R/XESH999R/backupset/2017_08_14/o1_mf_nnndf_TAG20170814T130803_drbxgwpf_.bkp
channel ORA_DISK_1: piece handle=/u01/zfs/XESH999R/XESH999R/backupset/2017_08_14/o1_mf_nnndf_TAG20170814T130803_drbxgwpf_.bkp tag=TAG20170814T130803
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:35
Finished restore at 14-AUG-17

datafile 1 switched to datafile copy
input datafile copy RECID=9 STAMP=951227735 file name=/u01/nfs2/oradata/XESH999R/XESH999R/system01.dbf
datafile 2 switched to datafile copy
input datafile copy RECID=10 STAMP=951227735 file name=/u01/nfs2/oradata/XESH999R/XESH999R/sysaux01.dbf
datafile 3 switched to datafile copy
input datafile copy RECID=11 STAMP=951227735 file name=/u01/nfs2/oradata/XESH999R/XESH999R/undotbs01.dbf
datafile 4 switched to datafile copy
input datafile copy RECID=12 STAMP=951227735 file name=/u01/nfs2/oradata/XESH999R/XESH999R/users01.dbf
datafile 5 switched to datafile copy
input datafile copy RECID=13 STAMP=951227735 file name=/u01/nfs2/oradata/XESH999R/XESH999R/example01.dbf

Starting recover at 14-AUG-17
using channel ORA_DISK_1

starting media recovery

channel ORA_DISK_1: starting archived log restore to default destination
channel ORA_DISK_1: restoring archived log
archived log thread=1 sequence=30
channel ORA_DISK_1: reading from backup piece /u01/zfs/XESH999R/XESH999R/backupset/2017_08_14/o1_mf_annnn_TAG20170814T130928_drbxkk2x_.bkp
channel ORA_DISK_1: piece handle=/u01/zfs/XESH999R/XESH999R/backupset/2017_08_14/o1_mf_annnn_TAG20170814T130928_drbxkk2x_.bkp tag=TAG20170814T130928
channel ORA_DISK_1: restored backup piece 1
channel ORA_DISK_1: restore complete, elapsed time: 00:00:01
archived log file name=/u01/zfs/XESH999R/XESH999R/archivelog/2017_08_14/o1_mf_1_30_drc080cv_.arc thread=1 sequence=30
channel default: deleting archived log(s)
archived log file name=/u01/zfs/XESH999R/XESH999R/archivelog/2017_08_14/o1_mf_1_30_drc080cv_.arc RECID=46 STAMP=951227736
unable to find archived log
archived log thread=1 sequence=31
RMAN-01471: ===========================================================
RMAN-01469: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-01471: ===========================================================
RMAN-03002: failure of recover command at 08/14/2017 13:55:37
RMAN-06144: media recovery requesting unknown archived log for thread 1 with sequence 31 and starting SCN of 1044850

RMAN> exit

******************************************
Note: the error can be ignored as RMAN has applied all the available archive log files and is now trying to apply a non-existent archive log file.the error can be ignored as RMAN has applied all the available archive log files and is now trying to apply a non-existent archive log file.
**********************************************



Step: 13
===================================================================================
Now try to start your database with resetlogs and will get below error, then rename your redologs. ( @PUTRAJAYA Site).
------------------------------------------------------------------------------------------------------------------------------------------------

SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-00349: failure obtaining block size for '/u01/nfs/oradata/XESH999R/XESH999R/redo01.log'
ORA-27041: unable to open file
Linux-x86_64 Error: 2: No such file or directory
Additional information: 9


SQL> alter database rename file  '/u01/nfs/oradata/XESH999R/XESH999R/redo01.log' TO '/u01/nfs2/oradata/XESH999R/XESH999R/redo01.log';

Database altered.

SQL> alter database rename file  '/u01/nfs/oradata/XESH999R/XESH999R/redo02.log' TO '/u01/nfs2/oradata/XESH999R/XESH999R/redo02.log';

Database altered.

SQL> alter database rename file  '/u01/nfs/oradata/XESH999R/XESH999R/redo03.log' TO '/u01/nfs2/oradata/XESH999R/XESH999R/redo03.log';

Database altered.


Step: 14
========================================================================================
Again try to start your database with resetlogs and will get below error, Clear your one unclear redologfile. ( @PUTRAJAYA Site).
--------------------------------------------------------------------------------------------------------------------------------------------------------

SQL> ALTER DATABASE OPEN RESETLOGS;
ALTER DATABASE OPEN RESETLOGS
*
ERROR at line 1:
ORA-00392: log 1 of thread 1 is being cleared, operation not allowed
ORA-00312: online log 1 thread 1: '/u01/nfs2/oradata/XESH999R/XESH999R/redo01.log'



SQL> select GROUP#,THREAD#,BYTES,BLOCKSIZE,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE#,NEXT_TIME from v$log;

    GROUP#    THREAD#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1   52428800        512          1 NO  CLEARING_CURRENT       1044850 14-AUG-17   2.8147E+14
         3          1   52428800        512          1 YES CLEARING               1044763 14-AUG-17      1044850 14-AUG-17
         2          1   52428800        512          1 YES CLEARING               1041102 14-AUG-17      1044763 14-AUG-17

 
 
SQL>  alter database clear logfile '/u01/nfs2/oradata/XESH999R/XESH999R/redo01.log';

Database altered.

Step: 14
===========================================================================
Again try to start your database with resetlogs and you are done here..!!. ( @PUTRAJAYA Site).
---------------------------------------------------------------------------------------------------------------------------------


SQL> select GROUP#,THREAD#,BYTES,BLOCKSIZE,MEMBERS,ARCHIVED,STATUS,FIRST_CHANGE#,FIRST_TIME,NEXT_CHANGE#,NEXT_TIME from v$log;

    GROUP#    THREAD#      BYTES  BLOCKSIZE    MEMBERS ARC STATUS           FIRST_CHANGE# FIRST_TIM NEXT_CHANGE# NEXT_TIME
---------- ---------- ---------- ---------- ---------- --- ---------------- ------------- --------- ------------ ---------
         1          1   52428800        512          1 NO  CURRENT                1044850 14-AUG-17   2.8147E+14
         3          1   52428800        512          1 YES CLEARING               1044763 14-AUG-17      1044850 14-AUG-17
         2          1   52428800        512          1 YES CLEARING               1041102 14-AUG-17      1044763 14-AUG-17

SQL> ALTER DATABASE OPEN RESETLOGS;

Database altered.

SQL> exit

Wednesday, 2 August 2017

RMAN-08137 Error on Primary Database

I am going to explain how to bypass RMAN-08137 error when we use dataguard. When we try to delete archive log files in primary database,

See the  following error.
##############################################################################

RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
RMAN-08137: WARNING: archived log not deleted, needed for standby or upstream capture process
backup_arch  02.08.2017 00:51:54 ERROR   > ------------------- some errors found, rman backup completed wirh ERRORS [rc=35].
backup_arch  02.08.2017 00:51:56 ERROR   > script ora_backup_arch terminated with ERRORS [rc=1]
backup_arch  02.08.2017 00:51:56 ERROR   > Archivelog backup terminated with ERRORS, email with logfile send to fmb-ts-XX-db-ops-exadata@XXXX.com
 #############################################################################

There is a hidden parameter to bypass this. This parameter has been introduced with 11.2.0.x version.Before this version  setting log_archive_dest_n parameter as null  is enough to define standby database is completely unreachable and you can delete archivelog.But after this version there is a hidden parameter  to do this operation.First of all we have to disable log_archive_dest_state_n parameter



SQL> show parameter log_archive_dest_state


NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
log_archive_dest_state_2             string      DEFER

 => After you set this parameter as ‘DEFER’ we must set hidden parameter like following.Normally this parameter is TRUE.


=================================================================== 
 Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,
Data Mining and Real Application Testing options

SQL> select x.ksppinm name,y.ksppstvl value from sys.x$ksppi x,sys.x$ksppcv y where x.inst_id=userenv('Instance') and y.inst_id=userenv('Instance')
and x.indx=y.indx and x.ksppinm='_deferred_log_dest_is_valid';

NAME                           VALUE
------------------------------ ------------------------------
_deferred_log_dest_is_valid    TRUE

 => We can set this parameter like following.

 SQL> alter system set "_deferred_log_dest_is_valid" = FALSE scope=both;

 SQL> select x.ksppinm name,y.ksppstvl value from sys.x$ksppi x,sys.x$ksppcv y where x.inst_id=userenv('Instance') and y.inst_id=userenv('Instance')
and x.indx=y.indx and x.ksppinm='_deferred_log_dest_is_valid';


NAME                           VALUE
------------------------------ ------------------------------
_deferred_log_dest_is_valid    FALSE

 After this change my archive deletion job is working fine.

Thursday, 20 July 2017

DG Switchover:(Switchover from _G1 (GREAT_SER) Primary Database to _C1(COOL_SER)
====================================================================
Through DGMGRL:

The SWITCHOVER is technically a database role transition, where replication is reversed and primary/standby databases are changing their
current database roles:

=> Initial Primary Database: <Database_Name>_G1 will become the current Standby database after Switchover
=> Initial Physical Standby: <Database_Name>_C1 will become the current Primary database after Switchover

Primary:
dgmgrl> show configuration;
dgmgrl> show database ‘<INSTANCE_NAME>_G1';
dgmgrl> show database ‘<INSTANCE_NAME>_C1';

 Connect on standby site (C1) with DG-Broker to the database:


Standby(it will become new primary after switchover)

server>. goto $ DBNAME
server> dgmgrl
dgmgrl> connect sys/<password>@<INSTANCE_NAME>_C1

dgmgrl> show configuration;
--- No errors are reported, then


=> Pre-Check for any replication delay and DG Broker errors.

Resolve any delay and error(s) before:

dgmgrl> switchover to ‘<INSTANCE_NAME>_C1';

=> Make <INSTANCE_NAME>_C1 the primary database. Invoke the actual switchover.
Connect on standby site (C1) with DG-Broker to the database:


server>. goto $ DBNAME
server> dgmgrl
dgmgrl> connect sys/<password>@<INSTANCE_NAME>_C1

After first swithover you have to add tempfile with reuse command on new primary(old standby).

=====================================================================
alter tablespace TEMP add tempfile '/oracle/XXXX/oratemp01/o_XXX_TEMP_1.dbf' size 1g reuse;
=====================================================================

Post Check if Standby database is only mounted:

You must have a license to use Active Data Guard, where the standby database is opened

dgmgrl> show configuration;
dgmgrl> show database ‘<INSTANCE_NAME>_G1';
dgmgrl> show database ‘<INSTANCE_NAME>_C1';

server> sqlplus /nolog
sqlplus> connect sys/<password>@<INSTANCE_NAME>_G1
sqlplus>

col line format a200
select to_char(sysdate,'DD.MM.YYYY-HH24:MI:SS')||': ' ||i.host_name||', '
||nvl(d.DB_UNIQUE_NAME,d.NAME)||'('||d.NAME||','||i.INSTANCE_NAME||'), ' ||translate(d.DATABASE_ROLE,' ','_')||',
'||translate(d.OPEN_MODE,' ','_')||', ' ||i.VERSION||', startuptime='||to_char(i.STARTUP_TIME,'DD.MM.YYYY-HH24:MI:SS') as line from
V$DATABASE d, V$INSTANCE i ;
If Standby database is opened and you do not have a license for Active Data Guard, restart and mount only the database.


Thursday, 29 June 2017

Standby SYNC check

Check redo received and applied on standby:
STANDBY SIDE:

SELECT ARCH.THREAD# "Thread", ARCH.SEQUENCE# "Last Sequence Received",
APPL.SEQUENCE# "Last Sequence Applied", (ARCH.SEQUENCE# - APPL.SEQUENCE#) "Difference"
FROM
(SELECT THREAD# ,SEQUENCE# FROM V$ARCHIVED_LOG WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$ARCHIVED_LOG GROUP BY THREAD#)) ARCH,
(SELECT THREAD# ,SEQUENCE# FROM V$LOG_HISTORY WHERE (THREAD#,FIRST_TIME ) IN
(SELECT THREAD#,MAX(FIRST_TIME) FROM V$LOG_HISTORY GROUP BY THREAD#)) APPL
WHERE
ARCH.THREAD# = APPL.THREAD#
ORDER BY 1;

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                  56063                 54996       1067
         2                     85                    85          0


Primary:

select ads.dest_id,max(sequence#) "Current Sequence",
 max(log_sequence) "Last Archived"
   from v$archived_log al, v$archive_dest ad, v$archive_dest_status ads
   where ad.dest_id=al.dest_id
   and al.dest_id=ads.dest_id
   and al.resetlogs_change#=(select max(resetlogs_change#) from v$archived_log )
 group by ads.dest_id;


   DEST_ID Current Sequence Last Archived
---------- ---------------- -------------
         1              678           678
         2              678           679

Check for GAP on Standby:
Primary:
select max(sequence#) from v$log_history;

Standby :
select max(sequence#) from v$log_history; 


How to find the GAP on Standby with primay:


SELECT THREAD#, LOW_SEQUENCE#, HIGH_SEQUENCE# FROM V$ARCHIVE_GAP;

 Both primary and standby:

SELECT SEQUENCE#,APPLIED FROM V$ARCHIVED_LOG
ORDER BY SEQUENCE#; 

select sequence#, first_time, next_time, applied from v$archived_log;

========================================================================
If you start a foreground session, control is not returned to the command prompt until recovery is canceled by another session.
To start Redo Apply in the background, include the DISCONNECT keyword on the SQL statement. For example:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE DISCONNECT FROM SESSION;

or

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING current ARCHIVED LOGFILE DISCONNECT;
     ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT;



This statement starts a detached server process and immediately returns control to the user. While the managed recovery process is performing recovery in the background, the foreground process that issued the RECOVER statement can continue performing other tasks. This command does not disconnect the current SQL session.
=======================================================================
To stop Redo Apply, issue the following SQL statement:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;


--Monitoring Redo Apply on Physical Standby Databases---

SELECT PROTECTION_MODE, PROTECTION_LEVEL,DATABASE_ROLE ROLE, SWITCHOVER_STATUS FROM V$DATABASE;

SELECT PROCESS, STATUS, THREAD#, SEQUENCE#,BLOCK#, BLOCKS FROM V$MANAGED_STANDBY;

select sequence#, applied from v$archived_log order by sequence# ;

select THREAD#,SEQUENCE#,STANDBY_DEST,ARCHIVED,APPLIED from v$archived_log;
select RECOVERY_MODE from v$archive_dest_status;

select ERROR,STATUS from v$archive_dest_status;

SELECT MESSAGE FROM V$DATAGUARD_STATUS;

SELECT FS_FAILOVER_STATUS "FSFO STATUS",FS_FAILOVER_CURRENT_TARGET TARGET,FS_FAILOVER_THRESHOLD THRESHOLD,FS_FAILOVER_OBSERVER_PRESENT "OBSERVER PRESENT" FROM V$DATABASE;

SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#,NEXT_CHANGE# FROM V$ARCHIVED_LOG;

SELECT THREAD#, SEQUENCE#, FIRST_CHANGE#,NEXT_CHANGE# FROM V$LOG_HISTORY;

SELECT DEST_ID, APPLIED_SCN FROM V$ARCHIVE_DEST WHERE TARGET='STANDBY';
col DEST_NAME format a30;

SELECT DEST_NAME,STATUS,APPLIED_SEQ#,SYNCHRONIZATION_STATUS,GAP_STATUS from V$ARCHIVE_DEST_STATUS where DEST_NAME='STANDBY_ARCHIVE_DEST';

============================================
Restore Point:

col name format a30
col value format a30

SELECT name, value  FROM gv$parameter WHERE name LIKE '%flashback%';

SELECT flashback_on, log_mode FROM v$database;

-- normal restore point
CREATE RESTORE POINT BOPPUDI_1_BEFORE_DEMAGE;

col name format a15
col time format a32
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES';

NAME                                  SCN TIME                                DATABASE_INCARNATION# GUA STORAGE_SIZE
------------------------------ ---------- ----------------------------------- --------------------- --- ------------
BOPPUDI_1_BEFORE_DEMAGE                       1.4690E+13 23-JUN-18 05.28.57.000000000 AM                         8 YES            0
BOPPUDI_2_BEFORE_DEMAGE                       1.4690E+13 23-JUN-18 08.39.53.000000000 AM                         8 YES   4.2950E+10


HOW TO RECOVER FROM RESTORE POINT:
-----------------------------------------------------------
SELECT NAME, SCN, TIME, DATABASE_INCARNATION#,GUARANTEE_FLASHBACK_DATABASE, STORAGE_SIZE FROM V$RESTORE_POINT WHERE GUARANTEE_FLASHBACK_DATABASE='YES' ;

shutdown immediate;

startup mount;

flashback database to restore point before_damage;

alter database open resetlogs;


DROP RESTORE POINT:
-------------------------------------
DROP RESTORE POINT BOPPUDI_RESTORE;