Skip navigation
1 2 Previous Next

Dear DBA

22 posts

I love SQLcl but one must admit that it’s not widely available yet at customers’. So getting the hang of good ol’ SQL*Plus is still a must-have skill for Oracle DBAs. Here’s an interesting challenge, and an easy solution.

 

I’m selecting the lines that were input in the last 24 hours :

select * from nit.spri where moment > sysdate-1/24 order by moment ;

MOMENT NUM NOTES

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

24-07 13:50 100 insertion auto via KSH

24-07 13:50 101 insertion auto via KSH

24-07 13:50 102 insertion auto via KSH

24-07 13:50 103 insertion auto via KSH

24-07 13:50 104 insertion auto via KSH

24-07 13:50 105 insertion auto via KSH

24-07 13:51 106 insertion auto via KSH

24-07 13:51 107 insertion auto via KSH

24-07 13:51 108 insertion auto via KSH

24-07 13:51 100 insertion auto via KSH

24-07 14:00 200 insertion auto via KSH

24-07 14:00 201 insertion auto via KSH

24-07 14:00 202 insertion auto via KSH

24-07 14:00 203 insertion auto via KSH

24-07 14:00 204 insertion auto via KSH

24-07 14:00 205 insertion auto via KSH

24-07 14:00 206 insertion auto via KSH

24-07 14:06 200 insertion auto via KSH

24-07 14:06 201 insertion auto via KSH

24-07 14:06 202 insertion auto via KSH

24-07 14:06 203 insertion auto via KSH

24-07 14:06 204 insertion auto via KSH

24-07 14:06 205 insertion auto via KSH

24-07 14:06 206 insertion auto via KSH

24-07 14:06 207 insertion auto via KSH

24-07 14:07 208 insertion auto via KSH

24-07 14:07 209 insertion auto via KSH

24-07 14:10 400 insertion auto via KSH

24-07 14:10 401 insertion auto via KSH

24-07 14:10 402 insertion auto via KSH

24-07 14:10 403 insertion auto via KSH

24-07 14:10 404 insertion auto via KSH

24-07 14:10 405 insertion auto via KSH

24-07 14:10 406 insertion auto via KSH

24-07 14:10 407 insertion auto via KSH

24-07 14:10 408 insertion auto via KSH

24-07 14:13 400 insertion auto via KSH

24-07 14:13 401 insertion auto via KSH

24-07 14:13 402 insertion auto via KSH

24-07 14:13 403 insertion auto via KSH

24-07 14:13 404 insertion auto via KSH

24-07 14:13 405 insertion auto via KSH

24-07 14:13 406 insertion auto via KSH

24-07 14:14 407 insertion auto via KSH

24-07 14:14 408 insertion auto via KSH

24-07 14:14 409 insertion auto via KSH

24-07 14:15 500 insertion auto via KSH

24-07 14:15 501 insertion auto via KSH

24-07 14:15 502 insertion auto via KSH

24-07 14:15 503 insertion auto via KSH

24-07 14:15 504 insertion auto via KSH

24-07 14:15 505 insertion auto via KSH

24-07 14:15 506 insertion auto via KSH

24-07 14:15 507 insertion auto via KSH

24-07 14:17 600 insertion auto via KSH

24-07 14:22 601 insertion auto via KSH


 

56 rows selected.


56 rows is too many for my need, so I decide to select the lines that were input in the last 20 minutes. This means that I must change

where moment > sysdate-1/24

to

where moment > sysdate-20/1440


As you probably know, in SQL*Plus, a character string can be changed into another with the change keyword (c for short):

 

select log_mode,open_mode,status from v$database

*

ERROR at line 1:

ORA-00904: "STATUS": invalid identifier

 

SQL> c/status/db_unique_name

1* select log_mode,open_mode,db_unique_name from v$database

SQL> /

LOG_MODE OPEN_MODE DB_UNIQUE_NAME

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

NOARCHIVELOG READ WRITE orcl


 

As you can see in the example above, the typical field separator is a slash. So how am I going to turn  sysdate-1/24 into sysdate-20/1440 ?

Fortunately, SQL*Plus allows for any field separator you like. Let’s choose an exclamation point:

 

>-c!1/24!20/1440

  1* select * from nit.spri where moment > sysdate-20/1440 order by moment

  >-/

MOMENT NUM NOTES

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

24-07 14:06 200 insertion auto via KSH

24-07 14:06 201 insertion auto via KSH

24-07 14:06 202 insertion auto via KSH

24-07 14:06 203 insertion auto via KSH

24-07 14:06 204 insertion auto via KSH

24-07 14:06 205 insertion auto via KSH

24-07 14:06 206 insertion auto via KSH

24-07 14:06 207 insertion auto via KSH

24-07 14:07 208 insertion auto via KSH

24-07 14:07 209 insertion auto via KSH

24-07 14:10 400 insertion auto via KSH

24-07 14:10 401 insertion auto via KSH

24-07 14:10 402 insertion auto via KSH

24-07 14:10 403 insertion auto via KSH

24-07 14:10 404 insertion auto via KSH

24-07 14:10 405 insertion auto via KSH

24-07 14:10 406 insertion auto via KSH

24-07 14:10 407 insertion auto via KSH

24-07 14:10 408 insertion auto via KSH

24-07 14:13 400 insertion auto via KSH

24-07 14:13 401 insertion auto via KSH

24-07 14:13 402 insertion auto via KSH

24-07 14:13 403 insertion auto via KSH

24-07 14:13 404 insertion auto via KSH

24-07 14:13 405 insertion auto via KSH

24-07 14:13 406 insertion auto via KSH

24-07 14:14 407 insertion auto via KSH

24-07 14:14 408 insertion auto via KSH

24-07 14:14 409 insertion auto via KSH

24-07 14:15 500 insertion auto via KSH

24-07 14:15 501 insertion auto via KSH

24-07 14:15 502 insertion auto via KSH

24-07 14:15 503 insertion auto via KSH

24-07 14:15 504 insertion auto via KSH

24-07 14:15 505 insertion auto via KSH

24-07 14:15 506 insertion auto via KSH

24-07 14:15 507 insertion auto via KSH

24-07 14:17 600 insertion auto via KSH

24-07 14:22 601 insertion auto via KSH


 

39 rows selected.


 

Now I’ve got 39 rows instead of 56 and I’m happy: mostly because I know how to change a character string with a slash in it!


 

NOTE: this does not work in SQLcl:

 

05-09 08:23 dsvm1644 SYSTEM > c/status/sql_idSP2-0023: String not found
17095doesnotworkinSQLcl.jpeg

 


 

Ljubljana, Slovenia, May 23rd: I gave 2 presentations to about 30 members of SIOUG:

 

20170523_162554.jpgWP_20170523_10_58_53_Pro.jpg

 

Oddity: I got to stay in a hangar with carboard/wooden boxes as offices/bedrooms inside:20170523_080646.jpg I recommend it!

 

 

Madrid, Spain, May 24: Speakers' Dinner: 20170524_220125.jpg

May 25: Technical SPOUG Day, in Oracle's Madrid offices: 20170525_154140.jpg

I did my "Performance Tuning with SQLDeveloper" live demo in front of about 40 people: MadridMay25DearDBA.jpg

 

May 30-31st, Dusseldorf: I gave my "DBAs, Secure Your Listener" talk to about 50 people at DOAG Databank : 20170530_130252.jpg20170530_131557.jpg

 

Glasgow, United Kingdom, June 21st: during UKOUG's Scotland 2017 event,ougscot17.gif

I did my "Performance Tuning with SQLDeveloper live demo" in front of about 15 people. 20170621_135744.jpg

Back in January, I answered the Call for Papers of the 2017 OTN EMEA Tour . To my surprise, I am going to speak at the following three locations of the  2017 OTN EMEA Tour:otnemea_2017-255x300.pnghttp://otnemea.com/

 

Llubjana, Slovenia (May 23rd): I look like a stranger on the agenda, hobnobbing with Oracle ACE directors

OTN EMEA TOUR 2017, 23 May Ljubljana, Slovenia with @brost @fdernoncourt @gurcan_orhan @joc1954 https://t.co/0YVSuTR0N9

— Jure Bratina (@JureBratina) May 4, 2017

<

Madrid, Spain (May 25): I guess I must be a Rock Star speaker -- see pictures .

Madrid25May.jpegMadrid25MayRockStar.jpeg

 

Dusseldorf, Germany: I'm going back to DOAG's Databanken Konferenz.  It's a 2-day event (May 30-31). Here's  the agenda

