RMAN-08138: WARNING: archived log not deleted - must create more backups archived log file name

RMAN-08138: WARNING: archived log not deleted - must create more backups archived log file name      

 

RMAN-08138 : Reason of  RMAN-08138 is RMAN DELETION policy

while we deleting archivelogs files using RMAN, RMAN checks whether archivelog file has been taken backup N times to Disk or Tape before deleting archivelog files.

 

Default :  ARCHIVELOG DELETION POLICY TO NONE

 

RMAN> show all;

RMAN configuration parameters for database with db_unique_name TESTPRY are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

CONFIGURE BACKUP OPTIMIZATION ON;

CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default

CONFIGURE CONTROLFILE AUTOBACKUP ON;

CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default

CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;

CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default

CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'F:\ORCL_BACKUP\TEST_BACKUP\02_FEB_2016\TEST_%d_%U';

CONFIGURE MAXSETSIZE TO UNLIMITED; # default

CONFIGURE ENCRYPTION FOR DATABASE OFF; # default

CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default

CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default

CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\APP\DILIPBAKSHI\PRODUCT\11.2.0\DBHOME_2\DATABASE\SNCFTESTPRY.ORA'; # default

 

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;
old RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name TESTPRY are:
CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;
CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'F:\ORCL_BACKUP\TEST_BACKUP\02_FEB_2016\TEST_%d_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\APP\DILIPBAKSHI\PRODUCT\11.2.0\DBHOME_2\DATABASE\SNCFTESTPRY.ORA'; # default

RMAN> backup archivelog all FORMAT 'F:\temp\Temp3\Backup\RMAN_BACKUP\%n_ARCHIVE_BKP_%p_%s_%D_%M_%Y_%U' delete input;

 Starting backup at 17-NOV-16

using channel ORA_DISK_1
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1686 RECID=2307 STAMP=928106656
channel ORA_DISK_1: starting piece 1 at 17-NOV-16
channel ORA_DISK_1: finished piece 1 at 17-NOV-16
piece handle=F:\TEMP\TEMP3\BACKUP\RMAN_BACKUP\TESTPRYX_ARCHIVE_BKP_1_1693_17_11_2016_KTRL4Q5H_1_1 tag=TAG20161117T104712 comment=NONE

channel ORA_DISK_1: backup set complete, elapsed time: 00:00:07

channel ORA_DISK_1: deleting archived log(s)

RMAN-08138: WARNING: archived log not deleted - must create more backups archived log file name      =F:\APP\DILIPBAKSHI\FLASH_RECOVERY_AREA\TESTPRY\ARCHIVELOG\2016_11_16\O1_MF_1_1686_D2S7B6YJ_.ARC

thread=1 sequence=1686

Finished backup at 17-NOV-16

 

Starting Control File and SPFILE Autobackup at 17-NOV-16
piece handle=F:\APP\DILIPBAKSHI\FLASH_RECOVERY_AREA\TESTPRY\AUTOBACKUP\2016_11_17\O1_MF_S_928147640_D2THC29D_.BKP comment=NONE

Finished Control File and SPFILE Autobackup at 17-NOV-16

Note : According to above policy RMAN will not delete those Archivelog files which have not been  backed up 2 Times to Disk

RMAN> list archivelog all backed up 1 times to DISK;

List of Archived Log Copies for database with db_unique_name TESTPRY
=====================================================================
Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
2307    1    1686    A 16-NOV-16
Name:F:\APP\DILIPBAKSHI\FLASH_RECOVERY_AREA\TESTPRY\ARCHIVELOG\2016_11_16 \O1_MF_1_1686_D2S7B6YJ_.ARC

 

RMAN> list archivelog all backed up 0 times to DISK;

List of Archived Log Copies for database with db_unique_name TESTPRY
=====================================================================
Key     Thrd Seq     S Low Time
------- ---- ------- - ---------
2307    1    1686    A 16-NOV-16
Name:F:\APP\DILIPBAKSHI\FLASH_RECOVERY_AREA\TESTPRY\ARCHIVELOG\2016_11_16\O1_MF_1_1686_D2S7B6YJ_.ARC

2308    1    1687    A 16-NOV-16
Name:F:\APP\DILIPBAKSHI\FLASH_RECOVERY_AREA\TESTPRY\ARCHIVELOG\2016_11_17\O1_MF_1_1687_D2THGPLY_.ARC

 

RMAN> CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;

old RMAN configuration parameters:

