Tag: delete

ORA-16038 ORA-19504

Posted on by Osama Mustafa in Database
Archiver Hung in Oracle database 
Summary
1. check how much space is used by archiver
2. check the last good backup of the database
3. delete archive log files older than last good backup
4. crosscheck archive log
1. check how much space is used by archiver
  Sql>  select count(*),sum(blocks*block_size) from v$archived_log where    backup_count=0 and      deleted=’NO’;
   Sql> select * from v$flash_recovery_area_usage;
2. check the last good backup of the database

  set pages 999 lines 120

   col STATUS format a9
   col hrs format 999.99
   col start_time format a15
   col end_time format a15
   col dev format a5
   col inbytes format a10
   col outbytes format a10
   select
       INPUT_TYPE, STATUS,
       to_char(START_TIME,’mm/dd/yy hh24:mi’) start_time,
       to_char(END_TIME,’mm/dd/yy hh24:mi’)   end_time,
       elapsed_seconds/3600                   hrs,
       output_device_type dev,
       input_bytes_display inbytes,
       output_bytes_display outbytes
    from V$RMAN_BACKUP_JOB_DETAILS
   order by session_key;

   3. delete archive log files older than last good backup
rman target / nocatalog
 allocate channel for maintenance device type disk;
 crosscheck archivelog all;
 delete noprompt archivelog until time ‘sysdate – 1’;
 delete noprompt expired archivelog all;
 delete noprompt obsolete device type disk;   

4. crosscheck archive log
crosscheck archivelog all;
 release channel;
 exit;  
Thank you 
Osama mustafa

Working with RMAN Stored Scripts

Posted on by Osama Mustafa in Scripts

Creating Stored Scripts: CREATE SCRIPT

 

Make sure RMAN is connected to the right target database and the recovery catalog. Then run the CREATE SCRIPT command, as shown in this example: 

 
 
CREATE SCRIPT full_backup 
{     
  BACKUP DATABASE PLUS ARCHIVELOG;
  DELETE OBSOLETE;
} 
 
 

 you can also provide a COMMENT with descriptive information:

CREATE GLOBAL SCRIPT global_full_backup 
COMMENT 'use only with ARCHIVELOG mode databases'
{     
  BACKUP DATABASE PLUS ARCHIVELOG;
  DELETE OBSOLETE;
}

Running Stored Scripts: EXECUTE SCRIPT

 

RUN { EXECUTE SCRIPT full_backup; }

Displaying a Stored Script: PRINT SCRIPT

PRINT SCRIPT full_backup;
PRINT SCRIPT full_backup TO FILE 'my_backup.txt';

Listing Stored Scripts: LIST SCRIPT NAMES

LIST SCRIPT NAMES;

LIST ALL SCRIPT NAMES;

Updating Stored Scripts: REPLACE SCRIPT

REPLACE SCRIPT full_backup 
{
  BACKUP DATABASE PLUS ARCHIVELOG;
}

Deleting Stored Scripts: DELETE SCRIPT

  

DELETE SCRIPT 'full_backup';
DELETE GLOBAL SCRIPT 'global_full_backup';

 

 

 

 Thank you 

Osama mustafa

 

 

 

Useful Command in RMAN

Posted on by Osama Mustafa in Scripts

List Command :

1)RMAN> list backup;
List all your backup sets.
***************
2)RMAN>LIST BACKUPSET;
Lists only backup sets and proxy copies.
***************
3)RMAN>LIST COPY;
Lists of Image copies and Archive Logs.
***************
4)RMAN>LIST EXPIRED BACKUP;
Backups did not found after crosscheck. That is backup is manually moved or deleted from OS.
***************
5)RMAN>LIST BACKUP BY FILE;
List backup by Datafile, controlfile, spfile.
***************
6)RMAN>LIST BACKUP SUMMARY;
Lists backup sets, proxy copies, and disk copies.
***************
7)RMAN>LIST BACKUP OF DATABASE;
LIST BACKUP LIKE ‘/tmp/%’; list backup of datafile 1; LIST ARCHIVELOG ALL BACKED UP 2 TIMES TO DEVICE TYPE sbt;LIST COPY OF DATAFILE 2 COMPLETED BETWEEN ’17-MAR-2008′ AND ’22-MAR-2008′; are also available.
***************
8) RMAN>LIST INCARNATION;
LIST INCARNATION OF DATABASE; to see the incarnations of your database.

Report Command:

RMAN REPORT command analyzes the available backups and return results about while files need backup which files are obsolete etc.

1)REPORT NEED BACKUP;
Determine which database files need backup under a specific retention policy.
***************
2)REPORT UNRECOVERABLE;
Report which database files require backup because they have been affected by some NOLOGGING operation.
***************
3)REPORT SCHEMA;
Lists and displays information about the database files.
***************
4)REPORT OBSOLETE;
REPORT OBSOLETE displays the backups that are obsolete according to the current retention policy.
***************
5)REPORT NEED BACKUP RECOVERY WINDOW OF 2 DAYS DATABASE SKIP TABLESPACE data01;
If you use Recovery Catalog then past data can be shown.Like, REPORT SCHEMA AT TIME ‘SYSDATE-10’;
***************

Delete Command:

Delete commands is used to delete any backup or backupsets.

1)RMAN> delete backupset all;
Delete all your backup sets
***************
2) RMAN> delete copy all;
Delete all image copies.
***************
3) RMAN>DELETE OBSOLETE;
Delete obsolete Backups.
***************

thank you all .