datenbank_2017-banner_speaker.jpg

 

 

 

 

 

 

 

 

 

 

 

And last March, UKOUG contacted me asking me if I could speak at SCOTLAND 2017 (June 21st).ougscot17-com-600x110-gen-v5.gif

I logged 2 abstracts and in late April was told that one of them had been selected.  I am gald I'm going as it will be my first time speaking at a UKOUG event and I'm going to learn cool stuff from great speakers.  See the agenda.

If you must move the OS file of an RMAN backup, moving it at the OS level is perfectly fine and will not in any way alter your ability to restore it, but RMAN must know where to find it.  So how do you tell RMAN about that file's new location?  Enter CATALOG BACKUPPIECE.  This command simply registers into the RMAN catalog (that is, the database's CONTROL FILES and possibly an RMAN Catalog database)

 

And there is no risk to register a backuppiece from another database, as RMAN warns you that this backuppiece does not belong to this one and gives you the SID of the database it comes from:

 

RMAN> CATALOG BACKUPPIECE 'V:\backup\fra\0TS0SMQB_1_1' ;

ORA-19870: erreur lors de la restauration de l'ÚlÚment de sauvegarde V:\BACKUP\FRA\0TS0SMQB_1_1

ORA-19691: V:\BACKUP\FRA\0TS0SMQB_1_1 provient d'une autre base de donnÚes : id=3682310869, nom=OTHERDB

 

Just in passing, you can also catalog an RMAN datafile copy: RMAN> CATALOG DATAFILECOPY 'D:\oradata\users01.dbf'; and ARCHIVE LOGs: RMAN> CATALOG ARCHIVELOG 'D:\archives\MYDB\ARC0000027304_0837506108.arc', 'D:\archives\MYDB\ARC0000027308_0837506108.arc';

 

But what if you have many backuppieces to register with RMAN?   Enter CATALOG RECOVERY AREA and CATALOG START WITH.

 

First, if you want to move many RMAN files into your Flash Recovery Area (FRA), do it at the OS level:

then run CATALOG RECOVERY AREA

 

RMAN will list the files it found the ask for your manual confirmation to catalog all of them:

The result can be of 3 kinds:

1. it will list the files that rightly belong to the current database and have been cataloged:

Voulez-vous vraiment cataloguer les fichiers ci-dessus (YES ou NO) ? yes

catalogage des fichiers...

catalogage terminÚ

 

Liste des fichiers cataloguÚs

=======================

Nom de fichier : V:\BACKUP\FRA\MYDB\B8S1KIFP_1_1

Nom de fichier : V:\BACKUP\FRA\MYDB\C-3960881722-20170405-00

Nom de fichier : V:\BACKUP\FRA\MYDB\C-3960881722-20170405-01

Nom de fichier : V:\BACKUP\FRA\WSVPHX\C-3960881722-20170406-00

 

2. It will list the files that are RMAN files but belong to other databases:

Liste des fichiers qui n'Útaient pas cataloguÚs

=======================================

Nom de fichier : V:\BACKUP\FRA\MYDB\0US0SMQI_1_1

  RMAN-07518: Cause : fichier de base de donnÚes ÚtrangÞre, DBID : 3682310869, nom de base de donnÚes : OTHERDB1

Nom de fichier : V:\BACKUP\FRA\MYDB\11S0TJ5K_1_1

  RMAN-07518: Cause : fichier de base de donnÚes ÚtrangÞre, DBID : 3682310869, nom de base de donnÚes : OTHERDB2

Nom de fichier : V:\BACKUP\FRA\MYDB\17S0TTN4_1_1

  RMAN-07518: Cause : fichier de base de donnÚes ÚtrangÞre, DBID : 3682310869, nom de base de donnÚes : OTHERDB1

 

3. it will list the files that cannot be cataloged:

La liste des fichiers dans la zone de rÚcupÚration n'est pas gÚrÚe par la base de donnÚes

==========================================================

Nom de fichier : V:\BACKUP\FRA\MYDB\B6S1KI7L_1_1

  RMAN-07526: Cause : ce fichier n'est pas un fichier Oracle Managed Files

Nom de fichier : V:\BACKUP\FRA\MYDB\F4S0SQRN_1_1

  RMAN-07526: Cause : ce fichier n'est pas un fichier Oracle Managed Files

le nombre de fichiers non gÚrÚs par la zone de rÚcupÚration est 2, totalisant 1.95GB

 

such as files that simply are not Oracle files:

Nom de fichier : V:\BACKUP\FRA\MYDB\JohnDoe.txt

  RMAN-07517: Cause : l'en-tÛte de fichier est endommagÚ

 

You can also use the same command with NOPROMPT:

and you will not be prompted to confirm you want the found files to be cataloged.

 

That was for CATALOG RECOVERY AREA, which is synonymous with CATALOG DB_RECOVERY_FILE_DEST (both commands behave exactly the same, with or without NOPROMPT).  But if you are moving your RMAN file to locations that are not your FRA, use CATALOG START WITH.

 

CATALOG START WITH will catalog all the files located in the specified directory and its subdirectories.  It will work as CATALOG RECOVERY AREA: first list the found files, then ask for your manual confirmation to catalog all these files, then give the same 3 kinds of results: cataloged, other DB, not Oracle.

On Windows with Oracle 11gR1, I have tested that these all behave the same:

catalog start with 'V:\backup\fra' ;      catalog start with 'V:\backup\fra\\' ;      catalog start with 'V:\backup\fra/' ;     catalog start with 'V:\backup\fra\' ;      catalog start with "V:\backup\fra/" ;      catalog start with "V:\backup\fra\" ;      catalog start with "V:\backup\fra" ;

 

The result is something like:

Voulez-vous vraiment cataloguer les fichiers ci-dessus (YES ou NO) ? yes

catalogage des fichiers...

catalogage terminÚ

 

Liste des fichiers cataloguÚs

=======================

Nom de fichier : V:\BACKUP\fra\F6S0SR4E_1_1

Nom de fichier : V:\BACKUP\fra\ICS0VFGC_1_1

Nom de fichier : V:\BACKUP\fra\MYDB\AUTOBACKUP\2017_04_18\O1_MF_S_941592559_DHBMYJ73_.BKP

Nom de fichier : V:\BACKUP\fra\MYDB\AUTOBACKUP\2017_04_18\O1_MF_S_941592586_DHBMZBW7_.BKP

...

Liste des fichiers qui n'Útaient pas cataloguÚs

=======================================

Nom de fichier : V:\BACKUP\fra\0TS0SMQB_1_1

  RMAN-07518: Cause : fichier de base de donnÚes ÚtrangÞre, DBID : 3682310869, nom de base de donnÚes : OTHERDB2

Nom de fichier : V:\BACKUP\fra\13S0TMM3_1_1

  RMAN-07518: Cause : fichier de base de donnÚes ÚtrangÞre, DBID : 3682310869, nom de base de donnÚes : OTHERDB1

...

Nom de fichier : V:\BACKUP\fra\WSVPHX\JohnDoe.txt

  RMAN-07517: Cause : l'en-tÛte de fichier est endommagÚ

 

Now, something important. With CATALOG BACKUPPIECE/ARCHIVELOG/CONTROLFILECOPY/DATAFILECOPY, CATALOG RECOVERY AREA and CATALOG START WITH, we have told RMAN that there are RMAN files in those new locations (typically, after moving those files there ourselves).  But RMAN has NOT been told that those files are NO LONGER in their ORIGINAL locations.  Indeed, RMAN will think there are 2 copies of each of those files we have moved then cataloged. Demontration with one RMAN backuppiece file.

 

With LIST BACKUP, I discover that a backuppiece is located where it should not be:

 

BS Key  Type LV Size       Device Type Elapsed Time Completion Time 

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

19635   Full    1016.32M   DISK        00:04:15     17-04-14 01:29:24

        BP Key: 19635   Status: AVAILABLE  Compressed: YES  Tag: SVG COMPLETE

        Piece Name: C:\ORACLE\ORA112\DATABASE\B6S1KI7L_1_1

  Liste des fichiers de données dans l'ensemble de sauvegarde 19635

  File LV Type Ckp SCN    Ckp Time          Name

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

  1       Full 622954959  17-04-14 01:25:10 C:\ORACLE\ORADATA\MYDB\SYSTEM01.DBF

  2       Full 622954959  17-04-14 01:25:10 C:\ORACLE\ORADATA\MYDB\SYSAUX01.DBF

 

