Different RMAN Retention Policies for DISK and TAPE

Short note for myself on how to implement different RMAN Retention policies for DISK and TAPE backups in the same configuration.

The goal is to have:

  • Retention for DISK backups as 7 Days;
  • Retention for TAPE backups as 60 Days;

Implementation:

  • set policy for TAPE as RMAN CONFIGURATION
    • CONFIGURE RETENTION POLICY TO RECOVERY WINDOW OF 60 DAYS;
  • for deletion of OBSOLETE backups use next commands as a single block:
    • DELETE OBSOLETE DEVICE TYPE  TAPE;
    • DELETE OBSOLETE RECOVERY WINDOW OF 7 DAYS DEVICE TYPE DISK ;

Setup TEST Environment, even without Tape Device present:

  • mkdir -p /tmp/rman/disk
  • mkdir -p /tmp/rman/sbt
  • RMAN>
    • CONFIGURE DEFAULT DEVICE TYPE TO disk;
    • CONFIGURE DEVICE TYPE sbt PARALLELISM 4;
    • CONFIGURE CHANNEL DEVICE TYPE sbt PARMS=’SBT_LIBRARY=oracle.disksbt, ENV=(BACKUP_DIR=/tmp/rman/sbt)’;
    • CONFIGURE DEVICE TYPE disk PARALLELISM 4;
    • CONFIGURE CHANNEL DEVICE TYPE disk FORMAT=’/tmp/rman/disk/%U’;

Explicitly Closing Oracle Database Link

From time to time (actually, once in a several years ;-)) I need to explicitly close opened database link, so here I’m putting some notes for myself on this topic.

Additionally to implicitly closing database link by closing you connection we have next methods for explicitly closing specific database link:

  1. ALTER SESSION CLOSE DATABASE LINK my_db_link;
  2. EXECUTE DBMS_SESSION.CLOSE_DATABASE_LINK(‘my_db_link‘);

Continue reading

Using SQLPatch to inject HINTs when you can’t touch the code

Here I’m putting some example code on how to use SQLPatch for HINTing SQLs when it’s not possible to touch the application code or use other techniques to fix execution plan.

SQL Patches:

  • work since 11.2.0.3
  • are internally based on SQL Profiles;
  • may be used to add HINTs to SQLs when you can’t touch code(like with Packaged Application);
  • don’t require Diagnostics or Tuning Pack Licenses;
  • sql text is normalized before storing, so additional spaces and case mismatch is not a problem;
  • don’t handle SQL embedded literals, except through unpublished call to I_CREATE_SQL_PROFILE – see example later;
  • RESULT_CACHE hint is honored since 12.1.0.2 and 12.2 – Check BUG:16974854;
  • to check if SQL Profile was used add +NOTE when displaying execution plan
    • SELECT * FROM table(dbms_xplan.display_cursor(format=>’+NOTE‘));
  • to use HINTs with corrent syntax get them from desired execution plan:
    • SELECT * FROM table(dbms_xplan.display_cursor(format=>’+OUTLINE‘));

Continue reading

IMPDB with REMAP_SCHEMA may create(import) Trigger on table in Wrong Schema

Funny situation was discovered recently by one of our customers. Actually, it was an incident with Core Banking System, so, in reality, it wasn’t Funny….

I have checked and found that this issue reproduces not only with customer’s database using software version 11.2.0.4, but with version 12.1.0.2 with latest Oracle Database Proactive Bundle Patch(160719) installed.

To reproduce issue you need: Continue reading

Global Context + Session Client Identifier = Strange Behaviour or Documentation BUG ?

Here I’ll describe some strange/interesting/ behaviour of Global Application Context connected with usage of Session Client Identifier, that is quite usually met in Application Express, J2EE Agent, Oracle Data Integrator starting from release 11.1.1.7 and many other applications that uses JDBC Connection Pooling in conjunction with setting Session Client Identifier.

So what is the problem ?

The problem is that Global Application Context doesn’t work as expected and described in documentation – attributes’ VALUES ARE NOT VISIBLEContinue reading

Oracle Database Patchset 11.2.0.4 is ready for downloading

Oracle RDBMS PatchSet version 11.2.0.4 is available for downloading since 27-AUG-2013 ! again some time earlier than OOW2013

But(as of 29-AUG-2013) it’s still available only for Linux x86-64 + Linux x86 and all Oracle Solarises(x86, x86-64, SPARC 32-bit, SPARC 64-bit)

Checked on 29-OCT-2013 – patchset aditionally available for next platforms: Microsoft Windows(32-bit), Microsoft Windows x64(64-bit), HP-UX Itanium(32-bit), HP-UX Itanium(64-bit), IBM AIX on POWER Systems (32-bit), IBM AIX on POWER Systems (64-bit)

Based on  NOTE:742060.1 Release Schedule of Current Database Releases:

  • 11.2.0.4 on Linux x66(new platform ? ;)) released – this joke no more present in mentioned document 😉
  • this is the terminal patch set for 11.2 Continue reading