CONFIGURE ARCHIVELOG DELETION POLICY TO BACKED UP 2 TIMES TO DISK;
new RMAN configuration parameters:
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
new RMAN configuration parameters are successfully stored

RMAN> show all;

RMAN configuration parameters for database with db_unique_name TESTPRY are:

CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 7 DAYS;

CONFIGURE BACKUP OPTIMIZATION ON;
CONFIGURE DEFAULT DEVICE TYPE TO DISK; # default
CONFIGURE CONTROLFILE AUTOBACKUP ON;
CONFIGURE CONTROLFILE AUTOBACKUP FORMAT FOR DEVICE TYPE DISK TO '%F'; # default
CONFIGURE DEVICE TYPE DISK BACKUP TYPE TO COMPRESSED BACKUPSET PARALLELISM 1;
CONFIGURE DATAFILE BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE ARCHIVELOG BACKUP COPIES FOR DEVICE TYPE DISK TO 1; # default
CONFIGURE CHANNEL DEVICE TYPE DISK FORMAT   'F:\ORCL_BACKUP\TEST_BACKUP\02_FEB_2016\TEST_%d_%U';
CONFIGURE MAXSETSIZE TO UNLIMITED; # default
CONFIGURE ENCRYPTION FOR DATABASE OFF; # default
CONFIGURE ENCRYPTION ALGORITHM 'AES128'; # default
CONFIGURE COMPRESSION ALGORITHM 'BASIC' AS OF RELEASE 'DEFAULT' OPTIMIZE FOR LOAD TRUE ; # default
CONFIGURE ARCHIVELOG DELETION POLICY TO NONE;
CONFIGURE SNAPSHOT CONTROLFILE NAME TO 'F:\APP\DILIPBAKSHI\PRODUCT\11.2.0\DBHOME_2\DATABASE\SNCFTESTPRY.ORA'; # default

 

RMAN>

RMAN> backup archivelog all FORMAT 'F:\temp\Temp3\Backup\RMAN_BACKUP\%n_ARCHIVE_BKP_%p_%s_%D_%M_%Y_%U' delete input;

Starting backup at 17-NOV-16

current log archived
using channel ORA_DISK_1
skipping archived log file F:\APP\DILIPBAKSHI\FLASH_RECOVERY_AREA\TESTPRY\ARCHIVELOG\2016_11_16\O1_MF_1_1686_D2S7B6YJ_.ARC; already backed up 1 time(s)
channel ORA_DISK_1: starting compressed archived log backup set
channel ORA_DISK_1: specifying archived log(s) in backup set
input archived log thread=1 sequence=1687 RECID=2308 STAMP=928147759
input archived log thread=1 sequence=1688 RECID=2309 STAMP=928149066
channel ORA_DISK_1: starting piece 1 at 17-NOV-16
channel ORA_DISK_1: finished piece 1 at 17-NOV-16
piece handle=F:\TEMP\TEMP3\BACKUP\RMAN_BACKUP\TESTPRYX_ARCHIVE_BKP_1_1695_17_11_2016_KVRL4RIB_1_1 tag=TAG20161117T111106 comment=NONE
channel ORA_DISK_1: backup set complete, elapsed time: 00:00:03
channel ORA_DISK_1: deleting archived log(s)
archived log file name=F:\APP\DILIPBAKSHI\FLASH_RECOVERY_AREA\TESTPRY\ARCHIVELOG\2016_11_17\O1_MF_1_1687_D2THGPLY_.ARC RECID=2308 STAMP=928147759
archived log file name=F:\APP\DILIPBAKSHI\FLASH_RECOVERY_AREA\TESTPRY\ARCHIVELOG\2016_11_17\O1_MF_1_1688_D2TJQK8D_.ARC RECID=2309 STAMP=928149066
channel ORA_DISK_1: deleting archived log(s)
archived log file name=F:\APP\DILIPBAKSHI\FLASH_RECOVERY_AREA\TESTPRY\ARCHIVELOG\2016_11_16\O1_MF_1_1686_D2S7B6YJ_.ARC RECID=2307 STAMP=928106656
Finished backup at 17-NOV-16

Starting Control File and SPFILE Autobackup at 17-NOV-16
piece handle=F:\APP\DILIPBAKSHI\FLASH_RECOVERY_AREA\TESTPRY\AUTOBACKUP\2016_11_17\O1_MF_S_928149073_D2TJQW31_.BKP comment=NONE
Finished Control File and SPFILE Autobackup at 17-NOV-16

 

