Rac Cache Fusion Flow
http://www.datadisk.co.uk/html_docs/rac/cache_fusion.htm
http://www.datadisk.co.uk/html_docs/rac/cache_fusion.htm
Wednesday 22 June 2016
How to Delete Old Obsolete and Expired Oracle RMAN Backup.
If you are sysadmin, sometimes you might find yourself dealing with Oracle backups.
Apart from taking oracle RMAN backup, you should also understand how to Delete the old backups from the RMAN catalog and from physical filesystem.
If you don’t properly delete obsolete and expired Oracle DB backup from the catalog, it will cause some unnecessary throw error message during backup and also it will take-up space at the filesystem level.
Also, it is not recommended to directly remove the RMAN backup files from the filesystem using Linux rm command.
This tutorial explains how to identify the backups that are obsolete and expired, and how to properly delete them from RMAN.
The main parameter that decides what to delete is the retention policy. To identify your retention policy, connect using RMAN and and execute “show all” and look for the following line.
The main parameter that decides what to delete is the retention policy. To identify your retention policy, connect using RMAN and and execute “show all” and look for the following line.
RMAN Retention Policy
In the following example, retention policy is 4 days. So, any backup that is older than 4 days is considered obsolete and old.
$ rman target / RMAN> SHOW ALL; CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 4 DAYS;
There is a big difference between Obsolte and Expired RMAN backup. We’ll explain both in this article.
I. Delete Obsolete Backup
1. What is an Obsolete Backup?
In our example above, any backup that we have in our system (both on RMAN catalog, and as physical RMAN backup files at the filesystem level) that is older than 4 days is considered obsolete. Please keep in mind that sometimes it might not be just 4 days. It depends on whether a full-backup is available within the last 4 days. If you don’t have a full backup in the last 4 days, then what RMAN considers as obsolete will be even longer than that. i.e Until the last full backup.
Obsolete backups are those that are not required to satisfy RMAN requirement of what is specified in the retention policy to recover the database from the backup.
The following three things will happen when you perform “DELETE OBSOLETE” from RMAN prompt:
- The physical backup files are removed from the filesystem level (or from tape backup)
- The backup entries are removed from the RMAN recovery catalog
- The entries are marked as DELETED in the Oracle control file
2. View Backups Before Delete Obsolete
In the following example, I see that there are lot of backup that I have on the system that are way older than what I need to satisfy my recovery requirement. i.e I have backups starting from 6th June until 26th Sep (several months).
RMAN> LIST BACKUP SUMMARY; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 624 B F A DISK 06-JUN-14 1 1 YES TAG20140606T220138 625 B F A DISK 06-JUN-14 1 1 YES TAG20140606T220138 626 B F A DISK 06-JUN-14 1 1 NO TAG20140606T221610 627 B F A DISK 13-JUN-14 1 1 YES TAG20140613T220147 .. .. 666 B F A DISK 05-SEP-14 1 1 YES TAG20140905T220151 667 B F A DISK 12-SEP-14 1 1 YES TAG20140912T220151 668 B F A DISK 19-SEP-14 1 1 YES TAG20140919T220152 669 B F A DISK 26-SEP-14 1 1 YES TAG20140926T220206
3. Perform RMAN CrossCheck
Before we start executing the delete obsolete command, it is always recommended to do a crosscheck of the backup as shown below.
crosscheck backup command will check for the records in the RMAN repository to make sure they are accurate. If there is an record in the RMAN catalog that is not available on the physical filesystem, it will make that entry with appropriate status.
RMAN> CROSSCHECK BACKUP; allocated channel: ORA_DISK_1 channel ORA_DISK_1: SID=37 device type=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: SID=22 device type=DISK crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/rman-backups/dev-db/full_okpa6ke3_788_1 RECID=642 STAMP=849564099 crosschecked backup piece: found to be 'AVAILABLE' .. .. backup piece handle=/rman-backups/dev-db/full_qvpiu46h_863_1 RECID=686 STAMP=858722513 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/rman-backups/dev-db/full_r4pjgir0_868_1 RECID=687 STAMP=859327328 Crosschecked 46 objects
4. Delete Obsolete RMAN backup
Once the crosscheck is done, it is time to delete the old obsolete backup using the DELETE OBSOLETE command as shown below.
RMAN> DELETE OBSOLETE; RMAN retention policy will be applied to the command RMAN retention policy is set to recovery window of 4 days using channel ORA_DISK_1 using channel ORA_DISK_2 Deleting the following obsolete backups and copies: Type Key Completion Time Filename/Handle -------------------- ------ ------------------ -------------------- Backup Set 625 06-JUN-14 Backup Piece 643 06-JUN-14 /rman-backups/dev-db/full_ojpa6ke3_787_1 Backup Set 624 06-JUN-14 Backup Piece 642 06-JUN-14 /rman-backups/dev-db/full_okpa6ke3_788_1 .. .. Backup Set 664 05-SEP-14 Backup Piece 682 05-SEP-14 /rman-backups/dev-db/full_qjphp6ug_851_1 Backup Set 667 12-SEP-14 Backup Piece 685 12-SEP-14 /rman-backups/dev-db/full_qqpiblig_858_1 Do you really want to delete the above objects (enter YES or NO)? YES
Once you say “YES” to the above confirmation, then it will start deleting those obsolete backups.
backup piece handle=/rman-backups/dev-db/full_ojpa6ke3_787_1 RECID=643 STAMP=849564099 deleted backup piece backup piece handle=/rman-backups/dev-db/full_okpa6ke3_788_1 RECID=642 STAMP=849564099 deleted backup piece .. .. backup piece handle=/rman-backups/dev-db/full_qjphp6ug_851_1 RECID=682 STAMP=857512912 deleted backup piece backup piece handle=/rman-backups/dev-db/full_qqpiblig_858_1 RECID=685 STAMP=858117712 Deleted 40 objects
WARNING: This will also physically delete the RMAN backup files from the filesystem. So, be careful and know exactly what you are doing before you execute this command.
5. Other Delete Obsolete Options
If you are writing a shell script that will automatically do this for you on an on-going basis, you don’t want to manually say “YES” to delete obsolete command as shown above.
Instead, you can ignore the prompt and automatically delete all obsolete backups as shown below.
RMAN> DELETE NOPROMPT OBSOLETE;
Also, if you want to delete obsolete backup based on your own recovery window criteria (instead of what is configured in RMAN when you do “show all”), you can specify it as shown below. The following will delete old backups based on recovery window of 10 days.
RMAN> DELETE OBSOLETE RECOVERY WINDOW OF 10 DAYS;
6. View Backups After Delete Obsolete
Finally, if you do the list backup summary, you’ll notice that this has only the backups that are required to satisfy the recovery criteria. All other backups are deleted.
RMAN> LIST BACKUP SUMMARY; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 661 B F A DISK 29-AUG-14 1 1 YES TAG20140829T220152 663 B F A DISK 29-AUG-14 1 1 NO TAG20140829T221624 665 B F A DISK 05-SEP-14 1 1 YES TAG20140905T220151 666 B F A DISK 05-SEP-14 1 1 YES TAG20140905T220151 668 B F A DISK 19-SEP-14 1 1 YES TAG20140919T220152 669 B F A DISK 26-SEP-14 1 1 YES TAG20140926T220206
II. Delete Expired Backup
1. What is an Expired Backup?
When you have an entry in the RMAN repository for a backup, but there are no corresponding physical rman backup files at the filesystem level, that is considered as expired entry.
But, you need to execute the crosscheck command, which will go through all the records in the RMAN catalog, and mark any expired records appropriately.
2. Perform RMAN CrossCheck
Sometimes when you try to execute the delete command, you might get the following “RMAN-06207” error message, when there is a mis-match of the status for the records in the RMAN repository.
RMAN-06207: WARNING: 20036 objects could not be deleted for DISK channel(s) due RMAN-06208: to mismatched status. Use CROSSCHECK command to fix status RMAN-06210: List of Mismatched objects RMAN-06211: ========================== RMAN-06212: Object Type Filename/Handle RMAN-06213: --------------- --------------------------------------------------- RMAN-06214: Backup Piece /rman-backup/sales-db/full_52ob26cd_149666_1 RMAN-06214: Backup Piece /rman-backup/sales-db/full_50ob26cd_149664_1
As you see from the following output, crosscheck backup finds few records that are expired, and marking them as EXPIRED status in the catalog.
RMAN> CROSSCHECK BACKUP; allocated channel: ORA_DISK_1 channel ORA_DISK_1: sid=453 devtype=DISK allocated channel: ORA_DISK_2 channel ORA_DISK_2: sid=435 devtype=DISK crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/rman-backup/sales-db/full_4cpqjjca_196748_1 recid=217396 stamp=866766218 crosschecked backup piece: found to be 'AVAILABLE' backup piece handle=/rman-backup/sales-db/full_4epqjjca_196750_1 recid=217397 stamp=866766218 .. .. crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/rman-backup/sales-db/ctl_c-863661937-20141013-19 recid=211590 stamp=860879752 crosschecked backup piece: found to be 'EXPIRED' backup piece handle=/rman-backup/sales-db/ctl_c-863661937-20141013-1a recid=211591 stamp=860879769 Crosschecked 1253 objects
3. View Backups Before Delete Expired
As you see from the output below, there are several records in the RMAN catalog that are marked as EXPIRED.
RMAN> LIST BACKUP SUMMARY; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 728642 B A X DISK 29-SEP-14 1 1 YES DBARCHLOGS_20140929 728643 B A X DISK 29-SEP-14 1 1 YES DBARCHLOGS_20140929 .. .. 819598 B A A DISK 29-DEC-14 1 1 YES DBARCHLOGS_20141229 819607 B F A DISK 29-DEC-14 1 1 NO TAG20141229T161328
Note: The 4th column which has the title of “S” is Status column. The value “X” indicates EXPIRED status. The value “A” indicates AVAILABLE status.
4. Delete Expired RMAN Catalog Entries
The following will delete all the records that are in RMAN catalog which are marked as EXPIRED. In other words, this deletes the expired records that don’t have the corresponding physical RMAN backup file in the filesystem.
RMAN> DELETE EXPIRED BACKUP; using channel ORA_DISK_1 using channel ORA_DISK_2 List of Backup Pieces BP Key BS Key Pc# Cp# Status Device Type Piece Name ------- ------- --- --- ----------- ----------- ---------- 728642 728712 1 1 EXPIRED DISK /rman-backup/sales-db/full_52ob26cd_149666_1 728643 728713 1 1 EXPIRED DISK /rman-backup/sales-db/full_50ob26cd_149664_1 .. .. Do you really want to delete the above objects (enter YES or NO)?
As shown above, before deleting it will ask for a confirmation. Say “YES” to the above, which will delete those files as shown below.
backup piece handle=/rman-backup/sales-db/full_52ob26cd_149666_1 RECID=591 STAMP=840751343 deleted backup piece backup piece handle=/rman-backup/sales-db/full_50ob26cd_149664_1 RECID=592 STAMP=840752380 deleted backup piece .. Deleted 107 EXPIRED objects
5. View Backups After Delete Expired
After deleting the expired entries, view the catalog to make sure it contains only the active available RMAN backup records.
RMAN> LIST BACKUP SUMMARY; List of Backups =============== Key TY LV S Device Type Completion Time #Pieces #Copies Compressed Tag ------- -- -- - ----------- --------------- ------- ------- ---------- --- 809652 B F A DISK 20-DEC-14 1 1 YES DBFULLFILE_20141220 809653 B F A DISK 20-DEC-14 1 1 YES DBFULLFILE_20141220 809654 B F A DISK 20-DEC-14 1 1 YES DBFULLFILE_20141220 809655 B F A DISK 20-DEC-14 1 1 YES DBFULLFILE_20141220 809656 B F A DISK 20-DEC-14 1 1 YES DBFULLFILE_20141220 .. .. 819598 B A A DISK 29-DEC-14 1 1 YES DBARCHLOGS_20141229 819607 B F A DISK 29-DEC-14 1 1 NO TAG20141229T161328
As you see from the Status column (4th column), we see only values “A”, which indicates that all the records in the RMAN catalog are in AVAILABLE status now.
Thank's to Mr Ramesh Natarajan
http://www.thegeekstuff.com/2015/01/delete-oracle-rman-backup/?utm_source=feedburner&utm_medium=email&utm_campaign=Feed%3A+TheGeekStuff+(The+Geek+Stuff)
Sunday 19 June 2016
Oracle DBA Activity.
Daily Activity
- Oracle Database instance is running or not
- Database Listener is running or not.
- Check any session blocking the other session
- Check the alert log for an error
- Check is there any dbms jobs running & check the status of the same
- Check the Top session using more Physical I/O
- Check the number of log switch per hour
- How_much_redo_generated_per_hour.sql
- Run the statpack report
- Detect lock objects
- Check the SQL query consuming lot of resources.
- Check the usage of SGA
- Display database sessions using rollback segments
- State of all the DB Block Buffer
Weekly Activity
- Check the objects fragmented
- Check the Chaining & Migrated Rows
- Check the size of tables & check weather it need to partition or not
- Check for Block corruption
- Check the tables without PK
- Check the tables having no Indexes
- Check the tables having more Indexes
- Check the tables having FK but there is no Index
- Check the objects having the more extents
- Check the frequently pin objects & place them in separate tablespace & in cache
- Check the objects reload in memory many time
- Check the free space at O/s Level
- Check the CPU, Memory usage at O/s level define the threshold for the same.
- Check the used & free Block at object level as well as on tablespaces.
- Check the objects reaching to it’s Max extents
- Check free Space in the tablespace
- Check invalid objects of the database
- Check open cursor not reaching to the max limit
- Check locks not reaching to the max lock
- Check free quota limited available of each user
- Check I/O of each data file
Monthly Activity
- Check the database size & compare it previous size to find the exact growth of the database
- Find Tablespace Status, segment management, initial & Max Extents and Extent Management
- Check location of data file also check auto extendable or not
- Check default tablespace & temporary tablespace of each user
- Check the Indexes which is not used yet
- Check the Extents of each object and compare if any object extent are overridden which is define at tablespace level
- Tablespace need coalescing
- Check the overall database statistics
- Trend Analysis of objects with tablespace, last analyzed, no. of Rows, Growth in days & growth in KB
Nightly Activity
- Analyzed the objects routinely.
- Check the Index need to Rebuild
- Check the tablespace for respective Tables & Indexes
One Time Activity
- Database user creation with required privileges
- Make the portal of Oracle Predefined error with possible solution.
- Check database startup time(if not 24X7)
- Check location of control file
- Check location of log file
- Prepare the Backup strategy and test all the recovery scenario
If we together build a comprehensive list of Oracle DBA daily/weekly/monthly routine tasks, then many Oracle new DBAs like me will benefit from it.
Shared Pool Information For Tuning
Shared Pool Information
Here are some scripts related to Shared Pool Information .
Quick Check
SHARED POOL QUICK CHECK NOTES:
select 'You may need to increase the SHARED_POOL_RESERVED_SIZE' Description,
'Request Failures = '||REQUEST_FAILURES Logic
from v$shared_pool_reserved
where REQUEST_FAILURES > 0
and 0 != (
select to_number(VALUE)
from v$parameter
where NAME = 'shared_pool_reserved_size')
union
select 'You may be able to decrease the SHARED_POOL_RESERVED_SIZE' Description,
'Request Failures = '||REQUEST_FAILURES Logic
from v$shared_pool_reserved
where REQUEST_FAILURES < 5
and 0 != (
select to_number(VALUE)
from v$parameter
where NAME = 'shared_pool_reserved_size')
Memory Usage
SHARED POOL MEMORY USAGE NOTES:
select OWNER,
NAME||' - '||TYPE object,
SHARABLE_MEM
from v$db_object_cache
where SHARABLE_MEM > 10000
and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
order by SHARABLE_MEM desc
Loads
LOADS INTO SHARED POOL NOTES:
select OWNER,
NAME||' - '||TYPE object,
LOADS
from v$db_object_cache
where LOADS > 3
and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
order by LOADS desc
Executions
SHARED POOL EXECUTION NOTES:
select OWNER,
NAME||' - '||TYPE object,
EXECUTIONS
from v$db_object_cache
where EXECUTIONS > 100
and type in ('PACKAGE','PACKAGE BODY','FUNCTION','PROCEDURE')
order by EXECUTIONS desc
Details
SHARED POOL DETAIL NOTES:
select OWNER,
NAME,
DB_LINK,
NAMESPACE,
TYPE,
SHARABLE_MEM,
LOADS,
EXECUTIONS,
LOCKS,
PINS
from v$db_object_cache
order by OWNER, NAME
Library Cache Statistics
SHARED POOL V$LIBRARYCACHE STATISTIC NOTES:
select NAMESPACE,
GETS,
GETHITS,
round(GETHITRATIO*100,2) gethit_ratio,
PINS,
PINHITS,
round(PINHITRATIO*100,2) pinhit_ratio,
RELOADS,
INVALIDATIONS
from v$librarycache
Reserve Pool Settings
SHARED POOL RESERVED SIZE NOTES:
select NAME,
VALUE
from v$parameter
where NAME like '%reser%'
Pinned Objects
PINNED OBJECT NOTES:
select NAME,
TYPE,
KEPT
from v$db_object_cache
where KEPT = 'YES'
Subscribe to:
Posts (Atom)