Search This Blog

Friday, 20 July 2018

UNNAMED datafiles alerts at Standby:


Error on standby due to lack of space datafile has been created logically on standby with "UNNAMED",but physically file has not created:
---------------------------------------------------------------------------------------------------------------------

Errors in file /oracle/base/diag/rdbms/owsh120a/OWSH120A1/trace/OWSH120A1_pr00_29534.trc:
ORA-01111: name for data file 50 is unknown - rename to correct file
ORA-01110: data file 50: '/oracle/product/11204/db_8/dbs/UNNAMED00050'

STANDBY:
==========
SQL> select FILE#,NAME from v$datafile  where FILE#=50;
     FILE# NAME
---------- ------------------------------------------------------------
        50 /oracle/product/11204/db_8/dbs/UNNAMED00050

SQL> show parameter standby_

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
standby_archive_dest                 string      ?/dbs/arch
standby_file_management              string      AUTO


SQL> alter system set standby_file_management=manual;

System altered.

SQL> alter database create datafile '/oracle/product/11204/db_8/dbs/UNNAMED00050' as '/oracle/archivpool/OWSH120A_datafile/OWSH120A_gsms_central_dmnspc09.dbf';

Database altered.

SQL> alter system set standby_file_management=auto;

System altered.

SQL> select FILE#,NAME from v$datafile  where FILE#=50;

     FILE#
----------
NAME
--------------------------------------------------------------------------------
        50 /oracle/archivpool/OWSH120A_datafile/OWSH120A_gsms_central_dmnspc09.dbf


=> Then start MRP process and check DG sync issue:


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

PROCESS   STATUS          THREAD#  SEQUENCE#     BLOCK#     BLOCKS
--------- ------------ ---------- ---------- ---------- ----------
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
ARCH      CONNECTED             0          0          0          0
RFS       IDLE                  0          0          0          0
MRP0      APPLYING_LOG          1     120985        345    1048576

6 rows selected.

Standby archive seq:

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         1                 120984                120984          0
         2                 111745                111745          0


=> Then resize the datafile on primary to fit datafile in oradata.And then rename the datafile to orginal location in standby:

1.Cancel the mrp before rename:

SQL> ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

Database altered.


2.convert standby file management to Manual:


SQL> alter system set standby_file_management=manual;


System altered.


3.Move datafile to orginal location from archivepool:


orcldb@ah4hi044:~> mv /oracle/archivpool/OWSH120A_datafile/OWSH120A_gsms_central_dmnspc09.dbf /oracle/OWSH120A/oradata01/OWSH120A_gsms_central_dmnspc09.dbf
orcldb@ah4hi044:~> ll /oracle/OWSH120A/oradata01/OWSH120A_gsms_central_dmnspc09.dbf
-rw-r----- 1 orcldb oinstall 3221258240 Jul 20 03:41 /oracle/OWSH120A/oradata01/OWSH120A_gsms_central_dmnspc09.dbf

4.Then rename the datafile in standby database :

SQL> alter database rename file  '/oracle/archivpool/OWSH120A_datafile/OWSH120A_gsms_central_dmnspc09.dbf' to '/oracle/OWSH120A/oradata01/OWSH120A_gsms_central_dmnspc09.dbf';

Database altered.

SQL> alter system set standby_file_management=auto;

System altered.

SQL> select FILE#,NAME from v$datafile  where FILE#=50;

     FILE# NAME
---------- ---------------------------------------------------------------------
        50 /oracle/OWSH120A/oradata01/OWSH120A_gsms_central_dmnspc09.dbf














Monday, 16 July 2018

Recover block from standby if block corrupted on primary:

With the help:

https://shivanandarao-oracle.com/2014/05/03/recovering-a-corruptedlost-datafile-on-primary-database-from-the-standby-database/


To find corrupted block:
 ================================================================
SYS@TWSH722P SQL> select * from V$DATABASE_BLOCK_CORRUPTION;

     FILE#     BLOCK#     BLOCKS CORRUPTION_CHANGE# CORRUPTION_TYPE
