Wednesday 24 July 2019

ORA-38701 ORA-27037 when select * from v$restore_point;

When trying to find the name of a restore point that has one or all of its flashback logs not available you will get this error:

SQL> select * from v$restore_point;
select * from v$restore_point
              *
ERROR at line 1:
ORA-38701: Flashback database log 112 seq 10593 thread 1: "/fra/ERDB/flashback/o1_mf_gbd00tqk_.flb"
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

You have to get the restore point name from RMAN which read the details from the controlfile instead of the dictionary:

RMAN> list restore point all;
using target database control file instead of recovery catalog
SCN              RSP Time  Type       Time      Name
---------------- --------- ---------- --------- ----
6418212255444              GUARANTEED 01-JUL-19 TEMP

Drop the restore point from SQLPLUS:
SQL> drop restore point temp;

Monday 22 July 2019

Unable to Drop a REDOLOG Group ORA-00313 ORA-27037 No such file or directory

Cannot drop a standby redo logfile group because its files were already deleted on OS:

SQL> alter database drop standby logfile group 26;
alter database drop standby logfile group 26
*
ERROR at line 1:
ORA-00313: open failed for members of log group 26 of thread 2
ORA-00312: online log 26 thread 2: '/data01/awsdev2/datafiles/group_26.273.968987155'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3
ORA-00312: online log 26 thread 2: '/data01/awsdev2/datafiles/group_26.273.968987153'
ORA-27037: unable to obtain file status
Linux-x86_64 Error: 2: No such file or directory
Additional information: 3

Fix:

Re-populate the logfile members inside this group:
SQL> alter database clear unarchived logfile group 26;

Database altered.

Now you can drop the standby redolog group:
SQL> alter database drop standby logfile group 26;

Database altered.

Monday 15 July 2019

Pause/Resume/Kill a Running RMAN Backup

To Check if there is an RMAN backup is currently running:
col START_TIME for a15
col END_TIME for a15
col TIME_TAKEN_DISPLAY for a10
col INPUT_BYTES_DISPLAY heading "DATA SIZE" for a10
col OUTPUT_BYTES_DISPLAY heading "Backup Size" for a11
col OUTPUT_BYTES_PER_SEC_DISPLAY heading "Speed/s" for a10
col output_device_type heading "Device_TYPE" for a11
SELECT to_char (start_time,'DD-MON-YY HH24:MI') START_TIME,to_char(end_time,'DD-MON-YY HH24:MI') END_TIME, time_taken_display, status,input_type, output_device_type,input_bytes_display, output_bytes_display,output_bytes_per_sec_display,COMPRESSION_RATIO COMPRESS_RATIO FROM v$rman_backup_job_details WHERE status like 'RUNNING%';

To Pause an already running RMAN backup: [This is only applicable for Linux OS]
set pages 0 feedback off 
select 'TO PAUSE THE RUNNING RMAN BACKUP RUN THIS OS COMMAND:=>    kill -STOP '||listagg (p.spid, ' ')  WITHIN GROUP (ORDER BY p.spid) from v$session s, v$process p where s.program like 'rman@%' and p.addr=s.paddr;

To Resume an already "Paused" RMAN backup: [This is only applicable for Linux OS]
set pages 0 feedback off 
select 'TO RESUME A "PAUSED" RMAN BACKUP RUN THIS OS COMMAND:=>  kill -CONT '||listagg (p.spid, ' ')  WITHIN GROUP (ORDER BY p.spid) from v$session s, v$process p where s.program like 'rman@%' and p.addr=s.paddr;

To Terminate an already running RMAN backup: [This is only applicable for Linux OS]
set pages 0 feedback off 
select 'TO KILL  THE RUNNING RMAN BACKUP RUN THIS OS COMMAND:=>  kill -9 '||listagg (p.spid, ' ')  WITHIN GROUP (ORDER BY p.spid) from v$session s, v$process p where s.program like 'rman@%' and p.addr=s.paddr;