Note : This time No RMAN-08138: warning  raised.

Script for USER AUDIT which oracle database user created new user

col OS_USERNAME format a15
col USERNAME format a15
col USERHOST format a15
col TERMINAL format a20
col ACTION format a10
col GRANTEE format a30
col OS_PROCESS format a30
select dba_audit_trail.OS_USERNAME,
  dba_audit_trail.USERNAME,
  dba_audit_trail.USERHOST,
  dba_audit_trail.TERMINAL,
  dba_audit_trail.TIMESTAMP,
  dba_audit_trail.ACTION,
  dba_audit_trail.ACTION_NAME,
  dba_audit_trail.GRANTEE,
  dba_audit_trail.SESSIONID,
  dba_audit_trail.SESSIONID,
  dba_audit_trail.OS_PROCESS,
  dba_audit_trail.LOGOFF_TIME,
  dba_audit_trail.SQL_TEXT
from  dba_audit_trail
where dba_audit_trail.USERNAME IN (select grantee from dba_role_privs
         where granted_role IN (select role from role_sys_privs where PRIVILEGE like 'CREATE USER'))
and dba_audit_trail.SQL_TEXT like 'create%';

DELETE TABLE DATA USING CURSOR

create or replace PROCEDURE EMPMAST.DELETE1_DATA_From_INDIA_EMP
 AS
 v_city INDIA_EMP.CITY%TYPE;
 CURSOR del_cursor1 IS SELECT DistrictName INTo v_city
                                           FROM ( select DistrictName
                                                         from    EMPMAST.DISTRICT_MASTER
                                                         ORDER BY dbms_random.value ) WHERE rownum = 1;
BEGIN
 OPEN del_cursor1;
 LOOP
 FETCH del_cursor1 into v_city;
 EXIT WHEN del_cursor1%NOTFOUND;
 DELETE from EMPMAST.INDIA_EMP;
 COMMIT;
 END LOOP;
 dbms_output.put_line('TOTLA '||sql%rowcount||' Deleted');
 CLOSE del_cursor1;
 end DELETE1_DATA_From_INDIA_EMP;
 /


 DELETE FROM EMPMAST.INDIA_EMP WHERE CITY=(select DistrictName from    EMPMAST.DISTRICT_MASTER ORDER BY dbms_random.value ) WHERE rownum = 1);

ORA-31608: specified object of type OBJECT_GRANT not found

SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','DILIP') from dual;
ERROR:
ORA-31608: specified object of type OBJECT_GRANT not found
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.DBMS_METADATA", line 3915
ORA-06512: at "SYS.DBMS_METADATA", line 5826
ORA-06512: at line 1


no rows selected
SQL>
SQL>
SQL>
SQL> grant select on dual to dilip;
Grant succeeded.
SQL> SELECT DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','DILIP') from dual;
DBMS_METADATA.GET_GRANTED_DDL('OBJECT_GRANT','DILIP')
--------------------------------------------------------------------------------
  GRANT SELECT ON "SYS"."DUAL" TO "DILIP";

SHRINK SPACE OF FRAGMENTED TABLE

                                        CREATE TABLE SCOTT.TEST_SHRINK


CREATE TABLE SCOTT.TEST_SHRINK AS SELECT * FROM ALL_OBJECTS;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
insert into SCOTT.TEST_SHRINK select * from SCOTT.TEST_SHRINK;
COMMIT;


exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TEST_SHRINK',estimate_percent=>100);




FIND OUT TABLE FULL SIZE ,ACTUAL SIZE  AND PERCENTAGE OF FRAGMENTATION


set lines 300 pages 300 ;
select a.owner, a.table_name, b.bytes/1024/1024/1024 "Size(G)",  (a.avg_row_len*a.num_rows/1024/1024/1024) "Actual(G)",
to_char(last_analyzed,'DD-MON-YY') "LAST ANAL",
trunc((b.bytes/1024/1024/1024) - (a.avg_row_len*a.num_rows/1024/1024/1024)) "Diff(G)" ,
(b.bytes-a.avg_row_len*a.num_rows)*100/ b.bytes "% Frag"
from     dba_tables a,
    dba_segments b
where    a.table_name = b.segment_name
and     a.owner = b.owner
and  a.table_name = 'TEST_SHRINK'
and a.owner='SCOTT';

TAKE TABLE COUNT

select count(1) from scott.TEST_SHRINK;


select num_rows, blocks, empty_blocks, last_analyzed from user_tables where table_name = 'TEST_SHRINK';