So I want to move that B6S1KI7L_1_1 file to my FRA and notify RMAN of that change:

First move the file at the OS level, then, in RMAN:

RMAN> CATALOG START WITh 'V:\BACKUP\FRA' ;

recherche de tous les fichiers correspondant au modèle V:\backup\fra\

Liste des fichiers inconnus de la base de données

=====================================

Nom de fichier : V:\BACKUP\FRA\B6S1KI7L_1_1

Voulez-vous vraiment cataloguer les fichiers ci-dessus (YES ou NO) ? catalogage des fichiers...

catalogage terminé

Liste des fichiers catalogués

=======================

Nom de fichier : V:\BACKUP\FRA\B6S1KI7L_1_1

 

Now let's look at the details of that backuppiece:

RMAN> LIST BACKUPPIECE 19635 ;

Liste des ensembles de sauvegarde

===================

BS Key  Type LV Size

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

19635  Full    1016.32M

  Liste des fichiers de données dans l'ensemble de sauvegarde 19635

  File LV Type Ckp SCN    Ckp Time          Name

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

1       Full 622954959  17-04-14 01:25:10 C:\ORACLE\ORADATA\MYDB\SYSTEM01.DBF

  2       Full 622954959  17-04-14 01:25:10 C:\ORACLE\ORADATA\MYDB\SYSAUX01.DBF

  3       Full 622954959  17-04-14 01:25:10 C:\ORACLE\ORADATA\MYDB\UNDOTBS01.DBF

  4       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\USERS01.DBF

  5       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\DATA01.DBF

  6       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\DATA02.DBF

  7       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\DATA03.DBF

  8       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\INDX01.DBF

  9       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\INDX02.DBF

  10      Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\INDX03.DBF

  11      Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\LOB_S_01.DBF

  12      Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\LOB_B_01.DBF

  13      Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\TOOLS_01.DBF

  Backup Set Copy #1 of backup set 19635

  Device Type Elapsed Time Completion Time   Compressed Tag

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

  DISK        00:04:15     17-04-14 01:29:24 YES        SVG COMPLETE

    Liste des éléments de sauvegarde pour l'ensemble de sauvegarde 19635, Copie #1

    BP Key  Pc# Status      Piece Name

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

    19635   1   AVAILABLE  C:\ORACLE\ORA112\DATABASE\B6S1KI7L_1_1

 

  Backup Set Copy #2 of backup set 19635

  Device Type Elapsed Time Completion Time   Compressed Tag

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

  DISK        00:04:15     17-04-20 20:10:23 YES        SVG COMPLETE

    Liste des éléments de sauvegarde pour l'ensemble de sauvegarde 19635, Copie #2

    BP Key  Pc# Status      Piece Name

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

    19773   1   AVAILABLE  V:\BACKUP\FRA\B6S1KI7L_1_1

 

AARGH! RMAN thinks there are 2 copies of backupiece 19635 (one at the old location, one at the new location), and both are listed as available (when only the one in the new location is available).  So let's tell RMAN to harmonize what is at at the OS level and in its catalog. To do so we can use CROSSCHECK BACKUP or CROSSCHECK BACKUPPIECE (other options are CROSSCHECK COPY, CROSSCHECK BACKUPSET, CROSSCHECK CONTROLFILECOPY, CROSSCHECK DATAFILECOPY, CROSSCHECK ARCHIVELOG.  You can apply filters such as CROSSCHECK BACKUP OF DATABASE COMPLETED AFTER 'SYSDATE-4/24'; ). CROSSCHECK will go throught the files that match the given criteria and check whether they are found at the OS level where RMAN thinks they are. Those that are found at the location known by RMAN will be marked as available, those that are not found there will be marked as EXPIRED.

 

RMAN>  CROSSCHECK BACKUP ;

canal affecté : ORA_DISK_1

canal ORA_DISK_1 : SID=245 type d'unité=DISK

élément de sauvegarde vérifié : repéré comme étant 'EXPIRED'

descripteur d'élément de sauvegarde=C:\ORACLE\ORA112\DATABASE\B6S1KI7L_1_1 RECID=19635 STAMP=941246710

élément de sauvegarde vérifié : repéré comme étant 'AVAILABLE'

descripteur d'élément de sauvegarde=V:\BACKUP\FRA\B6S1KI7L_1_1 RECID=19773 STAMP=941812809

élément de sauvegarde vérifié : repéré comme étant 'AVAILABLE'

descripteur d'élément de sauvegarde=C:\ORACLE\ORA112\DATABASE\C-3960881722-20170414-00 RECID=19636 STAMP=941246966

élément de sauvegarde vérifié : repéré comme étant 'AVAILABLE'

descripteur d'élément de sauvegarde=C:\ORACLE\ORA112\DATABASE\B8S1KIFP_1_1 RECID=19637 STAMP=941246969

élément de sauvegarde vérifié : repéré comme étant 'AVAILABLE'

descripteur d'élément de sauvegarde=C:\ORACLE\ORA112\DATABASE\C-3960881722-20170414-01 RECID=19638 STAMP=941246981

...

 

I could also have just run CROSSCHECK BACKUPPIECE 19635   ;

 

So now, RMAN knows that one of my 2 copies is EXPIRED:

 

RMAN> LIST BACKUPPIECE 19635 ;

Liste des ensembles de sauvegarde

===================

BS Key  Type LV Size

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

19635   Full    1016.32M

  Liste des fichiers de données dans l'ensemble de sauvegarde 19635

  File LV Type Ckp SCN    Ckp Time          Name

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

  1       Full 622954959  17-04-14 01:25:10 C:\ORACLE\ORADATA\MYDB\SYSTEM01.DBF

  2       Full 622954959  17-04-14 01:25:10 C:\ORACLE\ORADATA\MYDB\SYSAUX01.DBF

  3       Full 622954959  17-04-14 01:25:10 C:\ORACLE\ORADATA\MYDB\UNDOTBS01.DBF

  4       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\USERS01.DBF

  5       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\DATA01.DBF

  6       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\DATA02.DBF

  7       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\DATA03.DBF

  8       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\INDX01.DBF

  9       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\INDX02.DBF

  10      Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\INDX03.DBF

  11      Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\LOB_S_01.DBF

  12      Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\LOB_B_01.DBF

  13      Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\TOOLS_01.DBF

  Backup Set Copy #1 of backup set 19635

  Device Type Elapsed Time Completion Time   Compressed Tag

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

  DISK        00:04:15     17-04-14 01:29:24 YES        SVG COMPLETE

    Liste des éléments de sauvegarde pour l'ensemble de sauvegarde 19635, Copie #1

    BP Key  Pc# Status      Piece Name

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

    19635   1   EXPIRED    C:\ORACLE\ORA112\B6S1KI7L_1_1

  Backup Set Copy #2 of backup set 19635

  Device Type Elapsed Time Completion Time   Compressed Tag

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

  DISK        00:04:15     17-04-20 20:10:23 YES        SVG COMPLETE

    Liste des éléments de sauvegarde pour l'ensemble de sauvegarde 19635, Copie #2

    BP Key  Pc# Status      Piece Name

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

    19773   1   AVAILABLE   V:\BACKUP\FRA\B6S1KI7L_1_1

 

So next time I run DELETE EXPIRED, that EXPIRED copy will no longer appear in RMAN

 

RMAN> DELETE EXPIRED BACKUP ;

utilisation du canal ORA_DISK_1

Liste des ÚlÚments de sauvegarde

BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name

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

19635   19635   1   1   EXPIRED     DISK        C:\ORACLE\ORA112\DATABASE\B6S1KI7L_1_1

Voulez-vous vraiment supprimer les objets ci-dessus (YES ou NO) ? yes

ÚlÚment de sauvegarde supprimÚ

descripteur d'ÚlÚment de sauvegarde=C:\ORACLE\ORA112\DATABASE\B6S1KI7L_1_1 RECID=19635 STAMP=941246710

1 objets EXPIRED supprimÚs

 

So now let's list BACKUPSET 19635:

 

RMAN> LIST BACKUPset 19635 ;

Liste des ensembles de sauvegarde

===================

BS Key  Type LV Size

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

19635   Full    1016.32M

  Liste des fichiers de donnÚes dans l'ensemble de sauvegarde 19635

  File LV Type Ckp SCN    Ckp Time          Name

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

