GAP resolution Document

 x`GAP on DR side resolution steps : 

check the current sequence on Primary using : 

set time on 

set lines 200

select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from v$instance;

select max(sequence#) from v$log_history ; 

select name,dbid,open_mode, log_mode,DB_Unique_name,database_role,switchover_status,protection_mode,flashback_on, force_logging from v$database;

and check the same status on DR - (you will get the difference of archives to be applied on DR)

now check if the archive is available on Primary , if not then we need to restore the archive from rman backup using 

RMAN> list backup of archivelog logseq=174111;

 --- thread value to get from v$managed_standby using 

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

TO CHECK DR SYNC STATUS : 

select max(sequence#) from v$log_history ; 

 

set lines 200

SELECT thread#, process, pid, status, client_process, sequence#, block# FROM gv$managed_standby ORDER BY thread#, process;

col FIRST for a25

col NEXT for a25 

col COMPLETION for a25

select thread#, sequence#, applied,to_char(first_time,'mm/dd/yy hh24:mi:ss') first,to_char(next_time, 'mm/dd/yy hh24:mi:ss') next,to_char(completion_time, 'mm/dd/yy hh24:mi:ss') completion

from v$archived_log where thread# = 1 order by first_time;

SELECT  a.thread#, b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq  ARC_DIFF FROM (SELECT thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE   applied = 'YES' GROUP BY thread#) a, (SELECT thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;

select max(sequence#) from v$archived_log;

SELECT  /*+ rule */ 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# ORDER BY 1;

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

to check last applied archive date: 

select max(checkpoint_time) from v$datafile_header;

to check which archive applied at what time - date on the server : 

select NAME,THREAD#,SEQUENCE#,ARCHIVED,APPLIED,DELETED,STATUS,COMPLETION_TIME from v$archived_log; 

alter system checkpoint : before shutdown DR 

rman target /

delete archivelog until time 'sysdate -30';

delete force archivelog until time 'SYSDATE-08';

delete force archivelog all completed before 'SYSDATE-30';

recover automatic standby database 

delete force archivelog until time 'SYSDATE-15';

!find /archive_logs/flash_recovery_area/KCECM_PRIM/archivelog/ -name '*588*'

!find /archive_logs/flash_recovery_area/KCECM_STBY/archivelog/ -name '*588*'

!find /oraarchlogsgnb/ephsprdg/EPHSTDRG/archivelog/ -name '*20515*'

!find /oraarchlogsgnb/ephsprdg/EPHSTDRG/archivelog/ -name '*2067*'

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

Recovery usage : 

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

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;

   

   

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

restore archivelog from sequence 174258  until sequence 174259;

restore archivelog sequence 174596;

find /oraarchlogs/EPHSTDR/archivelog/ -name '*177964*'

GNB DR 

find /oraarchlogsgnb/ephsprdg/EPHSTDRG/archivelog/ -name '*19341*'

GNB PROD 

find /oraarchlogsgnb/ephsprdg/EPHSPRDG/archivelog/ -name '*19341*'

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

   

RMAN> restore archivelog sequence 174112 thread 1;

for Multiple Archives apply together : 

RMAN> restore archivelog from sequence 174598 until sequence 174599;

delete archivelog from sequence 186324 until sequence 186336;

Starting restore at 26-AUG-22

using target database control file instead of recovery catalog

allocated channel: ORA_DISK_1

channel ORA_DISK_1: SID=142 device type=DISK

channel ORA_DISK_1: starting archived log restore to default destination

channel ORA_DISK_1: restoring archived log

archived log thread=1 sequence=173548

channel ORA_DISK_1: reading from backup piece /orabckups/ephsprd/ARC_INC_EPHSPRD_t1113699614_s54698_p1.arc

channel ORA_DISK_1: piece handle=/orabckups/ephsprd/ARC_INC_EPHSPRD_t1113699614_s54698_p1.arc tag=TAG20220826T010014

channel ORA_DISK_1: restored backup piece 1

channel ORA_DISK_1: restore complete, elapsed time: 00:00:35

Finished restore at 26-AUG-22

similarly , we need to restore all the files which are not shipped to the target (DR) and restore it on primary and then it will start applying on DR. 

ERROR : on GNB : 

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

ORA-19815: WARNING: db_recovery_file_dest_size of 75161927680 bytes is 100.00% used, and has 0 remaining bytes available.

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

at oS level when checked the space was visible but in alert log file when checked using the query 

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;

   

   it showed that the utilization is 100% , and there was some obsolete backup / old backups , removed some old backup using RMAN and that started appying the log 

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

   

   

   

   

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

to stop DR database :

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

select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from v$instance;

select name,dbid,open_mode, log_mode,DB_Unique_name,database_role,switchover_status,protection_mode,flashback_on, force_logging from v$database;

ALTER DATABASE RECOVER MANAGED STANDBY DATABASE CANCEL;

shutdown immediate;

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

to start those database back (DR)

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

startup nomount;

alter database mount standby database;

select INSTANCE_NAME,HOST_NAME,VERSION,STARTUP_TIME,STATUS from v$instance;

select name,dbid,open_mode, log_mode,DB_Unique_name,database_role,switchover_status,protection_mode,flashback_on, force_logging from v$database;

select PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;

alter database recover managed standby database disconnect from session;

select PROCESS, STATUS,SEQUENCE#,BLOCK#,BLOCKS, DELAY_MINS FROM V$MANAGED_STANDBY;

select max(sequence#) from v$log_history ; 

FIREWALL ISSUE on OPB : 

[root@mprvld001 ~]# systemctl stop firewalld

[root@mprvld001 ~]# systemctl status firewalld

● firewalld.service - firewalld - dynamic firewall daemon

   Loaded: loaded (/usr/lib/systemd/system/firewalld.service; enabled; vendor preset: enabled)

   Active: inactive (dead) since Sat 2023-05-13 07:45:23 EDT; 3s ago

     Docs: man:firewalld(1)

  Process: 1238 ExecStart=/usr/sbin/firewalld --nofork --nopid $FIREWALLD_ARGS (code=exited, status=0/SUCCESS)

Main PID: 1238 (code=exited, status=0/SUCCESS)

 

 AWEZ Given Queries for DR : 

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

 

 SELECT dest_id,

sequence#,

applied,

deleted,

TO_CHAR (first_time, 'dd/mm/yyyy hh24:mi:ss') first_time

FROM (SELECT dest_id,

first_time,

sequence#,

applied,

deleted

FROM v$archived_log 

ORDER BY first_time DESC, dest_id)

WHERE APPLIED='YES' and ROWNUM <= 100;

set linesize 300

set pages 70

col name for a30

col value for a60

col message for a90

col destination for a35

col dest_name for a40

select name,value from gv$parameter where name in ('db_name','db_unique_name','db_domain','db_file_name_convert','log_file_name_convert','fal_server','fal_client','remote_login_passwordfile','standby_file_management','dg_broker_start','dg_broker_config_file1','dg_broker_config_file2');

select status,instance_name,database_role,open_mode,protection_mode,switchover_status from gv$instance,gv$database;

select name,(space_limit/1024/1024/1024) "Limit in GB",(space_used/1024/1024/1024) "Used in GB" from v$recovery_file_dest;

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

select thread#,max(sequence#) from gv$archived_log where applied='YES' group by thread#;

select dest_id, dest_name, status, target, archiver , destination from GV$ARCHIVE_DEST where destination IS NOT NULL;

select inst_id,process,status,sequence#,thread#,client_process from gv$managed_standby;

select group#,thread#,status,members,(bytes/1024/1024)"Each ORL File Size in MB" from gv$log;

select group#,thread#,status,(bytes/1024/1024)"Each SRL File Size in MB" from gv$standby_log;

May 12 19:33:04 mprvld001.mail.opb.on.ca systemd[1]: Starting firewalld - dynamic firewall daemon...

May 12 19:33:05 mprvld001.mail.opb.on.ca systemd[1]: Started firewalld - dynamic firewall daemon.

May 13 07:45:22 mprvld001.mail.opb.on.ca systemd[1]: Stopping firewalld - dynamic firewall daemon...

May 13 07:45:23 mprvld001.mail.opb.on.ca systemd[1]: Stopped firewalld - dynamic firewall daemon.

[root@mprvld001 ~]#

set scan on

set feed off

set linesize 200

column thread format a6;

column "PR-Archived" format a13;

column "STBY-Archived" format a15;

column "STBY-Applied" format a14;

column "Shipping GAP " format a16;

column "Applied GAP " format a20;

--ACCEPT DEST PROMPT 'Enter the Standby Archive Log Destination : '

SELECT *

FROM (SELECT LPAD (t1, 4, ' ') "Thread",

LPAD (pricre, 9, ' ') "PR-Archived",

LPAD (stdcre, 10, ' ') "STBY-Archived",

LPAD (stdnapp, 9, ' ') "STBY-Applied",

LPAD (pricre - stdcre, 13, ' ')

"Shipping GAP ",

LPAD (stdcre - stdnapp, 15, ' ')

"Applied GAP "

FROM ( SELECT MAX (sequence#) stdcre, thread# t1

FROM v$archived_log

WHERE standby_dest = 'YES'

AND resetlogs_id IN

(SELECT MAX (RESETLOGS_ID)

FROM v$archived_log)

AND thread# IN (1, 2, 3, 4)

GROUP BY thread#) a,

( SELECT MAX (sequence#) stdnapp, thread# t2

FROM v$archived_log

WHERE standby_dest = 'YES'

AND resetlogs_id IN

(SELECT MAX (RESETLOGS_ID)

FROM v$archived_log)

AND thread# IN (1, 2, 3, 4)

AND applied = 'YES'

GROUP BY thread#) b,

( SELECT MAX (sequence#) pricre, thread# t3

FROM v$archived_log

WHERE standby_dest = 'NO'

AND resetlogs_id IN

(SELECT MAX (RESETLOGS_ID)

FROM v$archived_log)

AND thread# IN (1, 2, 3, 4)

GROUP BY thread#) c

WHERE a.t1 = b.t2 AND b.t2 = c.t3 AND c.t3 = a.t1)

ORDER BY 1

/

set feed on

break on off

Reference DOcuments from Oracle : 

REFERENCES

NOTE:734862.1 - Step By Step Guide On How To Recreate Standby Control File When Datafiles Are On ASM And Using Oracle Managed Files

NOTE:549078.1 - ORA-600 [25016] Errors Trying To Recover Managed Standby Database

NOTE:753893.1 - Impact of Very Large Controlfiles on Data Guard Environments

NOTE:387339.1 - MAA - Creating a Single Instance Physical Standby for a RAC Primary

NOTE:404724.1 - ORA-03135 When Connecting to the Database

NOTE:739522.1 - ORA - 03135 : Connection Lost Contact While Shipping from Primary Server to Standby server

NOTE:47325.1 - Init.ora Parameter "DB_FILE_NAME_CONVERT" Reference Note

BUG:6029179 - MRP WAITING FOR LOGS THAT ARE ALREADY APPLIED

NOTE:743101.1 - ORA-01110 ORA-1122 ORA-01251 When Restoring With a Backup from a Standby DB

NOTE:836986.1 - Steps to perform for Rolling Forward a Physical Standby Database using RMAN Incremental Backup.

NOTE:1537316.1 - Data Guard Gap Detection and Resolution Possibilities

NOTE:759181.1 - Dataguard Physical Standby occurs ORA-600 [2130] after added new node to primary RAC database.

NOTE:787354.1 - Troubleshooting ORA-3135 Connection Lost Contact

NOTE:290817.1 - Rolling a Standby Forward using an RMAN Incremental Backup in 9i

NOTE:6004936.8 - Bug 6004936 - Standby apply stops after ORA-16146

NOTE:730066.1 - Troubleshooting ORA-3136 Connection Timeouts Errors - Database Diagnostics

NOTE:799353.1 - How to Resolve Error in Remote Archiving

NOTE:304488.1 - Using standby_file_management with Raw Devices

RMAN Backup Details

 1- 

set lines 200 
col START_TIME for a20 
col END_TIME for a20 
col Hours format 9999.99
col STATUS format a10
select SESSION_KEY, INPUT_TYPE, STATUS,
to_char(START_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_start_time,
to_char(END_TIME,'mm-dd-yyyy hh24:mi:ss') as RMAN_Bkup_end_time,
elapsed_seconds/3600 Hours from V$RMAN_BACKUP_JOB_DETAILS
order by session_key;

2- 

with backup size : 

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

set lines 200

col OUTPUT_BYTES_DISPLAY for a30

col TIME_TAKEN_DISPLAY for a30 

select start_time, status, input_type, output_bytes_display, time_taken_display from v$rman_backup_job_details order by start_time desc;

Only INCR : 

set lines 200

col OUTPUT_BYTES_DISPLAY for a30

col TIME_TAKEN_DISPLAY for a30 

select start_time, status, input_type, output_bytes_display, time_taken_display from v$rman_backup_job_details where INPUT_TYPE='DB INCR' order by start_time desc;

------

rman target /

delete archivelog until time 'sysdate -30';

delete force archivelog until time 'SYSDATE-15';

delete force archivelog all completed before 'SYSDATE-30';

crosscheck backup of database;

crosscheck backup of archivelog all;

crosscheck backup of controlfile;

delete expired backup;

delete noprompt obsolete;

ORA 01103 : Database name 'PUM2PIX' in control file is not 'HCMPRD1'

 we encountered issue after linux team performed patching on the server , after server reboot is done , they requested to start the database services , we set the environment and tried starting the database. database did not start , gave error "ORA 01103 : Database name 'PUM2PIX' in control file is not 'HCMPRD1'" before going into mount. 

we checked the file system accesses and control file permission , all looked good , we checked the value in pfile for the database name db_name='PUM2PIX' 

we started the database using pfile and that worked, 

after logging in using pfile , we created spfile and rebooted the database , that worked !!! 

sometime even minor trick will help a big save. :) 

delete archive log from RMAN of last 8 days

 delete force archivelog until time 'SYSDATE-08';

Linux Memory

 ps -A --sort -rss -o comm, pcpu, pmem, pid, user| head -n 15 

free | grep Mem | awk '{print $3/$2 * 100.0}'

DR scripts

 

select max(sequence#) from v$log_history ; 

 

set lines 200

SELECT thread#, process, pid, status, client_process, sequence#, block# FROM gv$managed_standby ORDER BY thread#, process;

col FIRST for a25

col NEXT for a25 

col COMPLETION for a25

select thread#, sequence#, applied,to_char(first_time,'mm/dd/yy hh24:mi:ss') first,to_char(next_time, 'mm/dd/yy hh24:mi:ss') next,to_char(completion_time, 'mm/dd/yy hh24:mi:ss') completion

from v$archived_log where thread# = 1 order by first_time;

SELECT  a.thread#, b. last_seq, a.applied_seq, a. last_app_timestamp, b.last_seq-a.applied_seq  ARC_DIFF FROM (SELECT thread#, MAX(sequence#) applied_seq, MAX(next_time) last_app_timestamp FROM gv$archived_log WHERE   applied = 'YES' GROUP BY thread#) a, (SELECT thread#, MAX (sequence#) last_seq FROM gv$archived_log GROUP BY thread#) b WHERE a.thread# = b.thread#;

select max(sequence#) from v$archived_log;

SELECT  /*+ rule */ 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# ORDER BY 1;

archive generation - compact script Oracle

select trunc(COMPLETION_TIME,'DD') Day, thread#, 

round(sum(BLOCKS*BLOCK_SIZE)/1024/1024/1024) GB,

count(*) Archives_Generated from v$archived_log 

group by trunc(COMPLETION_TIME,'DD'),thread# order by 1;

archive generation per hour Oracle !

 set linesize 140
set feedback off
set timing off
set pagesize 1000
col ARCHIVED format a8
col ins    format 99  heading "DB"
col member format a80
col status format a12
col archive_date format a20
col member format a60
col type   format a10
col group#  format 99999999
col min_archive_interval format a20
col max_archive_interval format a20
col h00 heading "H00" format  a3 
col h01 heading "H01" format  a3 
col h02 heading "H02" format  a3 
col h03 heading "H03" format  a3 
col h04 heading "H04" format  a3 
col h05 heading "H05" format  a3 
col h06 heading "H06" format  a3 
col h07 heading "H07" format  a3 
col h08 heading "H08" format  a3 
col h09 heading "H09" format  a3 
col h10 heading "H10" format  a3 
col h11 heading "H11" format  a3 
col h12 heading "H12" format  a3 
col h13 heading "H13" format  a3 
col h14 heading "H14" format  a3 
col h15 heading "H15" format  a3 
col h16 heading "H16" format  a3 
col h17 heading "H17" format  a3 
col h18 heading "H18" format  a3 
col h19 heading "H19" format  a3 
col h20 heading "H20" format  a3 
col h21 heading "H21" format  a3 
col h22 heading "H22" format  a3 
col h23 heading "H23" format  a3 
col total format a6
col date format a10
select * from v$logfile order by group#;
select * from v$log order by SEQUENCE#;
select max( sequence#) last_sequence, max(completion_time) completion_time, max(block_size) block_size from v$archived_log ;
SELECT instance ins,
       log_date "DATE" ,
       lpad(to_char(NVL( COUNT( * ) , 0 )),6,' ') Total,
       lpad(to_char(NVL( SUM( decode( log_hour , '00' , 1 ) ) , 0 )),3,' ') h00 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '01' , 1 ) ) , 0 )),3,' ') h01 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '02' , 1 ) ) , 0 )),3,' ') h02 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '03' , 1 ) ) , 0 )),3,' ') h03 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '04' , 1 ) ) , 0 )),3,' ') h04 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '05' , 1 ) ) , 0 )),3,' ') h05 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '06' , 1 ) ) , 0 )),3,' ') h06 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '07' , 1 ) ) , 0 )),3,' ') h07 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '08' , 1 ) ) , 0 )),3,' ') h08 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '09' , 1 ) ) , 0 )),3,' ') h09 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '10' , 1 ) ) , 0 )),3,' ') h10 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '11' , 1 ) ) , 0 )),3,' ') h11 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '12' , 1 ) ) , 0 )),3,' ') h12 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '13' , 1 ) ) , 0 )),3,' ') h13 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '14' , 1 ) ) , 0 )),3,' ') h14 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '15' , 1 ) ) , 0 )),3,' ') h15 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '16' , 1 ) ) , 0 )),3,' ') h16 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '17' , 1 ) ) , 0 )),3,' ') h17 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '18' , 1 ) ) , 0 )),3,' ') h18 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '19' , 1 ) ) , 0 )),3,' ') h19 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '20' , 1 ) ) , 0 )),3,' ') h20 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '21' , 1 ) ) , 0 )),3,' ') h21 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '22' , 1 ) ) , 0 )),3,' ') h22 ,
       lpad(to_char(NVL( SUM( decode( log_hour , '23' , 1 ) ) , 0 )),3,' ') h23  
FROM   (
        SELECT thread# INSTANCE ,
               TO_CHAR( first_time , 'YYYY-MM-DD' ) log_date ,
               TO_CHAR( first_time , 'hh24' ) log_hour 
        FROM   v$log_history 
       ) 
GROUP  BY 
       instance,log_date
ORDER  BY 
       log_date ; 
       
select trunc(min(completion_time - first_time))||'  Day  '||
       to_char(trunc(sysdate,'dd') + min(completion_time - first_time),'hh24:mm:ss')||chr(10) min_archive_interval, 
       trunc(max(completion_time - first_time))||'  Day  '||
       to_char(trunc(sysdate,'dd') + max(completion_time - first_time),'hh24:mm:ss')||chr(10) max_archive_interval
from gv$archived_log 
where sequence# <> ( select max(sequence#) from gv$archived_log ) ;
set feedback on
set timing on

 As seen below obsolete EXTRACT1_NAME is showing REQ_SCN = 0.

SQL>SELECT CAPTURE_NAME, CAPTURE_TYPE, STATUS, to_char(REQUIRED_CHECKPOINT_SCN,'999999999999999')as REQ_SCN ,to_char(OLDEST_SCN,'999999999999999')as OLDEST_SCN FROM DBA_CAPTURE;

CAPTURE_NAME    CAPTURE_TYPE STATUS          REQ_SCN      OLDEST_SCN
--------------  ------------ -------        --------      ----------
OGG$CAP_EXTRACT-NAME LOCAL        ENABLED        64983227        64983227
OGG$CAP_EXTRACT1-NAME LOCAL        ENABLED               0               0

 

After unregistering as follows RMAN was able to delete the old archive logs:

UNREGISTER EXTRACT EXTRACT-NAME DATABASE

find /home -xdev -type f -size +1G -exec ls -lrth {} \;

find . -name '*.trc' -mtime +2 -exec rm -f {} \; 

UNDO required size - using Stats

 SELECT 'The Required undo tablespace size using Statistics In Memory is ' || dbms_undo_adv.required_undo_size(900) || ' MB' required_undo_size FROM dual;

Query to get 10 Large Object in Oracle Database

SELECT * FROM (select SEGMENT_NAME,SEGMENT_TYPE,BYTES/1024/1024/1024 GB,TABLESPACE_NAME from dba_segments order by 3 desc) WHERE ROWNUM <= 10;

Recovery file usage

 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;

Meta data tablespace

 

set lines 190
set pages 5000
set long 50000
set head off echo off
select 'select dbms_metadata.get_ddl(''TABLESPACE'','''
|| tablespace_name || ''') from dual;' from dba_tablespaces where tablespace_name='TSTDOCMGMT01';