---------- ---------- ---------- ------------------ ---------------------------
         6          2        126                  0 ALL ZERO
         6        128         18                  0 ALL ZERO
         6        147        109                  0 ALL ZERO
         6      20736        128                  0 ALL ZERO

4 rows selected.

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

Standby:
=======
backup as copy datafile 6 format '/oracle/TWSH722P/archive/temp_TWSH722P/o_TWSH722P_USERS_1.dbf';

scp (to archive pool)

Primary:
======
 alter database datafile 6 offline;

 rman target /

 catalog datafilecopy '/oracle/TWSH722P/archive/o_TWSH722P_USERS_1.dbf';
 run {
 SET NEWNAME FOR DATAFILE '/oracle/TWSH722P/archive/backup_satndby/o_TWSH722P_USERS_1.dbf' TO '/oracle/TWSH722P/oradata01/o_TWSH722P_USERS_1.dbf';
 }
 restore datafile 6;
 recover datafile 6;

  alter database datafile 6 online;

Note: Here i have used set newname bcz in db level we are not maintaing db_create_file_dest parameter .



========================================================================
[orcldb@dcplnx22981091 oradata01]$ rman target /

Recovery Manager: Release 11.2.0.4.0 - Production on Mon Jul 16 12:35:04 2018

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

connected to target database: OWSH722P (DBID=1734500440)

RMAN> catalog start with '/oracle/TWSH722P/archive/backup_satndby';

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

List of Files Unknown to the Database
=====================================
File Name: /oracle/TWSH722P/archive/backup_satndby/o_TWSH722P_USERS_1.dbf

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

RMAN>  run {
 SET NEWNAME FOR DATAFILE '/oracle/TWSH722P/archive/backup_satndby/o_TWSH722P_USERS_1.dbf' TO '/oracle/TWSH722P/oradata01/o_TWSH722P_USERS_1.dbf';
 }2> 3>

executing command: SET NEWNAME

RMAN> restore datafile 6;

Starting restore at 16-JUL-18
allocated channel: ORA_DISK_1
channel ORA_DISK_1: SID=428 device type=DISK

channel ORA_DISK_1: restoring datafile 00006
input datafile copy RECID=96458 STAMP=981635722 file name=/oracle/TWSH722P/archive/backup_satndby/o_TWSH722P_USERS_1.dbf
destination for restore of datafile 00006: /oracle/TWSH722P/oradata01/o_TWSH722P_USERS_1.dbf
channel ORA_DISK_1: copied datafile copy of datafile 00006
output file name=/oracle/TWSH722P/oradata01/o_TWSH722P_USERS_1.dbf RECID=0 STAMP=0
Finished restore at 16-JUL-18

RMAN> recover datafile 6;

Starting recover at 16-JUL-18
using channel ORA_DISK_1

starting media recovery
media recovery complete, elapsed time: 00:00:00

Finished recover at 16-JUL-18

RMAN>



RMAN> VALIDATE DATAFILE 6 BLOCK 2,128,147,20736;

Starting validate at 16-JUL-18
using channel ORA_DISK_1
channel ORA_DISK_1: starting validation of datafile
channel ORA_DISK_1: specifying datafile(s) for validation
input datafile file number=00006 name=/oracle/TWSH722P/oradata01/o_TWSH722P_USERS_1.dbf
channel ORA_DISK_1: validation complete, elapsed time: 00:00:01
List of Datafiles
=================
File Status Marked Corrupt Empty Blocks Blocks Examined High SCN
---- ------ -------------- ------------ --------------- ----------
6    OK     0              0            4               13087849105005
  File Name: /oracle/TWSH722P/oradata01/o_TWSH722P_USERS_1.dbf
  Block Type Blocks Failing Blocks Processed
  ---------- -------------- ----------------
  Data       0              0
  Index      0              2
  Other      0              2

Finished validate at 16-JUL-18

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.

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