1       Full 622954959  17-04-14 01:25:10 C:\ORACLE\ORADATA\MYDB\SYSTEM01.DBF

  2       Full 622954959  17-04-14 01:25:10 C:\ORACLE\ORADATA\MYDB\SYSAUX01.DBF

  3       Full 622954959  17-04-14 01:25:10 C:\ORACLE\ORADATA\MYDB\UNDOTBS01.DBF

  4       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\USERS01.DBF

  5       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\DATA01.DBF

  6       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\DATA02.DBF

  7       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\DATA03.DBF

  8       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\INDX01.DBF

  9       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\INDX02.DBF

  10      Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\INDX03.DBF

  11      Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\LOB_S_01.DBF

  12      Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\LOB_B_01.DBF

  13      Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\TOOLS_01.DBF

  Backup Set Copy #3 of backup set 19635

  Device Type Elapsed Time Completion Time   Compressed Tag

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

  DISK        00:04:15     17-04-20 22:28:00 YES        SVG COMPLETE

    Liste des ÚlÚments de sauvegarde pour l'ensemble de sauvegarde 19635, Copie #3

    BP Key  Pc# Status      Piece Name

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

    19784   1   AVAILABLE   V:\BACKUP\FRA\B6S1KI7L_1_1

  Backup Set Copy #4 of backup set 19635

  Device Type Elapsed Time Completion Time   Compressed Tag

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

  DISK        00:04:15     17-04-20 22:52:54 YES        SVG COMPLETE

    Liste des ÚlÚments de sauvegarde pour l'ensemble de sauvegarde 19635, Copie #4

    BP Key  Pc# Status      Piece Name

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

    19821   1   AVAILABLE   V:\BACKUP\FRA\B6S1KI7L_1_1

  Backup Set Copy #2 of backup set 19635

  Device Type Elapsed Time Completion Time   Compressed Tag

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

  DISK        00:04:15     17-04-20 20:10:23 YES        SVG COMPLETE

    Liste des ÚlÚments de sauvegarde pour l'ensemble de sauvegarde 19635, Copie #2

    BP Key  Pc# Status      Piece Name

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

    19773   1   AVAILABLE   V:\BACKUP\FRA\B6S1KI7L_1_1

 

BackupPIECE 19635 is no longer listed.  However, I can see 3 copies of backupSET 19635, with different backupPIECE keys. These 3 copies are the results of my various CATALOG commands (CATALOG BACKUPPIECE, CATALOG START WITH etc.).  Each time a CATALOG command found an RMAN file belong to the current database, it registed a new copy of it. I can easily delete those extra copies with the DELETE BACKUPPIECE command:

RMAN> DELETE BACKUPPIECE 19773 ;

utilisation du canal ORA_DISK_1

Liste des ÚlÚments de sauvegarde

BP Key  BS Key  Pc# Cp# Status      Device Type Piece Name

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

19773   19635   1   2   AVAILABLE   DISK        V:\BACKUP\FRA\B6S1KI7L_1_1

Voulez-vous vraiment supprimer les objets ci-dessus (YES ou NO) ? yes

ÚlÚment de sauvegarde supprimÚ

descripteur d'ÚlÚment de sauvegarde=V:\BACKUP\FRA\B6S1KI7L_1_1 RECID=19773 STAMP=941812809

1 objets supprimÚs

 

And now, I have 2 copies left only:

RMAN> LIST BACKUPset 19635 ;

Liste des ensembles de sauvegarde

===================

BS Key  Type LV Size

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

19635   Full    1016.32M

  Liste des fichiers de donnÚes dans l'ensemble de sauvegarde 19635

  File LV Type Ckp SCN    Ckp Time          Name

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

1       Full 622954959  17-04-14 01:25:10 C:\ORACLE\ORADATA\MYDB\SYSTEM01.DBF

  2       Full 622954959  17-04-14 01:25:10 C:\ORACLE\ORADATA\MYDB\SYSAUX01.DBF

  3       Full 622954959  17-04-14 01:25:10 C:\ORACLE\ORADATA\MYDB\UNDOTBS01.DBF

  4       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\USERS01.DBF

  5       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\DATA01.DBF

  6       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\DATA02.DBF

  7       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\DATA03.DBF

  8       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\INDX01.DBF

  9       Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\INDX02.DBF

  10      Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\INDX03.DBF

  11      Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\LOB_S_01.DBF

  12      Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\LOB_B_01.DBF

  13      Full 622954959  17-04-14 01:25:10 D:\ORACLE\ORA112\ORADATA\MYDB\TOOLS_01.DBF

  Backup Set Copy #4 of backup set 19635

  Device Type Elapsed Time Completion Time   Compressed Tag

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

  DISK        00:04:15     17-04-20 22:52:54 YES        SVG COMPLETE

    Liste des ÚlÚments de sauvegarde pour l'ensemble de sauvegarde 19635, Copie #4

    BP Key  Pc# Status      Piece Name

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

    19821   1   AVAILABLE   V:\BACKUP\FRA\WSVPHX\B6S1KI7L_1_1

  Backup Set Copy #3 of backup set 19635

  Device Type Elapsed Time Completion Time   Compressed Tag

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

  DISK        00:04:15     17-04-20 22:28:00 YES        SVG COMPLETE

    Liste des ÚlÚments de sauvegarde pour l'ensemble de sauvegarde 19635, Copie #3

    BP Key  Pc# Status      Piece Name

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

    19784   1   AVAILABLE   V:\BACKUP\FRA\WSVPHX\B6S1KI7L_1_1

 

To sum up:

  1. first move your RMAN files at the OS level. Don't be afraid to move files that do not pertain to your DB: RMAN will detect them.
  2. Run CATALOG START WITH 'newlocation' or CATALOG DB_RECOVERY_FILE_DEST to tell RMAN about the new location
  3. run CROSSCHECK for RMAN to list any file not found at the OS level as EXPIRED
  4. run DELETE EXPIRED to remove any mention of the old location from the RMAN catalog.

Whenever you run a datapump job, a table is created in the schema of the user with whom you launched datapump. That table is called the MASTER TABLE, and takes the name of your JOB_NAME:

expdp system/Admin123@PDBorcl1644 directory=EXP_DIR schemas=oe dumpfile=PDBORCLoe%U.dmp parallel=2 REUSE_DUMPFILES=YES KEEP_MASTER=YES JOB_NAME=THATSMYJOBNAMEFORMYEXPORT

 

SYSTEM > select table_name from user_tables where table_name like '%EXPORT%';
TABLE_NAME                 
THATSMYJOBNAMEFORMYEXPORT

 

But if you run a datapump job without a job_name, the master table will bear a system-generated name:

SYS_EXPORT_SCHEMA_01 or SYS_IMPORT_SCHEMA_01 for "SCHEMAS=" jobs

SYS_IMPORT_FULL_01 or SYS_EXPORT_FULL_01 for "FULL=YES" jobs

 

Unless otherwise indicated (with the KEEP_MASTER=YES parameter), that MASTER TABLE is dropped at the end of a succesful datapump job.  In case of a failed job, however, that MASTER TABLE remains.  So if your daily datapump job is not monitored and keeps failing every day, you will get a new MASTER TABLE per day, with an incremented suffix.

 

Now, let's say that our "FULL=YES" EXPDP  job has failed 53 times.  We  now have 53 of those MASTER TABLES.  Interestingly, if we drop the SYS_EXPORT_FULL_49 MASTER TABLE:

 

SYS AS SYSDBA> drop table sys.SYS_EXPORT_FULL_49 ;

 

49ismissing.jpg

the next time our EXPDP job runs, it will not create the SYS_EXPORT_FULL_54 table, but will re-create our dropped SYS_EXPORT_FULL_49 table:

49isback.png

 

But even more worth of note is that if our datapump job fails 100 times, Oracle does not create SYS_EXPORT_FULL_100 and generates an error

ORA-31626: job does not exist

99.jpg

Conclusions:

  1. let's always monitor our datapump jobs
  2. I recommend using a JOB_NAME with a variable so that the JOB NAME, and thus the MASTER TABLE, will always have a different name.  Examples:

          JOB_NAME=importof${2}${3}into${4}${5} in Unix

         JOB_NAME=refresh%date% in Windows

I had to create JAVA SOURCES (CREATE OR REPLACE JAVA SOURCE NAMED...), and JAVA FUNCTIONS (create or replace function blabla RETURN NUMBER AS LANGUAGE JAVA NAME...).  But they would not compile.  The error was ORA-29548 Java system class reported: could not identify release specified in classes.bin

Searching for the cause, I realized that everything Java-related returned that ORA-29548 error. For instance:

create function get_java_property(prop in varchar2)

return varchar2 is

language java name 'java.lang.System.getProperty(java.lang.String) return java.lang.String';

/