DELETE THE DUPLICATE ROWS FROM THE TABLE

DELETE FROM SCOTT.TEST_SHRINK A WHERE ROWID > (
    SELECT min(rowid) FROM SCOTT.TEST_SHRINK B
    WHERE A.OBJECT_ID = B.OBJECT_ID);

select count(1) from scott.TEST_SHRINK;

exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TEST_SHRINK',estimate_percent=>100);
select num_rows, blocks, empty_blocks, last_analyzed from user_tables where table_name = 'TEST_SHRINK';

FIND OUT TABLE FULL SIZE ,ACTUAL SIZE  AND PERCENTAGE OF FRAGMENTATION

----------------------Use above script

BEFORE TABLE SIZE REPORT

OWNER                          TABLE_NAME                        Size(G)  Actual(G) LAST ANAL    Diff(G)     % Frag
------------------------------ ------------------------------ ---------- ---------- --------- ---------- ----------
SCOTT                          TEST_SHRINK                    2.94244385 .023890277 09-FEB-16          2 99.1880804


CHECK ROW MOVEMENT ENABLE FOR A TABLE TEST_SHRINK

Select owner,table_name, ROW_MOVEMENT, TABLESPACE_NAME from dba_tables where owner=’SCOTT’ and Table_name =’TEST_SHRINK’;


ALTER TABLE SCOTT.TEST_SHRINK Enable row Movement;


CHECK TABLESPACE USED FREE SPACE BEFORE DOING SHRINK

col "NAME" format a30
 set lines 140
 set pages 1000
 select a.tablespace_name "NAME",
 round((b.totalspace - a.freespace),1)"USED_SPACE_MB",
 round(a.freespace,1) "FREE_SPACE_MB",
 round(b.totalspace) "TOTAL_SPACE_MB",
 round(100 * (a.freespace / b.totalspace)) "%_FREE"
 from
  ( select tablespace_name,
 sum(bytes)/1024/1024 TotalSpace
   from dba_data_files
   group by tablespace_name) b,
 ( select tablespace_name,
    sum(bytes)/1024/1024 FreeSpace
   from dba_free_space
   group by tablespace_name) a
  where b.tablespace_name = a.tablespace_name(+)
  order by 1;


ALTER TABLE SCOTT.TEST_SHRINK SHRINK SPACE;

select num_rows, blocks, empty_blocks, last_analyzed from user_tables where table_name = 'TEST_SHRINK';
exec dbms_stats.gather_table_stats(ownname=>'SCOTT',tabname=>'TEST_SHRINK',estimate_percent=>100);


FIND OUT TABLE FULL SIZE ,ACTUAL SIZE  AND PERCENTAGE OF FRAGMENTATION

DISABLE ROW MOVEMENT

ALTER TABLE SCOTT.TEST_SHRINK DISABLE row Movement;

Usefull Scripts

*******************Dataguard Monitoring Scripts******************************
SELECT name,
               open_mode,
               database_role,
               protection_mode
FROM v$database;
--------------------------------------------------------------------------------------------------
On Primary
select to_char(Current_SCN) from v$database;


On Standby
select to_char(Current_SCN) from v$database;


Difference of both output should not big, if big check


SELECT * from v$ARCHIVE_GAP;




SELECT MESSAGE
FROM V$DATAGUARD_STATUS;
-------------------------------------------------------------------------
SELECT PROCESS,
                STATUS,
                THREAD#,
                SEQUENCE#,
                BLOCK#,
                BLOCKS
FROM V$MANAGED_STANDBY ;




------------------------------------------------------------------------
select registrar,
          creator,
          thread#,
          sequence#,
          first_change#,
          next_change#
from v$archived_log;
------------------------------------------------------------------------




select 'Last Log applied : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') "Applied_Time"
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log where applied='YES')
union
select 'Last Log received : ' Logs, to_char(next_time,'DD-MON-YY:HH24:MI:SS') "Received_Time"
from v$archived_log
where sequence# = (select max(sequence#) from v$archived_log);






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


SELECT to_char(max(FIRST_TIME),'hh24:mi:ss dd-mm-yyyy')
FROM v$archived_log
WHERE applied='YES';




------------------------------------------------------------------------------------------------------------
SELECT thread#,
                group#,
                sequence#,
                bytes,
                archived,
               status
FROM v$standby_log
ORDER BY thread#, group#;
-----------------------------------------------------------------------------------------------------------