9 Ekim 2019 Çarşamba

RMAN => What is the difference between Full Backup & Level 0 Backup?


Hi,

While I was checking someone's RMAN backup strategy, I saw a very wrong configuration. They are taking Full Backup (Not Level 0) every sunday and taking Level 1 backup at the rest of the days.

I asked; why do you use Full Backup instead of Level 0?

They answered "Because if we use Level 0 backup, we see 'Incr Backup' at the Enterprise Manager Backup Reports Screen":)

They think that Level 0 and Full Backup are identical.

Let's test and see if they are really same or not:

--> Create RMAN backups with 2 different method while database is mounted:

Full Backup

RMAN> backup as compressed backupset database format '/u01/app/oracle/backup/full_backup_%U.bck';

Level 0 Backup

backup as compressed backupset incremental level 0 database format '/u01/app/oracle/backup/level0_%U.bck';

Check the size of backups:








As you see, the size of the backups are identical.

-->I will delete all backups and take a "Full Backup" one more time:

RMAN> delete noprompt force backup;

RMAN> backup as compressed backupset database format '/u01/app/oracle/backup/full_backup_%U.bck';


--> I will take a "Incremental Backup" and check the logs:






















The RMAN log says "there is no parent backup or copy of datafile". So it means that the "Full Backup" which is previously taken before incremental backup is useless for "Incremental Backup Strategy"

Also, check the size of the incremental backup;






Total size of incremental backups and full backup are almost same. Therefore, we can understand that our Level 1 incremental backup is actually a Level 0 full backup.

Conclusion

Do not use "Full Backups" for your Incremental Backup strategy. Your incremental backups can be used only if you have Level 0 parent backups.



4 Ekim 2019 Cuma

How to Identify Hot Objects for "dbms_shared_pool.markhot" after Oracle Database 12cR2?


Hi,

With 12cR2, there are some modifications for X$KGLOB table.

The columns KGLOBT23 was replaced with "KGLOBLCT" and  KGLOBT24 was replaced with "KGLOBPCT".

So, you can use the following query to identify hot objects then you can mark them with dbms_shared_pool package.

select *
  from (Select case
                 when (kglhdadr = kglhdpar) then
                  'Parent'
                 else
                  'Child ' || kglobt09
               end cursor,
               kglhdadr ADDRESS,
               substr(kglnaobj, 1, 20) name,
               kglnahsh hash_value,
               kglobtyd type,
               KGLOBLCT LOCKED_TOTAL,
               KGLOBPCT PINNED_TOTAL,
               kglhdexc EXECUTIONS,
               kglhdnsp NAMESPACE
          from x$kglob
         order by KGLOBPCT desc)
 where rownum <= 20