select get_java_property('java.version') from dual;

returned the dreaded ORA-29548.

 

After quite a bit of googling, I found MOS note ID 2149046.1,  which deals with that very issue.  According to that note, in my case, the cause would be that when installing a Java-related PSU, the post-install action (typically, an sql script) was not run.  I had applied a Java-related PSU to a number of Oracle Homes and databases  a few weeks before, so I checked whether all the instalation and post_installation actions had been taken properly.  It turns out that patch 21153423, which I applied to a number of ORACLE_HOMES, requires an sql script to be run in startup upgrade mode:

cd %ORACLE_HOME%\sqlpatch\21153423

sqlplus /nolog

SQL> CONNECT / AS SYSDBA
SQL> shutdown
SQL> startup upgrade
SQL> @postinstall.sql
SQL> shutdown
SQL> startup

 

As you can see, this must be run on every database of each server.  How to check whether it has been run on each database?  The contents of postinstall.sql reveal that this script drops a SYS package:

execute immediate 'drop package SYS.sqljutl2';

So all we have to do is check for the presence of that package.  To that effect, you can use the nifty SQLDeveloper feature found in View/Find Db Ojbect:

SearchSQLJUTL2.jpg Or you can use the following SELECT:

select OBJECT_TYPE,STATUS,OBJECT_NAME,OWNER from DBA_OBJECTS where OBJECT_NAME like '%JUTL2%' order by  OWNER,OBJECT_TYPE,STATUS;

Most databases returned no rows for that SELECT, except one!  And as you have correctly guessed, it was the database on which I was getting ORA-29548.

Si I applied the script according to the steps aforementionned, in startup upgrade mode:

running the postinstall.jpg

 

 

and after restarting my database in normal mode, I was able to compile my Java code!

 

And the SELECT above ran OK:

select get_java_property('java.version') from dual;

GET_JAVA_PROPERTY('JAVA.VERSION')    

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

1.5.0_65

You might remember I kicked off my career as a speaker at Oracle conferences last May (see my July blog post).  After not doing well in Dusseldorf at DOAG Databanken 2016, I was surprised to find out that DOAG selected my new talk "Performance Tuning with SQL Developer" for their big DOAG 2016 conference in Nurenberg on November 15-18, arguably the biggest Oracle conference in Europe.20161115_164219.jpg  On my way to Nurnberg, I stopped over in Köln and made a flash visit to the wonderful Köln cathedral (maybe the tallest in Europe, see picture).  If you go through Köln sometime, do like me, it's a must-see!20161114_094214.jpg

 

The DOAG conference + exhibition is impressive in terms of size and numbers.  I understand there were more than 2000 registered participants, 400 sessions, 120 speakers.

20161115_183910.jpg

There are more pictures to be seen on the DOAG2016 website : https://2016.doag.org/en/photo-gallery-page-2/

 

On the first evening, I got to take part in the Speakers' dinner, in a cosy restaurant in the center of Nurnberg.  On the 2nd day, I got to deliver my presentation in this big 255-seat room (see video)

My talk was mostly a demonstration of SQLDeveloper's performance tuning features.  There was a lot to show but I managed to go through most of what I had prepared in 45mn.

20161116_125027.jpg161116PTWSpanorama.jpg161116PTWSinstanceviewer.jpg

But most of all, I got to attend quality presentations by other speakers, that taught me important stuff like this for example: 20161116_094627.jpg

And you know what?  All the presentation material is available for download: https://2016.doag.org/en/presentations-for-download/

So for me, DOAG 2016 was BIG!!!!

If you have several 11g or 12c databases on the same machine, it can be tedious if your morning routine involves checking their respective ALERT logs.  Thanks to ADRCI, and a shell script of mine, you can save yourself time.

 

In case you did not know, the Oracle Automatic Diagnostic Repository Command Interpreter (ADRCI) allows you to:

  1. display problems (Oracle errors)
  2. display incidents (occurences of problems)
  3. display/search the ALERT log
  4. display/search the LISTENER log
  5. display/search TRACE files
  6. purge TRACE files
  7. package information to be sent to Oracle Support

 

Cool example:

ADRCI>  show alert -p "message_text like '%ORA-%' and originating_timestamp >=systimestamp-180" -term ;

This searches throught your ALERT log for ORA- messages originating in the last 180 days.

 

My script combines the display of INCIDENTS and of the ALERT log for each database on your machine.  So that if you see 0 incidents, you might not even bother to scour the ALERT LOG.  If you do see incidents, then you know straight away what they are and when they occured, without scrolling through the ALERT log, which will still open up if you want to peruse it.

Bdos.jpg

 

Here are the contents of the script for Windows:

 

REM 160825 opens the alert logs of the Oracle databases of this machine YOURSERVERNAME

echo alertlog of 1STDB

adrci exec="set base C:\\oracle;set home diag\\rdbms\\1stdb\\1stdb; show incident ; show alert;"

echo alertlog of 2nddb

adrci exec="set base C:\\oracle;set home diag\\rdbms\\2nddb\\2nddb;show incident ; show alert;"

echo alertlog of 3rddb

adrci exec="set base C:\\oracle;set home diag\\rdbms\\3rddb\\3rddb;show incident ; show alert;"

 

Notes:

  1. the double backslashes are compulsory on Windows
  2. the names of the folders are case-sensitive (if the folder name is upper-case, type it in upper case in the script)
  3. if you do not know your ADRCI base and/or your ADRCI home for each database, you can obtain that with a SELECT * FROM GV$DIAG_INFO ;

 

