Oracle DBA

Tuesday, 24 November 2015

Resolve Archive gap between PRIMARY and STANDBY

To Check the below query to confirm ARCHIVE GAP Primary and Standby servers.

A Physical Standby database syncs with Primary by continuous apply of archive logs from a Primary Database.

When the logs are missing on standby difference is huge (Suppose more than 600 logs), you have to rebuild the standby database from scratch.

Please use below query to find out archive gap on Standby STEP 1:

set time on
set lines 200

prompt ++++++++++++++++++++LAST SEQUENCE RECIEVED FROM PRODUCTION and APPLIED ON STANDBY+++++++++++++++++++++++++

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#
;

OUTPUT ARCHIVE:-

    Thread Last Sequence Received Last Sequence Applied Difference
---------- ---------------------- --------------------- ----------
         2                  31852                 31852          50
         1                  49127                 49127          50

prompt ++++++++++++++++++++CHECK FOR GAP AT STANDBY+++++++++++++++++++++++++

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


Find the SCN on the PRIMARY STEP 2:



SQL> select current_scn from v$database;



 



CURRENT_SCN



———–  



222271661





Find the SCN on the STANDBY:



 



SQL> select current_scn from v$database;



CURRENT_SCN



———–  



223762173


Clearly you can see there is difference








In Primary Server will take increment level  backup with SCN STEP 3:-





run {


allocate channel c1 type disk format ‘/oradata/ora_backup/rman/QTSMMDB/%U.bkp’;


backup  AS COMPRESSED BACKUPSET  incremental from
scn 223762173 database;





}





run


{


allocate channel c1 type disk format



‘/oradata/ora_backup/rman/QTSMMDB/%U.bkp’;


backup current controlfile for standby;






}









Stop and shutdown the managed standby apply process STEP 4:


SQL> alter database recover managed standby database cancel;


Database altered.


Shutdown the standby database


SQL> shut immediate





Step 5:-


Copy the backup dump to  standby instance.






step 6:-






Connect to RMAN on STANDBY, Catalog backup files to RMAN using below commands STEP 7:





$ rman target /





RMAN> startup nomount;






RMAN> catalog start with ‘/oradata/ora_backup/Standby/c-3875280703-20150907-02’;





RMAN> restore standby controlfile from


'/oradata/ora_backup/Standby/c-3875280703-20150907-02';





RMAN> sql 'alter database mount standby database';





RMAN> catalog start with ‘/oradata/ora_backup/Standby/’;





PERFORM RECOVER:






RMAN> recover database;





Start managed recovery process STEP 8:





SQL> alter database recover managed standby database disconnect from session;





Database altered.





Check the SCN’s in primary and standby it should be close to each other.

Tuesday, 17 November 2015

ORA-00600 Internal Error Solution Troubleshooting

Have you observed an ORA-0600 error reported in your alert log?

The ORA-600 error is the generic internal error number for Oracle program exceptions. It indicates that a process has encountered a low-level, unexpected condition.

The ORA-600 error statement includes a list of arguments in square brackets:

ORA 600 "internal error code, arguments: [%s], [%s],[%s], [%s], [%s]"

The first argument is the internal message number or character string. This argument and the database version number are critical in identifying the root cause and the potential impact to your system. The remaining arguments in the ORA-600 error text are used to supply further information (e.g. values of internal variables etc).

The first argument may help to narrow your problem to known issues. However, this argument can point to functionality that is referenced by many areas in the Oracle source code. The more detailed call stack information reported in the ORA-600 error trace may be needed to find a solution.

Looking for the best way to diagnose?

Whenever an ORA-600 error is raised, a trace file is generated and an entry is written to the alert.log with details of the trace file location. As of Oracle 11g, the database includes an advanced fault diagnosability infrastructure to manage trace data.

1) Check the Alert Log

The alert log may indicate additional errors or other internal errors at the time of the problem. Focus your analysis of the problem on the first internal error in the sequence. There are some exceptions, but often additional internal errors are side-effects of the first error condition.

The associated trace file may be truncated if the MAX_DUMP_FILE_SIZE parameter is not setup high enough or to „unlimited‟. If you see a message at the end of the trace file

"MAX DUMP FILE SIZE EXCEEDED"

there could be vital diagnostic information missing in the file and finding the root issue may be very difficult. Set the MAX_DUMP_FILE_SIZE appropriately and regenerate the error for complete trace information.

There are alternative checks that can be made to make sure everything's fine because these internal errors can also be because of the following reasons:

Timeouts
File corruption
Failed data checks in memory
Hardware, memory, or I/O errors
Incorrectly restored files.

Note:- we are getting correct solution for ORA-00600 Error , we raised SR to oracle Support so if you are still left with unsolved ORA-600 then it'll be better to go for Oracle Support.

Tuesday, 26 August 2014

The database is down. Please specify the host credentials to access database restart and diagnostics tools

The database is down. Please specify the host credentials to access database restart and diagnostics tools.
Enterprise manager shows that database is down but in real it is not.

[oracle@grhldb01 trace]$ sqldba

SQL*Plus: Release 11.2.0.3.0 Production on Fri Sep 13 10:38:01 2013

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

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

SQL> select name,open_mode from v$database;

NAME    OPEN_MODE
------------
TCHDB   READ WRITE

check the listener status.

[oracle@grhldb01 ~]$ lsnrctl status

LSNRCTL for Linux: Version 11.2.0.3.0 - Production on 26-AUG-2014 13:16:23

Connecting to (ADDRESS=(PROTOCOL=tcp)(HOST=)(PORT=1521))

STATUS of the LISTENER

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

Alias LISTENER

Version TNSLSNR for Linux: Version 11.2.0.3.0 - Production

Start Date 13-MAR-2014 12:53:12

Uptime 165 days 23 hr. 23 min. 10 sec

Trace Level off

Security ON: Local OS Authentication

SNMP ON

Listener Parameter File /orabin/grid11gR2/grid/network/admin/listener.ora

Listener Log File /orabin/grid11gR2/base/diag/tnslsnr/grhldb01/listener/alert/log.xml

Listening Endpoints Summary...

(DESCRIPTION=(ADDRESS=(PROTOCOL=ipc)(KEY=LISTENER)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=1521)))

(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)(HOST=10.128.0.42)(PORT=1521)))

Services Summary...

Service "+ASM" has 1 instance(s).

Instance "+ASM1", status READY, has 1 handler(s) for this service...

Service "TCHDB" has 2 instance(s).

Instance "TCHDB1", status READY, has 2 handler(s) for this service...