Search This Blog

Monday, 16 July 2018

Standby backup issue due to mrp running other nodes:



edit database 'OESH7A7D' set state='APPLY-OFF';
edit database 'OESH7A7D' set state='APPLY-ON' with apply instance ='OESH7A7D1';
show database 'OESH7A7D';

select thread#,max(sequence#) from gv$log_history group by thread#;

Wednesday, 11 July 2018

To clear the log file from trace location:

Find and Delete:

older than 2 days:
---------------------

 find . -name "*trc" -mmin +2880 -exec rm {} \;
 find . -name "*trm" -mmin +2880 -exec rm {} \;

 older than one day:
 ---------------------
 find . -name "*trc" -mmin +440 -exec rm {} \;

Saturday, 23 June 2018

How to check all sessions information (active and inactive):

Below is the query to check connection with services:
======================================

col sid head "Sid" form 9999 trunc
  col SERVICE_NAME for a30;
 col serial# form 99999 trunc head "Ser#"
 col username form a8 trunc
 col osuser form a7 trunc
 col machine form a20 trunc head "Client|Machine"
 col program form a15 trunc head "Client|Program"
 col login form a11
 col "last call" form 9999999 trunc head "Last Call|In Secs"
 col status form a6 trunc
 select sid,serial#,substr(username,1,10) username,substr(osuser,1,10) osuser,
 substr(program||module,1,15) program,substr(machine,1,22) machine,
 SERVICE_NAME,
 to_char(logon_time,'ddMon hh24:mi') login,
 last_call_et "last call",status,sql_hash_value
 from v$session
 order by login
 /
 

Wednesday, 13 June 2018

Warning: ORA-16826: apply service state is inconsistent with the DelayMins property



        Need to check on DelayMins configuration on all standby’s

ð  DGMGRL> show database verbose 'TESH957P_DC2' 'delaymins';

        Check MRP process is running or not.

ð  SYS@TESH957P SQL>SELECT PROCESS, STATUS, THREAD#, SEQUENCE#,BLOCK#, BLOCKS FROM V$MANAGED_STANDBY where PROCESS like 'MRP%';

          Need to restart Intended State option:

ð  edit database "TESH957P_DC1" set state=apply-off;

ð  edit database "TESH957P_DC1" set state=apply-on;

         And need to cancel the MRP process while enabling i have used " CURRENT LOGFILE " option.

ð  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

ð  ALTER DATABASE RECOVER MANAGED STANDBY DATABASE USING CURRENT LOGFILE DISCONNECT FROM SESSION;

Thursday, 14 December 2017

SAR report


Use below command when ever need SAR report:

 sar -P ALL  ( CPU Usage of Individual CPU or Core (sar -P))

sar -W      (View page exchange status)

sar -B    (Paging Statistics)

sar -b     (I/O Transfer Rate)

sar -R     (These fields are displayed (pages are 4K or 8K in size)

sar -r     (RAM Load: Displaying the RAM Load)

sar -S    (Swap Space Used )

sar -H     (hugepages utilization)

sar -v    (status of inode, file and other kernel tables)

sar -q     (Report queue length and load averages)

sar -y                     (Report TTY devices activity.)

sar -d     (Individual Block Device I/O)

sar -n DEV  (Network Utilization Statistics)

sar -n EDEV      (statistics on failures (errors) from the network devices

sar -n NFS       ( statistics about NFS client activity are reported)

sar -n NFSD    (statistics about NFS server activity are reported)

sar -n  SOCK      (statistics on sockets in use are reported (IPv4).

COPY the sa fiel to any temporary location then get the individual log file like below:


=> [root@dcplnx22981099 sa]# cp sa10 /home/users/orcldb/raj/
=> got to the "/home/users/orcldb/raj/"

sar -r -f sa10 > sar10-ram.log 
 sar -P ALL -f sa10 > sar-cpu.log
sar -n DEV -f sa10 > sar10-network.log


[orcldb@dcplnx22981099 raj]$ ls -ltr sa*
-rw-r----- 1 orcldb oinstall 1328084 Oct 11 22:55 sar10
-rw-r--r-- 1 root   root     1758368 Oct 12 02:40 sa10
-rw-r----- 1 orcldb oinstall   27074 Oct 12 02:40 sar10-ram.log
-rw-r----- 1 orcldb oinstall  558663 Oct 12 02:45 sar-cpu.log
-rw-r----- 1 orcldb oinstall   88494 Oct 12 02:45 sar10-network.log

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

Get the Ksar(to graphical mode) from "ksar github" get the the ksar at "GitHub releases page." => v5.2.3 => ksar-5.2.3-all.jar.

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



Friday, 24 November 2017

Archive log restoration to Specific destination:

RMAN> catalog start with '/oracle/RAJA33DT/archive/RAJA33DT';

using target database control file instead of recovery catalog
searching for all files that match the pattern /oracle/RAJA33DT/archive/RAJA33DT

List of Files Unknown to the Database
=====================================
File Name: /oracle/RAJA33DT/archive/RAJA33DT/RAJA33DT-114008-20170701-63999.1.PITR.redo

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

List of Cataloged Files
=======================
File Name: /oracle/RAJA33DT/archive/RAJA33DT/RAJA33DT-114008-20170701-63999.1.PITR.redo


RMAN> list backup of archivelog all;

RMAN> restore archivelog logseq 113983;

RMAN> restore archivelog logseq 113983;

or

NOTE: (If you want to restore any specific location u have set the destination otherwise it will store default archive location)

run {
allocate channel t1 type 'sbt_tape';
set archivelog destination to '/home/oracle/terry/logfile/';  => (this will change the default restore location.)
restore archivelog logseq xxxxx;

or

restore archivelog from logseq=##### until logseq=#####;
}
=======================================================================
 RMAN MAUNAL BACKUP command:

 rman target=/ log=/oracle/RAJAD/base/admin/RAJAD/var/tmp/rman-arch-04396.temp cmdfile=/oracle/RAJAD/base/admin/RRAJAD/var/tmp/rman-arch-25815.rman

 =================================================================================================
 [orcldb@dcplnx22502547 log]$ cat /oracle/RAJAD/base/admin/RAJAD/var/tmp/rman-arch-25815.rman
set echo on
run {
 set command id to 'RMAN';
 allocate channel DISK01 device type disk format '/oracle/RAJAD/archive/RAJAD/%d-%e-%T-%s.%p.PITR.redo';
 backup tag='PITR' filesperset=32 as compressed backupset skip inaccessible (archivelog all not backed up delete all input);
 delete noprompt archivelog all backed up 1 times to device type disk;
}
exit;

Gathering stats

---------------------------------------------------------
Gathering stats
---------------------------------------------------------
Schema statistics:
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('<SCHEMA_NAME>',DBMS_STATS.AUTO_SAMPLE_SIZE);

System statistics:
EXEC DBMS_STATS.GATHER_SCHEMA_STATS ('SYS');
EXEC DBMS_STATS.GATHER_DATABASE_STATS (gather_sys=>TRUE);
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;

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

nohup ./gather_stats_RAJA023P.ksh  >test.log &

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