On Windows, create a bat file with that script and create a shortcut to it (in C:\Users\Public\Desktop for instance).  Then double-clicking on that shortcut will run the bat file (yes, that's one mouse-click!):

Bshortccut.jpg

You will then see in a DOS window the incidents for the 1st database (or hopefully, 0 incidents), and the alert log for the 1st database will open up in Notepad.  You can peruse it if you will, then close it, and in the same DOS window, you will see the incidents (or hopefully, 0 incidents) for the 2nd database, and the alert log for the 2nd database will open up in Notepad.

Bdos2.jpg

This process will repeat for each of your server's databases (or rather, for those you have included in the script).

 

I think this is a nifty way to peruse ALERT logs when you have a handful of databases to check.  It is faster than going to each alert.log location, and than launching 15 ADRCI commands.

Don't hesitate to suggest/request improvements.

 

 

As a long time SQLDeveloper user, I recently thought I should share my know-how and some tips and tricks on that wonderful tool that I deem very helpful to both DBAs and developers.  To that effect, I have designed a 50-mn presentation, with live demos of SQLDeveloper, entitled "How to Improve Your Productivity with SQLDeveloper".  I was selected to give that presentation last May both at DOAG's Databanken 2016 and at OUGF's Harmony 16.

Here is the abstract of my presentation, just to make your mouth water:Harmony16HTIYPWSabstract.jpg

This presentation is mostly a demo of Oracle's SQLDeveloper.  For those who do not yet use that product, it will give an overview of its capabilities and, in all likelihood, make their mouth water.  For those who know it already, it will open their eyes to some fantastic possibilities, right at their fingertips.  This demonstration should render SQLDeveloper more useful to attendees by orders of magnitude.

The demonstration follows a realistic scenario, as we will pretend we are tasked by our manager to
- extract some data from a source database
- create a new table in a destination database
- import that data into that new table
- run into an out-of-space problem, and quickly fix it
- optimise a SELECT statement, compare execution plans
- fix a live performance problem

To accomplish those tasks, we will leverage SQLDeveloper's many user-friendly features, with tips and tricks galore along the wayWe will also cover formatting code, search and replace, command history, reports, schema comparisons, monitoring, automatic script generation, and how to learn more about the Oracle database thanks to SQLDeveloper!

Attendees will leave with
- tips to do their daily tasks faster
- the knowledge that SQLDeveloper can accomplish much more than meets the eye
- ideas to work smarter

Whether you use SQLDeveloper to administer databases, write code, simply interrogate databases, or whatever else you need to do, this presentation will give you many tips and tricks to be better at what you do, and open new possibilities for you and your database projects.

SQLDeveloper comes with a Command Line Interface called SQLcl.  It is touted as the next SQL*Plus, and is indeed a huge improvement.  Those of you used to rlwrap will be glad to know that command history and recall are now natively built into that CLI.  Here is how to run it on Windows (that is, in a DOS session):

1-CD.jpgFirst cd into a directory where you keep your SQL scripts.  It will then be easier to run them with SQLcl

 

2-DIR.jpgHere you can see I 've got a login.sql file, which will be taken into account by SQLcl, just as SQL*Plus would.  You can also see a few .sql files, which I intend to run from SQLcl.

Here are a few examples of command lines to launch SQLcl:

D:\tools\sqldev\sqldeveloperFR\sqldeveloper\bin\sql myuser@//85.01.20.143:1522/ORCL

D:\tools\sqldev\sqldeveloperFR\sqldeveloper\bin\sql myuser/thepassword@//networkalias:1522/ORCL

D:\tools\sqldev\sqldeveloperFR\sqldeveloper\bin\sql myuser@alias   -- that's an alias from your local tnsnames.ora if you have one.

 

I'm going to connect using a user with little privileges, so I do not mind hard-coding the password in the command line:

3-1stlaunch.jpgAs you can see, I launch SQLcl using its full path, so as to keep my current directory as the working directory.  Since my command-line contains all the required information to find my database and connect to it, running it will connect me and I can start typing SQL  statements straight away.


And since my working directory is the one containing my sql script, I can easily run them like this:

4-COUNTobjects.jpg


So the real boon is that now, we can have our own collection of SQLcl command lines, to either just connect to our favorite databases, or run a script straight away.

D:\tools\sqldev\sqldeveloperFR\sqldeveloper\bin\sql myuser/thepassword@//networkalias:1522/ORCL @activesesss

D:\tools\sqldev\sqldeveloperFR\sqldeveloper\bin\sql myuser/thepassword@//networkalias:1522/BIGDB

D:\tools\sqldev\sqldeveloperFR\sqldeveloper\bin\sql myuser/thepassword@//networkalias:1529/OTHERBIGDB


So now, I can just open a DOS windows, paste those 4 lines of text into it, and be connected to my database of choice within a few seconds, along with the result of a script of mine:


D:

cd D:\tools\sqldev\

D:\tools\sqldev\sqldeveloperFR\sqldeveloper\bin\sql bAW00uiVP/bAW00uiVP@//networkalias:1549/xa13 @countobjects

host title B-UIVP

6-finalCLs.jpg


The issue

Schema A contains version v1 of the DDL of an in-house application.  The developers of that application deliver  version v2 and you are tasked to implement on schema A the DDL differences between v1 and v2.

 

What SQLDeveloper can do

 

SQLDeveloper offers a tool, called Database Diff,  to compare several schemas, or objects from several schemas.

P1.jpg

To keep it simple, let’s compare 2 schemas without taking constraints into account:

P2.jpg

Next screen, select the objects you want to be compared:

P3.jpg

In the next screen, you can filter out some objects. Here we’re going to skip it because we want to compare all the objects in both schemas.

 

Next, there’s a summary of what we’ve chosen. Always review it for mistakes!

P4.jpg

When you click on Finish, the following dialog box appears:

P5.jpg

This may run for a long time, and all the while your SQLDeveloper is not available.  But you can click on “Run in Backgound” and use your SQLDeveloper again (you can then monitor the progress of Diff Database in View/Task Progress).

 

When that process finishes, the following “Diff Report” tab appears:

P6.jpg

The objects with a green plus sign are the new objects: those that are in one schema but not in the other.  The objects with an orange warning sign are the objects that are in both schemas but do not have the same definition.

 

If you click on one of those objects, a block appears below with the details of the difference:

P7.jpg

Here the “REPEAT_COUNT” column exists in one schema but not in the other.

 

In the “Script” tab, you will find the necessary DDL to implement the difference:

P8.JPG

And if you click on the “SQL” icon at the top left-hand corner:

p9.jpg

you are given the possibility to create one or several DDL scripts to implement those differences:

P10.jpg

Single File: all the necessary DDL code will be saved to one file of your choice

Separate Files: will create one file per object

Type Files: will create one file for all the indexes, another files for all the tables, another file for all the views etc.

Separate Directories: will create one file per object and sort those files per object type in sub-directories, and create a global script that runs all the others:

--  File created - Wednesday-May-25-2016  

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

@D:\hello\SEQUENCES\SEQ_PROCESS_ID_SEQ.sql

@D:\hello\SEQUENCES\SEQ_ACTIVITY_ID_SEQ.sql

@D:\hello\TABLES\DS001_QZ_SIMPLE_TRIGGERS.sql

@D:\hello\TABLES\DS001_PROCESS_INSTANCE_IN_USE.sql

@D:\hello\TABLES\DS001_ACTIVITY.sql

@D:\hello\INDEXES\IDX_QZ_FT_JG.sql

Clipboard: will copy all the necessary DDL to the clipboard

Worksheet: will copy all the necessary DDL to a new SQLDeveloper worksheet, like this:

P11.jpg

so you can run the DDL straight away from that new worksheet.

 

That was an overview of what Database Diff can do for you.  Please leave comments or questions below.

LIMIT the Duration of your Nightly Batch Job: implement a session maximum duration


The issue at hand

In your production environment, the nightly batch job spilled over the morning activity. How can you limit the duration of your batch job (or any Oracle session for that matter) ? Let's do it with the Oracle Profile CONNECT_TIME feature because it's easy to implement.


The feature we are going to use: CONNECT_TIME in a PROFILE

A profile is an Oracle feature that you ascribe to Oracle users to limit what they can do. It's typically used to manage the fact that an account (ie an Oracle user) will lock after a set number of failed logon attempts.

Here is what a profile can manage:

For passwords:

  • Expire in (days)         
  • Lock (days past expiration)  
  • Number of passwords to keep          
  • Number of days to keep for 
  • Complexity function 
  • No. failed logins       
  • No. days to lock for

For resources:

  • Concurrent Sessions (Per User)        
  • Reads/Session (Blocks)        
  • Reads/Call (Blocks)  
  • Private SGA (KBytes)          
  • Composite Limit (Srvc. Units)
  • Profile CPU/Session (Sec./100)        
  • CPU/Call (Sec./100) 
  • Idle Time (Minutes)
  • Connect Time (Minutes)

DEFPROFILE.PNGlimibatch.JPG

How to setup a PROFILE

To benefit from a profile's features, you must

  1. set the resource_limit parameter to TRUE
  2. create a profile with the desired limitations
  3. ascribe that profile to the relevant Oracle users

 

Example of the implementation of a PROFILE named "limitebatch", with connect_time=180

ALTER SYSTEM SET RESOURCE_LIMIT=TRUE ;  --1

-- DROP PROFILE limitebatch cascade;

show parameter resource_limit ;

CREATE PROFILE limitebatch LIMIT CONNECT_TIME 180 ;  -- 2

alter user utilisateurbatch PROFILE limitebatch;            -- 3

Select username,profile from dba_users where lower(username) ='utilisateurbatch' ;

 

NAME           TYPE    VALUE

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

resource_limit boolean TRUE 

 

Profile LIMITEBATCH créé(e).

 

 

User UTILISATEURBATCH modifié(e).

 

 

USERNAME                       PROFILE                     

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

UTILISATEURBATCH                      LIMITEBATCH                  

UPROPRIETAIRE                      DEFAULT

 

Once this profile has been ascribed to that user, no session from that user will be able to last more than 3H.  If the 3H limit is reached,here is what you will find in the batch log:

 

ERROR at line 1:
ORA-02399:,temps max. de connexion dépassé, vous êtes déconnecté
ORA-06512: à ligne 2272
ORA-02399: temps max. de connexion dépassé, vous êtes déconnecté
Disconnected from Oracle Database 11g Enterprise Edition Release

To disable that feature, do not drop the profile (so as to keep it for future use), but ascribe the DEFAULT profile to your user:

ALTER USER UTILISATEURBATCH PROFILE DEFAULT ; 

SELECT USERNAME,PROFILE FROM DBA_USERS WHERE lower(username) ='utilisateurbatch';

 

What really happens when a session is disconnected because the CONNECT_TIME limit has been reached?

The ORA-02399 error is generated of course, but in concrete terms, according to Oracle documentation

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6010.htm

If a user exceeds the CONNECT_TIME or IDLE_TIME session resource limit, then the database rolls back the current transaction and ends the session. When the user process next issues a call, the database returns an error.

LIMITER LA DUREE d’un BATCH : mise en place d’un temps maximum d’activité


La problématique

Plusieurs fois, en production, des batchs de nuit ont empiété sur le matin. Comment limiter la durée des batchs ? Voyons ensemble la fonctionnalité  CONNECT_TIME des profils car elle est simple à mettre en oeuvre.

 

La fonctionnalité : le CONNECT_TIME du PROFILE

Le profil est une fonctionnalité Oracle, qu’on attribue aux utilisateurs Oracle pour limiter ce qu’ils peuvent faire. C’est notamment utilisé pour gérer le fait que le compte se verrouille après tant d’erreurs de mot de passe.

Voici ce que peut gérer un profil :

Pour les mots de passe :

  • Expire in (days)     
  • Lock (days past expiration)
  • Number of passwords to keep      
  • Number of days to keep for
  • Complexity function
  • No. failed logins   
  • No. days to lock for

Pour les ressources :

  • Concurrent Sessions (Per User)      XKCD+-+1073.png
  • Reads/Session (Blocks)    
  • Reads/Call (Blocks)
  • Private SGA (KBytes)      
  • Composite Limit (Srvc. Units)
  • Profile CPU/Session (Sec./100)    
  • CPU/Call (Sec./100)
  • Idle Time (Minutes)
  • Connect Time (Minutes)

limibatch.JPGDEFPROFILE.PNG

Mode d’emploi du PROFILE

Pour bénéficier des fonctionnalités d’un profil, il faut

  1. changer le paramètre resource_limit pour le passer à TRUE
  2. créer un profil avec les limites voulues
  3. attribuer ce profil aux utilisateurs concernés

 

Exemple de mise en place d'un PROFILE nommé "limitebatch", avec connect_time=180

ALTER SYSTEM SET RESOURCE_LIMIT=TRUE ;  --1

-- DROP PROFILE limitebatch cascade;

show parameter resource_limit ;

CREATE PROFILE limitebatch LIMIT CONNECT_TIME 180 ;  -- 2

alter user utilisateurbatch PROFILE limitebatch;            -- 3

Select username,profile from dba_users where lower(username) ='utilisateurbatch' ;

 

NAME           TYPE    VALUE

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

resource_limit boolean TRUE 

 

Profile LIMITEBATCH créé(e).

 

 

User UTILISATEURBATCH modifié(e).

 

 

USERNAME                       PROFILE                     

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

UTILISATEURBATCH                      LIMITEBATCH                  

UPROPRIETAIRE                      DEFAULT

 

Un fois le profil attribué à l’utilisateur, aucune session de cet utilisateur ne pourra durer au-delà de 3H.

Si cela se produit, voici ce qu’on trouvera dans la log du batch :

 

ERROR at line 1:
ORA-02399:,temps max. de connexion dépassé, vous êtes déconnecté
ORA-06512: à ligne 2272
ORA-02399: temps max. de connexion dépassé, vous êtes déconnecté
Disconnected from Oracle Database 11g Enterprise Edition Release

Pour désactiver cette fonctionnalité, ne pas supprimer le profil (il pourra resservir), mais attribuer à l’utilisateur le profil DEFAULT :

ALTER USER UTILISATEURBATCH PROFILE DEFAULT ;

SELECT USERNAME,PROFILE FROM DBA_USERS WHERE lower(username) ='utilisateurbatch';

 

Que se passe-t’il quand une session est déconnectée ?

Cela génère l’erreur ORA-02399 bien sûr, mais concrètement, d’après la doc oracle

http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_6010.htm

Si un utilisateur dépasser la limite CONNECT_TIME or IDLE_TIME,  la transaction en cours de l’utilisateur est rollbackée et sa session coupée. Lorsque le processus utilisateur fait un nouvel appel server, la base de données renvoie l’erreur ora-02399.

The following was simulated on 12.1.0.2 but it's similar on older versions (=> 10gR2 though).

When faced with the "buffer busy waits" wait event, it's interesting to find out which blocks are being accessed simultaneously, and thus generating that wait event, so as to determine which part of your code is causing that Concurrency.  Granted, an AWR report will report the most contended for segments (in the "Segments by Buffer Busy Waits" section), but they may not necessarily be the segments behind the "buffer busy waits"  events you want to investigate.

So let's have several concurrent sessions update the same blocks and observe what happens.  Then we will discover how to determine which object those blocks belong to.


I've got a CHANNELS2 table that contains 5 rows scattered over 4 blocks. 

BLOCKS    TABLE_NAME

4    CHANNELS2

 

I am going to update that table heavily with 6 concurrent sessions.  Each session is going to update all of that same CHANNELS2 table 1 million times (nevermind the usefulness of this -- it's strictly for demo purposes). 

BEGIN

FOR i IN 1 .. 100000 loop

UPDATE channels2 SET channel_id=rownum+i;

COMMIT ;

END loop;

END;

/

 

While the 6 concurrent user sessions are running, my SYSTEM session will monitor v$session in real-time to observe those "buffer busy waits" wait events, with that SELECT, run manually at short intervals:

SELECT username,SID,p1,p2,event,status,logon_time,blocking_session,wait_time,ROW_WAIT_OBJ#,ROW_WAIT_ROW# FROM v$session WHERE TYPE='USER' ORDER BY logon_time;

In order to witness that for yourselves, please watch the enclosed short video, which has the added boon to demonstrate how to do it in a nifty way with SQLDeveloper.

The important information reported by that SELECT is the P1 and P2 columns, which, for each buffer busy wait, list the file_id and the block id of the data block that is being contended for.

SH    247    111286803 buffer busy waits                   ACTIVE   04:12:28                        0 964370
SH    249    111286803 buffer busy waits                    ACTIVE   04:14:34                        0 964370
SH    131    111286803 buffer busy waits                   ACTIVE   04:14:40                        0 964370

 

7 rows selected

 

USERNAME   SID    P1    P2 EVENT                               STATUS   LOGON_TIME BLOCKING_SESSION  WAIT_TIME ROW_ ROW_

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

SYSTEM129 1413697536     1 SQL*Net message to client           ACTIVE   02:58:50                       -1 938870
SH    250 1415053318262167 enq: TX - row lock contention       ACTIVE   03:31:25           247     0 964370
SH    364    111286803 buffer busy waits                   ACTIVE   03:32:46                        0 964370
SH    362 1413697536     1 SQL*Net message from client         INACTIVE 03:33:39                        0   -10
SH    247    111286803 buffer busy waits                   ACTIVE   04:12:28                        0 964370
SH    249    111286803 buffer busy waits                   ACTIVE   04:14:34                        0 964370
SH    131    111286803 buffer busy waits                   ACTIVE   04:14:40                       -1   -10

 

7 rows selected

 

USERNAME   SID    P1    P2 EVENT                               STATUS   LOGON_TIME BLOCKING_SESSION  WAIT_TIME ROW_ ROW_

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

SYSTEM129 1413697536     1 SQL*Net message to client           ACTIVE   02:58:50                       -1 938870
SH    250 2364282784   354 latch: In memory undo latch         ACTIVE   03:31:25           247     0 964370
SH    364    111286803 buffer busy waits                   ACTIVE   03:32:46                        0 964370
SH    362 1413697536     1 SQL*Net message from client         INACTIVE 03:33:39                        0   -10
SH    247     4   224 buffer busy waits                   ACTIVE   04:12:28                       -1   -10
SH    249    111286803 buffer busy waits                   ACTIVE   04:14:34                        0 964370
SH    131    111286803 buffer busy waits                   ACTIVE   04:14:40                        0 964370

 

So, as the video plainly shows (and the short text excertp here above), the most contended for block is block 1286803 of file#11.  First let's find out what file11 is:

FILE#    NAME

11    /home/oracle/app/oracle/oradata/cdb1/orcl/example01.dbf

which undoubtedly belongs to the EXAMPLE tablespace.  But which object(s) of that tablespace contain(s) block 1286803?  There are several ways to find out, but this one, if you can afford it, might be the quickest.

With my SYSTEM session (SID 129, as identified here above):

ALTER SESSION SET TRACEFILE_IDENTIFIER = mydumpedblock;

alter system dump datafile 11 block 1286803 ;

This ALTER SYSTEM DUMP command dumps the contents of datablock 1286803 into a text file, located in my "Diag Trace" folder.

select * from v$diag_info where name='Diag Trace';

INST_ID    NAME    VALUE    CON_ID

1    Diag Trace    /home/oracle/app/oracle/diag/rdbms/cdb1/cdb1/trace    0

 

Now in a terminal, I can peruse the generated file, which I can easily identify thanks to the TRACEFILE_IDENTIFIER used above:

[oracle@localhost trace]$ ls -lrtR | tail

-rw-r-----. 1 oracle oracle      81 Mar 28 18:25 cdb1_ora_6638.trm

-rw-r-----. 1 oracle oracle   41669 Mar 28 18:25 cdb1_ora_6638.trc

-rw-r-----. 1 oracle oracle     670 Mar 28 18:30 cdb1_mmon_2174.trm

-rw-r-----. 1 oracle oracle    7140 Mar 28 18:30 cdb1_mmon_2174.trc

-rw-r-----. 1 oracle oracle      70 Mar 28 18:31 cdb1_ora_6638_MYDUMPEDBLOCK.trm

-rw-r-----. 1 oracle oracle   39463 Mar 28 18:31 cdb1_ora_6638_MYDUMPEDBLOCK.trc

The header of that trace file confirms that it was generated by my SID 129 session:

*** SESSION ID:(129.13337) 2016-03-28 18:31:56.662

The data to the right-hand side of the dump file shows some of the alphanumeric data from my CHANNELS2 table:

[Direct Sales.Dir]

[ect....Channel t]

[otal...,.....Yd.]

[Tele Sales.Direc number: this

But how do you tell for sure which object this data is coming from?  Keep in mind this object could be an index.  Look for the "seg" character string in that dump file, and you wil find "seg/obj" followed by an hexadecimal: this is the object_id of the object you are looking for.

seg/obj: 0x178b5

  Grab a scientific calculator and convert to decimal this 178b5 heximal value.  That's 96 437 in decimal.

select owner, object_type ||' '||object_name " the long sought-after object" from all_objects  where object_id=96437;

OWNER     the long sought-after object

SH    TABLE CHANNELS2

And voilà!  With a few SELECTs and a block dump, I was able to determine which block of which object was causing my "buffer busy waits" wait events.



Additonal note for version 12c: the object_id appears in plain decimal in the dump file:

BH (0x883dad98) file#: 11 rdba: 0x0293a293 (10/1286803) class: 1 ba: 0x8807e000

  set: 12 pool: 3 bsz: 8192 bsi: 0 sflg: 0 pwc: 0,0

  dbwrid: 0 obj: 96437

But in version 12c, it's even useless to generate a dump file, since the object_id of the "buffer busy waits" event appears in the ROW_WAIT_OBJ# column of v$session.

SH    249    111286803 buffer busy waits                   ACTIVE   04:14:34                        0 964370
SH    131    111286803 buffer busy waits                   ACTIVE   04:14:40                        0 96437

The famous EXPLAIN PLAN FOR command gives you the planned execution plan for a given query.  Here is an example:


explain plan for

  2  select  prod_id,unit_cost,unit_price from costs where unit_price > 700      ;

Plan FOR succeeded.

SQL> SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT                                                                                                                                 

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

Plan hash value: 1918128980                                                                                                                        

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

| Id  | Operation           | Name  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |                                                      

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

|   0 | SELECT STATEMENT    |       | 50050 |   684K|   136   (1)| 00:00:01 |       |       |                                                      

|   1 |  PARTITION RANGE ALL|       | 50050 |   684K|   136   (1)| 00:00:01 |     1 |    28 |                                                      

|*  2 |   TABLE ACCESS FULL | COSTS | 50050 |   684K|   136   (1)| 00:00:01 |     1 |    28 |                                                      

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

Predicate Information (identified by operation id):                                                                                                

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

   2 - filter("UNIT_PRICE">700)

 

This is the planned execution plan for my SELECT -- it is what Oracle intends to do to execute my query, after evaluating, to the best of its knowledge (statistics), several execution plans. When you ask for EXPLAIN PLAN FOR, the query is not actually run -- only the intended execution plan is computed.  This comes in handy for queries that last a long time, the performance of which you need to assess.  The resulting execution plan gives you  an idea of how well that query is going to perform if you actually run it.


If you can afford to run the query (if you can wait for it to complete, if you are not on a production system, if you are allowed to modify data...), do it and ask Oracle to show the plan it actually used.  This is better for 2 reasons:

  1. if you actually run the query, you can time it (SQLDeveloper automatically tells you how long your script/query has run at the top of its result tab; or use set timing on in SQL+ ) and get an accurate idea of how well your query performs (in your current environnment)
  2. you get the plan that was actually used by Oracle to execute your query, and some extra information (see below)


Here is how to do it.  This is in SQLCLI and on Oracle 12.1.0.2 but it's the same in SQL+ and on 11g.

 

create table costs2 as select * from costs;

create index fd_up2 on costs2 (unit_price) ;

select TABLE_NAME, NUM_ROWS,LAST_ANALYZED from USER_TABLES where TABLE_NAME like 'COST%' ;

 

explain plan for

select prod_id,unit_cost,unit_price from costs2 where unit_price > 700;

SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY);

 

select /*+ gather_plan_statistics */  prod_id,unit_cost,unit_price from costs2 where unit_price > 700;

SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST')) ;

 

Output:

SQL> create table costs2 as select * from costs;

Table COSTS2 created.

SQL> create index fd_up2 on costs2 (unit_price) ;

Index FD_UP2 created.

SQL> select TABLE_NAME, NUM_ROWS,LAST_ANALYZED from USER_TABLES where TABLE_NAME like 'COST%' ;

TABLE_NAMENUM_ROWS LAST_ANALYZED

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

COSTS2      82112 29-FEB-16
COSTS       82112 18-OCT-15

 

As as side note, here is demonstrated a nifty 12c feature: table statistics are automatically gathered at creation time.  My COSTS2 table has just been created  and NUM_ROWS is not empty even though I have not run DBMS_STATS.GATHER_TABLE_STATS on it.  Statistics have been gathered for it, as witnessed by today's date in the LAST_ANALYZED column.


explain plan for

  2   select prod_id,unit_cost,unit_price from costs2 where unit_price > 700;

Plan FOR succeeded.

 

SQL> SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT                                                                                                                                 

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

Plan hash value: 2840313604                                                                                                                        

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

| Id  | Operation         | Name   | Rows  | Bytes | Cost (%CPU)| Time     |                                                                       

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

|   0 | SELECT STATEMENT  |        | 50050 |   684K|   104   (1)| 00:00:01 |                                                                       

|*  1 |  TABLE ACCESS FULL| COSTS2 | 50050 |   684K|   104   (1)| 00:00:01 |                                                                       

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

Predicate Information (identified by operation id):                                                                                                

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

   1 - filter("UNIT_PRICE">700)

 

select /*+ gather_plan_statistics */  prod_id,unit_cost,unit_price from costs2 where unit_price > 700;

 

14790.93

1190.69

... thousands of result lines here ....

21808.861026.61
14790.931190.69
141001.751219.13

 

6,161 rows selected

Elapsed: 410

 

SELECT *  FROM   TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST')) ;

PLAN_TABLE_OUTPUT                                                                                                                                 

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

SQL_ID  74rkrtfzxh4yk, child number 0                                                                                                              

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

select /*+ gather_plan_statistics */  prod_id,unit_cost,unit_price from                                                                            

costs2 where unit_price > 700                                                                                                                      

                                                                                                                                                   

Plan hash value: 2840313604                                                                                                                        

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

| Id  | Operation         | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |                                                             

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

|   0 | SELECT STATEMENT  |        |      1 |        |   6161 |00:00:00.01 |     777 |                                                             

|*  1 |  TABLE ACCESS FULL| COSTS2 |      1 |  50050 |   6161 |00:00:00.01 |     777 |                                                             

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

Predicate Information (identified by operation id):                                                                                                

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

   1 - filter("UNIT_PRICE">700)

 

The top execution plan is the result of our EXPLAIN PLAN FOR command.  It contains, as displayed by our SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY), the id, operation, name, rows, bytes, cost, and time columns.

While the bottom execution plan is the one that was actually executed by Oracle, as it appears after the execution of our query, as displayed by our SELECT *  FROM  TABLE (DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST')).  It contains the Id, operation, name, starts, e-rows, a-rows, a-time, and buffers columns.  The e-rows column contains the same data as the rows column of the 1st SELECT, as it is the estimated rows.  The a-rows column is the actual rows.  So not only do you get the actual plan but you also discover whether your statistics are pertinent.


So how do you get that actual plan?  You must implement 2 things:

  1. as you can see in the example above, you must add the /*+ gather_plan_statistics */ hint to your query.  This tells Oracle to gather extra execution statistics as it executes the query.  It does add a small overhead, so use with caution.
  2. set serverouput must be set to off, as the SELECT *  FROM  TABLE (DBMS_XPLAN.DISPLAY_CURSOR(null,null,'ALLSTATS LAST')) will display the statistics of the query that was last run in that session. And set serveroutput on adds an extra call to to DBMS_OUTPUT.