RMAN - Cuando no se encuentra un archive log --- RMAN-06726: could not locate archived log

 Lo importante en todo esto, es estar dentro del catalogo correspondiente, sino el crosscheck NO FNCIONA !!

 

oracle backup of logfiles is failing with error

RMAN-06726: could not locate archived log


Issue is that there are some expired logs which still have references in catalogue. 

To fix this issue the cross check

RMAN> crosscheck archivelog all;

You would notice that there are some validations of some of the files fail with  message

validation failed for archived log

After that check all expired logs

RMAN> list expired archivelog all;

and then delete all expired logs

RMAN> delete expired archivelog all;

This will ask to delete them, type yes and hit enter




Issue is fixed.

Re-run the backup and it is all good.

Check Database and Table Space in MySQL

 

Database Space Usage Report

SELECT s.schema_name, CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00),"Mb") total_size, CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),"Mb") data_used, CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_free, IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used FROM INFORMATION_SCHEMA.SCHEMATA s, INFORMATION_SCHEMA.TABLES t WHERE s.schema_name = t.table_schema GROUP BY s.schema_name ORDER BY total_size DESC

Table Space Usage Report

SELECT s.schema_name, table_name, CONCAT(IFNULL(ROUND((SUM(t.data_length)+SUM(t.index_length))/1024/1024,2),0.00),"Mb") total_size, CONCAT(IFNULL(ROUND(((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/1024/1024,2),0.00),"Mb") data_used, CONCAT(IFNULL(ROUND(SUM(data_free)/1024/1024,2),0.00),"Mb") data_free, IFNULL(ROUND((((SUM(t.data_length)+SUM(t.index_length))-SUM(t.data_free))/((SUM(t.data_length)+SUM(t.index_length)))*100),2),0) pct_used FROM INFORMATION_SCHEMA.SCHEMATA s, INFORMATION_SCHEMA.TABLES t WHERE s.schema_name = t.table_schema GROUP BY s.schema_name, table_name ORDER BY total_size DESC

Check for Tables that have Free Space

SELECT s.schema_name, table_name, IFNULL(ROUND(SUM(data_free)/1024,2),0.00) data_free FROM INFORMATION_SCHEMA.SCHEMATA s, INFORMATION_SCHEMA.TABLES t WHERE s.schema_name = t.table_schema GROUP BY s.schema_name, table_name HAVING data_free > 100 ORDER BY data_free DESC

This SQL will check for all objects with free space. It will display tables with any more than 100Kb of free space so you may want to tweak the having clause, but the idea is that it can spot tables which may benefit from an Optimize Table command.

Monitor the MySQL Log File

 MySQL provides a built-in function called load_file which is capable of reading a file from the file system and returning the results as a single column single row result set via SQL. This function can be used to read the error log using the following technique:

1. Locate the MySQL Error Log, from a command shell enter the following:

   C:\> C:\AppD4DBInstallDir\mysql\bin\startClient.bat mysql> show variables like 'log_error';

   The system should return something like the following:

   +-------------------------------------------------------------------+ |  Variable_name | Value                                            | +-------------------------------------------------------------------+ | log_error      | C:\AppD4DBInstallDir\mysql\data\hostname.err     | +-------------------------------------------------------------------+

2. Check that you can read the file from the command line:

   mysql> select load_file ('C:\AppD4DBInstallDir\mysql\data\hostname.err');

Como ver sesiones bloqueadas de usuarios.

Blocking sessions are a problem for the DBA and we need a way to find them so we can deal with them. Blocking sessions occur when a session issues an insert, update or delete command that changes a row.

When the change occurs, the row is locked until the session either commits the change, rolls the change back or the user logs off the system. You can see where problems might occur, for example a user might make a change and then forget to commit it and leaves for the weekend without logging off the system.

We can use this query to find these nasty blocking sessions. We use our old friend, v$session to find the blocking session, and also a list of sessions locked by that session.

Here is a query that gives us a list of blocking sessions and the sessions that they are blocking:

select
   blocking_session,
   sid,
   serial#,
   wait_class,

   seconds_in_wait
from
   v$session

where
   blocking_session is not NULL

order by
   blocking_session;

 

BLOCKING_SESSION        SID    SERIAL# WAIT_CLASS           SECONDS_IN_WAIT

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

             148        135      61521 Idle                              64

In this case, we find that session 148 is blocking session 135 and has been for 64 seconds. We would then want to find out who is running session 148, and go find them and see why they are having a problem.

ORA-16665

 Database: 12c Release 1

Error code: ORA-16665
Description: timeout waiting for the result from a database
Cause: The Data Guard broker was forced to time out a network connection to a remote database because: - The network call to the remote database did not complete in a timely manner. - The remote database was unable to execute the command due to an instance failure.
Action: Check Data Guard broker logs for the details of the failure. If the network call did not complete in a timely manner, increase the CommunicationTimeout configuration property value and reissue the command.

Database: 11g Release 2
Error code: ORA-16665
Description: timeout waiting for the result from a database
Cause: The Data Guard broker was forced to time out a network connection to a remote database because: - The network call to the remote database did not complete in a timely manner. - The remote database was unable to execute the command due to an instance failure.
Action: Check Data Guard broker logs for the details of the failure. If the network call did not complete in a timely manner, increase the CommunicationTimeout configuration property value and reissue the command.

Chequeo del parametro... db_recovery_file_dest_size

 How do I increase the limit for db_recovery_file_dest_size?

Answer:  Even though db_recovery_file_dest_size is 100%, deleting files from the directory was not the ultimate solution because I also had to update the RMAN catalog to remove the old redo log entries.

Also note errors/bugs in v$_flash_recovery_area.

The trick to increasing the limit for db_recovery_file_dest_size was to:

1 - Start the database, then mount the database (Do not open the database)

2 - Use RMAN to run a crosscheck on the archive logs and then update the RMAN catalog to delete the expired redo logs that I had manually deleted via the OS to free-up space in the disk filesystem directory.  See these notes on using the RMAN crosscheck command.

The following scripts will check space utilization for db_recovery_file_dest_size:

col name     format a32
col size_mb  format 999,999,999
col used_mb  format 999,999,999
col pct_used format 999

select
   name,
   ceil( space_limit / 1024 / 1024) size_mb,
   ceil( space_used / 1024 / 1024) used_mb,
   decode( nvl( space_used, 0),0, 0,
   ceil ( ( space_used / space_limit) * 100) ) pct_used
from
    v$recovery_file_dest
order by
   name desc;

*********************************

set lines 100
col name format a60

select
   name,
   floor(space_limit / 1024 / 1024) "Size MB",
   ceil(space_used / 1024 / 1024)   "Used MB"
from
   v$recovery_file_dest
order by
   name;